Oracle logical standby monitoring

in this examle i am going to use php for getting oracle logical standby replication status. This connects primary and standby servers with oci, then query them which logs were applied and which logs are being applied currently.

php code is:

<?php

@require_once ('stil1.css');

if (isset($_POST['submit'])) {

$dbuser = $_POST['username'];

$dbpass = $_POST['pass'];

$conn1 = oci_connect($dbuser, $dbpass, '//primsrv:1521/primdb');

$conn1 = oci_connect($dbuser, $dbpass, '//standsrv:1521/standdb');

if (!$conn1) {

     echo "can not connect: " . var_dump( oci_error() );

     die();

   }

if (!$conn2) {

     echo "can not connect: " . var_dump( oci_error() );

     die();

   }

   

$sel = oci_parse($conn1, "select SEQUENCE#, ARCHIVED, APPLIED from (select SEQUENCE#, ARCHIVED, APPLIED from v\$archived_log where DEST_ID = 2 order by SEQUENCE# desc) where rownum < 101");

oci_execute($sel, OCI_DEFAULT);

?>

<div style="float: left">

   <table class="liste">

<tr>

<th colspan=3>PRIMARY</th>

</tr>

<tr>

<th>SEQUENCE#</th>

<th>ARCHIVED</th>

<th>APPLIED</th>

</tr>

<?php

   while (oci_fetch($sel)) {

   echo "<tr>";

   echo "<td>" . oci_result($sel, "SEQUENCE#") . "</td>";

   echo "<td>" . oci_result($sel, "ARCHIVED") . "</td>";

   echo "<td>" . oci_result($sel, "APPLIED") . "</td>";

   echo "</tr>";

   }

   echo "</table>";

   echo "</div>";

oci_free_statement($sel);

oci_close($conn1);

   

$sel = oci_parse($conn2, "select SEQUENCE#, FIRST_TIME, APPLIED from (SELECT L.SEQUENCE#, L.FIRST_TIME, (CASE WHEN L.NEXT_CHANGE# < P.READ_SCN THEN 'YES'WHEN L.FIRST_CHANGE# < P.APPLIED_SCN THEN 'CURRENT' ELSE 'NO' END) APPLIED FROM DBA_LOGSTDBY_LOG L, DBA_LOGSTDBY_PROGRESS P ORDER BY SEQUENCE# desc) where rownum < 101");

oci_execute($sel, OCI_DEFAULT);   

?>

<div style="float: right">

   <table class="liste">

<tr>

<th colspan=3>STANDBY</th>

</tr>

<tr>

<th>SEQUENCE#</th>

<th>FIRST_TIME</th>

<th>APPLIED</th>

</tr>

<?php

   while (oci_fetch($sel)) {

   echo "<tr>";

   echo "<td>" . oci_result($sel, "SEQUENCE#") . "</td>";

   echo "<td>" . oci_result($sel, "FIRST_TIME") . "</td>";

   echo "<td>" . oci_result($sel, "APPLIED") . "</td>";

   echo "</tr>";

   }

   echo "</table>";

   echo "</div>";

oci_free_statement($sel);

oci_close($conn2);

} else {

?>

<form action="post_same_page.php" method="post">

Username:<input type="text" name="username" value="" /><br />

Password:<input type="password" name="pass" /><br />

<input type="submit" name="submit" value="Show" />

</form>

<?php

}

?>

stil1.css is the stylesheet:

<style type="text/css">
table.liste {
border-width: 1px;
border-spacing: 0px;
border-style: outset;
border-color: #CBE09D;
border-collapse: separate;

}
table.liste th {
border-width: 1px;
padding: 1px;
border-style: groove;
border-color: #CBE09D;

}
table.liste td {
border-width: 1px;
padding: 1px;
border-style: groove;
border-color: #CBE09D;

}
</style>