Oracle 12c-Manual Data Guard Failover Step by Step

 When we have planned maintenance activity we can simply switch roles of Primary and Standby database. But what if Primary Database crashes and we can’t recover it in a time.

Data Guard has a solution for that. We can Fail Over Primary database role to Standby Database and we don’t need to switch Standby Database role to Primary Database.

Types of FailOver:

1.Manual FailOver:

If the Primary database fails, one of the standby databases can be made to assume the Primary role. This role change of standby database to primary, in case of failure of existing primary, is called failover. There may or may not be data loss. This is not a planned activity and is performed only in catastrophic cases where the Primary database fails.

2. Fast-start FailOver: 

We need to configure this in data guard broker. So when Primary Database is unavailable. Dgbroker will fail over primary database role to One of standby database configured previously in dgbroker.

Failover Considerations: Things we need to take care while using this option.

In this option old primary database will be disabled from the Data Guard configuration.

If primary database crashed and generated archived not copied to standby database then data loss is possible.

This option is critical and should only be used in EMERGENCY.

Note: To use this option we should use Standby Database is most current[Maximum synchronized]

 STEP 1:

 Check Standby Database role

 SQL>select name,open_mode,database_role from v$database.

 STEP 2:

 Stop the MRP Process if it is running.

 Sql>alter database recover managed standby database cancel;

 STEP 3:

 Apply the following command to finish database recovery

 Sql>alter database recover managed standby database finish;

 Step 4:

 Use the following command to activate standby database to a primary.

 Sql>alter database activate Standby database;

 Step 5:

 Check open mode and database role from a v$database

 SQL>select name,open_mode,database_role from v$database.

 Step 6:

 Shut down primary database.

 Step 7:

 Startup database

 Step 8:

 Check open mode and database role from the v$database.

 SQL>select name,open_mode,database_role from v$database.

PRACTICE :

                                                                    

Oracle 12c-Step by Step Manual Switchover Data Guard

 Switchover:

          It is reversal of role between a standby database and a primary database. The primary database assumes role of standby database and the standby database assumes role of primary database after the switchover. There is no data loss in a switchover. This is generally a planned activity.

Step 1:

 ON Primary

 Select switchover_status from v$database..

 Step 2 :

 Check that there is no active users connected to the databases.

 Select  distinct ousers,username from v$session

 Step 3:

 Switch the current online redo log file on primary database and verify that it has been applied.

 SQL>alter system switch logfile

 Step  4 :

 Connect with primary database and initiate the switchover.

 SQL>alter database commit to switchover to physical standby;

 Now, the primary database is converted into standby database.The controlfile is backed up to the current SQL session trace file before the switchover. This makes it possible to reconstruct a current control file,if necessary.

 If we try to perform a switchover when other instances are running then we will get ORA-01105 as follows :

SQL>alter database commit to switchover to physical standby with Session shutdown;

 The above statement first terminates all active sessions by closing the primary database. Then any non-archived redo log files are transmitted and applied to standby database. Apart from that an end-of-redo marker is added to the header of the last log file that was archived.A backup of current control file is created and the current control file is converted into a standby control file.

 Step  5 : 

Shut down and restart the  primary instance(RED).

SQL>Shut immediate; 

SQL> Startup mount;

Step  6 : 

Verify the switchover status in the v$database view.

After we change the primary database to the physical standby role and the switchover notification is received by the standby databases in the configuration, we should verify if the switchover notification was processed by the target standby database by querying the "  column of the v$database fixed view on the target standby database.

On Primary Database(prim)

SQL> select name,open_mode,db_unique_name,switchover_status from v$database.

NAME      OPEN_MODE       DB_UNIQUE_NAME         SWITCHOVER_STATUS
------       -----------          --------------------         ----------------------
PRIM     MOUNTED                prim                    TO PRIMARY

On Old Standby Database(PRIM)
SQL> select name,open_mode,db_unique_name,switchover_status from v$database.

Important Notes :

The switchover_status column of v$database can have the following values:

