Estimate size for undo tablespace

Sizing an UNDO tablespace requires three pieces of information:

UR: UNDO_RETENTION in seconds
UPS: Number of undo data blocks generated per second
DBS: Overhead varies based on extent and file size (db_block_size)

UndoSpace = (UR * (UPS * DBS) + DBS)
or, when the guesstime equates to zero, then add a multiplier (24) to the overhead (DBS) to derive more appropriate results:
UndoSpace = (UR * (UPS * DBS)) + (DBS * 24)

UNDO_RETENTION and DB_BLOCK_SIZE can be obtained from the initialization file. UPS can be acquired from the following query:

SELECT (sum(undoblks))/sum((end_time-begin_time)*86400)
FROM v$undostat;

How to Shutdown and Start Grid Control and All Its Components

Grid Control: 10.2.0.4

Shutdown Grid Control

1. $OMS_HOME/bin/emctl stop oms
2. $OMS_HOME/bin/emctl stop iasconsole
3. $OMS_HOME/opmn/bin/opmnctl stopall
4. $AGENT_HOME/bin/emctl stop agent
5. shutdown repository database
6. shutdown listener

Start Grid Control

1. start listener
2. start repository database
3. $OMS_HOME/bin/emctl start oms
4. $OMS_HOME/opmn/bin/opmnctl startproc ias-component=WebCache
5. $AGENT_HOME/bin/emctl start agent
6. $OMS_HOME/bin/emctl start iasconsole

Oracle Components Enabled

Oracle 10.2.0.2

You can check what components, like JVM or XML DB, are loaded into your Oracle Server by query view DBA_REGISTRY:

SQL> SELECT comp_name, status FROM dba_registry;

Enable archivelog mode in RAC

In 10g Release 1, the database must be mounted (not open) by an exclusive instance. In other words, set the CLUSTER_DATABASE parameter to FALSE. After executing the ALTER DATABASE SQL statement to change the archive log mode, shutdown the instance and restart it with the CLUSTER_DATABASE parameter set to TRUE before you restart the other instances.

1. Login to one of the nodes, disable the cluster instance parameter and set log archive destination:

$ sqlplus / as sysdba
SQL> alter system set cluster_database=false scope=spfile sid='';
SQL> alter system set log_archive_dest_1='' scope=spfile sid='*';

2. Shutdown all instances:

$ srvctl stop database -d 

3. Using the local instance, mount the database:

$ sqlplus / as sysdba
SQL> startup mount;

4. Enable archivelog mode

SQL> alter database archivelog;

5. Re-enable support for clustering from the current instance:

SQL> alter system set cluster_database=true scope=spfile sid='';

6. Shutdown the local instance:

SQL> shutdown immediate;

7. Bring all instances back up:

$ srvctl start database -d 

From 10g Release 2, you don't need to modify CLUSTER_DATABASE parameter to enable archivelog mode.

1. Login to one of the nodes, set log archive destination:

$ sqlplus / as sysdba
SQL> alter system set log_archive_dest_1='' scope=spfile sid='*';

2. Shutdown all instances:

$ srvctl stop database -d 

3. Using the local instance, mount the database:

$ sqlplus / as sysdba
SQL> startup mount;

4. Enable archivelog mode

SQL> alter database archivelog;

5. Shutdown the local instance:

SQL> shutdown immediate;

6. Bring all instances back up:

$ srvctl start database -d 

Memory Tuning Tips

Oracle 10gR2

You can check the current size of components in sga using v$ views like:
V$SGA
V$SGASTAT
V$SGA_DYNAMIC_COMPONENTS

If you enabled ASMM, memory statistics will be calculated and indication of memory tuning can be found in the following v$ views:
V$SGA_TARGET_ADVICE
V$PGA_TARGET_ADVICE
V$DB_CACHE_ADVICE
V$SHARED_POOL_ADVICE
V$JAVA_POOL_ADVICE
V$STREAM_POOL_ADVICE

Another interactive way to tune memory is through 'Memory Advisor' interface which can be found in 'Advisor Central' from Grid Control.

