Oracle Enterprise Manager Repository

Install Enterprise Manager Repository

$ emca -config dbcontrol db -repos create

STARTED EMCA at Aug 30, 2008 08:40:26 PMEM Configuration Assistant, Version 10.2.0.3.0 ProductionCopyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:

Database SID: ORCLDB

..

..

..
Other emca commands

Drop Enterprise Manager repository:
emca -deconfig dbcontrol db -repos drop

Create Enterprise Manager repository:
emca -config dbcontrol db -repos create

Recreate Enterprise Manager repository:
emca -config dbcontrol db -repos recreate

NOTE: Configuration files for the Enterprise Manager are found in AGENT_HOME/sysman/config where AGENT_HOME is $ORACLE_HOME/server_name.domain_SID.

Oracle Database Architecture

The general purpose of a database is to store and retrieve related information. An Oracle database has a logical and a physical structure. The physical structure of the database is the set of operating system files in the database. An Oracle database consists of three file types.

• Data files containing the actual data in the database
• Redo logs containing a record of changes made to the database to enable recovery of the data in case of failures
• Control files containing information necessary to maintain and verify database integrity

Memory Structure

Oracle’s memory structure consists of two memory areas known as:

• System Global Area (SGA): Allocated at instance startup, and is a fundamental component of an Oracle Instance
• Program Global Area (PGA): Allocated when the server process is started

System Global Area (SGA)

• SGA is dynamic and sized using SGA_MAX_SIZE.
• SGA memory allocated and tracked in granules by SGA components
– Contiguous virtual memory allocation
– Size based on SGA_MAX_SIZE

Shared Pool

The shared pool is used to store the most recently executed SQL statements and the most recently used data definitions.
• It consists of two key performance-related memory structures:
– Library cache
– Data dictionary cache
• Sized by the parameter SHARED_POOL_SIZE

Library Cache

The library cache stores information about the most recently used SQL and PL/SQL statements. The library cache:
• Enables the sharing of commonly used statements
• Is managed by a least recently used (LRU) algorithm
• Consists of two structures:
– Shared SQL area
– Shared PL/SQL area
• Has its size determined by the shared pool sizing

Data Dictionary Cache

The data dictionary cache is a collection of the most recently used definitions in the database.
• It includes information about database files, tables, indexes, columns, users, privileges, and other database objects.
• During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access.
• Caching the data dictionary information into memory improves response time on queries.
• Size is determined by the shared pool sizing.

Database Buffer Cache

The database buffer cache stores copies of data blocks that have been retrieved from the data files.
• It enables great performance gains when you obtain and update data.
• It is managed through a least recently used (LRU) algorithm.
• DB_BLOCK_SIZE determines the primary block size.

Redo Log Buffer Cache

The redo log buffer cache records all changes made to the database data blocks.
• Its primary purpose is recovery.
• Changes recorded within are called redo entries.
• Redo entries contain information to reconstruct or redo changes.
• Size is defined by LOG_BUFFER.

Large Pool

The large pool is an optional area of memory in the SGA configured only in a shared server environment.
• It relieves the burden placed on the shared pool.
• This configured memory area is used for session memory (UGA), I/O slaves, and backup and restore operations.
• Unlike the shared pool, the large pool does not use an LRU list.
• Sized by LARGE_POOL_SIZE.

Java Pool

The Java pool services the parsing requirements for Java commands.
• Required if installing and using Java.
• It is stored much the same way as PL/SQL in database tables.
• It is sized by the JAVA_POOL_SIZE parameter.

Program Global Area Components

The Program Global Area or Process Global Area (PGA) is a memory region that contains data and control information for a single server process or a single background process. The PGA is allocated when a process is created and deallocated when the process is terminated. In contrast to the SGA, which is shared by several processes, the PGA is an area that is used by only one process. In a dedicated server configuration, the PGA includes these components:
• Sort area: Used for any sorts that may be required to process the SQL statement
• Session information: Includes user privileges and performance statistics for the session
• Cursor state: Indicates the stage in the processing of the SQL statements that are currently used by the session
• Stack space: Contains other session variables

Oracle Database Process

Database Writer (DBWn)

DBWn writes when:
• Checkpoint
• Dirty buffers threshold reached
• No free buffers
• Timeout
• RAC ping request
• Tablespace offline
• Tablespace read only
• Table DROP or TRUNCATE
• Tablespace BEGIN BACKUP

Log Writer (LGWR)

LGWR writes:
• At commit
• When one-third full
• When there is 1 MB of redo
• Every 3 seconds
• Before DBWn writes

System Monitor (SMON)

Responsibilities:
• Instance recovery:
– Rolls forward changes in the redo logs
– Opens the database for user access
– Rolls back uncommitted transactions
• Coalesces free space ever 3 sec
• Deallocates temporary segments

Process Monitor (PMON)

Cleans up after failed processes by:
• Rolling back the transaction
• Releasing locks
• Releasing other resources
• Restarts dead dispatchers

Checkpoint (CKPT)

Responsible for:
• Signalling DBWn at checkpoints
• Updating datafile headers with checkpoint information
• Updating control files with checkpoint information
At every log switch
• When an instance has been shut down with the normal, transactional, or immediate
option
• When forced by setting the initialization parameter FAST_START_MTTR_TARGET.
• When manually requested by the database administrator
• When the ALTER TABLESPACE [OFFLINE NORMALREAD ONLYBEGIN
BACKUP] cause checkpointing on specific data files.

Oracle 9.x to 9.x Upgrade

1) Install Oracle binaries with new patch set

2) Check for invalid objects and run utlrp.sql to recompile

SQL> SELECT OWNER, OBJECT_TYPE, STATUS, COUNT(*)
FROM DBA_OBJECTS WHERE STATUS<>’VALID’
GROUP BY OWNER, OBJECT_TYPE, STATUS;

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

3) Shutdown database and listener

4) Copy the parameter file to new oracle home and make sure
SHARED_POOL_SIZE & JAVA_POOL_SIZE greater than 150mb

5) Change /etc/oratab to new oracle home and relogin to set correct Oracle home

6) Connect as sysdba and run migrate script

connect / as sysdba
startup migrate
spool patch.log
@$ORACLE_HOME/rdbms/admin/catpatch.sql
spool off

7) Review for any errors

8) shutdown and startup

9) Run utlrp.sql to recompile any invalid objects

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

10) Modify the listener.ora file with new ORACLE_HOME and start the listener

11) Start listener

Oracle 9i to 10g Upgrade

1) Install Oracle10g Binaries on the host and upgrade to latest version to 10g.

2) Check for invalid objects and run utlrp.sql to recompile

SQL> SELECT OWNER, OBJECT_TYPE, STATUS, COUNT(*)
FROM DBA_OBJECTS WHERE STATUS<>’VALID’
GROUP BY OWNER, OBJECT_TYPE, STATUS;

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql -- 9i Oracle home

3) Connect to Oracle 9i database and run the below script from new 10g oracle
home

SQL> $ORACLE_HOME/rdbms/admin/utlu102i.sql -- 10g Oracle home

4) Run the below sql, which gives all the user having connect privilege on
oracle9i. Preserve the output

SELECT grantee FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP','LOGSTDBY_ADMINISTRATOR', 'ORDSYS','ORDPLUGINS',
'OEM_MONITOR', 'WKSYS', 'WKPROXY','WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS','WMSYS',
'OLAPDBA', 'OLAPSVR', 'OLAP_USER','OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA','SI_INFORMTN_SCHEMA',
'XDB', 'ODM');

5) Connect to 9i database and create pfile from spfile

SQL> create pfile from spfile;

6) Take backup of database

7) Edit pfile to add below listed 10g parameters and remove/comments the
deprecated parameters

AQ_TM_PROCESSES=0 -- If specified
Set streams_pool_size = 50MB
Set session_max_open_files = 20
Set max_enabled_roles = 50
db_cache_size = 200M (at the least)
. SHARED_POOL_SIZE=150M (at the least)
JAVA_POOL_SIZE=150M (at the least)
LARGE_POOL_SIZE=150M (at the least)

8) Shutdown database and create spfile from pfile

SQL> create spfile from pfile;

9) Add the entry to /etc/oratab file and recompile/relogin to set the oracle 10g
home

ORCLDB:/u01/app/oracle/product/10.2.0/db_1:N

10) Copy spfile to Oracle 10g home dbs directory

$ cp /oracle/9i/9.2.0.6/dbs/spfileorcldb.ora $ORACLE_HOME/dbs

11) Start upgrade database and make sure it is the right database

$sqlplus sys as sysdba
SQL> startup upgrade
SQL> SELECT NAME FROM V$DATABASE;

12) Add SYSAUX tablespace and ensure adequate space in SYSTEM tablespace (set to
2GB and autoextend on)

SQL> CREATE TABLESPACE sysaux
2 DATAFILE '/oracle/oradata/ORCLDB/sysaux01.dbf'
3 SIZE 300M REUSE AUTOEXTEND ON
4 EXTENT MANAGEMENT LOCAL
5 SEGMENT SPACE MANAGEMENT AUTO
6 ONLINE;

SQL> ALTER TABLESPACE SYSTEM AUTOEXTEND ON;

13) Run catupgrd.sql script as sysdba

SQL> spool /home/oracle/upgrade_orcldb.log
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

14) Shutdown and startup database

SQL> shutdown immediate
SQL> startup

15) Run utlrp.sql to recompile any invalid objects

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> SELECT OWNER, OBJECT_TYPE, STATUS, COUNT(*)
FROM DBA_OBJECTS WHERE STATUS<>’VALID’
GROUP BY OWNER, OBJECT_TYPE, STATUS;

16) Change AQ_TM_PROCESSES,COMPATIBLE parameters and ensure log_archive_format
has %T,%S,%R

SQL> alter system set AQ_TM_PROCESSES=2 scope=spfile;
SQL> alter system set COMPATIBLE=10.2.0.3 scope=spfile;

17) Shutdown and startup to enable parameters

SQL> shutdown immediate
SQL> startup

18) Modify the listener.ora file with new ORACLE_HOME and start the listener

19) Recreate password file

$ orapwd file=orapwORCLDB password=password entries=5

20) Check remote connectivity

$ sqlplus sys@ORCLDB as sysdba

21) Grant the below privileges to all the users which you have collected at step
4

GRANT CREATE VIEW TO <USERNAME>;
GRANT CREATE TABLE TO <USERNAME>;
GRANT ALTER SESSION TO <USERNAME>;
GRANT CREATE CLUSTER TO <USERNAME>;
GRANT CREATE SESSION TO <USERNAME>;
GRANT CREATE SYNONYM TO <USERNAME>;
GRANT CREATE SEQUENCE TO <USERNAME>;
GRANT CREATE DATABASE LINK TO <USERNAME>;

Appendix A: Initialization Parameters Obsolete in 10g

ENQUEUE_RESOURCES
DBLINK_ENCRYPT_LOGIN
HASH_JOIN_ENABLED
LOG_PARALLELISM
MAX_ROLLBACK_SEGMENTS
MTS_CIRCUITS
MTS_DISPATCHERS
MTS_LISTENER_ADDRESS
MTS_MAX_DISPATCHERS
MTS_MAX_SERVERS
MTS_MULTIPLE_LISTENERS
MTS_SERVERS
MTS_SERVICE
MTS_SESSIONS
OPTIMIZER_MAX_PERMUTATIONS
ORACLE_TRACE_COLLECTION_NAME
ORACLE_TRACE_COLLECTION_PATH
ORACLE_TRACE_COLLECTION_SIZE
ORACLE_TRACE_ENABLE
ORACLE_TRACE_FACILITY_NAME
ORACLE_TRACE_FACILITY_PATH
PARTITION_VIEW_ENABLED
PLSQL_NATIVE_C_COMPILER
PLSQL_NATIVE_LINKER
PLSQL_NATIVE_MAKE_FILE_NAME
PLSQL_NATIVE_MAKE_UTILITY
ROW_LOCKING
SERIALIZABLE
TRANSACTION_AUDITING
UNDO_SUPPRESS_ERRORS

Appendix B: Initialization Parameters Deprecated in 10g

LOGMNR_MAX_PERSISTENT_SESSIONS
MAX_COMMIT_PROPAGATION_DELAY
REMOTE_ARCHIVE_ENABLE
SERIAL_REUSE
SQL_TRACE
BUFFER_POOL_KEEP (replaced by DB_KEEP_CACHE_SIZE) BUFFER_POOL_RECYCLE
(replaced by DB_RECYCLE_CACHE_SIZE) GLOBAL_CONTEXT_POOL_SIZE
LOCK_NAME_SPACE LOG_ARCHIVE_START MAX_ENABLED_ROLES
PARALLEL_AUTOMATIC_TUNING PLSQL_COMPILER_FLAGS (replaced by
PLSQL_CODE_TYPE and PLSQL_DEBUG)

KNOWN ISSUES

While upgrade following error was encountered.
create or replace
*
ERROR at line 1:
ORA-06553: PLS-213: package STANDARD not accessible.
ORA-00955: name is already used by an existing object

