Thursday, December 31, 2009

Ten Great Tips For 2010

Ten Great Tips For 2010

1. Stay out of Trouble.

2. Aim for Greater Heights.

3. Stay Focused on your Job.

4. Exercise to Maintain Good Health.

5. Practice Team Work.

6. Rely on your Trusted Partner to Watch your Back. Take your Time Trusting Others.

7. Save for Rainy Days.

8. Rest and Relax.

9. Always Take Time to Smile.

AND

10. Realize That Nothing is Impossible.

Wishing You and Your Family A Very Happy, Prosperous and Healthy New Year 2010!

Tuesday, December 15, 2009

Export issue in 11g (11.1.0.7) with R12 (12.1.1)

I got one request to export entire R12 (12.1.1) database. I thought it going to be easiest task for me, but it turn to be most challenging for me. This is how proceed:
1. Create Directory in database:
create directory SUNEXP as '/sundeep/exp';
2. Grant read, write on SUNEXP to DBA role, as I want to start expdp as system.
grant read,write on directory EXPDIR to "DBA";
3. Ran expdp full export:
expdp system/password DIRECTORY=SUNEXP FULL=Y COMPRESSION=ALL DUMPFILE=exp_sundeep.dmp LOGFILE=exp_sundeep.log

I thought I am done and it will gona take its own sweet time. But it failed at very first step:
----------------------------------------------------------------------------------
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS []
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_AW_EXP".SCHEMA_CALLOUT(:1,0,1,'11.01.00.00.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 923
ORA-33272: Analytic workspace ZPB.ZPBANNOT cannot be opened.
ORA-00600: internal error code, arguments: [xschkmk:!dict], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 7834
---------------------------------------------------------------------------------------
Now the issue is from where to start troubleshooting it. Then I thought of running this as normal exp command:
exp system/password FULL=Y FILE=exp_sundeep.dmp LOG=exp.log statistics=none feedback=1000000 buffer=2000000 compress=Y FILESIZE=4294967296 resumable=N

It started gracefully and end was not that graceful and I got the same error:
----------------------------------------------------------------------------------------
. exporting post-schema procedural objects and actions
EXP-00008: ORACLE error 33272 encountered
ORA-33272: Analytic workspace ZPB.ZPBANNOT cannot be opened.
ORA-00600: internal error code, arguments: [xschkmk:!dict], [], [], [], [], [], [], [], [], [], [], []ORA-06512: at "SYS.DBMS_AW", line 67
ORA-06512: at "SYS.DBMS_AW", line 131
ORA-06512: at "SYS.DBMS_AW", line 933
ORA-06512: at "SYS.DBMS_AW", line 1295
ORA-06512: at "SYS.DBMS_AW_EXP", line 575
ORA-06512: at "SYS.DBMS_AW_EXP", line 697
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_AW_EXP.schema_info_exp
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.
---------------------------------------------------------------------------------------
Yup now there seems to be some issue. I focused on "ORA-33272: Analytic workspace ZPB.ZPBANNOT cannot be opened" which is my key words to start working on this issue.

SQL> select aw_name, aw_version from all_aws where owner='ZPB';

AW_NAME AW_V
------------------------------ ----
ZPBANNOT 9.1
ZPBCODE 10.2
ZPBDATA 9.1

See here was the issue, ZPBANNOT was 9.1 version and my db was 11.1. I thought of export is alone and delete it so that I can proceed and once done I can import it back. Following are the command do the same:
set serveroutput on
exec dbms_aw.execute('aw attach ZPB.ZPBANNOT');
exec dbms_aw.execute('limit name to all');
exec dbms_aw.execute('allstat');
exec dbms_aw.execute('export all to eif file ''SUNEXP/ZPB.ZPBANNOT_old.eif'' ');

When I ran first command it failed:
set serverout on
SQL> exec dbms_aw.execute('aw attach ZPB.ZPBANNOT RW');
exec dbms_aw.execute('aw attach ZPB.ZPBANNOT RW')
*
ERROR at line 1:
ORA-33272: Analytic workspace ZPB.ZPBANNOT cannot be opened.
ORA-00600: internal error code, arguments: [xschkmk:!dict], [], [], [], [], [],[], [], [], [], [], []ORA-06512: at "SYS.DBMS_AW", line 93
ORA-06512: at "SYS.DBMS_AW", line 122
----------------------------------------------------------------------------------------
One of the note says ZPBDATA or ZPBANNOT are obsolete objects and we can delete them.
I did this to delete them

set serveroutput on
exec dbms_aw.execute('aw delete zpb.zpbdata')
exec dbms_aw.execute('aw delete zpb.zpbannot')

Then started the same expdp command which I used earlier and this time it worked. It looks really simple but it ate my 1.5 days to resolve this issue.

Happy Troubleshooting !!!

Wednesday, December 9, 2009

OPP on Virtual Host

In one of my earlier post I have mentioned how to use Virtual host for Concurrent manager. In this post I am specifically talking about issue you might encounter while using report output as xml and it doesn't show the actual output rather it shows html tags. So just understand the concept how OPP is being used in xml reports and then look for solution.

An Application user submits an XML Publisher based report.
The Standard Concurrent Manager process the request.
The XML data file is generated by the standard concurrent manager.
This can be done by various methods:
Oracle Reports - Report Definition File (RDF)
XML Publisher Data Template - XML data template linked to the Data Definition
Any other process that produces XML output
A Post processing action defines that the output needs to be generated by the Output Post Processor hence it is triggered by the standard manager.
The Output Post Processor generates the final report and informs the standard Concurrent Manager whether that was successful.
The Standard Concurrent Manager finalizes the concurrent request.

Now the error in report:
----------------------------------
One or more post-processing actions failed. Consult the OPP service log for details.

Now the error in OPP log:
----------------------------------
oracle.apps.fnd.cp.util.RemoteFileException: An Error occured during file transmission in the FND File Server. The Applications File Server could not open the file 12345333 for read.

Now the resolution:
----------------------------------
Was worried why OPP is not able to read the file, we have Shared APPL_TOP too. Then though it might be due to virtual host. Then came across one note: 824887.1, it was like sure shot for me and it worked. There is a profile option "Concurrent: Force Local Output File Mode" it was set to No I made it Yes and checked the failed request. Vola it worked as expected and my team mates were jumping. For more detail about this profile look at metalink note mentioned earlier.

Happy Troubleshooting !!!

Sunday, November 29, 2009

Installing VMware with Oracle Enterprise linux 64 bit

Last few days I was busy with installating R 12 (12.1.1) on VMware. It was very hectic but learning experience for me. In my previous post I have put screens for installing VMware on Windows 7 and Oracle Enterprise linux over and above VM. Here are the explaination of all figures.
Fig 1 : This is the first screen which will see after you click on installation file for VMware 2.0.1, which I downloaded from http://www.vmware.com/download/server.
Fig 2 : It ask for location. I installed it on my USB external drive so I have it as F drive.
Fig 3 : Now Enter FQDN and change VM Storage Path too.
Fig 4 : Installation in Progress.
Fig 5 : Once the installation is done, you will have an icon for VMware to connect. Just double click on it and make sure you added hostname.domainname in the windows/system32/driver/etc/hosts file. And in IE trusted domain too. Make sure you have username and password for the user you installed on windows session. As it takes Windows username and password for access. Once you are in we have to create Virtual Machine. This is the first screen where you have to give VM name. I gave it RAC1.
Fig 6 : Select Guest operating system, I selected Linux and in version other linux 64 bit.
Fig 7 : Give Memory size, I gave it 1024 MB.
Fig 8 : Create VM disk, I have size of 10GB for this.
Fig 9 : Network Connection keep default.
Fig 10 : Select the place where you will have your Guest OS. As I have DVD for the same I selected E drive.
Fig 11 : I didn't added Floppy Drive and Added USB Controller.
Fig 12 : That's the Final screen and select Finish.
Fig 13 : Once you login now you will see RAC1 (VM), but you have to install some add in for VM connection, so look at the screen and install it.
Fig 14 : Add-in getting installed.
Fig 15 : Once you power on the VM and click on that, make sure you have DVD in your Laptop, as it will going to install OS now.
Fig 16 : Select yes on this message.
Fig 17 : Keep selection as shown in fig.
Fig 18 : Select yes on this figure.
Fig 19 : Add hostname and IP manually.
Fig 20 : Add Root Password.
Fig 21 : Click next to start installation.
Fig 22 : Progress and you are done with installation. It will ask for reboot and some more question to answer and you are done with installation.

Happy Troubleshooting !!!

Installing VMware with Oracle Enterprise Linux 64 bit























Sunday, October 18, 2009

Couldn't open ICX Application Message file

One off my team mate reported an issue while opening forms in Purchasing Super user he encountered error message:
"Message Dictionary couldn't open the ICX application message file. Cannot open file /u02/apps/apps_st/appl/icx/12.0.0/mesg/US.msb for reading."

When I checked the location it was not there. Then I though running generate message file from adadmin but it didn't help. Here is the solution :

We have run Concurrent request "Generate Messages" for "Oracle iProcurement" and it will generate US.msb file under required location.

Happy Troubleshooting !!!

R12 New features

While using R12 I come across few things which might help you guys too:

1. It doesn't require to change apps password in any config files. So no need to run autoconfig if you used to do it.
2. All startup files are in $INST_TOP/admin/scripts
3. While creating Share Appl Top we can keep all other tops as read only except INST_TOP.
4. While running $INST_TOP/admin/scripts/adautocfg.sh it require apps password only. No need to provide Context file location any more.
5. CONTEXT File location is $INST_TOP/appl/admin
6. If you want to install R12 on virtual host start rapidwiz as "rapidwiz -servername virtual"
7. Just change s_cphost to physical hostname and run autoconfig, otherwise ICM and WF Mailer container components will have issue while starting.

I will keep on posting stuff as and when I come across. Till then

Happy Trobleshooting !!!

How to change Oracle user ORACLE_HOME

We have to change our ora user $ORACLE_HOME from /u02/oracle to /u02/oracle/11.1.0. Here are steps which I have followed and done this.

1. Run adpreclone.pl on dbtier as oracle user
perl adpreclone.pl dbTier
2. Shutdown down database and listener.
3. mv all files from /u02/oracle to /u02/oracle/11.1.0
4. run adcfgclone.pl for binaries only to avoid db creation
perl adcfgclone.pl dbTechStack
It will ask you certain info, please provide it accordingly.
5. update profiles from $OH/appsutil/install/
sqlplus "/ as sysdba" @adupdlib.sql so
6. change directory to $OH/appsutil/clone/bin
perl adcfgclone.pl dbconfig $OH/appsutil/context_file.xml
7. run autoconfig on appstiers too.
8. Start all services and check.

Happy Troubleshooting !!!

MWA server in R12

If you are suppose to run MWA server in R12 you might feel like something is missing here from 11i. So just read it and you will be able to handle it.
Prior to R12:
1. The MWA server startup files were located under "$MWA_TOP/bin" directory.
2. The MWA Server configuration files were located under "$MWA_TOP/secure" directory.
In R12
1. Scripts are under $INST_TOP/admin/scripts
2. Config files are under $INST_TOP/admin/install

Here are few commands and which we used to use in 11i, this is how we are going to use them in R12:
1. Startup the MWA server
mwactl.sh start
2. Shutdown the MWA server
mwactl.sh -login adminuid/pwd stop_force
3. Startup MWA Dispatcher
mwactl.sh start_dispatcher
4. Shutdown MWA Dispatcher
mwactl.sh stop_dispatcher

By default these scripts were not included in adstpall.sh and adstrtal.sh scripts. To add them enable
1. s_other_service_group_status
2. s_mwastatus
in context file and run autoconfig.

If you want to check port number for mwa. Look at following paramters in context file:
1. s_mwaPortNo.
2. s_mwaTelnetPortNo.

Happy Troubleshooting !!!

Rename Temp datafiles in 11G

Sorry all its big gap I took to post any update here. Biggest problem is office security. Now I can't access my blog site from office network. So I have to wait till I get some time at home. And as you might know this is festival season here in India to took so long. Any ways come to the point. Recently we have installed R 12.1.1 fresh installation on solaris and had to change many things after install. Temp datafiles location is one of them. But I got confused initially. Becuase when I was checking tablespace_name, file_name from dba_temp_files, I was getting TEMP1 and TEMP2 as tablespace names. But when I checked temporary_tablespace in dba_users I saw it as TEMP. So was worried how come? where is the link between these two miss matched things. Temporary Tablespace Group were the cause for it. Or I would say solution for this.
Here is the brief information about them:
1. A temporary tablespace group must consist of at least one tablespace. There is no explicit max number of tablespaces.
2. If you delete all members from a TTG, the group is automatically deleted as well.
3. When you assign a temporary tablespace to a user, you can use the TTG name instead of the actual temporary tablespace name. you can also use the TTG name when you assign the default temporary tablespace for the database.

This is how we can create one TTG;
1. Create Temporary Tablespace temp1 tempfile '/u02/oracle/temp1_01.dbf' size 100M tablespace group temp;
2. Add more via : Alter tablespace temp2 tablespace group temp;
3. Setting group as default temporary tablespace for the database:
Alter database Default Temporary Tablespace Temp;
4. Query dba_tablespace_groups for information about them.
------------------------------------------------------------------

Now coming to our topic how to rename tempfiles:
1. Create another temp tablespace to make it default.
Create Temporary Tablepspace temp_sd tempfile '/u02/oracle/temp/temp_sd_01.dbf' size 2000M;
2. Make this as default Temp Tablespace:
Alter database default temporary tablespace temp_sd;
3. Check database properties to be sure:
Select property_name, property_value from database_properties where property_name like '%Default_TEMP_TABLESPACE%';
4. Alter database tempfile '/u02/oracle/temp01.dbf' offline;
5. Copy file from old location to new location using OS commands.
cp /u02/oracle/temp01.dbf /u02/oracle/temp/temp01.dbf
6. Rename file
Alter database rename file '/u02/oracle/temp01.dbf' to '/u02/oracle/temp/temp01.dbf';
7. Bring it online
Alter database tempfile '/u02/oracle/temp/temp01.dbf' online;
8. Again change the default:
Alter database default temporary tablespace temp;
9. Drop the newly create temp tablespace
Drop Tablespace temp_sd including contents and datafiles;

