Master Note of Linux OS Requirements for Database Server [ID 851598.1]

Master Note of Linux OS Requirements for Database Server [ID 851598.1]
		Modified 01-JUL-2011 Type REFERENCE Status PUBLISHED

In this Document
Purpose
Scope
Master Note of Linux OS Requirements for Database Server
Red Hat Enterprise Linux (RHEL)
SuSE Linux Enterprise Server (SLES)
Oracle Enterprise Linux (OEL)
Using My Oracle Support Effectively
Generic Links
Community Discussions
References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.1 - Release: 9.2 to 11.2
Oracle Server - Standard Edition - Version: 9.2.0.1 to 11.2.0.1 [Release: 9.2 to 11.2]
Linux x86
IBM: Linux on System z
Generic Linux
IBM: Linux on POWER Systems
Linux x86-64
Linux Itanium

Purpose

This Master Note is intended to provide an index and references to the most frequently used My Oracle Support articles with respect to Linux OS Requirements for Oracle Database Software Installation.

The vast majority of software installation and relinking problems on Linux are caused by missing OS requirements.

If you are encountering a software installation or relinking problem, it is vital that you ensure your OS meets all of the minimum requirements documented in the appropriate article below before creating a new SR.

Scope

Only Linux articles are referenced in this article. For an "all-in-one" article that covers OS requirements for most major OS platforms, please refer to...

Document 169706.1 Oracle Database ... Installation and Configuration Requirements Quick Reference

Where a My Oracle Support article for a given version/platform is "Not available", you should refer to the Installation Guide and the article referenced above.

Master Note of Linux OS Requirements for Database Server

Red Hat Enterprise Linux (RHEL)

Document 376183.1 Defining a "default RPMs" installation of the RHEL OS

Quick Links for RHEL

RHEL5: x86 x86_64 Itanium zLinux Power
RHEL4: x86 x86_64 Itanium zLinux Power
RHEL3: x86 x86_64 Itanium zLinux Power


RHEL5 x86:

9.2.0 - Not certified, not supported, not planned.
10.1.0 - Not certified, not supported, not planned.
10.2.0 - Document 419646.1 Requirements For Installing Oracle 10gR2 On RHEL 5 (x86)
11.1.0 - Document 438765.1 Requirements for Installing Oracle 11gR1 32bit RDBMS on RHEL 5
11.2.0 - Document 880936.1 Requirements for Installing Oracle 11gR2 RDBMS on RHEL (and OEL) 5 on 32-bit x86



RHEL5 x86-64:

9.2.0 - Not certified, not supported, not planned.
10.1.0 - Not certified, not supported, not planned.
10.2.0 - Document 421308.1 Requirements For Installing Oracle10gR2 On RHEL 5 (x86_64)
11.1.0 - Document 438766.1 Requirements for Installing Oracle 11gR1 RDBMS on RHEL 5 on AMD64/EM64T
11.2.0 - Document 880989.1 Requirements for Installing Oracle 11gR2 RDBMS on RHEL (and OEL) 5 on AMD64/EM64T>



RHEL5 Itanium:

9.2.0 - Not certified, not supported, not planned.
10.1.0 - Not certified, not supported, not planned.
10.2.0 - Document 748378.1 Requirements for Installing Oracle 10gR2 RDBMS on RHEL 5 on Linux Itanium (ia64)



RHEL5 zLinux:

9.2.0 - Not certified, not supported, not planned.
10.1.0 - Not certified, not supported, not planned.
10.2.0 - Document 741646.1 Requirements for Installing Oracle 10gR2 RDBMS on RHEL 5 on zLinux (s390x)



RHEL5 Power:

10.2.0 - Document 341507.1 Oracle Database Server on Linux on IBM POWER



RHEL4 x86:

9.2.0 - Document 303859.1 Requirements for Installing Oracle 9iR2 on RHEL 4
10.1.0 - Document 392940.1 Requirements for Installing Oracle 10.1.0.x RDBMS on RHEL 4 x86 platform
10.2.0 - Document 343431.1 Requirements for Installing Oracle 10gR2 RDBMS on RHEL 4 x86 platform
11.1.0 - Document 430653.1 Requirements for Installing Oracle 11gR1 32-bit on RHEL 4
11.2.0 - Document 880211.1 Requirements for Installing Oracle 11gR2 RDBMS on RHEL (and OEL) 4 x86



RHEL4 x86-64:

9.2.0 - Document 353529.1 Requirements for Installing Oracle 9iR2 64-bit on RHEL 4 x86-64 (AMD64/EM64T)
10.1.0 - Document 390900.1 Requirements for Installing Oracle 10g (10.1.0.x) RDBMS on RHEL 4 on AMD64/EM64T (Linux x86-64)
10.2.0 - Document 339510.1 Requirements for Installing Oracle 10gR2 RDBMS on RHEL 4 on AMD64/EM64T
11.1.0 - Document 437123.1 Requirements for Installing Oracle 11gR1 RDBMS on RHEL 4 on AMD64/EM64T
11.2.0 - Document 880942.1 Requirements for Installing Oracle 11gR2 RDBMS on RHEL (and OEL) 4 on AMD64/EM64T



RHEL4 Itanium:

9.2.0 - Not available
10.1.0 - Not available
10.2.0 - Not available



RHEL4 zLinux:

9.2.0 - Not certified, not supported, not planned.
10.1.0 - Not certified, not supported, not planned.
10.2.0 - Document 420382.1 Requirements for Installing Oracle 10gR2 RDBMS on RHEL 4 on zLinux (s390x)



RHEL4 Power:

10.1.0 - Not certified, not supported, not planned.
10.2.0 - Document 341507.1 Oracle Database Server on Linux on IBM POWER



RHEL3 x86:

