By Brian Fitzgerald

Introduction

Oracle Database 12.1 was upgraded to version 19.3 using the AutoUpgrade utility and the Transient Logical Standby. Actual downtime was less than 10s.

News about AutoUpgrade

For news about AutoUpgrade, please refer Oracle product manager Mike Dietrich’s blog.

System Description

The system is Linux 7.7 on Amazon AWS EC2. Details appear in this table:

System Configuration

Initially, the system is a Data Guard network with a physical standby.

Non-default Database system configurations

Selected non-default instance parameters are:

Existing Data Guard 12c physical standby

We start this article with a working Data Guard physical standby with these issues already handled on the primary and the standby:

• network route
• static listeners
• archivelog
• flashback on
• forced logging
• 3×500 MB online redo log groups
• 4×500 MB standby redo log groups
• audit directory
• data guard broker
• tested switchover
• spfile moved to ASM
• tnsnames.ora in 12c oracle home
• Restart is setup
• oratab is configured and pointing to the 12c home
• srvctl tested

The standby spfile has been moved to ASM so that it will be unnecessary to move it after the upgrade, or a possible downgrade.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA01/XIENT/PARAMETERFILE/spfile.275.1021229119

Existing Data

Example table ordermessage has one row.

SQL> @ insert1.sql

1 row created.

SQL> commit;

Commit complete.
SQL> select ClOrdID from ordermessage order by TransactTime;

CLORDID
------------------------------
XL46W2NCVM22N02RQ2YK

Preparations

Careful preparation should be completed days ahead of the planned upgrade. AutoUpgrade Analyze and Fixups can be run and the results reviewed. The 19c oracle homes, including tnsnames.ora and orapw should be prepared for the upgrade.

Download AutoUpgrade

Download the latest autoupgrade.jar

Install to the 19c oracle home on primary and standby

[oracle@ip-172-31-91-148 dbhome_1]$ alias oh
alias oh='cd $ORACLE_HOME'
[oracle@ip-172-31-91-148 dbhome_1]$ oh
[oracle@ip-172-31-91-148 dbhome_1]$ find . -name autoupgrade.jar -ls
76988898 3616 -rw-r--r-- 1 oracle oinstall 3702360 Oct 7 20:35 ./rdbms/admin/autoupgrade.jar

Set this alias for convenience

[oracle@ip-172-31-91-148 dbhome_1]$ AH=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@ip-172-31-91-148 dbhome_1]$ alias au='rlwrap $AH/jdk/bin/java -jar $AH/rdbms/admin/autoupgrade.jar'

Use rlwrap for CLI readline editing and history recall.

Check the AutoUpgrade version:

[oracle@ip-172-31-91-148 dbhome_1]$ au -version
build.hash 67fee5b
build.version 20190823
build.date 2019/08/23 18:08:47

Source AutoUpgrade configuration file

The source configuration file is eqtrd.12c.19c.conf. Notice that target_home need not appear in the source configuration file.

global.autoupg_log_dir=/u01/app/oracle/autoupgrade

upg1.dbname=EQTRD
upg1.start_time=now
upg1.source_home=/u01/app/oracle/product/12.1.0/dbhome_1
upg1.sid=EQTRD
upg1.log_dir=/u01/app/oracle/autoupgrade
upg1.upgrade_node=ip-172-31-88-93.ec2.internal
upg1.target_version=19.3

Analyze

Use AutoUpgrade to analyze for issues and needed fixups.

If you scroll down, you will find a horizontal scrollbar. You may scroll right to view the right-most columns.