Happy Troubleshooting !!!
Happy Diwali !!!

Wednesday, September 16, 2009

Getting Tech Stack Information on 11i and R12

We have to collect this information whenever boss ask :-). In Oracle Apps 11i and R12 instances we have txkInventory.pl and TXKScript.pl which can help us to get this information.
As applmgr user execute below command:
perl $FND_TOP/patch/115/bin/TXKScript.pl -script=$FND_TOP/patch/115/bin/txkInventory.pl -txktop=$APPL_TOP -contextfile=$CONTEXT_FILE -appspass=apps -outfile=$OA_HTML/apps_techinfo.html

As Oracle user execute below command:
perl $ORACLE_HOME/appsutil/bin/TXKScript.pl -script=$ORACLE_HOME/appsutil/bin/txkInventory.pl -txktop=$ORACLE_HOME/appsutil/temp -contextfile=$CONTEXT_FILE -appspass=apps -outfile=$ORACLE_HOME/appsutil/temp/oracle_techdb.html

Happy Troubleshooting !!!

Tuesday, August 11, 2009

R12 (12.1.1) Installed on Solaris 10

After some initial struggle with server we were able to finish our 12.1.1 installation on Solaris 10.
I am trying to list mistakes we made or issues we encountered.
1. We started 12.1.1 on Solaris 8, which was mistake. As 11g database is not certified on Solaris 8.
2. After moving to Solaris 10, our team forget to bounce Server after setting Kernal parameters.
3. As we have one disk and it was damn slow, so took 17 hrs to complete.
4. Make sure you have atleast 250GB free space for Vision instance installation.
5. 47 GB binaries will take and 60GB stage area.

Anyways after creating stage area from edelivery binaries, we were able to complete Vision instance installation.

Happy Troubleshooting !!!

Sunday, July 26, 2009

R12 12.1.1 Installation on Solaris

You might be wondering where this guy disappear. I am getting released from my current engagement that's why busy in transition and other works. And got another engagement in hand for R12 (12.1.1) installation on solaris. I am working with team to get this done. Will update all with the status and issues faced.
Until then Stay tuned.
Happy Troubleshooting.

Tuesday, June 30, 2009

IDM 10.1.4.3 installed

Just to update everyone those who ere waiting for IDM 10.1.4.3 patchset results. We have successfully implemented this is our test environment and the issue which were reported during 10.1.4.0.1 installation with Kerberose related to "Administer Partner Application" is resolved in 10.1.4.3 patch. Moreover one more issue we found in older version, there were huge error files generation in $ORACLE_HOME/hostname_domain.com_IDM10143/sysman/recv/errors which we didn't found in this patch. I guess those who were waiting can install this one.

Happy Troubleshooting !!!

Thursday, June 11, 2009

Responsibility not Appearing

I have some strange issue with one user. This user has 4 resposibilites assigned to him in dev, due to security issue we have to end his two responsibility. Now user want this "Sysadmin Inquiry" responsibility again. I did end date and told him about that. Now after 24 hrs he came back and asked what is the status. That means he is not able to see this responsbility under his ID.I did enable and disable other responsibilities under his id, he can see immediate impact of that. But not this one. Now for checking I did assigned this responsibility to my id and can see it without any issue. I did ran all these
1. Navigate to the define user form (FNDSCAUS).
2. Query up the user (that is having the issue) and insure the responsibility is NOT end dated.
3. END DATE the USER (not the responsibility) and save the record.
4. UNEND DATE the USER and save the record.
5. Run the request 'Sync responsibility role data into the WF table'.6
. Run the request "Synchronize WF LOCAL tables".
7. Run the request "Workflow Directory Services User/Role Validation" (with parameters 10000, yes, yes, yes).
8. Have the user log off and back on and verify that the responsibility appears.
but no help. Not sure why only one responsbility is creating problem for this user. After this got good help from one of the friend in Oracle Forum, he suggested to read note 466135.1, Man it reaaly helped a lot to resolve this issue. Thanks Hussein

Happy Troubleshooting !!!

Tuesday, May 19, 2009

ActiveChgImp:Error in Mapping EngineODIException: DIP_GEN_INITIALIZATION_EXCEPTION

