For testing purpose I need to configure the Standby database on the same host machine where the Primary database resides in UAT server.
Primary database. db1
Standby database. db2
Hostname.oracle.xxx.com
Steps which I had followed to configure the same.
1. Configure the Listener and TNSnames.
Listener file.
[oracle@oracle dbs]$ more /u01/app/oracle/product/11.2.0/db_2/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_2/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle.xxx.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = db1)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_2)
)
(SID_DESC =
(SID_NAME = db2)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_2)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
TNSNAMES.ora
[oracle@oracle dbs]$ more /u01/app/oracle/product/11.2.0/db_2/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db1)
)
)
DB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db2)
)
)
Now Reload the Listener.
[oracle@oracle dbs]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-JUN-2013 14:12:37
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.cybage.com)(PORT=1521)))
The command completed successfully
2. Set the LOG_ARCHIVE_DEST parameter on Primary Database.
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=/u01/app/oracle/admin
/db1/arch VALID_FOR=(ALL_LOGFI
LES,ALL_ROLES)
SQL>alter system set log_archive_config='dg_config(db1,db2)';
System altered.
SQL> show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(db1,db2)
SQL> alter system set log_archive_dest_2='SERVICE=db2 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db2';
System altered.
SQL> alter system set log_archive_dest_10 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.
SQL> select * from v$dataguard_config;
DB_UNIQUE_NAME
------------------------------
db1
db2
3. Create password file.
[oracle@oracle dbs]$ orapwd file=orapwdb1 password=Oracle force=y
[oracle@oracle dbs]$ cp orapwdb1 orapwdb2
[oracle@oracle dbs]$ ls
hc_db1.dat hc_db2.dat initdb1.ora initdb2.ora lkDB1 lkDB2 orapwdb1 snapcf_db1.f spfiledb1.ora
4. Create pfile of Primary database.
SQL>create pfile from spfile;
5. [oracle@oracle dbs]$cp initdb1.ora initdb2.ora
4. With the help of pfile create the required Dir and sub-dirs.
[oracle@oracle dbs]$ mkdir -p /u01/app/oracle/admin/db2/adump /u01/app/oracle/oradata/db2/ /u01/app/oracle/fast_recovery_area/db2/
6. Make the necessary changes in pfile for standby.
[oracle@oracle dbs]$ more initdb2.ora
*.audit_file_dest='/u01/app/oracle/admin/db2/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/db2/control01.ctl','/u01/app/oracle/fast_recovery_area/db2/control02.ctl'
*.db_block_size=8192
*.db_domain='cybage.com'
*.db_name='db2'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=db2XDB)'
*.log_archive_config='dg_config=(db1,db2)'
*.log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.log_archive_dest_1='SERVICE=db2 async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db2'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_10='ENABLE'
#*.memory_target=1694498816
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_management='AUTO'
*.undo_retention=1600
*.undo_tablespace='UNDOTBS2'
7. Open the standby 'db2' in nomount mode with the help of initdb2.ora parameter file.
[oracle@oracle dbs]$ export ORACLE_SID=db2
[oracle@oracle dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 20 18:01:57 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_2/dbs/initdb2.ora';
ORACLE instance started.
Total System Global Area 175775744 bytes
Fixed Size 1343668 bytes
Variable Size 117444428 bytes
Database Buffers 50331648 bytes
Redo Buffers 6656000 bytes
SQL>
8. Connect the Auxiliary database through Primary RMAN command prompt.
[oracle@oracle dbs]$ export ORACLE_SID=db1
[oracle@oracle dbs]$ rman target / auxiliary sys/Oracle12@db2
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jun 20 18:03:57 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB1 (DBID=1406063903)
connected to auxiliary database: DB2 (not mounted)
RMAN> run
2>{
3>duplicate target database for standby from active database
4>spfile
5>parameter_value_convert 'db1','db2'
6>set db_unique_name='db2'
7>set fal_client='db2'
8>set fal_server='db1'
9>;
10>}
Starting Duplicate Db at 20-JUN-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_2/dbs/orapwdb1' auxiliary format
'/u01/app/oracle/product/11.2.0/db_2/dbs/orapwdb2' targetfile
'/u01/app/oracle/product/11.2.0/db_2/dbs/spfiledb1.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/db_2/dbs/spfiledb2.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_2/dbs/spfiledb2.ora''";
}
executing Memory Script
Starting backup at 20-JUN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=147 device type=DISK
Finished backup at 20-JUN-13
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_2/dbs/spfiledb2.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/db2/adump'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/db2/control01.ctl'', ''/u01/app/oracle/fast_recovery_area/db2/control02.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=db2XDB)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=/u01/app/oracle/admin/db2/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''db2'' comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''db2'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''db1'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/db2/adump'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/db2/control01.ctl'', ''/u01/app/oracle/fast_recovery_area/db2/control02.ctl'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=db2XDB)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''LOCATION=/u01/app/oracle/admin/db2/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''db2'' comment= '''' scope=spfile
sql statement: alter system set fal_client = ''db2'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''db1'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1690705920 bytes
Fixed Size 1345380 bytes
Variable Size 989857948 bytes
Database Buffers 687865856 bytes
Redo Buffers 11636736 bytes
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/db2/control01.ctl';
restore clone controlfile to '/u01/app/oracle/fast_recovery_area/db2/control02.ctl' from
'/u01/app/oracle/oradata/db2/control01.ctl';
}
executing Memory Script
Starting backup at 20-JUN-13
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_2/dbs/snapcf_db1.f tag=TAG20130620T181234 RECID=10 STAMP=818619160
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 20-JUN-13
Starting restore at 20-JUN-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=9 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 20-JUN-13
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/db2/temp02.dbf";
set newname for tempfile 2 to
"/u01/app/oracle/oradata/db2/temp03.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/db2/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/db2/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/db2/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/db2/undotbs02.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/db2/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/db2/sysaux01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/db2/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/db2/undotbs02.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/db2/temp02.dbf in control file
renamed tempfile 2 to /u01/app/oracle/oradata/db2/temp03.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 20-JUN-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/db1/sysaux01.dbf
output file name=/u01/app/oracle/oradata/db2/sysaux01.dbf tag=TAG20130620T181252
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:46
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/db1/system01.dbf
output file name=/u01/app/oracle/oradata/db2/system01.dbf tag=TAG20130620T181252
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/db1/undotbs02.dbf
output file name=/u01/app/oracle/oradata/db2/undotbs02.dbf tag=TAG20130620T181252
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/db1/users01.dbf
output file name=/u01/app/oracle/oradata/db2/users01.dbf tag=TAG20130620T181252
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 20-JUN-13
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=10 STAMP=818619390 file name=/u01/app/oracle/oradata/db2/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=818619391 file name=/u01/app/oracle/oradata/db2/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=818619391 file name=/u01/app/oracle/oradata/db2/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=818619391 file name=/u01/app/oracle/oradata/db2/undotbs02.dbf
Finished Duplicate Db at 20-JUN-13
RMAN>
Now the standby is configured. Lets check its status.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
DB1 MOUNTED
SQL> alter database open;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
DB1 READ ONLY
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB1 READ ONLY PHYSICAL STANDBY
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string db2
SQL> select process,sequence#,status from v$managed_standby;
PROCESS SEQUENCE# STATUS
--------- ---------- ------------
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
RFS 0 IDLE
RFS 0 IDLE
RFS 491 IDLE
RFS 0 IDLE
8 rows selected.
SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> select process,sequence#,status from v$managed_standby;
PROCESS SEQUENCE# STATUS
--------- ---------- ------------
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
RFS 0 IDLE
RFS 0 IDLE
RFS 491 IDLE
RFS 0 IDLE
MRP0 491 WAIT_FOR_LOG
9 rows selected.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB1 READ ONLY WITH APPLY PHYSICAL STANDBY
Thanks for your time. Have a good day. :)
Primary database. db1
Standby database. db2
Hostname.oracle.xxx.com
Steps which I had followed to configure the same.
1. Configure the Listener and TNSnames.
Listener file.
[oracle@oracle dbs]$ more /u01/app/oracle/product/11.2.0/db_2/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_2/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle.xxx.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = db1)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_2)
)
(SID_DESC =
(SID_NAME = db2)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_2)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
TNSNAMES.ora
[oracle@oracle dbs]$ more /u01/app/oracle/product/11.2.0/db_2/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db1)
)
)
DB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db2)
)
)
Now Reload the Listener.
[oracle@oracle dbs]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-JUN-2013 14:12:37
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.cybage.com)(PORT=1521)))
The command completed successfully
2. Set the LOG_ARCHIVE_DEST parameter on Primary Database.
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=/u01/app/oracle/admin
/db1/arch VALID_FOR=(ALL_LOGFI
LES,ALL_ROLES)
SQL>alter system set log_archive_config='dg_config(db1,db2)';
System altered.
SQL> show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(db1,db2)
SQL> alter system set log_archive_dest_2='SERVICE=db2 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db2';
System altered.
SQL> alter system set log_archive_dest_10 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.
SQL> select * from v$dataguard_config;
DB_UNIQUE_NAME
------------------------------
db1
db2
3. Create password file.
[oracle@oracle dbs]$ orapwd file=orapwdb1 password=Oracle force=y
[oracle@oracle dbs]$ cp orapwdb1 orapwdb2
[oracle@oracle dbs]$ ls
hc_db1.dat hc_db2.dat initdb1.ora initdb2.ora lkDB1 lkDB2 orapwdb1 snapcf_db1.f spfiledb1.ora
4. Create pfile of Primary database.
SQL>create pfile from spfile;
5. [oracle@oracle dbs]$cp initdb1.ora initdb2.ora
4. With the help of pfile create the required Dir and sub-dirs.
[oracle@oracle dbs]$ mkdir -p /u01/app/oracle/admin/db2/adump /u01/app/oracle/oradata/db2/ /u01/app/oracle/fast_recovery_area/db2/
6. Make the necessary changes in pfile for standby.
[oracle@oracle dbs]$ more initdb2.ora
*.audit_file_dest='/u01/app/oracle/admin/db2/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/db2/control01.ctl','/u01/app/oracle/fast_recovery_area/db2/control02.ctl'
*.db_block_size=8192
*.db_domain='cybage.com'
*.db_name='db2'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=db2XDB)'
*.log_archive_config='dg_config=(db1,db2)'
*.log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.log_archive_dest_1='SERVICE=db2 async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db2'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_10='ENABLE'
#*.memory_target=1694498816
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_management='AUTO'
*.undo_retention=1600
*.undo_tablespace='UNDOTBS2'
7. Open the standby 'db2' in nomount mode with the help of initdb2.ora parameter file.
[oracle@oracle dbs]$ export ORACLE_SID=db2
[oracle@oracle dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 20 18:01:57 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_2/dbs/initdb2.ora';
ORACLE instance started.
Total System Global Area 175775744 bytes
Fixed Size 1343668 bytes
Variable Size 117444428 bytes
Database Buffers 50331648 bytes
Redo Buffers 6656000 bytes
SQL>
8. Connect the Auxiliary database through Primary RMAN command prompt.
[oracle@oracle dbs]$ export ORACLE_SID=db1
[oracle@oracle dbs]$ rman target / auxiliary sys/Oracle12@db2
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jun 20 18:03:57 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB1 (DBID=1406063903)
connected to auxiliary database: DB2 (not mounted)
RMAN> run
2>{
3>duplicate target database for standby from active database
4>spfile
5>parameter_value_convert 'db1','db2'
6>set db_unique_name='db2'
7>set fal_client='db2'
8>set fal_server='db1'
9>;
10>}
Starting Duplicate Db at 20-JUN-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_2/dbs/orapwdb1' auxiliary format
'/u01/app/oracle/product/11.2.0/db_2/dbs/orapwdb2' targetfile
'/u01/app/oracle/product/11.2.0/db_2/dbs/spfiledb1.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/db_2/dbs/spfiledb2.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_2/dbs/spfiledb2.ora''";
}
executing Memory Script
Starting backup at 20-JUN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=147 device type=DISK
Finished backup at 20-JUN-13
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_2/dbs/spfiledb2.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/db2/adump'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/db2/control01.ctl'', ''/u01/app/oracle/fast_recovery_area/db2/control02.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=db2XDB)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=/u01/app/oracle/admin/db2/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''db2'' comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''db2'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''db1'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/db2/adump'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/db2/control01.ctl'', ''/u01/app/oracle/fast_recovery_area/db2/control02.ctl'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=db2XDB)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''LOCATION=/u01/app/oracle/admin/db2/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''db2'' comment= '''' scope=spfile
sql statement: alter system set fal_client = ''db2'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''db1'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1690705920 bytes
Fixed Size 1345380 bytes
Variable Size 989857948 bytes
Database Buffers 687865856 bytes
Redo Buffers 11636736 bytes
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/db2/control01.ctl';
restore clone controlfile to '/u01/app/oracle/fast_recovery_area/db2/control02.ctl' from
'/u01/app/oracle/oradata/db2/control01.ctl';
}
executing Memory Script
Starting backup at 20-JUN-13
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_2/dbs/snapcf_db1.f tag=TAG20130620T181234 RECID=10 STAMP=818619160
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 20-JUN-13
Starting restore at 20-JUN-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=9 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 20-JUN-13
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/db2/temp02.dbf";
set newname for tempfile 2 to
"/u01/app/oracle/oradata/db2/temp03.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/db2/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/db2/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/db2/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/db2/undotbs02.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/db2/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/db2/sysaux01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/db2/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/db2/undotbs02.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/db2/temp02.dbf in control file
renamed tempfile 2 to /u01/app/oracle/oradata/db2/temp03.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 20-JUN-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/db1/sysaux01.dbf
output file name=/u01/app/oracle/oradata/db2/sysaux01.dbf tag=TAG20130620T181252
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:46
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/db1/system01.dbf
output file name=/u01/app/oracle/oradata/db2/system01.dbf tag=TAG20130620T181252
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/db1/undotbs02.dbf
output file name=/u01/app/oracle/oradata/db2/undotbs02.dbf tag=TAG20130620T181252
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/db1/users01.dbf
output file name=/u01/app/oracle/oradata/db2/users01.dbf tag=TAG20130620T181252
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 20-JUN-13
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=10 STAMP=818619390 file name=/u01/app/oracle/oradata/db2/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=818619391 file name=/u01/app/oracle/oradata/db2/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=818619391 file name=/u01/app/oracle/oradata/db2/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=818619391 file name=/u01/app/oracle/oradata/db2/undotbs02.dbf
Finished Duplicate Db at 20-JUN-13
RMAN>
Now the standby is configured. Lets check its status.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
DB1 MOUNTED
SQL> alter database open;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
DB1 READ ONLY
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB1 READ ONLY PHYSICAL STANDBY
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string db2
SQL> select process,sequence#,status from v$managed_standby;
PROCESS SEQUENCE# STATUS
--------- ---------- ------------
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
RFS 0 IDLE
RFS 0 IDLE
RFS 491 IDLE
RFS 0 IDLE
8 rows selected.
SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> select process,sequence#,status from v$managed_standby;
PROCESS SEQUENCE# STATUS
--------- ---------- ------------
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
RFS 0 IDLE
RFS 0 IDLE
RFS 491 IDLE
RFS 0 IDLE
MRP0 491 WAIT_FOR_LOG
9 rows selected.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB1 READ ONLY WITH APPLY PHYSICAL STANDBY
Thanks for your time. Have a good day. :)
No comments:
Post a Comment