Hello Friend's,
In This Post we will discuss about how to migrate database from 12.2 Noncdb to 19c PDB
So let's get started
Source :
Hostname : dm01db12c.database.com
Database : OTM8P
Version : 12.2.0.1
CDB/PDB : NON CDB/PDB
Oacle_Home : /oracle/app/orawork/product/12.2.0.1/db_1
Target :
Hostname : dm01db12c.database.com
Database : CDBOTM8P
Version : 19.0.0.0
CDB/PDB : Yes
Oacle_Home : /oracle/app/orawork/product/19.0.0.0/db_1
Create Tables to check after the migration :
[oracle@dm01db12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jul 10 19:33:15 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,v$instance;
SQL> SQL> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- -----------------------------
OTM8P dm01db12c.database.com PRIMARY READ WRITE ARCHIVELOG 12.2.0.1.0 ALLOWED 10-JUL-2020 19:32:01
SQL> create tablespace bigtab datafile '+DBDATA' size 2G autoextend on next 500M maxsize 5G;
Tablespace created.
SQL> create user bigone identified by bigone quota unlimited on bigtab;
User created.
SQL> grant connect,resource to bigone;
Grant succeeded.
SQL> alter user bigone default tablespace bigtab;
User altered.
SQL> CREATE TABLE bigone.bigtab (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
); 2 3 4 5 6
Table created.
SQL> DECLARE
l_lookup_id NUMBER(10);
l_create_date DATE;
BEGIN
FOR i IN 1 .. 1000000 LOOP
IF MOD(i, 3) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -24);
l_lookup_id := 2;
ELSIF MOD(i, 2) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -12);
l_lookup_id := 1;
ELSE
l_create_date := SYSDATE;
l_lookup_id := 3;
END IF;
INSERT INTO bigone.bigtab (id, created_date, lookup_id, data)
VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
END LOOP;
COMMIT;
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
PL/SQL procedure successfully completed.
SQL> select count(*) from bigone.bigtab;
COUNT(*)
----------
1000000
STEP 1: Perform the following steps to cleanly shut down the non-CDB database:
[oracle@dm01db12c ~]$ ps -ef |grep pmon
grid 9617 1 0 19:11 ? 00:00:00 asm_pmon_+ASM
oracle 26617 1 0 19:32 ? 00:00:00 ora_pmon_OTM8P
oracle 32575 20226 0 19:40 pts/2 00:00:00 grep --color=auto pmon
[oracle@dm01db12c ~]$
[oracle@dm01db12c ~]$
[oracle@dm01db12c ~]$ srvctl stop database -d OTM8P -o immediate
or :
Shutdown immediate on sql prompt
STEP 2: Start database open read only
[oracle@dm01db12c ~]$ srvctl start database -d OTM8P -o "read only"
[oracle@dm01db12c ~]$
[oracle@dm01db12c ~]$
[oracle@dm01db12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jul 10 19:49:19 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,v$instance;
SQL> SQL> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- -----------------------------
OTM8P dm01db12c.database.com PRIMARY READ ONLY ARCHIVELOG 12.2.0.1.0 ALLOWED 10-JUL-2020 19:49:03
STEP 3: GENERATE A PDB XML FILE:
[oracle@dm01db12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jul 10 19:49:19 2020
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> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- -----------------------------
OTM8P dm01db12c.database.com PRIMARY READ ONLY ARCHIVELOG 12.2.0.1.0 ALLOWED 10-JUL-2020 19:49:03
SQL>
SQL> BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => '/oracle/app/orawork/NonCDB122.xml');
END;
/ 2 3 4
PL/SQL procedure successfully completed.
SQL> !ls -ltr /oracle/app/orawork/NonCDB122.xml
-rw-r--r--. 1 oracle asmadmin 7496 Jul 10 19:52 /oracle/app/orawork/NonCDB122.xml
SQL>
STEP 4: SHUTDOWN THE 12c NON-CDB:
[oracle@dm01db12c ~]$ srvctl stop database -d OTM8P -o immediate
[oracle@dm01db12c ~]$
STEP 5: START THE 19c CDB :
Chek the compatibility where the otput should be YES. If NO, you can check for violations (pdb_plug_in_violations).
If there is compatibility 'NO' It's Mostly related to mismatch with database components or different patch levels.
SQL>
SQL> SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/oracle/app/orawork/NonCDB122.xml')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/SQL> 2 3 4 5 6 7 8 9 10 11
NO
PL/SQL procedure successfully completed.
STEP 6: CHECK FOR ERRORS:
After the CDB database startup completes, perform the below steps to check for errors in the PDB_PLUG_IN_VIOLATIONS view:
SQL> select message from pdb_plug_in_violations where type like '%ERR%' and status <> 'RESOLVED';
MESSAGE
--------------------------------------------------------------------------------
PDB's version does not match CDB's version: PDB's version 12.2.0.1.0. CDB's vers
ion 19.0.0.0.0.
'19.3.0.0.0 Release_Update 1904101227' is installed in the CDB but no release up
dates are installed in the PDB
This error can be ignored :
STEP 7: CONNECT TO THE 19C CDB AND PLUG INTO THE PDBs to CDB:
Perform the below steps to connect to the CDB and plug into the PDDB12C database by using the non-CDB XML file:
--- Creating the PDBs in to 19c CDB ---
SQL> create pluggable database PDBOTM8P using '/oracle/app/orawork/NonCDB122.xml' COPY FILE_NAME_CONVERT=('+DBDATA','+DATA');
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBOTM8P MOUNTED
COPY: The datafiles of the noncdb remain intact, and the noncdb is copied to create a PDB at the new location and keep the original datafiles intact at the original location. This means that a noncdb database is still operational after the creation of the PDB.
MOVE: The datafiles of the noncdb are moved to a new location to create a PDB. In this case, the noncdb database is not available after the PDB is created.
NOCOPY: The datafiles of the noncdb are used to create a PDB2, and it uses the same location as the noncdb. In this case, the noncdb database is not available after the PDB is created.
You can use the FILE_NAME_CONVERT parameter to specify the new location of the datafiles while using either the COPY or MOVE option.
Step 7 : Start with the PDB upgrade after migration to CDB :
Open database in upgrade mode "alter pluggable database PDBOTM8P open upgrade"
[oracle@dm01db12c db_1]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 11 14:36:42 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBOTM8P MIGRATE YES
SQL>
SQL>
SQL>
SQL> !mkdir -p /oracle/app/orawork/upg_logs
Start with the PDB upgrade Steps
[oracle@dm01db12c db_1]$ dbupgrade -c PDBOTM8P -l /oracle/app/orawork/upg_logs
Argument list for [/oracle/app/orawork/product/19.0.0.0/db_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = PDBOTM8P
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = /oracle/app/orawork/upg_logs
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]
/oracle/app/orawork/product/19.0.0.0/db_1/rdbms/admin/orahome = [/oracle/app/orawork/product/19.0.0.0/db_1]
/oracle/app/orawork/product/19.0.0.0/db_1/bin/orabasehome = [/oracle/app/orawork/product/19.0.0.0/db_1]
catctlGetOraBaseLogDir = [/oracle/app/orawork/product/19.0.0.0/db_1]
Analyzing file /oracle/app/orawork/product/19.0.0.0/db_1/rdbms/admin/catupgrd.sql
Log file directory = [/oracle/app/orawork/upg_logs]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/oracle/app/orawork/upg_logs/catupgrd_catcon_23693.lst]
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/upg_logs/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/upg_logs/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 1
Database Name = CDBOTM8P
DataBase Version = 19.0.0.0.0
PDB Parallel SQL Process Count = [2] is higher or equal to CPU Count = [1]
Concurrent PDB Upgrades defaulting to CPU Count [1]
Parallel SQL Process Count (PDB) = 2
Parallel SQL Process Count (CDB$ROOT) = 4
Concurrent PDB Upgrades = 1
Generated PDB Inclusion:[PDBOTM8P]
CDB$ROOT Open Mode = [OPEN]
Start processing of PDBs (PDBOTM8P)
[/oracle/app/orawork/product/19.0.0.0/db_1/perl/bin/perl /oracle/app/orawork/product/19.0.0.0/db_1/rdbms/admin/catctl.pl -c 'PDBOTM8P' -l /oracle/app/orawork/upg_logs -I -i pdbotm8p -n 2 /oracle/app/orawork/product/19.0.0.0/db_1/rdbms/admin/catupgrd.sql]
Argument list for [/oracle/app/orawork/product/19.0.0.0/db_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = PDBOTM8P
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = pdbotm8p
Child Process I = 1
Log Dir l = /oracle/app/orawork/upg_logs
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 2
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]
/oracle/app/orawork/product/19.0.0.0/db_1/rdbms/admin/orahome = [/oracle/app/orawork/product/19.0.0.0/db_1]
/oracle/app/orawork/product/19.0.0.0/db_1/bin/orabasehome = [/oracle/app/orawork/product/19.0.0.0/db_1]
catctlGetOraBaseLogDir = [/oracle/app/orawork/product/19.0.0.0/db_1]
Analyzing file /oracle/app/orawork/product/19.0.0.0/db_1/rdbms/admin/catupgrd.sql
Log file directory = [/oracle/app/orawork/upg_logs]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/oracle/app/orawork/upg_logs/catupgrdpdbotm8p_catcon_24057.lst]
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/upg_logs/catupgrdpdbotm8p*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/upg_logs/catupgrdpdbotm8p_*.lst] files for spool files, if any
Number of Cpus = 1
Database Name = CDBOTM8P
DataBase Version = 19.0.0.0.0
PDBOTM8P Open Mode = [MIGRATE]
Generated PDB Inclusion:[PDBOTM8P]
CDB$ROOT Open Mode = [OPEN]
Components in [PDBOTM8P]
Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [APEX EM MGW ODM RAC WK]
------------------------------------------------------
Phases [0-107] Start Time:[2020_07_11 14:40:44]
Container Lists Inclusion:[PDBOTM8P] Exclusion:[NONE]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [PDBOTM8P] Files:1 Time: 32s
*************** Catalog Core SQL ***************
Serial Phase #:1 [PDBOTM8P] Files:5 Time: 45s
Restart Phase #:2 [PDBOTM8P] Files:1 Time: 2s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [PDBOTM8P] Files:19 Time: 33s
Restart Phase #:4 [PDBOTM8P] Files:1 Time: 2s
************* Catalog Final Scripts ************
Serial Phase #:5 [PDBOTM8P] Files:7 Time: 21s
***************** Catproc Start ****************
Serial Phase #:6 [PDBOTM8P] Files:1 Time: 19s
***************** Catproc Types ****************
Serial Phase #:7 [PDBOTM8P] Files:2 Time: 20s
Restart Phase #:8 [PDBOTM8P] Files:1 Time: 2s
**************** Catproc Tables ****************
Parallel Phase #:9 [PDBOTM8P] Files:67 Time: 56s
Restart Phase #:10 [PDBOTM8P] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [PDBOTM8P] Files:1 Time: 85s
Restart Phase #:12 [PDBOTM8P] Files:1 Time: 2s
************** Catproc Procedures **************
Parallel Phase #:13 [PDBOTM8P] Files:94 Time: 20s
Restart Phase #:14 [PDBOTM8P] Files:1 Time: 1s
Parallel Phase #:15 [PDBOTM8P] Files:120 Time: 26s
Restart Phase #:16 [PDBOTM8P] Files:1 Time: 1s
Serial Phase #:17 [PDBOTM8P] Files:22 Time: 11s
Restart Phase #:18 [PDBOTM8P] Files:1 Time: 1s
***************** Catproc Views ****************
Parallel Phase #:19 [PDBOTM8P] Files:32 Time: 32s
Restart Phase #:20 [PDBOTM8P] Files:1 Time: 0s
Serial Phase #:21 [PDBOTM8P] Files:3 Time: 16s
Restart Phase #:22 [PDBOTM8P] Files:1 Time: 1s
Parallel Phase #:23 [PDBOTM8P] Files:25 Time: 227s
Restart Phase #:24 [PDBOTM8P] Files:1 Time: 0s
Parallel Phase #:25 [PDBOTM8P] Files:12 Time: 126s
Restart Phase #:26 [PDBOTM8P] Files:1 Time: 2s
Serial Phase #:27 [PDBOTM8P] Files:1 Time: 0s
Serial Phase #:28 [PDBOTM8P] Files:3 Time: 9s
Serial Phase #:29 [PDBOTM8P] Files:1 Time: 0s
Restart Phase #:30 [PDBOTM8P] Files:1 Time: 1s
*************** Catproc CDB Views **************
Serial Phase #:31 [PDBOTM8P] Files:1 Time: 6s
Restart Phase #:32 [PDBOTM8P] Files:1 Time: 1s
Serial Phase #:34 [PDBOTM8P] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [PDBOTM8P] Files:293 Time: 22s
Serial Phase #:36 [PDBOTM8P] Files:1 Time: 0s
Restart Phase #:37 [PDBOTM8P] Files:1 Time: 0s
Serial Phase #:38 [PDBOTM8P] Files:6 Time: 8s
Restart Phase #:39 [PDBOTM8P] Files:1 Time: 2s
*************** Catproc DataPump ***************
Serial Phase #:40 [PDBOTM8P] Files:3 Time: 54s
Restart Phase #:41 [PDBOTM8P] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [PDBOTM8P] Files:13 Time: 142s
Restart Phase #:43 [PDBOTM8P] Files:1 Time: 1s
Parallel Phase #:44 [PDBOTM8P] Files:11 Time: 15s
Restart Phase #:45 [PDBOTM8P] Files:1 Time: 1s
Parallel Phase #:46 [PDBOTM8P] Files:3 Time: 9s
Restart Phase #:47 [PDBOTM8P] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [PDBOTM8P] Files:1 Time: 19s
Restart Phase #:49 [PDBOTM8P] Files:1 Time: 3s
************** Final RDBMS scripts *************
Serial Phase #:50 [PDBOTM8P] Files:1 Time: 10s
************ Upgrade Component Start ***********
Serial Phase #:51 [PDBOTM8P] Files:1 Time: 7s
Restart Phase #:52 [PDBOTM8P] Files:1 Time: 2s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [PDBOTM8P] Files:2 Time: 391s
***************** Upgrading XDB ****************
Restart Phase #:54 [PDBOTM8P] Files:1 Time: 2s
Serial Phase #:56 [PDBOTM8P] Files:3 Time: 15s
Serial Phase #:57 [PDBOTM8P] Files:3 Time: 10s
Parallel Phase #:58 [PDBOTM8P] Files:10 Time: 12s
Parallel Phase #:59 [PDBOTM8P] Files:25 Time: 13s
Serial Phase #:60 [PDBOTM8P] Files:4 Time: 16s
Serial Phase #:61 [PDBOTM8P] Files:1 Time: 0s
Serial Phase #:62 [PDBOTM8P] Files:32 Time: 10s
Serial Phase #:63 [PDBOTM8P] Files:1 Time: 0s
Parallel Phase #:64 [PDBOTM8P] Files:6 Time: 13s
Serial Phase #:65 [PDBOTM8P] Files:2 Time: 23s
Serial Phase #:66 [PDBOTM8P] Files:3 Time: 35s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [PDBOTM8P] Files:1 Time: 0s
Serial Phase #:69 [PDBOTM8P] Files:1 Time: 9s
Parallel Phase #:70 [PDBOTM8P] Files:2 Time: 44s
Restart Phase #:71 [PDBOTM8P] Files:1 Time: 1s
Parallel Phase #:72 [PDBOTM8P] Files:2 Time: 11s
Serial Phase #:73 [PDBOTM8P] Files:2 Time: 10s
***************** Upgrading SDO ****************
Restart Phase #:74 [PDBOTM8P] Files:1 Time: 1s
Serial Phase #:76 [PDBOTM8P] Files:1 Time: 64s
Serial Phase #:77 [PDBOTM8P] Files:2 Time: 9s
Restart Phase #:78 [PDBOTM8P] Files:1 Time: 1s
Serial Phase #:79 [PDBOTM8P] Files:1 Time: 41s
Restart Phase #:80 [PDBOTM8P] Files:1 Time: 2s
Parallel Phase #:81 [PDBOTM8P] Files:3 Time: 69s
Restart Phase #:82 [PDBOTM8P] Files:1 Time: 2s
Serial Phase #:83 [PDBOTM8P] Files:1 Time: 13s
Restart Phase #:84 [PDBOTM8P] Files:1 Time: 0s
Serial Phase #:85 [PDBOTM8P] Files:1 Time: 16s
Restart Phase #:86 [PDBOTM8P] Files:1 Time: 0s
Parallel Phase #:87 [PDBOTM8P] Files:4 Time: 136s
Restart Phase #:88 [PDBOTM8P] Files:1 Time: 0s
Serial Phase #:89 [PDBOTM8P] Files:1 Time: 9s
Restart Phase #:90 [PDBOTM8P] Files:1 Time: 0s
Serial Phase #:91 [PDBOTM8P] Files:2 Time: 28s
Restart Phase #:92 [PDBOTM8P] Files:1 Time: 3s
Serial Phase #:93 [PDBOTM8P] Files:1 Time: 6s
Restart Phase #:94 [PDBOTM8P] Files:1 Time: 1s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [PDBOTM8P] Files:1 Time: 22s
Restart Phase #:96 [PDBOTM8P] Files:1 Time: 1s
*********** Final Component scripts ***********
Serial Phase #:97 [PDBOTM8P] Files:1 Time: 8s
************* Final Upgrade scripts ************
Serial Phase #:98 [PDBOTM8P] Files:1 Time: 188s
******************* Migration ******************
Serial Phase #:99 [PDBOTM8P] Files:1 Time: 6s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [PDBOTM8P] Files:1 Time: 7s
Serial Phase #:101 [PDBOTM8P] Files:1 Time: 8s
Serial Phase #:102 [PDBOTM8P] Files:1 Time: 10s
***************** Post Upgrade *****************
Serial Phase #:103 [PDBOTM8P] Files:1 Time: 27s
**************** Summary report ****************
Serial Phase #:104 [PDBOTM8P] Files:1 Time: 8s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [PDBOTM8P] Files:1 Time: 6s
Serial Phase #:106 [PDBOTM8P] Files:1 Time: 4s
Serial Phase #:107 [PDBOTM8P] Files:1 Time: 0s
------------------------------------------------------
Phases [0-107] End Time:[2020_07_11 15:20:52]
Container Lists Inclusion:[PDBOTM8P] Exclusion:[NONE]
------------------------------------------------------
Grand Total Time: 2409s [PDBOTM8P]
LOG FILES: (/oracle/app/orawork/upg_logs/catupgrdpdbotm8p*.log)
Upgrade Summary Report Located in:
/oracle/app/orawork/upg_logs/upg_summary.log
Time: 2483s For PDB(s)
Grand Total Time: 2483s
LOG FILES: (/oracle/app/orawork/upg_logs/catupgrd*.log)
Grand Total Upgrade Time: [0d:0h:41m:23s]
Step 8 : Login to database and check for invalid object and recompile :
PDB will be in restricted mode Dont panic
[oracle@dm01db12c db_1]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 11 15:25:21 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,v$instance;
SQL> SQL> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- -----------------------------
CDBOTM8P dm01db12c.database.com PRIMARY READ WRITE ARCHIVELOG 19.0.0.0.0 ALLOWED 11-JUL-2020 14:18:33
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBOTM8P MOUNTED
SQL>
SQL> alter pluggable database PDBOTM8P open;
Warning: PDB altered with errors.
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBOTM8P READ WRITE YES
SQL>
SQL> alter session set container=PDBOTM8P;
Session altered.
SQL> select message from pdb_plug_in_violations where type like '%ERR%' and status <> 'RESOLVED';
no rows selected
SQL>
SQL>
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
6043
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@dm01db12c db_1]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -c 'PDBOTM8P' -e -b utlrp -d $ORACLE_HOME/rdbms/admin utlrp.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/oracle/app/orawork/product/19.0.0.0/db_1/utlrp_catcon_7164.lst]
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/product/19.0.0.0/db_1/utlrp*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/product/19.0.0.0/db_1/utlrp_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@dm01db12c db_1]$
[oracle@dm01db12c db_1]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 11 15:38:16 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBOTM8P READ WRITE YES
SQL>
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
STEP 9 : Now, as we expected to have is to execute the script noncdb_to_pdb.sql
which will clean up the new PDB from things that should not be presented there.
ALTER SESSION SET CONTAINER=PDBOTM8P;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
After the script is completed.
Shut database and restart it again.
now the database will be out of restricted mode.
[oracle@dm01db12c db_1]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 11 16:17:49 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBOTM8P MOUNTED
SQL>
SQL>
SQL> alter pluggable database PDBOTM8P open read write;
Pluggable database altered.
SQL>
SQL>
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBOTM8P READ WRITE NO
SQL> alter session set container=PDBOTM8P;
Session altered.
SQL>--We can check the table is there after migration to CDB what we created before the activity.
SQL> select count(*) from bigone.bigtab;
COUNT(*)
----------
1000000
STEP 10 : Check the timezone of PDB and upgrade the timezone :
SQL> col VALUE$ for a15
SQL> select value$, con_id from containers(SYS.PROPS$) where NAME='DST_PRIMARY_TT_VERSION' order by 2;
VALUE$ CON_ID
--------------- ----------
26 3
[oracle@dm01db12c db_1]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -c 'PDBOTM8P' -l /oracle/app/orawork/upg_logs -b utltz_upg_check -d $ORACLE_HOME/rdbms/admin utltz_upg_check.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/oracle/app/orawork/upg_logs/utltz_upg_check_catcon_19941.lst]
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/upg_logs/utltz_upg_check*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/upg_logs/utltz_upg_check_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@dm01db12c db_1]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -c 'PDBOTM8P' -l /oracle/app/orawork/upg_logs -b utltz_upg_apply -d $ORACLE_HOME/rdbms/admin utltz_upg_apply.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/oracle/app/orawork/upg_logs/utltz_upg_apply_catcon_21356.lst]
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/upg_logs/utltz_upg_apply*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/upg_logs/utltz_upg_apply_*.lst] files for spool files, if any
catcon.pl: completed successfully
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBOTM8P READ WRITE NO
SQL>
SQL>
SQL> alter session set container=PDBOTM8P;
Session altered.
SQL> col VALUE$ for a15
SQL> select value$, con_id from containers(SYS.PROPS$) where NAME='DST_PRIMARY_TT_VERSION' order by 2;
VALUE$ CON_ID
--------------- ----------
32 3
Non CDB 12.2 to 19c CDB migration completes here
Hope This Help's
See You Next Post
Regards
Sultan Khan
7 comments
Click here for commentsGreat article sir
ReplyGood post easy to understand steps
Replygood article
Replygood article
ReplyGood post
ReplyNice post 👍
ReplyGood, explained in detail
ReplyConversionConversion EmoticonEmoticon