Incremental RMAN backup approach in 2 node RAC and standalone standby

Incremental RMAN backup approach in 2 node RAC and standalone standby
----------------------------------------------------------------------

Primary - 2 node RAC - deopvdvvnode5a(TEST1),deopvdvvnode5b(TEST2)
Secondary - standalone DB - deopvdvvnode4a(TEST1)

1) Find current_scn number from both primary and standby.

PRIMARY:TEST\sys> select current_scn from v$database;

CURRENT_SCN
-----------
   65445955

TEST1\sys> select current_scn from v$database;

CURRENT_SCN
-----------
   42188320

2) Find the exact time , since its lagging. Need to hit for both the scn on primary , since on standby it will fail.

PRIMARY:TEST\sys> select scn_to_timestamp(65445955) from dual;

SCN_TO_TIMESTAMP(65445955)
---------------------------------------------------------------------------
02-MAR-16 04.02.32.000000000 PM

select scn_to_timestamp(42188320) from dual;

3)  Stop the managed standby apply process:

SQL> alter database recover managed standby database cancel;

Database altered.

4)  Shutdown the standby database

5)  On the primary, take an incremental backup from the SCN number where the standby has been stuck:

run {
allocate channel c1 type disk format '/opt/app/oracle/oraback/%U.rmb';
backup incremental from scn 42188320 database;
}

RMAN> run {
allocate channel c1 type disk format '/opt/app/oracle/oraback/%U.rmb';
backup incremental from scn 42188320 database;
}2> 3> 4>

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=15 instance=TEST1 device type=DISK

Starting backup at 02-MAR-16

backup will be obsolete on date 09-MAR-16
archived logs will not be kept or backed up
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DG_TEST/test/datafile/sysaux.257.893130419
input datafile file number=00001 name=+DG_TEST/test/datafile/system.256.893130419
input datafile file number=00003 name=+DG_TEST/test/datafile/undotbs1.258.893130419
input datafile file number=00006 name=+DG_TEST/test/datafile/undotbs2.268.893130659
input datafile file number=00005 name=+DG_TEST/test/datafile/example.267.893130515
input datafile file number=00004 name=+DG_TEST/test/datafile/users.259.893130419
channel c1: starting piece 1 at 02-MAR-16
channel c1: finished piece 1 at 02-MAR-16
piece handle=/opt/app/oracle/oraback/0dqvg095_1_1.rmb tag=TAG20160302T164109 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:46

backup will be obsolete on date 09-MAR-16
archived logs will not be kept or backed up
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 02-MAR-16
channel c1: finished piece 1 at 02-MAR-16
piece handle=/opt/app/oracle/oraback/0eqvg0an_1_1.rmb tag=TAG20160302T164109 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-MAR-16
released channel: c1

6)  On the primary, create a new standby controlfile:

PRIMARY:TEST\sys> alter database create standby controlfile as '/opt/app/oracle/oraback/DEL1_standby.ctl';

Database altered.

7)  Copy all the files from primary to standby host:

8)  Bring up the instance in nomount mode:

SQL> startup nomount

9)  Check the location of the controlfile:

SQL> show parameter control_files

10) Restore the standby control file.

RMAN> restore standby controlfile from '/opt/app/oracle/oraback/DEL1_standby.ctl';

11)  Mount the standby database:

RMAN> alter database mount;

12) Catalog the backups.

13) Recover these files.

RMAN> recover database;

Starting recover at 02-MAR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=329 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/02/2016 17:01:29
RMAN-06094: datafile 1 must be restored

RMAN> exit

Error came as RMAN was using information from control file with Primary ASM directory strucuture. So we need to use switch command to upate RMAN repository.

14) Switch datafiles.

RMAN> switch datafile 1 to copy;
RMAN> switch datafile 2 to copy;
RMAN> switch datafile 3 to copy;
RMAN> switch datafile 4 to copy;
RMAN> switch datafile 5 to copy;
RMAN> switch datafile 6 to copy;

15) Start recovery again.

TRAINING_STANDBY:deopvdvvnode4a:[TEST1]:/opt/app/oracle/oraback> rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 2 17:12:43 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2191276923, not open)

