Installgen
Features and Benefits
Installgen
Demo Available for download...
#!/usr/local/bin/perl
# script: /opt/server_scripts\prod1_standbydb_change_role.pl (renamed from 80_sol_prod1_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
# 05-21-2002 dsimpson Updated for use on Solaris.
# 10-21-2002 dsimpson Updated for use on Mac OS X.
# Added updating of ORACLE_HOME ENV var after
# swapping /etc/profile.
# 11-09-2002 dsimpson Corrected Oracle startup file pathnames
# used during switchover/failover.
# Changed Perl getlogin() to ENV{'LOGNAME'}
# when verifying logged in user running this script.
# This output file was created by Installgen version 1.38 on Sun Nov 10 14:51:56 2002. By .com Solutions Inc. www.dotcomsolutionsinc.net
use strict;
use File::Copy;
# define subs
sub rename_files_primary_to_standby();
sub rename_files_standby_to_primary();
sub listener_and_agent_stop();
sub listener_and_agent_start();
sub get_hostname();
# insure that environment variable is used by this perl script
# -- disable environment variable for UNIX servers - use the shell's variables from /etc/profile --
#$ENV{'ORACLE_SID'} = "PROD1";
# 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 $server_hostname='';
my $current_start_time = 0;
my $max_mtime=0;
my $archivelog_directory_path ="/archive/";
my $destination_directory_path="/archive/" . "prod1s" . "_archivelog_standby_mnt";
my @archivelog_directory_list=();
my @files_to_copy=();
my $filename_item=();
my $inode='';
my $inode_mtime='';
# make sure the oracle user is running this script
my ($login_name) = $ENV{'LOGNAME'};
if ($login_name ne "oracle")
{
# the user running this script is not oracle - exit
print "You must be logged in as the oracle user to run this script, exiting....";
# exit immediately!
die
}
&get_hostname;
# 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.
# set env var - to primary SID
#$ENV{'ORACLE_SID'} = "PROD1";
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";
$prompt2 = ;
# rename standby startup script to enable its use on startup
rename ("/etc/rc3.d/disabled-S81_prod1_standby_startup_script","/etc/rc3.d/S81_prod1_standby_startup_script");
# create CRON jobs for execution of monitoring and file rotation scripts
print "\n";
print "From a oracle UNIX terminal session, enter the following CRON jobs:\n";
print " ******** Monitor standby database status nightly at 11:50PM ********\n";
print "50 23 * * 0-6 /opt/server_scripts/prod1_standby_status.pl\n";
print "\n";
print "Enter a CRON job for either (but not both) of the following scripts:\n";
print " ******** Move Primary Database Archivelogs Every 4 Hours ********\n";
print "02 0,4,8,12,16,20 * * 0-6 /opt/server_scripts/prod1_archivelog_move_job.pl\n";
print "\n";
print " ******** RMAN Backup of Archivelogs Every 4 Hours ********\n";
print "02 0,4,8,12,16,20 * * 0-6 /opt/server_scripts/prod1_rman_archivelog_backup_job.sh\n";
print "\n";
print "Press the Return key after these tasks have been completed.\n";
$answer = ;
print "\n";
print "The 1st query which follows should show TO STANDBY.\n";
print "The 2nd query should show 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 - to stop DBSNMP account accesses
&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);
system ("/u01/v901/bin/sqlplus /nolog \@$temp_sql_filename");
print "\n";
print "Commit to switchover primary to standby.\n";
print "\n";
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
set echo on
-- shutdown and startup in restricted mode - to make sure it does not fail
--SHUTDOWN IMMEDIATE
--STARTUP RESTRICT
-- make sure current log gets archived and automatically transferred to standby db
ALTER SYSTEM ARCHIVE LOG CURRENT;
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;
SHUTDOWN IMMEDIATE
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 ("/u01/v901/bin/sqlplus /nolog \@$temp_sql_filename");
# switch config files from primary to standby
&rename_files_primary_to_standby;
print "\n";
print "Using a root terminal session, execute the following commands:\n";
print "\n";
print "mv /etc/profile /etc/profile-primary\n";
print "mv /etc/profile-standby /etc/profile\n";
print "\n";
print "Press the return key to continue.\n";
$prompt4 = ;
print "\n";
print "Execute script /opt/server_scripts/prod1_standbydb_change_role.pl\n";
print "on the standby server.\n";
print "(Select option #3 to switch the standby server into 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
STARTUP NOMOUNT pfile='/u01/v901/dbs/initprod1s.ora';
ALTER DATABASE MOUNT STANDBY DATABASE;
-- DELAY 0 for no delay
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 240 PARALLEL 8 DISCONNECT FROM SESSION;
SELECT process, status, thread#, sequence#, block#, blocks FROM v\$managed_standby;
host rm /u01/v901/database/spfilepprod1s.ora
create spfile='/u01/v901/dbs/spfileprod1s.ora' FROM pfile='/u01/v901/dbs/initprod1s.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);
# set standby SID ENV variable value for ORACLE_SID before starting sqlplus
system ("ORACLE_SID=prod1s;export ORACLE_SID;/u01/v901/bin/sqlplus /nolog \@$temp_sql_filename");
#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
system ("/u01/v901/bin/tnsping prod1");
system ("/u01/v901/bin/tnsping prod1s");
print "\n";
print "hostname=$server_hostname\n";
print "If either of the previous 2 tnsping commands failed, \n";
if ($server_hostname eq "blade1")
{
print "cp /export/oracle/70_prod1_primary_tnsnames_1.ora /u01/v901/network/admin/tnsnames.ora-primary\n";
print "cp /export/oracle/71_prod1_failover_tnsnames_1.ora /u01/v901/network/admin/tnsnames.ora\n";
print "cp /export/oracle/89_sol_primary_standby_listener_1.ora /u01/v901/network/admin/listener.ora\n";
print "cp /export/oracle/49_listener_ora_1.ora /u01/v901/network/admin/listener.ora-primary\n";
}
if ($server_hostname eq "ultra1")
{
print "cp /export/oracle/70_prod1_primary_tnsnames_1.ora /u01/v901/network/admin/tnsnames.ora-primary\n";
print "cp /export/oracle/71_prod1_failover_tnsnames_1.ora /u01/v901/network/admin/tnsnames.ora\n";
print "cp /export/oracle/88_sol_standby_primary_listener_1.ora /u01/v901/network/admin/listener.ora-primary\n";
print "cp /export/oracle/74_sol_prod1_standby_listener_1.ora /u01/v901/network/admin/listener.ora\n";
}
print "\n";
print "\n";
print "Please re-login any oracle terminal sessions using:\n";
print "su - oracle\n";
print "in order to use the updated ENV variables from /etc/profile.\n"; print "\n";
print "Press the return key to continue.\n";
print "\n";
$prompt11 = ;
# done with primary to standby switchover tasks on primary server
}
# ====================================== 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 reversible!! -----\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";
system ("/u01/v901/bin/agentctl stop");
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);
system ("/u01/v901/bin/sqlplus /nolog \@$temp_sql_filename");
#stop listener and agent
&listener_and_agent_stop;
# swap /etc/profile files - before startup
print "\n";
print "Using a root terminal session, execute the following commands:\n";
print "\n";
print "mv /etc/profile /etc/profile-primary\n";
print "mv /etc/profile-standby /etc/profile\n";
print "\n";
print "Press the return key to continue.\n";
$prompt4 = ;
# switch config files from primary to standby
&rename_files_primary_to_standby;
#start listener and agent
&listener_and_agent_start;
print "\n";
print "Now execute script /opt/server_scripts/prod1_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
system ("/u01/v901/bin/tnsping prod1");
system ("/u01/v901/bin/tnsping prod1s");
print "\n";
print "hostname=$server_hostname\n";
print "If either of the previous 2 tnsping commands failed, \n";
if ($server_hostname eq "blade1")
{
print "cp /export/oracle/70_prod1_primary_tnsnames_1.ora /u01/v901/network/admin/tnsnames.ora-primary\n";
print "cp /export/oracle/71_prod1_failover_tnsnames_1.ora /u01/v901/network/admin/tnsnames.ora\n";
print "cp /export/oracle/89_sol_primary_standby_listener_1.ora /u01/v901/network/admin/listener.ora\n";
print "cp /export/oracle/49_listener_ora_1.ora /u01/v901/network/admin/listener.ora-primary\n";
}
if ($server_hostname eq "ultra1")
{
print "cp /export/oracle/70_prod1_primary_tnsnames_1.ora /u01/v901/network/admin/tnsnames.ora-primary\n";
print "cp /export/oracle/71_prod1_failover_tnsnames_1.ora /u01/v901/network/admin/tnsnames.ora\n";
print "cp /export/oracle/88_sol_standby_primary_listener_1.ora /u01/v901/network/admin/listener.ora-primary\n";
print "cp /export/oracle/74_sol_prod1_standby_listener_1.ora /u01/v901/network/admin/listener.ora\n";
}
print "\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
# set env var - to standby SID
#$ENV{'ORACLE_SID'} = "PROD1S";
print "\n";
print "Execute script /opt/server_scripts/prod1_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 prod1_standbydb_change_role.pl.\n";
print "has been run on the primary server.\n";
print "\n";
$prompt6 = ;
# rename standby startup script to disable its use on startup - this will be the new primary server
rename ("/etc/rc3.d/S81_prod1_standby_startup_script","/etc/rc3.d/disabled-S81_prod1_standby_startup_script");
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);
system ("/u01/v901/bin/sqlplus /nolog \@$temp_sql_filename");
# 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='/u01/v901/dbs/initprod1s.ora';
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
-- DELAY 0 for no delay
--ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 0 DISCONNECT FROM SESSION;
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);
system ("/u01/v901/bin/sqlplus /nolog \@$temp_sql_filename");
print "\n";
print "Please press the return key to continue.\n";
print "\n";
$answer = ;
chop ($answer);
}
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
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 ("/u01/v901/bin/sqlplus /nolog \@$temp_sql_filename");
# rename config files - for primary use
&rename_files_standby_to_primary;
print "\n";
print "Using a root terminal session, execute the following commands:\n";
print "\n";
print "mv /etc/profile /etc/profile-standby\n";
print "mv /etc/profile-primary /etc/profile\n";
print "\n";
print "Press the return key to continue.\n";
print "\n";
$prompt4 = ;
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
-- check for symlinks if this startup fails
STARTUP pfile='/u01/v901/dbs/initprod1.ora';
host rm /u01/v901/dbs/spfileprod1.ora
create spfile='/u01/v901/dbs/spfileprod1.ora' FROM pfile='/u01/v901/dbs/initprod1.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);
# set primary SID ENV variable value for ORACLE_SID before starting sqlplus
system ("ORACLE_SID=prod1;export ORACLE_SID;/u01/v901/bin/sqlplus /nolog \@$temp_sql_filename");
#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 prod1_standby_status.pl script\n";
print "which was previously scheduled via CRON.\n";
print "\n";
print "2) Continue executing script /opt/server_scripts/prod1_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 "3) Please re-login any oracle terminal sessions using:\n";
print "su - oracle\n";
print "in order to use the updated ENV variables from /etc/profile.\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
system ("/u01/v901/bin/tnsping prod1");
system ("/u01/v901/bin/tnsping prod1s");
print "\n";
print "hostname=$server_hostname\n";
print "If either of the previous 2 tnsping commands failed, \n";
if ($server_hostname eq "blade1")
{
print "cp /export/oracle/70_prod1_primary_tnsnames_1.ora /u01/v901/network/admin/tnsnames.ora\n";
print "cp /export/oracle/71_prod1_failover_tnsnames_1.ora /u01/v901/network/admin/tnsnames.ora-standby\n";
print "cp /export/oracle/89_sol_primary_standby_listener_1.ora /u01/v901/network/admin/listener.ora-standby\n";
print "cp /export/oracle/49_listener_ora_1.ora /u01/v901/network/admin/listener.ora\n";
}
if ($server_hostname eq "ultra1")
{
print "cp /export/oracle/70_prod1_primary_tnsnames_1.ora /u01/v901/network/admin/tnsnames.ora-standby\n";
print "cp /export/oracle/71_prod1_failover_tnsnames_1.ora /u01/v901/network/admin/tnsnames.ora\n";
print "cp /export/oracle/88_sol_standby_primary_listener_1.ora /u01/v901/network/admin/listener.ora\n";
print "cp /export/oracle/74_sol_prod1_standby_listener_1.ora /u01/v901/network/admin/listener.ora-standby\n";
}
print "\n";
}
# ====================================== option 4 ======================================
if ($answer eq "4" )
{
# selected 4
# Standby failover to primary role.
print "\n";
print "----- This procedure is not reversible!! -----\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 '/archive/arch_1_101.arc';\n";
print "\n";
print "Press the return key to continue.\n";
print "\n";
$prompt6 = ;
#stop listener and agent - prevent access until ready
&listener_and_agent_stop;
# 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
host ORACLE_SID=prod1s
host export ORACLE_SID
-- finish managed recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
SHUTDOWN IMMEDIATE;
host rm /u01/v901/dbs/spfileprod1.ora
create spfile='/u01/v901/dbs/spfileprod1.ora' FROM pfile='/u01/v901/dbs/initprod1.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);
system ("/u01/v901/bin/sqlplus /nolog \@$temp_sql_filename");
#stop listener and agent
&listener_and_agent_stop;
# swap /etc/profile files - before startup
print "\n";
print "Using a root terminal session, execute the following commands:\n";
print "\n";
print "mv /etc/profile /etc/profile-standby\n";
print "mv /etc/profile-primary /etc/profile\n";
print "\n";
print "Press the return key to continue.\n";
$prompt4 = ;
# swap listener.ora and tnsnames.ora files - before startup
# switch config files from standby to primary
&rename_files_standby_to_primary;
#start listener and agent
&listener_and_agent_start;
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
set echo on
STARTUP pfile='/u01/v901/dbs/initprod1.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);
# set primary SID ENV variable value for ORACLE_SID before starting sqlplus
system ("ORACLE_SID=prod1;export ORACLE_SID;/u01/v901/bin/sqlplus /nolog \@$temp_sql_filename");
}
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";
# re-starting database as primary - using primary db init.ora
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='/u01/v901/dbs/initprod1.ora';
host rm /u01/v901/dbs/spfileprod1.ora
create spfile='/u01/v901/dbs/spfileprod1.ora' FROM pfile='/u01/v901/dbs/initprod1.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);
system ("/u01/v901/bin/sqlplus /nolog \@$temp_sql_filename");
}
# 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
# disable the script which starts up the database in managed recovery mode
rename ("/etc/rc3.d/S81_prod1_standby_startup_script","/etc/rc3.d/disabled-S81_prod1_standby_startup_script");
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
system ("/u01/v901/bin/tnsping prod1");
system("/u01/v901/bin/tnsping prod1s");
print "\n";
print "hostname=$server_hostname\n";
print "If either of the previous 2 tnsping commands failed, \n";
if ($server_hostname eq "blade1")
{
print "cp /export/oracle/70_prod1_primary_tnsnames_1.ora /u01/v901/network/admin/tnsnames.ora\n";
print "cp /export/oracle/71_prod1_failover_tnsnames_1.ora /u01/v901/network/admin/tnsnames.ora-standby\n";
print "cp /export/oracle/89_sol_primary_standby_listener_1.ora /u01/v901/network/admin/listener.ora-standby\n";
print "cp /export/oracle/49_listener_ora_1.ora /u01/v901/network/admin/listener.ora\n";
}
if ($server_hostname eq "ultra1")
{
print "cp /export/oracle/70_prod1_primary_tnsnames_1.ora /u01/v901/network/admin/tnsnames.ora-standby\n";
print "cp /export/oracle/71_prod1_failover_tnsnames_1.ora /u01/v901/network/admin/tnsnames.ora\n";
print "cp /export/oracle/88_sol_standby_primary_listener_1.ora /u01/v901/network/admin/listener.ora\n";
print "cp /export/oracle/74_sol_prod1_standby_listener_1.ora /u01/v901/network/admin/listener.ora-standby\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);
system ("/u01/v901/bin/sqlplus /nolog \@$temp_sql_filename");
}
# ====================================== 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='/u01/v901/dbs/initprod1s.ora';
ALTER DATABASE MOUNT STANDBY DATABASE;
-- DELAY 0 for no delay
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 240 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);
system ("/u01/v901/bin/sqlplus /nolog \@$temp_sql_filename");
}
# ====================================== 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);
system ("/u01/v901/bin/sqlplus /nolog \@$temp_sql_filename");
# 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 NFS.\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 " (This should have already been done during the setup process.)\n";
print "\n";
print "Please press the return key after these steps have been done.\n";
print "\n";
$prompt2 = ;
chop($prompt2);
# 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 "\n";
# copy files to standby server nfs mount point for archivelogs directory
foreach $filename_item (@files_to_copy)
{
my $temp1 = $archivelog_directory_path.$filename_item;
my $temp2 = $destination_directory_path."/".$filename_item;
print "Copying $temp1 to $temp2\n";
File::Copy::copy("$temp1","$temp2");
}
print "\n";
print "Run the /opt/server_scripts/prod1_standbydb_change_role.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);
system ("/u01/v901/bin/sqlplus /nolog \@$temp_sql_filename");
print "\n";
print "Ready to apply archivelog files from the primary server.\n";
print "Execute script /opt/server_scripts/prod1_standbydb_change_role.pl on the \n";
print "primary server to copy the archivelog files to the archivelog \n";
print "directory of the standby server.\n";
print "\n";
print "Once this has been done, press the Return key to continue.\n";
print "\n";
$answer = ;
# 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 CANCEL;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT pfile='/u01/v901/dbs/initprod1s.ora';
ALTER DATABASE MOUNT STANDBY DATABASE;
-- DELAY 0 for no delay
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 240 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);
system ("/u01/v901/bin/sqlplus /nolog \@$temp_sql_filename");
print "\n";
print "Finished applying archivelog files to standby server.\n";
print "\n";
}
# ---------------
# --------------- 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 ("/u01/v901/network/admin/tnsnames.ora","/u01/v901/network/admin/tnsnames.ora-primary");
rename ("/u01/v901/network/admin/tnsnames.ora-standby","/u01/v901/network/admin/tnsnames.ora");
rename ("/u01/v901/network/admin/listener.ora","/u01/v901/network/admin/listener.ora-primary");
rename ("/u01/v901/network/admin/listener.ora-standby","/u01/v901/network/admin/listener.ora");
rename ("/var/opt/oracle/oratab","/var/opt/oracle/oratab-primary");
rename ("/var/opt/oracle/oratab-standby","/var/opt/oracle/oratab");
}
# ------------- rename_files_standby_to_primary sub
sub rename_files_standby_to_primary() {
# rename Oracle configuration files from standby database to primary database role
rename ("/u01/v901/network/admin/tnsnames.ora","/u01/v901/network/admin/tnsnames.ora-standby");
rename ("/u01/v901/network/admin/tnsnames.ora-primary","/u01/v901/network/admin/tnsnames.ora");
rename ("/u01/v901/network/admin/listener.ora","/u01/v901/network/admin/listener.ora-standby");
rename ("/u01/v901/network/admin/listener.ora-primary","/u01/v901/network/admin/listener.ora");
rename ("/var/opt/oracle/oratab","/var/opt/oracle/oratab-standby");
rename ("/var/opt/oracle/oratab-primary","/var/opt/oracle/oratab");
}
# ------------- listener_and_agent_stop sub
sub listener_and_agent_stop() {
# stop agent
print "\n";
print "Stopping Oracle Intelligent Agent.\n";
print "\n";
system ("/u01/v901/bin/agentctl stop");
# stop listener
print "\n";
print "Stopping Listener.\n";
print "\n";
system ("/u01/v901/bin/lsnrctl stop");
}
# ------------- listener_and_agent_start sub
sub listener_and_agent_start() {
# start agent
print "\n";
print "Starting Oracle Intelligent Agent.\n";
print "\n";
system ("/u01/v901/bin/agentctl start");
# start listener
print "\n";
print "Starting Listener.\n";
print "\n";
system ("/u01/v901/bin/lsnrctl start");
}
# ------------- get_hostname sub
sub get_hostname() {
# get hostname from orahome/network/admin/hostname file
my $hostname_path_full = "/u01/v901" . "/network/admin/hostname";
open (FILE1,"$hostname_path_full") || die ("Could not open input file $hostname_path_full for writing.
Does the full directory path exist?");
$server_hostname = ;
print "hostname=$server_hostname";
close FILE1;
}
