#!/usr/bin/perl

# This is free code by Jeremiah Wilton
# Please do not use without due credit
# 
# This script will perform the following steps to fail a primary
# database to its standby.
# - Check parameters are correct, and represent running SIDS
# - Verify primary is open, and standby is a standby of the same database

use Getopt::Long;
use IPC::Open3;

GetOptions(
    "from_sid=s" => \$from_sid,
    "to_sid=s" => \$to_sid,
    "debug" => \$debug,
    );

sub set_env_from_oratab {
    my ($sid, $orahome, $startup);
    open ORATAB, "/etc/oratab";
    while (<ORATAB>){
	next if /^#|^\s/;
	($sid,$orahome,$startup) = split /:/;
	$oracle_home{$sid}=$orahome;
    }
    close ORATAB;
    if ($oracle_home{$_[0]} ne '') {
	$ENV{"ORACLE_SID"} = $_[0];
	$ENV{"ORACLE_HOME"} = $oracle_home{$from_sid};
    }
    else {
	print "$_[0] has no entry in the oratab\n";
	exit 1;
    }
}

sub open_svrmgrl_session {
    my ($sid, $pid, $ifh, $ofh);
    $sid = $_[0];
    print "Opening svrmgrl session for $sid\n" if $debug;
    &set_env_from_oratab($sid);
    $infh{$sid} = 'IFH' . uc $sid;
    $outfh{$sid} = 'OFH' . uc $sid;
    $ifh = $infh{$sid};
    $ofh = $outfh{$sid};
    $pid{$sid} = open3($ifh, $ofh, '', "$ENV{ORACLE_HOME}/bin/svrmgrl");
    print { $ifh } "connect internal\n";
    while (<$ofh>) {
	print "$_\n" if $debug;
	if (/^ORA-/){
	    print $_;
	    print {$infh{$sid}} "exit\n";
	    close $infh{$sid};
	    close $outfh{$sid};
	    exit 1;
	}
	last if /Connected/;
    }
}


sub close_svrmgrl_session {
    my ($sid, $ifh, $ofh);
    $sid = $_[0];
    $ifh = $infh{$sid};
    $ofh = $outfh{$sid};
    print "Closing svrmgrl session for $sid\n" if $debug;
    print { $ifh } "exit\n";
    close $ifh;
    close $ofh;
}

sub get_state_of_instance {
    my ($datacoming, $dbname, $cftype, $sid, $ifh, $ofh);
    $sid = $_[0];
    $ifh = $infh{$sid};
    print "I'll talk to the $ifh filehandle\n" if $debug;
    $ofh = $outfh{$sid};
    print "Getting state of $sid...\n";
    print { $ifh } "select name, controlfile_type from v\$database;\n";
    while (<$ofh>) {
	print if $debug;
	if (/^ORA-/) {
	    print "$_\n" ;
	    close_svrmgrl_session($sid);
	    exit 1;
	}
	if (/^-----/) {
	    $datacoming = 'Y';
	    next;
	}
	if ($datacoming eq 'Y') {
	    chomp;
	    ($dbname, $cftype) = split /\s+/, $_;
	    print "DBNAME is $dbname CFTYPE is $cftype\n" if $debug;
	    $datacoming = '';
	}
	last if /selected/;
    }
    return ($dbname, $cftype);
}

sub shutdown_instance {
    my ($sid);
    $sid = $_[0];
    print "Shutting down $sid\n";
    $ifh = $infh{$sid};
    $ofh = $outfh{$sid};
    print { $ifh } "shutdown immediate\n";
    while (<$ofh>) {
	print if $debug;
	if (/^ORA-/) {
	    print "$_\n" ;
	    close_svrmgrl_session($sid);
	    exit 1;
	}
	last if /instance.shut.down/;
    }
}

