Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: creating standby database

RE: creating standby database

From: Reddy, Gautam <Gautam_Reddy_at_Dell.com>
Date: Wed, 13 Sep 2000 15:07:59 -0500
Message-Id: <10618.116894@fatcity.com> 





Sorry could not post it earlier was out of office.


#!/usr/local/opt/oracle/contrib/bin/expect- 




# 

# Script Name: db_rollfw 

# Global Variables 

# Variables that we will globalize in a function if it is needed 

# In tcl all variable must be set before using 


set STD(PROG) [file tail $argv0]        ;# Program name 
set STD(DIR) [glob -- ~oracle/] ;# Backup working directory 
set STD(TMP) /tmp               ;# Temp directory 
set STD(BIN) $STD(DIR)/local/bin                ;# Binary Directory 
set STD(FPATH) $STD(DIR)/local/func             ;# Function Directory 
set STD(CONF) /var/opt/oracle   ;# Charmer's config file directory 
set STD(VERBOSE) 0 
set STD(DEBUG) 0 
set STD(RCVRCNT) 0                      ;# The number of files currently


recovered 


set stable 0 


set fuser_cmd /usr/local/bin/su_fuser 


set STD(ARCH_EXT) arc 



proc check_stop {} { 


# This function checks to see if I should stop by 

# checking for the existance of a stop file and 

# by checking against the current time 



global STD 


global need_file 


upvar stop stop 



set stop_var "stop" 


set primary_var "primary" 



set fname "check_stop" 


if {[info exists stop]} {return 0} 


if { $STD(VERBOSE) == 1 } { 


puts "\[$STD(PROG)]  INFO:  Checking for a stop condition" 
  } 


if {![file exists $STD(TMP)/stop_recovery]} { 
if { $STD(VERBOSE) == 1 } { 


puts "\[$STD(PROG)]  INFO: File $STD(TMP)/stop_recovery does not exist, co 
ntinueing\n" 


    } 


return 0 


  } 


set ptr [open $STD(TMP)/stop_recovery r] 
set stop_point [gets $ptr] 


close $ptr 


  # 


# Compare this stop_point with the test var 

if {$stop_var == $stop_point} { 


if { $STD(VERBOSE) == 1 } { 


puts "\[$STD(PROG)]  INFO: Stopping database recovery"} 
set stop 1 


send "cancel\r" 


exec $STD(ORANOTIFY) -n $STD(PROG) -s $STD(STANDBY_ORACLE_SID) -l CRITICAL
-m "Recovery Canceled" 


return 0 


} else { 


if {$primary_var == $stop_point} { 


if { $STD(VERBOSE) == 1 } { 

puts "\[$STD(PROG)]  INFO: Starting standby as primary database" 
puts "The database will be made primary, mounted, opened with reset logs," 
puts "and then shutdown. Please do a backup before you reopen the database"}





set stop 1 


send "CANCEL;\r" 


expect -re "\nSVRMGR> " {send "alter database activate standby database;\r"}


expect -re "\nSVRMGR> " {send "shutdown;\r"} 
expect -re "\nSVRMGR> " {send "startup restrict;\r"} 
expect -re "\nSVRMGR> " {send "exit;\r"} 


exec $STD(ORANOTIFY) -n $STD(PROG) -s $STD(STANDBY_ORACLE_SID) -l CRITICAL
-m "Recovery Stopped Started Primary. Please do a backup." 
} else { 


exec $STD(ORANOTIFY) -n $STD(PROG) -s $STD(STANDBY_ORACLE_SID) -l CRITICAL
-m "You must put the work stop in the stop_recovery file" 
if { $STD(VERBOSE) == 1 } { 


puts "\nYou must put the work stop in the stop_recovery file\n" 


    } 


return 0 


  } 


  } 


} 



