Cascading Dataguard

You have a primary database in your corporate offices, and you want to create a standby database in another building on your local area network (LAN). In addition, you have a legal insurance requirement to keep the redo data and backup copies off site at a geographically distant location outside of your LAN but on your wide area network (WAN).

You could define two destinations on your primary database so that redo data could be transmitted to both of these sites, but this would put an extra workload on your primary database throughput due to the network latency of sending the redo data over the WAN.

To solve this problem, you could define a tight connection between your primary and physical standby databases in your LAN using the LGWR and SYNC network transports and standby redo log files. This would protect against losing access to the primary database, and it provides an alternate site for production when maintenance is required on the primary database. The secondary location on the WAN could be serviced by the physical standby database, ensuring that the redo data is stored off site. Nightly backups on the production database could then be moved to the WAN remote standby database, which removes the requirement to ship tapes to the off-site storage area.

Finally, in a worst case scenario, where you lose access to both the primary database and the physical standby database on the LAN, you could fail over to the remote standby database with minimal data loss. If you can gain access to the redo log file of the last standby database from the original standby database, you could recover it on the remote standby database, incurring no data loss.

The only time you would incur problems by sending the information over the WAN is during a switchover or failover, when the physical standby database has transitioned to the primary role. However, this configuration would still meet your insurance requirements.

To reduce the load on your primary system, you can implement cascaded destinations, whereby a standby database receives its redo data from another standby database, instead of directly from the primary database. You can configure a physical standby database to retransmit the incoming redo data it receives from the primary database to other remote destinations in the same manner as the primary database

Active Database duplication

Oracle 11g introduced the ability to create duplicate databases directly without the need for a backup. This is known as active database duplication. The process is similar to the backup-based duplication

• You don't need a backup of the source system, but it does have to be in archive mode
• The passwords in the password files must match for both servers
• Both the source and destination database servers require a "tnsnames.ora" entry for the destination database
• The destination server requires static listener configuration in a "listener.ora" file
  SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC =
        (GLOBAL_DBNAME = DB11G.WORLD)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        (SID_NAME = DB11G)
      )
    )
  
  LISTENER =
    (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dup2.localdomain)(PORT = 1521))
      )
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
    )

The command are

RMAN > DUPLICATE DATABASE TO DB11G   FROM ACTIVE DATABASE   SPFILE   NOFILENAMECHECK;

To create a standby database from active database files:

DUPLICATE TARGET DATABASE   FOR STANDBY   FROM ACTIVE DATABASE   DORECOVER   SPFILE     SET "db_unique_name"="foou" COMMENT ''Is a duplicate''     SET LOG_ARCHIVE_DEST_2="service=inst3 ASYNC REGISTER      VALID_FOR=(online_logfile,primary_role)"     SET FAL_CLIENT="inst3" COMMENT "Is standby"     SET FAL_SERVER="inst1" COMMENT "Is primary"   NOFILENAMECHECK

Oracle Data Guard Broker DGMGRL Configuration

Step-by-step instructions on how to set up  DG broker

1. Set up init parameters on primary to enable broker
   
   

   ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH; ALTER SYSTEM SET DB_UNIQUE_NAME='apple1'; ALTER SYSTEM SET DB_DOMAIN='db_domain';

2. Set up init parameters on standby
   
   

   ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH; ALTER SYSTEM SET DB_UNIQUE_NAME='apple2'; ALTER SYSTEM SET DB_DOMAIN='db_domain';

3. GLOBAL_DBNAME should be set to <<db_unique_name>>_DGMGRL.<<db_domain>> in listener.ora on all instances of both primary and standby.
   
   
   

   SID_LIST_LISTENER =   (SID_LIST =  (SID_DESC =         (GLOBAL_DBNAME = apple1_dgmgrl)         (ORACLE_HOME = c:\oracle\1020)         (SID_NAME = apple1)         ) )

4. TNSNAMES.ora need to change to the correct service_names as the listener.ora
   
   This is important otherwise you'll have TNS-12154 error during switchover operation.
5. Create the configuration
   
   
   

   CREATE CONFIGURATION 'AppleDR' AS > PRIMARY DATABASE IS 'apple1' > CONNECT IDENTIFIER IS 'apple1'ADD DATABASE 'apple2' AS > CONNECT IDENTIFIER IS 'apple2' maintained as physical;

6. Enable the configuration
   

   DGMGRL> ENABLE CONFIGURATION Enabled. DGMGRL> SHOW CONFIGURATION > show database verbose apple1

   
   
7. Troubleshooting
    Let us see some sample issues and their fix
   
   Issue
   
   

   DGMGRL> CONNECT sys/sys  ORA-16525: the Data Guard broker is not yet available  Fix  Set dg_broker_start=true

   
   
   
   Issue
    After enabling the configuration, on issuing SHOW CONFIGURATION, this error comes
   Warning: ORA-16608: one or more sites have warnings
   
   Fix
    To know details of the error, you may check log which will be generated at bdump with naming as drc{DB_NAME}.log or there are various monitorable properties that can be used to query the database status and assist in further troubleshooting.
8. Monitoring the Data Guard Broker Configuration
   
   If we receive any error or warnings we can obtain more information about the same by running the commands as shown below. In this case there is no output seen because currently we are not experiencing any errors or warning.
   
   
   

   DGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport'; DGMGRL> SHOW DATABASE 'TESTPRI' 'LogXptStatus'; DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties'; DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentLogXptProps'; DGMGRL> SHOW DATABASE 'TESTDG' 'StatusReport'; DGMGRL> SHOW DATABASE 'TESTDG' 'LogXptStatus'; DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentProperties'; DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentLogXptProps';

   
   
   ;
    

