kp-dba

Monday, 16 July 2018

Clickable headers in PHP

This is a tiny example of how to make header columns clickable. The code is listing the content of the table my_table in a MySQL database called myDb. It can be a good idea to add a link to your main menu as well, to avoid getting lost :)

my.php:

<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
    border: 1px solid black;
}
</style>
</head>
<body>
<?php

$servername = "myServer";
$username = "me";
$password = "myPw";
$dbname = "myDb";
$nbo = 0;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

if (!$conn->set_charset("utf8")) {
    printf("Error loading character set utf8: %s\n", $mysqli->error);
    exit();
}
// to prevent getting totally lost
echo '<a href="index.html">HOME</a>';
$column='LastLogon';
$sort='desc';

if(isset($_GET['sorting'])) {
if($_GET['sorting']=='asc')
     $sort='desc';
else
     $sort='asc';
}

if($_GET['column']=='Name')
   $column = "Name";
elseif($_GET['column']=='Company')
   $column = "Company";
elseif($_GET['column']=='LastLogon')
   $column = "LastLogon";

$sql = "select Name,Company,Department,LastLogon from my_table order by $column $sort";
$result = $conn->query($sql);
// now, instead of having just the column name in the table header, you must add a link and pass the column name
if ($result->num_rows > 0) {
    echo '<table><tr> <th><a href="my.php?sorting='.$sort.'&column=Name">Name</a></th> <th><a href="my.php?sorting='.$sort.'&column=Company">Company</a></th> <th>Department</th> <th> <a href="my.php?sorting='.$sort.'&column=LastLogon">LastLogon</a> </th> </tr>';
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $nbo++;
        echo "<tr><td>" . $row["Name"]. "</td><td>" . $row["Company"] . "</td><td>" . $row["Department"] . "</td><td>" . $row["LastLogon"] . "</td></tr>";
    }
    echo "</table>";
    echo "<br>" . $nbo . " rows";
} else {
    echo "0 results";
}

$conn->close();
?>
</body>
</html>

Friday, 25 May 2018

Troubleshoot oracle em 12c agent

If your oracle em agent refuse to start on your server and you see error "out of heap/memory", try to move all files under the state directory to a temp directory.

