Build Physical standby Data Guard On Signal Node

1. Preparing Network Configuration:

$ cat $ORACLE_HOME/network/admin/listener.ora

LISTENER =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node1.ocmu.com)(PORT = 1521)))

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = prod.ocmu.com)

      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)

      (SID_NAME = prod))

    (SID_DESC =

      (GLOBAL_DBNAME = prodstd.ocmu.com)

      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)

      (SID_NAME = prodstd))

    (SID_DESC =

      (GLOBAL_DBNAME = prod_DGMGRL.ocmu.com)

      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)

      (SID_NAME = prod))

    (SID_DESC =

      (GLOBAL_DBNAME = prodstd_DGMGRL.ocmu.com)

      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)

      (SID_NAME = prodstd)))

$ cat $ORACLE_HOME/network/admin/tnsnames.ora

prod =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = node1.ocmu.com)(PORT = 1521))

    (CONNECT_DATA = (SERVICE_NAME = prod.ocmu.com)(SID_NAME = prod)))

prodstd =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = node1.ocmu.com)(PORT = 1521))

    (CONNECT_DATA = (SERVICE_NAME = prodstd.ocmu.com)(SID_NAME = prodstd)))

LISTENER =

  (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = node1.ocmu.com)(PORT = 1521)))

2. Erase Old Archivelog: 

In emrep:

$ rman target sys/oracle@prod catalog rman/oracle@emrep

RMAN> CROSSCHECK ARCHIVELOG ALL;

RMAN> DELETE ARCHIVELOG ALL;

3. Preparing Directory And Primary DB parameters, Generate Standby Control File And PFile:

In prod:

$ orapwd file=orapwprodstd password=oracle entries=10 ignorecase=y

$ mkdir -p $ORACLE_BASE/archivelog/{prod,prodstd}

SQL> ALTER SYSTEM SET DB_UNIQUE_NAME=prod SCOPE=SPFILE;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,prodstd)' SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archivelog/prod VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=prodstd ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prodstd' SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 SCOPE=BOTH;

SQL> ALTER SYSTEM SET FAL_SERVER=prodstd SCOPE=BOTH;

SQL> ALTER SYSTEM SET FAL_CLIENT=prod SCOPE=BOTH;

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='prodstd','prod' SCOPE=SPFILE;

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='prodstd','prod' SCOPE=SPFILE;

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/prod/redo04.log' SIZE 52429312;

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/prod/redo05.log' SIZE 52429312;

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/prod/redo06.log' SIZE 52429312;

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/prod/redo07.log' SIZE 52429312;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE FORCE LOGGING;

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/oradata/prodstd/control01.ctl';

SQL> CREATE PFILE='/u01/app/oracle/product/11.1.0/db_1/dbs/initprodstd.ora' FROM SPFILE;

4. Preparing Standby DB parameters, Copy Password File And Control Files, Duplicate Datafiles And Logfiles:

$ cat $ORACLE_HOME/dbs/initprodstd.ora

prod.__db_cache_size=423624704

prod.__java_pool_size=12582912

prod.__large_pool_size=4194304

prod.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

prod.__pga_aggregate_target=209715200

prod.__sga_target=641728512

prod.__shared_io_pool_size=0

prod.__shared_pool_size=192937984

prod.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/prod/adump'

*.audit_trail='db'

*.compatible='11.1.0.0.0'

*.control_files='/u01/app/oracle/oradata/prodstd/control01.ctl','/u01/app/oracle/oradata/prodstd/control02.ctl','/u01/app/oracle/oradata/prodstd/control03.ctl'

*.db_block_size=8192

*.db_domain='ocmu.com'

*.db_file_name_convert='prod','prodstd'

*.db_name='prod'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.db_unique_name='PRODSTD'

*.diagnostic_dest='/u01/app/oracle/product/11.1.0/db_1/log'

*.dispatchers=''

*.fal_client='PRODSTD'

*.fal_server='PROD'

*.log_archive_config='DG_CONFIG=(prodstd,prod)'

*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog/prodstd VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prodstd'

*.log_archive_dest_2='SERVICE=prod ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.arc'

*.log_archive_max_processes=30

*.log_file_name_convert='prod','prodstd'

*.memory_target=848297984

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sec_case_sensitive_logon=TRUE

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

$ cp -a $ORACLE_HOME/dbs/orapwprod $ORACLE_HOME/dbs/orapwprodstd

$ cp -aR $ORACLE_BASE/oradata/prod $ORACLE_BASE/oradata/prodstd > Both Database must be shutdown and  no include control files

$ cp -a /u01/app/oracle/oradata/prodstd/control01.ctl /u01/app/oracle/oradata/prodstd/control02.ctl

$ cp -a /u01/app/oracle/oradata/prodstd/control01.ctl /u01/app/oracle/oradata/prodstd/control03.ctl

5. Startup Log Apply In Standby Database:

In prodstd:

SQL> CREATE SPFILE FROM PFILE='/u01/app/oracle/product/11.1.0/db_1/dbs/initprodstd.ora';

SQL> STARTUP MOUNT

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Note: The SYNC may slow

6. Verify Log Apply:

In prod:

SQL> ALTER SYSTEM SWITCH LOGFILE;

In prodstd:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

7. Open And Configure Broker Function:

In prod and prodstd:

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

$ dgmgrl sys/oracle@prod

DGMGRL> CREATE CONFIGURATION prod AS PRIMARY DATABASE IS prod CONNECT IDENTIFIER IS prod;

DGMGRL> ADD DATABASE prodstd AS CONNECT IDENTIFIER IS prodstd;

DGMGRL> ENABLE CONFIGURATION;

Note: Also need time to execute
DGMGRL> SHOW CONFIGURATION;

DGMGRL> EDIT DATABASE prod SET PROPERTY LogXptMode='SYNC';

DGMGRL> EDIT DATABASE prodstd SET PROPERTY LogXptMode='SYNC';

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

DGMGRL> SHOW CONFIGURATION;

DGMGRL> SHOW DATABASE VERBOSE prod;

DGMGRL> SHOW DATABASE VERBOSE prodstd;

8. Test Switchover:

DGMGRL> SWITCHOVER TO prodstd

DGMGRL> SWITCHOVER TO prod

Note:

1. Shutdown Database from Primary database, Startup Database from Standby database

2. On Signal Node may need adjustment SGA and Memory Target parameters

3. GLOBAL_DBNAME in listener.ora = show parameter service_names, so the SWITCHOVER can work

4. Check listener status

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24644775/viewspace-1463237/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24644775/viewspace-1463237/