Logical Standby behaves similarly to physical standby, except it uses a more complex method of applying SQL statements (logical) instead of block level redo changes (physical). While It is rarely used as a protection mechanism for a primary database it is common to use logical standby as a reporting database. Possible reasons for using logical standby are:
- It can be used for rolling database upgrades (e.g. upgrade primary database first, standby database later).
- Saves extra license cost of Active Data Guard option if you want the standby database to be open read only while still keeping up to date with the primary database (logical standby databases do this by default without extra cost options).
- You can add additional schemas and database objects to support your reporting architecture while keeping the replicated schemas under data guard replicated mode.
However, all of these benefits will come at a cost if the reporting database ever encounters disasters and you need to restore the database. Rebuilding a logical standby database from primary can lead to extended outage for the user. Rebuilding from primary will also not restore all the additional schemas and reporting specific tuning implemented to the logical standby database since last build.
This article demonstrates how you can protect your reporting environment by using a back up your logical standby database using the traditional methods available using rman and then recover it by restoring the database backup and performing media recovery on the archived logs, in conjunction with the backup.
The following steps describe step by step approach using “no catalog” option with a preconfigured auto backup location for the logical standby database running on a two node real application cluster database.
When SQL Apply is started for the first time following point-in-time recovery, it must be able to either find the required archived logs on the local system or to fetch them from the primary database. Use the V$LOGSTDBY_PROCESS view to determine if any archived logs need to be restored on the primary database.
Step 1. Turn off the Cluster by setting cluster_database=false
Step 2. Startup the db in nomount state and verify cluster is turned off
Step 3. Shutdown and Startup the db in Mount state
Step 4. Restore Control file from backup using point in time option
Step 5. Restore and Recover database from backup using point in time option
Step 6. Turn on the cluster by setting cluster_database=true
Step 7. Start logical apply process
| #!/bin/kshexport ORACLE_HOME=/ora/app/oracle/product/11.2.0/dbhome_1export ORACLE_SID=LGSTBYDB1export RESTORECTL=/home/oracle/cdsiusa/LGSTBYDB/rman/logs/restoreCTL.logexport RESTOREDB=/home/oracle/cdsiusa/LGSTBYDB/rman/logs/restoreDB.log
echo “——————————————————————————” echo “`date` [1. Turn off cluster database]” echo “——————————————————————————” ${ORACLE_HOME}/bin/sqlplus “/ as sysdba” << EOF alter system set cluster_database=false scope=spfile; shutdown immediate; exit EOF echo “——————————————————————————” echo “`date` [2. Logical Standby Database…startup nomount]” echo “——————————————————————————” ${ORACLE_HOME}/bin/sqlplus “/ as sysdba” << EOF shutdown abort; startup nomount; exit EOF echo “——————————————————————————” echo “`date` [3. Logical Standby Database…startup mount]” echo “——————————————————————————” ${ORACLE_HOME}/bin/sqlplus “/ as sysdba” << EOF shutdown abort; startup mount; exit EOF echo “——————————————————————————” echo “`date` [4. Logical Standby Database…restore controlfile from backup]” echo “——————————————————————————” ${ORACLE_HOME}/bin/rman target / MSGLOG ${RESTORECTL} << EOF run { set until time “TO_DATE(’03-11-2013 15:22:00′, ‘DD-MM-YYYY HH24:MI:SS’)”; allocate channel c1 device type disk; restore controlfile to ‘+DG_DATA_01/LGSTBYDB/control01.ctl’; release channel c1; allocate channel c2 device type disk; replicate controlfile from ‘+DG_DATA_01/LGSTBYDB/control01.ctl’; release channel c2; } exit EOF echo “——————————————————————————” echo “`date` [5. Logical Standby Database…restore and recover database from backup]” echo “——————————————————————————” ${ORACLE_HOME}/bin/rman target / MSGLOG ${RESTOREDB} << EOF SET PARALLELMEDIARESTORE OFF run { set until time “TO_DATE(’03-11-2013 15:22:00′, ‘DD-MM-YYYY HH24:MI:SS’)”; allocate channel c1 device type disk; allocate channel c2 device type disk; restore database; recover database; release channel c1; release channel c2; } exit EOF echo “——————————————————————————” echo “`date` [6. Turn on cluster database]” echo “——————————————————————————” ${ORACLE_HOME}/bin/sqlplus “/ as sysdba” << EOF alter system set cluster_database=true scope=spfile; shutdown immediate; startup; exit EOF echo “——————————————————————————” echo “`date` [7. Start Logical Standby Apply Process]” echo “——————————————————————————” ${ORACLE_HOME}/bin/sqlplus “/ as sysdba” << EOF alter database start logical standby apply; exit EOF |
Start a conversation about Managed Services
- We provide a personalized experience
- Thought & strategic leadership is included for all our clients
- Flexible arrangements working on-site or remote
- Boutique services, so that you don’t pay for services you don’t need
- One-on-one support & training for your staff
- We provide you with expert-level resources with real world experience