9.2.0 - Document 252217.1 Requirements for Installing Oracle 9iR2 32-bit on RHEL 3
10.1.0 - Document 394360.1 Requirements for Installing Oracle 10g 32-bit on RHEL 3
10.2.0 - Not available
11.1.0 - Not certified, not supported, not planned.
11.2.0 - Not certified, not supported, not planned.



RHEL3 x86-64:

9.2.0 - Document 308588.1 Requirements for Installing Oracle 9iR2 x86_64 on RHEL 3
10.1.0 - Document 351679.1 Requirements for RPM Arch. for 10g x86_64 on RHEL 3
10.2.0 - Document 353735.1 Requirements for RPM Arch. for 10gR2 x86_64 on RHEL 3
11.1.0 - Not certified, not supported, not planned.
11.2.0 - Not certified, not supported, not planned.



RHEL3 Itanium:

9.2.0 - Not available
10.1.0 - Not available
10.2.0 - Not available
11.1.0 - Not certified, not supported, not planned.
11.2.0 - Not certified, not supported, not planned.



RHEL3 zLinux:

9.2.0 - Not certified, not supported, not planned.
10.1.0 - Not certified, not supported, not planned.
10.2.0 - Not certified, not supported, not planned.
11.1.0 - Not certified, not supported, not planned.
11.2.0 - Not certified, not supported, not planned.



RHEL3 Power:

10.2.0 - Not certified, not supported, not planned.
11.1.0 - Not certified, not supported, not planned.
11.2.0 - Not certified, not supported, not planned.


SuSE Linux Enterprise Server (SLES)

Document 386391.1 Defining a "default RPMs" installation of the SLES OS

Quick Links for SLES

SLES11: x86 x86_64
SLES10: x86 x86_64 zLinux Power
SLES 9: x86 x86_64 zLinux Power


SLES11 x86:

9.2.0 - Not certified, not supported, not planned.
10.1.0 - Not certified, not supported, not planned.
11.1.0 - Document 849583.1 Requirements for Installing Oracle 11gR1 32-bit (x86) on SLES 11
11.2.0 - Document 881025.1 Requirements for Installing Oracle 11gR2 32-bit (x86) on SLES 11



SLES11 x86_64:

9.2.0 - Not certified, not supported, not planned.
10.1.0 - Not certified, not supported, not planned.
10.2.0 - Document 956194.1 Requirements for Installing Oracle 10gR2 64-bit (AMD64/EM64T) on SLES 11
11.1.0 - Document 1081555.1 Requirements for Installing Oracle 11gR1 64-bit (AMD64/EM64T) on SLES 11
11.2.0 - Document 881044.1 Requirements for Installing Oracle 11gR2 64-bit (AMD64/EM64T) on SLES 11



SLES10 x86:

9.2.0 - Not certified, not supported, not planned.
10.1.0 - Not certified, not supported, not planned.
10.2.0 - Document 387137.1 Requirements for Installing Oracle 10gR2 32-bit (x86) on SLES 10
11.1.0 - Document 452818.1 Requirements for Installing Oracle 11gR1 32-bit on SLES 10
11.2.0 - Document 763386.1 Requirements for Installing Oracle 11gR2 32-bit on SLES 10 (x86)



SLES10 x86-64:

9.2.0 - Not certified, not supported, not planned.
10.1.0 - Not certified, not supported, not planned.
10.2.0 - Document 373681.1 Requirements for Installing Oracle 10gR2 64-bit (AMD64/EM64T) on SLES 10
11.1.0 - Document 457143.1 Requirements for Installing Oracle 11gR1 64-bit (AMD64/EM64T) on SLES 10
11.2.0 - Document 884435.1 Requirements for Installing Oracle 11gR2 64-bit (AMD64/EM64T) on SLES 10



SLES10 s390x (zLinux):

9.2.0 - Not available
10.1.0 - Not available
10.2.0 - Document 1082253.1 Requirements for Installing Oracle 10gR2 RDBMS on SUSE SLES 10 on zLinux (s390x)
11.1.0 - Not certified, not supported, not planned.



SLES10 Power:

10.2.0 - Document 341507.1 Oracle Database Server on Linux on IBM POWER



SLES9 x86:

9.2.0 - Document 427976.1 Requirements for Installing Oracle 9iR2 32-bit on SLES 9
10.1.0 - Not available
10.2.0 - Document 400429.1 Requirements for Installing Oracle 10gR2 32-bit on SLES 9
11.1.0 - Not certified, not supported, not planned.



SLES9 x86-64:

9.2.0 - Document 361169.1 Requirements for Installing Oracle 9iR2 64-bit on SuSE SLES 9 x86_64 (AMD64/EM64T)
10.1.0 - Not available
10.2.0 - Document 365607.1 Requirements for Installing Oracle 10gR2 RDBMS on SLES 9 on AMD/EM64T
11.1.0 - Not certified, not supported, not planned.



SLES9 s390x (zLinux):

9.2.0 - Document 270577.1 Installing Oracle 9i on IBM z/Series - SLES8/9
10.1.0 - Not available
10.2.0 - Document 431443.1 Requirements for Installing Oracle 10gR2 RDBMS on SLES 9 on zLinux (s390x)
11.1.0 - Not certified, not supported, not planned.



SLES9 Power:

10.2.0 - Document 341507.1 Oracle Database Server on Linux on IBM POWER


Oracle Enterprise Linux (OEL)

Document 401167.1 Defining a "default RPMs" installation of the Oracle EL OS

Quick Links for OEL

OEL5: x86 x86_64
OEL4: x86 x86_64


OEL5 x86:

9.2.0 - Not certified, not supported, not planned.
10.1.0 - Not certified, not supported, not planned.
10.2.0 - Identical to RHEL5 x86. See "RHEL5 x86:" section above.
11.1.0 - Identical to RHEL5 x86. See "RHEL5 x86:" section above.
11.2.0 - Identical to RHEL5 x86. See "RHEL5 x86:" section above.



OEL5 x86-64:

9.2.0 - Not certified, not supported, not planned.
10.1.0 - Not certified, not supported, not planned.
10.2.0 - Identical to RHEL5 x86-64. See "RHEL5 x86-64:" section above.
11.1.0 - Identical to RHEL5 x86-64. See "RHEL5 x86-64:" section above.
11.2.0 - Identical to RHEL5 x86_64. See "RHEL5 x86_64:" section above.



OEL4 x86:

9.2.0 - Identical to RHEL4 x86. See "RHEL4 x86:" section above.
10.1.0 - Identical to RHEL4 x86. See "RHEL4 x86:" section above.
10.2.0 - Identical to RHEL4 x86. See "RHEL4 x86:" section above.
11.1.0 - Identical to RHEL4 x86. See "RHEL4 x86:" section above.
11.2.0 - Identical to RHEL4 x86. See "RHEL4 x86:" section above.



OEL4 x86-64:

9.2.0 - Identical to RHEL4 x86-64. See "RHEL4 x86-64:" section above.
10.1.0 - Document 436800.1 Requirements for Installing Oracle 10g RDBMS on OEL 4 on AMD64/EM64T
10.2.0 - Document 418890.1 Requirements for Installing Oracle 10gR2 RDBMS on OEL 4 update 4 on AMD64/EM64T
- Document 435900.1 Requirements for Installing Oracle 10gR2 RDBMS on OEL 4 update 5 on AMD64/EM64T
11.1.0 - Identical to RHEL4 x86-64. See "RHEL4 x86-64:" section above.
11.2.0 - Identical to RHEL4 x86_64. See "RHEL4 x86_64:" section above.

Using My Oracle Support Effectively

Document 374370.1 New Customers Start Here
Document 747242.5 My Oracle Support Configuration Management FAQ
Document 868955.1 My Oracle Support Health Checks Catalog
Document 166650.1 Working Effectively With Global Customer Support
Document 199389.1 Escalating Service Requests with Oracle Support Services

Generic Links

Document 854428.1 Patch Set Updates for Oracle Products
Document 1061295.1 Patch Set Updates - One-off Patch Conflict Resolution
Document 881382.1 Critical Patch Update October 2009 Patch Availability Document for Oracle Products
Document 967472.1 Critical Patch Update January 2010 Patch Availability Document for Oracle Products
Document 1060989.1 Critical Patch Update April 2010 Patch Availability Document for Oracle Products
Document 756671.1 Oracle Recommended Patches -- Oracle Database
Document 268895.1 Oracle Database Server Patchset Information, Versions: 8.1. 7 to 11.2.0
Document 161549.1 Oracle Database Server and Networking Patches for Microsoft Platforms

Community Discussions

Still have questions? Use the communities window below to discuss this subject with your peers.

NOTE: The communities window below is the live community, not a screenshot!

For Database Install Linux/Unix Community, click here to open in main browser window.
For Database Install Windows Community, click here to open in main browser window.

References

NOTE:169706.1 - Oracle Database on Unix AIX,HP-UX,Linux,Mac OS X,Solaris,Tru64 Unix Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)
NOTE:841292.1 - Linux Threads: Why some Oracle RDBMS Releases do not work on some Linux Releases?

Related Products Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Standard Edition Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition Keywords INSTALLATION; LINUX; ORACLE DATABASE; PREREQS; RHEL; RPM; SUSE; ZLINUX

Back to top

Rate this document

How to Change Applications Passwords using Applications Schema Password Change Utility (FNDCPASS or AFPASSWD)

How to Change Applications Passwords using Applications Schema Password Change Utility (FNDCPASS or AFPASSWD) [ID 437260.1]
		Modified 08-MAY-2011 Type HOWTO Status PUBLISHED

In this Document
Goal
Solution
Using the FNDCPASS Utility:
Verify the new password.
Examples:
Using the AFPASSWD Utility as of R12.1.2:
Diagnostics & Utilities Community:
Troubleshooting FNDCPASS
References

---

Applies to:

Oracle Application Object Library - Version: 11.5.10.2 and later [Release: 11.5.10 and later ]
Information in this document applies to any platform.
Checked for relevance 23-APR-2011

Goal

• The goal of this document is to help understand the process of changing passwords in Oracle Applications. As the Applications directory structure has changed a little, the files that need to be updated have also changed, although the FNDCPASS commands to change/reset the passwords remained pretty much the same.
  
  
• For R12.1.2, an enhanced version of FNDCPASS is available using AFPASSWD noted at the bottom of this document.

Solution

Since changing passwords frequently helps ensure database security, Oracle Applications provides a command line utility, FNDCPASS, to change/reset Oracle Applications schema passwords. This utility changes the password registered in Oracle Applications tables, changes the schema password in the database and can also change user passwords.

Note: One cannot change a schema name, such as APPLSYS or GL, after a product is installed, with FNDCPASS.
Ensure that the entire Oracle Applications system has been shut down before changing any schema passwords.
All users should log out and the Applications system should be down before running this utility.
If Oracle Applications user passwords are being changed then the relevant users should not be logged in.
Before changing any passwords, you should make a backup of the tables FND_USER and FND_ORACLE_USERID.

Note: SOURCE the environment FIRST. Ex:

1. Log into the Operating system level by way of the applmgr user.
2. Run the environment script APPSORA.env:
a. cd $APPL_TOP
b. Run APPSORA.env.
c. The above should also run _.env, but can verify by running it.
d. cd admin.
e. Run adovars.env.

Using the FNDCPASS Utility:

FNDCPASS / 0 Y \

/

Please set the depending on your needs:

Note:
The SYSTEM token is used when changing the APPLSYS password.
The ORACLE token is used when changing a SINGLE Applications schema password.
The ALLORACLE token is used when changing ALL Applications schema passwords.
The USER token is used when changing an Applications USER password.

Note: Passwords for APPLSYS and the APPS schemas -- including the MRC schema -- must be the same. If you change the password for one, FNDCPASS automatically changes the others. When changing APPS (or APPLSYS) and APPLSYSPUB passwords, do not restart the system until the entire password change process has been completed.

Verify the new password.

If you changed the password for APPS (and APPLSYS), restart all concurrent managers, then log on to Oracle Applications to test the new password.

Examples:

A). To change the APPS and APPLSYS schema password:

Use the following command to change passwords for schema that are used by shared components of Oracle Applications.

FNDCPASS  0 Y  SYSTEM  

FNDCPASS uses the following arguments when changing the APPLSYS password. When specifying the SYSTEM token, FNDCPASS expects the next arguments to be the APPLSYS username and the new password.

• logon The Oracle username/password.
• system/password The username and password for the SYSTEM DBA account.
• username The APPLSYS username. For example, 'applsys'.
• new_password The new password.

This command does the following:

1. Validates APPLSYS.
2. Re-registers password in Oracle Applications.
3. Changes the APPLSYS and all APPS passwords (for multi-APPS schema installations) to the same password.
   Because everything with a Privilege Level [set to any of ('E', 'U', 'D')] in the FND_ORACLE_USERID table must always have the same password, FNDCPASS updates these passwords as well as APPLSYS's password.
   For example, the APPS password will be updated when the APPLSYS password is changed.
   
4. ALTER USER is executed to change the ORACLE password for the above ORACLE users.

For instance, the following command changes the APPLSYS password to 'WELCOME'.

FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME

B). To change an Oracle Applications schema password (other than APPS/APPLSYS):

Use this command to change the password of a schema provided by an individual product in Oracle Applications.

FNDCPASS  0 Y  ORACLE  

Use the above command with the following arguments. When specifying the ORACLE token, FNDCPASS expects the next arguments to be an ORACLE username and the new password.

• logon The Oracle username/password.
• system/password The username and password for the SYSTEM DBA account.
• username The Oracle username. For example, 'GL'.
• new_password The new password.

For example, the following command changes the GL user password to 'GL1'.

FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1

C). To change all ORACLE schema passwords:

Use this command to change the passwords of all schemas provided by Oracle Applications products.

FNDCPASS  0 Y  ALLORACLE 

Use the above command with the following arguments. When specifying the ALLORACLE token, FNDCPASS expects the next argument to be the new password.

• logon The Oracle username/password.
• system/password The username and password for the SYSTEM DBA account.
• new_password The new password.

For example, the following command changes all ORACLE schema passwords to "WELCOME":

FNDCPASS apps/apps 0 Y system/manager ALLORACLE WELCOME 

For additional information on the use of ALLORACLE, please reference NOTE 189367.1 - Best Practices for Securing the E-Business Suite

D). To change an Oracle Applications user's password:

Use this command to change an individual Oracle Applications user's password.

FNDCPASS  0 Y  USER   

Use the above command with the following arguments. When specifying the USER token, FNDCPASS expects the next arguments to be an Oracle Applications username and the new password.

• logon The Oracle username/password.
• system/password The username and password for the System DBA account.
• username The Oracle Applications username. For example, 'VISION'.
• new_password The new password.

For example, if you were changing the password for the user VISION to 'WELCOME', you would use the following command:

FNDCPASS apps/apps 0 Y system/manager USER VISION WELCOME 

Using the AFPASSWD Utility as of R12.1.2:

For Applications release 12.1.2, please reference page 11-8 of the 'Oracle E-Business Suite System Administrator's Guide - Configuration' for use of the AFPASSWD utility. Document 457166.1 must be used for migration from FNDCPASS.

NOTE:
AFPASSWD only prompts for passwords required for the current operation,
allowing separation of duties between applications administrators and database administrators.
This also improves interoperability with Oracle Database Vault.
In contrast, the FNDCPASS utility currently requires specification of the APPS and the
SYSTEM usernames and corresponding passwords, preventing separation of duties
between applications administrators and database administrators.

When changing a password with AFPASSWD, the user is prompted to enter the new password twice to confirm.

AFPASSWD can be run from the database tier as well as the application tier.
In contrast, FNDCPASS can only be run from the application tier.

How to Perform a Health Check on the Database

How to Perform a Health Check on the Database [ID 122669.1]
		Modified 24-MAR-2011 Type BULLETIN Status PUBLISHED

Applies to:

Oracle Server - Enterprise Edition - Version: 7.3.4.0 to 11.2.0.1 - Release: 7.3.4 to 11.2
Information in this document applies to any platform.

Purpose

This article explains how to perform a Basic Health Check on the database verifying
several configuration issues.  General guidelines are given on what areas to investigate
to get a better overview on how the database is working and evolving. These guidelines
will reveal common issues regarding configuration as well as problems that may occur in the future.

For a more in depth health check to check Database structure and data dictionary integrity,
please follow the appropriate links in chapter 11.

The areas investigated here are mostly based on scripts and are brought to you without
any warranty, these scripts may need to be adapted for next database releases and features.
This article will probably need to be extended to serve specific application need0s/checks.

Although some performance areas are discussed in this article, it is not the intention
of this article to give a full detailed explanation of optimizing the database performance.  

Scope and Application

