In this post we will see 12.2.0.1 Physical Standby Database creation using RMAN

Primary Server Details :

Hostname : Primary

IP Address : 172.168.190.15

DB Unique Name : INFRADBP_PRIME

Standby Server Details :

Hostname : Standby

IP Address : 172.168.190.20

DB Unique Name : INFRADBP_STBY

 

Please click on the INDEX and browse for more interesting posts.

To check on how to create a Dataguard Environment, please click on the below link :

Dataguard Setup

Assumptions Made :

  • The OS is compatible with Oracle 19c
  • The RPM’s are updated to Oracle 19c recommendations
  • The Oracle 19c Binaries are installed in both Primary and Standby Servers

 

Steps

1. Take RMAN and Export backup in Primary

2. Gather dictionary statistics in Primary

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

 

3. Purge Recycle Bin in Primary

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

 

4. Run Pre-upgrade utility in Primary

[oracle@primary backup]$ /u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19c/rdbms/admin/preupgrade.jar TERMINAL TEXT
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2021-03-30T16:40:25

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  INFRADBP
     Container Name:  infradbp
       Container ID:  0
            Version:  12.2.0.1.0
     DB Patch Level:  No Patch Bundle applied
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  26
  Database log mode:  ARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Real Application Clusters              [to be upgraded]  OPTION OFF
  Oracle Workspace Manager               [to be upgraded]  VALID
  OLAP Analytic Workspace                [to be upgraded]  VALID
  Oracle Label Security                  [to be upgraded]  VALID
  Oracle Database Vault                  [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID
  Oracle Multimedia                      [to be upgraded]  VALID
  Oracle Spatial                         [to be upgraded]  VALID
  Oracle OLAP API                        [to be upgraded]  VALID

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  1.  (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.

      None of the fixed object tables have had stats collected.

      Gathering statistics on fixed objects, if none have been gathered yet, is
      recommended prior to upgrading.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  INFORMATION ONLY
  ================
  2.  To help you keep track of your tablespace allocations, the following
      AUTOEXTEND tablespaces are expected to successfully EXTEND during the
      upgrade process.

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                             470 MB       500 MB
      SYSTEM                             800 MB       914 MB
      TEMP                                32 MB       150 MB
      UNDOTBS1                            70 MB       439 MB

      Minimum tablespace sizes for upgrade are estimates.

  3.  Check the Oracle Backup and Recovery User's Guide for information on how
      to manage an RMAN recovery catalog schema.

      If you are using a version of the recovery catalog schema that is older
      than that required by the RMAN client version, then you must upgrade the
      catalog schema.

      It is good practice to have the catalog schema the same or higher version
      than the RMAN client version you are using.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database INFRADBP
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/u01/app/oracle/cfgtoollogs/INFRADBP_PRIME/preupgrade/preupgrade_fixups
    .sql

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  4.  Upgrade the database time zone file using the DBMS_DST package.

      The database is using time zone file version 26 and the target 19 release
      ships with time zone file version 32.

      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 19 Oracle Database
      Globalization Support Guide.

  5.  To identify directory objects with symbolic links in the path name, run
      $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
      Recreate any directory objects listed, using path names that contain no
      symbolic links.

      Some directory object path names may currently contain symbolic links.

      Starting in Release 18c, symbolic links are not allowed in directory
      object path names used with BFILE data types, the UTL_FILE package, or
      external tables.

  6.  (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Oracle recommends gathering dictionary statistics after upgrade.

      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  7.  Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

      This recommendation is given for all preupgrade runs.

      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database INFRADBP
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/u01/app/oracle/cfgtoollogs/INFRADBP_PRIME/preupgrade/postupgrade_fixup
    s.sql


==================
PREUPGRADE SUMMARY
==================
  /u01/app/oracle/cfgtoollogs/INFRADBP_PRIME/preupgrade/preupgrade.log
  /u01/app/oracle/cfgtoollogs/INFRADBP_PRIME/preupgrade/preupgrade_fixups.sql
  /u01/app/oracle/cfgtoollogs/INFRADBP_PRIME/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@/u01/app/oracle/cfgtoollogs/INFRADBP_PRIME/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/INFRADBP_PRIME/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2021-03-30T16:40:26

 

5. As per the Pre-Upgrade log, resize the datafiles in Primary

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/infradbp/users01.dbf
/u01/app/oracle/oradata/infradbp/undotbs01.dbf
/u01/app/oracle/oradata/infradbp/system01.dbf
/u01/app/oracle/oradata/infradbp/sysaux01.dbf

SQL> alter database datafile '/u01/app/oracle/oradata/infradbp/sysaux01.dbf' resize 950M;

Database altered.

SQL> alter database datafile '/u01/app/oracle/oradata/infradbp/system01.dbf' resize 950M;

Database altered.

SQL> alter database datafile '/u01/app/oracle/oradata/infradbp/undotbs01.dbf' resize 450M;

Database altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/infradbp/temp01.dbf

SQL> alter database tempfile '/u01/app/oracle/oradata/infradbp/temp01.dbf' resize 150M;

 

6. Run preupgrade_fixups.sql in Primary

SQL> @/u01/app/oracle/cfgtoollogs/INFRADBP_PRIME/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2021-03-30 16:40:21

For Source Database:     INFRADBP
Source Database Version: 12.2.0.1.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    1.  pre_fixed_objects         YES         None.
    2.  tablespaces_info          YES         None.
    3.  rman_recovery_version     NO          Informational only.
                                              Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade.  To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

 

7. Check for Invalid Objects in Primary

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
         0

 

8. Check whether materialized views are running or not in Primary

Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.

SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

no rows selected

SQL> declare
list_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
end;
/

PL/SQL procedure successfully completed.

 

9. Stop Redo apply in standby

You need to make sure that Standby Database is in SYNC with Primary Database.
Once they are in SYNC, stop MRP process

SQL> alter database recover managed standby database cancel;

Database altered.

10. Stop RFS process in Primary

SQL> alter system set log_archive_dest_state_2=DEFER;

11. Stop listener and database in 12c in both primary and standby

12. Copy listener,tns,sqlnet.ora and password file in 19c TNS_ADMIN in both primary and standby

Change the ORACLE_HOME in the listner.ora file

13. Copy the init file in both primary and standby 19c dbs location

14. Start listener in 19c location in primary

[oracle@primary ~]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-MAR-2021 12:25:45

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19c/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19c/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/primary/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.168.190.15)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.168.190.15)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                29-MAR-2021 12:25:45
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19c/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/primary/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.168.190.15)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "infradbp" has 1 instance(s).
  Instance "infradbp", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

