A recent database of a customer reported an error in OPEN and needed to be restored. It was found that a tablespace was in hot backup -- ALTERTABLESPACETEST1BEGINBACKUP; then the instance was shut down abnormally (possibly ABORT or KILLSMON, this error is reported when the storage is directly disabled. In ORACLE10.2.0.1 and 11.2.0.4
A recent database of a customer reported an error during OPEN and needed to be restored. The reason was that a TABLESPACE was being hot BACKUP -- alter tablespace TEST1 begin backup; then, the instance is shut down abnormally (it may be ABORT or kill smon, which is said to be caused by direct storage shutdown), and this error occurs when it is restarted. In ORACLE 10.2.0.1 and 11.2.0.4
A recent database of a customer reported an error during OPEN and needed to be restored. The reason was that a TABLESPACE was being hot BACKUP --> alter tablespace TEST1 begin backup; then, the instance is shut down abnormally (it may be ABORT or kill smon, which is said to be caused by direct storage shutdown), and this error occurs when it is restarted. This error is reproduced in ORACLE 10.2.0.1 and 11.2.0.4.
The two versions have the same situation but the error message is not the same., The details are as follows:
In version 10.2.0.1.0, when a hot backup is in progress, the following error occurs when the instance is restarted:SQL> select * from v $ version where rownum = 1; BANNER databases Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod view the status of the data file at this time: SQL> select tablespace_name, STATUS from dba_tablespaces; set linesize 200 set pagesize 200col file_name for a50select file_name, tablespace_name, status from dba_data_files;
TABLESPACE_NAME STATUS certificate --------- SYSTEM has already passed ONLINETEMP1 ONLINETEMP2 onlineexample onlineindx onlinetestls onlineusers onlineoltp ONLINETEST1 ONLINETEST2 ONLINETEST3 ONLINE15 rows selected. SQL> FILE_NAME TABLESPACE_NAME STATUS when running ---------/u01/app/PROD/disk1/system01.dbf SYSTEM AVAILABLE/u01/app/PROD/disk1/undotbs01.dbf UNDOTBS AVAILABLE/u01 /app/PROD/disk1/sysaux01.dbf sysaux available/u01/app/PROD/disk1/example. dbf example available/u01/app/PROD/disk1/indx. dbf indx available/u01/app/PROD/disk1/tools. dbf tools available/u01/app/PROD/disk1/users. dbf users available/u01/app/PROD/disk1/oltp. dbf oltp available/u01/app/PROD/disk1/REGISTRATION. dbf registration available/u01/app/PROD/disk1/test1.dbf TEST1 AVAILABLE/u01/app/PROD/disk1/test2.dbf TEST2 AVAILABLE/u01/app/PROD/disk1/paitest3 availablero12 selected. ######################################## ############### 3 --> Issue the Hot Backup tablespace command: SQL> ALTER TABLESPACE TEST1 BEGIN BACKUP; Tablespace altered. --> query the STATUS of the data file at this time: SQL> select tablespace_name, STATUS from running; TABLESPACE_NAME STATUS --------- SYSTEM has been deleted successfully using ONLINEEXAMPLE ONLINEINDX ONLINETOOLS ONLINEUSERS ONLINEOLTP has been deleted using ONLINE15 rows selected. SQL> set linesize 200SQL> set pagesize 200SQL> col file_name for a50SQL> select file_name, file_id, tablespace_name, status from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME STATUS certificate ---------- certificate ---------/u01/app/PROD/disk1/system01.dbf 1 system available/u01/app/PROD/disk1/quota 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 AVAILABLE12 rows selected. 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 ACTIVE 195848 22:12:0711 not active 0 12 not active 012 rows selected. --> you can see that there is a data file in the ACTIVE state. Combined with the information in dba_data_files, this file belongs to the TEST1 tablespace in the hot backup. In this case, start a new session, KILL the SMON process, or use the shutdown abort command to close the database. Shutdown immediate close will prompt: SQL> shutdown immediate; ORA-01149: cannot shutdown-file 10 has online backup setORA-01110: data file 10: '/u01/app/PROD/disk1/test1.dbf' -- the ALERT Log prompts Tue Nov 11 22:50:55 2014 Shutting down instance: further logons disabled ##################################### ############ KILL an instance process, the following error is reported when you restart: SQL> startupORACLE instance started.
Total System Global Area 524288000 bytesFixed Size 1220360 bytesVariable Size 159383800 bytesDatabase Buffers 360710144 bytesRedo Buffers 2973696 bytes
Database mounted.
ORA-01113: file 10 needs media recovery
ORA-01110: data file 10: '/u01/app/PROD/disk1/test1.dbf'
ALERT Log information:
Tue Nov 11 22:15:44 2014
ALTER DATABASE OPEN
ORA-1113 signalled during: alter database open...
Check the related view. The table space in TEST1, the data file no. 10, is still in the ACTIVE state. --: SQL> select * from v $ backup where STATUS = 'activity ';
FILE # status change # TIME ---------- ------------------ ----------------------- 10 ACTIVE 195848 22:12:07 --> the TIME here is the start TIME of the Hot BACKUP command begin backup and the SCN at that TIME.
In this case, you can use two commands to solve the problem:
1. alter database recover datafile 10;
2. alter tablespace TEST1 end backup;######################################## #############--> Solve and OPEN the database and verify the data file status; SQL> ALTER DATABASE RECOVER datafile 10; Database altered. SQL> select * from v $ backup where STATUS = 'active'; no rows selectedSQL> alter database open; Database altered. ALERT Log information: Tue Nov 11 22:15:44 2014 alter database OPENORA-1113 signalled during: alter database open... tue Nov 11 22:17:35 2014 alter database recover datafile 10Tue Nov 11 22:17:35 2014 Media Recovery StartTue 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. logTue Nov 11 22:17:36 2014 Media Recovery Complete (PROD) Completed: alter database recover datafile 10 Check DATABASE status: 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 22:12:0711 not active 0 12 not active 012 rows selected.
SQL> select tablespace_name, STATUS from region; set linesize 200TABLESPACE_NAME STATUS --------- SYSTEM has been released without using ONLINEEXAMPLE ONLINEINDX ONLINETOOLS ONLINEUSERS ONLINEOLTP using ONLINETEST1 using ONLINE15 rows selected. SQL> set pagesize 200SQL> col file_name for a50SQL> select file_name, file_id, tablespace_name, status from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME STATUS certificate ---------- certificate ---------/u01/app/PROD/disk1/system01.dbf 1 system available/u01/app/PROD/disk1/quota 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
######################################## ######################################## #############
In version 11.2.0.4.0, when a hot backup is being performed on the tablespace, the instance restart error is disabled and the solution is as follows:--> The error message of 11 GB is clearer than that of 10 Gb, and the basic judgment direction is provided based on the error message. 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 1149659 21:49:55 7 not active 07 rows selected. SQL> select sysdate from dual; SYSDATE-------------------2014/11/11 22: 05: 26SQL> ALTER TABLESPACE TEST1 BEGIN BACKUP; Tablespace altered.
Shutdown abort instance and restart: SQL> startupORACLE instance started. Total System Global Area 418484224 bytesFixed Size 1365040 bytesVariable Size 322964432 bytesDatabase Buffers 88080384 bytesRedo Buffers 6074368 bytes
Database mounted.
ORA-10873: file 6 needs to be either taken out of backup mode or media
Recovered
ORA-01110: data file 6: '/u01/app/oracle/oradata/bys1/test01.dbf'The error message in the ALERT Log is: Completed: alter database MOUNTTue Nov 11 22:06:36 2014 alter database OPENErrors in file/u01/app/oracle/diag/rdbms/bys1/bys1/trace/bys1_ora_3910.trc: ORA-10873: file 6 needs to be either taken out of backup mode or media recoveredORA-01110: data file 6: '/u01/app/oracle/oradata/bys1/test01.dbf' ORA-10873 signalled during: alter database open... tue Nov 11 22:06:36 2014 Checker run found 1 new persistent data failuresTue Nov 11 22:21:36 2014
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 ACTIVE 1171509 22:05:40 7 not active 07 rows selected.
End backup or RECOVER DATAFILE
SQL> ALTER TABLESPACE TEST1 end BACKUP;Tablespace altered. ALERT Log: Tue Nov 11 23:00:56 2014 alter tablespace TEST1 end BACKUPCompleted: alter tablespace TEST1 end BACKUPTue Nov 11 23:01:23 2014 recover datafile related logs: tue Nov 11 21:51:02 2014 Checker run found 1 new persistent data failuresTue Nov 11 22:00:46 2014 alter database recover datafile 6 Media Recovery StartSerial Media Recovery startedRecovery of Online Redo Log: thread 1 Group 3 Seq 48 Reading mem 0 Mem #0:/u01/app/oracle/oradata/bys1/redo03.logRecovery of Online Redo Log: thread 1 Group 1 Seq 49 Reading mem 0 Mem #0:/u01/app/oracle/oradata/bys1/redo01.logMedia Recovery Complete (bys1) Completed: alter database recover datafile 6 Tue Nov 11 22:01:01 2014 alter database open
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 1171509 22:05:40 7 not active 07 rows selected.