Seems like this week will go to resolve OID issues for us. We had another issue in our dev OID setup, where synchronization with AD is not happening and log says this:
=======================================================
ActiveChgImp:Error in Mapping EngineODIException: DIP_GEN_INITIALIZATION_EXCEPTION
java.lang.NullPointerException
at oracle.ldap.odip.util.DirUtils.getLastChgNum(DirUtils.java:48)
at oracle.ldap.odip.gsi.LDAPReader.initAvailableChgKey(LDAPReader.java:884)
at oracle.ldap.odip.gsi.LDAPReader.initialise(LDAPReader.java:250)
at oracle.ldap.odip.engine.AgentThread.readerInitialise(AgentThread.java:460)
at oracle.ldap.odip.engine.AgentThread.mapInitialise(AgentThread.java:509)
at oracle.ldap.odip.engine.AgentThread.execMapping(AgentThread.java:375)
at oracle.ldap.odip.engine.AgentThread.run(AgentThread.java:237)
ActiveChgImp:about to Update exec status
Updated Attributes
orclodipLastExecutionTime: 20090518024937
orclodipConDirLastAppliedChgNum: 0
orclOdipSynchronizationStatus: Mapping Failure, Agent Execution Not Attempted
orclOdipSynchronizationErrors: Profile Initialization Failure
============================================================
Metalink note: 276481.1 does help to understand the issue and that is with dipassistance. After checking $ORACLE_HOME/ldap/odi/conf/ActiveChgImp.cfg file we realized the issue. As this is not the correct file. To make is correct. I have to stop our second odiserv instance and disable ActiveChgImp profile from oidadmin.
Once that is done. We again modified the Import Connector profile with correct cfg file. and enable ActiveChgImp profile and start odiserv instance and vola now log file looks better.

Happy Troubleshooting !!!

Monday, May 18, 2009

APP-FND-01564: ORACLE error 31202 in changepassword

We had this issue in our production this morning. Our dedicated sysadmin team were not able to change any user password from frontend. They were receiving following error "ORA-20001: Unable to call fnd_ldap_wrapper.update_user".
Issue came to us and we tried changing via FNDCPASS and in log we saw following message"
===============================================================
APP-FND-01564: ORACLE error 31202 in changepassword
Cause: changepassword failed due to ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials. Password Policy Error :9000: GSL_PWDEXPIRED_EXCP :Your Password has expired. Please contact the Administrator to change your password.ORA-06512: at "APPS.FND_LDAP_UTIL",.
The SQL statement being executed at the time of the error was: begin :r := fnd_web_sec.change_password(:u,:p); end; and was executed from the file &ERRFILE.
================================================================
Message was clear that we had some issue with our OID account. Metalink note id 401836.1 helped us to crack this issue. Issue was appsDN password in OID got expired and we have to reset the password. Here is how to resolve this error:

To implement the solution, login to the E-Business Suite database as the 'APPS' user and execute the following steps:
(1) SQL> select fnd_preference.get('#INTERNAL', 'LDAP_SYNCH','USERNAME') Apps_Instance_OID_Account from dual;
Example:
APPS_INSTANCE_OID_ACCOUNT
-------------------------------------------------------------------------------- orclApplicationCommonName="MYAPPSINSTANCE",cn="EBusiness",cn=Products,cn=OracleContext,dc=mycompany,dc=com (2) SQL> select fnd_preference.eget('#INTERNAL', 'LDAP_SYNCH','EPWD','LDAP_PWD') Apps_Password from dual;
Example:
APPS_PASSWORD
-------------------------------------------------------------------------------- mypassword1
(3) Now test the above credentials with ldapbind,
Example:
> ldapbind -h -p -D orclApplicationCommonName="MYAPPSINSTANCE",cn="EBusiness",cn=Products,cn=OracleContext,dc=mycompany,dc=com -w mypassword1 invalid credentials
Password Policy Error :9000: GSL_PWDEXPIRED_EXCP :Your Password has expired.
Note: If above returns the same password expired message, then login to oidadmin (Oracle Directory Manager) and expand Entry Management and navigate to the Apps User DN entry expanding backwards on the DN, ie:
+ dc=com
+ dc=mycompany
+ cn=OracleContext
+ cn=Products
+ cn="EBusiness"
+ orclApplicationCommonName="MYAPPSINSTANCE"
And type the same password returned from the sql query on step 2 as value for userpassword attribute, then click Apply.
(4) Then retest the previous ldapbind and it should now work:
> ldapbind -h -p -D orclApplicationCommonName="MYAPPSINSTANCE",cn="EBusiness",cn=Products,cn=OracleContext,dc=mycompany,dc=com -w mypassword1
bind successful

Happy Troubleshooting !!!

Monday, April 20, 2009

Better way to check Applied Patches

