Non-CDB Oracle Database To a PDB on a CDB（19C）

2023-01-06 21:34:13

一、前提：

1.源库和目标库运行的主机版本一致。

Red Hat Enterprise Linux Server release 7.8 (Maipo)

2.数据库版本保持一致。（19C）

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

3.数据库文件目录需要保持一致。

Notes:如果数据文件必须被拷贝至不同目录，例如ASM盘；只需要在create pluggable database时设置参数 SOURCE_FILE_NAME_CONVERT

官方文档对source_file_name_convert的说明：

Specify this clause only if the contents of the XML file do not accurately describe the locations of the source files. 
If the files that must be used to plug in the source database are no longer in the location specified in the XML file, 
then use this clause to map the specified file names to the actual file names.

二、迁移步骤：

1、Non-CDB open to read_only model in the source database

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  629144664 bytes
Fixed Size                  8899672 bytes
Variable Size             184549376 bytes
Database Buffers          432013312 bytes
Redo Buffers                3682304 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

2、Non-CDB Create the XML file for the PDB in the source database

SQL> exec DBMS_PDB.DESCRIBE('/home/oracle/orclpdb.xml');

PL/SQL procedure successfully completed.


-rw-r--r-- 1 oracle oinstall 6.6K Jan 17 16:22 orclpdb.xml

oracle@19c:/home/oracle$ pwd
/home/oracle

--orclpdb.xml内容
oracle@19c:/home/oracle$ cat orclpdb.xml 

