导入导出

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

创建PDB#

1.查看当前容器

$ sqlplus / as sysdba
SQL> show con_name;
 
CON_NAME
------------------------------
CDB$ROOT

2. 创建一个新的PDB

$ 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

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

4. 删除一个pdb

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

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

1）启动pdb和关闭pdb

a) 启动pdb

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

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

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

SQL> shutdown immediate;
Pluggable Database closed.
SQL> show pdbs;
 
    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     3 PDB1               MOUNTED

6. 配置监听

$ 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. 测试与登陆

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

数据泵导入导出PDB数据#

以pdb1数据库为例

1. 启动pdb1数据库

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

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

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

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

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

4. 创建表空间

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. 导出数据