ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle dataguard

oracle dataguard

原创 Linux操作系统 作者:zyliyuanrong 时间:2013-09-18 20:37:47 0 删除 编辑

ORACLE10G DATAGUARD配置筆記
環境:
 OS:RHEL5+ORACLE10G10.2.0.1
 IP:172.17.61.160(primary) 172.17.61.161(standby)
 ORACLE_SID:orcl
 ORACLE_HOME:/u01/app


一、配置standby database為MAXIMIZE PERFORMANCE模式
二、轉換模式由MAXIMIZE PERFORMANCE到MAXIMIZE PROTECTION
三、主庫和備庫的switchover.

一、配置standby database為MAXIMIZE PERFORMANCE模式
 
 --主庫操作
 1.設置主庫為force logging
  SQL> alter database force logging;

 2.設置主庫為歸檔模式:
  SQL> archive log list
  SQL> shutdown immediate
  SQL> startup mount
  SQL> alter database archivelog;
  SQL> archive log list

 建議再追加設定歸檔位置
  # mkdir -p /u01/app/archlog
   alter system set log_archive_dest_1 = 'Location=/u01/app/archlog' scope=both

 3.檢查主機是否有口令文件,如沒有需建立
   orapwd file='/u01/app/dbs/orapworcl' password=sys entries=5
    orapworcl文件名不能錯了

 4.為主數據庫添加"備用聯機日誌文件"
 SQL> alter database add standby logfile group 4
 ('/u01/oradata/orcl/redo04.log') size 50m;

 SQL> alter database add standby logfile group 5
 ('/u01/oradata/orcl/redo05.log') size 50m;

 SQL> alter database add standby logfile group 6
 ('/u01/oradata/orcl/redo06.log') size 50m;

 SQL> alter database add standby logfile group 7
 ('/u01/oradata/orcl/redo07.log') size 50m;

 5.修改主庫參數文件 !!
   SQL> create pfile='/u01/app/admin/orcl/pfile/initprim.ora' from spfile;

   orcl.__db_cache_size=100663296
   orcl.__java_pool_size=4194304
   orcl.__large_pool_size=4194304
   orcl.__shared_pool_size=54525952
   orcl.__streams_pool_size=0
   *.audit_file_dest='/oracle/admin/orcl/adump'
   *.background_dump_dest='/oracle/admin/orcl /bdump'
   *.compatible='10.2.0.1.0'
   *.control_files='/oracle/oradata/orcl/control01.ctl','/oracle/oradata/orcl/control02.ctl','/oracle/oradata/ orcl/control03.ctl'
   *.core_dump_dest='/oracle/admin/orcl/cdump'
   *.db_block_size=8192
   *.db_domain=''
   *.db_file_multiblock_read_count=16
   *.db_name='orcl'
   *.db_recovery_file_dest='/oracle /flash_recovery_area'
   *.db_recovery_file_dest_size=2147483648
   *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
   *.job_queue_processes=10
   *.log_archive_format='%T%S%r.ARC'
   *.log_archive_max_processes=3
   *. open_cursors=300
   *.pga_aggregate_target=16777216
   *.processes=150
   *.remote_login_passwordfile='EXCLUSIVE'
   *.sga_target=167772160
   *.undo_management='AUTO'
   *.undo_tablespace='UNDOTBS1'
   *.user_dump_dest='/oracle/admin/orcl /udump'
   
   #add below parameter for standy database
   *.DB_UNIQUE_NAME='10gpri'
   *.log_archive_config='DG_CONFIG=(10gpri,10gstandby)'
   *.log_archive_dest_1='location=/oracle/oradata/orcl/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=10gpri'
   *.log_archive_dest_2='SERVICE=10gstandby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gstandby'
   *.STANDBY_FILE_MANAGEMENT=AUTO
   *.LOG_ARCHIVE_DEST_STATE_1=ENABLE
   *.LOG_ARCHIVE_DEST_STATE_2=ENABLE
   *.FAL_SERVER='10gstandby'
   *.FAL_CLIENT ='10gpri'

 6.主庫用PFILE建立SPFILE
 [oracle@Rod-DG01 pfile]$ sqlplus '/ as sysdba'
 SQL> create spfile from pfile='/u01/app/admin/orcl/pfile/initprim.ora';

 7.建立備用庫的控製文件
 SQL> alter database create standby controlfile as '/home/oracle/standby_ctl01.ctl';

 8.配置主數據庫listener及tnsnames
  [oracle@Rod-DG01 admin]$ cat listener.ora

  SID_LIST_LISTENER =
  (SID_LIST =
  (SID_DESC =
  (GLOBAL_DBNAME = orcl)
  (ORACLE_HOME = /u01/app)
  (SID_NAME = orcl)
  )
  )

  LISTENER =
  (DESCRIPTION_LIST =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Rod-DG01)(PORT = 1521))
   )
  )

  #加1522端 ??口供以後做switchover
  SID_LIST_LISTENER1 =
  (SID_LIST =
   (SID_DESC =
    (GLOBAL_DBNAME = orcl)
     (ORACLE_HOME = /u01/app)
    (SID_NAME = orcl)
   )
  )

  LISTENER1 =
  (DESCRIPTION_LIST =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Rod-DG01)(PORT = 1522))
   )
  )

 [oracle@Rod-DG01 admin]$ cat tnsnames.ora
 #1521和1522端 ??口都能連上主機和備機,這樣在做switchover時就不需要改這裡的設置了
  10gpri =
   (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = ( PROTOCOL = TCP)(HOST = 172.17.61.161)(PORT = 1522))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.61.160)(PORT = 1522))
    )
    (CONNECT_DATA =
     (SID = orcl)
    )
   )


  10gstandby =
   (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.61.160)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.61.161)(PORT = 1521))
    )
    (CONNECT_DATA =
     (SID = orcl)
    )
   )

