Backup DBA

What is most important work for DBA’s ?
It is Database BACKUP.

There are simple three methods which you can use to take database backup.
1. COLD backup / Consistent backup / Offline backup
2. HOT backup / Inconsistent backup / Online backup / User Managed backup
3. RMAN / Inconsistent backup / Online backup / Server Managed backup

How to take COLD backup? ( Database running in NO ARCHIVELOG MODE)
1. conn with sysdba user.
SQL>shutdown abort;
SQL>startup restrict;
SQL>shutdown normal;
2. take copy of all datafiles,controlfile,redologfiles & parameter files through OS copy command.
3. startup database
SQL>startup

First use "SHUTDOWN IMMEDIATE" command to shutdown database server without "SHUTDOWN ABORT" but if "SHUTDOWN IMMEDIATE" command is HANG then use "SHUTDOWN ABORT" + "STARTUP RESTRICT" + "SHUTDOWN NORMAL"



How to take HOT backup?
1. conn with sysdba user.
SQL> alter database begin backup;
2. copy all datafiles except “tempfiles” through OS COPY command.
SQL> alter database end backup;
SQL> alter system switch logfile;
SQL> alter database backup controlfile to ‘OS_PATH’;
SQL> create pfile=’OS_PATH\INIT.ORA’ from spfile;

How to take RMAN backup?
1. cmd> set ORACLE_SID=
2. conn with RMAN prompt
cmd>rman target /
RMAN> run
{
Backup database;
Backup archivelog all;
}


NOTE: above are just simple commands which you follow to take database backup.

Except above you can use EXPORT/IMPORT/DATAPUMP to take logical backup of DATABASE TABLES, SCHEMAS, DATABASE.

At database level
cmd>exp username/pwd@tns_entry file=OS-PATH FULL=y

At schema level
cmd>exp username/pwd@tns_entry file=OS-PATH OWNER=username(scott)

At table level
cmd>exp username/pwd@tns_entry file=OS-PATH TABLES=tablename(emp)