ORACLE控制文件丢失后的两种恢复方法

1.控制文件被强行删除后，数据库未关闭情况下

1.1查看控制文件位置

SYS@EVA>show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/EVA/control01.ctl,
/u01/app/oracle/oradata/EVA/control02.ctl


1.2将控制文件强制删除

SYS@EVA>!rm -f /u01/app/oracle/oradata/EVA/control*


1.3通过ckpt进程发现控制文件的句柄还在，但文件已被删除

[oracle@oracledb EVA]$ ps -ef | grep ckpt
oracle 18494 1 0 13:39 ? 00:00:00 ora_ckpt_EVA
[oracle@oracledb EVA]$ ll /proc/18494/fd
total 0
lr-x------ 1 oracle oinstall 64 Jul 4 13:44 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 Jul 4 13:44 1 -> /dev/null
l-wx------ 1 oracle oinstall 64 Jul 4 13:44 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Jul 4 13:44 256 -> /u01/app/oracle/oradata/EVA/control01.ctl (deleted)
lrwx------ 1 oracle oinstall 64 Jul 4 13:44 257 -> /u01/app/oracle/oradata/EVA/control02.ctl (deleted)


1.4此时数据库还能正常运行(控制文件丢失后创建了用户，测试表，并插入一行数据)

SYS@EVA>create user scott identified by tiger default tablespace tbs1;
SYS@EVA>grant dba to scott;
[oracle@oracledb ~]$ sqlplus scott/tiger
SCOTT@EVA>create table a01 (id number,value varchar2(10));
SCOTT@EVA>insert into a01 VALUES(1,'AAAAA');
SCOTT@EVA>COMMIT;
SCOTT@EVA>select * from a01;

ID VALUE
---------- ----------------------------------------
1 AAAAA


1.5由于数据库还未关闭，控制文件的句柄还在，我们直接将句柄文件复制到控制文件原目录

[oracle@oracledb EVA]$ cp /proc/18494/fd/256 /u01/app/oracle/oradata/EVA/control01.ctl
[oracle@oracledb EVA]$ cp /proc/18494/fd/257 /u01/app/oracle/oradata/EVA/control02.ctl


1.6关闭数据库，重新启动

SYS@EVA>shut immediate
Database closed.
ORA-03113: end-of-file on communication channel --关库时会报这个异常,貌似没啥影响
Process ID: 20960
Session ID: 7 Serial number: 5646
SYS@EVA>exit
[oracle@oracledb ~]$ sqlplus / as sysdba --重新登录sqlplus后正常启库
idle>startup
ORACLE instance started.
Total System Global Area 2516582400 bytes
Fixed Size 8623832 bytes
Variable Size 822085928 bytes
Database Buffers 1677721600 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.


1.7验证数据是否丢失

SCOTT@EVA>select * from a01;
ID VALUE
---------- ----------------------------------------
1 AAAAA


2.控制文件被强行删除后，一顿操作猛如虎，数据库被关掉了

在有备份的情况下可以用备份来恢复，以下只讲述在无任何备份的情况下手工重建控制文件过程

2.1环境准备

--删除控制文件
SYS@EVA>!rm -f /u01/app/oracle/oradata/EVA/control*
--模拟业务向表中插入一条数据
SCOTT@EVA>insert into a01 values(2,'BBBBB');
1 row created.
SCOTT@EVA>commit;
Commit complete.
SCOTT@EVA>select * from a01;
ID VALUE
---------- ----------------------------------------
1 AAAAA
2 BBBBB
--切换几次redo日志,将日志覆盖
SCOTT@EVA>alter system switch logfile;
System altered.
SCOTT@EVA>/
System altered.
SCOTT@EVA>/
System altered.
SCOTT@EVA>/
System altered.


2.2根据官方提供控制文件重建脚本，需要确认的信息

1.数据库名(这个很好确认,直接nomount模式show parameter db_name即可)
2.是否为归档模式
3.redo日志文件的路径及大小
4.数据文件的路径
5.数据库字符集


2.3确认数据库名

--将实例启动到nomount状态,通过参数文件查看数据库名
idle>startup nomount
ORACLE instance started.
Total System Global Area 2516582400 bytes
Fixed Size 8623832 bytes
Variable Size 822085928 bytes
Database Buffers 1677721600 bytes
Redo Buffers 8151040 bytes
idle>show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string EVA


2.4确认是否为归档模式

--通过以下两个参数确认数据库为非归档模式
idle>show parameter log_archive_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_start boolean FALSE
idle>show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string


2.5确认redo日志路径及大小

--通过alert日志查询路径
[oracle@oracledb ~]$ vi /u01/app/oracle/diag/rdbms/eva/EVA/trace/alert_EVA.log
...........省略部分内容.............
2024-07-04T15:07:38.051125+08:00
Thread 1 opened at log sequence 26
Current log# 2 seq# 26 mem# 0: /u01/app/oracle/oradata/EVA/redo02.log
Successful open of redo thread 1
--查看redo文件数量及大小
[oracle@oracledb ~]$ ll -h /u01/app/oracle/oradata/EVA/redo*
-rw-r----- 1 oracle oinstall 201M Jul 4 15:07 /u01/app/oracle/oradata/EVA/redo01.log
-rw-r----- 1 oracle oinstall 201M Jul 4 15:15 /u01/app/oracle/oradata/EVA/redo02.log
-rw-r----- 1 oracle oinstall 201M Jul 4 15:07 /u01/app/oracle/oradata/EVA/redo03.log


2.6确认数据文件的路径

--通过alert日志查询
[oracle@oracledb ~]$ vi /u01/app/oracle/diag/rdbms/eva/EVA/trace/alert_EVA.log
...........省略部分内容.............
2024-07-03T13:19:38.642706+08:00
create tablespace tbs1 datafile '/u01/app/oracle/oradata/EVA/tbs1.dbs' size 10M
Completed: create tablespace tbs1 datafile '/u01/app/oracle/oradata/EVA/tbs1.dbs' size 10M
--使用find命令查询
[oracle@oracledb trace]$ find /u01 -name *.dbf
/u01/app/oracle/oradata/EVA/system01.dbf
/u01/app/oracle/oradata/EVA/sysaux01.dbf
/u01/app/oracle/oradata/EVA/undotbs01.dbf
/u01/app/oracle/oradata/EVA/temp01.dbf
/u01/app/oracle/oradata/EVA/users01.dbf
/u01/app/oracle/oradata/EVA/tbs1.dbf


2.7确认数据库字符集

--通过alert日志查询
[oracle@oracledb ~]$ vi /u01/app/oracle/diag/rdbms/eva/EVA/trace/alert_EVA.log
...........省略部分内容.............
Verifying minimum file header compatibility for tablespace encryption..
Verifying file header compatibility for tablespace encryption completed for pdb 0
Database Characterset is AL32UTF8
No Resource Manager plan active
2024-07-04T15:07:38.981667+08:00
--每个oracle版本字符集存在system.dbf中的固定位置,使用dd命令dump出相应的块查询
--通过其他同版本的数据库中查询对应的数据块(12C为例)
SYS@EVA>select distinct dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from props$;
file# block#
------ -------
1 1313
--使用dd命令dump出相应的块进行查询
[oracle@oracledb trace]$ dd if=/u01/app/oracle/oradata/EVA/system01.dbf of=/tmp/char.txt bs=8192 skip=1313 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.0343255 s, 239 kB/s
[oracle@oracledb trace]$ strings /tmp/char.txt|grep -1 NLS_CHARACTERSET
Numeric characters,
NLS_CHARACTERSET
AL32UTF8


2.8手撸控制文件重建脚本

CREATE CONTROLFILE REUSE DATABASE "EVA" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/EVA/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/EVA/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/EVA/redo03.log' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/EVA/system01.dbf',
'/u01/app/oracle/oradata/EVA/sysaux01.dbf',
'/u01/app/oracle/oradata/EVA/undotbs01.dbf',
'/u01/app/oracle/oradata/EVA/users01.dbf',
'/u01/app/oracle/oradata/EVA/tbs1.dbf'
CHARACTER SET AL32UTF8
;


2.9运行重建脚本,打开数据库

idle>CREATE CONTROLFILE REUSE DATABASE "EVA" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/EVA/redo01.log' SIZE 200M BLOCKSIZE 512,
9 GROUP 2 '/u01/app/oracle/oradata/EVA/redo02.log' SIZE 200M BLOCKSIZE 512,
10 GROUP 3 '/u01/app/oracle/oradata/EVA/redo03.log' SIZE 200M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/oradata/EVA/system01.dbf',
14 '/u01/app/oracle/oradata/EVA/sysaux01.dbf',
15 '/u01/app/oracle/oradata/EVA/undotbs01.dbf',
16 '/u01/app/oracle/oradata/EVA/users01.dbf',
17 '/u01/app/oracle/oradata/EVA/tbs1.dbf'
18 CHARACTER SET AL32UTF8
19 ;
Control file created.
--重建完成后实例自动mount状态
idle>select status from v$instance;
STATUS
------------
MOUNTED
--打开数据库,验证数据
SYS@EVA>alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery --报错需要介质恢复
ORA-01110: data file 1: '/u01/app/oracle/oradata/EVA/system01.dbf'
SYS@EVA>recover database;
Media recovery complete.
SYS@EVA>alter database open;
Database altered.
--验证数据无丢失
SYS@EVA>select * from scott.a01;
ID VALUE
---------- ----------------------------------------
1 AAAAA
2 BBBBB
--重建控制文件后需要手动添加临时表空间文件
SYS@EVA>alter tablespace temp add tempfile '/u01/app/oracle/oradata/EVA/temp01.dbf' reuse;
--至此整个流程完成