[oracle@ip-172-31-88-93 ~]$ au -config eqtrd.12c.19c.conf -mode analyze
AutoUpgrade tool launched with default options
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be analyzed
Type 'help' to list console commands
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|        MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
| 110|  EQTRD|PRECHECKS|PREPARING|RUNNING|19/10/11 14:38|     N/A|14:38:09|Loading DB info|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+--------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|       MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+--------------+
| 110|  EQTRD|PRECHECKS|PREPARING|RUNNING|19/10/11 14:38|     N/A|14:38:16|Remaining 6/72|
+----+-------+---------+---------+-------+--------------+--------+--------+--------------+
Total jobs 1
upg> Job 110 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished successfully     [1]
Jobs failed                    [0]
Jobs pending                   [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 110 FOR EQTRD

Fixups

Use AutoUpgrade to run Fixups:

[oracle@ip-172-31-88-93 ~]$ au -config eqtrd.12c.19c.conf -mode fixups
AutoUpgrade tool launched with default options
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be processed
Type 'help' to list console commands
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|        MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
| 111|  EQTRD|PRECHECKS|PREPARING|RUNNING|19/10/11 14:43|     N/A|14:43:28|Loading DB info|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+-------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|      MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+-------------+
| 111|  EQTRD|PREFIXUPS|EXECUTING|RUNNING|19/10/11 14:43|     N/A|14:43:42|Remaining 3/3|
+----+-------+---------+---------+-------+--------------+--------+--------+-------------+
Total jobs 1
upg> Job 111 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished successfully     [1]
Jobs failed                    [0]
Jobs pending                   [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 111 FOR EQTRD

Target AutoUpgrade configuration file

Prepare the target AutoUpgrade configuration file. Notice that dbname and sid are changed. upgrade_node must match the target hostname. The configuration file is xient.12c.19c.conf.

global.autoupg_log_dir=/u01/app/oracle/autoupgrade

upg1.dbname=XIENT
upg1.start_time=now
upg1.source_home=/u01/app/oracle/product/12.1.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/19.3.0/dbhome_1
upg1.sid=XIENT
upg1.log_dir=/u01/app/oracle/autoupgrade
upg1.upgrade_node=ip-172-31-91-148.ec2.internal
upg1.target_version=19.3

Prepare 19c oracle homes

Prepare the 19c oracle homes with the password file and tnsnames.ora. Primary:

[oracle@ip-172-31-88-93 ~]$ alias oh
alias oh='cd $ORACLE_HOME'
[oracle@ip-172-31-88-93 ~]$ oh
[oracle@ip-172-31-88-93 dbhome_1]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_1
[oracle@ip-172-31-88-93 dbhome_1]$ cp -p dbs/orapwEQTRD /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
[oracle@ip-172-31-88-93 dbhome_1]$ cp -p network/admin/tnsnames.ora /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/

standby:

[oracle@ip-172-31-91-148 ~]$ oh
[oracle@ip-172-31-91-148 dbhome_1]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_1
[oracle@ip-172-31-91-148 dbhome_1]$ cp -p dbs/orapwXIENT /u01/app/oracle/product/19.3.0/dbhome_1/dbs/ 
[oracle@ip-172-31-91-148 dbhome_1]$ cp -p network/admin/tnsnames.ora /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/

Create the Transient Logical Standby

For this exercise, I find it helpful to have four terminal windows open, two on the primary and two on the standby. One window per host is for tailing the alert log, and the other is for commands.

Data Guard configuration

Note the Data Guard configuration. At the standby:

SQL> select * from v$dataguard_config;

DB_UNIQUE_NAME  PARENT_DBUN     DEST_ROLE         CURRENT_SCN     CON_ID
--------------- --------------- ----------------- ----------- ----------
EQTRD           NONE            PRIMARY DATABASE      1868165          0
XIENT           EQTRD           PHYSICAL STANDBY      1868075          0

Disable the Data Guard broker configuration

We are not going to stop the Data Guard broker. Disabling the configuration freezes the current Data Guard system state.

DGMGRL> disable configuration
Disabled.

Standby is mounted

Note that the standby database is mounted:

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

Primary restore point

The primary restore point is required for the upgrade procedure later. Create it now:

SQL> create restore point upgrd1 guarantee flashback database;

Restore point created.

Cancel managed recovery

On the standby:

SQL> recover managed standby database cancel
Media recovery complete.

Create standby restore point

On the standby:

SQL> create restore point upgrd2 guarantee flashback database;

Restore point created.

Some DBAs create additional restore points at later steps.

Create the logical standby

For this step, it is helpful to watch two windows.

On the standby:

SQL> alter database recover to logical standby keep identity;

The session hangs …

On the primary:

SQL> exec dbms_logstdby.build;

PL/SQL procedure successfully completed.

SQL>

dbms_logstdby.build runs in approximately three seconds. Three more seconds later, the alter database finishes and the sqlplus prompt appears.

Database altered.

SQL>

Run standby apply

On the standby:

SQL> alter database open;

Database altered.

SQL> alter database start logical standby apply immediate;

Database altered.

SQL> select state from v$logstdby_state;

STATE
----------------------------------------------------------------
INITIALIZING

SQL> select state from v$logstdby_state;

STATE
----------------------------------------------------------------
INITIALIZING

SQL> select state from v$logstdby_state;

STATE
----------------------------------------------------------------
LOADING DICTIONARY

SQL> select state from v$logstdby_state;

STATE
----------------------------------------------------------------
LOADING DICTIONARY

SQL> select state from v$logstdby_state;

STATE
----------------------------------------------------------------
LOADING DICTIONARY

SQL> select state from v$logstdby_state;

STATE
----------------------------------------------------------------
APPLYING

SQL> select state from v$logstdby_state;

STATE
----------------------------------------------------------------
IDLE

SQL>

Standby log miner dictionary build may hang at INITIALIZING for more than one minute. It could take up to 5 minutes to reach IDLE.

Foreign archivelogs

Archivelogs identified as “foreign” may appear at the standby

Wed Oct 16 02:21:23 2019
LOGMINER: End mining logfile for session 1 thread 1 sequence 33, +RECO01/XIENT/foreign_archivelog/EQTRD/2019_10_16/thread_1_seq_33.316.1021774867

Stop standby apply

When logical standby state reaches IDLE, stop standby apply.

On the primary:

SQL> alter system set log_archive_dest_state_2=defer scope=memory;

System altered.

From this point until the catchup, the primary continues to operate normally with users connected. Archived logs at the primary contain transactions that will be replayed at the standby later.

On the standby:

SQL> alter database stop logical standby apply;

Database altered.

Shutdown 12c

On the standby:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Upgrade

Upgrade the standby to 19c.

Setup the shell environment

Set the 19c environment.

On the standby:

[oracle@ip-172-31-91-148 ~]$ unset ORACLE_SID
[oracle@ip-172-31-91-148 ~]$ . oraenv
ORACLE_SID = [oracle] ?
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.3.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ip-172-31-91-148 ~]$ export ORACLE_SID=XIENT

Upgrade the Restart

[oracle@ip-172-31-91-148 ~]$ srvctl upgrade database -database XIENT -oraclehome /u01/app/oracle/product/19.3.0/dbhome_1

startup upgrade

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1577054672 bytes
Fixed Size                  8896976 bytes
Variable Size             385875968 bytes
Database Buffers         1174405120 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

Check the status. The following output is expected:

SQL> select logins, status from v$instance;

LOGINS     STATUS
---------- ------------
RESTRICTED OPEN MIGRATE

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

oratab

Note that the Oracle Agent has updated the Oracle home in oratab.

[oracle@ip-172-31-91-148 ~]$ grep XIENT /etc/oratab
XIENT:/u01/app/oracle/product/19.3.0/dbhome_1:N         # line added by Agent

Start screen

If you lose your session during the upgrade, you are going to lose direct control over the upgrade. To recover, you would need to search your global.autoupg_log_dir for the state and try to figure out how to recover, if necessary.

Protect your session with screen.

[oracle@ip-172-31-91-148 ~]$ screen

If you get disconnected, reconnect to screen:

[oracle@ip-172-31-91-148 ~]$ screen -r

You can detach from screen with ctrl-a-d.

Run AutoUpgrade

Use AutoUpgrade to upgrade the standby database.

On the standby, in screen:

[oracle@ip-172-31-91-148 ~]$ AH=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@ip-172-31-91-148 ~]$ alias au='rlwrap $AH/jdk/bin/java -jar $AH/rdbms/admin/autoupgrade.jar'
[oracle@ip-172-31-91-148 ~]$ au -config xient.12c.19c.conf -mode upgrade
AutoUpgrade tool launched with default options
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be processed
Type 'help' to list console commands
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|    MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
| 105|  XIENT|DBUPGRADE|EXECUTING|RUNNING|19/10/12 02:47|     N/A|02:48:05|0%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 105|  XIENT|DBUPGRADE|EXECUTING|RUNNING|19/10/12 02:47|     N/A|02:51:08|12%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 105|  XIENT|DBUPGRADE|EXECUTING|RUNNING|19/10/12 02:47|     N/A|03:00:14|49%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 105|  XIENT|DBUPGRADE|EXECUTING|RUNNING|19/10/12 02:47|     N/A|03:09:21|92%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|    MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
| 105|  XIENT|DBUPGRADE|EXECUTING|RUNNING|19/10/12 02:47|     N/A|03:12:52|0%Compiled |
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 105|  XIENT|DBUPGRADE|EXECUTING|RUNNING|19/10/12 02:47|     N/A|03:15:56|80%Compiled |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+--------+--------------+--------+--------+-------------------+
|Job#|DB_NAME|    STAGE|OPERATION|  STATUS|    START_TIME|END_TIME| UPDATED|            MESSAGE|
+----+-------+---------+---------+--------+--------------+--------+--------+-------------------+
| 105|  XIENT|DBUPGRADE|EXECUTING|FINISHED|19/10/12 02:47|     N/A|03:18:21|Restarting Database|
+----+-------+---------+---------+--------+--------------+--------+--------+-------------------+
Total jobs 1
upg> lsj
+----+-------+----------+---------+-------+--------------+--------+--------+-------------+
|Job#|DB_NAME|     STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|      MESSAGE|
+----+-------+----------+---------+-------+--------------+--------+--------+-------------+
| 105|  XIENT|POSTFIXUPS|EXECUTING|RUNNING|19/10/12 02:47|     N/A|03:22:21|Remaining 1/3|
+----+-------+----------+---------+-------+--------------+--------+--------+-------------+
Total jobs 1
upg> Job 105 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished successfully     [1]
Jobs failed                    [0]
Jobs pending                   [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 105 FOR XIENT

Exit screen

You may exit screen now. To exit screen, exit your shell.

Update the static listener

In the grid account, edit listener.ora. You can do this while you are waiting for the upgrade to finish.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = XIENT)
    )
  )

Reload the listener

[grid@ip-172-31-91-148 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 12-OCT-2019 02:26:43

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-172-31-91-148.ec2.internal)(PORT=1521)))
The command completed successfully

Check the listener status

[grid@ip-172-31-91-148 admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 12-OCT-2019 02:26:48

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-172-31-91-148.ec2.internal)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                11-OCT-2019 13:17:23
Uptime                    0 days 13 hr. 9 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19.3.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/ip-172-31-91-148/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-172-31-91-148.ec2.internal)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA01" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO01" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "XIENT" has 1 instance(s).
  Instance "XIENT", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Continued operations on the 12c primary

Meanwhile, on primary, which is still on 12c, users may continue to modify data:

SQL> select banner from v$version where banner like 'Oracle Database%';

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> @ insert2.sql

1 row created.

SQL> commit;

Commit complete.

SQL> select ClOrdID from ordermessage order by TransactTime;

CLORDID
------------------------------
XL46W2NCVM22N02RQ2YK
DMYD9FS3FD3CYO77CU4R

Catch up the upgraded standby

Initial state

The standby is open and upgraded, but still lacks application data updates that were made to the primary during the upgrade.

SQL> select banner from v$version where banner like 'Oracle Database%';

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> select ClOrdID from ordermessage order by TransactTime;

