Skip navigation

 

 

1. About

 

The following demonstrates several ways how to run Oracle database tasks using cron, for example, to use sqlplus or rman.

 

OS: Oracle Linux

 

2. Configuring the Oracle Environment

 

Keep in mind that cron does not perform a user login and will not process the user login profile, such as .profile or .bash_profile, or execute .bashrc for interactive sessions.

 

It is necessary to define the Oracle command shell environment to run any Oracle Database applications. You can either define required environment variables by hand or use oraenv provided by Oracle. The oraenv script is typically located in /usr/local/bin unless specified otherwise.

 

You can define the Oracle database shell environment in a crontab file or shell script in several ways. Method 1 or 2 are probably the safest, but there is no real advantage one way or the other.

 

 

2.1 Method 1

 

#/bin/bash

ORACLE_SID=orcl  # your Oracle SID

ORAENV_ASK=NO

grep -wo "$ORACLE_SID" /etc/oratab >/dev/null \

&& { source /usr/local/bin/oraenv >/dev/null

     export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; } \

|| { echo "ERROR: Unknown Oracle SID $ORACLE_SID"; }

 

 

2.2 Method 2

 

#/bin/bash

ORACLE_SID=$(grep -wo orcl /etc/oratab) # your Oracle SID

if [ "$ORACLE_SID" ]; then

  . /usr/local/bin/oraenv <<<${ORACLE_SID} >\dev\null

  export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

else

  echo "ERROR: Unknown Oracle SID $ORACLE_SID"

fi

 

 

2.3 Method 3

 

#/bin/bash

ORACLE_SID=orcl # your Oracle SID

if [ "$(grep -wo $ORACLE_SID /etc/oratab)" ]; then

  export ORACLE_SID

  export ORACLE_BASE=/u01/app/oracle

  export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1

  export LD_LIBRARY_PATH=$ORACLE_HOME/lib

  export PATH=$ORACLE_HOME/bin:$PATH

  export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

else

  echo "ERROR: Unknown Oracle SID $ORACLE_SID"

fi

 

Note:

 

Any script that defines Oracle environment variables, as well as oraenv, needs to be source executed, so it applies to the current shell process. You can source execute a script using source or [dot space].

 

 

 

3. Scripting RMAN and SQLPLUS Examples

 

 

The below uses a simple EOI as the "here document" marker. This allows to use shell variables, e.g.: $spoolfile, but $ signs in SQL need to be escaped with a forward slash.

#/bin/bash

 

# This procedure uses a temporary SQL spoolfile.

spoolfile=/tmp/orafra_$(date +%N).tmp

sqlplus -s /nolog 1>/dev/null 2>&1 <<EOI

connect system/oracle_4U

set pages 0 feed off

spool ${spoolfile}

select * from v\$recovery_file_dest;

EOI

 

# Get the data and convert free_space to human readable.

values=$( cat ${spoolfile} )

values=$( echo $values ) # convert CR and blanks to blank.

fra_dir=$( cut -d' ' -f1 <<<${values} )

free_space=$( cut -d' ' -f3 <<<${values} )

free_space=$( numfmt --from=si --to=iec $free_space )

 

# Mail a message

mail_msg="Free space in $fra_dir: $free_space"

echo $mail_msg

mailx -s "FRA free" root@localhost <<<${mail_msg}

 

The next example uses "EOI" between double quotes, which does not require to escape $ signs in SQL, however, it won't translate shell variables. The example also redirects all sqlplus output into variable dump_loc.

#/bin/bash

 

 

dump_loc=$( sqlplus -s /nolog <<"EOI"

connect system/oracle_4U

set pages 0 feed off

select value from v$parameter

where name='background_dump_dest';

EOI

)

echo $dump_loc

 

Executing a simply command, using sqlplus -S to suppress the SQL banner, etc.

#/bin/bash

 

echo "shutdown immediate" | sqlplus -S / as sysdba

 

An alternative to using "here document" marker by creating a variable.

#/bin/bash

 

cmd="

backup incremental level 1 database plus archivelog delete input;

delete noprompt obsolete;

"

