Installgen
Features and Benefits
Installgen
Demo Available for download...
Bookmark This Page
#! /usr/local/bin/perl
# script: c:\server_scripts\prod3_standbydb_change_role.pl (renamed from 80_win_prod3_standbydb_change_role_1.pl)
# Features: This perl script changes the role of the servers in a standby database configuration.
# Role changes include:
# primary to standby switchover
# primary to standby failover
# standby to primary switchover
# standby to primary failover
# standby to read-only
# read-only to standby
#
# Script Sequence#: 80
# Used By: run manually by DBA
# Copyright 2002 by .com Solutions Inc.
#
# ---------------------- Revision History ---------------
# Date By Changes
# 01-18-2002 dsimpson Initial Release
# 06-19-2002 dsimpson (option #3) Added setting of job_queue_processes=0 before switchover
# (option #3) Added startup of instance without 8 recovery processes
# (option #3) Added recovery "FINISH" clause before switchover to
# work-around Oracle bug.
# (option #7) Re-wrote archivelog copying code to avoid
# copying the file currently being written.
# 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::Lanman;
use Win32::Service;
# define subs
sub rename_files_primary_to_standby();
sub rename_files_standby_to_primary();
sub listener_and_agent_stop();
sub listener_and_agent_start();
# insure that environment variable is used by this perl script
$ENV{'ORACLE_SID'} = "PROD3";
# list of program and command line parameters to execute via operating system
my @proglist='';
my $temp_sql_filename = "temp_sql.sql";
my $tempsqlcode="";
# list of files to delete
my @filelist='';
my $failover_method='';
my $STANDBY_SERVER_MOUNT_POINT ='';
my $answer='';
my $prompt2='';
my $prompt3='';
my $prompt4='';
my $prompt5='';
my $prompt6='';
my $prompt7='';
my $prompt8='';
my $prompt9='';
my $prompt10='';
my $prompt11='';
my $prompt12='';
my $prompt13='';
my $current_start_time = 0;
my $max_mtime=0;
my $archivelog_directory_path ="c:\\archive\\";
my @archivelog_directory_list=();
my @files_to_copy=();
my $filename_item=();
my $inode='';
my $inode_mtime='';
my $temp1 = '';
my $temp2 = '';
# 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;
# ask which role to switch into
print "\n";
print "--------- Oracle Standby Database Roles ---------\n";
print "Enter the option number for requested role change for this database:\n";
print "1) Primary to standby switchover.[step 1]\n";
print "2) Primary to standby failover. [step 1]\n";
print "3) Standby to primary switchover.[step 2]\n";
print "4) Standby to primary failover. [step 2]\n";
print "5) Standby to read-only\n";
print "6) Read-only to standby (resuming managed recovery).\n";
print "7) Copy archivelog files from primary to standby.[step 1]\n";
print "8) Apply missed archivelog files at standby. [step 2]\n";
print "\n";
$answer = ;
chop ($answer);
# ====================================== option 1 ======================================
if ($answer eq "1" )
{
# selected 1
# Primary to standby switchover.
print "\n";
print "Preparing to switchover primary server to standby server.\n";
print "Please perform the following tasks on the primary server:\n";
print "1) Close (or kill) all user sessions connected to the database.\n";
print "2) Network connectivity to the standby database must be maintained.\n";
print "3) The standby database must be open, and performing managed recovery.\n";
print "\n";
print "Press the return key to continue.\n";
print "\n";
$prompt2 = ;
# stop OracleStandbyStartup service for the prod3 instance - before removing
Win32::Lanman::StopService('','',"OracleStandbyStartupPROD3");
# remove OracleStandbyStartup service for the prod3 instance
Win32::Lanman::DeleteService('','',"OracleStandbyStartupPROD3");
# 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 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 perl.exe c:\\server_scripts\\prod3_standby_status.pl");
system (@proglist);
print "\n";
print "The 1st query should show TO STANDBY.\n";
print "The 2nd query the names of the users/programs logged into the database.\n";
print "[There should not be any users logged into the database.]\n";
print "[If there are users/processes/programs logged into the.]\n";
print "[database, the 1st query will show SESSIONS ACTIVE instead]\n";
print "[of TO STANDBY.]\n";
print "\n";
# stop listener and agent
&listener_and_agent_stop;
my $tempsqlcode=<<"EOF";
set echo on
connect / as SYSDBA
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SELECT SWITCHOVER_STATUS FROM V\$DATABASE;
SELECT SID, PROCESS, PROGRAM FROM V\$SESSION WHERE TYPE = 'USER' AND SID <> (SELECT DISTINCT SID FROM V\$MYSTAT);
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);
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
set echo on
-- make sure current log gets archived and automatically transferred to standby db
ALTER SYSTEM ARCHIVE LOG CURRENT;
-- shutdown and startup in restricted mode - to make sure it does not fail
SHUTDOWN IMMEDIATE
STARTUP RESTRICT
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SELECT SID, PROCESS, PROGRAM FROM V\$SESSION WHERE TYPE = 'USER' AND SID <> (SELECT DISTINCT SID FROM V\$MYSTAT);
ALTER DATABASE COMMIT TO SWITCHOVER TO 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);
# check for failure of switchover
print "\n";
print "Did the previous switchover command fail(y/n)?\n";
print "[If yes, then enter 'y' to disconnect users/programs/processes]\n";
print "[by bouncing the instance.]\n";
print "\n";
$prompt10 = ;
chop ($prompt10);
if ($prompt10 eq "y" )
{
# shut down instance to remove all user processes
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
set echo on
-- make sure current log gets archived and automatically transferred to standby db
ALTER SYSTEM ARCHIVE LOG CURRENT;
-- shutdown and startup in restricted mode - to make sure it does not fail
SHUTDOWN IMMEDIATE
STARTUP RESTRICT
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SELECT SID, PROCESS, PROGRAM FROM V\$SESSION WHERE TYPE = 'USER' AND SID <> (SELECT DISTINCT SID FROM V\$MYSTAT);
ALTER DATABASE COMMIT TO SWITCHOVER TO 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);
print "\n";
print "Please press the return key to continue.\n";
print "\n";
$answer = ;
chop ($answer);
}
# switch config files from primary to standby
&rename_files_primary_to_standby;
print "\n";
print "Now execute script c:\\server_scripts\prod3_standbydb_change_role.pl\n";
print "on the standby server.\n";
print "(Select option #3 to switch the standby server to become the primary server.)\n";
print "\n";
print "Press the return key to continue.\n";
print "\n";
$prompt4 = ;
# shutdown, re-startup instance in managed recovery role
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;
host del c:\\v901\\database\\spfilepprod3s.ora
create spfile='c:\\v901\\database\\spfilepprod3s.ora' FROM pfile='c:\\v901\\database\\initprod3s.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);
#stop and start listener and agent
&listener_and_agent_stop;
&listener_and_agent_start;
# test for Net8 connectivity to primary and standby instances with tnsping
@proglist = ("c:\\v901\\bin\\tnsping.exe prod3");
system (@proglist);
@proglist = ("c:\\v901\\bin\\tnsping.exe prod3s");
system (@proglist);
print "\n";
print "If either of the previous 2 tnsping commands failed, \n";
print "copy 70_prod3_primary_tnsnames_1.ora as c:\\v901\\network\\admin\\tnsnames.ora-primary\n";
print "copy 71_prod3_failover_tnsnames_1.ora as c:\\v901\\network\\admin\\tnsnames.ora\n";
print "copy 49_listener_ora_1.ora as c:\\v901\\network\\admin\\listener.ora\n";
print "\n";
print "Press the return key to continue.\n";
print "\n";
$prompt11 = ;
# prompt for login user change for StandbyStartupService
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";
}
# ====================================== option 2 ======================================
if ($answer eq "2" )
{
# selected 2
# Primary to standby failover.
# In a disaster - it may not be possible to run this script on the primary server.
print "\n";
print "----- This procedure is not reversable!! -----\n";
print "The standby setup will require re-instantiation\n";
print "after this server is failed over as the primary server.\n";
print "\n";
print "The primary server will be failed-over to the standby server.\n";
print "[In a disaster - it is unlikely that it will be possible]\n";
print "[to run this script on the primary server.]\n";
print "[If it is possible to run this script on the primary]\n";
print "[server, then it might be advisible to perform a ]\n";
print "[switchover instead of a failover procedure.]\n";
print "\n";
print "Press the return key to continue.\n";
print "\n";
$prompt5 = ;
# stop agent
print "\n";
print "Stopping Oracle Intelligent Agent.\n";
print "\n";
@proglist = ("c:\\v901\\bin\\agentctl.exe stop");
system (@proglist);
print "\n";
print "Attempting to archive the last redo log to the standby server.\n";
print "\n";
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
-- make sure current log gets archived and automatically transferred to standby db
ALTER SYSTEM ARCHIVE LOG CURRENT;
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);
print "\n";
print "Now execute script c:\\server_scripts\\prod3_standbydb_change_role.pl\n";
print "on the standby server.\n";
print "(This script will failover the standby server into the primary server.)\n";
print "\n";
print "Press the return key to continue.\n";
print "\n";
$prompt13 = ;
# test for Net8 connectivity to primary and standby instances with tnsping
@proglist = ("c:\\v901\\bin\\tnsping.exe prod3");
system (@proglist);
@proglist = ("c:\\v901\\bin\\tnsping.exe prod3s");
system (@proglist);
print "\n";
print "If either of the previous 2 tnsping commands failed, \n";
print "copy 70_prod3_primary_tnsnames_1.ora as c:\\v901\\network\\admin\\tnsnames.ora-primary\n";
print "copy 71_prod3_failover_tnsnames_1.ora as c:\\v901\\network\\admin\\tnsnames.ora\n";
print "copy 49_listener_ora_1.ora as c:\\v901\\network\\admin\\listener.ora\n";
print "\n\n";
print "Continue running this script on the previous\n";
print "standby server - which is now the new primary server.\n";
print "\n";
}
# ====================================== option 3 ======================================
if ($answer eq "3" )
{
# selected 3
# Standby switchover to primary role
print "\n";
print "Execute script c:\\server_scripts\\prod3_standbydb_change_role.pl\n";
print "on the standby server.\n";
print "(Select option #1 to switch the primary server into the standby server.)\n";
print "\n";
print "Press the return key to continue - after script prod3_standbydb_change_role.pl.\n";
print "has been run on the primary server.\n";
print "\n";
$prompt6 = ;
# delete the service which starts up the database in managed recovery mode
# stop OracleStandbyStartup service for the prod3 instance - before removing
Win32::Lanman::StopService('','',"OracleStandbyStartupPROD3");
# remove OracleStandbyStartup service for the prod3 instance
Win32::Lanman::DeleteService('','',"OracleStandbyStartupPROD3");
print "\n";
print "The 1st query should show TO PRIMARY.\n";
print "The 2nd query the names of the users/programs logged into the database.\n";
print "[There should not be any users logged into the database.]\n";
print "[If there are users/processes/programs logged into the.]\n";
print "[database, the 1st query will show SESSIONS ACTIVE instead]\n";
print "[of TO PRIMARY.]\n";
print "\n";
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SELECT SWITCHOVER_STATUS FROM V\$DATABASE;
SELECT SID, PROCESS, PROGRAM FROM V\$SESSION WHERE TYPE = 'USER' AND SID <> (SELECT DISTINCT SID FROM V\$MYSTAT);
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);
# check for failure - due to users logged in
print "\n";
print "Did the previous query show any users/programs/processes logged in(y/n)?.\n";
print "\n";
$prompt9 = ;
chop ($prompt9);
if ($prompt9 eq "y" )
{
# shut down instance to remove all user processes
my $tempsqlcode=<<"EOF";
CONNECT / AS SYSDBA
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 DISCONNECT FROM SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SELECT SWITCHOVER_STATUS FROM V\$DATABASE;
SELECT SID, PROCESS, PROGRAM FROM V\$SESSION WHERE TYPE = 'USER' AND SID <> (SELECT DISTINCT SID FROM V\$MYSTAT);
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);
print "\n";
print "Please press the return key to continue.\n";
print "\n";
$answer = ;
chop ($answer);
}
# rename config files - for primary use
&rename_files_standby_to_primary;
print "\n";
print "Now attempting to switchover from standby to primary.\n";
print "\n";
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SELECT SWITCHOVER_STATUS FROM V\$DATABASE;
SELECT SID, PROCESS, PROGRAM FROM V\$SESSION WHERE TYPE = 'USER' AND SID <> (SELECT DISTINCT SID FROM V\$MYSTAT);
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SHUTDOWN IMMEDIATE
STARTUP pfile='c:\\v901\\database\\initprod3.ora';
host del c:\\v901\\database\\spfileprod3.ora
create spfile='c:\\v901\\database\\spfileprod3.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);
#stop and start listener and agent
&listener_and_agent_stop;
&listener_and_agent_start;
print "\n";
print "This server is now the primary server.\n";
print "\n";
print "1) Please disable the prod3_standby_status.pl script\n";
print "which was previously scheduled via the AT scheduler.\n";
print "\n";
print "2) Continue executing script c:\\server_scripts\\prod3_standbydb_change_role.pl\n";
print "on the standby server.\n";
print "[This will complete switching the other server into the standby server.]\n";
print "\n";
print "Press the return key to continue.\n";
print "\n";
$prompt12 = ;
# test for Net8 connectivity to primary and standby instances with tnsping
@proglist = ("c:\\v901\\bin\\tnsping.exe prod3");
system (@proglist);
@proglist = ("c:\\v901\\bin\\tnsping.exe prod3s");
system (@proglist);
print "\n";
print "If either of the previous 2 tnsping commands failed, \n";
print "copy 70_prod3_primary_tnsnames_1.ora as c:\\v901\\network\\admin\\tnsnames.ora\n";
print "copy 71_prod3_failover_tnsnames_1.ora as c:\\v901\\network\\admin\\tnsnames.ora-standby\n";
print "copy 74_win_prod3_standby_listener_1.ora as c:\\v901\\network\\admin\\listener.ora\n";
print "\n";
}
# ====================================== option 4 ======================================
if ($answer eq "4" )
{
# selected 4
# Standby failover to primary role.
print "\n";
print "----- This procedure is not reversable!! -----\n";
print "The standby setup will require re-instantiation\n";
print "after this server is failed over as the primary server.\n";
print "\n";
print "[If possible, run this script on the primary server]\n";
print "[first, and select option #2 to archive and transfer]\n";
print "[the current online redo log file to this standby server.]\n";
print "\n";
print "Press the return key to continue.\n";
print "\n";
$prompt5 = ;
print "\n";
print "If option #2 of this script executed successfully on the\n";
print "primary server, this step may be skipped because the last\n";
print "archived redo log has already been transferred and applied\n";
print "to this standby server.\n";
print "\n\n";
print "Otherwise, manually copy archived redo logs from the failed primary database if possible.\n";
print "Using sqlplus in another window, manually register these files with this\n";
print "standby database using the following commands:\n";
print "(EXAMPLE) ALTER DATABASE REGISTER LOGFILE 'c:\\archive\\arch_1_101.arc';\n";
print "\n";
print "Press the return key to continue.\n";
print "\n";
$prompt6 = ;
# determine whether remaining archived logs are applied or skipped
print "Enter the option number for the failover method:\n";
print "1) Apply all archived logs, then failover.\n";
print "2) Immediate failover.\n";
print "\n";
$failover_method = ;
chop ($failover_method);
if ($failover_method eq "1" )
{
# selected 1 - apply all archived logs
print "\n";
print "Finishing recovery on the standby database.\n";
print "attempting to apply all archived redo log files.\n";
print "\n";
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
set echo on
-- finish managed recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
SHUTDOWN IMMEDIATE;
STARTUP pfile='c:\\v901\\database\\initprod3.ora';
host del c:\\v901\\database\\spfileprod3.ora
create spfile='c:\\v901\\database\\spfileprod3.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);
}
if ($failover_method eq "2" )
{
# selected 2 - skip remaining archived logs - data loss will occur
print "\n";
print "Cancelling recovery on the standby database.\n";
print "Archivelog files following the current archivelog\n";
print "file will be skipped. \n";
print "--- Data will be lost --- \n";
print "\n";
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
set echo on
-- finish managed recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
SHUTDOWN IMMEDIATE;
STARTUP pfile='c:\\v901\\database\\initprod3.ora';
host del c:\\v901\\database\\spfileprod3.ora
create spfile='c:\\v901\\database\\spfileprod3.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);
}
# rename config files - for primary use
&rename_files_standby_to_primary;
#stop and start listener and agent
&listener_and_agent_stop;
&listener_and_agent_start;
# no need to rename standby control files - rman created the standby
# controlfiles using the same names as the primary controlfiles
# delete the service which starts up the database in managed recovery mode
# stop OracleStandbyStartup service for the prod3 instance - before removing
Win32::Lanman::StopService('','',"OracleStandbyStartupPROD3");
# remove OracleStandbyStartup service for the prod3 instance
Win32::Lanman::DeleteService('','',"OracleStandbyStartupPROD3");
# use oradim to set OracleService to startup automatically upon system reboot
@proglist = ("c:\\v901\\bin\\oradim.exe -EDIT -SID PROD3 -STARTMODE a");
system (@proglist);
# re-starting database as primary - using primary db init.ora
print "\n";
print "This standby database has been failed-over as the primary database.\n";
print "Continue running this script on the previous\n";
print "primary server.\n";
print "\n";
print "Press the return key to continue.\n";
print "\n";
$prompt12 = ;
# test for Net8 connectivity to primary and standby instances with tnsping
@proglist = ("c:\\v901\\bin\\tnsping.exe prod3");
system (@proglist);
@proglist = ("c:\\v901\\bin\\tnsping.exe prod3s");
system (@proglist);
print "\n";
print "If either of the previous 2 tnsping commands failed, \n";
print "copy 70_prod3_primary_tnsnames_1.ora as c:\\v901\\network\\admin\\tnsnames.ora\n";
print "copy 71_prod3_failover_tnsnames_1.ora as c:\\v901\\network\\admin\\tnsnames.ora-standby\n";
print "copy 74_win_prod3_standby_listener_1.ora as c:\\v901\\network\\admin\\listener.ora\n";
print "\n";
}
# ====================================== option 5 ======================================
if ($answer eq "5" )
{
# selected 5 - Standby to read-only role.
print "\n";
print "Switching from standby to read-only role.\n";
print "[The archivelog file currently being applied]\n";
print "[will be completed.]\n";
print "\n";
print "Press the return key to continue.\n";
print "\n";
$prompt7 = ;
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
set echo on
SELECT process, status, thread#, sequence#, block#, blocks FROM v\$managed_standby;
-- cancel managed recovery - current logfile will be applied
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN READ ONLY;
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);
}
# ====================================== option 6 ======================================
if ($answer eq "6" )
{
# selected 6 - Read-only to standby role (resuming managed recovery).
print "\n";
print "Switching from read-only to standby role.\n";
print "Please kill all active user sessions on the database.\n";
print "\n";
print "Press the return key to continue.\n";
print "\n";
$prompt8 = ;
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
set echo on
-- shutdown database to insure all user processes have been removed
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);
}
# ====================================== option 7 ======================================
if ($answer eq "7" )
{
# selected 7
# Copy all archivelogs from primary to standby.
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
set echo on
SELECT MAX(SEQUENCE#) FROM v\$log_history;
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 archivelog files
print "\n";
print "Prepare to copy archivelog files from the primary 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";
$prompt2 = ;
chop($prompt2);
# enter name of remote mount point
print "\n";
print "Please enter the drive letter or mount point path for the\n";
print "standby server's volume:\n";
print "\n";
$STANDBY_SERVER_MOUNT_POINT = ;
chop($STANDBY_SERVER_MOUNT_POINT);
# copy all of the archivelog files from the primary to the standby server
# Note: All files within the archivelog directory will be copied. Enclosed
# directories will be skipped.
# get current time - seconds since epoch
$current_start_time = time();
$max_mtime=$current_start_time - (120);
# get directory listing of archivelogs directory
opendir(DIR1,$archivelog_directory_path) || die ("Unable to open directory: $archivelog_directory_path");
@archivelog_directory_list=readdir(DIR1);
closedir(DIR1);
# find archivelog files (and file sizes) in primary server directory modified more than 2 minutes ago
foreach $filename_item (@archivelog_directory_list)
{
$inode=stat($archivelog_directory_path.$filename_item);
$inode_mtime = (stat($archivelog_directory_path.$filename_item))[9];
if (!-d $archivelog_directory_path.$filename_item)
{
# the file is old enough to be copied - and is not a directory
push (@files_to_copy,$filename_item) if $inode_mtime < $max_mtime;
}
}
print "\n";
print "Copying files older than 2 minutes old from primary to standby...\n";
print "(Archivelog files older than 2 minutes will be copied, in order)\n";
print "(to prevent partly written files from being copied.)\n";
print "\n";
# copy files to standby server nfs mount point for archivelogs directory
foreach $filename_item (@files_to_copy)
{
$temp1 = $archivelog_directory_path.$filename_item;
$temp2 = $STANDBY_SERVER_MOUNT_POINT."\\".$filename_item;
print "Copying $temp1 to $temp2\n";
File::Copy::copy("$temp1","$temp2");
}
print "\n";
print "Run the 80_win_prod3_standbydb_change_role_1.pl script on the standby server\n";
print "and select option #8 to apply the archivelog files.\n";
print "\n";
}
# ====================================== option 8 ======================================
if ($answer eq "8" )
{
# selected 8
# Copy all archivelogs from standby database mount point - and apply archivelogs
# query standby database status
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
set echo on
SELECT process, status, thread#, sequence#, block#, blocks FROM v\$managed_standby;
SELECT MAX(SEQUENCE#) FROM v\$log_history;
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);
print "\n";
print "Please enter the full pathname to the network\n";
print "shared folder used by the primary database server to copy\n";
print "the archivelog files.\n";
print "\n";
$STANDBY_SERVER_MOUNT_POINT = ;
chop($STANDBY_SERVER_MOUNT_POINT);
# 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 $STANDBY_SERVER_MOUNT_POINT to c:\\archive\\.\n";
print "\n";
@proglist = ("xcopy /Y $STANDBY_SERVER_MOUNT_POINT\\*.ARC c:\\archive\\\\*");
system (@proglist);
# shutdown, startup database in managed recovery mode - applying all logs
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
set echo on
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
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 files from shared folder: $STANDBY_SERVER_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 $STANDBY_SERVER_MOUNT_POINT\\*.ARC");
system (@proglist);
}
}
# ---------------
# --------------- subroutines
# ---------------
# ------------- rename_files_primary_to_standby sub
sub rename_files_primary_to_standby() {
# rename Oracle configuration files from primary database to standby database role
rename ("c:\\v901\\network\\admin\\tnsnames.ora","c:\\v901\\network\\admin\\tnsnames.ora-primary");
rename ("c:\\v901\\network\\admin\\tnsnames.ora-standby","c:\\v901\\network\\admin\\tnsnames.ora");
#listener file always remains the same
#rename ("c:\\v901\\network\\admin\\listener.ora","c:\\v901\\network\\admin\\listener.ora-primary");
#rename ("c:\\v901\\network\\admin\\listener.ora-standby","c:\\v901\\network\\admin\\listener.ora");
}
# ------------- rename_files_standby_to_primary sub
sub rename_files_standby_to_primary() {
# rename Oracle configuration files from standby database to primary database role
rename ("c:\\v901\\network\\admin\\tnsnames.ora","c:\\v901\\network\\admin\\tnsnames.ora-standby");
rename ("c:\\v901\\network\\admin\\tnsnames.ora-primary","c:\\v901\\network\\admin\\tnsnames.ora");
#listener file always remains the same
#rename ("c:\\v901\\network\\admin\\listener.ora","c:\\v901\\network\\admin\\listener.ora-standby");
#rename ("c:\\v901\\network\\admin\\listener.ora-primary","c:\\v901\\network\\admin\\listener.ora");
}
# ------------- listener_and_agent_stop sub
sub listener_and_agent_stop() {
# stop agent
print "\n";
print "Stopping Oracle Intelligent Agent.\n";
print "\n";
@proglist = ("c:\\v901\\bin\\agentctl.exe stop");
system (@proglist);
# stop listener
print "\n";
print "Stopping Listener.\n";
print "\n";
@proglist = ("c:\\v901\\bin\\lsnrctl.exe stop");
system (@proglist);
}
# ------------- listener_and_agent_start sub
sub listener_and_agent_start() {
# start agent
print "\n";
print "Starting Oracle Intelligent Agent.\n";
print "\n";
@proglist = ("c:\\v901\\bin\\agentctl.exe start");
system (@proglist);
# start listener
print "\n";
print "Starting Listener.\n";
print "\n";
@proglist = ("c:\\v901\\bin\\lsnrctl.exe start");
system (@proglist);
}