Not Allowed:-Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases
Session Active:- Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted
Switchover Pending:- This is a standby database and the primary database switchover request has been received but not processed.
Switchover Latent:- The switchover was in pending mode, but did not complete and went back to the primary database
To Primary:- This is a standby database, with no active sessions, that is allowed to switch over to a primary database
To Standby:- This is a primary database, with no active sessions, that is allowed to switch over to a standby database
Recovery Needed:- This is a standby database that has not received the switchover request

Create Physical Standby Database in Data Gaurd Using RMAN Backup on Oracle 12C

 Primary Database - Dhee

Standby Database - Dheedr

 

STEP 1;

Check the Primary DB is in archive log mode or not.If it is not then enable it

Sql>starup mount

Sql>alter database archivelog

Sql>archive log list

 

STEP 2:

Enable force logging in the Primary Database.

Sql>alter database force logging;

  Check it by

  Select force_logging from v$database;

 

STEP 3 :

 Verify database initialization parameters db_name and db_unique_name on primary

 Show parameter db_name

 Show parameter db_unique_name

 

STEP 4:

 Set the Log_archive_config for datagaurd configuration.

 Sql>alter system set log_archive_config=’DG_config=(dhee,dheedr)’;

 Then

Show parameter log_archive_config

 

STEP 5:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=dheedr NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dheedr';

 Show parameter log_archive_dest_2

 Alter system set log_archive_dest_2=enable;

 Show parameter log_archive_dest_state_2.

 

STEP 6:

 alter system set log_archive_format='%t_%s_%r.arc' scope=spfile

 Show parameter log_archive_format.

 

STEP 7:

 Set log_Archive_max_processes=30

 Sql>alter system set log_archive_max_processes=30;

 Show parameter log_archive_max_processes

 

STEP 8:

 Set remote_login_passwordfile to exclusive

 Sql>alter system set remote_login_passwordfile=exclusive;

 Show parameter remote_login_passwordfile

 

STEP 9:

 Set fal_server and fal_client for the primary database.

 Sql>alter system set fal_server=dheedr

 Sql>alter system set fal_client=dhee

 

STEP 10:

 Set standby_file_management to auto

 Sql>alter system set standby_file_management = auto;

 Show parameter standby_file_management

 

STEP 11:

 Configure the listener and tns entries in both the servers

 Listener.ora:

 SID_LIST_LISTENER =

 (SID_LIST =

 (SID_DESC =

 (GLOBAL_DBNAME = dhee)

 (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/dbhome_1)

 (SID_NAME = dhee)

 ) )

 LISTENER =

 (DESCRIPTION =

 (ADDRESS = (PROTOCOL = TCP)(HOST = dheedr)(PORT = 1521))

 )

 ADR_BASE_LISTENER = /u01/app/oracle

 

STEP 12:

 Tnsnames.ora:

 dhee =

 (DESCRIPTION =

 (ADDRESS_LIST =

 (ADDRESS = (PROTOCOL = TCP)(HOST = dhee)(PORT = 1521))

 )

 (CONNECT_DATA =

 (SERVICE_NAME = dhee)

 (GLOBAL_NAME = dhee)

 (UR=A)

 ) )

 dheedr =

 (DESCRIPTION =

 (ADDRESS_LIST =

 (ADDRESS = (PROTOCOL = TCP)(HOST =dheedr)(PORT = 1521))

 )

 (CONNECT_DATA =

 (SERVICE_NAME = dhee)

 (GLOBAL_NAME = dhee)

 (UR=A)

 ) )

 

STEP 13:

 Check the tnsping utility on Primary and Standby.

 Tnsping dhee

 Tnsping dheedr

 

STEP 14:

 Backup the primary database via RMAN utility.

 RMAN>backup database plus archivelog;

 

STEP 15:

 Create the standby control file on the primary database.

 RMAN>alter database create standby controlfil  as ‘$ORACLE_HOME/dbs’

 Create the passwordfile on the primary database.

 Orapwd file=’$ORACLE_HOME/dbs/orapwdhee Password=siva@1998

 Entries=10.

 Create pfile from spfile.

 Create pfile=’$ORACLE_HOME/dbs/initdhee.ora’ from spfile

 

 STEP 16:

 Move the files like standby controlfile,passwordfile & backup pieces to the standby database through scp connection.

 

