For scripts with long execution time it can be a good idea to check if the script is already running. This to avoid mess and unnecessary load on server caused by multiple instances of the same script.
Add the following code to the script header:
already=`ps -ef | grep "$0" | grep -v grep|wc -l`
if [ $already -gt 1 ]
then
echo "$0 already running"
exit 1
fi
Welcome to my dba blog. Here you can find Linux and Oracle stuff for daily task operations. Click on the picture above to get home. Click on the posts for more details. Anything found here can be used freely, but at your own risk of course. Please tailor scripts and commands to meet your own installation.
Friday, 20 July 2018
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>
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
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.
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;
- grant select any dictionary to new-user;
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%';
#!/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%';
Subscribe to:
Posts (Atom)