Oracle 10g Release 2
Contributed by Georger Araujo
In order to correctly make a hot backup of an Oracle database, these steps must be followed:
- Put each tablespace in backup mode
- Back up its datafiles
- Put tablespace out of backup mode
- Back up the control file
- Manually archive redo logs
- Back up redo logs
After this, we'll also delete the archived redo logs after we back them up to save disk space. The database should be, of course, running in ARCHIVELOG mode. Parameter log_archive_format should be '%t_%s_%r.arc' - be careful because the default is '%t_%s_%r.dbf'! I changed this on my instance because datafiles are also .dbf and we are going to delete archived redo logs with the copy_remove_archives.sh script, and we don't want to delete datafiles by mistake. If you can't/don't want to change your parameter, edit copy_remove_archives.sh accordingly.
The strategy described here for backing up an Oracle 10g Release 2 with Bacula does this:
- Back up database and archived redo logs to local directory using a PL/SQL script
- Back up the local directory to tape using Bacula
- Delete files in local directory to save disk space
Here go our directories. Change these as appropriate:
- /app/oracle/backup → Local directory that the PL/SQL script will back up to
- /app/oracle/oradata/db1 → Directory that contains the datafiles
- /app/oracle/oradata/db1/archives → Directory that contains the archived redo logs
- /app/oracle/oradata/db1/scripts → Directory that contains the scripts we are going to use
And these will be the scripts (they're all in /app/oracle/oradata/db1/scripts):
- runhotbackup.sql → PL/SQL script that backs up the database
- copy_remove_archives.sh → Copies archived redo logs created up to the time of the backup to backup directory and deletes them afterwards
- recoverhot.sql → Recovers the database after a restore
- runbeforebackup.sh → Runs PL/SQL script that backs up the database
- runafterbackup.sh → Deletes files from backup directory after Bacula copied them to tape
We'll assume users bacula and oracle exist in the system. They will be required to run some commands, and this can be achieved with the following /etc/sudoers files:
# sudoers file. # # This file MUST be edited with the 'visudo' command as root. # # See the sudoers man page for the details on how to write a sudoers file. # # Host alias specification Host_Alias LOCAL = localhost, DBSERVER, DBSERVER.example.com # User alias specification # Cmnd alias specification Cmnd_Alias BKPORA = /bin/su - oracle -c /app/oracle/oradata/db1/scripts/runbeforebackup.sh, /bin/su - oracle -c /app/oracle/oradata/db1/scripts/runafterbackup.sh Cmnd_Alias ETCORA = /etc/init.d/oracle start, /etc/init.d/oracle stop # Defaults specification # User privilege specification root ALL=(ALL) ALL # Uncomment to allow people in group wheel to run all commands # %wheel ALL=(ALL) ALL # Same thing without a password # %wheel ALL=(ALL) NOPASSWD: ALL # Samples # %users ALL=/sbin/mount /cdrom,/sbin/umount /cdrom # %users localhost=/sbin/shutdown -h now # Users bacula and oracle must be able to start up and shut down Oracle bacula, oracle LOCAL = NOPASSWD: ETCORA # User bacula must be able to run the commands specified # in the RunBeforeJob and RunAfterJob directives bacula LOCAL = NOPASSWD: BKPORA
Here goes runhotbackup.sql:
set serveroutput on
set feedback off
set linesize 5000
set trimspool off
spool /app/oracle/oradata/db1/scripts/hotbackupscript.sql
declare
cursor tbspc is
select tablespace_name from
dba_tablespaces
where contents <> 'TEMPORARY'
order by tablespace_name;
cursor datfil (tbspcname varchar2) is
select df.file_name from dba_data_files df, v$datafile d
where
df.tablespace_name = tbspcname
and
df.file_name = d.name
and
d.enabled like '%WRITE%'
order by df.tablespace_name;
tabcur tbspc%rowtype;
datcur datfil%rowtype;
sql_string Varchar2(500);
tsname1 varchar2(30);
tsname2 varchar2(30);
begin
dbms_output.enable(2000000);
dbms_output.put_line('host rm -f /app/oracle/backup/*');
open tbspc;
loop
fetch tbspc into tabcur;
exit when tbspc%notfound;
sql_string := 'ALTER TABLESPACE ' || tabcur.tablespace_name || ' BEGIN BACKUP;';
dbms_output.put_line(sql_string);
for datcur in datfil(tabcur.tablespace_name)
loop
sql_string := 'host rsync -vcW ' || datcur.file_name || ' /app/oracle/backup/';
dbms_output.put_line(sql_string);
end loop;
sql_string := 'ALTER TABLESPACE '|| tabcur.tablespace_name ||' END BACKUP;';
dbms_output.put_line(sql_string);
end loop;
close tbspc;
dbms_output.put_line('alter database backup controlfile to trace;');
dbms_output.put_line('alter database backup controlfile to ''/app/oracle/backup/' || to_char(sysdate,'yymmddhhmi') || '.ctl'';');
dbms_output.put_line('alter system archive log all;');
dbms_output.put_line('alter system archive log current;');
dbms_output.put_line('host /app/oracle/oradata/db1/scripts/copy_remove_archives.sh');
dbms_output.put_line(' ');
end;
/
spool off
@/app/oracle/oradata/db1/scripts/hotbackupscript.sql
host rm /app/oracle/oradata/db1/scripts/hotbackupscript.sql
exit
The copy_remove_archives.sh script that we reference near the end of runhotbackup.sql goes below:
#!/bin/sh
ARCHIVEDIR=/app/oracle/oradata/db1/archives
touch $ARCHIVEDIR/rm_older_than_me
find $ARCHIVEDIR/ -maxdepth 1 -name "*.arc" ! -newer $ARCHIVEDIR/rm_older_than_me -exec rsync -vcW {} /app/oracle/backup \; -print
find $ARCHIVEDIR/ -maxdepth 1 -name "*.arc" ! -newer $ARCHIVEDIR/rm_older_than_me -exec rm {} \; -print
rm $ARCHIVEDIR/rm_older_than_me
exit 0
The PL/SQL script will be called by runbeforebackup.sh:
#!/bin/sh sqlplus / as SYSDBA @/app/oracle/oradata/db1/scripts/runhotbackup.sql exit 0
OK, we have our database backed up to a local directory. Now we should back up /app/oracle/backup to tape. Your job's fileset must include this directory. Excerpt from bacula-dir.conf:
# Oracle Server
Job {
Name = "DBSERVER"
JobDefs = "OraDefault"
Schedule = "NIGHTLY10PMORACLE"
RunBeforeJob = "sudo /bin/su - oracle -c /app/oracle/oradata/db1/scripts/runbeforebackup.sh"
RunAfterJob = "sudo /bin/su - oracle -c /app/oracle/oradata/db1/scripts/runafterbackup.sh"
Write Bootstrap = "/var/bacula/DBSERVER.bsr"
}
Now that we have everything backed up, it's time to delete the contents of /app/oracle/backup to save disk space. Here goes runafterbackup.sh:
#!/bin/sh /bin/rm -f /app/oracle/backup/* exit 0
Should you ever need to recover your database (I hope it's not the case!), you should be able to do so with the recoverhot.sql script. Here are its contents:
Note however that the script quoted below is specific to the database of the original author of this page. It WILL NOT work for YOUR database without modification! You should look to the scripts created by the hotbackup script above, as part of the “alter database backup controlfile” commands. The below should be treated as an example only, as it could break your database beyond repair if your backups are not in order!
sqlplus / as SYSDBA
STARTUP NOMOUNT;
CREATE CONTROLFILE REUSE DATABASE "DB1" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
-- I use 64M on my instance, you have to choose a size that suits yours.
GROUP 1 '/app/oracle/oradata/db1/redo_g01_a.log' SIZE 64M,
GROUP 2 '/app/oracle/oradata/db1/redo_g02_a.log' SIZE 64M,
GROUP 3 '/app/oracle/oradata/db1/redo_g03_a.log' SIZE 64M
-- STANDBY LOGFILE
DATAFILE
'/app/oracle/oradata/db1/system01.dbf',
'/app/oracle/oradata/db1/undotbs01.dbf',
'/app/oracle/oradata/db1/sysaux01.dbf',
'/app/oracle/oradata/db1/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
-- You'll probably have to run these by hand after applying the archives.
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/db1/temp01.dbf'
SIZE 20M AUTOEXTEND ON NEXT 640K MAXSIZE 32767M;
ALTER SYSTEM SET LOG_ARCHIVE_START=TRUE SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/app/oracle/oradata/db1/archives' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
And that's it. Works rock solid on my server.
