oracle 11g dataguard 建立

一、  Rman線上建立物理Standby (不允許停主庫的情況下)

1.        測試環境

	主庫(安裝Oracle及資料庫) Primary Database	備庫(只安裝Oracle軟體) Standby Database
IP地址	192.168.1.185	192.168.7.76
SID:	Checkdb	checkdbsb
DB_UNIQUE_NAME	checkdb	checkdbsb
資料檔案路徑	/u02/oradata/checkdb/	/u02/oradata/checkdbsb
歸檔日誌/standby日誌路徑	/u02/oradata/archive/ /u02/oradata/checkdb/	/u02/oradata/archive/ /u02/oradata/checkdbsb

網路配置要求： 兩臺伺服器可以相互ping 通

2.      開啟Primary資料庫的Forced logging模式 (防止主資料庫表時候使用nologing)

查詢此模式是否已經開啟﹕select force_logging from v$database; 開啟此模式﹕alter database force logging;

3.              根據Primary的目錄結構，在Standby上建立相應的目錄(/u02/oradata/checkdb/--資料檔案目錄，/u02/oradata/archive/----歸檔目錄等)

二、  主備庫listener.ora和tnsnames.ora檔案修改

1.      主資料庫配置

[oracle@oscheckdb admin]$ cat listener.ora

#listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0.1/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = checkdb)

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

      (SID_NAME = checkdb)

    )

    (SID_DESC =

     (GLOBAL_DBNAME = checkdbsb)

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

      (SID_NAME = checkdbsb)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = oscheckdb)(PORT = 1521))

    )

  )

[oracle@oscheckdb admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.1.0.1/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

CHECKDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.185)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = checkdb)

    )

  )

CHECKDBSB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =checkdbsb.bitland.com.cn)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = checkdbsb)

    )

  )

Standby 資料庫配置

[oracle@checkdbsb admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0.1/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = checkdb)

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

      (SID_NAME = checkdb)

    )

    (SID_DESC =

     (GLOBAL_DBNAME = checkdbsb)

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

      (SID_NAME = checkdbsb)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = checkdbsb)(PORT = 1521))

    )

  )

[oracle@checkdbsb admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.1.0.1/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

CHECKDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.185)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = checkdb)

    )

  )

CHECKDBSB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.7.76)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = checkdbsb)

    )

  )

三、  引數配置

1.      置Primary的動態引數檔案(spfile)與Standby的靜態引數檔案(pfile)，檔案的位置在﹕$ORACLE_HOME/dbs，配置方法如下﹕

Primary的spfilecheckdb.ora檔案:

SQL> alter system set log_file_name_convert='checkdbsb', 'checkdb' scope=spfile ;

alter system set fal_client='checkdb';

alter system set fal_server='checkdbsb';

alter system set  db_unique_name='checkdb'

alter system set log_archive_config='DG_CONFIG=('checkdb', 'checkdbsb')' ;

alter system set log_archive_dest_1='LOCATION=/u02/oradata/archive/

VALID_FOR=(ALL_LOGFILES, ALL_ROLES)  DB_UNIQUE_NAME =checkdb ;

alter system set log_archive_dest_2='service='checkdbsb' LGWR ASYNC VALID_FOR=

 (ONLINE_LOGFILES, PRIMARY_ROLE)  DB_UNIQUE_NAME='checkdbsb' ; 

alter system set log_archive_dest_state_1='ENABLE' ;

alter system set log_archive_dest_state_2='ENABLE' ;

alter system set standby_file_management='AUTO' ;

 

standby initcheckdbsb.ora配置

checkdbsb.__db_cache_size=486539264

checkdbsb.__java_pool_size=16777216

checkdbsb.__large_pool_size=16777216

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

checkdbsb.__pga_aggregate_target=469762048

checkdbsb.__sga_target=1409286144

checkdbsb.__shared_io_pool_size=0

checkdbsb.__shared_pool_size=838860800

checkdbsb.__streams_pool_size=33554432

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

*.audit_trail='db'

*.compatible='11.1.0.0.0'

*.control_files='/u02/oradata/checkdbsb/control01.ctl','/u02/oradata/checkdbsb/control02.ctl','/u02/oradata/checkdbsb/control03.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='checkdb'

*.fal_client='checkdb'

*.fal_server='checkdbsb'

*.instance_name='checkdbsb'

*.db_unique_name='checkdbsb'

#*.db_file_name_convert='checkdb','checkdbsb'  取消不需要使用

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

*.db_recovery_file_dest_size=2147483648

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=checkdbsbXDB)'

*.log_archive_config='DG_CONFIG=(checkdb,checkdbsb)'

*.log_archive_dest_1='LOCATION=/u02/oradata/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=checkdbsb'

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

*.memory_target=1875902464

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

四、  建立密碼檔案

Primary密碼檔案設定：orapwd file=$ORACLE_HOME/dbs/orapwcheckdb  password=**** entries=10

Standby密碼檔案設定：orapwd file=$ORACLE_HOME/dbs/orapwcheckdbsb  password=**** entries=10

備注﹕redo傳輸服務通過認証的網路會話來傳輸redo log，其中password必須相同，以確保redo log資料的順利傳輸。

五、  在Primary DB上備份控制檔案，然後對資料庫進行全備份，將備份檔案傳至Standby的相同目錄

 $ rman target / nocatalog

RMAN> backup current controlfile for standby format '/home/oracle/control01.ctl';RMAN> sql"alter system switch logfile";

RMAN> sql"alter system switch logfile"；

RMAN> backup database plus archivelog﹔

備注﹕(備份異常(RMAN-06059) 解決方法﹕change archivelog all crosscheck)

六、  將Standby啟動為nomount狀態

export ORACLE_SID=checkdbsb

 sqlplus / as sysdba

SQL> startup nomount pfile=’$ORACLE_HOME/dbs/initcheckdbsb.ora’

SQL> ORACLE instance started.

七、  在Standby上進行duplicate操作

 [oracle@checkdbsb admin]$ rman auxiliary /

  RMAN> connect target sys/sys@checkdb

  RMAN> duplicate target database for standby dorecover;

  備注：如果是Standby失效，需重建時使用命令：

duplicate target database for standby nofilenamecheck;

八、  啟動Standby應用

   alter database recover managed standby database disconnect from session;

九、  檢測Data Guard是否成功

Primary:

   SQL> select process,status from v$managed_standby;

     PROCESS   STATUS

         --------- ------------

         ARCH      CLOSING

         ARCH      CLOSING

         LNS       WRITING

   同時在Primary上切換日誌檔案，alter system switch logfile 

Standby:

        SQL> select process,status from v$managed_standby;

                PROCESS   STATUS

         --------- ------------

         ARCH      CONNECTED

         ARCH      CONNECTED

         MRP0      WAIT_FOR_LOG

         RFS               IDLE

SQL> select sequence#,first_time,next_time,applied from v$archived_log;

 

 SEQUENCE# FIRST_TIM NEXT_TIME APP

---------- --------- --------- ---

       557 20-AUG-13 20-AUG-13 YES

       629 21-AUG-13 21-AUG-13 YES

       630 21-AUG-13 21-AUG-13 YES

       631 21-AUG-13 21-AUG-13 YES

       632 21-AUG-13 21-AUG-13 YES

       560 21-AUG-13 21-AUG-13 YES

  備注﹕要確保MRP0與RFS程式起來，其中MRP0負責應用日誌，RFS負責接收日誌

十、 結束

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15187685/viewspace-769049/，如需轉載，請註明出處，否則將追究法律責任。