CREATING LOGICAL STANDBY DATABASE – ORACLE 10GR2
Creating a Logical Standby Database

 

Database DB_UNIQUE_NAME Oracle Net Service Name Host name
Primary s2idm s2idm svlidmdb01-stg
Logical standby s1idm s1idm sacidmdb01-stg

 

1.     Prerequisite Conditions on Primary database

1.1   Determine Support for Data Types and Storage Attributes for Tables

See “Oracle Data Guard concepts and Administration 10g Release 2(10.2)” Appendix C for a complete list of data type and storage type considerations.

1.2    Ensure Table Rows in the Primary Database Can Be Uniquely Identified

1.2.1 Find tables without unique logical identifier in the primary database.

[SQL1]

 SQL> 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

—————————— ——————————

TSMSYS                         SRS$

XELLERATE                      PLAN_TABLE

1.2.2 Add a disabled primary-key RELY constraint.

[SQL2]

SQL> ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;

 

2.     

Create a Physical Standby Database

2.1 Preparing the Primary Database for Standby Database Creation

2.1.1 Enable Forced Logging
[SQL3]

SQL> ALTER DATABASE FORCE LOGGING;

2.1.2 Create a Password File if one does not already exist
[CMD1]

Svlidmdb01-stg{oracle}: cd $ORACLE_HOME/dbsSvlidmdb01-stg{oracle}: orapwd file=orapw${ORACLE_SID} password=xxxx entries=10

2.1.3 Configure a Standby Redo Log

2.1.3.1 Ensure log file sizes are identical on the primary and standby databases.

2.1.3.2 Determine the appropriate number of standby redo log file groups.
(maximum number of logfiles for each thread + 1) * maximum number of threads

 

Using this equation reduces the likelihood that the primary instance’s log writer (LGWR) process will be blocked because a standby redo log file cannot be allocated on the standby database. For example, if the primary database has 2 log files for each thread and 2 threads, then 6 standby redo log file groups are needed on the standby database
2.1.3.3 Verify related database parameters and settings (MAXLOGFILES, MAXLOGMEMBERS)
[SQL4]

SQL> select type,records_total from v$controlfile_record_section  2  where type = ‘REDO LOG’;TYPE                         RECORDS_TOTAL

—————————- ————-

REDO LOG                                40

* 40 is MAXLOGFILES.

SQL> select dimlm from x$kccdi;

DIMLM

———-

3

* 3 is MAXLOGMEMBERS.

2.1.3.4 Create standby redo log file groups.

– The group number must be between 1 and the value of the MAXLOGFILES clause.
– Do not skip log file group numbers.
– Although the standby redo log is only used when the database is running in the standby role,
Oracle recommends that you create a standby redo log on the primary database so that the
primary database can switch over quickly to the standby role without the need for additional
DBA intervention.

[SQL5]

ALTER DATABASE ADD STANDBY LOGFILE GROUP 20
(‘/oracle/redo_1/s2idm/sbredo20a.log’,
‘/oracle/redo_2/s2idm/sbredo20b.log’) SIZE 200M;ALTER DATABASE ADD STANDBY LOGFILE GROUP 21
(‘/oracle/redo_1/s2idm/sbredo21a.log’,
‘/oracle/redo_2/s2idm/sbredo21b.log’) SIZE 200M;ALTER DATABASE ADD STANDBY LOGFILE GROUP 22
(‘/oracle/redo_1/s2idm/sbredo22a.log’,
‘/oracle/redo_2/s2idm/sbredo22b.log’) SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 23
(‘/oracle/redo_1/s2idm/sbredo23a.log’,
‘/oracle/redo_2/s2idm/sbredo23b.log’) SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 24
(‘/oracle/redo_1/s2idm/sbredo24a.log’,
‘/oracle/redo_2/s2idm/sbredo24b.log’) SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 25
(‘/oracle/redo_1/s2idm/sbredo25a.log’,
‘/oracle/redo_2/s2idm/sbredo25b.log’) SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 26
(‘/oracle/redo_1/s2idm/sbredo26a.log’,
‘/oracle/redo_2/s2idm/sbredo26b.log’) SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 27
(‘/oracle/redo_1/s2idm/sbredo27a.log’,
‘/oracle/redo_2/s2idm/sbredo27b.log’) SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 28
(‘/oracle/redo_1/s2idm/sbredo28a.log’,
‘/oracle/redo_2/s2idm/sbredo28b.log’) SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 29
(‘/oracle/redo_1/s2idm/sbredo29a.log’,
‘/oracle/redo_2/s2idm/sbredo29b.log’) SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 30
(‘/oracle/redo_1/s2idm/sbredo30a.log’,
‘/oracle/redo_2/s2idm/sbredo30b.log’) SIZE 200M;

2.1.3.5 Verify the standby redo log file groups were created on the Primary database once it has
been created. (Primary, create them on Standby later)

[SQL6]

SQL> set linesize 120;SQL> set pagesize 60;SQL> column member format a40;

SQL> SELECT v$logfile.member,v$logfile.group#,v$standby_log.status,v$standby_log.bytes

FROM v$standby_log, v$logfile

WHERE v$standby_log.group# = v$logfile.group#

ORDER BY v$logfile.group#;

MEMBER                                       GROUP# STATUS          BYTES

—————————————- ———- ———- ———-

/oracle/redo_1/s2idm/sbredo20a.log               20 UNASSIGNED  209715200

/oracle/redo_2/s2idm/sbredo20b.log               20 UNASSIGNED  209715200

/oracle/redo_1/s2idm/sbredo21a.log               21 UNASSIGNED  209715200

/oracle/redo_2/s2idm/sbredo21b.log               21 UNASSIGNED  209715200

/oracle/redo_1/s2idm/sbredo22a.log               22 UNASSIGNED  209715200

/oracle/redo_2/s2idm/sbredo22b.log               22 UNASSIGNED  209715200

/oracle/redo_1/s2idm/sbredo23a.log               23 UNASSIGNED  209715200

/oracle/redo_2/s2idm/sbredo23b.log               23 UNASSIGNED  209715200

/oracle/redo_1/s2idm/sbredo24a.log               24 UNASSIGNED  209715200

/oracle/redo_2/s2idm/sbredo24b.log               24 UNASSIGNED  209715200

/oracle/redo_1/s2idm/sbredo25a.log               25 UNASSIGNED  209715200

/oracle/redo_2/s2idm/sbredo25b.log               25 UNASSIGNED  209715200

/oracle/redo_1/s2idm/sbredo26a.log               26 UNASSIGNED  209715200

/oracle/redo_2/s2idm/sbredo26b.log               26 UNASSIGNED  209715200

/oracle/redo_1/s2idm/sbredo27a.log               27 UNASSIGNED  209715200

/oracle/redo_2/s2idm/sbredo27b.log               27 UNASSIGNED  209715200

/oracle/redo_1/s2idm/sbredo28a.log               28 UNASSIGNED  209715200

/oracle/redo_2/s2idm/sbredo28b.log               28 UNASSIGNED  209715200

/oracle/redo_1/s2idm/sbredo29a.log               29 UNASSIGNED  209715200

/oracle/redo_2/s2idm/sbredo29b.log               29 UNASSIGNED  209715200

/oracle/redo_1/s2idm/sbredo30a.log               30 UNASSIGNED  209715200

/oracle/redo_2/s2idm/sbredo30b.log               30 UNASSIGNED  209715200

22 rows selected.

2.1.4 Set Primary Database Initialization Parameters

Database DB_UNIQUE_NAME Oracle Net Service Name
Primary s2idm s2idm
Physical standby s1idm s1idm

2.1.4.1 Primary Database: Primary Role Initialization Parameters

DB_NAME=s2idmDB_UNIQUE_NAME=s2idmLOG_ARCHIVE_CONFIG=’DG_CONFIG=(s2idm,s1idm)’

CONTROL_FILES=’/oracle/redo_1/s2idm/control01.ctl’, ‘ /oracle/redo_2/s2idm/control02.ctl’, ‘/oracle/oradata/s2idm/control03.ctl’

LOG_ARCHIVE_DEST_1=’LOCATION=/oracle/arch_1/s2idm/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=s2idm’

LOG_ARCHIVE_DEST_2=’SERVICE=s1idm LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=s1idm’

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=s2idm_%r_%t_%s.arc

LOG_ARCHIVE_MAX_PROCESSES=30

2.1.4.2 Primary Database: Standby Role Initialization Parameters

FAL_CLIENT=s2idmFAL_SERVER=s1idmDB_FILE_NAME_CONVERT=’s1idm’,’s2idm’

LOG_FILE_NAME_CONVERT=’/oracle/arch_1/s1idm/’,’/oracle/arch_1/s2idm/’,’/oracle/arch_2/s1idm/’,’/oracle/arch_2/s2idm/’

STANDBY_FILE_MANAGEMENT=AUTO

2.1.4.3 To verify
[SQL7]

set linesize 150;set pagesize 60;column name format a25;

column value format a100;

select name, value

from v$parameter where name in (‘db_name’,

‘db_unique_name’,

‘log_archive_config’,

‘control_files’,

‘log_archive_dest_1′,

‘log_archive_dest_2′,

‘log_archive_dest_3′,
‘log_archive_dest_4′,

‘log_archive_dest_state_1′,

‘log_archive_dest_state_2′,
‘log_archive_dest_state_3′,
‘log_archive_dest_state_4′,

‘remote_login_passwordfile’,

‘log_archive_format’,

‘log_archive_max_processes’,

‘fal_server’,

‘fal_client’,

‘db_file_name_convert’,

‘log_file_name_convert’,

‘standby_file_management’

) order by name;

NAME                      VALUE

————————- ——————————————————————————————–

control_files             /oracle/redo_1/s2idm/control01.ctl, /oracle/redo_2/s2idm/control02.ctl,
/oracle/oradata/s2idm/data1/control03.ctl

db_file_name_convert      /oracle/oradata/s1idm/data2/, /oracle/oradata/s2idm/data2/,
/oracle/oradata/s1idm/data1/, /oracle/oradata/s2idm/data1/

db_name                   s2idm

db_unique_name            s2idm

fal_client                (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST=svlidmdb01-stg.corp.netapp.com)(PORT=7300)))

(CONNECT_DATA=(SERVICE_NAME=s2idm_XPT.corp.netapp.com)
(INSTANCE_NAME=s2idm)(SERVER=dedicated)))

fal_server                (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST=sacidmdb01-stg.corp.netapp.com)(PORT=7300)))

(CONNECT_DATA=(SERVICE_NAME=s1idm_XPT.corp.netapp.com)

(INSTANCE_NAME=s1idm)(SERVER=dedicated)))

log_archive_config        DG_CONFIG=(s1idm,s2idm)

log_archive_dest_1        location=”/oracle/arch_1/s2idm/”, valid_for=(ALL_LOGFILES,ALL_ROLES)

log_archive_dest_2        location=/oracle/arch_2/s2idm/

log_archive_dest_3

log_archive_dest_4

log_archive_dest_state_1  ENABLE

log_archive_dest_state_2  ENABLE

log_archive_dest_state_3  ENABLE

log_archive_dest_state_4  ENABLE

log_archive_format        s2idm_%r_%t_%s.arc

log_archive_max_processes 10

log_file_name_convert     /oracle/redo_1/s1idm, /oracle/redo_1/s2idm, /oracle/redo_2/s1idm, /oracle/redo_2/s2idm

remote_login_passwordfile EXCLUSIVE

standby_file_management   AUTO

*  save $ORACLE_HOME/dbs/spfiles2idm.ora before you change the values.
* The sample of fixing configuration issues:

SQL> alter system set log_archive_config=’DG_CONFIG=(s1idm,s2idm)’ scope=both;

SQL> alter system set db_file_name_convert=
‘/oracle/oradata/s1idm/data1′,’/oracle/oradata/s2idm/data1′,
‘/oracle/oradata/s1idm/data2′,’/oracle/oradata/s2idm/data2′ scope=spfile;

SQL> alter system set log_file_name_convert=
‘/oracle/arch_1/s1idm/’,’/oracle/arch_1/s2idm/’,
‘/oracle/arch_2/s1idm/’,’/oracle/arch_2/s2idm/’ scope=spfile;

SQL> alter system set fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=sacidmdb01-stg.corp.netapp.com)(PORT=7300)))(CONNECT_DATA=(SERVICE_NAME=s2idm_XPT.corp.netapp.com)(INSTANCE_NAME=s2idm)(SERVER=dedicated)))’ scope=both;

SQL> alter system set fal_client=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=svlidmdb01-stg.corp.netapp.com)(PORT=7300)))(CONNECT_DATA=(SERVICE_NAME=s1idm_XPT.corp.netapp.com)(INSTANCE_NAME=s1idm)(SERVER=dedicated)))

SQL> alter system set standby_file_management=AUTO;

2.1.5 Generate rename script from the Primary database for the datafiles and logfiles.

2.1.5.1 Create create_rename_datafile_for_stdby.sql and run it on Primary database.
[SQL8]

Set linesize 120;set pagesize 500;set heading off;

spool spool_create_rename_datafile_for_stndby.sql

select ‘ALTER DATABASE RENAME FILE ”’||file_name||”’ TO ‘||chr(10)||””||file_name||”’ ; ‘ from dba_data_files

UNION ALL

select ‘ALTER DATABASE RENAME FILE ”’||member||”’ TO ‘||chr(10)||””||member||”’ ; ‘ from v$logfile ;

select ‘ALTER DATABASE RENAME FILE ”’||file_name||”’ TO ‘||chr(10)||””||file_name||”’ ; ‘

from dba_temp_files;

spool off

2.1.5.2 Modify the output of create_rename_datafile_for_stdby.sql,
spool_create_rename_datafile_for_stndby.sql to fit the file locations on Standby System
[SQL9]

ALTER DATABASE RENAME FILE ‘/oracle/oradata/s2idm/data1/system01.dbf’ TO’/oracle/oradata/s1idm/data1/system01.dbf’ ;ALTER DATABASE RENAME FILE ‘/oracle/oradata/s2idm/data1/tivoliorts01.dbf’ TO

‘/oracle/oradata/s1idm/data1/tivoliorts01.dbf’ ;

ALTER DATABASE RENAME FILE ‘/oracle/oradata/s2idm/data2/sysaux01.dbf’ TO

‘/oracle/oradata/s1idm/data2/sysaux01.dbf’ ;
. . .

2.1.6 Enable Archiving if archiving is not enabled on the primary database.
[SQL10]

SQL> SHUTDOWN IMMEDIATE;SQL> STARTUP MOUNT;SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

2.2 Creating a Physical Standby Database

2.2.1 Create a Backup Copy of the Primary Database Datafiles (Primary)

$ $DBA_UNIX/hotbackup_multi_filers.sh s2idm$ sudo rsh svlstgflr11 snap rename s2idm_stg_oradata01 s2idm_daily.0 s2idm_bef_cr_phystd_oradata01_030309$ sudo rsh svlstgflr12 snap rename s2idm_stg_oradata02 s2idm_daily.0 s2idm_bef_cr_phystd_oradata02_030309

$ sudo rsh svlstgflr11 snap create s2idm_stg_arch01 s2idm_bef_cr_phystd_arch01_030309
$ sudo rsh svlstgflr11 snap create s2idm_stg_redo01 s2idm_bef_cr_phystd_redo01_030309
$ sudo rsh svlstgflr11 snap create s2idm_stg_stdby1 s2idm_bef_cr_phystd_stdby1_030309
$ sudo rsh svlstgflr12 snap create s2idm_stg_arch02 s2idm_bef_cr_phystd_arch02_030309
$ sudo rsh svlstgflr12 snap create s2idm_stg_redo02 s2idm_bef_cr_phystd_redo02_030309
$ sudo rsh svlstgflr12 snap create s2idm_stg_stdby2 s2idm_bef_cr_phystd_stdby2_030309

$ sudo rsh svlstgflr11 snap list s2idm_stg_oradata01
$ sudo rsh svlstgflr12 snap list s2idm_stg_oradata02
$ sudo rsh svlstgflr11 snap list s2idm_stg_arch01
$ sudo rsh svlstgflr11 snap list s2idm_stg_redo01
$ sudo rsh svlstgflr11 snap list s2idm_stg_stdby1
$ sudo rsh svlstgflr12 snap list s2idm_stg_arch02
$ sudo rsh svlstgflr12 snap list s2idm_stg_redo02
$ sudo rsh svlstgflr12 snap list s2idm_stg_stdby2

         Insure consistency in backup datafiles, standby controlfile, and logfiles

2.2.2 Create a Control File for the Standby Database (Primary)
[SQL111]

SQL> STARTUP MOUNT;SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/tmp/s1idm.ctl’;SQL> ALTER DATABASE OPEN;

2.2.3 Prepare an Initialization Parameter File for the Standby Database (Primary)

2.2.3.1 Copy the primary database parameter file to the standby database.
[SQL12]

SQL> CREATE PFILE=’/tmp/inits2idm.ora’ FROM SPFILE;

2.2.3.2 Modify initialization parameters at the /tmp/inits1idm.ora file from section 2.2.3.1.

DB_NAME=s2idmDB_UNIQUE_NAME=s1idmLOG_ARCHIVE_CONFIG=’DG_CONFIG=(s1idm,s2idm)’

CONTROL_FILES=‘/oracle/redo_1/s1idm/control01.ctl’,’/oracle/redo_2/s1idm/control02.ctl’,’/oracle/oradata/s1idm/data1/control03.ctl’

DB_FILE_NAME_CONVERT=‘s2idm’,’s1idm’

LOG_FILE_NAME_CONVERT=

‘/orace/arch_1/s2idm/‘,’/oracle/arch_1/s1idm/,’/oracle/arch_2/s2idm/’,’/oracle/arch_2/s1idm/’

LOG_ARCHIVE_FORMAT=’s1idm_%r_%t_%s.arc’

LOG_ARCHIVE_DEST_1=

‘LOCATION=/oracle/arch1/s1idm/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=s1idm’

LOG_ARCHIVE_DEST_2=

‘SERVICE=s2idm LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=s2idm’

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

STANDBY_FILE_MANAGEMENT=AUTO

FAL_CLIENT=s1idm

FAL_SERVER=s2idm

2.2.4 Copy Files from the Primary System to the Standby System (Primary)

2.2.4.1 Cleanup spaces on Standby System.
(close all the Opsware/putty connections except one connection to standby system to prevent
accidental mistakes at section 2.2.4.1.)

sacidmdb01-stg{oracle}: rm /oracle/oradata/s1idm/data1/*sacidmdb01-stg{oracle}: rm /oracle/arch_1/s1idm/*sacidmdb01-stg{oracle}: rm /oracle/redo_1/s1idm/*

sacidmdb01-stg{oracle}: rm /oracle/stdby_1/s1idm/*

sacidmdb01-stg{oracle}: rm /oracle/oradata/s1idm/data2/*

sacidmdb01-stg{oracle}: rm /oracle/arch_2/s1idm/*

sacidmdb01-stg{oracle}: rm /oracle/redo_2/s1idm/*

sacidmdb01-stg{oracle}: rm /oracle/stdby_2/s1idm/*

2.2.4.2 Backup datafiles created in Section 2.2.1

sacidmdb01-stg{oracle}: sudo rsh sacstgflr09 ndmpd password ndmpuser
password 2VCB3KFe3pgFzCj5
sacidmdb01-stg{oracle}: sudo rsh sacstgflr10 ndmpd password ndmpuser
password AcQmiKKilGvSlgPZsvlidmdb01-stg{oracle}: sudo rsh svlstgflr11 ndmpcopy -da ndmpuser:2VCB3KFe3pgFzCj5
/vol/s2idm_stg_oradata01/.snapshot/s2idm_bef_cr_phystd_oradata01_030309/ sacstgflr09:/vol/s1idm_stg_oradata01/
svlidmdb01-stg{oracle}: sudo rsh svlstgflr12 ndmpcopy -da ndmpuser:AcQmiKKilGvSlgPZ/vol/s2idm_stg_oradata02/.snapshot/s2idm_bef_cr_phystd_oradata02_030309/ sacstgflr10:/vol/s1idm_stg_oradata02/ 

 

2.4.5.7 All the online redo logs. (This is recommended for switchover and failover operations)

svlidmdb01-stg{oracle}: sudo rsh svlstgflr11 ndmpcopy -da ndmpuser:2VCB3KFe3pgFzCj5 /vol/s2idm_stg_redo01/.snapshot/s2idm_bef_cr_phystd_redo01_030309/ sacstgflr09:/vol/s1idm_stg_redo01svlidmdb01-stg{oracle}: sudo rsh svlstgflr12 ndmpcopy -da ndmpuser:AcQmiKKilGvSlgPZ /vol/s2idm_stg_redo02/.snapshot/s2idm_bef_cr_phystd_redo02_030309/ sacstgflr10:/vol/s1idm_stg_redo02 

 

2.4.4.6 All available archived redo logs with modifying name

(On Primary)svlidmdb01-stg{oracle}: sudo rsh svlstgflr11 ndmpcopy -da ndmpuser:2VCB3KFe3pgFzCj5 /vol/s2idm_stg_arch01/.snapshot/s2idm_bef_cr_phystd_arch01_030309/ sacstgflr09:/vol/s1idm_stg_arch01svlidmdb01-stg{oracle}: sudo rsh svlstgflr12 ndmpcopy -da ndmpuser:AcQmiKKilGvSlgPZ /vol/s2idm_stg_arch02/.snapshot/s2idm_bef_cr_phystd_arch02_030309/ sacstgflr10:/vol/s1idm_stg_arch02

(on Standby)
sacidmdb01-stg{oracle}: cd /oracle/arch_2/s1idm
sacidmdb01-stg{oracle}: mv s2idm_599071098_1_36642.arc s1idm_599071098_1_36642.arc
sacidmdb01-stg{oracle}: mv s2idm_599071098_1_36643.arc s1idm_599071098_1_36643.arc

sacidmdb01-stg{oracle}: mv s2idm_599071098_1_36644.arc s1idm_599071098_1_36644.arc
sacidmdb01-stg{oracle}: mv s2idm_599071098_1_36645.arc s1idm_599071098_1_36645.arc

2.2.4.3 Standby control file created in Section 2.2.2

(On Primary)
svlidmdb010-stg{oracle}: cp /tmp/s1idm.ctl /u/oracle/IDM/<date>(on Standby)
sacidmdb01-stg{oracle}: cp –p /u/oracle/IDM/<date>/s1idm.ctl /oracle/redo_1/s1idm/control01.ctl
sacidmdb01-stg{oracle}: cp –p /u/oracle/IDM/<date>/s1idm.ctl /oracle/redo_2/s1idm/control02.ctl
sacidmdb01-stg{oracle}: cp –p /u/oracle/IDM/<date>/s1idm.ctl /oracle/oradata/s1idm/data1/control03.ctl

2.2.4.4 Initialization parameter file created in Section 2.2.3

(On Primary)
svlidmdb01-stg{oracle}: cp /tmp/inits1idm.ora /u/oracle/IDM/<date>(on Standby)
sacidmdb01-stg{oracle}: cp /u/oracle/IDM/<date>/inits1idm.ora $ORACLE_HOME/dbs

2.2.4.5 spool_create_rename_datafile_for_stndby.sql created in Section 2.1.5.2

(On Primary)
svlidmdb01-stg{oracle}: cp spool_create_rename_datafile_for_stndby.sql /u/oracle/IDM/<date>(on Standby)
sacidmdb01-stg{oracle}: cp /u/oracle/IDM/<date>/spool_create_rename_datafile_for_stndby.sql $ORACLE_HOME/dbs

2.2.5 Set Up the Environment to Support the Standby Database (Standby)

2.2.5.1 Create a password file.

sacidmdbp{oracle}: cd $ORACLE_HOME/dbssacidmdbp{oracle}: orapwd file=orapw${ORACLE_SID} password =xxxx entries=10

2.2.5.2 Configure listeners for the primary and standby databases.

* Primary database

s2idm_ls =  (DESCRIPTION_LIST =    (DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = svlidmdb01-stg.corp.netapp.com)(PORT = 7300))

)

)

)

SID_LIST_s2idm_ls =

(SID_LIST =

(SID_DESC =

(ORACLE_HOME = /oracle/product/102_s2idm/server)

(SID_NAME = s2idm)

)

)

* Standby database

s1idm_ls =  (DESCRIPTION_LIST =    (DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = sacidmdb01-stg.corp.netapp.com)(PORT = 7300))

)

)

)

SID_LIST_s1idm_ls =

(SID_LIST =

(SID_DESC =

(ORACLE_HOME = /oracle/product/102_s1idm/server)

(SID_NAME = s1idm)

)

)

2.2.5.3 Create Oracle Net service names.

They will be used by redo transport services.
The connect descriptor must also specify that a dedicated server be used.

2.2.5.4 Create a server parameter file for the standby database. (Standby)
on an idle standby database using inits2idm.ora modified at session 2.2.3.2.
[SQL13]

SQL> CREATE SPFILE FROM PFILE=’inits1idm.ora’; 

This section prevents error at section 2.2.6.1
[SQL14]

SQL> startup nomountORACLE instance started.Total System Global Area 1.6526E+10 bytes

Fixed Size                  2100104 bytes

Variable Size            2533360760 bytes

Database Buffers         1.3858E+10 bytes

Redo Buffers              132116480 bytes

SQL> alter database mount standby database;

alter database mount standby database

*

ERROR at line 1:

ORA-01103: database name ‘S2IDM’ in control file is not ‘S1IDM’

2.2.6 Start the Physical Standby Database (Standby)

2.2.6.1 Start the physical standby database in recovery mode.
[SQL15]

SQL> STARTUP NOMOUNT
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;SQL> alter system set standby_file_management=manual;SQL> @?/dbs/spool_create_rename_datafile_for_stndby.sql

SQL> alter system set standby_file_management=auto;

2.2.6.2 Put the standby database into sustained recovery mode (Standby)
                    [SQL16] 

SQL> ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE UNTIL CANCEL;ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE UNTIL CANCEL*

ERROR at line 1:

ORA-00279: change 6623342973907 generated at 02/25/2009 23:30:22 needed for

thread 1

ORA-00289: suggestion : /oracle/arch_2/s1idm/s1idm_599071098_1_36646.arc

ORA-00280: change 6623342973907 for thread 1 is in sequence #36646

ORA-00278: log file ‘/oracle/arch_2/s1idm/s1idm_599071098_1_36646.arc’ no

longer needed for this recovery

ORA-00308: cannot open archived log

‘/oracle/arch_2/s1idm/s1idm_599071098_1_36646.arc’

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3
SQL> ALTER DATABASE RECOVER CANCEL;

Database altered.

at alert_s1idm.log :Thu Feb 26 15:37:42 2009Media Recovery Log /oracle/arch_2/s1idm/s1idm_599071098_1_36642.arc

Thu Feb 26 15:37:43 2009

Media Recovery Log /oracle/arch_2/s1idm/s1idm_599071098_1_36643.arc

Thu Feb 26 15:37:43 2009

Media Recovery Log /oracle/arch_2/s1idm/s1idm_599071098_1_36644.arc

Thu Feb 26 15:38:05 2009

Media Recovery Log /oracle/arch_2/s1idm/s1idm_599071098_1_36645.arc
Thu Feb 26 15:38:05 2009

Media Recovery Log /oracle/arch_2/s1idm/s1idm_599071098_1_36646.arc

Errors with log /oracle/arch_2/s1idm/s1idm_599071098_1_36646.arc

ORA-279 signalled during: ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE UNTIL CANCEL…

Thu Feb 26 15:39:31 2009

alter database recover cancel

Thu Feb 26 15:39:32 2009

Media Recovery Canceled

Completed: alter database recover cancel

 

 

2.2.6.2 Start Redo Apply.
[SQL17]

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

at alert_s1idm1.log :

Thu Feb 26 15:51:29 2009Completed: alter database recover managed standby database disconnect from sessionFri Feb 27 01:04:51 2009

Clearing online redo logfile 1 complete

Clearing online redo logfile 2 /oracle/redo_1/s1idm/redo02a.log

Clearing online log 2 of thread 1 sequence number 36638

Clearing online redo logfile 2 complete

Clearing online redo logfile 3 /oracle/redo_1/s1idm/redo03a.log

Clearing online log 3 of thread 1 sequence number 36640
. . .
Media Recovery Waiting for thread 1 sequence 36646

2.2.6.3 Test archival operations to the physical standby database. (Primary)
[SQL18]

SQL> ALTER SYSTEM SWITCH LOGFILE;

2.2.7 Create Data Guard Configuration at OEM. (Add Standby Database)
2.2.7.1 Click “Add Standby Database” button at OEM Data Guard page.

2.2.7.1 Select “Manage an existing standby database with Data Guard broker”, then click “Continue” button

2.2.7.2 Select proper database and then click “Next” button.

2.2.7.3 The new physical standby database(s1idm on sacidmdb01-stg) will be added at Data Guard configuration automatically. It may take a time to catch up all the archive log of Primary database.
2.2.8 Verify the Physical Standby Database Is Performing Properly (Standby)

2.2.8.1 Identify the existing archived redo log files. (Standby)
[SQL19]

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME       FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;SEQUENCE# FIRST_TIM NEXT_TIME

———- ——— ———

36646 25-FEB-09 26-FEB-09

36647 26-FEB-09 26-FEB-09

36648 26-FEB-09 26-FEB-09

36649 26-FEB-09 26-FEB-09

36650 26-FEB-09 26-FEB-09

36651 26-FEB-09 26-FEB-09

36652 26-FEB-09 26-FEB-09

36653 26-FEB-09 26-FEB-09

8 rows selected.

2.2.8.2 Force a log switch to archive the current online redo log file. (Primary)
[SQL20]

SQL> ALTER SYSTEM SWITCH LOGFILE;

2.2.8.3 Verify the new redo data was archived on the standby database. (Standby)
[SQL21]

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME       FROM V$ARCHIVED_LOG
WHERE SEQUENCE# > 36646
ORDER BY SEQUENCE#;SEQUENCE# FIRST_TIM NEXT_TIME

———- ——— ———

36647 26-FEB-09 26-FEB-09

36648 26-FEB-09 26-FEB-09

36649 26-FEB-09 26-FEB-09

36650 26-FEB-09 26-FEB-09

36651 26-FEB-09 26-FEB-09

36652 26-FEB-09 26-FEB-09

36653 26-FEB-09 26-FEB-09

36654 26-FEB-09 26-FEB-09

8 rows selected.

2.2.8.4 Verify new archived redo log files were applied. (Standby)
[SQL22]

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG     ORDER BY SEQUENCE#;SEQUENCE# APP

———- —

36646 YES

36647 YES

36648 YES

36649 YES

36650 YES

36651 YES

36652 YES

36653 YES

36654 YES

9 rows selected.

2.3 (Optional) Post-Creation Steps

2.3.1 Upgrade the data protection mode

2.3.2 Enable Flashback Database

2.4 Comparison init parameter values between Primary database and Physical standby database

Primary Database(s2idm/svlidmdb01-stg) Physical Stdby(s1idm/sacidmdb01-stg)
db_file_name_convert /oracle/oradata/s2idm/, /oracle/oradata/s2idm/data2/, /oracle/oradata/s2idm/, /oracle/oradata/s2idm/data1/ /oracle/oradata/s2idm/data1, /oracle/oradata/s1idm/data1, /oracle/oradata/s2idm/data2,
/oracle/oradata/s1idm/data2
db_name s2idm s2idm
db_unique_name s2idm s1idm
fal_client (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=svlidmdb01-stg.corp.netapp.com)(PORT=7300))) (CONNECT_DATA=(SERVICE_NAME=s2idm_XPT.corp.netapp.com)(INSTANCE_NAME=s2idm)(SERVER=dedicated))) (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=sacidmdb01-stg.corp.netapp.com)(PORT=7300))) (CONNECT_DATA=(SERVICE_NAME=s1idm_XPT.corp.netapp.com)(INSTANCE_NAME=s1idm)(SERVER=dedicated)))
fal_server (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=sacidmdb01-stg.corp.netapp.com)(PORT=7300))) (CONNECT_DATA=(SERVICE_NAME=s1idm_XPT.corp.netapp.com)(INSTANCE_NAME=s1idm)(SERVER=dedicated))) (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=svlidmdb01-stg.corp.netapp.com)(PORT=7300)))                          (CONNECT_DATA=(SERVICE_NAME=s2idm_XPT.corp.netapp.com)(SERVER=dedicated)))
log_archive_config DG_CONFIG=(s1idm,s2idm) DG_CONFIG=(s2idm,s1idm)
log_archive_dest_1 location=”/oracle/arch_1/s2idm/”, valid_for=(ONLINE_LOGFILE,ALL_ROLES) location=”/oracle/arch_1/s1idm/”, valid_for=(ALL_LOGFILES,ALL_ROLES)
log_archive_dest_2 location=/oracle/arch_2/s2idm/ location=/oracle/arch_2/s1idm/
log_archive_dest_3 service=”(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=sacidmdb01-stg.corp.netapp.com)(PORT=7300)))(CONNECT_DATA=(SERVICE_NAME=s1idm_XPT.corp.netapp.com)(INSTANCE_NAME=s1idm)(SERVER=dedicated)))”,    LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1  reopen=300 db_unique_name=”s1idm” register net_timeout=180 valid_for=(online_logfile,primary_role) SERVICE=s2idm LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=s2idm
log_archive_dest_4 location=”/oracle/stdby_1/s1idm/”, valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)
log_archive_dest_state_1 ENABLE ENABLE
log_archive_dest_state_2 ENABLE ENABLE
log_archive_dest_state_3 ENABLE ENABLE
log_archive_dest_state_4 ENABLE ENABLE
log_archive_format s2idm_%r_%t_%s.arc s1idm_%r_%t_%s.arc
log_archive_max_processes 10 10
log_file_name_convert /oracle/redo_1/s2idm,/oracle/redo_1/s2idm,/oracle/redo_2/s2idm,

/oracle/redo_2/s2idm

/oracle/arch_1/s2idm/, /oracle/arch_1/s1idm/, /oracle/arch_2/s2idm/, /oracle/arch_2/s1idm/
remote_login_passwordfile EXCLUSIVE EXCLUSIVE
standby_file_management AUTO AUTO

 

3.     Stop Redo Apply on the Physical Standby Database

[SQL23] 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;Database altered. 

 

4.     

Prepare the Primary Database to Support a Logical Standby Database

4.1 Prepare the Primary Database for Role Transitions

4.1.1 Logical Standby Role Initialization Parameters (Primary)

This parameter only takes effect when the primary database is transitioned to the standby role.

LOG_ARCHIVE_DEST_4=’LOCATION=/oracle/stdby_1/s2idm/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)                   DB_UNIQUE_NAME=s2idm’LOG_ARCHIVE_DEST_STATE_4=ENABLE

[SQL24]

set linesize 150;set pagesize 60;column name format a30;

column value format a70;

select name, value

from v$parameter where name in (‘log_archive_dest_4′,’log_archive_dest_state_4′);

NAME                           VALUE

—————————— ———————————————————————-

log_archive_dest_4

log_archive_dest_state_4       ENABLE

[SQL25]

SQL> alter system set LOG_ARCHIVE_DEST_4=’LOCATION=/oracle/stdby_1/s2idm/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=s2idm’ scope=both;

4.2 Build a LogMiner Dictionary in the Redo Data (Primary)

[SQL26] 

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
PL/SQL procedure successfully completed.

 

5.     

Transition to a Logical Standby Database

5.1 Convert to a Logical Standby Database (Standby)

[SQL27]

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY s1idm; Database altered.

If the [SQL27] is failed,

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY s1idm;Database altered.
alter database recover to logical standby s1idm*

ERROR at line 1:

ORA-19953: database should not be open

SQL> SHUTDOWN IMMEIDATE;
SQL> STARTUP MOUNT;

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY s1idm;

Database altered.
at alert_s1idm.log

ALTER DATABASE RECOVER TO LOGICAL STANDBY s1idmStandby became primary SCN: 6604387783851Tue Mar 10 14:39:35 2009

Setting recovery target incarnation to 4

Tue Mar 10 14:39:35 2009

Converting standby mount to primary mount.

Tue Mar 10 14:39:35 2009

ACTIVATE STANDBY: Complete – Database mounted as primary (s1idm)

*** DBNEWID utility started ***

DBID will be changed from 3644489009 to new DBID of 3207369063 for database S2IDM

DBNAME will be changed from S2IDM to new DBNAME of S1IDM

Starting datafile conversion

Setting recovery target incarnation to 1

Datafile conversion complete

Database name changed to S1IDM.

Modify parameter file and generate a new password file before restarting.

Database ID for database S1IDM changed to 3207369063.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open with RESETLOGS option.

Succesfully changed database name and ID.

*** DBNEWID utility finished succesfully ***

Completed: alter database recover to logical standby s1idm

If a dictionary build is not successfully performed on the primary database, this command will never
complete. You can cancel the SQL statement by issuing the following SQL statement from another SQL
session.
[SQL28]

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

5.2 Create a New Password File

Because the conversion process changes the database name for the logical standby database, you
must re-create the password file.

Sacidmdb01-stg{oracle}: cd $ORACLE_HOME/dbsSacidmdb01-stg{oracle}: orapwd file=orapw${ORACLE_SID} password=xxxx entries=10

5.3 Adjust Initialization Parameters for the Logical Standby Database

5.3.1 On the logical standby database,

[SQL29]

SQL> SHUTDOWN IMMEDIATE;SQL> STARTUP MOUNT;

5.3.2 Modifying Initialization Parameters for a Logical Standby Database

LOG_ARCHIVE_DEST_1=’LOCATION=/arch1/s2idm/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=s2idm’LOG_ARCHIVE_DEST_2=’SERVICE=s1idm LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=s1idm’

LOG_ARCHIVE_DEST_3=’LOCATION=/arch2/s2idm/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)

DB_UNIQUE_NAME=s2idm’

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_DEST_STATE_3=ENABLE

[SQL30]

set linesize 150;set pagesize 60;column name format a30;

column value format a70;

select name, value

from v$parameter where name in (‘db_name’,

‘db_unique_name’,

‘log_archive_dest_1′,

‘log_archive_dest_2′,

‘log_archive_dest_3′,

‘log_archive_dest_4′,

‘log_archive_dest_state_1′,

‘log_archive_dest_state_2′,

‘log_archive_dest_state_3′,

‘log_archive_dest_state_4′

) order by name;
NAME                           VALUE

—————————— ———————————————————————-

db_name                        S1IDM

db_unique_name                 s1idm

log_archive_dest_1             location=”/oracle/arch_1/s1idm/”, valid_for=(ALL_LOGFILES,ALL_ROLES)

log_archive_dest_2             location=/oracle/arch_2/s1idm/

log_archive_dest_3             SERVICE=s2idm LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=s2idm

log_archive_dest_4             location=”/oracle/stdby_1/s1idm/”, valid_for=(STANDBY_LOGFILE,STANDBY_

ROLE)

log_archive_dest_state_1       ENABLE

log_archive_dest_state_2       ENABLE

log_archive_dest_state_3       ENABLE

log_archive_dest_state_4       ENABLE

 

6.     Open the Logical Standby Database and start SQL Apply 

SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

 

7.     Configure Data Guard on the Logical Standby database
7.1  Configure Data Guard using OEM (refer section 2.2.7)

7.2 Logical Standby DB must be changed from guard_status of “ALL” to “STANDBY”.

SQL> select guard_status from v$database;GUARD_S——-

ALL

SQL> alter database guard standby;

SQL> select guard_status from v$database;

GUARD_S

——-

STANDBY

7.3 Configure Standby Role Properties

sacidmdb01-stg{oracle}: dgmgrlDGMGRL for Linux: Version 10.2.0.2.0 – 64bit ProductionCopyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.

DGMGRL> connect /

Connected.

DGMGRL> edit database ‘s1idm’ set property ‘LsbyMaxServers’=’9′;

Property “LsbyMaxServers” updated

DGMGRL> edit database ‘s1idm’ set property ‘LsbyMaxSga’=’512′;

Property “LsbyMaxSga” updated

DGMGRL> edit database ‘s1idm’ set property ‘LsbyTxnConsistency’=’FULL’;

Property “LsbyTxnConsistency” updated

DGMGRL> edit database ‘s1idm’ set property ‘LsbyMaxEventsRecorded’=’999′;

Property “LsbyMaxEventsRecorded” updated

DGMGRL> edit database ‘s1idm’ set property ‘LsbyRecordSkipErrors’=’true’;

Property “LsbyRecordSkipErrors” updated

DGMGRL> edit database ‘s1idm’ set property ‘LsbyRecordSkipDdl’=’true’;

Property “LsbyRecordSkipDdl” updated

 

8.     

Verify the Logical Standby Database Is Performing Properly

8.1 Monitoring Log File Archival Information

8.1.1 Determine the status of redo log files. (Primary) 

SQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG; 

8.1.2 Determine the most recent archived redo log file. (Primary)

SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

8.1.3 Determine the most recent archived redo log file at each destination. (Primary)

SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#2> FROM V$ARCHIVE_DEST_STATUS3> WHERE STATUS <> ‘DEFERRED’ AND STATUS <> ‘INACTIVE’;

DESTINATION STATUS ARCHIVED_THREAD# ARCHIVED_SEQ#

—————— —— —————- ————-

/private1/prmy/lad VALID 1 947

standby1 VALID 1 947

8.1.4 Find out if archived redo log files have been received. (Primary)

SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM2> (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1)3> LOCAL WHERE

4> LOCAL.SEQUENCE# NOT IN

5> (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND

6> THREAD# = LOCAL.THREAD#);

THREAD# SEQUENCE#

——— ———

1 12

1 13

1 14

8.1.5 (Optional) Trace the progression of transmitted redo on the standby site.

8.1.6 Comparison init parameter values between Primary database and Logical standby database

Primary Database(s2idm/svlidmdb01-stg) Logical Stdby(s1idm/sacidmdb01-stg)
db_file_name_convert /oracle/oradata/s2idm/, /oracle/oradata/s2idm/data2/, /oracle/oradata/s2idm/, /oracle/oradata/s2idm/data1/ /oracle/oradata/s2idm/data1, /oracle/oradata/s1idm/data1, /oracle/oradata/s2idm/data2,
/oracle/oradata/s1idm/data2
db_name s2idm s1idm
db_unique_name s2idm s1idm
fal_client (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=svlidmdb01-stg.corp.netapp.com)(PORT=7300))) (CONNECT_DATA=(SERVICE_NAME=s2idm_XPT.corp.netapp.com)(INSTANCE_NAME=s2idm)(SERVER=dedicated))) (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=sacidmdb01-stg.corp.netapp.com)(PORT=7300))) (CONNECT_DATA=(SERVICE_NAME=s1idm_XPT.corp.netapp.com)(INSTANCE_NAME=s1idm)(SERVER=dedicated)))
fal_server (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=sacidmdb01-stg.corp.netapp.com)(PORT=7300))) (CONNECT_DATA=(SERVICE_NAME=s1idm_XPT.corp.netapp.com)(INSTANCE_NAME=s1idm)(SERVER=dedicated))) (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=svlidmdb01-stg.corp.netapp.com)(PORT=7300)))                          (CONNECT_DATA=(SERVICE_NAME=s2idm_XPT.corp.netapp.com)(SERVER=dedicated)))
log_archive_config DG_CONFIG=(s1idm,s2idm) DG_CONFIG=(s2idm,s1idm)
log_archive_dest_1 location=”/oracle/arch_1/s2idm/”, valid_for=(ONLINE_LOGFILE,ALL_ROLES) location=”/oracle/arch_1/s1idm/”, valid_for=(ALL_LOGFILES,ALL_ROLES)
log_archive_dest_2 location=/oracle/arch_2/s2idm/ location=/oracle/arch_2/s1idm/
log_archive_dest_3 service=”(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=sacidmdb01-stg.corp.netapp.com)(PORT=7300)))(CONNECT_DATA=(SERVICE_NAME=s1idm_XPT.corp.netapp.com)(INSTANCE_NAME=s1idm)(SERVER=dedicated)))”,    LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1  reopen=300 db_unique_name=”s1idm” register net_timeout=180 valid_for=(online_logfile,primary_role) SERVICE=s2idm LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=s2idm
log_archive_dest_4 LOCATION=/oracle/stdby_1/s2idm/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=s2idm location=”/oracle/stdby_1/s1idm/”, valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)
log_archive_dest_state_1 ENABLE ENABLE
log_archive_dest_state_2 ENABLE ENABLE
log_archive_dest_state_3 ENABLE ENABLE
log_archive_dest_state_4 ENABLE ENABLE
log_archive_format s2idm_%r_%t_%s.arc s1idm_%r_%t_%s.arc
log_archive_max_processes 10 10
log_file_name_convert /oracle/redo_1/s2idm, /oracle/redo_1/s2idm,/oracle/redo_2/s2idm, /oracle/redo_2/s2idm /oracle/arch_1/s2idm/, /oracle/arch_1/s1idm/, /oracle/arch_2/s2idm/, /oracle/arch_2/s1idm/
remote_login_passwordfile EXCLUSIVE EXCLUSIVE
standby_file_management AUTO AUTO

On primary database:

SQL> alter system set db_file_name_convert=
‘/oracle/oradata/s1idm/data1′,’/oracle/oradata/s2idm/data1′,
‘/oracle/oradata/s1idm/data2′,’/oracle/oradata/s2idm/data2′ scope=spfile;SQL> alter system set log_file_name_convert=
‘/oracle/arch_1/s1idm/’,’/oracle/arch_1/s2idm/’,
‘/oracle/arch_2/s1idm/’,’/oracle/arch_2/s2idm/’ scope=spfile;

 

8.2 Monitoring SQL Apply on Logical Standby Databases

8.3 Managing a Logical Standby Database

References:

  1. Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)
  2. Oracle® Data Guard Broker 10g Release 2 (10.2)
  3. NON-AUTOMATIC STANDBY DATABASE CREATION (http://www.mbjconsulting.com/oracle_links/Create_Standby_DB.html)
  4. Oracle Database 10 Data Guard Logical Standby (http://www.amcpu.org/wiki/index.php?title=Oracle_Database_10_Data_Guard_Logical_Standby#Introduction )