在Oracle 10g下單機Physical Standby

參見： http://aluocp.itpub.net/post/17417/457564

作業系統: Windows
Oracle版本：10.2.0.1

主庫SID: ALEX
從庫SID: ALUOCP


一、建立過程：

1、在主庫執行force logging
SQL> alter database force logging;


2、設定主庫的引數檔案和從庫的引數檔案：
主庫確認以下引數：
db_name='alex'
DB_UNIQUE_NAME='alex'
control_files='D:\oracle\oradata\alex\control01.ctl','D:\oracle\oradata\alex\control02.ctl','D:\oracle\oradata\alex\control03.ctl'
FAL_CLIENT='alex'
FAL_SERVER='aluocp'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(alex,aluocp)'
LOG_ARCHIVE_DEST_1='LOCATION=D:\oracle\oradata\alex\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=alex'
LOG_ARCHIVE_DEST_2='SERVICE=aluocp LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=aluocp'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
LOG_ARCHIVE_MAX_PROCESSES=5
STANDBY_ARCHIVE_DEST='D:\oracle\oradata\alex\standbyarchive'
STANDBY_FILE_MANAGEMENT='AUTO'
DB_FILE_NAME_CONVERT='D:\oracle\oradata\aluocp','D:\oracle\oradata\alex'
LOG_FILE_NAME_CONVERT='D:\oracle\oradata\aluocp\archive','D:\oracle\oradata\alex\archive'

從庫確認以下引數：
db_name='alex'
DB_UNIQUE_NAME='aluocp'
control_files='D:\oracle\oradata\aluocp\control01.ctl','D:\oracle\oradata\aluocp\control02.ctl','D:\oracle\oradata\aluocp\control03.ctl'
FAL_CLIENT='aluocp'
FAL_SERVER='alex'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(alex,aluocp)'
LOG_ARCHIVE_DEST_1='LOCATION=D:\oracle\oradata\aluocp\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=aluocp'
LOG_ARCHIVE_DEST_2='SERVICE=alex LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=alex'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
LOG_ARCHIVE_MAX_PROCESSES=5
STANDBY_ARCHIVE_DEST='D:\oracle\oradata\aluocp\standbyarchive'
STANDBY_FILE_MANAGEMENT='AUTO'
DB_FILE_NAME_CONVERT='D:\oracle\oradata\alex','D:\oracle\oradata\aluocp'
LOG_FILE_NAME_CONVERT='D:\oracle\oradata\alex\archive','D:\oracle\oradata\aluocp\archive'


3、在主庫開啟archivelog和flashback：
SQL> create spfile from pfile;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database flashback on;
SQL> alter database open;


4、在主庫新增standby redo log：
考慮新增的公式：(maximum number of logfiles for each thread + 1) * maximum number of threads
standby redo log至少比online redo log多一組
SQL> alter database add standby LOGFILE GROUP 4 ('D:\ORACLE\ORADATA\ALEX\REDO04.log') size 50m;
SQL> alter database add standby LOGFILE GROUP 5 ('D:\ORACLE\ORADATA\ALEX\REDO05.log') size 50m;
SQL> alter database add standby LOGFILE GROUP 6 ('D:\ORACLE\ORADATA\ALEX\REDO06.log') size 50m;
SQL> alter database add standby LOGFILE GROUP 7 ('D:\ORACLE\ORADATA\ALEX\REDO07.log') size 50m;


5、Copy主庫所有資料檔案和redo log檔案到從庫目錄

                                    
6、在主庫Rename資料檔案和日誌檔案：                                 
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\SYSTEM01.DBF' to 'D:\ORACLE\ORADATA\aluocp\SYSTEM01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\UNDOTBS01.DBF' to 'D:\ORACLE\ORADATA\aluocp\UNDOTBS01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\SYSAUX01.DBF' to 'D:\ORACLE\ORADATA\aluocp\SYSAUX01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\USERS01.DBF' to 'D:\ORACLE\ORADATA\aluocp\USERS01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\TEMP01.DBF' to 'D:\ORACLE\ORADATA\aluocp\TEMP01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\REDO01.LOG' to 'D:\ORACLE\ORADATA\aluocp\REDO01.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\REDO02.LOG' to 'D:\ORACLE\ORADATA\aluocp\REDO02.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\REDO03.LOG' to 'D:\ORACLE\ORADATA\aluocp\REDO03.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\REDO04.LOG' to 'D:\ORACLE\ORADATA\aluocp\REDO04.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\REDO05.LOG' to 'D:\ORACLE\ORADATA\aluocp\REDO05.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\REDO06.LOG' to 'D:\ORACLE\ORADATA\aluocp\REDO06.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\REDO07.LOG' to 'D:\ORACLE\ORADATA\aluocp\REDO07.LOG';

                                    
7、在主庫生成standby control file：
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\oracle\oradata\STANDBY.CTL';