sub dismount_instance {
    my ($sid);
    $sid = $_[0];
    print "Dismounting $sid\n";
    $ifh = $infh{$sid};
    $ofh = $outfh{$sid};
    print { $ifh } "alter database dismount;\n";
    while (<$ofh>) {
	print if $debug;
	if (/^ORA-/) {
	    print "$_\n" ;
	    close_svrmgrl_session($sid);
	    exit 1;
	}
	last if /processed/;
    }
}

sub get_current_online_log {
    my ($sid, $numlogs, $lastlog, $sequence, $ifh, $ofh, $datacoming);
    $sid = $_[0];
    $ifh = $infh{$sid};
    $ofh = $outfh{$sid};
    print "Getting current online log from $sid\n" if $debug;
    print { $ifh } "select count(*) from v\$log where status = 'CURRENT';\n";
    while (<$ofh>) {
	print if $debug;
	if (/^ORA-/) {
	    print "$_\n" ;
	    close_svrmgrl_session($sid);
	    exit 1;
	}
	if (/^-----/) {
	    $datacoming = 'Y';
	    next;
	}
	if ($datacoming eq 'Y') {
	    chomp;
	    $numlogs = s/\s+//g;
	    print "There are $numlogs active logs on $sid\n" if $debug;
	    $datacoming = '';
	}
	last if /selected/;
    }
    if ($numlogs ne '1') {
	print "Multiple online logs are active, go do something about it.\n";
	close_svrmgrl_session($sid);
	exit 1;
    }
    print { $ifh } "select sequence#, member from v\$logfile lf, v\$log l where lf.group#=l.group# and l.status = 'CURRENT' and rownum = 1;\n";
	while (<$ofh>) {
	print if $debug;
	if (/^ORA-/){
	    print "$_\n";
	    close_svrmgrl_session($sid);
	    exit 1;
	}
	if (/^-----/) {
	    $datacoming = 'Y';
	    next;
	}
	if ($datacoming eq 'Y') {
	    chomp;
	    ($foo, $sequence, $lastlog) = split /\s+/, $_;
	    print "The current online log on $sid is $lastlog, sequence number $sequence.\n";
	    $datacoming = '';
	}
	last if /selected/;
    }
    return ($sequence, $lastlog);
}

sub get_archived_redo_logs {
    my ($sid, $log, $sequence, $ifh, $ofh, %logs, $datacoming);
    $sid = $_[0];
    $ifh = $infh{$sid};
    $ofh = $outfh{$sid};
    print "Getting archived logs from $sid\n" if $debug;
    print { $ifh } "select sequence#, name from v\$archived_log order by sequence#;\n";
    while (<$ofh>) {
	print if $debug;
	last if /selected/;
	if (/^ORA-/){
	    print "$_\n";
	    close_svrmgrl_session($sid);
	    exit 1;
	}
	if (/^-----/) {
	    $datacoming = 'Y';
	    next;
	}
	if ($datacoming eq 'Y') {
	    chomp;
	    ($foo, $sequence, $log) = split /\s+/, $_;
	    $logs{$sequence} = $log;
	    print "$sequence $log\n" if $debug;
	}
    }
    return (%logs);
}

sub get_controlfile_locations {
    my ($sid, $ifh, $ofh, @cfs, $datacoming);
    $sid = $_[0];
    $ifh = $infh{$sid};
    $ofh = $outfh{$sid};
    print "Getting controlfile locations from $sid\n";
    print { $ifh } "select name from v\$controlfile;\n";
    while (<$ofh>) {
	print if $debug;
	last if /selected/;
	if (/^ORA-/){
	    print "$_\n";
	    close_svrmgrl_session($sid);
	    exit 1;
	}
	if ($datacoming eq 'Y') {
	    print;
	    chomp;
	    push @cfs, $_;
	}
	if (/^-----/) {
	    $datacoming = 'Y';
	    next;
	}
    }
    return (@cfs);
}

