Oracle Database Rolling Upgrade From 11g to 19c Using a Data Guard with Minimum Downtime

Hi,
Database Rolling Upgrade is using Transient Logical Standby (SQL Apply) which allows you to run different database versions during rolling upgrade. And we can do this upgrade with minimum production downtime.

Test Environment:
We have primary and physical standby database running in 11.2.0.4. And we installed oracle 19.3 software binary on both primary and physical standby nodes.
primary: 192.168.5.2
standby: 192.168.5.3

You can find our scenario as below picture.

1) Let me check the physical standby gap status
SQL> SELECT thr, applied,
seq,
DECODE (
thr,
LAG (thr, 1, 0) OVER (ORDER BY thr),
seq – LAG (seq, 1, 0)OVER (ORDER BY seq),
0) lag
FROM ( select * from (SELECT thread# thr, val.applied, MAX (sequence#) seq
FROM v$archived_log val, v$database vdb
WHERE val.resetlogs_change# = vdb.resetlogs_change# and DELETED <> ‘YES’
GROUP BY thread#, applied)
ORDER BY 1, 2 DESC);

SQL>select process, status, thread#, sequence#, round(block#*512/1024/1024,2) CURRENT_REDO_MB, round(blocks*512/1024/1024,2) REDO_SIZE_MB from v$managed_standby order by process,group#,thread#,sequence#;

2) Stop the apply process.
sql> alter database recover managed standby database cancel;

3)Open the database in read only mode
sql>alter database open read only;

4) Check the flashback feature is on in both primary and standby databases
sql>select flashback_on from v$database;

5) Create guaranteed restore point on primary database
sql>create restore point before_upgrade guarantee flashback database;
select * from v$restore_point;

6) Start the apply process and wait for syncronisation completion.
alter database recover managed standby database dısconnect from sessıon;

7) Stop the apply process again.
sql> alter database recover managed standby database cancel;

8) Convert physical standby database to logical standby database.
sql>alter database close;
sql>alter database recover to logical standby keep identity;

9) Restart the standby database.
sql> shutdown immediate;
sql> startup;
sql>select db_unique_name,database_role from v$database;

10) Start the log apply process.
sql>alter database start logical standby apply immediate;

11) let me check if the data dictionary build is completed
sql>select * from v$logstdby_state where state = ‘LOADING DICTIONARY’;

12) Now, we can start the upgrade Logical standby database to Oracle 19.3
mkdir -p /home/oracle/logs
cd /home/oracle/logs
sqlplus / as sysdba
/u01/app/oracle/product/11.2/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.3/db_1/rdbms/admin/preupgrade.jar FILE TEXT DIR /home/oracle/logs

13) Shutdown logical standby database and copy listener.ora, tnsnames.ora to,spfile, password file new ORACLE_HOME (19.3).
SQL>shutdown immediate;
lsnrctl stop
export ORACLE_HOME=/u01/app/oracle/product/19.3/db_1
export path=$ORACLE_HOME/bin:$path
export ORACLE_SID=dbmaster
lsnrctl start
sqlplus / as sysdba
sql> startup upgrade;

14) Start the manual upgrade process.
cd $ORACLE_HOME/rdbms/admin
/u01/app/oracle/product/19.3/db_1/perl/bin/perl catctl.pl -n 4 -l /home/oracle/logs catupgrd.sql

15) After the Upgrade process completion, start the database.
lsnrctl start
sqlplus / as sysdba
SQL> startup;
select substr(comp_id,1,15) comp_id, substr(comp_name,1,30) comp_name, substr(version,1,10) version, status from dba_registry

16) We can start the log apply process on new upgraded logical standby database.
sql> alter database start logical standby apply;

17) Now we can stop the primary database and switchover the role.
On the 11.2.0.4 primary;
sql>alter database commit to switchover to logical standby;
On the 19.3 logical standby;
sql> alter database commit to switchover to primary;
sql> select db_unique_name,database_role from v$database;

18) Flashback database to restore point for converting to physical standby.
sql> shutdown immediate
sql> startup mount
sql> flashback database to restore point before_upgrade;
sql> alter database convert to physical standby;

19) Shutdown database and start physical standby database with new Oracle 19.3 and copy listener.ora, tnsnames.ora to,spfile, password file new ORACLE_HOME (19.3).
sql> shutdown immediate
lsnrctl stop
export ORACLE_HOME=/u01/app/oracle/product/19.3/db_1
export path=$ORACLE_HOME/bin:$path
export ORACLE_SID=dbmaster
lsnrctl start
sqlplus / as sysdba
sql> startup mount;
sql> select database_role from v$database;

20) Apply all the archived log.
sql> alter database recover managed standby database using current logfile through next switchover disconnect;

21) we can drop the restore point
drop restore point before_upgrade;

22) Set the compatible parameter to 19.3.
sql> alter system set compatible = ‘19.3.0.0.0’ scope=spfile;

Advertisement