Oracle Data Guard standby-27

Data Guard

It’s a standby database. A copy of primary database. Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data.

Primary and standby will be in sync mode.

Standby is used for high availability.

It will reduce the recovery and downtime and used for D/R.

Technologies in standby data guard

1. Physical standby (Redo apply technologies P–Archive–S)
2. Logical Standby (SQL statement will run)
3. Switchover (Changing)
4. Failover (For server shutdowns)
5. Data guard broker (Third server for witness)

Use of broker:

Data guard broker will be managed as centralized.

The switchover and failover will be automated.

We need separate listener for data guard broker.

A primary can have 32 standby DBs (9i onwards)

We can configure a standby DBs in standalone — stand alone or RAC vice Vera.

Requirement for standby database

OS should be same

OS bit version same

Oracle version same

DB should be in archive log mode

Enterprise edition

Optional:

RAM can be anything

Mount point name can be anything for both servers

How to implement physical standby database

 

 

The DB name should be same

Instance name can be different

Net service name needed for connect and sync between primary and standby

Primary — Standby one net service name (For archive transfer)

Standby — Standby Second net service name (For archive gap fix)

Primary — Standby Third net service name (For Switch over)

Standby — Standby Fourth net service name (For archive gap fix)

We need minimum 2 net service names in both servers.

 

Login to primary:

$ vi tnsname.ora

1.

Prabhu_standby =

(DESCRIPTION =    ( (ADDRESS = (PROTOCOL = TCP) (HOST = 192.9.200.10)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = Prabhu)))

 

2.

Prabhu_Primary =

(DESCRIPTION =    ( (ADDRESS = (PROTOCOL = TCP) (HOST = 192.9.200.7)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = Prabhu)))

 

 

Login to Standby:

$ vi tnsname.ora

1.

Prabhu_primary =

(DESCRIPTION =    ( (ADDRESS = (PROTOCOL = TCP) (HOST = 192.9.200.7)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = Prabhu)))

 

2.

Prabhu_standby =

(DESCRIPTION =    ( (ADDRESS = (PROTOCOL = TCP) (HOST = 192.9.200.10)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = Prabhu)))

 

Primary

Note down all the locations of DB files.

Sql> select name from v$datafile;

/opt/oracle/oradata/prabu/system.dbf .. etcc

Sql> select member from v$logfile;

/opt/oracle/oradata/prabu/redo01.log .. etcc

 

Sql> Alter database create standby controlfile as ‘/opt/control01.ctl’;

Sql> create pfile =’/opt/intprabhu.ora’ from spfile;

Sql> shutdown immediate;

 

$ vi /opt/initprabhu.ora

Cope to last line and add the followings

Log_archive_dest_1=’location=/opt/archive/prabhu’ – Primary Archive log

Log_archive_dest_2=’SERVICE=prabhu_standby’

Log_archive_dest_state_1=enable/disable  – Primary state

Log_archive_dest_state_2=enable

Switch Over parameters

Standby_archive_dest=’/opt/archive/prabhu’ – Primary of standby archive receiving location

DB_file_name_convert =’/data/oracle/oradata/prabhu’, ‘/opt/oracle/oradata/prabu’

log_file_name_convert =’/data/oracle/oradata/prabhu’, ‘/opt/oracle/oradata/prabu’

standby_file_manegment = auto / manual

Save the pfile.

Login to standby: – Create a DB folders in standby

/opt/oracle (ORACLE_BASE )–> (Admin , Oradata & diag)

Admin –>DBname (pfile,adump,ddump & create)

Oradata –> DBnames

Diag –> RDMBS–>DBname–>DBname–>Trace (bdump,cdump & udump)

Come to primary:

$ cd /opt/oracle/oradata/prabu

$ scp –r *.dbf oracle@192.9.20.10:/data/oracle/oradata/prabu

$ scp –r *.log oracle@192.9.20.10:/data/oracle/oradata/prabu

$ scp –r /opt/control01.ctl oracle@192.9.20.10:/data/oracle/oradata/prabu

$ scp –r /opt/initprabu.ora oracle@192.9.20.10:/data/oracle/oradata/prabu

