The most import is to create the following oracle directory as sys user
1.sql> create or replace BACKUP_ORACLE_DIR
as ‘\path\to\directory’;
2. Decide the a destination for database’s datafiles
3. Database must be in archive log mode
4. The procedure has the followings inputs
Input 1 = ‘WIN’ for windows and ‘UNIX’ for unix platforms
Input 2 = destination for database’s datafiles
5. This backup strategy does not make backup any tablespace that its status is read only or offline as well as the temporary tablespace.
Use the sql>set serveroutput on; in order to catch any errors
exec createBackup('win','C:\backup');
/*
Michael Georgiou
Run the script as sysdba
*/
CREATE OR REPLACE PROCEDURE CreateBackup
(
os IN VARCHAR2,
copy_files_dest IN VARCHAR2
)
IS
BEGIN
DECLARE
directory_counter INTEGER :=0;
archive_counter INTEGER :=0;
os_command VARCHAR2(10):='cp';
counter INTEGER :=0;
--------------------------------
-- Which tablespaces ---------
CURSOR TS_NAME IS
SELECT tablespace_name
FROM dba_tablespaces
WHERE status NOT IN ('READ ONLY','OFFLINE')
AND CONTENTS <> 'TEMPORARY';
---------------------------------
-- Find tablespaces datafiles---
CURSOR DF_NAME(ts_nm VARCHAR2) IS
SELECT file_name
FROM dba_data_files
WHERE tablespace_name = ts_nm;
---------------------------------
file_handle Utl_File.file_type;
BEGIN
Dbms_Output.ENABLE(1000);
SELECT COUNT(*) INTO directory_counter
FROM dba_directories
WHERE directory_name='BACKUP_ORACLE_DIR';
SELECT COUNT(*) INTO archive_counter
FROM v$database
WHERE log_mode='ARCHIVELOG';
IF UPPER(os)='WIN' THEN
os_command := 'xcopy /y';
ELSE
os_command := 'cp';
END IF;
IF archive_counter = 1 THEN
counter :=1;
ELSE
Dbms_Output.put_line('ERROR:');
Dbms_Output.put_line('Media recovery is not enabled');
counter:=0;
END IF;
IF directory_counter = 1 THEN
counter:=1;
ELSE
Dbms_Output.put_line('ERROR:');
Dbms_Output.put_line('The Oracle Directory BACKUP_ORACLE_DIR does not exit');
counter:=0;
END IF;
IF counter=1 THEN
file_handle := Utl_File.fopen( 'BACKUP_ORACLE_DIR', 'HotBackup.sql', 'w');
FOR TS IN TS_NAME LOOP
file_handle := Utl_File.fopen('BACKUP_ORACLE_DIR', 'HotBackup.sql', 'a');
Utl_File.put_line(file_handle,'alter tablespace 'TS.tablespace_name' begin backup;');
Utl_File.fclose(file_handle);
FOR DF IN DF_NAME(TS.tablespace_name) LOOP
file_handle := Utl_File.fopen('BACKUP_ORACLE_DIR', 'HotBackup.sql', 'a');
Utl_File.put_line(file_handle,'HOST 'os_command' 'DF.file_name' 'copy_files_dest);
Utl_File.fclose(file_handle);
END LOOP;
file_handle := Utl_File.fopen('BACKUP_ORACLE_DIR', 'HotBackup.sql', 'a');
Utl_File.put_line(file_handle,'alter tablespace 'TS.tablespace_name' end backup;');
Utl_File.fclose(file_handle);
END LOOP;
file_handle := Utl_File.fopen('BACKUP_ORACLE_DIR', 'HotBackup.sql', 'a');
Utl_File.put_line(file_handle,'Alter system archive log current;');
Utl_File.put_line(file_handle,'exit');
Utl_File.fclose(file_handle);
END IF;
END;
END;
/
show errors;
