熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

在單機上創建物理Oracle數據庫9istandby

2013-11-13 15:41:24  來源: Oracle 

  說明Oraclei 數據庫的 Data Guard 特性確保對數據進行完整的保護是 Oracle i 的一個關鍵特性之一Data Guard 可以創建物理的 Standby 數據庫也可以創建邏輯的Standby數據庫還可以混合使用靈活性比較強.如果對Standby機制的了解有更高的期望或者想得到關於Oracle數據庫的data guard 和Standby的更多信息請參考官方的文檔

  現有的數據庫實例 (Primary)名字orcl

  預創建的Standby數據庫實例名字orcl

  數據庫版本信息          SQL> SELECT * FROM v$version;
        BANNER
       
        Oraclei Enterprise Edition Release Production
        PL/SQL Release Production
        CORE Production
        TNS for bit Windows: Version Production
        NLSRTL Version Production

  准備工作:

  首先確認Primary數據庫是否在歸檔模式下          SQL> SHOW user
        USER is SYS
        SQL>
        SQL> ARCHIVE log list
        Database log mode Archive ModeAutomatic archival EnabledArchive destination D:\oracle\arcOldest online log sequence Next log sequence to archive Current log sequence 如果不在歸檔模式下調整數據庫首先提交命令修改SPfile
        SQL>ALTER SYSTEM SET log_archive_start=TRUE SCOPE=SPFILE;

  然後關閉數據庫實例          SQL>SHUTDOWN immediate;

  備份數據庫          SQL>STARTUP MOUNT
        SQL>ALTER DATABASE ARCHIVELOG;
        SQL>ALTER DATABASE OPEN;
        SQL>SHUTDOWN IMMEDIATE

  備份

   Primary Database 需要做的准備工作

   激活 Forced Logging          SQL> ALTER DATABASE FORCE LOGGING;

   設置本地歸檔目標          SQL> ALTER SYSTEM SET log_archive_dest_=LOCATION=D:\oracle\arc SCOPE=BOTH;

  此操作直接生效

  創建物理的Standby數據庫

   標記出Primary數據庫的數據文件          SQL> SELECT NAME FROM v$datafile;
        NAME
       
        D:\ORACLE\ORADATA\ORCL\SYSTEMDBFD:\ORACLE\ORADATA\ORCL\UNDOTBSDBFD:\ORACLE\ORADATA\ORCL\DRSYSDBFD:\ORACLE\ORADATA\ORCL\INDXDBFD:\ORACLE\ORADATA\ORCL\TOOLSDBFD:\ORACLE\ORADATA\ORCL\USERSDBFD:\ORACLE\ORADATA\ORCL\XDBDBF

   關閉Instance 拷貝數據文件到既定目的地          SQL> SHUTDOWN IMMEDIATE;

   為Standby 數據庫創建控制文件          SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE
        AS E:\oracle\oradata\orcl\STANDBYCTL;

  要注意這個控制文件的名字不要和Primary的控制文件名字重復

   為Standby數據庫准備初始化參數文件名字          SQL> CREATE PFILE=E:\oracle\admin\orcl\pfile\initorclora FROM SPFILE;

   設定初始化Physical Standby Database參數          *aq_tm_processes=
        *background_dump_dest=E:\oracle\admin\orcl\bdump
        patible=
        ntrol_files=E:\oracle\oradata\orcl\STANDBYCTL
        re_dump_dest=E:\oracle\admin\orcl\cdump
        *db_block_size=
        *db_cache_size=
        *db_domain=
        *db_file_multiblock_read_count=
        *db_name=orcl
        *dispatchers=(PROTOCOL=TCP) (SERVICE=DEMOXDB)
        *fast_start_mttr_target=
        *hash_area_size=
        *hash_join_enabled=TRUE
        *instance_name=orcl
        *java_pool_size=
        *job_queue_processes=
        *large_pool_size=
        *log_archive_dest_=location=E:\oracle\arc
        *log_archive_start=TRUE
        *open_cursors=
        *optimizer_mode=FIRST_ROWS
        *pga_aggregate_target=
        *processes=
        *query_rewrite_enabled=TRUE
        *remote_login_passwordfile=EXCLUSIVE
        *shared_pool_size=
        *sort_area_size=
        *star_transformation_enabled=TRUE
        *timed_statistics=TRUE
        *undo_management=AUTO
        *undo_retention=
        *undo_tablespace=UNDOTBS
        *user_dump_dest=E:\oracle\admin\orcl\udump
        lock_name_space=orcl
        standby_file_management=AUTO
        remote_archive_enable=TRUE
        standby_archive_dest=E:\oracle\standbyarc
        db_file_name_convert=(D:\oracle\oradata\orcl E:\oracle\oradata\orcl)
        log_file_name_convert=(D:\oracle\oradata\orcl E:\oracle\oradata\orcl)
        log_archive_dest_=(LOCATION=E:\oracle\standbyarc)

  整個操作的過程中容易出現錯誤的地方幾乎都集中在此處必須認真仔細的對待這個文件標記為黑色的地方是需要進行修改的

   創建一個Windows服務

            WINNT> oradim NEW SID orcl STARTMODE manual

   Create a Server Parameter File for the Standby Database

  可參考執行如下操作          C:\>SET oracle_sid=orcl
        C:\>SQLPLUS /nolog
        SQL> CONNECT / as sysdba
        SQL> CREATE SPFILE FROM PFILE=E:\oracle\admin\orcl\pfile\initorclora;

   啟動物理Standby數據庫          C:\>set oracle_sid=orcl
        C:\>sqlplus /nolog
        SQL> connect / as sysdba
        SQL> STARTUP NOMOUNT;
        SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

   在Standby數據庫上初始化Log Apply 服務          SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

   激活到物理Standby數據庫的歸檔          SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_=SERVICE=orcl SCOPE=BOTH;
        SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_=ENABLE SCOPE=BOTH;

   啟動遠程歸檔          SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; 在primary database上執行

  安裝完的的驗證

   在Primary database上          alter system set standby_archive_dest=E:\oracle\standbyarc scope=both;

   在database查詢V$ARCHIVED_LOG

  (其實也可以直接到相關目錄下查看Log是否創建):          SQL> SELECT SEQUENCE# FIRST_TIME NEXT_TIME
        FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
        SEQUENCE# FIRST_TIM NEXT_TIME JAN JAN JAN JAN 在Primary數據庫上歸檔當前的Log
        SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

   驗證是否收到          SQL> SELECT SEQUENCE# FIRST_TIME NEXT_TIME
        FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
        SEQUENCE# FIRST_TIM NEXT_TIME JAN JAN JAN JAN JAN JAN

   驗證是否新的歸檔Redo日志已經被應用          SQL> SELECT sequence# applied FROM v$archived_log ORDER BY sequence#;
        SEQUENCE# APP YES YES YESOK表明我們還是成功的

  附加內容:

  primary數據庫的Pfile內容

  *aq_tm_processes=
        *background_dump_dest=D:\oracle\admin\orcl\bdump
        patible=
        ntrol_files=D:\oracle\oradata\orcl\controlctlD:\oracle\oradata\orcl\controlctlD:\oracle\oradata\orcl\controlctl
        re_dump_dest=D:\oracle\admin\orcl\cdump
        *db_block_size=
        *db_cache_size=
        *db_domain=
        *db_file_multiblock_read_count=
        *db_name=orcl
        *dispatchers=(PROTOCOL=TCP)
        *fast_start_mttr_target=
        *hash_join_enabled=TRUE
        *instance_name=orcl
        *java_pool_size=
        *job_queue_processes=
        *large_pool_size=
        *log_archive_dest_=LOCATION=D:\oracle\arc
        *log_archive_start=TRUE
        *open_cursors=
        *pga_aggregate_target=
        *processes=
        *query_rewrite_enabled=FALSE
        *remote_login_passwordfile=EXCLUSIVE
        *shared_pool_size=
        *sort_area_size=
        *star_transformation_enabled=FALSE
        *timed_statistics=TRUE
        *undo_management=AUTO
        *undo_retention=
        *undo_tablespace=UNDOTBS
        *user_dump_dest=D:\oracle\admin\orcl\udump
        *standby_archive_dest=E:\oracle\standbyarc
        *remote_archive_enable=true


From:http://tw.wingwit.com/Article/program/Oracle/201311/17140.html
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.