Monitor SGA/PGA while Database Upgrade is running

This blog is always a reminder to myself as well – just in case you wonder about that simple example and all steps here 😉

Currently I work with an important customer from Switzerland – and they upgrade their database landscape to Oracle Database 12.1.0.2. But in some rare cases we see the upgrade failing at different stages with dying PQ slaves or running out of memory. The reason may be that the customer is using only MEMORY_TARGET=1G – but actually our preupgrd.sql has nothing too complain about. And from reading the docs all seems to be fine as well (MEMORY_TARGET>152M).

The temporary workaround used by the customer is to increase MEMORY_TARGET prior to the upgrade to 4G – and decrease it later on to the old value. This works but it does not answer the question yet if something is wrong with MEMORY_TARGET or if our script does not recommend the correct value. And I can’t answer this at the moment – but in order to collect some information I’ve did the following in my environment:

  1. Simple SQL script check_sga.sh to monitor the change in pool shares while the upgrade is running:
    #!/bin/sh
    ORACLE_BASE=/u01/app/oracle
    ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2
    
    if [ "$1" = "" ] ; then
      ORACLE_SID=UPGR
    else
      ORACLE_SID=$1
    fi
    
    TNS_ADMIN=/u01/app/oracle/product/12.1.0.2/network/admin
    NLS_LANG=AMERICAN_AMERICA.AL32UTF8
    PATH=$ORACLE_HOME/bin:$PATH
    LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
    CLASSPATH=.:$ORACLE_HOME/jdbc/lib/classes12.jar:$ORACLE_HOME/jdbc/lib/nls_charset12.jar:$ORACLE_HOME/rdbms/jlib/xdb.jar:$ORACLE_HOME/lib/xmlparserv2.jar:$ORACLE_HOME/sqlj/lib/utl_dbws.jar
    export ORACLE_BASE ORACLE_HOME ORACLE_SID NLS_LANG CLASSPATH PATH LD_LIBRARY_PATH TNS_ADMIN
    
    sqlplus -s "sys/oracle@upgr as sysdba" @check_sga.sql >> check_sga.txt
    
  2. Simple shell script to call the SQL script check_sga.sql periodically – and there would be other views to monitor the SGA behavior especially for resize operations than the V$MEMORY_DYNAMIC_COMPONENTS I used below: Shared Memory Management Views in Oracle 12c.
    SET PAGESIZE 50000
    SET LINESIZE 25000
    SET NUMWIDTH 5
    SET FEEDBACK OFF
    set echo off
    set heading on
    set headsep off
    set wrap off
    column MB format 999999
    column OPERATION format a22
    
    SELECT TO_CHAR(SYSDATE, 'dd-mm-yy hh24:mi:ss') "TIMESTAMP" from dual;
    
    select component, current_size/power(1024,2) "MB", last_oper_type "OPERATION" from v$memory_dynamic_components where current_size != 0;
    
    exit
    
  3. Enable the listener to accept remote connections via TPC while the database is in RESTRICTED SESSION more.
    This is from my tnsnames.ora – I had to add the (UR=A) setting:

    UPGR =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
    (UR = A)
          (SERVICE_NAME = UPGR)
    )
      )
    
  4. Change the script properties to make it executable without any issues from cron
    $ chmod 777 check_sqg.sh
    $ chmod +x checg_sga.sh
    
  5. Add the shell script to crontab for periodic execution every minute ==> * * * * *
    $ export EDITOR=vi
    $ crontab -e* * * * * /home/oracle/check_sga.sh.
    
  6. Start the database in UPGRADE mode with a very tiny memory_target (700MB in my example) and no other memory values specified
    :*.audit_file_dest='/u01/app/oracle/admin/UPGR/adump'
    *.audit_trail='NONE'
    *.compatible='12.1.0'
    *.control_files='/oradata/UPGR/control01.ctl','/oradata/UPGR/control02.ctl'
    *.db_block_size=8192
    *.db_domain=''
    *.db_name='UPGR'
    *.db_securefile='ALWAYS'
    *.diagnostic_dest='/oradata'
    *.memory_target=734003200
    *.open_cursors=300
    *.processes=300
    *.remote_login_passwordfile='EXCLUSIVE'
    *.sessions=100
    *.undo_tablespace='UNDOTBS1'
    
  7. Monitor the output and see what’s happening
    PGA SGA Upgrade Usage Shift 12c
    What do we see here:

    1. The Upgrade took roughly 34 minutes – it’s one my laptop, 2 years old, 5400rpm spinning disk, a true high end system 😉 Inside an Oracle VirtualBox image.
    2. SGA_TARGET and PGA_TARGET stay constant throughout the upgrade – no shift from one to another part is happening
    3. When the Java component upgrade happens at 13:13h there’s a huge allocation of JAVA_POOL_SIZE taken away from the Default Buffer Cache.
    4. The Shared Pool is slightly increasing during the upgrade – mem shifted also from the Default Buffer Cache.
  8. I did the same exercise now while recompiling the database objects afterwards – this has nothing to do with the original question but I was simply curious:

    Of course we see plenty of shared pool getting allocated – that is expected. But see also how heavily the buffer cache shrinks with such a small SGA. That’s why we always recommend to have DB_CACHE_SIZE set in addition as a minimum barrier to prevent the database (regardless if you use SGA_TARGET or MEMORY_TARGET) from getting too low during regular operation).
    And just on the side: The recompilation took only 4 minutes from 13:53h-13:57h – but as there was no load on the database the pools stayed constant afterwards.

I’m pretty sure this all can be done way more sophisticated – but I wanted to have a simple example working quickly in my environment. And it does what I want.

Now the next step will be to verify and compare this with the customer’s database upgrades.

–Mike

Share this: