Contributed by Georger Araujo
In order to correctly make a hot backup of an Oracle database, these steps must be followed:
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:
Here go our directories. Change these as appropriate:
And these will be the scripts (they're all in /app/oracle/oradata/db1/scripts):
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.