Making User-Managed Backups of Online Tablespaces and Datafiles

We can take all or individual datafiles backup when database is open but the procedure is differs depending on whether the online tablespace is READ-WRITE or READ-ONLY mode.

Note: You should not backup temporary tablespace

Tablespace is READ-WRITE mode.
we must put tablespace in backup mode when tablespace is read-write mode, online and database is open.

alter tablespace tablespace_name begin backup;
through OS utility copy datafiles to backup location
alter tablespace tablespace_name end backup;
alter system archive log current;

For multiple tablespace backup we can use

alter database begin backup;
through OS utility copy all datafiles to backup location
alter database end backup;
alter system archive log current;


Ending a Backup After an Instance Failure or SHUTDOWN ABORT

During backup period if database instance failure or power failure or shutdown abort command is issue. and when we try to open database after fix mention error then we will get below error message

ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF'

Then first check V$BACKUP views.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 3224323 27-SEP-07
2 ACTIVE 3224323 27-SEP-07
3 ACTIVE 3224323 27-SEP-07
4 ACTIVE 3224323 27-SEP-07

All datafiles is still in backup mode...oh, so we need to take out all datafiles from backup mode.

SQL> alter database end backup;

Database altered.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 3224323 27-SEP-07
2 NOT ACTIVE 3224323 27-SEP-07
3 NOT ACTIVE 3224323 27-SEP-07
4 NOT ACTIVE 3224323 27-SEP-07

SQL> alter database open;

Database altered.


Keep in mind

1. Do not use ALTER DATABASE END BACKUP if you have restored any of the affected files from a backup.

2. We can only use "alter database end backup" statement when database is mount mode.

3. We can also use "RECOVER DATABASE" command instead of "alter database end backup" statement but it is slow process.

Making User-Managed Backups of Read-Only Tablespaces

We tablespace is READ-ONLY mode then no need to put tablespace in BACKUP mode. becuase oracle server already prevent to make changes on tablespace.