Oracle 9i Online User Managed Backup

Online backup through User Managed.

Oracle Version: 9.0.1.1.1

OS: Windows (But you can apply below procedure for all platforms)

Step:1

Before going further understand what is important files for backup.

1. Controlfile

2. Datafiles

3. SPFILE/PFILE

4. Redolog files

5. Tempfiles

First three points and files are most important and part of the database. so in online hot backup we need to take only first tell files backup.

Forget about Point 4,5, why...? becuase redolog files copy we get as a "archivelog files" and tempfile is temparory files, this file doesn't store user data. and doesn't important & required for database. suppose if we lost tempfile then we create new tempfile for database.

So we need to consider only three points from 1 to 3 (above).

The procedure is follow

1. Put tablespace in backup mode.

2. copy the datafiles which tablespace in backup mode

3. once copy process is complete putout the tablespace from backup mode

4. switch the logfile.

For example:

The following is the tablespace which we need to take backup.

SQL> select tablespace_name from dba_data_files;
TABLESPACE_NAME

------------------------------

SYSTEM

UNDOTBS

INDX

TOOLS

USERS

SQL> alter tablespace SYSTEM begin backup;
Tablespace altered.

SQL> host copy c:\ora9ihome\oradata\ora9i\system01.dbf e:\ora9i\backup

1 file(s) copied.

SQL> --we can check which tablespace/datafile is currently in backup mode

SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------

1 ACTIVE 120148 02-AUG-08

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 NOT ACTIVE 0

5 NOT ACTIVE 0

SQL> alter tablespace SYSTEM end backup;
Tablespace altered.

SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------

1 NOT ACTIVE 120148 02-AUG-08

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 NOT ACTIVE 0

5 NOT ACTIVE 0

Repeat above steps for all tablespace which we want to take backup. always check v$backup view for any datafile is still in backup mode, if you found any which datafile already copied to backup location and still showing in backup mode then putout that tablespace in backup mode through "alter tablespace TABLESPACE NAME end backup;"

Once you complete all datafile backup then force log switch.

SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

After taking all datafiles backup now turn is controlfile backup.

SQL> alter database backup controlfile to

'e:\ora9i\backup\control01.ctl';

Database altered.

Above statement create BINARY controlfile backup copy.

After datafiles and controlfile backup now turn is init.ora (spfile) backup.

SQL> create pfile='e:\ora9i\backup\init.ora' from spfile;
File created.

---here Oracle 9i backup procedure is complete---