Rolling Database Upgrade from 12.1.0.2 to 19.8 Using Transient Logical Standby

Step by step instruction on how to Upgrade your database with minimal downtime using Transient Logical Standby.

Useful Links:

Approaches:

There are several methods which can be used:

  • Manually from command line
  • Manually using phsyru.sh script which is available since 11g (Doc ID 949322.1)
  • Use DBMS_ROLLING package which is available since 12.1.0.1 (Doc ID 2086512.1)

Note: for both manual methods Data Guard Broker needs to be turned off. For DBMS_ROLLING approach you can leave it running and drive the rolling upgrade process semi-automatic.

In this post I will use manual command line approach without Data Broker setup.

Licensing:

You can do rolling upgrades without an extra license for Transient Logical. But as soon as you use the DBMS_ROLLING package you’ll use functionality of Active Data Guard, and therefore must license it. Manual Transient Logical Standby or the use of physru.sh should not require an ADG license.

For clarification please contact the Oracle license specialists.

In my case I will use manual approach, but standby databases will be in OPEN READ ONLY state – so in this case I should have ADG be licenced anyway.

Cross platform:

It can go cross platform in some rare cases, but never across Endianness. This means, you can have such a process between Windows and Linux but never between AIX and Linux (Doc ID 1085687.1).

Test Lab Setup:

I’ve prepared environment below to demonstrate proof of concept:

INIT_FINAL

Here are some database parameters for each instance:

PROD:

*.service_names='prod','soe'
*.archive_lag_target=900
*.log_archive_config='dg_config=(PROD,PRODS,PRODUPG,PRODUPGS)'
*.log_archive_dest_1='LOCATION=/data/arclogs/PROD'
*.log_archive_dest_2='SERVICE=PRODS LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODS'
*.log_archive_dest_3='SERVICE=PRODUPG LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODUPG'
*.log_archive_dest_state_2=ENABLE
*.log_archive_dest_state_3=ENABLE
*.log_archive_format='prod_%t_%s_%r.arc'
*.standby_file_management=AUTO
*.fal_server=PRODS,PRODUPG;
*.fal_client=PROD;

PRODS:

*.service_names='prod','soe'
*.archive_lag_target=900
*.log_archive_config='dg_config=(PROD,PRODS,PRODUPG,PRODUPGS)'
*.log_archive_dest_1='LOCATION=/data/arclogs/PRODS'
*.log_archive_dest_2='SERVICE=PROD LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD'
*.log_archive_dest_state_2='DEFER'
*.log_archive_format='prods_%t_%s_%r.arc'
*.standby_file_management=AUTO
*.fal_server=PROD
*.fal_client=PRODS
*.db_file_name_convert = ('/data/PROD/','/data/PRODS/')
*.log_file_name_convert = ('/data/PROD/','/data/PRODS/')

PRODUPG:

*.service_names='prod','soe'
*.archive_lag_target=900
*.log_archive_config='dg_config=(PROD,PRODS,PRODUPG,PRODUPGS)'
*.log_archive_dest_1='LOCATION=/data/arclogs/PRODUPG'
*.log_archive_dest_2='SERVICE=PROD LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD'
*.log_archive_dest_3='SERVICE=PRODUPGS LGWR ASYNC NOAFFIRM VALID_FOR=(ALL_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=PRODUPGS'
*.log_archive_dest_state_2='DEFER'
*.log_archive_dest_state_3='DEFER'
*.log_archive_format='produpg_%t_%s_%r.arc'
*.standby_file_management=AUTO
*.fal_server=PROD,PRODUPGS
*.fal_client=PRODUPG
*.db_file_name_convert = ('/data/PROD/','/data/PRODUPG/')
*.log_file_name_convert = ('/data/PROD/','/data/PRODUPG/')

PRODUPGS:

*.service_names='prod','soe'
*.archive_lag_target=900
*.log_archive_config='dg_config=(PROD,PRODS,PRODUPG,PRODUPGS)'
*.log_archive_dest_1='LOCATION=/data/arclogs/PRODUPGS'
*.log_archive_dest_2='SERVICE=PRODUPG LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODUPG'
*.log_archive_dest_state_2=DEFER
*.log_archive_format='produpgs_%t_%s_%r.arc'
*.standby_file_management=AUTO
*.fal_server=PRODUPG
*.fal_client=PRODUPGS
*.db_file_name_convert = ('/data/PROD/','/data/PRODUPGS/','/data/PRODUPG/','/data/PRODUPGS/')
*.log_file_name_convert = ('/data/PROD/','/data/PRODUPGS/','/data/PRODUPG/','/data/PRODUPGS/')

Note: I’ve also setup “application” server (srv-apps-soe.test.com) with installed BIND DNS server and SwingBench Load generator tool which I will use to simulate database activity during whole experiment.

I. Prerequisite:

0. [OPTIONAL] Start SwingBench Tool

As I said before I will use that tool for workload simulation:

>>> root

$ which sbst
++++++++++
alias sbst='/root/swingbench/bin/charbench -c /root/swingbench/configs/Stress_Test.xml -dt thin'
/root/swingbench/bin/charbench
++++++++++

$ cat /root/swingbench/configs/Stress_Test.xml | head -10
++++++++++
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<SwingBenchConfiguration xmlns="http://www.dominicgiles.com/swingbench/config">
<Name>Stress Test</Name>
<Comment>Simple relational CRUD workload test</Comment>
<Connection>
<UserName>soe</UserName>
<Password>*********</Password>
<ConnectString>//prod-soe-db.test.com:1521/soe</ConnectString>
<DriverType>Oracle10g Type IV jdbc driver (thin)</DriverType>
</Connection>
++++++++++

$ sbst
++++++++++
Author : Dominic Giles
Version : 2.6.0.1137

Results will be written to results.xml.
Hit Return to Terminate Run...

Time Users TPM TPS
11:05:20 AM    10 5818 1033
++++++++++

1. Check you database for unsupported data types

Before setting up a logical standby database, ensure the logical standby database can maintain the data types and tables in your primary database:

SYS@PROD 12c > SELECT COUNT(*) FROM DBA_LOGSTDBY_UNSUPPORTED;

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

Ensure Table Rows in the Primary Database Can Be Uniquely Identified. Find Tables Without Unique Logical Identifier in the Primary Database:

SYS@PROD 12c > SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE 
WHERE (OWNER, TABLE_NAME) NOT IN (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED) 
AND BAD_COLUMN = 'Y';

no rows selected

2. Check current incarnation of each database

SYS@PROD/PRODS/PRODUPG/PRODUPGS 12c > SELECT INCARNATION#, RESETLOGS_CHANGE#, STATUS 
FROM V$DATABASE_INCARNATION;

INCARNATION# RESETLOGS_CHANGE# STATUS
------------ ----------------- -------
           1                 1 CURRENT

Note: this is to avoid Bug described here 554358.1, which looks fixed in 11.1, but anyway it’s better to set same incarnation for all databases on your environment if you have such opportunity.

3. Check Online/Standby Redo Logs configuration

SYS@PRODUPG 12c > @redo_status_size_info

##
## Redo logs:
##

    GROUP# TYPE    MEMBER                                                                      STATUS        SIZE_MB
---------- ------- --------------------------------------------------------------------------- ---------- ----------
         1 ONLINE  /data/PRODUPG/redo01.log                                                    UNUSED           1024
         2 ONLINE  /data/PRODUPG/redo02.log                                                    UNUSED           1024
         3 ONLINE  /data/PRODUPG/redo03.log                                                    UNUSED           1024
                                                                                                          ----------
Total                                                                                                           3072

##
## Standby logs:
##

    GROUP# TYPE    MEMBER                                                                      STATUS        SIZE_MB
---------- ------- --------------------------------------------------------------------------- ---------- ----------
         4 STANDBY /data/PRODUPG/redo01_STBY.log                                               ACTIVE           1024
         5 STANDBY /data/PRODUPG/redo02_STBY.log                                               UNASSIGNED       1024
         6 STANDBY /data/PRODUPG/redo03_STBY.log                                               UNASSIGNED       1024
         7 STANDBY /data/PRODUPG/redo04_STBY.log                                               UNASSIGNED       1024
                                                                                                          ----------
Total                                                                                                           4096

Note: in a Data Guard environment size of SRls should be exactly the same as the size of ORls across the primary and standby database. Count of SRL should equal to Count of ORLs + 1. Otherwise you can face with issue described here 2481907.1

4. Enable Supplimental Logging on Primary Database

ATTENTION: this needs to be performed during off hours, because enabling supplemental logging can cause your database to hang and affect performance if its under heavy load. Reason is that Oracle will invalidate all DML cursors in the cursor cache, which will have an effect on performance until the cache is repopulated. Besides, we also need to wait for the completion of all the in-flight transaction so that any redo generated after this DDL would have the right supplemental logging attributes. (Alter Database Add Supplemental Log Data Hangs (Doc ID 406498.1)).

SYS@PROD 12c  > COL "MINIMUM" FOR A20
COL "PRIMARY KEY" FOR A20
COL "UNIQUE KEY" FOR A20
COL "FOREGIN KEY" FOR A20
COL "ALL" FOR A20

SELECT SUPPLEMENTAL_LOG_DATA_MIN "MINIMUM",
SUPPLEMENTAL_LOG_DATA_PK "PRIMARY KEY",
SUPPLEMENTAL_LOG_DATA_UI "UNIQUE KEY",
SUPPLEMENTAL_LOG_DATA_FK "FOREGIN KEY",
SUPPLEMENTAL_LOG_DATA_ALL "ALL"
FROM V$DATABASE;

MINIMUM              PRIMARY KEY          UNIQUE KEY           FOREGIN KEY          ALL
-------------------- -------------------- -------------------- -------------------- --------------------
NO                   NO                   NO                   NO                   NO

SYS@PROD 12c  > ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

SYS@PROD 12c  > SELECT SUPPLEMENTAL_LOG_DATA_MIN "MINIMUM",
SUPPLEMENTAL_LOG_DATA_PK "PRIMARY KEY",
SUPPLEMENTAL_LOG_DATA_UI "UNIQUE KEY",
SUPPLEMENTAL_LOG_DATA_FK "FOREGIN KEY",
SUPPLEMENTAL_LOG_DATA_ALL "ALL"
FROM V$DATABASE;

