Scripts To Perform Dynamic Hot/Online Backups

Scripts To Perform Dynamic Hot/Online Backups [ID 152111.1]
		Modified 08-MAR-2011 Type BULLETIN Status PUBLISHED

   PURPOSE -------  For this article basis database knowledge is necessary.  This article will explain how you can create online backups and it contains 2  scripts which can be used to create you hotbackup's.  In this article the terms  hotbackup  and online backup will be used.  The script in the note will create another script which can be used to take the hot backup.   The topics explained are: 1) check archive log mode 2) change archive log mode 3) script hotbackup.bat + explanation 4) script hotbackup.sql + explanation 5) Scheduling   How to create online backups. -----------------------------  --- 1 CHECK ARCHIVE LOG MODE ---   To create online backups you database has to be in archivelog mode.  To check if this is the case execute the next query in sqlplus connecting as system        select log_mode from v$database;  If the the result is NOARCHIVELOG this must be changed, see topic 2. If the  result is ARCHIVELOG go to topic 3.   --- 2 CHANGE ARCHIVE LOG MODE ---  To change you settings from noarchivelog mode to archive log mode read note: 69739.1 This note explains in detail how to change from noarchive log to archivelog and visa versa.   --- 3 SCRIPT HOTBACKUP.BAT ---  The script will start with setting 4 parameters. These parameters must be changed to the values you would like to use.     parameter SCRIPTS    => location of the hotbackup.bat and hotbackup.sql    parameter ORACLE_SID => SID name of your database    parameter TEMP_DIR   => location of a temporary directory (example c:\temp)    parameter DEST_DIR   => location where the copys of the datafiles, controlfile will be created  After this the script will check for old logfiles, datafiles and will create the destination directory if it doesn't exists.  The next part of deleting or renaming old datafiles can be changed. In this script the renaming of old datafiles isn't used. But if you want to save a hotbackup of the day before remove the REM and added the  rem the next part.  Now the hotbackup.sql script is called and the parameters are also passed. For more info about this script see topic 4.  When the hotbackup.sql is executed, the log files (2 in total) will be merged together. When done the program will exit.     Rem **************************************************************************************** Rem Rem  Script to create hot backup of database Rem Rem  09-07-2001 B. de Cock Buning Rem  created script for hotbackup. Rem  11-07-2001 B. de Cock Buning Rem  changed script for dynamic locations (add parameters) Rem Rem  Set SCRIPTS=d:\oracle\admin Set ORACLE_SID=prod Set TEMP_DIR=c:\temp Set DEST_DIR=E:\oracle\backup  Rem Rem Delete old hotbackup.log file Rem if exist %TEMP_DIR%\hotbackup.log del %TEMP_DIR%\hotbackup.log /q if exist %DEST_DIR%\%ORACLE_SID%\Log\backup.log del %DEST_DIR%\%ORACLE_SID%\Log\backup.log  Rem Rem Create backup location   Rem if not exist %DEST_DIR%\%ORACLE_SID% md %DEST_DIR%\%ORACLE_SID% >> %TEMP_DIR%\hotbackup.log   Rem Rem Go to the log directory Rem if not exist %DEST_DIR%\%ORACLE_SID%\Log md %DEST_DIR%\%ORACLE_SID%\Log >> %TEMP_DIR%\hotbackup.log cd %DEST_DIR%\%ORACLE_SID%\Log cd   Rem Rem Delete old log files Rem if exist *.log del *.log /q >> %TEMP_DIR%\hotbackup.log if exist *.lst del *.lst /q >> %TEMP_DIR%\hotbackup.log  Rem Rem Rename the old backup files Rem Create a copy of the current backup files  Rem Rem if exist %DEST_DIR%\%ORACLE_SID%\*.ctl ren %DEST_DIR%\%ORACLE_SID%\*.ctl *.ctl.old >> %TEMP_DIR%\hotbackup.log Rem if exist %DEST_DIR%\%ORACLE_SID%\*.log ren %DEST_DIR%\%ORACLE_SID%\*.log *.log.old >> %TEMP_DIR%\hotbackup.log Rem if exist %DEST_DIR%\%ORACLE_SID%\*.dbf ren %DEST_DIR%\%ORACLE_SID%\*.dbf *.dbf.old >> %TEMP_DIR%\hotbackup.log  Rem Rem Delete the old backup files Rem Saves no copy of current backup Rem if exist %DEST_DIR%\%ORACLE_SID%\*.ctl del %DEST_DIR%\%ORACLE_SID%\*.ctl /q >> %TEMP_DIR%\hotbackup.log if exist %DEST_DIR%\%ORACLE_SID%\*.log del %DEST_DIR%\%ORACLE_SID%\*.log /q >> %TEMP_DIR%\hotbackup.log if exist %DEST_DIR%\%ORACLE_SID%\*.dbf del %DEST_DIR%\%ORACLE_SID%\*.dbf /q >> %TEMP_DIR%\hotbackup.log   Rem Rem Start the hot backup script Rem D:\Oracle\Oracle817\Bin\sqlplus "sys/oracle@%ORACLE_SID% as sysdba" @%SCRIPTS%\hotbackup.sql %ORACLE_SID% %TEMP_DIR% %DEST_DIR%  type %TEMP_DIR%\hotbackup.log >> %DEST_DIR%\%ORACLE_SID%\Log\backup.log  exit  Rem ******************************************************************************************************************************   Rem end of script    --- 4 SCRIPT HOTBACKUP.SQL ---  This script will create a spool file wich will be save in your temporary location. The spool file will be used to finally execute the statement for the backup. The spool file is filled with the result of the queries.   NOTE: when you have more then 2 controlfiles or the names are different then mentioned in the script (remark 3) change the names to the names you use or add a controlfile. This because when recovery is needed you will not have any problem with the naming used and the names define in your parameter file of the parameter control_files.   remark HOTBACKUP.sql remark remark Make a hot backup of the database. remark remark Parameters: 1 - SID for the database remark             2 - Temp for temporary directory  remark             3 - Destination for locatie of the backup remark    remark  remark 1- for all tablespaces which are not ACTIVE  remark      make tablespaces active remark 2- for all loggroups remark      logswitch, so all changes/ inserts in redologfiles are archiveerd   remark 3- for every  controlfile remark      make backup of the  controlfile remark 4- backup of the controlfile to trace directory remark 5- for every tablespace remark      set tablespace in backup mode remark      copy datafile to destination directory remark      set tablespace in active mode remark 6- show status of  tablespaces  remark remark  09-07-2001 B. de Cock Buning  remark  Create script for dynamic hotbackup remark   set pagesize 0 set feedback off set linesize 132 set trimspool on set verify off  define dbsid=&1 define temp =&2 define destination=&3  spool &temp\backup_prod.sql   select 'Rem Start tijd:' || to_char( sysdate, 'dd-mm-yyyy hh24:mi' ) from dual; prompt Spool &destination\&dbsid\log\backup.log;  remark 1 select 'alter tablespace '||t.name||' end backup;' from sys.file$ f, v$backup v, sys.ts$ t where v.file# = f.file# and   f.ts#   = t.ts# and   v.status = 'ACTIVE';  remark 2 select 'alter system switch logfile;' from sys.v_$log;  remark 3 prompt alter database backup controlfile to '&destination\&dbsid\control01.ctl';; prompt alter database backup controlfile to '&destination\&dbsid\control02.ctl';;  remark 4 prompt alter database backup controlfile to trace;;  remark 5 select 'alter tablespace '||tablespace_name||' begin backup;' || chr(10) ||        'host copy '||file_name||' &destination\&dbsid\ >> &temp\hotbackup.log' || chr(10) ||        'alter tablespace '||tablespace_name||' end backup;' || chr(10) from dba_data_files;   remark 6 prompt select file#,status, to_char(time,'DD-MM-YYYY HH24:Mi') TIME from v$backup;;  prompt Spool off;; prompt exit;;  spool off  set feedback on set heading on set pagesize 40 set linesize 80       --- 5 SCHEDULE SCRIPT ---  To schedule the scripts for execution at night you needed to use a tool to schedule it. One easy to use tool is winat. This tool is included in the windows recourse kit.  When you install this tool, make sure the service schedule is running and also starts automaticly. To check this after the installation open your control panel with subtopic services and check the service schedule it must be up and running.   After all these steps you have a good hotbackup senario. Of course you have to test your script for errors. At the destination location there will be a subfolder with the name log containing a file called backup.log. This file will display the result of the backup. If there is any kind of error during the execution check this file and solve the problem