<?xml version="1.0" encoding="UTF-8"?>
<PDB>
  <xmlversion>1</xmlversion>
  <pdbname>orcl</pdbname>
  <cid>0</cid>
  <byteorder>1</byteorder>
  <vsn>318767104</vsn>
  <vsns>
    <vsnnum>19.0.0.0.0</vsnnum>
    <cdbcompt>19.0.0.0.0</cdbcompt>
    <pdbcompt>19.0.0.0.0</pdbcompt>
    <vsnlibnum>0.0.0.0.24</vsnlibnum>
    <vsnsql>24</vsnsql>
    <vsnbsv>8.0.0.0.0</vsnbsv>
  </vsns>
  <dbid>1589865411</dbid>
  <ncdb2pdb>1</ncdb2pdb>
  <cdbid>1589865411</cdbid>
  <guid>B91497A16ECE34FEE053C838A8C0F823</guid>
  <uscnbas>2039590</uscnbas>
  <uscnwrp>0</uscnwrp>
  <undoscn>9</undoscn>
  <rdba>4194824</rdba>
  <tablespace>
    <name>SYSTEM</name>
    <type>0</type>
    <tsn>0</tsn>
    <status>1</status>
    <issft>0</issft>
    <isnft>0</isnft>
    <encts>0</encts>
    <flags>0</flags>
    <bmunitsize>8</bmunitsize>
    <file>
      <path>/u01/app/oracle/oradata/ORCL/system01.dbf</path>
      <afn>1</afn>
      <rfn>1</rfn>
      <createscnbas>9</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>115200</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>318767104</vsn>
      <fdbid>1589865411</fdbid>
      <fcpsb>2039589</fcpsb>
      <fcpsw>0</fcpsw>
      <frlsb>1920977</frlsb>
      <frlsw>0</frlsw>
      <frlt>1062084677</frlt>
      <autoext>1</autoext>
      <maxsize>4194302</maxsize>
      <incsize>1280</incsize>
      <plugscn>0</plugscn>
      <plugafn>0</plugafn>
      <plugdbid>0</plugdbid>
    </file>
  </tablespace>
  <tablespace>
    <name>SYSAUX</name>
    <type>0</type>
    <tsn>1</tsn>
    <status>1</status>
    <issft>0</issft>
    <isnft>0</isnft>
    <encts>0</encts>
    <flags>0</flags>
    <bmunitsize>8</bmunitsize>
    <file>
      <path>/u01/app/oracle/oradata/ORCL/sysaux01.dbf</path>
      <afn>3</afn>
      <rfn>3</rfn>
      <createscnbas>5480</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>66560</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>318767104</vsn>
      <fdbid>1589865411</fdbid>
      <fcpsb>2039589</fcpsb>
      <fcpsw>0</fcpsw>
      <frlsb>1920977</frlsb>
      <frlsw>0</frlsw>
      <frlt>1062084677</frlt>
      <autoext>1</autoext>
      <maxsize>4194302</maxsize>
      <incsize>1280</incsize>
      <plugscn>0</plugscn>
      <plugafn>0</plugafn>
      <plugdbid>0</plugdbid>
    </file>
  </tablespace>
  <tablespace>
    <name>UNDOTBS1</name>
    <type>2</type>
    <tsn>2</tsn>
    <status>1</status>
    <issft>0</issft>
    <isnft>0</isnft>
    <encts>0</encts>
    <flags>0</flags>
    <bmunitsize>8</bmunitsize>
    <file>
      <path>/u01/app/oracle/oradata/ORCL/undotbs01.dbf</path>
      <afn>4</afn>
      <rfn>4</rfn>
      <createscnbas>1920446</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>42880</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>318767104</vsn>
      <fdbid>1589865411</fdbid>
      <fcpsb>2039589</fcpsb>
      <fcpsw>0</fcpsw>
      <frlsb>1920977</frlsb>
      <frlsw>0</frlsw>
      <frlt>1062084677</frlt>
      <autoext>1</autoext>
      <maxsize>4194302</maxsize>
      <incsize>640</incsize>
      <plugscn>0</plugscn>
      <plugafn>0</plugafn>
      <plugdbid>0</plugdbid>
    </file>
  </tablespace>
  <tablespace>
    <name>TEMP</name>
    <type>1</type>
    <tsn>3</tsn>
    <status>1</status>
    <issft>0</issft>
    <isnft>0</isnft>
    <encts>0</encts>
    <flags>0</flags>
    <bmunitsize>128</bmunitsize>
    <file>
      <path>/u01/app/oracle/oradata/ORCL/temp01.dbf</path>
      <afn>1</afn>
      <rfn>1</rfn>
      <createscnbas>1921094</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>4096</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>318767104</vsn>
      <autoext>1</autoext>
      <maxsize>4194302</maxsize>
      <incsize>80</incsize>
      <plugscn>0</plugscn>
      <plugafn>0</plugafn>
      <plugdbid>0</plugdbid>
    </file>
  </tablespace>
  <tablespace>
    <name>USERS</name>
    <type>0</type>
    <tsn>4</tsn>
    <status>1</status>
    <issft>0</issft>
    <isnft>0</isnft>
    <encts>0</encts>
    <flags>0</flags>
    <bmunitsize>8</bmunitsize>
    <file>
      <path>/u01/app/oracle/oradata/ORCL/users01.dbf</path>
      <afn>7</afn>
      <rfn>7</rfn>
      <createscnbas>32876</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>640</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>318767104</vsn>
      <fdbid>1589865411</fdbid>
      <fcpsb>2039589</fcpsb>
      <fcpsw>0</fcpsw>
      <frlsb>1920977</frlsb>
      <frlsw>0</frlsw>
      <frlt>1062084677</frlt>
      <autoext>1</autoext>
      <maxsize>4194302</maxsize>
      <incsize>160</incsize>
      <plugscn>0</plugscn>
      <plugafn>0</plugafn>
      <plugdbid>0</plugdbid>
    </file>
  </tablespace>
  <recover>0</recover>
  <optional>
    <ncdb2pdb>1</ncdb2pdb>
    <csid>873</csid>
    <ncsid>2000</ncsid>
    <options>
      <option>APS=19.0.0.0.0</option>
      <option>CATALOG=19.0.0.0.0</option>
      <option>CATJAVA=19.0.0.0.0</option>
      <option>CATPROC=19.0.0.0.0</option>
      <option>CONTEXT=19.0.0.0.0</option>
      <option>DV=19.0.0.0.0</option>
      <option>JAVAVM=19.0.0.0.0</option>
      <option>OLS=19.0.0.0.0</option>
      <option>ORDIM=19.0.0.0.0</option>
      <option>OWM=19.0.0.0.0</option>
      <option>SDO=19.0.0.0.0</option>
      <option>XDB=19.0.0.0.0</option>
      <option>XML=19.0.0.0.0</option>
      <option>XOQ=19.0.0.0.0</option>
    </options>
    <olsoid>0</olsoid>
    <dv>0</dv>
    <APEX>NULL</APEX>
    <parameters>
      <parameter>processes=300</parameter>
      <parameter>nls_language='AMERICAN'</parameter>
      <parameter>nls_territory='AMERICA'</parameter>
      <parameter>sga_target=629145600</parameter>
      <parameter>db_block_size=8192</parameter>
      <parameter>compatible='19.0.0'</parameter>
      <parameter>open_cursors=300</parameter>
      <parameter>pga_aggregate_target=209715200</parameter>
    </parameters>
    <sqlpatches>
      <sqlpatch>19.3.0.0.0 Release_Update 1904101227 (RU): APPLY SUCCESS</sqlpatch>
    </sqlpatches>
    <tzvers>
      <tzver>primary version:32</tzver>
      <tzver>secondary version:0</tzver>
    </tzvers>
    <walletkey>0</walletkey>
    <services>
      <service>SYS$BACKGROUND,</service>
      <service>SYS$USERS,</service>
      <service>orclXDB,orclXDB</service>
      <service>orcl,orcl</service>
    </services>
    <opatches/>
    <hasclob>1</hasclob>
    <awr/>
    <hardvsnchk>0</hardvsnchk>
    <localundo>1</localundo>
    <apps/>
    <dbedition>8</dbedition>
    <dvopsctl>2</dvopsctl>
    <clnupsrcpal>1</clnupsrcpal>
  </optional>