MINIMUM              PRIMARY KEY          UNIQUE KEY           FOREGIN KEY          ALL
-------------------- -------------------- -------------------- -------------------- --------------------
IMPLICIT             YES                  YES                  NO                   NO

Note: in my case I can skip it, because it will be enabled as part of building the LogMiner dictionary. Supplemental logging is automatically set up to log primary key and unique-constraint/index columns. But anyway I’ve desided to enable it manually on primary database before the maintenance.

Note: for databases created using Oracle Database 11g Release 2 (11.2) or later, supplemental logging information is automatically propagated to any existing physical standby databases. However, for databases in earlier releases, or if the database was created using an earlier release and then upgraded to 11.2, you must check whether supplemental logging is enabled at the physical standby(s) if it is also enabled at the primary database. If it is not enabled at the physical standby(s), then before performing a switchover or failover, you must enable supplemental logging on all existing physical standby databases.

5. Prepare (install/patch)  Oracle Home 19c on each server

>>> oracle@srv-dc1-db1 / srv-dc2-db2 / srv-dc1-db3 / srv-dc2-db4

// Install New Home
$ mkdir -p /u01/app/oracle/product/198000/dbhome_1 $ unzip /u01/soft/LINUX.X64_193000_db_home.zip -d /u01/app/oracle/product/198000/dbhome_1 $ /u01/app/oracle/product/198000/dbhome_1/runInstaller -ignorePrereqFailure -waitforcompletion -silent \ -responseFile /u01/app/oracle/product/198000/dbhome_1/install/response/db_install.rsp \ oracle.install.option=INSTALL_DB_SWONLY \ ORACLE_HOSTNAME=$(hostname) \ UNIX_GROUP_NAME=oinstall \ INVENTORY_LOCATION=/u01/app/oraInventory \ SELECTED_LANGUAGES=en,en_GB \ ORACLE_HOME=/u01/app/oracle/product/198000/dbhome_1 \ ORACLE_BASE=/u01/app/oracle \ oracle.install.db.InstallEdition=EE \ oracle.install.db.OSDBA_GROUP=dba \ oracle.install.db.OSBACKUPDBA_GROUP=dba \ oracle.install.db.OSDGDBA_GROUP=dba \ oracle.install.db.OSKMDBA_GROUP=dba \ oracle.install.db.OSRACDBA_GROUP=dba \ SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \ DECLINE_SECURITY_UPDATES=true ++++++++++ As a root user, execute the following script(s): 1. /u01/app/oracle/product/198000/dbhome_1/root.sh Execute /u01/app/oracle/product/198000/dbhome_1/root.sh on the following nodes: [srv-dc2-db4] Successfully Setup Software. ++++++++++ >>> root /u01/app/oracle/product/198000/dbhome_1/root.sh >>> oracle $ grep -i "home name" /u01/app/oraInventory/ContentsXML/inventory.xml ++++++++++ <HOME NAME="OraDB12Home1" LOC="/u01/app/oracle/product/12102/dbhome_1" TYPE="O" IDX="1"/> <HOME NAME="OraDB19Home1" LOC="/u01/app/oracle/product/198000/dbhome_1" TYPE="O" IDX="2"/> ++++++++++ $ mv /u01/app/oracle/product/198000/dbhome_1/OPatch /u01/app/oracle/product/198000/dbhome_1/OPatch_default $ unzip /u01/soft/p6880880_190000_Linux-x86-64.zip -d /u01/app/oracle/product/198000/dbhome_1 $ ls -ld /u01/app/oracle/product/198000/dbhome_1/OPatch* ++++++++++ drwxr-x---. 14 oracle oinstall 4096 Apr 22 2020 /u01/app/oracle/product/198000/dbhome_1/OPatch drwxr-x---. 14 oracle oinstall 4096 Nov 14 13:01 /u01/app/oracle/product/198000/dbhome_1/OPatch_default ++++++++++ // Correct bash_profile or any other environment files on your server(s) $ cat ~/.bash_profile ++++++++++ # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs oh12c() { export ORACLE_SID=PRODUPGS export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/12102/dbhome_1 common } oh19c() { export ORACLE_SID=PRODUPGS export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/198000/dbhome_1 common } common() { # Common Vars export ORACLE_HOSTNAME=$(hostname) export TMP=/tmp export TMPDIR=$TMP export PATH=/usr/sbin:$PATH export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/sbin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib export PS1='\[\033[0;32m\]$ORACLE_SID> \[\033[0;33m\]\u@\h\[\033[00m\] [\t] \w]\$ ' # DBA Tools alias rman="rlwrap ${ORACLE_HOME}/bin/rman" alias sp="rlwrap ${ORACLE_HOME}/bin/sqlplus / as sysdba" alias cdob="cd $ORACLE_BASE" alias cdoh="cd $ORACLE_HOME" alias cdtns="cd $ORACLE_HOME/network/admin" alias cddbs="cd $ORACLE_HOME/dbs" } ++++++++++ $ oh19c $ opatch version ++++++++++ OPatch Version: 12.2.0.1.21 OPatch succeeded. ++++++++++ // Apply patch 31281355 - Database Release Update 19.8.0.0.200714 $ cd /u01/soft && unzip p31281355_190000_Linux-x86-64.zip && cd 31281355 $ opatch prereq CheckConflictAgainstOHWithDetail -ph ./ $ opatch apply // Apply patch 31219897 - Oracle JavaVM Component Release Update 19.8.0.0.200714 $ cd /u01/soft && unzip p31219897_190000_Linux-x86-64.zip && cd 31219897 $ opatch prereq CheckConflictAgainstOHWithDetail -ph ./ $ opatch apply // Check installed patches $ opatch lspatches ++++++++++ 31219897;OJVM RELEASE UPDATE: 19.8.0.0.200714 (31219897) 31281355;Database Release Update : 19.8.0.0.200714 (31281355) 29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399) OPatch succeeded. ++++++++++ // Copy config from 12c to 19c Home $ cp /u01/app/oracle/product/12102/dbhome_1/dbs/spfile*.ora /u01/app/oracle/product/198000/dbhome_1/dbs/ $ cp /u01/app/oracle/product/12102/dbhome_1/dbs/orapw* /u01/app/oracle/product/198000/dbhome_1/dbs/ $ cp /u01/app/oracle/product/12102/dbhome_1/network/admin/*.ora /u01/app/oracle/product/198000/dbhome_1/network/admin/

Note: correct sqlnet.ora / listener.ora / tnsnames.ora if required.

6. Comment crontab jobs on each server

Check database server crontab and disable any jobs(backups, cleanup) related to database. This can cause some unexpected events during maintenance so it will be better to temporary disable all jobs and enable at the end of maintenance.

7. Check free space for Archive/Flashback Log files

You have to ensure that you have enouch free space for Archive Log files on your servers. This can become a problem because archive logs needs to be saved during whole period of future primary database upgrade process for future synchronization. So in my case I need to check if there is enough space on srv-dc1-db1 and srv-dc1-db3 servers.

Here are some queries which can help to you to estimate size:

-- Daily Archive Logs Generation --

SQL> SELECT TRUNC(COMPLETION_TIME,'DD') DAY, THREAD#,
ROUND(SUM(BLOCKS*BLOCK_SIZE)/1024/1024) MB,
COUNT(*) ARCHIVES_GENERATED FROM V$ARCHIVED_LOG
GROUP BY TRUNC(COMPLETION_TIME,'DD'),THREAD# ORDER BY 1;

-- Hourly Archive Log Generation --

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SQL> SELECT TRUNC(COMPLETION_TIME,'HH') HOUR,THREAD# ,
ROUND(SUM(BLOCKS*BLOCK_SIZE)/1024/1024) MB,
COUNT(*) ARCHIVES FROM V$ARCHIVED_LOG
GROUP BY TRUNC(COMPLETION_TIME,'HH'),THREAD# ORDER BY 1;

Note: Another way is to use script from Oracle Doc: Script To Find Redolog Switch History And Find Archivelog Size For Each Instances In RAC (Doc ID 2373477.1).

8. Check current patch level

SYS@PROD 12c  > @upg_patch_info.sql

##
## Upgrade / Patch History
##

ACTION_TIME                         ACTION       NAMESPACE  VERSION                                     ID COMMENTS                                                     BUNDLE_SERIES
----------------------------------- ------------ ---------- ----------------------------------- ---------- ------------------------------------------------------------ ---------------
                                    BOOTSTRAP    DATAPATCH  12.1.0.2                                       RDBMS_12.1.0.2.0DBPSU_LINUX.X64_161210

$ oh12c
$ opatch lspatches
++++++++++++++++++++++++++
31219939;Database PSU 12.1.0.2.200714, Oracle JavaVM Component (JUL2020)
31113348;Database Patch Set Update : 12.1.0.2.200714 (31113348)

OPatch succeeded.
++++++++++++++++++++++++++

$ oh19c
$ opatch lspatches
++++++++++++++++++++++++++
31219897;OJVM RELEASE UPDATE: 19.8.0.0.200714 (31219897)
31281355;Database Release Update : 19.8.0.0.200714 (31281355)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.
++++++++++++++++++++++++++

Note: it’s pretty important to compare patch level on current 12c and future 19c Oracle Homes on all servers in Data Guard environment to avoid any unexpected behaviour during maintenance. 

9. Take a backup of your current primary database

It’s up to you on how to take a backup of your database. I suppose most of DBA’s should already have reliable backup strategy for Oracle Databases they have. I prefer RMAN backups and here is the script I usually use.

10. Create guarantee restore point on each database

PRODS / PRODUPG / PRODUPGS

-- Create OS directory to store flashback logs --

$ mkdir -p /data/flashback

-- Check and correct flashback related parameters --

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=50G SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/data/flashback' SCOPE=BOTH;

System altered.

SQL> SET LINES 400 PAGES 1000
COL NAME FOR A45
COL VALUE FOR A20
COL ISSYS_MODIFIABLE FOR A16
COL ISPDB_MODIFIABLE FOR A16
COL ISSES_MODIFIABLE FOR A16
COL DESCRIPTION FOR A75
SELECT NAME
       ,VALUE
       ,ISSYS_MODIFIABLE
       ,ISPDB_MODIFIABLE
       ,ISSES_MODIFIABLE
       ,DESCRIPTION
FROM   V$SYSTEM_PARAMETER2
WHERE  NAME IN ('db_flashback_retention_target','db_recovery_file_dest','db_recovery_file_dest_size');

NAME                                          VALUE                ISSYS_MODIFIABLE ISPDB_MODIFIABLE ISSES_MODIFIABLE DESCRIPTION
--------------------------------------------- -------------------- ---------------- ---------------- ---------------- ---------------------------------------------------------------------------
db_recovery_file_dest                         /data/flashback      IMMEDIATE        FALSE            FALSE            default database recovery file location
db_recovery_file_dest_size                    53687091200          IMMEDIATE        FALSE            FALSE            database recovery files size limit
db_flashback_retention_target                 1440                 IMMEDIATE        FALSE            FALSE            Maximum Flashback Database log retention time in minutes.

-- Check current flashback status --

SQL> SELECT NAME,FLASHBACK_ON FROM V$DATABASE;

NAME                                          FLASHBACK_ON
--------------------------------------------- ------------------
PROD                                          NO

-- Stop Media Recovery --

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- Enable flashback --

SQL> ALTER DATABASE FLASHBACK ON;
SQL> SELECT NAME,FLASHBACK_ON FROM V$DATABASE;

NAME                                          FLASHBACK_ON
--------------------------------------------- ------------------
PROD                                          YES

-- Create guarantee restore point --

SQL> CREATE RESTORE POINT BEFORE_SOE_19C_UPGRADE GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> COL NAME FOR A30
COL TIME FOR A40
COL GUARANTEE_FLASHBACK_DATABASE FOR A40
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

NAME                                  SCN TIME                                     DATABASE_INCARNATION# GUARANTEE_FLASHBACK_DATABASE             STORAGE_SIZE
------------------------------ ---------- ---------------------------------------- --------------------- ---------------------------------------- ------------
BEFORE_SOE_19C_UPGRADE            2466080 18-NOV-20 11.31.55.000000000 AM                              1 YES                                        1073741824

-- Enable Media Recovery --

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION USING CURRENT LOGFILE;

PROD

-- Create OS directory to store flashback logs --

$ mkdir -p /data/flashback

-- Check and correct flashback related parameters --

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=50G SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/data/flashback' SCOPE=BOTH;

System altered.

SQL> SET LINES 400 PAGES 1000
COL NAME FOR A45
COL VALUE FOR A20
COL ISSYS_MODIFIABLE FOR A16
COL ISPDB_MODIFIABLE FOR A16
COL ISSES_MODIFIABLE FOR A16
COL DESCRIPTION FOR A75
SELECT NAME
       ,VALUE
       ,ISSYS_MODIFIABLE
       ,ISPDB_MODIFIABLE
       ,ISSES_MODIFIABLE
       ,DESCRIPTION
FROM   V$SYSTEM_PARAMETER2
WHERE  NAME IN ('db_flashback_retention_target','db_recovery_file_dest','db_recovery_file_dest_size');

NAME                                          VALUE                ISSYS_MODIFIABLE ISPDB_MODIFIABLE ISSES_MODIFIABLE DESCRIPTION
--------------------------------------------- -------------------- ---------------- ---------------- ---------------- ---------------------------------------------------------------------------
db_recovery_file_dest                         /data/flashback      IMMEDIATE        FALSE            FALSE            default database recovery file location
db_recovery_file_dest_size                    53687091200          IMMEDIATE        FALSE            FALSE            database recovery files size limit
db_flashback_retention_target                 1440                 IMMEDIATE        FALSE            FALSE            Maximum Flashback Database log retention time in minutes.

-- Check current flashback status --

SQL> SELECT NAME,FLASHBACK_ON FROM V$DATABASE;

NAME                                          FLASHBACK_ON
--------------------------------------------- ------------------
PROD                                          NO

-- Enable flashback --

SQL> ALTER DATABASE FLASHBACK ON;
SQL> SELECT NAME,FLASHBACK_ON FROM V$DATABASE;

NAME                                          FLASHBACK_ON
--------------------------------------------- ------------------
PROD                                          YES

-- Create guarantee restore point --

SQL> CREATE RESTORE POINT BEFORE_SOE_19C_UPGRADE GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> COL NAME FOR A30
COL TIME FOR A40
COL GUARANTEE_FLASHBACK_DATABASE FOR A40
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

NAME                                  SCN TIME                                     DATABASE_INCARNATION# GUARANTEE_FLASHBACK_DATABASE             STORAGE_SIZE
------------------------------ ---------- ---------------------------------------- --------------------- ---------------------------------------- ------------
BEFORE_SOE_19C_UPGRADE            2466080 18-NOV-20 11.31.55.000000000 AM                              1 YES                                        1073741824

II. Create Transient Logical Standby from existing Physical Standby

Ref. to Step by Step Guide on How to Create Logical Standby (Doc ID 738643.1)

1. Make Sure that Physical Standby is in Sync with Primary Database

SYS@PRODUPG / PRODUPGS 12c > SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
FROM 
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, 
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL 
WHERE 
ARCH.THREAD# = APPL.THREAD# 
ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied
---------- ---------------------- ---------------------
         1                    122                   122

SYS@PRODUPG / PRODUPGS 12c > @stby_lag_time.sql

##
## Time Lag:
##

NAME                           VALUE
------------------------------ ----------------------------------------
transport lag                  +00 00:00:00
apply lag                      +00 00:00:00
apply finish time              +00 00:00:00.000
estimated startup time         17

2. Stop Redo Apply on the Physical Standby Database

SYS@PRODUPG 12c> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SYS@PRODUPG 12c > ALTER SYSTEM SET log_archive_dest_state_3=DEFER;

SYS@PRODUPGS 12c > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

3.  Build a Dictionary in the Redo Data on Primary Database

The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete. Long-running transactions executed on the primary database will affect the timeliness of this command.

SYS@PROD 12c > EXECUTE DBMS_LOGSTDBY.BUILD;

PL/SQL procedure successfully completed.

4.  Convert to a Logical Standby Database

SYS@PRODUPG 12с > ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY;

Database altered.

SYS@PRODUPG 12с > SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

DATABASE_ROLE    INSTANCE                       OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------------- ------------------------------ -------------------- -------------------- -------------------- --------------------
LOGICAL STANDBY  PRODUPG                        MOUNTED              MAXIMUM PERFORMANCE  UNPROTECTED          NOT ALLOWED

Alert log:

Wed Nov 18 15:30:22 2020
ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY
Wed Nov 18 15:30:22 2020
Media Recovery Start: Managed Standby Recovery (PRODUPG)
Wed Nov 18 15:30:22 2020
Started logmerger process
Wed Nov 18 15:30:22 2020
Managed Standby Recovery not using Real Time Apply
Wed Nov 18 15:30:22 2020
Parallel Media Recovery started with 2 slaves
Wed Nov 18 15:30:22 2020
Media Recovery Log /data/arclogs/PRODUPG/produpg_1_129_1054192630.arc
Wed Nov 18 15:30:22 2020
Media Recovery Log /data/arclogs/PRODUPG/produpg_1_130_1054192630.arc
Wed Nov 18 15:30:26 2020
Media Recovery Log /data/arclogs/PRODUPG/produpg_1_131_1054192630.arc
Wed Nov 18 15:30:27 2020
Incomplete Recovery applied until change 2558477 time 11/18/2020 15:26:36
Wed Nov 18 15:30:27 2020
Media Recovery Complete (PRODUPG)
Wed Nov 18 15:30:27 2020
Stopping Emon pool
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Stopping Emon pool
Wed Nov 18 15:30:27 2020
SMON: disabling cache recovery
Wed Nov 18 15:30:28 2020
Killing 4 processes (PIDS:2562,2552,2555,2558) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 2795
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 2558477 time 11/18/2020 15:26:36
Resetting resetlogs activation ID 465985398 (0x1bc65f76)
Online log /data/PRODUPG/redo01.log: Thread 1 Group 1 was previously cleared
Online log /data/PRODUPG/redo02.log: Thread 1 Group 2 was previously cleared
Online log /data/PRODUPG/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 2558475
Wed Nov 18 15:30:30 2020
Setting recovery target incarnation to 2
Wed Nov 18 15:30:31 2020
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
RECOVER TO LOGICAL STANDBY: Complete - Database mounted as logical standby
Completed: ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY

5. Open the Logical Standby Database

SYS@PRODUPG 12с > ALTER DATABASE OPEN;

Database altered.

6. Configure Transient Logical Standby parameters for rolling upgrade

SYS@PRODUPG 12с > EXEC DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'FALSE');

PL/SQL procedure successfully completed.

SYS@PRODUPG 12с > EXEC DBMS_LOGSTDBY.APPLY_SET('MAX_EVENTS_RECORDED', DBMS_LOGSTDBY.MAX_EVENTS);

PL/SQL procedure successfully completed.

SYS@PRODUPG 12с > EXEC DBMS_LOGSTDBY.APPLY_SET('RECORD_UNSUPPORTED_OPERATIONS', 'TRUE');

PL/SQL procedure successfully completed.

SYS@PRODUPG 12с > EXEC DBMS_LOGSTDBY.APPLY_SET('MAX_SERVERS', '15');

PL/SQL procedure successfully completed.

SYS@PRODUPG 12с > EXEC DBMS_LOGSTDBY.APPLY_SET('MAX_SGA', '50');

PL/SQL procedure successfully completed.

7. Start Logical Apply on Standby

SYS@PRODUPG 12с > ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

8. Wait until logminer dictionary has fully loaded

SYS@PRODUPG 12с > SELECT NVL(TRANSLATE((SELECT SUBSTR(L.STATUS, 12, 4) FROM V$LOGSTDBY L, V$LOGSTDBY_STATE S WHERE L.STATUS LIKE 'ORA-16115%' AND S.STATE = 'LOADING DICTIONARY'), '%', ' '), '0') as "Progress %"
FROM DUAL;

