Installgen
Features and Benefits
Installgen
Demo Available for download...
Bookmark This Page
#! /usr/local/bin/perl
# script: prod3_closed_db_backup_job_1.pl (renamed from 31_win_prod3_closed_db_backup_job_1.pl)
# Features: This perl script performs a closed database backup to the c:\backup location.
# This script also builds the closed database restore script when it builds
# the perl script for the backup.
# Note: This script only needs scheduled if the same task has not
# been scheduled via an OEM job.
#
# Output Files: prod3_closed_db_backup_files_1.bat
# prod3_closed_db_restore_files_1.bat
# c:\backup\prod3_closed_db_backup_job_1.log
# c:\backup\prod3_disaster_recovery.txt
#
# Script Sequence#: 31
# Used By: run automatically via AT scheduler
# Usage:
# ******** Closed Oracle Database backup nightly at 4:03 ********
# AT 4:03 /every:M,T,W,Th,F,S,Su c:\server_scripts\prod3_closed_db_backup_job_1.bat
# Copyright 2002 by .com Solutions Inc.
#
# ---------------------- Revision History ---------------
# Date By Changes
# 12-21-2001 dsimpson Initial Release
# 12-27-2001 dsimpson Added ORACLE_SID environment variable
# 07-29-2002 dsimpson Simplified "system" call code.
# This output file was created by Installgen version 1.38 on Thu Nov 14 17:16:25 2002. By .com Solutions Inc. www.dotcomsolutionsinc.net
use strict;
# insure that environment variable is used by this perl script
$ENV{'ORACLE_SID'} = "PROD3";
my $tempsqlcode='';
my @proglist='';
my $temp_rman_filename="temp_rman.sql";
my $temp_sql_filename="temp_sql.sql";
my $single_quote_var= chr(39);
my $file_to_rotate='';
# write the SQL file which obtains the list of datafiles for backup
open (FILE1,">c:\\server_scripts\\prod3_closed_db_backup_files_get_1.sql") || die ("Could not open output file c:\\server_scripts\\prod3_closed_db_backup_files_get_1.sql for writing. \n Does the full directory path exist?");
print FILE1 "-- script: prod3_closed_db_backup_files_get_1.sql\n";
print FILE1 "-- Features: This sql script obtains a list of files to be backed up. \n";
print FILE1 "-- Output File: prod3_closed_db_backup_files_1.bat\n";
print FILE1 "--\n";
print FILE1 "-- Copyright 2002 by .com Solutions Inc.\n";
print FILE1 "--\n";
print FILE1 "-- ---------------------- Revision History ---------------\n";
print FILE1 "-- Date By Changes\n";
print FILE1 "-- 12-04-2001 dsimpson Initial Release\n";
print FILE1 " \n";
print FILE1 "set echo off\n";
print FILE1 "set feedback off\n";
print FILE1 "set verify off\n";
print FILE1 "set pagesize 0\n";
print FILE1 "set linesize 150\n";
print FILE1 "spool c:\\server_scripts\\prod3_closed_db_backup_files_1.bat\n";
print FILE1 "select 'REM script: prod3_closed_db_backup_files_1.bat ' from dual;\n";
print FILE1 "select 'REM Features: This batch file performs a closed database backup to' from dual;\n";
print FILE1 "select 'REM the c:\\backup location. ' from dual;\n";
print FILE1 "select 'REM ' from dual;\n";
print FILE1 "select 'REM Used By: executed by prod3_closed_db_backup_job_1.pl ' from dual;\n";
print FILE1 "select 'REM ' from dual;\n";
print FILE1 "select 'REM Copyright 2002 by .com Solutions Inc. ' from dual;\n";
print FILE1 "select 'REM ' from dual;\n";
print FILE1 "select 'REM ---------------------- Revision History --------------- ' from dual;\n";
print FILE1 "select 'REM Date By Changes ' from dual;\n";
print FILE1 "select 'REM 12-21-2001 dsimpson Initial Release ' from dual;\n";
print FILE1 "select 'REM ' from dual;\n";
print FILE1 "select '' from dual;\n";
print FILE1 "select '' from dual;\n";
print FILE1 "select 'REM ' from dual;\n";
print FILE1 "select 'REM ' from dual;\n";
print FILE1 "select 'c:\\v901\\bin\\ocopy.exe '|| name ||' c:\\backup\\prod3_'|| substr(name,instr(name,'\\',-1,1)+1) from V\$DATAFILE\n";
print FILE1 "union\n";
print FILE1 "select 'c:\\v901\\bin\\ocopy.exe ' || member || ' c:\\backup\\prod3_' || substr(member,instr(member,'\\',-1,1)+1) from V\$LOGFILE\n";
print FILE1 "union\n";
print FILE1 "select 'c:\\v901\\bin\\ocopy.exe ' || name ||' c:\\backup\\prod3_' || substr(name,instr(name,'\\',-1,1)+1) from V\$CONTROLFILE;\n";
print FILE1 "select 'REM check each backed up datafile with DBVERIFY' from dual;\n";
print FILE1 "select 'c:\\v901\\bin\\dbv.exe file=c:\\backup\\prod3_' || substr(name,instr(name,'\\',-1,1)+1) || ' blocksize=8192 logfile=prod3_' || substr(name,instr(name,'\\',-1,1)+1) || '_dbverify.log' from V\$DATAFILE;\n";
print FILE1 "select 'mv prod3_' || substr(name,instr(name,'\\',-1,1)+1) || '_dbverify.log' || ' c:\\backup\\' || substr(name,instr(name,'\\',-1,1)+1) || '_dbverify.log' from V\$DATAFILE;\n";
print FILE1 "select 'type c:\\backup\\prod3_' || substr(name,instr(name,'\\',-1,1)+1) || '_dbverify.log' from V\$DATAFILE;\n";
print FILE1 "spool off\n";
# close the output file
close (FILE1);
# write the SQL file which obtains the list of datafiles for restore
open (FILE1,">c:\\server_scripts\\prod3_closed_db_restore_files_get_1.sql") || die ("Could not open output file c:\\server_scripts\\prod3_closed_db_restore_files_get_1.sql for writing. \n Does the full directory path exist?");
print FILE1 "-- script: prod3_closed_db_restore_files_get_1.sql\n";
print FILE1 "-- Features: This sql script obtains a list of files to be restored. \n";
print FILE1 "-- Output File: prod3_closed_db_restore_files_1.bat\n";
print FILE1 "--\n";
print FILE1 "-- Copyright 2002 by .com Solutions Inc.\n";
print FILE1 "--\n";
print FILE1 "-- ---------------------- Revision History ---------------\n";
print FILE1 "-- Date By Changes\n";
print FILE1 "-- 12-04-2001 dsimpson Initial Release\n";
print FILE1 " \n";
print FILE1 "set echo off\n";
print FILE1 "set feedback off\n";
print FILE1 "set verify off\n";
print FILE1 "set pagesize 0\n";
print FILE1 "set linesize 150\n";
print FILE1 "spool c:\\server_scripts\\prod3_closed_db_restore_files_1.bat\n";
print FILE1 "select 'REM script: prod3_closed_db_restore_files_1.bat ' from dual;\n";
print FILE1 "select 'REM Features: This batch file performs a closed database restore from' from dual;\n";
print FILE1 "select 'REM the c:\\backup location. ' from dual;\n";
print FILE1 "select 'REM ' from dual;\n";
print FILE1 "select 'REM Used By: executed manually by DBA' from dual;\n";
print FILE1 "select 'REM ' from dual;\n";
print FILE1 "select 'REM Copyright 2002 by .com Solutions Inc. ' from dual;\n";
print FILE1 "select 'REM ' from dual;\n";
print FILE1 "select 'REM ---------------------- Revision History --------------- ' from dual;\n";
print FILE1 "select 'REM Date By Changes ' from dual;\n";
print FILE1 "select 'REM 12-04-2001 dsimpson Initial Release ' from dual;\n";
print FILE1 "select 'REM ' from dual;\n";
print FILE1 "select 'mv c:\\backup\\prod3_' || substr(name,instr(name,'\\',-1,1)+1) || ' ' || name from V\$DATAFILE\n";
print FILE1 "union\n";
print FILE1 "select 'mv c:\\backup\\prod3_' || substr(member,instr(member,'\\',-1,1)+1) || ' ' || member from V\$LOGFILE\n";
print FILE1 "union\n";
print FILE1 "select 'mv c:\\backup\\prod3_' || substr(name,instr(name,'\\',-1,1)+1) || ' ' || name from V\$CONTROLFILE;\n";
print FILE1 "spool off\n";
# close the output file
close (FILE1);
# write the SQL file which obtains the list of disaster recovery info
my $FORMAT_VAR='99,999,990.90';
my $TS_FREE='SM$TS_FREE';
my $TS_AVAIL='SM$TS_AVAIL';
open (FILE1,">c:\\server_scripts\\prod3_disaster_recovery_get.sql") || die ("Could not open output file c:\\server_scripts\\prod3_disaster_recovery_get.sql for writing. \n Does the full directory path exist?");
print FILE1 "-- File: prod3_disaster_recovery_get.sql\n";
print FILE1 "-- Output File: prod3_disaster_recovery.txt\n";
print FILE1 "set echo off\n";
print FILE1 "set feedback off\n";
print FILE1 "set verify off\n";
print FILE1 "set pagesize 0\n";
print FILE1 "set linesize 100\n";
print FILE1 "spool c:\\backup\\prod3_disaster_recovery.txt\n";
print FILE1 "select '-- File: prod3_disaster_recovery.txt ' from dual;\n";
print FILE1 "select '-- Features: This text file provides a listing of tablespaces' from dual;\n";
print FILE1 "select '-- data file names,sizes and user account info. ' from dual;\n";
print FILE1 "select '-- ' from dual;\n";
print FILE1 "select '-- ' from dual;\n";
print FILE1 "select '-- Used By: DBA for disaster recovery purposes ' from dual;\n";
print FILE1 "select '-- ' from dual;\n";
print FILE1 "select '-- Copyright 2002 by .com Solutions Inc. ' from dual;\n";
print FILE1 "select '-- ' from dual;\n";
print FILE1 "select '-- ---------------------- Revision History --------------- ' from dual;\n";
print FILE1 "select '-- Date By Changes ' from dual;\n";
print FILE1 "select '-- 12-04-2001 dsimpson Initial Release ' from dual;\n";
print FILE1 "select ' ' from dual;\n";
print FILE1 "select 'Report produced on: ' || sysdate from dual;\n";
print FILE1 "select 'Database name: prod3' from dual;\n";
print FILE1 "select 'Database server hostname: host1' from dual;\n";
print FILE1 "select ' ' from dual;\n";
print FILE1 "select 'Tablespace' || CHR(9) || 'File' || CHR(9) || CHR(9) || CHR(9) || CHR(9) || 'Size (bytes)' from dual;\n";
print FILE1 "select tablespace_name || CHR(9) || CHR(9) || file_name || CHR(9) || CHR(9) || bytes from dba_data_files order by tablespace_name;\n";
print FILE1 "select ' ' from dual;\n";
print FILE1 "select 'Username' || CHR(9) || 'Status' || CHR(9) || 'Tablespace'|| CHR(9) || 'Temp' || CHR(9) || 'Created' from dual;\n";
print FILE1 "select username || CHR(9) || CHR(9) || account_status || CHR(9) || default_tablespace || CHR(9) || CHR(9) || temporary_tablespace || CHR(9) || created from dba_users order by username;\n";
print FILE1 "select ' ' from dual;\n";
print FILE1 "select 'Tablespace' || CHR(9) || CHR(9) || CHR(9) || 'Status' || CHR(9) || CHR(9) || 'Size (Mb)' || CHR(9) || 'Used (Mb)' || CHR(9) || 'Free (Mb)' from dual;\n";
print FILE1 "SELECT D.TABLESPACE_NAME,D.STATUS,TO_CHAR((A.BYTES/1024/1024),$single_quote_var$FORMAT_VAR$single_quote_var),TO_CHAR(((A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024),$single_quote_var$FORMAT_VAR$single_quote_var),TO_CHAR(((A.BYTES/1024/1024)-(A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024),$single_quote_var$FORMAT_VAR$single_quote_var) FROM DBA_TABLESPACES D,SYS.$TS_AVAIL A,SYS.$TS_FREE F WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME AND F.TABLESPACE_NAME(+)=D.TABLESPACE_NAME;\n";
print FILE1 "spool off\n";
# close the output file
close (FILE1);
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
set echo on
-- temporarily change the user dump dest so that
-- the text copy of the control file rebuilding sql commands
-- go into the c:\\backup\\ directory
ALTER SYSTEM SET USER_DUMP_DEST='c:\\backup';
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
ALTER SYSTEM SET USER_DUMP_DEST='c:\\u01\\udump';
-- get list of datafiles for backup batch file
\@c:\\server_scripts\\prod3_closed_db_backup_files_get_1.sql
-- get list of datafiles for restore batch file
\@c:\\server_scripts\\prod3_closed_db_restore_files_get_1.sql
-- output the disaster recovery info to c:\\backup\\prod3_disaster_recovery.txt
\@c:\\server_scripts\\prod3_disaster_recovery_get.sql
spool off
set echo on
set feedback on
set verify on
set pagesize 24
shutdown immediate;
exit;
EOF
open (FILE1,">$temp_sql_filename") || die ("Could not open output file $temp_sql_filename for writing. \n Does the full directory path exist?");
print FILE1 ($tempsqlcode);
# close the output file
close (FILE1);
system ("c:\\v901\\bin\\sqlplus.exe /nolog \@$temp_sql_filename");
# make backup copies of the backup and restore scripts so that they get put onto tape
system ("xcopy /Y c:\\server_scripts\\prod3_closed_db_backup_files_get_1.sql c:\\backup\\");
system ("xcopy /Y c:\\server_scripts\\prod3_closed_db_restore_files_get_1.sql c:\\backup\\");
system ("xcopy /Y c:\\server_scripts\\prod3_closed_db_restore_files_1.bat c:\\backup\\");
system ("xcopy /Y c:\\server_scripts\\prod3_closed_db_backup_files_1.bat c:\\backup\\");
# make a backup copy of the init.ora file
system ("xcopy /Y c:\\v901\\database\\initprod3.ora c:\\backup\\");
# make a backup copy of the orapwd file
system ("xcopy /Y c:\\v901\\database\\orapwprod3 c:\\backup\\");
# copy the database files to the c:\backup\ directory
system ("c:\\server_scripts\\prod3_closed_db_backup_files_1.bat");
# make a copy of alert.log file into c:\backup directory
system ("xcopy /Y c:\\u01\\bdump\\prod3ALRT.LOG c:\\backup\\");
# Rotate the Oracle c:\u01\bdump\prod3ALRT.LOG before the database starts
$file_to_rotate="c:\\u01\\bdump\\" . "prod3" . "ALRT.LOG";
rename ($file_to_rotate.".5",$file_to_rotate.".6");
rename ($file_to_rotate.".4",$file_to_rotate.".5");
rename ($file_to_rotate.".3",$file_to_rotate.".4");
rename ($file_to_rotate.".2",$file_to_rotate.".3");
rename ($file_to_rotate.".1",$file_to_rotate.".2");
rename ($file_to_rotate.".0",$file_to_rotate.".1");
rename ($file_to_rotate,$file_to_rotate.".0");
# start up the database again once the files have been copied
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
set echo on
startup;
exit;
EOF
open (FILE1,">$temp_sql_filename") || die ("Could not open output file $temp_sql_filename for writing. \n Does the full directory path exist?");
print FILE1 ($tempsqlcode);
# close the output file
close (FILE1);
system ("c:\\v901\\bin\\sqlplus.exe /nolog \@$temp_sql_filename");
