In drpdb1 as root:
###############################
Copy password file:
######## dcpdb1 User root: #################
# whoami
root
# pwd
/u02/app/oracle/product/11.2.0/dbhome_1/dbs
#
# scp orapwdcpdb1 drpdb1:/u02/app/oracle/product/11.2.0/dbhome_1/dbs
Warning: the RSA host key for 'drpdb1' differs from the key for the IP address '172.31.200.1'
Offending key for IP in /.ssh/known_hosts:6
Matching host key in /.ssh/known_hosts:9
Are you sure you want to continue connecting (yes/no)? yes
Password:
orapwdcpdb1 100% 3072 3.0KB/s 3.0KB/s 00:00
#
###### drpdb1 user root: #########
# cd /u02/app/oracle/product/11.2.0/dbhome_1/dbs
# pwd
/u02/app/oracle/product/11.2.0/dbhome_1/dbs
# ls
hc_drpdb.dat init.ora orapwdcpdb1
initdrpdb.ora lkDRPDB peshm_drpdb_0
# mv orapwdcpdb1 orapwdrpdb
# ls
hc_drpdb.dat init.ora orapwdrpdb
initdrpdb.ora lkDRPDB peshm_drpdb_0
******************************************************************
drpdb1: as grid user:
******************************************************************
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=drpdb1-vip.nblitd.com)(PORT=1521))))' scope=both sid='+ASM1';
alter system register;
******************************************************************
drpdb1 as oracle user:
******************************************************************
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=drpdb1-vip.nblitd.com)(PORT=1521))))'scope=BOTH SID='drpdb1';
alter system register;
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=drpdb1-vip.nblitd.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=drpdb1-vip.nblitd.com)(PORT=1971))))'scope=BOTH SID='drpdb1';
alter system register;
******************************************************************
dcpdb1: as grid user:
******************************************************************
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dcpdb1-vip.nblitd.com)(PORT=1521))))' scope=both sid='+ASM1';
alter system register;
******************************************************************
dcpdb2: as grid user:
******************************************************************
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dcpdb2-vip.nblitd.com)(PORT=1521))))' scope=both sid='+ASM2';
alter system register;
******************************************************************
dcpdb2: as oracle user:
******************************************************************
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dcpdb2-vip.nblitd.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=dcpdb2-vip.nblitd.com)(PORT=1971))))'scope=BOTH SID='dcpdb2';
alter system register;
******************************************************************
dcpdb1 as oracle user:
******************************************************************
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dcpdb1-vip.nblitd.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=dcpdb1-vip.nblitd.com)(PORT=1590))))'scope=BOTH SID='dcpdb1';
alter system register;
******************************************************************
add logfile with thread 2:
******************************************************************
SQL> ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 5 ('+DATA','+DATA') SIZE 200M;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 6 ('+DATA','+DATA') SIZE 200M;
******************************************************************
create tablespace:
******************************************************************
SQL> CREATE TABLESPACE T24MASTERDATA
DATAFILE '+DATA' SIZE 10G;
SQL> CREATE TABLESPACE T24MASTERINDEX
DATAFILE '+DATA' SIZE 3G;
SQL> alter database force logging;
Database altered.
******************************************************************
add standby logfile with thread 1:
******************************************************************
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
2 GROUP 7 ('+DATA','+DATA') size 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
2 GROUP 8 ('+DATA','+DATA') size 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
2 GROUP 9 ('+DATA','+DATA') size 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
2 GROUP 10 ('+DATA','+DATA') size 200M;
Database altered.
******************************************************************
add standby logfile with thread 2:
******************************************************************
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
2 GROUP 11 ('+DATA','+DATA') size 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
2 GROUP 12 ('+DATA','+DATA') size 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
2 GROUP 13 ('+DATA','+DATA') size 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
2 GROUP 14 ('+DATA','+DATA') size 200M;
Database altered.
SQL> alter system set log_archive_config = 'DG_CONFIG=(dcpdb,drpdb)'scope=both sid='*';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dcpdb' scope=both sid='*';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=drpdb SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=drpdb' scope=both sid='*';
System altered.
SQL> alter system set log_archive_dest_state_1=enable scope=both;
System altered.
SQL> alter system set log_archive_dest_state_2=enable scope=both;
System altered.
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
System altered.
SQL> alter system set log_archive_max_processes=5 scope=both sid='*';
System altered.
SQL> alter system set fal_server=drpdb scope=both sid='*';
System altered.
SQL> alter system set fal_client=dcpdb scope=both sid='*';
System altered.
SQL> alter system set db_file_name_convert='drpdb','dcpdb' scope=spfile sid='*';
System altered.
SQL> alter system set log_file_name_convert='drpdb','dcpdb' scope=spfile sid='*';
System altered.
SQL> alter system set db_unique_name='dcpdb' scope=spfile sid='*';
System altered.
SQL> alter system set standby_file_management=AUTO scope=both sid='*';
System altered.
SQL> alter database set standby database to maximize availability;
Database altered.
drpdb1: user oracle
** Create a listener with following listener.ora file on the standby server:
LISTENERDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = drpdb1)(PORT = xxxx))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCxxxx))
)
)
SID_LIST_LISTENERDB =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /u02/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = drpdb)
(GLOBAL_DBNAME= drpdb.nblitd.com)
)
)
ADR_BASE_LISTENERDB = /u02/app/oracle
N.B: Restart all server login again in dcpdb1 as oracle then
SQL> create pfile from spfile;
File created.
copy pfile to another location and modify parameter then paste this pfile in dbs location in drpdb1(dr).
initdcpdb1.ora file:
*.audit_file_dest='/u02/app/oracle/admin/drpdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/drpdb/controlfile/current.261.809456283','+DATA/drpdb/controlfile/current.260.809456283'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+DATA'
*.db_create_online_log_dest_2='+DATA'
*.db_domain='nblitd.com'
*.db_file_name_convert='dcpdb','drpdb'
*.db_name='dcpdb'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=535797170176
*.db_unique_name='drpdb'
*.diagnostic_dest='/u02/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dcpdbXDB)'
*.fal_client='drpdb'
*.fal_server='dcpdb'
dcpdb2.instance_number=2
dcpdb1.instance_number=1
*.job_queue_processes=1000
#*.local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST =drpdb1.nblitd.com)(PORT=1590))'
*.log_archive_config='DG_CONFIG=(dcpdb,drpdb)'
*.log_archive_dest_1='SERVICE=dcpdb SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dcpdb'
*.log_archive_dest_2='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=drpdb'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=10
*.log_file_name_convert='dcpdb','drpdb'
*.memory_target=15440281600
*.open_cursors=5000
*.processes=5000
*.remote_login_passwordfile='exclusive'
*.sessions=5505
*.standby_file_management='AUTO'
dcpdb2.thread=2
dcpdb1.thread=1
dcpdb1.undo_tablespace='UNDOTBS1'
dcpdb2.undo_tablespace='UNDOTBS2'#It will be comment for single instance DR
In DRPDB1 as root:
# cd /u02/app/oracle/product/11.2.0/dbhome_1/dbs/
# ls
hc_drpdb.dat init.ora orapwdrpdb
initdcpdb1.ora lkDRPDB peshm_drpdb_0
# mv initdcpdb1.ora initdrpdb.ora
#
N:B: IN DRPDB1: user oracle
$ export ORACLE_BASE=/u02/app/oracle
$ export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
$ export GRID_HOME=/u01/app/11.2.0/grid
$ export ORACLE_SID=drpdb
$ cd $ORACLE_HOME/bin
./sqlplus " / as sysdba"$
SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 13 15:53:13 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u02/app/oracle/product/11.2.0/dbhome_1/dbs/initdrpdb1.ora'
ORACLE instance started.
Total System Global Area 1.5400E+10 bytes
Fixed Size 2184872 bytes
Variable Size 7683967320 bytes
Database Buffers 7683964928 bytes
Redo Buffers 29409280 bytes
SQL>
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
In DCPDB1 login as oracle:
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export GRID_HOME=/u01/app/11.2.0/grid
export ORACLE_SID=dcpdb1
cd $ORACLE_HOME/bin
$ ./rman target sys/HPinvent123nbl@dcpdb AUXILIARY sys/HPinvent123nbl@drpdb
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Mar 14 13:33:16 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DCPDB (DBID=322322458)
connected to auxiliary database: DCPDB (not mounted)
RMAN>
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 14-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=5649 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u02/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdcpdb1' auxiliary format
'/u02/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdrpdb' ;
}
executing Memory Script
Starting backup at 14-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5655 instance=dcpdb1 device type=DISK
Finished backup at 14-MAR-13
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DATA/drpdb/controlfile/current.256.809999501';
restore clone controlfile to '+DATA/drpdb/controlfile/current.257.809999501' from
'+DATA/drpdb/controlfile/current.256.809999501';
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''+DATA/drpdb/controlfile/current.256.809999501'', ''+DATA/drpdb/controlfile/current.257.809999501'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Starting backup at 14-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u02/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_dcpdb1.f tag=TAG20130314T135301 RECID=1 STAMP=810049983
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 14-MAR-13
Starting restore at 14-MAR-13
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 14-MAR-13
sql statement: create spfile from memory
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 15399526400 bytes
Fixed Size 2184872 bytes
Variable Size 7717521752 bytes
Database Buffers 7650410496 bytes
Redo Buffers 29409280 bytes
sql statement: alter system set control_files = ''+DATA/drpdb/controlfile/current.256.809999501'', ''+DATA/drpdb/controlfile/current.257.809999501'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 15399526400 bytes
Fixed Size 2184872 bytes
Variable Size 7717521752 bytes
Database Buffers 7650410496 bytes
Redo Buffers 29409280 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";
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" ;
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 14-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/dcpdb/datafile/t24masterdata.277.809459733
output file name=+DATA/drpdb/datafile/t24masterdata.258.809999543 tag=TAG20130314T135343
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:15:26
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DATA/dcpdb/datafile/t24masterindex.278.809459821
output file name=+DATA/drpdb/datafile/t24masterindex.259.810000471 tag=TAG20130314T135343
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:04:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/dcpdb/datafile/sysaux.257.809456141
output file name=+DATA/drpdb/datafile/sysaux.260.810000755 tag=TAG20130314T135343
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/dcpdb/datafile/system.256.809456139
output file name=+DATA/drpdb/datafile/system.261.810000841 tag=TAG20130314T135343
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/dcpdb/datafile/undotbs1.258.809456141
output file name=+DATA/drpdb/datafile/undotbs1.262.810000907 tag=TAG20130314T135343
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/dcpdb/datafile/undotbs2.267.809456519
output file name=+DATA/drpdb/datafile/undotbs2.263.810000931 tag=TAG20130314T135343
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/dcpdb/datafile/users.259.809456141
output file name=+DATA/drpdb/datafile/users.264.810000939 tag=TAG20130314T135343
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 14-MAR-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=1 STAMP=810051347 file name=+DATA/drpdb/datafile/system.261.810000841
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=810051347 file name=+DATA/drpdb/datafile/sysaux.260.810000755
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=810051347 file name=+DATA/drpdb/datafile/undotbs1.262.810000907
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=810051347 file name=+DATA/drpdb/datafile/users.264.810000939
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=810051347 file name=+DATA/drpdb/datafile/undotbs2.263.810000931
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=810051347 file name=+DATA/drpdb/datafile/t24masterdata.258.809999543
datafile 7 switched to datafile copy
input datafile copy RECID=7 STAMP=810051347 file name=+DATA/drpdb/datafile/t24masterindex.259.810000471
Finished Duplicate Db at 14-MAR-13
RMAN>
Login DRPDB1 as oracle:
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export GRID_HOME=/u01/app/11.2.0/grid
export ORACLE_SID=drpdb
cd $ORACLE_HOME/bin
./sqlplus " / as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 14 14:23:29 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL>
SQL> select database_role,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL> alter database open read only;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
SQL>
SQL> select database_role,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL>
How to Open DR in Read only Mode?
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 1.5400E+10 bytes
Fixed Size 2184872 bytes
Variable Size 7717521752 bytes
Database Buffers 7650410496 bytes
Redo Buffers 29409280 bytes
Database mounted.
SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
SQL>
##################### Query #########################
set pagesize 999
set linesize 999
col member for a30
col status for a18
select l.thread#,l.group#,l.members, member,l.archived,l.status
from v$log l, v$logfile lf
where l.group# = lf.group#;
THREAD# GROUP# MEMBERS MEMBER ARC STATUS
---------- ---------- ---------- ------------------------------ --- ------------------
1 2 2 +DATA/drpdb/onlinelog/group_2. YES CLEARING
267.810000953
1 2 2 +DATA/drpdb/onlinelog/group_2. YES CLEARING
268.810000955
1 1 2 +DATA/drpdb/onlinelog/group_1. YES CLEARING_CURRENT
265.810000949
1 1 2 +DATA/drpdb/onlinelog/group_1. YES CLEARING_CURRENT
266.810000951
2 4 2 +DATA/drpdb/onlinelog/group_4. YES CLEARING
271.810000961
2 4 2 +DATA/drpdb/onlinelog/group_4. YES CLEARING
272.810000963
2 5 2 +DATA/drpdb/onlinelog/group_5. YES CLEARING
273.810000965
2 5 2 +DATA/drpdb/onlinelog/group_5. YES CLEARING
274.810000967
1 3 2 +DATA/drpdb/onlinelog/group_3. YES CLEARING
269.810000955
1 3 2 +DATA/drpdb/onlinelog/group_3. YES CLEARING
270.810000957
2 6 2 +DATA/drpdb/onlinelog/group_6. YES CLEARING_CURRENT
275.810000967
2 6 2 +DATA/drpdb/onlinelog/group_6. YES CLEARING_CURRENT
276.810000969
12 rows selected.
SQL> select name from v$controlfile;
NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA/drpdb/controlfile/current.256.809999501
+DATA/drpdb/controlfile/current.257.809999501
SQL> select file_name, tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------
+DATA/drpdb/datafile/users.264.810000939 USERS
+DATA/drpdb/datafile/undotbs1.262.810000907 UNDOTBS1
+DATA/drpdb/datafile/sysaux.260.810000755 SYSAUX
+DATA/drpdb/datafile/system.261.810000841 SYSTEM
+DATA/drpdb/datafile/undotbs2.263.810000931 UNDOTBS2
+DATA/drpdb/datafile/t24masterdata.258.809999543 T24MASTERDATA
+DATA/drpdb/datafile/t24masterindex.259.810000471 T24MASTERINDEX
7 rows selected.
SQL>
SQL> select recovery_mode from v$archive_dest_status;
RECOVERY_MODE
-----------------------
IDLE
MANAGED REAL TIME APPLY
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
32 rows selected.
SQL>
SQL> select database_role,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL> select database_mode, recovery_mode, protection_mode, synchronization_status, synchronized from v$archive_dest_status;
DATABASE_MODE RECOVERY_MODE PROTECTION_MODE SYNCHRONIZATION_STATUS SYN
--------------- ----------------------- -------------------- ---------------------- ---
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
32 rows selected.
SQL>
SQL>
Change the Protection Mode
New Protection Mode: Maximum Availability.
On Primary:
alter system set LOG_ARCHIVE_DEST_1='SERVICE=drpdb SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=drpdb' scope=both sid='*';
alter database set standby database to maximize availability;
On Standby:
alter system set LOG_ARCHIVE_DEST_1='SERVICE=dcpdb SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dcpdb' scope=both sid='*';
alter database set standby database to maximize availability;
DR Shutdown & Startup Process:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1.5400E+10 bytes
Fixed Size 2184872 bytes
Variable Size 7717521752 bytes
Database Buffers 7650410496 bytes
Redo Buffers 29409280 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
SQL> select database_role,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL>
SQL> select status from v$instance;
STATUS
------------------
OPEN
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL>
SQL> select open_mode ,protection_mode , database_role from v$database ;
SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG order by SEQUENCE# ;
########################### Test Log Transport ##################
On the primary server, check the latest archived redo log and force a log switch.
dcpdb1: and dcpdb2:
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time
FROM v$archived_log
ORDER BY sequence#;
ALTER SYSTEM SWITCH LOGFILE;
Check the new archived redo log has arrived at the standby server and been applied.
drpdb1:
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#;
############################ END Test Log Transport ##########
###############################
Copy password file:
######## dcpdb1 User root: #################
# whoami
root
# pwd
/u02/app/oracle/product/11.2.0/dbhome_1/dbs
#
# scp orapwdcpdb1 drpdb1:/u02/app/oracle/product/11.2.0/dbhome_1/dbs
Warning: the RSA host key for 'drpdb1' differs from the key for the IP address '172.31.200.1'
Offending key for IP in /.ssh/known_hosts:6
Matching host key in /.ssh/known_hosts:9
Are you sure you want to continue connecting (yes/no)? yes
Password:
orapwdcpdb1 100% 3072 3.0KB/s 3.0KB/s 00:00
#
###### drpdb1 user root: #########
# cd /u02/app/oracle/product/11.2.0/dbhome_1/dbs
# pwd
/u02/app/oracle/product/11.2.0/dbhome_1/dbs
# ls
hc_drpdb.dat init.ora orapwdcpdb1
initdrpdb.ora lkDRPDB peshm_drpdb_0
# mv orapwdcpdb1 orapwdrpdb
# ls
hc_drpdb.dat init.ora orapwdrpdb
initdrpdb.ora lkDRPDB peshm_drpdb_0
******************************************************************
drpdb1: as grid user:
******************************************************************
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=drpdb1-vip.nblitd.com)(PORT=1521))))' scope=both sid='+ASM1';
alter system register;
******************************************************************
drpdb1 as oracle user:
******************************************************************
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=drpdb1-vip.nblitd.com)(PORT=1521))))'scope=BOTH SID='drpdb1';
alter system register;
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=drpdb1-vip.nblitd.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=drpdb1-vip.nblitd.com)(PORT=1971))))'scope=BOTH SID='drpdb1';
alter system register;
******************************************************************
dcpdb1: as grid user:
******************************************************************
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dcpdb1-vip.nblitd.com)(PORT=1521))))' scope=both sid='+ASM1';
alter system register;
******************************************************************
dcpdb2: as grid user:
******************************************************************
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dcpdb2-vip.nblitd.com)(PORT=1521))))' scope=both sid='+ASM2';
alter system register;
******************************************************************
dcpdb2: as oracle user:
******************************************************************
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dcpdb2-vip.nblitd.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=dcpdb2-vip.nblitd.com)(PORT=1971))))'scope=BOTH SID='dcpdb2';
alter system register;
******************************************************************
dcpdb1 as oracle user:
******************************************************************
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dcpdb1-vip.nblitd.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=dcpdb1-vip.nblitd.com)(PORT=1590))))'scope=BOTH SID='dcpdb1';
alter system register;
******************************************************************
add logfile with thread 2:
******************************************************************
SQL> ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 5 ('+DATA','+DATA') SIZE 200M;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 6 ('+DATA','+DATA') SIZE 200M;
******************************************************************
create tablespace:
******************************************************************
SQL> CREATE TABLESPACE T24MASTERDATA
DATAFILE '+DATA' SIZE 10G;
SQL> CREATE TABLESPACE T24MASTERINDEX
DATAFILE '+DATA' SIZE 3G;
SQL> alter database force logging;
Database altered.
******************************************************************
add standby logfile with thread 1:
******************************************************************
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
2 GROUP 7 ('+DATA','+DATA') size 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
2 GROUP 8 ('+DATA','+DATA') size 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
2 GROUP 9 ('+DATA','+DATA') size 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
2 GROUP 10 ('+DATA','+DATA') size 200M;
Database altered.
******************************************************************
add standby logfile with thread 2:
******************************************************************
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
2 GROUP 11 ('+DATA','+DATA') size 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
2 GROUP 12 ('+DATA','+DATA') size 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
2 GROUP 13 ('+DATA','+DATA') size 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
2 GROUP 14 ('+DATA','+DATA') size 200M;
Database altered.
SQL> alter system set log_archive_config = 'DG_CONFIG=(dcpdb,drpdb)'scope=both sid='*';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dcpdb' scope=both sid='*';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=drpdb SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=drpdb' scope=both sid='*';
System altered.
SQL> alter system set log_archive_dest_state_1=enable scope=both;
System altered.
SQL> alter system set log_archive_dest_state_2=enable scope=both;
System altered.
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
System altered.
SQL> alter system set log_archive_max_processes=5 scope=both sid='*';
System altered.
SQL> alter system set fal_server=drpdb scope=both sid='*';
System altered.
SQL> alter system set fal_client=dcpdb scope=both sid='*';
System altered.
SQL> alter system set db_file_name_convert='drpdb','dcpdb' scope=spfile sid='*';
System altered.
SQL> alter system set log_file_name_convert='drpdb','dcpdb' scope=spfile sid='*';
System altered.
SQL> alter system set db_unique_name='dcpdb' scope=spfile sid='*';
System altered.
SQL> alter system set standby_file_management=AUTO scope=both sid='*';
System altered.
SQL> alter database set standby database to maximize availability;
Database altered.
drpdb1: user oracle
** Create a listener with following listener.ora file on the standby server:
LISTENERDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = drpdb1)(PORT = xxxx))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCxxxx))
)
)
SID_LIST_LISTENERDB =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /u02/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = drpdb)
(GLOBAL_DBNAME= drpdb.nblitd.com)
)
)
ADR_BASE_LISTENERDB = /u02/app/oracle
N.B: Restart all server login again in dcpdb1 as oracle then
SQL> create pfile from spfile;
File created.
copy pfile to another location and modify parameter then paste this pfile in dbs location in drpdb1(dr).
initdcpdb1.ora file:
*.audit_file_dest='/u02/app/oracle/admin/drpdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/drpdb/controlfile/current.261.809456283','+DATA/drpdb/controlfile/current.260.809456283'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+DATA'
*.db_create_online_log_dest_2='+DATA'
*.db_domain='nblitd.com'
*.db_file_name_convert='dcpdb','drpdb'
*.db_name='dcpdb'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=535797170176
*.db_unique_name='drpdb'
*.diagnostic_dest='/u02/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dcpdbXDB)'
*.fal_client='drpdb'
*.fal_server='dcpdb'
dcpdb2.instance_number=2
dcpdb1.instance_number=1
*.job_queue_processes=1000
#*.local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST =drpdb1.nblitd.com)(PORT=1590))'
*.log_archive_config='DG_CONFIG=(dcpdb,drpdb)'
*.log_archive_dest_1='SERVICE=dcpdb SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dcpdb'
*.log_archive_dest_2='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=drpdb'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=10
*.log_file_name_convert='dcpdb','drpdb'
*.memory_target=15440281600
*.open_cursors=5000
*.processes=5000
*.remote_login_passwordfile='exclusive'
*.sessions=5505
*.standby_file_management='AUTO'
dcpdb2.thread=2
dcpdb1.thread=1
dcpdb1.undo_tablespace='UNDOTBS1'
dcpdb2.undo_tablespace='UNDOTBS2'#It will be comment for single instance DR
In DRPDB1 as root:
# cd /u02/app/oracle/product/11.2.0/dbhome_1/dbs/
# ls
hc_drpdb.dat init.ora orapwdrpdb
initdcpdb1.ora lkDRPDB peshm_drpdb_0
# mv initdcpdb1.ora initdrpdb.ora
#
N:B: IN DRPDB1: user oracle
$ export ORACLE_BASE=/u02/app/oracle
$ export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
$ export GRID_HOME=/u01/app/11.2.0/grid
$ export ORACLE_SID=drpdb
$ cd $ORACLE_HOME/bin
./sqlplus " / as sysdba"$
SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 13 15:53:13 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u02/app/oracle/product/11.2.0/dbhome_1/dbs/initdrpdb1.ora'
ORACLE instance started.
Total System Global Area 1.5400E+10 bytes
Fixed Size 2184872 bytes
Variable Size 7683967320 bytes
Database Buffers 7683964928 bytes
Redo Buffers 29409280 bytes
SQL>
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
In DCPDB1 login as oracle:
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export GRID_HOME=/u01/app/11.2.0/grid
export ORACLE_SID=dcpdb1
cd $ORACLE_HOME/bin
$ ./rman target sys/HPinvent123nbl@dcpdb AUXILIARY sys/HPinvent123nbl@drpdb
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Mar 14 13:33:16 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DCPDB (DBID=322322458)
connected to auxiliary database: DCPDB (not mounted)
RMAN>
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 14-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=5649 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u02/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdcpdb1' auxiliary format
'/u02/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdrpdb' ;
}
executing Memory Script
Starting backup at 14-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5655 instance=dcpdb1 device type=DISK
Finished backup at 14-MAR-13
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DATA/drpdb/controlfile/current.256.809999501';
restore clone controlfile to '+DATA/drpdb/controlfile/current.257.809999501' from
'+DATA/drpdb/controlfile/current.256.809999501';
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''+DATA/drpdb/controlfile/current.256.809999501'', ''+DATA/drpdb/controlfile/current.257.809999501'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Starting backup at 14-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u02/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_dcpdb1.f tag=TAG20130314T135301 RECID=1 STAMP=810049983
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 14-MAR-13
Starting restore at 14-MAR-13
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 14-MAR-13
sql statement: create spfile from memory
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 15399526400 bytes
Fixed Size 2184872 bytes
Variable Size 7717521752 bytes
Database Buffers 7650410496 bytes
Redo Buffers 29409280 bytes
sql statement: alter system set control_files = ''+DATA/drpdb/controlfile/current.256.809999501'', ''+DATA/drpdb/controlfile/current.257.809999501'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 15399526400 bytes
Fixed Size 2184872 bytes
Variable Size 7717521752 bytes
Database Buffers 7650410496 bytes
Redo Buffers 29409280 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";
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" ;
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 14-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/dcpdb/datafile/t24masterdata.277.809459733
output file name=+DATA/drpdb/datafile/t24masterdata.258.809999543 tag=TAG20130314T135343
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:15:26
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DATA/dcpdb/datafile/t24masterindex.278.809459821
output file name=+DATA/drpdb/datafile/t24masterindex.259.810000471 tag=TAG20130314T135343
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:04:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/dcpdb/datafile/sysaux.257.809456141
output file name=+DATA/drpdb/datafile/sysaux.260.810000755 tag=TAG20130314T135343
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/dcpdb/datafile/system.256.809456139
output file name=+DATA/drpdb/datafile/system.261.810000841 tag=TAG20130314T135343
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/dcpdb/datafile/undotbs1.258.809456141
output file name=+DATA/drpdb/datafile/undotbs1.262.810000907 tag=TAG20130314T135343
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/dcpdb/datafile/undotbs2.267.809456519
output file name=+DATA/drpdb/datafile/undotbs2.263.810000931 tag=TAG20130314T135343
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/dcpdb/datafile/users.259.809456141
output file name=+DATA/drpdb/datafile/users.264.810000939 tag=TAG20130314T135343
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 14-MAR-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=1 STAMP=810051347 file name=+DATA/drpdb/datafile/system.261.810000841
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=810051347 file name=+DATA/drpdb/datafile/sysaux.260.810000755
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=810051347 file name=+DATA/drpdb/datafile/undotbs1.262.810000907
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=810051347 file name=+DATA/drpdb/datafile/users.264.810000939
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=810051347 file name=+DATA/drpdb/datafile/undotbs2.263.810000931
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=810051347 file name=+DATA/drpdb/datafile/t24masterdata.258.809999543
datafile 7 switched to datafile copy
input datafile copy RECID=7 STAMP=810051347 file name=+DATA/drpdb/datafile/t24masterindex.259.810000471
Finished Duplicate Db at 14-MAR-13
RMAN>
Login DRPDB1 as oracle:
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export GRID_HOME=/u01/app/11.2.0/grid
export ORACLE_SID=drpdb
cd $ORACLE_HOME/bin
./sqlplus " / as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 14 14:23:29 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL>
SQL> select database_role,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL> alter database open read only;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
SQL>
SQL> select database_role,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL>
How to Open DR in Read only Mode?
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 1.5400E+10 bytes
Fixed Size 2184872 bytes
Variable Size 7717521752 bytes
Database Buffers 7650410496 bytes
Redo Buffers 29409280 bytes
Database mounted.
SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
SQL>
##################### Query #########################
set pagesize 999
set linesize 999
col member for a30
col status for a18
select l.thread#,l.group#,l.members, member,l.archived,l.status
from v$log l, v$logfile lf
where l.group# = lf.group#;
THREAD# GROUP# MEMBERS MEMBER ARC STATUS
---------- ---------- ---------- ------------------------------ --- ------------------
1 2 2 +DATA/drpdb/onlinelog/group_2. YES CLEARING
267.810000953
1 2 2 +DATA/drpdb/onlinelog/group_2. YES CLEARING
268.810000955
1 1 2 +DATA/drpdb/onlinelog/group_1. YES CLEARING_CURRENT
265.810000949
1 1 2 +DATA/drpdb/onlinelog/group_1. YES CLEARING_CURRENT
266.810000951
2 4 2 +DATA/drpdb/onlinelog/group_4. YES CLEARING
271.810000961
2 4 2 +DATA/drpdb/onlinelog/group_4. YES CLEARING
272.810000963
2 5 2 +DATA/drpdb/onlinelog/group_5. YES CLEARING
273.810000965
2 5 2 +DATA/drpdb/onlinelog/group_5. YES CLEARING
274.810000967
1 3 2 +DATA/drpdb/onlinelog/group_3. YES CLEARING
269.810000955
1 3 2 +DATA/drpdb/onlinelog/group_3. YES CLEARING
270.810000957
2 6 2 +DATA/drpdb/onlinelog/group_6. YES CLEARING_CURRENT
275.810000967
2 6 2 +DATA/drpdb/onlinelog/group_6. YES CLEARING_CURRENT
276.810000969
12 rows selected.
SQL> select name from v$controlfile;
NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA/drpdb/controlfile/current.256.809999501
+DATA/drpdb/controlfile/current.257.809999501
SQL> select file_name, tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------
+DATA/drpdb/datafile/users.264.810000939 USERS
+DATA/drpdb/datafile/undotbs1.262.810000907 UNDOTBS1
+DATA/drpdb/datafile/sysaux.260.810000755 SYSAUX
+DATA/drpdb/datafile/system.261.810000841 SYSTEM
+DATA/drpdb/datafile/undotbs2.263.810000931 UNDOTBS2
+DATA/drpdb/datafile/t24masterdata.258.809999543 T24MASTERDATA
+DATA/drpdb/datafile/t24masterindex.259.810000471 T24MASTERINDEX
7 rows selected.
SQL>
SQL> select recovery_mode from v$archive_dest_status;
RECOVERY_MODE
-----------------------
IDLE
MANAGED REAL TIME APPLY
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
32 rows selected.
SQL>
SQL> select database_role,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL> select database_mode, recovery_mode, protection_mode, synchronization_status, synchronized from v$archive_dest_status;
DATABASE_MODE RECOVERY_MODE PROTECTION_MODE SYNCHRONIZATION_STATUS SYN
--------------- ----------------------- -------------------- ---------------------- ---
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
UNKNOWN IDLE MAXIMUM PERFORMANCE STATUS NOT AVAILABLE NO
32 rows selected.
SQL>
SQL>
Change the Protection Mode
New Protection Mode: Maximum Availability.
On Primary:
alter system set LOG_ARCHIVE_DEST_1='SERVICE=drpdb SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=drpdb' scope=both sid='*';
alter database set standby database to maximize availability;
On Standby:
alter system set LOG_ARCHIVE_DEST_1='SERVICE=dcpdb SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dcpdb' scope=both sid='*';
alter database set standby database to maximize availability;
DR Shutdown & Startup Process:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1.5400E+10 bytes
Fixed Size 2184872 bytes
Variable Size 7717521752 bytes
Database Buffers 7650410496 bytes
Redo Buffers 29409280 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
SQL> select database_role,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL>
SQL> select status from v$instance;
STATUS
------------------
OPEN
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL>
SQL> select open_mode ,protection_mode , database_role from v$database ;
SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG order by SEQUENCE# ;
########################### Test Log Transport ##################
On the primary server, check the latest archived redo log and force a log switch.
dcpdb1: and dcpdb2:
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time
FROM v$archived_log
ORDER BY sequence#;
ALTER SYSTEM SWITCH LOGFILE;
Check the new archived redo log has arrived at the standby server and been applied.
drpdb1:
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#;
############################ END Test Log Transport ##########
No comments:
Post a Comment