ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (flashback,physical standby resetlogs)

oracle实验记录 (flashback,physical standby resetlogs)

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

闪回

闪回事物查询

SQL> select * from test;

         A
----------
         9
         2
         9
         9

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1874427

SQL> update test set a=8;

已更新4行。

SQL> update test set a=8;

已更新4行。

SQL> insert into test values (5);

已创建 1 行。

SQL> delete test where a=2;

已删除0行。

SQL> delete test where a=5;

已删除 1 行。

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1874454

SQL> select versions_starttime starttime,versions_endtime endtime,versions_opera
tion from test versions between scn 1874427 and 1874454 ;

STARTTIME            ENDTIME              V
-------------------- -------------------- -

 

 

 

SQL> commit
  2  ;

提交完成。~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~必须提交的才行

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1874516

SQL> select * from test;

         A
----------
         8
         8
         8
         8

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1874524

SQL> insert into test values (2);

已创建 1 行。

SQL> commit;

提交完成。

SQL> insert into test values (3);

已创建 1 行。

SQL> commit;

提交完成。

SQL> dlete test where a=3;
SP2-0734: 未知的命令开头 "dlete test..." - 忽略了剩余的行。
SQL> delete test where a=3;

已删除 1 行。

SQL> commit;

提交完成。

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1874544

SQL> select versions_starttime starttime,versions_endtime endtime,versions_opera
tion from test versions between scn 1874524 and 1874544 ;

STARTTIME            ENDTIME              V
-------------------- -------------------- -
22-8月 -08 02.39.53                       D
下午

22-8月 -08 02.39.32  22-8月 -08 02.39.53  I
下午                 下午

22-8月 -08 02.39.26                       I
下午

 


STARTTIME            ENDTIME              V
-------------------- -------------------- -

 

已选择7行。

SQL>

 

SQL> select versions_starttime starttime,versions_endtime endtime,versions_op
tion, versions_xid from test versions between scn 1874524 and 1874544 ;

STARTTIME            ENDTIME              V VERSIONS_XID
-------------------- -------------------- - ----------------
22-8月 -08 02.39.53                       D 0400090063050000
下午

22-8月 -08 02.39.32  22-8月 -08 02.39.53  I 04000B0063050000
下午                 下午

22-8月 -08 02.39.26                       I 0400080063050000
下午

 


STARTTIME            ENDTIME              V VERSIONS_XID
-------------------- -------------------- - ----------------

 

已选择7行。

SQL>

SQL> select versions_starttime starttime,versions_endtime endtime,versions_oper
tion, versions_xid from test versions between scn 1874524 and 1874544 order by~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~排序下
ERSIONS_STARTTIME;

STARTTIME            ENDTIME              V VERSIONS_XID
-------------------- -------------------- - ----------------
22-8月 -08 02.39.26                       I 0400080063050000             插入
下午

22-8月 -08 02.39.32  22-8月 -08 02.39.53  I 04000B0063050000~~~~~~~~~~~~~插入
下午                 下午

22-8月 -08 02.39.53                       D 0400090063050000~~~~~~~~~~~~~~~~~删除
下午

 


STARTTIME            ENDTIME              V VERSIONS_XID
-------------------- -------------------- - ----------------

 

已选择7行。

SQL>

 

分析下这些列
VERSIONS_STARTSCN
VERSIONS_STARTTIME
该记录操作时的scn 或时间,如果为空,表示该行记录是在查询范围外创建
的。
VERSIONS_ENDTIME
VERSIONS_ENDSCN 该记录失效时的scn 或时间,如果为空,说明记录当前时间在当前表内存在, 或者已经被删除了,可以配合着VERSIONS_OPERATION 列来看,如果
VERSIONS_OPERATION 列值为D,说明该列已被删除,如果该列为空,则
说明记录在这段时间无操作。

VERSIONS_XID 该操作的事务ID
VERSIONS_OPERATION 对该行执行的操作:I 表示insert,D 表示delete,U 表示update。
提示:对于索引键的update 操作,版本查询可能会将其识别成两个操作:
DELETE 和INSERT。


闪回事务查询

可以查处undo语句

