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.
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.
Fixups
Use AutoUpgrade to run Fixups:
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:
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:
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
Continued operations on the 12c primary
Meanwhile, on primary, which is still on 12c, users may continue to modify data:
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.
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
Enable the Data Guard configuration
Connect to the Transient Logical Standby:
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
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.
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
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.
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.
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:
Standby:
compatible error
You cannot change compatibility if a guaranteed restore point exists.
Solutions:
- Set compatible to its original value.
or:
- 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.
