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 :
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