ORACLE Memory Management !

The Memory Stucture
There are five memory stuctures that make up the System Global Area (SGA). The SGA will store many internal data structures that all processes need access to, cache data from disk, cache redo data before writing to disk, hold parsed SQL plans and so on.
SGA
Oracle Architecture
Shared Pool

The shared pool consists of the following areas:
Library cache includes the shared SQL area, private SQL areas, PL/SQL procedures and
packages the control structures such as locks and library cache handles

Dictionary cache is a collection of database tables and views containing information about the
database, its structures and users.

Buffers for parallel execution messages and control structures
Use the parameter SHARED_POOL_SIZE in the init.ora file to adjust

Buffer cache
This area holds copies of read data blocks from the datafiles. The buffers in the cache contain
two lists, the write list and the least used list (LRU). The write list holds dirty buffers which
contain modified data not yet written to disk. The least used list holds free buffers (no useful
data) , pinned buffers (being accessed) and dirty buffers that have not yet been moved to the
write list. This pool is broken down into three pools, recycle (goal here is to age out a block as
soon as it is no longer needed), keep (goal is to keep warm/hot block in the pool for as long as
possible) and default buffer pool.
The size is deteremnied by the following in the system paramenter file:
DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE

Use the parameter DB_BLOCK_BUFFERS in the init.ora file to adjust

Redo buffer
The redo buffer is where data that needs to be written to the online redo logs will be cached
temporarily before it is written to disk. However the contents of this area are flushed:
· Every three seconds
· Whenever someone commits
· When its gets one third full or contains 1MB of cached redo log data.
These entries contain necessary information to reconstruct/redo changes by the INSERT,
UPDATE, DELETE, CREATE, ALTER and DROP commands.

Use the parameter LOG_BUFFER in the init.ora file to adjust
Large Pool This is an optional memory area that provide large areas of memory for:
· MTS - to allocate the UGA region in the SGA
· Parallel execution of statements - to allow for the allocation of inter-processing message
buffers, used to coordinate the parallel query servers.
· Backup - for RMAN disk I/O buffers
The large pool is basically a non-cached version of the shared pool.
Oracle Architecture
Parallel execution message buffers.
Use the parameter LARGE_POOL_SIZE in the init.ora file to adjust
Java Pool
used to execute java code within the database.
Use the parameter JAVA_POOL_SIZE in the init.ora file to adjust
Display information regarding the SGA
Memory area Memory
location Displaying the information
SGA sql> show sga;
SGA (detailed) use table v$sgastat;
Buffer cache SGA use table v$bh;
Display memory allocation SGA compute sum of bytes on pool
break on pool skip 1
select pool, name bytes from v$sgastat order by pool, name;

Display the redo buffer SGA select * from v$sga where name = 'Redo Buffers';

Library cache shared pool use table v$librarycache;
Dictionary cache shared pool use table v$rowcache;

PGA and UGA
If you have MTS configured then the UGA must be stored in a memory stucture that everyone has access to and this would be the SGA. However if you are using a dedicated server connection then the UGA becomes part of the PGA

Each process connected to the database requires its own area of memeory this is know as the Program Global Area (PGA). This area stores variables, arrays and other information that do not need to be shared with other processes.

Oracle Architecture Session Information:
PGA in an instance running without the multi-threaded server
(named Shared Server in Oracle9i) requires additional memory for
the user's session, such as private SQL areas and other information.
If the instance is running the multi-threaded server, this extra
memory is not in the PGA, but is instead allocated in the SGA (the Shared Pool).

Stack space
The memory allocated to hold a sessions variables, arrays, etc and
other information relating to the session. However for a shared
server the session memory is shared and not private Display information regarding the PGA
Session information v$sesstat, v$statname

Display PGA and UGA usage
select a.name, b.name from v$statname a, v$mystat b where a.statistic# = b.statistic#
and a.name like '%ga %';

Tunning the PGA/UGA
alter the following parameters
SORT_AREA_SIZE (PGA)
SORT_AREA_RETAINED_SIZE (UGA)
Shrink the PGA area > exec dbms_session.free_unused_user_memory;

How To Create dbconsole ?

Problem :-
I am using three database with single $ORACLE_HOME i.e single Oracle Binary. When I created three database one after another, the dbconsole services was overwritten by one another, and at last none is available and functioning.

