Oracle 11g IPS ( Incident Packaging Service)

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

Virag Sharma virag123@gmail.com

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 adrci> ips add incident 9817 package 4 Added incident 9817 to package 4 adrci> 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/ORA600kci_20070514184516_COM_1.zip, mode complete adrci>>

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

How to check alert.log in Oracle 11g

How to check alert.log in Oracle 11g

Virag Sharma virag123@gmail.com

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 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

$adrci adrci> 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 NAME TYPE VALUE --------------- --------------------------- ------------------------- 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

Oracle 11g and ACL ( access control list )

Oracle 11g and ACL ( access control list )

Virag Sharma virag123@gmail.com

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; COMP_NAME STATUS -------------------------------------------- -------- 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" SQL>@?/rdbms/admin/catqm.sql

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

SQL> exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('MY.xml', 'Comments ..', 'SCOTT', TRUE, 'connect'); SQL>exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('MY.xml','*.abc.com'); 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. DECLARE acl_path VARCHAR2(4000); BEGIN SELECT acl INTO acl_path FROM dba_network_acls WHERE host = 'host_name' AND lower_port IS NULL AND upper_port IS NULL; IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path, 'user_name','connect') IS NULL THEN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path, 'user_name', TRUE, 'connect'); END IF; EXCEPTION WHEN no_data_found THEN DBMS_ NETWORK_ACL_ADMIN.CREATE_ACL('ACL_name.xml', 'ACL description', 'user_name', TRUE, 'connect'); DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('ACL_name.xml','host_name'); END; COMMIT;

UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL, and UTL_INADDR PL/SQL Packages A new security measure is introduced in this release for the following network-related PL/SQL packages: UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL, and UTL_INADDR. 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 www.oracle.com 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 SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('acl_for_oracle.xml', - > 'ACL for www.oracle.com', 'SCOTT', TRUE, 'connect') SQL> REM Adds ADAMS the privilege to the ACL to make TCP connections also SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('acl_for_oracle.xml', - > 'ADAMS', TRUE, 'connect') SQL> REM Assigns the new ACL to www.oracle.com for TCP/IP port 80 (HTTP) SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('acl_for_oracle.xml', - > 'www.oracle.com', 80) SQL> REM Commits to make the ACL take effect SQL> COMMIT 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 and DBA_NETWORK_ACL_PRIVILEGES. 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

Oracle 11g new features

Oracle 11g New Features

Virag Sharma virag123@gmail.com

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 otn.oracle.com published New Feature of Oracle Database 11g

http://www.oracle.com/technology/products/database/oracle11g/pdf/database-11g-new-features-whitepaper.pdf


Also otn.oracle.com published lots of Oracle Database 11g Focus Area Whitepapers

http://www.oracle.com/technology/products/database/oracle11g/index.html

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