Oracle 9i 配置備用資料庫步驟

1.主庫設定成歸檔模式

2.複製主庫的表空間檔案、Redo、複製到備庫的對應目錄

3.複製主庫的引數檔案到備庫
/u02/app/oracle/admin/devdb1/script/init.ora

4.從庫生成密碼檔案
/u01/app/oracle/ora920/bin/orapwd file=/u01/app/oracle/ora920/dbs/orapwdevdb1 password=change_on_install

5.主庫生成備用資料庫專用控制檔案
alter database create standby controlfile as '/u02/oradata/devdb1/stdcotrl.ctl';

6. 將備用資料庫專用控制檔案複製到備用資料庫（並複製3份）
cp stdcotrl.ctl stdcotrl01.ctl
cp stdcotrl.ctl stdcotrl02.ctl
cp stdcotrl.ctl stdcotrl03.ctl
rm stdcotrl.ctl

7.修改備用資料庫引數檔案
cp /u01/app/oracle/admin/devdb1/scripts/init.ora /u01/app/oracle/admin/devdb1/scripts/initstandby.ora
vi /u01/app/oracle/admin/devdb1/scripts/initstandby.ora

將 control_files=("/u02/oradata/devdb1/control01.ctl", "/u02/oradata/devdb1/control02.ctl", "/u02/oradata/devdb1/control03.ctl") 這行註釋掉
增加下列行
utl_file_dir='/u01/app/oracle'
standby_archive_dest='/u02/oradata/devdb1/stdarch'
fal_server='PRIMARY'
fal_client='STANDBY'
standby_file_management='AUTO'
control_files=("/u02/oradata/devdb1/stdcotrl01.ctl", "/u02/oradata/devdb1/stdcotrl02.ctl", "/u02/oradata/devdb1/stdcotrl03.ctl")

7.修改主庫及備用資料庫的TNSNAMES.ora新增下列內容
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.131)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = devdb1.freelynet.com)
)
)

STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.132)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = devdb1.freelynet.com)
)
)

主庫備庫啟動 lsnrctl start
主庫測試 TNS
tnsping primary
tnsping standby
備庫測試 TNS
tnsping primary
tnsping standby

8. 啟動備用資料庫
startup nomount pfile="/u01/app/oracle/admin/devdb1/scripts/initstandby.ora";
alter database mount standby database; //切換到備用資料庫模式並mount
alter database recover managed standby database disconnect from session; //切換到管理模式，自動應用歸檔日誌

9. 主庫設定歸檔日誌路徑
alter system set log_archive_dest_2='service=standby mandatory reopen=60';

10. 主庫進行日誌切換測試

alter system switch logfile;

10. 主庫的alter 檔案中應有下列內容
Tue Mar 18 05:46:45 2008
ARC0: Evaluating archive log 3 thread 1 sequence 6
ARC0: Beginning to archive log 3 thread 1 sequence 6
Creating archive destination LOG_ARCHIVE_DEST_2: 'standby'
Tue Mar 18 05:46:45 2008
Thread 1 advanced to log sequence 7
Current log# 1 seq# 7 mem# 0: /u02/oradata/devdb1/redo01.log
Tue Mar 18 05:46:45 2008
Creating archive destination LOG_ARCHIVE_DEST_1: '/u02/oradata/devdb1/archive/1_6.dbf'
ARC0: Completed archiving log 3 thread 1 sequence 6

11.備庫的alter 檔案中應有下列內容
Media Recovery Waiting for thread 1 seq# 6
Media Recovery Log /u02/oradata/devdb1/stdarch/1_6.dbf

12.備庫關閉
recover managed standby database cancel; 退出自動應用模式
shutdown immediate

13. 備庫切換到只讀模式
recover managed standby database cancel; 退出自動應用模式
shutdown immediate

14. 備庫從只讀回到自動應用
alter database close
alter database recover managed standby database disconnect from session;

15. 主庫修改引數檔案
cp init.ora initprimary.ora
vi initprimary.ora
新增下列行

utl_file_dir='/u01/app/oracle'
standby_archive_dest='/u02/oradata/devdb1/stdarch'
fal_server='STANDBY'
fal_client='PRIMARY'
standby_file_management='AUTO'

16. 主庫切換到備用模式

alter database commit to switchover to physical standby with session shutdown;
shutdown immediate;
startup nomount pfile="/u01/app/oracle/admin/devdb1/scripts/initprimary.ora";
alter database mount standby database;
alter database recover managed standby database disconnect from session;

17. 備庫切換到主模式
alter database commit to switchover to primary
shutdown immediate;
startup pfile="/u01/app/oracle/admin/devdb1/scripts/initstandby.ora";

18. 備庫切換到主模式後設定歸檔日誌傳送路徑
alter system set log_archive_dest_2='service=primary mandatory reopen=60'; //將日誌發回 主庫

19.檢查主庫、備庫的alter 檔案

主庫
Tue Mar 18 05:58:14 2008
Media Recovery Log /u02/oradata/devdb1/stdarch/1_8.dbf
Media Recovery Waiting for thread 1 seq# 9

備庫
Creating archive destination LOG_ARCHIVE_DEST_2: 'primary'
Creating archive destination LOG_ARCHIVE_DEST_1: '/u02/oradata/devdb1/archive/1_8.dbf'
ARC1: Completed archiving log 1 thread 1 sequence 8

20. 備庫切換回備用模式
alter database commit to switchover to physical standby with session shutdown;
shutdown immediate;
startup nomount pfile="/u01/app/oracle/admin/devdb1/scripts/initstandby.ora";
alter database mount standby database;
alter database recover managed standby database disconnect from session;

21.主庫切換到主模式
alter database commit to switchover to primary
shutdown immediate;
startup pfile="/u01/app/oracle/admin/devdb1/scripts/initprimary.ora";

22. 主庫設定歸檔日誌傳送路徑
alter system set log_archive_dest_2='service=standby mandatory reopen=60';

[@more@]