Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Thursday, October 30, 2008

What in the world is a Storage Frame ?

Recently, I was asked for information about storage frame of the largest Production server in our environment.  There were two questions:

1. What is the storage frame name ?
2. What is the storage frame type ?

I spoke to some storage guys to understand what was the information that was being sought, and they told me that:

Storage Frame Type could be SAN, NAS or local disk
Storage Frame Name could be Symmetrix DMX3000 or other SAN products.

So Storage Frame means the hardware which is being used for storage, it may be:

1. A Storage Area Network (SAN)
2. A Network Attached Storage (NAS) device
3. Local disk attached to server

While I was googling for information about storage frames I found an article on Alejandro Vargas Oracle Blog about Database location on a storage frame.  This is what he says:

Every production environment needs to be built according to this standard:

      The formula for database location on the frame is 20%-40%-20%-20%:

  • 20% of the disk located on the external cylinder is the best performing area of the frame, to be used by online redo logs, undo segments, temp segments and most accessed database files
  • 40% near the external cylinder is a good performing area, to be used by all other database files
  • Next 20% is a slow performing area and should not be used for database files, except parameter files, archived logs, software install
  • Last 20% correspond to the inner cylinder, is the slowest performing area and should not be used for database files at all.
Lun's to be used by the database needs to be mapped to assure that the database is located in the most performing areas of your frame.

Wednesday, October 29, 2008

V$DATAFILE status shows RECOVER after creation of controlfile

Anand Reddy pinged me today with a basic question.  He had received cold backup of a database from Production support team.  After creation of control file, he checked the status of datafiles:

select distinct status from v$datafile;

RECOVER
SYSTEM

This is expected behavior.  I googled for this and found this article on Saibabu's Oracle blog.

To get the true status of datafile, you need to query v$datafile_header.

select status from v$datafile_header;

Here's some more information from Official Oracle manuals:

To determine whether datafiles require media recovery:

  1. Start SQL*Plus and connect to the target database instance. For example, issue the following commands to connect to trgt:

    % sqlplus  SQL> CONNECT SYS/password@trgt AS SYSDBA 
  2. Determine the status of the database by executing the following SQL query:

    SELECT STATUS FROM V$INSTANCE; 

    If the status is OPEN, then the database is open. Nevertheless, some datafiles may require media recovery.

  3. Query V$DATAFILE_HEADER to determine the status of your datafiles. Run the following SQL statements to check the datafile headers:

    COL FILE# FORMAT 999 COL STATUS FORMAT A7 COL ERROR FORMAT A10 COL TABLESPACE_NAME FORMAT A10 COL NAME FORMAT A30  SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME  FROM   V$DATAFILE_HEADER  WHERE  RECOVER = 'YES'  OR     (RECOVER IS NULL AND ERROR IS NOT NULL); 

    Each row returned represents a datafile that either requires media recovery or has an error requiring a restore. Check the RECOVER and ERROR columns. RECOVER indicates whether a file needs media recovery, and ERROR indicates whether there was an error reading and validating the datafile header.

    If ERROR is not NULL, then the datafile header cannot be read and validated. Check for a temporary hardware or operating system problem causing the error. If there is no such problem, you must restore the file or switch to a copy.

    If the ERROR column is NULL and the RECOVER column is YES, then the file requires media recovery (and may also require a restore from backup).

    Note:

    Because V$DATAFILE_HEADER only reads the header block of each datafile, it does not detect all problems that require the datafile to be restored. For example, this view cannot tell whether a datafile contains corrupt data blocks.
  4. Optionally, query V$RECOVER_FILE to list datafiles requiring recovery by datafile number with their status and error information. For example, execute the following query:

    SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME  FROM   V$RECOVER_FILE; 

    Note:

    You cannot use V$RECOVER_FILE with a control file restored from backup or a control file that was re-created after the time of the media failure affecting the datafiles. A restored or re-created control file does not contain the information needed to update V$RECOVER_FILE accurately.

    To find datafile and tablespace names, you can also perform useful joins using the datafile number and the V$DATAFILE and V$TABLESPACE views. For example:

    COL DF# FORMAT 999 COL DF_NAME FORMAT A35 COL TBSP_NAME FORMAT A7 COL STATUS FORMAT A7 COL ERROR FORMAT A10 COL CHANGE# FORMAT 99999999  SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name,         d.STATUS, r.ERROR, r.CHANGE#, r.TIME FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t WHERE t.TS# = d.TS# AND d.FILE# = r.FILE#; 

    The ERROR column identifies the problem for each file requiring recovery.

