A Comprehensive Website for Oracle Learning

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;

output_file UTL_FILE.FILE_TYPE;

v_msg VARCHAR2(100);

v_path VARCHAR2(100);

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

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.

#!/bin/sh

ORACLE_SID=trav

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

$ORACLE_HOME/bin/sqlplus /nolog << EOF

connect INTERNAL/

DECLARE

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

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

v_sid VARCHAR2(100) := 'trav';

BEGIN

script4coldbackup(v_bckhome,v_orahome,v_sid);

END;

/

exit

EOF

chmod 770 $BACKUP_HOME/script4coldbackup.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/script4coldbackup.sh

Schedule to run COLD Backup

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

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