Archive

Posts Tagged ‘dataguard’

Physical standby setup on Oracle 11gR2 – With Switchover/Failover

March 23, 2013 Leave a comment

Following setup has been carried out using 2 virtual machines.

Below is the setup scenario . Primary site has database installed and standby site has only binaries installed.

Primary Site

Standby Site

Hostname

10.0.0.31

10.0.0.32

Database Name

bharat

bharat

Database Unique Name

bharat_pri

bharat_stby

Net Service Name

bharat_pri

bharat_stby

Primary Server Setup

1. Enable FRA (for storing flash back files)

SQL> alter system set db_recovery_file_dest_size=2G scope=both;
SQL> alter system set db_recovery_file_dest= '/u02/app/oracle/backup/FRA/bharat';

2. Enable archive log mode

SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST';
SQL> alter system set log_archive_format='bharat_%t_%s_%r.arc' scope=spfile;
SQL> shut immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;

3. Turn flashback on

SQL> alter database flashback on;
SQL> select flashback_on from v$database;
SQL> show parameter db_flashback_retention_target
NAME                              TYPE        VALUE
 -----------------------------    ----------- ---------
 db_flashback_retention_target     integer       60

4. Set protection mode to max availability

SQL> alter database set standby database to maximize availability;
Database altered.
SQL> select protection_mode from v$database;
 PROTECTION_MODE
 --------------------
 MAXIMUM AVAILABILITY

5. Enable force logging

SQL> ALTER DATABASE FORCE LOGGING;
SQL> select name, force_logging from v$database;
NAME               FORCE_LOGGIN
---------                ------------
bharat                  YES

6. Enable Auto standby file management

SQL> alter system set standby_file_management = 'AUTO' scope=both;

7. Create standby log files

There should be one more than the existing logfiles and atleast one standby logfile should be max of current redo logs

SQL> alter database add standby logfile group 11 
                  '/u02/oradata/bharat/standby_redo01.rdo' size 5M;
SQL> alter database add standby logfile group 12 
                  '/u02/oradata/bharat/standby_redo02.rdo' size 5M;
SQL> alter database add standby logfile group 13 
                   '/u02/oradata/bharat/standby_redo03.rdo' size 5M;
SQL> alter database add standby logfile group 14 
                   '/u02/oradata/bharat/standby_redo04.rdo' size 5M;

8. If SSL isn’t used for redo transport we need to use oracle password file for authentication and bounce the database

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

9. Create password file

$> cd $ORACLE_HOME/dbs
$> orapwd file=orapwbharat password=<sys passwd>

10. Configure tnsnames.ora and listener.ora files

In tnsnames.ora

bharat_pri =(DESCRIPTION =(ADDRESS =(PROTOCOL = tcp)(HOST = 10.0.0.31 )
            (PORT = 1533))(CONNECT_DATA =(SID = bharat)(SERVER = DEDICATED)))
LISTENER_A=(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.31)(PORT = 1533))
bharat_stby =(DESCRIPTION =(ADDRESS =(PROTOCOL = tcp)(HOST = 10.0.0.32 )
             (PORT = 1533))(CONNECT_DATA =(SID = bharat)(SERVER = DEDICATED)))

In listener.ora

LISTENER_A=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
           (HOST=10.0.0.31)(PORT=1533))))
SID_LIST_LISTENER_A =(SID_LIST =(SID_DESC =
                     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_2)
                     (SID_NAME = bharat)))
LOG_FILE_LISTENER_A=LISTENER_A.LOG
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER_A=OFF
LOG_DIRECTORY_LISTENER_A=/u01/app/oracle/product/11.2.0/db_2/network/admin/log

11. Configuring Redo Transport

 SQL>  alter system set log_archive_dest_1 = 'location=use_db_recovery_file_dest 
       valid_for=(all_logfiles, all_roles) db_unique_name=bharat_pri' scope=both;

Above statement says to use the DB_RECOVERY_FILE_DEST as the location to archive to, and it should be used to archive all logfiles in any (all) roles, and that this is for the database prim1_pri

 SQL> alter system set log_archive_dest_2 = 'service=bharat_stby async 
 valid_for=(online_logfile,primary_role) db_unique_name=bharat_stby' scope=both;

Above statement says that if it is the primary database, it should transport online logfiles using the service name prim1_stby and that the target is the database prim1_stby

 SQL> alter system set fal_server = 'bharat_stby' scope=both;

Above statement specifies where the database should look for missing archive logs if there is a gap in the logs. It is used when the database is in the standby role and has a gap in the redo it has received

Note that the FAL_CLIENT parameter is deprecated in 11g and not needed.

12. We need to let the database know what other databases are in the Data Guard configuration

 SQL> alter system set log_archive_config = 'dg_config=(bharat_pri,bharat_stby)' 
                                                   scope=both;

Preparing the Standby environment

 1. Copy pfile from primary node to standby node and make these changes

  • Location of controlfiles if necessary
  • Location of diagnostic_dest, db_recovery_file_dest
  • In LOG_ARCHIVE_DEST_1 change db_unique_name to the standby SID
  • (i.e bharat_stby)
  • In LOG_ARCHIVE_DEST_2 change the service and db_unique_name to the
  • primary SID i.e bharat_pri).
  • Change FAL_SERVER to the primary SID (bharat_pri)
  • Add the following parameters:
  • db_unique_name=’bharat_stby’
  • db_name=’bharat’
  • db_file_name_convert and log_file_name_convert if you are changing the
  • location of the datafiles and/or logfiles.
  • db_file_name_convert= (‘/u02/oradata/bharat/’,’ /u02/oradata/bharat_stby/’)
  • log_file_name_convert=(‘/u02/oradata/bharat/’,’ /u02/oradata/bharat_stby/’)