STEP 17:

 Connect the database using the pfile by changing some parameters:

 *.db_unique_name='dheedr'

 *.fal_server='dhee'

 *.log_archive_dest_2='SERVICE=dhee ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dhee'

 

STEP 18:

 Take the database to nomount stage.

 Sql>startup nomount pfile=’$ORACLE_HOME/dbs/initdhee.ora’

 Take the database to mount stage.

 RMAN>restore controlfile from ‘$ORACLE_HOME/dbs/standbyctl.ctl’

 

STEP 19:

 Sql>alter database mount;

 

STEP 20:

 Create the Standby database

 RMAN>duplicate database ‘dhee’ for standby backup location ‘$ORACLE_HOME/dbs/’;

Apply the redo logs from the primary to standby database.

 

STEP 21:

                                                   

Configure a Physical Standby Database using Active Data Gaurd in oracle

Primary Database - Prim

      Standby Database - Primdr

 

STEP 1:

 Check the Primary DB is in archive log mode or not.If it is not then enable it.

 Sql>starup mount

 Sql>alter database archivelog

 Sql>archive log list

 

STEP 2:

 Enable force logging in the Primary Database.

 Sql>alter database force logging;

 Check it by

       Select force_logging from v$database;

 

STEP 3:

 Add the Standby redolog files on the primary database.

 Sql>Alter database add standby logfile thread 1 group 4(‘/u01/app/oracle/oradata/redo04.log’) size 50M;

 Alter database add standby logfile thread 1 group 5(‘/u01/app/oracle/oradata/redo05.log’) size 50M;

 Alter database add standby logfile thread 1 group 6(‘/u01/app/oracle/oradata/redo06.log’) size 50M;

 Alter database add standby logfile thread 1 group 7(‘/u01/app/oracle/oradata/redo07.log’) size 50M;

 

STEP 4:

 Verify database initialization parameters db_name and db_unique_name on primary

 Show parameter db_name

 Show parameter db_unique_name

 

STEP 5:

 Set the Log_archive_config for datagaurd configuration.

 Sql>alter system set log_archive_config=’DG_config=(prim,primdr)’;

 Then

 Show parameter log_archive_config

 

STEP 6:

 

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=primdr NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primdr';

 

Show parameter log_archive_dest_2

 Alter system set log_archive_dest_2=enable;

 Show parameter log_archive_dest_state_2.

 

STEP 7:

 alter system set log_archive_format='%t_%s_%r.arc' scope=spfile

 Show parameter log_archive_format.

 

STEP 8:

 Set log_Archive_max_processes=30

 Sql>alter system set log_archive_max_processes=30;

 Show parameter log_archive_max_processes

 

STEP 9:

 Set remote_login_passwordfile to exclusive

 Sql>alter system set remote_login_passwordfile=exclusive;

 Show parameter remote_login_passwordfile

 

STEP 10:

 Set fal_server and fal_client for the primary database.

 Sql>alter system set fal_server=primdr

 Sql>alter system set fal_client=prim

 

STEP 11:

 Set standby_file_management to auto

 Sql>alter system set standby_file_management = auto;

 Show parameter standby_file_management

 

STEP 12:

 Configure tns entry in the primary database.

  

Configure the listener in the Primary database.

 

STEP 13:

 Configure the tns entry in the Standby database.

                                                            

Configure the listener in the Standby database.

 

STEP 14:

 Check the tnsping utility on Primary and Standby.

 Tnsping prim

 Tnsping primdr

 

STEP 15:

 Create the Password file on the primary database and send it to standby database.

 

STEP 16:

 Create the pfile from spfile on the primary database and send it to the Standby database.

 

STEP 17:

 Using the pfile take the database at nomount stage.

                                                    

STEP 18:

 Connect the target and axiliary database using RMAN utility.

                                                                  

STEP 19:

 Once finished the duplicate,mount the database

 Sql>alter database mount;

 And then apply log files using MRP

 Sql>alter database recover managed standby database using current logfile disconnect from session;

 

STEP 20:

 Shutdown the database.

 Startup mount

 Alter database read only.

                                                  

STEP 21:

 Check the current log sequence on both side.

                                                              

                                                 

STEP 22:

 You can use the Primdr database as read only.