Search This Blog

Saturday, March 1, 2008

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

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)

1 comment:

Ariq said...

Very Useful Post