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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- profile檔案對sysdba使用者的影響
- 分支對程式碼效能的影響和優化優化
- 專案管理對工程質量的影響和對策(轉)專案管理
- 密碼即將過期提示的影響密碼
- Linux讀寫執行許可權對目錄和檔案的影響Linux
- Oracle DBWR,LGWR,CKPT,ARCH 觸發條件Oracle
- Oracle 12.2.0.1.0 PDB丟失資料檔案對CDB的影響Oracle
- 嚴格模式下對於this指向的影響模式
- sqlnet.ora檔案引起的TNS-00583對dataguard的影響SQL
- 檔案傳輸中斷怎麼辦？對檔案會有什麼影響？
- Oracle密碼檔案的作用和說明Oracle密碼
- 源資料檔案(.csv)中的空格串和空串對pandas讀取結果的影響
- 驗證DG最大效能模式下使用ARCH/LGWR及STANDBY LOG的不同情況模式
- 企業檔案加密對員工工作效率的影響有多大？加密
- DBWR,LGWR,CKPT,ARCH觸發條件總結
- 嚴格模式下對變數宣告的影響模式變數
- RAC環境下的SEQUENCE對應用的影響
- oracle本地驗證和密碼檔案Oracle密碼
- ORACLE密碼檔案和登入方式Oracle密碼
- try catch 對程式碼執行的效能影響
- 關於密碼檔案，SYS密碼密碼
- Oracle DBWR,LGWR,CKPT,ARCH 觸發條件 總結Oracle
- ID和密碼過多會影響計算機系統安全(轉)密碼計算機
- Oracle 密碼檔案Oracle密碼
- AI和機器學習對量化交易領域的影響AI機器學習
- JavaScript 事件對記憶體和效能的影響JavaScript事件記憶體
- mysql刪除和更新操作對效能的影響MySql
- rac中的密碼檔案密碼
- 【Oracle】-【COMMIT對索引的影響】-從trace看COMMIT對索引的影響OracleMIT索引
- 臨時資料檔案 offline 對於匯入匯出的影響
- Oracle密碼檔案的建立、使用和維護 (zt)Oracle密碼
- shrink 操作對索引的影響索引
- Update操作對索引的影響索引
- 資料庫Standby中的幾個概念 - LGWR, ARCH,ASYNC,SYNC,AFFIRM資料庫
- PMON、SMON、DBWn、LGWR、CKPT、ARCH等後臺程式說明
- padding和margin對於position定位的影響padding
- 操作分割槽表對global和local索引的影響索引
- 轉載-表空間和資料檔案offline的影響分析