Migrate a Non-Container Database (CDB) to a Pluggable Database (PDB) in Oracle Database 12c Release 2 (12.2)

Migration:

DBMS_PDB package permits to generate an XML metadata file from a non-CDB 12c database, Its like a unplug a Pluggable database.

Step 1 : Check the database its properly up and running.shutdown the non-CDB and start it in read-only mode.

[oracle@ram ~]$ export ORACLE_SID=hari
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 22:54:03 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name,open_mode from v$database;

NAME         OPEN_MODE
--------- --------------------
HARI         READ WRITE

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only;
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size 8620224 bytes
Variable Size 452986688 bytes
Database Buffers 788529152 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.

In the non-DBC database using the DBMS_PDB_DESCRIBE procedure  creates an XML file in the same way that the unplug operation does for a PDB.

Step 2 : Shutdown the non-CDB database.

SQL> begin dbms_pdb.describe(pdb_descr_file => '/u01/app/oracle/oradata/HARI/tmp/hari.xml');
2 end;
3 /

PL/SQL procedure successfully completed.

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

Step 3 : Connect to an existing CDB

Step 4 : Create a new PDB using that xml file which we described already in  the non-CDB database. Remember to configure the FILE_NAME_CONVERT parameter to convert the existing files to the new location.

[oracle@ram ~]$ export ORACLE_SID=Petonas
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 23:46:58 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create pluggable database pdb5new using '/u01/app/oracle/oradata/HARI/tmp/hari.xml'
2 copy file_name_convert = ('/u01/app/oracle/oradata/HARI/datafile/','/u01/app/oracle/oradata/PETONAS/datafile/pdb5new/');


Pluggable database created.

Step 5 : Switch to the PDB container and run the “$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql” script to clean up the new PDB, removing any items that should not be present in a PDB.

SQL> alter session set container=pdb5new;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql;

step 6 : Startup the PDB and check the open mode

SQL> alter session set container=pdb5new;

Session altered.

SQL> alter pluggable database open;

Pluggable database altered.

SQL> select name ,open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB5NEW
READ WRITE


SQL> col name for a15
SQL> /

NAME             OPEN_MODE
--------------- ----------
PDB5NEW          READ WRITE


The non-cdb database has been converted to CDB now successfully.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Hot Clone a Remote Non-CDB in Oracle Database 12c Release 2 (12.2)

Cloning a remote non-cdb:

Step 1  : Connect to the remote database. Make sure it up and running.

[oracle@ram admin]$ export ORACLE_SID=orcl
[oracle@ram admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 13 03:06:02 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 629149632 bytes
Database Buffers 197132288 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.

Step 2 : Create a user in the remote database for use with the database link.

SQL> CREATE USER r_user IDENTIFIED BY r_user;

User created.

SQL> GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO r_user;

Grant succeeded.

Step 3 : Check the remote non-CDB is archivelog mode. If not make it archivelog mode.

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> alter database close;
Database altered.

SQL> alter database archivelog;
Database altered.

SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 629149632 bytes
Database Buffers 197132288 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.

SQL>

Step 4 : In Oracle 12.1  the remote database to read-only mode before continuing, but this is not necessary in Oracle 12.2 provided the source database is in archivelog mode.

Step 5 : Switch to the local server and create a “tnsnames.ora” entry pointing to the remote database for use in the using clause of the database link.

[oracle@ram admin]$ export ORACLE_SID=dbwr
[oracle@ram admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 13 03:23:13 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> 
SQL> select db_link from dba_db_links;

DB_LINK
--------------
SYS_HUB

SQL> col name for a15
SQL> select name,open_mode from v$pdbs;

NAME             OPEN_MODE
--------------- ----------
PDB$SEED         READ ONLY
PDB5             READ WRITE
PDB6             READ WRITE
PDB1             READ WRITE

Step 6 : Connect to the local database to initiate the clone.

Step 7 : Create a database link in the local database, pointing to the remote database.

SQL> CREATE DATABASE LINK clone_link CONNECT TO r_user IDENTIFIED BY r_user USING 'orcl';

Database link created.

Step 8 : Create a new PDB in the local database by cloning the remote non-CDB. We are using Oracle Managed Files (OMF), so we don’t need to bother with FILE_NAME_CONVERT parameter for file name conversions. Since there is no PDB to name, we use “DB12C” as the PDB name.

SQL> create pluggable database db12c from dbwr@clone_link;

Pluggable database created

Step 9 : The new PDB has been created, but it is in the MOUNTED state.

column name for a15;

SQL> select name,open_mode from v$pdbs;

NAME                OPEN_MODE
-------------      -----------
DB12C               Mounted

SQL>

Step 10 : This PDB was created as a clone of a non-CDB, before it can be opened we need to run the “$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql” script to clean it up

ALTER SESSION SET CONTAINER=db12c;

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Step 11 : The PDB can now be opened in read-write mode.

ALTER PLUGGABLE DATABASE db12c OPEN;

SELECT name, open_mode FROM v$pdbs WHERE name = 'DB12C';

NAME                  OPEN_MODE
-------------------- ----------
DB12C                 READ WRITE

SQL>

Thus the cloning PDB has been created from non-cdb.

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba