preface

With the widespread application of Oracle database, DataGuard has basically become the standard configuration of database disaster recovery environment!

1, Introduction

When you need to upgrade the Oracle database, you also need to consider upgrading the DataGuard database version at the same time. How to upgrade quickly and safely?

Recommended scheme:

• Upgrade to NON-CDB mode:

First, close ADG synchronization, upgrade the main database to 19C, and open the standby database in mount mode under 19C to enable ADG synchronization.

• Upgrade to CDB mode:

1. First, upgrade the main database to 19C cdb+pdb mode, and rebuild the ADG for the standby database. 2. First, build a set of active and standby ADG environment of 19C CDB, turn off ADG synchronization, then upgrade the main database and insert it into the main database CDB to turn on ADG synchronization.

This paper mainly introduces CDB mode: first, upgrade the main database to 19C cdb+pdb mode, and rebuild ADG for the standby database.

2, Environmental preparation

The environmental installation process is ignored. Please refer to: Oracle database one click installation has never been so simple!

The test shall be simulated according to the production environment upgrade as far as possible:

Note: it is better to copy the source library to the new machine for cold standby upgrade, and keep the source library for fallback.

According to MOS document 2485457.1, you can obtain the download address of the latest version of AutoUpgrade tool:

The most recent version of AutoUpgrade can be downloaded via this link: version 20211115.

2, Upgrade master library

To upgrade the main library with AutoUpgrade tool, refer to: Practical part: AutoUpgrade can upgrade Oracle database conveniently and efficiently

1. Configuration config file

Edit the config file as follows:

cat<<EOF >/soft/conifg.cfg
# Global configurations
global.autoupg_log_dir=/soft/uplogs
global.raise_compatible=yes
global.drop_grp_after_upgrade=yes

# Database number 3 - Noncdb to PDB upgrade
upg3.log_dir=/soft/logs
upg3.sid=orcl
upg3.source_home=/u01/app/oracle/product/11.2.0/db
upg3.target_cdb=cdb19c
upg3.target_home=/u01/app/oracle/product/19.3.0/db
upg3.target_pdb_name=orcl
upg3.target_pdb_copy_option=file_name_convert=('/oradata/orcl/', '/oradata/CDB19C/orcl/')
upg3.start_time=NOW                  # Optional. 10 Minutes from now
upg3.upgrade_node=orcl           # Optional. To find out the name of your node, run the hostname utility. Default is 'localhost'
upg3.run_utlrp=yes              # Optional. Whether or not to run utlrp after upgrade
upg3.timezone_upg=yes           # Optional. Whether or not to run the timezone upgrade
upg3.target_version=19  # Oracle version of the target ORACLE_HOME.  Only required when the target Oracle database version is 12.2
upg3.remove_underscore_parameters=yes
upg3.source_tns_admin_dir=/u01/app/oracle/product/11.2.0/db/network/admin
upg3.target_tns_admin_dir=/u01/app/oracle/product/19.3.0/db/network/admin
EOF

2. Start upgrade

For specific upgrade commands, refer to: Practical part: AutoUpgrade can upgrade Oracle database conveniently and efficiently

So far, the main library has been upgraded!

3. Post upgrade check

Set pdb to start with cdb:

alter pluggable database all save state;

3, Build ADG

After upgrading the main database, start building ADG!

1. dbca create standby database

The standby database uses dbca to build ADG:

dbca -silent -createDuplicateDB \
-gdbName cdb19c \
-sid cdb19c \
-sysPassword oracle \
-primaryDBConnectionString 10.211.55.100:1521/cdb19c \
-nodelist orcl_stby \
-databaseConfigType SINGLE \
-createAsStandby -dbUniqueName cdb19c_stby \
-datafileDestination '/oradata'

2. Configure ADG parameters

Setting DG parameters for main library:

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(CDB19C,CDB19C_STBY)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB19C';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=CDB19C_stby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB19C_STBY';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER=CDB19C_STBY;
ALTER SYSTEM SET FAL_CLIENT=CDB19C;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/oradata/CDB19C','/oradata/CDB19C_STBY' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/oradata/CDB19C','/oradata/CDB19C_STBY'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Setting DG parameters for standby database:

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(CDB19C_STBY,CDB19C)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB19C_STBY';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=CDB19C ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB19C';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER=CDB19C;
ALTER SYSTEM SET FAL_CLIENT=CDB19C_STBY;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/oradata/CDB19C_STBY','/oradata/CDB19C' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/oradata/CDB19C_STBY','/oradata/CDB19C'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

3. Active and standby start synchronization

##Standby database execution
alter database recover managed standby database using current logfile disconnect from session;
    
##Main library execution
alter system set log_archive_dest_state_2=enable;

So far, ADG has been built.

4, Test after upgrade

Create test data from master library:

alter session set container=orcl;

sqlplus lucifer/lucifer@orcl

Check whether the standby database is synchronized:

After the upgrade, ADG synchronization is normal!

Reference article:

• How to Upgrade with AutoUpgrade and Data Guard
• Upgrade Database to 12.2 with Physical Standby