Oracle主備庫配置過程

實驗環境
作業系統：RHEL AS4
資料庫：Version 10.2.0.1.0
一台機器上，裝兩個資料庫，來進行主備切換實驗。多台機器道理相同。
整個操作過程為我實驗成功後，根據記憶整理，如有遺漏，請指正。

一、安裝設定詳細過程
正常安裝一個資料庫，可以用DBCA，也可以手動建庫，手動建庫資料檔案可以小一點兒，實驗速度會快一點兒。
資料庫名：beijing，初始的主庫，備庫名：shanghai

1.產生pfile，根據主備庫的要求，進行參數修改
SQL> create pfile='/dbfile/initbeijing.ora' from spfile;
#預設帶的參數
*.compatible='10.2.0.1.0'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='beijing'
*.db_recovery_file_dest='/Oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
log_archive_format=%t_%s_%r.arc

#DUMP目錄，也是預設帶的，需要修改
*.audit_file_dest='/oracle/admin/beijing/adump'
*.background_dump_dest='/oracle/admin/beijing/bdump'
*.user_dump_dest='/oracle/admin/beijing/udump'
*.core_dump_dest='/oracle/admin/beijing/cdump'

#主備切換需要增加的參數
db_unique_name=beijing
log_archive_config='dg_config=(beijing,shanghai)'
control_files='/dbfile/datafile/beijing/control01.ctl',
'/dbfile/datafile/beijing/control02.ctl',
'/dbfile/datafile/beijing/control03.ctl'
log_archive_dest_1=
'location=/dbfile/logfile/beijing/ valid_for=(all_logfiles,all_roles) db_unique_name=beijing'
log_archive_dest_2=
'service=shanghai lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=shanghai'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
standby_file_management=auto

#切換時的一些設定
fal_server=shanghai
fal_client=beijing
db_file_name_convert='shanghai','beijing'
log_file_name_convert='shanghai','beijing'

2.產生spfile，要在資料庫關閉狀態下進行
SQL> create spfile from pfile='/dbfile/initbeijing.ora';

3.開啟到mount狀態，啟動歸檔模式
SQL> alter database mount;
SQL> alter database archivelog;

4.建立備庫用的控制檔案
SQL> alter database create standby controlfile as '/dbfile/beijing.ctl';

5.關閉資料庫，準備備庫所需的檔案，替換控制檔案，然後啟動主庫(beijing)
SQL> shutdown immediate;
拷貝資料檔案，相當於冷備，替換控制檔案
[oracle@linux ~]$ cp -r /dbfile/datafile/beijing /dbfile/datafile/shanghai
[oracle@linux ~]$ cp /dbfile/beijing.ctl /dbfile/datafile/shanghai/control01.ctl
[oracle@linux ~]$ cp /dbfile/beijing.ctl /dbfile/datafile/shanghai/control02.ctl
[oracle@linux ~]$ cp /dbfile/beijing.ctl /dbfile/datafile/shanghai/control03.ctl
建立歸檔日誌目錄
[oracle@linux ~]$ mkdir -p /dbfile/datafile/shanghai
建立dump目錄，簡單起見，拷貝一下，因為有好幾個dump目錄。
[oracle@linux ~]$ cp -r $ORACLE_BASE/admin/beijing $ORACLE_BASE/admin/shanghai
啟動主庫
SQL> startup

6.修改tnsnames.ora，在$ORACLE_HOME/network/admin目錄中，如果沒有，就建立一個
在檔案中增加以下內容，也就是兩個庫的別名
shanghai =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = shanghai)
    )
  )
beijing =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = beijing)
    )
  )
 
7.啟動監聽，然後測試一下別名
[oracle@linux ~]$ lsnrctrl start
[oracle@linux ~]$ tnsping beijing
[oracle@linux ~]$ tnsping shanghai

8.建立備庫的密碼檔案，庫名為shanghai
[oracle@linux ~]$ orapwd $ORACLE_HOME/dbs/orapwshanghai password=oracle

9.使用主庫的資料檔案（剛才拷貝了），建立備庫的參數檔案
修改主庫的參數檔案initbeijing.ora，儲存為initshanghai.ora
*.compatible='10.2.0.1.0'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='beijing'
*.db_recovery_file_dest='/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
log_archive_format=%t_%s_%r.arc

*.audit_file_dest='/oracle/admin/shanghai/adump'
*.background_dump_dest='/oracle/admin/shanghai/bdump'
*.user_dump_dest='/oracle/admin/shanghai/udump'
*.core_dump_dest='/oracle/admin/shanghai/cdump'

db_unique_name=shanghai
log_archive_config='dg_config=(beijing,shanghai)'
control_files='/dbfile/datafile/shanghai/control01.ctl',
'/dbfile/datafile/shanghai/control02.ctl',
'/dbfile/datafile/shanghai/control03.ctl'
log_archive_dest_1=
'location=/dbfile/logfile/shanghai/ valid_for=(all_logfiles,all_roles) db_unique_name=shanghai'
log_archive_dest_2=
'service=beijing lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=beijing'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable

fal_server=beijing
fal_client=shanghai
db_file_name_convert='beijing','shanghai'
log_file_name_convert='beijing','shanghai'
standby_file_management=auto

10.啟動備庫(shanghai)，使用pfile(剛才修改的)產生spfile
[oracle@linux ~]$ export ORACLE_SID=shanghai
[oracle@linux ~]$ sqlplus / as sysdba
SQL> create spfile from pfile='/dbfile/initshanghai.ora';

11.啟動備庫到mount狀態，然後啟動redo應用
SQL> startup mount
SQL> alter database recover managed standby database disconnect from session

12.檢查日誌，看設定是否成功
主庫切換日誌
SQL> alter system switch logfile;
備庫查看有無新收到的日誌
SQL> select sequence#,first_time,next_time from v$archived_log order by 1;

SEQUENCE# FIRST_TIME          NEXT_TIME
---------- ------------------- -------------------
        16 2012-01-10 08:51:41 2012-01-10 08:51:45
        16 2012-01-10 08:51:41 2012-01-10 08:51:45
        17 2012-01-10 08:51:45 2012-01-10 08:51:48
        17 2012-01-10 08:51:45 2012-01-10 08:51:48
        18 2012-01-10 08:51:48 2012-01-10 08:52:26
        18 2012-01-10 08:51:48 2012-01-10 08:52:26
        19 2012-01-10 08:52:26 2012-01-10 08:53:57
        19 2012-01-10 08:52:26 2012-01-10 08:53:57
        20 2012-01-10 08:53:57 2012-01-10 08:54:06
        20 2012-01-10 08:53:57 2012-01-10 08:54:06
也可以到/dbfile/logfile/shanghai目錄中，看一下有無歸檔記錄檔

1 2 3 下一頁