Oracle 11g Active Data Guard step by step: How to create a P
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.
|
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
[oracle@prod dbs]$mkdir -p /u01/arch1/prod
[oracle@prod 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
[oracle@prod dbs]$orapwd file=orapwprod password=oracle entries=5 force=y
[oracle@prod dbs]$ls -lt orapwprod
-rw-r----- 1 oracleoinstall 2048 Jun 9 09:48 orapwprod
[oracle@prod dbs]$scp orapwprod 192.168.8.190:$ORACLE_HOME/dbs
[oracle@standby ~]$cd $ORACLE_HOME/dbs
[oracle@standbydbs]$ ls -lt orapwprod
-rw-r----- 1 oracleoinstall 2048 Jun 9 09:49 orapwprod
[oracle@standbydbs]$ 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
[oracle@prod prod]$scp /tmp/initprod.ora 192.168.8.190:$ORACLE_HOME/dbs
[oracle@standbydbs]$ 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
[oracle@standbydbs]$ mkdir -p /u01/app/oracle/admin/standby/adump
[oracle@standbydbs]$ chown -R oracle:oinstall /u01/app/oracle/admin/standby/adump
[oracle@standbydbs]$ chmod -R 775 /u01/app/oracle/admin/standby/adump
[oracle@standbyu01]$ mkdir -p /u01/arch1/standby
[oracle@standbyu01]$ 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
[oracle@standby ~]$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
[oracle@prod prod]$rman target / auxiliary sys/oracle@standby
connected to targetdatabase: PROD (DBID=229264367)
connected toauxiliary database: PROD (not mounted)
Onstandby
[oracle@standby ~]$rman target sys/oracle@prod auxiliary sys/oracle@standby
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
[oracle@standbydbs]$ mkdir -p /u01/app/oracle/oradata/STANDBY/controlfile
[oracle@prod ~]$ 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
QQ：444367417
MSN：xiangsir@hotmail.com
下一篇： Java web开发中文乱码问题