Thursday, October 29, 2009

Oracle lock_sga parameter tips

A nice article by Burleson Consulting

http://www.dba-oracle.com/p_lock_sga.htm

Monday, October 26, 2009

Stop dbconsole job hung in status Stop Pending

Some times a job run from EM Console usually backup when you try to stop it it has a status : "Stop Pending". Until this status changes you can not delete the job and the job may hung on this status. If this happens do the following :

Loging as sysman :

select job_id, job_name, job_owner from mgmt_job where job_name like '%%';

exec mgmt_job_engine.stop_all_executions_with_id('');

select EXECUTION_ID, status from MGMT_JOB_EXEC_SUMMARY
where job_id ='' ;

Stopped status=8 , but you may see other statuses. If so check for EM the job status. If its still Stop Pending then force the stop

exec mgmt_job_engine.stop_all_executions_with_id('',TRUE);

Now check the status either by running the query or using the EM and the delete the job from EM console

Thursday, October 22, 2009

Clearing dbconsole agent state

Some times dbconsole fails to start if it has crashed unexpected. Mostly this is an agent issue that can not start or can not upload some files. Then you can try to clearstate agent.

export ORACLE_HOME=AGENT_HOME. This is usually the db home if you are using dbconsole.
If you are using Grid Control Agent has its own home.

emctl stop agent.

cd $ORACLE_HOME/host_SID/sysman/emd

rm agntstmp.txt lastupld.xml
rm upload/*
rm state/*
emctl clearstate agent
emctl start agent

After few minutes issue

emctl status agent

and you must see a similar output :

Last successful upload : 2009-10-22 07:31:47
Total Megabytes of XML files uploaded so far : 4.42
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 24.94%


And you are ok.

Monday, October 19, 2009

Primary Keys , Numbers or Characters

A very good article about this battle on ASK Tom Site.

Surrogate versus Natural Keys
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:689240000346704229


Identifying a Primary Key
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:708398700346544598

Thursday, October 15, 2009

Oracle 10gR2 and XML

A very interesting article on OTN about XML features.

http://www.oracle.com/technology/pub/articles/quinlan-xml.html

Wednesday, October 7, 2009

Stop mview from auto refreshing

A materialized view create with the automatic refresh can not be alter to stop refreshing. In order to do that you must broke the dbms_job that was created in order to refresh the view.

Connect as mview owner.

select * from user_jobs where broken ='N';

In the WHAT column for the mview refresh job you will see :

dbms_refresh.refresh('"[OWNER]"."[MVIEW_NAME]"');

Take the JOB_ID form the job column and execute:

begin
dbms_job.broken(JOB_ID,TRUE);
commit;
end;
/

And the mview will stop refreshing automatically.

If you want the mview to start refreshing again just run the job.

begin
dbms_job.run(JOB_ID);
commit;
end;
/

Then the mview will refresh and continue refreshing by the interval.