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