1. 从PDB seed 创建 PDB
从上图中,我们可以看到pdb$seed的目录是pdbseed.
SQL> CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb3admin IDENTIFIED BY password FILE_NAME_CONVERT=('/u01/app/oracle/oradata/ORCL/pdbseed/','/u01/app/oracle/oradata/ORCL/pdb3/');-- PDB3 如果和PDB seed 放在同一个目录下,可以直接用 FILE_NAME_CONVERT=('pdbseed','pdb3')代替
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB3 MOUNTED 5 PDB2 MOUNTED SQL> alter pluggable database pdb3 open;
Pluggable database altered.
2.从其他本地PDB(LOCAL PDB)创建PDB
SQL> show con_name;
CON_NAME ------------------------------ CDB$ROOT SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB3 MOUNTED 5 PDB2 MOUNTED SQL> create pluggable database pdb4 from pdb3 file_name_convert=('pdb3','pdb4'); create pluggable database pdb4 from pdb3 file_name_convert=('pdb3','pdb4') * ERROR at line 1: ORA-65036: pluggable database PDB3 not open in required mode
SQL> alter pluggable database pdb3 open;
Pluggable database altered.
SQL> create pluggable database pdb4 from pdb3 file_name_convert=('pdb3','pdb4');--用PDB3创建PDB4
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB3 READ WRITE NO 5 PDB2 MOUNTED 7 PDB4 MOUNTED SQL> alter pluggable database pdb4 open;
3.从远程PDB创建新的PDB(target PDB is pdb22, source PDB is pdb1)
In the source database(PDB1) execute the following sql
QL> alter session set container=pdb1;
Session altered.
SQL> show con_name;
CON_NAME ------------------------------ PDB1 SQL> grant create pluggable database to pdbadmin;--without this permission grant, you give get the following error
ERROR at line 1: ORA-17628: Oracle error 1031 returned by remote Oracle server ORA-01031: insufficient privileges
Grant succeeded.
In the target database execute the following SQL
SQL> show con_name;
CON_NAME ------------------------------ CDB$ROOT SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB11 READ WRITE NO
SQL> create database link pdb1_lnk connect to pdbadmin identified by password using 'pdb1';
Database link created.
SQL> create pluggable database pdb22 from pdb1@pdb1_lnk;
Pluggable database created.
SQL>
4. UNPLUG PDB from source database and PLUG database to target database.
Doing the following operation from source database.
SQL> select name from v$database;
NAME --------- ORCL
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB3 MOUNTED 5 PDB2 MOUNTED 7 PDB4 MOUNTED SQL> alter pluggable database pdb3 2 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 4 PDB3 READ WRITE NO 5 PDB2 MOUNTED 7 PDB4 MOUNTED SQL> alter pluggable database pdb2 open;
Pluggable database altered.
SQL> alter pluggable database pdb4 open;
Pluggable database altered.
SQL> SQL> SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB3 READ WRITE NO 5 PDB2 READ WRITE NO 7 PDB4 READ WRITE NO SQL> show con_name;
CON_NAME ------------------------------ CDB$ROOT SQL> select name from v$database;
NAME --------- ORCL
SQL> alter pluggable database pdb4 unplug into '/u01/app/oracle/oradata/pdb4.xml'; alter pluggable database pdb4 unplug into '/u01/app/oracle/oradata/pdb4.xml' * ERROR at line 1: ORA-65025: Pluggable database PDB4 is not closed on all instances.
SQL> alter pluggable database pdb4 close;
Pluggable database altered.
SQL> alter pluggable database pdb4 unplug into '/u01/app/oracle/oradata/pdb4.xml'; --the xml will used to create pdb on the target datatabase
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB3 READ WRITE NO 5 PDB2 READ WRITE NO 7 PDB4 MOUNTED
SQL> drop pluggable database pdb4 keep datafiles;
Pluggable database dropped.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB3 MOUNTED 5 PDB2 MOUNTED
Do the following operation on the target databases SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB11 MOUNTED 4 PDB22 MOUNTED SQL> show con_name;
CON_NAME ------------------------------ CDB$ROOT SQL> select * from global_name;
GLOBAL_NAME -------------------------------------------------------------------------------- ORCL19C2
CREATE PLUGGABLE DATABASE pdb4 USING '/u01/app/oracle/oradata/pdb4.xml' --the path should be the same as we saved in the source database NOCOPY 3 TEMPFILE REUSE;
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB11 MOUNTED 4 PDB22 MOUNTED 6 PDB4 MOUNTED SQL> alter pluggable database pdb4 open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB11 MOUNTED 4 PDB22 MOUNTED 6 PDB4 READ WRITE NO SQL>
参考文档;Oracle Multitenant Administrator’s Guide, 19chttps://docs.oracle.com/en/database/oracle/oracle-database/19/multi/index.html
相关链接
发表评论