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)

Cold Backup with RMAN

Database Cold/offline Backup with RMAN

Database Require in mount mode when cold backup taken through RMAN.


C:\> set ORACLE_SID=sidname
C:\> rman TARGET=SYS

RMAN> run
{
#shutdown database and startup with mount mode for backup.
shutdown immediate;
startup mount;
#perform full database backup
backup database;
#open database for normal use
sql 'alter database open';
#backup current controlfile
backup current controlfile;
sql 'alter database backup controlfile to TRACE';
}

Logical_bkp_EXPORT_windows

Oracle Version : 10.1.0.2.0
OS system : Windows XP sp2

SQL> create or replace directory DATA as 'c:\data';

Directory created.

SQL> grant read,write on directory data to hr;

Grant succeeded.


SQL> spool c:\logical_bkp\orcl.sql
SQL> column today new_val dt
SQL> select to_char(sysdate,'ddmmyy') today from dual;

TODAY
------
070307

SQL> PROMPT host expdp system/oracle@orcl DIRECTORY=data DUMPFILE=orcl_&dt..dmp-

> LOGFILE=orcl_&dt..log SCHEMAS=hr
host expdp system/oracle@orcl DIRECTORY=data DUMPFILE=orcl_070307.dmp LOGFILE=or
cl_070307.log SCHEMAS=hr
SQL>PROMPT exit;
exit
SQL> spool off


SQL> host type c:\logical_bkp\orcl.bat
sqlplus -s system/oracle@orcl @c:\logical_bkp\orcl.sql

NOTE: Schedule ORCL.BAT file through SCHEDULE TASK utility of windows.

User_managed_HOT_BACKUP_linux

Oracle Version : 10.2.0.1.0
Linux Version : Ver 3 update 4

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 6 19:44:02 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn sys/oracle as sysdba
Connected.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Current log sequence 8
SQL> REM change archive log mode.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 1218944 bytes
Variable Size 83887744 bytes
Database Buffers 188743680 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL> REM archive log generate in FLASH_RECOVERY_AREA. >>i am configure FRA for database<<<
SQL> REM must sure DB_RECOVERY_FILE_DEST_SIZE have enough space.

SQL> alter system set db_recovery_file_dest_size = 5g;

System altered.

SQL> REM archive location is
SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /home/oracle/oracle/product/10
.2.0/db_1/flash_recovery_area
db_recovery_file_dest_size big integer 5G

SQL> REM archive log format
SQL> show parameter log_archive_format

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
SQL> set heading off echo off verify off feedback off linesize 150 pagesize 0
SQL> spool /home/oracle/hotbackup.sql
SQL> select 'alter tablespace ' ||tablespace_name||' begin backup;'
2 from dba_data_files;
alter tablespace USERS begin backup;
alter tablespace SYSAUX begin backup;
alter tablespace UNDOTBS1 begin backup;
alter tablespace SYSTEM begin backup;
SQL> select 'host cp '||name||' /home/oracle/backup/hot '
2 from v$datafile;
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/system01.dbf /home/oracle/backup/hot
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/undotbs01.dbf /home/oracle/backup/hot
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/sysaux01.dbf /home/oracle/backup/hot
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/users01.dbf /home/oracle/backup/hot

SQL> select 'alter database backup controlfile to ''/home/oracle/backup/hot/control01.ctl'';'
2 from dual;

alter database backup controlfile to '/home/oracle/backup/hot/control01.ctl';

SQL> select 'alter database backup controlfile to ''/home/oracle/backup/hot/control02.ctl'';'
2 from dual;

alter database backup controlfile to '/home/oracle/backup/hot/control02.ctl';

SQL> select 'alter database backup controlfile to ''/home/oracle/backup/hot/control03.ctl'';'
2 from dual;

alter database backup controlfile to '/home/oracle/backup/hot/control03.ctl';



SQL> select 'alter tablespace '||tablespace_name||' end backup;'
2 from dba_data_files;
alter tablespace USERS end backup;
alter tablespace SYSAUX end backup;
alter tablespace UNDOTBS1 end backup;
alter tablespace SYSTEM end backup;
SQL> spool off
SQL> set heading on echo on verify on feedback on
SQL> exit
[oracle@localhost ~]$ vi hotbkp.sh
[oracle@localhost ~]$ chmod 755 hotbkp.sh
[oracle@localhost ~]$ crontab -e
crontab: installing new crontab
[oracle@localhost ~]$ crontab -l
#Cold backup script every day 06:24 pm.log file generated in backup.log
24 19 * * * /home/oracle/coldbkp.sh > /home/oracle/backup.log