Progress %
----------------
0

SYS@PRODUPG 12с > /

Progress %
----------------
48

SYS@PRODUPG 12с > /

Progress %
----------------
62

SYS@PRODUPG 12с > /

Progress %
----------------
100

9. Test if TLS is working

SYS@PROD 12c > CREATE TABLE SOE.TEST_STBY_SYNC (
  id      NUMBER NOT NULL,
  title   VARCHAR2(100)  NOT NULL
);

SYS@PROD 12c > ALTER TABLE SOE.TEST_STBY_SYNC
  ADD (
    CONSTRAINT TEST_STBY_SYNC_PK PRIMARY KEY (id)
  );

SYS@PROD 12c > CREATE SEQUENCE SOE.TEST_STBY_SYNC_ID_SEQ
    INCREMENT BY 1
    START WITH 1
    MINVALUE 1
    MAXVALUE 100000
    CYCLE
    CACHE 2;

SYS@PROD 12c > INSERT INTO SOE.TEST_STBY_SYNC (id, title) VALUES (SOE.TEST_STBY_SYNC_ID_SEQ.NEXTVAL, dbms_random.string('x',100));

SYS@PROD 12c > /

SYS@PROD 12c > /

SYS@PROD 12c > commit;

SYS@PROD 12c > SELECT * FROM SOE.TEST_STBY_SYNC;

        ID TITLE
---------- ----------------------------------------------------------------------------------------------------
         1 16NJ0PVLCK5V6A7SXFITPZJWZO8VCO9AW60JA9T8OATRGWPUNT17YJP0VVECD5V8R6FGK01MQCL5W9RMHA3B7ZAR2FWOFPHFH880
         2 JB3MBYC78QKZKCTB06K4OYBAUDRF3TKCKO2P6XPQ7SYIPX5UUZ7T0OWDPV2FRWJSFMPQAI27ZJNEWG5ODZ83HHP9J44XU8JNFO80
         3 F82UIU7W4222UAI9D82ZQUO4RMVE7WECO8IVUGZMJRCEEN78HMN6JX46UX8QQM8TAKYOQIVX0FE5IVC88ILQTJIJ6RML28FTCB35

SYS@PRODUPG 12c > SELECT * FROM SOE.TEST_STBY_SYNC;

        ID TITLE
---------- ----------------------------------------------------------------------------------------------------
         1 16NJ0PVLCK5V6A7SXFITPZJWZO8VCO9AW60JA9T8OATRGWPUNT17YJP0VVECD5V8R6FGK01MQCL5W9RMHA3B7ZAR2FWOFPHFH880
         2 JB3MBYC78QKZKCTB06K4OYBAUDRF3TKCKO2P6XPQ7SYIPX5UUZ7T0OWDPV2FRWJSFMPQAI27ZJNEWG5ODZ83HHP9J44XU8JNFO80
         3 F82UIU7W4222UAI9D82ZQUO4RMVE7WECO8IVUGZMJRCEEN78HMN6JX46UX8QQM8TAKYOQIVX0FE5IVC88ILQTJIJ6RML28FTCB35

10. Check gap between primary and logical standby

SYS@PRODUPG 12c > ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SYS@PRODUPG 12c > COL LATEST_TIME FOR A30
COL APPLIED_TIME FOR A30
COL MINING_TIME FOR A30

SELECT LATEST_TIME, APPLIED_TIME, MINING_TIME, (LATEST_TIME-APPLIED_TIME)*24*3600 GAP_IN_SECOND 
FROM V$LOGSTDBY_PROGRESS;

LATEST_TIME                    APPLIED_TIME                   MINING_TIME                    GAP_IN_SECOND
------------------------------ ------------------------------ ------------------------------ -------------
19-NOV-2020 11:16:19           19-NOV-2020 11:16:18           19-NOV-2020 11:16:18                       1

11. Start Media Recovery on Cascade Standby (PRODUPGS)

SYS@PRODUPGS 12c > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;

Database altered.

SYS@PRODUPG 12c > ALTER SYSTEM SET log_archive_dest_state_3=ENABLE;

SYS@PRODUPG 12c > ALTER SYSTEM SWITCH LOGFILE;

SOE@PRODUPGS 12c > SELECT * FROM SOE.TEST_STBY_SYNC;

12. Monitor and Tune SQL Apply

Now you have to monitor if Logical Standby is working as expected (no gap with primary, no LCR Cache paging). You can take some adjustment to Logical Standby settings if required according to note.

13. Final picture before the upgrade phase

SYS@PROD 12c > SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

DATABASE_ROLE    INSTANCE                       OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------------- ------------------------------ -------------------- -------------------- -------------------- --------------------
PRIMARY          PROD                           READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  TO STANDBY

SYS@PRODS 12c > SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

DATABASE_ROLE    INSTANCE                       OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------------- ------------------------------ -------------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY PRODS                          READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  NOT ALLOWED

SYS@PRODUPG 12c > SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

DATABASE_ROLE    INSTANCE                       OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------------- ------------------------------ -------------------- -------------------- -------------------- --------------------
LOGICAL STANDBY  PRODUPG                        READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  NOT ALLOWED

SYS@PRODUPGS 12c > SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

DATABASE_ROLE    INSTANCE                       OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------------- ------------------------------ -------------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY PRODUPGS                       READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  NOT ALLOWED

III. Upgrade

Note: I will not post all steps in detail which required for upgrade, but you can check following Oracle Doc for step by step instructions: Oracle 19c – Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 19c (Doc ID 2539778.1).

1. Stop Logical Apply

SYS@PRODUPG 12c > ALTER DATABASE STOP LOGICAL STANDBY APPLY;

2. Stop Recovery on Cascade Standby

SYS@PRODUPGS 12c > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3. Upgrade Logical Standby

>>> oracle

$ oh12c

SYS@PRODUPG 12c > SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS!='VALID';

  COUNT(*)
----------
         1
		 
SYS@PRODUPG 12c > COL OWNER FORMAT A10
COL OBJECT_NAME FORMAT A40
COL OBJECT_TYPE FORMAT A25
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE STATUS!='VALID';

OWNER      OBJECT_NAME                              OBJECT_TYPE
---------- ---------------------------------------- -------------------------
SYS        PRODUCT_USER_PROFILE                     SYNONYM


SYS@PRODUPG 12c > EXEC UTL_RECOMP.RECOMP_PARALLEL(2);

PL/SQL procedure successfully completed.

SYS@PRODUPG 12c > SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS!='VALID';

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

SYS@PRODUPG 12c > PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

SYS@PRODUPG 12c > EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

SYS@PRODUPG 12c > SELECT VERSION FROM V$TIMEZONE_FILE;

   VERSION
----------
        18

SYS@PRODUPG 12c > @comp_info

##
## Components summary
##

MODIFIED                                 COMP_ID    COMP_NAME                                          VERSION              STATUS
---------------------------------------- ---------- -------------------------------------------------- -------------------- --------------------
19-OCT-20 08.19.25.000000000 AM          CATALOG    Oracle Database Catalog Views                      12.1.0.2.0           VALID
19-OCT-20 08.19.26.000000000 AM          CATPROC    Oracle Database Packages and Types                 12.1.0.2.0           VALID
19-OCT-20 08.19.28.000000000 AM          XDB        Oracle XML Database                                12.1.0.2.0           VALID
19-OCT-20 08.19.30.000000000 AM          JAVAVM     JServer JAVA Virtual Machine                       12.1.0.2.0           VALID
19-OCT-20 08.19.30.000000000 AM          XML        Oracle XDK                                         12.1.0.2.0           VALID
19-OCT-20 08.19.31.000000000 AM          CATJAVA    Oracle Database Java Packages                      12.1.0.2.0           VALID
19-OCT-20 08.19.32.000000000 AM          ORDIM      Oracle Multimedia                                  12.1.0.2.0           VALID
19-OCT-20 08.19.32.000000000 AM          CONTEXT    Oracle Text                                        12.1.0.2.0           VALID
19-OCT-20 08.19.32.000000000 AM          APS        OLAP Analytic Workspace                            12.1.0.2.0           VALID
19-OCT-20 08.19.34.000000000 AM          XOQ        Oracle OLAP API                                    12.1.0.2.0           VALID
19-OCT-20 08.19.34.000000000 AM          SDO        Spatial                                            12.1.0.2.0           VALID
19-OCT-20 08.19.35.000000000 AM          OLS        Oracle Label Security                              12.1.0.2.0           VALID
19-OCT-20 08.19.35.000000000 AM          DV         Oracle Database Vault                              12.1.0.2.0           VALID
19-OCT-20 08.19.37.000000000 AM          OWM        Oracle Workspace Manager                           12.1.0.2.0           VALID

14 rows selected.


$ mkdir -p /home/oracle/dba/upgrade

// Run preupgrade

$ /u01/app/oracle/product/12102/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/198000/dbhome_1/rdbms/admin/preupgrade.jar FILE TEXT DIR /home/oracle/dba/upgrade

+++++++++++++++
==================
PREUPGRADE SUMMARY
==================
  /home/oracle/dba/upgrade/preupgrade.log
  /home/oracle/dba/upgrade/preupgrade_fixups.sql
  /home/oracle/dba/upgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@/home/oracle/dba/upgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/home/oracle/dba/upgrade/postupgrade_fixups.sql

Preupgrade complete: 2020-11-19T13:26:58
+++++++++++++++

// Review recommendations

$ vi /home/oracle/dba/upgrade/preupgrade.log

// Review and run preupgrade_fixups.sql

SYS@PRODUPG 12c > @/home/oracle/dba/upgrade/preupgrade_fixups.sql

SYS@PRODUPG 12c > SHUTDOWN IMMEDIATE;

$ oh19c

 [!!!!!!!!!] // Copy spfile to New 19c Home

SYS@PRODUPG 19c> STARTUP UPGRADE;

SYS@PRODUPG 19c > SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;

INSTANCE_NAME    STATUS
---------------- ------------
PRODUPG          OPEN MIGRATE

$ cd $ORACLE_HOME/bin
$ ./dbupgrade
+++++++++++++++
------------------------------------------------------
Phases [0-107]         End Time:[2020_11_19 13:55:55]
------------------------------------------------------

