Saturday, February 27, 2010

How to Change / Reset ias_admin Password???

— ias_admin is user required to use Enterprise Manager Web (aka Application Server Console - iASConsole) Site.

— ias_admin password is set during Installation of Oracle Application Server (902, 904, 10.1.2.X) or Oracle Identity Management (Infra Tier 10.1.4.X)

—ias_admin account is NOT stored in OID (Oracle Internet Directory), It is stored in XML file (JAZN-XML - Java AuthoriZatioN) (More on JAZN-XML & JAZN-LDAP coming soon..)


You can reset/change ias_admin password in following ways

1. Using Enterprise Manager (Application Server Control) Web Site
–Login to Instance Home Page
–Click on Preferences on top right
–In new screen, click on “Change Password” on left menu
–Enter current password and New Password

2. Using Command line tool
emctl set password <old_password> <new_password>
like
emctl set password welcome1 welcome2
(Here welcome1 is current ias_admin password and welcome2 is new password which you
wish to reset)


If you don’t know current ias_admin password then change it in configuration file

3. Change ias_admin password directly in configuration file
–Backup $ORACLE_HOME/sysman/j2ee/config/jazn-data.xml
–Search for entry like below

<user>
<name>ias_admin</name>
<credentials>{903}8QkQ/crno3lX0f3+67dj6WxW9KJMXaCu</credentials>
</user>

and Update new password (welcome1 like )

<user>
<name>ias_admin</name>
<credentials>!welcome1</credentials>
</user>

Note: ! (Exclamation Mark in front of password. This signifies that password is stored in clear text)

Sunday, February 21, 2010

Managing Ports in Oracle Application Server (OAS)

The change of ports is required in Oracle Application Sever (OAS) is commonly required when doing any new Installation or reconfiguring the Installation. To change ports in Oracle Application Server (OAS) is a very difficult task and to be done very carefully.

Below is the link that i generally use whenever i require to change any port in Oracle Application Server (OAS).
Note: The below link applies to Oracle Application Server (OAS) 10g.

Managing Ports in Oracle Application Server (OAS)

If you have any queries/doubts, please leave a comment I'll reply ASAP.

Friday, January 22, 2010

Difference between Physical and Logical Standby Database

Physical Standby Database:
A physical standby database is a exact copy of the primary database. Oracle uses the primary database archive log file to recover the physical standby database.

We can open a physical standby database in read only mode, but at the time of read only mode, the received logs will not be applied.

When the logs are applied, the database is not accessible.

Logical Standby Database:
Logical Standby database are opened in read/write mode, even while they are is applied mode. That is, they can be used to generate reports and the like. It is indeed a fully functional database.

Bulk Delete Users From Oracle Internet Directory (OID)

Using ldapsearch and ldapdelete commands to remove OID users

1. First, you need to know what is the directory tree of users that you want to perform the deletion on (s_UserContainerDN), source of subscriber DN.

2. Get all the user entries first using ldapsearch command and spool it to a file

$ ldapsearch -h [oid_hostname] -p [oid_ldap_port] -D "cn=orcladmin" -w [password] -s one -b "cn=Users,[source subscriber DN]" "(objectclass=orcluserv2)" "cn=*" > users.ldif
Example:

$ ldapsearch -h [oid_hostname] -p [oid_ldap_port] -D "cn=orcladmin" -w [password] -s one -b "cn=Users,dc=spain,dc=oracle,dc=com" "(objectclass=orcluserv2)" "cn=*" > users.ldif

3. Edit the spool output ldif file, users.ldif to remove entires which you want to preserve such as orcladmin,Portal and public. You may want to preserve the following seeded users:

cn=orcladmin,cn=Users,[source subscriber DN]
cn=ias_admin,cn=Users,[source subscriber DN]
cn=PUBLIC,cn=Users,[source subscriber DN]
cn=Portal<*>,cn=Users,[source subscriber DN]
cn=sample<*>,cn=Users,[source subscriber DN]
cn=synd<*>,cn=Users,[source subscriber DN]
cn=uddi<*>,cn=Users,[source subscriber DN]

(Note:Example above include users/entries that could be seeded by certain midtier installs, so they may or may not exist in a particular OID)

4. Use the ldapdelete command along with the edited output ldif file to delete all the users:

$ ldapdelete -h [oid_hostname] -p [oid_ldap_port] -D "cn=orcladmin" -w [password] -c -v -f users.ldif

deleting entry cn=UNCLE_SAM,cn=Users,dc=spain,dc=oracle,dc=com
entry removed
deleting entry cn=LARRY_ELISSON,cn=users,dc=spain,dc=oracle,dc=com
entry removed

Note:Before removing users remove the users from the users.ldif which you dont want to delete.

There is one more command which is given below which might be helpful in some cases.
ldapadd -h myhost -p 389 -D "cn=orcladmin" -w welcome -f users.ldif

Sunday, January 10, 2010

Identifying unused indexes in Oracle9i

PURPOSE
-------

The purpose of this document is to explain how to find unused indexes
using the new feature in Oracle9: "Identifying Unused Indexes" via
ALTER INDEX MONITORING USAGE, as mentioned in Oracle9i Database
Administrator's Guide, Chapter 11.

The clause MONITORING / NOMONITORING USAGE is useful in determining
whether an index is being used.


SCOPE & APPLICATION
-------------------

This article is intended for database Administrators who want to
identify unused indexes in their database.


IDENTIFYING UNUSED INDEXES
--------------------------

You can find indexes that are not being used by using the ALTER INDEX
MONITORING USAGE functionality over a period of time that is
representative of your workload.

PART 1 will demonstrate the new feature using a simple example.

PART 2 will give a detailed instruction how to identify all unused
indexes in the database.


PART 1 - Monitoring usage of indexes - a simple example
---------------------------------------------------------

To demonstrate the new feature, you can use the following example:
(a) Create and populate a small test table
(b) Create Primary Key index on that table
(c) Query v$object_usage: the monitoring has not started yet
(d) Start monitoring of the index usage
(e) Query v$object_usage to see the monitoring in progress
(f) Issue the SELECT statement which uses the index
(g) Query v$object_usage again to see that the index has been used
(h) Stop monitoring of the index usage
(i) Query v$object_usage to see that the monitoring stopped


Detailed steps:

(a) Create and populate a small test table

create table products
(prod_id number(3),
prod_name_code varchar2(5));

insert into products values(1,'aaaaa');
insert into products values(2,'bbbbb');
insert into products values(3,'ccccc');
insert into products values(4,'ddddd');
commit;


(b) Create Primary Key index on that table

alter table products
add (constraint products_pk primary key (prod_id));


(c) Query v$object_usage: the monitoring has not started yet

column index_name format a12
column monitoring format a10
column used format a4
column start_monitoring format a19
column end_monitoring format a19
select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

no rows selected


(d) Start monitoring of the index usage

alter index products_pk monitoring usage;

Index altered.


(e) Query v$object_usage to see the monitoring in progress

select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
PRODUCTS_PK YES NO 04/25/2001 15:43:13

Note: Column MONITORING='YES', START_MONITORING gives the timestamp.


(f) Issue the SELECT statement which uses the index

First, make sure that index will be used for this statement.
Create plan_table in your schema, as required by Oracle Autotrace
utility:

@$ORACLE_HOME/rdbms/admin/utlxplan

Table created.

Use Oracle Autotrace utility to obtain the execution plan:

set autotrace on explain
select * from products where prod_id = 2;
.
.
Execution Plan
------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTS'
2 1 INDEX (UNIQUE SCAN) OF 'PRODUCTS_PK' (UNIQUE)

set autotrace off

Now, since you know the index will be used for this query, issue the
actual SELECT statement:

select * from products where prod_id = 2;

PROD_ID PROD_
---------- -----
2 bbbbb


(g) Query v$object_usage again to see that the index has been used

select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
PRODUCTS_PK YES YES 04/25/2001 15:43:13

Note: Column USED='YES'.


(h) Stop monitoring of the index usage

alter index products_pk nomonitoring usage;

Index altered.


(i) Query v$object_usage to see that the monitoring stopped

select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
PRODUCTS_PK NO YES 04/25/2001 15:43:13 04/25/2001 15:48:44

Note: Column MONITORING='NO', END_MONITORING gives the timestamp.



PART 2 - How to identify all unused indexes in the database
-------------------------------------------------------------

To identify all unused indexes in the database, you can do the
following:
(a) Create a SQL script to start monitoring all indexes except those
owned by users SYS and SYSTEM
(b) Create another script to stop monitoring all indexes except those
owned by users SYS and SYSTEM
(c) Connect as a user with ALTER ANY INDEX system privilege and run
the start monitoring script
(d) Perform normal activities in your database
(e) After a period of time that is representative of your workload,
run the stop monitoring script
(f) Query v$object_usage to see what indexes have not been used


Detailed steps:

(a) Create a SQL script to start monitoring all indexes except those
owned by users SYS and SYSTEM

set heading off
set echo off
set feedback off
set pages 10000
spool startmonitor.sql
select 'alter index '||owner||'.'||index_name||' monitoring usage;'
from dba_indexes
where owner not in ('SYS','SYSTEM');
spool off


(b) Create another script to stop monitoring all indexes except those
owned by users SYS and SYSTEM

set heading off
set echo off
set feedback off
set pages 10000
spool stopmonitor.sql
select 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
from dba_indexes
where owner not in ('SYS','SYSTEM');
spool off


(c) Connect as a user with ALTER ANY INDEX system privilege and run
the newly created script to start monitoring.

@startmonitor

(d) Perform normal activities in your database


(e) After a period of time that is representative of your workload,
connect as a user with ALTER ANY INDEX system privilege and run
the script to stop monitoring.

@stopmonitor


(f) Query v$object_usage in join with dba_indexes, to see what indexes
have not been used

select d.owner, v.index_name
from dba_indexes d, v$object_usage v
where v.used='NO' and d.index_name=v.index_name;



RELATED DOCUMENTS
-----------------

Note 1033478.6 Script Monitoring the Usage of Indexes(prior Oracle9i)
Note 1015945.102 How to Monitor Most Recently Accessed Indexessing script

Oracle9i Database Administrator's Guide

Saturday, January 9, 2010

Sql Scripts for knowing the transaction status....

select * from v$transaction;

--All tranasactions + sid and username
select s.sid,s.username, t.start_time, t.used_ublk, t.used_urec
from v$transaction t, v$session s
where t.ses_addr=s.saddr;

--All tranasactions + sid and username + first 64 bytes of SQL
select s.sid,s.username, t.start_time, t.used_ublk, t.used_urec,sql.sql_text
from v$transaction t, v$session s, v$sql sql
where t.ses_addr=s.saddr
and s.sql_address=sql.address and s.sql_hash_value=sql.hash_value;

How to do Object level tunning in Oracle???

Object-level tuning

1. Last analyzed date of all objects (table as well as indexes).
Command to analyze:
Analyze table table_name compute statistics; /* will estimate with 100 % of total records as default size*/
Analyze table table_name estimate statistics; /*will estimate with 1024 bytes as default size*/
Analyze table table_name estimate statistics for table sample 20 PERCENT; /* will estimate with20 % of total records as default size*/

Else we may use DBMS_STATS.GATHER_TABLE_STATS package e.g.

EXEC DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME =>null,
TABNAME => 'DW_CSC_COLLN',
CASCADE => TRUE,
METHOD_OPT => 'FOR COLUMNS’,
DEGREE=>8,
ESTIMATE_PERCENT=>100);

==> GATHER_INDEX_STATS Index statistics
==> GATHER_TABLE_STATS Table, column, and index statistics
==> GATHER_SCHEMA_STATS Statistics for all objects in a schema
==> GATHER_DATABASE_STATS Statistics for all objects in a database

Questions:
1. How to check the particular table has been analyzed with what percentage?
==> Sample_size column of dba_tables.

2. How to know when to analyze the object?
==> 10 % of DML occurrence is the thumb rule in particular time period( you may query user_tab_modifications to find out the occurrences of dmls since last analyzed statement executed on that particular object). To query from user_tab_modifications, monitoring should be on or execute alter table table_name monitoring on;
==> When 10 % of difference is there in num_rows column of dba_tables (information with data dictionary of total number of rows in one particular table) and count(*) output from “select count(*) from table_name;”

3. What does Analyzing of an object do?
By analyzing the objects, we can collect the latest object information (mentioned below) and stores/updates in Data Dictionary. It basically collects or updates statistics about specific objects in order to opt for best optimized execution plan.
==> Table Statistics
==> Number of rows
==> Number of blocks that have been used
==> Number of blocks never used
==> Average available free space
==> Number of chained rows
==> Average row length
==> Number of distinct values in a column
==> The low value and high value in a column
==> Index Statistics
==> Index level
==> Number of leaf blocks
==> Number of distinct keys

2. Check the blevel of indexes from dba_indexes
If Blevel of index is >=3 then
==> Rebuild the index with alter index index_name rebuild online;

3. Check the Type of Index
On the basis of cardinality we have to check whether the existing index on particular column is correct or not.
==> Less than 5% of difference in distinct_keys and count(*)/num_rows forces us to make bitmap indexes.
==> If the cardinality is less (<5%) then
Preferred index type is BITMAP
==> If the column is using a particular single row function (eg:UPPER,LOWER,SUBSTR etc) then
Preferred index type is FUNCTION BASED INDEX

4. Check the Status of Index
==> Status of indexes should be valid.

5. Check if the tables and indexes are in different tablespaces.

6. Check if there are any chained rows
How to check?
==> Chain_cnts column of dba_tables.
How to solve?
==> Reorganize the table by Exporting to different tablespace and import the same table back to original tablespace?
==> Or data move command. Alter table table_name data move;

7. Table fragmentation the tables.
How to check?
==> Compare blocks and empty_blocks columns of dba_tables, if difference is very high apply below solution. Below commands will work in same way as defragmentation works in windows.
How to solve?
Alter table shrink space cascade;
Before this we have to enable the row movement to allow shrinking.
Alter table table_name enable row movement;


Query To check all above things at one go…..
Query to obtain stats
select a.table_name "Table_name",
a.index_name "Index_name",
c.tablespace_name "Table's tablespace",
a.tablespace_name "Index Tablespace",
a.index_type,
a.blevel,
c.num_rows,
distinct_keys,
b.column_name,
a.status,
a.last_analyzed "index_lastanalyzed",
c.last_analyzed "table_lastanalyzed",
a.logging "index logging",
c.logging "table logging",
c.chain_cnt
from dba_indexes a, dba_ind_columns b, dba_tables c
where a.index_name=b.index_name
and a.table_name=c.table_name
and a.table_name =''