oracle实验记录 （flashback,physical standby resetlogs）
闪回
闪回事物查询
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博客 ” ，链接：https://blog.itpub.net/12020513/viewspace-607602/ ，如需转载，请注明出处，否则将追究法律责任。
