Configure Data Guard Fast Start Failover and failover.

Fast-start failover(FSFO) help us to automatically fail over to a previously configured standby database while primary database is not available. Fast-start failover to standby. It is quick and reliable. FSFO does not require any manual steps to invoke the failover. Fast-start failover can be used only in a broker configuration. It can be configured only through DGMGRL.


Logged in to sys on dgmgrl prompt.
dgmgrl
connect sys



Verify and modify FSFO parameters.
show database verbose accdb LogXptMode
show database verbose accstdb LogXptMode
show database verbose accdb FastStartFailoverTarget
show database verbose accstdb FastStartFailoverTarget



Modify FSFO related parameters.
edit database accdb set property LogXptMode='SYNC';
edit database accstdb set property LogXptMode='SYNC';
edit database accdb set property FastStartFailoverTarget='accstdb';
edit database accstdb set property FastStartFailoverTarget='accdb';



Enable FSFO. If we encounter the error as above please proceed to verify the FSFO requirements.

• Flashback should be ON on both primary and standby database
• Protection mode should be Maxavailability

Verify flashback on primary.
select flashback_on from v$database;



Verify flashback on standby.
select flashback_on from v$database;

Enable flashback on primary.
alter database flashback on;
select flashback_on from v$database;


Enable flashback on stanbdy. We should cancel managed recovery before enabling flashback.
recover managed standby database cancel;
alter database flashback on;
recover managed standby database disconnect;
select flashback_on from v$database;


If we notice some inconsistent then disable and enable configuration will resolve the issue.
show configuration
disable configuration;
enable configuration;
show configuration;



Set protection mode to maxavailability.
edit configuration set protection mode as MaxAvailability;



Enable FSFO. We can see Warning that observer has not been started.
enable fast_start failover;



We have to start observer on different host but right now we have 2 hosts so lets start observer on standby database. The control will not be return back once observer has been started.
start observer;




Make primary database unavailable.
shutdown abort;



Now we can see database has been successfully failover to accstdb.



Lets start older primary database to mount mode.
startup mount;


Now we can see observer has been initiated older primary database(accdb)  as standby database. This happends automatically. We do not need to do any thing manually.



Verify configuration.
show configuration;



Switch back to accdb. Lets make accdb as primary and accstdb as standby.
switchover to accdb;



Verify configuration and stop observer.
show configuration
stop observer;




We can notice observer has been stopped and we can get back control.

For details please visit Oracle documentation
https://docs.oracle.com/database/121/DGBKR/sofo.htm#DGBKR390

Read More

Configure Data Guard, switchover and convert

Data Guard configuration, switchover and convert database to snapshot standby.

Verify dg_broker_start parameter. Set parameter to true if it is false.

alter system set dg_broker_start=true;

Set dg_broker_start to true on standby server also.

Type dgmgrl once you enter the dgmgrl prompt type connect sys. Type password when prompt.

dgmgrl
connect sys

Type help to get list of command type help command to get syntax of the comand

help

help create

Create configuration with primary database as accdb.

create configuration noug_dg as

primary database is accdb

connect identifier is accdb;

Add standby database with add database command

add database accstdb as connect identifier is accstdb;

Check configuration with show command

show configuration.

Enable configuration with command below.

enable configuration;

Once you enable configuration you can switchover database role with command below

switchover to accstdb;

show configuration;

Switch back to accdb;

switchover to accdb;

Convert physical standby database to snaphost standby.

convert database accstdb to snapshot standby;

Check database role. While database is in snapshot standby mode there is one restore point created to revert back changes.

select open_mode, database_role from v$database;

col name format a60

set lines 200 pages 200

select name, storage_size, time from v$restore_point;

Create user dilli on snapshot standby database and create table in dilli schema

create user dilli identified by raj;

grant dba to dilli;

connect dilli/raj

create table abc as select * from dba_objects;

commit;

select count(*) from abc;

Check current DG configuration.

show configuration;

Convert database back to physical standby.

convert database accstdb to physical standby;

Verify database role.

select open_mode, database_role from v$database;

For details please visit Oracle documentation
https://docs.oracle.com/database/121/DGBKR/sofo.htm#DGBKR390

Read More

Step by Step Physical Standby database creation

Environment:

OS: Red Hat Enterprise Linux Server release 6.10

Oracle: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Hostname: primary.localdomain

                    stdby.localdomain

Oracle binary and Oracle database accdb has been installed on host primary where as we have installed Oracle binaries only on the standby host stdby. While installing Oracle on standby host we have selected install software only.

Primary