15. Start the 19c database in upgrade mode in primary

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 1795159104 bytes
Fixed Size                  8897600 bytes
Variable Size             503316480 bytes
Database Buffers         1275068416 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

 

16. Run the upgrade in primary

[oracle@primary ~]$ cd /u01/app/oracle/product/19c/bin/
[oracle@primary bin]$ ls -ltr dbupgrade
-rwxr-x---. 1 oracle oinstall 3136 Apr 17  2019 dbupgrade


[oracle@primary bin]$ ./dbupgrade

Argument list for [/u01/app/oracle/product/19c/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in                       c = 0
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 = 0
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]


/u01/app/oracle/product/19c/rdbms/admin/orahome = [/u01/app/oracle/product/19c]
/u01/app/oracle/product/19c/bin/orabasehome = [/u01/app/oracle/product/19c]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19c]

Analyzing file /u01/app/oracle/product/19c/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20210330185412]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20210330185412/catupgrd_catcon_16164.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20210330185412/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20210330185412/catupgrd_*.lst] files for spool files, if any


Number of Cpus        = 2
Database Name         = INFRADBP_PRIME
DataBase Version      = 12.2.0.1.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19c/cfgtoollogs/INFRADBP_PRIME/upgrade20210330185423/catupgrd_catcon_16164.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19c/cfgtoollogs/INFRADBP_PRIME/upgrade20210330185423/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19c/cfgtoollogs/INFRADBP_PRIME/upgrade20210330185423/catupgrd_*.lst] files for spool files, if any


Log file directory = [/u01/app/oracle/product/19c/cfgtoollogs/INFRADBP_PRIME/upgrade20210330185423]

Parallel SQL Process Count            = 4
Components in [INFRADBP_PRIME]
    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:[2021_03_30 18:54:36]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [INFRADBP_PRIME] Files:1    Time: 35s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [INFRADBP_PRIME] Files:5    Time: 39s
Restart  Phase #:2    [INFRADBP_PRIME] Files:1    Time: 4s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [INFRADBP_PRIME] Files:19   Time: 21s
Restart  Phase #:4    [INFRADBP_PRIME] Files:1    Time: 3s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [INFRADBP_PRIME] Files:7    Time: 16s
*****************   Catproc Start   ****************
Serial   Phase #:6    [INFRADBP_PRIME] Files:1    Time: 15s
*****************   Catproc Types   ****************
Serial   Phase #:7    [INFRADBP_PRIME] Files:2    Time: 13s
Restart  Phase #:8    [INFRADBP_PRIME] Files:1    Time: 3s
****************   Catproc Tables   ****************
Parallel Phase #:9    [INFRADBP_PRIME] Files:67   Time: 30s
Restart  Phase #:10   [INFRADBP_PRIME] Files:1    Time: 2s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [INFRADBP_PRIME] Files:1    Time: 72s
Restart  Phase #:12   [INFRADBP_PRIME] Files:1    Time: 2s
**************   Catproc Procedures   **************
Parallel Phase #:13   [INFRADBP_PRIME] Files:94   Time: 12s
Restart  Phase #:14   [INFRADBP_PRIME] Files:1    Time: 4s
Parallel Phase #:15   [INFRADBP_PRIME] Files:120  Time: 18s
Restart  Phase #:16   [INFRADBP_PRIME] Files:1    Time: 4s
Serial   Phase #:17   [INFRADBP_PRIME] Files:22   Time: 5s
Restart  Phase #:18   [INFRADBP_PRIME] Files:1    Time: 1s
*****************   Catproc Views   ****************
Parallel Phase #:19   [INFRADBP_PRIME] Files:32   Time: 24s
Restart  Phase #:20   [INFRADBP_PRIME] Files:1    Time: 4s
Serial   Phase #:21   [INFRADBP_PRIME] Files:3    Time: 14s
Restart  Phase #:22   [INFRADBP_PRIME] Files:1    Time: 3s
Parallel Phase #:23   [INFRADBP_PRIME] Files:25   Time: 200s
Restart  Phase #:24   [INFRADBP_PRIME] Files:1    Time: 4s
Parallel Phase #:25   [INFRADBP_PRIME] Files:12   Time: 137s
Restart  Phase #:26   [INFRADBP_PRIME] Files:1    Time: 2s
Serial   Phase #:27   [INFRADBP_PRIME] Files:1    Time: 0s
Serial   Phase #:28   [INFRADBP_PRIME] Files:3    Time: 5s
Serial   Phase #:29   [INFRADBP_PRIME] Files:1    Time: 0s
Restart  Phase #:30   [INFRADBP_PRIME] Files:1    Time: 2s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [INFRADBP_PRIME] Files:1    Time: 2s
Restart  Phase #:32   [INFRADBP_PRIME] Files:1    Time: 1s
Serial   Phase #:34   [INFRADBP_PRIME] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [INFRADBP_PRIME] Files:293  Time: 28s
Serial   Phase #:36   [INFRADBP_PRIME] Files:1    Time: 0s
Restart  Phase #:37   [INFRADBP_PRIME] Files:1    Time: 3s
Serial   Phase #:38   [INFRADBP_PRIME] Files:6    Time: 8s
Restart  Phase #:39   [INFRADBP_PRIME] Files:1    Time: 3s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [INFRADBP_PRIME] Files:3    Time: 62s
Restart  Phase #:41   [INFRADBP_PRIME] Files:1    Time: 4s
******************   Catproc SQL   *****************
Parallel Phase #:42   [INFRADBP_PRIME] Files:13   Time: 140s
Restart  Phase #:43   [INFRADBP_PRIME] Files:1    Time: 4s
Parallel Phase #:44   [INFRADBP_PRIME] Files:11   Time: 11s
Restart  Phase #:45   [INFRADBP_PRIME] Files:1    Time: 3s
Parallel Phase #:46   [INFRADBP_PRIME] Files:3    Time: 3s
Restart  Phase #:47   [INFRADBP_PRIME] Files:1    Time: 2s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [INFRADBP_PRIME] Files:1    Time: 9s
Restart  Phase #:49   [INFRADBP_PRIME] Files:1    Time: 2s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [INFRADBP_PRIME] Files:1    Time: 6s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [INFRADBP_PRIME] Files:1    Time: 2s
Restart  Phase #:52   [INFRADBP_PRIME] Files:1    Time: 3s
**********   Upgrading Java and non-Java   *********
Serial   Phase #:53   [INFRADBP_PRIME] Files:2    Time: 312s
*****************   Upgrading XDB   ****************
Restart  Phase #:54   [INFRADBP_PRIME] Files:1    Time: 4s
Serial   Phase #:56   [INFRADBP_PRIME] Files:3    Time: 10s
Serial   Phase #:57   [INFRADBP_PRIME] Files:3    Time: 6s
Parallel Phase #:58   [INFRADBP_PRIME] Files:10   Time: 6s
Parallel Phase #:59   [INFRADBP_PRIME] Files:25   Time: 8s
Serial   Phase #:60   [INFRADBP_PRIME] Files:4    Time: 11s
Serial   Phase #:61   [INFRADBP_PRIME] Files:1    Time: 0s
Serial   Phase #:62   [INFRADBP_PRIME] Files:32   Time: 7s
Serial   Phase #:63   [INFRADBP_PRIME] Files:1    Time: 0s
Parallel Phase #:64   [INFRADBP_PRIME] Files:6    Time: 9s
Serial   Phase #:65   [INFRADBP_PRIME] Files:2    Time: 26s
Serial   Phase #:66   [INFRADBP_PRIME] Files:3    Time: 39s
****************   Upgrading ORDIM   ***************
Restart  Phase #:67   [INFRADBP_PRIME] Files:1    Time: 3s
Serial   Phase #:69   [INFRADBP_PRIME] Files:1    Time: 5s
Parallel Phase #:70   [INFRADBP_PRIME] Files:2    Time: 35s
Restart  Phase #:71   [INFRADBP_PRIME] Files:1    Time: 3s
Parallel Phase #:72   [INFRADBP_PRIME] Files:2    Time: 5s
Serial   Phase #:73   [INFRADBP_PRIME] Files:2    Time: 4s
*****************   Upgrading SDO   ****************
Restart  Phase #:74   [INFRADBP_PRIME] Files:1    Time: 4s
Serial   Phase #:76   [INFRADBP_PRIME] Files:1    Time: 49s
Serial   Phase #:77   [INFRADBP_PRIME] Files:2    Time: 6s
Restart  Phase #:78   [INFRADBP_PRIME] Files:1    Time: 3s
Serial   Phase #:79   [INFRADBP_PRIME] Files:1    Time: 57s
Restart  Phase #:80   [INFRADBP_PRIME] Files:1    Time: 3s
Parallel Phase #:81   [INFRADBP_PRIME] Files:3    Time: 59s
Restart  Phase #:82   [INFRADBP_PRIME] Files:1    Time: 3s
Serial   Phase #:83   [INFRADBP_PRIME] Files:1    Time: 9s
Restart  Phase #:84   [INFRADBP_PRIME] Files:1    Time: 4s
Serial   Phase #:85   [INFRADBP_PRIME] Files:1    Time: 13s
Restart  Phase #:86   [INFRADBP_PRIME] Files:1    Time: 4s
Parallel Phase #:87   [INFRADBP_PRIME] Files:4    Time: 101s
Restart  Phase #:88   [INFRADBP_PRIME] Files:1    Time: 3s
Serial   Phase #:89   [INFRADBP_PRIME] Files:1    Time: 5s
Restart  Phase #:90   [INFRADBP_PRIME] Files:1    Time: 4s
Serial   Phase #:91   [INFRADBP_PRIME] Files:2    Time: 14s
Restart  Phase #:92   [INFRADBP_PRIME] Files:1    Time: 4s
Serial   Phase #:93   [INFRADBP_PRIME] Files:1    Time: 2s
Restart  Phase #:94   [INFRADBP_PRIME] Files:1    Time: 3s
*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******
Serial   Phase #:95   [INFRADBP_PRIME] Files:1    Time: 17s
Restart  Phase #:96   [INFRADBP_PRIME] Files:1    Time: 4s
***********   Final Component scripts    ***********
Serial   Phase #:97   [INFRADBP_PRIME] Files:1    Time: 4s
*************   Final Upgrade scripts   ************
Serial   Phase #:98   [INFRADBP_PRIME] Files:1    Time: 261s
*******************   Migration   ******************
Serial   Phase #:99   [INFRADBP_PRIME] Files:1    Time: 2s
***   End PDB Application Upgrade Pre-Shutdown   ***
Serial   Phase #:100  [INFRADBP_PRIME] Files:1    Time: 3s
Serial   Phase #:101  [INFRADBP_PRIME] Files:1    Time: 0s
Serial   Phase #:102  [INFRADBP_PRIME] Files:1    Time: 50s
*****************   Post Upgrade   *****************
Serial   Phase #:103  [INFRADBP_PRIME] Files:1    Time: 29s
****************   Summary report   ****************
Serial   Phase #:104  [INFRADBP_PRIME] Files:1    Time: 4s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:105  [INFRADBP_PRIME] Files:1    Time: 2s
Serial   Phase #:106  [INFRADBP_PRIME] Files:1    Time: 0s
Serial   Phase #:107  [INFRADBP_PRIME] Files:1     Time: 27s