Solution :-
Steps to recreate dbconsole -:
1. export ORACLE_HOME=
2. export ORACLE_SID=
3. SQL> drop user sysman cascade;
4. SQL> drop role MGMT_USER;
5. SQL> drop user MGMT_VIEW cascade;
6. SQL> drop public synonym MGMT_TARGET_BLACKOUTS;
7. SQL> drop public synonym SETEMVIEWUSERCONTEXT;


Remove the following directories from your filesystem if exits:


Now run the following command to configure the dbconsole

1. export ORACLE_HOME=
2. export ORACLE_SID=
3. cd $ORACLE_HOME/bin./emca -config dbcontrol db -repos create

After this, it will ask for the following information and give you result -:

STARTED EMCA at Jan 21, 2009 2:35:59 PMEM Configuration Assistant, Version 10.2.0.1.0 ProductionCopyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: unixcm
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):

-------------------------------------------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /orahome/oracle/product/10.2.0
Database hostname ................
Listener port number ................
Database SID ................
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Jan 21, 2009 2:36:33 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /orahome/oracle/product/10.2.0/cfgtoollogs/emca/unixcm/emca_2009-01-21_02-35-59-PM.log.
Jan 21, 2009 2:36:35 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Jan 21, 2009 2:38:08 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Jan 21, 2009 2:38:12 PM oracle.sysman.emcp.util.DBControlUtil startOMSINFO:
Starting Database Control (this may take a while) ...
Jan 21, 2009 2:39:55 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfullyJan 21, 2009 2:39:55 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: The Database Control URL is : http://www.blogger.com/5500/em
Follow the same steps above to create another dbconsole, but don't forget to export $ORACLE_HOME
$ORACLE_SID

To check status , stop and start dbconsole of a respective database, first always do this
export $ORACLE_HOME
export $ORACLE_SID
-------------------------------------------------------------------------------------------------

How to create password file when it is not present ?

Oracle Password File ($ORACLE_HOME/dbs/orapw or orapwSID) stores The passwords for users with administrative privileges. One needs to create a password files before remote administrators (like OEM) will be allowed to connect.

Follow this procedure to create a new password file:

  Log in as the Oracle software owner

  Run command: orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypasswd

  Shutdown the database (SQLPLUS> SHUTDOWN IMMEDIATE)

  Edit the INIT.ORA file and ensure REMOTE_LOGIN_PASSWORDFILE=exclusive is set.

  Startup the database (SQLPLUS> STARTUP)[/list]

NOTE: The orapwd utility presents a security risk in that it receives a password from the command line. This password is visible in the process table of many systems. Administrators needs to be aware of this!

Adding users to Password File:

One can select from the SYS.V_$PWFILE_USERS view to see which users are listed in the password file. New users can be added to the password file by granting them SYSDBA or SYSOPER privileges, or by using the orapwd utility

 

Database Cloning through RMAN

RMAN :: Clone Database to UAT - FTP to a different location

RMAN: Database Clone/Refresh from Production Server to a UAT/Development Server.

1. Production Server
oracledb1-> tail -3 /etc/oratab
dgtest:/u01/app/oracle/product/10.2.0/db_1:Y
dataware:/u01/app/oracle/product/10.2.0/db_1:Y
oracledb1->

2. UAT Server (Target Server to clone the database)
oracledb2-> tail -3 /etc/oratab
# Multiple entries with the same $ORACLE_SID are not allowed.
oracledb2->
You can see the Server is clean.
We are now going to restore & recover a Database with SID “Dataware” to on the UAT server.

3. Make sure that you have exact file system and mount points available on the Target server.
I am going to create the directory structure for BACKGROUND Dump locations and for Database file System exactly as in the Production server. In fact in most case’s the environment will be same.

