315控制文件丢失，控制文件有该文件初次创建信息备份，系统文件丢失
315控制文件丢失，控制文件有该文件初次创建信息备份，系统文件丢失
SQL> alter tablespace system add datafile '/u01/app/oracle/oradata/shujukuai/system04.dbf' size 100m;
Tablespace altered.
SQL> select file#,status$,crscnbas from file$;
FILE# STATUS$ CRSCNBAS
---------- ---------- ----------
1 2 8
2 2 523586
3 2 6294
4 2 10169
5 2 556970
6 2 599064
7 2 621748
7 rows selected.
SQL> select substr(name,1,50) dname,checkpoint_change#,last_change#,offline_change#,creation_change#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# CREATION_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ --------------- ---------------- -------
/u01/app/oracle/oradata/shujukuai/system01.dbf 620340 0 8 SYSTEM
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf 620340 0 523586 ONLINE
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf 620340 0 6294 ONLINE
/u01/app/oracle/oradata/shujukuai/users01.dbf 620340 0 10169 ONLINE
/u01/app/oracle/oradata/shujukuai/system02.dbf 620340 0 556970 SYSTEM
/u01/app/oracle/oradata/shujukuai/system03.dbf 620340 0 599064 SYSTEM
/u01/app/oracle/oradata/shujukuai/system04.dbf 621749 0 621748 SYSTEM
7 rows selected.
SQL> 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 620340 67
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf NO YES 620340 29
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf NO YES 620340 67
/u01/app/oracle/oradata/shujukuai/users01.dbf NO YES 620340 66
/u01/app/oracle/oradata/shujukuai/system02.dbf NO YES 620340 12
/u01/app/oracle/oradata/shujukuai/system03.dbf NO YES 620340 5
/u01/app/oracle/oradata/shujukuai/system04.dbf NO YES 621749 2
7 rows selected.
SQL> create table tb1(id number) tablespace system;
Table created.
SQL> alter system switch logfile;
System altered.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
621893
SQL> alter system switch logfile;
System altered.
SQL> alter database backup controlfile to '/u01/backup_dir/control.ctl' reuse;
Database altered.
SQL> create table tb2(id number) tablespace system;
Table created.
SQL> select substr(name,1,50) dname,checkpoint_change#,last_change#,offline_change#,creation_change#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# CREATION_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ --------------- ---------------- -------
/u01/app/oracle/oradata/shujukuai/system01.dbf 621997 0 8 SYSTEM
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf 621997 0 523586 ONLINE
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf 621997 0 6294 ONLINE
/u01/app/oracle/oradata/shujukuai/users01.dbf 621997 0 10169 ONLINE
/u01/app/oracle/oradata/shujukuai/system02.dbf 621997 0 556970 SYSTEM
/u01/app/oracle/oradata/shujukuai/system03.dbf 621997 0 599064 SYSTEM
/u01/app/oracle/oradata/shujukuai/system04.dbf 621997 0 621748 SYSTEM
7 rows selected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> host mv /u01/app/oracle/oradata/shujukuai/system04.dbf /u01/app/oracle/oradata/shujukuai/system04.bak
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 ls /u01/app/oracle/oradata/shujukuai/
redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf system02.dbf system03.bak system03.dbf system04.bak temp01.dbf undotbs01.dbf users01.dbf
SQL> host cp /u01/backup_dir/control.ctl /u01/app/oracle/oradata/shujukuai/control01.ctl
SQL> host cp /u01/backup_dir/control.ctl /u01/app/oracle/oradata/shujukuai/control02.ctl
SQL> host cp /u01/backup_dir/control.ctl /u01/app/oracle/oradata/shujukuai/control03.ctl
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2020192 bytes
Variable Size 121638048 bytes
Database Buffers 155189248 bytes
Redo Buffers 6365184 bytes
Database mounted.
SQL> select substr(name,1,50) dname,checkpoint_change#,last_change#,offline_change#,creation_change#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# CREATION_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ --------------- ---------------- -------
/u01/app/oracle/oradata/shujukuai/system01.dbf 621888 0 8 SYSTEM
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf 621888 0 523586 ONLINE
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf 621888 0 6294 ONLINE
/u01/app/oracle/oradata/shujukuai/users01.dbf 621888 0 10169 ONLINE
/u01/app/oracle/oradata/shujukuai/system02.dbf 621888 0 556970 SYSTEM
/u01/app/oracle/oradata/shujukuai/system03.dbf 621888 0 599064 SYSTEM
/u01/app/oracle/oradata/shujukuai/system04.dbf 621888 0 621748 SYSTEM 这个信息还存在于控制文件中的
7 rows selected.
那么这里都有这人数据文件信息，我们直接创建就是了撒
SQL> alter database create datafile '/u01/app/oracle/oradata/shujukuai/system04.dbf';
Database altered.
SQL> 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 621997 69
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf YES 621997 31
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf YES 621997 69
/u01/app/oracle/oradata/shujukuai/users01.dbf YES 621997 68
/u01/app/oracle/oradata/shujukuai/system02.dbf YES 621997 14
/u01/app/oracle/oradata/shujukuai/system03.dbf YES 621997 7
/u01/app/oracle/oradata/shujukuai/system04.dbf NO 621748 1
7 rows selected.
SQL> recover database using backup controlfile;
ORA-00279: change 621748 generated at 06/15/2010 00:54:50 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/SHUJUKUAI/archivelog/2010_06_15/o1_mf_1_8_%u_.arc
ORA-00280: change 621748 for thread 1 is in sequence #8
Specify log: {
AUTO
ORA-00279: change 621888 generated at 06/15/2010 00:57:26 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/SHUJUKUAI/archivelog/2010_06_15/o1_mf_1_9_%u_.arc
ORA-00280: change 621888 for thread 1 is in sequence #9
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/SHUJUKUAI/archivelog/2010_06_15/o1_mf_1_8_61g21phd_.arc' no longer needed for this recovery
ORA-00279: change 621997 generated at 06/15/2010 00:58:23 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/SHUJUKUAI/archivelog/2010_06_15/o1_mf_1_10_%u_.arc
ORA-00280: change 621997 for thread 1 is in sequence #10
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/SHUJUKUAI/archivelog/2010_06_15/o1_mf_1_9_61g23hwj_.arc' no longer needed for this recovery
ORA-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/SHUJUKUAI/archivelog/2010_06_15/o1_mf_1_10_%u_.arc' 这里表示THREAD#=1 SEQUENCE#=10
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 8 52428800 1 YES INACTIVE 620339 15-JUN-10
3 1 10 52428800 1 NO CURRENT 621997 15-JUN-10 就是他了
2 1 9 52428800 1 YES ACTIVE 621888 15-JUN-10
SQL> select member from v$logfile where group#=3;
MEMBER
-----------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/shujukuai/redo03.log
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile;
ORA-00279: change 621997 generated at 06/15/2010 00:58:23 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/SHUJUKUAI/archivelog/2010_06_15/o1_mf_1_10_%u_.arc
ORA-00280: change 621997 for thread 1 is in sequence #10
Specify log: {
/u01/app/oracle/oradata/shujukuai/redo03.log 输入这个在线日志进行恢复撒
Log applied.
Media recovery complete.
SQL> 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 623054 70
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf NO 623054 32
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf NO 623054 70
/u01/app/oracle/oradata/shujukuai/users01.dbf NO 623054 69
/u01/app/oracle/oradata/shujukuai/system02.dbf NO 623054 15
/u01/app/oracle/oradata/shujukuai/system03.dbf NO 623054 8
/u01/app/oracle/oradata/shujukuai/system04.dbf NO 623054 4
7 rows selected.
SQL> select substr(name,1,50) dname,checkpoint_change#,last_change#,offline_change#,creation_change#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# CREATION_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ --------------- ---------------- -------
/u01/app/oracle/oradata/shujukuai/system01.dbf 623054 623054 0 8 SYSTEM
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf 623054 623054 0 523586 ONLINE
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf 623054 623054 0 6294 ONLINE
/u01/app/oracle/oradata/shujukuai/users01.dbf 623054 623054 0 10169 ONLINE
/u01/app/oracle/oradata/shujukuai/system02.dbf 623054 623054 0 556970 SYSTEM
/u01/app/oracle/oradata/shujukuai/system03.dbf 623054 623054 0 599064 SYSTEM
/u01/app/oracle/oradata/shujukuai/system04.dbf 623054 623054 0 621748 SYSTEM
7 rows selected.
SCN全都一致，可以打开数据库了
SQL> alter database open resetlogs;
Database altered.
SQL> select * from tb1;
no rows selected
SQL> select * from tb2;
no rows selected
恢复成功，而且两个表都还在的。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/21158541/viewspace-667079/ ，如需转载，请注明出处，否则将追究法律责任。
