Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    369

    Unanswered: scripting question moving control files

    Hi gurus,

    I am trying to get the following script below to work with our Oracle 9i database on Windows 2000 Server: It is written in vbscript, wsh script and shell commands for Oracle 9i

    ' This script creates the control file
    ' The SID is changed to the proper mount host SID
    ' The file is then copied to the mount host
    '
    writemsg "Starting CreateControl"

    ' This oExec object is used for all script executions
    Dim WshShell, oExec, fso, execEnv
    Set WshShell = CreateObject("WScript.Shell")

    'Set the Oracle SID
    Set execEnv = WshShell.Environment( "PROCESS" )
    execEnv("ORACLE_SID") = currentEnvironment("StdSID")

    Set fso = CreateObject("Scripting.FileSystemObject")

    'TempControlFile is really the logs from running create trace file
    Dim ControlFile, TempControlFile, MountControlFile
    TempControlFile= currentEnvironment("StdTmp") & "\"
    TempControlFile= TempControlFile & currentEnvironment("MountSID") & "control.ctl"

    writemsg "Check 1- TEMP file: " & TempControlFile

    ' Output file, will be created using shares (thus the $) on mount host
    Dim MountPath, MountDrive, MountFolder
    MountPath = Split( currentEnvironment("MountTmp") , ":" )
    MountDrive = MountPath(0)
    MountFolder = MountPath(1)
    MountControlFile= MountDrive & "$" & MountFolder & "\"
    MountControlFile= MountControlFile & currentEnvironment("MountSID") & "control.ctl"

    writemsg "Check 2- SQL File:"

    Dim SqlFile
    SqlFile = currentEnvironment("ScriptDir") & "\create_control_file.sql"

    ' Make sure the SQL file exists
    If ( fso.FileExists( SqlFile ) = False ) Then
    writemsg "ERROR: " & SqlFile & " Not found. "
    writemsg "Exiting..."
    WScript.Quit(1)
    End If

    writemsg "Found SQL FILE: " & SqlFile

    ' Create the control file in the temporary file location
    Set oExec = WshShell.Exec( "sqlplus -s " & DQ & "/ as sysdba" & DQ & " @" & DQ & SqlFile & DQ )
    logTxtStream oExec.StdOut , currentEnvironment("StdTmp") & "\" _
    & currentEnvironment("MountSID") & "log_control.ctl"

    writemsg "Check- trying to create the new control file..."

    chkmsg oExec , "Creating control file"

    ' List all control files in the directory in order by date
    Dim FileRegExp, Folder, FileList, File, CurFile
    Set FileRegExp = New RegExp
    FileRegExp.Pattern = ".*\.trc"
    FileRegExp.IgnoreCase = True

    CurFile = Null

    writemsg "Check 3- can we create the control file? "

    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
    ' THIS IS WHERE THE SCRIPT KEEPS FAILING!!!!!!!!!!!!
    ' IT IS COMPLAINING THAT this is where the script keeps failing
    ' Error message: PATH NOT FOUND
    '
    '
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''

    writemsg "I CANNOT FIGURE OUT WHY THE SCRIPT (CreateControl.vbs) KEEPS FAILING RIGHT NOW"
    writemsg " WHEN IT TRIES TO FIND THE PATH for the CONTROL FILE IT FAILS"
    writemsg "THERE IS AN ERROR WHEN IT TRIES TO access the following.."
    On Error Resume Next ' to see if I can continue running beyond this error
    ' OK GOOD WE FOUND THE BUG IN THIS SCRIPT! Need to FIX BELOW SECTION-
    Set Folder = fso.GetFolder( currentEnvironment("ControlFileDumpDir") )
    Set FileList = Folder.Files
    For Each File in FileList
    If ( FileRegExp.Test( File.Name ) ) Then
    If ( IsNull( CurFile ) Or CurFile = "" ) Then
    Set CurFile = File
    End If

    If ( File.DateLastModified > CurFile.DateLastModified ) Then
    Set CurFile = File
    End If
    End If
    Next
    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''
    ' OK FOLKS- I am GOING TO TRY TO SEE IF I CAN SEE IF IT WILL RUN PAST THIS ERROR OR NOT
    ' IF IT DOES THEN I KNOW THAT THE ABOVE CODE NEEDS TO BE FURTHER DEBUGGED- BSP
    '
    ' ADD ERROR HANDLER TO RESUME EXECUTION OF THE SCRIPT
    '
    ' NOPE NO DICE MUST REPAIR BAD CODE ABOVE!
    '
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''
    'echo checks
    writemsg "Check 4- can we access the dump dir:" & ControlFileDumpDir
    writemsg "Check 5"

    ' Add another error handler to step through the code- BSP
    On Error Resume Next

    ' Verify the control file was created
    If ( IsNull( CurFile ) ) Then
    writemsg "Error! No generated control file found in " & currentEnvironment("ControlFileDumpDir")
    writemsg "Exiting..."
    WScript.Quit(1)
    Else
    ControlFile = CurFile.Name
    End If

    writemsg "Generated control file " & ControlFile
    WScript.Sleep 5000

    writemsg "Check 6- can we rename to control file to the new SID?"

    ' Modify the control file to the new SID
    Dim InFile, OutFile, WriteOK
    Set InFile = fso.OpenTextFile( currentEnvironment("ControlFileDumpDir") & "\" & ControlFile , 1 )

    Dim StartupRegEx, CreateRegEx, RecoverRegEx, CommentRegEx, EndRegex
    Set StartupRegEx = New RegExp
    Set CreateRegEx = New RegExp
    Set RecoverRegEx = New RegExp
    Set CommentRegEx = New RegExp
    Set EndRegex = New RegExp
    StartupRegEx.Pattern = "^STARTUP"
    CreateRegEx.Pattern = "^CREATE CONTROLFILE"
    RecoverRegEx.Pattern = "^RECOVER"
    EndRegEx.Pattern = "^# End of tempfile"
    CommentRegEx.Pattern = "(^#)|(^--)"

    WriteOK = Null

    ' Create the control file. This replaces tr_ctl.awk
    ' Begin the controlfile with error handling CONTROLFILE -> STARTUPFILE

    Dim NextLine
    Do While ( InFile.AtEndOfStream = False )
    '''''''''''PERFORM REPLACES HERE
    NextLine = Replace( InFile.ReadLine , currentEnvironment("StdSID"), currentEnvironment("MountSID"))

    '''''''''''PRINT OR NOT?
    ' Start printing at Startup
    If StartupRegEx.Test( NextLine ) Then
    writemsg "opening file"
    Set OutFile = fso.CreateTextFile( TempControlFile , true )
    OutFile.WriteLine( "whenever SQLERROR exit failure" )
    OutFile.WriteLine( "whenever OSERROR exit failure" )
    OutFile.WriteLine( "SHUTDOWN IMMEDIATE" )
    OutFile.WriteLine( NextLine )
    WriteOK = True
    ' might want to add pfile here...

    ' Change the create control file line to use SET instead of REUSE
    ElseIf CreateRegEx.Test( NextLine ) Then
    NextLine = Replace ( NextLine , "REUSE" , "SET" )
    OutFile.WriteLine( NextLine )

    ' Replace the recover command and set error handling appropriately
    ElseIf RecoverRegEx.Test( NextLine ) Then
    OutFile.WriteLine("whenever SQLERROR continue")
    OutFile.WriteLine("whenever OSERROR continue")
    OutFile.WriteLine("RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;")
    OutFile.WriteLine("CANCEL")
    OutFile.WriteLine("whenever SQLERROR exit failure")
    OutFile.WriteLine("whenever OSERROR exit failure")

    ElseIf CommentRegEx.Test( NextLine ) Then
    WriteOK = WriteOK

    ElseIf (WriteOK = True) Then
    OutFile.WriteLine( NextLine )
    End If

    If EndRegEx.Test(NextLine) Then
    writemsg "closing file"
    OutFile.WriteLine("EXIT")
    OutFile.Close
    WriteOK = Null
    End If

    Loop
    writemsg "Modifying control file"

    ' Verify that the target directory exists on the mount host

    writemsg "Check 7- can we copy the control file to the mount host?"

    ' Copy the control file to the mount host
    Set oExec = WshShell.Exec( "cmd /c copy " & TempControlFile & " \\" _
    & currentEnvironment("MountHost") & "\" & MountControlFile )
    writemsg "cmd /c copy " & TempControlFile & " \\" _
    & currentEnvironment("MountHost") & "\" & MountControlFile
    chkmsg oExec , "Copying control file"


    writemsg "Finished CreateControl"

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I think you have gotten horse before the cart.
    Are you 100% certain that you have a bullet proof MANUAL procedure that you are simply trying to automate?
    The manual procedure should be clear enough that you could give it to your Mother & she could complete the task.
    After you know EXACTLY what steps need to occur, the implementation is just a Small Matter Of Programming (SMOP)!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    Not commenting on your coding ability or syntax, but here's the process for those of us using Pfiles:

    1. Shutdown the database (immediate is fine).
    2. Edit init.ora to have new control file path.
    3. Move control files to new location.
    4. Start the database.

    Even if you have 10 control files in 10 different directories, that operation would only take <10 minutes. Not sure how many hours you are spending on the coded approach.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •