LGWR和ARCH下密碼檔案不一致對FAL的影響
DATAGUARD的資料傳輸方式有兩種：LGWR和ARCH。其中前者傳送的是聯機日誌的redo，後者傳輸的是歸檔。
FAL是9i推出的DATAGUARD的一大改進，它可以自動傳送備庫缺失的日誌，一般情況下，它是不需要手工干預的。
主庫的日誌要傳輸到備庫，兩者的密碼檔案中設定的口令必須一致。然而，如果我們不小心設定了不一致的密碼，則需要重新生成備庫的密碼檔案，但重置密碼檔案後，FAL還並不能自動處理日誌的GAP，需要DBA做一些額外的工作。
在這種情況下，用LGWR和ARCH兩種方式的處理方式有所區別，下面分別討論:
一、ARCH方式傳送日誌
--主庫引數
SQL> show parameter dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_2 string
log_archive_dest_2 string SERVICE=standby LGWR ASYNC VA
LID_FOR=(ONLINE_LOGFILES,PRIMA
RY_ROLE) DB_UNIQUE_NAME=standb
y
--在備庫上查詢
SQL> select * from v$standby_log;
no rows selected
我們知道，用LGWR傳送日誌，如果備庫沒有standby redo log，則oracle自動會變成用ARCH傳送歸檔。
為了模擬主備庫密碼檔案不一致，我們重新生成備庫密碼檔案，並置一個與主庫不一樣的密碼。
[oracle@standby dbs]$ mv orapwprimary orapwprimary.bak
[oracle@standby dbs]$ orapwd file=orapwprimary password=aaa entries=10
接著重啟主庫，檢視主庫的alert日誌，可以發現下面的資訊：
Tue Jul 10 22:10:38 2007
Errors in file /u01/oracle/admin/primary/bdump/primary_arc1_3338.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Tue Jul 10 22:10:38 2007
ORACLE Instance primary - Archival Error. Archiver continuing.
Tue Jul 10 22:10:47 2007
FAL[server]: Fail to queue the whole FAL gap
GAP - thread 1 sequence 68-68
DBID 1463588919 branch 626106231
Tue Jul 10 22:15:50 2007
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
Tue Jul 10 22:15:50 2007
Errors in file /u01/oracle/admin/primary/bdump/primary_arc1_3338.trc:
ORA-16191: Primary log shipping client not logged on standby
PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 16191.
從上面的資訊不難看出，oracle的確換成了ARCH的方式傳送日誌，並且因為密碼檔案的原因不能傳送到備庫中。
如果檢視備庫的日誌，也可以發現類似如下的資訊：
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 68-68
DBID 1463588919 branch 626106231
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
-------------------------------------------------------------
接著，我們在備庫還原密碼檔案，並重啟備庫：
[oracle@standby dbs]$ rm orapwprimary
[oracle@standby dbs]$ mv orapwprimary.bak orapwprimary
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 134218600 bytes
Database Buffers 29360128 bytes
Redo Buffers 2932736 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
然而，隔一段時間後觀察主、備庫的日誌，仍然有上面的報錯資訊，說明這種情況下重啟備庫是沒有效果的。
我們再重啟主庫試試：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 150995816 bytes
Database Buffers 12582912 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.
隔一小段時間可以分別在主、備庫上看到如下資訊：
--主庫
Tue Jul 10 22:41:57 2007
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
--備庫
Tue Jul 10 22:31:57 2007
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[5]: Assigned to RFS process 16058
RFS[5]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: No standby redo logfiles created
Tue Jul 10 22:32:26 2007
Media Recovery Log /u01/archivelog/1_70_626106231.dbf
Media Recovery Waiting for thread 1 sequence 71 (in transit)
以上資訊充分說明，DATAGUARD日誌傳送已經恢復正常。
實際上，經過測試，我們在修改密碼檔案後只需要重啟主庫即可恢復日誌正常傳送。
二、LGWR方式傳送日誌
為了讓oracle可以用LGWR方式傳送日誌，我們先在備庫上建立幾個standby redo log。
SQL> alter database add standby logfile group 4 '/u01/oracle/oradata/primary/standbyredo04.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 5 '/u01/oracle/oradata/primary/standbyredo5.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 6 '/u01/oracle/oradata/primary/standbyredo6.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 7 '/u01/oracle/oradata/primary/standbyredo7.log' size 50m;
Database altered.
重新生成一個與主庫不一致的密碼檔案：
[oracle@standby dbs]$ mv orapwprimary orapwprimary.bak
[oracle@standby dbs]$ orapwd file=orapwprimary password=aaa entries=10
重新生成一個密碼檔案後，必須重啟備庫才能模擬錯誤（不重啟的話是不會影響日誌傳輸的）
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 142607208 bytes
Database Buffers 20971520 bytes
Redo Buffers 2932736 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
此時主庫alert日誌報錯如下：
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
Tue Jul 10 23:06:30 2007
Errors in file /u01/oracle/admin/primary/bdump/primary_arc0_3457.trc:
ORA-16191: Primary log shipping client not logged on standby
PING[ARC0]: Heartbeat failed to connect to standby 'standby'. Error is 16191.
備庫alert報錯：
Tue Jul 10 22:53:06 2007
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 79-79
DBID 1463588919 branch 626106231
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
-------------------------------------------------------------
上面的資訊與ARCH方式傳送日誌的測試報錯類似，就不解析了。
我們重啟備庫，並備庫觀察日誌：
Tue Jul 10 23:13:28 2007
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 16235
RFS[2]: Identified database type as 'physical standby'
Tue Jul 10 23:25:21 2007
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 16237
RFS[3]: Identified database type as 'physical standby'
RFS[3]: Successfully opened standby log 4: '/u01/oracle/oradata/primary/standbyredo04.log'
Tue Jul 10 23:25:23 2007
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 16239
RFS[4]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Successfully opened standby log 4: '/u01/oracle/oradata/primary/standbyredo04.log'
從備庫的alert資訊看，日誌傳送已經恢復正常。
但此時主庫還會繼續報如下的錯誤：
Tue Jul 10 23:18:31 2007
FAL[server]: Fail to queue the whole FAL gap
GAP - thread 1 sequence 79-79
DBID 1463588919 branch 626106231
FAL[server]: Fail to queue the whole FAL gap
GAP - thread 1 sequence 80-80
DBID 1463588919 branch 626106231
LNS1 started with pid=18, OS id=3499
這是因為雖然備庫重建了密碼檔案，並且存在standby redo log，但主庫仍然嘗試用ARCH的方式傳送日誌，直到切換日誌。
我們切換一下主庫日誌，主庫alert資訊就不會再報錯了：
SQL> alter system switch logfile;
System altered.
Tue Jul 10 23:35:02 2007
ARC2: Standby redo logfile selected for thread 1 sequence 80 for destination LOG_ARCHIVE_DEST_2
Tue Jul 10 23:35:05 2007
LNS: Standby redo logfile selected for thread 1 sequence 81 for destination LOG_ARCHIVE_DEST_2
進一步測試可以發現：在這種情況下，只重啟主庫也可以恢復日誌的正常傳送。
簡單總結：
1、ARCH方式傳送日誌時，如果主備庫密碼檔案不一致，在重新設定密碼檔案後，必須重啟主庫才能使日誌傳送恢復正常。
2、LGWR方式傳送日誌時，如果主備庫密碼檔案不一致，在重新設定密碼檔案後，只需可以重啟主庫或重啟備庫就可使日誌傳送恢復正常。
看來LGWR在減少可能的資料損失同時，還可以減低主庫重啟的機率，推薦大家使用這種方式作為日誌傳送手段。
FAL是9i推出的DATAGUARD的一大改進，它可以自動傳送備庫缺失的日誌，一般情況下，它是不需要手工干預的。
主庫的日誌要傳輸到備庫，兩者的密碼檔案中設定的口令必須一致。然而，如果我們不小心設定了不一致的密碼，則需要重新生成備庫的密碼檔案，但重置密碼檔案後，FAL還並不能自動處理日誌的GAP，需要DBA做一些額外的工作。
在這種情況下，用LGWR和ARCH兩種方式的處理方式有所區別，下面分別討論:
一、ARCH方式傳送日誌
--主庫引數
SQL> show parameter dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_2 string
log_archive_dest_2 string SERVICE=standby LGWR ASYNC VA
LID_FOR=(ONLINE_LOGFILES,PRIMA
RY_ROLE) DB_UNIQUE_NAME=standb
y
--在備庫上查詢
SQL> select * from v$standby_log;
no rows selected
我們知道，用LGWR傳送日誌，如果備庫沒有standby redo log，則oracle自動會變成用ARCH傳送歸檔。
為了模擬主備庫密碼檔案不一致，我們重新生成備庫密碼檔案，並置一個與主庫不一樣的密碼。
[oracle@standby dbs]$ mv orapwprimary orapwprimary.bak
[oracle@standby dbs]$ orapwd file=orapwprimary password=aaa entries=10
接著重啟主庫，檢視主庫的alert日誌，可以發現下面的資訊：
Tue Jul 10 22:10:38 2007
Errors in file /u01/oracle/admin/primary/bdump/primary_arc1_3338.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Tue Jul 10 22:10:38 2007
ORACLE Instance primary - Archival Error. Archiver continuing.
Tue Jul 10 22:10:47 2007
FAL[server]: Fail to queue the whole FAL gap
GAP - thread 1 sequence 68-68
DBID 1463588919 branch 626106231
Tue Jul 10 22:15:50 2007
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
Tue Jul 10 22:15:50 2007
Errors in file /u01/oracle/admin/primary/bdump/primary_arc1_3338.trc:
ORA-16191: Primary log shipping client not logged on standby
PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 16191.
從上面的資訊不難看出，oracle的確換成了ARCH的方式傳送日誌，並且因為密碼檔案的原因不能傳送到備庫中。
如果檢視備庫的日誌，也可以發現類似如下的資訊：
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 68-68
DBID 1463588919 branch 626106231
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
-------------------------------------------------------------
接著，我們在備庫還原密碼檔案，並重啟備庫：
[oracle@standby dbs]$ rm orapwprimary
[oracle@standby dbs]$ mv orapwprimary.bak orapwprimary
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 134218600 bytes
Database Buffers 29360128 bytes
Redo Buffers 2932736 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
然而，隔一段時間後觀察主、備庫的日誌，仍然有上面的報錯資訊，說明這種情況下重啟備庫是沒有效果的。
我們再重啟主庫試試：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 150995816 bytes
Database Buffers 12582912 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.
隔一小段時間可以分別在主、備庫上看到如下資訊：
--主庫
Tue Jul 10 22:41:57 2007
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
--備庫
Tue Jul 10 22:31:57 2007
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[5]: Assigned to RFS process 16058
RFS[5]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: No standby redo logfiles created
Tue Jul 10 22:32:26 2007
Media Recovery Log /u01/archivelog/1_70_626106231.dbf
Media Recovery Waiting for thread 1 sequence 71 (in transit)
以上資訊充分說明，DATAGUARD日誌傳送已經恢復正常。
實際上，經過測試，我們在修改密碼檔案後只需要重啟主庫即可恢復日誌正常傳送。
二、LGWR方式傳送日誌
為了讓oracle可以用LGWR方式傳送日誌，我們先在備庫上建立幾個standby redo log。
SQL> alter database add standby logfile group 4 '/u01/oracle/oradata/primary/standbyredo04.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 5 '/u01/oracle/oradata/primary/standbyredo5.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 6 '/u01/oracle/oradata/primary/standbyredo6.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 7 '/u01/oracle/oradata/primary/standbyredo7.log' size 50m;
Database altered.
重新生成一個與主庫不一致的密碼檔案：
[oracle@standby dbs]$ mv orapwprimary orapwprimary.bak
[oracle@standby dbs]$ orapwd file=orapwprimary password=aaa entries=10
重新生成一個密碼檔案後，必須重啟備庫才能模擬錯誤（不重啟的話是不會影響日誌傳輸的）
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 142607208 bytes
Database Buffers 20971520 bytes
Redo Buffers 2932736 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
此時主庫alert日誌報錯如下：
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
Tue Jul 10 23:06:30 2007
Errors in file /u01/oracle/admin/primary/bdump/primary_arc0_3457.trc:
ORA-16191: Primary log shipping client not logged on standby
PING[ARC0]: Heartbeat failed to connect to standby 'standby'. Error is 16191.
備庫alert報錯：
Tue Jul 10 22:53:06 2007
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 79-79
DBID 1463588919 branch 626106231
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
-------------------------------------------------------------
上面的資訊與ARCH方式傳送日誌的測試報錯類似，就不解析了。
我們重啟備庫，並備庫觀察日誌：
Tue Jul 10 23:13:28 2007
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 16235
RFS[2]: Identified database type as 'physical standby'
Tue Jul 10 23:25:21 2007
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 16237
RFS[3]: Identified database type as 'physical standby'
RFS[3]: Successfully opened standby log 4: '/u01/oracle/oradata/primary/standbyredo04.log'
Tue Jul 10 23:25:23 2007
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 16239
RFS[4]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Successfully opened standby log 4: '/u01/oracle/oradata/primary/standbyredo04.log'
從備庫的alert資訊看，日誌傳送已經恢復正常。
但此時主庫還會繼續報如下的錯誤：
Tue Jul 10 23:18:31 2007
FAL[server]: Fail to queue the whole FAL gap
GAP - thread 1 sequence 79-79
DBID 1463588919 branch 626106231
FAL[server]: Fail to queue the whole FAL gap
GAP - thread 1 sequence 80-80
DBID 1463588919 branch 626106231
LNS1 started with pid=18, OS id=3499
這是因為雖然備庫重建了密碼檔案，並且存在standby redo log，但主庫仍然嘗試用ARCH的方式傳送日誌，直到切換日誌。
我們切換一下主庫日誌，主庫alert資訊就不會再報錯了：
SQL> alter system switch logfile;
System altered.
Tue Jul 10 23:35:02 2007
ARC2: Standby redo logfile selected for thread 1 sequence 80 for destination LOG_ARCHIVE_DEST_2
Tue Jul 10 23:35:05 2007
LNS: Standby redo logfile selected for thread 1 sequence 81 for destination LOG_ARCHIVE_DEST_2
進一步測試可以發現：在這種情況下，只重啟主庫也可以恢復日誌的正常傳送。
簡單總結：
1、ARCH方式傳送日誌時，如果主備庫密碼檔案不一致，在重新設定密碼檔案後，必須重啟主庫才能使日誌傳送恢復正常。
2、LGWR方式傳送日誌時，如果主備庫密碼檔案不一致，在重新設定密碼檔案後，只需可以重啟主庫或重啟備庫就可使日誌傳送恢復正常。
看來LGWR在減少可能的資料損失同時，還可以減低主庫重啟的機率，推薦大家使用這種方式作為日誌傳送手段。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/231499/viewspace-63835/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Linux讀寫執行許可權對目錄和檔案的影響Linux
- 分支對程式碼效能的影響和優化優化
- 檔案傳輸中斷怎麼辦？對檔案會有什麼影響？
- 源資料檔案(.csv)中的空格串和空串對pandas讀取結果的影響
- 企業檔案加密對員工工作效率的影響有多大？加密
- Oracle 密碼檔案Oracle密碼
- RAC環境下的SEQUENCE對應用的影響
- Oracle：ASM & 密碼檔案OracleASM密碼
- Oracle 12c的DG自動同步密碼檔案--ASM 新特性：共享密碼檔案Oracle密碼ASM
- SpringBoot專案配置檔案中密碼的加密Spring Boot密碼加密
- pytest 中，pytest.ini 檔案為什麼會對 fixture 產生影響
- 1.7.3. 禁用和共享資料庫密碼檔案資料庫密碼
- 元明粉和純鹼對染色的影響VOM
- 1.7. 建立和維護資料庫密碼檔案資料庫密碼
- Linux 下用 SCP 無需輸入密碼傳輸檔案Linux密碼
- 管理(002)：建立密碼檔案示例密碼
- 管理(005)：密碼檔案設定密碼
- 海外代理IP地址對網站SEO的影響和作用網站
- 聊聊虛擬化和容器對資料庫的影響資料庫
- ClubIntel：Y世代和Z世代對健身行業的影響Intel行業
- GHA：loot boxes對兒童和年輕人的影響
- unusable index對DML/QUERY的影響Index
- Nologging對恢復的影響(二)
- 語言對思維的影響
- Nologging對恢復的影響(一)
- rman開啟備份優化對備份歸檔的影響優化
- 專案資料視覺化對甲方客戶的影響視覺化
- 手工rm刪除歸檔日誌對備份歸檔日誌的影響
- PDF檔案有限制密碼，該如何編輯檔案？密碼
- 以前的windows安裝檔案可以刪除嗎 windowsold檔案刪除了有影響嗎Windows
- 網線的分類與對網速的影響 網線對網速影響大嗎？
- 浮動的盒子對img的影響
- 39、安全_1（許可權、使用者profile檔案、密碼檔案、VPD對行級訪問的控制）密碼
- 探索webpack熱更新對程式碼打包結果的影響（二）Web
- 低程式碼開發對軟體開發流程的影響
- 測試多分支開發對合並程式碼的影響
- 畢馬威：疫情對各行業的影響和未來發展趨勢（附下載）行業
- 中原地產：疫情對房地產行業的影響及應對（附下載）行業