Dataguard

Standby  (DR) /dataguard
  Data guard with a failover in another far away location -- running in maximum protection mode.
   LTRA  : local , DR database within the same datacenter
   RTRA  :remote , DR database in remove/different datacenter

physical standby and logical stanby -  

physical standby: binary redo is shipped from production to standby and applied just like recovery would apply it.

logical standby: binary redo is shipped from production to standby, where it is processed and turned into logical change records, which are queued up and then processed again and turned into sql inserts/updates/delete, which are then applied. 

Standby,ADG and snapshot standby diff.

 physical standby - DB in mount and read only.

 ADG  --offers more is that you can have the database open for read-only (eg reports) whilst it is still being kept in sync with your primary database

 Snapshot - open your database read write during the day and do the recover during the night. take guaretted store point.

Switch over and fail over Diff

Finally, I want to mention briefly the terms of switchover and failover.Switchover is the planned role change. It does not require re-installation of a new database. It can be used to test interoperability of  standby database, OS and hardware maintenance. The failover is the deformation of the production (primary) database and activating standby database as the primary. It is not reversible. When enabled, re-create the standby database.

Sync and Async data transfer mode?

Sync

1 – The user initiates a transaction. This transaction is written to a redo buffer. When the user commit the transaction then the LGWR process writes it redo log file.

2 – LNS (logwriter Network Service) reports to RFS (Remote File Service) committed redo. RFS writes to standby redo log file. If we use physical standby, the MRP (Managed Recovery Process) will apply to standby database . In  Logical Standby this is made by LSP (Logical Standby Process) .

3 – RFS sends information to LNS that data is processed successfully. LNS transmits this information to LGWR . Finally, commit information is send to the user that initiated the transaction (transaction) .

Data transfer is ensured by the synchronous redo transfer. But there is a disadvantage. If  a network failure occurs between production database (Primary) and the standby database  or  Primary database can not access  to the standby database then the primary database  will hang until standby response. In other words, the primary database can not serve. To avoid such situation, I think the most logical one to use “NET_TIMEOUT” parameter. With this parameter you can determine the timeout period. In case of an outage, Primary waits until the timeout period and will continue to serve when timeout period expires. Default value of this parameter in 10g is 180s and in 11g is 30s.

Asycn

Asynchronous reod transfer flow;

1 – The user initiates a transaction. This transaction is written to a redo buffer. When the user commit the transaction then the LGWR process writes it redo log file.

2 – LNS (logwriter Network Service) reports to RFS (Remote File Service) committed redo. RFS writes to standby redo log file. If we use physical standby, the MRP (Managed Recovery Process) will apply to standby database . In  Logical Standby this is made by LSP (Logical Standby Process) .

3 – Once Redo Buffer is recycled, LNS  automatically reads redo log files and begins to send redo from log files.

RFS doesn’t send information to LNS that data is processed successfully.

What are different protection mode of standby database and sync/async transfer method?

NAME     OPEN_MOD    PROTECTION_MODE                      DATABASE_ROLE

--------     --------- ------             --------------------                      --------------------

GPRD     MOUNTED        MAXIMUM PERFORMANCE             PHYSICAL STANDBY

 ****

Log Writing Process - LGWR/ ARCH

  Network Trans Mode - SYNC/ASYNC

  Disk Write Option - AFFIRM/NOAFFIRM

******

Maximum Protection (LGWR/SYNC/AFFIRM)

  -Zero data loss – two-sided protection

  primary waits for the receipt of information. if it doesn’t respond to primary, primary hangs

Maximum Availability (LGWR/SYNC/AFFIRM)

  -Zero data loss – single-sided protection

 primary waits for the receipt of information. if it doesn’t respond to primary, primary waits until the timeout parameter . (NET_TIMEOUT)

Maximum Performance (LGWR or ARCH /ASYNC/NOAFFIRM)

 -least data loss

 primary never waits for the receipt of information

Q. How to identify which transport mode (Archiver or Log Writer) you are using to ship ?

on primary side:

 SQL> show parameter log_archive_dest_

log_archive_dest_<n>    SERVICE=visr12_standby [ARCH | LGWR]

If neither the ARCH or LGWR attribute is specified, the default is ARCH.

  NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2                   string      service="(DESCRIPTION=(ADDRESS

                                                 =(PROTOCOL=TCP)(HOST= amxh1-sc

                                                 an) (PORT=1521)) (CONNECT_DATA

                                                 =(SERVICE_NAME=SMRPDR)(SERVER=

                                                 DEDICATED)))", LGWR ASYNC NOAF

                                                 FIRM delay=0 optional compress

                                                 ion=disable max_failure=0 max_connections=1 reopen=300
                                                db_unique_name="SMRPDR" net_timeout

                                                 =30, valid_for=(all_logfiles,p

                                                 rimary_role)

