Category: Oracle DBA

Exploring Vagrant with Ubuntu and Oracle XE

I ran across Andrej Koelewijn’s article about Vagrant over a year ago, and I have been wanting to explore it ever since.  I do a lot of playing around with different things, and Vagrant looked like it would be a cool, quick way to spin up a new image of an OS.  I would love to get it to the point of being able to spin up an instance of PeopleSoft.  I am no where near there now, though.

This is just my first run through.  I set an easy goal of trying to get Oracle XE running on Ubuntu via the repositories.  Unfortunately, I learned that that repository isn’t worth the time.  Still, it was a fruitful exercise, and I definitely will be playing around more with Vagrant.

Follow along if you like…

Read More

Troubleshooting Oracle Database Install

I have been trying to install the Oracle Database on Windows 8, and I keep getting this error message:

If you can’t see the image, here’s what it says:

File not found  c:\oracle\dbhome_1\owb\external\oc4j_applications\WFMLRSVCApp.ear

Then, the installer hangs at setting a value in the registry.

The specific message is “updating registery key ‘HKEY_LOCAL_MAHINE/SOFTWARE/ORACLE/KEY_OraDb11g_home1′”.  It stops between 72% and 73%.

I read some where that this is probably caused by bad installation media.  So, the first thing to check is whether the zip files that I downloaded were valid.  I found that Oracle has a Patch Metadata link that points to an XML file containing an md5sum for the files:

I used md5sum to verify that the files that I downloaded where complete:

Here’s a script that you can use if you downloaded the exact same files that I did:

[sourcecode]

echo "976634087BDC54C72E8DD187049D35C6 p10404530_112030_WINNT_1of6.zip" | md5sum -c
echo "F20265A371F737C43713E1E7F3440C2C p10404530_112030_WINNT_2of6.zip" | md5sum -c
pecho "8CC4F1132D1DD4FA36B4A13CFC24B9E6 p10404530_112030_WINNT_3of6.zip" | md5sum -c
echo "B84E2E894DAC29B96B7FF57C77A6E1C3 p10404530_112030_WINNT_4of6.zip" | md5sum -c
echo "710B7EC98AD69E51F02B5E73A9825771 p10404530_112030_WINNT_5of6.zip" | md5sum -c
echo "86D0422263F9F9464EE2E0222D10B3B4 p10404530_112030_WINNT_6of6.zip" | md5sum -c

[/sourcecode]

Everything checkout okay, so I proceeded to the next step.  I read in one of the comments on the download, that someone had to unzip each file individually into it’s own directory, and then combine them all.

So, I extract each zip file into it’s own directory…

Once, they were extracted individual, I copied the contents of each directory up one level:

This leaves a complete installation directory:

And, I can launch the installer from the database directory within that:

I still ran into this error.  Perhaps it is because I didn’t delete the directory where I had tried and failed before.

I hit continue several times, and finally, it finished!

Before completing my PeopleSoft installation on this image, I ended up migrating to a new laptop, and I decided to start over with the new laptop.  On my second attempt, I extracted each zip file to it’s own folder.  On that install, I receive no errors.

Resources

Sudo Plus X11 Forwarding

I had trouble the other day running the Oracle installer in GUI mode over SSH.  I had the -X option set, and I could run a GUI program as myself.  The problem was that I couldn’t run the GUI program after I used sudo to change to my oracle user.

I found the answer on the “Bag of Tricks” blog:

Bag of Tricks: x forwarding and sudo for oracle installs

To build upon that, I turned it into a script.  The script adds the x cookie, runs an interactive bash, and finally removes the cookie at the end.

[sourcecode]

#!/bin/sh

user=$1
if [ -z "$user" ]; then
user=<default user>
fi

displayNum=`echo $DISPLAY | sed -e ‘s/^.*://’ -e ‘s/\.[0123456789]*//’`
echo "Display # = $displayNum"
cookie=`xauth list | grep ":$displayNum"`
echo "Cookie = $cookie"
cookiename=`echo $cookie | sed ‘s/\s*MIT-MAGIC.*$//’`
echo "Cookie Name: $cookiename"
echo "user = $user"
sudo -u $user bash -c "xauth list; xauth add $cookie; bash; xauth remove $cookiename"

[/sourcecode]

I put the script in my ~/bin directory and called it ms (my-sudo).  That way I could call it easily.

I hope someone finds that usedful.  Any comments are welcomed.

Oracle DBA: EMPLID Table Counts Script

Here’s a handy script that I put together today.  It finds all of the tables with the EMPLID field in it and prints a count of the rows that have a non-blank EMPLID value.

You can tweak it to work with other fields or find a certain value.

[sourcecode language=”sql”]
declare
cursor tbls is select distinct a.recname
from psrecfieldall a, psrecdefn b
where a.recname = b.recname
and b.rectype = 0
and a.fieldname = ‘EMPLID’;
tbl tbls%ROWTYPE;
cnt number;
tablename varchar2(50);
sqltext varchar2(200);
begin
open tbls;
loop
fetch tbls into tbl;
exit when tbls%NOTFOUND;

select sqltablename into tablename from psrecdefn where recname = tbl.recname;
if tablename = ‘ ‘ then
tablename := ‘PS_’ || tbl.recname;
end if;
sqltext := ‘select count(*) from ‘ || tablename || ‘ where EMPLID <> ” ” ‘;
execute immediate sqltext into cnt;
if cnt > 0 then
dbms_output.put_line(tbl.recname || ‘ = ‘ || cnt);
end if;
end loop;
close tbls;
exception
when others then
dbms_output.put_line(‘error with table: ‘ || tbl.recname);
dbms_output.put_line(‘    ‘ || SQLCODE || ‘: ‘ || SQLERRM);
dbms_output.put_line(‘    sql: ‘ || sqltext);
end;
/
[/sourcecode]

 

Any suggestions are welcome.

Oracle DBA: Public Synonyms

Recently, I wrote about granting read only access to the database for developers.  Tim Hall commented that my solution was insecure and granted too much, possibly allowing a loophole.  At the same time, I recently wanted to provide public synonyms to users.

So, the following role, procedures, and trigger attempt to accomplish those two goals:

  • Allow for securely granting readonly access
  • Automatically create public synonyms

Oracle DBA: Running DBVerify on all files

One of the steps to a PeopleTools Upgrade is running a database verify.  In Oracle, that means using the dbv program.

Unfortunately, I didn’t see a “check the whole database” option.  So, I had to create a way to run it for each file…

I ran this in the database:

[sourcecode language=”sql”]

select ‘dbv file=’ || file_name
|| ‘ blocksize=’
|| (select value from v$parameter where name = ‘db_block_size’)
|| ‘ logfile=E:\psca\output\dbv\’ || tablespace_name || ‘.txt’
from SYS.dba_data_files;

[/sourcecode]

Note: you will have to change the path for the logfile.

Then, you can copy the contents of that file and run it on the command line.

Please comment if you have a better suggestion!

Resources

Oracle: semget failed!

I had an issue with starting a database a while back.  Basically, I ran out of resources on the server, and I had to adjust the kernel parameters.  Here are the details…

Error message:

[sourcecode]
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates
[/sourcecode]

Read More

Script: Check Flash Recovery Usage

I’ve been trying to monitor the archive log usage for our databases, and I came up with this little script.  I thought I would share in case it would help anyone, but also, please correct me if there is a better way or you see anything wrong.

Here’s the basic version.  You can change the database names in the for loop.

[sourcecode language=”bash”]

tmpfile=/tmp/$(whoami)sql.txt
echo "DB_NAME FILE_TYPE PERCENT_SPACE_USED NUMBER_OF_FILES" > $tmpfile
echo "——- ——————– —————— —————" >> $tmpfile
for e in FN91DMO FN91CFG FN91DEV FN91TST
do
export ORACLE_SID=$e
sqlplus -s / as sysdba >> $tmpfile <<!
set head off
set feedback off
select ‘$e’ DB_NAME, FILE_TYPE, PERCENT_SPACE_USED, NUMBER_OF_FILES
from v\$flash_recovery_area_usage
where PERCENT_SPACE_USED > 0;
exit;
!
done
sed ‘/^$/d’ $tmpfile
rm $tmpfile

[/sourcecode]

This next version calls a script to get the database names.  For example, I have the databases stored in an XML file, and I built a perl script that would read the XML file and print the database names.

[sourcecode language=”bash”]

tmpfile=/tmp/$(whoami)sql.txt
echo "DB_NAME FILE_TYPE PERCENT_SPACE_USED NUMBER_OF_FILES" > $tmpfile
echo "——- ——————– —————— —————" >> $tmpfile
for e in $(buildDBList)
do
export ORACLE_SID=$e
sqlplus -s / as sysdba >> $tmpfile <<!
set head off
set feedback off
select ‘$e’ DB_NAME, FILE_TYPE, PERCENT_SPACE_USED, NUMBER_OF_FILES
from v\$flash_recovery_area_usage
where PERCENT_SPACE_USED > 0;
exit;
!
done
sed ‘/^$/d’ $tmpfile
rm $tmpfile

[/sourcecode]

Finally, this version calls a script to check the status of the database.  If the database is down or temporarily off, it skips that database.

[sourcecode language=”bash”]

tmpfile=/tmp/$(whoami)sql.txt
echo "DB_NAME FILE_TYPE PERCENT_SPACE_USED NUMBER_OF_FILES" > $tmpfile
echo "——- ——————– —————— —————" >> $tmpfile
for e in $(buildDBList)
do
export ORACLE_SID=$e
checkDBStatus $e 2>&1 > /dev/null
if [ $? -eq 0 ]; then
sqlplus -s / as sysdba >> $tmpfile <<!
set head off
set feedback off
select ‘$e’ DB_NAME, FILE_TYPE, PERCENT_SPACE_USED, NUMBER_OF_FILES
from v\$flash_recovery_area_usage
where PERCENT_SPACE_USED > 0;
exit;
!
fi
done
sed ‘/^$/d’ $tmpfile
rm $tmpfile

[/sourcecode]

Resources

Creating a Readonly Database User

Here’s a common task: You need to create a database user with read only access.  You have some power users who know how to use SQL, and they are allowed to see all of the data in the system.  You can use these commands to create such a user (in Oracle):

[sourcecode language=”sql”]
create user PSRO identified by PSRO;
grant create session, select any table, select any dictionary to PSRO;
[/sourcecode]

Note: You can probably find this all over the web, but I wanted to capture it on my blog so that I could find the syntax easier.

Resources