Oracle:ORA-00283: recovery session canceled due to errors

我做了一个这样的实验：归档模式下进行手工热备并进行还原恢复，结果将数据文件还原后，却报出了如下错误：
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 1: ‘/opt/oracle/oradata/orcl/system01.dbf’
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: ‘/opt/oracle/oradata/orcl/system01.dbf’

因此开始了困难重重的排错历程

首先看一下我做了哪些事：
※ 开启归档模式
※ 向scott用户中的A表插入一条数据并提交，插入第二条数据未提交
※ alter database begin backup; 备份数据库
※ 将所有dbf文件,ctl文件备份到指定路径
※ alter database end backup; 结束数据库备份
※ 删除所有.df，模拟数据文件丢失
※ 还原数据文件
※ recover database;进行数据库恢复 –从这步开始报错
※ alter database open;开启数据库 –继续报错

-- 1 . 开启归档模式
startup mount
alter database archivelog

-- 2 . scott用户下往表A插入一条数据并提交
scott@ORCL>insert into A values ('20200103001');

1 row created.

scott@ORCL>
scott@ORCL>commit;

Commit complete.

scott@ORCL>
scott@ORCL>

-- 3 . 又插入一条数据未提交
scott@ORCL>insert into A values ('20200103002');

1 row created.

scott@ORCL>
-- 4 . 查看要备份哪些数据文件
scott@ORCL>select name from v$datafile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/opt/oracle/oradata/orcl/system01.dbf
/opt/oracle/oradata/orcl/sysaux01.dbf
/opt/oracle/oradata/orcl/undotbs01.dbf
/opt/oracle/oradata/orcl/users01.dbf
/opt/oracle/oradata/orcl/example01.dbf
/opt/oracle/oradata/orcl/users011.dbf

6 rows selected.

-- 5 . 查看数据文件对应哪些表空间
scott@ORCL>col file_name format a50
scott@ORCL>select file_id,file_name,tablespace_name from dba_data_files;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
         5 /opt/oracle/oradata/orcl/example01.dbf             EXAMPLE
         4 /opt/oracle/oradata/orcl/users01.dbf               USERS
         3 /opt/oracle/oradata/orcl/undotbs01.dbf             UNDOTBS1
         2 /opt/oracle/oradata/orcl/sysaux01.dbf              SYSAUX
         1 /opt/oracle/oradata/orcl/system01.dbf              SYSTEM
         6 /opt/oracle/oradata/orcl/users011.dbf              USERS011

6 rows selected.

scott@ORCL>
scott@ORCL>
scott@ORCL>
scott@ORCL>
-- 6 . 查看要备份哪些控制文件
scott@ORCL>select name from v$controlfile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/opt/oracle/oradata/orcl/control01.ctl
/opt/oracle/flash_recovery_area/orcl/control02.ctl
scott@ORCL>
-- 7 . 开始备份数据库
sys@ORCL>alter database begin backup;

Database altered.

--8 . cp数据文件和控制文件到指定路径下（这里有坑，接下来会讲解）
[root@db01 orclbk]# cp /opt/oracle/oradata/orcl/*.dbf /opt/oracle/oradatabk/orclbk
[root@db01 orcl]# cp /opt/oracle/oradata/orcl/control01.ctl /opt/oracle/oradatabk/orclbk
[root@db01 orclbk]# cp /opt/oracle/flash_recovery_area/orcl/control02.ctl /opt/oracle/oradatabk/orclbk
--9 . 备份期间，scn被冻结，不发生变化
sys@ORCL>select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            2431194
         2            2431194
         3            2431194
         4                  0
         5            2431194
         6            2431194

6 rows selected.
--9 . ACTIVE表示而已备份相应的数据文件，并且对于其中的数据块DBWN仍然可以刷新
sys@ORCL>select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                2431194 13-JAN-20
         2 ACTIVE                2431194 13-JAN-20
         3 ACTIVE                2431194 13-JAN-20
         5 ACTIVE                2431194 13-JAN-20
         6 ACTIVE                2431194 13-JAN-20

sys@ORCL>
-- 10 . 结束数据库备份
sys@ORCL>
sys@ORCL>alter database end backup;
alter database end backup
*
ERROR at line 1:
--11 . 这里报的error是一个历史性遗留问题，几个月前误删了正在使用中的users01文件，固重建了user011文件，将出问题的users01文件废弃掉了，代价是user01文件下的所有表都不能操作了，因此在备份数据库时，报出以下错误，可以暂且无视
ORA-01260: warning: END BACKUP succeeded but some files found not to be in backup mode

--12 . 删除所有数据文件，模拟数据文件丢失
[root@db01 orclbk]# cd /opt/oracle/oradata/orcl
[root@db01 orcl]# rm -f *.dbf

--13 . 还原数据文件
[root@db01 orcl]# cp /opt/oracle/oradatabk/orclbk/*.dbf /opt/oracle/oradata/orcl

sys@ORCL>
--14 . shutdown 数据库
sys@ORCL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORCL>
sys@ORCL>
--15 . startup 数据库
sys@ORCL>startup
ORACLE instance started.

Total System Global Area  409194496 bytes
Fixed Size                  2213856 bytes
Variable Size             310380576 bytes
Database Buffers           92274688 bytes
Redo Buffers                4325376 bytes
Database mounted.
--16 . 不出所料，数据库因丢失数据文件，报错如下
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/opt/oracle/oradata/orcl/system01.dbf'

--17 . 还原数据文件
[root@db01 orclbk]# cp /opt/oracle/oradata/orcl/*.dbf /opt/oracle/oradatabk/orclbk

--18 . 查看当前数据库状态
sys@ORCL>select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

--19 . 恢复数据库，结果报错如下
sys@ORCL>recover database;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 1: '/opt/oracle/oradata/orcl/system01.dbf'
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/opt/oracle/oradata/orcl/system01.dbf'

--20 . 打开数据库，继续结果报错如下
sys@ORCL>alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/opt/oracle/oradata/orcl/system01.dbf'


sys@ORCL>
sys@ORCL>
sys@ORCL>recover database
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 1: '/opt/oracle/oradata/orcl/system01.dbf'
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/opt/oracle/oradata/orcl/system01.dbf'

sys@ORCL>recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 1: '/opt/oracle/oradata/orcl/system01.dbf'
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/opt/oracle/oradata/orcl/system01.dbf'

排错
首先确认控制文件和数据文件scn不一致

--21 . 
sys@ORCL>select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
           2431562
           2431562
           2431562
           2214476
           2431562
           2431562

6 rows selected.



sys@ORCL>select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
                 0
                 0
                 0
                 0
                 0
                 0

6 rows selected.

查看trace文件和alert文件定位问题

上面第8步骤讲到的坑，仔细的人会注意到备份数据文件时linux文件目录拥有者及用户组由原来的oracle oinstall 变为root root了，这就意味着你还原后的文件权限变化了，这就是问什么看trace文件时会有permission denied的错误。

这里不用慌，更改 linux文件目录拥有者及用户组即可
chown -R oracle:oinstall test --大R代表递归更改，test下面的所有子目录都会更改，不加R参数，就只更改test目录拥有者和组
将所有文件改成 oracle oinstall

重建控制文件（这里是关键！）

idle>select status from v$instance
  2  ;

STATUS
------------
MOUNTED
idle>alter database backup controlfile to trace as '';

Database altered.

select d.value||b.bias||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc                                                                                                                                                             ' trace_file_name from ( select p.spid from sys.v$mystat m,sys.v$session s,sys.v                                                                                                                                                             $process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p, ( s                                                                                                                                                             elect t.instance from sys.v$thread t,sys.v$parameter v where v.name = 'thread' a                                                                                                                                                             nd (v.value = 0 or t.thread# = to_number(v.value))) i, ( select value from sys.v                                                                                                                                                             $parameter where name = 'user_dump_dest') d,(select DECODE(count(BANNER),0,'/','                                                                                                                                                             \') bias from v$version where upper(banner) like '%WINDOWS%') b;

TRACE_FILE_NAME
--------------------------------------------------------------------------------                                                                                                                                                             --------------------------------------------------------------------------------                                                                                                                                                             ----------------------------------------
/opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_1719.trc


-- 查看trace文件重建控制文件脚本，按照文件中提示进行操作
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/opt/oracle/oradata/orcl/redo01.log',
    '/opt/oracle/disk2/redo10b.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '/opt/oracle/oradata/orcl/redo02.log',
    '/opt/oracle/disk2/redo20b.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '/opt/oracle/oradata/orcl/redo03.log',
    '/opt/oracle/disk2/redo30b.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 4 (
    '/opt/oracle/oradata/orcl/redo04.log',
    '/opt/oracle/disk2/redo40b.log'
  ) SIZE 8M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/opt/oracle/oradata/orcl/system01.dbf',
  '/opt/oracle/oradata/orcl/sysaux01.dbf',
  '/opt/oracle/oradata/orcl/undotbs01.dbf',
  '/opt/oracle/oradata/orcl/users01.dbf',
  '/opt/oracle/oradata/orcl/example01.dbf',
  '/opt/oracle/oradata/orcl/users011.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Take files offline to match current control file.
ALTER DATABASE DATAFILE '/opt/oracle/oradata/orcl/users01.dbf' OFFLINE DROP;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/flash_recovery_area/ORCL/archivelog/2020_01_14/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/flash_recovery_area/ORCL/archivelog/2020_01_14/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/orcl/temp01.dbf' REUSE;
ALTER TABLESPACE TEMP3 ADD TEMPFILE '/opt/oracle/oradata/orcl/temp03.dbf' REUSE;
ALTER TABLESPACE TEMP2 ADD TEMPFILE '/opt/oracle/oradata/orcl/temp02.dbf' REUSE;
-- End of tempfile additions.
--
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- WARNING! The current control file needs to be checked against
-- the datafiles to insure it contains the correct files. The
-- commands printed here may be missing log and/or data files.
-- Another report should be made after the database has been
-- successfully opened.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/opt/oracle/oradata/orcl/redo01.log',
    '/opt/oracle/disk2/redo10b.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '/opt/oracle/oradata/orcl/redo02.log',
    '/opt/oracle/disk2/redo20b.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '/opt/oracle/oradata/orcl/redo03.log',
    '/opt/oracle/disk2/redo30b.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 4 (
    '/opt/oracle/oradata/orcl/redo04.log',
    '/opt/oracle/disk2/redo40b.log'
  ) SIZE 8M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/opt/oracle/oradata/orcl/system01.dbf',
  '/opt/oracle/oradata/orcl/sysaux01.dbf',
  '/opt/oracle/oradata/orcl/undotbs01.dbf',
  '/opt/oracle/oradata/orcl/example01.dbf',
  '/opt/oracle/oradata/orcl/users011.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/flash_recovery_area/ORCL/archivelog/2020_01_14/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/flash_recovery_area/ORCL/archivelog/2020_01_14/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/orcl/temp01.dbf' REUSE;
ALTER TABLESPACE TEMP3 ADD TEMPFILE '/opt/oracle/oradata/orcl/temp03.dbf' REUSE;
ALTER TABLESPACE TEMP2 ADD TEMPFILE '/opt/oracle/oradata/orcl/temp02.dbf' REUSE;
-- End of tempfile additions.
--

执行重建控制文件脚本
控制文件创建成功会提示Control file created.

recover database using bacup controlfile;
这里起初我选的AUTO，但是报错过不去，后来又选择filename，试了 如下redo文件，最终Log applied了
/opt/oracle/oradata/orcl/redo01.log --失败
/opt/oracle/oradata/orcl/redo03.log --失败
/opt/oracle/oradata/orcl/redo02.log --成功 Log applied
这里大家耐心挨个试一下

alter database open resetlogs;

到这里数据库终于正常打开，完结，撒花