$ cd /opt/oracle/product/11.2.0/dbhome/dbs

$ scp –r orapwprabhu oracle@192.9.200.10:/data/oracle/oradata/prabu

Sql> create spfile from pfile =’/opt/initprabu.ora’;

Sql> startup

 

Login to standby: – Modify the pfile

$ vi /data/initprabu.ora

Change the “/opt/” to  “/data”

Control_file= Replace all the existing location

= ‘/data/oracle/oradata/prabu/control.ctl’

Switch over parameter:

Log_archive_dest_1=’location=/data/archive/prabhu’ – Primary Archive log

Log_archive_dest_2=’SERVICE=prabhu_primary’

Log_archive_dest_state_1=enable/defer  – Primary state

Log_archive_dest_state_2=defer

Standby Db parameter:

Standby_archive_dest=’/data/archive/prabhu’ – Primary of standby archive receiving location

DB_file_name_convert =’/opt/oracle/oradata/prabhu’, ‘/data/oracle/oradata/prabu’

log_file_name_convert =’/opt/oracle/oradata/prabhu’, ‘/data/oracle/oradata/prabu’

standby_file_manegment = auto

 

$ export ORACLE_SID= prabu

$ sqlplus / nolog

Sql> conn sys/sys as sysdba

Sql> startup pfile =’/data/initprabu.ora’ nomount

Sql> alter database mount standby database; – We are using a stanby control file of primary

A standby database should be in mount to apply  archive log.

 

How to apply an archive log to standby

Sql> alter database recover managed standby database disconnect from session; – optional

The optional will apply continuously. Otherwise we need to run manually each time.

Sql> create spfile from pfile =’/data/initprabu.ora’;

Verification:

Login to primary

Sql> archive log list;

archivelog

enable

:/opt/archive/prabu

OLN:151

CLN:152

NLN:152

 

Standby

Sql> select sequence#, archived,applied from v$archived_log;

Check by run switch log and verify it.

Sql> alter system switch logfile;

Sql> create table dummy (dnon number);

Sql> insert into dummy values (1);

Commit

Swith lo

Check the stand by.

 

Sql> alter database open read only;

Sql> select * from dummy;

Sql> shutdown immediate

Sql> startup nomount

Sql> alter database mount standby database;

Sql> alter database recovery managed standby database disconnect from session; – MBR will start.

Log transport service

Data protection mode

1. Maximum performance – Default

The transfer archive log can be any order. But, the apply should be in sequence. The archive will be transferred and standby will apply it.

In case of N/W down, no problem to the primary.

2. Maximum protection – synchronous data

The redo will be in sync and DML will be transferred directly to standby asap.

In case of any network down/ delay the primary database will be in hang sate.

3. Maximum Availability – A synchronous

Redo will send the transaction to redo of standby. In case of N/W down, oracle will automatically convert to performance mode.

 

How to set the mode

Primary:

Sql> shutdown immediate

Sql> startup mount

Sql> alter database set standby database to maximum performance/protection/availability ;

Sql> alter database open;

Standby

Sql> alter database set standby database to maximum performance/protection/availability ;

 

Check the protection mode:

Sql> select protection_mode from v$database;

 

Sync — no delay, there will be  ‘standby redo file location’

Async — Delay – Direct apply

Check the redo log file

Sql> seect * from v$standby_log;

Change to sync mode:

Sql> alter database add standby logfile group 4 ‘/data/oracle/probu/redostd04.log’ size 100m;

Sql> alter database add standby logfile group 5 ‘/data/oracle/probu/redostd05.log’ size 100m;

Add member

Sql> alter database add standby logfile member 4 ‘/data/oracle/probu/redostd04.log’ to group 4;

Sql> alter database add standby logfile member 5 ‘/data/oracle/probu/redostd04.log’ to group 5;

Log apply Service

1. RFS – Remote file server (Will receive the archive log file from primary)
2. ARC – Archivelog processer (Change/convert the received standby redo log into archive log)
3. MRP- Managed recover process (It will apply a standby archive log files to sand by DB)

Standby