2. Create directory structures as required

3. Copy password file and rename it as orapwbharat

4.  Start the instance and create spfile

       SQL> startup nomount
       SQL> create spfile from pfile;
       SQL> shutdown
       SQL> startup nomount
       SQL> show parameter spfile

5. Add tnsnames and listener.ora and start listener

in tnsnames.ora

bharat_stby =(DESCRIPTION =(ADDRESS = (PROTOCOL = tcp)
                              (HOST = 10.0.0.32 ) (PORT = 1533))
                              (CONNECT_DATA =(SID = bharat)(SERVER = DEDICATED)))
LISTENER_B=(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.32)(PORT = 1533))
bharat_pri =(DESCRIPTION =(ADDRESS =(PROTOCOL = tcp)(HOST = 10.0.0.31 )
            (PORT = 1533))(CONNECT_DATA =(SID = bharat)(SERVER = DEDICATED)))

in listener.ora

 LISTENER_B=  (DESCRIPTION_LIST= (DESCRIPTION=
               (ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.32)(PORT=1533))))
SID_LIST_LISTENER_B =(SID_LIST =(SID_DESC =
                     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_2)
                     (SID_NAME = bharat)))
LOG_FILE_LISTENER_B=LISTENER_B.LOG
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER_B=OFF
LOG_DIRECTORY_LISTENER_B=/u01/app/oracle/product/11.2.0/db_2/network/admin/log


Creating the Standby Database using RMAN duplication

 1Connect to RMAN, specifying a full connect string for both the TARGET and AUXILLARY instances. DO not attempt to use OS authentication.

 $> rman TARGET sys@bharat_pri AUXILIARY sys@bharat_stby 
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;

or
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE 
                                           DORECOVER NOFILENAMECHECK;

Terminology  is below,
FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, 
              so it will not force a DBID change.
FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source 
                      datafile, without an additional backup step.
DORECOVER: The DUPLICATE will include the recovery step, bringing the standby 
           up to the current point in time.
NOFILENAMECHECK: Destination file locations are not checked.

2. Enable flashback

 SQL>alter database flashback on;

Starting Redo Apply on the Standby

 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING 
                                 CURRENT LOGFILE DISCONNECT FROM SESSION;
Use above statement for real time apply
 or
 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

If you prefer, you can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;

To stop the redo apply ( i.e cancel media recovery)

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Verifying that Redo Apply is working

1. On the primary server, check the latest archived redo log and force a log switch.

 ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
select Sequence#,first_time,next_time,applied 
from v$archived_log 
where (COMPLETION_TIME) >= (select sysdate-1/6 from dual);

 ALTER SYSTEM SWITCH LOGFILE;

2. Check the new archived redo log has arrived at the standby server and been applied.

 ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

 select sequence# Sequence#,first_time,next_time,applied 
 from v$archived_log where dest_id=2 and 
 (COMPLETION_TIME) >= (select sysdate-1/6 from dual);

TESTING SWITCHOVER

1. Convert primary database to standby

CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

2. Shutdown primary database

SHUTDOWN IMMEDIATE;

3. Mount old primary database as standby database

STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the original standby database issue the following commands.

1. Convert standby database to primary

CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

2. Shutdown standby database

SHUTDOWN IMMEDIATE;

3. Open old standby database as primary

STARTUP;

TESTING FAILOVER

If the primary database is not available the standby database can be activated as a primary database using the following statements.

 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
 ALTER DATABASE ACTIVATE STANDBY DATABASE;

Flashback failed primary database to physical standby database

Although a switchover is safe for both the primary and standby database, a failover makes the original primary database useless for converting to a standby database.

If flashback database is not enabled, the original primary must be dropped and recreated as a standby database.

Alternative is to enable flashback database on the primary (and the standby if desired) so in the event of a failover, the primary can be flashed back to the time before the failover and quickly converted to a standby database.

As we have already enabled flashback database feature, below are the steps to flashback a failed primary instance to physical standby database

1. Determine the SCN at which the old standby database became the primary database

On the new primary database, issue the following query to determine the SCN at which the old standby database became the new primary database:

SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

2. Flash back the failed primary database (below steps on old primary)

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO SCN standby_became_primary_scn;

3. Convert the old primary database to a physical standby database.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

4. Start redo apply on standby database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  USING CURRENT LOGFILE 
          DISCONNECT FROM SESSION;

Read-Only Standby and Active Data Guard

Once a standby database is configured, it can be opened in read-only mode to allow query access. When open in read-only mode, archive log shipping continues,but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.

To switch the standby database into read-only mode

SHUTDOWN IMMEDIATE; 
STARTUP MOUNT; 
ALTER DATABASE OPEN READ ONLY;

To resume managed recovery, do the following.

SHUTDOWN IMMEDIATE;
 STARTUP MOUNT; 
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

In 11g, Oracle introduced the Active Data Guard feature. This allows the standby database to be open in read-only mode, but still apply redo information.
This means a standby can be available for querying, yet still be up to date.

SHUTDOWN IMMEDIATE; 
STARTUP MOUNT; 
ALTER DATABASE OPEN READ ONLY; 
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

StartupForce

Where all the Action Is !!!!

Pavan DBA's Blog

The DBA Knowledge Store

ORACLE-BASE - Latest Articles

Where all the Action Is !!!!