make sure the agent is all dead, no java procs or anything
mkdir /tmp/agent_state.bad
mv $ORACLE_BASE/product/em/sysman/emd/state/* /tmp/agent_state.bad
now start the agent again, as root: /etc/init.d/gcstartup start

use emctl if you have the oms server running on the same server

Monday, 23 April 2018

Expanding an oracle standby database

If you have an application using both a primary and a secondary database you might experienced the error ORA-279. This can occur if you expand your primary database with new data files without updating your standby database.

When the archive log containing the expansion gets applied to your standby database you will see the following error in your alert log:

Parallel Media Recovery started
ORA-279 signalled during: ALTER DATABASE RECOVER database until time '2018-04-23:09:12:17' using backup controlfile ...
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /archive-file
Mon Apr 23 09:44:37 2018
File #72 added to control file as 'UNNAMED00072'. Originally created as:
'newly-added-file.dbf'
Recovery interrupted!

The fix to this problem is to tell the standby database about the new file.

Standby database> sqlplus / as sysdba

alter database create datafile 'UNNAMED00072' as 'newly-added-file.dbf';

And now re-run the recover process. It should pass with no errors. The UNNAMED# will of course vary for each install. Use your alert log for the exact info.

Tuesday, 10 April 2018

Oracle read only account

Create a new read only user or grant rights to existing user

create user new-user identified by pwd;
grant connect, create session, select any table to new-user;

Now you can select any object by prefixing with owner. If you want to browse dictionary objects you also need

grant select any dictionary to new-user;

For permanent users I always prefer to give specific access only to the objects needed and create synonyms to point to these objects. This gives a more generic source and also gives a better documentation of what this account is used for. And of course revoke the select any table right.

Tuesday, 13 March 2018

Expired oracle accounts - GRACE period

Have you ever experienced expired oracle users? Or perhaps just about and seen the user enter a GRACE period. This is a common mechanism to ensure that users renew their passwords and follow specified security profiles. But if a service account expires, applications might begin to fail. If you have a monitoring tool that allows you to add your own checks, it can be a good idea to look for accounts that are about to expire, to catch the situation before it becomes a problem. The reason that a service account expires may be that it has a wrong profile. So verify the profile as well. However, it does not take long to make a check that captures all databases. Before an account expires, it will enter a GRACE period. This gives you some time to adjust. In its simplest form, a check may look something like this. Install in cron and run every morning.

#!/bin/bash

script_dir="/your_scripts"
logfile="grace.log"
ORACLE_BASE="/your_oracle"
receivers="your-email-address"
running_db=`ps -ef|grep smon|grep -v grep|awk '{print $8}'|awk -F "ora_smon_" '{print $2}'`

export ORACLE_BASE

date > $script_dir/$logfile

for i in `echo $running_db`;do
    ORACLE_SID=$i
    ORAENV_ASK=NO
    . /usr/local/bin/oraenv 1> /dev/null 2> /dev/null

    $ORACLE_HOME/bin/sqlplus /nolog <$script_dir/grace.sql |grep tag: > $script_dir/$i.grace

    grep "GRACE" $script_dir/$i.grace 1> /dev/null 2> /dev/null
    found="$?"

    if [ "$found" -eq 0 ]
    then
       echo "$i contain one or more accounts in grace mode" >> $script_dir/$logfile
       cat $script_dir/$i.grace >> $script_dir/$logfile
    else
       rm -f $script_dir/$i.grace
    fi
done

nbo_lines=`cat $script_dir/$logfile|wc -l`

if [ "$nbo_lines" -gt 1 ] then
    cat $script_dir/$logfile | mailx -s "Oracle warning - accounts in GRACE mode" $receivers
fi

exit 0

grace.sql:
connect
select 'tag:'||username,account_status from dba_users where account_status like '%GRACE%';

Wednesday, 7 March 2018

Search for user information

You may have reasons to search for user information in your databases. Maybe you need to know the source of all clients. Lets say you want to move one or more of your databases. Then you need to find all the associated clients and alter the config. A query, in oracle, to view logged on users will only give you the activity right now.

select username||'.'||osuser|'.'||machine||'.'||program||'.'||process from v$session where type='USER'

If you want to get the complete picture you need to do this over a period of time, weeks. Either as a regular job and save the output, or make a logon trigger that can do it. When catching logon information on intervals you are able to capture logon and logout timing as well.

A scheduled task / cron job that run on a regular basis, with the purpose of getting an overview of connected clients can look something like this (you need to create the table s_accounting first)
declare
ls_day varchar2(8);
ls_time varchar2(5);

cursor c_user is
select username,osuser,machine,count(*) sessions
    from v$session
where username <> 'SYS'
       and status <> 'KILLED'
       and type = 'USER'
group by username,osuser,machine
order by machine,username;
begin
   ls_day := to_char(sysdate,'DDMMYYYY');
   ls_time := to_char(sysdate,'HH24:MI');
   for c_user_rec in c_user loop
      update s_accounting set s_logout=ls_time
       where s_day = ls_day and
             s_username=c_user_rec.username and
             s_osuser=c_user_rec.osuser and
             s_machine=c_user_rec.machine;
      if SQL%NOTFOUND then
         insert into s_accounting (s_day,s_username,s_osuser,s_machine,s_login,s_logout)
                values (ls_day, c_user_rec.username, c_user_rec.osuser,
                        c_user_rec.machine,ls_time, ls_time);
      end if;
   end loop;
commit;
exception
   when others then
      rollback;
      raise;
end;

If you use a logon trigger instead, then you will have the actual user in variables and can make the code much more compact. Keep in mind that a logon trigger can give you an extra load on your system if you have a loaded web server in front. To get an idea of the number of logon/logoff activity on your system you can check the audit log.

select action_name,count(*) from dba_audit_session where timestamp>(sysdate-7) group by action_name;

Sometimes you need to go deeper. Lets say you need more details about the login, like name, email, phone etc. This kind of information, if it exist, will be stored in your application data model. Sometimes this is linked to the connections you see, other times not. Most applications will have tools to handle this kind of user information. If you do not know the application and really need to get hold of the user information stored you have to ask the vendor, or you can start looking for it yourself. As a start you could try out the query below. Check out the result and query the objects for user info. Somethimes you need to go wider. Look for extra attributes, like last logon, email, short name, anything you can use to qualify the user and possibly add some extra value. Finally you quite often see applications using a central user repository for authentication. The application system documentation might give you an idea or two as well. Good luck.

select 'object='||owner||'.'||object_name from dba_objects where owner not like 'SYS%' and object_type in ('TABLE','VIEW') and (object_name like '%USER%' or object_name like '%LOGIN%' or object_name like '%LOGON%') and owner in (select username from dba_users where account_status='OPEN') order by owner,object_name;