How to take user managed Database Backups

TAKING OFFLINE BACKUPS. ( UNIX )

Shutdown the database if it is running. Then start SQL Plus and connect as SYSDBA.

$sqlplus

SQL> connect / as sysdba

SQL> Shutdown immediate

SQL> Exit

After Shutting down the database. Copy all the datafiles, logfiles, controlfiles, parameter file and password file to your backup destination.

TIP:

To identify the datafiles, Logfiles query the data dictionary tables V$DATAFILE and V$LOGFILE before shutting down.

Lets suppose all the files are in "/u01/ica" directory. Then the following command copies all the files to the backup destination /u02/backup.

$cd /u01/ica

$cp * /u02/backup/

Be sure to remember the destination of each file. This will be useful when restoring from this backup. You can create text file and put the destinations of each file for future use. Now you can open the database.

TAKING ONLINE (HOT) BACKUPS.(UNIX)

To take online backups the database should be running in Archivelog mode. To check whether the database is running in  Archivelog mode or Noarchivelog mode. Start sqlplus and then connect as SYSDBA.

After connecting give the command "archive log list" this will show you the status of archiving.

$sqlplus

Enter User:/ as sysdba

SQL> ARCHIVE LOG LIST

If the database is running in archive log mode then you can take online backups.

Let us suppose we want to take online backup of  "USERS" tablespace. You can query the V$DATAFILE view to find out the name of datafiles associated with this tablespace. Lets suppose the file is  

"/u01/ica/usr1.dbf ".

Give the following series of commands to take online backup of USERS tablespace.

$sqlplus

Enter User:/ as sysdba

SQL> alter tablespace users begin backup;

SQL> host cp /u01/ica/usr1.dbf   /u02/backup

SQL> alter tablespace users end backup;

SQL> exit;

ALTER DATABASE BEGIN BACKUP ON OPEN MODE

AS SYSDBA

sql>alter database begin backup;
Database altered.

sql>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 ACTIVE
2 ACTIVE
3 ACTIVE
4 ACTIVE

NOTE: All the datafiles are in backup mode, now you can copy your all datafiles to backup location.


sql>alter database end backup;
Database altered.
sql>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 NOT ACTIVE
2 NOT ACTIVE
3 NOT ACTIVE
4 NOT ACTIVE