Convert 12.2 Non-CDB (NONCDB12C) as PDB (NONCDB12C) into CDB (CDB1)
Contents
___________________________________________________________________________________________________
1. Overview
2. Environment
3. Shutdown NON-CDB (NONCDB12C)
4. Open Non-CDB in read only (this is for consistency)
5. Run DBMS_PDB.DESCRIBE to create an XML file
6. Shutdown the Non-CDB
7. Connect to Target
8. Check whether Non-CDB (NONCDB12C) can be plugged into CDB(CDB1)
9. Create target directory for datafiles
10. Plug-in Non-CDB (NONCDB12C) as PDB (NONCDB12C) into CDB1
11. Verify newly created PDB NONCDB12C on CDB1
12. Run noncdb_to_pdb.sql script on new PDB (NONCDB12C)
13. Verify table PDB_PLUG_IN_VIOLATIONS
___________________________________________________________________________________________________
There are 4 methods to move Non-CDB into a Pluggable Database (PDB) in a Container Database - Create PDB by cloning a Non-CDB - Use DBMS_PDB package - Use Oracle Data Pump export/import - Use GoldenGate Replication Note: If status is UNUSABLE state it must be dropped before a PDB with the same name as the unusable PDB can be created. YOu can view the status from CBD_PDBS OR DBA_PDBS views
Platform : Linux x86_64 Server Name : RAC1.RAJASEKHAR.COM, IP: 192.168.2.101 DB Version : Oracle 12.2.0.1, File system: Normal Source NON-CDB : NONCDB12C Target CDB : CDB1 Target PDB : NONCDB12C Oracle Home Path : /u01/app/oracle/product/12.2.0.1
3. Shutdown NON-CDB (NONCDB12C)
SQL> select name, open_mode, cdb from v$database; NAME OPEN_MODE CDB --------- -------------------- --- NONCDB12 READ WRITE NO <----- SQL> create user raj identified by raj default tablespace users temporary tablespace temp; User created. SQL> grant connect,resource to raj; Grant succeeded. SQL> alter user raj quota unlimited on users; User altered. SQL> conn raj/raj; Connected. SQL> show user USER is "RAJ" SQL> SQL> create table sugi(Name varchar2(10), Role varchar2(10)); Table created. SQL> insert into sugi values ('&a','&b'); Enter value for a: RAJ Enter value for b: DBA old 1: insert into sugi values ('&a','&b') new 1: insert into sugi values ('RAJ','DBA') 1 row created. SQL> / Enter value for a: SUGI Enter value for b: DBA old 1: insert into sugi values ('&a','&b') new 1: insert into sugi values ('SUGI','DBA') 1 row created. SQL> / Enter value for a: TEJU Enter value for b: DBA old 1: insert into sugi values ('&a','&b') new 1: insert into sugi values ('TEJU','DBA') 1 row created. SQL> / Enter value for a: VIJAY Enter value for b: DBA old 1: insert into sugi values ('&a','&b') new 1: insert into sugi values ('VIJAY','DBA') 1 row created. SQL> COMMIT; Commit complete. SQL> select * from sugi; <-- please check output once task completed. NAME ROLE ---------- ---------- RAJ DBA SUGI DBA TEJU DBA VIJAY DBA SQL> SQL> CONN / AS SYSDBA Connected. SQL> ALTER SYSTEM FLUSH BUFFER_CACHE; System altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u02/oradata/NONCDB12C/system01.dbf /u02/oradata/NONCDB12C/sysaux01.dbf /u02/oradata/NONCDB12C/undotbs01.dbf /u02/oradata/NONCDB12C/users01.dbf SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL>
4. Open Non-CDB in read only (this is for consistency)
SQL> STARTUP MOUNT EXCLUSIVE; ORACLE instance started. Total System Global Area 1660944384 bytes Fixed Size 8621376 bytes Variable Size 1056965312 bytes Database Buffers 587202560 bytes Redo Buffers 8155136 bytes Database mounted. SQL> SQL> alter database open read only; Database altered. SQL> select name, open_mode, cdb from v$database; NAME OPEN_MODE CDB --------- -------------------- --- NONCDB12 READ ONLY NO SQL>
5. Run DBMS_PDB.DESCRIBE to create an XML file
The DBMS_PDB. It describe procedure creates the XML file that contains all necessary information regarding the nonCDB Database SQL> BEGIN DBMS_PDB.DESCRIBE(pdb_descr_file => '/u02/oradata/NONCDB12C/noncdb12c.xml'); END; / PL/SQL procedure successfully completed. SQL> [oracle@rac1 ~]$ cat /u02/oradata/NONCDB12C/noncdb12c.xml Please click noncdb12c.xml <------ [oracle@rac1 ~]$
SQL> select name, open_mode, cdb from v$database; NAME OPEN_MODE CDB --------- -------------------- --- NONCDB12 READ ONLY NO SQL> SQL> SHUTDOWN IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@rac1 ~]$
On Target
===========
Connect to the target Container Database (CDB1) SQL> select name, open_mode, cdb from v$database; NAME OPEN_MODE CDB --------- -------------------- --- CDB1 READ WRITE YES <----- SQL>
8. Check whether non-cdb (NONCDB12C) can be plugged into CDB(CDB1)
SQL> SET SERVEROUTPUT ON DECLARE compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/u02/oradata/NONCDB12C/noncdb12c.xml', pdb_name => 'NONCDB12') WHEN TRUE THEN 'YES' ELSE 'NO' END; BEGIN DBMS_OUTPUT.PUT_LINE(compatible); END; / YES PL/SQL procedure successfully completed. SQL> -- Incase output is NO, then check PDB_PLUG_IN_VIOLATIONS view SQL> col cause for a10 col name for a10 col message for a35 word_wrapped select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NONCDB12C'; NAME CAUSE TYPE MESSAGE STATUS ---------- ---------- --------- ----------------------------------- --------- NONCDB12C Non-CDB to WARNING PDB plugged in is a non-CDB, PENDING PDB requires noncdb_to_pdb.sql be run. NONCDB12C Parameter WARNING CDB parameter memory_target PENDING mismatch: Previous 1584M Current 0 SQL>
9. Create target directory for datafiles
SQL> !mkdir -p /u01/app/oracle/oradata/CDB1/NONCDB12C
SQL>
10. Plug-in Non-CDB (NONCDB12C) as PDB (NONCDB12C) into CDB1
The NOCOPY option keeps the original location of the datafiles as the database is Plugged-In The SOURCE_FILE_NAME_CONVERT option allows us to specify a new location of the datafiles I am plugging the database in to a CDB on the same server with COPY clause and hence using FILE_NAME_CONVERT. SQL> CREATE PLUGGABLE DATABASE NONCDB12C USING '/u02/oradata/NONCDB12C/noncdb12c.xml' COPY file_name_convert=('/u02/oradata/NONCDB12C','/u01/app/oracle/oradata/CDB1/NONCDB12C'); Pluggable database created. SQL>
11. Verify newly created PDB NONCDB12C on CDB1
SQL> select name, open_mode, cdb from v$database; NAME OPEN_MODE CDB --------- -------------------- --- CDB1 READ WRITE YES SQL> SQL> col pdb_name for a30 SQL> SELECT pdb_name , status from cdb_pdbs; PDB_NAME STATUS ------------------------------ ---------- PDB1 NORMAL PDB$SEED NORMAL NONCDB12C NEW <-------- SQL> --- OR --- SQL> select pdb_name, status from dba_pdbs; PDB_NAME STATUS ------------------------------ ---------- PDB1 NORMAL PDB$SEED NORMAL NONCDB12C NEW SQL> SQL> col name for a30 SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------------------------------------- PDB$SEED READ ONLY PDB1 READ WRITE NONCDB12C MOUNTED <----- SQL> --check that datafiles copied to new location SQL> !ls -ltr /u01/app/oracle/oradata/CDB1/NONCDB12C* total 1397856 -rw-r-----. 1 oracle dba 33562624 Jul 22 21:39 temp01.dbf -rw-r-----. 1 oracle dba 5251072 Jul 22 21:39 users01.dbf -rw-r-----. 1 oracle dba 73408512 Jul 22 21:39 undotbs01.dbf -rw-r-----. 1 oracle dba 503324672 Jul 22 21:39 sysaux01.dbf -rw-r-----. 1 oracle dba 849354752 Jul 22 21:39 system01.dbf SQL>
12. Run noncdb_to_pdb.sql script on new PDB (NONCDB12C)
You have to convert the plugged non-CDB to a proper PDB by deleting unnecessary metadata from PDB SYSTEM tablespace. For this purpose, you execute the $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql script when connected to the PDB. SQL> select name, open_mode, cdb from v$database; NAME OPEN_MODE CDB --------- -------------------- --- CDB1 READ WRITE YES SQL> ALTER SESSION SET CONTAINER=NONCDB12C; Session altered. SQL> show con_name CON_NAME ------------------------------ NONCDB12C SQL> SQL> show con_id CON_ID ------------------------------ 4 SQL> 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> SET VERIFY OFF SQL> SQL> -- save settings SQL> STORE SET ncdb2pdb.settings.sql REPLACE Wrote file ncdb2pdb.settings.sql SQL> SQL> SET TIME ON 21:58:13 SQL> SET TIMING ON 21:58:13 SQL> 21:58:13 SQL> WHENEVER SQLERROR EXIT; 21:58:13 SQL> ... ... ... 22:22:53 SQL> -- restore old settings 22:22:53 SQL> START ncdb2pdb.settings.sql 22:22:53 SQL> set appinfo OFF 22:22:54 SQL> set appinfo "SQL*Plus" 22:22:54 SQL> set arraysize 15 22:22:54 SQL> set autocommit OFF 22:22:54 SQL> set autoprint OFF 22:22:54 SQL> set autorecovery OFF 22:22:54 SQL> set autotrace OFF 22:22:54 SQL> set blockterminator "." 22:22:54 SQL> set cmdsep OFF 22:22:54 SQL> set colinvisible OFF 22:22:54 SQL> set colsep " " 22:22:54 SQL> set compatibility NATIVE 22:22:54 SQL> set concat "." 22:22:54 SQL> set copycommit 0 22:22:54 SQL> set copytypecheck ON 22:22:54 SQL> set define "&" 22:22:54 SQL> set describe DEPTH 1 LINENUM OFF INDENT ON 22:22:54 SQL> set echo ON 22:22:54 SQL> set editfile "afiedt.buf" 22:22:54 SQL> set embedded OFF 22:22:54 SQL> set escape OFF 22:22:54 SQL> set escchar OFF 22:22:54 SQL> set exitcommit ON 22:22:54 SQL> set feedback ON 22:22:54 SQL> set flagger OFF 22:22:54 SQL> set flush ON 22:22:54 SQL> set fullcolname OFF 22:22:54 SQL> set heading ON 22:22:54 SQL> set headsep "|" 22:22:54 SQL> set linesize 80 22:22:54 SQL> set lobprefetch 0 22:22:54 SQL> set logsource "" 22:22:54 SQL> set long 80 22:22:54 SQL> set longchunksize 80 22:22:54 SQL> set markup HTML OFF HEAD " ....................................... ..... Intentionally left black here.. trimmed output ....................................... SQL> set termout ON 22:22:54 SQL> set time OFF SQL> set timing OFF SQL> set trimout ON SQL> set trimspool ON SQL> set underline "-" SQL> set verify OFF SQL> set wrap ON SQL> set xmloptimizationcheck OFF SQL> SQL> Click here for complete output noncdb_to_pdb_output <------
13. Verify table PDB_PLUG_IN_VIOLATIONS
Check out the table PDB_PLUG_IN_VIOLATIONS in order to see if there are error related to the plug in process, but read first the note "OPTION WARNING Database option mismatch: PDB installed version NULL" in PDB_PLUG_IN_VIOLATIONS (Doc ID 2020172.1)" [oracle@rac1 ~]$ . oraenv ORACLE_SID = [oracle] ? CDB1 The Oracle base has been set to /u01/app/oracle [oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sun Jul 22 22:41:00 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> col cause for a10 col name for a10 col message for a35 word_wrapped select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NONCDB12C';SQL> SQL> SQL> NAME CAUSE TYPE MESSAGE STATUS ---------- ---------- --------- ----------------------------------- --------- NONCDB12C Parameter WARNING CDB parameter memory_target RESOLVED mismatch: Previous 1584M Current 0 NONCDB12C Non-CDB to ERROR PDB plugged in is a non-CDB, PENDING PDB requires noncdb_to_pdb.sql be run. SQL> SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 NONCDB12C MOUNTED SQL> SQL> alter session set container=NONCDB12C; Session altered. SQL> show con_name CON_NAME ------------------------------ NONCDB12C SQL> SQL> alter database open; Database altered. SQL> SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 4 NONCDB12C READ WRITE NO SQL> SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sun Jul 22 22:58:51 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> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> SQL> SQL> select name, open_mode, cdb from v$database; NAME OPEN_MODE CDB --------- -------------------- --- CDB1 READ WRITE YES SQL> col pdb_name for a30 SQL> SELECT pdb_name , status from cdb_pdbs; PDB_NAME STATUS ------------------------------ ---------- PDB1 NORMAL PDB$SEED NORMAL NONCDB12C NORMAL <---- SQL> SQL> col cause for a10 col name for a10 col message for a35 word_wrapped select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NONCDB12C';SQL> SQL> SQL> NAME CAUSE TYPE MESSAGE STATUS ---------- ---------- --------- ----------------------------------- --------- NONCDB12C Parameter WARNING CDB parameter memory_target RESOLVED mismatch: Previous 1584M Current 0 NONCDB12C Non-CDB to ERROR PDB plugged in is a non-CDB, RESOLVED PDB requires noncdb_to_pdb.sql be run. SQL> SQL> alter session set container=NONCDB12C; Session altered. SQL> show con_name CON_NAME ------------------------------ NONCDB12C SQL> SQL> select * from raj.sugi; NAME ROLE ---------- ---------- RAJ DBA SUGI DBA TEJU DBA VIJAY DBA SQL>
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala