How to install Oracle STATSPACK

To install STATSPACK utility, I created a new tablespace to associate later to a new user, PERFSTAT created using spcreate.sql script






This script is located at:
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcreate.sql

Log in as SYS user and then run the sql script. It will ask for PERFSTAT password and his default and temporary tablespaces. Try to choose a good password because that user will have many privileges.


If no error is reported STATSPACK tool is available to use.


To simply create a new snap, connect as PERFSTAT user and then run:
SQL> exec statspack.snap;

Get another snapshot after 15-20 minutes.
To view the available snapshot use the following query:
SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')
"Date/Time" from stats$snapshot,v$database;

NAME SNAP_ID Date/Time
--------- ---------- -------------------
BILLING 21 17.02.2010:17:44:09
BILLING 22 17.02.2010:17:59:46
BILLING 1 16.02.2010:12:52:04
BILLING 11 16.02.2010:13:04:14

To create a new report run:
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spreport.sql

It will ask for two SNAP_ID, a report file name... and then your report will be created.

Adding a new database on 11gR2 using dbca

I needed to add a new database to test some BACKUP/RESTORE commands.












I selected a Custom Database

I used the same ASM disk groups available for an already active database.

I want to give 40% of available memory to SGA and PGA

I increased the number of processes.

Oracle 11g is using a new mechanism for managing shared memory: you need to configure large enough tmpfs on /dev/shm device to fit all requested memory up to MEMORY_MAX_TARGET.

To solve this problem you should correct as follow:

# umount tmpfs
# mount -t tmpfs shmfs -o size=3200m /dev/shm

The same alert on dbca log

To by-pass this error I reduced the amount of memory requested during this setup

The new database is now available to be used.