Monday, November 8, 2010

Check for view recompilation

Use the following to identify which DB's need view recompilation by using the following PL/SQL as the parameter for a SQL Script job:


WHENEVER SQLERROR EXIT FAILURE;
declare
xvar number;
begin
SELECT count(*) into xvar FROM sys.registry$history where ID = '6452863';
if xvar=0 then
dbms_output.put_line('Needs View Recompliation');
raise_application_error(-20101,'Needs View Recompilation');
else
dbms_output.put_line('View Recompliation Already Done');
end if;
end;
/


Run that against all targets, any that fail likely need the view recompilation job run.

Monday, August 16, 2010

Error after 11g upgrade

Saw the following error in utlrp after upgrading a db to 11gR2:
Warning: XDB now invalid, could not find xdbconfig


After a bit of digging I found metalink doc 944088.1.

Check your LD_LIBRARY_PATH for DB and Listener. If not set correctly you'll need to restart both DB and Listener, and then try utlrp again.

Wednesday, March 31, 2010

Now with syntax highlighting goodness

Via this excellent post, we now have syntax highlighting.


code goes here



Much better looking than quotes or pre alone.

RMAN duplicate set until clause for variable dates

I've used the following for my rman duplicate scripts to set a specific timestamp for clones.
set until time "to_date('03/29/2010 08:00:00','MM/DD/YYYY HH24:MI:SS')";

However, most of the time I just want to have my data as of an hour ago no matter what time I start the clone and don't want to edit the timestamp each time (less stuff to mess up). Apparently this syntax also works.
set until time "sysdate - 1/24";
You can take this a step further in complication with anything for dates, such as cloning to the most recent archivelog backup (assuming a scheduled archive log backup takes place every hour at 35 after)

set until time "trunc((sysdate - 30/1440),'HH24')+30/1440";

Sunday, March 7, 2010

Comparing files with slight differences


File compile-test.log is an output of a big compile from a database named TEST, and file is stored in TEST subdirectory.


File compile-prod.log is output of a big compile from a database named PROD, file is stored in PROD subdirectory.



diff PROD/compile-prod.log <(sed 's/TEST/PROD/g' ../TEST/compile-test.log

Saturday, January 30, 2010

Null in trigger source issue for expdp

Ran into a nasty little bug while doing a full database import using datapump.


ORA-39125: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS while calling DBMS_METADATA.CONVERT [TRIGGER:"SCHEMANAME"."SOME_TRIGGER_NAME"]
ORA-06502: PL/SQL: numeric or value error
LPX-00007: unexpected end-of-file encountered
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 6313

Found a number of Metalink DocID's that fit the bill, but the best suspect is 3897122.8. The issue is actually a bug in expdp where source with null lines causes expdp to basically create a corrupted export file. Fantastic.

Here's a query we used to identify the bad source code to be fixed:

select * from dba_source where text like '%'||CHR(0)||'%';

Found two triggers with null lines, probably put there by some kind of SQL dev tool. Rebuilt the triggers without null lines, and can now create an export file that can actually be imported.