#Hot backup script every day 08:06 pm. log file generated in backup1.log
29 20 * * * /home/oracle/hotbkp.sh > /home/oracle/backup1.log


hope this helps
Taj

COLD_BACKUP_linux

Oracle Version : 10.2.0.1.0
RedHat Linux : Ver 3 update 4


shutdown.sql


[oracle@localhost ~]$ cat shutdown.sql
SQL> PROMPT startup force;
startup force
SQL> PROMPT shutdown normal;
shutdown normal

SQL>PROMPT exit;
exit
[oracle@localhost ~]$


coldbackup.sql

[oracle@localhost ~]$ cat coldbackup.sql
SQL> select ' host cp '||name||' /home/oracle/backup/' from v$datafile
2 union all
3 select ' host cp '||name||' /home/oracle/backup/' from v$tempfile
4 union all
5 select ' host cp '||member||' /home/oracle/backup/' from v$logfile
6 union all
7 select ' host cp ' ||name||' /home/oracle/backup/' from v$controlfile;
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/system01.dbf /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/undotbs01.dbf /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/sysaux01.dbf /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/users01.dbf /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/temp01.dbf /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/redo03.log /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/redo02.log /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/redo01.log /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/control01.ctl /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/control02.ctl /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/control03.ctl /home/oracle/backup/

SQL> PROMPT host cp /home/oracle/oracle/product/10.2.0/db_1/dbs/spfileoracle.ora /home/oracle/backup

host cp /home/oracle/oracle/product/10.2.0/db_1/dbs/spfileoracle.ora /home/oracle/backup

SQL> PROMPT exit;
exit
[oracle@localhost ~]$


startup.sql

[oracle@localhost ~]$ cat startup.sql
SQL> PROMPT startup
startup

SQL>PROMPT exit;
exit
[oracle@localhost ~]$



coldbkp.sh

[oracle@localhost ~]$ cat coldbkp.sh
#!/bin/bash

ORACLE_SID=oracle
ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1
export ORACLE_SID
export ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH

sqlplus -s sys/oracle as sysdba @/home/oracle/shutdown.sql
sqlplus -s sys/oracle as sysdba @/home/oracle/coldbackup.sql
sqlplus -s sys/oracle as sysdba @/home/oracle/startup.sql


[oracle@localhost ~]$


Automatic Schedule through CRONTAB utility

[oracle@localhost ~]$ crontab -l
24 19 * * * /home/oracle/coldbkp.sh > /home/oracle/backup.log


NOTE:
1.Change ORACLE_SID,ORACLE_HOME,PATH variable according your side.
2.Connect with ORACLE user NOT root.
3.Give 755 premission to COLDBKP.SH script >>>chmod 755 coldbkp.sh<<<
4.Check backup.log file for any errors. >>>/home/oracle/backup.log<<<
5.Must test your backup on TEST SERVER.

hope this helps
Taj

COLD_BACKUP_windows

Database Version : 10.1.0.2.0
OS Platform : WinXP sp2
-------------------------------
Archive log : Disabled
Cold Backup
--------------------------------
I create three SQL scripts
1.shutdown.sql
2.startup.sql
3.copy.sql
----------------
Content of files.
1.shutown.sql
cmd>sqlplus "/@db01 as sysdba"
SQL>set headning off echo off verify off feedback off pagesize 0
SQL>spool d:\bkp_scripts\db01\shutdown.sql
SQL>PROMPT shutdown immediate;
shutdown immediate
SQL>PROMPT exit;
exit
SQL>spool off;
-----------------------------------
2.startup.sql
cmd>sqlplus "/@db01 as sysdba"
SQL>set heading off echo off verify off feedback off pagesize 0
SQL>spool d:\bkp_scripts\db01\startup.sql
SQL>PROMPT startup open pfile=d:\oracle\product\10.1.0\db_1\database\initDB01.ora
startup open pfile=d:\oracle\product\10.1.0\db_1\database\initDB01.ora
SQL>exit;
exit
SQL>spool off;
--------------------------------------------
3.copy.sql
cmd>sqlplus "/@db01 as sysdba"
SQL>spool d:\bkp_scripts\db01\copy.sql
SQL> select ' host COPY ' || name || ' d:\coldbkp_db01\ ' from v$datafile
2 union all
3 select ' host COPY ' || name || ' d:\coldbkp_db01\ ' from v$controlfile
4 union all
5 select ' host COPY ' || member || ' d:\coldbkp_db01\ ' from v$logfile;
6 union all
7 select ' host COPY ' || name || ' d:\coldbkp_db01\ ' from v$tempfile;

host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\SYSTEM01.DBF d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\UNDOTBS01.DBF d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\SYSAUX01.DBF d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\USERS01.DBF d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\CONTROL01.CTL d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\CONTROL02.CTL d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\CONTROL03.CTL d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\REDO03.LOG d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\REDO02.LOG d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\REDO01.LOG d:\coldbkp_db01\
host COPY C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\TEMP01.DBF d:\coldbkp_db01\

SQL> PROMPT host copy c:\oracle\product\10.1.0\db_1\database\initDB01.ora d:\coldbkp_db01\
host copy c:\oracle\product\10.1.0\db_1\database\initDB01.ora d:\coldbkp_db01\
SQL> PROMPT host copy c:\oracle\product\10.1.0\db_1\database\pwdDB01.ora d:\coldbkp_db01\
host copy c:\oracle\product\10.1.0\db_1\database\pwdDB01.ora d:\coldbkp_db01\
SQL>PROMPT exit;
exit
SQL> spool off
----------------------------------------------------------------
I schedule my backup every friday 11 pm. i create COLDBKP.CMD file
4.coldbkp.cmd
e:\oracle\ora90\bin\sqlplus -S "/@db01 as sysdba" @d:\bkp_scripts\db01\shutdown.sql
e:\oracle\ora90\bin\sqlplus -S "/@db01 as sysdba" @d:\bkp_scripts\db01\copy.sql
e:\oracle\ora90\bin\sqlplus -S "/@db01 as sysdba" @d:\bkp_scripts\db01\startup.sql
-------------------------------------------------------
Schedule through SCHEDULE TASK windows utility.

Note : above scripts is my own workshop example. Test first any scripts before using.i am not responsible for any damage.


Note : If you don't include redolog files in COLD BACKUP. then you must open your database with RESETLOGS ( recover database through COLDBACKUP)

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ORACLE
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> host del c:\oracle\product\10.1.0\oradata\oracle\*.LOG

SQL> host xcopy c:\oracle\product\10.1.0\oradata\oracle c:\ORACLEBKP
C:\oracle\product\10.1.0\oradata\oracle\CONTROL01.CTL
C:\oracle\product\10.1.0\oradata\oracle\CONTROL02.CTL
C:\oracle\product\10.1.0\oradata\oracle\CONTROL03.CTL
C:\oracle\product\10.1.0\oradata\oracle\SYSAUX01.DBF
C:\oracle\product\10.1.0\oradata\oracle\SYSTEM01.DBF
C:\oracle\product\10.1.0\oradata\oracle\TEMP01.DBF
C:\oracle\product\10.1.0\oradata\oracle\UNDOTBS01.DBF
C:\oracle\product\10.1.0\oradata\oracle\USERS01.DBF
8 File(s) copied

SQL> HOST del c:\oracle\product\10.1.0\oradata\oracle
c:\oracle\product\10.1.0\oradata\oracle\*, Are you sure (Y/N)? Y


SQL> host xcopy c:\ORACLEBKP c:\oracle\product\10.1.0\oradata\oracle
C:\ORACLEBKP\CONTROL01.CTL
C:\ORACLEBKP\CONTROL02.CTL
C:\ORACLEBKP\CONTROL03.CTL
C:\ORACLEBKP\SYSAUX01.DBF
C:\ORACLEBKP\SYSTEM01.DBF
C:\ORACLEBKP\TEMP01.DBF
C:\ORACLEBKP\UNDOTBS01.DBF
C:\ORACLEBKP\USERS01.DBF
8 File(s) copied


SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.

SQL> recover until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
oracle OPEN

SQL>

Logical_bkp_EXPORT_linux

Oracle Version : 10.1.3.0.0
OS System : Linux Ver 3
-----------------------------

Export_bkp_scheduling on linux server.
-------------------------------------
shell script for backup.
-----------------------

oracle@oracle]$vi expbkp.sh
#press i or a ( for insert mode)

#!/bin/bash
ORACLE_HOME= /db/app/oracle/orahome #put_oracle_home_path
export ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH

#for system date with dmp file.
value= ` date '%d%m%y'`
exp userid/pwd file=file_name$value.dmp log=log_name$value.log

#type shift + zz for file closing.
shift+ZZ
-----------------------------------------
oracle@oracle]$cat expbkp.sh # check expbkp.sh file contents.
--------------------------------------------------------
Schedule ur backup according ur time Eg> every night 10 pm. for scheduling use "CRONTAB" utility.

-------------------------------------
oracle@oracle]$crontab -e #for create new crontab file.
#press i or a for insert mode
0 20 * * * /db/app/expbkp.sh #give compete path of ur .sh ( bkp script file).

------------------------------------------
oracle@oracle]$crontab -l ( for check crontab file)
oracle@oracle]$crontab -r ( for delete crontab file entry )
----------------------------------------------------------