CLORDID
------------------------------
XL46W2NCVM22N02RQ2YK

With only one row, table ordermessage is out of date.

Resume transport

Resume transport from the primary to the standby:

Primary:

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

Standby:

SQL> alter database start logical standby apply immediate;

Database altered.

Check for catchup on the standby:

Primary:

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1964941

Standby:

SQL> select applied_scn, latest_scn, mining_scn from v$logstdby_progress;

APPLIED_SCN LATEST_SCN MINING_SCN
----------- ---------- ----------
1964938        1964946    1964944

Check switchover status.

On the primary:

SQL> select database_role, switchover_status from v$database;

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

Until now, the application was operating normally on the original primary. Primary application data is now caught up on the original standby.

Check the application data on the standby:

SQL> select ClOrdID from ordermessage order by TransactTime;

CLORDID
------------------------------
XL46W2NCVM22N02RQ2YK
DMYD9FS3FD3CYO77CU4R

The standby data is up to date!

Now it is time to quiesce the application. Otherwise, changes could be lost.

Switchover

We’re going to switchover the application to the Transient Logical Standby. This is the actual outage. Get your stopwatch ready!

Original primary:

SQL> alter database commit to switchover to logical standby;

Database altered.

Start your stopwatch!

alter database commit to switchover to logical standby could run for 4s.

