24 September 2015

step by step to migrate Non Container Database (Non CDB) to Container Database (CDB, as PDB)

Moving a Non Container Database (Non CDB) to Container Database (CDB, as PDB)

In this post, I discuss moving a regular Non CDB Database to CDB architecture. DBMS_PDB.DESCRIBE metadata procedure is used for the same purpose. Following analogy applies to the post,

ORCL - Non CDB Database (Instance)

Step -1 Identify CDB or non CDB database

[oracle@r12c orcl]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 10 18:49:34 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT name, open_mode FROM   v$pdbs ORDER BY name;

no rows selected

SQL> select con_id, dbid, name from v$pdbs;

no rows selected

SQL> select cdb from v$database;

CDB
---
NO

Step -2 Creat CDB database

Below steps to create CDB Database (Instance)
$ ./runinstaller

 

 



Step -3

Open the Non CDB (ORCL) in READ-ONLY Mode to keep it transactionally consistent.

SQL> STARTUP OPEN READ ONLY;
ORACLE instance started.

Total System Global Area 1275068416 bytes
Fixed Size                  2923968 bytes
Variable Size             872415808 bytes
Database Buffers          385875968 bytes
Redo Buffers               13852672 bytes
Database mounted.
Database opened.

Step -4
Connect to the Non-CDB Database(ORCL) and execute DBMS_PDB.DESCRIBE procedure to generate an xml file that will help to build the Pluggable Database on the CDB Database(CDB)

SQL> BEGIN
  DBMS_PDB.DESCRIBE(
    pdb_descr_file => '/tmp/cdb.xml');
END;
/  2    3    4    5

PL/SQL procedure successfully completed.

Step -5
Shutdown the Non CDB (ORCL) Database, and login to CDB Database (CDB) as SYS user

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Step -6
On CDB container database  Create the PDB Database by mapping the XML file that Is generated in step -4

[oracle@r12c datafile]$ export ORACLE_SID=cdb1
[oracle@r12c datafile]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 10 20:14:23 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select cdb from v$database;

CDB
---
YES

SQL> select name from v$database;

NAME
---------
CDB1

SQL> SELECT pdb_name, status FROM   dba_pdbs ORDER BY pdb_name;

PDB_NAME        STATUS
-----------       ----------
PDB             NORMAL
PDB$SEED        NORMAL

SQL> CREATE PLUGGABLE DATABASE orcl USING '/tmp/cdb.xml'
  COPY
  FILE_NAME_CONVERT = ('/db/app/product/oradata/orcl/','/db/app/product/oradata/orcl/pdb1/');  2    3

Pluggable database created.

SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB                            MOUNTED
ORCL                           MOUNTED

Step -7
Connect to the new PDB that has been created on CDB Database (CONDB) as SYS, and execute the "noncdb_to_pdb.sql" script.

SQL> ALTER SESSION SET CONTAINER=ORCL;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
SQL> SET SERVEROUTPUT ON
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> WHENEVER SQLERROR EXIT;
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01403: no data found"
DOC>   error if we're not in a PDB.
DOC>   This script is intended to be run right after plugin of a PDB,
DOC>   while inside the PDB.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL> VARIABLE cdbname VARCHAR2(128)
SQL> VARIABLE pdbname VARCHAR2(128)
SQL> BEGIN
  2    SELECT sys_context('USERENV', 'CDB_NAME')
  3      INTO :cdbname
  4      FROM dual
  5      WHERE sys_context('USERENV', 'CDB_NAME') is not null;
  6    SELECT sys_context('USERENV', 'CON_NAME')
  7      INTO :pdbname
  8      FROM dual
  9      WHERE sys_context('USERENV', 'CON_NAME') <> 'CDB$ROOT';
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> alter session set container = "&pdbname";

Session altered.

SQL>
SQL> -- leave the PDB in the same state it was when we started
SQL> BEGIN
  2    execute immediate '&open_sql &restricted_state';
  3  EXCEPTION
  4    WHEN OTHERS THEN
  5    BEGIN
  6      IF (sqlcode <> -900) THEN
  7        RAISE;
  8      END IF;
  9    END;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> WHENEVER SQLERROR CONTINUE;
SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@r12c datafile]$

Step -8
Open the PDB in READ WRITE Mode, confirm the status, and make it available for Client Operations,  it is also advisable to take a backup.

[oracle@r12c datafile]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 10 20:46:02 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB                            MOUNTED
ORCL                           MOUNTED

SQL> ALTER PLUGGABLE DATABASE ORCL open;

Pluggable database altered.

SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB                            MOUNTED
ORCL                           READ WRITE

No comments: