So you have created your standby database using the RMAN DUPLICATE command, you have set the minimum log switch individual using ARCHIVE_LAG_TARGET, and you have sorted out those nasty “datafile missing” errors using automatic file management.
Management is now happy with the Mean Time To Recovery (MTTR)… but not really. They review the documentation generated from your database failover test and see that the time to switchover is slow and that there is still a possibility of losing some data.
Enter standby redo logs. These are required to enable real time apply of redo data onto the standby. This is not the same level of redundancy or availability of Oracle RAC, but getting close.
Essentially, the standby redo logs are populated with redo information as fast as the primary redo logs, rather than waiting for the redo log to be archived and shipped to the standby. So the loss of data in the event of a failover is minimized.
Rather than regurgitate the documentation, I have attached the links to the specific parts of the documentation. Since I’m focused on providing ways for you to “get the job done”, I have also included some SQL that uses dynamic SQL to generate a series of ALTER DATABASE ADD STANDBY LOGFILE commands to run on both the primary and standby databases.
While it would be nice if the RMAN DUPLICATE command added the standby redo logs, it does not.
Have Fun!
Paul.
New Features in 10G Data Guard
Asynchronous Redo Transport Services
Sizing guidelines
Configuring real-time apply (using standby redo logs)
Now the best bit. You just want to create the standby redo logs, right? Then use the dynamic SQL and you’re done.
Oracle REGEX functions:
The SQL code:
select 'alter database add standby logfile '''|| regexp_substr(MEMBER,'/.+/')||'stdby_'|| regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')|| ''' size '||bytes||';' "Create Standby redo" from v$logfile lf , v$log l where l.group# = lf.group# /
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- 1 1 64184 52428800 2 YES ACTIVE 647791233 20070807 22:50 2 1 64185 52428800 2 NO CURRENT 647803055 20070807 23:00 3 1 64183 52428800 2 YES INACTIVE 647779820 20070807 22:40 SQL> col member for a40 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ------------------------------------------- 3 ONLINE /oracle/redo/prod1/redo03a.rdo NO 3 ONLINE /oracle/redo/prod1/redo03b.rdo NO 2 ONLINE /oracle/redo/prod1/redo02a.rdo NO 2 ONLINE /oracle/redo/prod1/redo02b.rdo NO 1 ONLINE /oracle/redo/prod1/redo01a.rdo NO 1 ONLINE /oracle/redo/prod1/redo01b.rdo NO "Create Standby redo" ------------------------------------------------------------------------------------------------ alter database add standby logfile '/oracle/redo/prod1/stdby_redo03a.rdo' size 52428800; alter database add standby logfile '/oracle/redo/prod1/stdby_redo03b.rdo' size 52428800; alter database add standby logfile '/oracle/redo/prod1/stdby_redo02a.rdo' size 52428800; alter database add standby logfile '/oracle/redo/prod1/stdby_redo02b.rdo' size 52428800; alter database add standby logfile '/oracle/redo/prod1/stdby_redo01a.rdo' size 52428800; alter database add standby logfile '/oracle/redo/prod1/stdby_redo01b.rdo' size 52428800; 6 rows selected.
Dynamic SQL to which generates extra standby redo log following the naming convention, stdby_redo0Group#.rdo
select 'alter database add standby logfile '''|| regexp_substr(MEMBER,'/.+/')||'stdby_'|| regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')|| ''' size '||bytes||';' "Create Standby redo" from v$logfile lf , v$log l where l.group# = lf.group# union all select 'alter database add standby logfile '''|| regexp_substr(MEMBER,'/.+/')||'stdby_redo0'||(select max(group#)+1 from v$log)||'.rdo'|| ''' size '||bytes||';' "Create Standby redo" from v$logfile lf , v$log l where l.group# = lf.group# and rownum <=2 /
Create Standby redo ------------------------------------------------------------------------------------------------ alter database add standby logfile '/oracle/redo/prod1/stdby_redo03a.rdo' size 52428800; alter database add standby logfile '/oracle/redo/prod1/stdby_redo03b.rdo' size 52428800; alter database add standby logfile '/oracle/redo/prod1/stdby_redo02a.rdo' size 52428800; alter database add standby logfile '/oracle/redo/prod1/stdby_redo02b.rdo' size 52428800; alter database add standby logfile '/oracle/redo/prod1/stdby_redo01a.rdo' size 52428800; alter database add standby logfile '/oracle/redo/prod1/stdby_redo01b.rdo' size 52428800; alter database add standby logfile '/oracle/redo/prod1/stdby_redo04.rdo' size 52428800; 7 rows selected.
12 Comments. Leave new
I like the technique, even adding the an extra SRL over the number of redo logs. However I would dispute what you say about RAC:
“This is not the same level of redundancy or availability of Oracle RAC, but getting close.” But if your building blows up or floods, unless you have a stretched RAC cluster your dataguard solution provides you much more protection way above the availability of RAC.
jason.
Indeed or you combine RAC with Dataguard and run a standby or two off the RAC cluster.
So you have the availability of RAC and the redundancy and Disaster Recovery (DR) of Dataguard.
Hi Paul,
you are right – RMAN’s duplicate command does not do everything for you regarding standby redo logs (I am about the case when they are already created on primary database). But after duplication of the target database proper entries (taking into consideration log_file_name_convert parameter on standby side) for standby logs will be created, but without files under that. The files can be simply recreated through ALTER DATABASE CLEAR LOGFILE GROUP (n – standby log file group) command. And after that you can continue with Data Guard (or Standby) configuration.
Regards,
Andrey Goryunov
Directory structure is the same on both primary and standby machines.
Backup is going locally to disk at location source_backup_directory
TNSNAMES entry STANDBY points to standby db on both machines.
TNSNAMES entry PROD points to prod db on both machines.
RMAN catalog exists, and is resolved via TNSNAMES entry rcatdb.
RMAN default channel locations.
On Source/Primary db:
su – oracle
rman target / catalog [email protected]
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;
RMAN> BACKUP CHECK LOGICAL FULL DATABASE PLUS ARCHIVELOG;
RMAN> exit;
scp source_backup_directory [email protected]:standby_backup_directoryOn Destination or Standby db:
sqlplus “/ as sysdba”
startup nomount
exit;On Source/Primary db:
rman target / catalog [email protected] auxiliary [email protected]
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;
RMAN> exit;On Source/primary db:
show parameter log_archive_dest;
alter system set log_archive_dest_x = ‘SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD’;
alter system set log_archive_dest_state_x = ‘ENABLE’;On Destination or Auxiliary STANDBY:
alter system set FAL_SERVER = ‘PROD’;
alter system set FAL_CLIENT = ‘STANDBY’;
shutdown immediate;
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;To check progress:
On Primary:
column destination format a30
select dest_id,destination,status,database_mode,recovery_mode,error from V$ARCHIVE_DEST_STATUS
where status != ‘INACTIVE’;To check progress on Primary or Standby:
select * from v$managed_standby;
SATISH NAWLE
SQL STAR
9224639278
We have two node RAC. We want to configure DG. The standby does not use RAC. Can anybody provide the steps.
I am assuming you meant you want a 3rd instance (Physical|Logical Standby) which gets its archivelogs from both RAC nodes.
I had discussed this Andrey some time ago and we know the way, just didn’t get around to blogging about it.
Always remember google is your searching-for-answers friend.
Here is a paper from Oracle which spells out in detail how to implement a Physical standby from a RAC cluster.
https://www.oracle.com/technology/deploy/availability/pdf/MAA_WP_10g_RACPrimaryRACPhysicalStandby.pdf
I love these blogs!
In the 10gR2 docs it states that you may decide to add a standby group on the primary if there are delays in the RFS trace files etc…
Specifically it says “Whenever you add an online redo log file group to the primary database, you must add a corresponding standby redo log file group to the standby database”
However, I only get errors if I try to add/drop standby logs on the standby:
“ORA-01156: recovery in progress may need access to files”
Any thoughts?
do “alter database recover managed standby database cancel” before add/drop standby logfiles.
I recently found out when setting up logical standby with real-time apply the logical standby database MUST be in ARCHIVELOG mode (relates to having the standby redo logs). I couldn’t find this requirement in any of the 10.2 documentation – I raised this with Oracle and they confirmed it is a documentation bug and fixed in 11g (not sure if they’re planning on updating the 10g documentation on tahiti.oracle.com).
This is the reference in the 11g documentation:
““Redo received from another Oracle database via redo transport is written to the current standby redo log group by a RFS background process. When a log switch occurs on the redo source database, incoming redo is then written to the next standby redo log group, and the previously used standby redo log group is archived by an ARCn background process.””
The only reference to ARCHIVELOG mode howeve is the ARCn process.
The bug reference number is 8513469.
Hi
I need to setup a three instance rac database.The datafiles and flash recovery area will be stored in ASM diskgroups called +data and +fra.
Which is the best location options for archivelogs so that they can be accessed during recovery without dba intervention ?
a-)
cluster file system with each instance writing to a shared location
or
b-)
cluster file system with each instance writing to a seperate location as long as all the locations are in directories under the same mount point
[…] v$logfile can be used to find the path of the redo log or their type (online/standby(used for standby database))For more information on standby redo logs :- https://www.pythian.com/news/581/oracle-standby-redo-logs/ […]
Nice script. I added set linesize 132 at the beginning and that helps.
thanks for posting.