SQL> desc flashback_transaction_query;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ---------------------------

 XID                                                RAW(8)
 START_SCN                                          NUMBER
 START_TIMESTAMP                                    DATE
 COMMIT_SCN                                         NUMBER
 COMMIT_TIMESTAMP                                   DATE
 LOGON_USER                                         VARCHAR2(30)
 UNDO_CHANGE#                                       NUMBER
 OPERATION                                          VARCHAR2(32)
 TABLE_NAME                                         VARCHAR2(256)
 TABLE_OWNER                                        VARCHAR2(32)
 ROW_ID                                             VARCHAR2(19)
 UNDO_SQL                                           VARCHAR2(4000)


SQL> select undo_sql from flashback_transaction_query where xid='04000B006305000~~~~~~~~~~~~~~~~刚才在闪回版本中查到的 事务操作时ID
0';

UNDO_SQL
--------------------------------------------------------------------------------

delete from "SYS"."TEST" where ROWID = 'AAAMcfAABAAAN2qAAE';

 

 

 

实验 主库resetlogs后 standby的情况


SQL> select db_unique_name,database_role from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE
------------------------------ ----------------
xhtest                         PRIMARY

SQL> select db_unique_name,database_role from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE
------------------------------ ----------------
standby1                       PHYSICAL STANDBY

SQL> select db_unique_name,database_role,OPEN_MODE,FLASHBACK_ON from v$database;


DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  FLA
------------------------------ ---------------- ---------- ---
standby1                       PHYSICAL STANDBY MOUNTED    NO

 

SQL> select db_unique_name,database_role,OPEN_MODE,FLASHBACK_ON from v$database;


DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  FLA
------------------------------ ---------------- ---------- ---
xhtest                         PRIMARY          READ WRITE NO

 

为了 可以补救 standby 我们给primary,standby 打开flashback on

SQL> archive log list;
数据库日志模式            存档模式
自动存档             启用
存档终点            D:\standbyphysical\archive1
最早的联机日志序列     527
下一个存档日志序列   0
当前日志序列           529
SQL> alter database flashback on;

数据库已更改。

SQL> select db_unique_name,database_role,OPEN_MODE,FLASHBACK_ON from v$database;


DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  FLA
------------------------------ ---------------- ---------- ---
standby1                       PHYSICAL STANDBY MOUNTED    YES

SQL>

 

 


SQL> alter database flashback on;

数据库已更改。

SQL> select db_unique_name,database_role,OPEN_MODE,FLASHBACK_ON from v$database;


DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  FLA
------------------------------ ---------------- ---------- ---
xhtest                         PRIMARY          MOUNTED    YES

 

开始实验
SQL> select name,applied,creator from v$archived_log;(standby1)

NAME                                               APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00527_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00528_0661538341.001 YES ARCH

已选择24行。


SQL> select name,applied,creator from v$archived_log;(xhtest)
standby1
F:\归档备份\ARC00527_0661538341.001
standby1
F:\归档备份\ARC00528_0661538341.001
standby1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~现在2边日志一致

 

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> flashback database to scn 1880962;

闪回完成。

SQL> select process,client_process,status from v$managed_standby;

PROCESS   CLIENT_P STATUS
--------- -------- ------------
ARCH      ARCH     CONNECTED
ARCH      ARCH     CONNECTED
RFS       UNKNOWN  ATTACHED


SQL> select name,applied,creator from v$archived_log
D:\STANDBYPHYSICAL\ARCHIVE\ARC00529_0661538341.001 NO  ARCH~~~~~~~~~~~~~~~~

已选择25行。

 

SQL> alter system switch logfile ;

系统已更改。
D:\STANDBYPHYSICAL\ARCHIVE\ARC00529_0661538341.001 NO  ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00001_0663438995.001 NO  ARCH~~~~~~~~~~~~~~~接受到resetlogs后的日志了

已选择26行。

SQL> alter database recover managed standby database disconnect from session;

数据库已更改。

SQL> select name,applied,creator from v$archived_log;

