控制文件、数据文件损坏、异常关闭，数据文件被覆盖

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博客 ” ，链接：http://blog.itpub.net/21158541/viewspace-666516/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21158541/viewspace-666516/