控制文件、数据文件损坏、异常关闭，数据文件被覆盖
SQL> set linesize 170
SQL> col name for a40
SQL> select tablespace_name,name from v$datafile_header;
TABLESPACE_NAME NAME
------------------------------ ----------------------------------------
SYSTEM /u01/app/oracle/oradata/shujukuai/system
01.dbf
UNDOTBS1 /u01/app/oracle/oradata/shujukuai/undotb
s01.dbf
SYSAUX /u01/app/oracle/oradata/shujukuai/sysaux
01.dbf
USERS /u01/app/oracle/oradata/shujukuai/users0
1.dbf
TABLESPACE_NAME NAME
------------------------------ ----------------------------------------
TBS01 /u01/app/oracle/oradata/shujukuai/tbs01.
dbf
SQL> drop tablespace tbs01 including contents and datafiles;
Tablespace dropped.
SQL> create tablespace tbs01 datafile '/u01/app/oracle/oradata/shujukuai/tbs01.dbf' size 10m;
Tablespace created.
SQL> create table t1(scn int) tablespace tbs01;
Table created.
SQL> insert into t1 select dbms_flashback.get_system_change_number from (select rownum from dba_objects where rownum<=1000);
1000 rows created.
SQL> commit;
Commit complete.
SQL> select group#,bytes,archived,first_change#,sequence#,status from v$log;
GROUP# BYTES ARC FIRST_CHANGE# SEQUENCE# STATUS
---------- ---------- --- ------------- ---------- ----------------
1 52428800 YES 602821 1 INACTIVE
2 52428800 NO 609406 2 INACTIVE
3 52428800 NO 627969 3 CURRENT 当前日志文件
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database backup controlfile to trace as '/u02/backup_files/shujukuai.sql' reuse;
Database altered.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> host rm -rf /u01/app/oracle/oradata/shujukuai/tbs01.dbf
SQL> host rm -rf /u01/app/oracle/oradata/shujukuai/control01.ctl
SQL> host rm -rf /u01/app/oracle/oradata/shujukuai/control02.ctl
SQL> host rm -rf /u01/app/oracle/oradata/shujukuai/control03.ctl
SQL> host vi /u02/backup_files/shujukuai.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SHUJUKUA" NORESETLOGS NOARCHIVELOG
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' 注意删除此行，因为这个数据文件已经不存在了
CHARACTER SET ZHS16GBK
;
SQL> @/u02/backup_files/shujukuai.sql
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 113247704 bytes
Database Buffers 163577856 bytes
Redo Buffers 6303744 bytes
Control file created.
SQL> select group#,bytes,archived,first_change#,sequence#,status from v$log;
GROUP# BYTES ARC FIRST_CHANGE# SEQUENCE# STATUS
---------- ---------- --- ------------- ---------- ----------------
1 52428800 NO 631904 4 INACTIVE
3 52428800 NO 627969 3 INACTIVE
2 52428800 NO 631906 5 CURRENT
SQL> select substr(name,1,40) dname,recover,fuzzy,checkpoint_change#,checkpoint_count from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------- --- --- ------------------ ----------------
/u01/app/oracle/oradata/shujukuai/system YES 627969 58
/u01/app/oracle/oradata/shujukuai/undotb YES 627969 18
/u01/app/oracle/oradata/shujukuai/sysaux YES 627969 58
/u01/app/oracle/oradata/shujukuai/users0 YES 627969 57
SQL> 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 5: '/u01/app/oracle/oradata/shujukuai/tbs01.dbf'
SQL> select substr(name,1,40) dname,recover,fuzzy,checkpoint_change#,checkpoint_count from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------- --- --- ------------------ ----------------
/u01/app/oracle/oradata/shujukuai/system YES YES 631815 58
/u01/app/oracle/oradata/shujukuai/undotb YES YES 631815 18
/u01/app/oracle/oradata/shujukuai/sysaux YES YES 631815 58
/u01/app/oracle/oradata/shujukuai/users0 YES YES 631815 57
0 0 咦，这里多了行空白的东西，想一下，是不是添加了一个空白的数据文件
SQL> alter database create datafile '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005' as '/u01/app/oracle/oradata/shujukuai/tbs01.dbf';
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/shujukuai/system01.dbf'
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select substr(name,1,40) dname,recover,fuzzy,checkpoint_change#,checkpoint_count from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
--------------------------------------------------------- ----- ---- ------------------ ----------------
/u01/app/oracle/oradata/shujukuai/system NO YES 651930 62
/u01/app/oracle/oradata/shujukuai/undotb NO YES 651930 22
/u01/app/oracle/oradata/shujukuai/sysaux NO YES 651930 62
/u01/app/oracle/oradata/shujukuai/users0 NO YES 651930 61
/u01/app/oracle/oradata/shujukuai/tbs01. NO YES 651930 5
SQL> select file#,status$,crscnbas from file$;
FILE# STATUS$ CRSCNBAS
---------- ---------- ----------
1 2 5
2 2 600647
3 2 6678
4 2 10685
5 2 631813 --这里当然就是这个数据文件的生日啦，FILE$这个基表记录了数据文件生日
这里我们看到基表中STATUS$状态都为2，说明是可用的。但是如果这个数据文件被删除，那么状态为1。当数据文件被删除后不会立即在FILE$中体现出来。而是逻辑删除
SQL> select count(*) from t1;
COUNT(*)
----------
1000
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/21158541/viewspace-666516/ ，如需转载，请注明出处，否则将追究法律责任。
