Archive for March, 2010

11g ASM rolling upgrade from to

March 30, 2010 Leave a comment

In Oracle 11g, we can now do rolling upgrades of the ASM instanaces in a RAC cluster configuration. In this example shown below, one of the ASM instances is being upgraded to while the other instance is still running on version While the ASM software is being upgraded, client connections to the RAC database will continue as the service will get relocated to the other node in the cluster which is not being currently upgraded. So in other words this is an online patch application.

On one of the nodes in the cluster, connect to the ASM instance (+ASM1) and issue the command to start the rolling migration.

SQL> alter system start rolling migration to ''

System altered.

Stop the database instance testrac1, shutdown the ASM instance +ASM1 and
then stop the listener which is running from the ASM home which will now be upgraded to

[oracle@nesdbts01]$ srvctl stop instance -d testrac -i testrac1

[oracle@nesdbts01]$ sqlplus sys as sysasm

SQL*Plus: Release - Production on Mar 29 09:44:46 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> shutdown immediate;

ASM diskgroups dismounted

ASM instance shutdown

SQL> quit

[oracle@nesdbts01] lsnrctl stop LISTENER_TESTRAC1

LSNRCTL for Linux: Version - Production on 30-MAR-2010 14:53:50

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=nesdbts02-p.ncs.local)(PORT=50235)))

The command completed successfully

The service running on TESTRAC1 will be relocated to the other surviving on node TESTRAC2

