ADG_REDIRECT_DML in Oracle Active Data Guard 19c

on
0

Run a shell script with an HTML result with CSS styles

on
HTML result from shell script useful for reports from database.

Run the script start.sh which will start the script scripts.sql. In the scripts.sql are another SQL scripts - SQL statements for database.

Output:

Code for start.sh

#!/bin/sh
DATE=`date '+%m%d%y_%H%M'`

sSID=$1

# Print beginning of webpage
html_header ()
{
    cat <<END
<html>
  <head><title>${1}</title>

  <style type="text/css">
 div {
  font-size:8pt;
  color: #000;
  margin-top: 1em;
  margin-botton: 2em;
  border-collapse:collapse;
  background-color:#F5F5F5;
  font-family: courier;
  margin-left: 2em;
  width:1100px;
  border:solid 1px;
 }
 .name {
   font-size:18pt;
   color: #7d181e;
   letter-spacing:1pt;
   font-weight:700;
  }

 .name2 {
   font-size:16pt;
   color: #d52a33;
 }

 .name3 {
   font-size:14pt;
   color: #d52a33;
 }

 ol {
   counter-reset: section;
   list-style-type: none;
 }
  
 ol li { counter-increment: section; }
            
 ol li:before  { content: counters(section, ".") ". "; }
  </style>
  </head>
  <body>
    <h3>${1}</h3>
    <!-- Table of Contents links -->
    <p>
 <ol>
  <li>
   <a href='#1_heading'>Database info</a>
   <ol> 
    <li><a href='#1_1_name'>Database name</a></li>
    <li><a href='#1_2_name'>Database version</a></li>
   </ol>
  </li>
  <li>
   <a href='#2_heading'>Database users and sessions</a>
   <ol>
    <li><a href='#2_1_name'>Number of sessions</a></li>
    <li><a href='#2_2_name'>Current sessions</a></li>
   </ol>
  </li>
 </ol>
    </p>
END
}

html_footer ()
{
  cat <<END
  </body>
</html>
END
}

html_title ()
{
  echo "<h3><a name='#${2}'>$1</a></h3>"
}

DatabaseIntroduction ()
{
echo "<pre>"
sqlplus -s /nolog <<EOF 
connect sys/<password>@<database_name> as sysdba
@scripts.sql
EOF
echo "</pre>"
}

html_header "Report Summary for ${DATE}"
DatabaseIntroduction
html_footer

Code for scripts.sql

@database_info.sql
@database_sessions.sql

Code for database_info.sql and database_sessions.sql

database_info.sql 
PROMPT <span class='name' id='1_heading'>1. Database info</span>
PROMPT
PROMPT
PROMPT <span class='name2' id='1_1_name'>1.1. Database name</a></span>
PROMPT
PROMPT <div>
PROMPT

select database_name from v$database;

PROMPT
PROMPT </div>
PROMPT

PROMPT
PROMPT <span class='name2' id='1_2_name'>1.2 Database version</span>
PROMPT
PROMPT <div>

select version from v$instance;

PROMPT
PROMPT </div>
PROMPT
database_sessions.sql 
PROMPT
PROMPT <span class='name' id='2_heading'>2. Database users and sessions</span>
PROMPT
PROMPT <span class='name2' id='2_1_name'>2.1 Number of sessions</span>
PROMPT
PROMPT <div>
PROMPT

select count(*) from v$session;

PROMPT
PROMPT </div>
PROMPT

PROMPT
PROMPT <span class='name2' id='2_2_name'>2.2 Current sessions</span>
PROMPT
PROMPT <div>
PROMPT

select distinct username from v$session;

PROMPT
PROMPT </div>
PROMPT

Comments

Post a Comment