rman target / <<<${cmd}

1. About

 

The following demonstrates how to enable users tom and bob to modify cron as user oracle.

 

OS: Oracle Linux 7

 

2. Example

 

Login as root:

 

[root@localhost ~]# groupadd oracron

[root@localhost ~]# adduser oracle

 

[root@localhost /]# visudo

 

Insert the following (press i):

 

## Allow members of the oracron group to execute crontab -e as user oracle

%oracron ALL=(oracle) NOPASSWD: crontab -e

 

Save and exit (type :wq)

 

Add user tom and bob to the oracron group.

 

[root@localhost ~]# usermod -a -G oracron tom

[root@localhost ~]# usermod -a -G oracron bob

 

Login as user tom or bob and execute the following respectively:

 

[tom@localhost ~]$ sudo -u oracle crontab -e

[bob@localhost ~]$ sudo -u oracle crontab -e

 

 

 

This document is the result of my own research and testing and was written in the hope to provide useful information for members of the Oracle community. It does not substitute any documentation provided by Oracle. Use at your own risk.

 

The information herein is provided under the terms and conditions of http://www.oracle.com/us/legal/terms/index.html. Please do not plagiarize.

 

1. About

 

The following demonstrates how to use RMAN to restore database ORCL112 using a different file directory structure.

 

System: Oracle Linux 7.3 (VM VirtualBox), Oracle Database 11.2.0.1

 

 

 

This document is the result of my private research and was written in the hope to provide useful information for members of the Oracle community. It is not meant to be a reference or to substitute any documentation provided by Oracle. Use on your own risk.

 

The information herein is provided under the terms and conditions of http://www.oracle.com/us/legal/terms/index.html.

Please do not plagiarize.

 

Copyright 2018, Dude! @ Oracle Community

 

 

2. Example

 

2.1 Create a backup of the database


Set the Oracle environment:

 

[ oracle@701 ~]$ . oraenv > /dev/null <<< ORCL112

 

[oracle@701 ~]$ rman target /
connected to target database: ORCL112 (DBID=3497253474)

 

Note the DBID!

 

Configure Controlfile Autobackup ON:

 

RMAN> configure controlfile autobackup on;

 

RMAN> backup database plus archivelog delete input;

 

If you restoring on a different server, verify the following:

 

The server has access to the RMAN backups, for example: /u03/fast_recovery_area.

$ORACLE_HOME/dbs/orapwORCL112 password file has been copied.

/etc/oratab includes an entry for ORCL112.

 

2.2 Restore the spfile from autobackup

 

oracle@702 ~]$ . oraenv > /dev/null <<< ORCL112

 

[oracle@702 ~]$ cd $ORACLE_HOME/dbs

 

Note: If you are restoring on the same server, shutdown ORCL112 and delete the init or spfile first.

 

[oracle@702 dbs]$ rman target /
connected to target database (not started)

RMAN> set DBID 3497253474  
RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
starting Oracle instance without parameter file for retrieval of spfile

RMAN> restore spfile from autobackup
      db_recovery_file_dest='/u03/orafra' db_name='ORCL112'; 

recovery area destination: /u03/orafra
database name (or database unique name) used for search: ORCL112
channel ORA_DISK_1: AUTOBACKUP /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985266639_fr7q1j2m_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180827
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985266639_fr7q1j2m_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 27-AUG-18



2.3 Create a pfile and modify the location of the control files

 

[oracle@702 dbs]$ echo "create pfile from spfile;" | sqlplus / as sysdba

 

Edit initORCL112.ora and modfiy location of controlfiles. E.g. /tmp/control01.ctl

 

2.4 Create a spfile and restore the control files

 

[oracle@702 dbs]$ echo "create spfile from pfile;" | sqlplus / as sysdba

 

[oracle@702 dbs]$ rman target /

RMAN> startup force nomount
(load new spfile)

RMAN> restore controlfile to '/tmp/control01.ctl' from autobackup;

recovery area destination: /u03/orafra
database name (or database unique name) used for search: ORCL112
channel ORA_DISK_1: AUTOBACKUP /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985265630_fr7p1z55_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180827
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985265630_fr7p1z55_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
Finished restore at 27-AUG-18

