Oralce10gdataguard配置

　　具體步驟如下

　　一主庫操作

　　修改主庫屬性

　　 SQL> alter database force logging;
Database altered
##查看狀態
SQL> select FORCE_LOGGING from v$database;
FOR

YES

　　修改數據庫為歸檔模式

SQL> alter system set log_archive_dest_=LOCATION=/arch/ scope=both;
System altered
SQL> shutdown immediate;
Database closed
Database dismounted
ORACLE instance shut down
SQL> startup mount;
ORACLE instance started
Total System Global Area  bytes
Fixed Size                  bytes
Variable Size              bytes
Database Buffers           bytes
Redo Buffers                bytes
Database mounted

SQL> alter database archivelog;
Database altered
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /arch/
Oldest online log sequence    
Next log sequence to archive  
Current log sequence          
SQL> alter database open;
Database altered

　　添加standby logfile（也可以不加）

　　為主數據庫添加備用聯機日志文件這裡要保證備日志文件與主庫聯機日志文件相同大小

　　添加備用日志文件是規則備用日志最少應該比redo log 多一個推薦的備重做日志數依賴於主數據庫上的線程數

　　（每線程日志文件最大數目 + ） * 線程數

　　
SQL> select GROUP#MEMBERSBYTES// from v$log;
    GROUP#    MEMBERS BYTES//

                                
                                
                                
SQL> select GROUP#MEMBER from v$logfile;
     GROUP# MEMBER

         /oracle/oradata/orcl/redolog
         /oracle/oradata/orcl/redolog
         /oracle/oradata/orcl/redolog
SQL> alter database add standby logfile
            group (/oracle/oradata/orclstd_redoalog/oracle/oradata/orcl/std_redoblog) size m
            group (/oracle/oradata/orcl/std_redoalog/oracle/oradata/orcl/std_redoblog) size m
            group (/oracle/oradata/orcl/std_redoalog/oracle/oradata/orcl/std_redoblog) size m
            group (/oracle/oradata/orcl/std_redoalog/oracle/oradata/orcl/std_redobdbf) size m;
Database altered

　　修改主庫參數文件

　　
SQL> create pfile=/oracle/orclora from spfile;
File created
orcl__db_cache_size=
orcl__java_pool_size=
orcl__large_pool_size=
orcl__shared_pool_size=
orcl__streams_pool_size=
*audit_file_dest=/oracle/admin/orcl/adump
*background_dump_dest=/oracle/admin/orcl/bdump
patible=
ntrol_files=/oracle/oradata/orcl/controlctl/oracle/oradata/orcl/controlctl/oracle/oradata/orcl/controlctl
re_dump_dest=/oracle/admin/orcl/cdump
*db_block_size=
*db_domain=
*db_file_multiblock_read_count=
*db_name=orcl
*db_recovery_file_dest=/oracle/flash_recovery_area
*db_recovery_file_dest_size=
*dispatchers=(PROTOCOL=TCP) (SERVICE=orclXDB)
*job_queue_processes=
*DB_UNIQUE_NAME=orclpri    ##必須 定義每個數據庫的唯一標識
*log_archive_config=DG_CONFIG=(orclpriorclstandby)     ###必須
*log_archive_dest_=LOCATION=/arch/ VALID_FOR=(ALL_LOGFILESALL_ROLES) DB_UNIQUE_NAME=orclpri         ###必須  本地的歸檔路徑
*LOG_ARCHIVE_DEST_=SERVICE=orclstandby arch ASYNC VALID_FOR=(ONLINE_LOGFILESPRIMARY_ROLE) DB_UNIQUE_NAME=orclstandby      ###必須(遠程服務器端的歸檔日志
)
*LOG_ARCHIVE_DEST_STATE_=ENABLE
*LOG_ARCHIVE_DEST_STATE_=ENABLE
*FAL_SERVER=orclstandby       ### 定義FAL服務器的Oracle Net服務的名稱
*FAL_CLIENT=orclpri    ### 定義備數據庫的Oracle Net服務名     （這兩個參數在主庫可有可無但備庫必須有ORACLE 老外工程師說這個必須有^_^）  
*open_cursors=
*pga_aggregate_target=
*processes=
*remote_login_passwordfile=EXCLUSIVE
a_target=
*undo_management=AUTO
*undo_tablespace=UNDOTBS
*user_dump_dest=/oracle/admin/orcl/udump
*STANDBY_FILE_MANAGEMENT=AUTO        ###設置為AUTO使得當數據文件添加到主數據庫或者從主數據庫刪除的時候對應的修改能夠在備用數據庫中自動執行

　　用pfile啟動再重新創建spfile

　　 SQL> shutdown immediate;
Database closed
Database dismounted
ORACLE instance shut down
SQL> startup pfile=/oracle/orclora
ORACLE instance started
Total System Global Area  bytes
Fixed Size                  bytes
Variable Size              bytes
Database Buffers           bytes
Redo Buffers                bytes
Database mounted
Database opened
SQL>
SQL> create spfile from pfile=/oracle/orclora;
File created