Verify database is on archive log mode or not. Convert primary database to archivelog mode.

SELECT log_mode FROM v$database;

Shutdown immediate

startup mount

alter database archivelog;

alter database open;

Check force logging has been enabled or not. Set database into force logging.

select name, force_logging from v$database;

alter database force logging;

Verify db_name and db_unique_name parameter. Both value should be same for primary database but  they will be different for standby database.

show parameter db_name

show parameter db_unique_name

Configure Standby related Oracle database parameters.

alter system set log_archive_config='DG_CONFIG=(accdb,accstdb)';

alter system set log_archive_dest_2='SERVICE=accstdb NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=accstdb';

alter system set log_archive_dest_state_2=enable;

alter system set fal_server='accstdb';

alter system set fal_client='accdb';

alter system set standby_file_management=auto;

Check additional parameters

show parameter passwordfile

show parameter log_archive_format

Add static services to the listener

SID_LIST_LISTENER =

(SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = accdb)

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

      (SID_NAME = accdb)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = accdb_dgmgrl)

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

      (SID_NAME = accdb)

    )

  )

Configure tnsnames.

ACCDB =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = accdb)

    )

  )

ACCSTDB =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = stdby.localdomain)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = accdb)

    )

  )

Reload listener and check listener status

lsnrctl reload

lsnrctl status

Create pfile to oracle home from current spfile.

Create pfile from spfile;

Now ship recently created pfile and password file from primary to standby server.

cd

scp pfile oracle@stdby:

cd $ORACLE_HOME/dbs

scp orapwaccdb oracle@stdby:

Ship listener and tnsnames files to standy server.

cd $ORACLE_HOME/network/admin

scp *.ora stdby:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/

Add standby redolog files to primary database

ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo01.log' SIZE 52428800;

ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo02.log' SIZE 52428800;

ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo03.log' SIZE 52428800;

ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo04.log' SIZE 52428800;

Standby

Create required directories on standby server

mkdir -p /u01/app/oracle/admin/accdb/adump

mkdir -p /u01/app/oracle/oradata/accdb

mkdir -p /u01/app/oracle/admin/DB11G/accdb

mkdir -p /u01/app/oracle/flash_recovery_area

mkdir -p /u01/app/oracle/flash_recovery_area/accdb

Open listener.ora file with the text editor and modify the high lighted sections as host and GLOBAL_DBNAME.

vi listener.ora

Reload listener and check listener status

lsnrctl reload

lsnrctl status

Open pfile with text editor and modify highlighted sections.

cd

vi pfile

Copy Password file.

cp orapwaccdb /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Login to standby server and create spfile from pfile. Once spfile is created startup database on nomount mode.

sqlplus / as sysdba

create spfile from pfile='/home/oracle/pfile';

startup nomount

Primary Database

Validate network connectivity with tnsping to accdb and accstdb tnsnames

tnsping accdb

tnsping accstdb

Connect to standby server as user sys. Just to make sure we can connect to standby server.

sqlplus sys@accstdb as sysdba

If you are able to connect to the standby server then connect rman target as primary and auxiliary as standby server. Fix if there is any issue connecting to standy from the primary.

rman target / auxiliary sys@accstdb

Execute duplicate command to create standby database from active database

duplicate target database for standby from active database nofilenamecheck;

Add logfile to standby database;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

ALTER DATABASE ADD LOGFILE '/u01/app/oracle/oradata/accdb/redo01.log' SIZE 52428800;

ALTER DATABASE ADD LOGFILE '/u01/app/oracle/oradata/accdb/redo02.log' SIZE 52428800;

ALTER DATABASE ADD LOGFILE '/u01/app/oracle/oradata/accdb/redo03.log' SIZE 52428800;

Add redo log and standby redo log files

ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo01.log' SIZE 52428800;

ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo02.log' SIZE 52428800;

ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo03.log' SIZE 52428800;

ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo04.log' SIZE 52428800;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Check database open_mode and its role

select name, open_mode, database_role from v$database;

Alter database to recovery mode.

alter database recover managed standby database disconnect;

Verify logfile switch.

alter system switch logfile;

We can noticed RFS receives archive log and apply

Active Standby database with Realtime apply

Cancel recovery on managed standby and open database on read only mode.

alter database recover managed standby database cancel;

alter database open read only;

Start recovery on managed standby 

alter database recover managed standby database using current logfile;

Create table on primary.

create table abc(sn number);

insert into abc values(1);

/

commit;

Verify table and rows on standby server

select * from abc;

For details please visit Oracle documentation

https://docs.oracle.com/database/121/DGBKR/sofo.htm#DGBKR390

Read More