Installgen
Features and Benefits
Installgen
Demo Available for download...
Bookmark This Page
#! /usr/local/bin/perl
# script: prod3_open_db_backup_job_1.pl (renamed from 33_win_prod3_open_db_backup_job_1.pl)
# Features: This perl script creates a sql script which performs an
# open database backup to the c:\backup location. It then
# executes the sql script it builds in order to perform the
# backup.
# This script also builds the open database restore sql file when it builds
# the sql file for the backup.
# A DBVERIFY is performed on each of the backup files created
# during the backup process.
# Note: This script only needs scheduled if the same task has not
# been scheduled via a OEM job.
#
# Output Files: prod3_open_db_backup_files_1.sql
# prod3_open_db_restore_files_1.sql
# c:\backup\prod3_open_db_backup_job_1.log
# c:\backup\prod3_disaster_recovery.txt
#
# Script Sequence#: 33
# Used By: run automatically via AT scheduler
# Usage:
# ******** Open Oracle Database backup nightly at 4:03 ********
# AT 4:03 /every:M,T,W,Th,F,S,Su c:\server_scripts\prod3_open_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.
# Corrected orapwd filename format.
# 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_open_db_backup_files_get_1.sql") || die ("Could not open output file c:\\server_scripts\\prod3_open_db_backup_files_get_1.sql for writing. \n Does the full directory path exist?");
print FILE1 "-- script: prod3_open_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 "-- for an open database backup (hot backup).\n";
print FILE1 "-- Output File: prod3_open_db_backup_files_1.sql\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-05-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_open_db_backup_files_1.sql\n";
print FILE1 "select '-- script: prod3_open_db_backup_files_1.sql ' from dual;\n";
print FILE1 "select '-- Features: This sql script performs an open database backup to' from dual;\n";
print FILE1 "select '-- the c:\\backup location. ' from dual;\n";
print FILE1 "select '-- Each backed up datafile is checked with DBVERIFY ' from dual;\n";
print FILE1 "select '-- after it is copied to the c:\\backup location. ' from dual;\n";
print FILE1 "select '-- ' from dual;\n";
print FILE1 "select '-- Used By: executed by 33_win_prod3_open_db_backup_job_1.pl ' 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-21-2001 dsimpson Initial Release ' from dual;\n";
print FILE1 "select '-- ' from dual;\n";
print FILE1 "select 'set echo on ' from dual;\n";
print FILE1 "select 'ALTER TABLESPACE ' || t.name || ' BEGIN BACKUP;' || CHR(10)\n";
print FILE1 "|| 'host c:\\v901\\bin\\ocopy.exe ' || f.name || ' c:\\backup\\prod3_' || substr(f.name,instr(f.name,'\\',-1,1)+1) || CHR(10)\n";
print FILE1 "|| 'ALTER TABLESPACE ' || t.name || ' END BACKUP;' || CHR(10)\n";
print FILE1 "|| 'ALTER SYSTEM ARCHIVE LOG CURRENT;' || CHR(10)\n";
print FILE1 "from V\$TABLESPACE t, V\$DATAFILE f where t.ts# = f.ts# order by t.name;\n";
print FILE1 "select ' ' from dual;\n";
print FILE1 "select '-- check each backed up datafile with DBVERIFY' from dual;\n";
print FILE1 "select 'host 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 'host c:\\v901\\bin\\ocopy.exe prod3_' || substr(name,instr(name,'\\',-1,1)+1) || '_dbverify.log' || ' c:\\backup' from V\$DATAFILE;\n";
print FILE1 "select 'host type c:\\backup\\prod3_' || substr(name,instr(name,'\\',-1,1)+1) || '_dbverify.log' from V\$DATAFILE;\n";
print FILE1 "spool off\n";
# write the SQL file which obtains the list of datafiles for restore
open (FILE1,">c:\\server_scripts\\prod3_open_db_restore_files_get_1.sql") || die ("Could not open output file c:\\server_scripts\\prod3_open_db_restore_files_get_1.sql for writing. \n Does the full directory path exist?");
print FILE1 "-- script: prod3_open_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_open_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-21-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_open_db_restore_files_1.sql\n";
print FILE1 "select 'REM script: prod3_open_db_restore_files_1.sql ' from dual;\n";
print FILE1 "select 'REM Features: This batch file performs an open database restore from' from dual;\n";
print FILE1 "select 'REM the c:\\backup location. ' from dual;\n";
print FILE1 "select 'REM Notes: This entire script should not normally be run.' from dual;\n";
print FILE1 "select 'REM Comment out the unneeded statements in order to restore' from dual;\n";
print FILE1 "select 'REM a subset of the database files which have been lost or damaged.' from dual;\n";
print FILE1 "select 'REM The database should be shut down when performing the restore.' from dual;\n";
print FILE1 "select 'REM ' from dual;\n";
print FILE1 "select 'REM Used By: executed manually by the 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-21-2001 dsimpson Initial Release ' from dual;\n";
print FILE1 "select ' ' from dual;\n";
print FILE1 "select ' ' from dual;\n";
print FILE1 "select 'copy c:\\backup\\prod3_' || substr(name,instr(name,'\\',-1,1)+1) || ' ' || name from V\$DATAFILE\n";
print FILE1 "union\n";
print FILE1 "select 'copy c:\\backup\\prod3_' || substr(member,instr(member,'\\',-1,1)+1) || ' ' || member from V\$LOGFILE\n";
print FILE1 "union\n";
print FILE1 "select 'copy c:\\backup\\prod3_' || substr(name,instr(name,'\\',-1,1)+1) || ' ' || name from V\$CONTROLFILE;\n";
print FILE1 "spool off\n";
# 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 off
-- perform an inconsistent backup of the controlfile
-- using this binary control file requires an OPEN RESETLOGS
-- delete existing file if it exists
host del /F /Q c:\\backup\\prod3_control01.ctl_bk
ALTER DATABASE BACKUP CONTROLFILE TO 'c:\\backup\\prod3_control01.ctl_bk';
-- 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 sql backup script
\@c:\\server_scripts\\prod3_open_db_backup_files_get_1.sql
-- get list of datafiles for restore script
\@c:\\server_scripts\\prod3_open_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
-- perform the actual backup and check the files with DBVERIFY
\@c:\\server_scripts\\prod3_open_db_backup_files_1.sql
set echo on
set feedback on
set verify on
set pagesize 24
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 a copy of alert.log file into c:\backup directory
system ("xcopy /Y c:\\u01\\bdump\\prod3ALRT.LOG c:\\backup\\");
# make backup copies of the backup and restore scripts so that they get put onto tape
system ("xcopy /Y c:\\server_scripts\\prod3_open_db_backup_files_get_1.sql c:\\backup\\");
system ("xcopy /Y c:\\server_scripts\\prod3_open_db_restore_files_get_1.sql c:\\backup\\");
system ("xcopy /Y c:\\server_scripts\\prod3_open_db_backup_files_1.sql c:\\backup\\");
system ("xcopy /Y c:\\server_scripts\\prod3_open_db_restore_files_1.sql 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\\pwdprod3.ora c:\\backup\\");
