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

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. 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 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 | Lesson 27 | Lesson 28 | Lesson 29 |

 

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.