Making User-Managed Backups of Online/Offline 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.

Making User-Managed Backups of Offline Tablespaces and Datafiles
We can take all or individual tablespace backup while tablespace is OFFLINE. and all other tablespace is remain available and open for systemwide use.


Procedure

Identify datafiles associate with tablespace

SQL> select tablespace_name,file_name
2 from dba_data_files
3 where tablespace_name = 'USERS';

TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
USERS C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF

SQL> alter tablespace users offline normal;

Tablespace altered.

SQL> --copy datafiles to backup location
SQL> alter tablespace users online;

Tablespace altered.

SQL> alter system archive log current;

System altered.


Keep in mind

1. We can't take system tablespace or any active undo segment tablespace OFFLINE.
2. Assume that a table is in tablespace Primary and its index is in tablespace Index. Taking tablespace Index offline while leaving tablespace Primary online can cause errors when DML is issued against the indexed tables located in Primary. The problem only manifests when the access method chosen by the optimizer needs to access the indexes in the Index tablespace.
3. If you took the tablespace offline using temporary or immediate priority, then you cannot bring the tablespace online unless you perform tablespace recovery.

Posted by Mohammed Taj at 12:33:00 PM 0 comments
Labels: User Managed Backup
Making User-Managed Backups of the Whole Database
We can take two types of backup
1. Consistent / coldbackup / offline
2. Inconsistent / hotbackup / online

How to take consistent backup and what is consistent backup.

Consistent backup means all datafile , controlfile, redolog file having same SCN number. and consistent must be taken after database shutdown with NORMAL, TRANSACTIONAL, IMMEDIATE options.

For consistent backup no need to operate database in archivelog mode or we can say if database running in no archivelog mode then we can use COLDBACKUP/CONSISTENT/OFFLINE backup method.


Procedure


1. shutdown IMMEDIATE;
2. COPY ALL *.dbf, *.log, *.ctl files
3. COPY spfilesid.ora,initsid.ora files
4. startup


Inconsistent or hotbackup or online backup

When database availability require 24x7 so we have to use ONLINE backup method.
and when we take online backup that backup called is inconsistent backup.
for online backup database MUST BE running in archivelog mode.

Control files play a crucial role in database restore and recovery. For databases running in ARCHIVELOG mode, Oracle Corporation recommends that you back up control files with the ALTER DATABASE BACKUP CONTROLFILE TO 'filename' statement.


Procedure

Hotbackup on Linux