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;
/
#!/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