Grand Total Time: 4219s

 LOG FILES: (/u01/app/oracle/product/198000/dbhome_1/cfgtoollogs/PRODUPG/upgrade20201119124521/catupgrd*.log)

Upgrade Summary Report Located in:
/u01/app/oracle/product/198000/dbhome_1/cfgtoollogs/PRODUPG/upgrade20201119124521/upg_summary.log

Grand Total Upgrade Time:    [0d:1h:10m:19s]
+++++++++++++++

SYS@PRODUPG 19c > STARTUP;

SYS@PRODUPG 19c > EXEC UTL_RECOMP.RECOMP_PARALLEL(8);

SYS@PRODUPG 19c > @?/rdbms/admin/utlrp.sql

SYS@PRODUPG 19c > SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS!='VALID';

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

4. Post-Upgrade Steps

// Update oratab entries

$ cat /etc/oratab | grep -i PRODUPG
+++++++++++++++
PRODUPG:/u01/app/oracle/product/198000/dbhome_1:N
+++++++++++++++

// Post-upgrade fixup script

SYS@PRODUPG 19c > @/home/oracle/dba/upgrade/postupgrade_fixups.sql
+++++++++++++++
Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    7.  old_time_zones_exist      NO          Manual fixup recommended.
    8.  post_dictionary           YES         None.
    9.  post_fixed_objects        NO          Informational only.
                                              Further action is optional.
+++++++++++++++

// Check components status

SYS@PRODUPG 19c > @comp_info

##
## Components summary
##

MODIFIED                                 COMP_ID    COMP_NAME                                          VERSION              VERSION_FULL         STATUS
---------------------------------------- ---------- -------------------------------------------------- -------------------- -------------------- --------------------
19-NOV-20 01.06.50.000000000 PM          RAC        Oracle Real Application Clusters                   19.0.0.0.0           19.8.0.0.0           OPTION OFF
19-NOV-20 02.08.32.000000000 PM          CATALOG    Oracle Database Catalog Views                      19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.32.000000000 PM          CATPROC    Oracle Database Packages and Types                 19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.33.000000000 PM          XDB        Oracle XML Database                                19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.39.000000000 PM          CATJAVA    Oracle Database Java Packages                      19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.39.000000000 PM          ORDIM      Oracle Multimedia                                  19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.39.000000000 PM          XML        Oracle XDK                                         19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.39.000000000 PM          CONTEXT    Oracle Text                                        19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.39.000000000 PM          JAVAVM     JServer JAVA Virtual Machine                       19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.40.000000000 PM          APS        OLAP Analytic Workspace                            19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.41.000000000 PM          SDO        Spatial                                            19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.41.000000000 PM          XOQ        Oracle OLAP API                                    19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.42.000000000 PM          OLS        Oracle Label Security                              19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.42.000000000 PM          DV         Oracle Database Vault                              19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.43.000000000 PM          OWM        Oracle Workspace Manager                           19.0.0.0.0           19.8.0.0.0           VALID

15 rows selected.

// Upgrade the Time Zone File Version After Upgrading Oracle Database

SYS@PRODUPG 19c > SELECT VERSION FROM V$TIMEZONE_FILE;

   VERSION
----------
        18

-- Time zone upgrade check script --

SYS@PRODUPG 19c > @?/rdbms/admin/utltz_upg_check.sql
+++++++++++++++
Session altered.

INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv18 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv32 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.

Session altered.
+++++++++++++++

-- Time zone apply script. Warning: This script will restart the database and adjust time zone data. --

SYS@PRODUPG 19c > @?/rdbms/admin/utltz_upg_apply.sql
+++++++++++++++
Session altered.

INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv32 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 3221222464 bytes
Fixed Size                  8901696 bytes
Variable Size             754974720 bytes
Database Buffers         2164260864 bytes
Redo Buffers              293085184 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 3221222464 bytes
Fixed Size                  8901696 bytes
Variable Size             754974720 bytes
Database Buffers         2164260864 bytes
Redo Buffers              293085184 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv32 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.

Session altered.
+++++++++++++++
		
SYS@PRODUPG 19c > SELECT VERSION FROM V$TIMEZONE_FILE;

   VERSION
----------
        32		
		
// Gather Dictionary Stats

SYS@PRODUPG 19c > EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

// Check parameters related to old 12c Home

SYS@PRODUPG 19c > SELECT NAME ,VALUE FROM V$SYSTEM_PARAMETER2 WHERE VALUE LIKE '%12102%';

no rows selected

// Start Listener from New 19c Home

$ oh12c
$ lsnrctl stop LIPRODUPG

$ oh19c
$ lsnrctl start LIPRODUPG

5. Start Cascade Standby from 19c Home

// Update oratab entries

$ cat /etc/oratab | grep -i PRODUPGS
+++++++++++++++
PRODUPGS:/u01/app/oracle/product/198000/dbhome_1:N
+++++++++++++++

// Start Listener from New 19c Home

$ oh12c
$ lsnrctl stop LIPRODUPGS

$ oh19c
$ lsnrctl start LIPRODUPGS

// Start Cascade Standby from New 19c Home

$ oh12c

SYS@PRODUPGS 12c > SHU IMMEDIATE;

$ oh19c

 [!!!!!!!!!] // Copy spfile to New 19c Home

SYS@PRODUPGS 19c > STARTUP MOUNT;
SYS@PRODUPGS 19c > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;

Database altered.

SYS@PRODUPG S19c >  ALTER SYSTEM SET log_archive_dest_state_3=ENABLE;

// Monitor Alert Log file on Cascade Standby

$ tail -100f /u01/app/oracle/diag/rdbms/produpgs/PRODUPGS/trace/alert_PRODUPGS.log
+++++++++++++++
...
...
...
2020-11-19T14:47:32.451194+03:00
Attempt to start background Managed Standby Recovery process (PRODUPGS)
Starting background process MRP0
2020-11-19T14:47:32.481851+03:00
MRP0 started with pid=8, OS id=17876
2020-11-19T14:47:32.487318+03:00
Background Managed Standby Recovery process started (PRODUPGS)
2020-11-19T14:47:37.715694+03:00
 Started logmerger process
2020-11-19T14:47:37.737825+03:00
PR00 (PID:17890): Managed Standby Recovery starting Real Time Apply
2020-11-19T14:47:38.146506+03:00
Parallel Media Recovery started with 2 slaves
2020-11-19T14:47:38.335743+03:00
stopping change tracking
2020-11-19T14:47:38.489574+03:00
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE
2020-11-19T14:47:38.496586+03:00
PR00 (PID:17890): Media Recovery Log /data/arclogs/PRODUPGS/produpgs_1_8_1056884422.arc
2020-11-19T14:48:15.402306+03:00
PR00 (PID:17890): Media Recovery Log /data/arclogs/PRODUPGS/produpgs_1_9_1056884422.arc
2020-11-19T14:48:15.802760+03:00
PR00 (PID:17890): Media Recovery Log /data/arclogs/PRODUPGS/produpgs_1_10_1056884422.arc
...
...
...
+++++++++++++++

// Wait for Sync between Logical and Cascade Standby

SYS@PRODUPGS 19c > SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
FROM 
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, 
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL 
WHERE 
ARCH.THREAD# = APPL.THREAD# 
ORDER BY 1;

+++++++++++++++
    Thread Last Sequence Received Last Sequence Applied
---------- ---------------------- ---------------------
         1                     25                    12
+++++++++++++++

SYS@PRODUPGS 19c > /

    Thread Last Sequence Received Last Sequence Applied
---------- ---------------------- ---------------------
         1                     26                    16

SYS@PRODUPGS 19c > /

    Thread Last Sequence Received Last Sequence Applied
---------- ---------------------- ---------------------
         1                     26                    26

// Open for Read-Only

SYS@PRODUPGS 19c > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SYS@PRODUPGS 19c  > ALTER DATABASE OPEN READ ONLY;

SYS@PRODUPGS 19c > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;

// Check components status

SYS@PRODUPGS 19c > @comp_info

##
## Components summary
##

MODIFIED                                 COMP_ID    COMP_NAME                                          VERSION              VERSION_FULL         STATUS
---------------------------------------- ---------- -------------------------------------------------- -------------------- -------------------- --------------------
19-NOV-20 01.06.50.000000000 PM          RAC        Oracle Real Application Clusters                   19.0.0.0.0           19.8.0.0.0           OPTION OFF
19-NOV-20 02.08.32.000000000 PM          CATALOG    Oracle Database Catalog Views                      19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.32.000000000 PM          CATPROC    Oracle Database Packages and Types                 19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.33.000000000 PM          XDB        Oracle XML Database                                19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.39.000000000 PM          CATJAVA    Oracle Database Java Packages                      19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.39.000000000 PM          ORDIM      Oracle Multimedia                                  19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.39.000000000 PM          XML        Oracle XDK                                         19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.39.000000000 PM          CONTEXT    Oracle Text                                        19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.39.000000000 PM          JAVAVM     JServer JAVA Virtual Machine                       19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.40.000000000 PM          APS        OLAP Analytic Workspace                            19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.41.000000000 PM          SDO        Spatial                                            19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.41.000000000 PM          XOQ        Oracle OLAP API                                    19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.42.000000000 PM          OLS        Oracle Label Security                              19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.42.000000000 PM          DV         Oracle Database Vault                              19.0.0.0.0           19.8.0.0.0           VALID
19-NOV-20 02.08.43.000000000 PM          OWM        Oracle Workspace Manager                           19.0.0.0.0           19.8.0.0.0           VALID

15 rows selected.

6. Enable SQL Apply to Logical Standby

Note: This is required to sync with Primary, because during Logical Standby Upgrade – Primary database was available for users/applications, thus there is a gap and databases needs to be synced.

SYS@PRODUPG 19c > ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

SYS@PRODUPG 19c > ALTER SYSTEM SWITCH LOGFILE;

SYS@PRODUPG 19c > /

SYS@PRODUPG 19c > /

7. Insert test data and ensure databases are in sync

SYS@PROD 12c > INSERT INTO SOE.TEST_STBY_SYNC (id, title) VALUES (SOE.TEST_STBY_SYNC_ID_SEQ.NEXTVAL, dbms_random.string('x',100));

