Skip to content

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

  • by

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, they are active and online. We have to exit the backup mode before shutting down the database.

Solutions

1. Exit Backup Mode

We can exit the backup mode to solve the error.

SQL> alter database end backup;

Database altered.

Then shutdown the database.

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.