Oracle 建立PDB-Plugging In an Unplugged PDB

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"，共同學習，共同成長！！！