Tuesday, October 28, 2008

Oracle Apps cloning techniques

1. RapidClone
  • Manual Process
  • Requires user interaction
  • Documented in Metalink Note 406982.1
2. Application Management Pack (needs separate license, not included in E-Business Suite)
  • Automated Process
  • Provides Data Scrambling
  • Allows Image Creation
  • Documented in Metalink Note 394448.1
3. Third party solutions
  • Ringmaster clone
Third party Clone Accelerators which speed up the copying step:

1. EMC Replication Accelator for E-Business Suite
2. Network Appliance Snapmirror

Monday, October 27, 2008

How to know whether your E-biz instance is SSO enabled

Whenever SSO is enabled, the user_guid column in FND_USER table is populated for all users who have SSO accounts.  Giving the following command 

select user_guid from apps.fnd_user
where user_guid is not null;

If the result is 0 rows, then your instance is not enabled for SSO.
If the result is > 0 rows, then your instance is SSO enabled.

The other obvious way is to try to login to the instance.  If you are presented with the SSO screen for username/password, then you are SSO enabled.

Saturday, October 25, 2008

Oracle Database versioning, what the dots in 10.2.0.4 mean

Have you ever wondered what each number in Oracle Databases' release stands for.  Here's what I found out:

Before Oracle 9.2.0 this was the release number format

Release Number Format

An Oracle7 Server distribution tape might be labeled "Release 7.0.4.1." The following sections translate this number.

7.0.4.1
Version NumberMaintenance Release NumberPatch Release NumberPort-Specific Patch Release Number

Starting with release 9.2, maintenance releases of Oracle Database are denoted by a change to the second digit of a release number. In previous releases, the third digit indicated a particular maintenance release.


Major Database Release Number

The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.

Database Maintenance Release Number

The second digit represents a maintenance release level. Some new features may also be included.

Application Server Release Number

The third digit reflects the release level of the Oracle Application Server (OracleAS).

Component-Specific Release Number

The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.

Platform-Specific Release Number

The fifth digit identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this digit will be the same across the affected platforms.

Checking Your Current Release Number

To identify the release of Oracle Database that is currently installed and to see the release levels of other database components you are using, query the data dictionary view PRODUCT_COMPONENT_VERSION. A sample query follows. (You can also query the V$VERSION view to see component-level information.) Other product release levels may increment independent of the database server.

COL PRODUCT FORMAT A35 COL VERSION FORMAT A15 COL STATUS FORMAT A15  SELECT * FROM PRODUCT_COMPONENT_VERSION;  PRODUCT                                  VERSION     STATUS ---------------------------------------- ----------- ----------- NLSRTL                                   10.2.0.1.0  Production Oracle Database 10g Enterprise Edition   10.2.0.1.0  Prod PL/SQL                                   10.2.0.1.0  Production ... 

Thursday, October 23, 2008

Pre-steps during OATM conversion not in metalink

1. Ensure that initialization parameters are set as O7_DICTIONARY_ACCESSIBILITY=TRUE 
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE or SHARED

2. Make sure that the temp tablespace size is bigger than the largest object size to be migrated. In one of our iterations,  the size of the biggest object to be migrated was an index called WF_ITEM_ATTRIBUTE_VALUES_PK of size 9GB.  We made sure that our temp tablespace had more than 9 GB of free space before starting the migration.

Wednesday, October 22, 2008

How to identify the filesystem type in Solaris

Give the following commands:

1.  Type devnm mount-point and press Return. The raw device name is displayed.
2.  Become superuser.
3.  Type fstyp /dev /rdsk /cntndnsn and press Return. The type of the file system is displayed:

$ devnm /usr
/dev/vx/dsk/usrdg/usr /usr

$ fstyp /dev/vx/dsk/usrdg/usr
fstyp: cannot stat or open

Become root

$ pbrun ksh
Sun Microsystems Inc.   SunOS 5.8       Generic Patch   February 2004
# fstyp /dev/vx/dsk/app76dg/app76
vxfs
#

