iSelfSchooling - Automated Oracle COLD or OFFLINE backup

iSelfSchooling.com Since 1999 References | Search more | Oracle Syntax | Free Online Oracle Training

Home .Services Login Start Learning Certification . .Share your BELIEF(s)...

. Online Accounting     .Copyright & User Agreement   |

    .Vision      .Biography     .Acknowledgement
.Contact Us      .Comments/Suggestions       Email2aFriend    |

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals II

Lesson 11

"Sometimes people carry to such perfection the mask they have assumed that in due course they actually become the person they seem." W. Somerset Maugham (1874 - 1965), The Moon and Sixpence

Automated Oracle COLD or OFFLINE backup

Steps to perform an automated Oracle COLD backup to your disk or SAN.

Create a folder

1- Create a directory that you want your coldbackup files to be stored.

Example: $cd /u07

$mkdir coldbackup

$chmod -R 770 coldbackup

UTL_FILE_DIR parameter

2- Add the following line to the database parameter. This parameter gives permission to Oracle to write or read from defined directory.

Example: utl_file_dir=/u07/coldbackup

Grant Privileges

3- Grant select privileges to the user who wants to run this backup process directly, instead of using roles. Notice that if the owner is SYS ignore this step.

Example:

GRANT SELECT ON v_$datafile TO xxx;

GRANT SELECT ON v_$logfile TO xxx;

GRANT SELECT ON v_$controlfile TO xxx;

Write a procedure

4- Write a stored procedure that will create a UNIX script, and then performs your COLD backup.

CREATE OR REPLACE PROCEDURE script4coldbackup

      (p_filepath IN VARCHAR2,

      p_orahome IN VARCHAR2,

      p_sid IN VARCHAR2)

IS

      -- Get the datafiles

      cursor c_filename is

            select name from v$datafile;

      -- Get the controlfiles

      cursor c_controlfile is

            select name from v$controlfile;

      -- Get the logfiles

      cursor c_logfile is

            select member from v$logfile;

      -- Define variables

      output_file UTL_FILE.FILE_TYPE;

      v_msg VARCHAR2(100);

      v_path VARCHAR2(100);

BEGIN

      -- set the variables

      v_path := p_filepath;

      output_file := UTL_FILE.FOPEN(v_path,

                        'script4coldbackup.sh','w');

      -- preparation for shutdown database...Your are write a script to backup

      UTL_FILE.PUTF(output_file,'%s ','#!/bin/sh');

      UTL_FILE.PUTF(output_file, 'ORACLE_SID=%s ',p_sid);

      UTL_FILE.PUTF(output_file,'%s ','export ORACLE_SID');

      UTL_FILE.PUTF(output_file,'ORACLE_HOME=%s ',p_orahome);

      UTL_FILE.PUTF(output_file,'%s ','export ORACLE_SID');

      UTL_FILE.PUTF(output_file,'%s ','$ORACLE_HOME/bin/sqlplus /nolog << EOF');

      UTL_FILE.PUTF(output_file,'%s ','CONNECT internal/');

      UTL_FILE.PUTF(output_file,'%s %s/coldbackup.log ','spool',v_path);

      UTL_FILE.PUTF(output_file,

                              '%s ','ALTER DATABASE BACKUP CONTROLFILE TO TRACE;');

      UTL_FILE.PUTF(output_file,'%s ','SHUTDOWN IMMEDIATE;');

      -- process the datafiles

      FOR this IN c_filename LOOP

            UTL_FILE.PUTF(output_file,'host cp %s %s',this.name,v_path);

      END LOOP;

      -- process the controlfiles

      FOR this IN c_controlfile LOOP

            UTL_FILE.PUTF(output_file,'host cp %s %s',this.name,v_path);

      END LOOP;

      -- process the logfiles

      FOR this IN c_logfile LOOP

            UTL_FILE.PUTF(output_file,'host cp %s %s',this.member,v_path);

      END LOOP;

      -- preparation for startup the database...

      UTL_FILE.PUTF(output_file,'%s ','CONNECT internal/');

      UTL_FILE.PUTF(output_file,'%s ','STARTUP;');

      UTL_FILE.PUTF(output_file,'%s ','exit');

      UTL_FILE.PUTF(output_file,'%s ','EOF');

      UTL_FILE.FCLOSE_ALL;

EXCEPTION

      WHEN UTL_FILE.INVALID_PATH THEN

            UTL_FILE.PUTF(output_file,'Invalid Path');

            UTL_FILE.FCLOSE(output_file);

      WHEN UTL_FILE.INVALID_MODE THEN

            UTL_FILE.PUTF(output_file,'Invalid Mode');

            UTL_FILE.FCLOSE(output_file);

      WHEN UTL_FILE.INVALID_OPERATION then

            UTL_FILE.PUTF(output_file,'Invalid Operation');

            UTL_FILE.FCLOSE(output_file);

      WHEN UTL_FILE.INVALID_FILEHANDLE then

            UTL_FILE.PUTF(output_file,'Invalid Filehandle');

            UTL_FILE.FCLOSE(output_file);

      WHEN UTL_FILE.WRITE_ERROR then

            UTL_FILE.PUTF(output_file,'Write Error');

            UTL_FILE.FCLOSE(output_file);

      WHEN UTL_FILE.READ_ERROR then

            UTL_FILE.PUTF(output_file,'Read Error');

            UTL_FILE.FCLOSE(output_file);

      WHEN UTL_FILE.INTERNAL_ERROR then

            UTL_FILE.PUTF(output_file,'Internal Error');

            UTL_FILE.FCLOSE(output_file);

      WHEN OTHERS THEN

            UTL_FILE.PUTF(output_file,'others');

            UTL_FILE.FCLOSE(output_file);

END script4coldbackup;

/

Write a Script

5- Write a UNIX script to create the "script4coldbackup.sh" script and then run it in the coldbackup directory.

Call it RunColdBackup.sh.

You should know UNIX shell script to understand this part.

#!/bin/sh

ORACLE_SID=your-sid

export ORACLE_SID

BACKUP_HOME=/u07/coldbackup

export BACKUP_HOME

ORACLE_HOME=/u01/app/oracle/product/8.1.7

export ORACLE_HOME

ORACLE_BASE=/u01/app/oracle

export ORACLE_BASE

-- this will exacute PL/SQL command in SQLPLUS

$ORACLE_HOME/bin/sqlplus /nolog << EOF

connect / as sysdba

DECLARE

      v_bckhome VARCHAR2(100) := '/u07/coldbackup';

      v_orahome VARCHAR2(100) := '/u01/app/oracle/product/oracle-version';

      v_sid VARCHAR2(100) := 'your-sid';

BEGIN

      -- execute the PL/SQL procedure that you created.

      script4coldbackup(v_bckhome,v_orahome,v_sid);

END;

/

exit

EOF

-- change the file to executable file.

chmod 770 $BACKUP_HOME/script4coldbackup.sh

--backup pfile, tnsname.ora, listener.ora, etc.

cp $ORACLE_HOME/network/admin/tnsnames.ora $BACKUP_HOME/.

cp $ORACLE_HOME/network/admin/listener.ora $BACKUP_HOME/.

cp $ORACLE_HOME/network/admin/sqlnet.ora $BACKUP_HOME/.

cp $ORACLE_BASE/admin/trav/pfile/inittrav.ora $BACKUP_HOME/.

--execute the shell script you create before.

$BACKUP_HOME/script4coldbackup.sh (your-path, your-oracle-home, sid)

Schedule to run COLD Backup

6- Schedule this job to run as an oracle user in the crontab file.

"Life is full of misery, loneliness, and suffering - and it's all over much too soon." Woody Allen (1935 - )

Questions:

Q: What are the steps to perform an automated Oracle COLD backup?

Q: What does the UTL_FILE_DIR parameter?

Q: What does the following SQL statements do?

GRANT SELECT ON v_$datafile TO xxx;

GRANT SELECT ON v_$logfile TO xxx;

GRANT SELECT ON v_$controlfile TO xxx;

Q: Write a stored procedure that will create a UNIX script, and then performs your COLD backup.

Q: Write a UNIX or DOS script to create the "script4coldbackup.sh" script and then run it in the coldbackup directory. Call it RunColdBackup.sh.