oracle實驗記錄 （physical standby 日誌應用方面）
實驗環境
SQL> select OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE from v$database;
OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE
---------- ------------------------------ ----------------
READ ONLY standby1 PHYSICAL STANDBY
OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE
---------- ------------------------------ ----------------
READ WRITE xhtest PRIMARY
注意 要想傳輸順利要求 @xhtest @standby1這樣連線資料庫才行
~
看一下常規的傳輸
log_archive_dest_n='server= 預設這個引數後面是 arcn
應用歸檔日誌 來同步standby
例
SQL> select name,applied from v$archived_log;
NAME APP
-------------------- ---
D:\STANDBYPHYSICAL\A YES
RCHIVE\ARC00040_0661
538341.001
D:\STANDBYPHYSICAL\A YES
RCHIVE\ARC00041_0661
538341.001
D:\STANDBYPHYSICAL\A YES
RCHIVE\ARC00042_0661
538341.001
D:\STANDBYPHYSICAL\A YES
RCHIVE\ARC00043_0661
538341.001
D:\STANDBYPHYSICAL\A YES
RCHIVE\ARC00044_0661
538341.001
D:\STANDBYPHYSICAL\A YES
RCHIVE\ARC00045_0661
538341.001
D:\STANDBYPHYSICAL\A YES
RCHIVE\ARC00046_0661
538341.001
D:\STANDBYPHYSICAL\A YES
RCHIVE\ARC00047_0661
538341.001
D:\STANDBYPHYSICAL\A YES
RCHIVE\ARC00052_0661
538341.001
D:\STANDBYPHYSICAL\A YES
RCHIVE\ARC00048_0661
538341.001
D:\STANDBYPHYSICAL\A YES
RCHIVE\ARC00049_0661
538341.001
D:\STANDBYPHYSICAL\A YES
RCHIVE\ARC00050_0661
538341.001
D:\STANDBYPHYSICAL\A YES
RCHIVE\ARC00051_0661
538341.001
D:\STANDBYPHYSICAL\A YES
RCHIVE\ARC00053_0661
538341.001
已選擇14行。 現在的環境在standby上應用到的位置
SQL> select * from test; （primary庫）
A
----------
2
1
1
2
3
5
1
99
111
已選擇9行。
SQL> insert into test values (222);
已建立 1 行。
SQL> commit;
提交完成。
standby上
SQL> alter database recover managed standby database cancel; 剛才一直是在alter database recover managed standby database disconnect from session狀態
資料庫已更改。
SQL> alter database open;
資料庫已更改。
SQL> select * from test;
A
----------
2
1
1
2
3
5
1
99
111 沒應用~~ 原因很簡單 沒收到primary歸檔
已選擇9行。
SQL> select OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE from v$database;
OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE
---------- ------------------------------ ----------------
READ ONLY standby1 PHYSICAL STANDBY
SQL> alter database recover managed standby database using current logfile disco
nnect from session; 使用線上 日誌檔案(standby的) 但此時候 並未開啟lgwr
資料庫已更改。
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only; hang住了
alter database recover managed standby database using current logfile disconnect from session
Tue Aug 05 17:35:46 2008
Stopping background process MMNL
Tue Aug 05 17:35:47 2008
Stopping background process MMON
Tue Aug 05 17:35:48 2008
Stopping Job queue slave processes
Tue Aug 05 17:35:48 2008
Stopping background process CJQ0
Tue Aug 05 17:35:48 2008
Job queue slave processes stopped
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Tue Aug 05 17:35:50 2008
SMON: disabling cache recovery
MRP0 started with pid=10, OS id=3708
Managed Standby Recovery starting Real Time Apply
...real time arch delay is 10
Starting datafile 1 with incarnation depth 0 in thread 1 sequence 54
Datafile 1: 'D:\STANDBYPHYSICAL\SYSTEM01.DBF'
Starting datafile 2 with incarnation depth 0 in thread 1 sequence 54
Datafile 2: 'D:\STANDBYPHYSICAL\UNDOTBS01.DBF'
Starting datafile 3 with incarnation depth 0 in thread 1 sequence 54
Datafile 3: 'D:\STANDBYPHYSICAL\SYSAUX01.DBF'
Starting datafile 4 with incarnation depth 0 in thread 1 sequence 54
Datafile 4: 'D:\STANDBYPHYSICAL\USERS01.DBF'
Starting datafile 5 with incarnation depth 0 in thread 1 sequence 54
Datafile 5: 'D:\STANDBYPHYSICAL\EXAMPLE01.DBF'
Starting datafile 6 with incarnation depth 0 in thread 1 sequence 54
Datafile 6: 'D:\STANDBYPHYSICAL\TESTTB.DBF'
Starting datafile 7 with incarnation depth 0 in thread 1 sequence 54
Datafile 7: 'D:\STANDBYPHYSICAL\TESTTB2.DBF'
Starting datafile 8 with incarnation depth 0 in thread 1 sequence 54
Datafile 8: 'D:\STANDBYPHYSICAL\TESTTB3.DBF'
Tue Aug 05 17:35:59 2008
Completed: alter database recover managed standby database us
Tue Aug 05 17:35:59 2008
Media Recovery Waiting for thread 1 sequence 54
Tue Aug 05 17:36:44 2008
alter database recover managed standby database cancel
Tue Aug 05 17:36:45 2008
idle dispatcher 'D000' terminated, pid = (10, 1)
Tue Aug 05 17:36:48 2008
MRP0: Background Media Recovery cancelled with status 16037
Tue Aug 05 17:36:48 2008
Errors in file d:\standbyphysical\bdump\standby1_mrp0_3708.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Recovery interrupted!
Tue Aug 05 17:36:49 2008
Waiting for MRP0 pid 3708 to terminate
Tue Aug 05 17:36:49 2008
Errors in file d:\standbyphysical\bdump\standby1_mrp0_3708.trc:
ORA-16037: user requested cancel of managed recovery operation
Completed: alter database recover managed standby database ca
Tue Aug 05 17:36:55 2008
alter database open read only
Tue Aug 05 17:36:56 2008
SMON: enabling cache recovery 看看standby的alert log
重起服務
SQL> startup mount;
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
SQL> alter database open read only;
資料庫已更改。
SQL> select * from test;
A
----------
2
1
1
2
3
5
1
99
111 沒成功，原因很簡單，primary庫的重做日誌沒有寫入standby的logfile中
已選擇9行。
SQL>
SQL> alter system switch logfile; 主庫
系統已更改。
SQL> alter database recover managed standby database disconnect from session;(standby)
SQL> alter database recover managed standby database cancel;(standby)
資料庫已更改。
SQL> alter database open read only;
資料庫已更改。
SQL>
SQL> select * from test;
A
----------
2
1
1
2
3
5
1
99
111
222
已選擇10行。 只有應用日誌才 可以
看下 日誌的傳輸
standby庫上`~~~~~~~~~~~~~~~~~~~~~~~~~~~~現在有17個日誌
SQL> select count(name) from v$archived_log;
COUNT(NAME)
-----------
17
SQL> shutdown immediate; 關閉standby 造成他不能接受primary傳輸過來的歸檔日誌
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL>
SQL> alter system switch logfile; primary2次操作 產生2個歸檔日誌
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL>
SQL> startup mount; 開啟 standby
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
SQL> select count(name) from v$archived_log;
COUNT(NAME)
----------- 還是隻有17個
17
SQL> alter database recover managed standby database disconnect from session;
資料庫已更改。
SQL> select count(name) from v$archived_log;
COUNT(NAME)
-----------
17
SQL> alter system switch logfile; 主庫又一次 產生1個歸檔
系統已更改。
SQL> select count(name) from v$archived_log; 過了一陣oracle自動補齊了 standby關閉時候差的日誌
COUNT(NAME)
-----------
20
日誌的 實時應用
要實現 日誌實時應用主要與 primary log_archive_dest_n='server= '
affirm,noaffirm（預設）
delay,nodelay（預設）
lgwr ,arcn*（預設）
lgwr中分sync ,async 預設（sync=parallel）
這幾個引數有關係
這幾個引數就關係著standby的 3個模式
SQL> show parameter log_archive_dest_2 (primary) 主庫遠端歸檔位置加了lgwr,sync
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=standby1 lgwr sync va
lid_for=(online_logfiles,prima
ry_role) db_unique_name=standb
y1
SQL>
SQL> select * from test;(主庫查)
A
----------
2
1
1
2
3
5
1
99
111
222
333
A
----------
66666
888
SQL> select * from test;（standby查）
A
----------
2
1
1
2
3
5
1
99
111
222
333
A
----------
66666
888
已選擇13行。
SQL> select PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE# from v$managed_standby;
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CLOSING ARCH 138
ARCH CONNECTED ARCH 0
RFS WRITING LGWR 139 primary配置後standby顯示
RFS RECEIVING ARCH 0
RFS RECEIVING UNKNOWN 0
SQL> delete test;（PRIMARY）
已刪除13行。
SQL> select sequence#,group#,first_change# from v$log;（PRIMARY）
SEQUENCE# GROUP# FIRST_CHANGE#
---------- ---------- -------------
138 1 1421907
139 2 1422231 當前的 日誌
137 3 1421906
SQL> select * from test;（PRIMARY）
未選定行
SQL> commit;（PRIMARY）
提交完成。寫進 logfile
SQL> alter database recover managed standby database using current logfile disco (standby)
nnect from session;
SQL> alter database recover managed standby database cancel; (standby)
資料庫已更改。
SQL> select * from test; (standby) 實時應用了
未選定行
lgwr裡面又分sync ,async net_timeout
sync ,async 是指定當設定lgwr後，日誌是同步傳輸還是非同步傳輸到standbylogfile;
注意lgwr必須需要建立standbylogfile;
重要的引數
主庫的log_archive_dest_n='service '決定很多重要
（一）預設是arcn 當主庫發生歸檔產生arch程式時候一個arcn程式主庫上本地歸檔同時另一個arcn程式（在主庫）傳給rfs程式 由rfs程式通過oraclenet 傳遞到standby庫 (rfs程式在standby)
當建立 standbylogfile 時候，會先傳到standbylogfile 然後由standby的arcn進行歸檔 ,若沒建立standbylogfile 時候將由rfs程式直接寫到standby_archive_dest位置
若沒有設定standby_archive_dest將自動建立在db_recover_file_dest下（standby）
SQL> select PROCESS,STATUS,CLIENT_PROCESS,SEQUENCE# from v$managed_standby;(standby)
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CONNECTED ARCH 0
ARCH CLOSING ARCH 156
RFS RECEIVING UNKNOWN 0 使用的arcn （RFS 程式在standby）
RFS RECEIVING UNKNOWN 0
SQL> select sequence#,status,group#,first_change#,last_change# from v$standby_lo standby上建立有standbylogfile
g;(standby)
SEQUENCE# STATUS GROUP# FIRST_CHANGE# LAST_CHANGE#
---------- ---------- ---------- ------------- ------------
0 UNASSIGNED 4 0 0
SQL> show parameter log_archive_dest_1(standby)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=D:\standbyphysical\ar standby的本地歸檔位置 archive1
chive1 valid_for=(all_logfiles
,all_roles) db_unique_name=sta
ndby1
SQL> show parameter standby_archive_dest(standby)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string D:\STANDBYPHYSICAL\ARCHIVE
SQL> alter system switch logfile;（主庫）
系統已更改。
select name from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00158_0661538341.001 注意歸檔到了archive1位置 也就是standby本地歸檔位置
已選擇119行。
SQL> alter database drop standby logfile group 4; 刪除了 standby的，standbylogfile
資料庫已更改。
SQL> select sequence#,status,group#,first_change#,last_change# from v$standby_lo
g;(standby)
未選定行
SQL> alter system switch logfile; 主庫在歸檔
系統已更改。
select name from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\ARC00159_0661538341.001 歸檔到了standby_archive_dest上，此時standbylogfile已經沒有
已選擇120行。
SQL> alter system set standby_archive_dest='';(standby)
系統已更改。
SQL> select name from v$archived_log;(standby)
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_11\O1_MF_1_161_49ZSTF7B_.ARC 注意已經到了db_recovery_file_dest的位置(STANDBY上的)
SQL> alter system switch logfile;(primary)
系統已更改。
SQL> show parameter db_recovery_file_dest(standby)
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
db_recovery_file_dest string D:\standbyphysical\archive
db_recovery_file_dest_size big integer 2G
SQL> alter system set db_recovery_file_dest='';(standby) 關閉standby db_recover_file_dest,現在standby_archive_dest也關閉，也沒有 standbylogfile
系統已更改。
SQL> alter system switch logfile;（primary）
系統已更改。
SQL> select name from v$archived_log;(standby)
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_11\O1_MF_1_161_49ZSTF7B_.ARC 注意沒有產生新的歸檔日誌在standby庫
SQL> alter system set standby_archive_dest='D:\STANDBYPHYSICAL\ARCHIVE';(standby) 設定下 才收到
SQL> select name from v$archived_log;(standby)
D:\STANDBYPHYSICAL\ARCHIVE\ARC00162_0661538341.001
已選擇123行。
SQL>
總結 以上arcn 就傳遞方式 當 建立了standby logfile時候 rfs 先傳遞到standbylogfile 再由standby的 arcn程式歸檔到standby本地歸檔位置
沒有建立standbylogfile時候rfs程式 將歸檔（主庫）直接傳遞到standby_archive_dest中，沒有standby_archive_dest 將傳到standby的 db_recover_file_dest中
當 關閉standby db_recover_file_dest,現在standby_archive_dest也關閉，也沒有 standbylogfile 將在standby log_arhive_dest_1中
standbylogfile>standby_archive_dest>db_recovery_file_dest
rfs(程式很重要) 在standby
SQL> alter system set log_archive_dest_2='service=standby1 lgwr sync valid_for=(
online_logfiles,primary_role) db_unique_name=standby1'
注意 以上這些 是在主庫 設定成log_archive_dest_n='service' arcn的情況
（二）LGWR
實時應用
在主庫設定
SQL> alter system set log_archive_dest_2='service=standby1 lgwr async valid_for
=(online_logfiles,primary_role) db_unique_name=standby1';
系統已更改。 lgwr裡面又分sync,async
當開了lgwr 的時候 sync時 過程是 當log_buffer的redo資料寫入redofile 同時 primary的lgwr邊寫入primary 的online logfile;邊開啟lsnn程式（主庫） 傳遞到standby，由standby的rfs程式，寫入standbylogfile ，sync指定的是同步，此時primary的事務會一直保持，知道含有lgwr sync的所有log_archive_dest_n都接收完畢
lgwr主要是開啟實時應用~~~若在standby 沒有設定standbylogfile primary設定了lgwr ,sync或async
SQL> alter system set log_archive_dest_2='service=standby1 lgwr sync valid_for=( 主庫
online_logfiles,primary_role) db_unique_name=standby1'
2 ;
系統已更改。
SQL> select * from v$standby_log; （STANDBY ）
未選定行 經過查詢standby沒有 standbylogfile
所以即便主庫設定了lgwr,sync等也 無法使用 實時應用
例
SQL> select * from test;
未選定行
SQL> insert into test values(1);
已建立 1 行。
SQL> commit;
提交完成。 主庫插入資料並且寫入onlinelogfile
SQL>
SQL> alter database recover managed standby database using current logfile disc 開啟實時應用在 standby
onnect from session;
資料庫已更改。
SQL> alter database recover managed standby database cancel;（STANDBY）
資料庫已更改。
SQL> alter database open;（STANDBY）
資料庫已更改。
SQL> select * from test;（STANDBY）
未選定行
SQL> 沒有實時應用 看下alert.log(standby)
Tue Aug 12 11:45:40 2008
alter database recover managed standby database using current logfile disconnect from session
MRP0 started with pid=17, OS id=2308
Managed Standby Recovery starting Real Time Apply
...real time arch delay is 10
Starting datafile 1 with incarnation depth 0 in thread 1 sequence 139
Datafile 1: 'D:\STANDBYPHYSICAL\SYSTEM01.DBF'
Starting datafile 2 with incarnation depth 0 in thread 1 sequence 139
Datafile 2: 'D:\STANDBYPHYSICAL\UNDOTBS01.DBF'
Starting datafile 3 with incarnation depth 0 in thread 1 sequence 139
Datafile 3: 'D:\STANDBYPHYSICAL\SYSAUX01.DBF'
Starting datafile 4 with incarnation depth 0 in thread 1 sequence 139
Datafile 4: 'D:\STANDBYPHYSICAL\USERS01.DBF'
Starting datafile 5 with incarnation depth 0 in thread 1 sequence 139
Datafile 5: 'D:\STANDBYPHYSICAL\EXAMPLE01.DBF'
Starting datafile 6 with incarnation depth 0 in thread 1 sequence 139
Datafile 6: 'D:\STANDBYPHYSICAL\TESTTB.DBF'
Tue Aug 12 11:45:48 2008
Completed: alter database recover managed standby database u
Tue Aug 12 11:45:49 2008
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE\ARC00139_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE\ARC00140_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE\ARC00141_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE1\ARC00142_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE1\ARC00143_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE1\ARC00144_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE1\ARC00145_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE1\ARC00146_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE1\ARC00147_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE1\ARC00148_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE1\ARC00149_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE1\ARC00150_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE1\ARC00151_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE\ARC00152_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE1\ARC00153_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE1\ARC00154_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE1\ARC00155_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE1\ARC00156_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE1\ARC00157_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE1\ARC00158_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE\ARC00159_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_11\O1_MF_1_160_49ZSTG2X_.ARC
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_11\O1_MF_1_161_49ZSTF7B_.ARC
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE\ARC00162_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE\ARC00163_0661538341.001
Media Recovery Log D:\STANDBYPHYSICAL\ARCHIVE\ARC00164_0661538341.001
Media Recovery Waiting for thread 1 sequence 165 還是在等待primary的歸檔
Tue Aug 12 11:48:18 2008
alter database recover managed standby database cancel
Tue Aug 12 11:48:23 2008
MRP0: Background Media Recovery cancelled with status 16037
Tue Aug 12 11:48:23 2008
Errors in file d:\standbyphysical\bdump\standby1_mrp0_2308.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply 沒有應用 實時~~~~~~~~~~~~~~~~~~~~~~~~
Recovery interrupted!~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tue Aug 12 11:48:24 2008
Errors in file d:\standbyphysical\bdump\standby1_mrp0_2308.trc:
ORA-16037: user requested cancel of managed recovery operation
Tue Aug 12 11:48:24 2008
Waiting for MRP0 pid 2308 to terminate
Completed: alter database recover managed standby database c
SQL> alter database add standby logfile group 4 'D:\standbyphysical\STANDBYRD01. 為standby新增 standbylogfile
log' reuse;
資料庫已更改。
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE#
---------- ---------------------------------------- ---------- ----------
BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIME LAST_CHANGE#
---------- ---------- --- ---------- ------------- -------------- ------------
LAST_TIME
--------------
4 UNASSIGNED 0 0
15728640 512 YES UNASSIGNED 0 0
SQL> alter database recover managed standby database using current logfile disc standby開啟實時應用
onnect from session;
SQL> select process ,status,client_process,sequence# from v$managed_standby;
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CONNECTED ARCH 0
ARCH CONNECTED ARCH 0
RFS WRITING LGWR 166
RFS ATTACHED ARCH 0
RFS RECEIVING UNKNOWN 0
MRP0 APPLYING_LOG N/A 166
已選擇6行。
SQL> alter database recover managed standby database cancel;
資料庫已更改。
SQL> select * from test;
A
----------
1
在實驗一次
SQL> select * from test; （primary）
A
----------
1
2
SQL>
SQL> select * from test;（standby）
A
----------
1
2
SQL> insert into test values(3);（primary）
已建立 1 行。
SQL> commit;（primary）寫進logfile；
提交完成。
資料庫已更改。
QL> select process ,status,client_process,sequence# from v$managed_standby;
ROCESS STATUS CLIENT_P SEQUENCE#
-------- ------------ -------- ----------
RCH CONNECTED ARCH 0
RCH CONNECTED ARCH 0
RP0 APPLYING_LOG N/A 166 失敗 沒有rfs程式
重新啟動了primary主庫的服務
SQL> exit
ERROR:
ORA-03113: 通訊通道的檔案結束
從 Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Productio
With the Partitioning, OLAP and Data Mining options(情況複雜) 斷開
C:\>sqlplus "/@xhtest as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 8月 12 13:32:10 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
連線到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
SQL> select process ,status,client_process,sequence# from v$managed_standby; (standby)
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CONNECTED ARCH 0
ARCH CONNECTED ARCH 0
MRP0 APPLYING_LOG N/A 168
RFS WRITING LGWR 168
RFS ATTACHED UNKNOWN 0
SQL> alter database recover managed standby database using current logfile disc
onnect from session;
alter database recover managed standby database using current logfile disconnec
t from session
*
第 1 行出現錯誤:
ORA-01153: 啟用了不相容的介質恢復
SQL> alter database recover managed standby database cancel;
資料庫已更改。
SQL> alter database recover managed standby database using current logfile disc
onnect from session;
資料庫已更改。
SQL> alter database recover managed standby database cancel;
資料庫已更改。
SQL> alter database open read only; hang住了 檢視standby的alert.log
lter database recover managed standby database using current logfile disconnect from session
MRP0 started with pid=9, OS id=1076
Managed Standby Recovery starting Real Time Apply
...real time arch delay is 10
Starting datafile 1 with incarnation depth 0 in thread 1 sequence 168
Datafile 1: 'D:\STANDBYPHYSICAL\SYSTEM01.DBF'
Starting datafile 2 with incarnation depth 0 in thread 1 sequence 168
Datafile 2: 'D:\STANDBYPHYSICAL\UNDOTBS01.DBF'
Starting datafile 3 with incarnation depth 0 in thread 1 sequence 168
Datafile 3: 'D:\STANDBYPHYSICAL\SYSAUX01.DBF'
Starting datafile 4 with incarnation depth 0 in thread 1 sequence 168
Datafile 4: 'D:\STANDBYPHYSICAL\USERS01.DBF'
Starting datafile 5 with incarnation depth 0 in thread 1 sequence 168
Datafile 5: 'D:\STANDBYPHYSICAL\EXAMPLE01.DBF'
Starting datafile 6 with incarnation depth 0 in thread 1 sequence 168
Datafile 6: 'D:\STANDBYPHYSICAL\TESTTB.DBF'
Media Recovery Waiting for thread 1 sequence 168 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 168 Reading mem 0
Mem# 0 errs 0: D:\STANDBYPHYSICAL\STANDBYRD01.LOG
Tue Aug 12 13:34:18 2008
Completed: alter database recover managed standby database u
Tue Aug 12 13:35:35 2008
alter database recover managed standby database cancel
Tue Aug 12 13:35:35 2008
Errors with log
MRP0: Background Media Recovery cancelled with status 16037
Tue Aug 12 13:35:35 2008
Errors in file d:\standbyphysical\bdump\standby1_mrp0_1076.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~``其實 應用了
Recovery interrupted!
Recovered data files restored to a consistent state at change 1444022~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~應用到的位置SCN
Tue Aug 12 13:35:36 2008
Errors in file d:\standbyphysical\bdump\standby1_mrp0_1076.trc:
ORA-16037: user requested cancel of managed recovery operation
Tue Aug 12 13:35:37 2008
Completed: alter database recover managed standby database c
Tue Aug 12 13:36:05 2008
alter database open read only
Tue Aug 12 13:36:06 2008
SMON: enabling cache recovery （注意這是hang 住的原因 該怎麼查）
重新啟動standby服務
C:\>sqlplus "/as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 8月 12 13:42:08 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
已連線到空閒例程。
SQL> startup
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> select * from test;
A
----------
3
1
2 可以了
關於歸檔位置實驗，08.08.14日~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=standby1 lgwr valid_f priamry的遠端歸檔位置
r=(online_logfiles,primary_ro lgwr預設為 sync同步
le) db_unique_name=standby1
SQL> show parameter standby_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ standby的 歸檔位置,主庫遠端歸檔傳遞到的位置
standby_archive_dest string D:\STANDBYPHYSICAL\ARCHIVE
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=D:\standbyphysical\ar standby本地歸檔位置
chive1 valid_for=(all_logfiles
,all_roles) db_unique_name=sta
ndby1
SQL> select process,status,CLIENT_PROCESS,SEQUENCE# from v$managed_standby; standby傳輸狀態 ，及其STANDBY上程式狀態
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CLOSING ARCH 344 ~~~~~~~~~~~~arcn歸檔到344
ARCH CONNECTED ARCH 0
RFS WRITING LGWR 345~~~~~~~~~~~~~~~lgwr應用到了 345
RFS RECEIVING UNKNOWN 0
RFS RECEIVING UNKNOWN 0
SQL> select group# from v$standby_log; 注意 有一組日誌
GROUP#
----------
4
SQL> select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00344_0661538341.00 ARCH
1
已選擇262行。 最後一行是344的歸檔
SQL> /
系統已更改。
SQL> /
系統已更改。
SQL> /
系統已更改。
SQL> /
系統已更改。
SQL> /
系統已更改。
SQL> select process,status,CLIENT_PROCESS,SEQUENCE# from v$managed_standby;
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CLOSING ARCH 349
ARCH CONNECTED ARCH 0
RFS WRITING LGWR 350
RFS RECEIVING UNKNOWN 0
RFS RECEIVING UNKNOWN 0
SQL> select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00344_0661538341.00 ARCH 初始位置
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00345_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00346_0661538341.00 ARCH
NAME CREATOR
-------------------------------------------------- -------
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00347_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00348_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00349_0661538341.00 ARCH
1
已選擇267行。 都是arcn 歸檔到了本地位置
SQL> alter system set log_archive_dest_2='service=standby1 lgwr async valid_for=
(online_logfiles,primary_role) db_unique_name=standby1';
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> /
系統已更改。
SQL> /
系統已更改。
SQL> /
系統已更改。
SQL> select process,status,CLIENT_PROCESS,SEQUENCE# from v$managed_standby;
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CLOSING ARCH 353
ARCH CONNECTED ARCH 0
RFS OPENING LGWR 354
RFS RECEIVING UNKNOWN 0
RFS RECEIVING UNKNOWN 0
SQL> select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00349_0661538341.00 ARCH
1
NAME CREATOR
-------------------------------------------------- -------
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00350_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00351_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE\ARC00352_0661538341.001 LGWR
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00353_0661538341.00 ARCH
1
SQL> /
系統已更改。
SQL>
SQL> select process,status,CLIENT_PROCESS,SEQUENCE# from v$managed_standby;
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CLOSING ARCH 353~~~~~~~~~~~~~~~~~~~~分開了 arch程式 只歸檔到353 新產生的354不是由standby本地arcn歸檔的
ARCH CONNECTED ARCH 0
RFS WRITING LGWR 355~~~~~~~~~~~~~~~~~~~~~~分開了
RFS RECEIVING UNKNOWN 0
RFS RECEIVING UNKNOWN 0
SQL> select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00349_0661538341.00 ARCH
1
NAME CREATOR
-------------------------------------------------- -------
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00350_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00351_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE\ARC00352_0661538341.001 LGWR
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00353_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE\ARC00354_0661538341.001 LGWR
已選擇272行。
SQL> alter system set log_archive_dest_2='service=standby1 lgwr sync valid_for=(
online_logfiles,primary_role) db_unique_name=standby1';
系統已更改。
SQL> select process,status,CLIENT_PROCESS,SEQUENCE# from v$managed_standby;
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CLOSING ARCH 353
ARCH CONNECTED ARCH 0
RFS WRITING LGWR 355
RFS RECEIVING UNKNOWN 0
RFS RECEIVING UNKNOWN 0
SQL> alter system switch logfile;
系統已更改。
SQL> /
系統已更改。
SQL> /
系統已更改。
SQL> /
系統已更改。
SQL> select process,status,CLIENT_PROCESS,SEQUENCE# from v$managed_standby;
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CLOSING ARCH 358
ARCH CONNECTED ARCH 0
RFS WRITING LGWR 359
RFS RECEIVING UNKNOWN 0
RFS RECEIVING UNKNOWN 0
SQL> select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\ARC00354_0661538341.001 LGWR
NAME CREATOR
-------------------------------------------------- -------
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00355_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00356_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00357_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00358_0661538341.00 ARCH
1
NAME CREATOR
-------------------------------------------------- -------
已選擇276行。
改回
SQL> alter system set log_archive_dest_2='service=standby1 lgwr valid_for=(onlin
e_logfiles,primary_role) db_unique_name=standby1';
系統已更改。
重新啟動服務
standby,priamry都重啟
C:\>sqlplus "/ as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on 星期四 8月 14 11:34:19 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
已連線到空閒例程。
SQL> startup mount
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes 連到standby
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
SQL>
C:\>sqlplus "/@xhtest as sysdba" 連線到primary
SQL*Plus: Release 10.1.0.2.0 - Production on 星期四 8月 14 11:34:34 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
連線到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
SQL> select process,status,CLIENT_PROCESS,SEQUENCE# from v$managed_standby; standby 沒有啟動程式再重起下primary的服務
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CONNECTED ARCH 0
ARCH CONNECTED ARCH 0
SQL> exit
從 Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options 斷開
C:\>sqlplus "/@xhtest as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on 星期四 8月 14 11:37:36 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
連線到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
SQL> select process,status,CLIENT_PROCESS,SEQUENCE# from v$managed_standby;
有了
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CONNECTED ARCH 0
ARCH CONNECTED ARCH 0
RFS WRITING LGWR 362~~~~~~~~~~~~~~~~~~~~~~~只有lgwr 是傳遞的當前primary的onlinelogfile
RFS ATTACHED UNKNOWN 0
RFS RECEIVING UNKNOWN 0
SQL>
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 360 INACTIVE 主庫查
2 361 INACTIVE
3 362 CURRENT
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00358_0661538341.00 ARCH
1
NAME CREATOR
-------------------------------------------------- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00361_0661538341.001 ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00359_0661538341.001 ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00360_0661538341.001 ARCH
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00362_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00363_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00364_0661538341.00 ARCH
NAME CREATOR
-------------------------------------------------- -------
1
已選擇282行。
SQL> select process,status,CLIENT_PROCESS,SEQUENCE# from v$managed_standby
2 ;
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CONNECTED ARCH 0
ARCH CLOSING ARCH 364
RFS WRITING LGWR 365
RFS RECEIVING UNKNOWN 0
RFS RECEIVING UNKNOWN 0
SQL> show parameter log_archive_dest_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=standby1 lgwr valid_fo
r=(online_logfiles,primary_rol
e) db_unique_name=standby1
SQL> /
系統已更改。
SQL> /
系統已更改。
SQL>
SQL> select name,creator from v$archived_log;
NAME CREATOR
-------------------------------------------------- -------
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00365_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00366_0661538341.00 ARCH
1
已選擇284行。
SQL> select process,status,CLIENT_PROCESS,SEQUENCE# from v$managed_standby;
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CONNECTED ARCH 0
ARCH CLOSING ARCH 366
RFS WRITING LGWR 367
RFS RECEIVING UNKNOWN 0
RFS RECEIVING UNKNOWN 0
SQL> select group# from v$standby_log;
GROUP#
----------
4
SQL> / primary switch logfile
系統已更改。
SQL> /
系統已更改。
SQL> select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00367_0661538341.00 ARCH
1
NAME CREATOR
-------------------------------------------------- -------
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00368_0661538341.00 ARCH
1
已選擇286行。
SQL> select group#,sequence#,status,first_change#,last_change# from v$standby_lo
g;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# LAST_CHANGE#
---------- ---------- ---------- ------------- ------------
4 369 ACTIVE 1546251 1546297
SQL> alter system switch logfile;
系統已更改。
SQL>
SQL> select group#,sequence#,status,first_change#,last_change# from v$standby_lo
g;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# LAST_CHANGE#
---------- ---------- ---------- ------------- ------------
4 370 ACTIVE 1546325 1546325
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
現在情況 新的實驗環境 進行實驗
SQL> select group# from v$standby_log;
GROUP#
----------
4
SQL>
SQL> select process,status,CLIENT_PROCESS,SEQUENCE# from v$managed_standby;
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CONNECTED ARCH 0
ARCH CLOSING ARCH 372
RFS WRITING LGWR 373
RFS RECEIVING UNKNOWN 0
RFS RECEIVING UNKNOWN 0
SQL>
SQL> select name,creator from v$archived_log;
NAME CREATOR
-------------------------------------------------- -------
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00372_0661538341.00 ARCH
1
已選擇290行。
SQL>
SQL> show parameter log_archive_dest_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=standby1 lgwr valid_fo
r=(online_logfiles,primary_rol
e) db_unique_name=standby1
SQL>
下面實驗刪除standby的最後一組日誌
SQL> alter database drop standby logfile group 4;
alter database drop standby logfile group 4
*
第 1 行出現錯誤:
ORA-00261: 正在歸檔或修改日誌 4 (執行緒 1)
ORA-00312: 聯機日誌 4 執行緒 1: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
SQL> startup force mount
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
SQL> alter database drop standby logfile group 4;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~刪除standbylogfile組
資料庫已更改。
SQL>
SQL> select group# from v$standby_log;
未選定行
SQL> select process,status,CLIENT_PROCESS,SEQUENCE# from v$managed_standby;
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CONNECTED ARCH 0~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ARCH CONNECTED ARCH 0
SQL> select name,creator from v$archived_log;
NAME CREATOR
-------------------------------------------------- -------
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00372_0661538341.00 ARCH
1
已選擇290行。
重新啟動pirmary後
SQL> select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\ARC00373_0661538341.001 ARCH 多了一個歸檔日誌 在standby_archive_dest位置
已選擇291行。
SQL> select process,status,CLIENT_PROCESS,SEQUENCE# from v$managed_standby
2 ;
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CONNECTED ARCH 0
ARCH CONNECTED ARCH 0
RFS WRITING LGWR 374~~~~~~~~~~~~~~~~~~~~~~
RFS ATTACHED UNKNOWN 0
SQL>
SQL> alter system switch logfile;
系統已更改。
SQL> /
系統已更改。
SQL> /
系統已更改。
SQL> select process,status,CLIENT_PROCESS,SEQUENCE# from v$managed_standby
2 ;
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CONNECTED ARCH 0~~~~~~~~~~~~~~~~~~~~~~~~沒有歸檔本地的arch沒有啟動
ARCH CONNECTED ARCH 0
RFS WRITING LGWR 377 當前是第377 與primary onlinelogfile 同步
RFS RECEIVING UNKNOWN 0
RFS RECEIVING UNKNOWN 0
SQL> select group#,sequence#,status from v$log;(pirmary)
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 375 INACTIVE
2 376 INACTIVE
3 377 CURRENT
SQL>
SQL> select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\ARC00373_0661538341.001 ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00374_0661538341.001 LGWR
D:\STANDBYPHYSICAL\ARCHIVE\ARC00375_0661538341.001 LGWR
D:\STANDBYPHYSICAL\ARCHIVE\ARC00376_0661538341.001 LGWR 歸檔到366看 arch沒有歸檔 那麼肯定是由 lgwr來完成的
已選擇294行。 最後3個了都是LGWR遠端傳遞過來由於沒有standbylogfile
看下alert.log~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~沒有standlogfile 由lgwr直接歸檔到standby_archive_dest
Thu Aug 14 15:30:30 2008
RFS[1]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE\ARC00374_0661538341.001'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE\ARC00374_0661538341.001'
RFS[1]: No standby redo logfiles created
RFS[1]: No standby redo logfiles selected (reason:7)
Thu Aug 14 15:30:32 2008
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 1120
RFS[3]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Thu Aug 14 15:30:33 2008
RFS[1]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE\ARC00375_0661538341.001'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE\ARC00375_0661538341.001'
RFS[1]: No standby redo logfiles created
RFS[1]: No standby redo logfiles selected (reason:7)
RFS[1]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE\ARC00376_0661538341.001'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE\ARC00376_0661538341.001'
RFS[1]: No standby redo logfiles created
RFS[1]: No standby redo logfiles selected (reason:7)
下面刪除standby_archive_dest 位置
SQL> show parameter standby_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string D:\STANDBYPHYSICAL\ARCHIVE
SQL>
SQL> alter system set standby_archive_dest='';改為沒有了
系統已更改。
SQL>
SQL> select process,status,CLIENT_PROCESS,SEQUENCE# from v$managed_standby
2 ;
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CONNECTED ARCH 0
ARCH CONNECTED ARCH 0
RFS WRITING LGWR 377 關鍵程式都還在，理論可以收到歸檔
RFS RECEIVING UNKNOWN 0
RFS RECEIVING UNKNOWN 0
SQL> alter system switch logfile;
系統已更改。
SQL> /
系統已更改。 2次歸檔
SQL> select process,status,CLIENT_PROCESS,SEQUENCE# from v$managed_standby
2 ;
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CONNECTED ARCH 0
ARCH CONNECTED ARCH 0 注意此時候失去了一個rfs程式用lsnn 連線lgwr(primary)的
RFS RECEIVING UNKNOWN 0
RFS RECEIVING UNKNOWN 0
select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\ARC00377_0661538341.001 LGWR 2次但只有一個歸檔，為什麼呢
已選擇295行。
SQL> /
系統已更改。
SQL> / 在來2次switch logfile 主庫
系統已更改。
select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\ARC00377_0661538341.001 LGWr~~~~~~~~~~~~~~~~~~~~~~~~~沒有產生新歸檔
已選擇295行。
分析下alert.log
Thu Aug 14 15:41:11 2008
ALTER SYSTEM SET standby_archive_dest='' SCOPE=BOTH;~~~~~~~~~~~~~~~操作關閉了這個 位置
Thu Aug 14 15:42:20 2008
RFS[1]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE\ARC00377_0661538341.001'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~產生了一次lgwr
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE\ARC00377_0661538341.001'
RFS[1]: No standby redo logfiles created
RFS[1]: No standby redo logfiles selected (reason:7)
Thu Aug 14 15:42:20 2008
Errors in file d:\standbyphysical\udump\standby1_rfs_3148_xh080729.trc:
ORA-16032: parameter STANDBY_ARCHIVE_DEST destination string cannot be translated~~~~~~~~~~~~~~~~~~~~~~~~~找不到這個位置
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST~~~~~~~~~~~oracle自動使用了db_recover_file_dest這個位置
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 2968
RFS[4]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_14\O1_MF_1_378_4B7RRP3X_.ARC'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_14\O1_MF_1_378_4B7RRP3X_.ARC'~~~~~~1
Created Oracle managed file D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_14\O1_MF_1_378_4B7RRP3X_.ARC
RFS[4]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_14\O1_MF_1_379_4B7RRPRW_.ARC'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_14\O1_MF_1_379_4B7RRPRW_.ARC'~~2
Created Oracle managed file D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_14\O1_MF_1_379_4B7RRPRW_.ARC
RFS[4]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_14\O1_MF_1_380_4B7RRQ9R_.ARC'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_14\O1_MF_1_380_4B7RRQ9R_.ARC'~~3
Created Oracle managed file D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_14\O1_MF_1_380_4B7RRQ9R_.ARC
歸檔到這個位置db_recover_file_dest後v$archived_log檢視可以查到~~~~~~~過了一會才查到··························
SQL> show parameter log_archive_dest_2（primary）~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=standby1 lgwr valid_fo
r=(online_logfiles,primary_rol
e) db_unique_name=standby1
SQL> select name,creator,applied from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\ARC00376_0661538341.001 LGWR NO
D:\STANDBYPHYSICAL\ARCHIVE\ARC00377_0661538341.001 LGWR NO
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 ARCH NO~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~查到了
8_08_14\O1_MF_1_378_4B7RRP3X_.ARC
NAME CREATOR APP
-------------------------------------------------- ------- ---
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 ARCH NO
8_08_14\O1_MF_1_379_4B7RRPRW_.ARC
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 ARCH NO
8_08_14\O1_MF_1_380_4B7RRQ9R_.ARC
已選擇298行。
那麼我們看下歸檔到這個 我們看不到的位置oracle會應用嗎
SQL> alter database recover managed standby database disconnect from session;
資料庫已更改。
SQL> select name,creator,applied from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\ARC00376_0661538341.001 LGWR YES
D:\STANDBYPHYSICAL\ARCHIVE\ARC00377_0661538341.001 LGWR YES
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 ARCH YES
8_08_14\O1_MF_1_378_4B7RRP3X_.ARC
NAME CREATOR APP
-------------------------------------------------- ------- ---
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 ARCH YES
8_08_14\O1_MF_1_379_4B7RRPRW_.ARC
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 ARCH YES
8_08_14\O1_MF_1_380_4B7RRQ9R_.ARC
已選擇298行。 全部應用了
結論在lgwr下 沒有standbylogfile的情況下~~~會先歸檔打standby_archive_dest 如果standby_archive_dest沒有 ，就歸檔到standby的 db_recover_file_dest中
SQL> /~~~~~~~~~~~~~~~~~~~~~~~~~~~primary switch logfile
系統已更改。
SQL> select name,creator,applied from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 ARCH NO ~~~重複測試下
8_08_14\O1_MF_1_381_4B7VL17B_.ARC
已選擇299行。
實驗關閉 db_recovery_file_dest~~~~standby_archive_dest也是關閉的
SQL> alter system set db_recovery_file_dest='';
系統已更改。
SQL> show parameter standby_archive_dest(standby)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string
SQL> show parameter db_recovery_file_dest(standby)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
SQL> select process,status,CLIENT_PROCESS,SEQUENCE# from v$managed_standby
2 ;
PROCESS STATUS CLIENT_P SEQUENCE#
--------- ------------ -------- ----------
ARCH CONNECTED ARCH 0
ARCH CONNECTED ARCH 0
RFS RECEIVING UNKNOWN 0
MRP0 WAIT_FOR_LOG N/A 382~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~開著應用呢
RFS RECEIVING UNKNOWN 0
RFS WRITING LGWR 382
SQL> /
系統已更改。
SQL> /~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~switch logfile 2次
系統已更改。
SQL> select name,creator,applied from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 LGWR YES
NAME CREATOR APP
-------------------------------------------------- ------- ---
8_08_14\O1_MF_1_382_4B7VKZSW_.ARC
已選擇300行。
2次 第一次 到了db_recover_file_dest中 ~~~~~~~~~~~~第2次 就沒有歸檔到了
SQL> /
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。 又歸檔幾次
過了一會
SQL> select name,creator,applied from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 LGWR YES
NAME CREATOR APP
-------------------------------------------------- ------- ---
8_08_14\O1_MF_1_382_4B7VKZSW_.ARC
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00383_0661538341.00 ARCH YES~~~~~~~~~~~~~~~~~~~~歸檔進來了~而且是standby本地位置
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00384_0661538341.00 ARCH YES
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00385_0661538341.00 ARCH YES
1
NAME CREATOR APP
-------------------------------------------------- ------- ---
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00386_0661538341.00 ARCH YES
1
SQL> select group# from v$standby_log;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~沒有standbylogfile
未選定行
SQL> show parameter log_archive_dest_2;(primary)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=standby1 lgwr valid_fo
r=(online_logfiles,primary_rol
e) db_unique_name=standby1
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
過了會~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~變成lgwr來處理的了，並且歸檔到standby本地位置了
SQL> select name,creator,applied from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00388_0661538341.00 LGWR YES
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00389_0661538341.00 LGWR NO
1
NAME CREATOR APP
-------------------------------------------------- ------- ---
已選擇307行。
總結: 當寫入方式為lgwr 在沒有standbylogfile時~~~~不會使用lgwr歸檔到standby_archive_dest 如果沒有standby_archive_dest會歸檔到db_recovery_file_dest如果db_recovery_file_dest也沒有，那麼 oracle會使用arcn程式歸檔~~~來處理下 但最後還是由lgwr來歸檔到standby本地位置
~~~~~~~~~~~~~~~~08.08.15實驗
看下現在的環境
SQL> show parameter log_archive_dest_2(primary)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=standby1 lgwr valid_fo
r=(online_logfiles,primary_rol
e) db_unique_name=standby1
SQL>
SQL> show parameter standby_archive_dest(standby)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string
SQL>
SQL> show parameter log_archive_dest_1(standby)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=D:\standbyphysical\ar
chive1 valid_for=(all_logfiles
,all_roles) db_unique_name=sta
ndby1
SQL> show parameter db_recovery_file_dest(standby)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\STANDBYPHYSICAL\ARCHIVE
db_recovery_file_dest_size big integer 2G
SQL> select group# from v$standby_log;
未選定行~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~沒有日誌組
SQL> select group#,status,sequence# from v$log;（primary）
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 INACTIVE 396
2 INACTIVE 397
3 CURRENT 398
SQL> select PROCESS,CLIENT_PROCESS,SEQUENCE#,STATUS from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
RFS LGWR 398 WRITING
RFS UNKNOWN 0 RECEIVING
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~這裡要說下 雖然沒有standbyfilelog了但lgwr程式還存在還是在保持與主庫的序號同步，不過不能應用實時應用
SQL> select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 LGWR
8_08_14\O1_MF_1_395_4B80K7KL_.ARC
NAME CREATOR
-------------------------------------------------- -------
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 ARCH
8_08_15\O1_MF_1_397_4B9TZJNZ_.ARC
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 ARCH
8_08_15\O1_MF_1_396_4B9TZNHN_.ARC
已選擇308行。 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~現在的狀態~
SQL> alter system set db_recovery_file_dest='';
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~primary進行了4次 日誌切換
系統已更改。
SQL> select name,creator from v$archived_log;
SQL>
SQL> select PROCESS,CLIENT_PROCESS,SEQUENCE#,STATUS from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
RFS UNKNOWN 0 RECEIVING~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~lgwr的程式沒了
SQL> select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 ARCH
8_08_15\O1_MF_1_397_4B9TZJNZ_.ARC
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 ARCH
8_08_15\O1_MF_1_396_4B9TZNHN_.ARC
NAME CREATOR
-------------------------------------------------- -------
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 LGWR~~~~~~~~~~~~~~~~~~第1次switch logfile
8_08_15\O1_MF_1_398_4B9TZ65V_.ARC
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00399_0661538341.00 ARCH~~~~~~~~~~~~~~~~~~第2次
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00400_0661538341.00 ARCH~~~~~~~~~~~~~~~~~~第3次
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00401_0661538341.00 ARCH~~~~~~~~~~~~~~~~~~第4次
NAME CREATOR
-------------------------------------------------- -------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~注意 過了很長時間 大約 4分鐘才收到
傳遞到了standby的本地歸檔位置
SQL> select PROCESS,CLIENT_PROCESS,SEQUENCE#,STATUS from v$managed_standby
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
RFS UNKNOWN 0 RECEIVING~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~lgwr的程式沒了
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL>
SQL> select name,creator from v$archived_log;
NAME CREATOR
-------------------------------------------------- -------
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00401_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00402_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00403_0661538341.00 LGWR~~~~~~~~~~~~~~~~~~~~~~~~又變成lgwr來處理的了~~
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00404_0661538341.00 LGWR
已選擇308行。
SQL> select PROCESS,CLIENT_PROCESS,SEQUENCE#,STATUS from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
RFS UNKNOWN 0 ATTACHED
RFS LGWR 405 WRITING~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~丫的 這個程式又開啟了
RFS UNKNOWN 0 RECEIVING
看下standby的 alert.log
ALTER SYSTEM SET db_recovery_file_dest='' SCOPE=BOTH;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~實驗開始關閉這個位置
Fri Aug 15 11:01:05 2008
RFS[1]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_15\O1_MF_1_398_4B9TZ65V_.ARC'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_15\O1_MF_1_398_4B9TZ65V_.ARC'
Created Oracle managed file D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_15\O1_MF_1_398_4B9TZ65V_.ARC~~~~~~~~~~~~~~~~~
RFS[1]: No standby redo logfiles created
Fri Aug 15 11:01:06 2008
Errors in file d:\standbyphysical\udump\standby1_rfs_4052_xh080729.trc:
ORA-16032: parameter STANDBY_ARCHIVE_DEST destination string cannot be translated~~~~~~~~~~~~~~~~~~
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 1860
RFS[3]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00399_0661538341.001'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00399_0661538341.001''~~~~~~~~~~~~~~~arcn'
RFS[3]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00400_0661538341.001'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00400_0661538341.001''~~~~~~~~~~~~~~~arcn'
RFS[3]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00401_0661538341.001'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00401_0661538341.001'''~~~~~~~~~~~~~~~arcn'
Fri Aug 15 11:14:49 2008
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 2256
RFS[4]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: No standby redo logfiles created
Fri Aug 15 11:14:50 2008
RFS[3]: No standby redo logfiles created
RFS[3]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00402_0661538341.001'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00402_0661538341.001'~~~~~~~~~~~~~~~arcn'
Fri Aug 15 11:14:51 2008
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[5]: Assigned to RFS process 3692
RFS[5]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Fri Aug 15 11:14:55 2008
RFS[4]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00403_0661538341.001'~~~~~~~~~~~~~~~lgwr這個程式 有了又~~~所以lgwr
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00403_0661538341.001'
RFS[4]: No standby redo logfiles created
Fri Aug 15 11:15:28 2008
RFS[4]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00404_0661538341.001''~~~~~~~~~~~~~~~lgwr這個程式 有了又~~~所以lgwr
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00404_0661538341.001'
RFS[4]: No standby redo logfiles created
看下是否跟 standby_file_management有關係
SQL> show parameter standby_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~現在為手動
standby_file_management string manual
SQL>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
我們修改為auto下看看會是什麼情況
SQL> alter system set standby_file_management='auto';
系統已更改。
SQL> show parameter standby_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string auto
SQL>
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00404_0661538341.00 LGWR
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00405_0661538341.00 LGWR~~~~~~~~~~~~~~~~~~第一次
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00406_0661538341.00 LGWR~~~~~~~~~~~~~~~~第2次
1
NAME CREATOR
-------------------------------------------------- -------
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00407_0661538341.00 LGWR~~~~~~~~~~~~~~~第3次
1
已選擇308行。~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~所以standby_file_management~~沒啥關係~~~~~
SQL> alter system archive log current;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`歸檔當前日誌 也傳遞到standby庫
SQL> select PROCESS,CLIENT_PROCESS,SEQUENCE#,STATUS from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
RFS UNKNOWN 0 RECEIVING
RFS LGWR 409 WRITING
RFS UNKNOWN 0 RECEIVING
SQL> select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00408_0661538341.00 LGWR
1
已選擇308行。~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
系統已更改。
SQL>
SQL> select group#,status,sequence# from v$log
2 ;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 INACTIVE 408
2 CURRENT 409
3 INACTIVE 407
SQL> alter system archive log current;
系統已更改。
SQL> select group#,status,sequence# from v$log
2 ;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 INACTIVE 408
2 ACTIVE 409
3 CURRENT 410
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~歸檔也造成日誌切換
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
1603235
SQL> alter system archive log current;
系統已更改。
SQL> select group#,status,sequence# from v$log
2 ;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 CURRENT 411
2 INACTIVE 409
3 INACTIVE 410
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
1606272
SQL>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~注意歸檔日誌不產生CKPT~~~~~~~~~~~~~
SQL> select PROCESS,CLIENT_PROCESS,SEQUENCE#,STATUS from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
RFS UNKNOWN 0 RECEIVING
RFS LGWR 411 WRITING
RFS UNKNOWN 0 RECEIVING
SQL> select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00409_0661538341.00 LGWR~~~~~~~~~~~~~~~~~~~~~~~經過2次歸檔傳遞到standby的歸檔日誌
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00410_0661538341.00 LGWR
1
已選擇308行。
ri Aug 15 13:35:48 2008
RFS[4]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00409_0661538341.001'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00409_0661538341.001'
RFS[4]: No standby redo logfiles created
RFS[4]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00410_0661538341.001'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00410_0661538341.001'
RFS[4]: No standby redo logfiles created
RFS[4]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00411_0661538341.001'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00411_0661538341.001'
RFS[4]: No standby redo logfiles created
SQL> alter system set db_recovery_file_dest='D:\STANDBYPHYSICAL\ARCHIVE'
2 ;
系統已更改。
SQL> alter system archive log current;
系統已更改。
SQL> alter system archive log current;
系統已更改。
SQL> alter system archive log current;
系統已更改。
SQL> select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00411_0661538341.00 LGWR
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00412_0661538341.00 LGWR~~~~~~~~~~~~~~~``1
1
NAME CREATOR
-------------------------------------------------- -------
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 LGWR~~~~~~~~~~~~~~~~~2
8_08_15\O1_MF_1_413_4BB8FS7B_.ARC
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 LGWR~~~~~~~~~~~~3
8_08_15\O1_MF_1_414_4BB8FWVC_.ARC
~~~~~~~~~再實驗下 RFS 程式
SQL> select PROCESS,CLIENT_PROCESS,SEQUENCE#,STATUS from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
RFS UNKNOWN 0 RECEIVING
RFS LGWR 415 WRITING
RFS UNKNOWN 0 RECEIVING
SQL> alter system set db_recovery_file_dest='';
系統已更改。
SQL> select PROCESS,CLIENT_PROCESS,SEQUENCE#,STATUS from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
RFS UNKNOWN 0 RECEIVING
RFS LGWR 415 WRITING
RFS UNKNOWN 0 RECEIVING
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> select PROCESS,CLIENT_PROCESS,SEQUENCE#,STATUS from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
RFS UNKNOWN 0 RECEIVING~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~關掉了lgwr
RFS UNKNOWN 0 RECEIVING
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> select PROCESS,CLIENT_PROCESS,SEQUENCE#,STATUS from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
RFS UNKNOWN 0 RECEIVING~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~還是關的
RFS UNKNOWN 0 RECEIVING
SQL> select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 LGWR
8_08_15\O1_MF_1_414_4BB8FWVC_.ARC
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 LGWR~~~~~~~~~~~~~~~~~~~~~~`這麼多次歸檔只多了一個
8_08_15\O1_MF_1_415_4BB8G28S_.ARC
standby alert.log
Fri Aug 15 14:52:38 2008
ALTER SYSTEM SET db_recovery_file_dest='' SCOPE=BOTH;
Fri Aug 15 14:53:22 2008
RFS[4]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_15\O1_MF_1_415_4BB8G28S_.ARC'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_15\O1_MF_1_415_4BB8G28S_.ARC'
Created Oracle managed file D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\2008_08_15\O1_MF_1_415_4BB8G28S_.ARC
RFS[4]: No standby redo logfiles created
Fri Aug 15 14:53:23 2008
Errors in file d:\standbyphysical\udump\standby1_rfs_2256_xh080729.trc:
ORA-16032: parameter STANDBY_ARCHIVE_DEST destination string cannot be translated
SQL> select name,creator from v$archived_log;
NAME CREATOR
-------------------------------------------------- -------
D:\STANDBYPHYSICAL\ARCHIVE\STANDBY1\ARCHIVELOG\200 LGWR
8_08_15\O1_MF_1_415_4BB8G28S_.ARC
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00416_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00417_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00418_0661538341.00 ARCH
1
NAME CREATOR
-------------------------------------------------- -------
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00419_0661538341.00 ARCH
1
已選擇308行。~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`過了一會著幾個日誌才出來由arcH完成
SQL> select PROCESS,CLIENT_PROCESS,SEQUENCE#,STATUS from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
RFS UNKNOWN 0 ATTACHED
RFS UNKNOWN 0 RECEIVING
SQL>
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL>
SQL> select PROCESS,CLIENT_PROCESS,SEQUENCE#,STATUS from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
RFS UNKNOWN 0 ATTACHED
RFS LGWR 422 WRITING~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~LGWR出來了
RFS UNKNOWN 0 RECEIVING
SQL> select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00419_0661538341.00 ARCH
1
NAME CREATOR
-------------------------------------------------- -------
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00420_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00421_0661538341.00 LGWR~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~``由lgwr產生了
1
已選擇308行。
總結：經過反覆2次實驗，發現在no standbylogfile 時候 當日志傳輸配置的是lgwr~~~~~~~時候會歸檔到standby_archive_dest中，建立的程式是lgwr直接傳遞過去
standby_archive_dest沒有，alert.log中會先記錄一個錯誤 ORA-16032: parameter STANDBY_ARCHIVE_DEST destination string cannot be translated~ ，然後會記錄Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST ，將使用standby_archive_dest 預設值為db_reocvery_file_dest，會傳遞到db_recovery_file_dest中，如果db_reocvery_file_dest也沒有 建立者還是lgwr，如果db_recovery_file_dest 也沒有設定
那麼 在主庫向standby傳遞日誌的時候會很慢 並且關閉了rfs lgwr程式，啟動了預設的arch程式歸檔到standby的本地歸檔位置log_archive_dest_1
,但過經過幾次傳遞 oracle又將自動啟動lgwr傳遞到standby的本地歸檔位置log_archive_dest_1
~~~~~~~~~~新的實驗環境
SQL> alter system set db_recovery_file_dest='D:\STANDBYPHYSICAL\ARCHIVE';
系統已更改。
SQL> alter system set standby_archive_dest='D:\STANDBYPHYSICAL\ARCHIVE';
系統已更改。
SQL> alter database add standby logfile group 4 'D:\standbyphysical\STANDBYRD01.
log'reuse;
資料庫已更改。
\
SQL> show parameter log_archive_dest_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=standby1 lgwr valid_fo
r=(online_logfiles,primary_rol
e) db_unique_name=standby1
SQL>
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL>
SQL> select name,creator from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00422_0661538341.00 LGWR
NAME CREATOR
-------------------------------------------------- -------
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00423_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE\ARC00424_0661538341.001 LGWR
已選擇308行。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00425_0661538341.00 ARCH
1
NAME CREATOR
-------------------------------------------------- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00426_0661538341.001 LGWR
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00427_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE\ARC00428_0661538341.001 LGWR~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~lgwr sync情況下預設sync 怎麼又開始 2個目錄交替使用了呢
Fri Aug 15 15:59:14 2008
ARC1: Evaluating archive log 4 thread 1 sequence 423
Fri Aug 15 15:59:15 2008
RFS[7]: No standby redo logfiles of size 20480 blocks available
Fri Aug 15 15:59:15 2008
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00423_0661538341.001'~~~~~~~~~~~~~~~~~~~~~第一個位置 ~
Fri Aug 15 15:59:19 2008
RFS[7]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE\ARC00424_0661538341.001'~~~~~~~~~~~~~~
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE\ARC00424_0661538341.001''~~~~~~~~~~~~~~第2個位置
RFS[7]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
Fri Aug 15 16:00:18 2008
ARC1: Evaluating archive log 4 thread 1 sequence 425
Fri Aug 15 16:00:18 2008
RFS[7]: No standby redo logfiles of size 20480 blocks available
Fri Aug 15 16:00:18 2008
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00425_0661538341.001'
Fri Aug 15 16:00:19 2008
RFS[7]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE\ARC00426_0661538341.001'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE\ARC00426_0661538341.001'
RFS[7]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
Fri Aug 15 16:00:23 2008
ARC1: Evaluating archive log 4 thread 1 sequence 427
Fri Aug 15 16:00:24 2008
RFS[7]: No standby redo logfiles of size 20480 blocks available
Fri Aug 15 16:00:24 2008
RFS[7]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE\ARC00428_0661538341.001'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE\ARC00428_0661538341.001'
Fri Aug 15 16:00:25 2008
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00427_0661538341.001'
RFS[7]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL>
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00431_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE\ARC00432_0661538341.001 LGWR
已選擇308行。
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=standby1 lgwr valid_fo
r=(online_logfiles,primary_rol
e) db_unique_name=standby1
SQL> alter system set log_archive_dest_2=' service=standby1 lgwr sync valid_for=
(online_logfiles,primary_role) db_unique_name=standby1';
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL>
D:\STANDBYPHYSICAL\ARCHIVE\ARC00434_0661538341.001 LGWR
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00435_0661538341.00 ARCH
1
NAME CREATOR
-------------------------------------------------- -------
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00436_0661538341.00 ARCH
1
D:\STANDBYPHYSICAL\ARCHIVE\ARC00437_0661538341.001 LGWR~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~還是2次2次迴圈使用
已選擇308行。
-- Connected User is Valid
RFS[9]: Assigned to RFS process 1688
RFS[9]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[9]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
Fri Aug 15 18:05:26 2008
ARC1: Evaluating archive log 4 thread 1 sequence 436
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00436_0661538341.001'~~~~~~~~~~~~~~~~~~~~~~~~~第一個位置
Fri Aug 15 18:05:26 2008
RFS[9]: No standby redo logfiles of size 20480 blocks available
RFS[9]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE\ARC00437_0661538341.001'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE\ARC00437_0661538341.001'~~~~~~~~~~~~~~~~~~~~~~第2個位置
RFS[9]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~```08.08.18實驗
SQL> select process,client_process,status,sequence# from v$managed_standby;
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
RFS LGWR WRITING 443
RFS UNKNOWN RECEIVING 0
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=standby1 lgwr sync va
lid_for=(online_logfiles,prima
ry_role) db_unique_name=standb
y1
SQL>
SQL> show parameter standby_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string D:\STANDBYPHYSICAL\ARCHIVE
SQL>
SQL> select name,creator,applied from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00446_0661538341.00 ARCH YES
1
已選擇308行。
SQL> alter system switch logfile;
系統已更改。
SQL> /
系統已更改。
SQL> /
系統已更改。
SQL> /
系統已更改。
SQL>
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00447_0661538341.00 ARCH NO
1
NAME CREATOR APP
-------------------------------------------------- ------- ---
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00448_0661538341.00 ARCH NO
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00449_0661538341.00 ARCH NO
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00450_0661538341.00 ARCH NO
1
已選擇312行。 都是由本地歸檔的了standby
SQL> select process,client_process,status,sequence# from v$managed_standby;
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CLOSING 450~~~~~~~~~~~~~~~~~~~~~~本地程式歸檔到第 450
RFS LGWR WRITING 451
RFS UNKNOWN RECEIVING 0
RFS UNKNOWN RECEIVING 0
SQL> alter system set log_archive_dest_2='service=standby1 lgwr async valid_for=
(online_logfiles,primary_role) db_unique_name=standby1';
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> /
系統已更改。
SQL> /
系統已更改。
SQL> select process,client_process,status,sequence# from v$managed_standby;
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CLOSING 452
RFS LGWR WRITING 454~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~注意 453由lgwr歸檔了
RFS UNKNOWN RECEIVING 0
RFS UNKNOWN RECEIVING 0
SQL> select name,creator,applied from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00451_0661538341.00 ARCH NO
1
NAME CREATOR APP
-------------------------------------------------- ------- ---
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00452_0661538341.00 ARCH NO
1
D:\STANDBYPHYSICAL\ARCHIVE\ARC00453_0661538341.001 LGWR NO
又開始迴圈了
ARC1: Evaluating archive log 4 thread 1 sequence 447
Mon Aug 18 11:08:02 2008
RFS[1]: No standby redo logfiles of size 20480 blocks available
RFS[1]: Waiting for thread 1 sequence 447 archival to complete
Mon Aug 18 11:08:02 2008
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00447_0661538341.001'
kccrsz: expanded controlfile section 11 from 308 to 336 records
requested to grow by 6 record(s); added 1 block(s) of records
Controlfile has resized from 196 to 198 blocks.
Mon Aug 18 11:08:03 2008
RFS[1]: Archival of thread 1 sequence 447 complete
RFS[1]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
Mon Aug 18 11:08:04 2008
ARC1: Evaluating archive log 4 thread 1 sequence 448
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00448_0661538341.001'
Mon Aug 18 11:08:04 2008
RFS[1]: No standby redo logfiles of size 20480 blocks available
RFS[1]: Waiting for thread 1 sequence 448 archival to complete
RFS[1]: Archival of thread 1 sequence 448 complete
RFS[1]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
Mon Aug 18 11:08:09 2008
db_recovery_file_dest_size of 2048 MB is 0.88% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Aug 18 11:08:14 2008
ARC1: Evaluating archive log 4 thread 1 sequence 449
Mon Aug 18 11:08:14 2008
RFS[1]: No standby redo logfiles of size 20480 blocks available
Mon Aug 18 11:08:14 2008
RFS[1]: Waiting for thread 1 sequence 449 archival to complete
Mon Aug 18 11:08:15 2008
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00449_0661538341.001'
RFS[1]: Archival of thread 1 sequence 449 complete
RFS[1]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
Mon Aug 18 11:08:16 2008
ARC1: Evaluating archive log 4 thread 1 sequence 450
Mon Aug 18 11:08:17 2008
RFS[1]: No standby redo logfiles of size 20480 blocks available
RFS[1]: Waiting for thread 1 sequence 450 archival to complete
Mon Aug 18 11:08:17 2008
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00450_0661538341.001'
Mon Aug 18 11:08:18 2008
RFS[1]: Archival of thread 1 sequence 450 complete
RFS[1]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
Mon Aug 18 11:10:27 2008
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 4072
RFS[4]: Identified database type as 'physical standby'
Primary database is in STANDBY RESYNCHRONIZATION mode
Mon Aug 18 11:10:38 2008
ARC1: Evaluating archive log 4 thread 1 sequence 451
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00451_0661538341.001'
Mon Aug 18 11:10:42 2008
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[5]: Assigned to RFS process 3564
RFS[5]: Identified database type as 'physical standby'
Primary database is in STANDBY RESYNCHRONIZATION mode
RFS[5]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
Mon Aug 18 11:10:43 2008
ARC1: Evaluating archive log 4 thread 1 sequence 452
Mon Aug 18 11:10:43 2008
RFS[5]: No standby redo logfiles of size 20480 blocks available
Mon Aug 18 11:10:43 2008
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00452_0661538341.001'
Mon Aug 18 11:10:47 2008
RFS[5]: Archived Log: 'D:\STANDBYPHYSICAL\ARCHIVE\ARC00453_0661538341.001'
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE\ARC00453_0661538341.001'
RFS[5]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
SQL> alter system set log_archive_dest_2='service=standby1 lgwr valid_for=(onli
ne_logfiles,primary_role) db_unique_name=standby1';
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00455_0661538341.00 ARCH NO
1
NAME CREATOR APP
-------------------------------------------------- ------- ---
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00456_0661538341.00 ARCH NO
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00457_0661538341.00 ARCH NO
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00458_0661538341.00 ARCH NO
1
已選擇320行。
Mon Aug 18 11:15:36 2008
RFS[6]: No standby redo logfiles of size 20480 blocks available
RFS[6]: Waiting for thread 1 sequence 455 archival to complete
Mon Aug 18 11:15:36 2008
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00455_0661538341.001'
Mon Aug 18 11:15:37 2008
RFS[6]: Archival of thread 1 sequence 455 complete
RFS[6]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
Mon Aug 18 11:15:39 2008
ARC1: Evaluating archive log 4 thread 1 sequence 456
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00456_0661538341.001'
Mon Aug 18 11:15:39 2008
RFS[6]: No standby redo logfiles of size 20480 blocks available
RFS[6]: Waiting for thread 1 sequence 456 archival to complete
RFS[6]: Archival of thread 1 sequence 456 complete
RFS[6]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
Mon Aug 18 11:15:41 2008
ARC1: Evaluating archive log 4 thread 1 sequence 457
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00457_0661538341.001'
Mon Aug 18 11:15:41 2008
RFS[6]: No standby redo logfiles of size 20480 blocks available
RFS[6]: Waiting for thread 1 sequence 457 archival to complete
RFS[6]: Archival of thread 1 sequence 457 complete
RFS[6]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
Mon Aug 18 11:15:48 2008
ARC1: Evaluating archive log 4 thread 1 sequence 458
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00458_0661538341.001'
Mon Aug 18 11:15:48 2008
RFS[6]: No standby redo logfiles of size 20480 blocks available
RFS[6]: Waiting for thread 1 sequence 458 archival to complete
RFS[6]: Archival of thread 1 sequence 458 complete
RFS[6]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
SQL> alter system set log_archive_dest_2='service=standby1 valid_for=(online_log
files,primary_role) db_unique_name=standby1';
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system set log_archive_dest_2='service=standby1 lgwr valid_for=(onli
ne_logfiles,primary_role) db_unique_name=standby1';
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00459_0661538341.00 ARCH NO
1
NAME CREATOR APP
-------------------------------------------------- ------- ---
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00460_0661538341.00 ARCH NO
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00461_0661538341.00 ARCH NO
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00462_0661538341.00 ARCH NO
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00464_0661538341.00 ARCH NO
NAME CREATOR APP
-------------------------------------------------- ------- ---
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00465_0661538341.00 ARCH NO
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00463_0661538341.00 ARCH NO
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00466_0661538341.00 ARCH NO
1
已選擇328行。
Mon Aug 18 11:17:57 2008
ARC1: Evaluating archive log 4 thread 1 sequence 459
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00459_0661538341.001'
Mon Aug 18 11:17:59 2008
RFS[4]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
Mon Aug 18 11:17:59 2008
ARC1: Evaluating archive log 4 thread 1 sequence 460
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00460_0661538341.001'
Mon Aug 18 11:18:03 2008
RFS[4]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
Mon Aug 18 11:18:04 2008
ARC1: Evaluating archive log 4 thread 1 sequence 461
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00461_0661538341.001'
Mon Aug 18 11:18:05 2008
RFS[4]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
Mon Aug 18 11:18:05 2008
ARC1: Evaluating archive log 4 thread 1 sequence 462
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00462_0661538341.001'
Mon Aug 18 11:22:17 2008
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[7]: Assigned to RFS process 3808
RFS[7]: Identified database type as 'physical standby'
Primary database is in MAXIMUM AVAILABILITY mode
RFS[7]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
Mon Aug 18 11:22:18 2008
ARC1: Evaluating archive log 4 thread 1 sequence 464
Mon Aug 18 11:22:18 2008
RFS[7]: No standby redo logfiles of size 20480 blocks available
Mon Aug 18 11:22:19 2008
RFS[7]: Waiting for thread 1 sequence 464 archival to complete
Mon Aug 18 11:22:19 2008
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00464_0661538341.001'
RFS[7]: Archival of thread 1 sequence 464 complete
RFS[7]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
Mon Aug 18 11:22:21 2008
RFS[4]: No standby redo logfiles of size 20480 blocks available
RFS[4]: Waiting for thread 1 sequence 465 archival to complete
Mon Aug 18 11:22:29 2008
ARC1: Evaluating archive log 4 thread 1 sequence 465
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00465_0661538341.001'
Mon Aug 18 11:22:29 2008
RFS[7]: No standby redo logfiles of size 20480 blocks available
RFS[7]: Waiting for thread 1 sequence 465 archival to complete
Mon Aug 18 11:22:30 2008
RFS[4]: Archival of thread 1 sequence 465 complete
RFS[4]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
Mon Aug 18 11:22:30 2008
ARC1: Evaluating archive log 4 thread 1 sequence 463
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00463_0661538341.001'
Mon Aug 18 11:22:30 2008
RFS[7]: Archival of thread 1 sequence 465 complete
RFS[7]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
Mon Aug 18 11:22:31 2008
ARC1: Evaluating archive log 4 thread 1 sequence 466
Mon Aug 18 11:22:31 2008
RFS[7]: No standby redo logfiles of size 20480 blocks available
RFS[7]: Waiting for thread 1 sequence 466 archival to complete
Mon Aug 18 11:22:31 2008
Committing creation of archivelog 'D:\STANDBYPHYSICAL\ARCHIVE1\ARC00466_0661538341.001'
Mon Aug 18 11:22:32 2008
RFS[7]: Archival of thread 1 sequence 466 complete
RFS[7]: Successfully opened standby log 4: 'D:\STANDBYPHYSICAL\STANDBYRD01.LOG'
總結:LGWR中 standby在接收歸檔日誌檔案時候,只有一組standbylogfile時 容易出現在2個位置standby_archive_dest ,standby的本地歸檔位置log_archive_dest_1
通過反覆實驗，多次檢視分析alert.log
發現 只要
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-607601/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle實驗記錄 （flashback,physical standby resetlogs）Oracle
- Physical Standby上開啟flashback database實驗日誌Database
- Oracle physical standbyOracle
- 最大效能Physical Standby設定記錄
- oracle實驗記錄 （手動建立 physical datagurad）Oracle
- oracle LOGICAL standby 日誌無法應用處理Oracle
- oracle實驗記錄 (oracle 10G dataguard(7)physical dg轉換)Oracle
- oracle日誌操作記錄Oracle
- oracle實驗記錄 (oracle 10G dataguard(5)實時應用)Oracle
- mysql audit-訪問日誌記錄應用MySql
- oracle實驗記錄 (oracle 10G dataguard(11)建立logical standby)Oracle
- Oracle補充日誌及日誌記錄規則Oracle
- 配置oracle 9i physical standby database時，duplicate命令的執行記錄OracleDatabase
- Oracle DG建立Physical Standby DatabaseOracleDatabase
- oracle Physical Standby failover stepOracleAI
- 建立Local Physical Standby Oracle9i standby 資料庫筆記Oracle資料庫筆記
- 記錄 | 實習日誌 9
- php日誌,記錄日誌PHP
- Util應用框架基礎(六) - 日誌記錄(一) - 正文框架
- Rust 實現日誌記錄功能Rust
- [zt]Logical STANDBY日誌應用延遲案例一則
- oracle實驗記錄 （flashback）Oracle
- oracle實驗記錄 （OMF）Oracle
- oracle實驗記錄 （NET）Oracle
- oracle實驗記錄 （audit）Oracle
- 日誌記錄器
- 【RAC】 oracleasm 工具對應的日誌記錄OracleASM
- 【DataGuard】Oracle 11g physical standby switchoverOracle
- Oracle10G Physical Standby Database setupOracleDatabase
- oracle實驗記錄 (oracle reset parameter)Oracle
- Standby_file_management引數導致日誌無法應用
- Linux作業系統啟動時自動啟用oracle standby備庫日誌應用Linux作業系統Oracle
- Util應用框架基礎(六) - 日誌記錄(三) - 寫入 Seq框架
- Util應用框架基礎(六) - 日誌記錄(四) - 寫入 Exceptionless框架Exception
- Oracle Data Redaction實驗記錄Oracle
- oracle實驗記錄 （block cleanout）OracleBloC
- oracle實驗記錄 （dump undo）Oracle
- oracle實驗記錄 （inlist card）Oracle