9i 克隆+data guard 實現資料庫搬遷
此案例是透過RDD(華為自己封裝的DD指令碼）+DATA GUARD來實現查詢庫搬遷的；
操作步驟說明：
1.克隆軟體
2.開歸檔
3.源庫開啟熱備begain backup （9i 只能對錶空間，不能對全庫）
4.RDD複製資料檔案到目錄庫；
5.源庫關閉熱備end backup;
6.源庫生成standby 控制檔案；
7.copy生成的standby控制檔案與密碼檔案、引數檔案、歸檔日誌到目標端
8.搭建data guard ，啟用到managed reocver 模式；讓系統自動追加；---注：此次data guard搭建目標端RAC只有其中一個節點進行日誌接受並應用；
說明：fal_tcx3a,fal_tcx3b表示主庫，fal_c4ocx3a表示備庫。
主庫：
1.開啟日誌
ALTER DATABASE FORCE LOGGING;
select log_mode,force_logging from v$database;
2.確保開啟歸檔且log_archive_start=true
3.配置系統引數
alter system set remote_archive_enable=TRUE scope=both;
alter system set standby_archive_dest='/arch5' sid='ocx3a' scope=both;
alter system set log_archive_dest_2='service=fal_tcx3a arch reopen=30 max_failure=10' scope=both;
alter system set standby_file_management=AUTO scope=both;
主庫：
alter system set fal_client='fal_tcx3a' scope=both sid='ocx3a';
alter system set fal_client='fal_tcx3b' scope=both sid='ocx3b';
alter system set fal_server='fal_c4ocx3a' scope=both sid='ocx3a';
alter system set fal_server='fal_c4ocx3a' scope=both sid='ocx3b';
4.網路netwok net配置
###DataGard####
fal_tcx3a=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.19.242.60)(PORT = 1521)))
(CONNECT_DATA =(SID = ocx3a))
)
fal_tcx3b=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.19.242.62)(PORT = 1521)))
(CONNECT_DATA =(SID = ocx3b))
)
fal_c4ocx3a=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.17.248.181)(PORT = 1521)))
(CONNECT_DATA =(SID = ocx3a))
)
fal_c4ocx3b=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.17.248.182)(PORT = 1521)))
(CONNECT_DATA =(SID = ocx3b))
)
5.填加standby redo log (由於此次只是採用ARCH 程式而非LGWR進行歸檔，所以不需要建standby redo )
alter database add standby logfile group 13 ('/arch5/standby/redo1_13.arc') size 1024m;
alter database add standby logfile group 14 ('/arch5/standby/redo1_14.log') size 1024m;
alter database add standby logfile group 15 ('/arch5/standby/redo1_15.arc') size 1024m;
alter database add standby logfile group 16 ('/arch5/standby/redo1_16.log') size 1024m;
alter database add standby logfile group 17 ('/arch5/standby/redo1_17.log') size 1024m;
alter database add standby logfile group 18 ('/arch5/standby/redo1_18.log') size 1024m;
alter database add standby logfile group 19 ('/arch5/standby/redo1_19.log') size 1024m;
6.生產上建立pfile.ora給standby 庫
create pfile='/arch5/standby_pfile.ora' from spfile;
scp -rp /arch5/pfile.ora root@10.17.248.181:/arch5
7.複製密碼檔案到standby 庫
8.rdd複製完資料檔案後切換兩次歸檔
alter system archive log current;
alter system archive log current;
9.生成standby 控制檔案（注：）
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/arch5/standby_control01.ctl';
scp -rp /arch5/standby_control01.ctl root@10.17.248.181:/arch5
10.考貝所有歸檔日誌檔案到備庫
備庫
1.備庫歸檔目錄/arch5
cd /arch5
2.網路netwok net配置
###DataGard####
fal_tcx3a=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.19.242.60)(PORT = 1521)))
(CONNECT_DATA =(SID = ocx3a))
)
fal_tcx3b=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.19.242.62)(PORT = 1521)))
(CONNECT_DATA =(SID = ocx3b))
)
fal_c4ocx3a=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.17.248.181)(PORT = 1521)))
(CONNECT_DATA =(SID = ocx3a))
)
fal_c4ocx3b=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.17.248.182)(PORT = 1521)))
(CONNECT_DATA =(SID = ocx3a))
)
3.建立spfile
create spfile='/dev/rspfilelv' from pfile='/arch5/standby_pfile.ora';
echo 'spfile='/dev/rspfilelv'' >initocx3a.ora
echo 'spfile='/dev/rspfilelv'' >initocx3b.ora
4.建立控制檔案
dd if=/arch5/standby_control01.ctl of=/dev/rctllv1
dd if=/arch5/standby_control01.ctl of=/dev/rctllv2
dd if=/arch5/standby_control01.ctl of=/dev/rctllv3
5.修改引數檔案新增如下內容
#added by hurp for data guard 2015-03-25
*.control_files='/dev/rctllv1','/dev/rctllv2','/dev/rctllv3'
*.standby_file_management=AUTO
*.remote_archive_enable=TRUE
#同一個目錄就不用轉換了
#*.db_file_name_convert=('/oradata/shrnc/','/oradata/shrnc/standby/')
#*.log_file_name_convert=('/oradata/shrnc/','/oradata/shrnc/standby/')
*.standby_archive_dest='/arch5'
*.log_archive_dest_2='service=fal_tcx3a arch reopen=30 max_failure=10'
alter system set log_archive_dest_2='service=fal_tcx3a arch reopen=30 max_failure=10' scope=both;
#沒有使用broker
#ocx3b.log_archive_config='dg_config=(shrnc,standby)'
ocx3a.fal_client='fal_c4ocx3a'
ocx3b.fal_client='fal_c4ocx3a'
ocx3a.fal_server='fal_tcx3a'
ocx3b.fal_server='fal_tcx3b'
備庫
alter system set fal_client='fal_c4ocx3a' scope=both sid='ocx3a';
alter system set fal_client='fal_c4ocx3a' scope=both sid='ocx3b';
alter system set fal_server='fal_tcx3a' scope=both sid='ocx3a';
alter system set fal_server='fal_tcx3b' scope=both sid='ocx3b';
6.備庫啟動到nomount狀態
startup nomount
5.啟動資料庫到
ALTER DATABASE MOUNT STANDBY DATABASE;
6.恢復資料庫
recover standby database;
7.啟動應用日誌執行恢復（只能在一個節點上執行）
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
備庫
alter system set fal_client='fal_c4ocx3a' scope=both sid='ocx3a';
alter system set fal_client='fal_c4ocx3a' scope=both sid='ocx3b';
alter system set fal_server='fal_tcx3a' scope=both sid='ocx3a';
alter system set fal_server='fal_tcx3b' scope=both sid='ocx3b';
主庫：
alter system set fal_client='fal_tcx3a' scope=both sid='ocx3a';
alter system set fal_client='fal_tcx3b' scope=both sid='ocx3b';
alter system set fal_server='fal_c4ocx3a' scope=both sid='ocx3a';
alter system set fal_server='fal_c4ocx3a' scope=both sid='ocx3b';
alter system set log_archive_dest_state_2='defer' scope=both;
alter system set log_archive_dest_state_2='enable' scope=both;
alter system set log_archive_dest_2='' scope=both;
alter system set standby_archive_dest='/arch5' scope=both;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
alter system resiter
alter database register logfile '/arch5/cx3a20000296936.ARC';
主備切換：
1.原備庫Tns檔案配置主庫連線（搭建時已分別在兩個節點上面已經設定了）
2.原備庫日誌檔案傳輸引數設定（如下已以設定）
alter system set log_archive_dest_2='service=fal_tcx3a arch reopen=30 max_failure=10' scope=both;
3.原主庫增加standby redo log （注：本次由於沒有采用LGWR程式進行日誌傳輸因此不需要配置此步）
alter database add standby logfile group 13 ('/arch5/standby/redo1_13.arc') size 1024m;
alter database add standby logfile group 14 ('/arch5/standby/redo1_14.log') size 1024m;
alter database add standby logfile group 15 ('/arch5/standby/redo1_15.arc') size 1024m;
alter database add standby logfile group 16 ('/arch5/standby/redo1_16.log') size 1024m;
alter database add standby logfile group 17 ('/arch5/standby/redo1_17.log') size 1024m;
alter database add standby logfile group 18 ('/arch5/standby/redo1_18.log') size 1024m;
alter database add standby logfile group 19 ('/arch5/standby/redo1_19.log') size 1024m;
alter database drop standby logfile group 13;
alter database drop standby logfile group 14;
alter database drop standby logfile group 15;
alter database drop standby logfile group 16;
alter database drop standby logfile group 17;
alter database drop standby logfile group 18;
alter database drop standby logfile group 19;
4.switchover 主備正式切換
注：首先RAC主備資料庫各保留一個節點，其它節點正常關閉；
主庫上操作:(主-->備)
Step 1 Verify that the primary database can be switched to the standby role.
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
Step 2 Initiate the switchover on the primary database.
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
Step 3 Shut down and then mount the former primary database.
SQL> SHUTDOWN ABORT;
SQL>STARTUP nomount;
SQL>ALTER DATABASE MOUNT STANDBY DATABASE;
備庫上操作:(備-->主)
Step 4 Verify that the switchover target is ready to be switched to the primary role.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO_PRIMARY
Step 5 Switch the target physical standby database role to the primary role.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Step 6 Open the new primary database.
SQL>ALTER DATABASE OPEN;
Step 7 modify fal_client and fal_server
切換後主庫：
alter system set fal_client='fal_c4ocx3a' scope=both sid='ocx3a';
alter system set fal_client='fal_c4ocx3b' scope=both sid='ocx3b';
alter system set fal_server='fal_tcx3a' scope=both sid='ocx3a';
alter system set fal_server='fal_tcx3a' scope=both sid='ocx3b';
切換後備庫
alter system set fal_client='fal_tcx3a' scope=both sid='ocx3a';
alter system set fal_client='fal_tcx3a' scope=both sid='ocx3b';
alter system set fal_server='fal_c4ocx3a' scope=both sid='ocx3a';
alter system set fal_server='fal_c4ocx3b' scope=both sid='ocx3b';
主庫上操作:(主-->備)
Step 8 Start Redo Apply on the new physical standby database.
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
問題排查及日誌檢視：
－－在standby上判斷日誌是否歸檔並應用
SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ#,ERROR FROM V$ARCHIVE_DEST_STATUS;
－－在standby上判斷最近哪個log SEQUENCE被應用了
SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;
SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
建議平時用這種方法查近應用的歸檔
select l.thread#, l.applied, max( l.sequence# ) max_seq#
from v$archived_log l, v$database d
where l.resetlogs_change# = d.resetlogs_change#
group by l.thread#, l.applied
order by l.applied, l.thread#;
－－在standby上檢視歸檔日誌建立者或位置
SELECT NAME, CREATOR, SEQUENCE#, APPLIED, COMPLETION_TIME FROM V$ARCHIVED_LOG;
－－在standby上檢視日誌歸檔歷史
SELECT FIRST_TIME, FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# FROM V$LOG_HISTORY;
備庫
select * from v$dataguard_status;
檢視datauard的日誌應用是否有延遲
select * from v$dataguard_stats
---檢視standby 管理程式
select inst_id,process,pid,status,THREAD#,SEQUENCE# from gv$managed_standby;
SELECT PROCESS ,STATUS ,THREAD#,SEQUENCE#,BLOCKS,BLOCK# FROM GV$MANAGED_STANDBY;
－－判斷哪個日誌沒有被standby site接受
SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM
(SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL
WHERE
LOCAL.SEQUENCE# NOT IN
(SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND
THREAD# = LOCAL.THREAD#);
錯誤：
Failed to request gap sequence. Thread #: 2, gap sequence: 297242-297242
All FAL server has been attempted.
解決方案：
從主庫COPY過去後註冊
alter database register logfile '/arch5/cx3b20000297242.ARC';
alter system set fal_client='fal_zw3a' scope=both sid='ozw3a';
alter system set fal_client='fal_zw3b' scope=both sid='ozw3b';
alter system set fal_client='fal_tcx3a' scope=both sid='ocx3a';
alter system set fal_client='fal_tcx3b' scope=both sid='ocx3b';
alter system set fal_server='fal_tcx3a' scope=both;
alter system set log_archive_dest_state_2='defer';
alter system set log_archive_dest_state_2='enable';
alter system set fal_server='fal_tcx3a' scope=both;
alter system set log_archive_dest_state_2='defer';
alter system set log_archive_dest_state_2='enable';
Failed to request gap sequence. Thread #: 1, gap sequence: 314793-314806
All FAL server has been attempted.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
alter system set log_archive_dest_2='service=fal_tcx3a arch reopen=30 max_failure=10' scope=both;
alter system set log_archive_dest_2='' scope=both;
操作步驟說明：
1.克隆軟體
2.開歸檔
3.源庫開啟熱備begain backup （9i 只能對錶空間，不能對全庫）
4.RDD複製資料檔案到目錄庫；
5.源庫關閉熱備end backup;
6.源庫生成standby 控制檔案；
7.copy生成的standby控制檔案與密碼檔案、引數檔案、歸檔日誌到目標端
8.搭建data guard ，啟用到managed reocver 模式；讓系統自動追加；---注：此次data guard搭建目標端RAC只有其中一個節點進行日誌接受並應用；
說明：fal_tcx3a,fal_tcx3b表示主庫，fal_c4ocx3a表示備庫。
主庫：
1.開啟日誌
ALTER DATABASE FORCE LOGGING;
select log_mode,force_logging from v$database;
2.確保開啟歸檔且log_archive_start=true
3.配置系統引數
alter system set remote_archive_enable=TRUE scope=both;
alter system set standby_archive_dest='/arch5' sid='ocx3a' scope=both;
alter system set log_archive_dest_2='service=fal_tcx3a arch reopen=30 max_failure=10' scope=both;
alter system set standby_file_management=AUTO scope=both;
主庫：
alter system set fal_client='fal_tcx3a' scope=both sid='ocx3a';
alter system set fal_client='fal_tcx3b' scope=both sid='ocx3b';
alter system set fal_server='fal_c4ocx3a' scope=both sid='ocx3a';
alter system set fal_server='fal_c4ocx3a' scope=both sid='ocx3b';
4.網路netwok net配置
###DataGard####
fal_tcx3a=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.19.242.60)(PORT = 1521)))
(CONNECT_DATA =(SID = ocx3a))
)
fal_tcx3b=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.19.242.62)(PORT = 1521)))
(CONNECT_DATA =(SID = ocx3b))
)
fal_c4ocx3a=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.17.248.181)(PORT = 1521)))
(CONNECT_DATA =(SID = ocx3a))
)
fal_c4ocx3b=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.17.248.182)(PORT = 1521)))
(CONNECT_DATA =(SID = ocx3b))
)
5.填加standby redo log (由於此次只是採用ARCH 程式而非LGWR進行歸檔，所以不需要建standby redo )
alter database add standby logfile group 13 ('/arch5/standby/redo1_13.arc') size 1024m;
alter database add standby logfile group 14 ('/arch5/standby/redo1_14.log') size 1024m;
alter database add standby logfile group 15 ('/arch5/standby/redo1_15.arc') size 1024m;
alter database add standby logfile group 16 ('/arch5/standby/redo1_16.log') size 1024m;
alter database add standby logfile group 17 ('/arch5/standby/redo1_17.log') size 1024m;
alter database add standby logfile group 18 ('/arch5/standby/redo1_18.log') size 1024m;
alter database add standby logfile group 19 ('/arch5/standby/redo1_19.log') size 1024m;
6.生產上建立pfile.ora給standby 庫
create pfile='/arch5/standby_pfile.ora' from spfile;
scp -rp /arch5/pfile.ora root@10.17.248.181:/arch5
7.複製密碼檔案到standby 庫
8.rdd複製完資料檔案後切換兩次歸檔
alter system archive log current;
alter system archive log current;
9.生成standby 控制檔案（注：）
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/arch5/standby_control01.ctl';
scp -rp /arch5/standby_control01.ctl root@10.17.248.181:/arch5
10.考貝所有歸檔日誌檔案到備庫
備庫
1.備庫歸檔目錄/arch5
cd /arch5
2.網路netwok net配置
###DataGard####
fal_tcx3a=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.19.242.60)(PORT = 1521)))
(CONNECT_DATA =(SID = ocx3a))
)
fal_tcx3b=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.19.242.62)(PORT = 1521)))
(CONNECT_DATA =(SID = ocx3b))
)
fal_c4ocx3a=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.17.248.181)(PORT = 1521)))
(CONNECT_DATA =(SID = ocx3a))
)
fal_c4ocx3b=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.17.248.182)(PORT = 1521)))
(CONNECT_DATA =(SID = ocx3a))
)
3.建立spfile
create spfile='/dev/rspfilelv' from pfile='/arch5/standby_pfile.ora';
echo 'spfile='/dev/rspfilelv'' >initocx3a.ora
echo 'spfile='/dev/rspfilelv'' >initocx3b.ora
4.建立控制檔案
dd if=/arch5/standby_control01.ctl of=/dev/rctllv1
dd if=/arch5/standby_control01.ctl of=/dev/rctllv2
dd if=/arch5/standby_control01.ctl of=/dev/rctllv3
5.修改引數檔案新增如下內容
#added by hurp for data guard 2015-03-25
*.control_files='/dev/rctllv1','/dev/rctllv2','/dev/rctllv3'
*.standby_file_management=AUTO
*.remote_archive_enable=TRUE
#同一個目錄就不用轉換了
#*.db_file_name_convert=('/oradata/shrnc/','/oradata/shrnc/standby/')
#*.log_file_name_convert=('/oradata/shrnc/','/oradata/shrnc/standby/')
*.standby_archive_dest='/arch5'
*.log_archive_dest_2='service=fal_tcx3a arch reopen=30 max_failure=10'
alter system set log_archive_dest_2='service=fal_tcx3a arch reopen=30 max_failure=10' scope=both;
#沒有使用broker
#ocx3b.log_archive_config='dg_config=(shrnc,standby)'
ocx3a.fal_client='fal_c4ocx3a'
ocx3b.fal_client='fal_c4ocx3a'
ocx3a.fal_server='fal_tcx3a'
ocx3b.fal_server='fal_tcx3b'
備庫
alter system set fal_client='fal_c4ocx3a' scope=both sid='ocx3a';
alter system set fal_client='fal_c4ocx3a' scope=both sid='ocx3b';
alter system set fal_server='fal_tcx3a' scope=both sid='ocx3a';
alter system set fal_server='fal_tcx3b' scope=both sid='ocx3b';
6.備庫啟動到nomount狀態
startup nomount
5.啟動資料庫到
ALTER DATABASE MOUNT STANDBY DATABASE;
6.恢復資料庫
recover standby database;
7.啟動應用日誌執行恢復（只能在一個節點上執行）
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
備庫
alter system set fal_client='fal_c4ocx3a' scope=both sid='ocx3a';
alter system set fal_client='fal_c4ocx3a' scope=both sid='ocx3b';
alter system set fal_server='fal_tcx3a' scope=both sid='ocx3a';
alter system set fal_server='fal_tcx3b' scope=both sid='ocx3b';
主庫：
alter system set fal_client='fal_tcx3a' scope=both sid='ocx3a';
alter system set fal_client='fal_tcx3b' scope=both sid='ocx3b';
alter system set fal_server='fal_c4ocx3a' scope=both sid='ocx3a';
alter system set fal_server='fal_c4ocx3a' scope=both sid='ocx3b';
alter system set log_archive_dest_state_2='defer' scope=both;
alter system set log_archive_dest_state_2='enable' scope=both;
alter system set log_archive_dest_2='' scope=both;
alter system set standby_archive_dest='/arch5' scope=both;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
alter system resiter
alter database register logfile '/arch5/cx3a20000296936.ARC';
主備切換：
1.原備庫Tns檔案配置主庫連線（搭建時已分別在兩個節點上面已經設定了）
2.原備庫日誌檔案傳輸引數設定（如下已以設定）
alter system set log_archive_dest_2='service=fal_tcx3a arch reopen=30 max_failure=10' scope=both;
3.原主庫增加standby redo log （注：本次由於沒有采用LGWR程式進行日誌傳輸因此不需要配置此步）
alter database add standby logfile group 13 ('/arch5/standby/redo1_13.arc') size 1024m;
alter database add standby logfile group 14 ('/arch5/standby/redo1_14.log') size 1024m;
alter database add standby logfile group 15 ('/arch5/standby/redo1_15.arc') size 1024m;
alter database add standby logfile group 16 ('/arch5/standby/redo1_16.log') size 1024m;
alter database add standby logfile group 17 ('/arch5/standby/redo1_17.log') size 1024m;
alter database add standby logfile group 18 ('/arch5/standby/redo1_18.log') size 1024m;
alter database add standby logfile group 19 ('/arch5/standby/redo1_19.log') size 1024m;
alter database drop standby logfile group 13;
alter database drop standby logfile group 14;
alter database drop standby logfile group 15;
alter database drop standby logfile group 16;
alter database drop standby logfile group 17;
alter database drop standby logfile group 18;
alter database drop standby logfile group 19;
4.switchover 主備正式切換
注：首先RAC主備資料庫各保留一個節點，其它節點正常關閉；
主庫上操作:(主-->備)
Step 1 Verify that the primary database can be switched to the standby role.
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
Step 2 Initiate the switchover on the primary database.
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
Step 3 Shut down and then mount the former primary database.
SQL> SHUTDOWN ABORT;
SQL>STARTUP nomount;
SQL>ALTER DATABASE MOUNT STANDBY DATABASE;
備庫上操作:(備-->主)
Step 4 Verify that the switchover target is ready to be switched to the primary role.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO_PRIMARY
Step 5 Switch the target physical standby database role to the primary role.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Step 6 Open the new primary database.
SQL>ALTER DATABASE OPEN;
Step 7 modify fal_client and fal_server
切換後主庫：
alter system set fal_client='fal_c4ocx3a' scope=both sid='ocx3a';
alter system set fal_client='fal_c4ocx3b' scope=both sid='ocx3b';
alter system set fal_server='fal_tcx3a' scope=both sid='ocx3a';
alter system set fal_server='fal_tcx3a' scope=both sid='ocx3b';
切換後備庫
alter system set fal_client='fal_tcx3a' scope=both sid='ocx3a';
alter system set fal_client='fal_tcx3a' scope=both sid='ocx3b';
alter system set fal_server='fal_c4ocx3a' scope=both sid='ocx3a';
alter system set fal_server='fal_c4ocx3b' scope=both sid='ocx3b';
主庫上操作:(主-->備)
Step 8 Start Redo Apply on the new physical standby database.
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
問題排查及日誌檢視：
－－在standby上判斷日誌是否歸檔並應用
SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ#,ERROR FROM V$ARCHIVE_DEST_STATUS;
－－在standby上判斷最近哪個log SEQUENCE被應用了
SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;
SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
建議平時用這種方法查近應用的歸檔
select l.thread#, l.applied, max( l.sequence# ) max_seq#
from v$archived_log l, v$database d
where l.resetlogs_change# = d.resetlogs_change#
group by l.thread#, l.applied
order by l.applied, l.thread#;
－－在standby上檢視歸檔日誌建立者或位置
SELECT NAME, CREATOR, SEQUENCE#, APPLIED, COMPLETION_TIME FROM V$ARCHIVED_LOG;
－－在standby上檢視日誌歸檔歷史
SELECT FIRST_TIME, FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# FROM V$LOG_HISTORY;
備庫
select * from v$dataguard_status;
檢視datauard的日誌應用是否有延遲
select * from v$dataguard_stats
---檢視standby 管理程式
select inst_id,process,pid,status,THREAD#,SEQUENCE# from gv$managed_standby;
SELECT PROCESS ,STATUS ,THREAD#,SEQUENCE#,BLOCKS,BLOCK# FROM GV$MANAGED_STANDBY;
－－判斷哪個日誌沒有被standby site接受
SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM
(SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL
WHERE
LOCAL.SEQUENCE# NOT IN
(SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND
THREAD# = LOCAL.THREAD#);
錯誤：
Failed to request gap sequence. Thread #: 2, gap sequence: 297242-297242
All FAL server has been attempted.
解決方案：
從主庫COPY過去後註冊
alter database register logfile '/arch5/cx3b20000297242.ARC';
alter system set fal_client='fal_zw3a' scope=both sid='ozw3a';
alter system set fal_client='fal_zw3b' scope=both sid='ozw3b';
alter system set fal_client='fal_tcx3a' scope=both sid='ocx3a';
alter system set fal_client='fal_tcx3b' scope=both sid='ocx3b';
alter system set fal_server='fal_tcx3a' scope=both;
alter system set log_archive_dest_state_2='defer';
alter system set log_archive_dest_state_2='enable';
alter system set fal_server='fal_tcx3a' scope=both;
alter system set log_archive_dest_state_2='defer';
alter system set log_archive_dest_state_2='enable';
Failed to request gap sequence. Thread #: 1, gap sequence: 314793-314806
All FAL server has been attempted.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
alter system set log_archive_dest_2='service=fal_tcx3a arch reopen=30 max_failure=10' scope=both;
alter system set log_archive_dest_2='' scope=both;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29446986/viewspace-1561604/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 9i Data Guard進行資料庫的災難防護（轉）Oracle資料庫
- 利用Oracle Data Guard完成跨平臺的資料庫遷移案例Oracle資料庫
- (轉) DB 遷移到Data Guard 實施方案
- Oracle 9i Data Guard進行資料庫的災難防護簡介(轉)Oracle資料庫
- Data Guard新特性：快照備用資料庫資料庫
- Data Guard Broker系列之四：資料庫管理資料庫
- Data Guard之Snapshot Standby資料庫功能[轉]資料庫
- 管理物理STANDBY資料庫——DATA GUARD概念和管理資料庫
- 建立物理STANDBY資料庫——DATA GUARD概念和管理資料庫
- Data Guard broker系列之五：資料庫角色轉換資料庫
- 管理邏輯STANDBY資料庫——DATA GUARD概念和管理資料庫
- 建立邏輯STANDBY資料庫——DATA GUARD概念和管理資料庫
- 利用RMAN建立10GRAC資料庫的DATA GUARD資料庫
- 資料泵實現資料遷移到異地庫
- oracle9204(9i)_dg(data guard)_重新命名主庫資料檔案_指南_轉摘官檔Oracle
- Oracle RAC & Data Guard搭建高可用資料庫系統方案Oracle資料庫
- DATA GUARD手工管理資料檔案
- Data Guard Broker系列之二：Data Guard Broker配置實戰
- DATA GUARD主庫丟失資料檔案的恢復(2)
- DATA GUARD主庫丟失資料檔案的恢復(3)
- DATA GUARD主庫丟失資料檔案的恢復(1)
- oracle10g data guard建立物理standby資料庫的例子Oracle資料庫
- 2.10 克隆資料庫資料庫
- 使用duplicate克隆資料庫資料庫
- Data Guard主備庫切換
- Data Guard備份資料庫位置及目錄的選擇方案資料庫
- Data Guard 的3種資料保護模式模式
- spring-data-mongodb多資料庫訪問實現SpringMongoDB資料庫
- DATA GUARD部署模式——DATA GUARD概念和管理模式
- 靜默建立oracle資料庫及克隆資料庫Oracle資料庫
- 【DataGuard】Oracle 11g物理Data Guard之Snapshot Standby資料庫功能Oracle資料庫
- oracle10g data guard 主備資料庫配置引數說明Oracle資料庫
- 不停機 data guard 注意事項 （重建orapw對資料庫的影響）資料庫
- 使用Data Guard Broker進行Data Guard物理備用庫配置(Oracle 19c)Oracle
- 【流雲 】使用DBMS_FILE_TRANSFER配置DATA GURAD和克隆資料庫資料庫
- iOS Core Data 資料遷移 指南iOS
- 監控Data Guard實時同步
- 資料庫遷移之資料泵實驗資料庫