Tuesday, October 21, 2008

Terminal Critical Patch Update

I got this from Oracle’s Oct 2008 Critical Patch Update (Metalink Note: 735216.1)

Table 11 Critical Patch Update Availability for Oracle Database
Platform11.1.0.711.1.0.610.2.0.410.2.0.310.2.0.210.1.0.59.2.0.8 DV9.2.0.8

Terminal Critical Patch Update

-

CPUJUL2009

-

CPUJAN2009

-

CPUJAN2012

CPUJUL2010

CPUJUL2010


Last CPU for 9.2.0.8 is July 2010 CPU. This is strange considering 9i is obsoleted.
Last CPU for 10.2.0.3 is Jan 2009 CPU.
Last CPU for 11.1.0.6 is July 2009 CPU.

12.0.5 is only for Financials and HR

Recently Sandeep Panchal asked me for R12.0.5 media kit.  I had recently downloaded R12.0.4 media for him from edelivery.oracle.com.  There was no R12.0.5 on edelivery.oracle.com.  

After reading a few notes on metalink, I realized that there is no 12.0.5 for all products.  Steven Chan's blog has this article about the same. 12.0.5 as a Release Update Pack was released only for Financials and HR. Please refer to Metalink Note 423541.1 for R12 Release Update Pack Schedule and 577406.1 for Oracle Financials and Oracle HRMS Release Update Packs 12.0.5 (RUP5).  To bring Financials and HR products to 12.0.5,  you need to apply these two patches on a base 12.0.4 install:

Patch 6836355 - Oracle Financials Family Release Update Pack 5 For 12.0 (R12.FIN_PF.A.DELTA.5)
Patch 6610000 - Oracle HRMS Release Update Pack 5 for 12.0 (R12.HR_PF.A.DELTA.5)



Monday, October 20, 2008

How to check if a port is in use

This is the most common question asked by DBAs and System Admins.

The easiest way to find out is this:

telnet hostname port number

For example to check if port 22000 is in use, use this command:

telnet justanexample.com 22000

If the port is in use, the screen will go blank.  You can then press Ctrl + ] to get to telnet prompt, from where you can type exit and quit.

If the port is not in use, you'll get a message like  "Connect timed out".  This works for all operating systems supporting TCP/IP that's all unix and all windows versions.

Friday, October 17, 2008

Context variables to check for configuring load balancer

For both 11i and R12 these are the context file variables which need to be changed when configuring a BigIP load balancer for multiple application tiers:

Thursday, October 16, 2008

Error publishing Mobile Field Service application

Raju pinged me today and showed these errors:

$ cd /11ioltp/erpapp/appl/csl/11.5.0/html/download
$ pwd
$CSL_TOP/html/download
$ cat runjav_11ioltp_csl.sh
java -DJTFDBCFILE=$FND_TOP/secure/$CONTEXT_NAME.dbc -DAFLOG_ENABLED=TRUE -DAFLOG_LEVEL=STATEMENT oracle.apps.asg.setup.InstallationMgr erp11i.justanexample.com 1521 erp11i apps apps123 mobileadmin mobileadmin CSL_TOP csl/10CSL.ini html/download/csl.zip
$ pwd
$CSL_TOP/html/download
$ pwd
$CSL_TOP/html/download
$ ksh
$ sh runjav_11ioltp_csl.sh
java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at oracle.apps.asg.setup.AsgResourceManager.setApplicationPublishedTime(AsgResourceManager.java:184)
at oracle.apps.asg.setup.UnzipFile.doUnZip(UnzipFile.java:204)
at oracle.apps.asg.setup.PublishPub.resolveZipFile(PublishPub.java:550)
at oracle.apps.asg.setup.PublishPub.publishPubs(PublishPub.java:459)
at oracle.apps.asg.setup.InstallationMgr.main(InstallationMgr.java:2149)
Caused by: java.lang.Exception: Virtualpath: /CSL is invalid.
at oracle.mobile.admin.ResourceManager.setApplicationPublishedTime(Unknown Source)
... 9 more
[APPLICATION]
[ROLE]
[ACL]
WTG-10020: Script file processed completely
$
))
$ cat runjav_11ioltp_csl.sh
java -DJTFDBCFILE=$FND_TOP/secure/11ioltp_11ioltp.dbc -DAFLOG_ENABLED=TRUE -DAFLOG_LEVEL=STATEMENT oracle.apps.asg.setup.InstallationMgr 11ioltp.justanexample.com 1521 ERP11I apps apps123 mobileadmin mobileadmin CSL_TOP csl/10CSL.ini html/download/csl.zip
for JTM publishing
$ date
Fri Oct 17 17:29:13 EDT 2008
$ ksh
$ sh runjav_11ioltp_jtm.sh
java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at oracle.apps.asg.setup.AsgResourceManager.setApplicationPublishedTime(AsgResourceManager.java:184)
at oracle.apps.asg.setup.UnzipFile.doUnZip(UnzipFile.java:204)
at oracle.apps.asg.setup.PublishPub.resolveZipFile(PublishPub.java:550)
at oracle.apps.asg.setup.PublishPub.publishPubs(PublishPub.java:459)
at oracle.apps.asg.setup.InstallationMgr.main(InstallationMgr.java:2149)
Caused by: java.lang.Exception: Virtualpath: /CSL is invalid.
at oracle.mobile.admin.ResourceManager.setApplicationPublishedTime(Unknown Source)
... 9 more

File System error: $CSL_TOP/html/download/CSL2 is not accessible

I am still working on this one.

Wednesday, October 15, 2008

Oct 2008 CPU

Metalink Note 738921.1 describes the October 2008 CPU for E-Business Suite 11i and R12.

If you are patched till July 2008 CPU, these are the patches you need to apply on 11i:

7369190 10203 DB security patch (Oct2008 CPU)

6194129 Developer 6i Patchset 19
7156414 Post patch for Dev 6i patchset 19
6125732 Post patch for Dev 6i patchset 19
6857221 Post patch for Dev 6i patchset 19
7343387 Apps Interop Patch for Patchset 19

6859224 Oct 2008 CPU Reports patch

6823842 Applications Framework 
6642209 Internet Supplier Portal

Tuesday, October 14, 2008

10.2.0.3 unstable release for Apps 11i

There is a consensus among Production support DBAs that 10.2.0.3 is an unstable release for Apps 11i.  We have had instance or DB crash in almost all the Production instances which have gone live with Oracle Database 10.2.0.3.  Each crash was due to a known bug for which one off patches were available.  There are 550+ one off patches on top of 10.2.0.3.  If you scour through all of them, you'll find around 60 patches which fix ORA-600/7445, DB/instance crash issues.  Since these patches are one-offs, Oracle gives their standard warning about applying these only when you face the particular issue as these are not regression tested.  

This leads to a peculiar scenario.  A production database crashes due to a known bug.  DBAs log severity 1 SR with Oracle.  Oracle gives them a one off patch which is already available because this is a known bug.  DBAs apply the patch, and the issue is fixed.  

Business is aghast at this approach.  Its like you wait for the instance to crash to apply a patch. They do not like this reactive practice at all.  They want the DBAs to apply patches for all such known bugs.  Oracle says that if you apply all these one off patches, you might end up with new bugs.  So the path of least resistance is to apply those DB patches which are marked as Recommended in metalink.

This is a never ending story.  Even if you upgrade to 10.2.0.4, there are bugs on top of 10.2.0.4 too.  I am unsure if we can ever get out of this loop.

Monday, October 13, 2008

Changes to default functionality in 11g database

Many default functionalities of Oracle RDBMS have changed in 11g.  Here's a list:
  • Conventional export is no longer supported
  • Alert / trace file locations are different 
  • New passwords are case sensitive by default
  • The exact value of internal SGA overhead, or Startup overhead in Shared Pool, is listed in the new v$sgainfo view
  • job_queue_processes parameter is non-basic in 11g
  • Transparent Data Encryption at Tablespace Level (Metalink note 432776.1)
  • Case sensitive passwords and strong user authentication (Metalink Note 429465.1)
Will update this list, as and when I come to know about the changes.

Friday, October 10, 2008

Upgrade Oracle 9iAS release 1(1.0.2.2.x) to AS 10g release 3 (10.1.3.4)