9.複製文件到備機(於備機操作)
  scp -p oracle@172.17.61.160:/u01/oradata/orcl/*.dbf . 複製datafile
  scp -p oracle@172.17.61.160:/u01/oradata/orcl/*.log . redelog文件也需要復製到備機
  scp -p oracle@172.17.61.160:/home/oracle/standby_ctl01.ctl . redelog備機專用控制檔


複製&建立三分控制檔
 [oracle@Rod-DG02 ~]cd /u01/oradata/orcl
 [oracle@Rod-DG02 orcl]$mv standby_ctl01.ctl control01.ctl
 [oracle@Rod-DG02 orcl]$cp control01.ctl control02.ctl
 [oracle@Rod-DG02 orcl]$cp control01.ctl control03.ctl

 

10.複製並修改備機的參數文件
 [oracle@Rod-DG02 ~]cd /u01/app/admin/orcl/pfile
 [oracle@Rod-DG02 pfile]$scp -p oracle@172.17.61.160:/u01/app/admin/orcl/pfile/initprim.ora initstandby.ora

 修改為如下:
 orcl.__db_cache_size=100663296
 orcl.__java_pool_size=4194304
 orcl.__large_pool_size=4194304
 orcl.__shared_pool_size=54525952
 orcl.__streams_pool_size=0
 *.audit_file_dest='/oracle/admin/orcl/adump'
 *.background_dump_dest='/oracle /admin/orcl/bdump'
 *.compatible='10.2.0.1.0'
 *.control_files='/oracle/oradata/orcl/control01.ctl','/oracle/oradata/orcl/control02.ctl','/ oracle/oradata/orcl/control03.ctl'
 *.core_dump_dest='/oracle/admin/orcl/cdump'
 *.db_block_size=8192
 *.db_domain=''
 *.db_file_multiblock_read_count=16
 *.db_name='orcl'
 *.db_recovery_file_dest ='/oracle/flash_recovery_area'
 *.db_recovery_file_dest_size=2147483648
 *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
 *.job_queue_processes=10
 *.log_archive_format='%T%S%r.ARC'
 *.log_archive_max_processes =3
 *.open_cursors=300
 *.pga_aggregate_target=16777216
 *.processes=150
 *.remote_login_passwordfile='EXCLUSIVE'
 *.sga_target=167772160
 *.undo_management='AUTO'
 *.undo_tablespace='UNDOTBS1'
 *.user_dump_dest='/oracle /admin/orcl/udump'
 #須修改項目如下
 *.DB_UNIQUE_NAME='10gstandby'
 *.log_archive_config='DG_CONFIG=(10gpri,10gstandby)'
 *.log_archive_dest_1='location=/oracle/oradata/orcl/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=10gstandby'
 *.log_archive_dest_2 ='SERVICE=10gpri arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gpri'
 *.STANDBY_FILE_MANAGEMENT=AUTO
 *.LOG_ARCHIVE_DEST_STATE_1=ENABLE
 *.LOG_ARCHIVE_DEST_STATE_2=ENABLE
 *.FAL_SERVER='10gpri'
 *.FAL_CLIENT='10gstandby'

 11.生成備用庫密碼文件
  orapwd file='/u01/app/dbs/orapworcl' password=sys entries=5

 12.修改備機的listener及tnsnames
  [oracle@Rod-DG02 admin]$ cat listener.ora
  SID_LIST_LISTENER =
  (SID_LIST =
   (SID_DESC = :
    (GLOBAL_DBNAME = orcl)
    (ORACLE_HOME = /u01/app)
    (SID_NAME = orcl)
   )
  )

 LISTENER =
 (DESCRIPTION_LIST =
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = Rod-DG02)(PORT = 1521))
  )
 )

#加一個1522的端口供以後做switchover
 SID_LIST_LISTENER1 =
  (SID_LIST =
   (SID_DESC =
    (GLOBAL_DBNAME = orcl)
    (ORACLE_HOME = /u01/app)
    (SID_NAME = orcl)
   )
  )

 LISTENER1 =
  (DESCRIPTION_LIST =
   (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = Rod-DG02)(PORT = 1522))
   )
  )


[oracle@Rod-DG02 admin]$ cat tnsnames.ora
 10gpri =
 (DESCRIPTION =
  (ADDRESS_LIST =   
   (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.61.160)(PORT = 1521))
  )
  (CONNECT_DATA =
   (SID = orcl)
  )
 )


 10gstandby =
  (DESCRIPTION =
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.61.160)(PORT = 1522))
   )
   (CONNECT_DATA =
    (SID = orcl)
   )
  )

13.測試主備之間網絡連通
 [oracle@Rod-DG01 admin]$ lsnrctl start
 [oracle@Rod-DG01 admin]$ tnsping 10gstandby
 [oracle@Rod-DG02 admin]$ lsnrctl start
 [oracle@Rod-DG02 admin]$ tnsping 10gpri

14.打開備庫
 SQL> startup nomount pfile='/u01/app/admin/orcl/pfile/initstandby.ora';
 SQL> ALTER DATABASE MOUNT STANDBY DATABASE; 
 SQL> create spfile from pfile='/u01/app/admin/orcl/pfile/initstandby.ora';
 SQL> alter database recover managed standby database disconnect from session;

15.打開主庫
 SQL> startup

16.測試是否OK
主庫:
 SQL> alter system switch logfile;

 主備庫執行:
 SQL> select FIRST_TIME,NEXT_TIME, APPLIED,SEQUENCE# from v$archived_log order by SEQUENCE#;

 FIRST_TIME NEXT_TIME APP SEQUENCE#
 ------------------- ------------------- --- ---- ------
 2007-10-16 11:39:33 2007-10-16 14:16:39 YES 5
 2007-10-16 14:16:39 2007-10-16 14:21:06 YES 6
 
 確認SEQUENCE#同步即可!

二、轉換模式由MAXIMIZE PERFORMANCE到MAXIMIZE PROTECTION

 1.在備機上:
  SQL> shutdown immediate;
  SQL> startup mount;

 2.在主機上
  SQL> shutdown immediate;
  SQL> startup mount;
  SQL> alter system set log_archive_dest_2='SERVICE=10gstandby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gstandby';
  SQL>alter database set standby database to maximize protection;
  SQL>alter database open;
  SQL> select protection_mode from v$database;

  PROTECTION_MODE
  --------------------
  MAXIMUM PROTECTION


 3.在備機上
 SQL> recover managed standby database disconnect from session;

 4.測試:
 在主機上執行SQL> alter system switch logfile;
 在備機上查看v$standby_log視圖
 SQL> select GROUP#,THREAD#,SEQUENCE#,USED,ARCHIVED,STATUS from v$standby_log;
  GROUP# THREAD# SEQUENCE# USED ARC STATUS
  ---------- ---------- ---------- ---------- - -- ----------
  4 1 36 59392 YES ACTIVE
  5 1 0 512 NO UNASSIGNED
  6 0 0 512 YES UNASSIGNED
  7 0 0 512 YES UNASSIGNED

 MAXIMIZE PROTECTION和MAXIMIZE AVAILABILITY模式下,備機不能先關閉,會出現如下錯誤
 SQL> shutdown immediate;
 ORA-01154: database busy. Open, close, mount, and dismount not allowed now

 正確的開關機順序是:
 關機:先關主機,後關備機
 開機:先開備機,後開主機

三、主庫和備庫的switchover.

 注意:Swithover時只能先從Primary切到Standby,再從Standby切到Primary.

 1.準備原主庫是否有standby redo log,上面1.4已建好了。

 2.準備主庫和備庫的參數文件,最好就是將兩個數據庫的參數文件互換,在兩台機器上同時保留主庫和備庫的參數文件。
  [oracle@Rod-DG02 /]$ cd /u01/app/admin/orcl/pfile
  [oracle@Rod-DG02 pfile]scp -p oracle@172.17.61.160:/oracle/admin/orcl/pfile/initprim.ora initprimary.ora
  [oracle@Rod-DG02 pfile]scp -p initstandby.ora oracle@172.17.61.160:/oracle/admin/orcl/pfile/

 3.從primary切換到standby
  connect / as sysdba
  SQL> alter database commit to switchover to physical standby with session shutdown;
  SQL> shutdown
  SQL> startup mount pfile=/oracle/admin/orcl/pfile/initstandby.ora;
  SQL> recover managed standby database disconnect;

 4.啟動新備庫端的Listener (port=1522)
  [oracle@Rod-DG01 admin]$ lsnrctl stop
  [oracle@Rod-DG01 admin]$ lsnrctl start LISTENER1

 5.從standby切換到primary
  connect / as sysdba
  SQL> alter database commit to switchover to primary;
  SQL> shutdown
  SQL> startup pfile=/oracle/admin/orcl/pfile/initprimary.ora
  startup pfile=/home/oracle/switchover/initprim.ora

 6.啟動新主庫端的Listener (port=1522)
  [oracle@Rod-DG02 admin]$ lsnrctl stop
  [oracle@Rod-DG02 admin]$ lsnrctl start LISTENER1

7.OK

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

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2012-08-02

  • 博文量
    5
  • 访问量
    9118