控制文件、系统文件丢失,异常停机、恢复起点SCN小于丢失文件起始SCN
SQL> alter tablespace system add datafile '/u01/app/oracle/oradata/shujukuai/system03.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
6 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 598763 0 8 SYSTEM
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf 598763 0 523586 ONLINE
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf 598763 0 6294 ONLINE
/u01/app/oracle/oradata/shujukuai/users01.dbf 598763 0 10169 ONLINE
/u01/app/oracle/oradata/shujukuai/system02.dbf 598763 0 556970 SYSTEM
/u01/app/oracle/oradata/shujukuai/system03.dbf 599065 0 599064 SYSTEM
6 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 598763 63
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf NO YES 598763 25
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf NO YES 598763 63
/u01/app/oracle/oradata/shujukuai/users01.dbf NO YES 598763 62
/u01/app/oracle/oradata/shujukuai/system02.dbf NO YES 598763 8
/u01/app/oracle/oradata/shujukuai/system03.dbf NO YES 599065 2
6 rows selected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> host mv /u01/app/oracle/oradata/shujukuai/system03.dbf /u01/app/oracle/oradata/shujukuai/system03.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 temp01.dbf undotbs01.dbf users01.dbf
SQL> host vi /u01/backup_dir/control_system.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/system02.dbf'
CHARACTER SET WE8ISO8859P1
;
SQL> @/u01/backup_dir/control_system.sql
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2020192 bytes
Variable Size 117443744 bytes
Database Buffers 159383552 bytes
Redo Buffers 6365184 bytes
Control file 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 598763 0 8 SYSTEM
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf 598763 0 523586 RECOVER
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf 598763 0 6294 RECOVER
/u01/app/oracle/oradata/shujukuai/users01.dbf 598763 0 10169 RECOVER
/u01/app/oracle/oradata/shujukuai/system02.dbf 598763 0 556970 SYSTEM
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 YES 598763 63
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf YES YES 598763 25
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf YES YES 598763 63
/u01/app/oracle/oradata/shujukuai/users01.dbf YES YES 598763 62
/u01/app/oracle/oradata/shujukuai/system02.dbf YES YES 598763 8
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 6: '/u01/app/oracle/oradata/shujukuai/system03.dbf'
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 599066 0 8 SYSTEM
/u01/app/oracle/oradata/shujukuai/undotbs01.dbf 599066 0 523586 RECOVER
/u01/app/oracle/oradata/shujukuai/sysaux01.dbf 599066 0 6294 RECOVER
/u01/app/oracle/oradata/shujukuai/users01.dbf 599066 0 10169 RECOVER
/u01/app/oracle/oradata/shujukuai/system02.dbf 599066 0 556970 SYSTEM
/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006 599064 0 599064 SYSTEM
6 rows selected.
SQL> alter database create datafile '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006' as '/u01/app/oracle/oradata/shujukuai/system03.dbf';
Database altered.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21158541/viewspace-667071/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21158541/viewspace-667071/