ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (physical standby 日志应用方面)

oracle实验记录 (physical standby 日志应用方面)

原创 Linux操作系统 作者:fufuh2o 时间:2009-06-26 10:04:27 0 删除 编辑

实验环境
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    426847