MIGRATE DATABASE FROM NON-CDB TO CDB (12.2 TO 19C)

 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

Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

Register Archive log file manually in standby Database

How to check UNDO tablespace usage and who is using more undo