There is no direct upgrade path from Oracle9iAS Rel 1 to OracleAS 10g. Furthermore, there are no white papers or support articles which discuss upgrading Oracle9iAS Rel 1 to OracleAS 10g. This is because Oracle9iAS Rel 1 was desupported on 30th June 2004 and the first production release of OracleAS 10g - OracleAS 10g Rel 2 (10.1.2.0.0) was not in shipping until January 2005. 
 
For Apps instances, it is well documented in metalink notes 

186981.1: Oracle Application Server with Oracle E-Business Suite Release 11i FAQ
207159.1: Oracle E-Business Suite Release 11i Technology Stack Documentation Roadmap
223927.1: Statement of Direction: OracleAS Integration with Oracle E-Business Suite
and 233436.1: Installing Oracle Application Server 10g with Oracle E-Business Suite Release 11i

For non-apps instances, our approach is to upgrade first to Oracle 9iAS Release 2 and then upgrade from this version to OracleAS 10g Rel 1 which is finally upgraded to Oracle AS 10g Release 3.  Here's a list of high level steps:
 
Task 1: Install the Oracle 9iAS Release 2
 
Task 2: Use the Oracle 9iAS Migration assistant to migrate Oracle HTTP Server, 9i AS containers for J2EE and webcache
 
Task 3: Perform any manual tasks necessary to complete the upgrade of these compoents
 
Task 4: Install AS 10g Release 1(9.0.4) infrastructure services
 
Task 5: Install the Oracle Application Server 10g (9.0.4) middle tier
 
Task 6: Use OracleAS Upgrade Assistant to upgrade HTTP Server, containers for J2EE and Web Cache
 
Task 7: Perform any manual upgrade tasks necessary to complete the upgrade of each component.
            
Task 8: Determine an AS 10g Release 3 (10.1.3.4) topology to install and configure
 
Task 9: Install and Configure 10g AS release 3 environment
 
Task 10: Perform any required pre-deployment tasks like configure data sources, resource adapters, http server
           
Task 11: Redeoply your 9i AS applications on AS 10g release 3
 
Task 12: Verify your redeployed applications on AS 10g release 3
 
Task 13: Decommission the 9i AS and AS 10g release  1Oracle Homes.
 
So the upgrade path will be like this:
 
Oracle 9i AS release 1 ----> Oracle 9i AS release 2 ----> Oracle AS 10g release 1 -----> Oracle AS 10g release 3

Thursday, October 9, 2008

Difference between Jserv and OC4J

Oracle 11i uses Jserv as the servlet engine. R12 uses OC4J as the servlet engine.

In Oracle9iAS Release 1 (1.0.2.2.x), the first release to include OC4J, there were two JSP containers:

The container developed by Oracle and formerly known as "OracleJSP"

The container licensed from Ironflare AB and formerly known as the "Orion JSP container". Apps 11i uses Orion JSP container.

The OracleJSP container offered a number of advantages, including useful value-added features and enhancements such as for globalization and SQLJ support. The Orion container also offered advantages, including superior speed, but had disadvantages as well. It did not always exhibit standard behavior when compared to the JSP 1.1 reference implementation (Tomcat), and its support for internationalization and globalization was not as complete.

In Oracle9iAS Release 2 (9.0.2) and higher, these two containers are integrated into a single JSP container, referred to as the "OC4J JSP container". This container offers the best features of both original versions and runs efficiently as a servlet in the OC4J servlet container. The integrated container primarily consists of the OracleJSP translator and the Orion JSP runtime, running with a simplified dispatcher and the OC4J core runtime classes.

In Oracle9iAS Release 1 (1.0.2.2.x), JServ was the primary servlet environment. There are significant differences between the OC4J 9.0.4 servlet environment, which is a servlet 2.3 implementation, and the JServ servlet environment, which is a servlet 2.0 implementation. The following is a summary of highlights, particularly with respect to JSP implementations:

Standard application environment versus globals.jsa: A well-defined concept of a Web application exists in the servlet 2.3 definition, but did not exist in the servlet 2.0 definition. The servlet standard and OC4J implementation now define the concept of the document root of a Web application, and how to package an application.