SGA_TARGET vs. SGA_MAX_SIZE
SGA_MAX_SIZE is static parameter, while SGA_TARGET is dynamic. The size of SGA_MAX_SIZE is allocated from system memory after system start. On some platforms, the difference (SGA_MAX_SIZE-SGA_TARGET) is in virtual memory, which onlys taks space from disk. While on other platforms (Windows and Linux), it takes memory from system, which means much more SGA_MAX_SIZE than SGA_TARGET can be resouce waste on production system.

ORA-04031 and SGA settings

Oracle 10.2.0.2
OS RHEL 4

Recently, I got error message from Grid Control for one of our production databases.

Failed to connect to database instance: ORA-04031: unable to allocate 4120 bytes of shared memory ("large pool","unknown object","session heap","kzctxhugi1") (DBD ERROR: OCISessionBegin).

The message is pretty obvious: the large pool is not large enough for new allocations. Since it's 10g and we are using ASMM, the SGA is self-adjusted. We put 1.5G to sga_target and didn't specify a value for large_pool_size. Oracle suggests give a minimum value to large_pool_size so that Oracle won't squeeze large pool too small.

Another question I have during the research is that what's point of having both sga_target and sga_max_size.

Well, since sga_max_size is static and sga_target is dynamic, having set a larger value for sga_max_size than sga_target gives you a tuning margin for sga_target later without restarting the database. One thing we should pay attention is that on some OS, such as Windows and Linux, memory is allocated the same size as sga_max_size when the instance is started, but on some other OS, such as Sun Solaris, memory is allocated the same size as sga_target, and the other free memory (sga_max_size - sga_target) is ready for other use.

Grid Control Agent Crash with 'too many open files' Error

Grid Control Agent 10.2.0.4
Database: 10.2.0.2
OS Platform: RHEL Release 4 64bit

Agent crashes a lot, intermittently with 'too many open files' error inside Grid Control. Trace file 'emagent.trc' gives 'health check' error lik this:

2008-03-24 15:24:52 Thread-4124650400 ERROR fetchlets.healthCheck: GIM-00105: file not found
2008-03-24 15:24:52 Thread-4124650400 ERROR engine: [oracle_database,,health_check] : nmeegd_GetMetricData failed : Instance Health Check initialization failed due to one of the following causes: the owner of the EM agent process is not same as the owner of the Oracle instance processes; the owner of the EM agent process is not part of the dba group; or the database version is not 10g (10.1.0.2) and above.
2008-03-24 15:24:52 Thread-4124650400 WARN collector: Error exit. Error message:Instance Health Check initialization failed due to one of the following causes: the owner of the EM agent process is not same as the owner of the Oracle instance processes; the owner of the EM agent process is not part of the dba group; or the database version is not 10g (10.1.0.2) and above

Cause: Bug 5872000 - Healthcheck Error Occurs fror 32Bit Database on 64Bit OS Due to Bug4526916 Fix.
The Healthcheck file, namely $ORACLE_HOME/dbs/hc_.cat file differs in size from the memory structure used by the Agent to read it. This file is created by the database on startup time, if not present.

This happens when the database is e.g. 10.2.0.4 and the agent is 10.2.0.3 and vice versa.

Possible solutions:

1. Apply Patch 5872000 to databases on 64-bit machine.
This needs to be applied on top of 10.1 -> 10.2.0.3, and 11.1.0.6 databases. THe file $ORACLE_HOME/dbs/hc_.dat may need to be removed before starting up the database after patch application. This file is created on database start up if not present. The agent uses this file for the Healthcheck metric. By recreating the file on start up after the patch application, the file is the correct one needed by the agent.
2.Disable the healthcheck metric per database in Grid Control.
Check 379423.1 in Metalink on 'How to edit or disable the Health Check Metric Collection in Grid Control 10.2'.

Note: If the second workaround is applied, then you have to redo it everytime you add new database target into Grid Control.

Reference Doc in Metalink:
564617.1
566607.1
379423.1
469227.1