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 12

“Now, I say to you today my friends, even though we face the difficulties of today and tomorrow, I still have a dream. It is a dream deeply rooted in the American dream. I have a dream that one day this nation will rise up and live out the true meaning of its creed: - 'We hold these truths to be self-evident, that all men are created equal.' “ Martin Luther King Jr. (1929 - 1968), Speech at Civil Rights March on Washington, August 28, 1963

 

Automated Oracle HOT or ONLINE backup

Steps to create an automated Oracle HOT or Online backup to your hard disk or SAN:

 

Create a folder

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

Example: $cd /u07

$mkdir hotbackup

$chown -R oracle:dba hotbackup

$chmod -R 770 coldbackup

 

UTL_FILE_DIR parameter

2- Add the following line to the database parameter.

Example: utl_file_dir=/u07/hotbackup

Grant Privileges

3- Grant select privileges to the user who wants to run this

backup process directly, instead of using roles. 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 procedure to create a script to perform the actual HOT or ONLINE backup.

CREATE OR REPLACE PROCEDURE script4hotbackup

      (p_filepath IN VARCHAR2,

      p_orahome IN VARCHAR2,

      p_sid IN VARCHAR2)

IS

 

      -- Get the tablespace name

      cursor c_tablespace is

            select distinct tablespace_name from dba_data_files;

 

      -- Get the datafiles

      cursor c_filename (p_tablespace VARCHAR2) is

            select file_name from dba_data_files

            where tablespace_name = p_tablespace;

 

      output_file UTL_FILE.FILE_TYPE;

      v_msg VARCHAR2(100);

      v_path VARCHAR2(100);

      v_arch VARCHAR2(100) := '/u01/app/oracle/admin/<sid>/arch';

 

BEGIN

      v_path := p_filepath;

      output_file := UTL_FILE.FOPEN(v_path,'script4coldbackup.sh','w');

 

      -- preparation for shutdown database...

      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;');

 

      -- Process Online backup for each tablespace.

      FOR this IN c_tablespace LOOP

            UTL_FILE.PUTF(output_file,'ALTER TABLESPACE %s BEGIN BACKUP;',

                  this.tablespace_name);

 

            -- process the datafiles in each tablespace

            FOR v_datafile IN c_filename (this.tablespace_name) LOOP

                  UTL_FILE.PUTF(output_file,'host cp %s %s;',v_datafile.file_name,v_path);

            END LOOP;

 

            UTL_FILE.PUTF(output_file,'ALTER TABLESPACE %s END BACKUP;',

                  this.tablespace_name);

 

      END LOOP;

 

      -- Process ONLINE backup for the controlfiles

      UTL_FILE.PUTF(output_file,

            'ALTER DATABASE BACKUP CONTROLFILE TO %s/controlbkup.ctl;',v_path);

 

      -- Swith the online redo log file.

      UTL_FILE.PUTF(output_file,'ALTER SYSTEM SWITCH LOGFILE;');

 

      -- Copy all archive log files...

      UTL_FILE.PUTF(output_file,'host cp %s/arc*.log %s',v_arch,v_path);

 

      -- Remove all copied archive log files...

      UTL_FILE.PUTF(output_file,'host rm %s/arc*.log',v_arch);

      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 script4hotbackup;

/

 

Write a Script

5- Write a UNIX script to create the "script4hotbackup.sh" script and then run it in the hotbackup directory. Call it RunHotBackup.sh.

#!/bin/sh

ORACLE_SID=<sid>

export ORACLE_SID

 

BACKUP_HOME=/u07/hotbackup

export BACKUP_HOME

 

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

export ORACLE_HOME

 

ORACLE_BASE=/u01/app/oracle

export ORACLE_BASE

 

$ORACLE_HOME/bin/sqlplus /nolog << EOF

connect / as sysdba

 

DECLARE

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

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

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

 

BEGIN

      script4hotbackup(v_bckhome,v_orahome,v_sid);

END;

/

exit

EOF

 

chmod 770 $BACKUP_HOME/script4hotbackup.sh

 

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/.

 

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

 

Schedule to run the HOT backup

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

 

“A strong positive mental attitude will create more miracles than any wonder drug.” Patricia Neal

 

Questions:

Q: What are the steps to create an automated Oracle HOT backup?

Q: Write a procedure to create a script to perform the actual HOT or ONLINE backup.

Q: Write a UNIX script to create the "script4hotbackup.sh" script and then run it in the hotbackup directory. Call it RunHotBackup.sh.