Sunday, July 29, 2007

Oracle 11g IPS ( Incident Packaging Service)

Oracle 11g new Features , IPS ( Incident Packaging Service)

Virag Sharma

To make oracle support better , oracle 11g introduce new feature / Service known IPS ( Incident Packaging Service ) . Basically it is a part/extention of Oracle 11g new feature ADR ( Automatic Diagnostic repository)[Click Here for details ]. ADR is new concept in oracle 11g and it is just a file based repository of diagnostic data. ADR gives lot fexibility to maintain / handle diagnostic data.

When critical errors(i.e Problem) are detected, they automatically create an “incident” ( one occurrence of problem is Incident i.e relation between problem and incident is one to many).
Information(trace ,dump ) related to the incident is automatically captured in file based repository known as ADR ( Automatic Diagnostic repository), all the incident related files are taged with Incident Number and certain health checks are run automatically. This information can be packaged to be sent to Oracle support

Incident Packaging Service (IPS) wraps up all information about an incidentand allows you to send the whole package to Oracle Support.

Here is steps to collect and send data to support using IPS

$ adrci
adrci> help ips
adrci> show incident
( For example above command show incident No 9817 for ORA-600 [XYZ] )

adrci> ips create package incident
9817 <= ( it will give package No.)
adrci> ips create package incident 9817
Created package 4 based on incident id 9817, correlation level typical

ips add incident 9817 package 4
Added incident 9817 to package 4


adrci>>ips add file
/u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log package 4

Added file /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log to
package 4

adrci>>ips generate package 4 in /tmp
Generated package 4 in file /tmp/, mode

Send above created file to Support

TAG = > Oracle 11g New Features , Oracle 11g tips and tricks, Oracle Database 11g New Features for DBA, Oracle Database 11g New Features

Friday, July 27, 2007

How to check alert.log in Oracle 11g

Virag Sharma

Oracle 11g introduce new tool/utility called ADRCI known as ADR command line tool.
This tool allow user to interact with ADR ,check alert log, check health monitor(HM) status ,

Package incident and problem information into a zip file for send to Oracle Support. Etc.

No username/password need to log in to ADRCI, ADRCI interact with file system and ADR data is secured only by operating system permissions on the ADR directories.

adrci> set editor vi
adrci> show alert ( it will open alert in vi editor )
adrci> show alert -tail ( Similar to Unix tail command )
adrci> show alert -tail 200 ( Similar to Unix Command tail -200 )
adrci> show alert -tail -f ( Similar to Unix command tail -f )

adrci> show alert -tail 100 -f ( Similar to tail -100f )

Too list all the "ORA-" error run following command

show alert -P "MESSAGE_TEXT LIKE '%ORA-%'"


In 11g alert file is saved in 2 location, one is in alert directory ( in XML format log.xml ) and
old style alert file in trace directory. Both these directory reside inside ADR HOME. ADR (
Automatic Diagnostic Repository ) is a file system based directory structure outside of the database, So you can have access of diagnostic data even database is down.

New initialization parameter
DIAGNOSTIC_DEST decide where to store ADR data ( i.e diagnostic data). In oracle 11g parameter background_dump_dest, core_dump_dest, user_dump_dest is replaced by DIAGNOSTIC_DEST. Even you set background_dump_dest, core_dump_dest, user_dump_dest it not going to used i.e. 11g ignore these parameters.

SQL> show parameter diagnostic_dest

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

diagnostic_dest string /u01/app/oracle

More Oracle Database 11g New Features

  1. Oracle SQL REPLAY

  2. IPS ( Incident Packaging Service)
  3. Automatic Diagnostic Repository (ADR)

  4. Database Replay [Click for Detail]

TAG = > Oracle 11g New Features , Oracle 11g tips and tricks , Oracle Database 11g New Features for DBA , Oracle Database 11g New Features

Saturday, July 14, 2007

Oracle 11g and ACL ( access control list )

Virag Sharma
If your application code ( package , procedure , function ) uses utl_tcp , utl_smtp etc and you upgraded database to oracle 11g , then that code will not work even you gave execute permission on utl_tcp etc to user

You need execute addition steps and add user to ACL ( access control list )

How to check XML DB installed or not


SQL> select COMP_NAME, status from dba_registry;

-------------------------------------------- --------
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Oracle Real Application Clusters VALID
Oracle XML Database VALID


How to install XML DB
If above query not show XML DB component then run following sql to install XML DB

sqlplus "/ as sysdba"

How to add user to ACL
Following example show , how add scott user to ACL

'Comments ..', 'SCOTT', TRUE, 'connect');

SQL> Commit;

Note :- Don't forget to commit

As per Oracle 11g Upgrade manual

Configure Fine-Grained Access to External Network Services

Oracle Database 11g Release 1 (11.1) includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XML DB. If you have applications that use one of these packages, you must install Oracle XML DB if it is not already installed. You must also configure network access control lists (ACLs) in the database before these packages can work as they did in prior releases.

The following example first looks for any ACL currently assigned to host_name. If one is found, then the example grants user_name the CONNECT privilege in the ACL only if that user does not already have it. If no ACL exists for host_name, then the example creates a new ACL called ACL_name, grants the CONNECT privilege to user_name, and assigns the ACL to host_name.


acl_path VARCHAR2(4000);


SELECT acl INTO acl_path FROM dba_network_acls
WHERE host = 'host_name' AND lower_port
NULL AND upper_port IS NULL;

'user_name','connect') IS NULL THEN

'user_name', TRUE, 'connect');



WHEN no_data_found THEN



'ACL description', 'user_name', TRUE, 'connect');





A new security measure is introduced in this release for the following network-related
The invoker of those packages needs additional privileges to connect to an external
host or to resolve the name or the IP address of a host. The packages check the
invoker for the necessary privileges only when the calls are made at runtime and
raises an exception if the invoker lacks the privileges. This new security measure is
implemented by XML DB's access control list (ACL) mechanism and, therefore,
requires XML DB to be installed in order to use those packages.

Each external host that a database user wants to connect to or to resolve the name
or IP address for from the database is restricted by an access control list (ACL).
To grant a user the privileges for the host, the database administrator should create
an ACL, add the privileges to the ACL for the user, assign the ACL to the host
and commit the changes using the DBMS_NETWORK_ACL_ADMIN PL/SQL package.
For example, to give the users SCOTT and ADAMS the permission to connect
to via HTTP (namely to connect to TCP/IP port 80), the
database administrator should execute the following:

SQL> REM Creates a new ACL and adds SCOTT the privilege 
SQL> REM to the ACL to make TCP connections
> 'ACL for', 'SCOTT', TRUE, 'connect')
SQL> REM Adds ADAMS the privilege to the ACL to make TCP connections also
> 'ADAMS', TRUE, 'connect')
SQL> REM Assigns the new ACL to for TCP/IP port 80 (HTTP)
> '', 80)
SQL> REM Commits to make the ACL take effect

The invoker of the UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL packages needs the 'connect' privilege to make TCP/IP, HTTP, or SMTP connections to the external host. The invoker of the UTL_INADDR package needs the 'resolve' privilege to resolve the name or the IP address of the external host. Note that those privileges are not granted through the GRANT SQL statement but through the DBMS_NETWORK_ACL_ADMIN package.

The current ACL assignment to external hosts and the privileges currently defined
in the ACLs are shown through the system catalog views DBA_NETWORK_ACLS

The invokers of other database components that use those PL/SQL packages to
perform network operations from the database, which are XML DB's
HttpUriType, Oracle Multimedia (formerly interMedia), and Spatial, are
subject to the same network permission check and need the same privileges.

TAG=> Oracle 11g New Features, Oracle 11g tips and tricks, Oracle Database 11g New Features for DBA, Oracle Database 11g New Features

Thursday, July 12, 2007

Oracle 11g new features

Virag Sharma

As all other DBA , me too interested in Oracle 11g new feature to started reading/writing some good features of Oracle 11g.

For details about these Oracle 11g New features have a look on Site [ Click here ]

If you want to see summary about Oracle database Have a look on following link

Oracle Database 11g New Feature Summary [ Click Here ]

Above link also contains lots of other Best Site on 11g links and documents.

TAG => Oracle 11g New Features, Oracle 11g tips and tricks, Oracle Database 11g New Features for DBA, Oracle Database 11g New Features

Oracle Database 11g Launch party

Oracle Database 11g Launch party

Yesterday there was Oracle Database 11g launch party , and after launch part published New Feature of Oracle Database 11g

Also published lots of Oracle Database 11g Focus Area Whitepapers

As a all other DBA , I am intersted in on following Oracle 11g Database new features

Check/Click here to see following New Feature of Oracle Database 11g

1) Automatic Diagnostic Repository

2) Database Replay

3) Automatic Memory Tuning

4) Case sensitive password

5) Virtual columns and indexes

6) Interval Partition and System Partition

7) The Result Cache

8) ADDM RAC Enhancements

9) SQL Plan Management and SQL Plan Baselines

10) SQL Access Advisor & Partition Advisor

11) SQL Query Repair Advisor

12) SQL Replay Advisor

13) DBMS_STATS Enhancements

14) The Result Cache