RMAN> recover database;

Starting recover at 02-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=312 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DG_DATA/test_stby/datafile/system.257.899494475
destination for restore of datafile 00002: +DG_DATA/test_stby/datafile/sysaux.261.899494475
destination for restore of datafile 00003: +DG_DATA/test_stby/datafile/undotbs1.260.899494475
destination for restore of datafile 00004: +DG_DATA/test_stby/datafile/users.263.899494493
destination for restore of datafile 00005: +DG_DATA/test_stby/datafile/example.256.899494475
destination for restore of datafile 00006: +DG_DATA/test_stby/datafile/undotbs2.262.899494491
channel ORA_DISK_1: reading from backup piece /opt/app/oracle/oraback/0dqvg095_1_1.rmb
channel ORA_DISK_1: piece handle=/opt/app/oracle/oraback/0dqvg095_1_1.rmb tag=TAG20160302T164109
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

starting media recovery

archived log for thread 1 with sequence 7232 is already on disk as file +DG_DATA/test_stby/archivelog/2016_03_02/thread_1_seq_7232.882.905446609
archived log for thread 2 with sequence 7064 is already on disk as file +DG_DATA/test_stby/archivelog/2016_03_02/thread_2_seq_7064.884.905446609
archived log file name=+DG_DATA/test_stby/archivelog/2016_03_02/thread_1_seq_7232.882.905446609 thread=1 sequence=7232
archived log file name=+DG_DATA/test_stby/archivelog/2016_03_02/thread_2_seq_7064.884.905446609 thread=2 sequence=7064
unable to find archived log
archived log thread=1 sequence=7233
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/02/2016 17:13:06
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 7233 and starting SCN of 65468812

RMAN> exit

Error is exepected as all the archives has been applied and its waiting for a particular sequence.

16) Start the managed recovery process.

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

17) Check the primary and standby with below queries to find if primary/standby are in sync.

Primary:
SQL >

select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

PRIMARY:TEST\sys>   2    3    4

   THREAD# Last Primary Seq Generated
---------- --------------------------
         1                       7242
         2                       7070


PhyStdby:
SQL >

select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

   THREAD# Last Standby Seq Received
---------- -------------------------
         1                      7242
         2                      7070


PhyStdby:
SQL >

TEST1\sys> select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and applied='YES'
group by thread# order by 1;  2    3    4    5

   THREAD# Last Standby Seq Applied
---------- ------------------------
         1                     7241
         2                     7070


Note - Though its a 2-node cluster, incremental approach do not change much. Once all steps are done, you might see a lot of latency for thread 2. But after some time, both the threads will be in sync. 

A brief overview of Dataguard in 11g

Dataguard

 In a Data Guard configuration, a production database is referred to as a primary database. A standby database is a synchronized copy of the primary database. Using a backup copy of the primary database, you can create from one to 30 standby databases. The standby databases, togetherwith the primary database, make up a Data Guard configuration.

-- Using a backup copy of the primary database, We can create from one to 30 standby       databases

 Snapshot Standby Database

  

 A snapshot standby database is a database that is created by converting a physical standby database into a snapshot standby database. The snapshot standby database receives redo from the primary database, but does not apply the redo data until it is converted back into a physical standby database. The snapshot standby database can be used for updates, but those updates are discarded before the snapshot standby database is converted back into a physical standby database. The snapshot standby database is appropriate when We require a temporary, updatable version of a physical standby database.

 

 Services

 1) Redo transport

 2) Apply services

a) Redo Apply

b) SQL Apply

 3) Role management services

 Transitions

  

 1) Switch Over

 2) Failover

Processes

 

 Primary Database

 

 1) LGWR - Collect transactions information and updates online redo logs. 

SYNC - Online Redo logs (not archive logs) are directly passed to LNS (Log Writer network server) process,which ships the redo to RFS(Remote file server) on the standby. LGWR wait for confirmation from LNS before ack commit. 

ASYNC - independent LNS processes read the redo from either the redo log buffer in memory or the online redo log file, and then ship the redo to its standby database.

Other than starting the asynchronous LNS processes, LGWR has no interaction with any asynchronous standby destination.

  2) Archiver (ARCn)