Request dispatcher: The concept of the request dispatcher was introduced in the servlet 2.1 specification. This mechanism allows a JSP page or servlet to include content from another page or servlet or to forward execution to another page or servlet. For JServ, the OracleJSP implementation (but not the Orion JSP implementation) in Oracle9iAS Release 1 (1.0.2.2.x) emulated request dispatcher functionality. For OC4J in Oracle Application Server 10g (9.0.4), this emulation is no longer necessary.

Attribute storage: Beginning with the servlet 2.1 specification, request-level and application-level attribute storage is possible. Developers can use HTTP request objects and servlet context (application-level) objects to store and retrieve state information. For JServ, the OracleJSP implementation (but not the Orion JSP implementation) in Oracle9iAS Release 1 (1.0.2.2.x) emulated this functionality. For OC4J in Oracle Application Server 10g (9.0.4), this emulation is not necessary.

Servlet filtering: The concept of servlet filtering was introduced in the servlet 2.3 specification. This mechanism allows verification and modification of HTTP request and response objects by developers. This might be used, for example, for common headers and footers or customized authentication or authorization. This functionality was not available in Oracle9iAS Release 1 (1.0.2.2.x) but is available in Oracle Application Server 10g (9.0.4).

Globalization: The servlet 2.3 specification provides globalization support for HTTP parameters through the standard setCharacterEncoding() method of the HTTP request object. The OracleJSP implementation (but not the Orion JSP implementation) in Oracle9iAS Release 1 (1.0.2.2.x) supported globalization through the translate_params configuration parameter. Later OC4J JSP implementations also supported globalization through the setReqCharacterEncoding() method of a public utility class. You should now migrate your applications to setCharacterEncoding().

Wednesday, October 8, 2008

Youtube full screen shows blank

Namita pinged me today.  She had bought a new Dell Inspiron 1420 laptop.  Whenever she did a full screen / maximize in youtube, the screen went blank, though the sound continued.  I tried googling for this problem as it sounded like a known issue.  After many tries, on answers.yahoo.com, I found this:

Hello,

I was getting the very same anomaly, (a diagonal, white on top/grey on
bottom that flashed color when playing), and I just stumbled upon a
quick fix:

1. Right-click in the standard youtube video window (non-full
screen).
2. Choose 'Settings...' from the Flash Player's pop-up menu.
3. Uncheck the 'Enable Hardware Acceleration'
4. Click 'Close'.

Full-screen should now work, or at least it did for me. Some kind of
Flash incompatibility with latest Firefox build, it would seem. I am
running vista, so maybe that has something to do with it, as well.
Good luck!

Hope this helps.

I did as suggested above, and it started working fine.

Tuesday, October 7, 2008

Oracle Exadata

You must have already heard of Oracle Exadata Storage Server.  This is a new server from HP and Oracle which consists of a grid of 8 Intel based servers running Oracle Enterprise Linux.  More information is available on Kevin Closson's blog in this article.  What surprised me is that metalink notes have already started appearing for this product which was just announced by Larry Ellison in Oracle Openworld 2008.

735321.1 Exadata Storage Server Configuration Collection Guide
735323.1 Exadata Storage Server Diagnostic Collection Guide
735620.1 IORM (IO Resource Manager) Diagnostic Collection Guide

Friday, October 3, 2008

How to open a database without resetlogs

You do a restore of backup where online logs are present.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

If you recreate the controlfile after startup nomount, the DB would open without needing RESETLOGS;

SQL> alter database backup controlfile to trace;

SQL> startup nomount

Note: rename OLD
controlfiles prior to re-creating the controlfile.

--------------control.sql -------------
CREATE CONTROLFILE
REUSE DATABASE "V102" NORESETLOGS ARCHIVELOG

MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES
1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/just11i/dbdata/redo01.log' SIZE 100M,
GROUP 2 '/just11i/dbdata/redo02.log' SIZE 100M,
GROUP
3 '/just11i/dbdata/redo03.log' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/just11i/dbdata/system01.dbf',
'/just11i/dbdata/undotbs01.dbf',
'/just11i/dbdata/cwmlite01.dbf',
'/just11i/dbdata/drsys01.dbf',
'/just11i/dbdata/example01.dbf',
'/just11i/dbdata/indx01.dbf',
'/just11i/dbdata/odm01.dbf',
'/just11i/dbdata/tools01.dbf',
'/just11i/dbdata/users01.dbf',
'/just11i/dbdata/xdb01.dbf'
CHARACTER
SET WE8ISO8859P1
;