Original standby:

SQL> select database_role, switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
LOGICAL STANDBY  TO PRIMARY
SQL> alter database commit to switchover to logical primary;

Database altered.

alter database commit to switchover to logical primary could run for 15s or more.

Stop!

The first outage is over. Users may connect to the new logical primary.

Flashback the original primary

The original primary is going to become the physical standby.

SQL> startup force mount
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2924832 bytes
Variable Size             503320288 bytes
Database Buffers         1056964608 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL> flashback database to restore point upgrd1;

Flashback complete.

Convert the original primary to physical standby

SQL> alter database convert to physical standby;

Database altered.

Shut down original primary

SQL> shutdown abort
ORACLE instance shut down.

Environment

Set the 19c environment:

[oracle@ip-172-31-88-93 ~]$ unset ORACLE_SID
[oracle@ip-172-31-88-93 ~]$ . oraenv
ORACLE_SID = [oracle] ?
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.3.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ip-172-31-88-93 ~]$ ORACLE_SID=EQTRD

Upgrade original primary Restart

 [oracle@ip-172-31-88-93 ~]$ srvctl upgrade database -database EQTRD -oraclehome /u01/app/oracle/product/19.3.0/dbhome_1

startup mount

Mount the original primary.

[oracle@ip-172-31-88-93 ~]$ srvctl start database -database EQTRD -startoption MOUNT

Check oratab

Notice that the Oracle Agent updated the Oracle Home in oratab

[oracle@ip-172-31-88-93 ~]$ grep EQTRD /etc/oratab
EQTRD:/u01/app/oracle/product/19.3.0/dbhome_1:N         # line added by Agent

Static listener

Edit the static listener. In the grid account on the original primary:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = EQTRD)
    )
  )

Reload the listener

[grid@ip-172-31-91-148 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-OCT-2019 00:32:52

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-172-31-91-148.ec2.internal)(PORT=1521)))
The command completed successfully

Check the listener status

[grid@ip-172-31-88-93 admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-OCT-2019 03:56:08

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-172-31-88-93.ec2.internal)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                16-OCT-2019 00:25:28
Uptime                    0 days 3 hr. 30 min. 40 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19.3.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/ip-172-31-88-93/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-172-31-88-93.ec2.internal)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA01" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO01" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "EQTRD" has 2 instance(s).
  Instance "EQTRD", status UNKNOWN, has 1 handler(s) for this service...
  Instance "EQTRD", status READY, has 1 handler(s) for this service...
The command completed successfully

Enable the Data Guard configuration

Connect to the Transient Logical Standby:

[oracle@ip-172-31-91-148 ~]$ dgmgrl sys/Zystm.22@XIENT
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Oct 12 05:17:06 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "XIENT"
Connected as SYSDBA.

DGMGRL> enable configuration
Enabled.

Data Guard broker is going to detect that XIENT is the new primary. Data Guard broker will start log transport to EQTRD, and will start managed standby on EQTRD. Wait 5 to 10 minutes until recovery at the original primary is caught up.

A message such as the following should appear in the original primary alert log:

