This document will provide the steps to migrate Non-CDB to PDB.
Following are the steps:
In this testcase setup:
Name of the Non-CDB Database is : ORADB
Name of the Container Database is : NEWCDB
1. Verify the Database invalid objects before migration.
SQL>set linesize 400 pages 4000
col owner for a20
col object_name for a30
col object_type for a20
col staus for a10
select owner,object_name,object_type,status from dba_objects where status='INVALID';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------------------ ----------------------- -------
SYS CUST_REPORT_SERVICES PACKAGE BODY INVALID
1 row selected.
2. Ensure consistent (Offline Backup) database backup before activity.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
ORADB MOUNTED
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>Startup mount;
ORACLE instance started.
Total System Global Area 2.1475E+10 bytes
Fixed Size 6081440 bytes
Variable Size 1.4831E+10 bytes
Database Buffers 6576668672 bytes
Redo Buffers 61026304 bytes
Database mounted.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
ORADB MOUNTED
Check Database physical and create directory to keep backup.
SQL> select sum(bytes/1024/1024/1024) from dba_data_files;
SUM(BYTES/1024/1024/1024)
-------------------------
223.5
[oracle@ORAHOSTORADB] /tmp > df -h /backup
/dev/mapper/VG1-lv_backup 500G 305G 195G 61% /backup
[oracle@ORAHOSTORADB] /tmp >cd /backup
[oracle@ORAHOSTORADB] /backup >mkdir ORADB_CONSISTENTBKP
[oracle@ORAHOSTORADB] /backup >cd ORADB_CONSISTENTBKP
[oracle@ORAHOSTORADB] /backup/ORADB_CONSISTENTBKP >rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Mar 19 12:34:01 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB (DBID=2435001820, not open)
RMAN> RUN {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/backup/ORADB_CONSISTENTBKP/backup_%U';
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/backup/ORADB_CONSISTENTBKP/backup_%U';
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT '/backup/ORADB_CONSISTENTBKP/backup_%U';
BACKUP AS COMPRESSED BACKUPSET DATABASE;
BACKUP CURRENT CONTROLFILE FORMAT '/backup/ORADB_CONSISTENTBKP/cntrl_%s_%p_%t';
REL2> EASE CHANNEL disk1;
RELEASE CHANNEL disk2;
RELEASE CHANNEL disk3;
}3> 4> 5> 6> 7> 8> 9> 10>
using target database control file instead of recovery catalog
allocated channel: disk1
channel disk1: SID=570 device type=DISK
allocated channel: disk2
channel disk2: SID=712 device type=DISK
allocated channel: disk3
channel disk3: SID=854 device type=DISK
Starting backup at 19-MAR-20
channel disk1: starting compressed full datafile backup set
channel disk1: specifying datafile(s) in backup set
input datafile file number=00020 name=+DATA/ORADB/DATAFILE/DATA.276.1032075677
input datafile file number=00022 name=+DATA/ORADB/DATAFILE/DATA.272.1032075677
input datafile file number=00025 name=+DATA/ORADB/DATAFILE/DATA.358.1032074331
input datafile file number=00002 name=+DATA/ORADB/DATAFILE/sysaux.262.1032075677
input datafile file number=00021 name=+DATA/ORADB/DATAFILE/DATA.259.1032075677
input datafile file number=00015 name=+DATA/ORADB/DATAFILE/DATA.350.1032075751
channel disk1: starting piece 1 at 19-MAR-20
channel disk2: starting compressed full datafile backup set
channel disk2: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/ORADB/DATAFILE/users.336.1032074331
input datafile file number=00023 name=+DATA/ORADB/DATAFILE/DATA.257.1032074331
input datafile file number=00019 name=+DATA/ORADB/DATAFILE/DATA.369.1032075677
input datafile file number=00003 name=+DATA/ORADB/DATAFILE/undotbs1.270.1032074331
input datafile file number=00004 name=+DATA/ORADB/DATAFILE/lobs.271.1032074331
channel disk2: starting piece 1 at 19-MAR-20
channel disk3: starting compressed full datafile backup set
channel disk3: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/ORADB/DATAFILE/system.359.1032075677
input datafile file number=00011 name=+DATA/ORADB/DATAFILE/DATA_.273.1032074331
input datafile file number=00024 name=+DATA/ORADB/DATAFILE/DATA.337.1032075677
input datafile file number=00006 name=+DATA/ORADB/DATAFILE/indx.263.1032074331
input datafile file number=00007 name=+DATA/ORADB/DATAFILE/DATA.275.1032074331
input datafile file number=00014 name=+DATA/ORADB/DATAFILE/DATA.348.1032075677
channel disk3: starting piece 1 at 19-MAR-20
channel disk2: finished piece 1 at 19-MAR-20
piece handle=/backup/ORADB_CONSISTENTBKP/backup_lmurfq66_1_1 tag=TAG20200319T123413 comment=NONE
channel disk2: backup set complete, elapsed time: 00:09:25
channel disk1: finished piece 1 at 19-MAR-20
piece handle=/backup/ORADB_CONSISTENTBKP/backup_llurfq66_1_1 tag=TAG20200319T123413 comment=NONE
channel disk1: backup set complete, elapsed time: 00:14:55
channel disk3: finished piece 1 at 19-MAR-20
piece handle=/backup/ORADB_CONSISTENTBKP/backup_lnurfq66_1_1 tag=TAG20200319T123413 comment=NONE
channel disk3: backup set complete, elapsed time: 00:17:05
Finished backup at 19-MAR-20
Starting backup at 19-MAR-20
channel disk1: starting full datafile backup set
channel disk1: specifying datafile(s) in backup set
including current control file in backup set
channel disk1: starting piece 1 at 19-MAR-20
channel disk1: finished piece 1 at 19-MAR-20
piece handle=/backup/ORADB_CONSISTENTBKP/cntrl_78520_1_1035463881 tag=TAG20200319T125120 comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-MAR-20
Starting Control File and SPFILE Autobackup at 19-MAR-20
piece handle=+FRA/ORADB/AUTOBACKUP/2020_03_19/s_1035462707.977.1035463887 comment=NONE
Finished Control File and SPFILE Autobackup at 19-MAR-20
released channel: disk1
released channel: disk2
released channel: disk3
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@ORAHOSTORADB] /backup/ORADB_CONSISTENTBKP >ls -lrt
-rw-r-----. 1 oracle asmadmin 4492148736 Mar 19 12:43 backup_lmurfq66_1_1
-rw-r-----. 1 oracle asmadmin 7571537920 Mar 19 12:49 backup_llurfq66_1_1
-rw-r-----. 1 oracle asmadmin 8486076416 Mar 19 12:51 backup_lnurfq66_1_1
-rw-r-----. 1 oracle asmadmin 461275136 Mar 19 12:51 cntrl_78520_1_1035463881
3. Verify Patch details on both Source-ORADB and Target Container DB-NEWCDB(Should be same patches, if not apply Datapatch Before Migration)
Source-ORADB:
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
ORADB MOUNTED
SQL> select patch_id,action,status,action_time,description,bundle_series from DBA_REGISTRY_SQLPATCH;
PATCH_ID ACTION STATUS ACTION_TIME DESCRIPTION BUNDLE_SERIES
---------- --------------- --------------- ------------------------------ ------------------------------------------
26717470 APPLY SUCCESS 2020-04-22 16:56:04,534419 DATABASE BUNDLE PATCH 12.1.0.2.171017 DBBP
Target-NEWCDB:
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
NEWCDB MOUNTED
SQL> select patch_id,action,status,action_time,description,bundle_series from DBA_REGISTRY_SQLPATCH;
PATCH_ID ACTION STATUS ACTION_TIME DESCRIPTION BUNDLE_SERIES
---------- --------------- --------------- ------------------------------ ------------------------------------------
26717470 APPLY SUCCESS 2020-04-22 16:56:04,534419 DATABASE BUNDLE PATCH 12.1.0.2.171017 DBBP
As per above details, Source database patch and Target Container DB patch set are same.
4. Verify ASM DG free space(DATADG Free space should be grater than DB Physical Size)
[oracle@ORAHOST] /home/oracle > ps -ef|grep pmon
oracle 2428 1 0 Apr03 ? 00:00:44 ora_[01;31m pmon_NEWCDB
oracle 5322 1 0 07:01 ? 00:00:01 ora_[01;31m pmon_ORADB
grid 8024 1 0 2019 ? 00:12:09 asm_[01;31m pmon_+ASM
[oracle@ORAHOST] /home/oracle > . oraenv
ORACLE_SID = [oracle] ? ORADB
The Oracle base has been changed from /u01/app to /u01/app/oracle
[oracle@ORAHOSTORADB] /home/oracle > sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 6 10:06:42 2020
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORADB READ WRITE
SQL> select sum(bytes/1024/1024/1024) from dba_data_files;
SUM(BYTES/1024/1024/1024)
-------------------------
223.5
SQL> select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup;
NAME TOTAL_MB/1024 FREE_MB/1024
----------- ------------- ------------
ARCHIVE 510 461.542969
DATA 1400 505.53125
KEYSTORE 10 9.984375
LOG 20 5.84765625
1. To convert non-CDB to PDB, you have to cleanly shutdown the DB:
. oraenv
set environment to ORADB
[oracle@ORAHOSTORADB] /home/oracle > ps -ef|grep pmon
oracle 2428 1 0 Apr03 ? 00:00:45 ora_[01;31m pmon_NEWCDB
oracle 5322 1 0 07:01 ? 00:00:01 ora_[01;31m pmon_ORADB
grid 8024 1 0 2019 ? 00:12:09 asm_[01;31m pmon_+ASM
[oracle@ORAHOSTORADB] /home/oracle > . oraenv
ORACLE_SID = [VASA] ? ORADB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ORAHOSTORADB] /home/oracle > sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 6 10:16:27 2020
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORADB READ WRITE
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ORAHOSTORADB] /home/oracle >
Create Directory to keep XML file
[oracle@ORAHOSTORADB] /home/oracle > cd /tmp
[oracle@ORAHOSTORADB] /tmp >mkdir PDB
[oracle@ORAHOSTORADB] /tmp >cd PDB
[oracle@ORAHOSTORADB] /tmp/PDB > cd
2. Once the DB is shutdown cleanly, open it in read only mode:
[oracle@ORAHOSTORADB] /home/oracle > echo $ORACLE_SID
ORADB
[oracle@ORAHOSTORADB] /home/oracle >sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 6 10:18:09 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup open read only
ORACLE instance started.
Total System Global Area 2.1475E+10 bytes
Fixed Size 6081440 bytes
Variable Size 1.4697E+10 bytes
Database Buffers 6710886400 bytes
Redo Buffers 61026304 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORADB READ ONLY
3. Describe the database and generate the xml file:
SQL> BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/PDB/ORADB.xml');
END;
/ 2 3 4
PL/SQL procedure successfully completed.
SQL>
SQL> !ls -lrt /tmp/PDB/ORADB.xml
-rw-r--r--. 1 oracle oinstall 13555 Apr 6 10:19 /tmp/PDB/ORADB.xml
4. Shutdown the database.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
5. Check if it is compatible with cdb, run below in target CDB
[oracle@ORAHOSTORADB] /home/oracle > ps -ef|grep pmon
oracle 2428 1 0 Apr03 ? 00:00:45 ora_[01;31m pmon_NEWCDB
grid 8024 1 0 2019 ? 00:12:09 asm_[01;31m pmon_+ASM
[oracle@ORAHOSTORADB] /home/oracle > . oraenv
ORACLE_SID = [ORADB] ? NEWCDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ORAHOSTNEWCDB] /home/oracle >sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 6 10:20:48 2020
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
NEWCDB READ WRITE
Verify compatibility check (Should be YES)
SQL> SET SERVEROUTPUT ON;
DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/PDB/ORADB.xml')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/ SQL> 2 3 4 5 6 7 8 9
YES
PL/SQL procedure successfully completed.
Check PDB_PLUG_IN_VIOLATIONS view from cdb database if error raised
SQL> col cause for a30;
col name for a20;
col message for a80 word_wrapped;
col CAUSE for a20
SET PAGESIZE 100;
set linesize 300;
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='ORADB';
NAME CAUSE TYPE MESSAGE STATUS
-------------------- -------------------- --------- -------------------------------------------------------------------------------- ---------
ORADB Non-CDB to PDB WARNING PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. PENDING
ORADB OPTION WARNING Database option APS mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB OPTION WARNING Database option CONTEXT mismatch: PDB installed version NULL. CDB installed PENDING
version 12.1.0.2.0.
ORADB OPTION WARNING Database option DV mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB OPTION WARNING Database option OLS mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB OPTION WARNING Database option ORDIM mismatch: PDB installed version NULL. CDB installed PENDING
version 12.1.0.2.0.
ORADB OPTION WARNING Database option SDO mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB OPTION WARNING Database option XOQ mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB APEX WARNING APEX mismatch: PDB installed version NULL CDB installed version 4.2.5.00.08 PENDING
ORADB Parameter WARNING CDB parameter nls_language mismatch: Previous 'SWEDISH' Current 'AMERICAN' PENDING
ORADB Parameter WARNING CDB parameter nls_territory mismatch: Previous 'SWEDEN' Current 'AMERICA' PENDING
ORADB Parameter WARNING CDB parameter memory_target mismatch: Previous 15G Current 35G PENDING
ORADB Parameter WARNING CDB parameter memory_max_target mismatch: Previous 20G Current 40G PENDING
ORADB Parameter WARNING CDB parameter undo_retention mismatch: Previous 43200 Current 3600 PENDING
ORADB Parameter WARNING CDB parameter recyclebin mismatch: Previous 'OFF' Current 'on' PENDING
ORADB Parameter WARNING CDB parameter session_cached_cursors mismatch: Previous 100 Current 50 PENDING
ORADB Parameter WARNING CDB parameter job_queue_processes mismatch: Previous 50 Current 1000 PENDING
ORADB Service Name Conflic WARNING Service name or network name of service DEVSYTXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service DEVSYT in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SITSITXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SITSIT in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SITDMOXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SITDMO in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SIT in the PDB is invalid or conflicts PENDING
t with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service GGADMIN.OGG$Q_INEDW in the PDB is PENDING
t invalid or conflicts with an existing service name or network name in the CDB.
25 rows selected.
In case you got errors like below:
ERROR PSU bundle patch 1 (PSU Patch 12345): Installed in the CDB but not in the PDB.
ERROR PSU bundle patch 1 (PSU Patch 12345): Installed in the PDB but not in the CDB.
Need to apply Patch in either PDB or CDB to avoid Patch conflict error
6. Connect to the CDB where database has to be plugged in:
. oraen
set environment to NEWCDB
sqlplus / as sysdba
[oracle@ORAHOSTORADB] /home/oracle > ps -ef|grep pmon
oracle 2428 1 0 Apr03 ? 00:00:45 ora_[01;31m pmon_NEWCDB
grid 8024 1 0 2019 ? 00:12:09 asm_[01;31m pmon_+ASM
[oracle@ORAHOSTORADB] /home/oracle > . oraenv
ORACLE_SID = [ORADB] ? NEWCDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ORAHOSTNEWCDB] /home/oracle >sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 6 10:20:48 2020
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
NEWCDB READ WRITE
7. Create a pluggable database
SQL> CREATE PLUGGABLE DATABASE ORADB AS CLONE USING '/tmp/PDB/ORADB.xml';
Pluggable database created.
8. Switch to the PDB container and run the "$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql"
SQL> ALTER SESSION SET CONTAINER=ORADB;
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>#######################################################################
###########################################################################
###########################################################################
########This script may take several mins or hrs dependes on DB size#######
###########################################################################
###########################################################################
###########################################################################
###########################################################################
PL/SQL procedure successfully completed.
9. Startup the PDB and check the open mode.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
6 ORADB MOUNTED
SQL> ALTER PLUGGABLE DATABASE OPEN;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
6 ORADB READ WRITE NO
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
-------------------- ----------
ORADB READ WRITE
1 row selected.
Post Migration Checks:
10. Check Voilation error after post migration from CDB
SQL> !ps -ef|grep pmon
oracle 2428 1 0 Apr03 ? 00:00:45 ora_pmon_NEWCDB
grid 8024 1 0 2019 ? 00:12:10 asm_pmon_+ASM
oracle 8097 17953 0 10:54 pts/2 00:00:00 /bin/bash -c ps -ef|grep pmon
oracle 8099 8097 0 10:54 pts/2 00:00:00 grep pmon
oracle 27833 1 0 Mar26 ? 00:01:53 ora_pmon_CFGDEV2
SQL>exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ORAHOSTNEWCDB] /home/oracle > . oraenv
ORACLE_SID = [NEWCDB] ? NEWCDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ORAHOSTNEWCDB] /home/oracle >
[oracle@ORAHOSTNEWCDB] /home/oracle >
[oracle@ORAHOSTNEWCDB] /home/oracle > sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 6 10:55:30 2020
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> col cause for a30;
col name for a20;
col message for a80 word_wrapped;
col CAUSE for a20
SET PAGESIZE 100;
set linesize 300;
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='ORADB'; SQL> SQL> SQL> SQL> SQL> SQL>
NAME CAUSE TYPE MESSAGE STATUS
-------------------- -------------------- --------- -------------------------------------------------------------------------------- ---------
ORADB Parameter WARNING CDB parameter nls_language mismatch: Previous 'SWEDISH' Current 'AMERICAN' RESOLVED
ORADB Parameter WARNING CDB parameter nls_territory mismatch: Previous 'SWEDEN' Current 'AMERICA' RESOLVED
ORADB Parameter WARNING CDB parameter memory_target mismatch: Previous 15G Current 35G RESOLVED
ORADB Parameter WARNING CDB parameter memory_max_target mismatch: Previous 20G Current 40G RESOLVED
ORADB Parameter WARNING CDB parameter undo_retention mismatch: Previous 43200 Current 3600 RESOLVED
ORADB Parameter WARNING CDB parameter recyclebin mismatch: Previous 'OFF' Current 'on' RESOLVED
ORADB Parameter WARNING CDB parameter session_cached_cursors mismatch: Previous 100 Current 50 RESOLVED
ORADB Parameter WARNING CDB parameter job_queue_processes mismatch: Previous 50 Current 1000 RESOLVED
ORADB Service Name Conflic WARNING Service name or network name of service ORADB in the PDB is invalid or conflicts RESOLVED
t with an existing service name or network name in the CDB.
ORADB Non-CDB to PDB ERROR PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. RESOLVED
ORADB OPTION WARNING Database option APS mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB OPTION WARNING Database option CONTEXT mismatch: PDB installed version NULL. CDB installed PENDING
version 12.1.0.2.0.
ORADB OPTION WARNING Database option DV mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB OPTION WARNING Database option OLS mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB OPTION WARNING Database option ORDIM mismatch: PDB installed version NULL. CDB installed PENDING
version 12.1.0.2.0.
ORADB OPTION WARNING Database option SDO mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB OPTION WARNING Database option XOQ mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB APEX WARNING APEX mismatch: PDB installed version NULL CDB installed version 4.2.5.00.08 PENDING
ORADB Service Name Conflic WARNING Service name or network name of service DEVSYTXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service DEVSYT in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SITSITXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SITSIT in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SITDMOXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SITDMO in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SIT in the PDB is invalid or conflicts PENDING
t with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service GGADMIN.OGG$Q_INEDW in the PDB is PENDING
t invalid or conflicts with an existing service name or network name in the CDB.
26 rows selected.
11. Check GUID of PDB(It should match with Datafile Path)
SQL> alter session set container=ORADB;
Session altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
-------------------- --------------------
NEWCDB READ WRITE
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
-------------------- ----------
ORADB READ WRITE
SQL> selet name from v$controlfile;
NAME
----------------------------------------------------------------------
+DATA/NEWCDB/CONTROLFILE/current.363.1035818709
+LOG/NEWCDB/CONTROLFILE/current.292.1035818709
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
+DATA/NEWCDB/DATAFILE/undotbs1.357.1035808135
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/system.265.1037010901
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/sysaux.295.1037010901
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/lobs.267.1037010903
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/users.322.1037010903
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/indx.262.1037010903
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA_.338.1037010901
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA.269.1037010901
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA.321.1037010903
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA.263.1037010903
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA.328.1037010901
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA.271.1037010903
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA.311.1037010903
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA.319.1037010903
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA.287.1037010903
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA.324.1037010903
16 rows selected.
SQL> select name from v$tempfile;
NAME
----------------------------------------------------------------------------------------------------
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/TEMPFILE/temp.313.1037011249
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/TEMPFILE/temp1.315.1037011249
SQL> col NAME for a15
select CON_ID, GUID, NAME, OPEN_MODE,RESTRICTED from v$pdbs;
CON_ID GUID NAME OPEN_MODE RES
---------- -------------------------------- --------------- ---------- ---
6 B21R324R3G643J6K93W54M14X75B59F4 ORADB READ WRITE NO
SQL> select con_id, dbid, con_uid, guid, name, open_mode from v$containers;
CON_ID DBID CON_UID GUID NAME OPEN_MODE
---------- ---------- ---------- -------------------------------- --------------- ----------
6 4183383913 4183383913 B21R324R3G643J6K93W54M14X75B59F4 ORADB READ WRITE
12. Once DB Migrated to PDB Monitor DB and if all looks fine drop NON-CDB(Single Instance)
Oracle DOC:How to Convert Non-CDB to PDB Database in 12c - Testcase (Doc ID 2012448.1)
Following are the steps:
In this testcase setup:
Name of the Non-CDB Database is : ORADB
Name of the Container Database is : NEWCDB
Prerequisites:
1. Verify the Database invalid objects before migration.
SQL>set linesize 400 pages 4000
col owner for a20
col object_name for a30
col object_type for a20
col staus for a10
select owner,object_name,object_type,status from dba_objects where status='INVALID';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------------------ ----------------------- -------
SYS CUST_REPORT_SERVICES PACKAGE BODY INVALID
1 row selected.
2. Ensure consistent (Offline Backup) database backup before activity.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
ORADB MOUNTED
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>Startup mount;
ORACLE instance started.
Total System Global Area 2.1475E+10 bytes
Fixed Size 6081440 bytes
Variable Size 1.4831E+10 bytes
Database Buffers 6576668672 bytes
Redo Buffers 61026304 bytes
Database mounted.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
ORADB MOUNTED
Check Database physical and create directory to keep backup.
SQL> select sum(bytes/1024/1024/1024) from dba_data_files;
SUM(BYTES/1024/1024/1024)
-------------------------
223.5
[oracle@ORAHOSTORADB] /tmp > df -h /backup
/dev/mapper/VG1-lv_backup 500G 305G 195G 61% /backup
[oracle@ORAHOSTORADB] /tmp >cd /backup
[oracle@ORAHOSTORADB] /backup >mkdir ORADB_CONSISTENTBKP
[oracle@ORAHOSTORADB] /backup >cd ORADB_CONSISTENTBKP
[oracle@ORAHOSTORADB] /backup/ORADB_CONSISTENTBKP >rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Mar 19 12:34:01 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB (DBID=2435001820, not open)
RMAN> RUN {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/backup/ORADB_CONSISTENTBKP/backup_%U';
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/backup/ORADB_CONSISTENTBKP/backup_%U';
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT '/backup/ORADB_CONSISTENTBKP/backup_%U';
BACKUP AS COMPRESSED BACKUPSET DATABASE;
BACKUP CURRENT CONTROLFILE FORMAT '/backup/ORADB_CONSISTENTBKP/cntrl_%s_%p_%t';
REL2> EASE CHANNEL disk1;
RELEASE CHANNEL disk2;
RELEASE CHANNEL disk3;
}3> 4> 5> 6> 7> 8> 9> 10>
using target database control file instead of recovery catalog
allocated channel: disk1
channel disk1: SID=570 device type=DISK
allocated channel: disk2
channel disk2: SID=712 device type=DISK
allocated channel: disk3
channel disk3: SID=854 device type=DISK
Starting backup at 19-MAR-20
channel disk1: starting compressed full datafile backup set
channel disk1: specifying datafile(s) in backup set
input datafile file number=00020 name=+DATA/ORADB/DATAFILE/DATA.276.1032075677
input datafile file number=00022 name=+DATA/ORADB/DATAFILE/DATA.272.1032075677
input datafile file number=00025 name=+DATA/ORADB/DATAFILE/DATA.358.1032074331
input datafile file number=00002 name=+DATA/ORADB/DATAFILE/sysaux.262.1032075677
input datafile file number=00021 name=+DATA/ORADB/DATAFILE/DATA.259.1032075677
input datafile file number=00015 name=+DATA/ORADB/DATAFILE/DATA.350.1032075751
channel disk1: starting piece 1 at 19-MAR-20
channel disk2: starting compressed full datafile backup set
channel disk2: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/ORADB/DATAFILE/users.336.1032074331
input datafile file number=00023 name=+DATA/ORADB/DATAFILE/DATA.257.1032074331
input datafile file number=00019 name=+DATA/ORADB/DATAFILE/DATA.369.1032075677
input datafile file number=00003 name=+DATA/ORADB/DATAFILE/undotbs1.270.1032074331
input datafile file number=00004 name=+DATA/ORADB/DATAFILE/lobs.271.1032074331
channel disk2: starting piece 1 at 19-MAR-20
channel disk3: starting compressed full datafile backup set
channel disk3: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/ORADB/DATAFILE/system.359.1032075677
input datafile file number=00011 name=+DATA/ORADB/DATAFILE/DATA_.273.1032074331
input datafile file number=00024 name=+DATA/ORADB/DATAFILE/DATA.337.1032075677
input datafile file number=00006 name=+DATA/ORADB/DATAFILE/indx.263.1032074331
input datafile file number=00007 name=+DATA/ORADB/DATAFILE/DATA.275.1032074331
input datafile file number=00014 name=+DATA/ORADB/DATAFILE/DATA.348.1032075677
channel disk3: starting piece 1 at 19-MAR-20
channel disk2: finished piece 1 at 19-MAR-20
piece handle=/backup/ORADB_CONSISTENTBKP/backup_lmurfq66_1_1 tag=TAG20200319T123413 comment=NONE
channel disk2: backup set complete, elapsed time: 00:09:25
channel disk1: finished piece 1 at 19-MAR-20
piece handle=/backup/ORADB_CONSISTENTBKP/backup_llurfq66_1_1 tag=TAG20200319T123413 comment=NONE
channel disk1: backup set complete, elapsed time: 00:14:55
channel disk3: finished piece 1 at 19-MAR-20
piece handle=/backup/ORADB_CONSISTENTBKP/backup_lnurfq66_1_1 tag=TAG20200319T123413 comment=NONE
channel disk3: backup set complete, elapsed time: 00:17:05
Finished backup at 19-MAR-20
Starting backup at 19-MAR-20
channel disk1: starting full datafile backup set
channel disk1: specifying datafile(s) in backup set
including current control file in backup set
channel disk1: starting piece 1 at 19-MAR-20
channel disk1: finished piece 1 at 19-MAR-20
piece handle=/backup/ORADB_CONSISTENTBKP/cntrl_78520_1_1035463881 tag=TAG20200319T125120 comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-MAR-20
Starting Control File and SPFILE Autobackup at 19-MAR-20
piece handle=+FRA/ORADB/AUTOBACKUP/2020_03_19/s_1035462707.977.1035463887 comment=NONE
Finished Control File and SPFILE Autobackup at 19-MAR-20
released channel: disk1
released channel: disk2
released channel: disk3
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@ORAHOSTORADB] /backup/ORADB_CONSISTENTBKP >ls -lrt
-rw-r-----. 1 oracle asmadmin 4492148736 Mar 19 12:43 backup_lmurfq66_1_1
-rw-r-----. 1 oracle asmadmin 7571537920 Mar 19 12:49 backup_llurfq66_1_1
-rw-r-----. 1 oracle asmadmin 8486076416 Mar 19 12:51 backup_lnurfq66_1_1
-rw-r-----. 1 oracle asmadmin 461275136 Mar 19 12:51 cntrl_78520_1_1035463881
3. Verify Patch details on both Source-ORADB and Target Container DB-NEWCDB(Should be same patches, if not apply Datapatch Before Migration)
Source-ORADB:
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
ORADB MOUNTED
SQL> select patch_id,action,status,action_time,description,bundle_series from DBA_REGISTRY_SQLPATCH;
PATCH_ID ACTION STATUS ACTION_TIME DESCRIPTION BUNDLE_SERIES
---------- --------------- --------------- ------------------------------ ------------------------------------------
26717470 APPLY SUCCESS 2020-04-22 16:56:04,534419 DATABASE BUNDLE PATCH 12.1.0.2.171017 DBBP
Target-NEWCDB:
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
NEWCDB MOUNTED
SQL> select patch_id,action,status,action_time,description,bundle_series from DBA_REGISTRY_SQLPATCH;
PATCH_ID ACTION STATUS ACTION_TIME DESCRIPTION BUNDLE_SERIES
---------- --------------- --------------- ------------------------------ ------------------------------------------
26717470 APPLY SUCCESS 2020-04-22 16:56:04,534419 DATABASE BUNDLE PATCH 12.1.0.2.171017 DBBP
As per above details, Source database patch and Target Container DB patch set are same.
4. Verify ASM DG free space(DATADG Free space should be grater than DB Physical Size)
[oracle@ORAHOST] /home/oracle > ps -ef|grep pmon
oracle 2428 1 0 Apr03 ? 00:00:44 ora_[01;31m pmon_NEWCDB
oracle 5322 1 0 07:01 ? 00:00:01 ora_[01;31m pmon_ORADB
grid 8024 1 0 2019 ? 00:12:09 asm_[01;31m pmon_+ASM
[oracle@ORAHOST] /home/oracle > . oraenv
ORACLE_SID = [oracle] ? ORADB
The Oracle base has been changed from /u01/app to /u01/app/oracle
[oracle@ORAHOSTORADB] /home/oracle > sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 6 10:06:42 2020
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORADB READ WRITE
SQL> select sum(bytes/1024/1024/1024) from dba_data_files;
SUM(BYTES/1024/1024/1024)
-------------------------
223.5
SQL> select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup;
NAME TOTAL_MB/1024 FREE_MB/1024
----------- ------------- ------------
ARCHIVE 510 461.542969
DATA 1400 505.53125
KEYSTORE 10 9.984375
LOG 20 5.84765625
Step for converting non-CDB database to PDB.
1. To convert non-CDB to PDB, you have to cleanly shutdown the DB:
. oraenv
set environment to ORADB
[oracle@ORAHOSTORADB] /home/oracle > ps -ef|grep pmon
oracle 2428 1 0 Apr03 ? 00:00:45 ora_[01;31m pmon_NEWCDB
oracle 5322 1 0 07:01 ? 00:00:01 ora_[01;31m pmon_ORADB
grid 8024 1 0 2019 ? 00:12:09 asm_[01;31m pmon_+ASM
[oracle@ORAHOSTORADB] /home/oracle > . oraenv
ORACLE_SID = [VASA] ? ORADB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ORAHOSTORADB] /home/oracle > sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 6 10:16:27 2020
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORADB READ WRITE
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ORAHOSTORADB] /home/oracle >
Create Directory to keep XML file
[oracle@ORAHOSTORADB] /home/oracle > cd /tmp
[oracle@ORAHOSTORADB] /tmp >mkdir PDB
[oracle@ORAHOSTORADB] /tmp >cd PDB
[oracle@ORAHOSTORADB] /tmp/PDB > cd
2. Once the DB is shutdown cleanly, open it in read only mode:
[oracle@ORAHOSTORADB] /home/oracle > echo $ORACLE_SID
ORADB
[oracle@ORAHOSTORADB] /home/oracle >sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 6 10:18:09 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup open read only
ORACLE instance started.
Total System Global Area 2.1475E+10 bytes
Fixed Size 6081440 bytes
Variable Size 1.4697E+10 bytes
Database Buffers 6710886400 bytes
Redo Buffers 61026304 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORADB READ ONLY
3. Describe the database and generate the xml file:
SQL> BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/PDB/ORADB.xml');
END;
/ 2 3 4
PL/SQL procedure successfully completed.
SQL>
SQL> !ls -lrt /tmp/PDB/ORADB.xml
-rw-r--r--. 1 oracle oinstall 13555 Apr 6 10:19 /tmp/PDB/ORADB.xml
4. Shutdown the database.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
5. Check if it is compatible with cdb, run below in target CDB
[oracle@ORAHOSTORADB] /home/oracle > ps -ef|grep pmon
oracle 2428 1 0 Apr03 ? 00:00:45 ora_[01;31m pmon_NEWCDB
grid 8024 1 0 2019 ? 00:12:09 asm_[01;31m pmon_+ASM
[oracle@ORAHOSTORADB] /home/oracle > . oraenv
ORACLE_SID = [ORADB] ? NEWCDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ORAHOSTNEWCDB] /home/oracle >sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 6 10:20:48 2020
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
NEWCDB READ WRITE
Verify compatibility check (Should be YES)
SQL> SET SERVEROUTPUT ON;
DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/PDB/ORADB.xml')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/ SQL> 2 3 4 5 6 7 8 9
YES
PL/SQL procedure successfully completed.
Check PDB_PLUG_IN_VIOLATIONS view from cdb database if error raised
SQL> col cause for a30;
col name for a20;
col message for a80 word_wrapped;
col CAUSE for a20
SET PAGESIZE 100;
set linesize 300;
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='ORADB';
NAME CAUSE TYPE MESSAGE STATUS
-------------------- -------------------- --------- -------------------------------------------------------------------------------- ---------
ORADB Non-CDB to PDB WARNING PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. PENDING
ORADB OPTION WARNING Database option APS mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB OPTION WARNING Database option CONTEXT mismatch: PDB installed version NULL. CDB installed PENDING
version 12.1.0.2.0.
ORADB OPTION WARNING Database option DV mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB OPTION WARNING Database option OLS mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB OPTION WARNING Database option ORDIM mismatch: PDB installed version NULL. CDB installed PENDING
version 12.1.0.2.0.
ORADB OPTION WARNING Database option SDO mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB OPTION WARNING Database option XOQ mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB APEX WARNING APEX mismatch: PDB installed version NULL CDB installed version 4.2.5.00.08 PENDING
ORADB Parameter WARNING CDB parameter nls_language mismatch: Previous 'SWEDISH' Current 'AMERICAN' PENDING
ORADB Parameter WARNING CDB parameter nls_territory mismatch: Previous 'SWEDEN' Current 'AMERICA' PENDING
ORADB Parameter WARNING CDB parameter memory_target mismatch: Previous 15G Current 35G PENDING
ORADB Parameter WARNING CDB parameter memory_max_target mismatch: Previous 20G Current 40G PENDING
ORADB Parameter WARNING CDB parameter undo_retention mismatch: Previous 43200 Current 3600 PENDING
ORADB Parameter WARNING CDB parameter recyclebin mismatch: Previous 'OFF' Current 'on' PENDING
ORADB Parameter WARNING CDB parameter session_cached_cursors mismatch: Previous 100 Current 50 PENDING
ORADB Parameter WARNING CDB parameter job_queue_processes mismatch: Previous 50 Current 1000 PENDING
ORADB Service Name Conflic WARNING Service name or network name of service DEVSYTXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service DEVSYT in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SITSITXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SITSIT in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SITDMOXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SITDMO in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SIT in the PDB is invalid or conflicts PENDING
t with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service GGADMIN.OGG$Q_INEDW in the PDB is PENDING
t invalid or conflicts with an existing service name or network name in the CDB.
25 rows selected.
In case you got errors like below:
ERROR PSU bundle patch 1 (PSU Patch 12345): Installed in the CDB but not in the PDB.
ERROR PSU bundle patch 1 (PSU Patch 12345): Installed in the PDB but not in the CDB.
Need to apply Patch in either PDB or CDB to avoid Patch conflict error
6. Connect to the CDB where database has to be plugged in:
. oraen
set environment to NEWCDB
sqlplus / as sysdba
[oracle@ORAHOSTORADB] /home/oracle > ps -ef|grep pmon
oracle 2428 1 0 Apr03 ? 00:00:45 ora_[01;31m pmon_NEWCDB
grid 8024 1 0 2019 ? 00:12:09 asm_[01;31m pmon_+ASM
[oracle@ORAHOSTORADB] /home/oracle > . oraenv
ORACLE_SID = [ORADB] ? NEWCDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ORAHOSTNEWCDB] /home/oracle >sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 6 10:20:48 2020
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
NEWCDB READ WRITE
7. Create a pluggable database
SQL> CREATE PLUGGABLE DATABASE ORADB AS CLONE USING '/tmp/PDB/ORADB.xml';
Pluggable database created.
8. Switch to the PDB container and run the "$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql"
SQL> ALTER SESSION SET CONTAINER=ORADB;
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>#######################################################################
###########################################################################
###########################################################################
########This script may take several mins or hrs dependes on DB size#######
###########################################################################
###########################################################################
###########################################################################
###########################################################################
PL/SQL procedure successfully completed.
9. Startup the PDB and check the open mode.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
6 ORADB MOUNTED
SQL> ALTER PLUGGABLE DATABASE OPEN;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
6 ORADB READ WRITE NO
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
-------------------- ----------
ORADB READ WRITE
1 row selected.
Post Migration Checks:
10. Check Voilation error after post migration from CDB
SQL> !ps -ef|grep pmon
oracle 2428 1 0 Apr03 ? 00:00:45 ora_pmon_NEWCDB
grid 8024 1 0 2019 ? 00:12:10 asm_pmon_+ASM
oracle 8097 17953 0 10:54 pts/2 00:00:00 /bin/bash -c ps -ef|grep pmon
oracle 8099 8097 0 10:54 pts/2 00:00:00 grep pmon
oracle 27833 1 0 Mar26 ? 00:01:53 ora_pmon_CFGDEV2
SQL>exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ORAHOSTNEWCDB] /home/oracle > . oraenv
ORACLE_SID = [NEWCDB] ? NEWCDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ORAHOSTNEWCDB] /home/oracle >
[oracle@ORAHOSTNEWCDB] /home/oracle >
[oracle@ORAHOSTNEWCDB] /home/oracle > sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 6 10:55:30 2020
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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> col cause for a30;
col name for a20;
col message for a80 word_wrapped;
col CAUSE for a20
SET PAGESIZE 100;
set linesize 300;
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='ORADB'; SQL> SQL> SQL> SQL> SQL> SQL>
NAME CAUSE TYPE MESSAGE STATUS
-------------------- -------------------- --------- -------------------------------------------------------------------------------- ---------
ORADB Parameter WARNING CDB parameter nls_language mismatch: Previous 'SWEDISH' Current 'AMERICAN' RESOLVED
ORADB Parameter WARNING CDB parameter nls_territory mismatch: Previous 'SWEDEN' Current 'AMERICA' RESOLVED
ORADB Parameter WARNING CDB parameter memory_target mismatch: Previous 15G Current 35G RESOLVED
ORADB Parameter WARNING CDB parameter memory_max_target mismatch: Previous 20G Current 40G RESOLVED
ORADB Parameter WARNING CDB parameter undo_retention mismatch: Previous 43200 Current 3600 RESOLVED
ORADB Parameter WARNING CDB parameter recyclebin mismatch: Previous 'OFF' Current 'on' RESOLVED
ORADB Parameter WARNING CDB parameter session_cached_cursors mismatch: Previous 100 Current 50 RESOLVED
ORADB Parameter WARNING CDB parameter job_queue_processes mismatch: Previous 50 Current 1000 RESOLVED
ORADB Service Name Conflic WARNING Service name or network name of service ORADB in the PDB is invalid or conflicts RESOLVED
t with an existing service name or network name in the CDB.
ORADB Non-CDB to PDB ERROR PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. RESOLVED
ORADB OPTION WARNING Database option APS mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB OPTION WARNING Database option CONTEXT mismatch: PDB installed version NULL. CDB installed PENDING
version 12.1.0.2.0.
ORADB OPTION WARNING Database option DV mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB OPTION WARNING Database option OLS mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB OPTION WARNING Database option ORDIM mismatch: PDB installed version NULL. CDB installed PENDING
version 12.1.0.2.0.
ORADB OPTION WARNING Database option SDO mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB OPTION WARNING Database option XOQ mismatch: PDB installed version NULL. CDB installed version PENDING
12.1.0.2.0.
ORADB APEX WARNING APEX mismatch: PDB installed version NULL CDB installed version 4.2.5.00.08 PENDING
ORADB Service Name Conflic WARNING Service name or network name of service DEVSYTXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service DEVSYT in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SITSITXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SITSIT in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SITDMOXDB in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SITDMO in the PDB is invalid or PENDING
t conflicts with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service SIT in the PDB is invalid or conflicts PENDING
t with an existing service name or network name in the CDB.
ORADB Service Name Conflic WARNING Service name or network name of service GGADMIN.OGG$Q_INEDW in the PDB is PENDING
t invalid or conflicts with an existing service name or network name in the CDB.
26 rows selected.
11. Check GUID of PDB(It should match with Datafile Path)
SQL> alter session set container=ORADB;
Session altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
-------------------- --------------------
NEWCDB READ WRITE
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
-------------------- ----------
ORADB READ WRITE
SQL> selet name from v$controlfile;
NAME
----------------------------------------------------------------------
+DATA/NEWCDB/CONTROLFILE/current.363.1035818709
+LOG/NEWCDB/CONTROLFILE/current.292.1035818709
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
+DATA/NEWCDB/DATAFILE/undotbs1.357.1035808135
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/system.265.1037010901
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/sysaux.295.1037010901
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/lobs.267.1037010903
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/users.322.1037010903
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/indx.262.1037010903
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA_.338.1037010901
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA.269.1037010901
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA.321.1037010903
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA.263.1037010903
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA.328.1037010901
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA.271.1037010903
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA.311.1037010903
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA.319.1037010903
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA.287.1037010903
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/DATAFILE/DATA.324.1037010903
16 rows selected.
SQL> select name from v$tempfile;
NAME
----------------------------------------------------------------------------------------------------
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/TEMPFILE/temp.313.1037011249
+DATA/NEWCDB/B21R324R3G643J6K93W54M14X75B59F4/TEMPFILE/temp1.315.1037011249
SQL> col NAME for a15
select CON_ID, GUID, NAME, OPEN_MODE,RESTRICTED from v$pdbs;
CON_ID GUID NAME OPEN_MODE RES
---------- -------------------------------- --------------- ---------- ---
6 B21R324R3G643J6K93W54M14X75B59F4 ORADB READ WRITE NO
SQL> select con_id, dbid, con_uid, guid, name, open_mode from v$containers;
CON_ID DBID CON_UID GUID NAME OPEN_MODE
---------- ---------- ---------- -------------------------------- --------------- ----------
6 4183383913 4183383913 B21R324R3G643J6K93W54M14X75B59F4 ORADB READ WRITE
12. Once DB Migrated to PDB Monitor DB and if all looks fine drop NON-CDB(Single Instance)
Oracle DOC:How to Convert Non-CDB to PDB Database in 12c - Testcase (Doc ID 2012448.1)
No comments:
Post a Comment