------------------------------------------------------
Phases [0-107]         End Time:[2021_03_30 19:31:52]
------------------------------------------------------

Grand Total Time: 2240s

 LOG FILES: (/u01/app/oracle/product/19c/cfgtoollogs/INFRADBP_PRIME/upgrade20210330185423/catupgrd*.log)

Upgrade Summary Report Located in:
/u01/app/oracle/product/19c/cfgtoollogs/INFRADBP_PRIME/upgrade20210330185423/upg_summary.log

Grand Total Upgrade Time:    [0d:0h:37m:20s]

17. Start the database to perform post upgrade actions in Primary

Perform the After Upgrade steps as per the Pre-upgrade utility log

18. Upgrade time zone in Primary

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        26

    
  SHUTDOWN IMMEDIATE;

      STARTUP UPGRADE;

-- Begin upgrade to the latest version.
SET SERVEROUTPUT ON
DECLARE
  l_tz_version PLS_INTEGER;
BEGIN
  l_tz_version := DBMS_DST.get_latest_timezone_version;

  DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
  DBMS_DST.begin_upgrade(l_tz_version);
END;
/

SHUTDOWN IMMEDIATE;
STARTUP;

-- Do the upgrade.
SET SERVEROUTPUT ON
DECLARE
  l_failures   PLS_INTEGER;
