Skip to main content

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>

Comments

Popular posts from this blog

Sending Jboss Server Logs to Logstash Using Filebeat with Multiline Support

In addition to sending system logs to logstash, it is possible to add a prospector section to the filebeat.yml for jboss server logs. Sometimes jboss server.log has single events made up from several lines of messages. In such cases Filebeat should be configured for a multiline prospector.
Filebeat takes lines do not start with a date pattern (look at pattern in the multiline section "^[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}" and negate section is set to true) and combines them with the previous line that starts with a date pattern.

server.log file excerpt where DatePattern: yyyy-MM-dd-HH and ConversionPattern: %d %-5p [%c] %m%n
Logstash filter: