At this point we have a primary database and a standby database, so now we need to start using the Data Guard Broker to manage them.
Connect to both databases (primary and standby) and issue the following command
PRODRHEL------Primary Database
PRODOL------Standby Database
Register the Service name on both sides:
SQL>alter system set service_names='PROD','PRODRHEL' scope=both;(Primary)
SQL>alter system set service_names='PROD','PRODOL' scope=both;(Standby)
#Enable broker:
On primary:
SQL> alter system set dg_broker_start=true;
SQL> show parameter dg_broker_start;
On standby:
SQL> alter system set dg_broker_start=true;
SQL> show parameter dg_broker_start;
#Register the primary database with broker
On primary, connect to DGMGRL utility and register the primary database with broker
On primary:
$dgmgrl sys/prod@prodrhel
DGMGRL>create configuration my_dg as primary database is prodrhel connect identifier is prodrhel;
DGMGRL>show configuration;
On standby database:
DGMGRL>add database prodol as connect identifier is prodol;
DGMGRL>show configuration;
#Enable configuration
DGMGRL>ENABLE CONFIGURATION;
#Stop log apply:
$dgmgrl sys/prod@prodrhel
DGMGRL>show configuration;
DGMGRL>show database prodol;
DGMGRL>edit database prodol set state=APPLY-OFF;
DGMGRL>show database prodol;
#Start log apply:
DGMGRL>edit database prodol set state=APPLY-ON;
DGMGRL>show database prodol;
How we can manually enable log shipping from primary to standby, the same way we can use broker to enable log shipping
#Disable log shipping/transport:
$dgmgrl sys/prod@prodrhel
DGMGRL> show configuration;
DGMGRL> edit database prodrhel set state=TRANSPORT-OFF;
DGMGRL> show database prodrhel;
#Enable log shipping/transport:
DGMGRL> edit database prodrhel set state=TRANSPORT-ON;
DGMGRL> show database prodrhel;
#Database Switchover
A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs.
This process is known as a Switchover and can be performed using the following commands. Connect to the primary database (prodrhel) and switchover to the standby database (prodol).
$dgmgrl sys/prod@prodrhel
DGMGRL> SWITCHOVER TO prodol;
Performing switchover NOW, please wait...
Operation requires a connection to instance "PROD" on database "prodol"
Connecting to instance "PROD"...
Connected.
New primary database "prodol" is opening...
Operation requires startup of instance "PROD" on database "prodrhel"
Starting instance "PROD"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "prodol"
DGMGRL>
#Let's switch back to the original primary. Connect to the new primary (prodol) and switchover to the new standby database (prodrhel).
$dgmgrl sys/prod@prodol
DGMGRL>SWITCHOVER TO prodrhel;
Performing switchover NOW, please wait...
Operation requires a connection to instance "PROD" on database "prodrhel"
Connecting to instance "PROD"...
Connected.
New primary database "prodrhel" is opening...
Operation requires startup of instance "PROD" on database "prodol"
Starting instance "PROD"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "prodrhel"
DGMGRL>
#Database Failover
If the primary database is not available the standby database can be activated as a primary database using the following statements.
Connect to the standby database (prodol) and failover.
$dgmgrl sys/prod@prodol
DGMGRL>FAILOVER TO prodol;
Since the standby database is now the primary database it should be backed up immediately.The original primary database can now be configured as a standby. If flashback database was enabled on the primary database, then this can be done relatively easily with the following command.
DGMGRL>reinstate database prodrhel;
#Snapshot Standby
Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode, all changes made whilst in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, thought it works just the same if it is.
Connect to the primary (prodrhel) database and convert the standby database (prodol) to a snapshot standby.
$dgmgrl sys/prod@prodrhel
DGMGRL>show configuration;
DGMGRL>CONVERT DATABASE prodol TO SNAPSHOT STANDBY;
DGMGRL>show configuration;
=======Example========
$sqlplus / as sysdba
SQL> create table student(id number(5));
SQL>begin
for i in 1 .. 100000 loop
insert into student values(1);
end loop;
end;
/
SQL> commit;
Commit complete.
SQL> select count(*) from student;
COUNT(*)
----------
100000
When you are finished with the snapshot standby, convert it back to a standby database.
$dgmgrl sys/prod@prodrhel
DGMGRL>show configuration;
DGMGRL>CONVERT DATABASE prodol TO PHYSICAL STANDBY;
DGMGRL>show configuration;
#Changing from Maximum Performance to Maximum Availability
Using DGMGRL connect to either the primary or the standby database.
$dgmgrl sys/prod@prodrhel
DGMGRL>SHOW DATABASE VERBOSE 'prodol';
DGMGRL>edit database prodol set property logxptmode=SYNC;
DGMGRL>edit database prodrhel set property logxptmode=SYNC;
DGMGRL>edit configuration set protection mode as maxavailability;
DGMGRL> show configuration;
#Let's switch back to the original status
DGMGRL> edit configuration set protection mode as maxperformance;
DGMGRL>edit database prodol set property logxptmode=ASYNC;
DGMGRL>edit database prodrhel set property logxptmode=ASYNC;
DGMGRL> show configuration;
#Enable Fast-Start-Failover Data Guard Broker
While Oracle Data Guard definitely protects a database when the entire production site is lost via its failover capabilities, it’s still necessary for an Oracle DBA to intervene to complete the failover process.
With this activity, we can enable automatic failover using Fast-Start-Failover Observer with Data Guard broker.
---Configure FSFO----
Check StaticConnectIdentifier: In order to enable FSFO, the StaticConnectIdentifier parameter must be set both in primary and standby
On primary(prodrhel):
$dgmgrl sys/prod@prodrhel
DGMGRL> show database prodrhel StaticConnectIdentifier;
DGMGRL> show database prodol StaticConnectIdentifier;
If StaticConnectIdentifier is blank: The StaticConnectIdentifier takes its value from LOCAL_LISTENER parameter from the database.
If this value is not set (or blank) for any database above, then connect to sqlplus and edit LOCAL_LISTENER parameter
SQL> ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.204)(PORT=1521))';
Once you make changes to LOCAL_LISTENER parameter, you must restart the listener.
Define FastStartFailoverTarget: In general, there can be more than one physical standby database.
So, we need to pair physical standby with primary to let Fast Start Failover know which physical standby to be activated
On primary (prodrhel):
$dgmgrl sys/prod@prodrhel
DGMGRL> SHOW FAST_START FAILOVER
DGMGRL> EDIT DATABASE prodrhel SET PROPERTY FastStartFailoverTarget = 'prodol';
DGMGRL> EDIT DATABASE prodol SET PROPERTY FastStartFailoverTarget = 'prodrhel';
DGMGRL> show database verbose prodrhel;
DGMGRL> show database verbose prodol;
Define FastStartFailoverThreshold: Next we need to let broker know when to initiate automatic failover.
What is the time (in seconds) that FSFO will wait before initiating failover
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold=30;
DGMGRL> show fast_start failover
Define FastStartFailoverLagLimit: We can optionally define how much time (in seconds) data we are ready to lose in case the Data Guard is in Max Performance Mode
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverLagLimit = 30;
---Enable FSFO: Now we can enable FSFO. Never start observer on production database
On Standby Server:
$dgmgrl sys/prod@prodol
DGMGRL> ENABLE FAST_START FAILOVER;
DGMGRL> show configuration;
DGMGRL> start observer;
Observer started
11:25:34.09 Wednesday, November 02, 2022
Initiating Fast-Start Failover to database "prodol"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "prodol"
11:25:48.30 Wednesday, November 02, 2022
11:27:53.85 Wednesday, November 02, 2022
Initiating reinstatement for database "prodrhel"...
Reinstating database "prodrhel", please wait...
Operation requires shutdown of instance "PROD" on database "prodrhel"
Shutting down instance "PROD"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "PROD" on database "prodrhel"
Starting instance "PROD"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "prodrhel" ...
Reinstatement of database "prodrhel" succeeded
11:29:04.71 Wednesday, November 02, 2022
Observer stopped
You have mail in /var/spool/mail/oracle
[oracle@erpproddb ~]$
----Test FSFO Configuration
Let us simulate a failure. We will abort primary (prodrhel) instance and wait for FSFO to perform automatic failover.
Simulate failure: On prodrhel, the current primary, let us shut abort the instance
On primary (prodrhel):
sqlplus / as sysdb
SQL> shut abort;
Check logfiles: At this stage, check alert log and observer log files. FSFO must perform automatic failover and prodol would be your new primary database
On new primary (prodol):
$sqlplus / as sysdba
SQL> set pagesize 200;
SQL> set linesize 200;
SQL> select DATABASE_ROLE,name, open_mode, db_unique_name,PROTECTION_MODE,SWITCHOVER_STATUS from v$database;
#Reinstate Failed Primary
Mount the failed primary (prodrhel) and it will auto reinstate.
Note: Do not open the database as it will be switched to physical standby
On failed primary (prodrhel):
sqlplus / as sysdba
SQL> startup mount;
dgmgrl sys/prod@prodrhel
DGMGRL> show configuration;
You can perform switchover to get back the original configuration
On current primary (prodol):
$dgmgrl sys/prod@prodol
DGMGRL> show configuration;
DGMGRL> switchover to prodrhel;
-----Disable FSFO----
$dgmgrl sys/prod@prodrhel
DGMGRL> show configuration;
DGMGRL> DISABLE FAST_START FAILOVER;
-----Stop observer:
$dgmgrl sys/prod@prodol
DGMGRL> stop observer;
No comments:
Post a Comment