BEGIN
  DBMS_DST.upgrade_database(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
  DBMS_DST.end_upgrade(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/

The output of the above commands is given below :
SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        26

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1879045424 bytes
Fixed Size                  8897840 bytes
Variable Size             486539264 bytes
Database Buffers         1375731712 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
SQL> SET SERVEROUTPUT ON
DECLARE
  l_tz_version PLS_INTEGER;
BEGIN
  l_tz_version := DBMS_DST.get_latest_timezone_version;

  DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
  DBMS_DST.begin_upgrade(l_tz_version);
END;
/SQL>   2    3    4    5    6    7    8    9
l_tz_version=32
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1879045424 bytes
Fixed Size                  8897840 bytes
Variable Size             486539264 bytes
Database Buffers         1375731712 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
SQL> SET SERVEROUTPUT ON
DECLARE
  l_failures   PLS_INTEGER;
BEGIN
  DBMS_DST.upgrade_database(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
  DBMS_DST.end_upgrade(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/SQL>   2    3    4    5    6    7    8    9

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0

PL/SQL procedure successfully completed.


-- Check new settings.
SQL> SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat              32          0

SQL> COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20

SELECT property_name, property_value
FROM   database_properties
WHERE  property_name LIKE 'DST_%'
ORDER BY property_name;
SQL> SQL> SQL>   2    3    4
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         32
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE


exit;
EOF

19. Identify directory objects with symbolic links in the path name in Primary

Starting from Release 18c, symbolic links are not allowed in directory object path names used with BFILE data types, the UTL_FILE package, or external tables.

SQL> @$ORACLE_HOME/rdbms/admin/utldirsymlink.sql
No DIRECTORY OBJECTS with symlinks found.

PL/SQL procedure successfully completed.

No errors.

 

20. Gather Fixed objects dictionary statistics in Primary

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

21. Validate DBA Objects and DBA Registry in Primary

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
      2273

SQL> desc dba_registry
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COMP_ID                                   NOT NULL VARCHAR2(30)
 COMP_NAME                                          VARCHAR2(255)
 VERSION                                            VARCHAR2(30)
 VERSION_FULL                                       VARCHAR2(30)
 STATUS                                             VARCHAR2(44)
 MODIFIED                                           VARCHAR2(29)
 NAMESPACE                                 NOT NULL VARCHAR2(30)
 CONTROL                                   NOT NULL VARCHAR2(128)
 SCHEMA                                    NOT NULL VARCHAR2(128)
 PROCEDURE                                          VARCHAR2(128)
 STARTUP                                            VARCHAR2(8)
 PARENT_ID                                          VARCHAR2(30)
 OTHER_SCHEMAS                                      VARCHAR2(4000)

SQL> col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;SQL> SQL> SQL> SQL> SQL>

COMP_ID    COMP_NAME                                VERSION         STATUS
---------- ---------------------------------------- --------------- --------------------------------------------
CATALOG    Oracle Database Catalog Views            19.0.0.0.0      UPGRADED
CATPROC    Oracle Database Packages and Types       19.0.0.0.0      UPGRADED
JAVAVM     JServer JAVA Virtual Machine             19.0.0.0.0      UPGRADED
XML        Oracle XDK                               19.0.0.0.0      UPGRADED
CATJAVA    Oracle Database Java Packages            19.0.0.0.0      UPGRADED
APS        OLAP Analytic Workspace                  19.0.0.0.0      UPGRADED
RAC        Oracle Real Application Clusters         19.0.0.0.0      UPGRADED
XDB        Oracle XML Database                      19.0.0.0.0      UPGRADED
OWM        Oracle Workspace Manager                 19.0.0.0.0      UPGRADED
CONTEXT    Oracle Text                              19.0.0.0.0      UPGRADED
ORDIM      Oracle Multimedia                        19.0.0.0.0      UPGRADED
SDO        Spatial                                  19.0.0.0.0      UPGRADED
XOQ        Oracle OLAP API                          19.0.0.0.0      UPGRADED
OLS        Oracle Label Security                    19.0.0.0.0      UPGRADED
DV         Oracle Database Vault                    19.0.0.0.0      UPGRADED

15 rows selected.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@primary bin]$ pwd
/u01/app/oracle/product/19c/bin
[oracle@primary bin]$ cd ../rdbms/admin/
[oracle@primary admin]$ sqlplus '/as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 30 19:44:18 2021
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> @utlrp.sql
Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2021-03-30 19:44:22

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2021-03-30 19:49:42

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
         0

SQL> col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
SQL> SQL> SQL> SQL> SQL>
SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

COMP_ID    COMP_NAME                                VERSION         STATUS
---------- ---------------------------------------- --------------- --------------------------------------------
CATALOG    Oracle Database Catalog Views            19.0.0.0.0      VALID
CATPROC    Oracle Database Packages and Types       19.0.0.0.0      VALID
JAVAVM     JServer JAVA Virtual Machine             19.0.0.0.0      VALID
XML        Oracle XDK                               19.0.0.0.0      VALID
CATJAVA    Oracle Database Java Packages            19.0.0.0.0      VALID
APS        OLAP Analytic Workspace                  19.0.0.0.0      VALID
RAC        Oracle Real Application Clusters         19.0.0.0.0      OPTION OFF
XDB        Oracle XML Database                      19.0.0.0.0      VALID
OWM        Oracle Workspace Manager                 19.0.0.0.0      VALID
CONTEXT    Oracle Text                              19.0.0.0.0      VALID
ORDIM      Oracle Multimedia                        19.0.0.0.0      VALID
SDO        Spatial                                  19.0.0.0.0      VALID
XOQ        Oracle OLAP API                          19.0.0.0.0      VALID
OLS        Oracle Label Security                    19.0.0.0.0      VALID
DV         Oracle Database Vault                    19.0.0.0.0      VALID

22. Run Post Upgrade Fixups in Primary

SQL> @/u01/app/oracle/cfgtoollogs/INFRADBP_PRIME/preupgrade/postupgrade_fixups.sql

Session altered.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Package created.

No errors.

Package body created.


PL/SQL procedure successfully completed.

No errors.





Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2021-03-30 16:40:25

For Source Database:     INFRADBP
Source Database Version: 12.2.0.1.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    4.  old_time_zones_exist      YES         None.
    5.  dir_symlinks              YES         None.
    6.  post_dictionary           YES         None.
    7.  post_fixed_objects        NO          Informational only.
                                              Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete.  To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.


Session altered.

 

23. Start the standby database from 19c location in mount mode

SQL> Startup mount

24. Enable RFS Process in Primary

SQL> alter system set log_archive_dest_state_2=ENABLE;

25. Start the Standby Listener from 19c location

26. Enable Redo Apply in Standby

SQL> alter database recover managed standby database disconnect from session;

Database altered.

27. Once the databases are in sync, stop the standby database, and create spfile from pfile in standby

28. Start the standby database and check the SYNC

29. Verify the version of the standby database

SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0