Snapshot standby in Oracle 10g

Snapshot standby allow the standby database to open for testing, reporting prupose. You can revert back the transactions to continue in standby mode. You can also use snapshot standby in Oracle 10g.  11g reduces these steps making this a feature they call the “SNAPSHOT DATABASE”.
Steps:1.Set Flash Recovery Area parameters
2.Create a guranteed restore point
3.Activate standby database
4.Perform read/write testing
5.Revert to original standby
1.Set Flash Recovery Area parameters

SQL> alter system set db_recovery_file_dest_size=2G; SQL> alter system set db_recovery_file_dest='D:\oracle\product\10.2.0\fra';

2.Create a guranteed restore pointto revert to when you need to return to the original state.

SQL> create restore point pre_activ_snap guarantee flashback database; SQL> select name, time, storage_size,GUARANTEE_FLASHBACK_DATABASE from V$restore_point; NAME TIME STORAGE_SIZE GUA -------------------------------------------------- -------------------------------- ------------ --- PRE_ACTIV_SNAP 20-MAR-10 07.59.35.000000000 PM 8192000 YES

3.Activate the standby database

SQL> alter database activate standby database; SQL> select name, open_mode, controlfile_type from V$database; NAME OPEN_MODE CONTROL -------------------------------------------------- -------------------- ------- ORCL MOUNTED CURRENT SQL> alter database open; SQL> select name, open_mode, controlfile_type from V$database; NAME OPEN_MODE CONTROL -------------------------------------------------- -------------------- ------- ORCL READ WRITE CURRENT

Now the standby database is in READ/WRITE mode after the activation.

PRMY> ALTER SYSTEM ARCHIVE LOG CURRENT; PRMY> ALTER SYSTEM SET DG_BROKER_START=FALSE; PRMY> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

NOW you can use the physical standby safely for testing purposes, it is a fully functional database with data the same as production as of a certain point in time, no new data will be added…remember your Recovery Time Objective.

When testing is over, Revert….back into a physical standby.

STBY> STARTUP MOUNT FORCE; STBY> FLASHBACK DATABASE TO RESTORE POINT HOTFIX1; STBY> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; STBY> STARTUP MOUNT FORCE; STBY> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

(*using REAL-TIME APPLY)

Some useful SQL commands

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT; select * from v$flash_recovery_area_usage;

Setting Up Standby Database

When you create your STANDBY database, you'll need to create directories for database administration files, database files, and archive logs. You'll also need to prepare the standby instance by copying and configuring a parameter file, creating a password file, and creating Windows services on Windows


Standby init.ora file

db_name = PRACTICE
instance_name = STANDBY
service_names = STANDBY
control_files = ("/oradata/STANDBY/standby.ctl)
log_archive_dest_l = 'location=/oracata/STANDBY/archive'
LOG_ARCHIVE_DEST_2 = "MANDATORY service=STANDBY reopen=30"
standby_archive_dest = "/oradata/STANDBY/archive"
background_dump_dest = /app/oracle/admin/STANDBY/bdump
user_dump_dest = /app/oracle/admin/STANDBY/udump
db_file_name_convert = "/oradata/PRACTICE", "/oradata/STANDBY" log_file_name_convert = "/oradata/PRACTICE", "/oradata/STANDBY"
lock_name_space = STANDBY

Mount standby database

LINUX> export ORACLE_SID=STANDBY;
LINUX> sqlplus /nolog
SQL> CONNECT sys/standby AS SYSDBA;
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE

Recover standby database
SQL> RECOVER MANAGED STANDBY DATABASE;

If your physical standby has standby redo logs configured, it is possible to have the MRP begin applying changes as soon as they arrive to the standby instead of waiting for a log switch boundary and for the standby redo log to be archived. This new functionality is called real-time apply.

SQL> Recover managed standby database using current logfile;

Activate the Standby Database

1. Cancel standby database
   SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
   
2. Activate Standby databse
   SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
   SQL> SHUTDOWN;
   SQL> STARTUP;

Steps : For Failover

1. Perform terminal recovery on the standby by issuing managed recovery with the FINISH keyword. The following command is to be used if you have
   
   alter database recover managed standby database finish;
2. If you do not have standby redo logs, or they are not active, you must enter the following command:
   
   alter database recover managed standby database finish skip standby logfile;
3. Once the terminal recovery command completes, convert the standby into a primary database by entering the following command:
   
   alter database commit to switchover to primary;
4. Step 5. Restart the new primary database.

Using Rman to create standby database


RMAN > connect target /
RMAN > run {
backup database
include current controlfile for standby;
sql "alter system current log file";
}


RMAN > connect auxiliary /
RMAN > Duplicate target database for standby dorecover;

Checking archive log statusPrimary
SQL > select status, error from v$archive_dest;

Standby
SQL > select * from v$standby_log;

Confirm Managed Recovery
How can you know if the managed archive propagation process is running properly? You can look in three places:.

1. Archive files Look at the STANDBY database archive destinationfor archive logs being transmitted from the primary. New archivelog files on the PRACTICE database will be reproduced in the/oradata/STANDBY/archive directory.
   
   
2. Standby alert log Check the STANDBY alert.log for archive logs applicationentries. If you haven't seen any activity yet, perform a few log switches on thePRACTICE database. Wait a few minutes and look for evidence that the newarchive logs were transported and applied.
   
   
3. Media Recovery Log /oradata/STANDBY/archive/71.arc Media Recovery Waiting for thread 1 seqtt 72
   Log history The third and final test is to select from v$log_history onboth the primary and standby databases. The following query should return the same number, bearing in mind that the standby might be afew seconds behind:.
   
   SQL> SELECT MAX(sequence^) FROM v$log_history;