Monday, April 28, 2008

New Metalink ?

Surfing my favorite EBS blogs, I saw something nice on the Online Apps DBA blog.
It seems Oracle had developed a new Metalink. Looks a lot better then the old one, check it out for yourself !

https://csm.oracle.com/CSP/ui/index.html

Monday, April 21, 2008

CPU April 2008 patch

Today I started the Cpu patch April, 2008.
I started with the RDBMS patch, followed by the HTTP patch.
No problems so far, unfortunatly for me the EBS 11i patches I have to apply are not cummulative. I have to apply EBS 11i CPU patches from October 2005 till now...
When applying so many EBS patches, something must be going wrong :)
I will keep you posted.

Thursday, April 17, 2008

Create pfile from memory...

During the post 11g upgrade steps, I discovered a new feature in 11g.
In the 11g database, you can now create a pfile/spfile from memory !

I was used to create a pfile from the current spfile in 10g, and offcourse create a spfile from the adjusted pfile...but now it's also possible to creaet a pfile from memory. The syntax is almost the same..

[oracle@ebs2 log]$ sqlplus '/ as sysdba'
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Apr 16 17:06:02 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create pfile='/ebs/proddb/11.1.0/dbs/init_prod.ora' from memory;


File created.

Wednesday, April 16, 2008

Post 11g upgrade steps..

After the database upgrade, you will have to perform some post upgrade steps like compiling invalid objects etc...

These are the steps I have taken :



1. verify upgrade by running utlu111s.sql



[oracle@ebs2 admin]$ sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Apr 14 20:30:58 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> conn / as sysdba


Connected to an idle instance.

SQL> startup

ORACLE instance started.
Total System Global Area 1071333376 bytes


Fixed Size 1304704 bytes

Variable Size 411043712 bytes

Database Buffers 645922816 bytes

Redo Buffers 13062144 bytes

Database mounted.

Database opened.

SQL> @utlu111s.sql

Oracle Database 11.1 Post-Upgrade Status Tool 04-14-2008 20:37:33.

Component Status Version HH:MM:SS.

Oracle Server. VALID 11.1.0.6.0 01:29:42

JServer JAVA Virtual Machine. VALID 11.1.0.6.0 00:00:00

Oracle Real Application Clusters. INVALID 11.1.0.6.0 00:00:04

OLAP Analytic Workspace. VALID 11.1.0.6.0 00:00:00

OLAP Catalog. VALID 11.1.0.6.0 00:00:00

Oracle OLAP API. VALID 11.1.0.6.0 00:00:00

Oracle XDK. VALID 11.1.0.6.0 00:00:00

Oracle Text. VALID 11.1.0.6.0 00:00:00

Oracle XML Database. VALID 11.1.0.6.0 00:00:00

Oracle Database Java Packages. VALID 11.1.0.6.0 00:00:00

Oracle Multimedia. VALID 11.1.0.6.0 00:00:00

Spatial. VALID 11.1.0.6.0 00:00:00

Gathering Statistics. 00:52:16



2. Run catuppst.sql

SQL> @catuppst.sql

3. compile invalid objects

SQL> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
COUNT(*)


----------

108064


SQL> @utlrp.sql

During compiling the invalid objects, I also copied the tns_admin directory and the appsutil directory to the new 11g $ORACLE_HOME

Nex step is to get EBS started.




Tuesday, April 15, 2008

Database upgrade...

Last weekend I started the database upgrade from 10gr2 to 11gr1. As I mentioned before, I decided to manually upgrade the database. Before upgrading I already recieved an error.

[oracle@ebs2 admin]$ sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Apr 10 08:19:14 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> conn / as sysdba

Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1071333376 bytes

Fixed Size 1304704 bytes
Variable Size 411043712 bytes
Database Buffers 645922816 bytes
Redo Buffers 13062144 bytes
Database mounted.
Database opened.
SQL> set echo on
SQL> spool upgrade.log
SQL> @catupgrd.sql
Upgrade error :

SQL> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
2 FROM registry$database
3 WHERE tz_version != (SELECT version from v$timezone_file);
SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')

*ERROR at line 1:
ORA-01722: invalid number

Have to upgrade the timezone to 4.

Current timezone :
SQL> select * from v$timezone_file;
FILENAME VERSION

------------ ----------
timezlrg.dat 2

To upgrade my timezone to version 4, I applied the following patches :
1. 5601428
2. 5746835
3. 5632264, NEED UPDATED TIMEZONE FILES (VERSION 4) FOR MORE DST RULE CHANGES

After upgrading the timezone I started teh upgrade again, and this time the catupgrd.sql went fine...
Today I will finish the database upgrade following metalink note 429825.1, Complete checklist for Manual Upgrade to 11gR1.

Wednesday, April 09, 2008

Installing 11g database software

Next step is to install the 11g database software. Be sure to install the 11g software in a new ORACLE_HOME. Also, only install the software ! Do not create a new database and do not choose to upgrade the database during the install.



After installing the software, create nls/data/9idata directory..