Being an Apps DBA patching is our bread and butter. And they way we check it make the difference. We had one issue and I was going through metalink note : 364439.1, which had this query. So thought of sharing with you all. Here you go:
===================================================
set serveroutput on
DECLARE
TYPE p_patch_array_type is varray(10) of varchar2(10);
--
p_patchlist p_patch_array_type;
p_appltop_name varchar2(50);
p_patch_status varchar2(15);
p_appl_top_id number;
--
CURSOR alist IS
select appl_top_id, name
from ad_appl_tops;
--
procedure println(msg in varchar2)
is
begin
dbms_output.enable;
dbms_output.put_line(msg);
end;
--
BEGIN
open alist;
--
p_patchlist:= p_patch_array_type('3240000','3460000','4204335','4125550','3942483','4733943');
--
LOOP
FETCH alist INTO p_appl_top_id,p_appltop_name;
EXIT WHEN alist%NOTFOUND;
--
IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
THEN
println(p_appltop_name ':');
for i in 1..p_patchlist.count
loop
p_patch_status := ad_patch.is_patch_applied('11i',p_appl_top_id,p_patchlist(i));
println('..Patch ' p_patchlist(i) ' was ' p_patch_status);
end loop;
END if;
println('.');
END LOOP;
--
close alist;
END;
/
===========================================================

Sample Output
---------------------------
Server1:
..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was EXPLICIT
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was EXPLICIT
..Patch 3942483 was NOT_APPLIED
..Patch 4733943 was EXPLICIT
.
.
Server2:
..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was NOT_APPLIED
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was EXPLICIT
..Patch 3942483 was EXPLICIT
..Patch 4733943 was NOT_APPLIED
.
.
Server3:
..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was NOT_APPLIED
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was NOT_APPLIED.
.Patch 3942483 was NOT_APPLIED
..Patch 4733943 was NOT_APPLIED
---------------
Happy Troubleshooting !!!

Wednesday, April 15, 2009

Renaming/Dropping and recreating online redo logs and Standby logfiles

We are in process of testing Dataguard with Physical standby. After we created Primary and Standby DB's we realize what we haven't put file names as per our standard. So I got the task to rename both, online redo logs and standby logs. This what I did :

1. Following is the script to identify all log files we have
SELECT GROUP#, BYTES, 'ONLINE' AS TYPE FROM V$LOG
UNION
SELECT GROUP#, BYTES, 'STANDBY' AS TYPE FROM V$STANDBY_LOG
ORDER BY 1;
2. Make sure protection mode is MAXPERFORMANCE:
SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE FROM V$DATABASE

First we start working on Standby database
3. STOP redo apply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
4. Set STANDBY_FILE_MANAGEMENT to MANUAL.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';
5. Gather current status of all member.
SQL> select member from v$logfile;
6. Prepare command to drop standby redo logfiles.
select 'alter database drop standby logfile group ' group# ';' from v$standby_log;
7. Prepare command to drop online redo logfiles.
select 'alter database drop logfile group ' group# ';' from v$log;
8. Check the status of online redolog group.
SELECT GROUP#, STATUS FROM V$LOG;
GROUP# STATUS
---------- ----------------
1 CLEARING_CURRENT
3 CLEARING
2 CLEARING
We can not drop CLEARING_CURRENT status logs, for that you have sync with Primary (Easy way is, leave them for a while and move ahead with droping Primary Database redo logs, and come back to Standby and you will be able to do it).
9. Clear the log group first before drop.
ALTER DATABASE CLEAR LOGFILE GROUP 1;
10. Now drop online redo log.
alter database drop logfile group 1;
Follow the same process for rest of the groups.
11. Now drop standby redo logs, leave the one which refuses to drop.
alter database drop standby logfile group 5;
12. Now create new online redo logs.
ALTER DATABASE ADD LOGFILE GROUP 1 '/path/redo_01.dbf' SIZE 10M;
13. Now we will crate standby redo logs.
ALTER DATABASE ADD STANDBY LOGFILE '/path/redo_std_by_05.dbf' SIZE 10M REUSE;
14. Again check the current status of member.
select member from v$logfile;

Now you will see latest updated names for all files. The one which left we can do them after We are done with Primary DB.

Now on Primary Database:
15. Follow step 5,6,7 to know current status and prepare commands.
16. Drop Standby redo logs and recreate them
alter database drop standby logfile group 5;
ALTER DATABASE ADD STANDBY LOGFILE '/path/redo_std_by_05.dbf' SIZE 10M REUSE;
17. Drop online Redo logs, recrate, switch and drop next inactive one.
alter database drop logfile group 1;
ALTER DATABASE ADD LOGFILE GROUP 1 '/path/redo_01.dbf' SIZE 10M;
ALTER SYSTEM SWITCH LOGFILE;
SELECT GROUP#, STATUS FROM V$LOG;
18. Check the new status.
select member from v$logfile;