Restore the second controlfile, as defined in init parameter file:

RMAN> restore controlfile to '/tmp/control02.ctl' from autobackup;

RMAN> startup force mount  

 

You may have to reset the incarnation, in case the restore may not find any backups:

 

For example: RMAN> reset database incarnation to 4;

 

RMAN> List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1       1       ORCL112  3497253474       PARENT  1          24-AUG-13

2       2       ORCL112  3497253474       PARENT  925702     14-FEB-17

3       3       ORCL112  3497253474       PARENT  2041934    25-JUL-17

4       4       ORCL112  3497253474       CURRENT 2402794    27-AUG-18

 

 

2.5 Retrieve all database file names, including redo log and temp files:

 

[ oracle@702 dbs]$ sqlplus / as sysdba <<'EOI'
COLUMN NAME FORMAT a60
SELECT FILE#, NAME FROM V$DATAFILE
UNION
SELECT GROUP#, MEMBER FROM V$LOGFILE
UNION
SELECT FILE#, NAME FROM V$TEMPFILE;
EOI


     1 /u02/oradata/orcl112/redo01.log
     1 /u02/oradata/orcl112/system01.dbf
     1 /u02/oradata/orcl112/temp01.dbf
     2 /u02/oradata/orcl112/redo02.log
     2 /u02/oradata/orcl112/sysaux01.dbf
     3 /u02/oradata/orcl112/redo03.log
     3 /u02/oradata/orcl112/undotbs01.dbf
     4 /u02/oradata/orcl112/users01.dbf
     5 /u02/oradata/orcl112/example01.dbf

 

 

2.6 Restore the database

 

[oracle@702 dbs]$ rman target /

 

Find the lowest SCN in the archivelog backups to avoid recovering using the online redo

logs, which do not exist on the new server.

 

Note: You can probably also use RMAN "RECOVER NOREDO" instead of "UNTIL SCN". This

           performs a recovery using archivelogs, but will not look for redo logs.

 

 

RMAN> list backup of archivelog all;

  List of Archived Logs in backup set 64
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    2       2403184    27-AUG-18 2403207    27-AUG-18


RMAN> run {
      ALLOCATE CHANNEL ch1 DEVICE TYPE disk;
      set until scn 2403184;
      SET NEWNAME FOR DATAFILE 1 TO '/tmp/system01.dbf';
      SET NEWNAME FOR DATAFILE 2 TO '/tmp/sysaux01.dbf';
      SET NEWNAME FOR DATAFILE 3 TO '/tmp/undotbs01.dbf';
      SET NEWNAME FOR DATAFILE 4 TO '/tmp/users01.dbf';
      SET NEWNAME FOR DATAFILE 5 TO '/tmp/example01.dbf';
      SET NEWNAME FOR TEMPFILE 1 TO '/tmp/temp01.dbf';
      sql "ALTER DATABASE RENAME FILE ''/u02/oradata/orcl112/redo01.log'' 
                                   TO ''/tmp/redo01.log'' ";
      sql "ALTER DATABASE RENAME FILE ''/u02/oradata/orcl112/redo02.log''
                                   TO ''/tmp/redo02.log'' ";
      sql "ALTER DATABASE RENAME FILE ''/u02/oradata/orcl112/redo03.log''
                                   TO ''/tmp/redo03.log'' ";
      restore database;
      switch datafile all;
      switch tempfile all;
      recover database;
}

allocated channel: ch1
channel ch1: SID=133 device type=DISK

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

sql statement: ALTER DATABASE RENAME FILE ''/u02/oradata/orcl112/redo01.log''                                    TO ''/tmp/redo01.log'' 

sql statement: ALTER DATABASE RENAME FILE ''/u02/oradata/orcl112/redo02.log''                                   TO ''/tmp/redo02.log'' 

sql statement: ALTER DATABASE RENAME FILE ''/u02/oradata/orcl112/redo03.log''                                   TO ''/tmp/redo03.log'' 

