By:Joel Perez( Oracle ACED )
In this article we will show you how to transparent failover works in standby with oracle 12cR2 database on cloud.To do this; we would require three oracle cloud instances.
Primary Instance Creation:
Login to your Oracle cloud services account, go to the "Oracle Database Cloud Service" page and create a new service.
Service
• Set Service Name as “Primary”
• Set the service Level as “Oracle Database Cloud Service”
• Set Metering Frequency as per your convenience
• Setthe Software Release as “Oracle Database 12c Release2”
• Set the Software Edition “Enterprise Edition”
• Set the Database type as “Single Instance”
Click on to continue
Service Details
• Select DB Name (SID) as “ORCL”
• Set an administrative password of your choice and confirm the password (this will be your sys password)
• Set Usable Database Storage as 25 GB
• Select compute shape as “OC3 -1 OCPU, 7.5GB RAM” (this is the bare minimum requirement)
• Set SSH Public key as shown below
Click onto continue
Finally, check the configuration and click on "Create" to create your cloud database.
After a few minutes, primary cloud instance will be created successfully.
Click on the service name (Primary) to open the main page of database.
Before trying to connect to the primary instance on the cloud machine, you have to enable dblistener access rule.
Steps to enable dblistener access rule are as follows
Open the database service and Access Rule from the menu.
Click on Actions menu for the ora_p2_dblistener rule and enable it
Standby Instance Creation Number 1
Login to your Oracle cloud services account, go to the "Oracle Database Cloud Service" page and create a new service.
Service
• Set Service Name as “StandBy-1”
• Setthe service Level as “Oracle Database Cloud Service”
• Set Metering Frequency as per your convenience
• Setthe Software Release as “Oracle Database 12c Release2”
• Set the Software Edition“Enterprise Edition”
• Setthe Database type as “Single Instance”
Click on to continue
Service Details
• Select DB Name (SID) as “ORCL”
• Set an administrative password of your choice and confirm the password (this will be your sys password)
• Set Usable Database Storage as 25 GB
• Select compute shape as “OC3 -1 OCPU, 7.5GB RAM” (this is the bare minimum requirement)
• Set SSH Public key as shown below
Click on to continue
Note:
This is a dummy database and will be dropped after creation.
Finally, check the configuration and click on "Create" to create your cloud database.
After a few minutes, standby cloud instance will be created successfully.
Click on the service name (StandBy-1) to open the main page of database.
Before trying to connect to the standby instance on the cloud machine, you have to enable dblistener access rule.
Steps to enable dblistener access rule are as follows
Open the database service and Access Rule from the menu.
Click on Actions menu for the ora_p2_dblistener rule and enable it
Standby Instance Creation Number 2
Similarly you can create another standby instance with service name (StandBy-2)
Open two different instances of the "Putty" executable and connect SSH to both the machines.Open two different instances of the "Putty" executable and connect SSH to both the machines.
Note:
Primary instance is shown in the black background and standby instance in yellow background
Connect to both instances and check the hostname and pmon on each server.
Note:
The database version must be (12.2).
We shall drop the default standby database and create it manually
In the next step we will configure static registration of instance on listener file.
Also we need to configure DG Broker in upcoming part, so I will add a static entry for the DGMGRL in the listener.ora file on both the primary and standby servers.
2
Now we can restart listener.
In the Next step we will be configuring tnsnames.ora with Public IP, which is provided on console.
Make sure tnsping is working on both sides.
Now we create pfile and password file on the standby machine. We also create necessary directories for standby.
We can nomount the database with pfile and check the “rman duplicate” command connection from primary database.
Now on primary side, we need to setup some parameter for creating standby as shown below.
Create instantiation for standby using the “rman duplicate” command.
After sometime standby databaseis created and will be running in mount state.
Now we are going to open the standby in read only mode and start the recovery.
Also verify the working of physical database.
Now we are going to configure another standby instance, with similar settings as done previously.
We need to modify some parameter on primary as shown below
After configuring listener and copy tns entry for second standby, we run the “rman duplicate” command from primary as shown below.
After successful configuration of second standby, we need to add some parameter and run the managed recovery process
Now we are going to open the standby in read only mode and start the recovery.\
Also verify the working of new physical database.
DG Broker Configuration
Primary and standby database server details:
Primary Server: Primary.compute-aeetisalat12345.oraclecloud.internal
Database: ORCL
Standby Server: StandBy-1.compute-aeetisalat12345.oraclecloud.internal
Database: STBY1
Standby Server: StandBy-2.compute-aeetisalat12345.oraclecloud.internal
Database: STBY2
On both Primary as well as Standby database start the Data Guard Broker process
DG_BROKER_START
Now, enable the Dataguard broker process by setting the value of the parameter “” to TRUE (as already configured above)
Edit the listener.ora on both nodes to add a static entry for DGMGRL
Add a static entry for the DGMGRL in the listener.ora file on both the primary and standby servers.
On the primary server, create the Dataguard Broker configuration.
Steps to create the configuration
Add the Standby database to the configuration
Add the another Standby database to the configuration
Enable the configuration
Transparent Failover to Active Data Guard
In environments with Active Data Guard, when the database role changes, users are disconnected and need to reconnect to the new primary. With this feature, users that are only querying the primary database and use a dedicated service that can failover; will be automatically connected to the new primary after the role change. During the role change they will feel a pause, but won’t need to reconnect.
As of Oracle Database 12c Release 2 (12.2.0.1), when a physical standby database is converted into a primary you have the option to keep any sessions connected to the physical standby, without disruption, during the switchover/failover. When the database is reopened as the primary, the suspended sessions resume their operations as if nothing had happened. If the database (or an individual PDB) is not opened in the primary role, the sessions will be terminated.
To enable this feature, the STANDBY_DB_PRESERVE_STATES initialization parameter in the standby side is used. STANDBY_DB_PRESERVE_STATES controls whether user sessions and other internal states of the instance are retained when a readable physical standby database is converted to a primary database. This parameter is meaningful on a physical standby database that is open in real-time query mode. This parameter can have following values:
NONE — No sessions on the standby are retained during a switchover/failover.
SESSION or ALL — User sessions are retained during switchover/failover.
Note: This parameter is only meaningful on a physical standby database that is open in real-time query mode. This needs Active Dataguard option
Now below I will connect to standby database with default of value of STANDBY_DB_PRESERVE_STATES
Now let’s perform Switchover to STBY1 instance
As you see with NONE value for STANDBY_DB_PRESERVE_STATES has been disconnected from session.
Now we can set STANDBY_DB_PRESERVE_STATES to ALL on current standby (ORCL).
Now let’s perform Switchover to ORCL instance
ALL
As we see after setting the value to, my transition role is changed to primary but session automatically resume with same session id.
Note:
Sessions that have long running queries or are using database links will not be retained regardless of the setting of this parameter.
So now you know, it is very easy to retain the session after transition roles changes in Oracle 12cR2.
We hope this article has proved useful and we invite you to continue reading our next publications focused on Oracle Cloud.
Reference:
https://docs.oracle.com/database/122/REFRN/STANDBY_DB_PRESERVE_STATES.htm#REFRN-GUID-8D332556-30B7-4C45-8557-50988DC2219E
About the Author
Joel Pérez is an Expert DBA ( Oracle ACE Director, Maximum Availability OCM, OCM Cloud Admin. & OCM12c/11g ) with over 17 years of Real World Experience in Oracle Technology, specialized in design and implement solutions of: High Availability, Disaster Recovery, Upgrades, Replication, Cloud and all area related to Oracle Databases. International consultant with duties, conferences & activities in more than 50 countries in 5 continents and countless clients around the world. Habitual and one of leading writers of Technical Oracle articles for: OTN Spanish & OTN Portuguese. Regular Speaker in worldwide Oracle events like: OTN LAD (Latin America), OTN MENA(Middle East & Africa), OTN APAC ( Asian Pacific), DTCC China and more. Joel has always been known for being a pioneer in Oracle technology since the early days of his career being the first Latin American awarded as “OTN Expert” at year 2003 by Oracle Corp., one of the first “Oracle ACE” globally in the Oracle ACE Program at year 2004 and as one of the biggest professional achievement in his career he was honoured as one of the first “OCM Database Cloud Administrator” & Maximum Availability OCM in the world. Currently Joel works as Chief Technologist in “Yunhe Enmo (Beijing) Technology Co.,Ltd”., company located in Beijing, China www.Enmotech.com