2009/08/19

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;

2009/08/17

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

2009/08/11

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;

2009/08/04

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