Oracle Apex Password Reset

You can reset the Oracle Application Express internal Admin password via this Apex supplied SQL - apxxepwd.sql

Handy values for Oracle Date Functions

1 / 24 one hour 1 / 1440 one minute 1 / 86400 one second

One way to test the use of the above:

SQL>ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SQL>SELECT SYSDATE FROM DUAL;
SQL>SELECT SYSDATE+1/86400 FROM DUAL;
SQL>SELECT SYSDATE+1/1440 FROM DUAL;
SQL>SELECT SYSDATE+1/24 FROM DUAL;

Spending Money

There are quite a few examples of legal tender printed with collectors in mind, for example a collection of "Proof" Coins honouring Sir Donald Bradman.

It's rare though to find them being produced for general circulation. Some nice examples are the Bradman 20 cent coin from Australia and the 50 cent Jonty Rhodes coin from South Africa. Also a 10 pound note from the Bank of England showing a cricket scene from Dicken's Pickwick Papers is pretty cool.

In my collection so far I have the following coins/medals Coins 'n More. Some I'm still trying to get hold of are World Cup commemoratives from Sri Lanka and India.

Interesting Memorabilia

Received a complimentary copy of a wonderful book, which I have advertised through my website. It contains the signature of every living New Zealand cricketer dating back to the 1930's as well as tons of illustrations, statistics etc etc in an attractive leather bound volume. Check out some scans of the signatures on my website Immortals and follow the links to the publisher if you wish to secure a copy for yourself.

PeopleSoft Status Lookups

Some useful queries joining the PSXLATITEM table to look up Process Monitor:

Run Status:

SELECT XLATSHORTNAME
     , FIELDVALUE
  FROM PSXLATITEM
 WHERE FIELDNAME='RUNSTATUS'
ORDER BY TO_CHAR(FIELDVALUE,'00');

Distribution Status:

SELECT XLATSHORTNAME
     , FIELDVALUE
  FROM PSXLATITEM
 WHERE FIELDNAME='DISTSTATUS'
ORDER BY TO_CHAR(FIELDVALUE,'00');

Output Destination Type:

SELECT XLATLONGNAME
     , FIELDVALUE
  FROM PSXLATITEM
 WHERE FIELDNAME='OUTDESTTYPE'
ORDER BY TO_CHAR(FIELDVALUE,'00');

Output Destination Format:

SELECT XLATLONGNAME
     , FIELDVALUE
  FROM PSXLATITEM
 WHERE FIELDNAME='OUTDESTFORMAT'
ORDER BY TO_CHAR(FIELDVALUE,'00');

PeopleSoft BBL Rogues

A tip from my friend David H.

To check on a unix server if a suspect pid is registering on the PeopleSoft Bulletin Board, run the following command:

tmadmin < bbi.in | grep {pid}

If the pid is not known to the BBL, you can use the unix kill command to get rid of it, or through tmadmin utility run bbclean.

Checking for Overdue Jobs in PeopleSoft

The following SQL is a little untidy as it's copied from a mod/plsql stored procedure I created to check for overdue jobs on the process scheduler. Being web-based, the orignal uses a Meta Refresh tag every 30 seconds to keep a proactive check on all Queued jobs in the next 60 minutes and alerts if any are overdue by more than a couple of minutes.

SELECT CASE WHEN sdate > 0 THEN to_char(trunc(sysdate)+sdate,'hh24:MI:SS') 
            ELSE
            '--:--:--'
            END OVERDUE
     , PRCSINSTANCE
     , PRCSTYPE
     , PRCSNAME
     , SERVERNAMERQST
     , SERVERNAMERUN
     , OPRID
     , RUNDTTM
     , XLATSHORTNAME            
  FROM (
SELECT SYSDATE -  A.RUNDTTM sdate 
     , A.PRCSINSTANCE
     , A.PRCSTYPE
     , A.PRCSNAME
     , A.SERVERNAMERQST
     , A.SERVERNAMERUN
     , A.OPRID
     , A.RUNDTTM
     , X.XLATSHORTNAME
  FROM PSPRCSRQST A
     , PSOPRDEFN B
     , PSPRCSPRFL C
     , PSXLATITEM X
 WHERE B.OPRID = A.OPRID
   AND B.PRCSPRFLCLS = C.CLASSID
   AND A.RUNSTATUS = X.FIELDVALUE
   AND A.RUNSTATUS = 5
   AND X.FIELDNAME = 'RUNSTATUS'
   AND A.RUNDTTM < SYSDATE + 60/1440
 order by A.RUNDTTM);

Oracle Database Cloning

To make a clone of an Oracle database from a standby database, you would need to follow these high-level steps:

Open Standby Database in Read Only Mode.


Generate dynamic script to copy files: SQL> select 'scp ' || name || ' ' || replace   (name,'{SOURCEDBNAME}','{TARGETDBNAME}') as filename from v$datafile;


Create a copy of the controlfile SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE;


Modify the controlfile for later use.   We generally make the following changes:
orig: CREATE CONTROLFILE REUSE DATABASE {SOURCEDBNAME} NORESETLOGS FORCE LOGGING ARCHIVELOG;
mods: CREATE CONTROLFILE SET DATABASE {TARGETDBNAME} RESETLOGS FORCE LOGGING NOARCHIVELOG;
Also Search and Replace {SOURCEDBNAME} with {TARGETDBNAME}.


  Shutdown the Source and Target Databases.


  Execute the dynamic script to copy files.


  Recreate the controlfile on the Target Database  SQL> STARTUP NOMOUNT
Execute the modified controlfile script.


 Open the Target Database  SQL> ALTER DATABASE OPEN RESETLOGS;


 Rename the Target Database  SQL> ALTER DATABASE RENAME GLOBAL_NAME TO {TARGETDBNAME};


 Open the Standby Database in recover mode.

PeopleSoft Jobs Stuck in Posting Mode

Every now and again the jobs run on the process scheduler will get stuck in "Posting" mode. Restarting the process scheduler or changing the status flag in psprcsrqst will usually do the trick, but sometimes this doesn't work either and PeopleSoft carries on trying to post the jobs.

The secret is to check the PS_CDM_TRANSFER and PS_CDM_LIST tables. This is where the jobs for posting are handed over. The following query will reveal what is stuck.

select b.prcsinstance
     , b.prcsname
     , b.prcstype
     , c.xlatshortname
     , a.transferinstance
     , a.servernamerun
     , a.transferstatus
     , a.nexttransferdttm
     , a.transferattemptcnt
  from ps_cdm_transfer a
     , ps_cdm_list b
     , psxlatitem c
 where a.transferinstance = b.transferinstance
   and b.diststatus = c.fieldvalue
   and c.fieldname='DISTSTATUS';

From the above SQL we can try and narrow down to a particular process which may be having trouble posting, by checking the distribution agent log and the webserver log of the report repository.

A sledgehammer approach could be to clear out the transfer table and place all processes with a distribution status of "Posting" to "Not Posted". This enables the option to Re-send Content the process via the Process Monitor. The SQLs to achieve this may look like:

update psprcsrqst set diststatus=4 where diststatus=7;
update psprcsque set diststatus=4 where diststatus=7;
truncate table ps_cdm_transfer;

The Re-send Content task can also be done via PL/SQL, but is not recommended as it allocates a single TRANSFERINSTANCE number to all of the stuck processes, which I don't think is an ideal situation. It may make further troubleshooting difficult and could possibly cause additional load on the distribution agent as it may submit the whole lot of the processes as one large job?? In any event, it's interesting to see the script you could use in conjunction with the above steps:

declare

cursor prcs is
select prcsinstance
  from psprcsrqst
 where diststatus=4; -- Not Posted

begin

 for i in prcs loop

 update psprcsrqst set diststatus=7 where prcsinstance=i.prcsinstance;

 update psprcsque set diststatus=7 where prcsinstance=i.prcsinstance;

 update ps_cdm_list set diststatus=8, transferinstance=0 where  prcsinstance=i.prcsinstance;

 commit;

 end loop;

end;
/

Another basic place to check is the Distribution tab under the Servers Definition page. Often the transfer method e.g. HTTP is overlooked there and this could cause the distribution to fail even if the Report Node is configured correctly.

Troubleshoot PeopleSoft Integration Broker

I've always been daunted by the process of troubleshooting EIP messaging failures. In particular messages from EXTernal sources. Not sure why, but the integration error logs don't seem to provide enough information and I always end up spending hours trying to figure out what's going wrong.

Well, I think I've simply been over-thinking the process and hence have over-complicated the task too much. The last couple of occasions I was able to fix things quite nicely and did so by following some very simple steps:

Step 1. Check the integrationGateway.properties file which lives on the webserver. Use Beyond Compare or some other such program to compare differences with an environment which is working. Make sure all entries are correct, especially the various gateways are set correctly.

If any changes are made, load the integrationGateway.properties file up through the PeopleSoft gateway page. Make sure to click "Apply" to Refresh this file.

Step 2. Check the gateway URL on the PeopleSoft gateways page.

Step 3. Check the PSMSGNODEDEFN table entries. Pay particular attention to the IBPASSWORD values to ensure they are correct. This can be tricky as they are encrypted, but have to make sure the password here is identical to the password on the external system.

Step 4. Perform ping tests via the Monitor Message pages.

These four simple steps seem to address majority of all issues.

Oracle Autonomous Transactions

Working as a Database Administrator, I always discover new things and seeing as this blog has been a waste of space so far, I figure I may as well start using it to jot down notes from work. At least I'll end up with a good knowledge repository of things I would like to store away for future reference.

The task at hand was to create a web-based stored procedure which would enable people from our Security team to add database users themselves, rather than forward on calls to the ever-busy DBAs.

So I started out by creating a mod/plsql stored procedure on an admin database, which would use database links to connect to the target database where the account needs to be created.

Problem Encountered:
Oracle does not allow DDL commands to run across a dblink.

Solution
Use the dblink to execute another stored procedure on the target, which would run the DDL commands.

Pretty straight forward at this point. Then comes the kicker ...

I needed to add an OUT parameter to the target procedure, so I could pass a message back to the calling source procedure advising the success/failure of the procedure at the target.

Problem Encountered:
Oracle Bug: where a combination of dblink, OUT parameter and DDLs are NOT allowed. According to MetaLink this would be rectified in version 11, but was hardly of any use to me at this point.

Solution:
Autonomous Transactions. Stumbled upon the use of Autonomous Transactions, which as I understand them, allow procedures to execute autonomously from the calling procedure. By running something like:

execute immediate 'DECLARE PRAGMA AUTONOMOUS TRANSACTION BEGIN END;';

I was able to have my cake and eat it too!

I Luv Cricket ..... but .....

I can never think of anything to talk about on the subject. The idea of this blog was to share heaps of knowledge and entertain visitors with trivia, jokes and anecdotes, but I guess I just don't have the knack of stringing words together.

I was contemplating doing myself a favour and deleting the blog. Then I thought, I may as well keep it going (boo, hiss, bad decision) and just use it for a variety of purposes. So starting today Nazim's Cricket Blog will contain 90% less cricket!