Oracle Standby redo Logs
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
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.
August 15th, 2007 at 3:13 pm
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.
August 15th, 2007 at 7:26 pm
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.
August 16th, 2007 at 12:38 am
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
August 11th, 2008 at 3:43 am
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 rcat_owner@rcatdb
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;
RMAN> BACKUP CHECK LOGICAL FULL DATABASE PLUS ARCHIVELOG;
RMAN> exit;
scp source_backup_directory oracle@standby:standby_backup_directoryOn Destination or Standby db:
sqlplus “/ as sysdba”
startup nomount
exit;On Source/Primary db:
rman target / catalog rcat_owner@rcatdb auxiliary sys@STANDBY
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