Installgen
Features and Benefits
Installgen
Demo Available for download...
Bookmark This Page
#! /usr/local/bin/perl
# script: prod3_rman_oem_backup_job_1.pl (renamed from 62_win_920_prod3_rman_oem_backup_job_1.pl)
# Features: This perl script performs an RMAN full database backup of all
# datafiles, archivelogs and the control file. The init.ora and orapw
# files are backed up via OS commands. The control file is also backed
# up to a trace file within the c:\backup directory.
# Any backup corruption found by RMAN is reported.
#
# Script Sequence#: 62
# Used By: scheduled via OEM job system
# Usage:
# ******** RMAN OEM Oracle database full backup at 4:03 ********
# c:\server_scripts\prod3_rman_oem_backup_job_1.bat
# Copyright 2002 by .com Solutions Inc.
#
# ---------------------- Revision History ---------------
# Date By Changes
# 12-23-2001 dsimpson Initial Release
# 12-27-2001 dsimpson Added ORACLE_SID environment variable
# 01-01-2002 dsimpson Added File::Copy declaration,
# corrected pathnames.
# 04-17-2002 dsimpson Added NOEXCLUDE to always force backup of
# Read-Only tablespaces, changed maxpiecesize = 1900M.
# Added use of oem sid domain.
# 07-30-2002 dsimpson Added "TO directoryname" clause to restore controlfile
# validate command for Oracle 9.2.0.
# Simplified "system" command calls.
# Removed "delete obsolete" command for backup sets.
# This output file was created by Installgen version 1.38 on Thu Nov 14 17:48:05 2002. By .com Solutions Inc. www.dotcomsolutionsinc.net
use strict;
use File::Copy;
# insure that environment variable is used by this perl script
$ENV{'ORACLE_SID'} = "PROD3";
my $tempsqlcode='';
my @proglist='';
my $temp_sql_filename="temp_sql.sql";
my $single_quote_var= chr(39);
# make a backup copy of the init.ora file
File::Copy::copy("c:\\v901\\database\\initprod3.ora","c:\\backup\\initprod3.ora");
# make a backup copy of the orapwd file
File::Copy::copy("c:\\v901\\database\\pwdprod3.ora","c:\\backup\\orapwprod3.ora");
# start the rman backup
my $tempsqlcode=<<"EOF";
connect catalog rman/rmanpassword\@prod5.world
configure retention policy to redundancy 3;
configure retention policy to recovery window of 3 days;
configure backup optimization on;
configure controlfile autobackup off;
configure default device type to Disk;
configure device type Disk parallelism 1;
configure datafile backup copies for device type Disk to 1;
configure channel device type Disk format 'c:\\backup\\%d_backup_%s_%p.bak';
configure maxsetsize to unlimited;
set controlfile autobackup format for device type Disk to 'c:\\backup\\prod3_controlfile_%F';
show all;
run {
allocate channel ch3 type Disk maxpiecesize = 1900M;
backup full database noexclude
include current controlfile
format 'c:\\backup\\%d_backup_%s_%p.bak'
tag 'prod3_datafile_daily';
}
run {
allocate channel ch3 type Disk maxpiecesize = 1900M;
backup archivelog all
delete all input
format 'c:\\archive\\%d_archivelog_%s_%p.bak'
tag 'prod3_archivelog_daily';
}
crosscheck backup;
restore controlfile to 'c:\\backup' validate;
restore tablespace SYSTEM validate;
restore archivelog all validate;
list backup of database;
report unrecoverable;
report schema;
report need backup;
delete noprompt expired backup of database;
delete noprompt expired backup of controlfile;
quit
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\\rman.exe target / \@$temp_sql_filename");
# 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);
# check for corrupt blocks found during RMAN backup
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
set echo on
set verify on
set feedback on
-- check for corruption found by RMAN
select * from V\$BACKUP_CORRUPTION;
-- 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';
-- output the disaster recovery info to c:\\backup\\prod3_disaster_recovery.txt
\@c:\\server_scripts\\prod3_disaster_recovery_get.sql
exit;
EOF
print "RMAN OEM backup job logfile: c:\\backup\\prod3_rman_oem_backup_job_1.log \n";
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");
# ---------------------- Troubleshooting ---------------
# to solve RMAN 6089 error - archivelog file not found, after file deleted
# after being logged into RMAN
# CHANGE ARCHIVELOG ALL VALIDATE;
