Creating a physical and a logical standby database in a DR environment

In this note I’m going through a procedure to configure a primary database with both a physical standby and a logical standby. It assumes that the role switch occurs only between primary database and physical standby. The logical standby never turns into primary role and only remains for reporting purpose. To simplify the task, I will create both physical standby and logical standby on the same host as the primary database is located; since I’m using new method of 11g “DUPLICATE FROM ACTIVE DATABASE”, the whole procedure will be applicable if the standbys were on a remote host.

Configuration of primary, physical standby and logical standby


Primary database (primary role)

db_name=DB11R2;
db_unique_name=DB11R2;
LOG_ARCHIVE_CONFIG='DG_CONFIG=(db11r2,db11r2dr,db11r2ls)';
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,  ALL_ROLES) DB_UNIQUE_NAME=db11r2';
LOG_ARCHIVE_DEST_2='SERVICE=db11r2dr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=db11r2dr';
LOG_ARCHIVE_DEST_3='SERVICE=db11r2ls LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=db11r2ls';

LOG_ARCHIVE_DEST_STATE_1=ENABLE;
LOG_ARCHIVE_DEST_STATE_2=ENABLE;
LOG_ARCHIVE_DEST_STATE_3=ENABLE;
STANDBY_ARCHIVE_DEST (deprecated)
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE;
fal_client=DB11R2;
fal_server=DB11R2DR;

Physical standby database (standby role)

db_name=DB11R2;
db_unique_name=DB11R2DR;
LOG_ARCHIVE_CONFIG='DG_CONFIG=(db11r2,db11r2dr,db11r2ls)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db11r2dr';
LOG_ARCHIVE_DEST_2='SERVICE=db11r2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=db11r2';
LOG_ARCHIVE_DEST_3='SERVICE=db11r2ls LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=db11r2ls';

LOG_ARCHIVE_DEST_STATE_1=ENABLE;
LOG_ARCHIVE_DEST_STATE_2=ENABLE;
LOG_ARCHIVE_DEST_STATE_3=ENABLE;
STANDBY_ARCHIVE_DEST (deprecated)
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE;
fal_client=DB11R2DR;
fal_server=DB11R2;

Logical standby database (standby role)

db_name=DB11R2;
db_unique_name=DB11R2LS;
LOG_ARCHIVE_CONFIG='DG_CONFIG=(db11r2,db11r2dr,db11r2ls)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db11r2ls';
LOG_ARCHIVE_DEST_2='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=db11r2ls';
LOG_ARCHIVE_DEST_STATE_1=ENABLE;
LOG_ARCHIVE_DEST_STATE_2=ENABLE;
STANDBY_ARCHIVE_DEST(deprecated);
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE;
fal_client  is N/A;
fal_server  is N/A;


Step 1) Modifying Primary database

Step 1.1) Ensure the primary database is running in archivelog mode

sys@db11r2> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     194
Next log sequence to archive  197
Current log sequence           197

Step 1.2) Ensure “Force Logging” is enabled on primary database

Force logging ensures complete media recovery. it forces the writing of redo logs of DMLs even if “NOLOGGING” is specified on the object.See “how does nologging operation affect standby database“; the database never generates redo records for temporary tablespaces and temporary segments, so forced logging has no affect on them.

sys@db11r2> select force_logging from v$database;

FORCE_LOGGING
----------------------
YES

If it’s not yet activated, run this command

sys@db11r2> alter database force logging;

Step 1.3) Add standby redo logs on primary database

Current redo logs on primary database may look like


sys@db11r2> select l.thread#, l.group#, member, bytes, l.status from v$log l, v$logfile lf where lf.group#=l.group# order by group#;

THREAD#     GROUP# MEMBER                                                                      BYTES STATUS
------- ---------- ---------------------------------------------------------------------- ---------- --------
 1          1 /app/oracle/oradata/DB11R2/redo01.log                                    52428800 INACTIVE
 1          1 /app/oracle/flash_recovery_area/DB11R2/onlinelog/redo01_b.log            52428800 INACTIVE
 1          2 /app/oracle/flash_recovery_area/DB11R2/onlinelog/redo02_b.log            52428800 CURRENT
 1          2 /app/oracle/oradata/DB11R2/redo02.log                                    52428800 CURRENT
 1          3 /app/oracle/flash_recovery_area/DB11R2/onlinelog/redo03_b.log            52428800 INACTIVE
 1          3 /app/oracle/oradata/DB11R2/redo03.log                                    52428800 INACTIVE

As shown above, we have 3 online redo log groups all in thread #1 with 2 group members each. We need to add one more standby redo log groups than the number of online redo log groups on primary database. If there were more than one threads,  Use the following equation to determine an appropriate number of standby redo log file groups: (maximum number of logfiles for each thread + 1) * maximum number of threads. E.g. we have 2 threads, 3 redo log groups on each, then we need (3+1)*2=8 SRL groups. Ensure MAXLOGFILES and MAXLOGMEMBERS settings that will not limit you to add standby redo logs. (v$controlfile_record_section.type=”REDO LOG”).


sys@db11r2> alter database add standby logfile group 4 size 50M, group 5 size 50M, group 6 size 50M, group 7 size 50M;
Database altered.

sys@db11r2> select group#, member, type, IS_RECOVERY_DEST_FILE, status from v$logfile;

 GROUP# MEMBER                                                                      TYPE    IS_ STATUS
---------- ------------------------------------------------------------------------ ------- --- -------
 3 /app/oracle/oradata/DB11R2/redo03.log                                            ONLINE  NO
 2 /app/oracle/oradata/DB11R2/redo02.log                                            ONLINE  NO
 1 /app/oracle/oradata/DB11R2/redo01.log                                            ONLINE  NO
 3 /app/oracle/flash_recovery_area/DB11R2/onlinelog/redo03_b.log                    ONLINE  NO
 2 /app/oracle/flash_recovery_area/DB11R2/onlinelog/redo02_b.log                    ONLINE  NO
 1 /app/oracle/flash_recovery_area/DB11R2/onlinelog/redo01_b.log                    ONLINE  NO
 4 /app/oracle/oradata/DB11R2/onlinelog/o1_mf_4_6mzx09kb_.log                       STANDBY NO
 4 /app/oracle/flash_recovery_area/DB11R2/onlinelog/o1_mf_4_6mzx09o6_.log           STANDBY YES
 5 /app/oracle/oradata/DB11R2/onlinelog/o1_mf_5_6mzx09r4_.log                       STANDBY NO
 5 /app/oracle/flash_recovery_area/DB11R2/onlinelog/o1_mf_5_6mzx0dj9_.log           STANDBY YES
 6 /app/oracle/oradata/DB11R2/onlinelog/o1_mf_6_6mzx0hdf_.log                       STANDBY NO
 6 /app/oracle/flash_recovery_area/DB11R2/onlinelog/o1_mf_6_6mzx0l9m_.log           STANDBY YES
 7 /app/oracle/oradata/DB11R2/onlinelog/o1_mf_7_6mzx0odz_.log                       STANDBY NO
 7 /app/oracle/flash_recovery_area/DB11R2/onlinelog/o1_mf_7_6mzx0r7s_.log           STANDBY YES

Step 1.4) Modify init parameters on primary database.


db_name=DB11R2;
db_unique_name=DB11R2;

log_archive_config='DG_CONFIG=(DB11R2, DB11R2DR, DB11R2LS)';

LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DB11R2';
LOG_ARCHIVE_DEST_2='SERVICE=DB11R2DR LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11R2DR';
LOG_ARCHIVE_DEST_3='SERVICE=DB11R2LS LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11R2LS';
LOG_ARCHIVE_DEST_STATE_1=ENABLE;
LOG_ARCHIVE_DEST_STATE_2=DEFER; # enable it after creating logical standby
LOG_ARCHIVE_DEST_STATE_3=DEFER; # enable it after creating physical standby

FAL_SERVER=DB11R2DR;
FAL_CLIENT=DB11R2;

LOG_ARCHIVE_MAX_PROCESSES=16; # it should be between 4 and 30

Note the LOG_ARCHIVE_DEST_STATE_2 and LOG_ARCHIVE_DEST_STATE_3 are deferred at present. We’ll enable after creating physical standby and logical standby.

Note the setting of LOG_ARCHIVE_MAX_PROCESSES=16. The archive processes on primary database are responsible for archiving ORL as well as sending archived logs to standby to resolve redo log gap. On the standby site, they are responsible for archiving SRLs and forwarding archived log to cascade standby databases. So we need to specify this parameter to at least 4 with maximum of 30.

Step 1.5) Add entries in tnsnames.ora for physical standby and logical standby on primary database

DB11R2DR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11R2DR)
    )
  )

DB11R2LS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11R2LS)
    )
  )

Step 2) Modifying Physical Standby database

Step 2.1) Create a password file on physical standby database

[oracle@gchen-pc db]$ cd /app/oracle/product/10.2.0/db/dbs

[oracle@gchen-pc dbs]$ orapwd file=orapwDB11R2DR password=iamsys entries=5

Step 2.2) Create a init parameter file from spfile on primary database for physical database

[oracle@gchen-pc dbs]$ export ORACLE_SID=DB11R2
[oracle@gchen-pc dbs]$ sqlplus / as sysdba
sys@db11r2> create pfile='/app/oracle/product/11.2.0/db/dbs/initDB11R2DR.ora' from spfile;

File created.

Modify “/app/oracle/product/10.2.0/db/dbs/initDB11R2DR.ora” to set the following values for physical standby database init parameters:

# ---[ File Destination Parameters ] --- #

*.audit_file_dest='/app/oracle/admin/DB11R2DR/adump'
*.db_create_file_dest='/app/oracle/oradata'
*.db_recovery_file_dest_size=4294967296
*.db_recovery_file_dest='/app/oracle/flash_recovery_area'
*.diagnostic_dest='/app/oracle'

# ---[ Role-independent Parameters ] --- #
*.archive_lag_target=900
*.compatible='11.2.0.0.0'
*.control_files='/app/oracle/oradata/DB11R2DR/control01.ctl','/app/oracle/flash_recovery_area/DB11R2DR/controlfile/control02.ctl'
*.db_name='DB11R2'
*.db_domain=''
*.control_file_record_keep_time=14
*.db_block_size=8192
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11R2DRXDB)'
*.instance_name='DB11R2DR
*.log_archive_max_processes=4
*.remote_login_passwordfile='exclusive'
*.memory_max_target=1572864000
*.memory_target=1572864000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.undo_tablespace='UNDOTBS1'
*.log_archive_format='%t_%s_%r.dbf'
*.log_checkpoints_to_alert=TRUE
*.standby_file_management='auto'

# ---[ Primary Role Parameters ] --- #

*.db_unique_name='DB11R2DR'
*.service_names='DB11R2DR'
*.log_archive_config='DG_CONFIG=(DB11R2, DB11R2DR, DB11R2LS)'
*.log_archive_dest_1='location=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DB11R2DR'
*.log_archive_dest_2='service=DB11R2 LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=DB11R2'
*.log_archive_dest_3='service=DB11R2LS LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=DB11R2LS'

*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='defer'   # enable when logical standby is created
*.log_archive_dest_state_3='enable'

# ---[ Standby Role Parameters ] --- #
*.db_file_name_convert='/app/oracle/oradata/DB11R2/','/app/oracle/oradata/DB11R2DR/'
*.log_file_name_convert='/app/oracle/flash_recovery_area/DB11R2/onlinelog/','/app/oracle/flash_recovery_area/DB11R2DR/onlinelog/'
*.fal_client='DB11R2DR'
*.fal_server='DB11R2'

Step 2.3) Create required directories for standby database

audit directory

[oracle@gchen-pc ~]$ cd /app/oracle/admin
[oracle@gchen-pc admin]$ mkdir -p DB11R2DR/adump

oradata directory

[oracle@gchen-pc admin]$ cd /app/oracle/oradata

[oracle@gchen-pc oradata]$ tree -d DB11R2
DB11R2
|-- onlinelog

[oracle@gchen-pc oradata]$ mkdir -p DB11R2DR/onlinelog

Flash recovery area directory

[oracle@gchen-pc oradata]$ cd /app/oracle/flash_recovery_area/
[oracle@gchen-pc flash_recovery_area]$ tree -d -L 1 DB11R2/
DB11R2/
|-- archivelog
|-- autobackup
|-- backupset
|-- backupset_archivelog
|-- backupset_database
|-- controlfile
|-- onlinelog
`-- snapshot_controlf

[oracle@gchen-pc flash_recovery_area]$ mkdir DB11R2DR
[oracle@gchen-pc flash_recovery_area]$ cd DB11R2DR
[oracle@gchen-pc DB11R2DR]$ mkdir archivelog autobackup backupset backupset_archivelog backupset_database controlfile onlinelog snapshot_controlf

Step 2.4) Add tnsnames entries of logical standby database and primary database on physical standby database

In the tnsnames.ora of physical standby database add these entries (as we are creating standby on a same host, it’s the same as the tnsnames.ora of primary database).

# primary database
DB11R2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = gchen-pc.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11R2)
    )
  )

#logical standby
DB11R2LS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = gchen-pc.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11R2LS)
    )
  )

Step 2.5) Static registration of physical standby database to listener

In the listener.ora of physical standby database, staticly register the standby database to listener. This is required by RMAN connection to standby when the standby is in nomount status.

LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=gchen-pc.localdomain)(PORT=1521))
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DB11R2DR)
(ORACLE_HOME = /app/oracle/product/11.2.0/db)
(SID_NAME = DB11R2DR)
)
)

Reload listener to take the changes into effect.

[oracle@gchen-pc admin]$ lsnrctl reload; lsnrctl status

Step 3) Create physical standby by “RMAN DUPLICATE FROM ACTIVE DATABASE”

Step 3.1) Start up standby database in nomount state


[oracle@gchen-pc dbs]$ export ORACLE_SID=DB11R2DR
[oracle@gchen-pc dbs]$ sqlplus / as sysdba
idle> startup nomount pfile='initDB11R2DR.ora';
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2217952 bytes
Variable Size             490735648 bytes
Database Buffers          335544320 bytes
Redo Buffers                6606848 bytes

Step 3.2) From primary database, connect to both primary database and standby database using RMAN

export ORACLE_SID=DB11R2
[oracle@gchen-pc dbs]$ rman target sys/iamsys@db11r2 auxiliary sys/iamsys@db11r2dr

connected to target database: DB11R2 (DBID=4171050541)
connected to auxiliary database (not started)
                                ^^^^^^^^^^^^^
                                (it should be "not mounted", it seems something wrong)

Step 3.3) Run this command to create standby database

RMAN> duplicate target database for standby from active database dorecover;

Starting Duplicate Db at 2011-01-26 10:47:35
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/26/2011 10:47:35
RMAN-06403: could not obtain a fully authorized session
RMAN-04006: error from auxiliary database: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

After googling the error and check network settings, it turned out to be a problem of setting of static registration of the standby database. I correct it and then reload  it.

Repeat the step 3.2 and 3.3, this time it seems working fine.


[oracle@gchen-pc dbs]$ rman target sys/iamsys@db11r2 auxiliary sys/iamsys@db11r2dr log dup.log


Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 27 00:13:29 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB11R2 (DBID=4171050541)
connected to auxiliary database: DB11R2 (not mounted)
                                        ^^^^^^^^^^^^^ correct state!

RMAN> duplicate target database for standby from active database dorecover;
Starting Duplicate Db at 27-JAN-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/app/oracle/product/11.2.0/db/dbs/orapwDB11R2' auxiliary format
 '/app/oracle/product/11.2.0/db/dbs/orapwDB11R2DR'   ;
}
executing Memory Script

Starting backup at 27-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Finished backup at 27-JAN-11

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/app/oracle/oradata/DB11R2DR/control01.ctl';
   restore clone controlfile to  '/app/oracle/flash_recovery_area/DB11R2DR/controlfile/control02.ctl' from
 '/app/oracle/oradata/DB11R2DR/control01.ctl';
}
executing Memory Script

Starting backup at 27-JAN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/app/oracle/flash_recovery_area/DB11R2/snapshot_controlf/snapcf_DB11R2.f tag=TAG20110127T001543 RECID=8 STAMP=741485744
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27-JAN-11

Starting restore at 27-JAN-11
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 27-JAN-11

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/app/oracle/oradata/DB11R2DR/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/app/oracle/oradata/DB11R2DR/system01.dbf";
   set newname for datafile  2 to
 "/app/oracle/oradata/DB11R2DR/sysaux01.dbf";
   set newname for datafile  3 to
 "/app/oracle/oradata/DB11R2DR/undotbs01.dbf";
   set newname for datafile  4 to
 "/app/oracle/oradata/DB11R2DR/users01.dbf";
   set newname for datafile  5 to
 "/app/oracle/oradata/DB11R2DR/guang_tbs_01.dbf";
   set newname for datafile  6 to
 "/app/oracle/oradata/DB11R2DR/guang_tbs_02.dbf";
   set newname for datafile  7 to
 "/app/oracle/oradata/DB11R2DR/users02.dbf";
   set newname for datafile  8 to
 "/app/oracle/oradata/DB11R2DR/users03.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/app/oracle/oradata/DB11R2DR/system01.dbf"   datafile
 2 auxiliary format
 "/app/oracle/oradata/DB11R2DR/sysaux01.dbf"   datafile
 3 auxiliary format
 "/app/oracle/oradata/DB11R2DR/undotbs01.dbf"   datafile
 4 auxiliary format
 "/app/oracle/oradata/DB11R2DR/users01.dbf"   datafile
 5 auxiliary format
 "/app/oracle/oradata/DB11R2DR/guang_tbs_01.dbf"   datafile
 6 auxiliary format
 "/app/oracle/oradata/DB11R2DR/guang_tbs_02.dbf"   datafile
 7 auxiliary format
 "/app/oracle/oradata/DB11R2DR/users02.dbf"   datafile
 8 auxiliary format
 "/app/oracle/oradata/DB11R2DR/users03.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /app/oracle/oradata/DB11R2DR/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 27-JAN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/app/oracle/oradata/DB11R2/system01.dbf
output file name=/app/oracle/oradata/DB11R2DR/system01.dbf tag=TAG20110127T001554
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/app/oracle/oradata/DB11R2/undotbs01.dbf
output file name=/app/oracle/oradata/DB11R2DR/undotbs01.dbf tag=TAG20110127T001554
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/app/oracle/oradata/DB11R2/sysaux01.dbf
output file name=/app/oracle/oradata/DB11R2DR/sysaux01.dbf tag=TAG20110127T001554
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/app/oracle/oradata/DB11R2/users03.dbf
output file name=/app/oracle/oradata/DB11R2DR/users03.dbf tag=TAG20110127T001554
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/app/oracle/oradata/DB11R2/guang_tbs_01.dbf
output file name=/app/oracle/oradata/DB11R2DR/guang_tbs_01.dbf tag=TAG20110127T001554
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/app/oracle/oradata/DB11R2/users01.dbf
output file name=/app/oracle/oradata/DB11R2DR/users01.dbf tag=TAG20110127T001554
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/app/oracle/oradata/DB11R2/guang_tbs_02.dbf
output file name=/app/oracle/oradata/DB11R2DR/guang_tbs_02.dbf tag=TAG20110127T001554
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/app/oracle/oradata/DB11R2/users02.dbf
output file name=/app/oracle/oradata/DB11R2DR/users02.dbf tag=TAG20110127T001554
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 27-JAN-11

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/app/oracle/flash_recovery_area/DB11R2/onlinelog/1_106_725414530.dbf" auxiliary format
 "/app/oracle/flash_recovery_area/DB11R2DR/onlinelog/1_106_725414530.dbf"   archivelog like
 "/app/oracle/flash_recovery_area/DB11R2/onlinelog/1_107_725414530.dbf" auxiliary format
 "/app/oracle/flash_recovery_area/DB11R2DR/onlinelog/1_107_725414530.dbf"   ;
   catalog clone archivelog  "/app/oracle/flash_recovery_area/DB11R2DR/onlinelog/1_106_725414530.dbf";
   catalog clone archivelog  "/app/oracle/flash_recovery_area/DB11R2DR/onlinelog/1_107_725414530.dbf";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 27-JAN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=106 RECID=177 STAMP=741485764
output file name=/app/oracle/flash_recovery_area/DB11R2DR/onlinelog/1_106_725414530.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=107 RECID=178 STAMP=741485950
output file name=/app/oracle/flash_recovery_area/DB11R2DR/onlinelog/1_107_725414530.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 27-JAN-11

cataloged archived log
archived log file name=/app/oracle/flash_recovery_area/DB11R2DR/onlinelog/1_106_725414530.dbf RECID=1 STAMP=741485953

cataloged archived log
archived log file name=/app/oracle/flash_recovery_area/DB11R2DR/onlinelog/1_107_725414530.dbf RECID=2 STAMP=741485953

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=741485953 file name=/app/oracle/oradata/DB11R2DR/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=741485953 file name=/app/oracle/oradata/DB11R2DR/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=741485953 file name=/app/oracle/oradata/DB11R2DR/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=741485953 file name=/app/oracle/oradata/DB11R2DR/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=741485953 file name=/app/oracle/oradata/DB11R2DR/guang_tbs_01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=741485953 file name=/app/oracle/oradata/DB11R2DR/guang_tbs_02.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=14 STAMP=741485953 file name=/app/oracle/oradata/DB11R2DR/users02.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=15 STAMP=741485953 file name=/app/oracle/oradata/DB11R2DR/users03.dbf

contents of Memory Script:
{
   set until scn  3993346;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 27-JAN-11
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 106 is already on disk as file /app/oracle/flash_recovery_area/DB11R2DR/onlinelog/1_106_725414530.dbf
archived log for thread 1 with sequence 107 is already on disk as file /app/oracle/flash_recovery_area/DB11R2DR/onlinelog/1_107_725414530.dbf
archived log file name=/app/oracle/flash_recovery_area/DB11R2DR/onlinelog/1_106_725414530.dbf thread=1 sequence=106
archived log file name=/app/oracle/flash_recovery_area/DB11R2DR/onlinelog/1_107_725414530.dbf thread=1 sequence=107
media recovery complete, elapsed time: 00:00:01
Finished recover at 27-JAN-11
RMAN-05535: WARNING: All redo log files were not defined properly.
RMAN-05535: WARNING: All redo log files were not defined properly.
RMAN-05535: WARNING: All redo log files were not defined properly.
Finished Duplicate Db at 27-JAN-11

Step 3.4) Connect to the physical standby database and start MRP (Managed Recovery Process)

export ORACLE_SID=DB11R2DR
sqlplus / as sysdba

idle> shutdown immediate;
idl> starup
Database opened.

sys@db11r2dr> recover managed standby database using current logfile disconnect;
Media recovery complete.

[oracle@gchen-pc dbs]$ ps -ef |grep mrp
oracle    8346     1  0 00:45 ?        00:00:01 ora_mrp0_DB11R2DR
oracle    8370  5019  0 00:47 pts/2    00:00:00 grep mrp

Step 3.5) Remember to enable log_archive_dest_n_state on primary database


sys@db11r2> alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE;

Step 4) Modifying Logical Standby database

Step 4.1) Create a password file on physical standby database

[oracle@gchen-pc db]$ cd /app/oracle/product/10.2.0/db/dbs

[oracle@gchen-pc dbs]$ orapwd file=orapwDB11R2LS password=iamsys entries=5

Step 4.2) Create a init parameter file from spfile on primary database and save it into Oracle Home of physical database

[oracle@gchen-pc dbs]$ export ORACLE_SID=DB11R2
[oracle@gchen-pc dbs]$ sqlplus / as sysdba
sys@db11r2> create pfile='/app/oracle/product/11.2.0/db/dbs/initDB11R2LS.ora' from spfile;

File created.

Modify “/app/oracle/product/10.2.0/db/dbs/initDB11R2DR.ora” to set the following values for physical standby database init parameters:

# ---[ File Destination Parameters ] --- #

*.audit_file_dest='/app/oracle/admin/DB11R2LS/adump'
*.db_create_file_dest='/app/oracle/oradata'
*.db_recovery_file_dest_size=4294967296
*.db_recovery_file_dest='/app/oracle/flash_recovery_area'
*.diagnostic_dest='/app/oracle'

# ---[ Role-independent Parameters ] --- #
*.archive_lag_target=900
*.compatible='11.2.0.0.0'
*.control_files='/app/oracle/oradata/DB11R2LS/control01.ctl','/app/oracle/flash_recovery_area/DB11R2LS/controlfile/control02.ctl'
*.db_name='DB11R2'
*.db_domain=''
*.control_file_record_keep_time=14
*.db_block_size=8192
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11R2LSXDB)'
*.instance_name='DB11R2LS'
*.log_archive_max_processes=4
*.remote_login_passwordfile='exclusive'
*.memory_max_target=1572864000
*.memory_target=1572864000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.undo_tablespace='UNDOTBS1'
*.log_archive_format='%t_%s_%r.dbf'
*.log_checkpoints_to_alert=TRUE
*.standby_file_management='auto'

# ---[ Primary Role Parameters ] --- #

*.db_unique_name='DB11R2LS'
*.service_names='DB11R2LS'
*.log_archive_config='DG_CONFIG=(DB11R2, DB11R2DR, DB11R2LS)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db11r2ls'
*.log_archive_dest_2='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=db11r2ls'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'

# ---[ Standby Role Parameters ] --- #
*.db_file_name_convert='/app/oracle/oradata/DB11R2/','/app/oracle/oradata/DB11R2LS/'
*.log_file_name_convert='/app/oracle/flash_recovery_area/DB11R2/onlinelog/','/app/oracle/flash_recovery_area/DB11R2LS/onlinelog/'

Step 4.3) Create required directories for standby database

audit directory

[oracle@gchen-pc ~]$ cd /app/oracle/admin
[oracle@gchen-pc admin]$ mkdir -p DB11R2LS/adump

oradata directory

[oracle@gchen-pc admin]$ cd /app/oracle/oradata

[oracle@gchen-pc oradata]$ tree -d DB11R2
DB11R2
|-- onlinelog

[oracle@gchen-pc oradata]$ mkdir -p DB11R2LS/onlinelog

Flash recovery area directory

[oracle@gchen-pc oradata]$ cd /app/oracle/flash_recovery_area/
[oracle@gchen-pc flash_recovery_area]$ tree -d -L 1 DB11R2/
DB11R2/
|-- archivelog
|-- autobackup
|-- backupset
|-- backupset_archivelog
|-- backupset_database
|-- controlfile
|-- onlinelog
`-- snapshot_controlf

[oracle@gchen-pc flash_recovery_area]$ mkdir DB11R2LS
[oracle@gchen-pc flash_recovery_area]$ cd DB11R2LS
[oracle@gchen-pc DB11R2DR]$ mkdir archivelog autobackup backupset backupset_archivelog backupset_database controlfile onlinelog snapshot_controlf

Step 4.4) Add tnsnames entries of physical standby database and primary database on physical standby database

In the tnsnames.ora of physical standby database add these entries (as we are creating standby on a same host, it’s the same as the tnsnames.ora of primary database).

# primary database
DB11R2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = gchen-pc.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11R2)
    )
  )

#physical standby
DB11R2DR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = gchen-pc.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11R2DR)
    )
  )

Step 4.5) Static registration of logical standby database to listener

In the listener.ora of physical standby database, staticly register the standby database to listener. This is required by RMAN connection to standby when the standby is in nomount status.

LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=gchen-pc.localdomain)(PORT=1521))
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DB11R2LS)
(ORACLE_HOME = /app/oracle/product/11.2.0/db)
(SID_NAME = DB11R2LS)
)
)

Reload listener to take the changes into effect.

[oracle@gchen-pc admin]$ lsnrctl reload; lsnrctl status

Step 5) Create logical standby database

We can create a logical standby database by setting up a physical standby database first.Before creating the physical standby, we have a few of prerequisites to check.

1) prerequisite

A few data types are not supported on standby database such as listed below:

  • BFILE
  • ROWID, UROWID
  • User-defined types
  • Collections (including VARRAYS and nested tables)
  • XML type
  • Encrypted columns
  • Multimedia data types (including Spatial, Image, and Context)

For a completed list of data type and storage consideration, please refer to Appendix C in document “Oracle® Data Guard Concepts and Administration” . Moreover, physical allocation of the data in logical standby database is different from that in primary database, so ROWID of query generated on primary database can not be used to identify corresponding rows in logical standby database. Instead, Oracle uses primary key, if presents in a table, along with modified columns as unique identifier, or in case of absence of primary key, uses unique index along with modified columns, or in case of absence of both, uses all modified columns as unique identifier. We need to find out all tables lack of ability to uniquely identify rows on standby database. 1.1) identifying data types unsupported by standby database Run the following query to find out data types on primary database that are not supported by logical standby.

select OWNER, TABLE_NAME, COLUMN_NAME, ATTRIBUTES, DATA_TYPE from dba_logstdby_unsupported;

OWNER      TABLE_NAME           COLUMN_NAME          ATTRIBUTES DATA_TYPE
---------- -------------------- -------------------- ---------- --------------------------------
SCOTT      EXCEPTIONS           ROW_ID                          UROWID

1.2 Ensure Table Rows in the Primary Database Can Be Uniquely Identified Use following query to display a list of tables that SQL Apply may not be able to uniquely identify:

SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
WHERE (OWNER, TABLE_NAME) NOT IN
(SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)
  AND BAD_COLUMN = 'Y';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SCOTT                          SCOTT_BLOB

1.3 create disabled primary key with RELY constraint on a table

If you ensure rows in a table are unique, you can create a disabled primary key RELY constraint on the table. Thus logging primary key along with modifications generates overhead on the primary database. For example: ALTER TABLE T ADD PRIMARY KEY (id, name) RELY DISABLE

Step 5.1) Start up the 2nd standby database in nomount state


[oracle@gchen-pc dbs]$ export ORACLE_SID=DB11R2LS
[oracle@gchen-pc dbs]$ sqlplus / as sysdba
idle> startup nomount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2217952 bytes
Variable Size             490735648 bytes
Database Buffers          335544320 bytes
Redo Buffers                6606848 bytes

Step 5.2) From primary database, connect to both primary database and standby database using RMAN

export ORACLE_SID=DB11R2
[oracle@gchen-pc dbs]$ rman target / auxiliary sys/iamsys@db11r2ls

connected to target database: DB11R2 (DBID=4171050541)
connected to auxiliary database DB11R2 (not mounted)

Step 5.3) Run this command to create standby database

[oracle@gchen-pc dbs]$ rman target sys/iamsys@db11r2 auxiliary sys/iamsys@db11r2ls

RMAN> spool log to 'duplication.log'
RMAN> duplicate target database for standby from active database dorecover;

From another shell monitor rman log file


Spooling started in log file: duplication.log

Recovery Manager11.2.0.1.0

RMAN>
Starting Duplicate Db at 2011-02-25 23:55:05
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK

contents of Memory Script:
{
 backup as copy reuse
 targetfile  '/app/oracle/product/11.2.0/db/dbs/orapwDB11R2' auxiliary format
 '/app/oracle/product/11.2.0/db/dbs/orapwDB11R2LS'   ;
}
executing Memory Script

Starting backup at 2011-02-25 23:55:08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
Finished backup at 2011-02-25 23:55:10

contents of Memory Script:
{
 backup as copy current controlfile for standby auxiliary format  '/app/oracle/oradata/DB11R2LS/control01.ctl';
 restore clone controlfile to  '/app/oracle/flash_recovery_area/DB11R2LS/controlfile/control02.ctl' from
 '/app/oracle/oradata/DB11R2LS/control01.ctl';
}
executing Memory Script

Starting backup at 2011-02-25 23:55:10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/app/oracle/flash_recovery_area/DB11R2/snapshot_controlf/snapcf_DB11R2.f tag=TAG20110225T235510 RECID=17 STAMP=744076511
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2011-02-25 23:55:14

Starting restore at 2011-02-25 23:55:14
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 2011-02-25 23:55:16

contents of Memory Script:
{
 sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
 set newname for tempfile  1 to
 "/app/oracle/oradata/DB11R2LS/temp01.dbf";
 switch clone tempfile all;
 set newname for datafile  1 to
 "/app/oracle/oradata/DB11R2LS/system01.dbf";
 set newname for datafile  2 to
 "/app/oracle/oradata/DB11R2LS/sysaux01.dbf";
 set newname for datafile  3 to
 "/app/oracle/oradata/DB11R2LS/undotbs01.dbf";
 set newname for datafile  4 to
 "/app/oracle/oradata/DB11R2LS/users01.dbf";
 set newname for datafile  5 to
 "/app/oracle/oradata/DB11R2LS/guang_tbs_01.dbf";
 set newname for datafile  6 to
 "/app/oracle/oradata/DB11R2LS/guang_tbs_02.dbf";
 set newname for datafile  7 to
 "/app/oracle/oradata/DB11R2LS/users02.dbf";
 set newname for datafile  8 to
 "/app/oracle/oradata/DB11R2LS/users03.dbf";
 backup as copy reuse
 datafile  1 auxiliary format
 "/app/oracle/oradata/DB11R2LS/system01.dbf"   datafile
 2 auxiliary format
 "/app/oracle/oradata/DB11R2LS/sysaux01.dbf"   datafile
 3 auxiliary format
 "/app/oracle/oradata/DB11R2LS/undotbs01.dbf"   datafile
 4 auxiliary format
 "/app/oracle/oradata/DB11R2LS/users01.dbf"   datafile
 5 auxiliary format
 "/app/oracle/oradata/DB11R2LS/guang_tbs_01.dbf"   datafile
 6 auxiliary format
 "/app/oracle/oradata/DB11R2LS/guang_tbs_02.dbf"   datafile
 7 auxiliary format
 "/app/oracle/oradata/DB11R2LS/users02.dbf"   datafile
 8 auxiliary format
 "/app/oracle/oradata/DB11R2LS/users03.dbf"   ;
 sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /app/oracle/oradata/DB11R2LS/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 2011-02-25 23:55:24
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/app/oracle/oradata/DB11R2/system01.dbf
output file name=/app/oracle/oradata/DB11R2LS/system01.dbf tag=TAG20110225T235524
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/app/oracle/oradata/DB11R2/undotbs01.dbf
output file name=/app/oracle/oradata/DB11R2LS/undotbs01.dbf tag=TAG20110225T235524
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/app/oracle/oradata/DB11R2/sysaux01.dbf
output file name=/app/oracle/oradata/DB11R2LS/sysaux01.dbf tag=TAG20110225T235524
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/app/oracle/oradata/DB11R2/users03.dbf
output file name=/app/oracle/oradata/DB11R2LS/users03.dbf tag=TAG20110225T235524
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/app/oracle/oradata/DB11R2/guang_tbs_01.dbf
output file name=/app/oracle/oradata/DB11R2LS/guang_tbs_01.dbf tag=TAG20110225T235524
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/app/oracle/oradata/DB11R2/users01.dbf
output file name=/app/oracle/oradata/DB11R2LS/users01.dbf tag=TAG20110225T235524
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/app/oracle/oradata/DB11R2/guang_tbs_02.dbf
output file name=/app/oracle/oradata/DB11R2LS/guang_tbs_02.dbf tag=TAG20110225T235524
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/app/oracle/oradata/DB11R2/users02.dbf
output file name=/app/oracle/oradata/DB11R2LS/users02.dbf tag=TAG20110225T235524
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2011-02-25 23:59:10

sql statement: alter system archive log current

contents of Memory Script:
{
 backup as copy reuse
 archivelog like  "/app/oracle/flash_recovery_area/DB11R2/archivelog/2011_02_25/o1_mf_1_278_6pjf1zlb_.arc" auxiliary format
 "/app/oracle/flash_recovery_area/DB11R2LS/archivelog/2011_02_25/o1_mf_1_278_%u_.arc"   ;
 catalog clone recovery area;
 switch clone datafile all;
}
executing Memory Script

Starting backup at 2011-02-25 23:59:12
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=278 RECID=582 STAMP=744076751
output file name=/app/oracle/flash_recovery_area/DB11R2LS/archivelog/2011_02_25/o1_mf_1_278_4fm5jdei_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 2011-02-25 23:59:15

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /app/oracle/flash_recovery_area/DB11R2LS/duplication.log
File Name: /app/oracle/flash_recovery_area/DB11R2LS/archivelog/2011_02_25/o1_mf_1_277_45m5jbo5_.arc
File Name: /app/oracle/flash_recovery_area/DB11R2LS/archivelog/2011_02_25/o1_mf_1_278_4fm5jdei_.arc
File Name: /app/oracle/flash_recovery_area/DB11R2LS/archivelog/2011_02_25/o1_mf_1_271_6pj24nhf_.arc
File Name: /app/oracle/flash_recovery_area/DB11R2LS/archivelog/2011_02_25/o1_mf_1_273_6pj25bfv_.arc
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /app/oracle/flash_recovery_area/DB11R2LS/archivelog/2011_02_25/o1_mf_1_277_45m5jbo5_.arc
File Name: /app/oracle/flash_recovery_area/DB11R2LS/archivelog/2011_02_25/o1_mf_1_278_4fm5jdei_.arc
File Name: /app/oracle/flash_recovery_area/DB11R2LS/archivelog/2011_02_25/o1_mf_1_271_6pj24nhf_.arc
File Name: /app/oracle/flash_recovery_area/DB11R2LS/archivelog/2011_02_25/o1_mf_1_273_6pj25bfv_.arc

List of Files Which Where Not Cataloged
=======================================
File Name: /app/oracle/flash_recovery_area/DB11R2LS/duplication.log
 RMAN-07517: Reason: The file header is corrupted

List of files in Recovery Area not managed by the database
==========================================================
File Name: /app/oracle/flash_recovery_area/DB11R2LS/controlfile/control02.ctl
 RMAN-07526: Reason: File is not an Oracle Managed File

number of files not managed by recovery area is 1, totaling 10.00MB

datafile 1 switched to datafile copy
input datafile copy RECID=17 STAMP=744076757 file name=/app/oracle/oradata/DB11R2LS/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=18 STAMP=744076757 file name=/app/oracle/oradata/DB11R2LS/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=19 STAMP=744076757 file name=/app/oracle/oradata/DB11R2LS/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=20 STAMP=744076757 file name=/app/oracle/oradata/DB11R2LS/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=21 STAMP=744076757 file name=/app/oracle/oradata/DB11R2LS/guang_tbs_01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=22 STAMP=744076757 file name=/app/oracle/oradata/DB11R2LS/guang_tbs_02.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=23 STAMP=744076757 file name=/app/oracle/oradata/DB11R2LS/users02.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=24 STAMP=744076757 file name=/app/oracle/oradata/DB11R2LS/users03.dbf

contents of Memory Script:
{
 set until scn  5625562;
 recover
 standby
 clone database
 delete archivelog
 ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 2011-02-25 23:59:19
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 278 is already on disk as file /app/oracle/flash_recovery_area/DB11R2LS/archivelog/2011_02_25/o1_mf_1_278_4fm5jdei_.arc
archived log file name=/app/oracle/flash_recovery_area/DB11R2LS/archivelog/2011_02_25/o1_mf_1_278_4fm5jdei_.arc thread=1 sequence=278
media recovery complete, elapsed time: 00:00:01
Finished recover at 2011-02-25 23:59:26
Finished Duplicate Db at 2011-02-25 23:59:56

RMAN>

Monitor rman process by querying on views v$session_longops, v$rman_status select s.process, r.operation, r.status, r.mbytes_processed as “PCT”, s.status from v$rman_status r join v$session s using (sid); Step 5.4) Connect to the physical standby database and start MRP (Managed Recovery Process)

export ORACLE_SID=DB11R2LS
sqlplus / as sysdba

idle> shutdown immediate;
idl> starup
Database opened.

sys@db11r2ls> recover managed standby database using current logfile disconnect;
Media recovery complete.

[oracle@gchen-pc dbs]$ ps -ef |grep mrp
oracle    8346     1  0 00:45 ?        00:00:01 ora_mrp0_DB11R2LS
oracle    8370  5019  0 00:47 pts/2    00:00:00 grep mrp

Step 5.4) Make sure this physical standby is in Sync with primary databaseUse following query on Standby to check:

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied
---------- ---------------------- ---------------------
         1                    187                   187

Step 5.5) Stop Redo Apply on the Physical Standby Database


sys@db11r2ls> alter database recover managed standby database cancel;

Step 5.6) Build a Dictionary in the Redo Data on Primary Database

export ORACLE_SID=DB11R2
sqlplus / as sysdba
sys@db11r2> exec DBMS_LOGSTDBY.BUILD; 

The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete. Long-running transactions executed on the primary database will affect the timeliness of this command.
Convert to a Logical Standby Database

sys@db11r2ls> alter database recover to logical standby DB11R2LS;
NOTE: at the first of execution of the previous command, it hanged for quit a long time before I canceled it. The alert.log file explained that database had been waiting for latest archived log to be arrive from primary but failed to receive it, just as shown below:

"Media Recovery Waiting for thread 1 sequence 188 (in transit)..."

After repeating to re-create the standby for several times, I found the problem is that I did not fully in accordance with the steps described in note 738643.1, as I built the standby dictionary on primary (step 5.6) before stopping the managed recovery process on standby (step 5.5). Having fixed that problem, the command “alter database recover to logical standby” was executed instantly.
Step 5.7) Shutdown and Startup Logical Standby Database in Mount Stage

sys@db11gr2ls> shutdown immediate;
idle> startup mount;

Step 5.8) Open the Logical Standby Database
idle> alter database open resetlogs;
Step 5.9) Start Logical Apply on Standby

sys@db11gr2ls> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Reference: Cascaded Standby Databases [ID 409013.1]
Step by Step Guide on How to Create Logical Standby [ID 738643.1]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: