Amazon Contextual Product Ads

Sunday, 15 May 2011

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)

No comments:

Post a Comment