1.Create database[IRIS] and ASM on primary
2.Install binary and create ASM instance in standby[IRISSTBDY]
3. The primary database must meet two conditions before a standby database can be created from it:
•it must be in force logging mode and
•it must be in archive log mode (also automatic archiving must be enabled and a local archiving destination must be defined
4. Enable Primary to receive redo
alter dataabse add standby logfile ‘+ASM_DATA/IRIS/stdby_redo01.log’ SIZE 200m;
alter dataabse add standby logfile ‘+ASM_DATA/IRIS/PRIMDB/stdby_redo02.log’ SIZE 200m;
alter dataabse add standby logfile ‘+ASM_DATA/IRIS/PRIMDB/stdby_redo03.log’ SIZE 200m;
5.Primary database initialization parameters
Primary Standby
IRIS IRISSTDBY
alter system set db_name=IRIS scope=spfile;
alter system set db_unique_name=IRIS scope=spfile;
alter system set log_archive_config=’dg_config=(IRIS,IRISSTDBY)’ scope=spfile;
alter system set log_archive_dest_1=’location=‘+ASM_DATA/IRIS’ valid_for=(all_logfiles,all_roles) db_unique_name=primdb’ scope=spfile;
alter system set log_archive_dest_2='service=IRISSTDBY async valid_for=(online_logfiles,primary_role) db_unique_name=IRISSTDBY' scope=spfile;
log_archive_dest_2='service=IRISSTDBY async valid_for=(online_logfiles,primary_role) db_unique_name=IRISSTDBY'
alter system set log_archive_dest_state_1=enable scope=spfile;
alter system set log_archive_dest_state_2=enable scope=spfile;
alter system set remote_login_passwordfile=exclusive scope=spfile;
alter system set log_archive_format=’IRIS_%t_%s_%r.arc’ scope=spfile;
alter system set fal_server=IRIS scope=spfile;
alter system set db_file_name_convert=’IRISSTDBY’,'IRIS’ scope=spfile;
alter system set log_file_name_convert=’+ASM_DATA/IRISSTDBY’,‘+ASM_DATA/IRIS’’ scope=spfile;
alter system set standby_file_management=auto scope=spfile;
Primary init.parameters:
IRIS.__db_cache_size=1493172224
IRIS.__java_pool_size=16777216
IRIS.__large_pool_size=16777216
IRIS.__oracle_base='/u01/oradata/oracle'#ORACLE_BASE set from environment
IRIS.__pga_aggregate_target=1375731712
IRIS.__sga_target=2063597568
IRIS.__shared_io_pool_size=0
IRIS.__shared_pool_size=503316480
IRIS.__streams_pool_size=0
*.audit_file_dest='/u01/oradata/oracle/admin/IRIS/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+ASM_DATA/IRIS/control01.ctl','+ASM_DATA/IRIS/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='IRISSTDBY','IRIS'
*.db_name='IRIS'
*.db_recovery_file_dest='+ASM_DATA'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='IRIS'
*.diagnostic_dest='/u01/oradata/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=IRISXDB)'
*.fal_client='IRIS'
*.fal_server='IRISSTDBY'
*.log_archive_config='dg_config=(IRIS,IRISSTDBY)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=IRIS'
*.log_archive_dest_2='service=IRISSTDBY async valid_for=(online_logfiles,primary_role) db_unique_name=IRISSTDBY'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='IRIS_%t_%s_%r.arc'
*.log_file_name_convert='+ASM_DATA/IRISSTDBY','+ASM_DATA/IRIS'
*.memory_target=3435134976
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
6. Create the password file for primary and standby database.
cd $ORACLE_HOME/dbs
orapwd file=orapwTESTSBY password=sree123 entries=10
7. Create pfile from spfile in pri and transfer the pfile to destination server
Check and change the high lighted parameters below:
parameters of standby
[oracle@guestvm10 dbs]$ cat initIRISSTDBY1.ora
db_cache_size=1543503872
java_pool_size=16777216
large_pool_size=16777216
#pga_aggregate_target=1375731712
#sga_target=2063597568
shared_pool_size=452984832
streams_pool_size=0
audit_file_dest='/u01/oradata/oracle/admin/IRISSTDBY/adump'
audit_trail='db'
compatible='11.2.0.0.0'
control_files='+ASM_DATA/IRISSTDBY/control01.ctl','+ASM_DATA/IRISSTDBY/control02.ctl'
DB_FILE_NAME_CONVERT='+ASM_DATA/IRIS','+ASM_DATA/IRISSTDBY'
LOG_FILE_NAME_CONVERT='+ASM_DATA/IRIS','+ASM_DATA/IRISSTDBY'
db_block_size=8192
db_domain=''
db_name='IRIS'
db_recovery_file_dest='+ASM_DATA'
db_recovery_file_dest_size=4070572032
db_unique_name='IRISSTDBY'
#diagnostic_dest='/u01/oradata/oracle'
memory_target=3435134976
open_cursors=300
processes=150
remote_login_passwordfile='EXCLUSIVE'
standby_file_management='AUTO'
undo_tablespace='UNDOTBS1'
dispatchers='(PROTOCOL=TCP) (SERVICE=IRISSTDBYXDB)'
fal_server=IRIS
fal_client=IRISSTDBY
log_archive_config='dg_config=(IRIS,IRISSTDBY)'
log_archive_dest_1='location=+ASM_DATA/IRISSTDBY'
#remote_archive_enable='true'
log_archive_dest_2='SERVICE=IRIS VALID_FOR=(ONLINE_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=IRIS'
log_archive_dest_state_2='DEFER'
log_archive_format='IRISSTDBY_%t_%s_%r.arc'
8.Listener will be started on standby also when asm instance is created.
listener in pri
[oracle@guestvm9 tmp]$ cat /u01/oradata/oracle/product/11.2.0/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/oradata/oracle/product/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))
(ADDRESS = (PROTOCOL = TCP)(HOST = ahc333.ah.nl)(PORT = 1523))
)
)
ADR_BASE_LISTENER = /u01/oradata/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
listener in standby
[oracle@guestvm10 dbs]$ cat /u01/oradata/oracle/product/11.2.0/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/oradata/oracle/product/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =IRISSTDBY)
(ORACLE_HOME = /u01/oradata/oracle/product/11.2.0/db1)
(SID_NAME = IRISSTDBY)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ahc332.ah.nl)(PORT = 1523))
)
ADR_BASE_LISTENER = /u01/oradata/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
9.Add tns entries of primary and standby then configure the TNS entries in the tnsnames.ora file.
[oracle@guestvm9 tmp]$ cat /u01/oradata/oracle/product/11.2.0/db1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/oradata/oracle/product/11.2.0/db1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
IRIS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ahc333.ah.nl)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = IRIS)
)
)
IRISSTDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ahc332.ah.nl)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = IRISSTDBY)
)
)
tnsnames in standby
[oracle@guestvm10 dbs]$ cat /u01/oradata/oracle/product/11.2.0/db1/network/admin/tnsnames.ora
IRIS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ahc333.ah.nl)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = IRIS)(UR=A)
)
)
IRISSTDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ahc332.ah.nl)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = IRISSTDBY)(UR=A)
)
)
10.start standby db in nomount
[oracle@guestvm10 ~]$ ps -ef
grep smon
oracle 23733 1 0 02:47 ? 00:00:00 asm_smon_+ASM
oracle 23939 22555 0 03:11 pts/2 00:00:00 grep smon
[oracle@guestvm10 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 22 03:11:36 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u01/oradata/oracle/product/11.2.0/db1/dbs/initIRISSTDBY1.ora';
ORACLE instance started.
Total System Global Area 3423965184 bytes
Fixed Size 2218152 bytes
Variable Size 1862272856 bytes
Database Buffers 1543503872 bytes
Redo Buffers 15970304 bytes
SQL> exit
11.[oracle@guestvm10 ~]$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Dec 22 03:49:32 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target sys/oracle@IRIS
connected to target database: IRIS (DBID=435622768)
RMAN> connect auxiliary sys/oracle@IRISSTDBY
connected to auxiliary database: IRIS (not mounted)
RMAN> show all;
RMAN configuration parameters for database with db_unique_name IRIS are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+ASM_DATA/IRIS/snapcf_IRIS.f';
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 08-NOV-11
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 '/u01/oradata/oracle/product/11.2.0/db1/dbs/orapwIRIS' auxiliary format
'/u01/oradata/oracle/product/11.2.0/db1/dbs/orapwIRISSTDBY' ;
}
executing Memory Script
Starting backup at 08-NOV-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
Finished backup at 08-NOV-11
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+ASM_DATA/IRISSTDBY/control01.ctl';
restore clone controlfile to '+ASM_DATA/IRISSTDBY/control02.ctl' from
'+ASM_DATA/IRISSTDBY/control01.ctl';
}
executing Memory Script
Starting backup at 08-NOV-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=+ASM_DATA/IRIS/snapcf_IRIS.f tag=TAG201234222T035345 RECID=2 STAMP=220529056
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 08-NOV-11
Starting restore at 08-NOV-11
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 08-NOV-11
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"+ASM_DATA/IRISSTDBY/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"+ASM_DATA/IRISSTDBY/system01.dbf";
set newname for datafile 2 to
"+ASM_DATA/IRISSTDBY/sysaux01.dbf";
set newname for datafile 3 to
"+ASM_DATA/IRISSTDBY/undotbs01.dbf";
set newname for datafile 4 to
"+ASM_DATA/IRISSTDBY/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"+ASM_DATA/IRISSTDBY/system01.dbf" datafile
2 auxiliary format
"+ASM_DATA/IRISSTDBY/sysaux01.dbf" datafile
3 auxiliary format
"+ASM_DATA/IRISSTDBY/undotbs01.dbf" datafile
4 auxiliary format
"+ASM_DATA/IRISSTDBY/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +ASM_DATA/IRISSTDBY/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 08-NOV-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+ASM_DATA/IRIS/system01.dbf
output file name=+ASM_DATA/IRISSTDBY/system01.dbf tag=TAG40345222T035987
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:04:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+ASM_DATA/IRIS/sysaux01.dbf
output file name=+ASM_DATA/IRISSTDBY/sysaux01.dbf tag=TAG40345222T035987
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:04:16
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+ASM_DATA/IRIS/undotbs01.dbf
output file name=+ASM_DATA/IRISSTDBY/undotbs01.dbf tag=TAG40345222T035987
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=+ASM_DATA/IRIS/users01.dbf
output file name=+ASM_DATA/IRISSTDBY/users01.dbf tag=TAG40345222T035987
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 08-NOV-11
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=2 STAMP=770529715 file name=+ASM_DATA/IRISSTDBY/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=770529717 file name=+ASM_DATA/IRISSTDBY/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=770529721 file name=+ASM_DATA/IRISSTDBY/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=770529726 file name=+ASM_DATA/IRISSTDBY/users01.dbf
Finished Duplicate Db at 08-NOV-11
SQL> alter database open;
Database altered.
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-16000: database open for read-only access
SQL> SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-16000: database open for read-only access
SQL> SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
37 08-NOV-11 08-NOV-11
38 08-NOV-11 08-NOV-11
39 08-NOV-11 08-NOV-11
40 08-NOV-11 08-NOV-11
41 08-NOV-11 23-DEC-11
42 09-NOV-11 24-DEC-11
43 10-NOV-11 24-DEC-11
44 10-NOV-11 25-DEC-11
45 11-NOV-11 25-DEC-11
9 rows selected.
The following steps show you how to shut down a standby database:
1. Find out if the standby database is performing managed recovery. If the MRP0 or MRP process exists, then the standby database is performing managed recovery.
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
2. Cancel managed recovery operations.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3. Shut down the standby database.
SQL> SHUTDOWN IMMEDIATE;
2.Install binary and create ASM instance in standby[IRISSTBDY]
3. The primary database must meet two conditions before a standby database can be created from it:
•it must be in force logging mode and
•it must be in archive log mode (also automatic archiving must be enabled and a local archiving destination must be defined
4. Enable Primary to receive redo
alter dataabse add standby logfile ‘+ASM_DATA/IRIS/stdby_redo01.log’ SIZE 200m;
alter dataabse add standby logfile ‘+ASM_DATA/IRIS/PRIMDB/stdby_redo02.log’ SIZE 200m;
alter dataabse add standby logfile ‘+ASM_DATA/IRIS/PRIMDB/stdby_redo03.log’ SIZE 200m;
5.Primary database initialization parameters
Primary Standby
IRIS IRISSTDBY
alter system set db_name=IRIS scope=spfile;
alter system set db_unique_name=IRIS scope=spfile;
alter system set log_archive_config=’dg_config=(IRIS,IRISSTDBY)’ scope=spfile;
alter system set log_archive_dest_1=’location=‘+ASM_DATA/IRIS’ valid_for=(all_logfiles,all_roles) db_unique_name=primdb’ scope=spfile;
alter system set log_archive_dest_2='service=IRISSTDBY async valid_for=(online_logfiles,primary_role) db_unique_name=IRISSTDBY' scope=spfile;
log_archive_dest_2='service=IRISSTDBY async valid_for=(online_logfiles,primary_role) db_unique_name=IRISSTDBY'
alter system set log_archive_dest_state_1=enable scope=spfile;
alter system set log_archive_dest_state_2=enable scope=spfile;
alter system set remote_login_passwordfile=exclusive scope=spfile;
alter system set log_archive_format=’IRIS_%t_%s_%r.arc’ scope=spfile;
alter system set fal_server=IRIS scope=spfile;
alter system set db_file_name_convert=’IRISSTDBY’,'IRIS’ scope=spfile;
alter system set log_file_name_convert=’+ASM_DATA/IRISSTDBY’,‘+ASM_DATA/IRIS’’ scope=spfile;
alter system set standby_file_management=auto scope=spfile;
Primary init.parameters:
IRIS.__db_cache_size=1493172224
IRIS.__java_pool_size=16777216
IRIS.__large_pool_size=16777216
IRIS.__oracle_base='/u01/oradata/oracle'#ORACLE_BASE set from environment
IRIS.__pga_aggregate_target=1375731712
IRIS.__sga_target=2063597568
IRIS.__shared_io_pool_size=0
IRIS.__shared_pool_size=503316480
IRIS.__streams_pool_size=0
*.audit_file_dest='/u01/oradata/oracle/admin/IRIS/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+ASM_DATA/IRIS/control01.ctl','+ASM_DATA/IRIS/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='IRISSTDBY','IRIS'
*.db_name='IRIS'
*.db_recovery_file_dest='+ASM_DATA'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='IRIS'
*.diagnostic_dest='/u01/oradata/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=IRISXDB)'
*.fal_client='IRIS'
*.fal_server='IRISSTDBY'
*.log_archive_config='dg_config=(IRIS,IRISSTDBY)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=IRIS'
*.log_archive_dest_2='service=IRISSTDBY async valid_for=(online_logfiles,primary_role) db_unique_name=IRISSTDBY'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='IRIS_%t_%s_%r.arc'
*.log_file_name_convert='+ASM_DATA/IRISSTDBY','+ASM_DATA/IRIS'
*.memory_target=3435134976
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
6. Create the password file for primary and standby database.
cd $ORACLE_HOME/dbs
orapwd file=orapwTESTSBY password=sree123 entries=10
7. Create pfile from spfile in pri and transfer the pfile to destination server
Check and change the high lighted parameters below:
parameters of standby
[oracle@guestvm10 dbs]$ cat initIRISSTDBY1.ora
db_cache_size=1543503872
java_pool_size=16777216
large_pool_size=16777216
#pga_aggregate_target=1375731712
#sga_target=2063597568
shared_pool_size=452984832
streams_pool_size=0
audit_file_dest='/u01/oradata/oracle/admin/IRISSTDBY/adump'
audit_trail='db'
compatible='11.2.0.0.0'
control_files='+ASM_DATA/IRISSTDBY/control01.ctl','+ASM_DATA/IRISSTDBY/control02.ctl'
DB_FILE_NAME_CONVERT='+ASM_DATA/IRIS','+ASM_DATA/IRISSTDBY'
LOG_FILE_NAME_CONVERT='+ASM_DATA/IRIS','+ASM_DATA/IRISSTDBY'
db_block_size=8192
db_domain=''
db_name='IRIS'
db_recovery_file_dest='+ASM_DATA'
db_recovery_file_dest_size=4070572032
db_unique_name='IRISSTDBY'
#diagnostic_dest='/u01/oradata/oracle'
memory_target=3435134976
open_cursors=300
processes=150
remote_login_passwordfile='EXCLUSIVE'
standby_file_management='AUTO'
undo_tablespace='UNDOTBS1'
dispatchers='(PROTOCOL=TCP) (SERVICE=IRISSTDBYXDB)'
fal_server=IRIS
fal_client=IRISSTDBY
log_archive_config='dg_config=(IRIS,IRISSTDBY)'
log_archive_dest_1='location=+ASM_DATA/IRISSTDBY'
#remote_archive_enable='true'
log_archive_dest_2='SERVICE=IRIS VALID_FOR=(ONLINE_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=IRIS'
log_archive_dest_state_2='DEFER'
log_archive_format='IRISSTDBY_%t_%s_%r.arc'
8.Listener will be started on standby also when asm instance is created.
listener in pri
[oracle@guestvm9 tmp]$ cat /u01/oradata/oracle/product/11.2.0/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/oradata/oracle/product/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))
(ADDRESS = (PROTOCOL = TCP)(HOST = ahc333.ah.nl)(PORT = 1523))
)
)
ADR_BASE_LISTENER = /u01/oradata/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
listener in standby
[oracle@guestvm10 dbs]$ cat /u01/oradata/oracle/product/11.2.0/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/oradata/oracle/product/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =IRISSTDBY)
(ORACLE_HOME = /u01/oradata/oracle/product/11.2.0/db1)
(SID_NAME = IRISSTDBY)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ahc332.ah.nl)(PORT = 1523))
)
ADR_BASE_LISTENER = /u01/oradata/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
9.Add tns entries of primary and standby then configure the TNS entries in the tnsnames.ora file.
[oracle@guestvm9 tmp]$ cat /u01/oradata/oracle/product/11.2.0/db1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/oradata/oracle/product/11.2.0/db1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
IRIS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ahc333.ah.nl)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = IRIS)
)
)
IRISSTDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ahc332.ah.nl)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = IRISSTDBY)
)
)
tnsnames in standby
[oracle@guestvm10 dbs]$ cat /u01/oradata/oracle/product/11.2.0/db1/network/admin/tnsnames.ora
IRIS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ahc333.ah.nl)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = IRIS)(UR=A)
)
)
IRISSTDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ahc332.ah.nl)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = IRISSTDBY)(UR=A)
)
)
10.start standby db in nomount
[oracle@guestvm10 ~]$ ps -ef
grep smon
oracle 23733 1 0 02:47 ? 00:00:00 asm_smon_+ASM
oracle 23939 22555 0 03:11 pts/2 00:00:00 grep smon
[oracle@guestvm10 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 22 03:11:36 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u01/oradata/oracle/product/11.2.0/db1/dbs/initIRISSTDBY1.ora';
ORACLE instance started.
Total System Global Area 3423965184 bytes
Fixed Size 2218152 bytes
Variable Size 1862272856 bytes
Database Buffers 1543503872 bytes
Redo Buffers 15970304 bytes
SQL> exit
11.[oracle@guestvm10 ~]$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Dec 22 03:49:32 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target sys/oracle@IRIS
connected to target database: IRIS (DBID=435622768)
RMAN> connect auxiliary sys/oracle@IRISSTDBY
connected to auxiliary database: IRIS (not mounted)
RMAN> show all;
RMAN configuration parameters for database with db_unique_name IRIS are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+ASM_DATA/IRIS/snapcf_IRIS.f';
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 08-NOV-11
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 '/u01/oradata/oracle/product/11.2.0/db1/dbs/orapwIRIS' auxiliary format
'/u01/oradata/oracle/product/11.2.0/db1/dbs/orapwIRISSTDBY' ;
}
executing Memory Script
Starting backup at 08-NOV-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
Finished backup at 08-NOV-11
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+ASM_DATA/IRISSTDBY/control01.ctl';
restore clone controlfile to '+ASM_DATA/IRISSTDBY/control02.ctl' from
'+ASM_DATA/IRISSTDBY/control01.ctl';
}
executing Memory Script
Starting backup at 08-NOV-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=+ASM_DATA/IRIS/snapcf_IRIS.f tag=TAG201234222T035345 RECID=2 STAMP=220529056
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 08-NOV-11
Starting restore at 08-NOV-11
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 08-NOV-11
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"+ASM_DATA/IRISSTDBY/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"+ASM_DATA/IRISSTDBY/system01.dbf";
set newname for datafile 2 to
"+ASM_DATA/IRISSTDBY/sysaux01.dbf";
set newname for datafile 3 to
"+ASM_DATA/IRISSTDBY/undotbs01.dbf";
set newname for datafile 4 to
"+ASM_DATA/IRISSTDBY/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"+ASM_DATA/IRISSTDBY/system01.dbf" datafile
2 auxiliary format
"+ASM_DATA/IRISSTDBY/sysaux01.dbf" datafile
3 auxiliary format
"+ASM_DATA/IRISSTDBY/undotbs01.dbf" datafile
4 auxiliary format
"+ASM_DATA/IRISSTDBY/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +ASM_DATA/IRISSTDBY/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 08-NOV-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+ASM_DATA/IRIS/system01.dbf
output file name=+ASM_DATA/IRISSTDBY/system01.dbf tag=TAG40345222T035987
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:04:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+ASM_DATA/IRIS/sysaux01.dbf
output file name=+ASM_DATA/IRISSTDBY/sysaux01.dbf tag=TAG40345222T035987
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:04:16
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+ASM_DATA/IRIS/undotbs01.dbf
output file name=+ASM_DATA/IRISSTDBY/undotbs01.dbf tag=TAG40345222T035987
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=+ASM_DATA/IRIS/users01.dbf
output file name=+ASM_DATA/IRISSTDBY/users01.dbf tag=TAG40345222T035987
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 08-NOV-11
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=2 STAMP=770529715 file name=+ASM_DATA/IRISSTDBY/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=770529717 file name=+ASM_DATA/IRISSTDBY/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=770529721 file name=+ASM_DATA/IRISSTDBY/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=770529726 file name=+ASM_DATA/IRISSTDBY/users01.dbf
Finished Duplicate Db at 08-NOV-11
SQL> alter database open;
Database altered.
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-16000: database open for read-only access
SQL> SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-16000: database open for read-only access
SQL> SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
37 08-NOV-11 08-NOV-11
38 08-NOV-11 08-NOV-11
39 08-NOV-11 08-NOV-11
40 08-NOV-11 08-NOV-11
41 08-NOV-11 23-DEC-11
42 09-NOV-11 24-DEC-11
43 10-NOV-11 24-DEC-11
44 10-NOV-11 25-DEC-11
45 11-NOV-11 25-DEC-11
9 rows selected.
The following steps show you how to shut down a standby database:
1. Find out if the standby database is performing managed recovery. If the MRP0 or MRP process exists, then the standby database is performing managed recovery.
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
2. Cancel managed recovery operations.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3. Shut down the standby database.
SQL> SHUTDOWN IMMEDIATE;