1. Parameter file
2. Controlfiles
3. Redolog files
4. Archiving
5. Datafiles
5.1 Autoextend
5.2 Location
6. Tablespaces
6.1 SYSTEM Tablespace
6.2 SYSAUX Tablespace (10g Release and above)
6.3 Locally vs Dictionary Managed Tablespaces
6.4 Temporary Tablespace
6.5 Tablespace Fragmentation
7. Objects
7.1 Number of Extents
7.2 Next extent
7.3 Indexes
8. AUTO vs MANUAL undo
8.1 AUTO UNDO
8.2 MANUAL UNDO
9. Memory Management
9.1 Pre Oracle 9i
9.2 Oracle 9i
9.3 Oracle 10g
9.4 Oracle 11g
10. Logging & Tracing
10.1 Alert File
10.2 Max_dump_file_size
10.3 User and core dump size parameters
10.4 Audit files
11. Advanced Health Checking

How to Perform a Health Check on the Database

1. Parameter file

The parameter file can exists in 2 forms. First of all we have the text-based
version, commonly referred to as init.ora or pfile, and a binary-based file,
commonly referred to as spfile. The pfile can be adjusted using a standard Operating
System editor, while the spfile needs to be managed through the instance itself.
It is important to realize that the spfile takes presedence above the pfile, meaning
whenever there is an spfile available this will be automatically taken unless
specified otherwise.

NOTE: Getting an RDA report after making changes to the database configuration is
also a recommendation. Keeping historical RDA reports will ensure you have
an overview of the database configuration as the database evolves.

Reference:
Note 249664.1 Pfile vs SPfile

2. Controlfiles

It is highly recommended to have at least two copies of the controlfile. This can
be done by mirroring the controlfile, strongly recommended on different physical
disks. If a controlfile is lost, due to a disk crash for example, then you can
use the mirrored file to startup the database. In this way fast and easy recovery
from controlfile loss is obtained.

connect as sysdba
SQL> select status, name from v$controlfile;

STATUS NAME
------- ---------------------------------
/u01/oradata/L102/control01.ctl
/u02/oradata/L102/control02.ctl



The location and the number of controlfiles can be controlled by the 'control_files'
initialization parameter.

3. Redolog files

The Oracle server maintains online redo log files to minimize loss of data in the
database. Redo log files are used in a situation such as instance failure to recover
commited data that has not yet been written to the data files. Mirroring the
redo log files, strongly recommended on different physical disks, makes recovery more
easy in case one of the redo log files is lost due to a disk crash, user delete, etc.

connect as sysdba
SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
--------- ------- ------ -----------------------------------
1 ONLINE /u01/oradata/L102/redo01_A.log
1 ONLINE /u02/oradata/L102/redo01_B.log

2 ONLINE /u01/oradata/L102/redo02_A.log
2 ONLINE /u02/oradata/L102/redo02_B.log

3 ONLINE /u01/oradata/L102/redo03_A.log
3 ONLINE /u02/oradata/L102/redo03_B.log



At least two redo log groups are required, although it is advisable to have at least
three redo log groups when archiving is enabled (see the following chapter). It is
common, in environments where there are intensive log switches, to see the ARCHiver
background process fall behind of the LGWR background process. In this case the LGWR
process needs to wait for the ARCH process to complete archiving the redo log file.

References:
Note 102995.1 Maintenance of Online Redo Log Groups and Members

4. Archiving

Archiving provides the mechanism needed to backup the changes of the database.
The archive files are essential in providing the necessary information to recover the
database. It is advisable to run the database in archive log mode, although you may
have reasons for not doing this, for example in case of a TEST environment where you
accept to loose the changes made between the current time and the last backup.
You may ignore this chapter when the database doesn't run in archive log mode.

There are several ways of checking the archive configuration, below is one of them:

connect as sysdba
SQL> archive log list

Database log mode No Archive Mode --OR-- Archive Mode
Automatic archival Disabled --OR-- Enabled
Archive destination --OR-- USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence seq. no
Current log sequence seq. no



Pre-10g, if the database is running in archive log mode but the automatic archiver
process is disabled, then you were required to manually archive the redolog files.
If this is not done in time then the database is frozen and any activity is prevented.
Therefore you should enable automatic archiving when the database is running in archive
log mode. This can be done by setting the 'log_archive_start' parameter to true in
the parameter file.
Starting from 10g, this parameter became obsolete and is no longer required to be set
explicitly. It is important that there is enough free space on the dedicated disk(s)
for the archive files, otherwise the ARCHiver process can't write and a crash is inevitable.

References:
Note 69739.1 How to Turn Archiving ON and OFF
Note 122555.1 Determine how many disk space is needed for the archive files

5. Datafiles

5.1 Autoextend

The autoextend command option enables or disables the automatic extension of
data files. If the given datafile is unable to allocate the space needed, it
can increase the size of the datafile to make space for objects to grow.

A standard Oracle datafile can have, at most, 4194303 Oracle datablocks.
So this also implies that the maximum size is dependant on the Oracle Block size used.

DB_BLOCK_SIZE Max Mb value to use in any command
~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2048 8191 M
4096 16383 M
8192 32767 M
16384 65535 M



Starting from Oracle 10g, we have a new functionality called BIGFILE, which
allows for bigger files to be created. Please also consider that every Operating
System has its limits, therefore you should make sure that the maximum size of
a datafile cannot be extended past the Operating System allowed limit.

To determine if a datafile and thus, a tablespace, has AUTOEXTEND capabilities:

SQL> select file_id, tablespace_name, bytes, maxbytes, maxblocks, increment_by, file_name
from dba_data_files
where autoextensible = 'YES';



References:
Note 112011.1 ALERT: RESIZE or AUTOEXTEND can "Over-size" Datafiles and Corrupt the Dictionary
Note 262472.1 10g BIGFILE Type Tablespaces Versus SMALLFILE Type