Alter database recovery managed standby database disconnect from session;

 

How to stop MRP

Sql> alter database recover managed standby database cancel;

MRP Status:

Sql> select process_status from v$managed_standby;

RFS – Attached

ARC – Attached

MRP – Applying

 

Data file management

 

Standby_file_management = auto/ manual

In case, if you have a ‘/opt/tools.dbf’ and ‘/data/tools.dbf’. Want to add one more data file in primary this parameter will take it automatically.

 

Manual Process

Primary:

Sql> alter tablespace tools offline;

$ cd /opt/oracle/oradata/prabu

$ scp –r /opt/oracle/oradata/prabu/tools02.dbf oracle@192.9.200.10/data/oradata/prabu

Sql> alter tablespace tools online;

Sql> alter system switch logfile;

Standby: – Verify  the data file

Sql> select name from v$datafile;

Sql> select * from v$managed_standby;

Drop tablespace

Sql> drop tablespace tools including contents and datafile;

Sql> alter system switch logfile;

 

Standby:

$ cd /data/oracle/oradata/prbu

$ rm –fr tools01.dbf tools02.dbf

 

Rename datafile

Sql> alter tablespace tools offline;

$ cd /opt/oracle/oradata/prabu

$ cp tools01.dbf toolsnew01.dbf

$ scp toolsnew01.dbf oracle@192.9.200.10/data/oradata/prabu

Sql> alter tablespace tools rename datafile ‘/opt/oracle/oradata/prabu/tools01.dbf’ to ‘/opt/oracle/oradata/prabu/toolsnew01.dbf’

Sql> alter tablespace tools online;

 

Standby:

Sql> alter database recovery managed standby database cancel;

Sql> alter tablespace tools rename datafile ‘/data/oracle/oradata/prabu/tools01.dbf’ to ‘/data/oracle/oradata/prabu/toolsnew01.dbf’

Sql> alter database recovery managed standby database disconnect from session;

 

Archive Gap

There are sometimes gap can happen from moving archive from P –S. The gap mostly because of N/W slowness.

P (1 to 100.arc) In –S (70 to 75 not moved)

Check the archive gap:

Sql> select * from v$archive_gap;

Primary:

$ cd /opt/archive/prabu

$ scp –r *71.arc *72.arc *73.arc *74.arc @oracle192.9.200.10:/data/archive/prabu

 

Standby:

Sql> alter database register logfile ‘/data/archive/prabu/*71.arc’

Sql> alter database register logfile ‘/data/archive/prabu/*72.arc’ so on…

 

Scenario: The standby server archive is full. And there is one archive filled in half state.

Ex: P (71.arc – 800MB) S (71.arc – 300MB)

We need to delete the half copied file and need to register it.

Sql> alter database register or replace logfile ‘/data/archive/prabu/*71.arc’;

How to fix the gap permanently

FAL- Fetch archive log background process

It is a dedicated BG and will monitor and send the files.

 

Parameters:

Primary                              Standby

FAL_server                         FAL_server

FAL_client                          FAL_ client

 

In primary FAL_server and FAL_clent has a link and stand by also. It has like a cross link in the above image.

The lisnter & TNS entries should be correct.

FAL_server= prabu_standby (p-s)               FAL_server= prabu_prim

FAL_client = prabu_prim                FAL_ client = prabu_standby (S-S)

Primary:

Sql> alter system set fal_server=prabu_standby;

Sql> alter system set fal_client =prabu_ prim;

 

Standby:

Sql> alter system set fal_server=prabu_ prim;

Sql> alter system set fal_client =prabu_standby;

 

Scenario: Even though, we fix gap. Sometimes the long archive gap can happen and it will not fixed by FAL.

Ex: P (1 to 5000.arc)  S (2500 to 5000.arc)

 

For this cases, we need to take a RMAN incremental backup and need to apply it. Since, manually copying all archives will take more time.

Primary:

Sql> select current_scn from v$database; — 27384231

Standby

Sql> select current_scn from v$database; — 13384731

 

Primary:

$ rman target /

Rman> backup incremental from scn =13384731 database;

Sql> alter database create  standby controlfile as ‘/opt/const01.ctl’;

 

$ cd /backup/rman

$ scp incremental_probu.bkp oracle@192.9.200.10:/data

$ scp /opt/const01.ctl oracle@192.9.200.10:/data

 

Standy:

$ rman target  /

Rman> catalog start with ‘/data’ – to register backup

yes

Rman> exit

Sql> alter database recover managed standby database cancel;

Sql> shutdown immediate

$vi /data/initprabu.ora

Come to control file and replace the existing one.

Control_files =’/data/constd01.ctl’

Save it.

 

Sql> startup pfile =’/data/initprabu.ora’ nomount

Sql> alter database mount standby database;

 

Open one more prompt:

$rman target /

Rman> recover database;

Message: Restored and recovered successfully, you will get it.

Sql> alter database recover managed standby database disconnect from session;

 

Check both primary & standby are in sync.

Sql> select sequence#,archived,applied from v$database;

Sql> select current_scn from v$database;

 

Change the standby to  run using SPfile:

Sql> create spfile from pfile= ‘/data/initprabu.ora’;

Sql> shutdown immediate

Sql> startup mount

Sql> alter database mount standby database;

Sql> alter database recover managed standby database disconnect from session;

Day to Day activity:

Check both primary and standby are in sync and good. We can delete the last 3 days old archive files from both servers.

 

Scenario:

Planned network outage, How to manage, since it will hang the primary database.

Sql> alter system set log_archive_dest_2= ‘SERVICE=prabu_standby’ delay= 240; (240 is in minutes)

 

 

Dynamics views for data guard:

 

Sql>  select sequence#,archived,applied from v$archived_log;

Sql>  select archived_sequence#, applied_sequence# from v$archived_dest_status;

Sql>  select process,status from v$managed_standby; – (RFS-attached, ARC- started,MRP-applying)

Sql>  select * from v$log_histroy;

Sql>  select error from v$archived_dest; – Check the P&S error

Sql>  select message from v$datagurad_status; – Check the archive and destination etc.

 

Switch Over

 

Change the parameters to standby

 

Change the parameters to primary

Primary:

Sql> select swith_over_status from v$database;

Sql> alter database committo swithover to physical status;

Internally, all data will be committed , our primary control file convert to standby control file.

 

Sql> shutdown immediate;

Sql> startup nomount;

Sql> alter database mount standby database;

Come to existing database:

Sql> select switchover_status from v$database; – Switch over pending

Sql> alter database commit to swithover to primary; – It will convert to normal control file

Sql> shutdown immediate

Sql>startup

Sql> alter system set log_archive_dest_2=enable;

Sql>alter system switch logfile;

 

Standby:

Sql> alter system set log_archive_dest_2=defer;

Sql> alter database recover managed standby database disconnect from session;

 

Check the primary & secondary, all good. Down and up the listener.

Fail Over

P –S–P

Case1 Fire accident

Case2 Corruption

Once, we have done the failover, we cannot fail back. We need to set up again.

Case2:

Ste1: Check the archive gap and fix first.

Sql> alter database recover managed standby database finish;

Note: Once, we have given finish, we cannot start the MRP process again.

Case1:

There will be a minimal data loss. Since there was a corruption or H/W issue.

Sql> alter database recover managed standby database finish skip standby logfile;

It will apply the received log files.

Sql> alter database commit to swithover to primary;

Sql> shutdown immediate;

Sql> startup;

Change the  hostname and Ip address of the  standby and down+up the listener.

 

11g new feature in data guard:

 

In 11g we can have a standby database in read only mode. It will be used for reporting. Prior to 11g the database in mount-inactive state.

 

Change to Active data guard:

Standby:

Sql> alter database recover managed standby database cancel; – Stop the MRP

Sql> alter database open read only;

Sql> alter database recover managed standby database disconnect from session;

Sql> select name,open_mode from v$database;

Verify the P&S are in sync, may be switch log.

 

Data guard broker:

It’s a configuration primary and standby. It will join both servers.

We need separate license for this.

We can create broker in primary or standby itself.

To use of this, we can manage as centralized server and switchover is automated.