2019-10-17T02:24:01.118981+00:00
PR00 (PID:9485): Media Recovery Waiting for T-1.S-31 (in transit)

Check the Data Guard configuration

DGMGRL> show configuration

Configuration - ORCL_CONFIG

  Protection Mode: MaxPerformance
  Members:
  XIENT - Primary database
    EQTRD - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 20 seconds ago)

Errors

The following errors should not appear if no steps were missed.

ORA-1033: ORACLE initialization or shutdown in progress

DGMGRL> add database 'EQTRD' as connect identifier is 'EQTRD' maintained as physical;
Error: ORA-1033: ORACLE initialization or shutdown in progress

Failed.

There could be a password file issue. Copy the 12c orapw to the 19c home.

On the original primary:

[oracle@ip-172-31-88-93 ~]$ cp -p /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwEQTRD /u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwEQTRD

Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

DGMGRL> add database 'EQTRD' as connect identifier is 'EQTRD' maintained as physical;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Solution: clear log_archive_dest_2.

On the original primary:

SQL> alter system set log_archive_dest_2='';

System altered.

Switchover to the original primary

The next step is going to result in another application outage.

DGMGRL> switchover to EQTRD
Performing switchover NOW, please wait...
Operation requires a connection to database "EQTRD"
Connecting ...
Connected to "EQTRD"
Connected as SYSDBA.
New primary database "EQTRD" is opening...
Oracle Clusterware is restarting database "XIENT" ...
Connected to an idle instance.
Connected to an idle instance.
Connected to "XIENT"
Connected to "XIENT"
Switchover succeeded, new primary is "eqtrd"

The switchover completed in less than 40s.

Flashback to restore point

This section contains backout procedures that you can use on the primary or the standby.

Remove or disable Data Guard

You can try to flashback the primary and the standby and resume Data Guard. If you do, you may notice an error such as this one:

In the broker:

ERROR ORA-16700: the standby database has diverged from the primary database

In the standby alert log:

Thu Oct 17 03:53:40 2019
Errors in file /u01/app/oracle/diag/rdbms/xient/XIENT/trace/XIENT_mrp0_3527.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+DATA01/XIENT/DATAFILE/system.271.1021840189'

For the sake of demonstration, we will remove Data Guard and then flashback and open either the primary or the standby, but not both.

Remove the Data Guard broker configuration.

DGMGRL> disable configuration
Disabled.
DGMGRL> remove configuration
Removed configuration
DGMGRL>

Stop the brokers and disable transport.

On the primary:

SQL> alter system set log_archive_dest_2='';

System altered.

SQL> alter system set dg_broker_start=false;

System altered.

On the standby:

SQL> alter system set dg_broker_start=false;

System altered.

Flashback the primary

SQL> startup force mount
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2924832 bytes
Variable Size             503320288 bytes
Database Buffers         1056964608 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL> flashback database to restore point upgrd1;

Flashback complete.

Clear parameters that are invalid in 12.1. Shut down abort.

SQL> alter system reset "__unified_pga_pool_size" sid = 'EQTRD' scope=spfile;

System altered

SQL> shutdown abort
ORACLE instance shut down.

Downgrade Restart.

[oracle@ip-172-31-88-93 ~]$ srvctl downgrade database -database EQTRD -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 -targetversion 12.1.0.2

Set the 12c environment.

[oracle@ip-172-31-88-93 ~]$ unset ORACLE_SID
[oracle@ip-172-31-88-93 ~]$ . oraenv
ORACLE_SID = [oracle] ?
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ip-172-31-88-93 ~]$ ORACLE_SID=EQTRD

Startup mount and open resetlogs.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2924832 bytes
Variable Size             503320288 bytes
Database Buffers         1056964608 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

Notice that the Agent has updated oratab to 12c.

[oracle@ip-172-31-88-93 ~]$ grep EQTRD /etc/oratab
EQTRD:/u01/app/oracle/product/12.1.0/dbhome_1:N         # line added by Agent

Check the application data. Changes that were made during the upgrade have been lost.

SQL> select ClOrdID from ordermessage order by TransactTime;