oracledb1-> pwd
/u01/app/oracle/admin/dataware
oracledb1-> ls -ltr
total 88
drwxr-x--- 2 oracle oinstall 4096 Jul 21 13:39 dpdump
drwxr-x--- 2 oracle oinstall 4096 Jul 21 13:39 cdump
drwxr-x--- 2 oracle oinstall 4096 Jul 21 13:44 pfile
drwxr-x--- 2 oracle oinstall 4096 Nov 24 12:32 udump
drwxr-x--- 2 oracle oinstall 36864 Nov 24 12:33 bdump
drwxr-x--- 2 oracle oinstall 12288 Nov 24 12:34 adump
oracledb1->
********************************************************
oracledb2-> mkdir dataware
oracledb2-> cd dataware
oracledb2-> mkdir dpdump cdump pfile udump bdump adump
oracledb2-> ls -ltr
total 48
drwxr-xr-x 2 oracle oinstall 4096 Dec 5 13:04 udump
drwxr-xr-x 2 oracle oinstall 4096 Dec 5 13:04 pfile
drwxr-xr-x 2 oracle oinstall 4096 Dec 5 13:04 dpdump
drwxr-xr-x 2 oracle oinstall 4096 Dec 5 13:04 cdump
drwxr-xr-x 2 oracle oinstall 4096 Dec 5 13:04 bdump
drwxr-xr-x 2 oracle oinstall 4096 Dec 5 13:04 adump
oracledb2-> pwd
/u01/app/oracle/admin/dataware
**********************************************************
oracledb2-> pwd
/oradata/dataware
oracledb2-> ls -ltr
total 8
drwxr-xr-x 2 oracle oinstall 4096 Dec 5 13:09 arch
oracledb2->
*** If the database as a Flash Recovery area enable make sure that you care the directory for that as well.
*********************************************

4. On the Production Database create pfile from spfile, where we are going to have a copy of that.
SQL> !hostname
oracledb1.mycorpdomain.com
SQL> create pfile='/oradata/backup/initdataware.ora' from spfile;
File created.
SQL> !ls -ltr /oradata/backup/
total 8
-rw-r--r-- 1 oracle oinstall 1143 Dec 5 13:03 initdataware.ora
***********
Connect to the Production database with RMAN,
oracledb1-> rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Fri Dec 5 13:05:47 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DATAWARE (DBID=197345076)
RMAN>
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/backup/dataware/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oradata/backup/dataware/%U';
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oradata/backup/dataware/snapcf_dataware.f';

RMAN> backup database plus archivelog;
Starting backup at 05-DEC-07
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=56 recid=54 stamp=670070919
piece handle=/oradata/backup/dataware/0sk1ga0l_1_1 tag=TAG20071205T131604 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
Finished backup at 05-DEC-07
Starting backup at 05-DEC-07
current log archived
using channel ORA_DISK_1
piece handle=/oradata/backup/dataware/0tk1ga3l_1_1 tag=TAG20071205T131741 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Starting Control File and SPFILE Autobackup at 05-DEC-07
piece handle=/oradata/backup/dataware/c-197345076-20071205-00 comment=NONE
Finished Control File and SPFILE Autobackup at 05-DEC-07
5. We have a backup of the Production Database; we are now going to FTP this to the UAT / Development Site. In fact when we do the refresh or clone, we use Tapes to restore the previous Images of the backup’s. As this is just a Test scenario I am not worried about the additional load on the Network and of course taking backup at odd times.

oracledb1-> sftp 192.168.2.135
Connecting to 192.168.2.135...
oracle@192.168.2.135's password:
Permission denied, please try again.
oracle@192.168.2.135's password:
sftp> pwd
Remote working directory: /oradata/rman/dataware
6. Login to the UAT server and copy the init parameter file to ORACLE_HOME/dbs location.
oracledb2-> cp -p initdataware.ora $ORACLE_HOME/dbs/

7. Startup the Database in nomount Stage to perform the Control file Restore

oracledb2-> export ORACLE_SID=dataware
oracledb2-> rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Fri Dec 5 14:02:14 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)

RMAN> startup nomount;

Oracle instance started
Total System Global Area 184549376 bytes
Fixed Size 1260792 bytes
Variable Size 159384328 bytes
Database Buffers 20971520 bytes
Redo Buffers 2932736 bytes

RMAN> restore controlfile from
2> '/oradata/rman/dataware/snapcf_dataware.f';
Starting restore at 05-DEC-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=/oradata/dataware/control01.ctl
Finished restore at 05-DEC-07

8. Mount the Database and restore all the archive log files to start the Recovery of the Database at UAT.
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
*** Recovering all the archivelog files....
RMAN> restore archivelog all;
Starting restore at 05-DEC-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/05/2007 14:06:30
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of log thread 1 seq 36 lowscn 866178 found to restore
WHY THE ABOVE ERROR? # This is what the problem I faced...
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/backup/dataware/%F'; ## See its poiting to a different mount point
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oradata/backup/dataware/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_dataware.f'; # default
*** So i changed the mount points on the UAT Server...will this work?
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/rman/dataware/%F';
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/backup/dataware/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/rman/dataware/%F';
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oradata/rman/dataware/%U';
old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oradata/backup/dataware/%U';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oradata/rman/dataware/%U';
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1
*********
RMAN> restore archivelog all from
2> '/oradata/rman/dataware/0sk1ga0l_1_1';
Starting restore at 05-DEC-07
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/05/2007 14:20:40
RMAN-06509: only SPFILE or control file can be restored from autobackup
No...
Why it’s happening like this?
and...

RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27 Full 782.38M DISK 00:01:26 05-DEC-07
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20071205T131604
Piece Name: /oradata/backup/dataware/0sk1ga0l_1_1
List of Datafiles in backup set 27
File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----
1 Full 1486233 05-DEC-07 /oradata/dataware/system01.dbf
3 Full 1486233 05-DEC-07 /oradata/dataware/sysaux01.dbf
8 Full 1486233 05-DEC-07 /oradata/dataware/students.dbf
and what i found is the control file which we restored before as cataloged with different backup tag and sets with different backup locations.
So we need to catalog the newly moved (FTP Location) files.

RMAN> catalog backuppiece '/oradata/rman/dataware/0rk1g9vo_1_1';
cataloged backuppiece
backup piece handle=/oradata/rman/dataware/0rk1g9vo_1_1 recid=27 stamp=672675911

RMAN> catalog backuppiece '/oradata/rman/dataware/0sk1ga0l_1_1';
cataloged backuppiece
backup piece handle=/oradata/rman/dataware/0sk1ga0l_1_1 recid=28 stamp=672675927

RMAN> catalog backuppiece '/oradata/rman/dataware/0tk1ga3l_1_1';
cataloged backuppiece
backup piece handle=/oradata/rman/dataware/0tk1ga3l_1_1 recid=29 stamp=672675936

RMAN> catalog backuppiece '/oradata/rman/dataware/c-197345076-20071205-00';
cataloged backuppiece
backup piece handle=/oradata/rman/dataware/c-197345076-20071205-00 recid=30 stamp=672675947
ALL SET # TO CHECK

RMAN> list backuppiece '/oradata/rman/dataware/0rk1g9vo_1_1';
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
27 26 1 2 AVAILABLE DISK /oradata/rman/dataware/0rk1g9vo_1_1

RMAN> run {
2> set until sequence 88 thread 1;
3> restore database;
4> recover database;
5> sql 'alter database open resetlogs';
6> }
executing command: SET until clause
Starting restore at 05-DEC-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata/dataware/system01.dbf
restoring datafile 00007 to /oradata/dataware/tsa01.dbf
restoring datafile 00007 to /oradata/dataware/students.dbf
channel ORA_DISK_1: reading from backup piece /oradata/rman/dataware/10k1gffk_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradata/rman/dataware/10k1gffk_1_1 tag=TAG20071205T144924
channel ORA_DISK_1: restore complete, elapsed time: 00:01:37
Finished restore at 05-DEC-07
Starting recover at 05-DEC-07
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=86
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=87
channel ORA_DISK_1: reading from backup piece /oradata/rman/dataware/13k1gfkd_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradata/rman/dataware/13k1gfkd_1_1 tag=TAG20071205T145156
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archive log filename=/oradata/dataware/arch/1_86_660663669.arc thread=1 sequence=86
archive log filename=/oradata/dataware/arch/1_87_660663669.arc thread=1 sequence=87
media recovery complete, elapsed time: 00:00:02
Finished recover at 05-DEC-07
sql statement: alter database open resetlog
Any options away from this please let me know...which is more easy than this.

Know about orainstRoot and root.sh

Why do you run orainstRoot and ROOT.SH once you finalize the Installation?
orainstRoot.sh needs to be run to change the Permissions and groupname to 770 and to dba.
Root.sh (ORACLE_HOME) location needs to be run to create a ORATAB in /etc/oratab or /opt/var/oratab in Solaris and to copy dbhome, oraenv and coraenv to /usr/local/bin.
orainstRoot.sh
[root@oracle11g ~]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory to 770.
Changing groupname of /u01/app/oraInventory to dba.
The execution of the script is complete
root.sh
[root@oracle11g ~]# /u01/app/oracle/product/11.1.0/db_1/root.sh
Running Oracle 11g root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.1.0/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...
Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

Oracle's Statspack Report Generation

Oracle STATSPACK installation steps
Step 1: Create the perfstat Tablespace