</PDB>

3、Shutdown the Non-CDB database in the source database

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
orcl             OPEN

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

4、Change directory to the new Oracle home，Run the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function in the target database

--If the output is YES, then the PDB is compatible

set serveroutput on
DECLARE
  compatible CONSTANT VARCHAR2(3) :=
    CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
    pdb_descr_file => '/home/oracle/orclpdb.xml',
    pdb_name       => 'ORCLPDB')
  WHEN TRUE THEN 'YES'
    ELSE 'NO'
  END;
  BEGIN
    DBMS_OUTPUT.PUT_LINE(compatible);
  END;
 12  /

YES

PL/SQL procedure successfully completed.

5、Plug Non-CDB database to PDB in the target database

SQL> create pluggable database orclpdb using '/home/oracle/orclpdb.xml' nocopy tempfile reuse;


Pluggable database created.


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB01                          MOUNTED
         4 ORCLPDB                        MOUNTED
         5 PDB02                          MOUNTED
         6 PDB03                          MOUNTED

6、Run the noncdb_to_pdb.sql script in the target database

SQL> alter session set container=ORCLPDB;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 ORCLPDB                        MOUNTED


SQL> @?/rdbms/admin/noncdb_to_pdb.sql
--由于内容过多，已省略

7、open orclpdb in the target database

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 PDB01                          MOUNTED
         4 ORCLPDB                        MOUNTED
         5 PDB02                          MOUNTED
         6 PDB03                          MOUNTED

SQL> alter pluggable database ORCLPDB open;

Pluggable database altered.

SQL> alter session set container=ORCLPDB;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 ORCLPDB                        READ WRITE NO

码农公寓

一、前提：

二、迁移步骤：

相关文章