Oracle RAC next challenger.

EnterpriseDB have released beta version of their Postgres Plus xDB Replication Server with Multi-Master . Oracle RAC have been best and only real multimaster solutions for several years. A lot of other multi-master systems have been trying to challenge Oracle RAC but none of them succeeded. Now EnterpriseDB is trying to do it with their new multi-master replication server.

Here you can find more information about this new product:
http://www.enterprisedb.com/multi-master-replication

Have to test this product before give any recommendations about it. But it looks interesting.

Oracle11g AMM Monitoring and Tuning

Monitoring AMM:

From dynamic view V$MEMORY_DYNAMIC_COMPONENTS shows the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.
SQL> select * from v$memory_dynamic_components order by component;


Tuning AMM:

From view V$MEMORY_TARGET_ADVICE you can see tuning advice for the MEMORY_TARGET initialization parameter. If you use Enterprise Manager (or Grid Control) you can also use graphical Memory Advisor.

SQL> select * from v$memory_target_advice order by memory_size;
V$MEMORY_TARGET_ADVICE view contains following columns:
MEMORY_SIZE (alternative MEMORY_TARGET sizes), MEMORY_SIZE_FACTOR (value 1 shows current memory size), ESTD_DB_TIME (estimated DB time to complete the current workload), ESTD_DB_TIME_FACTOR (factor for estimated DB time) and VERSION.

So from this view you can check how db_time is changing if you make changes in MEMORY_TARGET-


From V$MEMORY_RESIZE_OPS view you can see circular history buffer of the last 800 SGA resize requests.

Oracle 11g Automatic Memory Management (AMM) size changing.

Oracle 11g introduced new memory management parameters

MEMORY_MAX_TARGET and MEMORY_TARGET .

MEMORY_MAX_TARGET defines maximum size for database memory. And if you need to change it you'll need to restart database instance. And MEMORY_TARGET defines target memory size for database. This can be changed dynamically with no need for restart of instance. But MEMORY_TARGET can't be bigger than MEMORY_MAX_TARGET.

With these parameters set Oracle will automaticly tune PGA and SGA sizes as it needs.

With Linux (RHEL) you need first set tmpfs /dev/shm as big as you wan't to change your MEMORY_MAX_TARGET. And to do this you have to umount /dev/shm first then change it size (do this also in fstab) and then change the parameters in database.  

You can still use old ASMM and other memory management variants with Oracle 11g but usually AMM is good enough.

To change AMM parameters:

Go into the database with sqlplus (as sysdba):
This shows current memory target parameters:
SQL> show parameter target

These changes both AMM parameters:
SQL> alter system set MEMORY_MAX_TARGET = 10G SCOPE = SPFILE;
SQL> alter system set MAX_TARGET = 10G SCOPE = SPFILE;
SQL> shutdown immediate;
With Linux change /dev/shm bigger (read more above) in this step.
SQL> startup
And check that change was succesfull:
SQL> show parameter target

And if you already have big enough MEMORY_MAX_TARGET and you only need to change MAX_TARGET bigger you can do it with this on fly:
SQL> alter system set MAX_TARGET = 10G SCOPE = BOTH;

Archivelog destination change on fly

Sometimes you need to change your archivelog destination. You might need bigger disk etc.
Here is how you can do that.

After you have added new disk into OS (remember that with RAC you need to add same disk into all nodes) and in Oracle ASM ( if you use ASM) you need to do following:


1. Check which archivelog destination you are using now:

 Go into sqlplus (as sysdba):

(This will show you which parameter or parameters you have used with archivelog destination.)
(There can be several destinations with some configurations but usually only one.)
(And if there is now destination paths in these parameters then you have used "Flash Recovery Area" for archivelog destination.) 
SQL> show parameter log_archive_dest

Check also this (you might use same disk for flash recovery area or your archivelog destination might be in the flash recovery area). :
(This will show flash revovery area and flash recovery size.)
SQL> show parameter db_recovery

You can also use following command to see archivelog destination. But if you have multiple destinations this will show only one at the time:
SQL> archive log list

2. Change archivelog destination to new disk on fly:

If you are using some log_archive_dest parameter then you change it this way from sqlplus (as sysdba):
SQL> alter system set log_archive_dest_1 = 'LOCATION=/data/oradata/test/arch' scope=both;
If you are using same old disk for flash recovery are too change this also:
SQL> alter system set db_recovery_file_dest = ‘/data/oradata/test/flash’ scope=both;

If you are using flash recovery area for archive log destination you need to do following:

If you use ASM then add new bigger disk to ASM diskgroup where your flash recovery area is (check "db_recovery_file_dest" parameter (look above)) and start use it with changing db_recovery_file_dest_size parameter to bigger:
SQL>  alter system set db_recovery_file_dest_size=<new_size> scope=both;

And if you are not using ASM then you need to change "db_recovery_file_dest" parameter to point into new disk (like with "log_archive_dest" parameters):
SQL>  alter system set db_recovery_file_dest='<path>' scope=both;

3. Check that new destination is working as it should:

Check that everything is working ok:

SQL>  archive log list

SQL>  alter system archive log current;

If this does not give you errors you can remove old disk from OS (and from ASM if you are using it.).
 

Oracle 11.2.0.3 RHEL6 and VMware multicast problems in prerequisite check

When you install Oracle 11.2.0.3 on RHEL6 and VMware you might face multicast test fails in Prerequisite Checks step in Grid Infrastructure installation and in Database software installation.

These fails comes because cluvfy is running prerequisite tests for all ports and if your firewall is not allowing all of them then it gives those fails. But you can download mcasttest.pl script from MOS (My Oracle Support). Search it on the MOS with the script name. Correct MOS document is "Grid Infrastructure 11.2.0.2 Installation or Upgrade may fail due to Multicasting Requirement [ID 1212703.1]". This document gives also hints how to run the script. Document speaks about 11.2.0.2 version but you can use same script with 11.2.0.3 version checking. This script can be used to test multicast with the correct ports and if it goes throught then you can safely ignore multicast fails in prerequisite check.

for example:
perl mcasttest.pl -n node1_name,node2_name -i eth1

If you want to test your firewall settings after installation you can run cluvfy check from command line this way (as oracle user):
/app00/app/11.2.0/grid/bin/cluvfy stage -post hwos -n node1_name,node2_name -verbose 

Multicast check lines can be find end of the log file just like in the installation logs (grep Checking multicast communication  )