The STATSPACK utility requires an isolated tablespace to contain all of the objects and data. For uniformity, it is suggested that the tablespace be called perfstat, the same name as the schema owner for the STATSPACK tables. Note that I have deliberately not used the AUTOEXTEND option. It is important for the Oracle DBA to closely watch the STATSPACK data to ensure that the stats$sql_summary table is not taking an inordinate amount of space. We will talk about adjusting the STATSPACK thresholds later in this chapter.

Next, we create a tablespace called perfstat with at least 180 megabytes of space in the datafile:

>sqlplus /

SQL*Plus: Release 8.1.6.0.0 - Production on Tue Dec 12 14:08:11 2000

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


Connected to:
Oracle8i Enterprise Edition Release 8.1.6.1.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.6.1.0 - 64bit Production

SQL> create tablespace perfstat
2 datafile '/u03/oradata/prodb1/perfstat.dbf'
size 500m;

Step 2: Run the create Scripts

Now that the tablespace exists, we can begin the installation process of the STATSPACK software.

Because of the version differences, we will break this section into one for pre-8.1.7 installation and another for post-8.1.7 installs.

Run the pre-8.1.7 install scripts

The statscre.sql script creates a user called PERFSTAT, executes the script to create all of the STATSPACK tables, and installs the STATSPACK PL/SQL package. When you run this script, you will be prompted for the following information:

 Specify PERFSTAT user's default tablespace: perfstat

 Specify PERFSTAT user's temporary tablespace: temp

 Enter tablespace where STATSPACK objects will be created: perfstat

Install Prerequisites

Note that you must have performed the following before attempting to install STATSPACK:

1. Run catdbsyn.sql when connected as SYS.

2. Run dbmspool.sql when connected as SYS.

3. Allocate a tablespace called perfstat with at least 180 megabytes of storage.

NOTE: The STATSPACK scripts are designed to stop whenever an error is encountered. The statsctab.sql script contains the SQL*Plus directive whenever sqlerror exit;. This means that the script will cease execution if any error is encountered. If you encounter an error and you need to restart the script, just comment out the whenever sqlerror exit line and run the script again. Also, note that the STATSPACK install script contains SQL*Plus commands. Hence, be sure you run it from SQL*Plus and do not try to run it in SVRMGRL or SQL*Worksheet.

Once you have completed running the spcreate.sql script, you will need to ensure that you do not have errors. The STATSPACK utility creates a series of files with the .lis extension as shown here:

prodb2-/u01/app/oracle/product/8.1.6_64/rdbms/admin

>ls -al *.lis
-rw-r--r-- 1 oracle oinstall 4170 Dec 12 14:28 spctab.lis
-rw-r--r-- 1 oracle oinstall 3417 Dec 12 14:27 spcusr.lis
-rw-r--r-- 1 oracle oinstall 201 Dec 12 14:28 spcpkg.lis

To check for errors, you need to look for any lines that contain “ORA-” or the word “error”, since the presence of these strings indicates an error. If you are using Windows NT, you can check for errors by searching the output file in MS Word. However, most Oracle administrators on NT get a freeware grep for DOS, which is readily available on the Internet.

The code here shows the UNIX grep commands that are used to check for creation errors.

mysid-/u01/app/oracle/product/9.0.2/rdbms/admin> grep ORA- *.lis

mysid-/u01/app/oracle/product/9.0.2/rdbms/admin> grep -i error *.lis

spctab.lis:SPCTAB complete. Please check spctab.lis for any errors.
spcusr.lis:STATSCUSR complete. Please check spcusr.lis for any errors.
spcpkg.lis:No errors.

Now that we have installed the user, tables, indexes, and the package, we are ready to start collecting STATSPACK data. We will begin by testing the STATSPACK functionality and then schedule a regular STATSPACK collection job.

Step 3: Test the STATSPACK Install

To ensure that everything is installed correctly, we can demand two snapshots and then request an elapsed-time report. To execute a STATSPACK snapshot, we enter the statspack.snap procedure. If we do this twice, we will have two snapshots, and we can run the statsrep.sql report to ensure that everything is working properly. Here is the test to ensure that the install works properly. If you get a meaningful report after entering statsrep, then the install was successful. Also, note that the statsrep.sql script has an EXIT statement, so it will return you to the UNIX prompt when it has completed:

