ALTER DATABASE BEGIN/END BACKUP

To take backup manually or when cloning a database using the following SQL one can place the database in backup mode.

SQL> ALTER DATABASE BEGIN BACKUP;
Database altered.

To find if database or any tablespace is in backup mode, the status in V$BACKUP is ACTIVE
SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 ACTIVE 11453595 31-OCT-10
2 ACTIVE 11453595 31-OCT-10
3 ACTIVE 11453595 31-OCT-10
4 ACTIVE 11453595 31-OCT-10

… perform database manual or cloning
SQL> ALTER DATABASE END BACKUP;
Database altered.

To check if database is in backup mode
SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 NOT ACTIVE 11453595 31-OCT-10
2 NOT ACTIVE 11453595 31-OCT-10
3 NOT ACTIVE 11453595 31-OCT-10
4 NOT ACTIVE 11453595 31-OCT-10

If one tries to perform shutdown of database when database is in BACKUP mode, you will receive the following error “ORA-01149”
SQL> shutdown immediate;
ORA-01149: cannot shutdown – file 1 has online backup set
ORA-01110: data file 1: ‘/u01/oradata/TEST/system_01.dbf’

If one tries to set the database that is already in backup mode, one will receive the following error “ORA-01146”
SQL> alter database begin backup;
alter database begin backup
*
ERROR at line 1:
ORA-01146: cannot start online backup – file 1 is already in backup
ORA-01110: data file 1: ‘/u01/oradata/TEST/system_01.dbf’

In 9i to set database is backup mode, “ALTER DATABASE BEGIN/END BACKUP” doesn’t exists so it has to be done at individual table space level by executing “ALTER TABLESPACE <tablespace> BEGIN/END BACKUP”.

If one tries to perform incremental backup using RMAN it will skip backing up datafiles as datafiles are in backup
RMAN> backup incremental level 1 database;

Starting backup at 31-OCT-10
using channel ORA_DISK_1
RMAN-06554: WARNING: file 1 is in backup mode
RMAN-06554: WARNING: file 2 is in backup mode
RMAN-06554: WARNING: file 3 is in backup mode
RMAN-06554: WARNING: file 4 is in backup mode
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=/u01/oradata/TEST/sysaux_01.dbf
skipping datafile 00003 because it has not changed
input datafile fno=00001 name=/u01/oradata/TEST/system_01.dbf
skipping datafile 00001 because it has not changed
input datafile fno=00002 name=/u01/oradata/TEST/undo_01.dbf
skipping datafile 00002 because it has not changed
input datafile fno=00004 name=/u01/oradata/TEST/test_01.dbf
skipping datafile 00004 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped

One thought on “ALTER DATABASE BEGIN/END BACKUP

  1. Hi.
    I don’t think this statement is right “If one tries to perform incremental backup using RMAN it will skip backing up datafiles as datafiles are in backup”
    The message says those files didn’t change, so no backup is needed.
    Try opening the database and back it up again, even in begin backup mode, I think you’ll see the backup working

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s