Oracle 创建PDB-Plugging In an Unplugged PDB
一：
Unplugging PDB
---187 服务器将
SQL > alter session set container = chenpdb ;
Session altered.
--- 查看数据文件
SQL > select file_name from dba_data_files ;
FILE_NAME
--------------------------------------------------------------------------------
/ u01 / app / oracle19 / oradata / CHENDB / chenpdb / system01.dbf
/ u01 / app / oracle19 / oradata / CHENDB / chenpdb / sysaux01.dbf
/ u01 / app / oracle19 / oradata / CHENDB / chenpdb / undotbs01.dbf
/ u01 / app / oracle19 / oradata / CHENDB / chenpdb / users01.dbf
--- 查看临时文件
SQL > select file_name from dba_temp_files ;
FILE_NAME
--------------------------------------------------------------------------------
/ u01 / app / oracle19 / oradata / CHENDB / chenpdb / temp01.dbf
--控制文件
SQL > select name from v$controlfile ;
NAME
--------------------------------------------------------------------------------
/ u01 / app / oracle19 / oradata / CHENDB / control01.ctl
/ u01 / app / oracle19 / oradata / CHENDB / control02.ctl
---日志文件
SQL > select member from v$logfile ;
MEMBER
--------------------------------------------------------------------------------
/ u01 / app / oracle19 / oradata / CHENDB / redo03.log
/ u01 / app / oracle19 / oradata / CHENDB / redo02.log
/ u01 / app / oracle19 / oradata / CHENDB / redo01.log
SQL > conn / as sysdba
--- 提前备份chenpdb数据库
SQL > alter pluggable database chenpdb close immediate ;
SQL > alter pluggable database chenpdb unplug into '/home/oracle/chenpdb01.xml' ;
SQL > drop pluggable database chenpdb keep datafiles ;
Pluggable database dropped.
SQL > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
[oracle @ rac1 ~]$ ll - rth chenpdb *
- rw - r --r-- 1 oracle asmadmin 7.5K Mar 30 15:02 chenpdb.xml
- rw - r --r-- 1 oracle asmadmin 7.5K Mar 30 15:09 chenpdb01.xml
二：Plug Unplugging PDB
---187 将chenpdb数据库插回187服务器，并重命名为chenpdb01
SQL > create pluggable database chenpdb01 using '/home/oracle/chenpdb01.xml' ;
ERROR at line 1 :
ORA - 65018 : FILE_NAME_CONVERT or NOCOPY must be specified
SQL >
create pluggable database chenpdb01 using '/home/oracle/chenpdb01.xml'
FILE_NAME_CONVERT = ( '/u01/app/oracle19/oradata/CHENDB/chenpdb/' ,
'/u01/app/oracle19/oradata/CHENDB/chenpdb/' );
ERROR at line 1 :
ORA - 65180 : duplicate file name encountered -
/ u01 / app / oracle19 / oradata / CHENDB / chenpdb / system01.dbf
SQL > create pluggable database chenpdb01 using '/home/oracle/chenpdb01.xml' nocopy ;
Pluggable database created.
SQL > alter pluggable database chenpdb01 open ;
Pluggable database altered.
SQL > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 CHENPDB01 READ WRITE NO
---222 将187服务器拔下的chenpdb数据库插入到222服务器上，并重命名cjcpdb05
[oracle @ cjcos ~]$ mkdir chenpdb
---187
[oracle @ rac1 ~]$ scp chenpdb01.xml 192.168 .2.222 :/ home / oracle /
[oracle @ rac1 chenpdb]$ pwd
/ u01 / app / oracle19 / oradata / CHENDB / chenpdb
[oracle @ rac1 chenpdb]$ scp * 192.168 .2.222 :/ home / oracle / chenpdb
---222
[oracle @ cjcos ~]$ ll - rth / home / oracle / chenpdb
total 716 M
- rw - r ----- 1 oracle oinstall 341M Mar 30 15:27 sysaux01.dbf
- rw - r ----- 1 oracle oinstall 271M Mar 30 15:27 system01.dbf
- rw - r ----- 1 oracle oinstall 101M Mar 30 15:27 undotbs01.dbf
- rw - r ----- 1 oracle oinstall 5.1M Mar 30 15:27 users01.dbf
SQL >
set serveroutput on
DECLARE
l_result BOOLEAN ;
BEGIN
l_result := DBMS_PDB.check_plug_compatibility ( pdb_descr_file => '/home/oracle/chenpdb01.xml' ,
pdb_name => 'CHENPDB' );
IF l_result THEN
DBMS_OUTPUT.PUT_LINE ( 'Yes' );
ELSE
DBMS_OUTPUT.PUT_LINE ( 'No' );
END IF ;
END ;
/
Yes
PL / SQL procedure successfully completed.
---- 将chenpdb01.xml中/u01/app/oracle19/oradata/CHENDB路径改成/home/oracle
SQL >
CREATE PLUGGABLE DATABASE cjcpdb05 USING '/home/oracle/chenpdb01.xml'
FILE_NAME_CONVERT = ( '/home/oracle/chenpdb/' ,
'/u01/app/oracle12/oradata/cjcpdb05/' );
Pluggable database created.
--- 对应告警日志信息
2020 - 03 - 30 T16 : 03 : 31.128371 + 08 : 00
CREATE PLUGGABLE DATABASE cjcpdb05 USING '/home/oracle/chenpdb01.xml'
FILE_NAME_CONVERT = ( '/home/oracle/chenpdb/' ,
'/u01/app/oracle12/oradata/cjcpdb05/' )
2020 - 03 - 30 T16 : 04 : 19.547571 + 08 : 00
CJCPDB05 ( 7 ): Endian type of dictionary set to little
2020 - 03 - 30 T16 : 04 : 21.656166 + 08 : 00
****************************************************************
Pluggable Database CJCPDB05 with pdb id - 7 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW ,
then the pdb must be dropped
local undo - 1 , localundoscn - 0 x0000000000000118
****************************************************************
CJCPDB05 ( 7 ): Autotune of undo retention is turned on.
2020 - 03 - 30 T16 : 04 : 23.125207 + 08 : 00
CJCPDB05 ( 7 ): Undo initialization recovery : err : 0 start : 1187946727 end : 1187946728 diff : 1 ms ( 0.0 seconds )
CJCPDB05 ( 7 ): [ 1687 ] Successfully onlined Undo Tablespace 2 .
CJCPDB05 ( 7 ): Undo initialization online undo segments : err : 0 start : 1187946729 end : 1187946770 diff : 41 ms ( 0.0 seconds )
CJCPDB05 ( 7 ): Undo initialization finished serial : 0 start : 1187946726 end : 1187946772 diff : 46 ms ( 0.0 seconds )
CJCPDB05 ( 7 ): Database Characterset for CJCPDB05 is AL32UTF8
CJCPDB05 ( 7 ): JIT : pid 1687 requesting stop
CJCPDB05 ( 7 ): Buffer Cache flush started : 7
CJCPDB05 ( 7 ): Buffer Cache flush finished : 7
2020 - 03 - 30 T16 : 04 : 25.217625 + 08 : 00
Completed : CREATE PLUGGABLE DATABASE cjcpdb05 USING '/home/oracle/chenpdb01.xml'
FILE_NAME_CONVERT = ( '/home/oracle/chenpdb/' ,
'/u01/app/oracle12/oradata/cjcpdb05/' )
SQL > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 CJCPDB01 MOUNTED
4 CJCPDB02 MOUNTED
5 CJCPDB03 MOUNTED
6 CJCPDB04 MOUNTED
7 CJCPDB05 MOUNTED
SQL > alter session set container = cjcpdb05 ;
Session altered.
SQL > startup
SQL > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 CJCPDB01 MOUNTED
4 CJCPDB02 MOUNTED
5 CJCPDB03 MOUNTED
6 CJCPDB04 MOUNTED
7 CJCPDB05 READ WRITE NO
SQL > select file_name from dba_data_files ;
FILE_NAME
--------------------------------------------------------------------------------
/ u01 / app / oracle12 / oradata / cjcpdb05 / system01.dbf
/ u01 / app / oracle12 / oradata / cjcpdb05 / sysaux01.dbf
/ u01 / app / oracle12 / oradata / cjcpdb05 / undotbs01.dbf
/ u01 / app / oracle12 / oradata / cjcpdb05 / users01.dbf
SQL > conn chen / oracle @ cjcpdb05
SQL > select count (*) from employees_01 ;
COUNT (*)
----------
107
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
