Implementing Oracle Data Guard - Disaster Recovery Solution Contents
Introduction
Dataguard in Action
Dataguard Switch Over (Roles)
Dataguard Modes
Dataguard Fail over
IntroductionThe document is intended for DBAs who would like to implement a DataGuard environment and subsequently manage it on Oracle 9i and Oracle 10g Databases.
Oracle DataGuuard provides organizations with high availability, data protection, and disaster recovery for enterprise databases with extraordinary ease of use. A DataGuard configuration consists of 1 Production database and up to 9 Standby databases.
The Standby database is connected to the Production database via Oracle Net and can be dispersed geographically. You can even have a Standby database on the same system where the Primary database resides, however it would defeat the actual purpose. You can also have a Standby database for a Production real application cluster RAC.
Oracle provides 3 ways to manage a DataGuard environment.
SQL commands, DataGuard Broker GUI (OEM), and
DataGuard Broker command line (CLI) interface.
A DG environment requires Same Oracle Version, Primary database to be in archive mode, OS release version must also be same, however OS maint-level may differ.
Based upon my experience, I have found that the creation and management of Dataguard environment should be done with the traditional approach i.e, using SQL Commands. When used with Oracle OEM or DG Broker, one would not really know what is taking place behind the scenes and most of the times when confronted with errors, the errors are also deceiving and not as clear as when we use native sql method.
Oracle supports two types of Standby databases:
Physical-Standby which is a block level copy and is constantly being updated by applying redo information as in recovery mode where changes are applied block-for-block using rowid,
Logical Standby is the process where physical attributes of a Standby can differ like additional indexes etc. The process involves SQL statements being extracted from the archived log and then applied to the Standby database which is at the same time opened for users to perform reporting.
Logical Standby database can also be used for DR purpose; there is one major exception that the Maximum Protection mode is not supported by logical method.
Figure 1-1 shows a DataGuard configuration where 1 Production database has two Standby databases (physical and logical).
One very important point to note is that on the Standby database, the recovery is always done by reading the archived logs on the Standby and not from any other means like reading from Standby redo logs.
There are 3 types of DataGuard services; Log Transport Services, Log Apply Services, and Role Management Services.
Log Trsansport Services:
Log transport services enables and controls the automated transfer of redo data within a Data Guard configuration from the primary site to each of its standby sites.
Log transport services also controls the level of data protection for your database.
The DBA will configure log transport services to balance data protection and availability against database performance.
Log transport services will also coordinate with log apply services and role management services for switchover and failover operations.
Log Apply Services:
The Redo data received from Primary database has to be archived into archived redo logs on Standby and then oracle will extract the redo information from these archived logs and apply to the Standby database.
Role Management Services:
In a DG environment, a database can be either Primary or Standby. Changing their roles via failover or switch over is governed by these services.
Switch over is planned and is called role reversal.
Failover is when you have a real disaster and then you need to rebuild the Standby again.
You should note that regardless of any option used, a switch over will guarantee No Data loss and is done basically for planned maintenance of Primary system.
A DG environment works in one of the three modes (not roles which are switch over or failover). So Role is either switch over OR Fail over, while mode is (Max Protection, Max Availability, Max Performance.
Maximum Protection mode offers the ultimate in data protection. It guarantees no data loss will occur in the event the primary database fails. In order to provide this level of protection, the redo data needed to recover each transaction must be written to both the local (online) redo log and to a standby redo log on at least one standby database before the transaction can be committed. In order to guarantee no loss of data can occur, the primary database will shut down if a fault prevents it from writing its redo data to at least one remote standby redo log.
For this mode,
The LOG_ARCHIVE_DEST_n on the primary database
must make use of LGWR, SYNC, and AFFIRM
attributes.
Standby redo logs must be configured.
For example:
log_archive_dest_2='service=stdby_service LGWR SYNC AFFIRM'
Maximum Availability offers the same as data protection (sync) except that if there is a problem in the network and Standby database is not reachable, it will degrade the mode to maximum performance and Primary database can continue working. If while running in this mode and a network issue occurs and while that issue is still present and another disaster happens, you could end up loosing some data.
For example:
log_archive_dest_2='service=stdby_service LGWR SYNC AFFIRM'
Please note that the above two configuration tables are same, so what determines the protection or avail mode is executing the required alter database mode commands on primary database site.
Maximum Performance offers less data protection for Primary database. Redo is asynch transmitted to Standby and there is also no two phase commit processing, therefore there is a lesser performance effect on Primary.
Maximum performance is the default protection mode and provides the highest level of data protection that is possible without affecting the performance or availability of the primary database. With this protection mode, a transaction is committed as soon as the redo data needed to recover the transaction is written to the local (online) redo log.
When configuring the destination standby instance in the LOG_ARCHIVE_DEST_n, log transport services can be set to use either LGWR / ASYNC or ARCH. In order to reduce the amount of data loss on the standby destination if the primary database were to fail, set the LGWR and ASYNC attribute. Using this configuration, the primary database writes its redo stream to the standby database asynchronously with respect to the commitment of the transactions that create the redo data. When the nodes are connected with sufficient bandwidth, this mode provides a level of data protection that approaches that of maximum availability mode with minimal impact on primary database performance. Note that the use of standby redo logs is only necessary when configuring log transport services to use LGWR. When log transport services are configured to use ARCH, standby redo logs are not needed, because then it would become like old standby of 8i where archived logs will be shipped and applied to Standby.
For example:
log_archive_dest_2='service=stdby_service ARCH NOAFFIRM'
or(async not valid in arch case)
log_archive_dest_2='service=stdby_service LGWR ASYNC
NOAFFIRM'
When configuring log transport services to use LGWR and remotely archive in ASYNC mode, the LGWR process does not wait for each network I/O to complete before proceeding. This behavior is made possible by the use of an intermediate process, known as a Log Writer Network Server Process (LNS), which performs the actual network I/O and waits for each network I/O to complete. Each LNS has a user configurable buffer that is used to accept outbound redo data from the LGWR process. This is configured by specifying the size in 512 byte blocks on the ASYNC attribute in the archivelog destination parameter. For example, ASYNC=2048 indicates a 1MB buffer. As long as the LNS process is able to empty this buffer faster than the LGWR can fill it, the LGWR process will never stall. If the LNS cannot keep up, then the buffer will become full and the LGWR process will stall until either sufficient buffer space is freed up by a successful network transmission or a timeout occurs.
When configuring log transport services to remotely archive using the ARCH attribute, redo logs are transmitted to the destination only during an archival operation. The background archiver processes (ARCn) or a foreground archival process operation serves as the redo log transport service. Using ARCH to remotely archive redo data does not impact the primary database throughput as long as enough redo log groups exist so that the most recently used group can be archived before it must be reopened.
And now Figure-2 shows various compatible configurations.
Take note of the following important points to understand when configuring the standby database:
- The standby database that is used to satisfy the minimum requirements for a given protection mode must be enabled and ready to receive redo data from the primary database before you can switch to that mode.
- When archiving to a physical standby destination using the LGWR process, note that the changes (transactions) being made and committed on the primary database are not instantly written to the actual database files on the standby database. In Oracle9i Release 2, when log transport services is configured for maximum availability mode or maximum protection mode, the LGWR process on a physical primary database will send redo data to the standby redo logs (located on the standby database) at the same time it is writing redo data to the local (online) redo logs). Keep in mind that the LGWR process is actually communicating with a Remote File Server (RFS) process on the standby database server. This RFS process on the standby database is responsible for capturing and writing the redo data it obtains from the primary database to the standby redo logs.
- The Remote File Server process runs on the standby database and can receive redo data over the network from both LGWR and ARCn. The RFS process will write the redo data it receives to either a standby redo log or to a standby archived redo log.
- When a log switch occurs on the primary database, a log switch is also triggered on the standby database where the ARCH process then archives the standby redo logs to the archive destination specified on the standby database. After the archival process has completed on the standby database, the Managed Recovery Process (MRP) then writes the changes to the actual database files from the archived redo log files. Why is this important to point out? It illustrates the fact that the actual changes (transactions) being made and committed on the primary database do indeed make it over to the standby database, but get
- applied to the standby redo logs. These changes are only made to the actual database files on the standby database when a log switch occurs on the primary. Note that in Oracle10g, it is possible to configure changes made on the primary database to immediately write the changes to the database files on the standby database.
Data Guard in ActionStep1: Setting up Primary DatabaseI have included a simple one command shell “Create920db.sh “ script in my website that lets you create an oracle database.
Click here to download the Database Creation Toolkit.The following testcase is valid on Oracle 9i/10g Release 2 on HP and AIX Platforms.
Environment Settings:
Primary Machine:DBServerA
Standby Machine:DBServerB
SID: SHDP
Database Root folder:/dbatoolkit/dbcreation/SHDP
Config+control: $PDB/config
Dbf files: $PDB/data
Backup: $PDB/backup
Archived logs: $PDB/arch
Export $PDB= /dbatoolkit/dbcreation/SHDP
The directory structure is the same on both machines which can be eaily achieved by creating a unix link on the standby as of the similar directory stryuctre
| ON PRIMARY DATABASE SERVER --Create the database called SHDP Create920db.sh SHDP $ORACLE_HOME --Configure tnsnames and listener.ora Modify the listener.ora file to include entry for the new SID. Modify the tnsnames.ora to include the tnsname SHDP_DRC to point to the Standby Database name(which does not exists at this point). --Turn the primary database instance to archive mode+force logging Edit initSHDP.ora file and add lines: log_archive_dest_1="LOCATION=/dbatoolkit/dbcreation/SHDP/arch MANDATORY REOPEN=120" log_archive_min_succeed_dest=1 log_archive_format=arch_SHDP%S.log log_archive_start=true startup mount pfile=initSHDP.ora alter database archivelog; alter database open; shutdown immediate; startup pfile=initSHDP.ora ALTER DATABASE FORCE LOGGING; --Create instance start/stop scripts Create startup script for the instance: startSHDP.sh export ORACLE_SID=SHDP sqlplus -S /nolog <<-EOF connect sys/password as sysdba; startup pfile=$PDB/config/initSHDP.ora EOF exit Create stop script for the instance: stopSHDP.sh export ORACLE_SID=SHDP sqlplus -S /nolog <<-EOF connect sys/password as sysdba; shutdown immediate; EOF exit |
Step2: Backing up Primary Database for cloning
Creating a standby database requires the primary database to be cloned onto the standby server with special control file which is created for a cloned standby database. For the test case, I have created an rman backup script to first backup the primary database and then ftp the backupset to standby server and then start standby cloning. However in real production case, you must already have a standard backup that is running for your production database. In that case you just need to restore that backup onto the standby. Before you do that you need to backup the primary controlfile to be backed up as standby control file and then restored onto the standby database before actually restoring your production full backup.
I have included a script below which uses rman to backup database onto disk. You should substitute lines in case you are taking backup to a backup system like veritas netbackup. There is a separate discussion paper I have created on my webite which talks about scripts for backup/recover with veritas netbackup system.
| ON PRIMARY DATABASE SERVER From $PDB/backup folder: execute the command fullbkupSHDP.sh which consists of the source code export ORACLE_SID=SHDP DY=`date +"%a%d%m%y%H%M"` DSET="'$PDB/backup/SHDP_FULL_BACKUP_DBF"$DY"%U'" ARSET="'$PDB/backup/SHDP_FULL_BACKUPARC"$DY"%U'" CNTFILE="'$PDB/backup/SHDP_FULL_BACKUPCNT"$DY"%U'" BRTM="date +'%a:%d-%b-%Y-%H.%M.%S'" v_msg1="\n**RMAN SHDP FULL Database BACKUP Started:`$BRTM`**" echo $v_msg1 > $PDB/SHDP/SHDPFBKUP_rman.log rman target / msglog $PDB/SHDP/SHDPFBKUP_rman.log append << EOF RUN { sql 'alter system archive log current'; ALLOCATE CHANNEL ch01 DEVICE TYPE DISK; ALLOCATE CHANNEL ch02 DEVICE TYPE DISK; ALLOCATE CHANNEL ch03 DEVICE TYPE DISK; ALLOCATE CHANNEL ch04 DEVICE TYPE DISK; BACKUP incremental level 0 TAG SHDP_FULLDB$DY FORMAT $DSET DATABASE INCLUDE CURRENT CONTROLFILE FOR STANDBY; sql 'alter system archive log current'; RELEASE CHANNEL ch01; RELEASE CHANNEL ch02; RELEASE CHANNEL ch03; RELEASE CHANNEL ch04; #Archive logs backup ALLOCATE CHANNEL ch01 DEVICE TYPE DISK; BACKUP TAG SHDP_ARCHDB$DY FORMAT $ARSET ARCHIVELOG all; RELEASE CHANNEL ch01; #control file backup #ALLOCATE CHANNEL ch01 DEVICE TYPE DISK; #backup current controlfile #format $CNTFILE #tag SHDP_CNTDB$DY; #RELEASE CHANNEL ch01; } EOF v_msg2="\nxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx \n RMAN SHDP FULL Database BACKUP Ended:`$BRTM` \nxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx \n" echo $v_msg2 >> $PDB/SHDP/SHDPFBKUP_rman.log cat $PDB/SHDP/SHDPFBKUP_rman.log>>$$PDB/SHDP/SHDPFBKUP_rman_hist.log |
Step3: Preparing the Standby Database Server
On the Standby Database Server
- Create complete directory structure on the Standby server, with same path as of the Primary Database Server i.e. $PDB. And then create a logical link against the mount point which you like to use for the standby database. This will make both servers’ databases to follow the exact file name paths.
- Export $SDB= /dbatoolkit/dbcreation/SHDP
- Now create the similar file structure like $SDB/SHD (data,backup,config,arch)
- Copy/FTP from the Primary Server to the Standby server the following files:
initSHDP.ora, password file, backup sets(full+archive)
- Change the contents of the initSHDP.ora for the following parameters:
db_domain=DBServerB
Standby_archive_dest=/dbatoolkit/dbcreation/SHDP/arch
Standby_file_management=AUTO
Fal_server=SHDP_PRIM
Fal_client=SHDP_DRC
- Create an entry in the listener.ora file to include the SID=SHDP, although there is no such instance at this time.
- Modify the tnsnames.ora to include the name SHDP_PRIM for the Primary Database name. Do not create TNS entry for the Standby instance as you will not likely use it, because you should always use the EXPORT ORACLE_SID concept when working with the local database server on any machine
Step4: Cloning Primary Database to Standby Server
On the Standby Database Server:
| Startup the instance with nomount only. cd $SDB export ORACLE_SID=SHDP sqlplus ‘/as sysdba’ startup nomount pfile=$SDB/config/initSHDP.ora |
On the Primary Database Server:
Start Cloning:
| export ORACLE_SID=SHDP rman target / auxiliary sys/password@SHDP_DRC duplicate target database for Standby dorecover nofilenamecheck; Please observer closely the output of the above command and how rman executes the cloning process. The command may fail with the following error at the end: printing stored script: Memory Script { set until scn 145828; recover standby clone database delete archivelog ; } executing script: Memory Script executing command: SET until clause Starting recover at 30-JAN-07 using channel ORA_AUX_DISK_1 starting media recovery Oracle Error: ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/dbatoolkit/dbcreation/SHDP/data/system01.dbf' RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 01/30/2007 16:34:43 RMAN-03015: error occurred in stored script Memory Script RMAN-06053: unable to perform media recovery because of missing log RMAN-06025: no backup of log thread 1 seq 61 scn 145825 found to restore RMAN-06025: no backup of log thread 1 seq 60 scn 145383 found to restore RMAN> |
As you see there was error while applying archived logs because some archived logs are not found on the standby machine.
What we need to do is to move the missing archived logs from primary to standby. The names of missing archived logs are shown below:
RMAN-06025: no backup of log thread 1 seq 61 scn 145825 found to restore
RMAN-06025: no backup of log thread 1 seq 60 scn 145383 found to restore
After this you connect with rman on the Primry machine like:
rman target / auxiliary sys/password@SHDP_DRC
copy the recover script from the above error log and run it again:
run
{
set until scn 145828;
recover
standby
clone database
delete archivelog
;
}
This marks completion of recovery, therefore do not ignore any errors while duplicating the database
Step5: Post Cloning tasks/Completing the DG Env.
The objective should be to prepare the Primary and Standby database in such a way that the switch over task can be performed with minimum configuration changes and that the Modes (data protection….max performance) can also be changes as per need.
| Create Standby Redo logs on Standby Server: ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 ('/dbatoolkit/dbcreation/SHDP/data/redostby301.log', '/dbatoolkit/dbcreation/SHDP/data/redostby302.log') SIZE 10M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/dbatoolkit/dbcreation/SHDP/data/redostby401.log', '/dbatoolkit/dbcreation/SHDP/data/redostby402.log') SIZE 10M; Create Standby Redo logs on Primary Server: ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/dbatoolkit/dbcreation/SHDP/data/redostby301.log', '/dbatoolkit/dbcreation/SHDP/data/redostby302.log') SIZE 10M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/dbatoolkit/dbcreation/SHDP/data/redostby401.log', '/dbatoolkit/dbcreation/SHDP/data/redostby402.log') SIZE 10M; On Primary Database Server edit initSHDP.ora to add lines: #UN-Comment the Following when DB is Primary and Comment when Standby log_archive_dest_2='service=SHDP_DRC LGWR ASYNC=5M' log_archive_dest_state_2=enable remote_archive_enable=true #UN-Comment the Following when DB is Standby and Comment when Primary #Standby_archive_dest=/dbatoolkit/dbcreation/SHDP/arch #standby_file_management=AUTO #Fal_server=SHDP_DRC #Fal_client=SHDP_PRIM On Standby Database Server edit initSHDP.ora to add lines: #UN-Comment the Following when DB is Standby mode and Comment when Primary Standby_file_management=AUTO Standby_archive_dest=/dbatoolkit/dbcreation/SHDP/arch Fal_server=SHDP_PRIM Fal_client=SHDP_DRC #UN-Comment the Following when DB is Primary and Comment when Standby #log_archive_dest_2='service=SHDP_PRIM LGWR ASYNC=5M' #log_archive_dest_state_2=enable #remote_archive_enable=true |
As you have noted the above settings are only documented and they will take an effect once the two databases are recycled. However in that case the Primary database will be DBServerA and standby will be DBServerB.
Step6: Activating DR Environment:
PrimaryàDBServerA, StandbyàDBServerB
Since we only modified the above params and did not recycle the database we need to use the following commands to perform the changes of the init file dynamically. However if you can recycle the two instances then do so.
On Primary Database server:(Or recycle the instance)
| Alter system set log_archive_dest_2='service=SHDP_DRC LGWR ASYNC=5M' Must run the command: Alter system archive log current; Alter system switch logfile; |
On Standby Database server:
| The params of init file already active since before we clone it, the init file was already changed for : Standby_archive_dest=/dbatoolkit/dbcreation/SHDP/arch Standby_file_management=AUTO Fal_server=SHDP_PRIM Fal_client=SHDP_DRC Activate the standby database to recover mode: alter database recover managed standby database disconnect; You can always cancel recovery by issuing: Alter database recover managed standby database cancel; To open database in read only mode: First cancel the recovery then Alter database open read only; Recycle standby database: Shutdown immediate; Startup nomount pfile=initSHDP.ora alter database mount standby database; alter database recover managed standby database disconnect; |
Recycle the DR Environment:
| Shutdown primary first and then secondary. |
Check the status of Archived Logs Applying:
| select sequence#, DEST_ID, CREATOR, REGISTRAR, STANDBY_DEST, ARCHIVeD,APPLIED,NEXT_CHANGE# from v$archived_log order by sequence#,dest_id |
Data Guard Switch Over We assume here that Primary Database server is:DBServerA, while standby database server is DBServerB.
On Current Primary Database server (DBServerA)—Switching It To Standby
| Make sure Application is down and no active sessions in the database. Make sure that the last daily full backup was successful. Perform the last alter system switch logfile and then take the archived log backup. The above steps are required as a fallback plan. At this point open two telnet sessions and perform tail –f alert.log for both databases so you can actually watch the responses when you perform switch over operation. Log to sys as sysdba: Run sql scripts to record the temp files names and location because you will need this to recreate the temp files on standby server. SQL> alter database commit to switchover to physical standby with session shutdown; SQL> shutdown immediate; Edit the initSHDP.ora and un-comment the Standby and Comment out the Primary params.(Init file has the respective segments documented) SQL> startup nomount pfile=initSHDP.ora SQL> Alter database mount standby database; SQL> Alter system set log_archive_dest_state_2=DEFER SQL> select name,controlfile_type ,open_mode,database_role from v$database; At this point you have two standby databases in the DR env. |
On Current Standby Database server àSwitching it to Primary mode
| Log to sys as sysdba: SQL> Alter database recover managed standby database cancel; SQL> Alter database commit to switchover to primary with session shutdown; SQL> Shutdown immediate; Edit the initSHDP.ora and un-comment the Primary and Comment out the Standby params.(Init file has the respective segments documented) SQL> startup pfile=initSHDP.ora SQL> select name,controlfile_type ,open_mode,database_role from v$database; The redo logs will also be auto created. SQL> alter tablespace temp add TEMPFILE '/dbatoolkit/dbcreation/SHDP/data/ temp01.dbf' SIZE 10M ; It is very important that you create the temp files as was recorded in the previous step. SQL> alter system set log_archive_dest_state_2 = enable; SQL> alter system archive log current; SQL> Alter system switch logfile; |
On the new Standby Server (DBServerA)
| SQL> alter database recover managed standby database disconnect from session; At this point you can perform healthcheck of the DR environment by performing couple of log switches and checking the status by running: select sequence#, DEST_ID, CREATOR, REGISTRAR, STANDBY_DEST, ARCHIVeD,APPLIED,NEXT_CHANGE# from v$archived_log order by sequence#,dest_id |
Useful commands
| select sequence#, DEST_ID, CREATOR, REGISTRAR, STANDBY_DEST, ARCHIVeD,APPLIED,NEXT_CHANGE# from v$archived_log order by sequence#,dest_id select PROTECTION_MODE,PROTECTION_level from v$database; alter database mount standby database; Alter database recover managed standby database disconnect; Alter database recover managed standby database cancel; Alter database open read only; select name,controlfile_type ,open_mode,database_role from v$database; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY; |
Observations:
Switching back should not be a difficult task and you should perform it yourself by following the same procedure as described above.
When you recycle the DR environment, make sure to run the command “Alter system archive log current”. What I have noticed is that when you have logwr set as your transport service, it gets affected only when you run the above command as seen from the creator column in the following sql run at primary location:
select sequence#, DEST_ID, CREATOR, REGISTRAR, STANDBY_DEST, ARCHIVeD,APPLIED,NEXT_CHANGE# from v$archived_log
order by sequence#,dest_id
When set in the following mode:
Alter system set log_archive_dest_2='service=SHDP_DRC LGWR ASYNC=5M'
You can always create a table on the primary site (do not perform log switch), then go to the database server and use grep to find the table name in the current/active standby redo log(v$standby_log). You will find the table scripts present indicating that the trans are aysnch transferred and not wait for the archived log shipping.
If your current setting is set to 'service=SHDP_DRC LGWR ASYNC=5M', this means that the creator and log transport service will be logwr and as soon as you issue commit, the trans will be asycn applied to standby. I have explained above how to test this case.
Lets change the service to arch process as:
Alter system set log_archive_dest_2='service=SHDP_DRC ARCH
Now when you do couple of log switches, you will find that arch is the creator and will ship trans to standby only at log switch and will also ship to the standby archived logs. The standby redo is not in this picture and remained with status “not assigned”.
Please note that when you use arch as service, sync/asycnh is not applicable.
Should you need a time lag for the standby database, you can use the following parameter on your primary database.
Alter system set log_Archive_dest_2=’service=stdby Delay=240’
Delay is in minutes.
Alternatively you can leave the redo transfer as fast as possible and change the recovery time on the Standby Database server as:
-- Delay application of archived redo logs by 30 minutes.ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30; Return to no delay (Default).ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;
DataGuard Modes Maximum performance require any of the following settings:
log_archive_dest_2='service=SHDP_DRC LGWR ASYNC=5M'
log_archive_dest_2='service=SHDP_DRC LGWR SYNC’
log_archive_dest_2='service=SHDP_DRC ARCH’
Depending on the latency, you can tune the 5M buffer which may affect the transfer of redo from primary to standby. You need to perform testing.
Maximum Availability:
Make sure that the DG environment is already set to Maximum Protection and that the redo transfer is performing without any issues with respect to the network latency.
On Primary Database Instance:
| SQL> shutdown immediate; SQL> startup mount pfile=initSHDP.ora; SQL> select PROTECTION_MODE,PROTECTION_level from v$database; SQL> Alter system set log_archive_dest_2='service=SHDP_DRC LGWR SYNC AFFIRM'; SQL> Alter database set standby database to maximize Availability; SQL> Alter database open; SQL> select PROTECTION_MODE,PROTECTION_level from v$database; If you want these changed to be permanent than edit the initSHDP.ora file to replace the old log_archive_dest_2 setting with the above one. I just did it to test. Moreover you will also find information messages in the alert log and you should review them. |
Maximum Protection:
Make sure that the DG environment is already set to Maximum Availability mode and that the redo transfer is performing without any issues with respect to the network latency.
On Primary Database Instance:
| SQL> shutdown immediate; SQL> startup mount pfile=initSHDP.ora; SQL> select PROTECTION_MODE,PROTECTION_level from v$database; SQL> Alter system set log_archive_dest_2='service=SHDP_DRC LGWR SYNC AFFIRM'; SQL> Alter database set standby database to maximize Protection; SQL> Alter database open; SQL> select PROTECTION_MODE,PROTECTION_level from v$database; If you want these changed to be permanent than edit the initSHDP.ora file to replace the old log_archive_dest_2 setting with the above one. I just did it to test. Moreover you will also find information messages in the alert log and you should review them. |
DataGuard Fail Over During a failover operation, the standby database converts to Primary role and the old primary database is rendered unable to participate in a DR environment. You perform this task when your primary database/server suffered a major crash and you want to be able to switch over to standby to make the database available for users. However since you do not have enough downtime to fix the problems at the primary site, you decided to fail over.
You should note that after you failover you will require to re-create the old primary database as a standby and then perform a switch over operation.
If your primary site is still accessible, you should try to move all of the archived logs to standby (if not already move).
On Standby Database
| Make sure all archived logs available at standby have been applied by running the following sql and comparing with the arch location on the disk for the last archived log sequence number. Also query the table V$ARCHIVE_GAP to find out missing archived logs. SQL> select sequence#, DEST_ID, CREATOR, REGISTRAR, STANDBY_DEST, ARCHIVeD,APPLIED,NEXT_CHANGE# from v$archived_log order by sequence#,dest_id After copying the archived logs to standby, you need to register them if they have not been already registered (check v$archived_log) with the command: SQL> Alter database REGISTER PHYSICAL LOGFILE ‘/D/D..’; SQL> Alter database recover managed standby database cancel; SQL> Alter database recover managed standby database finish skip standby logfile; SQL> Alter database commit to switchover to primary; At this point it is advisable to recycle the new primary database and take a full backup. |
Useful References:
Oracle Data Guard, Concepts and Administration Release 9.
You should always keep the above manual in front of you. Especially the Appendix A:Troubleshooting.
http://www.dbazine.com/oracle/or-articles/foot7
http://www.oracle-base.com/articles/9i/DataGuard.php Go back to Data Guard Resources
Copyright © 2007 www.OracleFusions.com All rights reserved.
The information contained in this document represents my personal view on the issues discussed as of the date of publication, and I can not guarantee the accuracy of any information presented after the date of publication.
This document is for informational purposes only. I MAKE NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.
Contact: Support@oraclefusions.com
