How to Resolve ORA-01149: cannot shutdown - file 1 has online backup set

ORA-01149

ORA-01149 means that some data files are in backup mode, so you can't shutdown the database in this moment.

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

Let's see the backup state among data files.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME          CON_ID
---------- ------------------ ---------- --------- ----------
         1 ACTIVE                2926199 24-MAY-21          1
         3 ACTIVE                2926199 24-MAY-21          1
         4 ACTIVE                2926199 24-MAY-21          1
         5 NOT ACTIVE                  0                    2
         6 NOT ACTIVE                  0                    2
         7 ACTIVE                2926199 24-MAY-21          1
         8 NOT ACTIVE                  0                    2
         9 ACTIVE                2926199 24-MAY-21          3
        10 ACTIVE                2926199 24-MAY-21          3
        11 ACTIVE                2926199 24-MAY-21          3
        12 ACTIVE                2926199 24-MAY-21          3

     FILE# STATUS                CHANGE# TIME          CON_ID
---------- ------------------ ---------- --------- ----------
        13 ACTIVE                2926199 24-MAY-21          3

12 rows selected.

As we can see, almost all data files are in backup mode.

Solutions

1. Exit Backup Mode

We can exit the backup mode to solve the error.

SQL> alter database end backup;

Database altered.

We can shutdown the database now.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

2. Shutdown Abort

For some reason, we can't exit the backup mode, so aborting the database can also be a solution.

SQL> shutdown abort;

After that, we need more steps to open the database.

SQL> startup mount;

We recover the database to the latest change.

SQL> recover database;

If there's nothing wrong, we can open the database normally.

SQL> alter database open;

If some changes cannot be applied at this moment, you may consider to open the database with RESETLOGS.

SQL> alter database open resetlogs;

In conclusion, backup mode is extremely useful for duplicate a database server for testing purpose, but you need more steps to get it online.

Leave a Reply

Your email address will not be published. Required fields are marked *