proc usage {} { 


   # 


# Print simple usage statement and return 

global STD 


global ERRAY 



puts "\nUsage:  $STD(PROG) -s STANDBY_ORACLE_SID" 
puts "" 

        puts "   -s STANDBY_ORACLE_SID  Name of the oracle database" 
        puts "   -v                 verbose output" 


puts "" 


exec $STD(ORANOTIFY) -n $STD(PROG) -s $STD(STANDBY_ORACLE_SID) -l CRITICAL
-m "Usage:  $STD(PROG) -s STANDBY_ORACLE_SID Restart Recovery" 
exit $ERRAY(EPARSE) 


} 



proc parse_args {} { 


        # 


# Outputs: 


        #   STANDBY_ORACLE_SID 
        #   VERBOSE 
        #   DEBUG 

        # 




# Global variables 



global STD 


global argv 


global argc 


set fname "parse_args" 



# Process the command line arguments 

# switch 

# switch is similar to case, it checks the variable given to it against a

list 


# of values and executes the command(s) associated with the value upon a

match 


# here I am using exact because I don't want switch to try and pattern match




# the-lets switch know I am finished passing switches 

set count 0 


while {$count < $argc} { 


switch -exact --  [lindex $argv $count] { 
* s     { 


incr count 


set STD(STANDBY_ORACLE_SID) [lindex $argv $count] 
incr count 

                        } 
* v     { 




set STD(VERBOSE) 1 


incr count 

                        } 
* d     { 




set STD(DEBUG) 1 


incr count 

                        } 
                        default { usage } 
                } 
        } 




return 0 


} 



proc require_vars {variables} { 


global ERRAY 


foreach var $variables { 


if {[regexp {^(.*)(\(.*\))} $var junk globme]} { 
global $globme 


} else { 


global $var 

                } 




if {![info exists $var]} {error "Variable $var does not exist"} 
if {[string length "[set $var]"] < 1} {error "Variable $var is NULL"} 


        } 


} 



