Oracle无法正常OPEN(三）

在Oracle数据库中，如果几个数据文件丢失，导致数据库无法启动，报错“ORA-01157: cannot identify/lock data file 2 - see DBWR trace file”，如果没有物理备份的情况下，位于丢失数据文件的数据是无法找回的，但是没有丢失的数据文件上的数据还在，所有可以通过重建控制文件，去掉丢失的数据文件，来恢复数据库

创建测试数据

create tablespace tbs1 datafile '/data/oracle/prod/tbs01.dbf' size 10M;
conn acl_test/oracle;
create table t2 (id int,name varchar2(10)) tablespace tbs1;
insert into acl_test.t2 values(1,'aaa');
commit;

模拟数据文件丢失

mv tbs01.dbf  tbs01.dbf_bak

重启数据库

shutdown abort;
SQL> startup
ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  8625856 bytes
Variable Size             788529472 bytes
Database Buffers         2415919104 bytes
Redo Buffers                8151040 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/data/oracle/prod/tbs01.dbf'

重建控制文件

#备份出当前控制文件
alter database backup controlfile  to trace as  '/home/oracle/cur.trc';
#根据/home/oracle/cur.ctl编辑重建控制文件语句
[oracle@test ~]$ vi ctl.sql 

CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/data/oracle/prod/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/data/oracle/prod/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/data/oracle/prod/redo03.log'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
--   GROUP 4 '/data/oracle/prod/std1.log'  SIZE 200M BLOCKSIZE 512,
--   GROUP 5 '/data/oracle/prod/std2.log'  SIZE 200M BLOCKSIZE 512,
--   GROUP 6 '/data/oracle/prod/std3.log'  SIZE 200M BLOCKSIZE 512,
--   GROUP 7 '/data/oracle/prod/std4.log'  SIZE 200M BLOCKSIZE 512
DATAFILE
  '/data/oracle/prod/system01.dbf',
  '/data/oracle/prod/sysaux01.dbf',
  '/data/oracle/prod/undotbs01.dbf',
  '/data/oracle/prod/test01.dbf',
  '/data/oracle/prod/users01.dbf'
CHARACTER SET AL32UTF8
;

在nomount状态下执行

 startup nomount;
 @ctl.sql
 Control file created.

SQL> select open_mode from v$database;

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

恢复数据库

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 2: '/data/oracle/prod/tbs01.dbf'

#查看表空间
SQL> select name from v$tablespace
  2  ;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEST
USERS
TBS1
发现tbs1表空间还在，查看数据文件

SQL> select file#,name,status from v$datafile;

     FILE# NAME                                               STATUS
---------- -------------------------------------------------- -------
         1 /data/oracle/prod/system01.dbf                     SYSTEM
         2 /u01/app/oracle/product/12.1.0/db_1/dbs/UNNAMED000 RECOVER
           02

         3 /data/oracle/prod/sysaux01.dbf                     RECOVER
         4 /data/oracle/prod/undotbs01.dbf                    RECOVER
         5 /data/oracle/prod/test01.dbf                       RECOVER
         7 /data/oracle/prod/users01.dbf                      RECOVER

6 rows selected
#发现2号数据文件路径异常，尝试将此表空间offline
SQL> alter database datafile 2 offline;

Database altered.
#恢复数据库
SQL> recover database;
Media recovery complete.
尝试open数据库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 12452
Session ID: 9 Serial number: 19207
再次startup数据库
SQL> startup
ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  8625856 bytes
Variable Size             788529472 bytes
Database Buffers         2415919104 bytes
Redo Buffers                8151040 bytes
Database mounted.
Database opened.

查看测试数据

SQL> select * from acl_test.t2;
select * from acl_test.t2
                       *
ERROR at line 1:
ORA-00376: file 2 cannot be read at this time
ORA-01111: name for data file 2 is unknown - rename to correct file
ORA-01110: data file 2: '/u01/app/oracle/product/12.1.0/db_1/dbs/UNNAMED00002'

SQL> select file_name,tablespace_name,status from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                STATUS
------------------------------ ---------
/data/oracle/prod/system01.dbf
SYSTEM                         AVAILABLE

/data/oracle/prod/sysaux01.dbf
SYSAUX                         AVAILABLE

/data/oracle/prod/test01.dbf
TEST                           AVAILABLE


FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                STATUS
------------------------------ ---------
/data/oracle/prod/users01.dbf
USERS                          AVAILABLE

/u01/app/oracle/product/12.1.0/db_1/dbs/UNNAMED00002
TBS1                           AVAILABLE

/data/oracle/prod/undotbs01.dbf
UNDOTBS1                       AVAILABLE

删除丢失的表空间

DROP TABLESPACE tbs1 INCLUDING CONTENTS AND DATAFILES;