Now come back to Standby Database back and drop recreate left over online and standby redo logs.
19. Set STANDBY_FILE_MANAGEMENT to AUTO.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
20. start redo apply.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
21. Check the status of applied log again.
select sequence#, applied from v$archived_log;

Happy Troubleshooting !!!

Tuesday, April 7, 2009

User unable to connect to EBS with Kerberos

We had one issue, one of the user who rejoined us having issues in accessing Oracle Application with his ID. I did checked his access details and everything looks good to me. But whenever he hit the URL it says you do not have permission to access this page. Another direct access via ADI is working fine for him, but Oracle EBS having issues.
I did a query at OID end to findout if something wrong there, I used the following query to get his data:

ldapsearch -L -h myserver -p 389 -s sub -b "cn=example,cn=example, dc=example,dc=com" -w password -v "cn=*" uid orclguid

I got the 2 matches for his account. Which was causing the issue. Now which is active account that we need to findout. I checked with AD administrator which account is active for this user based on OU. So he gave me the output which I used to delete inactive account. And update GUID in FND_USER for this user and it worked.

User was very happy so do I.

Happy Troubleshooting !!!

Monday, March 30, 2009

Validating Guest user password

We generally come across a situation where we have to validate our Guest user password. I am mentioning some steps via backend to validate your Guest user password.
1. Check Value in DBC File
grep -i GUEST_USER_PWD $FND_SECURE/hostname_SID.dbc
GUEST_USER_PWD=GUEST/ORACLE

2. Check profile option value
sqlplus apps/passwd
SQL> select fnd_profile.value(’GUEST_USER_PWD’) from dual;
FND_PROFILE.VALUE(’GUEST_USER_PWD’)
——————————————————————————–
GUEST/ORACLE

Value for step 1 and 2 must be sync.

3. Guest user connectivity check
sqlplus apps/passwd
SQL> select FND_WEB_SEC.VALIDATE_LOGIN('GUEST','ORACLE') from dual;
FND_WEB_SEC.VALIDATE_LOGIN('GUEST','ORACLE')
——————————————————————————–-----
Y

Above is the value, then everything is perfect.
Happy Troubleshooting !!!

Friday, February 27, 2009

missing:..........appl/alr/11.5.0/admin/driver/alrfile.drv

I have certain patch to apply in my system, but unfortunately adpatch is coming out with following error for any patch.
Determining valid on-site files...
AutoPatch error:
The following file is missing:
..........appl/alr/11.5.0/admin/driver/alrfile.drv
AutoPatch error:Error reading product file driver files complete; make tapes
AutoPatch error:Error determining valid on-site files
You should check the file..........................appl/admin/SID/log/logfile.log for errors.

===============================
No answer in metalink for this. I did find for alrfile.drv in my system and did found the file in $APPL_TOP/amf/alr/11.5.0/admin/driver/alrfile.drv, just created softlink for alr under $APPL_TOP like
ln -s amf/alr alr
and reapplied the patch and it worked.

Happy Troubleshooting !!!

Thursday, February 26, 2009

IDM version 10.1.4.3

Those who were waiting for IDM version 10.1.4.3 here is some good news for them. IDM version 10.1.4.3 (7215628) has been released and it has recently got certified with EBS 11i. Metalink Note 743141.1 and 233436.1 has more information on the same
Although we decided not to upgrade as off now. But if get chance will test and upload results for WNA setup.

Happy Troubleshooting !!!

Tuesday, February 17, 2009

Changing Data Guard Protection level

We are in process of testing Data Guard setup. While doing that I come across a situation where I have to change Protection status from status from Maximum Performance to Maximum Availability:
On Primary:
alter system set log_archive_dest_2='SERVICE=Instance_Name LGWR SYNC AFFIRM valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=SID;
alter system set log_archive_dest_state_2=enable;


Shutdown and mount the primary and standby database:
SQL> -- [Connect to the primary database]
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT

SQL> -- [Connect to the standby database]
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT

On Primary
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

On Standby
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On Primary
ALTER DATABASE OPEN;

SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE FROM V$DATABASE;

Happy Troubleshooting !!!

Monday, February 16, 2009

Forgotten wallet manager password

We all know that wallet is secured with password. We need password to opne wallet and include some more certificates or for some admin work. But what if we forgot its password. Can we reset its password ? Answer is NO. So please do remember this wallet manaager password otherwise you have to redo all your work for wallet.
As per Metalink Note : 367755.1
"Unfortunately there is nothing that can be done here.
If the Wallet password is lost, a new Wallet and Certificate Request need to be created. Then a new certificate from this Certificate Request would need to be generated, and then imported to the new wallet."

Happy Troubleshooting !!!

Tuesday, January 13, 2009