NAME                                               APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00527_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00528_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00529_0661538341.001 NO  ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00001_0663438995.001 YES ARCH~~~~~~~~~~~~~~~~~应用了新产生的
D:\STANDBYPHYSICAL\ARCHIVE\ARC00002_0663438995.001 YES ARCH~~~~~~~~~~~~~~应用了新产生的

已选择27行。

SQL> alter database commit to switchover to physical standby;

数据库已更改。


SQL> select SWITCHOVER_STATUS from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> alter database commit to switchover to primary;

数据库已更改。

 


SQL> select db_unique_name,database_role,OPEN_MODE,FLASHBACK_ON from v$database;


DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  FLA
------------------------------ ---------------- ---------- ---
standby1                       PRIMARY          READ WRITE YES

 

SQL> select db_unique_name,database_role,OPEN_MODE,FLASHBACK_ON from v$database;


DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE  FLA
------------------------------ ---------------- ---------- ---
xhtest                         PHYSICAL STANDBY MOUNTED    YES

 


08.08.25实验~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB_UNIQUE_NAME  SWITCHOVER_STATUS    DATABASE_ROLE    FLA OPEN_MODE
--------------- -------------------- ---------------- --- ----------
standby1        RECOVERY NEEDED      PHYSICAL STANDBY YES MOUNTED


SQL> select db_unique_name,SWITCHOVER_STATUS,database_role,FLASHBACK_ON,open_mod
e from v$database;

DB_UNIQUE_NAME  SWITCHOVER_STATUS    DATABASE_ROLE    FLA OPEN_MODE
--------------- -------------------- ---------------- --- ----------
standby1        RECOVERY NEEDED      PHYSICAL STANDBY YES MOUNTED

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


SQL> select name,creator,applied from v$archived_log;

D:\STANDBYPHYSICAL\ARCHIVE\ARC00003_0663438995.001 FGRD    YES
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00004_0663438995.00 FGRD    YES
1

xhtest                                             FGRD    NO
D:\STANDBYPHYSICAL\ARCHIVE\ARC00005_0663438995.001 ARCH    NO

NAME                                               CREATOR APP
-------------------------------------------------- ------- ---
D:\STANDBYPHYSICAL\ARCHIVE\ARC00006_0663438995.001 ARCH    NO
D:\STANDBYPHYSICAL\ARCHIVE\ARC00007_0663438995.001 ARCH    NO
D:\STANDBYPHYSICAL\ARCHIVE\ARC00008_0663438995.001 ARCH    NO


SQL> show parameter log_archive_dest_2 (xhtest)上

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string       service=standby1  valid_for=(
                                                 online_logfiles,primary_role)
                                                 db_unique_name=standby1

 


select name from v$archived_log;
F:\归档备份\ARC00009_0663438995.001           在primary产生的最后一个归档日志是09


SQL> select name,creator,applied from v$archived_log;

NAME                                               CREATOR APP
-------------------------------------------------- ------- ---
D:\STANDBYPHYSICAL\ARCHIVE\ARC00006_0663438995.001 ARCH    YES
D:\STANDBYPHYSICAL\ARCHIVE\ARC00007_0663438995.001 ARCH    YES
D:\STANDBYPHYSICAL\ARCHIVE\ARC00008_0663438995.001 ARCH    YES
D:\STANDBYPHYSICAL\ARCHIVE\ARC00009_0663438995.001 ARCH    YES

已选择35行。                     standby一直在开着应用  应用到最新归档的09日志了

 

SQL> select current_scn from v$database;(xhtest)

CURRENT_SCN
-----------
    1936767

SQL> select group#,sequence#,status from v$log;(xhtest)

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          9 INACTIVE
         2          8 INACTIVE
         3         10 CURRENT

 


SQL> alter system switch logfile;(xhtest)

系统已更改。

 

SQL> select name,creator,applied from v$archived_log;(standby1)

D:\STANDBYPHYSICAL\ARCHIVE\ARC00010_0663438995.001 ARCH    YES~~~~~~~~~~~~~~~~~~~~~~~~最新收到primary的归档日志已经应用了

已选择36行。1936767SCN 包含在这个日志里了

SQL> alter system switch logfile;(xhtest)