sub get_trace_controlfile {
    my ($sid, $filename, $ifh, $ofh, $datacoming);
    $sid = $_[0];
    $ifh = $infh{$sid};
    $ofh = $outfh{$sid};
    print "Getting trace controlfile from $sid\n";
    print { $ifh } "alter database backup controlfile to trace;\n";
    while (<$ofh>) {
	print if $debug;
	last if /processed/;
	if (/^ORA-/){
	    print "$_\n";
	    close_svrmgrl_session($sid);
	    exit 1;
	}
    }
    print { $ifh } "select distinct p.value||'/ora_'||spid||'.trc' from v\$parameter p, v\$mystat m, v\$session s, v\$process p where p.name = 'user_dump_dest' and m.sid = s.sid and s.paddr = p.addr;\n";
    while (<$ofh>) {
	print if $debug;
	last if /selected/;
	if (/^ORA-/){
	    print "$_\n";
	    close_svrmgrl_session($sid);
	    exit 1;
	}
	if (/^-----/) {
	    $datacoming = 'Y';
	    next;
	}
	if ($datacoming eq 'Y') {
	    chomp;
	    s/\s+//g;
	    $filename = $_;
	    print "Trace controlfile is $filename\n";
	}
    }
    return ($filename);
}

sub mangle_and_move_trace_controlfile {
    my ($infile, $outfile, $script, $find, $replace);
    ($infile, $find, $replace) = @_;
    print "Removing extraneous text and replacing \"$find\" with \"$replace\" in controlfile script\n";
    $outfile = "/opt/app/oracle/admin/scripts/create-controlfile-$$.sql";
    open INTRC, "$infile";
    open OUTTRC, ">$outfile";
    while (<INTRC>) {
	$script = 'Y' if /^CREATE/;
	$script = 'N' if /\;/;
	next unless ($script eq 'Y');
	s/$find/$replace/g;
	print OUTTRC $_;
    }
    print OUTTRC "/\n";
    close INTRC;
    close OUTTRC;
    print "New script is $outfile\n";
    return $outfile;
}

sub run_controlfile_script {
    my ($sid, $script, $ifh, $ofh);
    ($sid, $script) = @_;
    print "Running controlfile script on $sid\n";
    $ifh = $infh{$sid};
    $ofh = $outfh{$sid};
    print { $ifh } "\@$script\n";
    while (<$ofh>) {
	print if $debug;
	if (/^ORA-/) {
	    print "$_\n" ;
	    close_svrmgrl_session($sid);
	    exit 1;
	}
	last if /processed/;
    }
}

