Starting with Oracle 11g Release 2 there is a new tool called “chopt” which lets you enable or disable database features such as partitioning, olap and so on without the need to re-install the bianries.

You can read about the utility here.

Oracle 10g introduced the SGA_TARGET and SGA_MAX_SIZE parameter which dynamically resized many SGA components on demand. With 11g Oracle developed this feature further to include the PGA as well – the feature is now called “Automatic Memory Management” (AMM) which is enabled by setting the parameter MEMORY_TARGET.

Automatic Memory Management makes use of the SHMFS – a pseudo file system just like /proc. Every file created in the SHMFS is created in memory.

Unfortunately using MEMORY_TARGET or MEMORY_MAX_SIZE together with Huge Pages is not supported. You have to choose either Automatic Memory Management or HugePages. In this post i´d like to discuss AMM and Huge Pages.

Read more…

Starting with Oracle 11g Oracle added several compression algorithms to compress data. They can be used for compressing tables, LOBs , compressed data pump exports or even RMAN backups. Unfortunately for some compression algorithms you need to purchase the “Advanced Compression Option”. The following table lists the available RMAN compression options, the most likely compression algorithm being used and states if an additional license is required:

This article is intended to take a look at the different compression methods available in Oracle 11g and to compare them.

Read more…

The following is a short guide on how to calculate the required kernel parameters for running Oracle 11g Release 2 database on Linux.

In addition to that i add some parameters recommended by myself.

Note that for running Oracle Grid Infrastrucure (aka “Clusterware”) some additional parameters might be required.

Read more…

In this and the following posts i will perform some crash and recover scenarios and show how to recover the cluster successfully.

At the moment the following tests are planned and will be published during the next days:

• suddenly turning off the power and restarting the node
• terminating private network connect between the cluster nodes
• recovering an ACFS file system which will not mount automatically
  (was not able to reproduce more than once; sorry guys)
• overwriting the ASM disk header with the disk group being offline
• corrupting an online and active ASM disk by writing chunks of random data to the disk randomly
• simulating disk errors by removing the device from the operating system
• corrupting  the OCR
• corrupting the Voting Disk

The environment used for the posts are explained in detail here.

Useful scripts can be found here.

Test #4: Corrupting the ASM disk with ASM disk group being online and active

After overwriting the ASM disk header while the disk group was offline we will now put some load on the full running cluster and corrupt the asm disk slightly.

Read more…

The following post is a summary of my tests with DBFS on a 11g Release 2 RAC.

DBFS – An Overview

Since the introduction of LOBs the database was used to store all kinds of files, for instance images, audio files, video files or even text files. Starting with Oracle 11g Release 1 introduced SecureFiles which introduced compression and de-duplication.

Oracle Database Filesystem (DBFS) creates a mountable file system which can be used to access files stored in the database as SecureFile LOBs. DBFS is a shared file system like NFS and consists of a server (the database) and a client. Just like a traditional database connection server and client can be on different systems which communicate over SQLNet. The same applies to DBFS as well.

The following figure illustrates DBFS:

(Source: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10645/adlob_fs.htm)

Read more…

There is a problem with shared oracle homes on ACFS documented here.

However if you have not configured SSH equivalence before this short guide shows how to do set up ssh equivalence between the nodes “rac1.regner.de” and “rac2.regner.de”:

1. Unpack the database installation files

2. Execute:

<PATH_TO_DATABASE_INSTALLATION>/sshsetup/sshUserSetup.sh
  -user ora11p -hosts "rac1.regner.de rac2.regner.de" -shared

Arguments:
-user: the name of the user for ssh equivalence to be configured
-hosts: space separated list of node names
-shared: indicates a shared oracle home (on acfs or nfs)

3. Check it

/usr/bin/ssh -o FallBackToRsh=no  -o PasswordAuthentication=no 
  -o StrictHostKeyChecking=yes  -o NumberOfPasswordPrompts=0 rac2 /bin/date

/usr/bin/ssh -o FallBackToRsh=no  -o PasswordAuthentication=no
  -o StrictHostKeyChecking=yes  -o NumberOfPasswordPrompts=0 rac1 /bin/date

The two commands above should return the current date. Thats it.

This is the final part of the Oracle 11g Release 2 installation guide. At this point you should have:

• Oracle database and grid infrastructure binaries installed
• at least ONE or better two disk groups configured for:
  • database and binary files
  • flash recovery area

Our database name will be “ORA11P”.

Preparations

• create directory for cfgtools

create /u01/app/oracle/cfgtoollogs
mkdir -p /u01/app/oracle/cfgtoollogs
chown root:dba /u01/app/oracle/cfgtoollogs
chmod 775 /u01/app/oracle/cfgtoollogs

• Check SSH equivalence once again:
  execute as user „ora11p“ (this is the user holding the oracle database binary installation):

/usr/bin/ssh -o FallBackToRsh=no  -o PasswordAuthentication=no 
  -o StrictHostKeyChecking=yes  -o NumberOfPasswordPrompts=0 rac2 /bin/date

/usr/bin/ssh -o FallBackToRsh=no  -o PasswordAuthentication=no 
  -o StrictHostKeyChecking=yes  -o NumberOfPasswordPrompts=0 rac1 /bin/date

Both commands should return the current date. If there are errors like „connection refused“, check:

• network connectivity between both nodes
• does a „normal“ SSH connection works?:
  • ssh root@rac1-priv
  • ssh root@rac1
  • ssh root@rac2-priv
  • ssh root@rac2

both commands should prompt for a password.

If SSH was working before and seems “gone” you might face a problem documented here.

• add ACFS oracle home to cluster registry

$GRID_HOME/bin/srvctl add filesystem -d /dev/asm/ora11p_home-132
  -v ora11p_home -g DATA2 -m /u01/app/oracle/product/11.2.0/ora11p -u ora11p

Arguments:
-d: path of advm volume
-v: name of volume
-m: mount point path

„-u ora11p“ ist important and must match the owner of the database binary installation; else dbca will raise error compaining about missing permissions

Create the database with dbca

Start dbca

Where to go now?

• Tune Instance, for instance:
  • memory_target
  • db_writer_processes
  • sessions parameter
  • …
• Configure, perform and test your backup

Starting with Oracle 11g Release 2 Oracle introduced a new cluster database connection concept: SCAN – “Single Client Access Name”.

SCAN on the server side

When installing a 11g Release 2 grid infrastructure you are asked for the cluster name which will be part of the SCAN. The notation for the SCAN is:

<clustername>-scan.<domain>

For instance if your cluster is named “rac” and the domain “regner.de” the SCAN name will be “rac-scan.regner.de”.

In order to successful install grid infrastructure you need to configure your DNS (hosts file entries will not work!) prior installing grid infrastructure to resolve the name accordingly. Oracle requires at least one, better three IPs configured for the scan name. Your DNS zone file might look like this:

In the example zone file above we configured three IPs for the scan: 172.23.15.3, 172.23.15.4 and 172.23.15.5.

After installation you will find three listener processes running (separated on all cluster nodes) as shown in the following figure:

Listener for SCAN2 and SCAN3 are running on node “rac1” and listener for SCAN1 is running on node “rac2”. For each SCAN listener there will be a dedicated network interface running with the same IP as configured in DNS:

Client connections

Connection to database “RAC11P” using SCAN would use this tnsnames entry:

RAC11P =
 (DESCRIPTION=
 (ADDRESS=(PROTOCOL=tcp)(HOST=rac-scan.regner.de)(PORT=1521))
 (CONNECT_DATA=(SERVICE_NAME=RAC11P))
 )

The “old fashioned” way still works:

RAC11P_old =
 (DESCRIPTION=
 (ADDRESS_LIST=
 (ADDRESS=(PROTOCOL=tcp)(HOST=rac1-vip.regner.de)(PORT=1521))
 (ADDRESS=(PROTOCOL=tcp)(HOST=rac2-vip.regner.de)(PORT=1521))
 )
 (CONNECT_DATA=(SERVICE_NAME=RAC11P))
 )

Connecting to a named instance:

RAC11P =
 (DESCRIPTION=
 (ADDRESS=(PROTOCOL=tcp)(HOST=rac-scan.regner.de)(PORT=1521))
 (CONNECT_DATA=(SERVICE_NAME=RAC11P)
 (INSTANCE_NAME=RAC11P1))
 )