Please make sure to set the following init parameters as below in the
spfile/init file or comment them out to their default values, at the
time of upgrading the database.

PLSQL_V2_COMPATIBILITY = FALSE
PLSQL_NATIVE_LIBRARY_DIR = ""
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT = 0

Steps to create new Linux partition

[root@centos u01]# fdisk -l

Disk /dev/hdb: 40.0 GB, 40020664320 bytes
255 heads, 63 sectors/track, 4865 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/hdb1 * 1 25 200781 83 Linux
/dev/hdb2 26 2256 17920507+ 83 Linux
/dev/hdb3 2257 2868 4915890 83 Linux
/dev/hdb4 2869 4865 16040902+ 5 Extended
/dev/hdb5 2869 3442 4610623+ 83 Linux
/dev/hdb6 3443 3990 4401778+ 83 Linux
/dev/hdb7 3991 4353 2915766 82 Linux swap / Solaris
/dev/hdb8 4354 4608 2048256 83 Linux
/dev/hdb9 4609 4761 1228941 83 Linux
/dev/hdb10 4762 4863 819283+ 83 Linux

Disk /dev/sda: 160.0 GB, 160041885696 bytes
255 heads, 63 sectors/track, 19457 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 3824 30716248+ c W95 FAT32 (LBA)
/dev/sda2 3825 7648 30716280 c W95 FAT32 (LBA)
/dev/sda3 7649 16317 69633742+ f W95 Ext'd (LBA)
/dev/sda5 7649 10453 22531131 b W95 FAT32
/dev/sda6 10454 13640 25599546 b W95 FAT32
/dev/sda7 13641 16317 21502971 b W95 FAT32
[root@centos u01]# fdisk /dev/sda

The number of cylinders for this disk is set to 19457.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)

Command (m for help): n
Command action
l logical (5 or over)
p primary partition (1-4)
p
Selected partition 4
First cylinder (16318-19457, default 16318):
Using default value 16318
Last cylinder or +size or +sizeM or +sizeK (16318-19457, default 19457):
Using default value 19457

Command (m for help): p

Disk /dev/sda: 160.0 GB, 160041885696 bytes
255 heads, 63 sectors/track, 19457 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 3824 30716248+ c W95 FAT32 (LBA)
/dev/sda2 3825 7648 30716280 c W95 FAT32 (LBA)
/dev/sda3 7649 16317 69633742+ f W95 Ext'd (LBA)
/dev/sda4 16318 19457 25222050 83 Linux
/dev/sda5 7649 10453 22531131 b W95 FAT32
/dev/sda6 10454 13640 25599546 b W95 FAT32
/dev/sda7 13641 16317 21502971 b W95 FAT32

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

[root@centos u01]# partprobe
Warning: Unable to open /dev/hdc read-write (Read-only file system). /dev/hdc has been opened read-only.

[root@centos u01]# mkfs -t ext3 /dev/sda4
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
3155936 inodes, 6305512 blocks
315275 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=0
193 block groups
32768 blocks per group, 32768 fragments per group
16352 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000

Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 27 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.

[root@centos u01]# e2label /dev/sda4 /u01

[root@centos u01]# cat /etc/fstab
LABEL=/u01 /u01 ext3 defaults 1 2

[root@centos ~]# mount /dev/sda4 /u01

10046/10053 trace with tkprof

(1) Create a 10046/10053 trace file using the following steps:

$ sqlplus /nolog
connect username/password
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever, level 12';
alter session set events '10053 trace name context forever, level 1';
-- If the query has binds define them using the correct data types and values
--variable b1 varchar2(10);
--variable b2 number;
--begin
--:b1 := 'bind value 1';
--:b2 := 42;
--end;

-- Run the query with a distinct comment /* comment_ */ to force a hard parse

select /* mycomm_2k08 */ empno,ename from emp where empno=10001;

select 'close the cursor' from dual;
exec dbms_session.reset_package;
alter session set events '10046 trace name context off';
alter session set events '10053 trace name context off';
quit

(2) Recreate the plan table to ensure it is the correct format for this version of the database:

$ sqlplus /nolog
connect username/password
drop table plan_table;
@?/rdbms/admin/utlxplan
quit

(3) Find the raw 10046/10053 trace file in the user dump destination and run tkprof against it:

$ tkprof .trc .tkprof sort=exeela waits=yes explain=username/password