User Managed Backups (Cold Backup)

User Managed Backup Overview:

A user-managed backup is made by performing a physical copy of data files using the OS commands. These copies are moved to a separate location using OS commands. The user maintains a record of the backups. For the recovery operation we have to move back (or to the new location of the database) the files and perform the recovery.  

The user-managed backups could be take at the following levels:

·         Data file level

·         Tablespace level

·         Database level 

Offline Backup (Cold Backup) steps :

A cold backup, also called an offline backup, is a database backup when the database is offline and thus not accessible for updating. This is the safest way to back up because it avoids the risk of copying data that may be in the process of being updated. However, a cold backup involves downtime because users cannot use the database while it is being backed up.

Below are steps for Cold Backup:

Step 1: Check the locations of  Datafiles, Redolog files, Temp files & controlfiles

SQL>select name from v$datafile;

SQL>select name from v$tempfile;
SQL>select name from v$controlfile;
SQL>select member from v$logfile;

Step2: Backup the control file and perform a trace of the control file.

SQL>alter database backup controlfile to 'u01/BACKUP/control_trace.ctl';

SQL>alter database backup controlfile to trace;

SQL>create pfile from spfile;

Init.ora and config.ora Located under $ORACLE_HOME/dbs directory (UNIX / LINUX)

On Windows %ORACLE_HOME/database directory

Step 3: Shut down the database.

SQL>shutdown

Copy all data files into the new directories on the new server Using with OS command. 

scp /u01/app/oradata/orcl/* host@192.168.1.1:/u01/oradata/backup (UNIX)
copy E:\app\oradata\orcl\* \\192.168.1.1\oradata\backup (Windows)
Copy Controlfile & Pfile backup files.
Once copied all the necessary files start the database.



Step 4: start the database


$sqlplus / as sysdba

SQL>startup