Creating a backup for Oracle database

I decided to write a simple script to backup the database while it is still running

This mean that I need to backup:

• control files
• datafiles
• logfiles
• startup files (spfile)

Note!! Before you execute this script make sure that your database is in Archive mode

First of all lets create a directories for the files

echo "Created By Michael Shapira"
rm files.txt 1> /dev/null 2> /dev/null
mkdir datafiles
mkdir contfiles
mkdir logfiles
mkdir startupfiles

”files.txt” will hold the commands to be executed 

echo "cp $ORACLE_HOME/dbs/spfileorcl2.ora startupfiles" >> files.txt

copy spfile to startup directory

set serveroutput on

spool files.txt

select 'cp ' || name || ' contfiles'
  from v $controlfile /
       SELECT 'cp ' || member || ' logfiles'
         FROM V $LOGFILE / DECLARE cursor c_tablespace is
              select tablespace_name name
                FROM dba_tablespaces
               where contents <> 'TEMPORARY';


cursor c_tableFiles(p_space_name VARCHAR2) is(
 select file_name, tablespace_name
   from dba_data_files
  where tablespace_name = p_space_name);
BEGIN
 dbms_output.put_line('sqlplus  "/as sysdba" << EOF');
 for r_names in c_tablespace loop
  begin
   dbms_output.put_line('alter tablespace ' || r_names.name ||
                        ' begin backup;');
   for r in c_tableFiles(r_names.name) loop
    dbms_output.put_line('! cp ' || r.file_name || ' datafiles');
   end loop;
   dbms_output.put_line('alter tablespace ' || r_names.name ||
                        ' end backup;');
  exception
   when OTHERS then
    null;
  end;
 end loop;
 dbms_output.put_line('exit');
 dbms_output.put_line('/');
 dbms_output.put_line('EOF');
end;
/ 
spool off
exit

Spool copy command of control and logfiles to “files.txt”. Next loop existing tablespaces (but not temporary ) alter them to begin backup. Copy relevant datafile to datafiles directory and stop backup on this tablespace

FILE=files.txtcat $FILE | grep -v '^[ ]*\\n\\'  > $FILE.tmpcat $FILE.tmp | sed -e 's/^[ \t]*//' | sed '/^$/d' > $FILE. $FILE

remove empty lines and align all code to the left in the files.txt. Execute set of commands in “files.txt”. This will actually do all work

jar -Mcvf db_backup.zip contfiles logfiles startupfiles datafilesrm -rf  $FILE* contfiles logfiles startupfiles datafiles

zip all directories and delete temporary files

That is all. Attached complete source and example of files.txt

 

files.txt (1.28 kb)

backup_db.sh (1.71 kb)