[oracle@nesdbts01]  sqlplus olasadm/*******@testrac1
SQL*Plus: Release - Production on Fri Dec 11 09:11:44 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> select count(*) from tedran;




SQL> select host_name from v$instance;



********ASM and Database Instance is shutdown here ****************

SQL> /


SQL> select count (*) from tedtran;




When installing the patch , only one node in the cluster must be selected where the patch will be applied, the other node in the cluster is where the ASM and Database instance is running and where all the client connection are being hosted

Run the patch.

Once completed, we should start the listener, the upgraded ASM instance and the database instance.

[oracle@nesdbts01]$ lsnrctl start LISTENER_TESTRAC1

[oracle@nesdbts01]$  sqlplus sys as sysasm

SQL*Plus:  Release  - Production on Mar 29 10:44:46 2010

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Enter password:

Connected to an idle instance.

SQL> startup

ASM instance started

Total System Global Area  283930624 bytes

Fixed Size                  2153056 bytes

Variable Size             256611744 bytes

ASM Cache                  25165824 bytes

ASM diskgroups mounted

[oracle@nesdbts01] srvctl start instance -d testrac -i testrac1

We will now repeat all the same steps on the other node middba2. After the patch has been applied to all the ASM Oracle Home’s we will see a message indicating that we now can turn off the ASM rolling upgrade.

Note: while the ASM in in ‘rolling upgrade’ mode, a limited number of ASM operations can be performed and certain ASM operations like rebalancing cannot be carried out.

SQL> alter system stop rolling migration;

System altered.

We can finally relocate the service back to its original node - nesdbts01

[oracle@nesdbts01]  srvctl relocate service -d testrac -s testrac1_service  -i testrac2 -t testrac1 -f
[oracle@nesdbts01]  srvctl status service -d testrac -s testrac1_service

Service testrac1_service is running on instance(s) testrac1

Categories: Oracle Rac

Memory Notification: Library Cache Object Loaded Into SGA” is being written continuously in alert log file

March 26, 2010 Leave a comment

These are warning messages generated in Oracle 10g Rel 2

To prevent or avoid these messages being generated, we need to adjust the size of a hidden initialization parameter “_kgl_large_heap_warning_threshold” to a high value or zero.

To find out what the value is set for the parameter run the following sql

SELECT a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '%kgl_large_heap%';

Parameter Session Value Instance Value
------------------------------------ -------------------- --------------------

_kgl_large_heap_warning_threshold 2097152 2097152

To change the hidden parameter value.
If you are using spfile, login as sysdba

SQL> connect /as sysdba
SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;

System altered.

Categories: Uncategorized

multiplexing oracle ASM control files

March 22, 2010 Leave a comment

SQL> show parameter control_files;

NAME            TYPE        VALUE
--------------- ----------- ------------------------------
control_files   string      +FLASH/testorac/controlfile/current.256.710173383
SQL> alter system set control_files='+DATA','+FLASH' scope=spfile ;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount ;
ORACLE instance started.

Total System Global Area  267227136 bytes
Fixed Size                  2212496 bytes
Variable Size             222301552 bytes
Database Buffers           37748736 bytes
Redo Buffers                4964352 bytes

SQL> exit
oracle@uknesdb99:~/scripts>  rman target /

Recovery Manager: Release - Production on Mon Mar 22 15:35:41 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTORAC (not mounted)

RMAN> restore controlfile  from '+FLASH/TESTORAC/CONTROLFILE/Current.256.710173383';

Starting restore at 22-MAR-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/testorac/controlfile/current.412.714325539
output file name=+FLASH/testorac/controlfile/current.440.714325539
Finished restore at 22-MAR-10

SQL> alter database mount ;

Database altered.

SQL> alter database open ;

Database altered.

SQL>show parameter control_files

+DATA/testorac/controlfile/current.412.714325539, +FLASH/testorac/controlfile/current.440.714325539

Categories: Oracle

script to backup Oracle ASM metadata

March 22, 2010 Leave a comment
Script taken from

# Date          Version Author          Comments
# 09/06/09      1.1     James Hardaker  Initial Version
# 10/06/09      1.2                     Added Error Handling
# 21/03/10      1.3     Mike desouza    11g compatibility
# Description
# Queries ASM for a list of disk groups and backs up the metadata
# of each to /app/oracle/backups
# Should be run as the oracle user

# Tunables



# Start of functions
# asm_metadata - backup the asm metadata
   export ORACLE_SID=`cat /etc/oratab|grep ^+ASM \
                      |awk 'BEGIN {FS=":"}; {print $1}'`
   export ORACLE_HOME=`cat /etc/oratab|grep ^+ASM \
                      |awk 'BEGIN {FS=":"} {print $2} '`

for DG in `asmcmd ls + | sed 's/\///'`
  echo "INFO: Backup of ${DG} diskgroup started at `date`"
  if [ -f ${BACKUPDIR}/asm_metadata_${DG}.bkp ]
    echo "INFO: Moving the file ${BACKUPDIR}/asm_metadata_${DG}.bkp \
          to ${BACKUPDIR}/asm_metadata_${DG}.old"
    cp ${BACKUPDIR}/asm_metadata_${DG}.bkp
    ${BACKUPDIR}/asm_metadata_${DG}.old 2>/dev/null
    if [ $? -ne 0 ]
     echo "WARN: Unable to backup ${BACKUPDIR}/asm_metadata_${DG}.bkp,
     will overwrite it anyway"
      rm -f ${BACKUPDIR}/asm_metadata_${DG}.bkp 2>/dev/null
    echo "INFO: Backing up the ${DG} diskgroup to
    rm ${BACKUPDIR}/asm_metadata_${DG}.err 2>/dev/null
    ${ORACLE_HOME}/bin/asmcmd md_backup \ 
    ${BACKUPDIR}/asm_metadata_${DG}.bkp -G ${DG} \
    if [ -s ${BACKUPDIR}/asm_metadata_${DG}.err ]
       echo ""
       echo "WARN: Unable to backup the ${DG}
             diskgroup to ${BACKUPDIR}/asm_metadata_${DG}.bkp"
       cat ${BACKUPDIR}/asm_metadata_${DG}.err
       echo ""
       echo "INFO: Backup of ${DG} diskgroup completed at `date`"
    echo ""

# End of functions

### Main Program

echo "INFO: ASM Metadata backup started at `date`"
echo ""
echo "INFO: This script is being run as user `/usr/bin/id -un`"
echo ""

# create the backup directory if it does not exist

if [ ! -d ${BACKUPDIR} ]
    mkdir ${BACKUPDIR} 2>/dev/null
    echo "INFO: Created the directory ${BACKUPDIR} as it did not exist"
    echo ""
    if [ $? -ne 0 ]
      echo "ERROR: Unable to create the directory ${BACKUPDIR}"
      exit 99

# backup the asm metadata


if [ ${ERR} != 0 ]
    echo "ERROR: An error occurred backing 
          up one or more disk groups, see above"
    exit 98
    echo "INFO: ASM Metadata backup completed at `date`"


Categories: Oracle

ORA-00845: MEMORY_TARGET not supported on this system

March 17, 2010 Leave a comment

Example taken from

ORA-00845: MEMORY_TARGET not supported on this system

Cause of the Problem
•Starting from Oracle 11g the automatic memory management feature is now defined with parameter MEMORY_TARGET and MEMMORY_MAX_TARGET.

•On linux file system the shared memory need to be mounted on /dev/shm directory on the operating system.

•And the size of /dev/shm needs to be greater than MEMORY_TARGET or MEMMORY_MAX_TARGET.

•The AMM (Automatic Memory Management) now in 11g manages both SGA and PGA together by MMAN process.

•The MEMORY_TARGET parameter in 11g comes for (SGA_TARGET+PGA_AGGREGATE_TARGET) which was in 10g.

•And MEMORY_MAX_TARGET parameter in 11g comes instead of SGA_MAX_TARGET parameter which was in 10g.

•The ORA-00845:can arises for the following two reasons on linux system.

1)If the shared memory which is mapped to /dev/shm directory is less than the size of MEMORY_TARGET or MEMORY_MAX_TARGET.
2)If the shared memory is not mapped to /dev/shm directory.

Solution of the Problem

Make sure /dev/shm is properly mounted. You can see it by,
df -h
The output should be similar like
$ df -kFilesystem            Size  Used Avail Use% Mounted on…shmfs                 1G    512M 512M  50% /dev/shm

We see here for /dev/shm we have assigned 1G memory. Now if you set MEMORY_TARGET more than 1G then above ORA-845 will arise. For example if you have MEMORY_TARGET or MEMORY_MAX_TARGET set to 12G then you can mount shared memory to 13g like below.

As a root user,
# mount -t tmpfs shmfs -o size=13g /dev/shm

In order to make the settings persistence so that it will affect after restarting machine add an entry in /etc/fstab similar to the following:

shmfs /dev/shm tmpfs size=13g 0

Categories: Uncategorized

Viewing ADR Locations with the V$DIAG_INFO View

March 16, 2010 Leave a comment

The V$DIAG_INFO view lists all important ADR locations for the current Oracle Database instance.


INST_ID NAME                  VALUE
------- --------------------- -------------------------------------------------------------
      1 Diag Enabled          TRUE
      1 ADR Base              /u01/oracle
      1 ADR Home              /u01/oracle/diag/rdbms/orclbi/orclbi
      1 Diag Trace            /u01/oracle/diag/rdbms/orclbi/orclbi/trace
      1 Diag Alert            /u01/oracle/diag/rdbms/orclbi/orclbi/alert
      1 Diag Incident         /u01/oracle/diag/rdbms/orclbi/orclbi/incident
      1 Diag Cdump            /u01/oracle/diag/rdbms/orclbi/orclbi/cdump
      1 Health Monitor        /u01/oracle/diag/rdbms/orclbi/orclbi/hm
      1 Default Trace File    /u01/oracle/diag/rdbms/orclbi/orclbi/trace/orcl_ora_22769.trc
      1 Active Problem Count  8
      1 Active Incident Count 20
Categories: Uncategorized

check for invalid components after upgrade

March 15, 2010 Leave a comment

SELECT r.comp_name, r.version, r.status FROM dba_registry r;

Categories: Uncategorized