5.2 Location

Verify the location of your datafiles. Overtime a database will grow and datafiles
may be added to the database. Avoid placing datafiles on a 'wherever there is space'
basis as this will complicate backup strategies and maintenance.

Below is an example of bad usage:

SQL> select * from v$dbfile;

FILE# NAME
--------- --------------------------------------------------
1 D:\DATABASE\SYS1D806.DBF
2 D:\DATABASE\D806\RBS1D806.DBF
3 D:\DATABASE\D806\TMP1D806.DBF
5 D:\DATABASE\D806\USR1D806.DBF
6 D:\USR2D806.DBF
7 F:\ORACLE\USR3D806.DBF



References:
Note 115424.1 How to Rename or Move Datafiles and Logfiles

6. Tablespaces

6.1 SYSTEM Tablespace

User objects should not be created in the system tablespace. Doing so can lead
to unnecessary fragmentation and preventing system tables of growing. The following query
returns a list of objects that are created in the system tablespace but not owned
by SYS or SYSTEM.

SQL> select owner, segment_name, segment_type
from dba_segments
where tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM');



6.2 SYSAUX Tablespace (10g Release and above)

The SYSAUX tablespace was automatically installed as an auxiliary tablespace to
the SYSTEM tablespace when you created or upgraded the database. Some database
components that formerly created and used separate tablespaces now occupy the
SYSAUX tablespace.

If the SYSAUX tablespace becomes unavailable, core database functionality will
remain operational. The database features that use the SYSAUX tablespace could
fail, or function with limited capability.

The amount of data stored in this tablespace can be significant and may grow
over time to unmanageble sizes if not configured properly. There are a few
components that need special attention.

To check which components are occupying space:

SQL> select space_usage_kbytes, occupant_name, occupant_desc
from v$sysaux_occupants
order by 1 desc;



Reference:
Note 329984.1 Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER

6.3 Locally vs Dictionary Managed Tablespaces

Locally Managed Tablespaces are available since Oracle 8i, however they became
the default starting from Oracle 9i. Locally Managed Tablespaces, also referred to
as LMT, have some advantage over Data Dictionary managed tablespaces.

To verify which tablespace is Locally Managed or Dictionary Managed, you can run
the following query:

SQL> select tablespace_name, extent_management
from dba_tablespaces;



References:
Note 93771.1 Introduction to Locally-Managed Tablespaces
Note 105120.1Advantages of Using Locally Managed vs Dictionary Managed Tablespaces

6.4 Temporary Tablespace

* Locally Managed Tablespaces use tempfiles to serve the temporary tablespace,
whereas Dictionary Managed Tablespaces use a tablespace of the type temporary.
When you are running an older version (pre Oracle 9i), then it is important to
check the type of tablespace used to store the temporary segments. By default,
all tablespaces are created as PERMANENT, therefore you should make sure that
the tablespace dedicated for temporary segments is of the type TEMPORARY.

SQL> select tablespace_name, contents
from dba_tablespaces;

TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
USER_DATA PERMANENT
ROLLBACK_DATA PERMANENT
TEMPORARY_DATA TEMPORARY



* Make sure that the users on the database are assigned a tablespace of the
type temporary. The following query lists all the users that have a permanent
tablespace specified as their default temporary tablespace.

SQL> select u.username, t.tablespace_name
from dba_users u, dba_tablespaces t
where u.temporary_tablespace = t.tablespace_name
and t.contents <> 'TEMPORARY';



Note: User SYS and SYSTEM will show the SYSTEM tablespace as there default
temporary tablespace. This value can be altered as well to prevent fragmentation
in the SYSTEM tablespace.

SQL> alter user SYSTEM temporary tablespace TEMP



*The space allocated in the temporary tablespace is reused. This is done for
performance reasons to avoid the bottleneck of constant allocating and de-allocating
of extents and segments. Therefore when looking at the free space in the temporary
tablespace, this may appear as full all the time. The following are a few queries
that can be used to list more meaningful information about the temporary segment usage:

This will give the size of the temporary tablespace:

SQL> select tablespace_name, sum(bytes)/1024/1024 mb
from dba_temp_files
group by tablespace_name;



This will give the "high water mark" of that temporary tablespace (= max used at one time):

SQL> select tablespace_name, sum(bytes_cached)/1024/1024 mb
from v$temp_extent_pool
group by tablespace_name;



This will give current usage:

SQL> select ss.tablespace_name,
sum((ss.used_blocks*ts.blocksize))/1024/1024 mb
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name;



6.5 Tablespace Fragmentation

Heavly fragmented tablespaces can have an impact on the performance, especially
when a lot of Full Table Scans are occurring on the system. Another disadvantage
of fragmentation is that you can get out-of-space errors while the total sum of
all free space is much more then you had requested.

The only way to resolve fragmentation is recreate the object. As of Oracle8i you
can use the 'alter table .. move' command. Prior to Oracle8i you could use export/import.

If you need to defragment your system tablespace, you must rebuild the whole database since
it is NOT possible to drop the system tablespace.

References:
Note 1020182.6 SCRIPT to detect tablespace fragmentation
Note 1012431.6 Common causes of Fragmentation
Note 158162.1 How To Move All Tables From One User To Another Tablespace

7. Objects

7.1 Number of Extents

While the performance hit on over extended objects is not significant, the
aggregate effect on many over extended objects does impact performance. The
following query will list all the objects that have allocated more extents
than a specified minimum. Change the <--minext--> value by an actual number,
in general objects allocating more then 100 a 200 extents can be recreated
with larger extent sizes:

