How to migrate an existing pre-12c database (non-CDB) to 12c CDB database? ..
REFERENCES
NOTE:1935365.1 - Multitenant Unplug/Plug Best Practices
NOTE:1564657.1 - How to migrate an existing pre-12c database (non-CDB) to 12c CDB database?
Aim to convert tnoncdb database to tcdb (CDB)
1.Prepare the non-CDB environment for conversion.
SQL> def
DEFINE _DATE = "12-DEC-20" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "tnoncdb1" (CHAR) <<<< on
DEFINE _USER = "SYS" (CHAR)
SQL>
BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/tnoncdb.xml');
END;
/
SQL> SQL> 2 3 4
PL/SQL procedure successfully completed.
Check all datafile
______________________________________________________________________________________________________________________
12 Dec 2020 08:15:08
File Report (all physical files)
Data File Report (all physical files) irac01-tnoncdb
______________________________________________________________________________________________________________________
Tablespace Name / File Class Filename File Size MB Auto Next Max MB
------------------------------ --------------------------------------------------------------------------- ------------------ ---- ---------------- ----------------
SYSAUX +DATA/TNONCDB/DATAFILE/sysaux.684.1058938847 670 YES 10 32,768
SYSTEM +DATA/TNONCDB/DATAFILE/system.797.1058938803 820 YES 10 32,768
TEMP +DATA/TNONCDB/TEMPFILE/temp.633.1058939071 43 YES 1 32,768
UNDOTBS1 +DATA/TNONCDB/DATAFILE/undotbs1.486.1058938873 285 YES 5 32,768
UNDOTBS2 +DATA/TNONCDB/DATAFILE/undotbs2.359.1058939103 50 YES 25 32,768
USERS +DATA/TNONCDB/DATAFILE/users.386.1058938875 5 YES 1 32,768
[ BLOCK TRACKING]DISABLED
[ CONTROL FILE ] +DATA/TNONCDB/CONTROLFILE/current.784.1058939063 10
[ CONTROL FILE ] +DATA/TNONCDB/CONTROLFILE/current.889.1058939063 10
[ ONLINE REDO LOG ] +DATA/TNONCDB/ONLINELOG/group_1.630.1058939065 200 200
[ ONLINE REDO LOG ] +DATA/TNONCDB/ONLINELOG/group_1.672.1058939065 200 200
[ ONLINE REDO LOG ] +DATA/TNONCDB/ONLINELOG/group_2.362.1058939065 200 200
[ ONLINE REDO LOG ] +DATA/TNONCDB/ONLINELOG/group_2.366.1058939065 200 200
[ ONLINE REDO LOG ] +DATA/TNONCDB/ONLINELOG/group_3.586.1058939371 200 200
[ ONLINE REDO LOG ] +DATA/TNONCDB/ONLINELOG/group_3.636.1058939371 200 200
[ ONLINE REDO LOG ] +DATA/TNONCDB/ONLINELOG/group_4.646.1058939371 200 200
[ ONLINE REDO LOG ] +DATA/TNONCDB/ONLINELOG/group_4.653.1058939371 200 200
[ Spfile or Pfile]SPFILE +DATA/TNONCDB/PARAMETERFILE/spfile.626.1058939373
18 rows selected.
Creating user for testing .. We will check this user on pdb database ...
SQL> show user
USER is "ANUJ"
SQL> create table test as select sysdate vdate from dual ;
Table created.
SQL> select * from test ;
SQL> def
DEFINE _DATE = "12-DEC-20" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "tnoncdb1" (CHAR) <<<<< now shutdown this database .
[oracle@irac01 tnoncdb]$ srvctl stop database -d tnoncdb
[oracle@irac01 tnoncdb]$ srvctl status database -d tnoncdb
Instance tnoncdb1 is not running on node irac01
Instance tnoncdb2 is not running on node irac02
=======================================================
now on CDB database
oracle@irac01 ~]$ . oraenv
ORACLE_SID = [tcdb1] ?
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.2.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ibrac01 ~]$ sqlplus / as sysdba
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL>
SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/tnoncdb.xml')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10
11 /
YES
PL/SQL procedure successfully completed.
set pagesize 300
col cause for a20
col name for a20
col message for a75 word_wrapped
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS
2 /
NAME CAUSE TYPE MESSAGE STATUS
-------------------- -------------------- --------- --------------------------------------------------------------------------- ---------
TNONCDB Non-CDB to PDB WARNING PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. PENDING
TNONCDB Parameter WARNING CDB parameter sga_max_size mismatch: Previous 2G Current 1664M PENDING
TNONCDB Parameter WARNING CDB parameter sga_target mismatch: Previous 2G Current 1664M PENDING
TNONCDB Parameter WARNING CDB parameter pga_aggregate_target mismatch: Previous 7222M Current 554M PENDING
CREATE PLUGGABLE DATABASE tnoncdb USING '/tmp/tnoncdb1.xml';
CREATE PLUGGABLE DATABASE tnoncdb USING '/tmp/tnoncdb1.xml';SQL> SQL>
CREATE PLUGGABLE DATABASE tnoncdb USING '/tmp/tnoncdb1.xml'
*
ERROR at line 1:
ORA-65139: Mismatch between XML metadata file and data file +DATA/TNONCDB/DATAFILE/system.797.1058938803 for value of fileblocks (103680 in the plug XML file, 104960 in the data file)
check following metalink note ... for above error
ORA-65139: Mismatch Between XML Metadata File And Data File. Database Not Open Read Only (Doc ID 1963139.1)
The following resolves the issue:
-- Set the ORACLE_SID variable to noncdb.
-- Connect to the noncdb instance.
sqlplus / as sysdba
-- If the instance is up, shut it down first.
shutdown immediate
-- Start up the database in mount exclusive mode.
startup mount exclusive
-- Open the database in read-only mode.
alter database open read only;
exec dbms_pdb.describe(pdb_descr_file=>'/opt/oracle/oradata/noncdb1/noncdb1.xml');
BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/tnoncdb1.xml');
END;
/
SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/tnoncdb1.xml')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/tnoncdb1.xml')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;SQL> SQL> SQL> 2 3 4 5 6 7 8
9 /
YES
PL/SQL procedure successfully completed.
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS;
NAME CAUSE TYPE MESSAGE STATUS
-------------------- -------------------- --------- --------------------------------------------------------------------------- ---------
TNONCDB Non-CDB to PDB WARNING PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. PENDING
TNONCDB Parameter WARNING CDB parameter sga_max_size mismatch: Previous 2G Current 1664M PENDING
TNONCDB Parameter WARNING CDB parameter sga_target mismatch: Previous 2G Current 1664M PENDING
TNONCDB Parameter WARNING CDB parameter pga_aggregate_target mismatch: Previous 7222M Current 554M PENDING
SQL>
CREATE PLUGGABLE DATABASE tnoncdb USING '/tmp/tnoncdb1.xml';
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS;
NAME CAUSE TYPE MESSAGE STATUS
-------------------- -------------------- --------- --------------------------------------------------------------------------- ---------
TNONCDB Non-CDB to PDB WARNING PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. PENDING
TNONCDB Parameter WARNING CDB parameter sga_max_size mismatch: Previous 2G Current 1664M PENDING
TNONCDB Parameter WARNING CDB parameter sga_target mismatch: Previous 2G Current 1664M PENDING
TNONCDB Parameter WARNING CDB parameter pga_aggregate_target mismatch: Previous 7222M Current 554M PENDING
SQL>
CREATE PLUGGABLE DATABASE tnoncdb USING '/tmp/tnoncdb1.xml';
Pluggable database created.
use convert parameter on prod <<<<<<<
===================================================================
create pluggable database exnoncdb
as clone
using '/opt/oracle/oradata/noncdb/noncdb.xml'
file_name_convert=('/opt/oracle/oradata/noncdb','/stage/oradata/exnoncdb')
copy;
as clone
using '/opt/oracle/oradata/noncdb/noncdb.xml'
file_name_convert=('/opt/oracle/oradata/noncdb','/stage/oradata/exnoncdb')
copy;
==================================================
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 VIHAAN READ WRITE NO
4 TNONCDB MOUNTED
SQL> alter pluggable database TNONCDB open instances=all ;
Warning: PDB altered with errors.
alter pluggable database TNONCDB close instances=all ;
SQL> SELECT NAME,STATUS,MESSAGE FROM PDB_PLUG_IN_VIOLATIONS;
NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- ---------
MESSAGE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TNONCDB RESOLVED
CDB parameter sga_max_size mismatch: Previous 2G Current 1664M
TNONCDB RESOLVED
CDB parameter sga_target mismatch: Previous 2G Current 1664M
TNONCDB RESOLVED
CDB parameter pga_aggregate_target mismatch: Previous 7222M Current 554M
NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- ---------
MESSAGE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TNONCDB PENDING
PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 VIHAAN READ WRITE NO
4 TNONCDB READ WRITE YES
SQL> alter session set CONTAINER=TNONCDB;
Session altered.
Imp !!!!!!!!!!!!!!!!
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
Session altered.
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> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
SQL>
set pages 2000
column message format a50
column status format a9
column type format a9
column con_id format 9
column name format a8
select con_id, name, type, message, status from PDB_PLUG_IN_VIOLATIONS where status<>'RESOLVED'
order by name,time;
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 TNONCDB MOUNTED
SQL> alter pluggable database TNONCDB open instances=all ;
Pluggable database altered.
SQL> SELECT NAME,STATUS,MESSAGE FROM PDB_PLUG_IN_VIOLATIONS;
NAME STATUS MESSAGE
-------- --------- --------------------------------------------------
TNONCDB RESOLVED PDB plugged in is a non-CDB, requires noncdb_to_pd
b.sql be run.
1 row selected.
Imp!!!
select * from dba_objects where status <> 'VALID';
no rows selected
SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi';
Session altered.
SQL> select * from anuj.test;
VDATE
----------------
12-12-2020 08:17
1 row selected.
Check all file !!!!!!
Tablespace Name / File Class Filename File Size MB Auto Next Max MB
------------------------------ --------------------------------------------------------------------------- ------------------ ---- ---------------- ----------------
SYSAUX +DATA/TCDB/DATAFILE/sysaux.334.1058941695 660 YES 10 32,768
SYSTEM +DATA/TCDB/DATAFILE/system.645.1058941653 810 YES 10 32,768
TEMP +DATA/TCDB/TEMPFILE/temp.641.1058941913 40 YES 1 32,768
TNONCDB.SYSAUX +DATA/TCDB/B6431750F11C2822E05322F56A40F4C0/DATAFILE/sysaux.338.1058950367 680 YES 10 32,768
TNONCDB.SYSTEM +DATA/TCDB/B6431750F11C2822E05322F56A40F4C0/DATAFILE/system.647.1058950367 820 YES 10 32,768
TNONCDB.UNDOTBS1 +DATA/TCDB/B6431750F11C2822E05322F56A40F4C0/DATAFILE/undotbs1.330.105895036 890 YES 5 32,768
7
TNONCDB.UNDOTBS2 +DATA/TCDB/B6431750F11C2822E05322F56A40F4C0/DATAFILE/undotbs2.349.105895036 50 YES 25 32,768
7
TNONCDB.USERS +DATA/TCDB/B6431750F11C2822E05322F56A40F4C0/DATAFILE/users.348.1058950367 5 YES 1 32,768
UNDOTBS1 +DATA/TCDB/DATAFILE/undotbs1.625.1058941721 305 YES 5 32,768
UNDOTBS2 +DATA/TCDB/DATAFILE/undotbs2.474.1058942129 25 YES 25 32,768
USERS +DATA/TCDB/DATAFILE/users.644.1058941721 5 YES 1 32,768
VIHAAN.SYSAUX +DATA/TCDB/B643F68F44676828E05322F56A40DA6B/DATAFILE/sysaux.639.1058942807 500 YES 10 32,768
VIHAAN.SYSTEM +DATA/TCDB/B643F68F44676828E05322F56A40DA6B/DATAFILE/system.623.1058942807 260 YES 10 32,768
VIHAAN.UNDOTBS1 +DATA/TCDB/B643F68F44676828E05322F56A40DA6B/DATAFILE/undotbs1.624.105894280 100 YES 5 32,768
7
VIHAAN.UNDO_2 +DATA/TCDB/B643F68F44676828E05322F56A40DA6B/DATAFILE/undo_2.649.1058942823 100 YES 5 32,768
VIHAAN.USERS +DATA/TCDB/B643F68F44676828E05322F56A40DA6B/DATAFILE/users.492.1058942823 5 YES 1 32,768
[ BLOCK TRACKING]DISABLED
[ CONTROL FILE ] +DATA/TCDB/CONTROLFILE/current.591.1058941905 19
[ ONLINE REDO LOG ] +DATA/TCDB/ONLINELOG/group_1.622.1058941909 200 200
[ ONLINE REDO LOG ] +DATA/TCDB/ONLINELOG/group_2.637.1058941909 200 200
[ ONLINE REDO LOG ] +DATA/TCDB/ONLINELOG/group_3.872.1058942559 200 200
[ ONLINE REDO LOG ] +DATA/TCDB/ONLINELOG/group_4.621.1058942559 200 200
[ Spfile or Pfile]SPFILE +DATA/TCDB/PARAMETERFILE/spfile.877.1058942559
23 rows selected.
| set line 200 | |
| set pages 1000 | |
| col COMP_ID format a8 | |
| col COMP_NAME format a34 | |
| col SCHEMA format a12 | |
| col STATUS format a10 | |
| col VERSION format a12 | |
| col CON_ID format 99 | |
| select CON_ID, COMP_ID, comp_name, schema, status, version from CDB_REGISTRY order by 1,2; |