　　在主庫創建密碼文件以及控制文件

　　 [oracle@node oracle]$ orapwd file=/oracle/product//db_/dbs/orapworclora password=oracle entries=
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS  /oracle/oradata/orcl/standbyctl;
Database altered

　　TNS信息如下

　　 主庫
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
orclpri =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
orclstandby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
監聽信息如下
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SERVICE_NAME=orclpri)
      (ORACLE_HOME = /oracle/product//db_)
        (SID_NAME=ORCL)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhostlocaldomain)(PORT = ))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
  )

　　把數據庫scp到備庫相應的目錄（包括密碼文件standby controlfile）

　　二備機操作

　　備份的參數文件內容

　　 orcl__db_cache_size=
orcl__java_pool_size=
orcl__large_pool_size=
orcl__shared_pool_size=
orcl__streams_pool_size=
*audit_file_dest=/oracle/admin/orcl/adump
*background_dump_dest=/oracle/admin/orcl/bdump
patible=
ntrol_files=/oracle/oradata/orcl/controlctl/oracle/oradata/orcl/controlctl/oracle/oradata/orcl/controlctl
re_dump_dest=/oracle/admin/orcl/cdump
*db_block_size=
*db_domain=
*db_file_multiblock_read_count=
*db_name=orcl
*db_recovery_file_dest=/oracle/flash_recovery_area
*db_recovery_file_dest_size=
*dispatchers=(PROTOCOL=TCP) (SERVICE=orclXDB)
*job_queue_processes=
*DB_UNIQUE_NAME=orclstandby    ##必須 定義每個數據庫的唯一標識
*log_archive_config=DG_CONFIG=(orclpriorclstandby)     ###必須
*log_archive_dest_=LOCATION=/arch/ VALID_FOR=(ALL_LOGFILESALL_ROLES) DB_UNIQUE_NAME=orclstandby         ###必須  本地的歸檔路徑
*LOG_ARCHIVE_DEST_=SERVICE=orclpri arch ASYNC VALID_FOR=(ONLINE_LOGFILESPRIMARY_ROLE) DB_UNIQUE_NAME=orclpri      ###必須(遠程服務器端的歸檔日志)
*LOG_ARCHIVE_DEST_STATE_=ENABLE
*LOG_ARCHIVE_DEST_STATE_=ENABLE
*FAL_SERVER=orclstandby       ### 定義FAL服務器的Oracle Net服務的名稱
*FAL_CLIENT=orclpri   ### 定義備數據庫的Oracle Net服務名     （這兩個參數在主庫可有可無但備庫必須有ORACLE 老外工程師說這個必須有^_^）  
*open_cursors=
*pga_aggregate_target=
*processes=
*remote_login_passwordfile=EXCLUSIVE
a_target=
*undo_management=AUTO
*undo_tablespace=UNDOTBS
*user_dump_dest=/oracle/admin/orcl/udump
*STANDBY_FILE_MANAGEMENT=AUTO

　　修改上面參數文件裡的

　　 ntrol_files=/oracle/oradata/orcl/controlctl/oracle/oradata/orcl/controlctl/oracle/oradata/orcl/controlctl
為
ntrol_files=/oracle/oradata/orcl/standbyctl ##在主機上生成的那個控制文件也可以多放幾份

　　TNS信息如下

　　 備庫
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
orclpri =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
orclstandby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
監聽信息如下
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SERVICE_NAME=orclstandby)
      (ORACLE_HOME = /oracle/product//db_)
        (SID_NAME=ORCL)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhostlocaldomain)(PORT = ))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
  )

　　用創建的參數文件啟動數據庫到nomount

　　 SQL> startup pfile=/oracle/orclstandbyora nomount;
ORACLE instance started
Total System Global Area  bytes
Fixed Size                  bytes
Variable Size              bytes
Database Buffers          bytes
Redo Buffers                bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered

　　修改備庫處於應用歸檔狀態

　　 SQL> alter database recover managed standby database disconnect from session;
Database altered

　　如果主庫從不過來歸檔可以通過在主庫側手工修改參數如下

　　 ALTER SYSTEM SET log_archive_dest_state_=DEFER SCOPE=MEMORY;
ALTER SYSTEM SET log_archive_dest_state_=ENABLE SCOPE=MEMORY;

　　測試

　　通過在主庫執行alter system switch logfile切換日志可以觀察到備庫會自動應用通過主庫傳過來的日志

　　三切換測試

　　在主庫端

　　 select switchover_stats from v$database;

　　如果是to standby  表可以正常切換

　　直接執行

　　 ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

　　否則執行

　　 ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
shutdown immediate;
startup nomount;
alter database mount standby database;

　　如果是to_primary  表可以正常切換

　　在備庫

　　在備庫

　　 SELECT SWITCHOVER_STATUS FROM V$DATABASE;

　　執行

　　 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

　　否則執行

　　 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
shutdown immediate;
startup;

　　然後觀察主備庫日志如果正常的話會看到備庫會自動應用日志

From:http://tw.wingwit.com/Article/program/Oracle/201311/16864.html