sub recover_and_open_database {
    my ($sid, $ifh, $ofh, $sequence, $complete);
    $sid = $_[0];
    print "Recovering database on $sid\n";
    $ifh = $infh{$sid};
    $ofh = $outfh{$sid};
    $sequence = 'undef';
    print { $ifh } "recover database until cancel\n";
    recovery: while ($complete ne 'Y') {
	svrmgrl: while (<$ofh>) {
	    print if $debug;
	    if (/^ORA-00283/) {
		print "$_\n" ;
		close_svrmgrl_session($sid);
		exit 1;
	    }
	    if (/^ORA\-00280/) {
		$sequence = (split /\#/, $_)[1];
	    }
	    if (/^Specify/) {
		print { $ifh } "$logs{$sequence}\n";
		next recovery;
	    }
	    if (/complete/) {
		$complete = 'Y';
		last svrmgrl;
	    }
	}
    }
    print "Opening database on $sid\n";
    print { $ifh } "alter database open noresetlogs;\n";
    while (<$ofh>) {
	print if $debug;
	if (/^ORA-/) {
	    print "$_\n" ;
	    close_svrmgrl_session($sid);
	    exit 1;
	}
	last if /processed/;
    }
}

sub get_standby_controlfile {
    my ($sid, $filename, $ifh, $ofh);
    $sid = $_[0];
    $ifh = $infh{$sid};
    $ofh = $outfh{$sid};
    print "Getting standby controlfile from $sid\n";
    $filename = "/opt/app/oracle/admin/scripts/standby-controlfile-$$"; 
    print { $ifh } "alter database create standby controlfile as '$filename';\n";
    while (<$ofh>) {
	print if $debug;
	if (/^ORA-/){
	    print "$_\n";
	    close_svrmgrl_session($sid);
	    exit 1;
	}
	last if /processed/;
    }
    return ($filename);
}

sub startup_standby {
    my ($sid);
    $sid = $_[0];
    print "Starting up $sid as a standby\n";
    $ifh = $infh{$sid};
    $ofh = $outfh{$sid};
    print { $ifh } "startup nomount\n";
    while (<$ofh>) {
	print if $debug;
	if (/^ORA-/) {
	    print "$_\n" ;
	    close_svrmgrl_session($sid);
	    exit 1;
	}
	last if /started/;
    }
    print { $ifh } "alter database mount standby database;\n";
    while (<$ofh>) {
	print if $debug;
	if (/^ORA-/) {
	    print "$_\n" ;
	    close_svrmgrl_session($sid);
	    exit 1;
	}
	last if /processed/;
    }
}

#main:

my ($dbname, $cftype, $sequence, $log, %logs, $word);
print "Performing graceful standby failover from $from_sid to $to_sid...\n";
print "Hit enter\n";
$word = <STDIN>;
print "Opening Server Manager sessions with both SIDs...\n";
foreach $target ($from_sid, $to_sid) {
    print "Calling open_svrmgrl_session($target)\n" if $debug;
    open_svrmgrl_session($target);
    print "Input filehandle for $target is $infh{$target}\n" if $debug;
    print "Output filehandle for $target is $outfh{$target}\n" if $debug;
}
print "Hit enter\n";
$word = <STDIN>;
print "Getting state of both instances...\n";
($dbname{$from_sid}, $cftype{$from_sid}) = get_state_of_instance($from_sid);
print "$from_sid is mounting $dbname{$from_sid} with a $cftype{$from_sid} controlfile.\n";

($dbname{$to_sid}, $cftype{$to_sid}) = get_state_of_instance($to_sid);
print "$to_sid is mounting $dbname{$to_sid} with a $cftype{$to_sid} controlfile.\n";

if ($dbname{$from_sid} ne $dbname{$to_sid}) {
    print "Both primary and standby must be mounting databases with the same name\n";
    exit 1;
}
elsif ($cftype{$from_sid} ne 'CURRENT') {
    print "Primary database must have a current controlfile\n";
    exit 1;
}
elsif ($cftype{$to_sid} ne 'STANDBY') {
    print "Standby database must have a standby controlfile\n";
    exit 1;
}

print "Hit enter\n";
$word = <STDIN>;

($sequence, $log) = get_current_online_log($from_sid);
%logs = get_archived_redo_logs($from_sid);
$logs{$sequence} = $log;
print "List of redologs for $from_sid:\n";
foreach (sort {$a <=> $b} keys %logs) {
    print "$_: $logs{$_}\n";
}

print "Hit enter\n";
$word = <STDIN>;
@controlfiles = get_controlfile_locations($from_sid);

print "Hit enter\n";
$word = <STDIN>;
$tracefile = get_trace_controlfile($from_sid);

print "Hit enter\n";
$word = <STDIN>;
$cf_script = mangle_and_move_trace_controlfile($tracefile, $from_sid, $to_sid);

print "Hit enter\n";
$word = <STDIN>;
shutdown_instance($from_sid);

print "Hit enter\n";
$word = <STDIN>;
dismount_instance($to_sid);

print "Hit enter\n";
$word = <STDIN>;
run_controlfile_script($to_sid, $cf_script);

print "Hit enter\n";
$word = <STDIN>;
recover_and_open_database($to_sid);

print "Hit enter\n";
$word = <STDIN>;
$sbcontrolfile = get_standby_controlfile($to_sid);

print "Hit enter\n";
$word = <STDIN>;
foreach (@controlfiles) {
    print "Copying $sbcontrolfile\n     to $_\n";
    system "cp $sbcontrolfile $_";
}

print "Hit enter\n";
$word = <STDIN>;
startup_standby($from_sid);

print "\nFailover complete; $to_sid is now the primary, and $from_sid is the standby.\n";