Post list the steps to configure a data guard environment with 11gR2 (11.2.0.3)
standalone server using ASM and role separation. Following table list the names and conventions used during the setup.
| Item |
On Primary |
On Standby |
|---|
| Host name | rac1 | rac2 |
| Database Name | ent11g2 | ent11g2s |
| Diskgroup Names | DATA
FLASH
APPS |
DG_DATA
DG_FLASH
APPS |
1. Create the relevant *dump directories in the standby server
cd $ORACLE_BASE/admin
mkdir ent11g2s
cd ent11g2s
mkdir adump dpdump hdump pfile
2. It is assumed primary db is in archive log mode if not put the db in archive log mode. This is required since duplication is done using active database.
3. Enable force logging on the primary database
alter database force logging;
4. Create standby log files for each thread on primary db. These should be same size as the online redo log. There should be at least one more redo log group per thread than the online redo log groups.
SQL> alter database add standby logfile thread 1 size 52428800;
5. Add static listener entries on standby and primary $GI_HOME's listener.ora file. This must be done as grid user. On primary add the following static listener entry to listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ent11g2.domain.net)
(SID_NAME = ent11g2)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/dbhome_1)
)
)
On standby
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ent11g2s.domain.net)
(SID_NAME = ent11g2s)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/dbhome_1)
)
)
Restart both primary and standby listeners for static listener entries to take effect. After the listener restart the listener status would show on primary
...
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "ent11g2.domain.net" has 2 instance(s).
Instance "ent11g2", status UNKNOWN, has 1 handler(s) for this service...
Instance "ent11g2", status READY, has 1 handler(s) for this service...
Service "ent11g2XDB.domain.net" has 1 instance(s).
Instance "ent11g2", status READY, has 1 handler(s) for this service...
The command completed successfully
On standby
...
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "ent11g2s.domain.net" has 1 instance(s).
Instance "ent11g2s", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
6. As oracle user in ORACLE_HOME'S create tns entries. On primary server's oracle home create a tns entry pointing to the standby instance
ENT11G2STNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ent11g2s.domain.net)
)
)
On standby's tnsnames.ora file add an entry pointing to the primary instance
ENT11G2TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ent11g2.domain.net)
)
)
7. Create a backup pfile from the primary spfile
SQL> create pfile='/home/oracle/pfile.ora' from spfile;
8. Modify the initialization parameters on the primary db. Remote archive dest state is set to defer, enable it before running the standby duplication. LGWR with ASYNC and NOAFFIRM is used as the redo transport this could be changed to suite the protection mode.
alter system set log_archive_config='dg_config=(ent11g2,ent11g2s)' scope=both ;
alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ent11g2' scope=both;
alter system set log_archive_dest_2='service=ENT11G2STNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=ent11g2s' scope=both;
alter system set log_archive_dest_state_2='defer' scope=both;
alter system set log_archive_dest_state_1='enable' scope=both;
alter system set fal_server='ENT11G2STNS' scope=both;
alter system set fal_client='ENT11G2TNS' scope=both ;
alter system set log_archive_max_processes=10 scope=both;
alter system set db_file_name_convert='+DG_DATA/ent11g2s','+DATA/ent11g2','+APPS/ent11g2s','+APPS/ent11g2' scope=spfile;
alter system set log_file_name_convert='+DG_FLASH/ent11g2s','+FLASH/ent11g2','+DG_DATA/ent11g2s','+DATA/ent11g2' scope=spfile;
alter system set standby_file_management='AUTO' scope=both;
Restart the database server for the changes to take effect
srvctl stop database -d ent11g2
srvctl start database -d ent11g2
9. Copy password to standby and rename with the instance name
scp orapwent11g2 rac2:/opt/app/oracle/product/11.2.0/dbhome_1/dbs/orapwent11g2s
10. Create pfile on standby with db_name parameter only
export ORACLE_SID=ent11g2s
cat initent11g2s.ora
db_name=ent11g2s
10. Start the standby instance in nomount mode
SQL> startup nomount
ORACLE instance started.
Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
11. Enable the remote log archive destination on primary
alter system set log_archive_dest_state_2='enable' SCOPE=both sid='*';
12. Run the duplicate from active database command from primary. Local listner is set automatically and will be reset during duplication process.
duplicate target database for standby from active database
spfile
parameter_value_convert 'ent11g2','ent11g2s','ENT11G2','ENT11G2S'
set db_unique_name='ent11g2s'
set db_file_name_convert='+DATA/ent11g2','+DG_DATA/ent11g2s','+APPS/ent11g2','+APPS/ent11g2s'
set log_file_name_convert='+FLASH/ent11g2','+DG_FLASH/ent11g2s','+DATA/ent11g2','+DG_DATA/ent11g2s'
set control_files='+DG_DATA','+DG_FLASH'
set db_create_file_dest='+DG_DATA'
set db_recovery_file_dest='+DG_FLASH'
set log_archive_max_processes='10'
set fal_client='ENT11G2STNS'
set fal_server='ENT11G2TNS'
reset local_listener
set log_archive_dest_2='service=ENT11G2TNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=ent11g2'
set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ent11g2s';
The complete output of the duplication process is given below
rman target / auxiliary sys/ent11g2db@ent11g2stns
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 4 16:56:36 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ENT11G2 (DBID=2557133120)
connected to auxiliary database: ENT11G2S (not mounted)
RMAN> duplicate target database for standby from active database
2> spfile
3> parameter_value_convert 'ent11g2','ent11g2s','ENT11G2','ENT11G2S'
4> set db_unique_name='ent11g2s'
5> set db_file_name_convert='+DATA/ent11g2','+DG_DATA/ent11g2s','+APPS/ent11g2','+APPS/ent11g2s'
6> set log_file_name_convert='+FLASH/ent11g2','+DG_FLASH/ent11g2s','+DATA/ent11g2','+DG_DATA/ent11g2s'
7> set control_files='+DG_DATA','+DG_FLASH'
8> set db_create_file_dest='+DG_DATA'
9> set db_recovery_file_dest='+DG_FLASH'
10> set log_archive_max_processes='10'
11> set fal_client='ENT11G2STNS'
12> set fal_server='ENT11G2TNS'
13> reset local_listener
14> set log_archive_dest_2='service=ENT11G2TNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=ent11g2'
15> set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ent11g2s';
Starting Duplicate Db at 04-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/opt/app/oracle/product/11.2.0/dbhome_1/dbs/orapwent11g2' auxiliary format
'/opt/app/oracle/product/11.2.0/dbhome_1/dbs/orapwent11g2s' targetfile
'+DATA/ent11g2/spfileent11g2.ora' auxiliary format
'/opt/app/oracle/product/11.2.0/dbhome_1/dbs/spfileent11g2s.ora' ;
sql clone "alter system set spfile= ''/opt/app/oracle/product/11.2.0/dbhome_1/dbs/spfileent11g2s.ora''";
}
executing Memory Script
Starting backup at 04-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Finished backup at 04-JUL-12
sql statement: alter system set spfile= ''/opt/app/oracle/product/11.2.0/dbhome_1/dbs/spfileent11g2s.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/opt/app/oracle/admin/ent11g2s/adump'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=ent11g2sXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''ent11g2s'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''+DATA/ent11g2'', ''+DG_DATA/ent11g2s'', ''+APPS/ent11g2'', ''+APPS/ent11g2s'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''+FLASH/ent11g2'', ''+DG_FLASH/ent11g2s'', ''+DATA/ent11g2'', ''+DG_DATA/ent11g2s'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''+DG_DATA'', ''+DG_FLASH'' comment=
'''' scope=spfile";
sql clone "alter system set db_create_file_dest =
''+DG_DATA'' comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest =
''+DG_FLASH'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes =
10 comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''ENT11G2STNS'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''ENT11G2TNS'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''service=ENT11G2TNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=ent11g2'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ent11g2s'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ''/opt/app/oracle/admin/ent11g2s/adump'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=ent11g2sXDB)'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''ent11g2s'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''+DATA/ent11g2'', ''+DG_DATA/ent11g2s'', ''+APPS/ent11g2'', ''+APPS/ent11g2s'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''+FLASH/ent11g2'', ''+DG_FLASH/ent11g2s'', ''+DATA/ent11g2'', ''+DG_DATA/ent11g2s'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''+DG_DATA'', ''+DG_FLASH'' comment= '''' scope=spfile
sql statement: alter system set db_create_file_dest = ''+DG_DATA'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest = ''+DG_FLASH'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 10 comment= '''' scope=spfile
sql statement: alter system set fal_client = ''ENT11G2STNS'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''ENT11G2TNS'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=ENT11G2TNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=ent11g2'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ent11g2s'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 230690136 bytes
Database Buffers 599785472 bytes
Redo Buffers 6574080 bytes
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DG_DATA/ent11g2s/controlfile/current.268.787763727'', ''+DG_FLASH/ent11g2s/controlfile/current.259.787763731'' comment=
''Set by RMAN'' scope=spfile";
backup as copy current controlfile for standby auxiliary format '+DG_DATA/ent11g2s/controlfile/current.267.787763731';
restore clone controlfile to '+DG_FLASH/ent11g2s/controlfile/current.258.787763731' from
'+DG_DATA/ent11g2s/controlfile/current.267.787763731';
sql clone "alter system set control_files =
''+DG_DATA/ent11g2s/controlfile/current.267.787763731'', ''+DG_FLASH/ent11g2s/controlfile/current.258.787763731'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set control_files = ''+DG_DATA/ent11g2s/controlfile/current.268.787763727'', ''+DG_FLASH/ent11g2s/controlfile/current.259.787763731'' comment= ''Set by RMAN'' scope=spfile
Starting backup at 04-JUL-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/opt/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ent11g2.f tag=TAG20120704T143626 RECID=5 STAMP=787761386
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 04-JUL-12
Starting restore at 04-JUL-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=138 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 04-JUL-12
sql statement: alter system set control_files = ''+DG_DATA/ent11g2s/controlfile/current.267.787763731'', ''+DG_FLASH/ent11g2s/controlfile/current.258.787763731'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 230690136 bytes
Database Buffers 599785472 bytes
Redo Buffers 6574080 bytes
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for tempfile 1 to
"+dg_data";
switch clone tempfile all;
set newname for datafile 1 to
"+dg_data";
set newname for datafile 2 to
"+dg_data";
set newname for datafile 3 to
"+dg_data";
set newname for datafile 4 to
"+dg_data";
set newname for datafile 5 to
"+apps";
backup as copy reuse
datafile 1 auxiliary format
"+dg_data" datafile
2 auxiliary format
"+dg_data" datafile
3 auxiliary format
"+dg_data" datafile
4 auxiliary format
"+dg_data" datafile
5 auxiliary format
"+apps" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +dg_data in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 04-JUL-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/ent11g2/datafile/system.256.787750113
output file name=+DG_DATA/ent11g2s/datafile/system.260.787763751 tag=TAG20120704T143647
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/ent11g2/datafile/sysaux.257.787750113
output file name=+DG_DATA/ent11g2s/datafile/sysaux.261.787763827 tag=TAG20120704T143647
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=+DATA/ent11g2/datafile/undotbs1.258.787750113
output file name=+DG_DATA/ent11g2s/datafile/undotbs1.262.787763883 tag=TAG20120704T143647
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+APPS/ent11g2/datafile/myapps.256.787760733
output file name=+APPS/ent11g2s/datafile/myapps.256.787763897 tag=TAG20120704T143647
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=+DATA/ent11g2/datafile/users.259.787750113
output file name=+DG_DATA/ent11g2s/datafile/users.263.787763905 tag=TAG20120704T143647
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 04-JUL-12
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=787763913 file name=+DG_DATA/ent11g2s/datafile/system.260.787763751
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=787763913 file name=+DG_DATA/ent11g2s/datafile/sysaux.261.787763827
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=787763913 file name=+DG_DATA/ent11g2s/datafile/undotbs1.262.787763883
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=787763913 file name=+DG_DATA/ent11g2s/datafile/users.263.787763905
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=787763913 file name=+APPS/ent11g2s/datafile/myapps.256.787763897
Finished Duplicate Db at 04-JUL-12
13. Once the duplication for standby is completed start the log apply process on standby
SQL> alter database recover managed standby database using current logfile disconnect;
and monitor the standby instance's alert log for recover process in action
alter database recover managed standby database using current logfile disconnect
Attempt to start background Managed Standby Recovery process (ent11g2s)
Wed Jul 04 17:07:05 2012
MRP0 started with pid=48, OS id=5459
MRP0: Background Managed Standby Recovery process started (ent11g2s)
started logmerger process
Wed Jul 04 17:07:11 2012
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 2 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +DG_FLASH/ent11g2s/archivelog/2012_07_04/thread_1_seq_36.265.787770401
Media Recovery Log +DG_FLASH/ent11g2s/archivelog/2012_07_04/thread_1_seq_37.264.787770403
Media Recovery Log +DG_FLASH/ent11g2s/archivelog/2012_07_04/thread_1_seq_38.256.787770405
Media Recovery Waiting for thread 1 sequence 39 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 39 Reading mem 0
Mem# 0: +DG_DATA/ent11g2s/onlinelog/group_4.258.787770345
Mem# 1: +DG_FLASH/ent11g2s/onlinelog/group_4.278.787770345
Completed: alter database recover managed standby database using current logfile disconnect
Do a log switch on primary and see archive log application on standby
QL> select sequence#,thread#,applied from v$archived_log;
SEQUENCE# THREAD# APPLIED
---------- ---------- ---------
36 1 YES
37 1 YES
38 1 IN-MEMORY
14. This conclude the data guard configuration. Next step is to bring the standby instance under oracle restart (high availability service) similar to primary db
srvctl config database -d ent11g2
Database unique name: ent11g2
Database name: ent11g2
Oracle home: /opt/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/ent11g2/spfileent11g2.ora
Domain: domain.net
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: ent11g2
Disk Groups: DATA,FLASH,APPS
Services:
Create a pfile from current spfile used by standby. At the end of the duplication standby's spfile is in the $ORACLE_HOME/dbs, this could be confirmed from the alert log which will have the following entry
Using parameter settings in server-side spfile /opt/app/oracle/product/11.2.0/dbhome_1/dbs/spfileent11g2s.ora
Create the pfile from spfile
SQL> create pfile='/home/oracle/pfileinitent11g2s.ora' from spfile;
File created.
Stop the log apply and shutdown the instance
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shutdown immediate;
Edit the pfile and remove any references to primary instance. The final pfile would only have standby entries
ent11g2s.__db_cache_size=599785472
ent11g2s.__java_pool_size=4194304
ent11g2s.__large_pool_size=4194304
ent11g2s.__pga_aggregate_target=419430400
ent11g2s.__sga_target=843055104
ent11g2s.__shared_io_pool_size=0
ent11g2s.__shared_pool_size=222298112
ent11g2s.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/ent11g2s/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DG_DATA/ent11g2s/controlfile/current.274.787770165','+DG_FLASH/ent11g2s/controlfile/current.282.787770167'#Set by RMAN
*.db_block_size=8192
*.db_create_file_dest='+DG_DATA'
*.db_domain='domain.net'
*.db_file_name_convert='+DATA/ent11g2','+DG_DATA/ent11g2s','+APPS/ent11g2','+APPS/ent11g2s'
*.db_name='ent11g2'
*.db_recovery_file_dest_size=9437184000
*.db_recovery_file_dest='+DG_FLASH'
*.db_unique_name='ent11g2s'
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ent11g2sXDB)'
*.fal_client='ENT11G2STNS'
*.fal_server='ENT11G2TNS'
*.log_archive_config='dg_config=(ent11g2,ent11g2s)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ent11g2s'
*.log_archive_dest_2='service=ENT11G2TNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_u
nique_name=ent11g2'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=10
*.log_file_name_convert='+FLASH/ent11g2','+DG_FLASH/ent11g2s','+DATA/ent11g2','+DG_DATA/ent11g2s'
*.open_cursors=300
*.pga_aggregate_target=419430400
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=840957952
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
Start the standby instance with the modified pfile
SQL> startup mount pfile='/home/oracle/pfileinitent11g2s.ora';
Create a spfile from the pfile give diskgroup as the spfile location
SQL> create spfile='+DG_DATA/ent11g2s/spfileent11g2s.ora' from pfile='/home/oracle/pfileinitent11g2s.ora';
SQL> shutdown immediate;
in $ORACLE_HOME/dbs create new pfile with following line
vi initent11g2s.ora
spfile='+DG_DATA/ent11g2s/spfileent11g2s.ora'
Remove the existing spfile created part of the duplication. Bring the standby instance under the control of oracle restart by running the following command as the oracle user. Startup mode is set to mount to prevent opening in read only mode which requires active data guard license.
$GI_HOME/bin/srvctl add database -d ent11g2s -o $ORACLE_HOME -m local -p "+DG_DATA/ent11g2s/spfileent11g2s.ora" -r physical_standby -s mount
If the database was running when the above command was executed then stop it using sqlplus and then start with
srvctl start database -d ent11g2s
srvctl config database -d ent11g2s
Database unique name: ent11g2s
Database name:
Oracle home: /opt/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DG_DATA/ent11g2s/spfileent11g2s.ora
Domain: domain.net
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Database instance: ent11g2s
Disk Groups: DG_DATA,DG_FLASH,APPS
Services:
This will establish all the dependencies of the database and following lines will disappear from the alert log. Before instance is brought under the oracle restart
SUCCESS: diskgroup DG_FLASH was mounted
ERROR: failed to establish dependency between database ent11g2s and diskgroup resource ora.DG_DATA.dg
ERROR: failed to establish dependency between database ent11g2s and diskgroup resource ora.DG_FLASH.dg
ARCH: STARTING ARCH PROCESSES
After
ALTER DATABASE MOUNT /* db agent *//* {0:0:307} */
NOTE: Loaded library: /opt/oracle/extapi/64/asm/orcl/1/libasm.so
NOTE: Loaded library: System
SUCCESS: diskgroup DG_DATA was mounted
SUCCESS: diskgroup DG_FLASH was mounted
ARCH: STARTING ARCH PROCESSES
Wed Jul 04 17:27:58 2012
ARC0 started with pid=26, OS id=6206
ARC0: Archival started
This conclude the configuration and setup of the standalone role separated ASM data guard configuration.
Alternately a pfile for the standby could be created with all the necessary parameter entries and use that to start the duplicate in nomount state. Then when running the duplicate command and omit the spfile clause.
In this scenario pfile created on step 10 would have all the necessary parameters, this could be created by modifying a pfile created from the primary.
more initent11g2s.ora
*.audit_file_dest='/opt/app/oracle/admin/ent11g2s/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DG_DATA','+DG_FLASH'
*.db_block_size=8192
*.db_create_file_dest='+DG_DATA'
*.db_domain='domain.net'
*.db_file_name_convert='+DATA/ent11g2','+DG_DATA/ent11g2s','+APPS/ent11g2','+APPS/ent11g2s'
*.db_name='ent11g2'
*.db_unique_name='ent11g2s'
*.db_recovery_file_dest='+DG_FLASH'
*.db_recovery_file_dest_size=9437184000
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ent11g2sXDB)'
*.fal_client='ENT11G2STNS'
*.fal_server='ENT11G2TNS'
*.log_archive_config='dg_config=(ent11g2,ent11g2s)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ent11g2s'
*.log_archive_dest_2='service=ENT11G2TNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_u
nique_name=ent11g2'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=10
*.log_file_name_convert='+FLASH/ent11g2','+DG_FLASH/ent11g2s','+DATA/ent11g2','+DG_DATA/ent11g2s'
*.open_cursors=300
*.pga_aggregate_target=419430400
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=840957952
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
Then execute the duplicate command as follows. In this case the auxiliary database name will be same as the primary name unlike in the method mentioned above (step 12)
rman target / auxiliary sys/ent11g2db@ent11g2stns
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 4 15:26:35 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ENT11G2 (DBID=2557133120)
connected to auxiliary database: ENT11G2 (not mounted)
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 04-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/opt/app/oracle/product/11.2.0/dbhome_1/dbs/orapwent11g2' auxiliary format
'/opt/app/oracle/product/11.2.0/dbhome_1/dbs/orapwent11g2s' ;
}
executing Memory Script
Starting backup at 04-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=151 device type=DISK
Finished backup at 04-JUL-12
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DG_DATA/ent11g2s/controlfile/current.267.787766873';
restore clone controlfile to '+DG_FLASH/ent11g2s/controlfile/current.258.787766875' from
'+DG_DATA/ent11g2s/controlfile/current.267.787766873';
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''+DG_DATA/ent11g2s/controlfile/current.267.787766873'', ''+DG_FLASH/ent11g2s/controlfile/current.258.787766875'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Starting backup at 04-JUL-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/opt/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ent11g2.f tag=TAG20120704T152853 RECID=6 STAMP=787764533
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 04-JUL-12
Starting restore at 04-JUL-12
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 04-JUL-12
sql statement: create spfile from memory
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 234884440 bytes
Database Buffers 595591168 bytes
Redo Buffers 6574080 bytes
sql statement: alter system set control_files = ''+DG_DATA/ent11g2s/controlfile/current.267.787766873'', ''+DG_FLASH/ent11g2s/controlfile/current.258.787766875'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 234884440 bytes
Database Buffers 595591168 bytes
Redo Buffers 6574080 bytes
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for tempfile 1 to
"+dg_data";
switch clone tempfile all;
set newname for datafile 1 to
"+dg_data";
set newname for datafile 2 to
"+dg_data";
set newname for datafile 3 to
"+dg_data";
set newname for datafile 4 to
"+dg_data";
set newname for datafile 5 to
"+apps";
backup as copy reuse
datafile 1 auxiliary format
"+dg_data" datafile
2 auxiliary format
"+dg_data" datafile
3 auxiliary format
"+dg_data" datafile
4 auxiliary format
"+dg_data" datafile
5 auxiliary format
"+apps" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +dg_data in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 04-JUL-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/ent11g2/datafile/system.256.787750113
output file name=+DG_DATA/ent11g2s/datafile/system.263.787766913 tag=TAG20120704T152928
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/ent11g2/datafile/sysaux.257.787750113
output file name=+DG_DATA/ent11g2s/datafile/sysaux.262.787766989 tag=TAG20120704T152928
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=+DATA/ent11g2/datafile/undotbs1.258.787750113
output file name=+DG_DATA/ent11g2s/datafile/undotbs1.261.787767045 tag=TAG20120704T152928
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+APPS/ent11g2/datafile/myapps.256.787760733
output file name=+APPS/ent11g2s/datafile/myapps.256.787767061 tag=TAG20120704T152928
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/ent11g2/datafile/users.259.787750113
output file name=+DG_DATA/ent11g2s/datafile/users.260.787767063 tag=TAG20120704T152928
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 04-JUL-12
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=787767066 file name=+DG_DATA/ent11g2s/datafile/system.263.787766913
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=787767066 file name=+DG_DATA/ent11g2s/datafile/sysaux.262.787766989
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=787767066 file name=+DG_DATA/ent11g2s/datafile/undotbs1.261.787767045
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=787767066 file name=+DG_DATA/ent11g2s/datafile/users.260.787767063
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=787767066 file name=+APPS/ent11g2s/datafile/myapps.256.787767061
Finished Duplicate Db at 04-JUL-12
Reset of the steps are same as above to bring the standby under oracle restart's control.
Some other related notes
When the oracle software is installed on the standby node the permission of the Oracle binary is as follows.
ls -l /opt/app/oracle/product/11.2.0/grid/bin/oracle # GI HOME
-rwsr-s--x 1 grid oinstall 203972955 Jul 4 10:05 /opt/app/oracle/product/11.2.0/grid/bin/oracle
ls -l /opt/app/oracle/product/11.2.0/dbhome_1/bin/oracle # ORACLE HOME
-rwsr-s--x 1 oracle oinstall 232399473 Jul 4 12:29 /opt/app/oracle/product/11.2.0/dbhome_1/bin/oracle
See the post related to oracle binary permissions. On primary the permission of the oracle binary was "oracle:asmadmin". The permission was left as it is (oracle:oinstall) and this did not cause any problem during the standby creation and at the end of the duplication the oracle binary permission was changed to "oracle:asmadmin"
ls -l /opt/app/oracle/product/11.2.0/dbhome_1/bin/oracle
-rwsr-s--x 1 oracle asmadmin 232399473 Jul 4 12:29 /opt/app/oracle/product/11.2.0/dbhome_1/bin/oracle
s -l /opt/app/oracle/product/11.2.0/grid/bin/oracle
-rwsr-s--x 1 grid oinstall 203972955 Jul 4 10:05 /opt/app/oracle/product/11.2.0/grid/bin/oracle
It was confirmed oracle binary permissions are in a smilar state after the installation and that when the database is created with dbca the permission gets changed to "oracle:asmadmin".
It is normal to see message similar to below in the standby alert log when the duplication process and this does not mean a problem
Errors in file /opt/app/oracle/diag/rdbms/ent11g2s/ent11g2s/trace/ent11g2s_lgwr_27303.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DG_FLASH/ent11g2s/onlinelog/group_1.257.787750219'
ORA-17503: ksfdopn:2 Failed to open file +DG_FLASH/ent11g2s/onlinelog/group_1.257.787750219
ORA-15012: ASM file '+DG_FLASH/ent11g2s/onlinelog/group_1.257.787750219' does not exist
ORA-00312: online log 1 thread 1: '+DG_DATA/ent11g2s/onlinelog/group_1.261.787750215'
ORA-17503: ksfdopn:2 Failed to open file +DG_DATA/ent11g2s/onlinelog/group_1.261.787750215
ORA-15012: ASM file '+DG_DATA/ent11g2s/onlinelog/group_1.261.787750215' does not exist
Errors in file /opt/app/oracle/diag/rdbms/ent11g2s/ent11g2s/trace/ent11g2s_lgwr_27303.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DG_FLASH/ent11g2s/onlinelog/group_1.257.787750219'
ORA-17503: ksfdopn:2 Failed to open file +DG_FLASH/ent11g2s/onlinelog/group_1.257.787750219
ORA-15012: ASM file '+DG_FLASH/ent11g2s/onlinelog/group_1.257.787750219' does not exist
ORA-00312: online log 1 thread 1: '+DG_DATA/ent11g2s/onlinelog/group_1.261.787750215'
ORA-17503: ksfdopn:2 Failed to open file +DG_DATA/ent11g2s/onlinelog/group_1.261.787750215
ORA-15012: ASM file '+DG_DATA/ent11g2s/onlinelog/group_1.261.787750215' does not exist
The alert logs will be created without an issue.
Useful metalink notes
RMAN Duplicate Database From RAC ASM To RAC ASM
[ID 461479.1]
Performing duplicate database with ASM/OMF/RMAN
[ID 340848.1]
ORA-7445 [kcrf_val_log_bsz] Creating Physical Standby With Rman Duplicate From Active
[ID 1320757.1]
Dataguard DB/LOG FILE NAME CONVERT has been set but files are created in a different directory
[ID 1348512.1]
OERR:RMAN 5529 WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only
[ID 1367794.1]
Related Posts
RAC to Single Instance Physical Standby
11gR2 RAC to RAC Data Guard
