|
How
can I perform an automatic COLD or OFFLINE Oracle backup without using any backup utility?
|
More Resources by
Google: |
|
|
|
|
By: John Kazerooni
Steps to perform an automated Oracle COLD backup to your disk or SAN.
1- Create a directory that you want your coldbackup files to be
stored.
Example: $cd /u07
$mkdir coldbackup
$chmod -R 770 coldbackup
2- Add the following line to the database parameter.
example: utl_file_dir=/u07/coldbackup
3- Grant select privileges to the user who wants to run this
backup process directly, instead of using roles. If the onwer
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;
4- Write a stored procedure, that will create a UNIX script, that
then, perform 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 \n',
'#!/bin/sh');
UTL_FILE.PUTF(output_file,
'ORACLE_SID=%s \n',p_sid);
UTL_FILE.PUTF(output_file,'%s \n',
'export ORACLE_SID');
UTL_FILE.PUTF(output_file,
'ORACLE_HOME=%s \n',p_orahome);
UTL_FILE.PUTF(output_file,'%s \n',
'export ORACLE_SID');
UTL_FILE.PUTF(output_file,'%s \n',
'$ORACLE_HOME/bin/sqlplus /nolog << EOF');
UTL_FILE.PUTF(output_file,'%s \n',
'CONNECT internal/');
UTL_FILE.PUTF(output_file,'%s %s/coldbackup.log \n',
'spool',v_path);
UTL_FILE.PUTF(output_file,'%s \n',
'ALTER DATABASE BACKUP CONTROLFILE TO TRACE;');
UTL_FILE.PUTF(output_file,'%s \n',
'SHUTDOWN IMMEDIATE;');
-- process the datafiles
FOR this IN c_filename LOOP
UTL_FILE.PUTF(output_file,'host cp %s %s\n',
this.name,
v_path);
END LOOP;
-- process the controlfiles
FOR this IN c_controlfile LOOP
UTL_FILE.PUTF(output_file,'host cp %s %s\n',
this.name,
v_path);
END LOOP;
-- process the logfiles
FOR this IN c_logfile LOOP
UTL_FILE.PUTF(output_file,'host cp %s %s\n',
this.member,
v_path);
END LOOP;
-- preparation for startup the database...
UTL_FILE.PUTF(output_file,'%s \n',
'CONNECT internal/');
UTL_FILE.PUTF(output_file,'%s \n',
'STARTUP;');
|