3142控制文件、系统文件丢失，异常停机、恢复起点SCN小于丢失文件起始SCN

控制文件、系统文件丢失，异常停机、恢复起点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/