Installgen
Features and Benefits
Installgen
Demo Available for download...
Bookmark This Page
#! /usr/local/bin/perl
# script: 77_win_920_prod3_primary_standbysetup_1.pl
# Features: This perl script sets up the primary server in a standby database configuration.
# Tasks include:
# performing the RMAN backup of the primary server,
# dynamically changing init.ora parameters to the new standby settings,
# copying updated versions of the tnsnames.ora, init.ora,
# status monitoring, change role, backup files
# and archived logs to the standby server,
#
# Required Files: 77_win_920_prod3_primary_standbysetup_1.pl
# 79_prod3_standby_status_1.pl
# 80_win_prod3_standbydb_change_role_1.pl
# 70_prod3_primary_tnsnames_1.ora
# 71_prod3_failover_tnsnames_1.ora
# 72_prod3_client_taf_tnsnames_1.ora
# 75_win_920_prod3_primary_init_1.ora
# 49_listener_ora_1.ora
# 76_win_920_prod3_standby_init_1.ora
# 81_prod3_standby_startup_service_1.pl
#
# Script Sequence#: 77
# Used By: run manually by DBA
# Copyright 2002 by .com Solutions Inc.
#
# ---------------------- Revision History ---------------
# Date By Changes
# 01-18-2002 dsimpson Initial Release
# 02-26-2002 dsimpson Changed maxpiecesize from 10G to 1900M
# to prevent any possible 32bit issues.
# 04-17-2002 dsimpson Added NOEXCLUDE to insure Read-Only tablespaces
# always get backed up.
# Added use of oem sid domain.
# 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;
use Win32::AdminMisc;
# insure that environment variables are used by this perl script
$ENV{'ORACLE_SID'} = "PROD3";
$ENV{'NLS_LANG'} = "AMERICAN_AMERICA.US8PC437";
# create NLS_LANG environment variable
Win32::AdminMisc::SetEnvVar("NLS_LANG","AMERICAN_AMERICA.US8PC437","ENV_SYSTEM",10);
# list of program and command line parameters to execute via operating system
my @proglist='';
# list of files to delete
my @filelist='';
my $answer='';
my $answer2='';
my $pause1='';
my $pause2='';
my $pause3='';
my $remote_mount2='';
my $backup_catalog_type='';
my $temp_sql_filename = "temp_sql.sql";
my $tempsqlcode="";
my $STANDBY_BACKUP_DEST='';
my $STANDBY_SERVER_MOUNT_POINT='';
# define subs
sub copy_to_standby($STANDBY_SERVER_MOUNT_POINT);
# make sure the Administrator user is running this script
my ($login_name) = Win32::LoginName;
if ($login_name ne "Administrator")
{
# the user running this script is not Administrator - exit
print "You must be logged in as the Administrator user to run this script, exiting....";
# exit immediately!
die
}
# make sure STDOUT and STDERR are not buffered
select (STDOUT);
$|=1;
select (STDERR);
$|=1;
# execute 78_win_920_prod3_standby_standbysetup_1.pl on standby server first - to remove old files
print "\n\n";
print "1)Manually copy the 78_win_920_prod3_standby_standbysetup_1.pl script to\n";
print "the standby server.\n";
print "\n";
print "2)Execute the 78_win_920_prod3_standby_standbysetup_1.pl script on the standby\n";
print "server host2 to remove old datafiles.\n";
print "\n";
print "Then press the Return key to continue.\n";
print "\n\n";
$answer = ;
# copy new tnsnames.ora file to c:\v901\network\admin - backup existing file
print "\n";
print "Copying new tnsnames.ora to c:\\v901\\network\\admin\\tnsnames.ora (backing up existing file as orig-tnsnames.ora).\n";
print "\n";
rename ("c:\\v901\\network\\admin\\tnsnames.ora","c:\\v901\\network\\admin\\orig-tnsnames.ora");
File::Copy::copy("70_prod3_primary_tnsnames_1.ora",'c:\\v901\\network\\admin\\tnsnames.ora');
# copy the standby version of the tnsnames.ora file - for switchover/failover situations
print "\n";
print "Copying 71_prod3_failover_tnsnames_1.ora to c:\\v901\\network\\admin\\tnsnames.ora-standby.\n";
print "\n";
File::Copy::copy("71_prod3_failover_tnsnames_1.ora",'c:\\v901\\network\\admin\\tnsnames.ora-standby');
# copy the standby version of the listener.ora file - for switchover/failover situations
print "\n";
print "Copying 74_win_prod3_standby_listener_1.ora to c:\\v901\\network\\admin\\listener.ora-standby.\n";
print "\n";
File::Copy::copy("74_win_prod3_standby_listener_1.ora","c:\\v901\\network\\admin\\listener.ora-standby");
# copy the standby version of the init.ora file - for switchover/failover situations
print "\n";
print "Copying 76_win_920_prod3_standby_init_1.ora to c:\\v901\\database\\initprod3s.ora.\n";
print "\n";
File::Copy::copy("76_win_920_prod3_standby_init_1.ora","c:\\v901\\database\\initprod3s.ora");
# copy the change role script to c:\server_scripts\prod3_standbydb_change_role.pl
print "\n";
print "Copying 80_win_prod3_standbydb_change_role_1.pl to c:\\server_scripts\\prod3_standbydb_change_role.pl.\n";
print "\n";
File::Copy::copy("80_win_prod3_standbydb_change_role_1.pl","c:\\server_scripts\\prod3_standbydb_change_role.pl");
# copy the primary-standby version of the init.ora file
print "\n";
print "Copying new initprod3.ora to c:\\v901\\database\\initprod3.ora (backing up existing file as orig-initprod3.ora).\n";
print "\n";
rename ("c:\\v901\\database\\initprod3.ora","c:\\v901\\database\\orig-initprod3.ora");
File::Copy::copy("75_win_920_prod3_primary_init_1.ora","c:\\v901\\database\\initprod3.ora");
# copy the OracleStandbyStartup service script to c:\server_scripts for failover/switchover situations
print "\n";
print "Copying 81_prod3_standby_startup_service_1.pl to c:\\server_scripts\\prod3_standby_startup_service.pl.\n";
print "\n";
File::Copy::copy("81_prod3_standby_startup_service_1.pl","c:\\server_scripts\\prod3_standby_startup_service.pl");
# install status_pl script in c:\server_scripts - to be activated in a switchover situation
print "\n";
print "Copying 79_prod3_standby_status_1.pl to c:\server_scripts\prod3_standby_status.pl.\n";
print "\n";
File::Copy::copy("79_prod3_standby_status_1.pl","c:\\server_scripts\\prod3_standby_status.pl");
# remove old spfile - rebuild it in sqlplus
@filelist=("c:\\v901\\database\\spfileprod3.ora");
unlink (@filelist);
# dynamically change database init.ora parameters without shutting down primary database
print "\n";
print "Updating init.ora parameters dynamically on primary database...\n";
print "\n";
print "\n";
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
set echo on
ARCHIVE LOG LIST;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=c:\\archive mandatory REOPEN=30';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=prod3s REOPEN=15';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=enable;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=enable;
ALTER SYSTEM SET DRS_START=true;
-- the following statement is not used, because RMAN creates and copies each
-- of the controlfiles automatically
--ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'c:\\u01\\prod3\\standby_control01.ctl';
ALTER SYSTEM ARCHIVE LOG CURRENT;
-- re-create spfile from pfile
create spfile='c:\\v901\\database\\spfilepprod3.ora' FROM pfile='c:\\v901\\database\\initprod3.ora';
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);
@proglist = ("c:\\v901\\bin\\sqlplus.exe /nolog \@$temp_sql_filename");
system (@proglist);
# remotely mount standby database volume with enough space to copy all files/data
print "\n\n";
print "Prepare to copy data to the standby server via a network share.\n";
print "1)(On the standby server.)Share the destination folder.\n";
print "\n";
print "2)(On the primary server)Mount the remote share for copying.\n";
print "\n";
print "Please press the return key after these steps have been done.\n";
print "\n\n";
$answer2 = ;
chop($answer2);
# enter name of remote mount point
print "\n\n";
print "Please enter the drive letter or mount point path for the\n";
print "standby server's volume:\n";
print "\n\n";
$STANDBY_SERVER_MOUNT_POINT = ;
chop($STANDBY_SERVER_MOUNT_POINT);
# perform RMAN backup of primary database
# ask whether entire database will fit on c:\backup location
print "\n\n";
print "--------- RMAN backup process ---------\n";
print "Select the option number for the type of RMAN backup to use for creating the standby database:\n";
print "1) Backup entire database and archivelogs onto c:\\backup location (no RMAN catalog - using controlfile info).\n";
print "2) Use existing RMAN backup of database (with or without an RMAN catalog).\n";
print "\n\n";
$answer = ;
chop($answer);
if ($answer == 1 )
{
# selected 1
# RMAN backup to c:\\backup\\%d_backup_%s_%p.bak location
# Note: The standby controlfile is created as a separate task in order to
# allow the RMAN backup process to be generic enough to be used for creating a standby
# database or providing for recovery of the primary database (without turning it into a
# standby database in the process of a restore/recovery).
# back up database to c:\backup
my $tempsqlcode=<<"EOF";
configure retention policy to redundancy 1;
configure retention policy to recovery window of 1 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;
run {
allocate channel ch3 type Disk rate 1500K maxpiecesize = 1900M;
backup full database noexclude
include current controlfile
format 'c:\\backup\\%d_backup_%s_%p.bak'
tag 'prod3_stby_datafiles';
release channel ch3;
}
run {
allocate channel ch3 type Disk rate 1500K maxpiecesize = 1900M;
backup archivelog all
format 'c:\\archive\\%d_archivelog_%s_%p.bak'
tag 'prod3_stby_archivelogs';
release channel ch3;
}
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);
@proglist = ("c:\\v901\\bin\\rman.exe target / \@$temp_sql_filename");
system (@proglist);
# copy config files to standby server mount point
©_to_standby($STANDBY_SERVER_MOUNT_POINT);
}
if ($answer == 2 )
{
# selected 2
# RMAN - use existing backup
# copy config files to standby server mount point
©_to_standby($STANDBY_SERVER_MOUNT_POINT);
}
# ---------------
# --------------- code below here gets executed in all situations
# ---------------
# rename (then delete) existing standby control file if it exists so RMAN will not fail
print "\n";
print "Renaming c:\\backup\\standby_control01.ctl to c:\\backup\\standby_control01.ctl.old\n";
print "\n";
rename ("c:\\backup\\standby_control01.ctl","c:\\backup\\standby_control01.ctl.old");
# attempt to delete old standby controlfile - just to make sure
@filelist = ("c:\\backup\\standby_control01.ctl.old");
unlink (@filelist);
print "\n";
print "Creating standby controlfile with RMAN\n";
print "to location: c:\\backup\\standby_control01.ctl\n";
print "\n";
# Note: The standby controlfile is created as a separate task in order to
# allow the RMAN backup process to be generic enough to be used for creating a standby
# database or providing for recovery of the primary database (without turning it into a
# standby database in the process of a restore/recovery).
my $tempsqlcode_rman_controlfile=<<"EOF";
configure channel device type Disk format 'c:\\backup\\standby_control01.ctl';
run {
backup current controlfile for standby;
SQL 'alter system archive log current';
}
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_rman_controlfile);
# close the output file
close (FILE1);
@proglist = ("c:\\v901\\bin\\rman.exe target / \@$temp_sql_filename");
system (@proglist);
# rename (then delete) existing standby control on standby server mount point/share
print "\n";
print "Renaming, then deleting $STANDBY_SERVER_MOUNT_POINT:\\standby_control01.ctl before copying to $STANDBY_SERVER_MOUNT_POINT:\\standby_control01.ctl.\n";
print "\n";
rename ("$STANDBY_SERVER_MOUNT_POINT:\\standby_control01.ctl","$STANDBY_SERVER_MOUNT_POINT:\\standby_control01.ctl.old");
# attempt to delete old standby controlfile on standby server mount point - just to make sure
@filelist = ("$STANDBY_SERVER_MOUNT_POINT:\\standby_control01.ctl.old");
unlink (@filelist);
# archive the current online redo log file - prior to copying all archivelogs
# to standby database server
my $tempsqlcode=<<"EOF";
connect / as sysdba
ALTER SYSTEM ARCHIVE LOG CURRENT;
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);
@proglist = ("c:\\v901\\bin\\sqlplus.exe /nolog \@$temp_sql_filename");
system (@proglist);
# copy all of the archivelog files from the primary to the standby server
# copying of archivelog files must be done after the standby controlfile has been created
# Note: The format of each archivelog file is expected to be *.ARC in order for
# the 78_win_920_prod3_standby_standbysetup_1.pl script to copy the archivelogs to the archive directory on the
# standby server
@proglist = ("xcopy /Y c:\\archive\\* $STANDBY_SERVER_MOUNT_POINT:\\*");
system (@proglist);
# copy standby controlfile, archivelogs and backup files to the standby database
print "\n";
print "Copying backed up files from c:\\backup\\* to $STANDBY_SERVER_MOUNT_POINT:\\*.\n";
print "\n";
@proglist = ("xcopy /Y c:\\backup\\* $STANDBY_SERVER_MOUNT_POINT:\\*");
system (@proglist);
print "\n\n";
print "Please continue running script 78_win_920_prod3_standby_standbysetup_1.pl\n";
print "on the standby database server.\n";
print "\n";
print "Press the return key to continue.\n";
print "\n\n";
$pause2 = ;
print "\n\n";
print "Please enter the type of backup which was previously done:\n";
print "1)RMAN backup - without an RMAN catalog (using controlfile info).\n";
print "2)RMAN backup - with an RMAN catalog.\n";
print "\n\n";
$backup_catalog_type = ;
chop($backup_catalog_type);
if ($backup_catalog_type == 1)
{
# if RMAN backup using controlfile was used
print "\n";
print "Creating entire standby database with\n";
print "RMAN duplicate database feature.\n";
print "[Using RMAN info from controlfile.]\n";
print "\n";
# RMAN - duplicate database for standby - NOCATALOG
my $tempsqlcode=<<"EOF";
configure channel device type Disk format 'c:\\backup\\%d_backup_%s_%p.bak';
run {
allocate auxiliary channel aux1 type Disk maxpiecesize = 1900M;
duplicate target database for standby
nofilenamecheck
dorecover;
release channel aux1;
}
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);
@proglist = ("c:\\v901\\bin\\rman.exe target / auxiliary sys/syspwd\@prod3s \@$temp_sql_filename");
system (@proglist);
}
# if RMAN backup using catalog was used
if ($backup_catalog_type == 2)
{
print "\n";
print "Copy the RMAN datafile and archivelog backup \n";
print "files to the same location on the standby.\n";
print "server as was used to back them up\n";
print "on the primary server.\n";
print "[If the c:\\backup\\ directory was previously used]\n";
print "[then this has already been done.]\n";
print "\n";
print "Press the return key to continue.\n";
print "\n\n";
$pause3 = ;
# RMAN - duplicate database for standby - using CATALOG
my $tempsqlcode=<<"EOF";
connect catalog rman/rmanpassword\@prod5.world
configure channel device type Disk format 'c:\\backup\\%d_backup_%s_%p.bak';
run {
allocate auxiliary channel aux1 type Disk maxpiecesize = 1900M;
duplicate target database for standby
nofilenamecheck
dorecover;
release channel aux1;
}
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);
@proglist = ("c:\\v901\\bin\\rman.exe target / auxiliary sys/syspwd\@prod3s \@$temp_sql_filename");
system (@proglist);
}
print "\n\n";
print "Please continue running script 78_win_920_prod3_standby_standbysetup_1.pl on the standby database server.\n";
print "Press the return key to continue.\n";
print "\n\n";
$answer = ;
# ------------ copy_to_standby sub
sub copy_to_standby($STANDBY_SERVER_MOUNT_POINT) {
# this sub copies Oracle configuration files to the standby server mount point
# copy the standby version of the tnsnames.ora file to $STANDBY_SERVER_MOUNT_POINT location - for the standby database
print "\n";
print "Copying 71_prod3_failover_tnsnames_1.ora to $STANDBY_SERVER_MOUNT_POINT:\\71_prod3_failover_tnsnames_1.ora.\n";
print "\n";
File::Copy::copy("71_prod3_failover_tnsnames_1.ora","$STANDBY_SERVER_MOUNT_POINT:\\71_prod3_failover_tnsnames_1.ora");
# copy primary tnsnames.ora file to to $STANDBY_SERVER_MOUNT_POINT location - for failover/switchover of the standby database
print "\n";
print "Copying 70_prod3_primary_tnsnames_1.ora to $STANDBY_SERVER_MOUNT_POINT:\\70_prod3_primary_tnsnames_1.ora.\n";
print "\n";
File::Copy::copy("70_prod3_primary_tnsnames_1.ora","$STANDBY_SERVER_MOUNT_POINT:\\70_prod3_primary_tnsnames_1.ora");
# copy the standby version of the listener.ora file to $STANDBY_SERVER_MOUNT_POINT location - for the standby database
print "\n";
print "Copying 74_win_prod3_standby_listener_1.ora to $STANDBY_SERVER_MOUNT_POINT:\\74_win_prod3_standby_listener_1.ora.\n";
print "\n";
File::Copy::copy("74_win_prod3_standby_listener_1.ora","$STANDBY_SERVER_MOUNT_POINT:\\74_win_prod3_standby_listener_1.ora");
# copy the primary version of the listener.ora file to $STANDBY_SERVER_MOUNT_POINT/ location - for failover/switchover
print "\n";
print "Copying 49_listener_ora_1.ora to $STANDBY_SERVER_MOUNT_POINT:\49_listener_ora_1.ora.\n";
print "\n";
File::Copy::copy("49_listener_ora_1.ora","$STANDBY_SERVER_MOUNT_POINT:\\49_listener_ora_1.ora");
# copy the standby version of the initprod3s.ora file to $STANDBY_SERVER_MOUNT_POINT location - for the standby database
print "\n";
print "Copying 76_win_920_prod3_standby_init_1.ora to $STANDBY_SERVER_MOUNT_POINT:\\76_win_920_prod3_standby_init_1.ora.\n";
print "\n";
File::Copy::copy("76_win_920_prod3_standby_init_1.ora","$STANDBY_SERVER_MOUNT_POINT:\\76_win_920_prod3_standby_init_1.ora");
# copy primary init.ora file to $STANDBY_SERVER_MOUNT_POINT - for failover/switchover situations
print "\n";
print "Copying 75_win_920_prod3_primary_init_1.ora to $STANDBY_SERVER_MOUNT_POINT:\\75_win_920_prod3_primary_init_1.ora.\n";
print "\n";
File::Copy::copy("75_win_920_prod3_primary_init_1.ora","$STANDBY_SERVER_MOUNT_POINT:\\75_win_920_prod3_primary_init_1.ora");
# copy the status monitoring perl script
print "\n";
print "Copying 79_prod3_standby_status_1.pl to $STANDBY_SERVER_MOUNT_POINT:\\79_prod3_standby_status_1.pl.\n";
print "\n";
File::Copy::copy("79_prod3_standby_status_1.pl","$STANDBY_SERVER_MOUNT_POINT:\\79_prod3_standby_status_1.pl");
# copy the change role script to standby server
print "\n";
print "Copying 80_win_prod3_standbydb_change_role_1.pl to $STANDBY_SERVER_MOUNT_POINT:\\80_win_prod3_standbydb_change_role_1.pl.\n";
print "\n";
File::Copy::copy("80_win_prod3_standbydb_change_role_1.pl","$STANDBY_SERVER_MOUNT_POINT:\\80_win_prod3_standbydb_change_role_1.pl");
# copy the OracleStandbyStartup service script to standby server
print "\n";
print "Copying 81_prod3_standby_startup_service_1.pl to $STANDBY_SERVER_MOUNT_POINT:\\81_prod3_standby_startup_service_1.pl.\n";
print "\n";
File::Copy::copy("81_prod3_standby_startup_service_1.pl","$STANDBY_SERVER_MOUNT_POINT:\\81_prod3_standby_startup_service_1.pl");
# copy the RotateAlertLog script to standby server
print "\n";
print "Copying 11_win_prod3_rotate_alert_log_1.pl to $STANDBY_SERVER_MOUNT_POINT:\\11_win_prod3_rotate_alert_log_1.pl.\n";
print "\n";
File::Copy::copy("11_win_prod3_rotate_alert_log_1.pl","$STANDBY_SERVER_MOUNT_POINT:\\11_win_prod3_rotate_alert_log_1.pl");
# copy the Rotate logs script to standby server
print "\n";
print "Copying 13_win_rotateoraclefiles_1.pl to $STANDBY_SERVER_MOUNT_POINT:\\13_win_rotateoraclefiles_1.pl.\n";
print "\n";
File::Copy::copy("13_win_rotateoraclefiles_1.pl","$STANDBY_SERVER_MOUNT_POINT:\\13_win_rotateoraclefiles_1.pl");
# ------------ copy_to_standby sub
}
