oracle實驗記錄 (physical standby 日誌應用方面)

fufuh2o發表於2009-06-26

實驗環境
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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章