I kept getting error messages like this:

ORA-01502: index 'SYSADM.PS_PSAESTMTDEFN' or partition of such index is in unusable state

I found the answer to the problem here:

ORA-01502 Oracle Index in Unusable State

As Katie mentioned in the comments, the status in 10g is UNUSABLE instead of INVALID.

Here is a quick script to rebuild all of the problem indexes:

declare
begin
   for index_rec in (select owner, index_name
                     from dba_INDEXES
                     where status = 'UNUSABLE')
   loop
      execute immediate 'alter index ' || index_rec.owner || '.' ||
          index_rec.index_name || ' rebuild';
   end loop;
end;

Analysis

Determining largest indexes by blocks

select index_name, leaf_blocks, num_rows
from dba_indexes
where owner = 'SYSADM'
and leaf_blocks is not null
order by leaf_blocks desc;

Determining indexes with the most free space

I found that the index with the most free space only had 32M free.


select a.owner, a.index_name, a.leaf_blocks, a.num_rows, b.tablespace_name,
b.blocks, b.blocks - a.leaf_blocks free_blocks,
(b.blocks - a.leaf_blocks) * to_number(p.value) / 1024 freespace_k
from dba_indexes a, dba_segments b, v$parameter p
where p.name = 'db_block_size'
and a.owner not in ('SYS', 'SYSTEM')
and a.index_type <> 'BITMAP'
and a.leaf_blocks is not null
and a.owner = b.owner
and a.index_name = b.segment_name
order by free_blocks desc;

Determining index space used by empty tables

One thing I noticed in the previous selection is that a number of indexes were on tables that had no rows, but the index had 128K allocated to them. I found that 21606 indexes matched this scenario totaling about 21G of unused space!

select count(*), sum(b.blocks), sum(b.blocks) * max(to_number(p.value)) / 1024 total_kilobytes
from dba_indexes a, dba_segments b, v$parameter p
where a.index_type <> 'BITMAP"
and a.owner = b.owner
and a.index_name = b.segment_name
and a.leaf_blocks = 0
and a.num_rows = 0
and b.blocks = 128;

Finding the PeopleSoft record name for the Oracle index name

In order to be able to put all of these changes into a PeopleSoft Project, I need to be able to tie the index to a PeopleSoft record. Here is an example join for dba_indexes and PSRECDEFN:

select o.index_name, o.table_name, r.recname
from dba_indexes o, psrecdefn r
where (r.sqltablename = o.table_name or (r.sqltablename = ' ' and o.table_anme = 'PS_' || r.recname))
and rownum < 5;

Creating a Project

I wanted to make PeopleSoft do most of the leg work, and so, my next step was to create a project in Application Designer. This way Application Designer will create all of the project header rows in the database (ex: PSPROJECTDEFN, etc.). I had to add the record for the first index just to be able to save the project. I did all this part manually in Application Designer.

I saved my project as SP_INDEX_REBUILD, but you can choose whatever name you would like. Just make sure that you change the name in the SQL below to the name that you choose.

You can use the first row in the project item table as a template for inserting the rest.

select * from psprojectitem
where projectname = 'SP_INDEX_REBUILD';

So, to insert the rest of the tables into the project, I used the statement in the database:

insert into psprojectitem
select distinct 'SP_INDEX_REBUILD', 0, 1, r.recname, 0, ' ', 0, ' ', 0, ' ', 0, 0, 0, 0, 1, 0
from dba_indexes a, dba_segments b, v$parameter p, psrecdefn r
where (r.sqltablename = a.table_name or (r.sqltablename = ' ' and a.table_name = 'PS_' || r.recname))
and p.name = 'db_block_size'
and a.index_type <> 'BITMAP'
and a.owner = b.owner
and a.index_name = b.segment_name
and a.leaf_blocks = 0
and a.num_rows = 0
and b.blocks = 128
and not exists (select 'x' from psprojectitem where projectname = 'SP_INDEX_REBUILD' and objectvalue1 = r.recname);

Warning: this statement did take some time to run. You may want to see if you can optimize it a little. It took about 20 minutes to complete.

Before I could see the new records attached to the project in Application Designer I had to clear the cache in Configuration Manager.

Inserting Rows for DDL

insert into psidxddlparm
select distinct recname, indexid, 2, 0, 'INIT', 0
from pskeydefn k, psprojectitem p
where k.recname = p.objectvalue1
and p.projectname = 'SP_INDEX_REBUILD'
and not exists (select 'x' from psidxddlparm i
where recname = i.recname
and indexid = i.indexid
and platformid = 2
and parmname = 'INIT');

Updating the rest of the rows

update psidxddlparm
set parmvalue = 0
where recname in (select objectvalue1
from psprojectitem
where objectvalue1 = recname);

Updating the Indexes

I cleared the cache to make sure that Application Designer recognized the new changes that I made in the database.

Then, just changing the indexes is not enough the make Application Designer realize that there is something that needs to be rebuilt. So, you have to change the build properties. On the Alter tab, make sure to “Alter even if no changes”. Then, build the project. I had to use the Alter tables options and not just the Create Indexes option to get it to build.

Added line to init.ora

db_2k_cache_size = 10M

If you don’t add this like, you will get the error message: “ORA-29339: tablespace block size 2048 does not match configure block sizes”.

Creating the new tablespace
create tablespace sp_empty_index
datafile ‘e:\oradata\HCM90\SP_EMPTY_INDEX.DBF’
size 10m
blocksize 2k
extent management local autoallocate
segment space management auto;

ALTER DATABASE DATAFILE ‘e:\oradata\HCM90\SP_EMPTY_INDEX.DBF’
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;

insert into psidxddlparm
select distinct recname, indexid, 2, 0, ‘INDEXSPC’, ‘sp_empty_index’
from pskeydefn k, psprojectitem p
where k.recname = p.objectvalue1
and p.projectname = ‘SP_INDEX_REBUILD’
and not exists (select ‘x’ from psidxddlparm i
where k.recname = i.recname
and k.indexid = i.indexid
and i.platformid = 2
and i.parmname = ‘INDEXSPC’);

update psidxddlparm
set parmvalue = ‘sp_empty_index’
where recname in (select objectvalue1
from psprojectitem
where objectvalue1 = recname)
and platformid = 2
and parmname = ‘INDEXSPC’;

select ‘alter index sysadm.’ || indexname || ‘ rebuild tablespace sp_empty_index;’
from (select distinct ‘PS’ || indexid || recname as indexname
from pskeydefn
where recname in (select objectvalue1
from psprojectitem
where objectvalue1 = recname)) a;

Reclaiming Space for the File System

I used the notes I made for resizing the database to get the free space to the file system.

PSINDEX was 47.89% free — 13.28G free.

Resources

DBAZine.com: Indexes – Part 2: Is This Index the Right Size?

recycle and keep buffers in other tablespace block sizes

Here is my Init Ora file for the database of my VMWare image. Hope this helps if you are attempting something similar.  This is what I finally got to work during installation.

DB_NAME = hcm90
DB_FILES = 1021
CONTROL_FILES = (E:\oradata\hcm90\CONTROL01.CTL,
E:\oradata\hcm90\CONTROL02.CTL,
E:\oradata\hcm90\CONTROL03.CTL)
OPEN_CURSORS = 255
db_block_size = 8192
UNDO_MANAGEMENT = AUTO
shared_pool_size = 72265318

Note: I am still trying to get the shared pool size to a good number.  I have lowered it considerably, but before I post that, I need to work out the kinks.  I had to have the shared pool size at 72,265,318 in order to get it to install.  When I had it set lower, I got an error message about memory.

Here is a list of the directories on my PeopleSoft 9.0 HRMS image and their sizes. Hopefully, this will give you an idea of the hard drive space required to install PeopleSoft 9.0 should you want to do something similar.

c:\ 22.2 GB used / 2.75 GB free = 24.9 GB total
  c:\bea = 176 MB
    c:\bea\jre142 = 40.0 MB
    c:\bea\Tuxedo9.1 = 135MB
  c:\Documents and Settings = 598 MB
  c:\install = 1.72 GB
  c:\NetExpressRuntime = 1.38 MB
  c:\oracle = 2.67 GB
    c:\oracle\appserv10g = 738 MB
    c:\oracle\oradata = 1.46 GB
  c:\Program Files = 529 MB
  c:\ps = 303 MB
  c:\psca = 91.6 MB
  c:\pshome = 10.6 GB
    c:\pshome\hcm90\bin = 296 MB
    c:\pshome\hcm90\data = 3.78 GB
    c:\pshome\hcm90\projects = 2.70 GB
    c:\pshome\hcm90\setup = 855 MB
    c:\pshome\hcm90\src = 225 MB
    c:\pshome\hcm90\verity = 2.07 GB
  c:\windows = 2.7 GB
e:\ 63.6 GB used / 16.3 GB free = 79.9 GB total
  e:\oradata\hcm90\PSINDEX.DBF = 23.4 GB
  e:\oradata\hcm90\PSINDEX2.DBF = 23.4 GB
  e:\oradata\hcm90\PSIMAGE.DBF = 2.04 GB
  e:\oradata\hcm90\SYSTEM01.DBF = 2.04 GB
  e:\oradata\hcm90\GPAPP.DBF = 21.94 GB
  e:\oradata\hcm90\TLWORK.DBF = 1.24 GB
  e:\oradata\hcm90\HRLARGE.DBF = 942 MB
  e:\oradata\hcm90\TLAPP.DBF = 800 MB

I previously blogged about installing PeopleSoft HRMS 9 on a VMWare Image. One of the problems I had was getting the database to start automatically with the service when the image boots. We constantly boot and shutdown the image since we can only have one copy of it running at a time.

In my case, the problem appears to be an Oracle bug. Installing a patch fixed the problem. But, here is everything I learned on the issue.

Patch

Downloading the Patch:

• Log into http://metalink.oracle.com
• Go to Patches and Updates
• Click on Simple Search
• Search for Patch “4547817“, Platform Microsoft Windows (32-bit)
• Download (543M)

Installing the Patch:

• Unzipped it
• Stopped the Web Server, App Server, Process Scheduler, and the Database
• Ran the setup.exe
• See the log below, I had to run catupgrd.sql

After Upgrading the oradim log said this:
Tue Jun 12 17:16:50 2007
c:\oracle\db10g\bin\oradim.exe -startup -sid hcm90 -usrpwd * -pfile c:\oracle\db10g\database\initHCM90.ora -log oradim.log -nocheck 0
Tue Jun 12 17:17:09 2007
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option

So, I used the upgrade option as describe here.

sqlplus /nolog
connect / as sysdba
startup upgrade
@c:\oracle\db10g\rdbms\admin\catupgrd.sql

Logs to Check

Specific to the Service:

%oracle_home%\database\oradim.log

Tue Jun 05 15:28:13 2007
c:\oracle\db10g\bin\oradim.exe -startup -sid hcm90 -usrpwd * -pfile c:\oracle\db10g\database\initHCM90.ora -log oradim.log -nocheck 0
Tue Jun 05 15:28:18 2007
ORA-12560: TNS:protocol adapter error

%oracle_home%\database\sqlnet.log

***********************************************************************
Fatal NI connect error 12560, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oraclehcm90)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))’))(CONNECT_DATA=(SID=hcm90)(CID=(PROGRAM=c:\oracle\db10g\bin\oradim.exe)(HOST=PSQA895)(USER=SYSTEM))))

VERSION INFORMATION:
TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 – Production
Time: 05-JUN-2007 15:28:18
Tracing not turned on.
Tns error struct:
ns main err code: 12560
TNS-12560: TNS:protocol adapter error
ns secondary err code: 0
nt main err code: 530
TNS-00530: Protocol adapter error
nt secondary err code: 2
nt OS err code: 0

Specific to the Listener

%oracle_home%\network\log\listener.log

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 – Production on 05-JUN-2007 15:28:09

Copyright (c) 1991, 2005, Oracle. All rights reserved.

System parameter file is c:\oracle\db10g\network\admin\listener.ora
Log messages written to c:\oracle\db10g\network\log\listener.log
Trace information written to c:\oracle\db10g\network\trace\listener.trc
Trace level is currently 0

Started with pid=348
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=psqa895)(PORT=1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
05-JUN-2007 15:29:23 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.150.25)(PORT=1040)) * establish * orcl * 12514

Other

&oracle_home%\sqlnet.log

The Service and the Registry

Deleting the Service:

At the command prompt> oradim -delete -sid <SID>

If either that doesn’t work or you have uninstalled, you have to open regedit and delete the following key:

HKLM\System\CurrentControlSet\Services\OracleService<SID>

Recreating Your Service:

At the command prompt>
oradim -new -sid <SID> -intpwd <password> -startmode AUTO -pfile <oracle_home>\database\init<SID>.ora

Other Registry Settings:

HKLM\Software\Oracle\Key_<Oracle Home Name>\ORA<SID>_AUTOSTART
Start the database when the service starts

HKLM\Software\Oracle\Key_<Oracle Home Name>\ORA<SID>_PFILE
Location of the Configuration File

HKLM\Software\Oracle\Key_<Oracle Home Name>\ORA<SID>_SHUTDOWN
Shutdown the database when the service stops

HKLM\Software\Oracle\Key_<Oracle Home Name>\ORA<SID>_SHUTDOWNTYPE
type of shutdown to do when stopping the server

Helpful Links

Windows Service Not Starting Your Database?

ORA-12560 on NT

Thread: ORACLE not available problem 10g release 2

Thread: DB not open, with Windows Service