Dataguard Rolling Upgrade using transient logical standby

Dataguard Rolling Upgrade using transient logical standby

I was successfully able to complete rolling upgrade using Dataguard for one of our environment and application downtime was reduced to just 2 minutes from 4 hours. Below are the high level steps performed and case study for this rolling database upgrade.

Current Dataguard configuration looks like as below:
Primary database: rec2p455 running on server dgtest02.
Physical standby database: rec3p455 running on server dgtest03.


1. Review prerequisites and best practices from Dataguard Rolling upgrade white paper.
a. Enable flashback and created a guaranteed restore point on each databases for flashaback purpose
b. Install upgraded ORACLE_HOME 11.2.0.4 on each servers dgtest02 and dgtest03 servers.
c. Make sure that your physical standby database is configured correctly to support switchover operations. Below parameters should be configured correctly on these databases -

On primary rec2p455 -
alter system set db_unique_name='rec2p455' scope=spfile sid='*';
alter system set log_archive_config = 'DG_CONFIG=(rec2p455,rec3p455)' scope=both sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';
alter system set db_file_name_convert='/oracle/data/ora01/rec2p455/','/oracle/data/ora01/rec3p455/','/oracle/data/ora01/rec3p455/','/oracle/data/ora01/rec2p455/','/oracle/data/ora02/rec2p455/','/oracle/data/ora02/rec3p455/','/oracle/data/ora02/rec3p455/','/oracle/data/ora02/rec2p455/','/oracle/data/ora03/rec2p455/','/oracle/data/ora03/rec3p455/','/oracle/data/ora03/rec3p455/','/oracle/data/ora03/rec2p455/','/oracle/data/ora04/rec2p455/','/oracle/data/ora04/rec3p455/','/oracle/data/ora04/rec3p455/','/oracle/data/ora04/rec2p455/' scope=spfile sid='*';
alter system set log_file_name_convert='/oracle/data/redo01/rec2p455/','/oracle/data/ora01/rec3p455/','/oracle/data/ora01/rec3p455/','/oracle/data/redo01/rec2p455/','/oracle/data/redo02/rec2p455/','/oracle/data/ora02/rec3p455/','/oracle/data/ora02/rec3p455/','/oracle/data/redo02/rec2p455/' scope=spfile sid='*';
alter system set log_archive_dest_2='SERVICE=rec3p455 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=rec3p455' scope=spfile sid='*';
alter system set log_archive_dest_state_2=ENABLE scope=spfile;
alter system set fal_server='rec3p455' scope=spfile sid='*';
alter system set fal_client='rec2p455' scope=spfile sid='*';
On standby rec3p455:
alter system set db_unique_name='rec3p455' scope=spfile sid='*';
alter system set log_archive_config = 'DG_CONFIG=(rec2p455,rec3p455)' scope=both sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';
alter system set db_file_name_convert='/oracle/data/ora01/rec2p455/','/oracle/data/ora01/rec3p455/','/oracle/data/ora01/rec3p455/','/oracle/data/ora01/rec2p455/','/oracle/data/ora02/rec2p455/','/oracle/data/ora02/rec3p455/','/oracle/data/ora02/rec3p455/','/oracle/data/ora02/rec2p455/','/oracle/data/ora03/rec2p455/','/oracle/data/ora03/rec3p455/','/oracle/data/ora03/rec3p455/','/oracle/data/ora03/rec2p455/','/oracle/data/ora04/rec2p455/','/oracle/data/ora04/rec3p455/','/oracle/data/ora04/rec3p455/','/oracle/data/ora04/rec2p455/' scope=spfile sid='*';
alter system set log_file_name_convert='/oracle/data/redo01/rec2p455/','/oracle/data/ora01/rec3p455/','/oracle/data/ora01/rec3p455/','/oracle/data/redo01/rec2p455/','/oracle/data/redo02/rec2p455/','/oracle/data/ora02/rec3p455/','/oracle/data/ora02/rec3p455/','/oracle/data/redo02/rec2p455/' scope=spfile sid='*';
alter system set log_archive_dest_2='SERVICE=rec2p455 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=re2p455' scope=spfile sid='*';
alter system set log_archive_dest_state_2=ENABLE scope=spfile;
alter system set fal_server='rec2p455' scope=spfile sid='*';

alter system set fal_client='rec3p455' scope=spfile sid='*';
2. Convert the existing Physical Standby rec3p455 to a Logical Standby using the keep identity feature which doesn't require database name to be changed. While logical standby build is going on, Applications are still conected to primary database rec2p455 on
icmfg74pharma server and performing usual transactions as below -
SQL> conn userid@rec2p455_dg
Enter password:
Connected.
SQL>
create table tmp as select 1 dummy from dual;
table created.

insert into tmp as select * from tmp;
1 row inserted;
/
2 rows inserted;
/
4 rows inserted;
/
8 rows inserted;
/
16 rows inserted;
/
32 rows inserted;
/
128 rows inserted;
commit;
256 rows inserted.
commit;

