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

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the ...
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify th...
Read more

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

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12      ...
Read more