Automated conventional hot backup, export backup and their clean up
script on a windows environment are pasted below - it is advised to go
through the script / test on a Dev-Test environment before moving over
to Prod since a couple of locations are to be hardcoded based on the
environment. Most of the work is being carried out by pl/sql scripts,
this gave me little chance to improvise my pl/sql skills ;-).
The .sql files in the .bat files create runtime sql files that will place the backup files in a folder named after the date string of the current day. The scripts are located in 'D:\oracle\backups\scripts' and the hot backup is taken in 'D:\oracle\backups\hot\DD-MON-YYYY' directory while the export backup is taken in 'D:\oracle\backups\exp\DD-MON-YYYY'. Based on your environment please change Drive letter and other locations, which has to be reflected in all the scripts and batch files.
Due to some limitations in the way this webpage is built some lines of the scripts may have been cropped, please click on the name of the script to open it in a new window which you can copy from.
The .sql files in the .bat files create runtime sql files that will place the backup files in a folder named after the date string of the current day. The scripts are located in 'D:\oracle\backups\scripts' and the hot backup is taken in 'D:\oracle\backups\hot\DD-MON-YYYY' directory while the export backup is taken in 'D:\oracle\backups\exp\DD-MON-YYYY'. Based on your environment please change Drive letter and other locations, which has to be reflected in all the scripts and batch files.
Due to some limitations in the way this webpage is built some lines of the scripts may have been cropped, please click on the name of the script to open it in a new window which you can copy from.
############################### # # # H O T B K U P # # # ###############################
################################ ### hot_backup_on_disk.bat #### ################################ set ORACLE_SID=PROD set ORACLE_HOME=D:\oracle\10.2.0 %ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\01-bkup_dest.sql %ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\02-hot_bkup.sql %ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\03-arch_switch.sql %ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\04-ctrl_file.sql
########################### #### 01-bkup_dest.sql ### ########################### set head off set feed off set line 150 set serveroutput on spool D:\oracle\backups\scripts\bkup_dest_create.sql declare dir varchar2(20); begin select to_char(sysdate,'DD-MON-YYYY') into dir from dual; dbms_output.put_line('host mkdir D:\oracle\backups\hot\'||dir); end; / spool off @D:\oracle\backups\scripts\bkup_dest_create.sql exit
########################## ### 02-hot_bkup.sql #### ########################## set head off set feed off set line 150 set serveroutput on spool D:\oracle\backups\scripts\hot_bkup_copy.sql declare cursor ts is select distinct(tablespace_name) from dba_data_files; cursor fn (ts_name in VARCHAR2) is select file_name from dba_data_files where tablespace_name=ts_name order by 1; dir varchar2(20); begin select to_char(sysdate,'DD-MON-YYYY') into dir from dual; dbms_output.put_line(chr(0)); dbms_output.put_line('-----------------------'); dbms_output.put_line('-- Copying Datafiles --'); dbms_output.put_line('-----------------------'); dbms_output.put_line(chr(0)); for c in ts loop dbms_output.put_line('alter tablespace '||c.tablespace_name - ||' begin backup;'); for d in fn (c.tablespace_name) loop dbms_output.put_line('host copy '||d.file_name|| - ' D:\oracle\backups\hot\'||dir||'\.'); end loop; dbms_output.put_line('alter tablespace '|| - c.tablespace_name||' end backup;'); dbms_output.put_line(chr(0)); end loop; end; / spool off @D:\oracle\backups\scripts\hot_bkup_copy.sql exit
############################# #### 03-arch_switch.sql #### ############################# ### ### The LOG_ARCHIVE_FORMAT parameter ### plays a significant role in here ### the format of archive files that ### is used here would be ### ${ORACLE_SID}_ARCH_*_*001, which ### has to be changed appropriately ### based on your environment ### set head off set feed off set line 150 set serveroutput on spool D:\oracle\backups\scripts\arch_switch_copy.sql declare prev_seq number; i number; j number; k number; dir varchar2(20); begin select to_char(sysdate,'DD-MON-YYYY') into dir from dual; select sequence# into i from v$log where status='CURRENT'; prev_seq:=i-1; execute immediate ('alter system switch logfile'); select sequence# into j from v$log where status='CURRENT'; execute immediate ('alter system switch logfile'); select sequence# into k from v$log where status='CURRENT'; execute immediate ('alter system switch logfile'); dbms_output.put_line(chr(0)); dbms_output.put_line('-----------------------'); dbms_output.put_line('-- Copying Logfiles ---'); dbms_output.put_line('-----------------------'); dbms_output.put_line(chr(0)); dbms_output.put_line('host copy - D:\oracle\oradata\arch\PROD_ARCH_*'||prev_seq||- '_*001 D:\oracle\backups\hot\'||dir||'\.'); dbms_output.put_line('host copy - D:\oracle\oradata\arch\PROD_ARCH_*'||i||- '_*001 D:\oracle\backups\hot\'||dir||'\.'); dbms_output.put_line('host copy - D:\oracle\oradata\arch\PROD_ARCH_*'||j||- '_*001 D:\oracle\backups\hot\'||dir||'\.'); dbms_output.put_line('host copy - D:\oracle\oradata\arch\PROD_ARCH_*'||k||- '_*001 D:\oracle\backups\hot\'||dir||'\.'); end; / spool off @D:\oracle\backups\scripts\arch_switch_copy.sql exit
########################## ### 04-ctrl_file.sql ### ########################## set head off set feed off set line 150 set serveroutput on spool D:\oracle\backups\scripts\ctrl_file_copy.sql declare dir varchar2(20); begin select to_char(sysdate,'DD-MON-YYYY') into dir from dual; execute immediate 'alter session set tracefile_identifier='||''''||dir||''''; execute immediate ('alter database backup controlfile to trace'); execute immediate 'alter database backup controlfile to'||''''||'D:\oracle\backups\hot\'||dir||'\controlfile_'||dir||'_bin.bkup'||''''; dbms_output.put_line('host move D:\oracle\admin\PROD\udump\*'- ||dir||'*.trc D:\oracle\backups\hot\'||dir||'\.'); end; / spool off @D:\oracle\backups\scripts\ctrl_file_copy.sql exit
############################### # # # E X P B K U P # # # ###############################
#################################### ### export_backup_on_disk.bat ### #################################### set ORACLE_SID=PROD set ORACLE_HOME=D:\oracle\10.2.0 %ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\01-exp_dest.sql %ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\02-exp_bkup.sql
######################### ### 01-exp_dest.sql ### ######################### set head off set feed off set line 150 set serveroutput on spool D:\oracle\backups\scripts\exp_dest_create.sql declare dir varchar2(20); begin select to_char(sysdate,'DD-MON-YYYY') into dir from dual; dbms_output.put_line('host mkdir D:\oracle\backups\exp\'||dir); end; / spool off @D:\oracle\backups\scripts\exp_dest_create.sql exit
######################### ### 02-exp_bkup.sql ### ######################### set head off set feed off set line 300 set serveroutput on spool D:\oracle\backups\scripts\exp_bkup_full.sql declare dir varchar2(20); begin select to_char(sysdate,'DD-MON-YYYY') into dir from dual; dbms_output.put_line('host exp system/manager file=D:\oracle\backups\exp\'||dir||'\PROD_full.dmp log=D:\oracle\backups\exp\'||dir||'\PROD_full.log full=y buffer=10485760 consistent=y compress=n'); end; / spool off @D:\oracle\backups\scripts\exp_bkup_full.sql exit
####################################### # # # C L E A N U P J O B S # # # #######################################
########################### ### delete_hot_bkup.bat ### ########################### ### ### Keeps the last 5 days of hot backups ### on disk while deleting anything older ### than that ### forfiles /p "D:\oracle\backups\hot" /s /m *.* /d -05 /c "cmd /c del @path"
########################### ### delete_exp_bkup.bat ### ########################### ### ### keeps the last 5 days of export backups ### on disk while deleting anything older than ### that ### forfiles /p "D:\oracle\backups\exp" /s /m *.* /d -05 /c "cmd /c del @path"
No comments:
Post a Comment