系统已更改。
SQL> select name,creator,applied from v$archived_log;(standby1)
D:\STANDBYPHYSICAL\ARCHIVE\ARC00011_0663438995.001 ARCH    YES

已选择37行。~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 应用到11了

此时候主库 不完全恢复 ~到 日志  10 那里
用的flashback

 


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> flashback database to scn 1936767;闪回到日志10

闪回完成。

SQL> alter database open resetlogs;

数据库已更改。

SQL> select group#,sequence#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          0 UNUSED
         3          0 UNUSED

 


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 ATTACHED
RFS       UNKNOWN           0 RECEIVING


SQL> select name,creator,applied from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\ARC00001_0663681150.001 ARCH    NO
D:\STANDBYPHYSICAL\ARCHIVE\ARC00002_0663681150.001 ARCH    NO

已选择39行。

SQL> alter database recover managed standby database disconnect from session;

数据库已更改。

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~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~mpro进程都没开启

 


SQL> select name,creator,applied from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\ARC00001_0663681150.001 ARCH    NO~~~~~~~~~~~~~~~~~~~根本没应用
D:\STANDBYPHYSICAL\ARCHIVE\ARC00002_0663681150.001 ARCH    NO

已选择39行。
alert.log中错误信息
Recovery interrupted!
Mon Aug 25 11:54:47 2008
Errors in file d:\standbyphysical\bdump\standby1_mrp0_1264.trc:
ORA-19906: recovery target incarnation changed during recovery~~~~~~~~~~~~~~~~~~~~~~~~

Mon Aug 25 11:55:44 2008
alter database recover managed standby database disconnect from session
MRP0 started with pid=18, OS id=2268
Managed Standby Recovery not using Real Time Apply
Datafile 1 (ckpscn 1936871) is orphaned on incarnation#=11
MRP0: Background Media Recovery terminated with error 19909
Mon Aug 25 11:55:49 2008
Errors in file d:\standbyphysical\bdump\standby1_mrp0_2268.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: 'D:\STANDBYPHYSICAL\SYSTEM01.DBF'

Mon Aug 25 11:55:49 2008
Errors in file d:\standbyphysical\bdump\standby1_mrp0_2268.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: 'D:\STANDBYPHYSICAL\SYSTEM01.DBF'

 

已经不能正常应用解决该问题

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> flashback database to scn 1936767;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

闪回完成。

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> select PROCESS,CLIENT_PROCESS,SEQUENCE#,STATUS from v$managed_standby;

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED

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

SQL> alter database recover managed standby database disconnect from session;

数据库已更改。

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
MRP0      N/A               3 WAIT_FOR_LOG

SQL> select name,creator,applied from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\ARC00001_0663681150.001 ARCH    YES
D:\STANDBYPHYSICAL\ARCHIVE\ARC00002_0663681150.001 ARCH    YES~~~~~~~~~~~~~~~~~~~~~~~~应用了ok 搞定

已选择39行。


小结:如果primary 不完全恢复到 一个时间,而standby已经应用了这个时间后的日志 那么~~~~standby在接受primary 产生的新归档日志 将不能 应用
只有将standby 恢复到primary 不完全恢复到的时间点才行


实验2:
看看是否必须和 primary 恢复到一样的SCN 时间点

 


SQL> select * from test;(xhtest)

         A
----------
         8
         8
         2
         8
         8

SQL> select * from test;

         A
----------
         8
         8
         2
         8
         8

SQL> insert into test values(444);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1942255

SQL>
SQL>
SQL> insert into test values(555);

已创建 1 行。

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1942261

SQL> commit;

提交完成。

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1942265

SQL>

SQL> select group#,sequence#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          1 INACTIVE
         2          2 INACTIVE
         3          3 CURRENT


 SQL> alter system  switch logfile;

系统已更改。
SQL> select name,creator,applied from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\ARC00003_0663681150.001 ARCH    YES~~~~~~~~~~~~~~应用了新传过来的 primary的 归档日志3

已选择40行。

 

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> flashback  database to scn 1942255;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~primary 到的地方

闪回完成。

 


SQL> alter database open resetlogs;

数据库已更改。

SQL> select group#,sequence#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          0 UNUSED
         3          0 UNUSED


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
MRP0      N/A               4 WAIT_FOR_LOG

