| Database | IP | DB_UNIQUE_NAME | Oracle Net Service Name |
| Primary | 192.168.8.180 | prod | Prod |
| Standby | 192.168.8.190 | standby | standby |
| Database | Archive_log_dest_1 | Archive_log_dest_2 |
| Primary | /u01/prod | Service=standby |
| Standby | /u01/standby | Service=prod |
1. Preparing the Primary Database forStandby Database Creation
1.1 Enable Forced Logging
SQL> alter database force logging;
1.2 Enable Forced Logging
[[email protected] dbs]$mkdir -p /u01/arch1/prod
[[email protected] dbs]$mkdir -p /u01/arch2/standby
SQL> alter systemset db_recovery_file_dest='/u01/arch1/prod';
SQL> shutdownimmediate;
SQL> startupmount;
SQL> alterdatabase archivelog;
SQL> alterdatabase open;
SQL> archive loglist
Database logmode Archive Mode
Automaticarchival Enabled
Archivedestination USE_DB_RECOVERY_FILE_DEST
Oldest online logsequence 54
Next log sequence toarchive 56
Current logsequence 56
1.3 Configure Redo TransportAuthentication Using Remote Login Password File
[[email protected] dbs]$orapwd file=orapwprod password=oracle entries=5 force=y
[[email protected] dbs]$ls -lt orapwprod
-rw-r----- 1 oracleoinstall 2048 Jun 9 09:48 orapwprod
[[email protected] dbs]$scp orapwprod 192.168.8.190:$ORACLE_HOME/dbs
[[email protected] ~]$cd $ORACLE_HOME/dbs
[[email protected]]$ ls -lt orapwprod
-rw-r----- 1 oracleoinstall 2048 Jun 9 09:49 orapwprod
[[email protected]]$ mv orapwprod orapwstandby
1.4 Configure Standby Redo Log ForPrimary Database
The synchronous and asynchronous redo transport modes require that aredo transport destination have a standby redo log. A standby redo log is usedto store redo received from another Oracle database. Standby redo logs arestructurally identical to redo logs, and are created and managed using the sameSQL statements used to create and manage redo logs.
Redo received from another Oracle database via redo transport iswritten to the current standby redo log group by an RFS foreground process.When a log switch occurs on the redo source database, incoming redo is thenwritten to the next standby redo log group, and the previously used standbyredo log group is archived by an ARCn foreground process.
standby redo log file must be at least as large as the largest redolog file in the redo log of the redo source database. For administrative ease,Oracle recommends that all redo log files in the redo log at the redo sourcedatabase and the standby redo log at a redo transport destination be of thesame size.
The standby redo log must have at least one more redo log group thanthe redo log at the redo source database, for each redo thread at the redosource database. At the redo source database, query the V$LOG view to determinehow many redo log groups are in the redo log at the redo source database andquery the V$THREAD view to determine how many redo threads exist at the redosource database.
SQL>alter database add standby logfile ('/u01/app/oracle/oradata/PROD/stblog01.log') size 10m;
SQL>alter database add standby logfile ('/u01/app/oracle/oradata/PROD/stblog02.log') size 10m;
SQL>alter database add standby logfile ('/u01/app/oracle/oradata/PROD/stblog03.log') size 10m;
SQL>alter database add standby logfile ('/u01/app/oracle/oradata/PROD/stblog04.log') size 10m;
1.5 Set Primary DatabaseInitialization Parameters
SQL> createpfile='/tmp/initprod.ora' from spfile;
Addthe following entries into the pfile '/tmp/initprod.ora':
Primary Database:Primary Role Initialization Parameters
DB_NAME=prod
DB_UNIQUE_NAME=prod
LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,standby)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/arch1/prod VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=prod'
LOG_ARCHIVE_DEST_2=
'SERVICE=standby LGWR SYNC AFFIRM
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES)
DB_UNIQUE_NAME=standby'
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
Primary Database:Standby Role Initialization Parameters
FAL_SERVER=standby
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/STANDBY','/u01/app/oracle/oradata/PROD'
LOG_FILE_NAME_CONVERT='/u01/arch1/STANDBY','/u01/arch1/PROD','/u01/arch2/STANDBY','/u01/arch2/PROD'
STANDBY_FILE_MANAGEMENT=auto
1.6 Set Standby DatabaseInitialization Parameters
[[email protected] prod]$scp /tmp/initprod.ora 192.168.8.190:$ORACLE_HOME/dbs
[[email protected]]$ mv initprod.ora initstandby.ora
*.__db_cache_size=41943040
*.__java_pool_size=4194304
*.__large_pool_size=4194304
*.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.__pga_aggregate_target=109051904
*.__sga_target=155189248
*.__shared_io_pool_size=0
*.__shared_pool_size=92274688
*.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/standby/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/STANDBY/controlfile/control0l.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain=''
*.db_name='prod'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='/u01/arch1'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=prodXDB)'
*.memory_target=262144000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_NAME=prod
DB_UNIQUE_NAME=standby
LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,standby)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/arch1/STANDBY VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_2=
'SERVICE=standby LGWR SYNC AFFIRM
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES)
DB_UNIQUE_NAME=prod'
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=prod
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/PROD','/u01/app/oracle/oradata/STANDBY'
LOG_FILE_NAME_CONVERT='/u01/arch1/PROD','/u01/arch1/STANDBY','/u01/arch2/PROD','/u01/arch2/STANDBY'
STANDBY_FILE_MANAGEMENT=auto
[[email protected]]$ mkdir -p /u01/app/oracle/admin/standby/adump
[[email protected]]$ chown -R oracle:oinstall /u01/app/oracle/admin/standby/adump
[[email protected]]$ chmod -R 775 /u01/app/oracle/admin/standby/adump
[[email protected]]$ mkdir -p /u01/arch1/standby
[[email protected]]$ mkdir -p /u01/arch2/prod
1.7 Configure Listener for Primaryand Standby Database
Primary:NETCA
Standby:static listener ( userd forrman duplicate)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_NAME =standby)
(ORAACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = standby)
)
)
1.7 Configure Net Service Name forPrimary and Standby Database
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =prod.oracle.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =standby.oracle.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = standby)
)
)
1.8 Duplicat Standby database fromPrimary database using RMAN
On standby
[[email protected] ~]$echo $ORACLE_SID
Standby
SQL> startupnomount
SQL> createspfile from pfile;
Connect to the primary and standby db using rman onprimary or on standby
On primary
[[email protected] prod]$rman target / auxiliary sys/[email protected]
connected to targetdatabase: PROD (DBID=229264367)
connected toauxiliary database: PROD (not mounted)
Onstandby
[[email protected] ~]$rman target sys/[email protected] auxiliary sys/[email protected]
connected to targetdatabase: PROD (DBID=229264367)
connected toauxiliary database: PROD (not mounted)
Create standby controlfile on the Primary Databaseserver using RMAN
sqlplus
1. startup mount the primary database;
2. alter database create standby controlfileas '/tmp/control0l.ctl';
3. alter primary database open;
rman
RMAN> copy current controlfile for standby to'/home/oracle/control0l.ctl';
Copy standby controlfile from Primary to Standby 'spath which is specified by the control_files intialization parameter of standby
[[email protected]]$ mkdir -p /u01/app/oracle/oradata/STANDBY/controlfile
[[email protected] ~]$ scpcontrol0l.ctl standby:/u01/app/oracle/oradata/STANDBY/controlfile/control0l.ctl
RMAN> duplicatetarget database for standby from active database nofilenamecheck;
datafile 1 switchedto datafile copy
input datafile copyRECID=7 STAMP=817682385 filename=/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_system_8v8y9m2x_.dbf
datafile 2 switchedto datafile copy
input datafile copyRECID=8 STAMP=817682385 filename=/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_sysaux_8v8y9m91_.dbf
datafile 3 switchedto datafile copy
input datafile copyRECID=9 STAMP=817682385 filename=/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_undotbs1_8v8y9mb8_.dbf
datafile 4 switchedto datafile copy
input datafile copyRECID=10 STAMP=817682385 filename=/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_users_8v8y9md4_.dbf
datafile 5 switchedto datafile copy
input datafile copyRECID=11 STAMP=817682385 filename=/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_example_8v8yglfz_.dbf
Finished DuplicateDb at 2013-06-09 21:59:48
Delete static listener configuration and reconfig thedynamic listener when duplicate finished
Check the instance status of standby when duplicatefinished
SQL> selectinstance_name,status from v$instance;
INSTANCE_NAME STATUS
----------------------------------------------------
standby MOUNTED
Open the standby instance in open read-only mode
SQL> alterdatabase open read only;
alter database openread only
*
ERROR at line 1:
ORA-10458: standbydatabase requires recovery
ORA-01152: file 1was not restored from a sufficiently old backup
ORA-01110: data file1:
'/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_system_8v8y9m2x_.dbf'
SQL> alterdatabase recover managed standby database disconnect from session;
SQL> alterdatabase open read only;
alter database openread only
*
ERROR at line 1:
ORA-10456: cannotopen standby database; media recovery session may be in
progress
SQL> alterdatabase recover managed standby database cancel;
Database altered.
alterdatabase recover managed standby database using current logfiledisconnect from session;
SQL> selectthread#,dest_id,max(sequence#) from
2 v$archived_log
3 group by thread#,dest_id
4 order by thread#;
THREAD# DEST_ID MAX(SEQUENCE#)
-------------------- --------------
1 1 39
selectthread#,dest_id,max(sequence#) from v$archived_loggroup by thread#,dest_idorder by thread#;
Primary:
SQL> selectGROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# ARC STATUS
-------------------- ---------- --- ----------------
1 1 43 YES ACTIVE
2 1 44 YES ACTIVE
3 1 45 NO CURRENT
SQL> alter systemarchive log current;
System altered.
SQL> selectGROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# ARC STATUS
-------------------- ---------- --- ----------------
1 1 46 NO CURRENT
2 1 44 YES ACTIVE
3 1 45 YES ACTIVE
SQL> selectGROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS from v$standby_log;
GROUP# THREAD# SEQUENCE# ARC STATUS
-------------------- ---------- --- ----------
4 1 0 NO UNASSIGNED
5 1 46 YES ACTIVE
6 0 0 YES UNASSIGNED
7 0 0 YES UNASSIGNED
Note: the sequence of current online redo log of Primary Database is equal to the sequence ofthe Active standby log of Standby Database,
It shows that the Real -TimeRedo Transport is enabled.
Data Guard Real Time SynchronizationTest
Primary
SQL> selectusername,account_status from dba_users where username = 'SCOTT';
USERNAME ACCOUNT_STATUS
--------------------------------------------------------------
SCOTT EXPIRED & LOCKED
Standby
SQL> select username,account_status from dba_userswhere username = 'SCOTT';
USERNAME ACCOUNT_STATUS
--------------------------------------------------------------
SCOTT EXPIRED & LOCKED
Primary
SQL> alter userscott account unlock;
User altered.
SQL> selectusername,account_status from dba_users where username = 'SCOTT';
USERNAME ACCOUNT_STATUS
--------------------------------------------------------------
SCOTT EXPIRED
Standby
SQL> select username,account_status from dba_userswhere username = 'SCOTT';
USERNAME ACCOUNT_STATUS
--------------------------------------------------------------
SCOTT EXPIRED
Primary
SQL> alter userscott identified by tiger;
User altered.
Standby
SQL> connscott/tiger;
Connected.
轉載請注明作者出處及原文連結，否則将追究法律責任：
作者：xiangsir
原文連結：http://blog.csdn.net/xiangsir/article/details/9106311
QQ：444367417
MSN：[email protected]
![ORACLE DATAGUARD CONCEPTS[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)