The ARCn process creates a copy of the online redo log files locally for use in a primary database recovery operation. 

ARCn is also responsible for shipping redo data to an RFS process at a standby database and for proactively detecting and resolving gaps on all standby databases. 

For Oracle Database 11g Release 2 (11.2), there can now be 30 archiver processes. The default value is four.

 Standby Database

 

 1) RFS (Remote File Server) - 

RFS receives redo information from the primary database and can write the redo into standby redo logs or directly to archived redo logs

Each LNSn and ARCn process from the primary database has its own RFS process.

 2) Archiver 

 

 3) MRP (Managed Recovery)

MRP is responsible for actual recovery. It applies the archive logs to the physical standby database

 4) LSP (Logical standby process)

 -- The limit of 30 standby databases is imposed by the LOG_ARCHIVE_DEST_n parameter

 

 -- We can use the Cascaded Redo Log Destinations feature to incorporate more than 30     

     standby databases in Wer configuration.

 

 -- Gap resoultion is automatic and is done with the help of archiver process. 

 

 Data Protection Modes

 

 1) Maximum Protection

-- This protection mode guarantees that no data loss occurs if the primary database fails. 

-- REDO data should be written to both local online redo log and standby redo log and then only commit will happen.

-- To ensure no data is lost, primary is shutdown if log is not written remotely to the standby redo log.

 2) Maximum Availability

 

-- This protection mode provides the highest possible level of data protection without compromising the availability of the primary database.

-- Its same as maximum protection, only the primary wont be shutdown if the redo data is not written to both online redo log and standby redo log. 

-- When all the gaps are resolved and the primary database is synchronized with the standby database, the primary database automatically resumes operating in maximum availability mode.

 

 3) Maximum Performance

-- The default protection mode provides the highest possible level of data protection without affecting the performance of the primary database

-- Primary DB will always be available, irrespective of standby synchornization. 

-- REDO data is written asynchronously to all the standby database.

Creating Phsical Standby Database

-- Force Logging

-- FORCE LOGGING forces redo to be generated even when NOLOGGING operations are executed.

-- Temporary tablespaces and temporary segments are not logged.

-- A standby redo log is required to implement:

• Synchronous transport mode

• Real-time apply

• Cascaded redo log destinations

• Role Changes.

SQL > SELECT group#, type, member FROM v$logfile

 where type='STANDBY';

 

SQL > SELECT group#, dbid, thread#, sequence#, status

 FROM v$standby_log;

 

-- Setting Initlization Parameters on the primary Database to control redo transport.

Mandatory

1) LOG_ARCHIVE_CONFIG

Syntax

LOG_ARCHIVE_CONFIG = {

[ SEND | NOSEND ][ RECEIVE | NORECEIVE ]

[ DG_CONFIG=(remote_db_unique_name1

[, ... remote_db_unique_name9) | NODG_CONFIG ] }

2) LOG_ARCHIVE_DEST_n

Syntax

LOG_ARCHIVE_DEST_2= 'SERVICE=test_stby VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test_stby' 

LOG_ARCHIVE_DEST_STATE_2=ENABLE

3) LOG_ARCHIVE_DEST_STATE_n

Syntax

service= streiprdb4.bizprd LGWR ASYNC net_timeout=20 reopen=30 db_unique_name=streiprdb4_bizprd valid_for=(all_logfiles,primary_role)

-- VALID_FOR (Role Based Destinations)

We supply two values for the VALID_FOR attribute: redo_log_type and database_role.

The redo_log_type keywords are:

• ONLINE_LOGFILE: This destination is used only when archiving online redo log files.

• STANDBY_LOGFILE: This destination is used only when archiving standby redo log files or receiving archive logs from another database.

• ALL_LOGFILES: This destination is used when archiving either online or standby redolog files.The database_role keywords are the following:

• PRIMARY_ROLE: This destination is used only when the database is in the primary database role.

• STANDBY_ROLE: This destination is used only when the database is in the standby (logical or physical) role.

• ALL_ROLES: This destination is used when the database is in either the primary or the standby (logical or physical) role.

 

