Oracle Class – Wissem's Oracle and NOSQL Tips – Tunisia's First Oracle, NOSQL Website

Introduction:
This example for Linux creates a job that performs a cold backup of the database using RMAN (Recovery Manager).
The example contains 4 scripts:

[oracle@wissem scripts]$ ls -rtl
total 16
-rwxr--r-- 1 oracle oinstall 143 May 30 19:14 main.sh
-rwxr--r-- 1 oracle oinstall 522 May 30 20:35 coldbackup.rman
-rw-r--r-- 1 oracle oinstall 642 May 30 20:40 main.sql
-rwxr--r-- 1 oracle oinstall 165 May 30 20:42 coldbackup.sh

Detached job:
You can use a detached job to run an application separately from the database. Detached job is independent of the status of the instance; The instance can be down and the detached job will run in another and independent process to the scheduler.

The example:

1- coldbackup.rman script:
it’s a script that simply invokes RMAN, shutdown the database, bring the instance to the mount state, open the wallet and perform a whole database backup plus archivelog.

You may have noticed the use of the Oracle wallet, it’s because I configured the persistent setting to use the encrypted backup. If you don’t open the wallet in this case, you will receive an error ORA-28365: wallet is not open. Remember if you want to create a password wallet you can run the following command via RMAN;

RMAN> sql 'alter system set encryption key authenticated by "wissem"';
sql statement: alter system set encryption key authenticated by "wissem"
RMAN>

You may have also noticed the use of “change archivelog all crosscheck;” command, this is to perform a cross check of the archivelog files. Missed, moved archivelog files will raise the ” RMAN-06059: expected archived log not found ” error, if you don’t cross check them.

The content of the script:

[oracle@wissem scripts]$ chmod u+x coldbackup.rman
[oracle@wissem scripts]$ cat coldbackup.rman
run {
# Shut down database for backups and put into MOUNT mode
shutdown immediate;
startup mount;
# Validate archivelog files
change archivelog all crosscheck;
#Open Wallet
sql 'alter system set encryption wallet open identified by "wissem"';
# Perform full database backup
backup database plus archivelog;
# Open database after backup
alter database open;
# Call notification routine to indicate job completed successfully
sql " BEGIN  DBMS_SCHEDULER.END_DETACHED_JOB_RUN(''sys.cold_backup_job'', 0,
  null); END; ";
}

[oracle@wissem scripts]$

2- coldbackup.sh script:
This script simply invokes RMAN, call the coldbackup.rman script and log the RMAN execution into a log file called “coldbackup.out”.

The content of the script:

[oracle@wissem scripts]$ chmod u+x coldbackup.sh
[oracle@wissem scripts]$ ls -rtl
total 4
-rwxr--r-- 1 oracle oinstall 173 May 30 18:40 coldbackup.sh
[oracle@wissem scripts]$
[oracle@wissem scripts]$ cat coldbackup.sh
#!/bin/sh
source /home/oracle/.bash_profile
$ORACLE_HOME/bin/rman TARGET / @$ORACLE_HOME/scripts/coldbackup.rman > /u01/app/oracle/backup/coldbackup.out
exit 0
[oracle@wissem scripts]$

The content of the .bash_profile file is:

[oracle@wissem ~]$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
	. ~/.bashrc
fi

# User specific environment and startup programs

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/home/oracle/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=ORAWISS; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
JAVA_HOME=/usr/lib/jvm/java-1.6.0-openjdk-1.6.0.0.x86_64
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

if [ $USER = "oracle" ]; then
  if [ $SHELL = "/bin/ksh" ]; then
    ulimit -p 16384
    ulimit -n 65536
  else
    ulimit -u 16384 -n 65536
  fi
fi
[oracle@wissem ~]$

3- main.sql script:
This script creates a program called “sys.cold_backup”, the type of the program is an external executable script located in /home/oracle/app/oracle/product/11.2.0/db_1/scripts/coldbackup.sh path. Inside the script we set the value “detached” as the ATTRIBUTE of the program previously created. Then, we create a scheduler job associated with the external program. the scheduler will run every day at 20H and 45 minutes. The job is created by default disabled, so we should enable it using the ENABLE procedure of the DBMS_SCHEDULER package.

