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.

How to disable automatic startup of RAC database

When I began to work in my current company there were 3 databases working on a RAC environment formed by two nodes. There were many reboots due to misconfiguration (one for all... they didn't use a gigabit switch for private interconnection, but.. a CROSS cable!!!!): I pretended to move some instances to new machines and now we have three RAC installations.



The following are the steps to disable two of the previous three instances from automatic startup.

I issued the following command to check the status of these databases:

srvctl status database -d SID -v



I shutted down both databases with immediate option:

srvctl stop database -d SID -o immediate



Again I looked at the status:


Then I definetely disabled the automatic startup of those databases with the following command:

srvctl disable database -d SID


That's all.

Oracle 11gR2 RAC installation: Database installation

Login to first node as the oracle user and start the runInstaller








Configure your Oracle Support Account


Create and configure a database



Server class

It's a Rac installation

Test the SSH conectivity from previos screenshot.

SSH connectivity already estabilished

Advanced installation

Installing the Enterprise Edition

Options availables

Choose the right path

General purpose database installation

Global database name: RAC3

Character sets

Security

Sample schemas, no thanks.

In the course of introducing Automatic Memory Management (AMM) in version 11g, Oracle performed a major change in the internal memory management on OS level. This affects the prerequisites for setting up a database. It is important to know that, prior to starting the Oracle instance, the underlying virtual file system has to be sized accordingly. The determining factor here is the value of the memory_max_target parameter. If not set explicitly it defaults to the memory_target parameter value.

The following error I got adding another database (for test my restore script) using dbca on the same RAC environment.

ORA-00845: MEMORY_TARGET not supported on this system

and on the alert log you could read the following warning message:

WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 1795162112 bytes. /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 1795063808 and used is 2394198016 bytes. Ensure that the mount point is /dev/shm for this directory.memory_target needs larger /dev/shm
I will cover this problem in another post.

Setting now a high value of database memory arise the following error

So from runInstaller choose a suitable memory. In my case I choose 44%

No Grid Control is available

As database storage I choosed Automatic Storage Management

I'm not enabling automated backups

Select the existing disk groups for ASM

That's all.