connect Oracle standby database gives exception ora-01219

Welcome Forums General PowerShell Q&A connect Oracle standby database gives exception ora-01219

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
1 month, 3 weeks ago.

  • Author
    Posts
  • #114405

    Participant
    Points: 0
    Rank: Member

    Hello All,

    I am getting an below oracle exception when i connect to oracle standby database to get archive sequence number. When i execute my powershell code normally, I am getting the result. But when i use try-catch-finally, i am getting below oracle exception when script makes a connection to standby database.

    powershell code : 
    
    $stdbyString = "Data Source=${datasource}; User ID=SYS; Password=*****; DBA Privilege=SYSDBA;"
    
        Try {
    
            $stdbyConnect = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($stdbyString)
    
            $stdbyConnect.Open() 
    
            $stdbyQuery = "select max(sequence#) from v`$log_history where first_time = (select max(first_time) from v`$log_history)"
        
                $stdbyCmd = new-Object Oracle.ManagedDataAccess.Client.OracleCommand($stdbyQuery, $stdbyConnect) 
    
                $stdbyResult = $stdbyCmd.ExecuteReader()
    
                $stdbySeq = $(
    
                     while ($stdbyResult.Read())
                     {
                     $stdbyResult.GetValue(0) 
                     }
                )
    
                write-host "Max Sequence of ${dbName} standby is :" $stdbySeq | ft
        }
    
        Catch [Oracle.ManagedDataAccess.Client.OracleException]{
                $stdbySeq = $_.Exception.Message
        } 
    
        finally {
    
             if ($stdbyConnect.State -eq 'Open') { $stdbyConnect.close() }
        } 
    
    

    Exception details :

    ORA-06550: line 1, column 107:

    PL/SQL: ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only

    ORA-06550: line 1, column 68:

    PL/SQL: SQL Statement ignored

    ORA-06550: line 1, column 197:

    PL/SQL: ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only

    ORA-06550: line 1, column 162:

    PL/SQL: SQL Statement ignored

    ORA-06550: line 1, column 295:

    PL/SQL: ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only

    ORA-06550: line 1, column 248:

    PL/SQL: SQL Statement ignored

  • #114430

    Participant
    Points: 42
    Rank: Member

    If you were to run your Select against the standby from pl/sql does it work? Is that sql valid against a standby Database? Does a simpler PL/SQL statement like "select sysdate from dual work"

    • #114435

      Participant
      Points: 0
      Rank: Member

      Hello Thom,

      Thanks for your Reply.

      If i remove try-catch block and execute any valid sql, SQLs are running fine. But when i use try-catch block to catch exception, I am getting Ora-01219 errors.

      I assume when Script makes connection using open() to standby database which is in mount stage and before executing actual SQL, I think ODP connects to a database it executes several SQL statements to get NLS values like below.

      
      SELECT VALUE from nls_session_parameters where PARAMETER='NLS_CALENDAR'; 
      
      

      These queries are resulting in ORA-01219 errors.

      I am looking for a solution to avoid this exception when i use try-catch.

You must be logged in to reply to this topic.