Cold backup script

For testing and development DB, it’ archive mode is noarchivelog, and it can be closed on non-working time.

We can use closed database backup.it have following advantages:

• Conceptually simple

• Easy to perform

• Require little operator interaction

[@more@]

--------- backup mutiple backup sets by date dirctory ---------------

#!/bin/bash

## set enviroment variable

ORACLE_SID=shenzhen

ORACLE_HOME=/u01/oracle

ORACLE_BASE=/u01

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

export ORACLE_SID ORACLE_HOME ORACLE_BASE NLS_LANG

## set the backup directory

backdate=`date -u +%Y%m%d`

backupdir=/u01/backup/cold/$backdate

mkdir -p $backupdir

echo "**************************************"

echo "* cold_backup.sh "

echo "**************************************"

$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" << EOF

set feedback off heading off verify off trimspool off

set pagesize 0 linesize 200

-- Set SQL*Plus user variables used in this script

define fil='/u01/workarea/cold_backup.sql'

prompt ... spooling to &fil

-- Create Backup command

spool &fil

select 'host cp ' || name ||' $backupdir' from v$datafile;

select 'host cp ' || member || ' $backupdir' from v$logfile;

select 'host cp ' || name || ' $backupdir' from v$controlfile;

select 'host cp $ORACLE_HOME/dbs/spfileshenzhen.ora $ORACLE_HOME/dbs/orapwshenzhen $backupdir' from dual;

spool off;

-- Shutdown Database

shutdown immediate;

@ &fil

startup;

exit

EOF

------------------------ backup keeping only one backup set ---------

#!/bin/bash

## set enviroment variable

ORACLE_SID=shenzhen

ORACLE_HOME=/u01/oracle

ORACLE_BASE=/u01

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

export ORACLE_SID ORACLE_HOME ORACLE_BASE NLS_LANG

echo "**************************************"

echo "* cold_backup.sh "

echo "**************************************"

$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" << EOF

set feedback off heading off verify off trimspool off

set pagesize 0 linesize 200

-- Set SQL*Plus user variables used in this script

define dir='/u01/backup/cold/'

define fil='/u01/workarea/cold_backup.sql'

prompt ... spooling to &fil

-- Create Backup command

spool &fil

select 'host cp ' || name ||' &dir' from v$datafile;

select 'host cp ' || member || ' &dir' from v$logfile;

select 'host cp ' || name || ' &dir' from v$controlfile;

select 'host cp $ORACLE_HOME/dbs/spfileshenzhen.ora $ORACLE_HOME/dbs/orapwshenzhen &dir' from dual;

spool off;

-- Shutdown Database

shutdown immediate;

@ &fil

startup;

exit