情景描述:
控制文件有文本备份
数据文件一致
控制文件和redo文件丢失
控制文件可以使用备份信息新建
没有redo 所以要resetlogs建立控制文件
resetlogs会使数据库重新建立redo文件
resetlogs后一定要重新备份数据库

先备份控制文件文本格式
SQL> alter database backup controlfile to trace as '/u01/oracle/ctl.sql';

Database altered.

SQL> select * from v$controlfile;

STATUS    NAME            IS_ BLOCK_SIZE FILE_SIZE_BLKS
---------- -------------------------------------------------- --- ---------- --------------
    /db254/control01.ctl          NO       16384  450
    /db254/control02.ctl          NO       16384  450
    /db254/control03.ctl          NO       16384  450

SQL> select * from v$logfile;

    GROUP# STATUS     TYPE    MEMBER         IS_
---------- ---------- ------- --------------------------------------------- ---
3       ONLINE /db254/redo03.log        NO
2       ONLINE /db254/redo02.log        NO
1       ONLINE /db254/redo01.log        NO

一致停库
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

删除redo和control文件
SQL> ! rm /db254/redo*

SQL> ! rm /db254/contro*

SQL>

启动数据库没有控制文件只能到nomount 报205
SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size      1218992 bytes
Variable Size     83887696 bytes
Database Buffers   197132288 bytes
Redo Buffers      2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL>

拿备份的文本中使用resetlog模式创建控制文件 resetlog会在open时重建redo

CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/db254/redo01.log' SIZE 50M,
GROUP 2 '/db254/redo02.log' SIZE 50M,
GROUP 3 '/db254/redo03.log' SIZE 50M
DATAFILE
'/db254/system01.dbf',
'/db254/undotbs01.dbf',
'/db254/sysaux01.dbf',
'/db254/users01.dbf',
'/db254/mytbs01.dbf',
'/db254/ind_tbs01.dbf',
'/db254/wb.dbf'
CHARACTER SET AL32UTF8
;

Control file created.

SQL> select status from v$instance;

STATUS
----------
MOUNTED

SQL> 控制文件已建好日志文件会在open时重新创建
SQL> ! ls /db254/control*
/db254/control01.ctl /db254/control02.ctl /db254/control03.ctl

SQL> ! ls /db254/redo*
ls: /db254/redo*: 没有那个文件或目录

SQL>
因为数据文件是一致的.所以不需要恢复
SQL> select name,FUZZY,CHECKPOINT_CHANGE#,CHECKPOINT_COUNT from v$datafile_header;

NAME         FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- ------------------ ----------------
/db254/system01.dbf       NO    1079164        247
/db254/undotbs01.dbf       NO    1079164        207
/db254/sysaux01.dbf       NO    1079164        244
/db254/users01.dbf       NO    1079164        257
/db254/mytbs01.dbf       NO    1079164        120
/db254/ind_tbs01.dbf       NO    1079164  54
/db254/wb.dbf        NO    1079164  11

7 rows selected.

SQL>

SQL> alter database open resetlogs;

Database altered.

SQL>

SQL> ! ls /db254/redo*
/db254/redo01.log /db254/redo02.log /db254/redo03.log

SQL>

ITPub博客

控制文件恢复之resetlogs建立控制文件实验

最新文章