SQL> alter system switch logfile;

系统已更改。

SQL> alter system switch logfile;

系统已更改。


SQL> select name,creator,applied from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\ARC00001_0663689986.001 ARCH    NO
D:\STANDBYPHYSICAL\ARCHIVE\ARC00002_0663689986.001 ARCH    NO

已选择42行。


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> alter database recover managed standby database disconnect from session;

数据库已更改。

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,applied from v$archived_log;

D:\STANDBYPHYSICAL\ARCHIVE\ARC00001_0663689986.001 ARCH    NO
D:\STANDBYPHYSICAL\ARCHIVE\ARC00002_0663689986.001 ARCH    NO

已选择42行。
standby alert.log
alter database recover managed standby database disconnect from session
MRP0 started with pid=17, OS id=2908
Managed Standby Recovery not using Real Time Apply
Datafile 1 (ckpscn 1942528) is orphaned on incarnation#=12
MRP0: Background Media Recovery terminated with error 19909
Mon Aug 25 14:33:58 2008
Errors in file d:\standbyphysical\bdump\standby1_mrp0_2908.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: 'D:\STANDBYPHYSICAL\SYSTEM01.DBF'

Mon Aug 25 14:33:58 2008
Errors in file d:\standbyphysical\bdump\standby1_mrp0_2908.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: 'D:\STANDBYPHYSICAL\SYSTEM01.DBF'

Mon Aug 25 14:33:58 2008
Completed: alter database recover managed standby database di

 


SQL> flashback database to scn  1942261;(standby1) ~~~~~~~~~~~~~~~~~~~~~~注意与primary flashback database scn不一样

闪回完成。

SQL> select db_unique_name,SWITCHOVER_STATUS,database_role,FLASHBACK_ON,open_mod
e from v$database;

DB_UNIQUE_NAME  SWITCHOVER_STATUS    DATABASE_ROLE    FLA OPEN_MODE
--------------- -------------------- ---------------- --- ----------
standby1        NOT ALLOWED          PHYSICAL STANDBY YES READ ONLY

 

 

SQL> select PROCESS,CLIENT_PROCESS,SEQUENCE#,STATUS from v$managed_standb

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
RFS       UNKNOWN           0 RECEIVING


SQL> alter database recover managed standby database disconnect from session

数据库已更改。

SQL> alter database recover managed standby database disconnect from session;

数据库已更改。

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


SQL> select name,creator,applied from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\ARC00001_0663689986.001 ARCH    NO
D:\STANDBYPHYSICAL\ARCHIVE\ARC00002_0663689986.001 ARCH    NO

已选择42行。
Managed Standby Recovery not using Real Time Apply
Datafile 1 (ckpscn 1942263) is orphaned on incarnation#=12
MRP0: Background Media Recovery terminated with error 19909
Mon Aug 25 15:03:15 2008
Errors in file d:\standbyphysical\bdump\standby1_mrp0_392.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: 'D:\STANDBYPHYSICAL\SYSTEM01.DBF'

Mon Aug 25 15:03:15 2008
Errors in file d:\standbyphysical\bdump\standby1_mrp0_392.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: 'D:\STANDBYPHYSICAL\SYSTEM01.DBF'

Mon Aug 25 15:03:15 2008
Completed: alter database recover managed standby database di
Mon Aug 25 15:04:09 2008
idle dispatcher 'D000' terminated, pid = (10, 1)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~失败


SSQL> 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> flashback  database to scn  1942255;~~~~~~~~~~~~~~~~~~~~~~~~~~与primary闪回位置一样 SCN

闪回完成。

SQL> startup force ;
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 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

SQL> alter database recover managed standby database disconnect from session;

SQL> select name,creator,applied from v$archived_log;

D:\STANDBYPHYSICAL\ARCHIVE\ARC00001_0663689986.001 ARCH    YES
D:\STANDBYPHYSICAL\ARCHIVE\ARC00002_0663689986.001 ARCH    YES

已选择42行。


   结论一定要与primary 不完全恢复到的时间点一样

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12020513/viewspace-607602/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    426873