8、在主庫Rename資料檔案和日誌檔案到原始：
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\SYSTEM01.DBF' to 'D:\ORACLE\ORADATA\alex\SYSTEM01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\UNDOTBS01.DBF' to 'D:\ORACLE\ORADATA\alex\UNDOTBS01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\SYSAUX01.DBF' to 'D:\ORACLE\ORADATA\alex\SYSAUX01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\USERS01.DBF' to 'D:\ORACLE\ORADATA\alex\USERS01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\TEMP01.DBF' to 'D:\ORACLE\ORADATA\alex\TEMP01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\REDO01.LOG' to 'D:\ORACLE\ORADATA\alex\REDO01.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\REDO02.LOG' to 'D:\ORACLE\ORADATA\alex\REDO02.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\REDO03.LOG' to 'D:\ORACLE\ORADATA\alex\REDO03.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\REDO04.LOG' to 'D:\ORACLE\ORADATA\alex\REDO04.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\REDO05.LOG' to 'D:\ORACLE\ORADATA\alex\REDO05.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\REDO06.LOG' to 'D:\ORACLE\ORADATA\alex\REDO06.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\REDO07.LOG' to 'D:\ORACLE\ORADATA\alex\REDO07.LOG';


9、把STANDBY.CTL移至從庫目錄，重新命名並做多功


10、建立從庫的服務和密碼檔案：
cmd> oradim -new -sid ALUOCP -intpwd aluocp -startmode m


11、設定主庫和從庫的listener和tnsnames，並啟動


12、啟動從庫，自動建立online redo log：
SQL> create spfile from pfile;
SQL> startup mount;
SQL> alter database recover managed standby database disconnect from session;


13、在主庫切換一次日誌，使備庫中記錄的原主庫的standby redo log資訊被清除：
SQL> alter system switch logfile;
稍等一會，等在主庫中查詢v$standby_log沒有原來主庫standby log的資訊返回


14、在從庫建立standby redo log
SQL> alter database recover managed standby database cancel;
SQL> alter database add standby LOGFILE GROUP 4 ('D:\ORACLE\ORADATA\ALUOCP\REDO04.log') size 50m;
SQL> alter database add standby LOGFILE GROUP 5 ('D:\ORACLE\ORADATA\ALUOCP\REDO05.log') size 50m;
SQL> alter database add standby LOGFILE GROUP 6 ('D:\ORACLE\ORADATA\ALUOCP\REDO06.log') size 50m;
SQL> alter database add standby LOGFILE GROUP 7 ('D:\ORACLE\ORADATA\ALUOCP\REDO07.log') size 50m;
SQL> alter database recover managed standby database disconnect from session;


15、檢查主庫和從庫的狀態：
檢查資料庫角色和狀態
SQL> select database_role, switchover_status from v$database;

檢查archive log的應用狀態
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

檢查archive log的遺失
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

檢查archive log的路徑是否可用
SQL> select dest_name,status,error from v$archive_dest;


二、測試過程

1、模式切換
1.1 啟動standby database到recover manage模式
SQL>alter database recover managed standby database disconnect from session;

1.2 啟動到實時日誌應用模式
SQL>alter database recover managed standby database using current logfile;
(之前需要先建立standby logfile,使用命令，同時還需要修改主庫的引數LOG_ARCHIVE_DEST_2= 'SERVICE=ALUOCP LGWR')

1.3 切換standby database到read only模式
SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;

1.4 切換回recover manage模式
SQL> alter database recover managed standby database disconnect from session;


2、Switchover
2.1 在主庫上
SQL>select switchover_status from v$database

2.2 switchover_status的值如果是To standby，可以直接switchover：
SQL> alter database commit to switchover to physical standby;
SQL> shutdowm immediate;
SQL> startup mount;

如果是sessions active，則需要在switchover的命令後面加上with session shutdown：
SQL> alter database commit to switchover to physical standby with session shutdown;
SQL> shutdowm immediate;
SQL> startup mount;

2.3 在目標從庫上
SQL> select switchover_status from v$database
switchover_status的值入股是To primary，可以直接switchover：
SQL> alter database commit to switchover to primary;
SQL> shutdown immediate;
SQL> startup;

如果是sessions active，則需要在switchover的命令後面加上with session shutdown：
SQL> alter database commit to switchover to primary with session shutdown;
SQL> shutdown immediate;
SQL> startup;


3、Failover
3.1 在目標從庫上，檢查遺失的archive log：
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

3.2 如有遺失，Copy到從庫目錄並應用：
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

3.3 再次檢查遺失的archive log：
SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST FROM V$ARCHIVED_LOG;

3.4 關閉從庫的recover模式：
SQL> alter database recover managed standby database finish;
OR
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
OR
SQL> alter database recover managed standby database finish skip standby logfile;

3.5 切換從庫為主庫：
SQL> alter database commit to switchover to primary;
SQL> shutdown immediate;
SQL> startup;


4、使用Flashback恢復failover的主庫
4.1 在新的主庫上查詢切換時候的SCN號：
SQL> SELECT standby_became_primary_scn FROM v$database;

4.2 恢復舊主庫後，Mount：
SQL> STARTUP MOUNT;

4.3 Flashback舊主庫到步驟4.1查詢得的SCN：
SQL> FLASHBACK DATABASE TO SCN ;

4.4 在舊主庫上，轉換control file為standby control file：
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

4.5 恢復舊主庫為新從庫：
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

4.6 在新主庫上，設定日誌傳輸到舊主庫（新從庫），並確認狀態：
SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE,DESTINATION, ERROR, SRL FROM V$ARCHIVE_DEST_STATUS;

4.7 在新主庫上，歸檔日誌到新從庫：
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

4.8 在新從庫上開啟recover模式：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
如果是real-time應用:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

[ 本帖最後由 aluocp 於 2008-3-18 20:10 編輯 ]