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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DATA GUARD主庫丟失資料檔案的恢復(3)
- DATA GUARD主庫丟失資料檔案的恢復(1)
- DATA GUARD主庫丟失資料檔案的恢復(2)
- 使用Data Guard Broker進行Data Guard物理備用庫配置(Oracle 19c)Oracle
- 2.10 克隆資料庫資料庫
- oracle 9i資料庫做spaOracle資料庫
- 【DG】Data Guard主備庫Failove切換AI
- 【DG】Data Guard主備庫Switchover切換
- 2 開始實用 Oracle Data GuardOracle
- 2.10.1.2 使用CloneDB克隆資料庫資料庫
- 異構資料庫資料遷移 oracle to mysql之oracle sqlloader和mysql load data資料庫OracleMySql
- MSSQL·最佳實踐·RDSSDK實現資料庫遷移上阿里雲RDSSQLServerSQL資料庫阿里Server
- Data Guard備庫日誌的實時應用與非實時應用
- Oracle Data Guard Broker元件Oracle元件
- Oracle Data Guard簡介Oracle
- 單機搭建Data Guard
- Oracle 9i 11g歷史庫升級遷移資料至19c CDBOracle
- 雲資料遷移（Cloud Data Migration，CDM）Cloud
- ORM實操之資料庫遷移ORM資料庫
- 資料庫遷移資料庫
- bd_ticket_guard_client_dataclient
- Oracle Data Guard和Broker概述Oracle
- 【DG】Data Guard搭建（physical standby）
- 1 關於 Oracle Data GuardOracle
- 2 Oracle Data Guard 安裝Oracle
- 1 Oracle Data Guard Broker 概念Oracle
- 2.10.1.3 使用 CloneDB克隆資料庫後續資料庫
- 2.10.1.1 有關使用CloneDB克隆資料庫資料庫
- [20180529]克隆資料庫與dblinks注意.txt資料庫
- 大資料雲搬遷的五大要領大資料
- cassandra百億級資料庫遷移實踐資料庫
- spring data mongodb配置+月庫實現SpringMongoDB
- laravel資料庫遷移Laravel資料庫
- 資料庫遷移 ：理解資料庫
- redis資料庫遷移Redis資料庫
- Bd-Ticket-Guard-Client-Data逆向client
- 8 Oracle Data Guard Broker 屬性Oracle
- 9 Oracle Data Guard 故障診斷Oracle
- 資料庫平滑遷移方案與實踐分享資料庫