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部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-607602/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle實驗記錄 （physical standby 日誌應用方面）Oracle
- oracle實驗記錄 （flashback）Oracle
- Physical Standby上開啟flashback database實驗日誌Database
- Oracle 10g FLASHBACK DATABASE 實驗記錄Oracle 10gDatabase
- Oracle physical standbyOracle
- 最大效能Physical Standby設定記錄
- 主庫歷經open resetlogs後，如何redo apply 物理備庫_flashback physical standby dbAPP
- oracle實驗記錄 （手動建立 physical datagurad）Oracle
- oracle實驗記錄 (oracle 10G dataguard(10)flashback 與dg)Oracle
- oracle實驗記錄 (oracle 10G dataguard(7)physical dg轉換)Oracle
- oracle實驗記錄 (oracle 10G dataguard(11)建立logical standby)Oracle
- oracle實驗記錄 （恢復-使用resetlogs open前備份恢復）Oracle
- 配置oracle 9i physical standby database時，duplicate命令的執行記錄OracleDatabase
- Oracle DG建立Physical Standby DatabaseOracleDatabase
- oracle Physical Standby failover stepOracleAI
- 建立Local Physical Standby Oracle9i standby 資料庫筆記Oracle資料庫筆記
- oracle實驗記錄 （OMF）Oracle
- oracle實驗記錄 （NET）Oracle
- oracle實驗記錄 （audit）Oracle
- 【DataGuarad】ORA-1153 trying to turn on Flashback for Physical Standby DatabaseDatabase
- 【DataGuard】Oracle 11g physical standby switchoverOracle
- Oracle10G Physical Standby Database setupOracleDatabase
- oracle實驗記錄 (oracle reset parameter)Oracle
- Oracle Data Redaction實驗記錄Oracle
- oracle實驗記錄 （block cleanout）OracleBloC
- oracle實驗記錄 （dump undo）Oracle
- oracle實驗記錄 （inlist card）Oracle
- oracle實驗記錄 (oracle 資料字典)Oracle
- oracle 10g physical standby 切換操作Oracle 10g
- ORACLE10g DataGuard 配置Physical Standby DatabaseOracleDatabase
- oracle 10g physical standby database creationOracle 10gDatabase
- DataGuard:Physical Standby Switchover
- Oracle檔案改名實驗記錄Oracle
- oracle實驗記錄 （選擇率）Oracle
- oracle實驗記錄 （dump logfile）Oracle
- oracle實驗記錄 （事務控制）Oracle
- oracle實驗記錄 （函式index）Oracle函式Index
- oracle實驗記錄 （bigfile tablespace）Oracle