Installgen
Features and Benefits
Installgen
Demo Available for download...
Bookmark This Page
#! /usr/local/bin/perl
# script: 78_win_901_prod3_standby_standbysetup_1.pl
# Features: This perl script sets up the standby server in a standby database configuration.
# Tasks include:
# copying updated versions of the tnsnames.ora, init.ora
# listener.ora, status monitoring, change role files from
# network shared folder, and starting database
# in managed recovery mode
#
# Required Files: 78_win_901_prod3_standby_standbysetup_1.pl
# 79_prod3_standby_status_1.pl
# 80_win_prod3_standbydb_change_role_1.pl
# 72_prod3_client_taf_tnsnames_1.ora
# 74_win_prod3_standby_listener_1.ora
# 49_listener_ora_1.ora
# 81_prod3_standby_startup_service_1.pl
# 11_win_prod3_rotate_alert_log_1.pl
# 13_win_rotateoraclefiles_1.pl
#
# Script Sequence#: 78
# Used By: run manually by DBA
# Copyright 2002 by .com Solutions Inc.
#
# ---------------------- Revision History ---------------
# Date By Changes
# 01-18-2002 dsimpson Initial Release
#
# 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;
use File::Copy;
use Win32::Lanman;
use Win32::AdminMisc;
use Win32::Service;
# insure that environment variables are used by this perl script
$ENV{'ORACLE_SID'} = "prod3";
$ENV{'NLS_LANG'} = "AMERICAN_AMERICA.US8PC437";
# 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 $mount_point='';
my $temp_sql_filename = "temp_sql.sql";
my $tempsqlcode="";
my $admin_account_name='';
my $admin_account_password='';
# 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;
# share standby database volume with enough space to copy all files
print "\n\n";
print "Prepare to copy data from the primary server via a network share.\n";
print "\n";
print "1)(On the standby server.)Share the destination folder.\n";
print "[This destination folder should usually be the same path as the]\n";
print "[RMAN backup folder on the primary server so that RMAN will be]\n";
print "[able to find and use the backup files to create the]\n";
print "[standby database.]\n";
print "\n";
print "2)(On the primary server.)Map the shared folder from the standby server \n";
print "to a drive letter.\n";
print "\n";
print "3)(On the standby server.)Shut down the Oracle instance.\n";
print "\n";
print "Please press the return key after these steps have been done.\n";
print "\n\n";
$answer2 = ;
# ask whether to remove old files
print "\n\n";
print "Remove all Oracle datafiles on the standby server?(y/n)\n";
print "\n\n";
$answer = ;
chop($answer);
if ($answer eq "y" )
{
# stop OracleService for the prod3 instance - before removing
Win32::Lanman::StopService('','',"OracleServicePROD3");
# remove OracleService for the prod3 instance
Win32::Lanman::DeleteService('','',"OracleServicePROD3");
# stop OracleRotateAlertLog service for the prod3 instance - before removing
Win32::Lanman::StopService('','',"OracleRotateAlertLogPROD3");
# remove OracleRotateAlertLog service for the prod3 instance
Win32::Lanman::DeleteService('','',"OracleRotateAlertLogPROD3");
# stop OracleStandbyStartup service for the prod3 instance - before removing
Win32::Lanman::StopService('','',"OracleStandbyStartupPROD3");
# remove OracleStandbyStartup service for the prod3 instance
Win32::Lanman::DeleteService('','',"OracleStandbyStartupPROD3");
# remove old controlfiles on standby server - with same names/paths as prod3 instance
@filelist=("c:\u01\prod3\control01.ctl","c:\u01\prod3\control02.ctl","c:\u01\prod3\control03.ctl");
unlink (@filelist);
# remove old temp datafiles on standby server
@filelist=("c:\u01\prod3\temp01.dbf");
unlink (@filelist);
# remove old non-temp datafiles on standby server
@filelist=("c:\u01\prod3\system01.dbf","c:\u01\prod3\tools01.dbf","c:\u01\prod3\users01.dbf","c:\u01\prod3\rbs01.dbf","c:\u01\prod3\indx01.dbf","c:\u01\prod3\xdb01.dbf","c:\u01\prod3\drsys01.dbf");
unlink (@filelist);
# remove old redo log files on standby server
@filelist=("c:\u01\prod3\redo01.log","c:\u01\prod3\redo01g1f2.log","c:\u01\prod3\redo02.log","c:\u01\prod3\redo02g2f2.log","c:\u01\prod3\redo03.log","c:\u01\prod3\redo03g3f2.log");
unlink (@filelist);
}
# prompt to wait for script 77_win_901_prod3_primary_standbysetup_1.pl to run
print "\n\n";
print "1)If the existing datafiles on the standby server were not\n";
print "removed successfully, remove these files manually.\n";
print "The following controlfiles should have been removed:\n";
print "c:\\u01\\prod3\\control01.ctl
c:\\u01\\prod3\\control02.ctl
c:\\u01\\prod3\\control03.ctl
\n";
print "The following datafiles should have been removed:\n";
print "c:\\u01\\prod3\\system01.dbf
c:\\u01\\prod3\\tools01.dbf
c:\\u01\\prod3\\users01.dbf
c:\\u01\\prod3\\rbs01.dbf
c:\\u01\\prod3\\indx01.dbf
c:\\u01\\prod3\\xdb01.dbf
c:\\u01\\prod3\\drsys01.dbf
";
print "c:\\u01\\prod3\\temp01.dbf
\n";
print "The following redo log files should have been removed:\n";
print "c:\\u01\\prod3\\redo01.log
c:\\u01\\prod3\\redo02.log
c:\\u01\\prod3\\redo03.log
\n";
print "2) Remove any previously created environment variables for\n";
print "ORACLE_SID or ORACLE_HOME on this server\n";
print "3)Continue executing the 77_win_901_prod3_primary_standbysetup_1.pl script on the primary server.\n";
print "\n";
print "Then press the Return key to continue.\n";
print "\n\n";
$answer = ;
print "\n\n";
print "Please enter the full pathname to the network\n";
print "shared folder used by the primary database server to copy files.\n";
print "\n\n";
$mount_point = ;
chop ($mount_point);
# copy primary tnsnames.ora file to c:\v901\network\admin - backup original file
print "\n";
print "Copying 70_prod3_primary_tnsnames_1.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("$mount_point\\70_prod3_primary_tnsnames_1.ora","c:\\v901\\network\\admin\\tnsnames.ora");
# copy the failover version of the tnsnames.ora file - for switchover/failover situations
# this version of the tnsnames.ora file is used on the standby and primary servers when the standby server becomes the primary server
print "\n";
print "Copying 71_prod3_failover_tnsnames_1.ora to c:\\v901\\network\\admin\\tnsnames.ora-failover.\n";
print "\n";
File::Copy::copy("$mount_point\\71_prod3_failover_tnsnames_1.ora","c:\\v901\\network\\admin\\tnsnames.ora-primary");
# copy primary listener.ora file to c:\v901\network\admin - for switchover/failover situations
print "\n";
print "Copying 49_listener_ora_1.ora to c:\\v901\\network\\admin\\listener.ora-primary.\n";
print "\n";
File::Copy::copy("$mount_point\\49_listener_ora_1.ora","c:\\v901\\network\\admin\\listener.ora-primary");
# copy standby listener.ora file to c:\v901\network\admin
print "\n";
print "Copying 74_win_prod3_standby_listener_1.ora to c:\\v901\\network\\admin\\listener.ora.\n";
print "\n";
File::Copy::copy("$mount_point\\74_win_prod3_standby_listener_1.ora","c:\\v901\\network\\admin\\listener.ora");
# copy standby init.ora file to c:\v901\database\initprod3s.ora
print "\n";
print "Copying 76_win_901_prod3_standby_init_1.ora to c:\\v901\\database\\initprod3s.ora.\n";
print "\n";
File::Copy::copy("$mount_point\\76_win_901_prod3_standby_init_1.ora","c:\\v901\\database\\initprod3s.ora");
# copy primary init.ora file to c:\v901\database\initprod3.ora - for failover/switchover situations
print "\n";
print "Copying 75_win_901_prod3_primary_init_1.ora to c:\\v901\\database\\initprod3.ora.\n";
print "\n";
File::Copy::copy("$mount_point\\75_win_901_prod3_primary_init_1.ora","c:\\v901\\database\\initprod3.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("$mount_point\\80_win_prod3_standbydb_change_role_1.pl","c:\\server_scripts\\prod3_standbydb_change_role.pl");
# copy the OracleStandbyStartup service script to c:\server_scripts\prod3_standby_startup_service.pl
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("$mount_point\\81_prod3_standby_startup_service_1.pl","c:\\server_scripts\\prod3_standby_startup_service.pl");
# copy the RotateAlertLog script to c:\server_scripts\prod3_rotate_alert_log_1.pl
print "\n";
print "Copying 11_win_prod3_rotate_alert_log_1.pl to c:\\server_scripts\\prod3_rotate_alert_log_1.pl.\n";
print "\n";
File::Copy::copy("$mount_point\\11_win_prod3_rotate_alert_log_1.pl","c:\\server_scripts\\prod3_rotate_alert_log_1.pl");
# copy the Rotate logs script to c:\server_scripts\rotateoraclefiles.pl
print "\n";
print "Copying 13_win_rotateoraclefiles_1.pl to c:\\server_scripts\\rotateoraclefiles.pl.\n";
print "\n";
File::Copy::copy("$mount_point\\13_win_rotateoraclefiles_1.pl","c:\\server_scripts\\rotateoraclefiles.pl");
# create new ORACLE_SID and NLS_LANG environment variables
Win32::AdminMisc::SetEnvVar("ORACLE_SID","PROD3","ENV_SYSTEM",10);
Win32::AdminMisc::SetEnvVar("NLS_LANG","AMERICAN_AMERICA.US8PC437","ENV_SYSTEM",10);
# rename current orapwd file
print "\n";
print "Renaming c:\\v901\\database\\pwdprod3.ora to c:\\v901\\database\\old-pwdprod3.ora.\n";
print "\n";
rename("c:\\v901\\database\\pwdprod3.ora","c:\\v901\\database\\old-pwdprod3.ora");
# create new service for standby instance - startup = manual
print "Creating OracleServicePROD3...\n";
@proglist = ("c:\\v901\\bin\\oradim.exe -NEW -SID PROD3 -STARTMODE m -PFILE c:\\v901\\database\\initprod3s.ora");
system (@proglist);
# create new OracleStandbyStartup service for the prod3 instance
print "Creating OracleStandbyStartupPROD3.\n";
print "(This service will start the standby database in managed recovery)\n";
print "(mode during Windows startup.)\n";
print "\n\n";
if (!Win32::Lanman::CreateService('', '', { name => "OracleStandbyStartupPROD3",
display => "OracleStandbyStartupPROD3",
type => &SERVICE_WIN32_OWN_PROCESS,
start => &SERVICE_AUTO_START,
control => &SERVICE_ERROR_IGNORE,
account => 'LocalSystem',
password => '',
filename => "perl c:\\server_scripts\\prod3_standby_startup_service.pl"}))
{
print "Creation of service failed; error: ";
# get the error code
print Win32::Lanman::GetLastError();
exit 1;
}
# create new OracleRotateAlertLog service for the prod3 instance
print "Creating OracleRotateAlertLogPROD3.\n";
print "(This service will rotate the Oracle Alert log during Windows startup.)\n";
print "\n\n";
if (!Win32::Lanman::CreateService('', '', { name => "OracleRotateAlertLogPROD3",
display => "OracleRotateAlertLogPROD3",
type => &SERVICE_WIN32_OWN_PROCESS,
start => &SERVICE_AUTO_START,
control => &SERVICE_ERROR_IGNORE,
account => 'LocalSystem',
password => '',
filename => "perl c:\\server_scripts\\prod3_rotate_alert_log_1.pl"}))
{
print "Creation of service failed; error: ";
# get the error code
print Win32::Lanman::GetLastError();
exit 1;
}
# start OracleServiceprod3 service - so that it will be available for RMAN
@proglist = ("net start OracleServicePROD3");
system (@proglist);
# startup standby instance prod3 in nomount mode - to prevent ORA-1034
my $tempsqlcode=<<"EOF";
connect / as sysdba
-- make sure instance is down first
shutdown abort
-- remove old orapw file
host del c:\\v901\\database\\old-pwdprod3.ora
-- create new orapw file
host c:\\v901\\bin\\orapwd.exe file=c:\\v901\\database\\pwdprod3.ora password=syspwd entries=20
-- now startup in nomount mode - since there are no files yet
STARTUP NOMOUNT pfile='c:\\v901\\database\\initprod3s.ora';
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);
# stop/start listener and agent
print "\n\n";
print "Stopping/Starting Listener and Oracle Intelligent Agent..\n";
print "\n\n";
@proglist = ("c:\\v901\\bin\\lsnrctl.exe stop");
system (@proglist);
@proglist = ("c:\\v901\\bin\\lsnrctl.exe start");
system (@proglist);
@proglist = ("c:\\v901\\bin\\agentctl.exe stop");
system (@proglist);
@proglist = ("c:\\v901\\bin\\agentctl.exe start");
system (@proglist);
# install status_pl script in c:\server_scripts
# copy the status monitoring perl script
print "\n\n";
print "Copying 79_prod3_standby_status_1.pl to c:\server_scripts\prod3_standby_status.pl.\n";
print "\n\n";
File::Copy::copy("$mount_point\\79_prod3_standby_status_1.pl","c:\\server_scripts\\prod3_standby_status.pl");
# create AT Scheduler entry for execution of monitoring script
print "\n\n";
print "The prod3_standby_status.pl script is being scheduled via the AT scheduler...\n";
print " ******** Monitor standby database status nightly at 11:50PM ********\n";
print "\n\n";
@proglist = ("AT 23:50 /every:M,T,W,Th,F,S,Su c:\\server_scripts\\prod3_standby_status.pl");
system (@proglist);
# create AT Scheduler entry for rotate logs script
print "\n\n";
print "The rotateoraclefiles.pl script is being scheduled via the AT scheduler...\n";
print " ******** Rotate database log files nightly at 11:55PM ********\n";
print "\n\n";
@proglist = ("AT 23:55 /every:M,T,W,Th,F,S,Su c:\\server_scripts\\rotateoraclefiles.pl");
system (@proglist);
# Note: The format of each archivelog file is expected to be *.ARC in order
# to copy the archivelogs from the mount point to the archive directory on the
# standby server
print "\n";
print "Copying archivelog files from $mount_point to c:\\archive\\.\n";
print "\n";
@proglist = ("xcopy /Y $mount_point\\*.ARC c:\\archive\\\\*");
system (@proglist);
# prompt to wait for script 77_win_901_prod3_primary_standbysetup_1.pl to run - again
print "\n\n";
print "1)Continue executing the 77_win_901_prod3_primary_standbysetup_1.pl\n";
print "script on the primary server.\n";
print "[An RMAN restore will be done to create the standby instance.]\n";
print "\n";
print "After RMAN has created the standby instance, press the Return key to continue.\n";
print "\n\n";
$answer = ;
# start database in managed recovery mode - with or without a delay - with session disconnect
print "\n\n";
print "Starting standby database in managed recovery mode...\n";
print "\n\n";
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
set echo on
SHUTDOWN IMMEDIATE
STARTUP NOMOUNT pfile='c:\\v901\\database\\initprod3s.ora';
ALTER DATABASE MOUNT STANDBY DATABASE;
-- DELAY 0 for no delay
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 0 PARALLEL 8 DISCONNECT FROM SESSION;
SELECT process, status, thread#, sequence#, block#, blocks FROM v\$managed_standby;
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);
# ask whether to remove archivelog files from shared folder
print "\n";
print "Remove archivelog (*.ARC) and backup (*.BAK) files from shared folder: $mount_point?(y/n)";
print "\n";
$answer = ;
chop($answer);
if ($answer eq "y" )
{
# delete archivelog files from mount point - only if requested
@proglist = ("del /Q $mount_point\\*.ARC");
system (@proglist);
@proglist = ("del /Q $mount_point\\*.BAK");
system (@proglist);
}
print "\n\n";
print "** All Done **\n";
print "** The standby database is now set up and **\n";
print "** operating in managed recovery mode.**\n";
print "\n";
print "Use script c:\\server_scripts\prod3_standbydb_change_role.pl to change \n";
print "the role of either the primary or standby databases. \n";
print "\n";
print "Script 79_prod3_standby_status_1.pl has been configured as an AT job \n";
print "to monitor the status of the primary and standby databases. \n";
print "\n";
print "Remove any unneeded backup or archivelog files which\n";
print "were copied from the primary server.\n";
print "\n";
print "++++ Change the name of the account which +++\n";
print "++++ runs the OracleStandbyStartupPROD3 Service +++\n";
print "++++ from LocalSystem to an account which is +++\n";
print "++++ a member of the ORA_DBA group and which +++\n";
print "++++ has the Local User Right to Run as a Service. +++\n";
print "++++ The Administrator account will generally be used +++\n";
print "++++ for this task. +++\n";
print "\n";
print "==== Make the same change for the account which ====\n";
print "==== runs the OracleRotateAlertLogPROD3 Service. ====\n";
print "\n\n";
