connect Oracle standby database gives exception ora-01219

PowerShell Help
1

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

2

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”

3

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.