SQL> select count(1) from tmp;

  COUNT(1)
----------
       256

On Primary rec2p455 -
---------------------------------------------------------------
CREATE RESTORE POINT PRE_UPGRADE_1 GUARANTEE FLASHBACK DATABASE;
EXECUTE DBMS_LOGSTDBY.BUILD;

On standby database rec3p455 for converting it to logical from physical -

CREATE RESTORE POINT PRE_UPGRADE_2 GUARANTEE FLASHBACK DATABASE; -

ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY;

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'FALSE');

CREATE RESTORE POINT PRE_UPGRADE_3 GUARANTEE FLASHBACK DATABASE;

3. Perform the upgrade on the logical standby rec3p455 following the upgrade process as per oracle documentation upgrade guide as you do in any normal database and start the apply process so that logical standby is completely synchronized with the primary database rec2p455. While upgrade is going on, primary database rec2p455 is still available and applications can query and perform transactions.

Database mounted.
Database opened.
> @$ORACLE_HOME/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool           08-29-2014 17:17:34
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.4.0  00:09:44
Final Actions
.                                                                00:00:10
Total Upgrade Time: 00:09:55

PL/SQL procedure successfully completed.

SQL> select * from dba_registry_history;

ACTION_TIME                                                                 ACTION                      NAMESPACE                         VERSION                                ID BUNDLE_SERIES           COMMENTS
--------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ---------- ------------------------------ -----------------------------
22-SEP-10 03.42.04.503870 PM                                                APPLY                       SERVER                    11.1.0.7                                3 CPU                            CPUOct2009
22-SEP-10 03.42.45.067417 PM                                                CPU                                                                                     6452863                                view recompilation
20-MAR-13 08.50.32.513178 PM                                                APPLY                       SERVER                    11.1.0.7                               13 PSU                            PSU 11.1.0.7.13
19-JUL-13 05.20.52.017167 PM                                                VIEW INVALIDATE                                                                         8289601                                view invalidation
19-JUL-13 05.21.00.848183 PM                                                UPGRADE                     SERVER                    11.2.0.3.0                                                               Upgraded from 11.1.0.7.0
19-JUL-13 05.26.58.688228 PM                                                APPLY                       SERVER                    11.2.0.3                                4 PSU                            PSU 11.2.0.3.4
20-NOV-13 05.46.43.353854 PM                                                APPLY                       SERVER                    11.2.0.3                                7 PSU                            PSU 11.2.0.3.7
29-AUG-14 05.16.13.596405 PM                                                VIEW INVALIDATE                                                                         8289601                                view invalidation
29-AUG-14 05.16.23.809173 PM                                                UPGRADE                     SERVER                    11.2.0.4.0                                                               Upgraded from 11.2.0.3.0
29-AUG-14 05.18.10.822624 PM                                                APPLY



4. Switchover the database role so that existing logical standby database rec3p455 becomes primary while rec2p455 becomes logical standby database. At this point downtime for application is required as switchover to logical standby database doesn't require database to be started so application connections will not failover even if you are using service to support transparent application failover. In my case switchover was done in less than a minute and sometimes it can be more than a minute as it takes additional time in applying the redo gap if any to database being converted to primary. Downtime can be arranged with application team for rolling upgrade accordingly. I would say 5-10 minutes would be sufficient.

On Primary rec3p455 -

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

On Standby  rec2p455
SELECT SWITCHOVER_STATUS FROM V$DATABASE;

ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL PRIMARY;


5. Flashback the original primary rec2p455 (now a logical standby) to the guaranteed restore
point from #1
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT PRE_UPGRADE_1;

6. Remount the original primary (now a logical standby) under the new Oracle Home.
shut down database;
a.) change oracle home in oratab and listener.ora.
b.) reload listener.ora
c.) create symbolic links to spfile and pwfile to $ORACLE_HOME/dbs as applicable if you are storing these files in another location otherwise simply copy the files from older home to this location
d.) startup mount;

7. Convert the logical standby rec2p455 back to a physical standby and resynchronize with the
new primary, automatically completing the upgrade process.
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
shut immediate;
startup mount;
RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; ( Before starting the managed recovery, make sure that standby destination is enabled on new primary database rec3p455).


8. Wait for physical standby to be upgraded via the redo stream. In alert log you can see that new incarnation for the the original primary(now standby) will be registered -

RFS[6]: Selected log 7 for thread 1 sequence 21 dbid 829525374 branch 856888795
Archived Log entry 4334 added for thread 1 sequence 20 rlc 856888795 ID 0x32798a25 dest 2:
Archived Log entry 4335 added for thread 1 sequence 19 rlc 856888795 ID 0x32798a25 dest 2:
Fri Aug 29 17:57:49 2014
 started logmerger process