SQL> @/tmp/control.sql

Control file created.

SQL> alter database open;

Database altered.

Thursday, October 2, 2008

rm -rf takes a long time

Jimmy pinged me today.  He was doing rm -rf to $COMMON_TOP before starting a clone.  The rm -rf was taking a long time.  I did a truss on his os process of rm -rf

$ truss -p 25517
unlink("OAAttachmentTableBean.class")           = 0
lstat64("OALabeledFieldLayoutBean.class", 0xFFBFEF98) = 0
unlink("OALabeledFieldLayoutBean.class")        = 0
lstat64("OADefaultFormStackLayoutBean.class", 0xFFBFEF98) = 0
unlink("OADefaultFormStackLayoutBean.class")    = 0
lstat64("OADefaultDoubleColumnBean.class", 0xFFBFEF98) = 0
unlink("OADefaultDoubleColumnBean.class")       = 0
lstat64("OAContentContainerBean.class", 0xFFBFEF98) = 0
unlink("OAContentContainerBean.class")          = 0
lstat64("OADefaultHideShowBean.class", 0xFFBFEF98) = 0
unlink("OADefaultHideShowBean.class")           = 0
lstat64("OAListOfValuesBean.class", 0xFFBFEF98) = 0
unlink("OAListOfValuesBean.class")              = 0
lstat64("OAStyledItemBean.class", 0xFFBFEF98)   = 0
unlink("OAStyledItemBean.class")                = 0
lstat64("OAStyledListBean.class", 0xFFBFEF98)   = 0
unlink("OAStyledListBean.class")                = 0
lstat64("OAFlexibleCellLayoutBean.class", 0xFFBFEF98) = 0
unlink("OAFlexibleCellLayoutBean.class")        = 0
lstat64("OAFlexibleLayoutBean.class", 0xFFBFEF98) = 0
unlink("OAFlexibleLayoutBean.class")            = 0
lstat64("OAFlexibleRowLayoutBean.class", 0xFFBFEF98) = 0
unlink("OAFlexibleRowLayoutBean.class")         = 0
lstat64("OAKFFLovBean.class", 0xFFBFEF98)       = 0
unlink("OAKFFLovBean.class")                    = 0
lstat64("OAMessageComponentLayoutBean.class", 0xFFBFEF98) = 0
unlink("OAMessageComponentLayoutBean.class")    = 0
getdents64(12, 0xFF26E000, 8192)                = 0
close(12)                                       = 0
chdir("..")                                     = 0
rmdir("layout")                                 = 0
stat64(".", 0xFFBFEFF8)                         = 0
lstat64("OADescriptiveFlexBean.class", 0xFFBFF0F0) = 0


The lstat64() function gets status information about a specified file and places it in the area of
memory pointed to by buf. If the named file is a symbolic link, lstat64() returns information about the symbolic link itself.

The unlink() function will erase full (or empty) directories, where permissions are ok.

The getdetns64() function syscall retrieves information about the content of a directory

It seems that, because of the large number of java files inside common top, it was taking a long time to recurse through them.

Wednesday, October 1, 2008

undo_retention in 10g and 11g

undo_retention definition remains the same in 10g and 11g:

UNDO_RETENTION

PropertyDescription
Parameter typeInteger
Default value900
ModifiableALTER SYSTEM
Range of values0 to 232 - 1 (max value represented by 32 bits)
Real Application ClustersOracle recommends that multiple instances have the same value.

UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.

The setting of this parameter should account for any flashback requirements of the system. Automatic tuning of undo retention is not supported for LOBs. The RETENTION value for LOB columns is set to the value of theUNDO_RETENTION parameter.

The UNDO_RETENTION parameter can only be honored if the current undo tablespace has enough space. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space. This action can potentially cause some queries to fail with a "snapshot too old" message.

The amount of time for which undo is retained for the Oracle Database for the current undo tablespace can be obtained by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT dynamic performance view.

There is a bug in Oracle 10g which causes space problems because of automatic tuning of undo_retention.  This is described in Metalink note 420525.1.  You need to apply DB patch  5387030 to resolve this issue.