How to check as OS level if dataguard setup ?
I did a ps to gather all the processes for Oracle db on the primary and standby sites, sorted them, then cross checked for differences.
I believe the only diff I found was that the standby may have ora_mrp0 or ora_pr(nn) processes, but only when doing managed recov., so no dice when standby is in a read-only reporting mode. T
he primary sometimes has an ora_cjq0, but it comes and goes when the DBMS Scheduler wakes/sleeps, so was also not reliable.
 The ora_rsm process is probably key, but I'm not using DG broker (yet).
lns and nss/nsa in standby

rfs process in standby --

 remote file server  getting the redos from the primary database ,archive log ifles 

Q. How to check if you are using Real-Time Apply or Managed ?

SQL> SELECT DEST_ID, RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS where DEST_ID in ('1','2');

   DEST_ID RECOVERY_MODE

---------- -----------------------

         1 MANAGED REAL TIME APPLY

         2 IDLE

 *** real time apply -- trasactoon as applying as soon as they are trasferred from online redo logfiles to stasdnby redo lg files evenif they are not yet archivned.

 Archves are not getting trasfered from primary to standby but they are getting generated from the content
 of online standby redo log files if we dont use real time apply

Q. How to identify standby redo logs ?

SQL> select * from v$standby_log;

Q. How to see members of standby redo log file

SQL> select * from  v$logfile where type=’STANDBY’;

Q. SRL and ORL diff,

Standby Redo Logs (SRLs)  and ORL .

 If we are in Max Protect (SYNC) mode, then SRLs are required otherwise this process will not work

How will you make sure archive that are applied gets deleted from primary.

rman target /

show all;

make sure “CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY” is presented

delete archivelog all; --> it will be deleted all the archive logs which were applied on standdy.

How to resync standby -- 

 1.)Manual registration of missing files  - If less gap say 10 archives missing
  2.)Using increment rman backup -  If gap is upwards of 100-200
  3.) RMAN rebuild   - If DR gets corrupted .

Which parameter in primary DB takes care of datafile changes ?
 Primary tablespace filled .If we add datafile in primary and it doest reflect in statndby what things will be looked for

 standby file management parameter  -- if we set it auto then automatically it takes care

Archive log location in oracle?

 show parameter reco/db_recovery_file_dest  - FRA location 

show parameter  log_archive_dest_1  -   local arcive location

show parameter  log_archive_dest_2  - standby connection

Why do we use dba file name conver and log file name convert

in the cloning -- if we provide diff locaiotn and name 

steps on standby to make phy_standby to primary.

SQL> alter database recover managed standby database cancel;

SQL> alter database activate physical standby database;

what is snapshot standby ,What it does and how does flashback rentetion policy and undo retention policy affects it

Another interesting use of dataguard is the snapshot standby, where you open your database read write during the day and do the recover during the night.

Snapshot standby in essence does:

- take a guaranteed restore point

- open the database read/write

Reverting back does:

- flashback database to that restore point

- put database back in recovery mode

So things you need to be aware of:

1) undo_retention is not relevant here

2) the amount of activity you do whilst in read/write mode will dictate how much flashback logging you will do (and you should be able to cater for that).

Steps involved in swtichover?

Swtich over:

 on primary side

 select sequence#,first_time,next_time from v$archiveed_log orderby sequece#;

 alter database commit to switchover to standby;

      - DB role changed from primary to standby

 shutdown imm

 startup nomount

 alter database mount standby database

 aleter database recover managed standby database disconnect from session

on stadnby side

 alter database commint to switchover to primary

 shut imme

 startup

 select sequence#,first_time,next_time from v$archiveed_log orderby sequece#;

Yesterday i made my physical standby database to primary (manually) and opened the database in READ WRITE mode. Because my primary database server crashed due to h/w issue and user requested me to open the database in READ WRITE mode and i did. 

Now i want to change the role back to PHYSICAL STANDBY as my actual primary database is up and running now.

*If* you have flashback enabled on the database, then you can "rewind" the standby back to the point just before you activated it, using:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> FLASHBACK DATABASE TO SCN [before_standby_became_primary_scn];

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

If you did *not* have flashback enabled, then you would need to restore a backup from before that point in time, or re-initiate the standby from scratch, whatever is most efficient for you.

Convert physical standby to snapshot standby

DB version should be 11g or higher and fra must be enabled.
Stop mrp
alter database convert to snapshot standby; ( automatically created GRP) //ensure enough space

How do you restore archive logs from tape?

RMAN> run {

2> allocate channel C1 device type SBT_TAPE  PARMS='BLKSIZE=1048576, SBT_LIBRARY=/GPRD/ora01/10.2.0/lib/libddobk.so, ENV=(STORAGE_UNIT=RMAN,BACKUP_HOST=amxp10ddom01.kci.com,ORACLE_HOME=/GPRD/ora01/10.2.0)';

3>  restore archivelog logseq 482215 ;

4> release channel t1;

5> }