ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [转帖]Oracle9i Standby (Dataguard) 建立

[转帖]Oracle9i Standby (Dataguard) 建立

原创 Linux操作系统 作者:tolywang 时间:2009-03-04 15:17:16 0 删除 编辑
  1. 準備工作
  • Primary Server IP:192.168.217.9
  • Standby Server IP:192.168.217.23
  • Primary Net Service Name: primary
  • Standby Net Service Name: standby
  • Primary ORACLE_SID=primary
  • Standby ORACLE_SID=standby
  • ORACLE_BASE=/opt/ora9
  • ORACLE_HOME=/opt/ora9/product/9.2
  • Primary 已經存在一個資料庫,Standby只要灌好Oracle軟體,不需要建立資料庫。
  • Primary DataBase已經開啟於Archive Log模式
  • Primary DB、Standby DB位於兩台不同的機器上
  • Primary DB與Standby DB的目錄結構都相同
  1. (primary)開啟Force Logging模式
    SQL> SQL*Plus: Release 9.2.0.8.0 - Production on 星期二 4月 1 17:51:14 2008

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    SQL> connect / as sysdba
    Connected.
    SQL> alter database force logging;


  2. (primary)造出最新的pfile
    SQL> create pfile from spfile;

  3. (primary)修改初始化參數($ORACLE_HOME\dbs\initprimary.ora)

    *.INSTANCE_NAME='primary'
    *.DB_NAME='primary'

    *.LOG_ARCHIVE_DEST_1='LOCATION=/opt/ora9/oradata/archive_log mandatory'
    *.LOG_ARCHIVE_DEST_2='SERVICE=standby OPTIONAL reopen=60 ARCH SYNC NOAFFIRM '-->(Maximum Performance 模式)
    *.LOG_ARCHIVE_DEST_STATE_1=ENABLE
    *.LOG_ARCHIVE_DEST_STATE_2=ENABLE
    *.LOG_ARCHIVE_FORMAT=%d_%t_%s.arc
    *.REMOTE_ARCHIVE_ENABLE=TRUE

    *.FAL_SERVER=standby
    *.FAL_CLIENT=primary
    *. STANDBY_ARCHIVE_DEST=/opt/ora9/oradata/archive_log
    *.STANDBY_FILE_MANAGEMENT=AUTO
    *._log_archive_callout='LOCAL_FIRST=TRUE'-->避免尖峰時間,switch log太快,系統hang住
    *.archive_lag_target=1800


  4. (primary)複製參數檔密碼檔至Standby DB

    $ scp $ORACLE_HOME/dbs/initprimary.ora    oracle@192.168.217.23:$ORACLE_HOME/dbs/initstandby.ora
    $ scp $ORACLE_HOME/dbs/orapwprimary    oracle@192.168.217.23:$ORACLE_HOME/dbs/orapwstandby


  5. (Standby)修改 Standby DB 之參數檔($ORACLE_HOME\dbs\initstandby.ora)

    *.INSTANCE_NAME='standby'
    *.DB_NAME='primary' -->需要跟primary db相同

    *.LOG_ARCHIVE_DEST_1='LOCATION=/opt/ora9/oradata/archive_log mandatory'
    *.LOG_ARCHIVE_DEST_2='SERVICE=primary OPTIONAL reopen=60 ARCH SYNC NOAFFIRM '-->(Maximum Performance 模式)
    *.LOG_ARCHIVE_DEST_STATE_1=ENABLE
    *.LOG_ARCHIVE_DEST_STATE_2=DEFER -->暫時關閉,等Switchover時才開啟。
    *.LOG_ARCHIVE_FORMAT=%d_%t_%s.arc
    *.REMOTE_ARCHIVE_ENABLE=TRUE

    *.FAL_SERVER=primary
    *.FAL_CLIENT=standby
    *. STANDBY_ARCHIVE_DEST=/opt/ora9/oradata/archive_log
    *.STANDBY_FILE_MANAGEMENT=AUTO
    *._log_archive_callout='LOCAL_FIRST=TRUE'
    *.archive_lag_target=1800


  6. (Standby)創建Standby DB上的相關目錄

    #su - oracle

    $ mkdir -p $ORACLE_BASE/backup -->用於 Rman備份
    $ mkdir -p $ORACLE_BASE/oradata/primary -->資料庫目錄
    $ mkdir -p $ORACLE_BASE/oradata/archive_log
    $ mkdir -p $ORACLE_BASE/oradata/standby_archive_log
    $ mkdir -p $ORACLE_BASE/admin/primary/bdump
    $ mkdir -p $ORACLE_BASE/admin/primary/cdump
    $ mkdir -p $ORACLE_BASE/admin/primary/create
    $ mkdir -p $ORACLE_BASE/admin/primary/pfile
    $ mkdir -p $ORACLE_BASE/admin/primary/udump


  7. (Primary)修改$ORACLE_HOME/network/admin/listener.ora

    LISTENER =
    (DESCRIPTION_LIST =
     (DESCRIPTION =
      (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.9)(PORT = 1521))
      )
     )
    )

    SID_LIST_LISTENER =
    (  SID_LIST =
       (SID_DESC =
        (SID_NAME = PLSExtProc)
        (ORACLE_HOME = /opt/ora9/product/9.2)
        (PROGRAM = extproc)
       )
       (SID_DESC =
        (GLOBAL_DBNAME = primary)
        (ORACLE_HOME = /opt/ora9/product/9.2)
        (SID_NAME = primary)
       )
    )


  8. (Standby)修改$ORACLE_HOME/network/admin/listener.ora

    LISTENER =
    (DESCRIPTION_LIST =
     (DESCRIPTION =
      (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.23)(PORT = 1521))
      )
     )
    )

    SID_LIST_LISTENER =
    (  SID_LIST =
       (SID_DESC =
        (SID_NAME = PLSExtProc)
        (ORACLE_HOME = /opt/ora9/product/9.2)
        (PROGRAM = extproc)
       )
       (SID_DESC =
        (GLOBAL_DBNAME = standby)
        (ORACLE_HOME = /opt/ora9/product/9.2)
        (SID_NAME = standby)
       )
    )


  9. (Standby&Primary)修改$ORACLE_HOME/network/admin/sqlnet.ora

    NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
    SQLNET.EXPIRE_TIME = 2


  10. (Primary)修改$ORACLE_HOME/network/admin/tnsnames.ora

    MASTER =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.9)(PORT = 1521))
      )
     (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = master)
     )
    )

    STANDBY =
    (DESCRIPTION =
     (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.23)(PORT = 1521))
     )
     (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
     )
    )



  11. (Standby)修改$ORACLE_HOME/network/admin/tnsnames.ora

    MASTER =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.9)(PORT = 1521))
      )
     (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = master)
     )
    )

    STANDBY =
    (DESCRIPTION =
     (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.23)(PORT = 1521))
     )
     (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
     )
    )



  12. (Primary)(Standby)若有修改listner.ora 需重新啟動listener

    #su - oracle
    $ lsnrctl stop
    $ lsnrctl start
    $ lsnrctl status


  13. (Standby)由pfile創出spfile,並且開啟Standby DB於nomount模式

    [oracle@standby dbs]$ sqlplus /nolog

    SQL*Plus: Release 9.2.0.8.0 - Production on 星期二 4月 1 23:41:33 2008

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    SQL> connect / as sysdba
    Connected to an idle instance.
    SQL> create spfile from pfile;

    File created.

    SQL> startup nomount;
    ORACLE instance started.

    Total System Global Area 235999648 bytes
    Fixed Size 450976 bytes
    Variable Size 201326592 bytes
    Database Buffers 33554432 bytes
    Redo Buffers 667648 bytes
    SQL> exit


  14. (Primary)使用Rman備份Primary資料庫,準備Duplicate至Standby DB

    [oracle@primary ora9]$ rman target / nocatalog

    Recovery Manager: Release 9.2.0.8.0 - Production

    Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

    connected to target database: MASTER (DBID=2710930473)
    using target database controlfile instead of recovery catalog

    RMAN> backup filesperset 5 database format '/opt/ora9/backup/%d_t%t_s%s_p%p' include current controlfile for standby;

    Starting backup at 02-4月 -08
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=18 devtype=DISK
    channel ORA_DISK_1: starting full datafile backupset
    channel ORA_DISK_1: specifying datafile(s) in backupset
    input datafile fno=00013 name=/opt/ora9/oradata/primary/DBS3.dbf
    input datafile fno=00006 name=/opt/ora9/oradata/primary/indx01.dbf
    input datafile fno=00009 name=/opt/ora9/oradata/primary/users01.dbf
    input datafile fno=00004 name=/opt/ora9/oradata/primary/drsys01.dbf
    input datafile fno=00008 name=/opt/ora9/oradata/primary/tools01.dbf
    ...... including current SPFILE in backupset
    including standby controlfile in backupset
    ......

    RMAN> sql 'alter system archive log current';

    sql statement: alter system archive log current

    RMAN> backup archivelog all ;

    Starting backup at 02-4月 -08
    current log archived
    using channel ORA_DISK_1
    .....


  15. (Primary)copy 剛剛的備份至Standby DB

    $ scp $ORACLE_BASE/backup/* oracle@192.168.217.23:/$ORACLE_BASE/backup


  16. (Primary)Duplicate 剛剛的備份至Standby DB

    RMAN> connect auxiliary sys@standby;

    auxiliary database Password:
    connected to auxiliary database: master (not mounted)

    RMAN> duplicate target database for standby nofilenamecheck dorecover;

    Starting Duplicate Db at 02-4月 -08
    using channel ORA_AUX_DISK_1

    printing stored script. Memory Script
    {
    restore clone standby controlfile to clone_cf; -->恢復Standby control file
    replicate clone controlfile from clone_cf;
    sql clone 'alter database mount standby database'; -->原本Standby 開啟於nomount模式',Rman將其轉變成mount 模式
    }
    executing script. Memory Script

    Starting restore at 02-4月 -08

    using channel ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: starting datafile backupset restore
    channel ORA_AUX_DISK_1: restoring controlfile
    output filename=/opt/ora9/oradata/master/control_sdby01.ctl
    channel ORA_AUX_DISK_1: restored backup piece 1
    .....



  17. (Primary)重新啟動Primary,使用剛剛修改過的initprimary.ora

    [oracle@baksrv dbs]$ sqlplus /nolog

    SQL*Plus: Release 9.2.0.8.0 - Production on 星期三 4月 2 11:41:05 2008

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    SQL> connect / as sysdba
    Connected.
    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> create spfile from pfile; -->做出新的spfile

    File created.

    SQL> startup;
    ORACLE instance started.

    Total System Global Area 235999648 bytes
    Fixed Size 450976 bytes
    Variable Size 201326592 bytes
    Database Buffers 33554432 bytes
    Redo Buffers 667648 bytes
    Database mounted.
    Database opened.
    SQL> alter system switch logfile;-->switch log一次
    System altered.
    SQL> select * from v$log; -->目前的log sequence# 為148,最新的archived log為147

    GROUP#  THREAD#  SEQUENCE#  BYTES  MEMBERS  ARC  STATUS  FIRST_CHANGE#   FIRST_TIME
    ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
    1     1      146      104857600  1    YES   INACTIVE  2178807 02-4月 -08
    2     1      147      104857600  1    YES   ACTIVE   2178808 02-4月 -08
    3     1      148      104857600  1    NO   CURRENT  2179548 02-4月 -08
    8     1      145      104857600  1    YES   INACTIVE  2176247 02-4月 -08



  18. (Standby)檢查Standby 是否有接收到Archived Log

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

    SEQUENCE# APP
    ---------- ---
    146 NO
    145 NO
    147 NO -->已經接收到147...Data Guard已經大致上完成了^_^

    SQL> alter database recover managed standby database disconnect from session; -->開始做自動 standby recover

    Database altered.

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

    SEQUENCE# APP
    ---------- ---
    146 YES
    145 YES
    147 YES -->原本NO的狀態,已經變成YES


  19. (Standby)加入Standby Redo Log..為了Switchover..建議Primary也加入

    SQL> alter database recover managed standby database cancel;

    SQL> Alter database add standby logfile '/opt/ora9/oradata/primary/stdby_redo01.log' size 100M;
    SQL> Alter database add standby logfile '/opt/ora9/oradata/primary/stdby_redo02.log' size 100M;
    SQL> Alter database add standby logfile '/opt/ora9/oradata/primary/stdby_redo03.log' size 100M;

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


  20. (Standby)檢查Standby

    SQL> select process,status,client_process,sequence#,block# from v$managed_standby;

    SQL> select * from v$standby_log; -->檢查Standby Redo log是否啟用。目前log sequence為150

    GROUP#   THREAD#    SEQUENCE#   BYTES  USED  ARC  STATUS FIRST_CHANGE#  FIRST_TIME LAST_CHANGE# LAST_TIME
    ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------- ------------ ----------
    4    1    150     104857600   0      YES    ACTIVE     2180477 02-4月 -08 0
    5    0    0      104857600   1024     YES    UNASSIGNED  0 0
    6    0    0      104857600   1024     YES    UNASSIGNED  0 0

    SQL>




 

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

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13335071