SQL> select owner, segment_type, segment_name, tablespace_name,
count(blocks), SUM(bytes/1024) "BYTES K", SUM(blocks)
from dba_extents
where owner NOT IN ('SYS','SYSTEM')
group by owner, segment_type, segment_name, tablespace_name
having count(*) > <--minext-->>
order by segment_type, segment_name;



7.2 Next extent

It is important that segments can grow and therefore allocate their next extent
when needed. If there is not enough free space in the tablespace then the next
extent can not be allocated and the object will fail to grow.
The following query returns all the segments that are unable to allocate their
next extent :

select s.owner, s.segment_name, s.segment_type,
s.tablespace_name, s.next_extent
from dba_segments s
where s.next_extent > (select MAX(f.bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name);



Note that if there is a lot of fragmentation in the tablespace, then this query
may give you objects that still are able to grow. The above query is based on
the largest free chunk in the tablespace available. If there are a lot of
'small' free chunks after each other, then Oracle will coalesce these to serve
the extent allocation.

Therefore it can be interesting to adapt the script in Note 1020182.6 'SCRIPT
to detect tablespace fragmentation' to compare the next extent for each object
with the 'contiguous' bytes (table space_temp) in the tablespace.

7.3 Indexes

The need to rebuild an index is very rare and often the coalescing the index is
a better option. Please see the following article for a full explanation:

Reference:
Note 122008.1 Script Lists All Indexes that Benefit from a Rebuild

8. AUTO vs MANUAL undo

Starting from Oracle 9i we introduced a new way of managing the before-images.
Previously this was achieved through the RollBack Segments or also referred to as
manual undo. Automatic undo is used when the UNDO_MANAGEMENT parameter is set to
AUTO. When not set or set to MANUAL then we use the 'old' rollback segment mechanism.
Although both versions are still available in current release, automatic undo is preferred.

8.1 AUTO UNDO

There is little to no configuration involved to AUM (Automatic Undo Management).
You basically define the amount of time the before image needs to be kept available.
This is controlled through the parameter UNDO_RETENTION, defined in seconds. So a
value of 900 indicates 15 minutes.

It is important to realize that this value is not honored when we are under space
pressure in the undo tablespace.

Therefore the following formula can be used to calculate the optimal undo tablespace size:

Note 262066.1: How To Size UNDO Tablespace For Automatic Undo Management

Starting from Oracle 10g, you may choose to use the GUARANTEE option, to make sure
the undo information does not get overwritten before the defined undo_retention time.

Note 311615.1: Oracle 10G new feature - Automatic Undo Retention Tuning

8.2 MANUAL UNDO

* Damaged rollback segments will prevent the instance to open the database. Only if names of rollback segments are known, corrective action can be taken. Therefore specify all the rollback segments in the 'rollback_segments' parameter in the init.ora

* Too small or not enough rollback segments can have serious impact on the behavior of your database. Therefore several issues must be taken into account. The following query will show you if there are not enough rollback segments online or if the rollback segments are too small.

SQL> select d.segment_name, d.tablespace_name, s.waits, s.shrinks,
s.wraps, s.status
from v$rollstat s, dba_rollback_segs d
where s.usn = d.segment_id
order by 1;

SEGMENT_NAME TABLESPACE_NAME WAITS SHRINKS WRAPS STATUS
--------------- ------------------ ----- --------- --------- --------
RB1 ROLLBACK_DATA 1 0 160 ONLINE
RB2 ROLLBACK_DATA 31 1 149 ONLINE
SYSTEM SYSTEM 0 0 0 ONLINE



The WAITS indicates which rollback segment headers had waits for them. Typically
you would want to reduce such contention by adding rollback segments.

If SHRINKS is non zero then the OPTIMAL parameter is set for that particular
rollback segment, or a DBA explicitly issued a shrink on the rollback segment.
The number of shrinks indicates the number of times a rollback segment shrinked
because a transaction has extended it beyond the OPTIMAL size. If this value is
too high then the value of the OPTIMAL size should be increased as well as the
overall size of the rollback segment (the value of minextents can be increased
or the extent size itself, this depends mostly on the indications of the WRAPS
column).

The WRAPS column indicate the number of times the rollback segment wrapped to
another extent to serve the transaction. If this number is significant then you
need to increase the extent size of the rollback segment.

Reference:
Note 62005.1 Creating, Optimizing, and Understanding Rollback Segments

9. Memory Management

This chapter is very version driven. Depending on which version you are running the
option available will be different. Overtime Oracle has invested a great deal of
time and effort in managing the memory more efficiently and transparently for the end-user.
Therefore it is advisable to use the automation features as much as possible.

9.1 Pre Oracle 9i

The different memory components (SGA & PGA) needed to be defined at the startup of the
database. These values were static. So if one of the memory components was too low the
database needed to be restarted to make the changes effective.
How to determine the optimal or best value for the different memory components is not
covered in this note, since this would lead us too far. However a parameter that was
often misused in these versions is the sort_area_size.

The 'sort_area_size' parameter in the init.ora defines the amount of memory that can be
used for sorting. This value should be chosen carefully since this is part of the
User Global Area (UGA) and therefore is allocated for each user individually.
If there are a lot of concurrent users performing large sort operation on the database
then the system can run out of memory.

E.g.: You have a sort_area_size of 1Mb, with 200 concurrent users on the database.
Although this memory is allocated dynamically, it can allocate up to 200Mb
and therefore can cause extensive swapping on the system.

9.2 Oracle 9i

Starting from Oracle 9i we introduced the parameters:

workarea_size_policy = [AUTO | MANUAL]
pga_aggregate_target =

This allows you define 1 pool for the PGA memory, which will be shared across sessions.
When you often receive ORA-4030 errors, then this can be an indication that this value is
specified too low.

9.3 Oracle 10g

Automatic Shared Memory Management (ASMM) was introduced in 10g. The automatic shared memory
management feature is enabled by setting the SGA_TARGET parameter to a non-zero value.

This feature has the advantage that you can share memory resources among the different components.
Resources will be allocated and deallocated as needed by Oracle automatically.

Automatic PGA Memory management is still available through the 'workarea_size_policy' and
'pga_aggregate_target' parameters.

9.4 Oracle 11g

Automatic Memory Management (AMM) is being introduced in 11g. This enables automatic tuning
of PGA and SGA with use of two new parameters named MEMORY_MAX_TARGET and MEMORY_TARGET.


Reference:
Note 443746.1 Automatic Memory Management(AMM) on 11g

10. Logging & Tracing

10.1 Alert File

The alert log file of the database is written chronologically. Data is always
appended and therefore this file can grow to an enormous size. It should be
cleared or truncated on a regular basis, as a large alert file occupies
unnecessary disk space and can slow down OS write performance to the file.


Pre-11g:

SQL> show parameter background_dump_dest

NAME TYPE VALUE
------------------------------ ------- ----------------------------------
background_dump_dest string D:\Oradata\Admin\PROD\Trace\BDump



11g and above:

SQL> show parameter diagnostic_dest

NAME TYPE VALUE
------------------------------ ------- ----------------------------------
diagnostic_dest string /oracle/admin/L111



10.2 Max_dump_file_size

Oracle Server processes generate trace files for certain errors or conflicts.
These trace files are of use for further analyzing the problem. The init.ora
parameter 'max_dump_file_size' limits the size of these trace files. The value
of this parameter should be specified in Operating System blocks.
Make sure the disk space can handle the maximum size specified, if not then
this value should be changed.

SQL> show parameter max_dump_file_size

NAME TYPE VALUE
---------------------------------- ------- ---------------------
max_dump_file_size integer 10240



10.3 User and core dump size parameters

The parameters 'user_dump_dest' and 'core_dump_dest' can contain a lot of trace information.
It is important to clear this directory at regular times as this can take up a significant
amount of space.

Note: starting from Oracle 11g, this location is controlled by the 'diagnostic_dest' parameter

Reference:
Note 564989.1 How To Truncate a Background Trace File Without Bouncing the Database

10.4 Audit files

By default, every connection as SYS or SYSDBA is logged in an operating system file.
The location is controlled through the parameter 'audit_file_dest'. If this parameter is
not set then the location defaults to $ORACLE_HOME/rdbms/audit.
Overtime this directory may contain a lot of auditing information and can take up a significant amount of space.

11. Advanced Health Checking

The previous chapters have been outlining the basic items to check to prevent common

database cavehats. In this section you will find references to several articles explaining
how a more in depth analyses and monitoring can be achieved. These article mainly focus
on Data Dictionary Integrity and DataBase structure verification.

Oracle Pre-11g:

Note 456468.1 Identify Data Dictionary Inconsistency
NOTE 136697.1 "hcheck8i.sql" script to check for known problems in Oracle8i,Oracle9i, and Oracle10g


Oracle 11g

Note 466920.1: 11g New Feature Health monitor

Related Products Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition Keywords HEALTHCHECK; INTEGRITY Errors ORA-4030; 4030 ERROR

Back to top

Merge Patches using admrgpch

Merge Patches using admrgpch

Admrgph utility is used to merge two or more patches in oracle apps. The advantage of merging patches is that it reduces downtime as the repetitive task of compiling invalid database objects, generating forms and reports,jar files etc.

How to use Admrgpch to merge patches

Download the patches in /patch directory. Now create 2 subdirectory in /patch say mergesource -which contains the unzipped patches to be merged and mergedest -which contains the merged patch. Please note that both mergesource and mergedest should be created as immediate child of same parent directory say /patch. Now you can execute the following command to merge the patches.

admrgpch -s -d -merge_name

For example->

admrgpch -s -d -merge_name

Please make sure the the merge path log file "admrgpch.log" does not contain any error. If the above command to merge patches completes successfully then it displays the following->

Executing the merge of the patch drivers
-- Processing patch: /patch/mergesource/5708576
-- Done processing patch: /patch/mergesource/5708576

-- Processing patch: /patch/mergesource/4428060
-- Done processing patch: /patch/mergesource/4428060

Copying files...

5% complete. Copied 269 files of 5373...
10% complete. Copied 538 files of 5373...
15% complete. Copied 806 files of 5373...
20% complete. Copied 1075 files of 5373...
25% complete. Copied 1344 files of 5373...
30% complete. Copied 1612 files of 5373...
35% complete. Copied 1881 files of 5373...
40% complete. Copied 2150 files of 5373...
45% complete. Copied 2418 files of 5373...
50% complete. Copied 2687 files of 5373...
55% complete. Copied 2956 files of 5373...
60% complete. Copied 3224 files of 5373...
65% complete. Copied 3493 files of 5373...
70% complete. Copied 3762 files of 5373...
75% complete. Copied 4030 files of 5373...
80% complete. Copied 4299 files of 5373...
85% complete. Copied 4568 files of 5373...
90% complete. Copied 4836 files of 5373...
95% complete. Copied 5105 files of 5373...
100% complete. Copied 5373 files of 5373...

Character-set converting files...

2 unified drivers merged.

Patch merge completed successfully

Please check the log file at ./admrgpch.log

Now go to the destination merge patch directory say "mergedest". You can see that the admrgpch already created a driver with name "u_.drv" say u_amebrup2.drv. Now apply the merged patch as a single patch using adpatch. So you have to give this driver name u_.drv" when prompted.

Restrictions of admrgpch ->

It will not merge patches of different releases,platform,different parallel modes. Also do not use admrgpch to merge AD and Non-AD patches ad AD patches will change the patch utility itself.