Create Physical Standby Using
RMAN Duplication From Active Database (New in 11g) :
Primary Site
|
Standby Site
|
|
Hostname
|
prod.erp.com
|
standby.erp.com
|
Database Name
|
prod
|
prod
|
Database Unique Name
|
prod
|
sbyprod
|
Net Service Name
|
prod
|
sbyprod
|
Primary Site Preparation :
1.
Enable Database Force Logging
SQL>
select FORCE_LOGGING from v$database;
FOR
---
NO
SQL>
ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> col group# for 9999
col member for a50
set linesize 120
SQL>
select member,type from v$logfile;
MEMBER
TYPE
--------------------------------------------------
-------
/u01/app/oracle/oradata/prod/redo03.log ONLINE
/u01/app/oracle/oradata/prod/redo02.log ONLINE
/u01/app/oracle/oradata/prod/redo01.log ONLINE
/u01/app/oracle/oradata/prod/sby_redo01 STANDBY
/u01/app/oracle/oradata/prod/sby_redo02 STANDBY
/u01/app/oracle/oradata/prod/sby_redo03 STANDBY
/u01/app/oracle/oradata/prod/sby_redo04 STANDBY
7 rows selected.
SQL> create pfile from spfile;
File created.
3.Modify the init.ora parameters:
Modify the init.ora parameters on the primary
database side to reflect the following values. If you are using spfile, you can
add these parameter online without shutting down the primary database.
DB_UNIQUE_NAME=prod
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(prod,sbyprod)’
LOG_ARCHIVE_DEST_1=’LOCATION=/u01/app/oracle/flash_recovery_area/prod/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod’
LOG_ARCHIVE_DEST_2=’SERVICE=sbyprod ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sbyprod’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=sbyprod
DB_FILE_NAME_CONVERT=’sbyprod’,'prod’
LOG_FILE_NAME_CONVERT=’/u01/app/oracle/flash_recovery_area/sbyprod/’,'/u01/app/oracle/flash_recovery_area/prod/’
STANDBY_FILE_MANAGEMENT=AUTO
SQL> alter system set DB_UNIQUE_NAME=prod
scope=spfile;
System altered.
SQL> alter system set
LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,sbyprod)' scope=spfile;
System altered.
SQL> alter system set
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/flash_recovery_area/prod/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod';
System altered.
SQL> alter system set
LOG_ARCHIVE_DEST_2='SERVICE=sbyprod ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=sbyprod' scope=spfile;
System altered.
SQL> alter system set
LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile;
System altered.
SQL> alter system set
LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile;
System altered.
SQL> alter system set
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;
System altered.
SQL> ALTER SYSTEM SET
log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile;
System altered.
SQL> alter system set
LOG_ARCHIVE_MAX_PROCESSES=30 scope=spfile;
System altered.
SQL> alter system set FAL_SERVER=sbyprod
scope=spfile;
System altered.
SQL> alter system set
DB_FILE_NAME_CONVERT='sbyprod','prod' SCOPE=spfile;
System altered.
SQL> alter system set
LOG_FILE_NAME_CONVERT='/u01/app/oracle/flash_recovery_area/sbyprod','/u01/app/oracle/flash_recovery_area/prod/'
scope=spfile;
System altered.
SQL> alter system set
STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
5.Create a init.ora file for Standby
Database:
SQL> create pfile from spfile;
File created.
---------------------------------------------------------------------
MAKE LISTENER.ORA AND TNSNAMES.ORA ENTRY ON
BOTH SIDE
check tnsping both side
Copy password file from pimary to standby and
rename
------------------------------------------------------
Creating Physical Standby Database :
1.Create Physical Standby Using RMAN
Duplication From Active Database (New in 11g) :
[oracle@standby admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on
Sun Apr 6 22:36:13 2014
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, OLAP, Data Mining and
Real Application Testing options
sql>
Create Physical Standby Using RMAN Duplication From Active Database
[oracle@standby dbhome_1]$ rman
Recovery Manager: Release 11.2.0.1.0 -
Production on Sun Apr 20 00:35:09 2014
Copyright (c) 1982, 2009, Oracle and/or its
affiliates. All rights reserved.
RMAN> connect target sys/oracle123@prod
connected to target database: PROD
(DBID=255899578)
RMAN> connect auxiliary
sys/oracle123@sbyprod
connected to auxiliary database: PROD (not
mounted)
RMAN> duplicate target database for
standby from active database;
Starting Duplicate Db at 20-APR-14
using target database control file instead of
recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprod' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwsbyprod' ;
}
executing Memory Script
Starting backup at 20-APR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=87 device type=DISK
Finished backup at 20-APR-14
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/sbyprod/control01.ctl';
restore clone controlfile to
'/u01/app/oracle/flash_recovery_area/sbyprod/control02.ctl' from
'/u01/app/oracle/oradata/sbyprod/control01.ctl';
}
executing Memory Script
Starting backup at 20-APR-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file
name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_prod.f
tag=TAG20140420T003558 RECID=4 STAMP=845339759
channel ORA_DISK_1: datafile copy complete,
elapsed time: 00:00:03
Finished backup at 20-APR-14
Starting restore at 20-APR-14
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file
copy
Finished restore at 20-APR-14
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
"/u01/app/oracle/oradata/sbyprod/temp01.dbf";
switch clone tempfile all;
set
newname for datafile 1 to
"/u01/app/oracle/oradata/sbyprod/system01.dbf";
set
newname for datafile 2 to
"/u01/app/oracle/oradata/sbyprod/sysaux01.dbf";
set
newname for datafile 3 to
"/u01/app/oracle/oradata/sbyprod/undotbs01.dbf";
set
newname for datafile 4 to
"/u01/app/oracle/oradata/sbyprod/users01.dbf";
set
newname for datafile 5 to
"/u01/app/oracle/oradata/sbyprod/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/sbyprod/system01.dbf" datafile
2
auxiliary format
"/u01/app/oracle/oradata/sbyprod/sysaux01.dbf" datafile
3
auxiliary format
"/u01/app/oracle/oradata/sbyprod/undotbs01.dbf" datafile
4
auxiliary format
"/u01/app/oracle/oradata/sbyprod/users01.dbf" datafile
5
auxiliary format
"/u01/app/oracle/oradata/sbyprod/example01.dbf" ;
sql
'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to
/u01/app/oracle/oradata/sbyprod/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 20-APR-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001
name=/u01/app/oracle/oradata/prod/system01.dbf
output file
name=/u01/app/oracle/oradata/sbyprod/system01.dbf tag=TAG20140420T003608
channel ORA_DISK_1: datafile copy complete,
elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002
name=/u01/app/oracle/oradata/prod/sysaux01.dbf
output file
name=/u01/app/oracle/oradata/sbyprod/sysaux01.dbf tag=TAG20140420T003608
channel ORA_DISK_1: datafile copy complete,
elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005
name=/u01/app/oracle/oradata/prod/example01.dbf
output file
name=/u01/app/oracle/oradata/sbyprod/example01.dbf tag=TAG20140420T003608
channel ORA_DISK_1: datafile copy complete,
elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003
name=/u01/app/oracle/oradata/prod/undotbs01.dbf
output file
name=/u01/app/oracle/oradata/sbyprod/undotbs01.dbf tag=TAG20140420T003608
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=/u01/app/oracle/oradata/prod/users01.dbf
output file
name=/u01/app/oracle/oradata/sbyprod/users01.dbf tag=TAG20140420T003608
channel ORA_DISK_1: datafile copy complete,
elapsed time: 00:00:01
Finished backup at 20-APR-14
sql statement: alter system archive log
current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=845339885
file name=/u01/app/oracle/oradata/sbyprod/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=845339885
file name=/u01/app/oracle/oradata/sbyprod/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=845339885
file name=/u01/app/oracle/oradata/sbyprod/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=845339885
file name=/u01/app/oracle/oradata/sbyprod/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=845339885
file name=/u01/app/oracle/oradata/sbyprod/example01.dbf
Finished Duplicate Db at 20-APR-14
RMAN>
SQL>
select database_role,PROTECTION_MODE,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE OPEN_MODE
---------------- --------------------
--------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE MOUNTED
SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
DISCONNECT FROM SESSION;
Database altered.
SQL>
select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
Verifications :
On Standby:
SQL>
select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
SQL>
archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/flash_recovery_area/sbyprod/
Oldest online log sequence 30
Next log sequence to archive 0
Current log sequence 31
SQL>
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
28 YES
29 YES
30 IN-MEMORY
-----------------------------------------------------------------
On Primary :
Do
some redo log switches
SQL>
ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> /
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination
/u01/app/oracle/flash_recovery_area/prod/
Oldest online log sequence 31
Next log sequence to archive 33
Current log sequence 33
SQL>
select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY
----------------------------------------------------------------------
On Standby:
SQL>
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
28 YES
29 YES
30 YES
31 YES
32 YES
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination
/u01/app/oracle/flash_recovery_area/sbyprod/
Oldest online log sequence 31
Next log sequence to archive 0
Current log sequence 33
SQL>
select PROCESS,STATUS from v$managed_standby;
PROCESS
STATUS
--------- ------------
ARCH
CLOSING
ARCH
CONNECTED
ARCH
CONNECTED
ARCH
CONNECTED
ARCH
CONNECTED
ARCH
CONNECTED
RFS
IDLE
RFS
IDLE
RFS
IDLE
PROCESS
STATUS
--------- ------------
RFS
IDLE
MRP0
APPLYING_LOG
35 rows selected.
No comments:
Post a Comment