BIB-7000 Cannot access X Server

Kindly attention !! This is not repeat of post, something new in stores.
If you have checked my earlier post regarding the same error I hope issue might have resolved for you. Even if it doesn't I have something new today, which will help not to use DISPLAY setting at all for Jserv.

If problem still exists when using VNC or Xvfb with JDK 1.4.2 or 1.5, proceed with instructions for setup below:
You are using J2SE 1.4.2 or J2SE 1.5 (this feature is not available in JDK 1.3.1)
You have jserv_ux_ias1022.properties version 15.80.115105.2 or higher. (patch TXK (FND) AUTOCONFIG TEMPLATE ROLLUP PATCH L (NOV 2005) or higher deliver support for this new feature)
The setting controlling the 'Headless' support for J2SE in the jserv.properties is the following: wrapper.bin.parameters=-Djava.awt.headless=[truefalse]
By default this is set to 'false'.
To change this to 'true' and thus enabling the 'Headless' support feature perform the following steps:
Use context-editor to change value for %s_java_awt_headless% to 'true'
Run Autoconfig to recreate the configuration files
Verify that now the above mentioned setting in jserv.properties is set to 'true'
Run the AOLJ test "X Server Accessibility" and verify it shows the success message

Remarks:

The DISPLAY setting is not used anymore when 'Headless' support is enabled. This Note only applies to Jserv. Non-java tools like Reports 6i may still require the X-server to be available in the environment.

Happy Troubleshooting !!!

Thursday, January 8, 2009

Where the Concurrent Manager kept himself Busy

We come across this question many time what Concurrent manager was doing. So here is one script which will help you to find this:

rem ------------------------------------------------------------------
rem Filename: cmup.sql
rem Purpose: Concurrent Manager Usage by Program /day
rem - Can only run from sqlplus
rem -----------------------------------------------------------------------

cl scr
set feedback off
set verify off
set line 1000
set pagesize 1111
set head off
spool d:\cmup.csv
prompt Concurent Program, Program, Total Duration(Min), Avg Duration(Min), Min Duration(Min), Max Duration(Min), Times_Run
select /*+ ALL_ROWS */
fcqtl.user_concurrent_queue_name', 'fcptl.program','round(sum(greatest(actual_completion_date - actual_start_date,0))*60*24, 2)', 'round(avg(greatest(actual_completion_date - actual_start_date,0))*60*24, 2)', 'round(min(greatest(actual_completion_date - actual_start_date,0))*60*24, 2)', 'round(max(greatest(actual_completion_date - actual_start_date,0))*60*24, 2)', 'count(*)
from
fnd_concurrent_programs fcp,
FND_CONC_REQ_SUMMARY_V fcptl,
fnd_concurrent_processes fcproc,
fnd_concurrent_queues_tl fcqtl,
fnd_concurrent_queues fcq
where
fcptl.phase_code = 'C'
and actual_completion_date is not null
and actual_start_date is not null
and fcq.concurrent_queue_id = fcproc.concurrent_queue_id
and fcq.application_id =fcproc.queue_application_id
and fcq.manager_type = 1
and fcptl.controlling_manager = fcproc.concurrent_process_id
and fcptl.program_application_id =fcp.application_id
and fcptl.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_name not in ('ACTIVATE','ABORT','DEACTIVATE','VERIFY')
and fcproc.queue_application_id = fcqtl.application_id
and fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id
and fcqtl.language=userenv('LANG')
and actual_start_date >= trunc(sysdate - 1) and actual_start_date <= trunc(sysdate)
group by
fcqtl.user_concurrent_queue_name,
fcptl.program
order by
round(sum(greatest(actual_completion_date - actual_start_date,0))*60*24, 2) desc,
fcqtl.user_concurrent_queue_name asc
/
spool off

Happy Troubleshooting !!!

How Many Archive Log Generated Today

Hi I am back to Business. I would like to start my first post in this new year with some useful information to find how many archive file generated today hourly. Here is the script:

/* archlogs.sql
find the number of archivelogs each hour of the day
*/
col MidN format 999
col 1AM format 999
col 2AM format 999
col 3AM format 999
col 4AM format 999
col 5AM format 999
col 6AM format 999
col 7AM format 999
col 8AM format 999
col 9AM format 999
col 10AM format 999
col 11AM format 999
col Noon format 999
col 1PM format 999
col 2PM format 999
col 3PM format 999
col 4PM format 999
col 5PM format 999
col 6PM format 999
col 7PM format 999
col 8PM format 999
col 9PM format 999
col 10PM format 999
col 11PM format 999
select to_char(first_time,'mm/dd/yy') logdate,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"
from v$log_history
group by to_char(first_time,'mm/dd/yy')
order by 1
/

Happy Troubleshooting !!!