所有控制文件丢失后的恢复
原创
©著作权归作者所有：来自51CTO博客作者dber_ablewang的原创作品，请联系作者获取转载授权，否则将追究法律责任
- 使用备份控制文件进行恢复
a. 准备数据库
create table tb01(id int);
insert into tb01 values(1);
insert into tb01 values(2);
commit;
b. 备份控制文件
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/O19C/control01.ctl
/opt/oracle/oradata/O19C/control02.ctl
[oracle@oracle19c ~]$ cd /opt/oracle/oradata/O19C
[oracle@oracle19c O19C]$ cp control0* /opt/oracle/pbak/
c. 插入数据
insert into tb01 values(3);
commit;
d. 删除控制文件，模拟控制文件损坏
[oracle@oracle19c O19C]$ rm -rf control0*
e. 关闭数据库
SQL> alter system checkpoint;
SQL> shutdown abort;
ORACLE instance shut down.
f. 还原控制文件
SQL> startup;
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
ORA-00205: error in identifying control file, check alert log for more info
#还原控制文件
[oracle@oracle19c O19C]$ cp /opt/oracle/pbak/control0* ./
SQL> alter database mount;
SQL> recover database using backup controlfile;
#如果打开了flashback 这里会报错：
ORA-00279: change 2249134 generated at 08/19/2024 09:44:41 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/product/19c/dbhome_1/dbs/arch1_7_1175272692.dbf
ORA-00280: change 2249134 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/opt/oracle/product/19c/dbhome_1/dbs/arch1_7_1175272692.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-00308: cannot open archived log
'/opt/oracle/product/19c/dbhome_1/dbs/arch1_7_1175272692.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
--------------------------------------
SQL> recover database using backup controlfile;
ORA-00279: change 2249134 generated at 08/19/2024 09:44:41 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/product/19c/dbhome_1/dbs/arch1_7_1175272692.dbf
ORA-00280: change 2249134 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/oradata/O19C/redo01.log #这里要按个尝试未归档的日志文件，知道出现下面的情况
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
SQL> select * from tb01;
ID
----------
1
2
3
g.执行不完全恢复后的全备
- 使用备份控制文件进行恢复（备份后新增数据文件）
a. 现状查看
SQL> select * from tb01;
ID
----------
1
2
3
SQL> select open_mode from v$database;
OPEN_MODE
----------------------------------------
READ WRITE
b. 备份控制文件
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/O19C/control01.ctl
/opt/oracle/oradata/O19C/control02.ctl
[oracle@oracle19c ~]$ cd /opt/oracle/oradata/O19C
[oracle@oracle19c O19C]$ cp control0* /opt/oracle/pbak/
c. 新建表空间
SQL> create tablespace tbs_aw datafile '/opt/oracle/oradata/O19C/aw01.dbf' size 20M,'/opt/oracle/oradata/O19C/aw02.dbf' size 20M;
d. 模拟数据库故障
[oracle@oracle19c O19C]$ rm -rf control0*
SQL> shutdown abort;
ORACLE instance shut down.
e. 还原控制文件
[oracle@oracle19c O19C]$ cp /opt/oracle/pbak/control0* ./
f. 恢复数据库
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> recover database using backup controlfile;
ORA-00279: change 2250967 generated at 09/12/2024 10:10:47 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/product/19c/dbhome_1/dbs/arch1_1_1179483047.dbf
ORA-00280: change 2250967 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/oradata/O19C/redo03.log
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: '/opt/oracle/oradata/O19C/redo03.log'
SQL> recover database using backup controlfile;
ORA-00279: change 2250967 generated at 09/12/2024 10:10:47 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/product/19c/dbhome_1/dbs/arch1_1_1179483047.dbf
ORA-00280: change 2250967 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/oradata/O19C/redo02.log
ORA-00326: log begins at change 2351431, need earlier change 2250967
ORA-00334: archived log: '/opt/oracle/oradata/O19C/redo02.log'
SQL> recover database using backup controlfile;
ORA-00279: change 2250967 generated at 09/12/2024 10:10:47 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/product/19c/dbhome_1/dbs/arch1_1_1179483047.dbf
ORA-00280: change 2250967 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/oradata/O19C/redo01.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 8: '/opt/oracle/oradata/O19C/aw02.dbf'
ORA-01110: data file 5: '/opt/oracle/oradata/O19C/aw01.dbf'
ORA-01112: media recovery not started
SQL> select file#, name FROM v$datafile;
FILE# NAME
-------------------------------------------------------------------------
1 /opt/oracle/oradata/O19C/system01.dbf
3 /opt/oracle/oradata/O19C/sysaux01.dbf
4 /opt/oracle/oradata/O19C/undotbs01.dbf
5 /opt/oracle/product/19c/dbhome_1/dbs/UNNAMED00005
7 /opt/oracle/oradata/O19C/users01.dbf
8 /opt/oracle/product/19c/dbhome_1/dbs/UNNAMED00008
SQL> alter database rename file '/opt/oracle/product/19c/dbhome_1/dbs/UNNAMED00005' to '/opt/oracle/oradata/O19C/aw01.dbf';
SQL> alter database rename file '/opt/oracle/product/19c/dbhome_1/dbs/UNNAMED00008' to '/opt/oracle/oradata/O19C/aw02.dbf';
SQL> select file#, name FROM v$datafile;
FILE# NAME
-------------------------------------------------------------------------
1 /opt/oracle/oradata/O19C/system01.dbf
3 /opt/oracle/oradata/O19C/sysaux01.dbf
4 /opt/oracle/oradata/O19C/undotbs01.dbf
5 /opt/oracle/oradata/O19C/aw01.dbf
7 /opt/oracle/oradata/O19C/users01.dbf
8 /opt/oracle/oradata/O19C/aw02.dbf
SQL> recover database using backup controlfile;
ORA-00279: change 2251400 generated at 09/12/2024 10:19:36 needed for thread 1
ORA-00289: suggestion : /opt/oracle/product/19c/dbhome_1/dbs/arch1_1_1179483047.dbf
ORA-00280: change 2251400 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/oradata/O19C/redo01.log
ORA-00279: change 2351431 generated at 09/12/2024 10:21:49 needed for thread 1
ORA-00289: suggestion : /opt/oracle/product/19c/dbhome_1/dbs/arch1_2_1179483047.dbf
ORA-00280: change 2351431 for thread 1 is in sequence #2
ORA-00278: log file '/opt/oracle/oradata/O19C/redo01.log' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/oradata/O19C/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
g. 进行恢复后的全备