proc parse_conf {} { 


# Parse the config file, the config file is

$CONF/standby_$STANDBY_ORACLE_SID.cfg 


        # 


# Global vars 



global STD 


global ERRAY 



set fname "parse_conf" 


if { $STD(VERBOSE) == 1 } { puts "\[$STD(PROG)]\[$fname]: INFO  Parsing the
config file"} 


set config $STD(CONF)/standby_$STD(STANDBY_ORACLE_SID).cfg 
if {[catch {open $config r} file]} {error $file} 
foreach line [split [read $file] \n] { 


if {![regexp -- {^[a-z]|^[A-Z]} $line]} {continue} 
switch -exact -- [lindex $line 0] \ 


STANDBY_ORACLE_SID {set STD(STANDBY_ORACLE_SID) [lindex $line 1]} \ 
ORACLE_HOME { 



set STD(ORACLE_BIN) [lindex $line 1]/bin 
set STD(ORACLE_HOME) [lindex $line 1] 

                        } \ 
ORANOTIFY {set STD(ORANOTIFY) [lindex $line 1]} \ 
        } 




set STD(LOG) $STD(DIR)/admin/$STD(STANDBY_ORACLE_SID)/standby   ; 
catch {close $file} 


} 


####################################################################### 


#                                                                     # 

#                   db_rollfw MAIN                                    # 

#                                                                     # 

####################################################################### 





# 

# Source the exit codes function 



source $STD(FPATH)/exit_codes.tcl 



# 

# Execute the exit_codes function 



exit_codes 


# 

# Parse the command line 



parse_args 


# 

# Parse the config file 

# catch-In a TCL program errors are not tolerated, if you 

# recieve an error, the program will exit with an error message, 

# (in expect you get a debug stack also).  If an external process returns 

# non-zero or writes to STDERR TCL interprets this as a failure. 

# Therefore you must anticipate failures.  That's what catch does, 

# it catches errors.  Catch will return a 0 if no errors are caught 

# and a 1 otherwise.  The syntax is catch {command and all args} 

# ?varname?.  Varname is optional and when present it will hold 

# either the error generated (caught) of the resulting output of the 

# program if it terminates normally. 



if {[catch parse_conf result]} { 


puts "\[$STD(PROG)] ERROR: $result" exec $STD(ORANOTIFY) -n $STD(PROG) -s
$STD(STANDBY_ORACLE_SID) -l CRITICAL -m "ERROR: $result" exit $ERRAY(EPARSE)



} 



# 

# Check for the existence of required variables 

# To add to this list put the variable name inside the square brackets []

without 


# the $ dereferencer 

# set required {STD(ORACLE_BIN)} 



set required {STD(STANDBY_ORACLE_SID) STD(ORACLE_BIN) STD(ORACLE_HOME)} 
if {[catch {require_vars $required} result]} { 
puts "\[$STD(PROG)] ERROR: $result" exec $STD(ORANOTIFY) -n $STD(PROG) -s
$STD(STANDBY_ORACLE_SID) -l CRITICAL -m "ERROR: $result" exit $ERRAY(EPARSE)



} 


# while there are logs roll the database forward and wait for the next 

if {$STD(VERBOSE) == 0} { 


log_user 0 


} 


set env(ORACLE_SID) $STD(STANDBY_ORACLE_SID) 
set env(ORACLE_HOME) $STD(ORACLE_HOME) 


spawn $STD(ORACLE_BIN)/svrmgrl 


set sql_id $spawn_id 


# 

# connect internal 



if { $STD(VERBOSE) == 1 } { puts "\n\[$STD(PROG)]  INFO: Connecting to the
database $STD(STANDBY_ORACLE_SID)\n"} 


expect "\nSVRMGR> " 


send "connect internal;\r" 


# 

# startup the database 



if { $STD(VERBOSE) == 1 } { puts "\n\[$STD(PROG)]  INFO: Starting up
database $STD(STANDBY_ORACLE_SID)\n"} 


expect "\nSVRMGR> " 


send "startup nomount;\r" 


# 

# Start the recovery 

# 



if { $STD(VERBOSE) == 1 } { puts "\n\[$STD(PROG)]  INFO: Initiating Database
recovery\n"} 


expect "\nSVRMGR> " 


send "alter database mount standby database exclusive;\r" 
expect -re "\nSVRMGR> " 


send "recover standby database until cancel;\r" 
while {![info exists stop]} { 


expect { 


* re "\nORA-00289: suggestion : (/.*.$STD(ARCH_EXT))\r\n" { 
set need_file $expect_out(1,string) set indx [llength [split $need_file
"/"]] set indx [expr $indx - 1] set file_name [lindex [split $need_file "/"]
$indx] 


if {$STD(RCVRCNT) > 0} { 


expect -re "ORA-00278: Logfile '(.*)' no longer.*CANCEL.\r\n" { 


# I should be removing the file here, but I am not for testing 

if { $STD(VERBOSE) == 1 } { 


puts "\[$STD(PROG)]  INFO:  Removing file $expect_out(1,string)" 

                                        } 


set ttime [expr [clock seconds] - $stime] 


# exec rm $expect_out(1,string) 



#exec ksh $STD(BIN)/rm_applied_log $expect_out(1,string) $ttime 
if { $STD(VERBOSE) == 1 } { 


puts "It took $ttime seconds to load log [file tail $expect_out(1,string)]."


                                        } 
                                } 




} else { 


expect -re ".*" 

                  } 
          } 




  } 


set CheckForFile $STD(LOG)/$file_name 


while {![file exists $CheckForFile] && ![info exists stop]} { 
if { $STD(VERBOSE) == 1 } { 


puts -nonewline "\[$STD(PROG)]  INFO:  The file \"$need_file\" " 
puts "does not exist waiting for it" 

                } 




sleep 15 


check_stop 


        } 


check_stop 


if {[info exists stop]} { 


if { $STD(VERBOSE) == 1 } { puts "\[$STD(PROG)]  INFO: Script Completed" } 
} else { 


if { $STD(VERBOSE) == 1 } { 


puts "\[$STD(PROG)]  INFO:  The file \"$need_file\" is being loaded" 

                } 




exec rm $CheckForFile 


set stime [clock seconds] 


send "\n" 


incr STD(RCVRCNT) 


  } 


} 


exec rm $STD(TMP)/stop_recovery 


#puts "Recovered $STD(RCVRCNT) redo logs" 
exec $STD(ORANOTIFY) -n $STD(PROG) -s $STD(STANDBY_ORACLE_SID) -l CRITICAL
-m "The recovery script has completed. Recovered $STD(RCVRCNT) redo logs" 
exit 



Thx


Gautam 





-----Original Message-----


From: Kenneth Berland [mailto:ken_at_hero.com]
Sent: Thursday, September 07, 2000 5:26 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: creating standby database




Post those scripts!  Also: How long does Oracle keep rows in
v$archived_log?



On Thu, 7 Sep 2000, Reddy, Gautam wrote:


> Got this reply from one of my  friend who works at  ORACLE Field Support.
> This is the reason why we are not using this feature.
> 
> Sounds like we may want to hold off on using this feature
> (log_archive_dest_2 = "service=name_of_other_host REOPEN=30" ) based on
> this.
> 
> "Be very careful when using these new Oracle8i features.  Actually would
> recommend NOT using them.  This is a very new feature and is not as robust
> as I would like it.  I recommend still manually FTPing the logs to the
> standby (without using the new Oracle8i feature) 
> 
> Go to WebIV and do a bug search on "standby ora-00600"  you will see a


list

> of 30 significant bugs. 
> 
> The big ones are ... If the you fill up the secondary sites archive
> destination it will crash your primary with an ORA-00600 then you must
> recover up to a point in time just before the ORA-00600 occurred. 
> 
> Network failures on mandatory (and sometimes optional) archive


destinations

> can also cause the primary to go down. 
> 
> I see your on NT.  The scripts I have to automatically ftp  and apply


files


> are written in TCL and Expect (both share ware).  If they write exect for
NT

> and you decide to manually ftp and apply the files, let me know and I will
> e-mail them to you " 
>   
>   
> 
> Thx
> Gautam 
>  
> 
> 
> -----Original Message-----
> Sent: Wednesday, September 06, 2000 3:55 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> I have:
> log_archive_dest_2 = "service=name_of_other_host REOPEN=30" 
> 
> in my pfile.
> 
> On Wed, 6 Sep 2000, Ashish Shah wrote:
> 
> > Hi All,
> > 
> > I am trying to create standby database on different
> > hosts.
> > 
> > - The database names are same on both hosts
> > - listener/tnsnames files are configured and
> > working
> > - I was able to create standby database
> > but having problem when i try to enable 
> > archive_Dest_2 on primary database.
> > 
> > SVRMGR> alter system set
> > log_archive_dest_2='service=reptest mandatory
> > reopen=60
> > ';
> > Statement processed.
> > 
> > SVRMGR> alter system set log_archive_dest_2 = enable;
> > ORA-02097: parameter cannot be modified because
> > specified value is invalid
> > ORA-16024: parameter LOG_ARCHIVE_DEST_2 cannot be
> > parsed
> > 
> > Any ideas??
> > 
> > When i do tnsping reptest on primary i do get Ok.
> > 
> > TIA.
> > 
> > 
> > 
> > =====
> > Ashish
> > Toronto, Canada
> > 
> > __________________________________________________
> > Do You Yahoo!?
> > Yahoo! Mail - Free email you can access from anywhere!
> > http://mail.yahoo.com/
> > -- 
> > Author: Ashish Shah
> >   INET: ar_shah_at_yahoo.com
> > 
> > Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> > San Diego, California        -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like subscribing).
> > 
> 
> -- 
> Author: Kenneth Berland
>   INET: ken_at_hero.com
> 
> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California        -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> -- 
> Author: Reddy, Gautam
>   INET: Gautam_Reddy_at_Dell.com
> 
> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California        -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 




-- 
Author: Kenneth Berland
  INET: ken_at_hero.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


Received on Wed Sep 13 2000 - 15:07:59 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US