CLORDID
------------------------------
XL46W2NCVM22N02RQ2YK

Rollback of primary to 12c is done.

Flashback the standby

As an alternative, flashback the standby.

SQL> recover managed standby database cancel
Media recovery complete.
SQL> flashback database to restore point upgrd2;

Flashback complete.

SQL> alter database activate physical standby database;

Database altered.

Reset parameters that are invalid in 12.1. Shut down abort.

SQL> alter system reset "__unified_pga_pool_size" sid = 'XIENT' scope=spfile;

System altered.

SQL> shutdown abort
ORACLE instance shut down.

Downgrade Restart.

[oracle@ip-172-31-91-148 ~]$ srvctl downgrade database -database XIENT -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 -targetversion 12.1.0.2

Set the 12c environment. Startup.

[oracle@ip-172-31-91-148 ~]$ unset ORACLE_SID
[oracle@ip-172-31-91-148 ~]$ . oraenv
ORACLE_SID = [oracle] ?
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ip-172-31-91-148 ~]$ export ORACLE_SID=XIENT

SQL> startup
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2924832 bytes
Variable Size             503320288 bytes
Database Buffers         1056964608 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

Notice that the Agent has updated oratab.

[oracle@ip-172-31-91-148 ~]$ grep XIENT /etc/oratab
XIENT:/u01/app/oracle/product/12.1.0/dbhome_1:N         # line added by Agent

Check the application data.

SQL> select ClOrdID from ordermessage order by TransactTime;

CLORDID
------------------------------
XL46W2NCVM22N02RQ2YK

Conclusion to flashbacks

This section was an outline of two possible, independent rollback procedures. Flashback will result in data loss. Further refinement is required.

Prior to beginning, we had moved spfile to ASM.

This concludes the section on flashback.

Final steps

If there are no problems with the upgrade, continue.

Drop the restore point

Primary:

SQL> drop restore point UPGRD1;

Restore point dropped.

Standby:

SQL> drop restore point UPGRD2;

Restore point dropped.

Update compatible

Primary:

SQL> alter system set compatible = '19.3.0.0.0' scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2924832 bytes
Variable Size             503320288 bytes
Database Buffers         1056964608 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

Standby:

SQL> alter system set compatible = '19.3.0.0.0' scope=spfile;

System altered.

SQL> startup force mount
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2924832 bytes
Variable Size             503320288 bytes
Database Buffers         1056964608 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL

compatible error

You cannot change compatibility if a guaranteed restore point exists.

SQL> alter system set compatible = '19.3.0.0.0' scope=spfile;

System altered.

SQL> startup force mount
ORACLE instance started.

Total System Global Area 1577054672 bytes
Fixed Size                  8896976 bytes
Variable Size             536870912 bytes
Database Buffers         1023410176 bytes
Redo Buffers                7876608 bytes
ORA-38880: Cannot advance compatibility from 12.1.0.2.0 to 19.3.0.0.0 due to
guaranteed restore points

Solutions:

1. Set compatible to its original value.

   SQL> alter system set compatible = '12.1.0.2.0' scope=spfile;
   
   System altered.
   
   SQL> startup force mount
   ORACLE instance started.
   
   Total System Global Area 1577054672 bytes
   Fixed Size                  8896976 bytes
   Variable Size             536870912 bytes
   Database Buffers         1023410176 bytes
   Redo Buffers                7876608 bytes
   Database mounted.
   SQL>
   

   or:

2. Drop the restore point.

Delete foreign archive logs

RMAN> list foreign archivelog all;

RMAN> delete noprompt foreign archivelog all;

Conclusion

Oracle Database was upgraded from version 12.1 to version 19.3 using AutoUpgrade and Transient Logical Standby. The actual application outage was 10 seconds.

Notes:

• The initial state was a working Data Guard system with a physical standby.
• Oracle Customer offers rolling upgrade script physru.
• In case of a problem with the upgrade, it is possible to flashback to a point in time before the upgrade.
• This is a zero data loss upgrade.
• There are two brief outages.
• The rollback is not fully developed in this article.
• As described here, downgrade to 12c results in data loss.