HEMESH ORAWORLD: October 2010

Tuesday, 26 October 2010

Order by Month

Sometime we stuck to get our desire output.So its good practice we kept such information where its easily available.
Good Luck.
e.g.
SELECT TO_CHAR (TRUNC (dateordered, 'mm'), 'Month'),
MEDIAN (dateshipped - dateordered)
FROM orders
GROUP BY TRUNC (dateordered, 'mm')

ORDER BY TRUNC (dateordered, 'mm');

e.g.
select a.id,a.name,TO_CHAR(trunc(ah.date_viewed,'mm'),'Mon-yyyy') "Month Downloaded",count(*) "No of times Viewed"
from articleviewhistory ah,article a
where ah.article_id = a.id
and ah.article_id in (1085,984,961,900,638,630,628,624,613,611)
group by a.id, a.name, trunc(ah.date_viewed,'mm')
order by 2,trunc(ah.date_viewed,'mm');

e.g.
select to_char(day,'MON-YYYY'), sum(daily_growth_mb)
from db_growth
group by to_char(day,'MON-YYYY')
order by to_date(to_char(day,'MON-YYYY'),'MM-YYYY') desc;
or best one

select to_char(trunc(date_created,'MM'),'MON-YYYY'),count(*) from twitter
group by trunc(date_created,'MM')
order by trunc(date_created,'MM')

Wednesday, 13 October 2010

Access Control List (ACL) Problems when using the UTL_TCP Package for FTP in Oracle 11g

I am sure you have read this. But pay attention that it is address to:
- particular IP address or host name
- particular Oracle user

First, create an ACL:

begin
dbms_network_acl_admin.create_acl (
acl => 'utlpkg.xml',
description => 'Normal Access',
principal => 'CONNECT',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
end;

Here the parameter principal => 'CONNECT' indicates that the ACL applies to the CONNECT role. You can define a user or a role here. The ACL is created as a file called utlpkg.xml.
After the creation, you can check to make sure the ACL was added:

SELECT any_path
FROM resource_view
WHERE any_path like '/sys/acls/%.xml';

The output is:
ANY_PATH
----------------------------------------------------------------------------
/sys/acls/ANONYMOUS/ANONYMOUS3553d2be53ca40e040a8c0680777c_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f93feb8dde040a8c068075b7_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f944b8dde040a8c068075b7_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f948b8dde040a8c068075b7_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f94cb8dde040a8c068075b7_acl.xml
/sys/acls/all_all_acl.xml
/sys/acls/all_owner_acl.xml
/sys/acls/bootstrap_acl.xml
/sys/acls/ro_all_acl.xml
/sys/acls/ro_anonymous_acl.xml
/sys/acls/utlpkg.xml

Note the last line in the output, which shows the ACL you just created. Next, add a privilege to this ACL. In this example, you are trying to limit this ACL to the user SCOTT. You can also define start and end dates.
begin
dbms_network_acl_admin.add_privilege (
acl             => 'utlpkg.xml',
principal       => 'SCOTT',
is_grant       => TRUE,
privilege       => 'connect',
start_date       => null,
end_date       => null);
end;

Assign hosts and other details that will be subject to this ACL:
begin
dbms_network_acl_admin.assign_acl (
acl => 'utlpkg.xml',
host => 'www.proligence.com',
lower_port => 22,
upper_port => 55);
end;

In this example, you are specifying that "the user SCOTT can call only the host www.proligence.com and only for the ports 22 through 55, and not outside it." Now, let's try it:
SQL> grant execute on utl_http to scott
2 /

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> select utl_http.request('http://www.proligence.com') from dual;
select utl_http.request('http://www.proligence.com') from dual
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1

Note the error "ORA-24247: network access denied by access control list (ACL)." The user called the http server on port 80, which is outside the allowed range 22-55. Therefore the action was prevented.
Now, add another rule to allow the communication:

1 begin
2 dbms_network_acl_admin.assign_acl (
3 acl => 'utlpkg.xml',
4 host => 'www.proligence.com',
5 lower_port => 1,
6 upper_port => 10000);
7* end;
8 /

PL/SQL procedure successfully completed.

SQL> conn scott/tiger
Connected.
SQL> select utl_http.request('http://www.proligence.com') from dual;

UTL_HTTP.REQUEST('HTTP://WWW.PROLIGENCE.COM')
--------------------------------------------------------------------------------
</iframe><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<HTML><HEAD><TITLE>Proligence Home</TITLE>
<META http-equiv=Content-Language content=en-us>

Tuesday, 12 October 2010

Each OMS Startup Adds the Error "Integration Class not found" in the emoms Logfiles

Applies to:

Enterprise Manager Grid Control - Version: 10.2.0.5 and later [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

Each time 10.2.0.5 OMS is restarted, the following errors are logged in $OMS_HOME/sysman/log/emoms.trc file:

<Timestamp> [Orion Launcher] ERROR app.ContextInitializer contextInitialized.453 - Integration Class not found: oracle.sysman.test.pp.common.intg.ProvTestIntg
<Timestamp> [Orion Launcher] ERROR app.ContextInitializer contextInitialized.453 - Integration Class not found: oracle.sysman.pp.paf.sample.ui.intg.PAFDemoIntegration

Cause

This is due to an internal bug: "error in emoms.trc:oracle.sysman.pp.paf.sample.ui.intg.pafdemointegration". The bug is fixed in Grid Control 11.1.

Solution

These error messages can be safely ignored. They are due to some misconfigured integration class XML files, but do not prevent the Provisioning Framework to work as expected.

Workaround

1. Backup and edit $OMS_HOME/j2ee/OC4J_EM/applications/em/em/WEB-INF/config/empp_intg.xml.
Remove the line:

2. Backup and edit $OMS_HOME/j2ee/OC4J_EM/applications/em/em/WEB-INF/config/demo_intg.xml.
Remove the line:

3. Bounce the OMS.

Saturday, 9 October 2010

ORA-12162: TNS:net service name is incorrectly specified

[oracle@Grid-EM ~]$ echo $ORACLE_SID

oraworld

[oracle@Grid-EM ~]$ echo $ORACLE_HOME

/oracle/product/11.2.0/dbhome_1

[oracle@Grid-EM ~]$ echo $TNS_ADMIN

[oracle@Grid-EM ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 9 13:59:01 2010

SQL> conn / as sysdba

ERROR:

ORA-12162: TNS:net service name is incorrectly specified

]$ cat initoraworld.ora

###########################################

# Database Identification

###########################################

db_domain=Grid-Em.co.uk

db_name=oraworld

*************** parameter file is case sensitive it follow ORACLE_SID variable value.

it means if ORACLE_SID=ORAWORLD then parameter file should be initORAWORLD.ora , spfileORAWORLD.ora

and if ORACLE_SID=oraworld then parameter file should be initoraworld.ora ,spfileoraworld.ora ******************************************************

[oracle@Grid-EM ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-OCT-2010 14:06:39

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Grid-EM.co.uk)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 09-OCT-2010 13:13:37

Uptime 0 days 0 hr. 53 min. 2 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File /oracle/diag/tnslsnr/Grid-EM/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Grid-EM.co.uk)(PORT=1521)))

Services Summary...

Service "Oracle8" has 1 instance(s).

Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...

Service "oraworld" has 1 instance(s).

Instance "oraworld", status UNKNOWN, has 1 handler(s) for this service...

Service "oraworld.Grid-Em.co.uk" has 1 instance(s).

Instance "oraworld", status READY, has 1 handler(s) for this service...

Service "oraworldXDB.Grid-Em.co.uk" has 1 instance(s).

Instance "oraworld", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@Grid-EM ~]$ export ORACLE_SID=oraworld

[oracle@Grid-EM ~]$ export ORACLE_HOME=/oracle/product/11.2.0/db_home1

--- sometimes even though "echo" display value of "ORACLE_SID & ORACLE_HOME" but they are not defined in environment. So it better we "export" it again manually and try it which I did in above steps.

[oracle@Grid-EM ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 9 14:08:20 2010

SQL> conn / as sysdba

Connected to an idle instance.

everything was fine here Listener.ora,TnsNames.ora,Init.ora but Only Environment variable ORACLE_SID was not "export" properly.

******************** **********Some times small mistake create big chaos***************

if you lost your init.ora you may get some where here "/oracle/admin/<ORACLE_SID>/pfile"