Oracle11gActiveDataGuardstepbystep:HowtocreateaP

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