SYS@PROD 12c > /

SYS@PROD 12c > /

SYS@PROD 12c > commit;

SYS@PROD 12c > SELECT * FROM SOE.TEST_STBY_SYNC;

SYS@PRODS 12c > SELECT * FROM SOE.TEST_STBY_SYNC;

SYS@PRODUPG 19c > SELECT * FROM SOE.TEST_STBY_SYNC;

SYS@PRODUPGS 19c > SELECT * FROM SOE.TEST_STBY_SYNC;

IV. Switchover

1. Check if all databases are in sync

SYS@PROD 12c > SELECT THREAD# ,MAX(SEQUENCE#) "LAST_SEQ_GENERATED"
FROM   V$ARCHIVED_LOG
WHERE  FIRST_TIME BETWEEN (SYSDATE-1) AND (SYSDATE+1)
GROUP  BY THREAD#
ORDER  BY 1;

   THREAD# LAST_SEQ_GENERATED
---------- ------------------
         1                154

SYS@PRODS 12c > SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
FROM 
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, 
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL 
WHERE 
ARCH.THREAD# = APPL.THREAD# 
ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied
---------- ---------------------- ---------------------
         1                    154                   154


SYS@PRODS 12c > @stby_lag_time

##
## Time Lag:
##

NAME                           VALUE
------------------------------ ----------------------------------------
transport lag                  +00 00:00:00
apply lag                      +00 00:00:00
apply finish time              +00 00:00:00.000
estimated startup time         20

SYS@PRODUPG 19c > ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SYS@PRODUPG 19c > COL LATEST_TIME FOR A30
COL APPLIED_TIME FOR A30
COL MINING_TIME FOR A30
SELECT LATEST_TIME, APPLIED_TIME, MINING_TIME, (LATEST_TIME-APPLIED_TIME)*24*3600 GAP_IN_SECOND 
FROM V$LOGSTDBY_PROGRESS;

LATEST_TIME                    APPLIED_TIME                   MINING_TIME                    GAP_IN_SECOND
------------------------------ ------------------------------ ------------------------------ -------------
20-NOV-2020 10:40:18           20-NOV-2020 10:40:17           20-NOV-2020 10:40:17                       1

SYS@PRODUPGS 19c > SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
FROM 
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, 
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL 
WHERE 
ARCH.THREAD# = APPL.THREAD# 
ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied
---------- ---------------------- ---------------------
         1                     31                    31

SYS@PRODUPGS 19c > @stby_lag_time.sql

##
## Time Lag:
##

NAME                           VALUE
------------------------------ ----------------------------------------
transport lag                  +00 00:00:00
apply lag                      +00 00:00:00
apply finish time              +00 00:00:00.000
estimated startup time         15

2. Check the swithcover_status

SYS@PROD 12c > SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SYS@PRODUPG 19c > SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED

3. Switchover

Note: Switchover to logical standby prevents any new user sessions connecting to database but this command will wait for the existing transactions to complete. So it is advisable not to have active transactions during switchover. Check v$transaction to know any active transactions which is delaying the switchover. Once this command executed sucessfully then the switchover_status of logical standby will become ‘TO PRIMARY’. No need to shutdown and start the primary database.

SYS@PRODS 12c > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SYS@PROD 12c > ALTER SYSTEM SET log_archive_dest_state_2=DEFER;

SYS@PROD 12c > ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

Database altered.

Alert Log:
----------
alter database commit to switchover to logical standby
Fri Nov 20 10:53:22 2020
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY (PROD)
Fri Nov 20 10:53:22 2020
LOGSTDBY: Ensuring no active hot backups.
LOGSTDBY: Enabling database guard to prevent new transactions.
LOGSTDBY: Guard setting change: Waiting for txns in-flight at scn 0x0000.00281358 [2626392] to complete
LOGSTDBY: Guard setting change: All in-flight txns at scn 0x0000.00281358 [2626392] completed
LOGSTDBY: Switchover DDL: Waiting for txns in-flight at scn 0x0000.00281358 [2626392] to complete
LOGSTDBY: Switchover DDL: All in-flight txns at scn 0x0000.00281358 [2626392] completed
Fri Nov 20 10:53:22 2020
Thread 1 advanced to log sequence 161 (LGWR switch)
  Current log# 2 seq# 161 mem# 0: /data/PROD/redo02.log
Fri Nov 20 10:53:23 2020
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_3
ARCH: Standby redo logfile selected for thread 1 sequence 160 for destination LOG_ARCHIVE_DEST_3
Fri Nov 20 10:53:23 2020
Archived Log entry 300 added for thread 1 sequence 160 ID 0x1bc65f76 dest 1:
Fri Nov 20 10:53:23 2020
LOGSTDBY: Waiting for pending archivals to dest [all].
Fri Nov 20 10:53:23 2020
Waiting for all non-current ORLs to be archived...
Fri Nov 20 10:53:23 2020
All non-current ORLs have been archived.
Fri Nov 20 10:53:23 2020
Waiting for all FAL entries to be archived...
Fri Nov 20 10:53:23 2020
All FAL entries have been archived.
Fri Nov 20 10:53:23 2020
Waiting for potential Logical Standby switchover target to become synchronized...
Fri Nov 20 10:53:24 2020
Active, synchronized Logical Standby switchover target has been identified
Fri Nov 20 10:53:24 2020
LOGSTDBY: Preparing the COMMIT TO SWITCHOVER TO LOGICAL STANDBY DDL at scn [2626401].
Fri Nov 20 10:53:25 2020
Thread 1 advanced to log sequence 162 (LGWR switch)
  Current log# 3 seq# 162 mem# 0: /data/PROD/redo03.log
Fri Nov 20 10:53:26 2020
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_3
ARCH: Standby redo logfile selected for thread 1 sequence 161 for destination LOG_ARCHIVE_DEST_3
Fri Nov 20 10:53:26 2020
Archived Log entry 302 added for thread 1 sequence 161 ID 0x1bc65f76 dest 1:
LOG_ARCHIVE_DEST_3 is a potential Logical Standby switchover target
Fri Nov 20 10:53:26 2020
Thread 1 cannot allocate new log, sequence 163
Checkpoint not complete
  Current log# 3 seq# 162 mem# 0: /data/PROD/redo03.log
Fri Nov 20 10:53:27 2020
Thread 1 advanced to log sequence 163 (LGWR switch)
  Current log# 1 seq# 163 mem# 0: /data/PROD/redo01.log
Fri Nov 20 10:53:27 2020
Archived Log entry 304 added for thread 1 sequence 162 ID 0x1bc65f76 dest 1:
Fri Nov 20 10:53:27 2020
LOGSTDBY: Switchover complete (PROD)
LOGSTDBY: enabling scheduler job queue processes.
Fri Nov 20 10:53:27 2020
JOBQ: re-enabling CJQ0
Completed: alter database commit to switchover to logical standby
----------

SYS@PROD 12c > SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

DATABASE_ROLE    INSTANCE                       OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------------- ------------------------------ -------------------- -------------------- -------------------- --------------------
LOGICAL STANDBY  PROD                           READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  NOT ALLOWED

SYS@PRODUPG 19c > SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
LOGICAL STANDBY  TO PRIMARY

SYS@PRODUPG 19c > ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

Alert Log:
----------
2020-11-20T10:53:26.573424+03:00
LOGSTDBY status: ORA-16257: Switchover initiated stop apply successfully completed
.... (PID:3846): Current role is logical and force mode is not set, not clearing SRLs
2020-11-20T10:55:00.941025+03:00
alter database commit to switchover to primary
2020-11-20T10:55:00.941106+03:00
ALTER DATABASE SWITCHOVER TO PRIMARY (PRODUPG)
2020-11-20T10:55:00.941190+03:00
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY (PRODUPG)
2020-11-20T10:55:00.941312+03:00
LOGSTDBY: Switchover initiated via DDL
LOGSTDBY: Preparing to Create Detached Dictionary Build, pre-lockdown scn is [0x0000000000411b32]
Fri Nov 20 10:55:01 2020
Logminer Bld: Build started
2020-11-20T10:55:01.065231+03:00
ALTER SYSTEM SWITCH ALL LOGFILE start (PRODUPG)
2020-11-20T10:55:01.099658+03:00
Thread 1 advanced to log sequence 34 (LGWR switch),  current SCN: 4266807
2020-11-20T10:55:01.099665+03:00
ALTER SYSTEM SWITCH ALL LOGFILE complete (PRODUPG)
  Current log# 1 seq# 34 mem# 0: /data/PRODUPG/redo01.log
2020-11-20T10:55:01.227619+03:00
Fri Nov 20 10:55:01 2020
Logminer Bld: Lockdown Complete.  DB_TXN_SCN is 0 4266807 LockdownSCN is 4266807
LOGSTDBY: Starting SCN of new stream from recent lockdown [0x0000000000411b37]
2020-11-20T10:55:01.228235+03:00
LOGSTDBY: Preparing the COMMIT TO SWITCHOVER TO PRIMARY DDL at scn [4266811].
LOGSTDBY: Successful close of the current log stream:
LOGSTDBY:   primary:       [466030966]
LOGSTDBY:   first scn:     [0x0000000000000000]
LOGSTDBY:   end scn:       [0x0000000000281367]
LOGSTDBY:   processed scn: [0x0000000000281368]
2020-11-20T10:55:01.273313+03:00
LOGSTDBY: terminating active RFS connections for role change
2020-11-20T10:55:01.275108+03:00
Process termination requested for pid 5829 , [info = 0] [request issued by pid: 5249, uid: 54321]
LOGSTDBY: terminated RFS process [5829]
2020-11-20T10:55:01.275287+03:00
LOGSTDBY: terminated RFS process [4216]
LOGSTDBY: terminated RFS process [4218]
2020-11-20T10:55:01.275631+03:00
Process termination requested for pid 4216 , [info = 0] [request issued by pid: 5249, uid: 54321]
2020-11-20T10:55:01.277326+03:00
LOGSTDBY: (dglcccsp) Archiving standby redo logfiles.
LOGSTDBY: (dglcccsp) Not using surrogate archiving mode
2020-11-20T10:55:01.279918+03:00
Process termination requested for pid 4218 , [info = 0] [request issued by pid: 5249, uid: 54321]
2020-11-20T10:55:01.281177+03:00
LOGSTDBY: (dglcccsp) Complete.  No standby redo logfiles needed archiving.
2020-11-20T10:55:01.288292+03:00
.... (PID:5249): Database role cleared from LOGICAL STANDBY [dglc.c:1953]
Starting background process LSP1
2020-11-20T10:55:01.314844+03:00
LSP1 started with pid=59, OS id=5958
2020-11-20T10:55:01.316273+03:00
LOGSTDBY: (LSP1) LogMiner Dictionary Build Process Created
2020-11-20T10:55:01.321716+03:00
LOGSTDBY: (LSP1) Archiving online logs as a primary database
2020-11-20T10:55:01.324594+03:00
LOGSTDBY: Disabling database guard.
2020-11-20T10:55:01.338519+03:00
LOGSTDBY: Database guard disabled.  User transactions are now permitted.
2020-11-20T10:55:01.340841+03:00
TT04 (PID:5960): Switchover in progress, stop clearing SRLs
2020-11-20T10:55:01.362124+03:00
LOGSTDBY: enabling scheduler job queue processes.
2020-11-20T10:55:01.362203+03:00
JOBQ: re-enabling CJQ0
Completed: alter database commit to switchover to primary
2020-11-20T10:55:02.111443+03:00
ARC3 (PID:3223): LGWR is scheduled to archive to LAD:3 after log switch
ARC3 (PID:3223): SRL selected for T-1.S-33 for LAD:3
2020-11-20T10:55:02.557572+03:00
Thread 1 advanced to log sequence 35 (LGWR switch),  current SCN: 4266844
  Current log# 2 seq# 35 mem# 0: /data/PRODUPG/redo02.log
2020-11-20T10:55:02.592680+03:00
ARC0 (PID:3213): Archived Log entry 187 added for T-1.S-34 ID 0x1befd729 LAD:1
2020-11-20T10:55:02.626051+03:00
ARC3 (PID:3223): Archived Log entry 188 added for T-1.S-33 ID 0x1befd729 LAD:1
2020-11-20T10:55:02.635651+03:00
LOGSTDBY: (LSP1) Starting Full LogMiner Dictionary Build
Fri Nov 20 10:55:02 2020
Logminer Bld: Build started
2020-11-20T10:55:02.813472+03:00
ALTER SYSTEM SWITCH ALL LOGFILE start (PRODUPG)
2020-11-20T10:55:02.820544+03:00
Thread 1 cannot allocate new log, sequence 36
Checkpoint not complete
  Current log# 2 seq# 35 mem# 0: /data/PRODUPG/redo02.log
2020-11-20T10:55:06.027239+03:00
Thread 1 advanced to log sequence 36 (LGWR switch),  current SCN: 4266854
  Current log# 3 seq# 36 mem# 0: /data/PRODUPG/redo03.log
2020-11-20T10:55:06.027379+03:00
ALTER SYSTEM SWITCH ALL LOGFILE complete (PRODUPG)
2020-11-20T10:55:06.042745+03:00
Fri Nov 20 10:55:06 2020
Logminer Bld: Lockdown Complete.  DB_TXN_SCN is 0 4266854 LockdownSCN is 4266854
2020-11-20T10:55:06.050504+03:00
ARC3 (PID:3223): Archived Log entry 191 added for T-1.S-35 ID 0x1befd729 LAD:1
2020-11-20T10:55:21.515366+03:00
ALTER SYSTEM ARCHIVE LOG
2020-11-20T10:55:21.527369+03:00
Thread 1 cannot allocate new log, sequence 37
Checkpoint not complete
  Current log# 3 seq# 36 mem# 0: /data/PRODUPG/redo03.log
2020-11-20T10:55:23.636755+03:00
Thread 1 advanced to log sequence 37 (LGWR switch),  current SCN: 4267227
  Current log# 1 seq# 37 mem# 0: /data/PRODUPG/redo01.log
2020-11-20T10:55:23.725128+03:00
LSP1 (PID:5958): Archived Log entry 192 added for T-1.S-36 ID 0x1befd729 LAD:1
Fri Nov 20 10:55:23 2020
Logminer Bld: Done
LOGMINER: Dictionary Build: Waiting for completion of transactions started at or before scn 4267227 (0x0000000000411cdb)
LOGMINER: Dictionary Build: All transactions started at or before scn 4267227 (0x0000000000411cdb) have completed
2020-11-20T10:55:23.791667+03:00
Thread 1 cannot allocate new log, sequence 38
Checkpoint not complete
  Current log# 1 seq# 37 mem# 0: /data/PRODUPG/redo01.log
2020-11-20T10:55:26.603611+03:00
Thread 1 advanced to log sequence 38 (LGWR switch),  current SCN: 4267245
  Current log# 2 seq# 38 mem# 0: /data/PRODUPG/redo02.log
2020-11-20T10:55:26.646562+03:00
LSP1 (PID:5958): Archived Log entry 193 added for T-1.S-37 ID 0x1befd729 LAD:1
LOGSTDBY: (LSP1) LogMiner Dictionary Build Process Completed
----------

SYS@PRODUPG 19c > SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

DATABASE_ROLE    INSTANCE                       OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------------- ------------------------------ -------------------- -------------------- -------------------- --------------------
PRIMARY          PRODUPG                        READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  TO STANDBY

4. Post switchover check

Note: redo transport from new primary to new logical standby is set automatically.

SYS@PRODUPG 19c > select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          TO STANDBY              <<<<<<< LOG SWITCH GAP value can be here

Note: in case of any GAP we get the status as above. Issue couple of log switchover (alter database switch logfile) on new primary. after sometime the status changes to ‘TO STANDBY’.

SYS@PRODUPG 19c > ALTER SYSTEM SWITCH LOGFILE;

SYS@PRODUPG 19c > /

SYS@PRODUPG 19c > /

SYS@PRODUPG 19c > SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          TO STANDBY

SYS@PROD 12c > SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
LOGICAL STANDBY  NOT ALLOWED

Note: do not try to start logical apply on PROD database as for now. After rolling upgrade normal SQL Apply command doesn’t works. Ref. to ORA-16100: not a valid Logical Standby database (Doc ID 1537931.1) for solution, but we will take care about it later.

5. Set Correct Role for Standby on New Primary

SYS@PRODUPG 19c > ALTER SYSTEM SET log_archive_dest_3='SERVICE=PRODUPGS LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODUPGS';

System altered.

SYS@PRODUPG 19c > ALTER SYSTEM SWITCH LOGFILE;

System altered.

SYS@PRODUPGS 19c > @stby_lag_time.sql

##
## Time Lag:
##

NAME                           VALUE
------------------------------ ----------------------------------------
transport lag                  +00 00:00:00
apply lag                      +00 00:00:00
apply finish time              +00 00:00:00.000
estimated startup time         15

V. Update DNS record

Update CNAME record so users/applications can still use “prod-soe-db.test.com” name, but it will forward them on current primary database server.

Before:

>>> root@srv-apps-soe

$ grep -i cname /var/named/test.com.db
+++++++++++++++
;CNAME record
prod-soe-db IN CNAME srv-dc1-db1.test.com.
+++++++++++++++

After:

>>> root@srv-apps-soe

$ grep -i cname /var/named/test.com.db
+++++++++++++++
;CNAME record
prod-soe-db IN CNAME srv-dc1-db3.test.com.
+++++++++++++++

$ systemctl restart named

VI. Back former primary and standby to live

1. Flashback former primary to pre-upgrade restore point

SYS@PROD 12c > SHUTDOWN IMMEDIATE;

SYS@PROD 12c > STARTUP MOUNT;

SYS@PROD 12c > FLASHBACK DATABASE TO RESTORE POINT BEFORE_SOE_19C_UPGRADE;

Flashback complete.

2. Convert to former primary from Logical Standby to Physical Standby

SYS@PROD 12c > SELECT STATUS,INSTANCE_NAME,DATABASE_ROLE,OPEN_MODE FROM V$DATABASE,V$INSTANCE;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      PROD             LOGICAL STANDBY  MOUNTED

SYS@PROD 12c > ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SYS@PROD 12c > SELECT STATUS,INSTANCE_NAME,DATABASE_ROLE,OPEN_MODE FROM V$DATABASE,V$INSTANCE;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      PROD             PHYSICAL STANDBY MOUNTED

SYS@PROD 12c > SHUTDOWN IMMEDIATE;

3. Start from New 19c Home

$ oh12c
$ lsnrctl stop LIPROD

$ oh19c
$ lsnrctl start LIPROD

 [!!!!!!!!!] // Copy spfile to New 19c Home

SYS@PROD 19c > STARTUP MOUNT;

SYS@PROD 19c > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;

SYS@PRODUPG 19c > ALTER SYSTEM SET log_archive_dest_state_2=ENABLE;

SYS@PROD 19c > @stby_lag_time.sql

##
## Time Lag:
##

NAME                           VALUE
------------------------------ ----------------------------------------
transport lag                  +00 00:00:00
apply lag                      +00 22:48:05
apply finish time              +00 00:22:54.854
estimated startup time         50

SYS@PROD 19c /

NAME                           VALUE
------------------------------ ----------------------------------------
transport lag                  +00 00:00:00
apply lag                      +00 22:46:38
apply finish time              +00 00:20:58.948
estimated startup time         50

SYS@PROD 19c /

NAME                           VALUE
------------------------------ ----------------------------------------
transport lag                  +00 00:00:00
apply lag                      +00 00:00:00
apply finish time              +00 00:00:00.000
estimated startup time         50

Note: monitor alert log on PROD and wait for sync between New Primary(PRODUPG) and Former Primary(PROD).

SYS@PROD 19c > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SYS@PROD 19c > ALTER DATABASE OPEN READ ONLY;

SYS@PROD 19c > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;

VII. Back former main standby to live

1. Flashback to pre-upgrade restore point

SYS@PRODS 12c > SHUTDOWN IMMEDIATE;

SYS@PRODS 12c > STARTUP MOUNT;

SYS@PRODS 12c > FLASHBACK DATABASE TO RESTORE POINT BEFORE_SOE_19C_UPGRADE;

Flashback complete.

SYS@PRODS 12c > SHUTDOWN IMMEDIATE;

2. Start New Cascade Standby from New 19c Home

$ oh12c
$ lsnrctl stop LIPRODS

$ oh19c
$ lsnrctl start LIPRODS

 [!!!!!!!!!] // Copy spfile to New 19c Home

SYS@PRODS 19c > STARTUP MOUNT;

SYS@PRODS 19c > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;

SYS@PROD 19c > ALTER SYSTEM SET log_archive_dest_2='SERVICE=PRODS LGWR ASYNC NOAFFIRM VALID_FOR=(ALL_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=PRODS' SCOPE=BOTH;

SYS@PROD 19c > ALTER SYSTEM SET log_archive_dest_state_3=DEFER;

SYS@PROD 19c > ALTER SYSTEM SET log_archive_dest_state_2=ENABLE;

SYS@PRODS 19c > @stby_lag_time

##
## Time Lag:
##

NAME                           VALUE
------------------------------ ----------------------------------------
transport lag                  +00 00:00:00
apply lag                      +00 00:00:00
apply finish time              +00 00:00:00.000
estimated startup time         20

Note: monitor alert log on PRODS and wait for sync between Cascade Physical Standby (PRODS) and Physical Standby (PROD).

SYS@PRODS 19c > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SYS@PRODS 19c > ALTER DATABASE OPEN READ ONLY;

SYS@PRODS 19c > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;

VIII. Final Post-Steps

1. Check/Correct /etc/oratab

>>> oracle@srv-dc1-db1

$ cat /etc/oratab | grep -i PROD
+++++++++++++++
PROD:/u01/app/oracle/product/198000/dbhome_1:N
+++++++++++++++

>>> oracle@srv-dc2-db2

$ cat /etc/oratab | grep -i PRODS
+++++++++++++++
PRODS:/u01/app/oracle/product/198000/dbhome_1:N
+++++++++++++++

>>> oracle@srv-dc1-db3

$ cat /etc/oratab | grep -i PRODUPG
+++++++++++++++
PRODUPG:/u01/app/oracle/product/198000/dbhome_1:N
+++++++++++++++

>>> oracle@srv-dc2-db4

$ cat /etc/oratab | grep -i PRODUPGS
+++++++++++++++
PRODUPGS:/u01/app/oracle/product/198000/dbhome_1:N
+++++++++++++++

2. Correct сrontab scripts

Review and correct crontab scripts on each server if code inside uses path to ORACLE_HOME.

3. Delete Restore Point

Note: delete restore point on each database if everything if okay.

SYS@PROD/PRODS/PRODUPG/PRODUPGS > DROP RESTORE POINT BEFORE_SOE_19C_UPGRADE;

Appendix

Database Status

SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

Data Guard Parameters

SQL> SET LINES 400 PAGES 1000
COL NAME FOR A30
COL VALUE FOR A150
SELECT NAME ,VALUE
FROM V$SYSTEM_PARAMETER
WHERE NAME IN ('archive_lag_target','log_archive_config','log_archive_format','standby_file_management','fal_server','fal_client','db_file_name_convert','log_file_name_convert')
OR NAME IN (SELECT LOWER(DEST_NAME) FROM V$ARCHIVE_DEST_STATUS WHERE DESTINATION IS NOT NULL)
UNION ALL
SELECT NAME
,VALUE
FROM V$SYSTEM_PARAMETER
WHERE NAME IN (SELECT SUBSTR(NAME,1,17) || 'STATE' || SUBSTR(NAME,17)
FROM V$SYSTEM_PARAMETER2
WHERE NAME IN (SELECT LOWER(DEST_NAME) FROM V$ARCHIVE_DEST_STATUS WHERE DESTINATION IS NOT NULL))
ORDER BY NAME;

Logical Standby Useful Queries

Check Logical Standby Parameters
SQL> SET LINES 400 PAGES 1000
COL NAME FOR A50
COL VALUE FOR A50
COL UNIT FOR A20
COL SETTING FOR A20
COL DYNAMIC FOR A10
SELECT * FROM DBA_LOGSTDBY_PARAMETERS ORDER BY DYNAMIC DESC, NAME;
Start/Stop Logical Apply Process
-- Start --

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

-- Stop --

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Check LCR Cache for Paging
SQL> SELECT NAME, TO_NUMBER(VALUE) VALUE FROM V$LOGSTDBY_STATS WHERE NAME = 'bytes paged out';

Note: If the LCR Cache fills up, then SQL Apply has the ability to page the LCR Cache into a SPILL table that resides in the SYSAUX tablespace. SQL Apply paging is an EXTERMELY EXPENSIVE operation and paging of the LCR Cache should be avoided. If the query returns a non-zero value paging is occurring, you should run the query on a regular basis to attempt to identify if a particular transaction on the primary database is responsible for the paging. If the number of Bytes Paged Out is constantly increasing consider increasing the value of the MAX_SGA logical standby parameter.

Check if LCR Cache utilization
SQL> SELECT NAME, ( Least(max_sga, bytes) / max_sga ) * 100 PCT_UTILIZATION 
FROM (SELECT *
FROM V$SGASTAT
WHERE NAME = 'Logminer LCR c'),
(SELECT value * ( 1024 * 1024 ) MAX_SGA
FROM DBA_LOGSTDBY_PARAMETERS
WHERE NAME = 'MAX_SGA');

Note: If the LCR Cache is too large, then the instance will not be able to redeploy the reserved memory to other parts of the SGA including the buffer cache. To determine if the LCR Cache is too large, the peak size of the LCR Cache will be reported in the v$sgastat view.

Verify that log apply services on the standby are currently running
SQL> SET LINES 400 PAGES 1000
COL PID FOR A10
COL TYPE FOR A20
COL STATUS FOR A60
SELECT PID, TYPE, STATUS, HIGH_SCN FROM V$LOGSTDBY ORDER BY HIGH_SCN DESC NULLS LAST;
Check if log apply services is progressing
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SQL> SET LINES 400 PAGES 1000
COL APPLIED_TIME FOR A20
COL READ_TIME FOR A20
COL NEWEST_TIME FOR A20
SELECT APPLIED_SCN, APPLIED_TIME, READ_SCN, READ_TIME, NEWEST_SCN, NEWEST_TIME
FROM DBA_LOGSTDBY_PROGRESS;
Check GAP between Primary and Logical Standby
-- In Seconds --

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SQL> SET LINES 400 PAGES 1000
COL LATEST_TIME FOR A30
COL APPLIED_TIME FOR A30
COL MINING_TIME FOR A30
SELECT LATEST_TIME, APPLIED_TIME, MINING_TIME, (LATEST_TIME-APPLIED_TIME)*24*3600 GAP_IN_SECOND
FROM V$LOGSTDBY_PROGRESS;

-- In Archive Logs --

SQL> SET LINES 400 PAGES 1000
COL FILE_NAME FOR A75
SELECT SUBSTR(FILE_NAME,1,100) FILE_NAME, SUBSTR(SEQUENCE#,1,10) "SEQ#",
FIRST_CHANGE#, NEXT_CHANGE#, TO_CHAR(TIMESTAMP, 'HH:MI:SS') TIMESTAMP,
DICT_BEGIN BEG, DICT_END END, SUBSTR(THREAD#,1,4) "THR#"
FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;
Check if logical apply is receiving errors while performing apply operations
SQL> SET LINES 400 PAGES 1000
COL STATUS FOR A75
SELECT XIDUSN, XIDSLT, XIDSQN, STATUS, STATUS_CODE
FROM DBA_LOGSTDBY_EVENTS
WHERE EVENT_TIME =
(SELECT MAX(EVENT_TIME)
FROM DBA_LOGSTDBY_EVENTS);

Note: Log apply services cannot apply unsupported DML statements, DDL statements,
and Oracle supplied packages to a logical standby database in SQL apply
mode. When an unsupported statement or package is encountered, SQL apply
operations stop. To determine if SQL apply has stopped due to errors you
should query the DBA_LOGSTDBY_EVENTS view. When querying the view, select
the columns in order by EVENT_TIME. This ordering ensures that a shutdown
failure appears last in the view. For example:

  • If an error requiring database management occurred (such as adding a
    tablespace, datafile, or running out of space in a tablespace), then you
    can fix the problem manually and resume SQL apply.
  • If an error occurred because a SQL statement was entered incorrectly,
    conflicted with an existing object, or violated a constraint then enter the
    correct SQL statement and use the DBMS_LOGSTDBY.SKIP_TRANSACTION procedure
    to ensure that the incorrect statement is ignored the next time SQL apply
    operations are run.

Physical Standby Useful Queries

Stop/Start Media Recovery
-- Start --

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION USING CURRENT LOGFILE;

-- Stop --

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Check recovery and protection modes
SQL> SET LINES 400 PAGES 1000
COL DEST_NAME FOR A20
COL STATUS FOR A10
COL TYPE FOR A10
COL DATABASE_MODE FOR A20
COL RECOVERY_MODE FOR A25
COL PROTECTION_MODE FOR A20
COL DESTINATION FOR A30
SELECT DEST_NAME, STATUS, TYPE, DATABASE_MODE, RECOVERY_MODE, PROTECTION_MODE, DESTINATION FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=1;
Check if recovery is running
SQL> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY ORDER BY SEQUENCE# DESC;
Check for GAP between Primary and Physical Standby
>>> primary - Get last Primary Sequence Generated

SQL> SELECT THREAD#, MAX(SEQUENCE#) "LAST PRIMARY SEQ GENERATED"
FROM V$ARCHIVED_LOG
WHERE FIRST_TIME BETWEEN (SYSDATE-1) AND (SYSDATE+1)
GROUP BY THREAD# ORDER BY 1;

>>> standby - Compare with Standby

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
Check Time GAP between Primary and Physical Standby
SQL> SET LINES 400 PAGES 1000
COL NAME FOR A30
COL VALUE FOR A40
SELECT NAME, VALUE FROM V$DATAGUARD_STATS;

2 Comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s