Fri Aug 29 17:57:49 2014
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 4 slaves
Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 12111287417698
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 /oracle/data/redo01/rec2p455/ora_log_01_01.rdo
Clearing online log 1 of thread 1 sequence number 3924
RFS[5]: Opened log for thread 1 sequence 3913 dbid 829525374 branch 838060608
Fri Aug 29 17:57:49 2014
Archived Log entry 4336 added for thread 1 sequence 21 ID 0x32798a25 dest 1:
Archived Log entry 4337 added for thread 1 sequence 3913 rlc 838060608 ID 0x32784362 dest 2:
Fri Aug 29 17:57:50 2014
Completed: alter database recover managed standby database disconnect from session
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /oracle/data/redo01/rec2p455/ora_log_02_01.rdo
Clearing online log 2 of thread 1 sequence number 3925
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /oracle/data/redo01/rec2p455/ora_log_03_01.rdo
Clearing online log 3 of thread 1 sequence number 3923
Clearing online redo logfile 3 complete
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_3911_838060608.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_3912_838060608.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_3913_838060608.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_1_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_2_856888795.arc
Fri Aug 29 17:58:00 2014
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_3_856888795.arc
Fri Aug 29 17:58:12 2014
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_4_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_5_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_6_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_7_856888795.arc
Fri Aug 29 17:58:26 2014
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_8_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_9_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_10_856888795.arc
Fri Aug 29 17:58:38 2014
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_11_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_12_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_13_856888795.arc
Fri Aug 29 17:58:48 2014
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_14_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_15_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_16_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_17_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_18_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_19_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_20_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_21_856888795.arc
Media Recovery Waiting for thread 1 sequence 22 (in transit)
Fri Aug 29 18:00:53 2014

9. Switch back to the original configuration (optional). This can be done later whenever applications are ready for another downtime of 2 minutes. Since this time switchover is required between primary and physical standby databases which required database remount, so application connections will also failover automatically to new primary database if they are connecting to database service supporting transparent application failover. All select queries will continue to execute but any transactions executed during the time when switchover is happening, needs to be re-executed.

On current primary rec3p455 -
SELECT SWITCHOVER_STATUS FROM V$DATABASE;

ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

On  standby rec2p455 -
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
alter database open;

On new standby rec3p455 after switchover -
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

10. Raise the COMPATIBLE settings

You can see that select queries (reporting sessions) are still alive and returning the correct results and connecting to primary database -
SQL> /

  COUNT(1)
----------
       256

SQL> select database_role, name from v$database;

DATABASE_ROLE    NAME
---------------- ---------
PRIMARY          REC2P455

SQL>  select * from dba_registry_history;

ACTION_TIME                                                                 ACTION                      NAMESPACE                         VERSION                                ID BUNDLE_SERIES           COMMENTS
--------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ---------- ------------------------------ -------------------------------
22-SEP-10 03.42.04.503870 PM                                                APPLY                       SERVER                    11.1.0.7                                3 CPU                            CPUOct2009
22-SEP-10 03.42.45.067417 PM                                                CPU                                                                                     6452863                                view recompilation
20-MAR-13 08.50.32.513178 PM                                                APPLY                       SERVER                    11.1.0.7                               13 PSU                            PSU 11.1.0.7.13
19-JUL-13 05.20.52.017167 PM                                                VIEW INVALIDATE                                                                         8289601                                view invalidation
19-JUL-13 05.21.00.848183 PM                                                UPGRADE                     SERVER                    11.2.0.3.0                                                               Upgraded from 11.1.0.7.0
19-JUL-13 05.26.58.688228 PM                                                APPLY                       SERVER                    11.2.0.3                                4 PSU                            PSU 11.2.0.3.4
20-NOV-13 05.46.43.353854 PM                                                APPLY                       SERVER                    11.2.0.3                                7 PSU                            PSU 11.2.0.3.7
29-AUG-14 05.16.13.596405 PM                                                VIEW INVALIDATE                                                                         8289601                                view invalidation
29-AUG-14 05.16.23.809173 PM                                                UPGRADE                     SERVER                    11.2.0.4.0                                                               Upgraded from 11.2.0.3.0
29-AUG-14 05.18.10.822624 PM                                                APPLY                       SERVER                    11.2.0.4                                2 PSU                            PSU 11.2.0.4.2

10 rows selected.
SQL> sho parameter db_unique

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
db_unique_name                       string                           rec2p455
SQL>

For reporting services, below service was created along with triggers to support transparent application connections failover -

exec DBMS_SERVICE.CREATE_SERVICE -
(service_name => 'rec2p455_dg', -
network_name => 'rec2p455_dg', -
aq_ha_notifications => true, -
failover_type => 'SELECT', -
failover_retries => 180, -
failover_delay => 1);


create or replace trigger start_database_service after startup on database
declare
role varchar2(50);
omode varchar2(50);
begin
select database_role into role from v$database;
  if role='PRIMARY' then
    dbms_service.start_service('rec2p455_dg');
  end if;
end;
/

CREATE OR REPLACE TRIGGER start_service_test_role_change AFTER
DB_ROLE_CHANGE ON DATABASE
DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE(''rec2p455_dg')');
END IF;
END;