SQL> execute statspack.snap
PL/SQL procedure successfully completed.
SQL> execute statspack.snap
PL/SQL procedure successfully completed.
SQL> @spreport
. . .

Step 4: Schedule Automatic STATSPACK Data Collections

Now that we have verified that STATSPACK is installed and working, we can schedule automatic data collection. By using the statsauto.sql script we can automatically schedule an hourly data collection for STATSPACK. The statsauto.sql script contains the following directive:

SQL> execute dbms_job.submit(:jobno, 'statspack.snap;',
trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

The important thing to note in this call to dbms_job.submit is the execution interval. The SYSDATE+1/24 is the interval that is stored in the dba_jobs view to produce hourly snapshots. You can change this as follows for different sample times. There are 1,440 minutes in a day, and you can use this figure to adjust the execution times.

Table 1 gives you the divisors for the snapshot intervals.

Minutes per Day	Minutes between Snapshots	Required Divisor
1,440	60	24
1,440	30	48
1,440	10	144
1,440	5	288

Table 1: Determining the Snapshot Interval

Hence, if we want a snapshot every ten minutes we would issue the following command:

SQL> execute dbms_job.submit(:jobno, 'statspack.snap;',
trunc(sysdate+1/144,'MI'), 'trunc(SYSDATE+1/144,''MI'')', TRUE, :instno);

In the real world, you may have times where you want to sample the database over short time intervals. For example, if you have noticed that a performance problem happens every day between 4:00 p.m. and 5:00 p.m., you can request more frequent snapshots during this period.

For normal use, you probably want to accept the hourly default and execute a snapshot every hour. Below is the standard output from running the statsauto.sql script:

SQL> connect perfstat/perfstat;
Connected.
SQL> @statsauto
PL/SQL procedure successfully completed.

Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

JOBNO
----------
1


Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME TYPE VALUE
------------------------------------ ------- -----------------------------
job_queue_processes integer 1

Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

JOB NEXT_DATE NEXT_SEC
---------- --------- --------
1 12-MAY-02 16:00:00

We can now see that a STATSPACK snapshot will automatically be executed every hour. We see that this is scheduled as job number 1, and we can use this job number to cancel this collection at any time using the dbms_job.remove procedure:

SQL> execute dbms_job.remove(1);

PL/SQL procedure successfully completed.

Installation Files Delivered with STATSPACK

Files Delivered with STATSPACK

In Oracle10g, STATSPACK utility consists of 20 SQL scripts located in the $ORACLE_HOME/rdbms/admin directory where the important database scripts are usually located. The following files form STATSPACK as distributed in Oracle10g:

 The spcreate.sql script is a main script for STATSPACK utility installation and should be run by user sys. This script calls some of the other creation scripts described below.

 The spcusr.sql script creates the perfstat schema. perfstat is the owner of all STATSPACK database objects that form the STATSPACK repository. This script also grants all the necessary authorities to user perfstat.

 The spctab.sql script creates all the STATSPACK objects under schema perfstat.

 The spcpkg.sql script creates a special statistics package that is necessary for snapshot and report generation.

 The spdrop.sql script uninstalls the STATSPACK utility from the database. This script calls the scripts: spdtab.sql and spdusr.sql

 The spauto.sql script is used to schedule the STATSPACK procedure called statspack.snap that gathers STATSPACK snapshots. In the AWR, this job performs a new background process called the Manageability Monitor (MMON).

 The sppurge.sql script purges old STATSPACK data from the repository. The snapshot range for the data to be cleared must be specified by the user.

 The sprepcon.sql is a new script in STATSPACK that is used to specify selected parameters that are related to report generation invoked by spreport.sql XE "spreport.sql" script.

 The spreport.sql and sprepins.sql are the scripts used for report generation. The spreport.sql script must be called to produce the STATSPACK report for a specified snapshot range.

 The sprepsql.sql and sprsqins.sql XE "sprsqins.sql" scripts are used to generate the STATSPACK report for SQL statements, statistics and plan usage.

 The sptrunc.sql script can be used to clear all the STATSPACK tables, thereby reclaiming space for the database.

 The spuexp.par is the name of the export parameter file which is used to export the whole STATSPACK user.

 The sp*.sql scripts are the upgrade scripts used to convert existing STATSPACK repository information to the latest Oracle10g version.

There are only two AWR installation scripts, which are also located in the $ORACLE_HOME/rdbms/admin directory:

 catawr.sql script creates data dictionary catalog objects for the AWR.

 dbmsawr.sql script creates the dbms_workload_repository package for database administrators.

By design, the AWR is created at the same time the database is created and is included in the data dictionary. This shows that the AWR is in the kernel part of the database that cannot function properly without it, while STATSPACK is a stand-alone utility that can be installed or removed from the database at any time. By default, the STATSPACK utility is not installed in the Oracle database. STATSPACK must be manually loaded into the database to start monitoring performance and gathering statistic history.

In Oracle10g, STATSPACK is shipped without the spdoc.txt file, which was a guide for working with STATSPACK that was included in previous versions. This is another mechanism by which Oracle Corporation urges the use of the AWR for performance tuning purposes rather than STATSPACK.

A look at the commonalities and differences between STATSPACK and AWR repository structures will be helpful at this point.

Overview of STATSPACK Scripts
Overview of the STATSPACK Scripts

The STATSPACK scripts have completely changed. All of the STATSPACK scripts are located in the $ORACLE_HOME/rdbms/admin directory.

Oracle 8.1.7 and Oracle9i Script Name	Pre Oracle 8.1.7 Script Name	Script Function
spdoc.txt	statspack.doc	Installation documentation
spcreate.sql	statscre.sql	Create user, tables & install packages
spreport.sql	statsrep.sql	Standard STATSPACK report
spauto.sql	statsauto.sql	Schedule automatic data collection
spuexp.par	statsuexp.par	Parameter file for full STATSPACK export
sppurge.sql	- new file -	Purge SQL for removing old snapshots
sptrunc.sql	- new file -	Script to truncate all STATSPACK tables
spup816.sql	- new file -	Upgrade script to moving to 8.1.6
spup817.sql	- new file -	Upgrade script to moving to 8.1.7
spdrop.sql	statsdrp.sql	Script to drop all STATSPACK tables
spcpkg.sql	statspack.sql	Script to create statspack package
spctab.sql	statsctab.sql	Creates STATSPACK tables
spcusr.sql	statscusr.sql	Creates STATSPACK user & assigns grants
spdtab.sql	statsdtab.sql	Drops all STATSPACK tables
spdusr.sql	statsdusr.sql	Drops the statspack user

Next, let’s take a closer look at these scripts and see details on how to install STATSPACK. Because of the differences between versions, we will have two sections: one for pre-8.1.7 and another for Oracle 8.1.7 and Oracle9i STATSPACK.

STATSPACK scripts for Oracle8 and Oracle8i

You can see all of the scripts by going to the $ORACLE_HOME/rdbms/admin directory and listing all files that begin with “stat”:

>cd $ORACLE_HOME/rdbms/admin
server1*db01-/u01/app/oracle/product/8.1.6_64/rdbms/admin

>ls -al stat*
-rw-r--r-- 1 oracle oinstall 1739 Dec 6 1999 statsauto.sql
-rw-r--r-- 1 oracle oinstall 843 Dec 6 1999 statscre.sql
-rw-r--r-- 1 oracle oinstall 27183 Nov 10 1999 statsctab.sql
-rw-r--r-- 1 oracle oinstall 4686 Nov 10 1999 statscusr.sql
-rw-r--r-- 1 oracle oinstall 792 Aug 27 1999 statsdrp.sql
-rw-r--r-- 1 oracle oinstall 3236 Nov 10 1999 statsdtab.sql
-rw-r--r-- 1 oracle oinstall 1081 Nov 10 1999 statsdusr.sql
-rw-r--r-- 1 oracle oinstall 26667 Dec 6 1999 statspack.doc
-rw-r--r-- 1 oracle oinstall 49821 Nov 10 1999 statspack.sql
-rw-r--r-- 1 oracle oinstall 46873 Nov 10 1999 statsrep.sql
-rw-r--r-- 1 oracle oinstall 559 Aug 27 1999 statsuexp.par

Let's begin by reviewing the functions of each of these files. Several of the files call subfiles, so it helps if we organize the files in a hierarchy:

 statscre.sql This is the first install script run after you create the tablespace. It calls several subscripts:

statscusr.sql This script creates a user called PERFSTAT with the required permissions.

statsctab.sql This creates the STATSPACK tables and indexes, owned by the PERFSTAT user.

statspack.sql This creates the PL/SQL package called STATSPACK with the STATSPACK procedures.

 statsauto.sql This script contains the dbms_job.submit commands that will execute a STATSPACK snapshot every hour.

 statsdrp.sql This script is used to drop all STATSPACK entities. This script calls these subscripts:

 statsdtab.sql This drops all STATSPACK tables and indexes.

 statsdusr.sql This script drops the PERFSTAT user.

 statsuexp.par This is an export parameter file for exporting the STATSPACK objects. This can be useful if you want to consolidate STATSPACK reports for several databases into a single STATSPACK structure.

 statspack.doc This is a generic read-me file explaining the installation and operation of the STATSPACK utility.

 statsrep.sql This is the only report provided in STATSPACK. It prompts you for the start and end snapshots, and then produces an elapsed-time report.

 statsrep80.sql This is a version of the STATSPACK report for Oracle 8.0.

Now that we understand the functions of each of the files, we are ready to install STATSPACK. Our first step is to review the installation files for the STATSPACK install.

STATSPACK scripts for post 8.1.6 STATSPACK

You can see all of the scripts by going to the $ORACLE_HOME/rdbms/admin directory and listing all files that begin with “sp”:

>cd $ORACLE_HOME/rdbms/admin
server1*db01-/u01/app/oracle/product/8.1.6_64/rdbms/admin

>ls -al sp*

-rw-r--r-- 1 oracle oinstall 1771 May 10 2001 spauto.sql
-rw-r--r-- 1 oracle oinstall 82227 May 10 2001 spcpkg.sql
-rw-r--r-- 1 oracle oinstall 877 May 10 2001 spcreate.sql
-rw-r--r-- 1 oracle oinstall 42294 May 10 2001 spctab.sql
-rw-r--r-- 1 oracle oinstall 7949 May 10 2001 spcusr.sql
-rw-r--r-- 1 oracle oinstall 69074 May 10 2001 spdoc.txt
-rw-r--r-- 1 oracle oinstall 758 May 10 2001 spdrop.sql
-rw-r--r-- 1 oracle oinstall 4342 May 10 2001 spdtab.sql
-rw-r--r-- 1 oracle oinstall 1363 May 10 2001 spdusr.sql
-rw-r--r-- 1 oracle oinstall 7760 May 10 2001 sppurge.sql
-rw-r--r-- 1 oracle oinstall 113753 May 10 2001 sprepins.sql
-rw-r--r-- 1 oracle oinstall 1284 May 10 2001 spreport.sql
-rw-r--r-- 1 oracle oinstall 26556 May 10 2001 sprepsql.sql
-rw-r--r-- 1 oracle oinstall 2726 May 10 2001 sptrunc.sql
-rw-r--r-- 1 oracle oinstall 588 May 10 2001 spuexp.par
-rw-r--r-- 1 oracle oinstall 30462 May 10 2001 spup816.sql
-rw-r--r-- 1 oracle oinstall 23309 May 10 2001 spup817.sql

Let's begin by reviewing the functions of each of these files. Several of the files call subfiles, so it helps if we organize the files as a hierarchy:

 spcreate.sql This is the first install script run after you create the tablespace. It calls several subscripts:

 spcsr.sql This script creates a user called PERFSTAT with the required permissions.

 spctab.sql This creates the STATSPACK tables and indexes, owned by the PERFSTAT user.

 spcpkg.sql This creates the PL/SQL package called STATSPACK with the STATSPACK procedures.

 spauto.sql This script contains the dbms_job.submit commands that will execute a STATSPACK snapshot every hour.

 spdrop.sql This script is used to drop all STATSPACK entities. This script calls these subscripts:

 spdtab.sql This drops all STATSPACK tables and indexes.

 spdusr.sql This script drops the PERFSTAT user.

 spdoc.txt This is a generic read-me file explaining the installation and operation of the STATSPACK utility.

 spreport.sql This is the shell for the only report provided in STATSPACK. It prompts you for the start and end snapshots, and then produces an elapsed-time report.

 sprepins.sql This is the actual SQL that produces the STATSPACK report.

 sppurge.sql This is a script to delete older unwanted snapshots.

 spuexp.par This is a export parameter file to export all of the STATSPACK data.

 sptrunc.sql This is a script to truncate all STATSPACK tables.

 spup816.sql This is a script to upgrade pre-8.1.7 STATSPACK tables to use the latest schema. Note that you must export the STATSPACK schema before running this script.

 spup817.sql This is a script to upgrade to Oracle 8.1.7 from Oracle 8.1.6.