admin

oracle12c PDB创建/删除/导入导出

admin 数据库 2021-05-10 1001浏览 0

创建PDB#

1.查看当前容器

1
2
3
4
5
6
$ sqlplus / as sysdba
SQL> show con_name;
 
CON_NAME
------------------------------
CDB$ROOT

 2. 创建一个新的PDB

1
2
3
4
5
6
mkdir -p /data/app/oracle/oradata/pdb1
 
SQL> create pluggable database pdb1 admin
user pdb1 identified by pdb1
file_name_convert=('/data/app/oracle/oradata/pdbseed/',
'/data/app/oracle/oradata/pdb1/');

3. 查看所有PDB

1
2
3
4
5
6
SQL> show pdbs;
 
    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED           READ ONLY  NO
     3 PDB1               MOUNTED

4. 删除一个pdb

1
2
SQL> alter pluggable database pdb2 close;
SQL> DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES;

5. 启动和关闭一个创建好的PDB

1)启动pdb和关闭pdb

    a) 启动pdb

1
2
3
4
5
6
7
8
9
10
SQL> alter pluggable database pdb1 open;
 
Pluggable database altered.
 
SQL> show pdbs;
 
    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED           READ ONLY  NO
     3 PDB1               READ WRITE NO

    b)关闭pdb

1
2
3
4
5
6
7
8
9
10
SQL> alter pluggable database pdb1 close;
 
Pluggable database altered.
 
SQL> show pdbs;
 
    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED           READ ONLY  NO
     3 PDB1               MOUNTED

 2)通过sqlplus使用传统的startup和shutdown命令来启动和关闭PDB

    a) 启动pdb

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> alter session set container=pdb1;
 
Session altered.
 
SQL> show pdbs;
 
    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     3 PDB1               MOUNTED
SQL> startup
Pluggable Database opened.
SQL> show pdbs;
 
    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     3 PDB1               READ WRITE NO

    b) 关闭pdb

1
2
3
4
5
6
7
SQL> shutdown immediate;
Pluggable Database closed.
SQL> show pdbs;
 
    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     3 PDB1               MOUNTED

6. 配置监听

1
2
3
4
5
6
7
8
9
10
11
12
13
$ vim $ORACLE_HOME/network/admin/tnsnames.ora    //如果此文件不存在,手动创建
 
LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = DB)(PORT = 1521))
 
pdb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DB)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )

7. 测试与登陆

1
2
$ tnsping pdb1              //测试登陆
$ sqlplus pdb1/pdb1@pdb1            //登陆

 数据泵导入导出PDB数据#

以pdb1数据库为例

1. 启动pdb1数据库

1
2
3
$ sqlplus / as sysdba
SQL> alter session set container=pdb1;
SQL> startup

 2. 创建导入数据目录和数据库映射关系

1
2
3
mkdir -p /home/oracle/dump_dir
SQL> alter session set container=pdb1;
SQL> create directory dump_dir as '/home/oracle/dump_dir';

 3. 创建一个dba权限的数据泵导入导出用户

1
2
SQL> grant dba to dp identified by dp;
SQL> grant read,write on directory dump_dir to dp;

4. 创建表空间

1
2
3
SQL> CREATE TABLESPACE "PDB1" DATAFILE
'/data/app/oracle/oradata/pdb1/pdb101.dbf' SIZE 128M AUTOEXTEND
ON NEXT 128M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

5. 导出数据

1
$ expdp dp/dp@pdb1 directory="dump_dir" dumpfile="aa.dmp" logfile=aa.log

6. 导入数据

1
$ impdp dp/dp@pdb1 directory="dump_dir" dumpfile="aa.dmp" logfile


版权声明

本站《作品展示》类文章均为原创,转载必须注明出处,技术分享类文章部分来源于网络,版权归原作者所有,若侵权请留言。