[oracle@ebs2 old]$ perl cr9idata.plCreating directory /ebs/proddb/11.1.0/nls/data/9idata ...Copying files to /ebs/proddb/11.1.0/nls/data/9idata...Copy finished.Please reset environment variable ORA_NLS10 to /ebs/proddb/11.1.0/nls/data/9idata!


Now apply some additional 11.1.0.6 RDBMS patches. Apply the following patches using Opatch :


1. 6778860, 11.1.0.6 BUNDLED PATCH FOR EBS 11I

2. 6501565, APPSST11G:11510CU2:+11G INTEGRATION ENVIRONMENT IS HAVING ISSUES IN DATABASE
3. 6670485, 11.5.10(TK11GR6E):POPULATE MEASURES WORKFLOW FAILING

4. 6636451, OLAP_TABLE - QUERIES FAIL IN 11G

Before running the first patch, be sure to apply patch 4898608, OPatch 10.2.0.1.6. Without this patch the opatch napply -skip_subset -skip_duplicate will fail...

After downgrading the excisting opatch utility, you can start the patches..


[oracle@ebs2 6778860]$ opatch napply -skip_subset -skip_duplicateInvoking OPatch 10.2.0.1.6
Oracle Interim Patch Installer version 10.2.0.1.6Copyright (c) 2007, Oracle Corporation. All rights reserved.
Setting N-Apply implicit patch base-directory to /ebs/patches/11g/6778860UTIL session
Oracle Home : /ebs/proddb/11.1.0Central Inventory : /etc/oraInventory from : /etc/oraInst.locOPatch version : 10.2.0.1.6OUI version : 11.1.0.6.0OUI location : /ebs/proddb/11.1.0/ouiLog file location : /ebs/proddb/11.1.0/cfgtoollogs/opatch/opatch2008-04-09_13-59-41PM.log
Invoking utility "napply"Checking conflict among patches...Checking if Oracle Home has components required by patches...Checking skip_duplicateChecking skip_subsetChecking conflicts against Oracle Home...OPatch continues with these patches: 6318357 6353873 6412375 6417271 6431470 6454237 6530141 6617137 6633314 6633347 6735167 6744214
Do you want to proceed? [yn]


When the 11g ORACLE_HOME is patched, the database (10gr2) must be upgraded to 11g. I choose the manual upgrade instead of using DBUA....

Applied Apps patches for 11g upgrade...

Yesterday I started the 11g upgrade off my own EBS database (10gr2).
Before doing anything with 11g, I first had to patch the EBS environment.
The following EBS patches have been applied :

1. Patch 3460000, 11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 2

2. Patch 6328363, 11GR1 APPS INTEROPERABILITY PATCH

3. Patchset 5903765, 11i.ATG_PF.H.RUP6

After succesfully (allthough Gabor provided me some errors he experienced....thnx again for that ;), I did not ran into any error...) installing these patches, I am now ready to install the 11g database software.

More to come.....

Tuesday, April 08, 2008

11g database upgrade EBS 11.5.10

Today I started the database upgrade to 11g on my own EBS 11i environment.

Because I did not patch my environment since the 10gr2 upgrade, the first patch to apply on my EBS environment is patch 3460000 'ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 2'
After this patch I have to install the 11g Release 1 interoperability patch for 11.5.10....
At this moment the CU2 is still running. To be continued...


Tuesday, April 01, 2008

Notifications not send with 10g Grid Control

We are working with 10g Grid Control (10.2.0.3) for a few months now and we noticed a problem with the notifications from OEM. It seemed OEM was holding back a lot of notifications. I could not find any error in OEM, and because some notifications did reach us by email, the email setup was oke.
According to note 285093.1 I installed the EMDiagkit to examine the problem.
Running the following statement showed me the problem..

[oemprd01@amssys07 bin]$ ./repvfy verify notifications -level 9

Please enter the SYSMAN password:

---------------------------------------------------------------------------

EMDIAG: 2008.0321 Repository: 10.2.0.3.0 31-Mar-2008 14:24:44
---------------------------------------------------------------------------
verifyNOTIFICATIONS
100. Notification backlog: 3
103. Stuck notification waiting to be delivered: 7352
104. Notification delivery delay: 2

So, more than 7000 notifications are stuck in OEM ?
I decided to log a SR on Metalink, because we were missing some critical notifications concerning our production databases...
After loggin the SR, I decided to check note 356774.1 again. I already noticed the DBMS_JOB's were running. But just to be sure I executed the following sql statements :

SQL> exec emd_maintenance.remove_em_dbms_jobs (to remove broken jobs..)

and

SQL> exec emd_maintenance.submit_em_dbms_jobs (submit all jobs again)

After these statements I recieved a few 1000 emails with old notifications !
The problem now seems to be solved, we are recieving a lot more emails than we used to.
Checking if some notifications are still hold back by OEM..

[oemprd01@amssys07 bin]$ ./repvfy verify notifications -level 9
Please enter the SYSMAN password:
---------------------------------------------------------------------------

EMDIAG: 2008.0321 Repository: 10.2.0.3.0 01-Apr-2008 10:00:07
---------------------------------------------------------------------------
verifyNOTIFICATIONS

And nothing is left behind this time....