UNDO文件丢失、控制文件丢失（实例、介质恢复起点小于丢失文件的起始SCN）
SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/shujukuai/undotbs02.dbf' size 10m;
Tablespace created.
SQL> select file#,status$,crscnwrp,crscnbas from file$;
FILE# STATUS$ CRSCNWRP CRSCNBAS
---------- ---------- ---------- ----------
1 2 0 5
2 2 0 600647
3 2 0 6678
4 2 0 10685
5 2 0 631813
6 2 0 673840
8 2 0 715465
7 2 0 712861
8 rows selected.
SQL> alter system set undo_tablespace=undotbs2 scope=both;
System altered.
SQL> select segment_id,segment_name,tablespace_name,status from dba_rollback_segs order by segment_id;
SEGMENT_ID SEGMENT_NAME TABLESPACE_NAME STATUS
---------- ------------------------------ ------------------------------ ----------------
0 SYSTEM SYSTEM ONLINE
1 _SYSSMU1$ UNDOTBS1 OFFLINE
2 _SYSSMU2$ UNDOTBS1 OFFLINE
3 _SYSSMU3$ UNDOTBS1 OFFLINE
4 _SYSSMU4$ UNDOTBS1 OFFLINE
5 _SYSSMU5$ UNDOTBS1 OFFLINE
6 _SYSSMU6$ UNDOTBS1 OFFLINE
7 _SYSSMU7$ UNDOTBS1 OFFLINE
8 _SYSSMU8$ UNDOTBS1 OFFLINE
9 _SYSSMU9$ UNDOTBS1 OFFLINE
10 _SYSSMU10$ UNDOTBS1 OFFLINE 这些都OFFLINE球了
SEGMENT_ID SEGMENT_NAME TABLESPACE_NAME STATUS
---------- ------------------------------ ------------------------------ ----------------
11 _SYSSMU11$ UNDOTBS2 ONLINE
12 _SYSSMU12$ UNDOTBS2 ONLINE
13 _SYSSMU13$ UNDOTBS2 ONLINE
14 _SYSSMU14$ UNDOTBS2 ONLINE
15 _SYSSMU15$ UNDOTBS2 ONLINE
16 _SYSSMU16$ UNDOTBS2 ONLINE
17 _SYSSMU17$ UNDOTBS2 ONLINE
18 _SYSSMU18$ UNDOTBS2 ONLINE
19 _SYSSMU19$ UNDOTBS2 ONLINE
20 _SYSSMU20$ UNDOTBS2 ONLINE
21 rows selected.
SQL> select * from v$rollname;
USN NAME
---------- -------------------------------------------------- 这个视图就只显示联机回滚段
0 SYSTEM
11 _SYSSMU11$
12 _SYSSMU12$
13 _SYSSMU13$
14 _SYSSMU14$
15 _SYSSMU15$
16 _SYSSMU16$
17 _SYSSMU17$
18 _SYSSMU18$
19 _SYSSMU19$
20 _SYSSMU20$
11 rows selected.
SQL> select * from v$rollname;
USN NAME
---------- --------------------------------------------------
0 SYSTEM
11 _SYSSMU11$
12 _SYSSMU12$
13 _SYSSMU13$
14 _SYSSMU14$
15 _SYSSMU15$
16 _SYSSMU16$
17 _SYSSMU17$
18 _SYSSMU18$
19 _SYSSMU19$
20 _SYSSMU20$
11 rows selected.
SQL> alter database backup controlfile to trace as '/u01/backup_dir/ctl.sql';
Database altered.
SQL> create table v(col1 number);
Table created.
SQL> insert into v values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into v values(2);
1 row created.
SQL> select xidusn,xidslot,xidsqn from v$transaction;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
20 23 4 看20号回滚段
1 row selected.
这里模拟未提交事务
重新打开一个会话B
SESSION 2>col dname for a50
SESSION 2>set linesize 170
SESSION 2>select substr(name,1,50) dname,checkpoint_change#,last_change#,offline_change#,creation_change# from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# CREATION_CHANGE#
-------------------------------------------------- ------------------ ------------ --------------- ----------------
/u01/app/oracle/oradata/shujukuai/system01.dbf 712951 712949 5
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf 712951 712949 600647
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf 712951 712949 6678
/u01/app/oracle/oradata/shujukuai/users01.dbf 712951 712949 10685
/u01/app/oracle/oradata/shujukuai/tbs01.dbf 712951 712949 631813
/u01/app/oracle/oradata/shujukuai/test2.dbf 712951 712949 673840
/u01/app/oracle/oradata/shujukuai/tbs1.dbf 712951 712949 712861
/u01/app/oracle/oradata/shujukuai/undotbs02.dbf 715466 0 715465
8 rows selected.
SESSION 2>select substr(name,1,50) dname,recover,fuzzy,checkpoint_change#,checkpoint_count from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- --- ------------------ ----------------
/u01/app/oracle/oradata/shujukuai/system01.dbf NO YES 712951 112
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf NO YES 712951 72
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf NO YES 712951 112
/u01/app/oracle/oradata/shujukuai/users01.dbf NO YES 712951 111
/u01/app/oracle/oradata/shujukuai/tbs01.dbf NO YES 712951 55
/u01/app/oracle/oradata/shujukuai/test2.dbf NO YES 712951 34
/u01/app/oracle/oradata/shujukuai/tbs1.dbf NO YES 712951 3
/u01/app/oracle/oradata/shujukuai/undotbs02.dbf NO YES 715466 2 看吧，这个文件的SCN果然是最球大的，这里已经模拟了实例介质恢复起点小于丢失UNDO文件的起始SCN
8 rows selected.
SESSION 2>shutdown abort;
ORACLE instance shut down.
SESSION 2>host rm -rf /u01/app/oracle/oradata/shujukuai/control01.ctl
SESSION 2>host rm -rf /u01/app/oracle/oradata/shujukuai/control02.ctl
SESSION 2>host rm -rf /u01/app/oracle/oradata/shujukuai/control03.ctl
SESSION 2>host rm -rf /u01/app/oracle/oradata/shujukuai/undotbs02.ctl
现在控制文件和回滚数据文件已丢失
SESSION 2>host vi /u01/backup_dir/ctl.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SHUJUKUA" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/shujukuai/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/shujukuai/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/shujukuai/redo03.log' SIZE 50M
DATAFILE
'/u01/app/oracle/oradata/shujukuai/system01.dbf',
'/u01/app/oracle/oradata/shujukuai/undotbs01.dbf',
'/u01/app/oracle/oradata/shujukuai/sysaux01.dbf',
'/u01/app/oracle/oradata/shujukuai/users01.dbf',
'/u01/app/oracle/oradata/shujukuai/tbs01.dbf',
'/u01/app/oracle/oradata/shujukuai/test2.dbf',
'/u01/app/oracle/oradata/shujukuai/tbs1.dbf', 注意要去掉这个逗号，可不要粗心哦
'/u01/app/oracle/oradata/shujukuai/undotbs02.dbf' 删除这行，因为数据文件已不存在
CHARACTER SET ZHS16GBK
;
SESSION 2>@/u01/backup_dir/ctl.sql
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 125830616 bytes
Database Buffers 150994944 bytes
Redo Buffers 6303744 bytes
Control file created.
SESSION 2>select substr(name,1,50) dname ,recover,fuzzy,checkpoint_change#,checkpoint_count from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- --- ------------------ ----------------
/u01/app/oracle/oradata/shujukuai/system01.dbf YES YES 712951 112
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf YES YES 712951 72
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf YES YES 712951 112
/u01/app/oracle/oradata/shujukuai/users01.dbf YES YES 712951 111
/u01/app/oracle/oradata/shujukuai/tbs01.dbf YES YES 712951 55
/u01/app/oracle/oradata/shujukuai/test2.dbf YES YES 712951 34
/u01/app/oracle/oradata/shujukuai/tbs1.dbf YES YES 712951 3 SCN都小于 715466
7 rows selected.
SESSION 2>select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 52428800 1 NO CURRENT 712950 01-JUN-10 小于 715466
3 1 0 52428800 1 YES UNUSED 0
2 1 0 52428800 1 YES UNUSED 0
SESSION 2>recover database;
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 8: '/u01/app/oracle/oradata/shujukuai/undotbs02.dbf'
SESSION 2>select substr(name,1,50) dname,checkpoint_change#,last_change#,offline_change#,creation_change# from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# CREATION_CHANGE#
-------------------------------------------------- ------------------ ------------ --------------- ----------------
/u01/app/oracle/oradata/shujukuai/system01.dbf 715467 0 5
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf 715467 0 600647
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf 715467 0 6678
/u01/app/oracle/oradata/shujukuai/users01.dbf 715467 0 10685
/u01/app/oracle/oradata/shujukuai/tbs01.dbf 715467 0 631813
/u01/app/oracle/oradata/shujukuai/test2.dbf 715467 0 673840
/u01/app/oracle/oradata/shujukuai/tbs1.dbf 715467 0 712861
/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00008 715465 0 715465
8 rows selected.
SESSION 2>select substr(name,1,50) dname ,recover,fuzzy,checkpoint_change#,checkpoint_count from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- --- ------------------ ----------------
/u01/app/oracle/oradata/shujukuai/system01.dbf YES YES 715467 112
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf YES YES 715467 72
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf YES YES 715467 112
/u01/app/oracle/oradata/shujukuai/users01.dbf YES YES 715467 111
/u01/app/oracle/oradata/shujukuai/tbs01.dbf YES YES 715467 55
/u01/app/oracle/oradata/shujukuai/test2.dbf YES YES 715467 34
/u01/app/oracle/oradata/shujukuai/tbs1.dbf YES YES 715467 3
0 0 数据文件不存在，所以数据文件头里没得内容撒
8 rows selected.
SESSION 2>select checkpoint_change#,controlfile_change#,resetlogs_change# from v$database;
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# RESETLOGS_CHANGE#
------------------ ------------------- -----------------
712950 0 712950
控制文件的SCN为0，因为它是新创建的，数据库的SCN为712950 且等于V$LOGS刚刚查出来的那个SCN，这里也看出来，用NORESETLOGS创建控制文件时，会读取日志文件的内容
SESSION 2>alter database create datafile '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00008' as '/u01/app/oracle/oradata/shujukuai/undotbs02.dbf';
Database altered.
SESSION 2>select substr(name,1,60) dname,checkpoint_change#,last_change#,offline_change#,creation_change# from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# CREATION_CHANGE#
-------------------------------------------------- ------------------ ------------ --------------- ----------------
/u01/app/oracle/oradata/shujukuai/system01.dbf 715467 0 5
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf 715467 0 600647
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf 715467 0 6678
/u01/app/oracle/oradata/shujukuai/users01.dbf 715467 0 10685
/u01/app/oracle/oradata/shujukuai/tbs01.dbf 715467 0 631813
/u01/app/oracle/oradata/shujukuai/test2.dbf 715467 0 673840
/u01/app/oracle/oradata/shujukuai/tbs1.dbf 715467 0 712861
/u01/app/oracle/oradata/shujukuai/undotbs02.dbf 715465 0 715465
8 rows selected.
SESSION 2>select substr(name,1,50) dname ,recover,fuzzy,checkpoint_change#,checkpoint_count from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- --- ------------------ ----------------
/u01/app/oracle/oradata/shujukuai/system01.dbf YES YES 715467 112 FUZZY全是YES，说明需要恢复撒
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf YES YES 715467 72
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf YES YES 715467 112
/u01/app/oracle/oradata/shujukuai/users01.dbf YES YES 715467 111
/u01/app/oracle/oradata/shujukuai/tbs01.dbf YES YES 715467 55
/u01/app/oracle/oradata/shujukuai/test2.dbf YES YES 715467 34
/u01/app/oracle/oradata/shujukuai/tbs1.dbf YES YES 715467 3
/u01/app/oracle/oradata/shujukuai/undotbs02.dbf YES NO 715465 1
8 rows selected.
SESSION 2>recover database;
Media recovery complete.
SESSION 2>alter database open;
Database altered.
SESSION 2>select count(*) from v;
COUNT(*)
----------
1
说明已经提交的写入日志文件了撒，是可以应用日志文件恢复回来的嘛
说明：
＝＝所以恢复的起点要小于UNDOTBS01.DBF的起始SCN，这样控制文件才有机会通过恢复前滚出控制文件附加UNDOTBS01.DBF文件的信息，包括UNDOTBS01.DBF的起始SCN
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21158541/viewspace-666866/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/21158541/viewspace-666866/
2782
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
