OEM IA DG startup

gbase1:oracle=> lsnrctl dbsnmp_start

gbase1:oracle=> lsnrctl help
LSNRCTL for HPUX: Version 8.1.7.3.0 - Production on 16-JUL-2003 14:19:01

(c) Copyright 1998 Oracle Corporation. All rights reserved.

The following operations are available
An asterisk (*) denotes a modifier or extended command:

start stop status
services version reload
save_config trace spawn
dbsnmp_start dbsnmp_stop dbsnmp_status
change_password quit exit
set* show*

gbase1:oracle=> ps -ef | grep dbsnmp
oracle 20189 1 0 14:15:11 ? 0:01 dbsnmp
oracle 22661 19297 3 14:20:51 pts/tm 0:00 grep dbsnmp
oracle 20192 20189 0 14:15:12 ? 0:00 dbsnmp



gbase1:oracle=> vppcntl -start

gbase1:oracle=> vppcntl -help
USAGE: vppcntl <-stop> <-start> <-ping> <-status> <-refresh>

-stop: shutdown the DataGatherer
-start: startup the DataGatherer
-ping: determine if the Data Gatherer is running
-status: determine if the Data Gatherer is running (same as -ping)
-refresh: re-read the Data Cartridge Registry

gbase1:oracle=> ps -ef | grep vpp
oracle 20257 1 0 14:17:26 pts/tm 0:00 /u01/app/oracle/product/8.1.7/bin/vppdc

Call operating system commands from PL/SQL using EPI

/*--------------------------------------------------------------------
* extproc.c
*
* Call operating system commands from PL/SQL using the External
* Procedure Interface.
*
*--------------------------------------------------------------------
* Setup instructions:
*
* 1. Compile this program: cc -G extproc.c -o extproc.so (on Unix)
* 2. Run $ORACLE_HOME/bin/extproc to ensure it is executable
* 3. Define this TNSNAMES.ORA entry (Use the correct domain):
* EXTPROC_CONNECTION_DATA.WORLD = (DESCRIPTION =
* (ADDRESS=(PROTOCOL=IPC)(KEY=extproc))
* (CONNECT_DATA=(SID=extproc)))
* 4. Define this LISTENER.ORA entry:
* EXTERNAL_PROCEDURE_LISTENER =
* (ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=extproc)))
* SID_LIST_EXTERNAL_PROCEDURE_LISTENER =
* (SID_LIST=(SID_DESC=(SID_NAME=extproc)
* (ORACLE_HOME=/app/oracle/product.8.1.7)(PROGRAM=extproc)))
* 5. Start the new listener: lsnrctl start EXTERNAL_PROCEDURE_LISTENER
* 6. SQL> create library shell_lib as '/app/oracle/local/extproc.so';
* /
* 7. SQL> create or replace function sysrun (syscomm in varchar2)
* return binary_integer
* as language C -- Use "as external" for older Oracle releases
* name "sysrun"
* library shell_lib
* parameters(syscomm string);
* /
* 8. Execute an OS command from PL/SQL:
* PL/SQL> declare
* rc number;
* begin
* rc := sysrun('/bin/ls -l');
* dbms_output.put_line('Return Code='||rc);
* end;
* /
*
*--------------------------------------------------------------------
* Notes:
*
* 1. When running shell-scripts, very few environment variables will be
* defined (as with cron jobs). Remember to set everything
* explicitly. Ie. $PATH, etc.
* 2. Rewrite this program using C Piping if you need to capture command
* output. Look at the popen (pipe open) function.
* 3. In addition to this, you can also try to make the external
* procedure example as provided by Oracle:
* $ cd $ORACLE_HOME/plsql/demo
* $ make -f demo_plsql.mk extproc.so
*
*--------------------------------------------------------------------
*/

int sysrun(char *command)
{
return system(command);
}

Oracle 9i Tablespace & Memory New Features

Temporary Tablespace Management
Specify default temporary tablespace (Good practice. Otherwise, SYSTEM is the default temporary tablespace.)
CREATE DATABASE db1 CONTROLFILE REUSE
LOGFILE ‘log1.log’ SIZE 10M
LOGFILE ‘log2.log’ SIZE 10M
DATAFILE ‘df1.dbf’ AUTOEXTEND ON
DEFAULT TEMPORARY TABLESPACE dts1
TEMPFILE ‘dts_1.f’ SIZE 60M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; (default to locally managed)
ALTER DATABASE db1 DEFAULT TEMPORARY TABLESPACE dts2; (user automatically reassigned)


Automatic Undo Management (AUM) and Rollback Segment Undo (RBU) Mode

