ORA-01149:cannot shutdown-file 1 has online backup set OR ORA-10873:file 1 needs to be either taken out of backup mode or media recovered

Oracle Error: While shutdown your database you may encounter following oracle error.

SQL> shutdown immediate;
ORA-01149: cannot shutdown – file 1 has online backup set
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/RTS/system01.dbf’

Cause:

If you try to shutdown database when database is in hot backup mode, you will encountered above mentioned error. Kindly verify with your team mates with hot backup activity, Oracle will not allow you to shutdown your database in above scenario, i.e. shutdown, shutdown immediate, shutdown transactional OR startup force will be failed with ORA-01149 oracle error.

Root cause detection:

Following query will help you to understand which oracle tablespace datafile is in hot backup mode.

SQL> select a.tablespace_name, b.status from dba_data_files a, v$backup b where a.file_id=b.file# order by tablespace_name;
TABLESPACE_NAME                STATUS
------------------------------ ------------------
SYSAUX                         ACTIVE
SYSTEM                         ACTIVE
UNDOTBS1                       ACTIVE
USERS                          ACTIVE

Status of the all datafile seems to be active. i.e. hot backup mode.

Solution:
Issue following SQL command in order to move out database from hot backup mode:

SQL> alter database end backup;
Database altered.

Now you can gracefully shutdown your database.

 

Cause: ORA-10873

In case you force fully trying to shutdown database with “shutdown abort” OR instance crashes for some reason OR database have not been shutdown gracefully than you would encountered “ORA-10873” error while next startup of your database.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1469792256 bytes
Fixed Size 2213456 bytes
Variable Size 1040189872 bytes
Database Buffers 419430400 bytes
Redo Buffers 7958528 bytes
Database mounted.
ORA-10873: file 1 needs to be either taken out of backup mode or media recovered
ORA-01110: data file 1: '/u01/app/oracle/oradata/RTS/system01.dbf'

Solution:

Bring out your database from hot backup mode with the help of following SQL:

SQL> alter database end backup;
Database altered.

OR, you can individually take out tablespaces from hot backup mode:

SQL> alter tablespace system end backup;
Tablespace altered.

Open database:

SQL> alter database open;
Database altered.

Your database opened in READ WRITE mode.

Stay Tune. 🙂

Leave a Reply