KILL实例进程,重新启动报错如下:
SQL> startup
Oracle instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220360 bytes
Variable Size 159383800 bytes
Database Buffers 360710144 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01113: file 10 needs media recovery
ORA-01110: data file 10: '/u01/app/PROD/disk1/test1.dbf'
ALERT日志中的相关信息:
Tue Nov 11 22:15:44 2014
ALTER DATABASE OPEN
ORA-1113 signalled during: ALTER DATABASE OPEN...
此时查看相关视图,10号数据文件--TEST1表空间的仍处于活动状态--:
SQL> select * from v$backup where STATUS='ACTIVE';
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- -------------------
10 ACTIVE 195848 2014/11/11 22:12:07 -->这里的时间是开始发出热备份命令BEGIN BACKUP的时间及当时SCN。
此时,可以使用两种命令来解决:
1.ALTER DATABASE RECOVER datafile 10;
2.ALTER TABLESPACE TEST1 END BACKUP;
#####################################################
-->如下是解决及OPEN数据库及验证数据文件状态;
SQL> ALTER DATABASE RECOVER datafile 10;
Database altered.
SQL> select * from v$backup where STATUS='ACTIVE';
no rows selected
SQL> alter database open;
Database altered.
ALERT日志信息:
Tue Nov 11 22:15:44 2014
ALTER DATABASE OPEN
ORA-1113 signalled during: ALTER DATABASE OPEN...
Tue Nov 11 22:17:35 2014
ALTER DATABASE RECOVER datafile 10
Tue Nov 11 22:17:35 2014
Media Recovery Start
Tue Nov 11 22:17:35 2014
Recovery of Online Redo Log: Thread 1 Group 5 Seq 6 Reading mem 0
Mem# 0 errs 0: /u01/app/PROD/disk1/redo05.log
Mem# 1 errs 0: /u01/app/PROD/disk2/redo05b.log
Tue Nov 11 22:17:36 2014
Media Recovery Complete (PROD)
Completed: ALTER DATABASE RECOVER datafile 10
检查数据库相关状态:
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- -------------------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 NOT ACTIVE 0
7 NOT ACTIVE 0
8 NOT ACTIVE 0
9 NOT ACTIVE 0
10 NOT ACTIVE 195848 2014/11/11 22:12:07
11 NOT ACTIVE 0
12 NOT ACTIVE 0
12 rows selected.
SQL> select tablespace_name,STATUS from dba_tablespaces;
set linesize 200
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS ONLINE
SYSAUX ONLINE
TEMPTS1 ONLINE
TEMP1 ONLINE
TEMP2 ONLINE
EXAMPLE ONLINE
INDX ONLINE
TOOLS ONLINE
USERS ONLINE
OLTP ONLINE
REGISTRATION ONLINE
TEST1 ONLINE
TEST2 ONLINE
TEST3 ONLINE
15 rows selected.
SQL> SQL> set pagesize 200
SQL> col file_name for a50
SQL> select file_name,file_id,tablespace_name,status from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME STATUS
-------------------------------------------------- ---------- ------------------------------ ---------
/u01/app/PROD/disk1/system01.dbf 1 SYSTEM AVAILABLE
/u01/app/PROD/disk1/undotbs01.dbf 2 UNDOTBS AVAILABLE
/u01/app/PROD/disk1/sysaux01.dbf 3 SYSAUX AVAILABLE
/u01/app/PROD/disk1/example.dbf 4 EXAMPLE AVAILABLE
/u01/app/PROD/disk1/indx.dbf 5 INDX AVAILABLE
/u01/app/PROD/disk1/tools.dbf 6 TOOLS AVAILABLE
/u01/app/PROD/disk1/users.dbf 7 USERS AVAILABLE
/u01/app/PROD/disk1/oltp.dbf 8 OLTP AVAILABLE
/u01/app/PROD/disk1/REGISTRATION.dbf 9 REGISTRATION AVAILABLE
/u01/app/PROD/disk1/test1.dbf 10 TEST1 AVAILABLE
/u01/app/PROD/disk1/test2.dbf 11 TEST2 AVAILABLE
/u01/app/PROD/disk1/test3.dbf 12 TEST3 AVAILABLE