Set UNDO_MANAGEMENT = {AUTO | MANUAL} (MANUAL is the default.)
Set UNDO_TABLESPACE: Specify an undo tablespace.
If the UNDO_MANAGEMENT isn’t specified, the instance starts in MANUAL (RBU) mode. The UNDO_TABLESPACE will be ignored. Other parameters: ROLLBACK_SEGMENTS, TRANSACTIONS, TRANSACTIONS_PER_ROLLBACK_SEGMENT, and MAX_ROLLBACK_SEGMENTS

If the instance is created in AUM mode, UNDO TABLESPACE clause can be specified.
CREATE DATABASE UNDO TABLESPACE undotbs01 DATAFILE SIZE 50M;
If UNDO TABLESPACE clause isn’t specified SYS_UNDOTS is created.
Default size: 10MB, AUTOEXTEND ON. Default Filename o1_mf_sys_undo_n_.dbf

CREATE UNDO TABLESPACE undotbs1 DATAFILE ‘undotbs1.dbf’ SIZE 50M;
Permanent, locally managed, read-write, in logging mode with default block size. Can be created with non-default block size. MINIMUM EXTENT and DEFAULT STORAGE are system generated.

ALTER TABLESPACE undotbs_1 ADD DATAFILE ‘undotbs_2.dbf’ AUTOEXTEND ON;
DROP TABLESPACE undotbs_2; (implicit INCLUDING CONTENTS clause)

Only one undo tablespace is used by an instance at one time, except for a PENDING OFFLINE UNDO tablespace.

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs02; (dynamic parameter)

UNDO_RETENTION = 900 (default, in seconds, dynamic parameter)
ALTER SYSTEM SET UNDO_RETENTION = 1200;

V$UNDOSTAT, DBA_UNDO_EXTENTS, V$ROLLSTAT, V$TRANSACTION

UNDO_SUPPRESS_ERRORS = true


Multiple Block Size

Block size can be 2K, 4K, 8K, 16K, or 32K.
DB_BLOCK_SIZE (standard block size, for SYSTEM tablespace, default for user tablespace)
DB_CACHE_SIZE (size of the DEFAULT buffer cache for standard block size, Minimum size = one granule (4MB or 16MB), default to 48M)
DB_2K_CACHE_SIZE, DB_4K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_16K_CACHE_SIZE, DB_32K_CACHE_SIZE (Minimum size for each cache: one granule)
CREATE TABLESPACE tbs_1 DATAFILE ‘tbs_1.dbf’ SIZE 10M BLOCKSIZE 4K;

All temporary tablespace including permanent ones being used as default temporary tablespaces must be of standard block size.

R2 supports locally managed SYSTEM tablespace which is of allocation type AUTOALLOCATE.


PGA Memory Management

PGA memory: tunable memory, untunable memory (remaining memory).
UNTUNABLE_MEMORY_SIZE + TUNABLE_MEMORY_SIZE <= PGA_AGGREGATE_TARGET

PGA_AGGREGATE_TARGET (system level parameter)
WORKAREA_SIZE_POLICY = {AUTO | MANUAL} (session and system level parameter. Manual tuning uses the %_AREA_SIZE parameters)

V$SYSSTAT, V$SESSTAT, V$MYSTAT, V$PROCESS
V$SQL_WORKAREA, V$SQL_WORKAREA_ACTIVE, V$PGASTAT
V$PGA_TARGET_ADVICE, V$PGA_TARGET_ADVICE_HISTOGRAM (enabled by setting PGA_AGGREGATE_TARGET, STATISTICS_LEVEL = ALL)


Dynamic SGA

The dynamic SGA is divided into contiguous memory blocks called granules.
The granule size is based on the static SGA_MAX_SIZE initialization parameter. 4MB if the SGA <= 128MB, 16MB otherwise (8MB for Windows, 16MB for Unix).

V$SGA_DYNAMIC_COMPONENTS, V$SGA_DYNAMIC_FREE_MEMORY
V$SGA_CURRENT_RESIZE_OPS, V$SGA_RESIZE_OPS


Dynamic Shared Pool

ALTER SYSTEM SET SHARED_POOL_SIZE = 64M; (dynamic, size will be rounded up to the next multiple of granule size.)
V$SHARED_POOL_ADVICE, V$LIBRARY_CACHE_MEMORY (enabled by setting STATISTICS_LEVEL)


Dynamic Large Pool

ALTER SYSTEM SET LARGE_POOL_SIZE = 16M; (dynamic, size will be rounded up to the next multiple of granule size.)


Buffer Cache

New parameters define cache sizes for primary block size buffers:
DB_CACHE_SIZE, DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, DB_nK_CACHE_SIZE

ALTER SYSTEM SET DB_CACHE_SIZE = 96M; (dynamic)
ALTER SYSTEM SET DB_8K_CACHE_SIZE = 16M; (dynamic)

DB_CACHE_ADVICE = {OFF | ON | READY} (dynamic instance-level parameter)
V$DB_CACHE_ADVICE