The content of the scirpt is:

[oracle@wissem scripts]$ cat main.sql
SET SERVEROUTPUT ON
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
    program_name   => 'sys.cold_backup',
    program_type   => 'executable',
    program_action => '/home/oracle/app/oracle/product/11.2.0/db_1/scripts/coldbackup.sh',
    enabled        =>  TRUE);

  DBMS_SCHEDULER.SET_ATTRIBUTE('sys.cold_backup', 'detached', TRUE);

  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'sys.cold_backup_job',
    program_name    => 'sys.cold_backup',
    repeat_interval => 'FREQ=DAILY;BYHOUR=20;BYMINUTE=45');

DBMS_SCHEDULER.ENABLE('sys.cold_backup_job');

EXCEPTION WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE('Error main.sql '||SQLERRM);
END;
/
exit;
[oracle@wissem scripts]$ 

3- main.sh script:

This is the main script that will call main.sql.

The content of the scirpt is:

[oracle@wissem scripts]$ cat main.sh
#!/bin/sh

source /home/oracle/.bash_profile

sqlplus sys/wissem as sysdba @/home/oracle/app/oracle/product/11.2.0/db_1/scripts/main.sql
exit;
[oracle@wissem scripts]$

Run the example:

We first run main.sh script. We will have the following output;

SQL> exec DBMS_SCHEDULER.STOP_JOB('SYS.COLD_BACKUP_JOB');

PL/SQL procedure successfully completed.

SQL> exec DBMS_SCHEDULER.DROP_JOB('sys.cold_backup_job');

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.drop_program('SYS.COLD_BACKUP');

PL/SQL procedure successfully completed.

SQL>   exit
[oracle@wissem scripts]$ ./main.sh 

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 20:40:16 2011
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
PL/SQL procedure successfully completed.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@wissem scripts]$

You can run the following query to see if the scheduler job was successfully created or not;

select * from dba_scheduler_jobs where JOB_NAME='COLD_BACKUP_JOB';

At 20H45, the detached job is running,
I queried the status of the database and it is in mount state;

SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL>

Also, using the command;

[wissem@wissem ~]$ ps -edf | grep rman
oracle   13195 11942  0 20:45 pts/0    00:00:01 rman target /
wissem   13264 13249  0 20:45 pts/1    00:00:00 grep --color=auto rman

Another method to see what is happening is to view the last few lines of the alert log file, using adrci utility like showing below;
(Contents from alert log file have been truncated to show only the necessary events)

[oracle@wissem backup]$ adrci

ADRCI: Release 11.2.0.1.0 - Production on Mon May 30 20:06:20 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/home/oracle/app/oracle"
adrci> show alert

Choose the alert log from the following homes to view:

1: diag/rdbms/orawiss/ORAWISS
2: diag/tnslsnr/meka/listener
3: diag/tnslsnr/wissem/istener_orawiss
4: diag/tnslsnr/wissem/listener
5: diag/tnslsnr/wissem/listener_orawiss
Q: to quit

Please select option: 1

...............

Successful mount of redo thread 1, with mount id 3190464559
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount
2011-05-30 20:45:43.989000 +02:00
alter system set encryption wallet open identified by *
Completed Successfully
   alter system set encryption wallet open identified by *
2011-05-30 20:47:08.279000 +02:00
alter database open
...............

Finally, you can list the content of the flash recovery area and you will find the backup files;

[oracle@wissem scripts]$ ls /home/oracle/backups/fra/ORAWISS/backupset/2011_05_30/
o1_mf_annnn_TAG20110530T204544_6y7sgrox_.bkp  o1_mf_nnndf_TAG20110530T204548_6y7sgwxm_.bkp
o1_mf_annnn_TAG20110530T204544_6y7sgrt6_.bkp  o1_mf_nnndf_TAG20110530T204548_6y7sgxgm_.bkp
[oracle@wissem scripts]$

Hope it helps,

Wissem