Oracle 19c introduced an essential feature for database administrators working with Data Guard environments: the ability to maintain standby sessions connected during role transition. This enhancement significantly improves the availability and reliability of databases by minimizing session disruptions. In this blog, we will explore this feature in depth, focusing on its implementation and benefits.
Understanding Standby Sessions and Role Changes
Standby Sessions: In a Data Guard configuration, a standby database is an exact replica of the primary database. The standby database stays synchronized with the primary database through applying redo logs. Standby sessions refer to the user connections established to this standby database.
Role Change: Role change in Data Guard involves switching the roles between the primary and standby databases.
This can involve either a switchover (planned role change) or a failover (unplanned role change due to primary database failure).
Key Parameters for Managing Standby Sessions
Oracle Database 12c Release 2 introduced the STANDBY_DB_PRESERVE_STATES initialization parameter, which has been carried forward into Oracle 19c. This parameter determines if sessions on the standby database remain active during a switchover or failover. The possible values are:
NONE: No sessions are retained.SESSIONorALL: User sessions are retained during the transition.
Implementing the Feature
To implement this feature, ensure your standby database is in real-time query mode, which requires the Active Data Guard option. Here’s how you can set up and demonstrate this feature:
Initial Configuration
Check the current configuration:
DGMGRL> show configuration;
Configuration - DB_CONFIG
Protection Mode: MaxProtection
Members:
DB_PRIMARY - Primary database
DB_STANDBY1 - Standby database
DB_STANDBY2 - Standby database
Fast-Start Failover: DISABLED
Configuration Status: SUCCESS
Note session information on standby:
SQL> select username,sid, serial# from v$session where sid=SYS_CONTEXT('USERENV','SID');
USERNAME SID SERIAL#
SYSTEM 65 2869
Check the standby database unique name and open mode:
SQL> show parameter db_unique_name;
NAME TYPE VALUE
db_unique_name string DB_STANDBY1
SQL> select open_mode from v$database;
OPEN_MODE
READ ONLY WITH APPLY
Performing a Switchover
With the default value of NONE for STANDBY_DB_PRESERVE_STATES, perform a switchover:
Check the parameter value:
SQL> show parameter standby_db_preserve_states;
NAME TYPE VALUE
standby_db_preserve_states string NONE
Initiate the switchover:
DGMGRL> switchover to 'DB_STANDBY1';
Performing switchover NOW, please wait...
Operation requires a connection to database "DB_STANDBY1"
Connecting ...
Connected to "DB_STANDBY1"
Connected as SYSDBA.
New primary database "DB_STANDBY1" is opening...
Operation requires start up of instance "DB_PRIMARY" on database "DB_PRIMARY"
Starting instance "DB_PRIMARY"...
ORACLE instance started.
Database mounted.
Database opened.
Connected to "DB_PRIMARY"
Switchover succeeded, new primary is "DB_STANDBY1"
Verify session status: While the switchover is ongoing, start a query on DB_STANDBY1. The session should be disconnected:
SQL> select * from dba_objects;
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 1915
Session ID: 65 Serial number: 2869
Retaining Sessions with Parameter Set to ALL
Set the parameter to ALL:
SQL> show parameter standby_db_preserve_states;
NAME TYPE VALUE
standby_db_preserve_states string ALL
Perform the switchover again:
DGMGRL> switchover to 'DB_PRIMARY';
Performing switchover NOW, please wait...
Operation requires a connection to database "DB_PRIMARY"
Connecting ...
Connected to "DB_PRIMARY"
Connected as SYSDBA.
New primary database "DB_PRIMARY" is opening...
Operation requires start up of instance "DB_STANDBY1" on database "DB_STANDBY1"
Starting instance "DB_STANDBY1"...
ORACLE instance started.
Database mounted.
Database opened.
Connected to "DB_STANDBY1"
Switchover succeeded, new primary is "DB_PRIMARY"
Verify session status: After the switchover, the session remains connected:
SQL> select username,sid, serial# from v$session where sid=SYS_CONTEXT('USERENV','SID');
USERNAME SID SERIAL#
SYSTEM 58 58847
Benefits of Retaining Standby Sessions
Retaining standby sessions during role changes provides several benefits:
- Minimized Disruption: User sessions continue seamlessly, reducing the impact on applications and users.
- Enhanced Availability: Critical operations can continue without interruption, improving overall database availability.
- Simplified Management: Less need for session re-establishment scripts or manual interventions.
Conclusion
Oracle 19c’s feature for retaining standby sessions during role transition in Data Guard is a significant enhancement for database administrators. By ensuring user sessions remain connected during transitions, it improves the reliability and availability of database environments. Implementing this feature involves setting the STANDBY_DB_PRESERVE_STATES parameter appropriately and ensuring the standby database is in real-time query mode. This feature demonstrates Oracle’s commitment to providing robust, high-availability solutions for enterprise databases.
See more on Oracle’s website!
Be Oracle Dataguard Certified Professional, this world is full of opportunities for qualified DBAs!
