Sunday, November 30, 2008

Oracle Applications Benchmarks

Just saw this article on Oracle site, benchmarks for medium and small configurations.

http://www.oracle.com/apps_benchmark/html/results.html

Hope this helps

DST Change - OEM start agent failed, agentTZRegion settings

Because of DST change, I saw the agents which were shutdown are not starting, tried manually but no luck.

$ emctl start agent

Oracle Enterprise Manager 10g Release 10.2.0.2.0.Copyright (c) 1996, 2006 Oracle Corporation. All rights reserved.Starting agent ...... failed.The agentTZRegion value in /home/oracle/product/10.2.0/em10g/agent10g/sysman/config/emd.properties is not in agreement with what agent thinks it should be.Please verify your environment to make sure that TZ setting has not changed since the last start of the agent.If you modified the timezone setting in the environment, please stop the agent and exectute 'emctl resetTZ agent' and also execute the script mgmt_target.set_agent_tzrgn(, ) to get the value propagated to repository.

Consult the log files in: /home/oracle/product/10.2.0/em10g/agent10g/sysman/log

Resolution
In emd.properties you need to correctly set the agentTZRegion value
agentTZRegion=-04:00

And login to OEM repository and run the following SQL to get the present values, so you can reset the value in repdb - login as SYS

select TARGET_NAME, timezone_region FROM mgmt_targets WHERE target_type = 'oracle_emd' AND target_name like '%hostname%';

TARGET_NAME TIMEZONE_REGION
-------------------------- --------------------------
hostname.domainname:2872 -05:00

Now reset the value to correct TZ settings

SQL> exec mgmt_target.set_agent_tzrgn('hostname.domainname:2872','-04:00');
SQL> commit;

Restart the agent, its successful

$emctl start agent

Oracle Enterprise Manager 10g Release 10.2.0.2.0.Copyright (c) 1996, 2006 Oracle Corporation. All rights reserved.Starting agent .............................. started.

OLAP Added to 10gR2 but XOQ component INVALID

Did not post new blogs for last couple of months, busy working on database upgrade, its a 10.2.0.3, size 5TB and 3 node RAC.

Recently I visited my favorite BLOG like every day/week, and at the same time we got a request to apply OLAP Patch 5746153 - which I applied successfully and done post-patch steps.
Component XOQ was INVALID, no matter what I do, its still Invalid. Did the usual steps - googled, searched metalink and then created tar. Finally I was able to get hold of good analyst who reffered me to note Note:466363.1, after following the steps I got below error.

SQL> select status from dba_registry where comp_id = 'XOQ';

STATUS
------------------------
INVALID

SQL> set serveroutput on size 10000
SQL> exec xoq_validate
compat -> 10.2.0

BEFORE BOOTSTRAPORA-37111: Unable to load the OLAP API sharable library: (Cannot map text forlibrary: mmap(0x0,0x13e6914, 0x5, 0x41, 58, 0x0) returns Permission denied.)
AFTER BOOTSTRAP: REGISTRY.INVALID
comp_name: Oracle OLAP API
version: 10.2.0.3.0
status: INVALID
PL/SQL procedure successfully completed.

$ls -l /home/oracle/10.2/lib//libolapapi10.sl
-rw-r--r-- 1 oracle dba 36056968 Apr 23 2007 /home/oracle/10.2/lib//libolapapi10.sl

Did the usual searching and finally did a CHMOD and ran xoa_validate to my surprise it got validated.

chmod 744 /home/oracle/10.2/lib//libolapapi10.sl

SQL> select status from dba_registry where comp_id = 'XOQ';

STATUS
------------------------
VALID

Then later I found its a know bug 6979371 and oracle ended-up publishing note 603179.1 for the same

Reference Metalink Notes
Note#: <466363.1>Titled: Oracle Olap API Invalid After Adding OLAP
Note#: <603179.1>Titled: Oracle Olap Api Invalid After Upgrade To 10.2.0.3

Friday, November 28, 2008

Flash Back Table

This article will help you recover a table upto a certain time. This Procedure was tested on Oracle 10g Release 2 Firstly create a test table which will be used for testing

Create table Test_tmp as select * from emp where 1=2;

Insert some values and issue a commit.
Now select the systimestamp from the database.

Select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
09-FEB-07 03.10.47.906882 PM +05:00

Now delete data from the Test_tmp table and commit it.

Delete from test;
Commit;

Now select the systimestamp again from the database.
select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
09-FEB-07 03.15.47.906882 PM +05:00

Now you know that table was truncated at 3:15. So we will perform recovery / Flashback till 3:11

INSERT INTO TEST_TMP
(SELECT * FROM TEST AS OF TIMESTAMP TO_TIMESTAMP(\'09-FEB-07 03:11:00\',\'DD-MON-YY HH24: MI: SS\'))

Commit;

Now you can see the data has been restored in the table.

SQL> Select * from TEST_TMP;

Sunday, November 23, 2008

AUTHENTICATION THROUGH OS

AUTHENTICATION THROUGH OS password defined by EXTERNALLY
---------------------------------------------------------------------------

step # 1

parameter must be like bellow

os_authent_prefix string OPS$

step # 2

know OS user name
SQL> select sys_context('USERENV','OS_USER') from dual;
SYS_CONTEXT('USERENV','OS_USER')
----------------------------------------------------------------------------
IBCSEBS07\Administrator

step # 3 user creation ( user name must be upper case)

SQL> create user "OPS$IBCSEBS07\ADMINISTRATOR" identified by externally;

User created.

SQL> grant connect,resource to "OPS$IBCSEBS07\ADMINISTRATOR";

Grant succeeded.

Recovery of Control file from trace

suppose all CONTROL is lost and every thing is ok. No RMAN backup is available.But control file backup as TRACE is exist.

Solution : We will up our database by TRACE control file backup.

Database : Now instance is down

step # 1

c:\> sqlplus /nologsql> conn /as sysdba
sql> startup nomount

step # 2
Copy : create controlfile script which is NORESETLOGS into command screen from trace file backup. as following

sql> CREATE CONTROLFILE REUSE DATABASE "DB1" NORESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'C:\ORACLE9I\ORADATA\DB1\REDO01.LOG' SIZE 100M,
GROUP 2 'C:\ORACLE9I\ORADATA\DB1\REDO02.LOG' SIZE 100M,
GROUP 3 'C:\ORACLE9I\ORADATA\DB1\REDO03.LOG' SIZE 100M

-- STANDBY LOGFILE
DATAFILE 'C:\ORACLE9I\ORADATA\DB1\SYSTEM01.DBF', 'C:\ORACLE9I\ORADATA\DB1\UNDOTBS01.DBF',
'C:\ORACLE9I\ORADATA\DB1\CWMLITE01.DBF',
'C:\ORACLE9I\ORADATA\DB1\DRSYS01.DBF',
'C:\ORACLE9I\ORADATA\DB1\EXAMPLE01.DBF',
'C:\ORACLE9I\ORADATA\DB1\INDX01.DBF',
'C:\ORACLE9I\ORADATA\DB1\ODM01.DBF',
'C:\ORACLE9I\ORADATA\DB1\TOOLS01.DBF',
'C:\ORACLE9I\ORADATA\DB1\XDB01.DBF',
'C:\ORACLE9I\ORADATA\DB1\RMAN_TS1.DBF',
'C:\ORACLE9I\ORADATA\DB1\TEST_TS1.DBF',
'C:\ORACLE9I\ORADATA\DB1\TEST_USERS1.DBF',
'C:\ORACLE9I\ORADATA\DB1\TEST_USERS2.DBF'
CHARACTER SET WE8MSWIN1252;

Control file created.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;
Database altered.

How to perform long deletes - (committing every n number of records)

When performing a DELETE from a table, Oracle will generate rollback. If the particular table you are trying to delete from contains a large number of records, it is possible for the transaction to fail because of the rollback segment not being large enough. One way to solve this issue is to use the TRUNCATE command. TRUNCATE does not generate rollback and has the added benefit of rebuilding any indexes on the table. Another solution would be to use the anonymous PL/SQL block below.

DECLARE

count NUMBER := 0;
total NUMBER := 0;

CURSOR del_record_cur IS
SELECT rowid
FROM .
WHERE
BEGIN
FOR rec IN del_record_cur LOOP
DELETE FROM .
WHERE rowid = rec.rowid;
total := total + 1;
count := count + 1;
IF (count >= 1000) THEN
COMMIT;
count := 0;
END IF;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Deleted ' total ' records from ..');
END;
/


Update
Soon after publishing this months DBA Tip, Geurts Maarten wrote in to indicate that the above PL/SQL block may not work where you may have either many processes running on the system or if the table is too large. If you do not have a rollback segment large enough, you may encounter the "snapshot too old" error. This is because the code block is modifing the snapshot the first cursor uses to get its rowids from.

You need to reopen the snapshot.
A PL/SQL BLock like:

BEGIN
loop
Delete .
where and rownum <>. where and rownum < 5000; -- exit the loop when there where no more 5000 reccods to delete.
exit when SQL%rowcount < 4999; -- commit to clear the rollback segments.
commit; -- this commit i forgot in the last mail, oops.
end loop;
commit; -- commit the last delete
END;

Note that the souce I typed is untested, it may have syntax errors. The point is: when you need big rollback statements it is getting slower. I have seen cases in a simular PL/SQL block (with an update instead of delete) where the first records were updated twice as fast as the last records. On the other hand, my constuction has to execute the SQL much more times, that is why i increased the commit size from 1000 to 5000.

RMAN Tablespace Point in Time Recovery Example

The following is a working example of RMAN TSPITR and was performed using Oracle 8.1.7.0.0 on Microsoft Windows 2000.

SCOPE & APPLICATION
--------------------------
Intended audience is for DBA's using RMAN as a backup solution.

RMAN Tablespace Point in Time Recovery Example
-----------------------------------------------------
RMAN Tablespace Point in Time Recovery Example

The following is a working example of RMAN TSPITR and was performed using Oracle 8.1.7.0.0 on Microsoft Windows 2000.

This example assumes:
1. A tns alias is used to connect to the RMAN catalog and target databases only. We use a local connection for the auxiliary instance. You can use a tns alias to connect to the auxiliary database if you wish, but it’s not necessary.
2. The production instance database files are located on D:\ORACLE8I\ORA817\ORADATA\ORCL817.
3. The production instance log files are located on D:\ORACLE8I\ORA817\ORADATA\ORCL817.
4. The auxiliary instance files are located on D:\ORACLE8I\ORA817\ADMIN\AUX1. We have chosen to store all auxiliary files in one location to help simplify the process.
5. RMAN backups are on disk; so all channels are allocated to disk.

===========================
I. Create the auxiliary initSID.ora.
===========================
1. The auxiliary instance MUST reside on the same machine as the target database.
2. Precreate one directory for all of the auxiliary files to be stored in.
This example uses D:\ORACLE8I\ORA817\ADMIN\AUX1. 3. Copy the initSID.ora from the target database to D:\ORACLE8I\ORA817\ADMIN\AUX1\initAUX1.ora.
4. Modify the initAUX1.ora file to include the following:
LOCK_NAME_SPACE=AUX1
DB_FILE_NAME_CONVERT=("D:\ORACLE8I\ORA817\ORADATA\ORCL817", “D:\ORACLE8I\ORA817\ADMIN\AUX1")
LOG_FILE_NAME_CONVERT=("D:\ORACLE8I\ORA817\ORADATA\ORCL817","D:\ORACLE8I\ORA817\ADMIN\AUX1")

Note: Using LOCK_NAME_SPACE allows us to have two databases with the same name on the same NT machine.

Note: Using DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT converts one string to another. Database files in D:\ORACLE8I\ORA817\ORADATA\ORCL817 will retain their same name, but will be restored to D:\ORACLE8I\ORA817\ADMIN\AUX1. You could also use "configure auxname" in RMAN to perform the datafile conversions.
5. Modify the initAUX1.ora file to change the controlfile location:
control_files = ("d:\oracle8i\ora817\admin\AUX1\control01.ctl")
6. Modify the initAUX1.ora file to comment out the following:
#log_archive_start = true#log_archive_dest_1 = “location=…#log_archive_format = %%ORACLE_SID%%T%TS%S.ARC

=================================
II. Create the auxiliary instance
=================================
1. From a command prompt type the following:
C:\> oradim -new -sid AUX1 -intpwd oracle -startmode manual -pfile D:\ORACLE8I\ORA817\ADMIN\AUX1\initAUX1.ora
2. Verify the instance has been created in “Services” and verify it is started.

=================================
III. Start the auxiliary instance
=================================
From a command prompt:
1. Set ORACLE_SID=AUX1
2. Start sqlplus and connect internal
3. Startup nomount pfile=$D:\ORACLE8I\ORA817\ADMIN\AUX1\initAUX1.ora
4. After the instance is successfully started, exit the session.

==================================================
IV. Connect to the catalog, target, and auxiliary databases using RMAN ==================================================
1. Make sure the target database is open.
2. Make sure the auxiliary instance is started and in no mount mode.

From a command prompt:

3. Set ORACLE_SID=AUX1
4. Set NLS_DATE_FORMAT=MON-DD-YYYY HH:MI:SS
5. Connect to RMAN:

rman target sys/password@target catalog rman/rman@rcat auxiliary / trace=d:\tspitr.log

(This will connect you to the target, catalog, and auxiliary database. It will also log the TSPITR session to "d:\tspitr.log")

===============
V. Start TSPITR
===============
run {
ALLOCATE AUXILIARY CHANNEL A1 TYPE DISK;
ALLOCATE CHANNEL c1 TYPE DISK;
recover tablespace users until time "TO_DATE('MAR-05-2002 11:27:00', 'MON-DD-YYYY HH:MI:SS')";
}

========================
VI. Testing the Recovery
========================
1. Connect as sys
2. Put the recovered tablespace online
3. Verify the object(s) exists.

Saturday, November 22, 2008

CURRENT / ACTIVE REDO LOG FILE LOST

SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 79693156 bytes
Database Buffers 83886080 bytes
Redo Buffers 2945024 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1:
'E:\ORACLE\PRODUCT\10.2.0\ORADATA\DBG2\REDO03.LOG'
ORA-00312: online log 3 thread 1:
'E:\ORACLE\PRODUCT\10.2.0\ORADATA\DBG2\SYSTEM\REDO03B.LOG'

SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

Sunday, November 16, 2008

oracle.apps.fnd.common.PoolException: Exception creating new Poolable object.

oracle.apps.fnd.common.PoolException: Exception creating new Poolable object.


Following error may appear when you click on AppsLocalLogin.jsp link while doing adconfig on DB tier:JSP errororacle.apps.fnd.common.PoolException: Exception creating new Poolable object.

It is best to have the services down while doing autoconfig, to avoid such errors.