Starting restore at 27-AUG-18
Starting implicit crosscheck backup at 27-AUG-18
Crosschecked 8 objects
Finished implicit crosscheck backup at 27-AUG-18

Starting implicit crosscheck copy at 27-AUG-18
Finished implicit crosscheck copy at 27-AUG-18

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985265630_fr7p1z55_.bkp
File Name: /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985266639_fr7q1j2m_.bkp


channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to /tmp/system01.dbf
channel ch1: restoring datafile 00002 to /tmp/sysaux01.dbf
channel ch1: restoring datafile 00003 to /tmp/undotbs01.dbf
channel ch1: restoring datafile 00004 to /tmp/users01.dbf
channel ch1: restoring datafile 00005 to /tmp/example01.dbf
channel ch1: reading from backup piece /u03/orafra/ORCL112/backupset/2018_08_27/o1_mf_nnndf_TAG20180827T125255_fr7p07m7_.bkp
channel ch1: piece handle=/u03/orafra/ORCL112/backupset/2018_08_27/o1_mf_nnndf_TAG20180827T125255_fr7p07m7_.bkp tag=TAG20180827T125255
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:01:15
Finished restore at 27-AUG-18

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=985267288 file name=/tmp/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=985267288 file name=/tmp/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=985267288 file name=/tmp/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=985267288 file name=/tmp/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=985267288 file name=/tmp/example01.dbf

renamed tempfile 1 to /tmp/temp01.dbf in control file

Starting recover at 27-AUG-18

starting media recovery

channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
archived log thread=1 sequence=34
channel ch1: reading from backup piece /u03/orafra/ORCL112/backupset/2018_08_27/o1_mf_annnn_TAG20180827T131010_fr7q0lcz_.bkp
channel ch1: piece handle=/u03/orafra/ORCL112/backupset/2018_08_27/o1_mf_annnn_TAG20180827T131010_fr7q0lcz_.bkp tag=TAG20180827T131010
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
archived log file name=/u03/orafra/ORCL112/archivelog/2018_08_27/o1_mf_1_34_fr7qotql_.arc thread=1 sequence=34
channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
archived log thread=1 sequence=1
channel ch1: reading from backup piece /u03/orafra/ORCL112/backupset/2018_08_27/o1_mf_annnn_TAG20180827T131010_fr7q0mnx_.bkp
channel ch1: piece handle=/u03/orafra/ORCL112/backupset/2018_08_27/o1_mf_annnn_TAG20180827T131010_fr7q0mnx_.bkp tag=TAG20180827T131010
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
archived log file name=/u03/orafra/ORCL112/archivelog/2018_08_27/o1_mf_1_1_fr7qow9l_.arc thread=1 sequence=1
channel default: deleting archived log(s)
archived log file name=/u03/orafra/ORCL112/archivelog/2018_08_27/o1_mf_1_34_fr7qotql_.arc RECID=95 STAMP=985267290
channel default: deleting archived log(s)
archived log file name=/u03/orafra/ORCL112/archivelog/2018_08_27/o1_mf_1_1_fr7qow9l_.arc RECID=96 STAMP=985267292
media recovery complete, elapsed time: 00:00:00
Finished recover at 27-AUG-18
released channel: ch1

 

2.7 Open the database and verify the result

 

RMAN> alter database open resetlogs;

database opened

sqlplus / as sysdba <<'EOI'
COLUMN NAME FORMAT a60
SELECT FILE#, NAME FROM V$DATAFILE
UNION
SELECT GROUP#, MEMBER FROM V$LOGFILE
UNION
SELECT FILE#, NAME FROM V$TEMPFILE;
EOI

     1 /tmp/redo01.log
     1 /tmp/system01.dbf
     1 /tmp/temp01.dbf
     2 /tmp/redo02.log
     2 /tmp/sysaux01.dbf
     3 /tmp/redo03.log
     3 /tmp/undotbs01.dbf
     4 /tmp/users01.dbf
     5 /tmp/example01.dbf

 

 

Ref: https://docs.oracle.com/cd/E18283_01/backup.112/e10642/rcmdupad.htm#CIHFIJJH