Sunday, August 12, 2007

Oracle Database 11g Release 1 (11.1) Installation ( CRS and Database Software )

Virag Sharma virag123@gmail.com
By default Oracle Enterprise Installation(OEL) installation not include following packages, so install it before starting Oracle Database & CRS 11g installation

The following packages (or later versions) must be installed on top of default OEL default installation

elfutils-libelf-devel-0.97
unixODBC-2.2.11
unixODBC-devel-2.2.11

As per Oracle Documentation
Packages for Asianux 2, Oracle Enterprise Linux 4.0, and Red Hat Enterprise Linux 4.0
The following packages (or later versions) must be installed: binutils-2.15.92.0.2-18
compat-libstdc++-33.2.3-47.3
elfutils-libelf-0.97-5
elfutils-libelf-devel-0.97-5
glibc-2.3.9.4-2.19
glibc-common-2.3.9.4-2.19
glibc-devel-2.3.9.4-2.19
gcc-3.4.5-2
gcc-c++-3.4.5-2
libaio-devel-0.3.105-2
libaio-0.3.105-2
libgcc-3.4.5
libstdc++-3.4.5-2
libstdc++-devel-3.4.5-2
make-3.80-5
sysstat-5.0.5
unixODBC-2.2.11
unixODBC-devel-2.2.11

Posted by at No comments:
Labels:

Sunday, July 29, 2007

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


Posted by at 4 comments:
Labels: , ,

Friday, July 27, 2007

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
Posted by at 6 comments:
Labels: , , ,

Saturday, July 14, 2007

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
Posted by at 2 comments:
Labels:

Thursday, July 12, 2007

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
Posted by at No comments:
Labels: ,
Subscribe to: Posts (Atom)
Google