To start the recovery process:
SQL> alter database recover managed standby database disconnect from session;
The disconnect from session places the recovery in the background, returning you to the SQL prompt.
To start the managed recover using Real Time Apply, use:
SQL> alter database recover managed standby database using current logfile;
To stop the managed recovery:
SQL> alter database recover managed standby database cancel;
To open the standby database, first stop the managed recovery process using the above command.
Then open the database. Because the control file is a standby control file, the database will open read only.
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
Or
SQL> alter database open read only;
To close the database and return it to managed recovery simply restart the recovery process.
SQL> alter database recover managed standby database using current logfile;
To startup the standby database simply start the database in a mount mode. Because the standby uses a standby control file it will automatically mount as a standby database.
SQL> startup mount;
If you start the database using just the startup command the database will automatically open read only. Once you start the managed recovery process, the database will close to a mount state and begin recovery.
With 11g you can configure the database to open read only and continue to recover changes while the database is open. To do this you must cancel the recovery process, open the standby, and then restart the recovery process.
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database disconnect from session;
Flashback Database is a technology introduced in Oracle 10g. Its function is to move the entire database back in time. If you flashback the primary database, you must also flashback the standby database. This is relatively simple as the standby is a copy of the production using the same scns. If you flashback the primary to an scn, then you must flashback the standby to the same or an earlier scn. Again, if flashback is active on the primary, then it must also be active on the standby. This is one of the few changes that does not propagate from the primary to the standby. This is because turning on flashback is executed in the control file, which does not get passed to the standby. So it you turn on flashback on the primary, you must also turn it on for the standby.
To flashback the primary database one hour:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT EXCLUSIVE
SQL> FLASHBACK DATABASE TO TIMESTAMP(SYSDATE-(1/24);
SQL> ALTER DATABASE OPEN RESETLOGS;
To flashback the standby execute the same commands.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT EXCLUSIVE
SQL> FLASHBACK DATABASE TO TIMESTAMP(SYSDATE-(2/24);
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
To turn on flashback use the following commands;
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN; ALTER DATABASE RECOVER?
One of the drawbacks of a Managed Standby is that if you actually need to temporarily use the standby in a read write mode, you would have to rebuild the standby because opening the database read write requires an open reset logs, creating a new instantiation. However in 10g and beyond, you can use flashback database to return the standby database to a point in time before you opened it read write. To perform this action you must first create a restore point before opening the database. This is the point that we will flash the database back to. You can also use a timestamp or an scn but a restore point is much easier. Either of the command below will create a restore point called ?before_open?.
SQL> create restore point before_open;
SQL> create restore point before_open guarantee flashback database;
You cannot create a restore point on the primary database and using it on the standby database. The restore point must be created on the standby database.
Now you can open the database read write.
SQL> alter database recover managed standby database finish force;
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
SQL> alter database open;
Or
SQL> alter database commit to switchover to primary;
SQL> STARTUP MOUNT FORCE;
SQL> alter database open;
To return the database to a managed standby you will:
1. Shutdown the open standby database.
2. Startup mount.
3. flashback the database to the restore point.
4. convert the control file back to a standby control file.
5. startup mount
6. restart managed recovery.
The actual commands look like this:
SQL> shutdown abort;
SQL> startup mount;
SQL> flashback database to restore point before_open;
SQL> alter database convert to physical standby;
SQL> startup mount force;
SQL> alter database recover managed standby?
Monitoring the Physical Standby
To insure the standby database is ready to perform a switchover or failover you must monitor the recovery progress. You can get information about the recovery process from a number of sources to include the alert log on both the primary and standby, the dbconsole and v$archived_log.
To insure the managed recover process is running:
Select
PROCESS,
SEQUENCE#,
STATUS
From
V$MANAGED_STANDBY;
One of the easiest way to verify that the standby is up to date with the primary is to compare the sequence number of the two database. The script below will get the last sequence number that was applied on the standby and compare it to the max sequence number of the primary. This script is for Linux but can be adapted for Windows.
#!/bin/ksh
ORACLE_SID=db06
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep $ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
$ORACLE_HOME/bin/sqlplus /nolog<<!
connect / as sysdba
set pages 0 heading off feedback off
spool /tmp/standby_applied.lst
select max(sequence#) from v$archived_log where applied = 'YES';
spool off;
connect perfstat/perfstat@DB09.ORACLE.COM
set pages 0 heading off feedback off
spool /tmp/primary_current.lst
select max(sequence#) from v$archived_log;
spool off;
exit
!
applied=`cat /tmp/standby_applied.lst`
current=`cat /tmp/primary_current.lst`
statsvalue=$(($current - $applied))
echo $statsvalue
if [[ $statsvalue -gt 4 ]];
then
echo "The Standby Database is falling behind!" > /tmp/stdby_alert.lst
echo "Standby Applied Sequence: $applied" >> /tmp/stdby_alert.lst
echo "Primary Current Sequence: $current" >> /tmp/stdby_alert.lst
echo "Difference: $statsvalue" >> /tmp/stdby_alert.lst
echo "" >> /tmp/stdby_alert.lst
df -h >> /tmp/stdby_alert.lst
echo "**********************************************************"
echo "The Standby is NOT Current"
cat /tmp/stdby_alert.lst |mail -s ?PROD Standby Monitor Alert **"\
prod_emergency@my_company.net
echo "**********************************************************"
fi
The script above will provide and email alert if the standby database falls behind the production database by more than four sequence number. Once scheduled to run hourly in a cron, you will quickly become aware if the standby stops, is not applying redo, or is just falling behind.
SQL> alter database recover managed standby database disconnect from session;
The disconnect from session places the recovery in the background, returning you to the SQL prompt.
To start the managed recover using Real Time Apply, use:
SQL> alter database recover managed standby database using current logfile;
To stop the managed recovery:
SQL> alter database recover managed standby database cancel;
To open the standby database, first stop the managed recovery process using the above command.
Then open the database. Because the control file is a standby control file, the database will open read only.
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
Or
SQL> alter database open read only;
To close the database and return it to managed recovery simply restart the recovery process.
SQL> alter database recover managed standby database using current logfile;
To startup the standby database simply start the database in a mount mode. Because the standby uses a standby control file it will automatically mount as a standby database.
SQL> startup mount;
If you start the database using just the startup command the database will automatically open read only. Once you start the managed recovery process, the database will close to a mount state and begin recovery.
With 11g you can configure the database to open read only and continue to recover changes while the database is open. To do this you must cancel the recovery process, open the standby, and then restart the recovery process.
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database disconnect from session;
Flashback Database is a technology introduced in Oracle 10g. Its function is to move the entire database back in time. If you flashback the primary database, you must also flashback the standby database. This is relatively simple as the standby is a copy of the production using the same scns. If you flashback the primary to an scn, then you must flashback the standby to the same or an earlier scn. Again, if flashback is active on the primary, then it must also be active on the standby. This is one of the few changes that does not propagate from the primary to the standby. This is because turning on flashback is executed in the control file, which does not get passed to the standby. So it you turn on flashback on the primary, you must also turn it on for the standby.
To flashback the primary database one hour:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT EXCLUSIVE
SQL> FLASHBACK DATABASE TO TIMESTAMP(SYSDATE-(1/24);
SQL> ALTER DATABASE OPEN RESETLOGS;
To flashback the standby execute the same commands.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT EXCLUSIVE
SQL> FLASHBACK DATABASE TO TIMESTAMP(SYSDATE-(2/24);
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
To turn on flashback use the following commands;
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN; ALTER DATABASE RECOVER?
One of the drawbacks of a Managed Standby is that if you actually need to temporarily use the standby in a read write mode, you would have to rebuild the standby because opening the database read write requires an open reset logs, creating a new instantiation. However in 10g and beyond, you can use flashback database to return the standby database to a point in time before you opened it read write. To perform this action you must first create a restore point before opening the database. This is the point that we will flash the database back to. You can also use a timestamp or an scn but a restore point is much easier. Either of the command below will create a restore point called ?before_open?.
SQL> create restore point before_open;
SQL> create restore point before_open guarantee flashback database;
You cannot create a restore point on the primary database and using it on the standby database. The restore point must be created on the standby database.
Now you can open the database read write.
SQL> alter database recover managed standby database finish force;
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
SQL> alter database open;
Or
SQL> alter database commit to switchover to primary;
SQL> STARTUP MOUNT FORCE;
SQL> alter database open;
To return the database to a managed standby you will:
1. Shutdown the open standby database.
2. Startup mount.
3. flashback the database to the restore point.
4. convert the control file back to a standby control file.
5. startup mount
6. restart managed recovery.
The actual commands look like this:
SQL> shutdown abort;
SQL> startup mount;
SQL> flashback database to restore point before_open;
SQL> alter database convert to physical standby;
SQL> startup mount force;
SQL> alter database recover managed standby?
Monitoring the Physical Standby
To insure the standby database is ready to perform a switchover or failover you must monitor the recovery progress. You can get information about the recovery process from a number of sources to include the alert log on both the primary and standby, the dbconsole and v$archived_log.
To insure the managed recover process is running:
Select
PROCESS,
SEQUENCE#,
STATUS
From
V$MANAGED_STANDBY;
One of the easiest way to verify that the standby is up to date with the primary is to compare the sequence number of the two database. The script below will get the last sequence number that was applied on the standby and compare it to the max sequence number of the primary. This script is for Linux but can be adapted for Windows.
#!/bin/ksh
ORACLE_SID=db06
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep $ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
$ORACLE_HOME/bin/sqlplus /nolog<<!
connect / as sysdba
set pages 0 heading off feedback off
spool /tmp/standby_applied.lst
select max(sequence#) from v$archived_log where applied = 'YES';
spool off;
connect perfstat/perfstat@DB09.ORACLE.COM
set pages 0 heading off feedback off
spool /tmp/primary_current.lst
select max(sequence#) from v$archived_log;
spool off;
exit
!
applied=`cat /tmp/standby_applied.lst`
current=`cat /tmp/primary_current.lst`
statsvalue=$(($current - $applied))
echo $statsvalue
if [[ $statsvalue -gt 4 ]];
then
echo "The Standby Database is falling behind!" > /tmp/stdby_alert.lst
echo "Standby Applied Sequence: $applied" >> /tmp/stdby_alert.lst
echo "Primary Current Sequence: $current" >> /tmp/stdby_alert.lst
echo "Difference: $statsvalue" >> /tmp/stdby_alert.lst
echo "" >> /tmp/stdby_alert.lst
df -h >> /tmp/stdby_alert.lst
echo "**********************************************************"
echo "The Standby is NOT Current"
cat /tmp/stdby_alert.lst |mail -s ?PROD Standby Monitor Alert **"\
prod_emergency@my_company.net
echo "**********************************************************"
fi
The script above will provide and email alert if the standby database falls behind the production database by more than four sequence number. Once scheduled to run hourly in a cron, you will quickly become aware if the standby stops, is not applying redo, or is just falling behind.
