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:

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

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

• Oracle Forums:  Issue in Installing Oracle Database 11.2.0.2 

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.

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;
/

 

Any suggestions are welcome.

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

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:

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;

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’s V$ Views
• Oracle9i Database Utilities: 13 DBVERIFY: Offline Database Verification Utility
• Burleson Consulting: About the Oracle dbvverify Utility

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:

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

Read More

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.

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

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.

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

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.

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

Resources

• Oracle Database Reference: V$FLASH_RECOVERY_AREA_USAGE
• Oracle Database Backup and Recovery Basics: 3.5 Setting Up a Flash Recovery Area for RMAN