Environment.
Primary 2 Node Database RAC
1. Instance rac1 host rac1
2. Instance rac2 host rac2
Standby Database STBY
Instance stby1 host stby
At Standby, I have 3 disks avaiable, so I created the new Disk group FRA as identical to Primary database.
select a.name,a.state,b.name,b.label,b.path
from v$asm_disk b, v$asm_diskgroup a
where a.GROUP_NUMBER=b.GROUP_NUMBER;
NAME STATE NAME LABEL PATH
---------- ----------- ---------- ---------- ---------------
DATA MOUNTED DISK1 DISK1 ORCL:DISK1
DATA MOUNTED DISK2 DISK2 ORCL:DISK2
DATA MOUNTED DISK3 DISK3 ORCL:DISK3
SQL> select label from v$asm_disk;
LABEL
----------
DISK4
DISK5
DISK6
DISK1
DISK2
DISK3
SELECT group_number, name, compatibility, database_compatibility, ALLOCATION_UNIT_SIZE/1024/1024 "AU_Size in MB" FROM v$asm_diskgroup;
GROUP_NUMBER NAME COMPATIBILITY DATABASE_COMPATIBILI AU_Size in MB
------------ ---------- -------------------- -------------------- -------------
1 DATA 11.2.0.0.0 10.1.0.0.0 1
create diskgroup FRA EXTERNAL REDUNDANCY
DISK
'ORCL:DISK4',
'ORCL:DISK5',
'ORCL:DISK6'
ATTRIBUTE
'au_size'='1M',
'compatible.asm'='11.2.0.0.0',
'compatible.rdbms'='10.1.0.0.0'
/
Diskgroup created.
select a.name,a.state,b.name,b.label,b.path
from v$asm_disk b, v$asm_diskgroup a
where a.GROUP_NUMBER=b.GROUP_NUMBER;
NAME STATE NAME LABEL PATH
---------- ----------- ---------- ------------------------------- --------------------
DATA MOUNTED DISK1 DISK1 ORCL:DISK1
DATA MOUNTED DISK2 DISK2 ORCL:DISK2
DATA MOUNTED DISK3 DISK3 ORCL:DISK3
FRA MOUNTED DISK4 DISK4 ORCL:DISK4
FRA MOUNTED DISK5 DISK5 ORCL:DISK5
FRA MOUNTED DISK6 DISK6 ORCL:DISK6
6 rows selected.
At Standby Database server create a temporary listener, listening to standby instance stby1.
[root@stby bin]# ./srvctl add listener -l listener_standby -p "TCP:1522" -o /u01/app/oracle/product/11.2.0/dbhome_1/
Make Static entry for Standby database.
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_STANDBY=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_STANDBY)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_STANDBY=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
LISTENER_STANDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stby.xxx.com)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = stby1)
(GLOBAL_DBNAME = stby)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
)
[root@stby bin]# ./srvctl start listener -l listener_standby
[oracle@stby dbs]lsnrctl status listener
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-OCT-2014 16:13:09
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 03-OCT-2014 14:03:18
Uptime 0 days 2 hr. 9 min. 50 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/stby/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.27.31.46)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.27.31.3)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.27.31.46)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.27.31.3)(PORT=1522)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "stby" has 1 instance(s).
Instance "stby1", status UNKNOWN, has 1 handler(s) for this service...
Service "stby.xxx.com" has 1 instance(s).
Instance "stby1", status READY, has 1 handler(s) for this service...
The command completed successfully
TNSNAMES.ORA file entry at both server i.e. Primary and standby.
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
########################################################################################################
# Primary Server Entry #
########################################################################################################
RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.xxx.com)
)
)
RAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.xxx.com)
(INSTANCE_NAME = rac1)
)
)
RAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.xxx.com)
(INSTANCE_NAME = rac2)
)
)
########################################################################################################
# Standby Server Entry #
########################################################################################################
STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stby-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby.xxx.com)
(UR = A )
(INSTANCE_NAME= stby1)
)
)
-bash-3.2$ ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE stby
ora.FRA.dg
ONLINE ONLINE stby
ora.LISTENER.lsnr
ONLINE ONLINE stby
ora.LISTENER_STANDBY.lsnr
ONLINE ONLINE stby
ora.asm
ONLINE ONLINE stby Started
ora.gsd
OFFLINE OFFLINE stby
ora.net1.network
ONLINE ONLINE stby
ora.ons
ONLINE ONLINE stby
ora.registry.acfs
ONLINE ONLINE stby
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE stby
ora.cvu
1 ONLINE ONLINE stby
ora.oc4j
1 ONLINE ONLINE stby
ora.scan1.vip
1 ONLINE ONLINE stby
ora.stby.vip
1 ONLINE ONLINE stby
-- > Copy the password file at standby location and rename with the Standby database name.
--> Modify the parameter file as below.
[oracle@stby dbs]vim initstby1.ora
stby1.__db_cache_size=117440512
stby1.__java_pool_size=4194304
stby1.__large_pool_size=4194304
stby1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
stby1.__pga_aggregate_target=209715200
stby1.__sga_target=314572800
stby1.__shared_io_pool_size=0
stby1.__shared_pool_size=180355072
stby1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/stby/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='xxx.com'
*.db_name='rac'
*.db_unique_name='stby'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbyXDB)'
*.fal_client='STBY'
*.fal_server='RAC'
stby1.instance_number=1
*.log_archive_config='DG_CONFIG=(rac,stby)'
*.log_archive_dest_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stby'
*.log_archive_dest_2='SERVICE=rac LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) COMPRESSION=ENABLE DB_UNIQUE_NAME=rac'
*.log_archive_dest_state_1='ENABLE'
#*.log_archive_dest_state_2='DEFER'
*.memory_target=524288000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.resumable_timeout=0
stby1.thread=1
stby1.undo_tablespace='UNDOTBS1'
~
--> Startup standby database stby1 in nomount mode using parameter file.
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initstby1.ora';
ORACLE instance started.
Total System Global Area 523108352 bytes
Fixed Size 1346052 bytes
Variable Size 398460412 bytes
Database Buffers 117440512 bytes
Redo Buffers 5861376 bytes
--> Connecting Standby Database from Primary Database.
[oracle@rac1 ~]rman target / auxiliary sys/Oracle12@stby
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 3 15:29:31 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: RAC (DBID=2444882112)
connected to auxiliary database: RAC (not mounted)
RMAN> run
2>
3> {
4> 5> duplicate target database for standby from active database
spfile
6> 7> 8>
parameter_value_convert 'rac','stby'
9> 10>
11> set cluster_database='FALSE'
12> 13> set db_file_name_convert='+DATA/rac','+DATA/stby','/u01/','+DATA/stby'
14> 15> set log_file_name_convert='+DATA/rac','+DATA/stby'
16>
set control_files='+DATA/'
17> 18>
set db_unique_name='stby'
set fal_client='stby'
19> 20> 21> 22> 23> set fal_server='rac'
24> 25> set standby_file_management='AUTO'
26>
27> set audit_file_dest='/u01/app/oracle'
28> 29> set diagnostic_dest='/u01/app/oracle'
30>
;
31> 32>
}33>
Starting Duplicate Db at 03-OCT-14
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=140 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwrac1' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1//dbs/orapwstby1' targetfile
'/u01/app/oracle/product/11.2.0/db_1/dbs/spfilerac1.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestby1.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestby1.ora''";
}
executing Memory Script
Starting backup at 03-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=161 instance=rac1 device type=DISK
Finished backup at 03-OCT-14
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestby1.ora''
contents of Memory Script:
{
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=stbyXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stby'' comment=
'''' scope=spfile";
sql clone "alter system set remote_listener =
''stby-scan:1521'' comment=
'''' scope=spfile";
sql clone "alter system set cluster_database =
FALSE comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''+DATA/rac'', ''+DATA/stby'', ''/u01/'', ''+DATA/stby'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''+DATA/rac'', ''+DATA/stby'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''+DATA/'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''stby'' comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''stby'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''rac'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set audit_file_dest =
''/u01/app/oracle'' comment=
'''' scope=spfile";
sql clone "alter system set diagnostic_dest =
''/u01/app/oracle'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=stbyXDB)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stby'' comment= '''' scope=spfile
sql statement: alter system set remote_listener = ''stby-scan:1521'' comment= '''' scope=spfile
sql statement: alter system set cluster_database = FALSE comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''+DATA/rac'', ''+DATA/stby'', ''/u01/'', ''+DATA/stby'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''+DATA/rac'', ''+DATA/stby'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''+DATA/'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''stby'' comment= '''' scope=spfile
sql statement: alter system set fal_client = ''stby'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''rac'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set audit_file_dest = ''/u01/app/oracle'' comment= '''' scope=spfile
sql statement: alter system set diagnostic_dest = ''/u01/app/oracle'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 523108352 bytes
Fixed Size 1346052 bytes
Variable Size 314574332 bytes
Database Buffers 201326592 bytes
Redo Buffers 5861376 bytes
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/stby/controlfile/current.262.859994701'' comment=
''Set by RMAN'' scope=spfile";
backup as copy current controlfile for standby auxiliary format '+DATA/stby/controlfile/current.263.859994701';
sql clone "alter system set control_files =
''+DATA/stby/controlfile/current.263.859994701'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set control_files = ''+DATA/stby/controlfile/current.262.859994701'' comment= ''Set by RMAN'' scope=spfile
Starting backup at 03-OCT-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_rac1.f tag=TAG20141003T153009 RECID=7 STAMP=859995012
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 03-OCT-14
sql statement: alter system set control_files = ''+DATA/stby/controlfile/current.263.859994701'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 523108352 bytes
Fixed Size 1346052 bytes
Variable Size 314574332 bytes
Database Buffers 201326592 bytes
Redo Buffers 5861376 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
"+data";
switch clone tempfile all;
set newname for datafile 1 to
"+data";
set newname for datafile 2 to
"+data";
set newname for datafile 3 to
"+data";
set newname for datafile 4 to
"+data";
set newname for datafile 5 to
"+data";
set newname for datafile 6 to
"+data";
set newname for datafile 7 to
"+DATA/stbydemo01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"+data" datafile
2 auxiliary format
"+data" datafile
3 auxiliary format
"+data" datafile
4 auxiliary format
"+data" datafile
5 auxiliary format
"+data" datafile
6 auxiliary format
"+data" datafile
7 auxiliary format
"+DATA/stbydemo01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +data 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
Starting backup at 03-OCT-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/rac/datafile/undotbs1.258.856899977
output file name=+DATA/stby/datafile/undotbs1.264.859994751 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/rac/datafile/sysaux.257.856899977
output file name=+DATA/stby/datafile/sysaux.265.859995107 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/rac/datafile/system.256.856899975
output file name=+DATA/stby/datafile/system.266.859995223 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/rac/datafile/example.264.856900139
output file name=+DATA/stby/datafile/example.267.859995297 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/rac/datafile/undotbs2.265.856900533
output file name=+DATA/stby/datafile/undotbs2.268.859995333 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/rac/datafile/users.259.856899977
output file name=+DATA/stby/datafile/users.269.859995369 tag=TAG20141003T153057
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=/u01/demo01.dbf
output file name=+DATA/stbydemo01.dbf tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 03-OCT-14
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=7 STAMP=859995378 file name=+DATA/stby/datafile/system.266.859995223
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=859995378 file name=+DATA/stby/datafile/sysaux.265.859995107
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=859995379 file name=+DATA/stby/datafile/undotbs1.264.859994751
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=859995379 file name=+DATA/stby/datafile/users.269.859995369
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=859995379 file name=+DATA/stby/datafile/example.267.859995297
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=859995380 file name=+DATA/stby/datafile/undotbs2.268.859995333
datafile 7 switched to datafile copy
input datafile copy RECID=13 STAMP=859995380 file name=+DATA/stbydemo01.dbf
Finished Duplicate Db at 03-OCT-14
RMAN>
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RAC READ ONLY PHYSICAL STANDBY
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
MRP0 WAIT_FOR_LOG
9 rows selected.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RAC READ ONLY WITH APPLY PHYSICAL STANDBY
Primary 2 Node Database RAC
1. Instance rac1 host rac1
2. Instance rac2 host rac2
Standby Database STBY
Instance stby1 host stby
At Standby, I have 3 disks avaiable, so I created the new Disk group FRA as identical to Primary database.
select a.name,a.state,b.name,b.label,b.path
from v$asm_disk b, v$asm_diskgroup a
where a.GROUP_NUMBER=b.GROUP_NUMBER;
NAME STATE NAME LABEL PATH
---------- ----------- ---------- ---------- ---------------
DATA MOUNTED DISK1 DISK1 ORCL:DISK1
DATA MOUNTED DISK2 DISK2 ORCL:DISK2
DATA MOUNTED DISK3 DISK3 ORCL:DISK3
SQL> select label from v$asm_disk;
LABEL
----------
DISK4
DISK5
DISK6
DISK1
DISK2
DISK3
SELECT group_number, name, compatibility, database_compatibility, ALLOCATION_UNIT_SIZE/1024/1024 "AU_Size in MB" FROM v$asm_diskgroup;
GROUP_NUMBER NAME COMPATIBILITY DATABASE_COMPATIBILI AU_Size in MB
------------ ---------- -------------------- -------------------- -------------
1 DATA 11.2.0.0.0 10.1.0.0.0 1
create diskgroup FRA EXTERNAL REDUNDANCY
DISK
'ORCL:DISK4',
'ORCL:DISK5',
'ORCL:DISK6'
ATTRIBUTE
'au_size'='1M',
'compatible.asm'='11.2.0.0.0',
'compatible.rdbms'='10.1.0.0.0'
/
Diskgroup created.
select a.name,a.state,b.name,b.label,b.path
from v$asm_disk b, v$asm_diskgroup a
where a.GROUP_NUMBER=b.GROUP_NUMBER;
NAME STATE NAME LABEL PATH
---------- ----------- ---------- ------------------------------- --------------------
DATA MOUNTED DISK1 DISK1 ORCL:DISK1
DATA MOUNTED DISK2 DISK2 ORCL:DISK2
DATA MOUNTED DISK3 DISK3 ORCL:DISK3
FRA MOUNTED DISK4 DISK4 ORCL:DISK4
FRA MOUNTED DISK5 DISK5 ORCL:DISK5
FRA MOUNTED DISK6 DISK6 ORCL:DISK6
6 rows selected.
At Standby Database server create a temporary listener, listening to standby instance stby1.
[root@stby bin]# ./srvctl add listener -l listener_standby -p "TCP:1522" -o /u01/app/oracle/product/11.2.0/dbhome_1/
Make Static entry for Standby database.
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_STANDBY=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_STANDBY)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_STANDBY=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
LISTENER_STANDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stby.xxx.com)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = stby1)
(GLOBAL_DBNAME = stby)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
)
[root@stby bin]# ./srvctl start listener -l listener_standby
[oracle@stby dbs]lsnrctl status listener
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-OCT-2014 16:13:09
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 03-OCT-2014 14:03:18
Uptime 0 days 2 hr. 9 min. 50 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/stby/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.27.31.46)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.27.31.3)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.27.31.46)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.27.31.3)(PORT=1522)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "stby" has 1 instance(s).
Instance "stby1", status UNKNOWN, has 1 handler(s) for this service...
Service "stby.xxx.com" has 1 instance(s).
Instance "stby1", status READY, has 1 handler(s) for this service...
The command completed successfully
TNSNAMES.ORA file entry at both server i.e. Primary and standby.
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
########################################################################################################
# Primary Server Entry #
########################################################################################################
RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.xxx.com)
)
)
RAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.xxx.com)
(INSTANCE_NAME = rac1)
)
)
RAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.xxx.com)
(INSTANCE_NAME = rac2)
)
)
########################################################################################################
# Standby Server Entry #
########################################################################################################
STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stby-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby.xxx.com)
(UR = A )
(INSTANCE_NAME= stby1)
)
)
-bash-3.2$ ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE stby
ora.FRA.dg
ONLINE ONLINE stby
ora.LISTENER.lsnr
ONLINE ONLINE stby
ora.LISTENER_STANDBY.lsnr
ONLINE ONLINE stby
ora.asm
ONLINE ONLINE stby Started
ora.gsd
OFFLINE OFFLINE stby
ora.net1.network
ONLINE ONLINE stby
ora.ons
ONLINE ONLINE stby
ora.registry.acfs
ONLINE ONLINE stby
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE stby
ora.cvu
1 ONLINE ONLINE stby
ora.oc4j
1 ONLINE ONLINE stby
ora.scan1.vip
1 ONLINE ONLINE stby
ora.stby.vip
1 ONLINE ONLINE stby
-- > Copy the password file at standby location and rename with the Standby database name.
--> Modify the parameter file as below.
[oracle@stby dbs]vim initstby1.ora
stby1.__db_cache_size=117440512
stby1.__java_pool_size=4194304
stby1.__large_pool_size=4194304
stby1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
stby1.__pga_aggregate_target=209715200
stby1.__sga_target=314572800
stby1.__shared_io_pool_size=0
stby1.__shared_pool_size=180355072
stby1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/stby/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='xxx.com'
*.db_name='rac'
*.db_unique_name='stby'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbyXDB)'
*.fal_client='STBY'
*.fal_server='RAC'
stby1.instance_number=1
*.log_archive_config='DG_CONFIG=(rac,stby)'
*.log_archive_dest_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stby'
*.log_archive_dest_2='SERVICE=rac LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) COMPRESSION=ENABLE DB_UNIQUE_NAME=rac'
*.log_archive_dest_state_1='ENABLE'
#*.log_archive_dest_state_2='DEFER'
*.memory_target=524288000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.resumable_timeout=0
stby1.thread=1
stby1.undo_tablespace='UNDOTBS1'
~
--> Startup standby database stby1 in nomount mode using parameter file.
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initstby1.ora';
ORACLE instance started.
Total System Global Area 523108352 bytes
Fixed Size 1346052 bytes
Variable Size 398460412 bytes
Database Buffers 117440512 bytes
Redo Buffers 5861376 bytes
--> Connecting Standby Database from Primary Database.
[oracle@rac1 ~]rman target / auxiliary sys/Oracle12@stby
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 3 15:29:31 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: RAC (DBID=2444882112)
connected to auxiliary database: RAC (not mounted)
RMAN> run
2>
3> {
4> 5> duplicate target database for standby from active database
spfile
6> 7> 8>
parameter_value_convert 'rac','stby'
9> 10>
11> set cluster_database='FALSE'
12> 13> set db_file_name_convert='+DATA/rac','+DATA/stby','/u01/','+DATA/stby'
14> 15> set log_file_name_convert='+DATA/rac','+DATA/stby'
16>
set control_files='+DATA/'
17> 18>
set db_unique_name='stby'
set fal_client='stby'
19> 20> 21> 22> 23> set fal_server='rac'
24> 25> set standby_file_management='AUTO'
26>
27> set audit_file_dest='/u01/app/oracle'
28> 29> set diagnostic_dest='/u01/app/oracle'
30>
;
31> 32>
}33>
Starting Duplicate Db at 03-OCT-14
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=140 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwrac1' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1//dbs/orapwstby1' targetfile
'/u01/app/oracle/product/11.2.0/db_1/dbs/spfilerac1.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestby1.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestby1.ora''";
}
executing Memory Script
Starting backup at 03-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=161 instance=rac1 device type=DISK
Finished backup at 03-OCT-14
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestby1.ora''
contents of Memory Script:
{
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=stbyXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stby'' comment=
'''' scope=spfile";
sql clone "alter system set remote_listener =
''stby-scan:1521'' comment=
'''' scope=spfile";
sql clone "alter system set cluster_database =
FALSE comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''+DATA/rac'', ''+DATA/stby'', ''/u01/'', ''+DATA/stby'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''+DATA/rac'', ''+DATA/stby'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''+DATA/'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''stby'' comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''stby'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''rac'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set audit_file_dest =
''/u01/app/oracle'' comment=
'''' scope=spfile";
sql clone "alter system set diagnostic_dest =
''/u01/app/oracle'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=stbyXDB)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stby'' comment= '''' scope=spfile
sql statement: alter system set remote_listener = ''stby-scan:1521'' comment= '''' scope=spfile
sql statement: alter system set cluster_database = FALSE comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''+DATA/rac'', ''+DATA/stby'', ''/u01/'', ''+DATA/stby'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''+DATA/rac'', ''+DATA/stby'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''+DATA/'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''stby'' comment= '''' scope=spfile
sql statement: alter system set fal_client = ''stby'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''rac'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set audit_file_dest = ''/u01/app/oracle'' comment= '''' scope=spfile
sql statement: alter system set diagnostic_dest = ''/u01/app/oracle'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 523108352 bytes
Fixed Size 1346052 bytes
Variable Size 314574332 bytes
Database Buffers 201326592 bytes
Redo Buffers 5861376 bytes
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/stby/controlfile/current.262.859994701'' comment=
''Set by RMAN'' scope=spfile";
backup as copy current controlfile for standby auxiliary format '+DATA/stby/controlfile/current.263.859994701';
sql clone "alter system set control_files =
''+DATA/stby/controlfile/current.263.859994701'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set control_files = ''+DATA/stby/controlfile/current.262.859994701'' comment= ''Set by RMAN'' scope=spfile
Starting backup at 03-OCT-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_rac1.f tag=TAG20141003T153009 RECID=7 STAMP=859995012
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 03-OCT-14
sql statement: alter system set control_files = ''+DATA/stby/controlfile/current.263.859994701'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 523108352 bytes
Fixed Size 1346052 bytes
Variable Size 314574332 bytes
Database Buffers 201326592 bytes
Redo Buffers 5861376 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
"+data";
switch clone tempfile all;
set newname for datafile 1 to
"+data";
set newname for datafile 2 to
"+data";
set newname for datafile 3 to
"+data";
set newname for datafile 4 to
"+data";
set newname for datafile 5 to
"+data";
set newname for datafile 6 to
"+data";
set newname for datafile 7 to
"+DATA/stbydemo01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"+data" datafile
2 auxiliary format
"+data" datafile
3 auxiliary format
"+data" datafile
4 auxiliary format
"+data" datafile
5 auxiliary format
"+data" datafile
6 auxiliary format
"+data" datafile
7 auxiliary format
"+DATA/stbydemo01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +data 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
Starting backup at 03-OCT-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/rac/datafile/undotbs1.258.856899977
output file name=+DATA/stby/datafile/undotbs1.264.859994751 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/rac/datafile/sysaux.257.856899977
output file name=+DATA/stby/datafile/sysaux.265.859995107 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/rac/datafile/system.256.856899975
output file name=+DATA/stby/datafile/system.266.859995223 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/rac/datafile/example.264.856900139
output file name=+DATA/stby/datafile/example.267.859995297 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/rac/datafile/undotbs2.265.856900533
output file name=+DATA/stby/datafile/undotbs2.268.859995333 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/rac/datafile/users.259.856899977
output file name=+DATA/stby/datafile/users.269.859995369 tag=TAG20141003T153057
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=/u01/demo01.dbf
output file name=+DATA/stbydemo01.dbf tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 03-OCT-14
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=7 STAMP=859995378 file name=+DATA/stby/datafile/system.266.859995223
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=859995378 file name=+DATA/stby/datafile/sysaux.265.859995107
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=859995379 file name=+DATA/stby/datafile/undotbs1.264.859994751
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=859995379 file name=+DATA/stby/datafile/users.269.859995369
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=859995379 file name=+DATA/stby/datafile/example.267.859995297
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=859995380 file name=+DATA/stby/datafile/undotbs2.268.859995333
datafile 7 switched to datafile copy
input datafile copy RECID=13 STAMP=859995380 file name=+DATA/stbydemo01.dbf
Finished Duplicate Db at 03-OCT-14
RMAN>
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RAC READ ONLY PHYSICAL STANDBY
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
MRP0 WAIT_FOR_LOG
9 rows selected.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RAC READ ONLY WITH APPLY PHYSICAL STANDBY