当前位置：首页 -> 基础 -> 数据库编程

表空间正在热备份时关闭实例重启报错的重现和解决(二)

2015-07-24 09:17:47 · 作者: · 浏览: 1
标签: 空间 正在 备份 关闭 实例 重启 重现 解决
0 ACTIVE 195848 2014/11/11 22:12:07 11 NOT ACTIVE 0 12 NOT ACTIVE 0 12 rows selected. -->可以看到此时有一个数据文件处于ACTIVE状态，结合dba_data_files中信息，此文件属于下在热备份的TEST1表空间。 此时，新开一个会话，KILL掉SMON进程，或者使用SHUTDOWN ABORT命令关闭 数据库。shutdown immediate关闭会提示如下: SQL> shutdown immediate; ORA-01149: cannot shutdown - file 10 has online backup set ORA-01110: data file 10: '/u01/app/PROD/disk1/test1.dbf' --此时ALERT日志提示 Tue Nov 11 22:50:55 2014 Shutting down instance: further logons disabled ###################################################

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/