Note - The VALID_FOR attribute is optional. However, Oracle recommends that you define a VALID_FOR attribute for each destination so that your 

      Data Guard configuration operates properly after a role transition.

  

Do not use the default value, VALID_FOR=(ALL LOGFILES, ALL_ROLES), for logical standby databases

  

-- SYNC/ASYNC AFFIRM/NOAFFIRM (Redo Transport Mode)

• SYNC - This is for maximum protection i.e. it will make sure that the transactions are written to atleast one standby and then 

will commit the transactions

• ASYNC - This is the default and maximum performance. No need for ACK from LNS to commit. 

• AFFIRM - Specifies that a redo transport destination acknowledges received redo data after writing it to the standby redo log

• NOAFFIRM - Specifies that a redo transport destination acknowledges received redo data before writing it to the standby redo log

 

4) FILE_NAME_CONVERT (If disk configuration is not the same)

DB_FILE_NAME_CONVERT

• DB_FILE_NAME_CONVERT applies only to a physical standby database and duplicate RMAN script. 

• If the standby database uses Oracle Managed Files (OMF), do not set the DB_FILE_NAME_CONVERT parameter.

• There is a 255-character limit on this parameter.

LOG_FILE_NAME_CONVERT

• LOG_FILE_NAME_CONVERT applies only to a physical standby database and duplicate RMAN script. 

STANDBY_FILE_MANAGEMENT

• STANDBY_FILE_MANAGEMENT applies to physical standby databases only, but can be set on a primary database for role changes.

5) FAL_CLIENT and FAL_SERVER

On physical standby databases, fetch archive log (FAL) provides a client/server mechanism for resolving gaps detected in the range of archived redo logs that are generated at the primary database and received at the standby database.

FAL_CLIENT - This is no longer required in 11g. 11g automatically detects it. 

FAL_SERVER - This initialization parameter specifies the Oracle Net service name for the standby database. 

 -- Primary Parameters Example

DB_NAME=test_prmy

DB_UNIQUE_NAME=test_prmy

LOG_ARCHIVE_CONFIG='DG_CONFIG=(test_prmy,test_stby)'

CONTROL_FILES='/u01/app/oracle/oradata/test_prmy/control1.ctl',

'/u01/app/oracle/oradata/test_prmy/control2.ctl'

LOG_ARCHIVE_DEST_2='SERVICE=test_stby VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=test_stby'

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=test%t_%s_%r.arc

 -- Creating an Oracle Net Service Name for Your Physical Standby Database

 

 -- Creating a Listener Entry for Your Standby Database

 

 -- Copying Your Primary Database Password File to the Physical Standby Database Host

 

 -- Creating an Initialization Parameter File for the Physical Standby Database

 

1) Create a parameter file only with the db name when using RMAN duplicate command as RMAN will automatically create spfile.

DB_NAME=test_prmy

2) Creating Directories for the Physical Standby Database

-- audit trail directory

-- directory for the data files

 -- Starting the Physical Standby Database in nomount mode.

 

 -- RMAN duplicate script to create the physical standby database

 

vi  phys_standby

 

run {

allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate auxiliary channel stby type disk;

duplicate target database for standby from active database

spfile

parameter_value_convert 'test_prmy','test_stby'

set db_unique_name='test_stby'

set db_file_name_convert='/test_prmy/','/test_stby/'

set log_file_name_convert='/test_prmy/','/test_stby/'

set control_files=

'/u01/app/oracle/oradata/test_stby.ctl'

set log_archive_max_processes='5'

set fal_client='test_stby'

set fal_server='test_prmy'

set standby_file_management='AUTO'

set log_archive_config='dg_config=(test_prmy,test_stby)'

set log_archive_dest_2='service=testprmy ASYNC

valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE)

db_unique_name=test_prmy';

}

 RMAN> connect target sys/password

 RMAN> connect auxiliary sys/password@test_stby

 RMAN> @phys_standby

 Enabling Real-Time Apply

 

• When we enable the optional real-time apply feature, log apply services apply the redo data from standby redo log files in real time.

• log apply services can apply redo data as it is received, without waiting for the current standby redo log file to be archived. This results in faster

switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins

• Execute the following command on the standby database to start Redo Apply:

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