ORA-01722: invalid number while running catupgrd.sql during database upgrade

Recently we encountered this issue while upgrading from 11.2.0.3 to 11.2.0.4.

ISSUE

catupgrd.sql script was failing with the below error:

DOC> run in the old oracle home prior to upgrading a pre-11.2 database: 
DOC> 
DOC> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION') 
DOC> * 
DOC> ERROR at line 1: 
DOC> ORA-01722: invalid number 
DOC> 
DOC> 
DOC> o Action: 
DOC> Shutdown database ("alter system checkpoint" and then "shutdown abort"). 
DOC> Revert to the original ORACLE_HOME and start the database. 
DOC> Run pre-upgrade tool against the database. 
DOC> Review and take appropriate actions based on the pre-upgrade 
DOC> output before opening the datatabase in the new software version. 
-- 
The following error is generated if (1) the old release uses a time 
DOC> zone file version newer than the one shipped with the new oracle 
DOC> release and (2) the new oracle home has not been patched yet: 
DOC> 
DOC> SELECT TO_NUMBER('MUST_PATCH_TIMEZONE_FILE_VERSION_ON_NEW_ORACLE_HOME') 
DOC> * 
DOC> ERROR at line 1: 
DOC> ORA-01722: invalid number 
DOC> 
DOC> o Action: 
DOC> Shutdown database ("alter system checkpoint" and then "shutdown abort"). 
DOC> Patch new ORACLE_HOME to the same time zone file version as used 
DOC> in the old ORACLE_HOME. 


SOLUTION

Get the output of the below queries from 11.2.0.3 and 11.2.0.4 homes:

1) SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value 
FROM DATABASE_PROPERTIES 
WHERE PROPERTY_NAME LIKE 'DST_%' 
ORDER BY PROPERTY_NAME; 

2) SELECT version FROM v$timezone_file; 

3) ls -ltr $ORACLE_HOME/ORACORE/ZONEINFO 


OUTPUT:

on 11.2.0.3 
========

1. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value 
FROM DATABASE_PROPERTIES 
WHERE PROPERTY_NAME LIKE 'DST_%' 
ORDER BY PROPERTY_NAME; 

PROPERTY_NAME VALUE 
------------------------------ ---------- 
DST_PRIMARY_TT_VERSION 18 
DST_SECONDARY_TT_VERSION 0 
DST_UPGRADE_STATE NONE 

2. SELECT version FROM v$timezone_file; 

SYS@host1> SELECT version FROM v$timezone_file; 

VERSION 
---------- 
18 

3.   @host1> cd /u01/sq/ora_1/db/11.2.0.3/oracore/zoneinfo/ 
@host1> ls -tlr 
total 14568 
-rw-r--r-- 1 oracle oinstall 6068 Jun 7 2010 readme.txt 
-rw-r--r-- 1 oracle oinstall 31525 Jun 7 2010 timezdif.csv 
-rw-r--r-- 1 oracle oinstall 351525 Aug 18 2011 timezone_9.dat 
-rw-r--r-- 1 oracle oinstall 302100 Aug 18 2011 timezone_8.dat 
-rw-r--r-- 1 oracle oinstall 286815 Aug 18 2011 timezone_7.dat 
-rw-r--r-- 1 oracle oinstall 286217 Aug 18 2011 timezone_6.dat 
-rw-r--r-- 1 oracle oinstall 286310 Aug 18 2011 timezone_5.dat 
-rw-r--r-- 1 oracle oinstall 286264 Aug 18 2011 timezone_4.dat 
-rw-r--r-- 1 oracle oinstall 286651 Aug 18 2011 timezone_3.dat 
-rw-r--r-- 1 oracle oinstall 274900 Aug 18 2011 timezone_2.dat 
-rw-r--r-- 1 oracle oinstall 274427 Aug 18 2011 timezone_1.dat 
-rw-r--r-- 1 oracle oinstall 344448 Aug 18 2011 timezone_14.dat 
-rw-r--r-- 1 oracle oinstall 344425 Aug 18 2011 timezone_13.dat 
-rw-r--r-- 1 oracle oinstall 345024 Aug 18 2011 timezone_12.dat 
-rw-r--r-- 1 oracle oinstall 345356 Aug 18 2011 timezone_11.dat 
-rw-r--r-- 1 oracle oinstall 345637 Aug 18 2011 timezone_10.dat 
-rw-r--r-- 1 oracle oinstall 801410 Aug 18 2011 timezlrg_9.dat 
-rw-r--r-- 1 oracle oinstall 616723 Aug 18 2011 timezlrg_8.dat 
-rw-r--r-- 1 oracle oinstall 601242 Aug 18 2011 timezlrg_7.dat 
-rw-r--r-- 1 oracle oinstall 586750 Aug 18 2011 timezlrg_6.dat 
-rw-r--r-- 1 oracle oinstall 587487 Aug 18 2011 timezlrg_5.dat 
-rw-r--r-- 1 oracle oinstall 531137 Aug 18 2011 timezlrg_4.dat 
-rw-r--r-- 1 oracle oinstall 527717 Aug 18 2011 timezlrg_3.dat 
-rw-r--r-- 1 oracle oinstall 507957 Aug 18 2011 timezlrg_2.dat 
-rw-r--r-- 1 oracle oinstall 493675 Aug 18 2011 timezlrg_1.dat 
-rw-r--r-- 1 oracle oinstall 791430 Aug 18 2011 timezlrg_14.dat 
-rw-r--r-- 1 oracle oinstall 782475 Aug 18 2011 timezlrg_13.dat 
-rw-r--r-- 1 oracle oinstall 785621 Aug 18 2011 timezlrg_12.dat 
-rw-r--r-- 1 oracle oinstall 787272 Aug 18 2011 timezlrg_11.dat 
-rw-r--r-- 1 oracle oinstall 792894 Aug 18 2011 timezlrg_10.dat 
-rwxr-xr-x 1 oracle oinstall 781669 Jun 11 2012 timezlrg_18.dat >>>>>>>>>>>>>>>>> 
-rwxr-xr-x 1 oracle oinstall 341718 Jun 11 2012 timezone_18.dat >>>>>>>>>>>>> 
-rwxr-xr-x 1 oracle oinstall 7150 Jun 11 2012 readme_18.txt 
drwxr-xr-x 2 oracle oinstall 4096 May 6 2013 big 
drwxr-xr-x 2 oracle oinstall 4096 May 6 2013 little 

on 11.2.0.4 
========

1. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value 
FROM DATABASE_PROPERTIES 
WHERE PROPERTY_NAME LIKE 'DST_%' 
ORDER BY PROPERTY_NAME; 

PROPERTY_NAME VALUE 
------------------------------ ---------- 
DST_PRIMARY_TT_VERSION 18 
DST_SECONDARY_TT_VERSION 0 
DST_UPGRADE_STATE NONE 

2. SQL> SELECT version FROM v$timezone_file; 

no rows selected 

3. @host1> cd /u01/sq/ora_1/db/11.2.0.4/oracore/zoneinfo/ 
@host1> ls -tlr 
total 14568 
-rw-r--r-- 1 oracle oinstall 6068 Jun 7 2010 readme.txt 
-rw-r--r-- 1 oracle oinstall 31525 Jun 7 2010 timezdif.csv 
-rw-r--r-- 1 oracle oinstall 351525 Jul 16 2013 timezone_9.dat 
-rw-r--r-- 1 oracle oinstall 302100 Jul 16 2013 timezone_8.dat 
-rw-r--r-- 1 oracle oinstall 286815 Jul 16 2013 timezone_7.dat 
-rw-r--r-- 1 oracle oinstall 286217 Jul 16 2013 timezone_6.dat 
-rw-r--r-- 1 oracle oinstall 286310 Jul 16 2013 timezone_5.dat 
-rw-r--r-- 1 oracle oinstall 286264 Jul 16 2013 timezone_4.dat 
-rw-r--r-- 1 oracle oinstall 286651 Jul 16 2013 timezone_3.dat 
-rw-r--r-- 1 oracle oinstall 274900 Jul 16 2013 timezone_2.dat 
-rw-r--r-- 1 oracle oinstall 274427 Jul 16 2013 timezone_1.dat 
-rw-r--r-- 1 oracle oinstall 344448 Jul 16 2013 timezone_14.dat 
-rw-r--r-- 1 oracle oinstall 344425 Jul 16 2013 timezone_13.dat 
-rw-r--r-- 1 oracle oinstall 345024 Jul 16 2013 timezone_12.dat 
-rw-r--r-- 1 oracle oinstall 345356 Jul 16 2013 timezone_11.dat 
-rw-r--r-- 1 oracle oinstall 345637 Jul 16 2013 timezone_10.dat 
-rw-r--r-- 1 oracle oinstall 801410 Jul 16 2013 timezlrg_9.dat 
-rw-r--r-- 1 oracle oinstall 616723 Jul 16 2013 timezlrg_8.dat 
-rw-r--r-- 1 oracle oinstall 601242 Jul 16 2013 timezlrg_7.dat 
-rw-r--r-- 1 oracle oinstall 586750 Jul 16 2013 timezlrg_6.dat 
-rw-r--r-- 1 oracle oinstall 587487 Jul 16 2013 timezlrg_5.dat 
-rw-r--r-- 1 oracle oinstall 531137 Jul 16 2013 timezlrg_4.dat 
-rw-r--r-- 1 oracle oinstall 527717 Jul 16 2013 timezlrg_3.dat 
-rw-r--r-- 1 oracle oinstall 507957 Jul 16 2013 timezlrg_2.dat 
-rw-r--r-- 1 oracle oinstall 493675 Jul 16 2013 timezlrg_1.dat 
-rw-r--r-- 1 oracle oinstall 791430 Jul 16 2013 timezlrg_14.dat 
-rw-r--r-- 1 oracle oinstall 782475 Jul 16 2013 timezlrg_13.dat 
-rw-r--r-- 1 oracle oinstall 785621 Jul 16 2013 timezlrg_12.dat 
-rw-r--r-- 1 oracle oinstall 787272 Jul 16 2013 timezlrg_11.dat 
-rw-r--r-- 1 oracle oinstall 792894 Jul 16 2013 timezlrg_10.dat 
-rw-r--r-- 1 oracle oinstall 341718 Sep 18 2013 timezone_17.dat 
-rw-r--r-- 1 oracle oinstall 779003 Sep 18 2013 timezlrg_17.dat 
-rw-r--r-- 1 oracle oinstall 6905 Aug 31 22:38 readme_17.txt 
drwxr-xr-x 2 oracle oinstall 4096 Aug 31 23:04 big 
drwxr-xr-x 2 oracle oinstall 4096 Aug 31 23:04 little 
@host1> 

from the above output we can see that the result for 2nd query on 11.2.0.4 is empty(NO ROW SELECED) because of 18 version TZ files does not exist at new 11.2.0.4 home.

Hence to resolve this issue we copied the TZ files from 11.2.0.3 home to new 11.2.0.4 home.
Make sure to copy the 18 version of files from 11.2.0.3 to 11.2.0.4 homes on below locations:

/u01/sq/ora_1/db/11.2.0.4/oracore/zoneinfo/
/u01/sq/ora_1/db/11.2.0.4/oracore/zoneinfo/big
/u01/sq/ora_1/db/11.2.0.4/oracore/zoneinfo/little

And we ran the catupgrd.sql script again and it was successful.

==========================*****========================================

Step by step - How to upgrade from 11.2.0.3 to 11.2.0.4 Grid and RDBMS in RAC & Data Guard Environment

This document is for upgrading databases in RAC and dataguard environment. In this upgrade, Grid and RDBMS home is upgraded. This upgrade is done by out-of-place approach(new home for both GI and DB). There are basically 6 steps to complete this requirement:

Step 1: Prerequisite checks and backups

Step 2: New Grid Binary Installation:

            - Fill in the response file
            - Run the prerequisite checks
            - Install the new GI binary

Step 3: Manual steps performed after Grid binary installation to upgrade the Grid.

Step 4: New RDBMS installation

           -Fill in the response file
           -Run the prerequisite checks
           -Install the new RDBMS binary

Step 5: Manual steps performed after new binary installation to upgrade the RDBMS

Step 6: Upgrade the database on standby side.

Now we will move step by step in detail:

Step 1: Prerequisite checks and backups

1) Check storage space for the new binary to be installed. We must have around 20 GB of space         available for each homes(Grid and RDBMS) on each node.
Also issue df -kh and make sure we space available in /tmp and /(root).

2) Remove the audit files and any large log files on each node(not deleting will slowdown the upgrade).

$cd $GRID_HOME/rdbms/audit
$rm *.aud

3) Take full database backup using RMAN and also take full database export.

4) Backup of Grid and RDBMS binaries using tar.

5) Backup of Oracle Inventory

6) Backup of OCR on each nodes.

7) Backup of ASM metadata.

8) Secure dataguard.

Step 2: New Grid home installation:

1) Fill in the response file for the new binary home. Edit the response file according to the requirement.

2) Run the installer to install the GRID using this response file.

Step 3: Grid Upgrade:

Manual Grid upgrade step after new binary installation:

1) On each node, copy listener.ora and sqlnet.ora to new Grid home(11.2.0.4).

2) If necessary, adjust existing ASM instance memory parameter:

     Log in as sysasm:
     SQL>show parameter memory_target

     if the value is smaller than 1536 m, then issue the following command:

     SQL>alter system set memory_target=1536m scope=spfile;

    The number 1536m has proven to be efficient in most environment, the change will not be     effective util next restart.

    SQL>show parameter memory_max_target

   If the value is smaller than 4096, then issue the following command:

   SQL>alter system set memory_max_target=4096 scope=spfile;

   The number 4096m has proven to be sufficient in most environment, the change will not be effective until next restart.

3) Stop clusterware and database resources gracefully on each node.

    #crsctl stop crs

    This command will bring down the database so no need to shutdown the database exclusively.

    Make sure all CRS deamons are sto resopped on each node:

  #crsctl stat resource -t

4) On each node, execute rootupgrade.sh script from new 11.2.0.4 home to upgrade clusterware.

    The rootupgrade.sh script can only be run sequentially node by node, and not in parallel between nodes.

  Note: This script takes 15-20 minutes to complete.

While switching to root user to execute rootupgrade.sh, "su -" or "su - root" provides the full root environment. While sudo, pbrun,"su root" or "su" or similar facilities don't. It is recommended to execute rootupgrade.sh with full root acces to avoid any issue.

5) Once the Grid upgrade script finishes, all the clusterware and database services automatically comes up. If not then manually start it.

6) Verify and cluster and database services on each node.

     #crsctl check crs
     #crsctl stat resource -t
     #ocrcheck -local
     $srvctl status database -d dbname

7) After all the nodes are upgraded, update environment variables to point to new home 11.2.0.4

    Below files needs to be updated:

    1) .bash_profile
    2) /etc/oratab

Note: Once the grid is installed and upgraded in the primary servers, do the grid installation and upgrade on the standby side too. Otherwise it will not allow to install new 11.2.0.4 RDBMS home on standby side.

Step 4: New RDBMS Home Installation

Like grid installation, first thing we need to do is to prepare the response file. Fill all the necessary parameter which are required to install the new home. And then run the installer using this response file. Install new RDBMS(11.2.0.4) on standby also.

Step 5: RDBMS Upgrade

1) Before we start database upgrade, we need to create the restore point.

    SQL>CREATE RESTORE POINT before_upgrade GURANTEE FLASHBACK DATABASE;

2)Copy the parameter file, password file, tnsnames.ora and sqlnet.ora to the new RDBMS home. For      RAC copy on all the nodes.

3) Count invalid object and make a note of it.

4) Check the space in system tablespace.

5) For RAC only, change the instance to exclusive mode(i.e. cluster_database=false)

6) Gather dictionary stats from existing binary home.

      SQL>execute dbms_stats.gather_dictionary_stats;

7) Execute the following pre-check script from the existing home to check instance fulfill pre-upgrade requirement.

    SQL>@/u01/sql/ora_2db/11.2.0.4/rdbms/admin/utlu112i.sql

Fix all the errors which is reported by this script before proceeding to the actual upgrade.
For RAC this step only needs to be executed only once, not from all the nodes.

8) Startup instance with startup upgrade option in SQLPLUS with new oracle home(11.2.0.4).
     For RAC this step needs to be executed from one node only(not from all nodes). And then run the       upgrade script.

     SQL>startup upgrade
   
     SQL>@/u01/sq/ora_2/db/11.2.0.4/rdbms/admin/catupgrd.sql

9) Then restart the instance and run the following sql script:

     SQL>shut immediate

     SQL>startup

     SQL>@/u01/sq/ora_2/db/11.2.0.4/rdbms/admin/catuppst.sql

    (For RAC, this script needs to be executed only from one node)

10) Run the post-upgrade check script from new home.

      SQL>@/u01/sq/ora_2/db/11.2.0.4/rdbms/admin/utlu112s.sql

      (For RAC, this script needs to be executed from only one node)

11) Recompile objects and compile the object list to ensure no new invalid objects

      SQL>@?/rdbms/admin/utluiobj.sql

      SQL>@?/rdbms/admin/utlrp.sql

      SQL>@?/rdbms/admin/utluiobj.sql

     (for RAC, this script needs to be executed only from one node)

12) For RAC, fallback instance from exclusive mode(cluster_database=true)

13) Update database resources in CRS registry:
   
       $srvctl upgrade database -d dbname -o /u01/sq/ora_2/db/11.2.0.4

14) Startup the remaining instances of the database.

      $srvctl start instance -d dbname -i instance_name

15) After database is upgraded, update the environment variable so that it points to 11.2.0.4 in the              below files:

       /etc/oratab for oracle user
       .bash_profile for oracle user
       listener.ora file

16) If there is oracle valet then add VNCR parameter in the listener.ora file.
      (for RAC, it applies to all nodes).


17) Shutdown and restart the database.

18) Ask application team to verify everything is running fine for them.

19) Drop restore point befor_upgrade.


STEP 6 - UPGRADE THE DATABASE ON STANDBY SIDE

Perform these steps only if there is a physical standby database associated with the database being upgraded.

1) Copy below file from 11.2.0.3 home to new home 11.2.0.4:

      tnsnames.ora
      sqlnet.ora
      parameter file
      password file

2) Shutdown the primary database as follows:

    $srvctl stop database -d DBNAME -o immediate

3) Shutdown the standby database as follows:

   $srvctl stop database -d DBNAME

4) Edit the /etc/oratab and listener.ora file to point to the new 11.2.0.4 home

5) Update the OCR configuration

   $srvctl upgrade database -d DBNAME -o /u01/sq/ora_2/db/11.2.0.4

6) Start the standby database in mount mode from new home 11.2.0.4

    $srvctl start database -d DBNAME -o mount

7) Start the primary database in open mode

    $srvctl start database -d DBNAME

8) Start the redo apply on standby database

    SQL>alter database recover managed standby database disconnect from session

9)  Verify the standby alert log file and make sure logs are shipping properly, also check the archive
      gap.


Suggestions are most welcome :)

Script to find archivelog generation per hour

set pagesize 120;
set linesize 200;
col day for a8;
spool archivelog.lst

PROMPT Archive log distribution per hours on each day …
  

select
  to_char(first_time,’YY-MM-DD’) day,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’00’,1,0)),’999′) “00”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’01’,1,0)),’999′) “01”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’02’,1,0)),’999′) “02”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’03’,1,0)),’999′) “03”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’04’,1,0)),’999′) “04”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’05’,1,0)),’999′) “05”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’06’,1,0)),’999′) “06”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’07’,1,0)),’999′) “07”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’08’,1,0)),’999′) “08”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’09’,1,0)),’999′) “09”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’10’,1,0)),’999′) “10”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’11’,1,0)),’999′) “11”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’12’,1,0)),’999′) “12”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’13’,1,0)),’999′) “13”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’14’,1,0)),’999′) “14”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’15’,1,0)),’999′) “15”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’16’,1,0)),’999′) “16”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’17’,1,0)),’999′) “17”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’18’,1,0)),’999′) “18”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’19’,1,0)),’999′) “19”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’20’,1,0)),’999′) “20”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’21’,1,0)),’999′) “21”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’22’,1,0)),’999′) “22”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’23’,1,0)),’999′) “23”,
  COUNT(*) TOT
from v$log_history
group by to_char(first_time,’YY-MM-DD’)
order by day ;

Script to identify segments generating redologs

SELECT to_char(begin_interval_time,’YY-MM-DD HH24′) snap_time, 
        dhso.object_name, 
        sum(db_block_changes_delta) BLOCK_CHANGED 
  FROM dba_hist_seg_stat dhss, 
       dba_hist_seg_stat_obj dhso, 
       dba_hist_snapshot dhs 
  WHERE dhs.snap_id = dhss.snap_id 
    AND dhs.instance_number = dhss.instance_number 
    AND dhss.obj# = dhso.obj# 
    AND dhss.dataobj# = dhso.dataobj# 
    AND begin_interval_time BETWEEN to_date(’12-02-07 12:00′,’YY-MM-DD HH24:MI’)  
                                AND to_date(’12-02-07 16:00′,’YY-MM-DD HH24:MI’) 
  GROUP BY to_char(begin_interval_time,’YY-MM-DD HH24′), 
           dhso.object_name 
  HAVING sum(db_block_changes_delta) > 0 
ORDER BY sum(db_block_changes_delta) desc ;

Script to know which SQL’s are generating redo

SELECT when, sql, SUM(sx) executions, sum (sd) rows_processed 
FROM ( 
      SELECT to_char(begin_interval_time,’YYYY_MM_DD HH24′) when, 
             dbms_lob.substr(sql_text,4000,1) sql, 
             dhss.instance_number inst_id, 
             dhss.sql_id, 
             sum(executions_delta) exec_delta, 
             sum(rows_processed_delta) rows_proc_delta 
        FROM dba_hist_sqlstat dhss, 
             dba_hist_snapshot dhs, 
             dba_hist_sqltext dhst 
        WHERE upper(dhst.sql_text) LIKE ‘%Z_PLACENO%’ 
          AND ltrim(upper(dhst.sql_text)) NOT LIKE ‘SELECT%’
          AND dhss.snap_id=dhs.snap_id 
          AND dhss.instance_Number=dhs.instance_number 
          AND dhss.sql_id = dhst.sql_id  
          AND begin_interval_time BETWEEN to_date(’12-02-07 12:00′,’YY-MM-DD HH24:MI’)  
                                      AND to_date(’12-02-07 16:00′,’YY-MM-DD HH24:MI’) 
        GROUP BY to_char(begin_interval_time,’YYYY_MM_DD HH24′), 
            dbms_lob.substr(sql_text,4000,1), 
              dhss.instance_number, 
             dhss.sql_id 
) 
group by when, sql;

Script to find PGA memory allocation to BG processes

*********************************************

PGA Memory allocation to background process

*********************************************

SELECT spid, program,

pga_max_mem max,

pga_alloc_mem alloc,

pga_used_mem used,

pga_freeable_mem free

FROM V$PROCESS

WHERE spid = 2587;

Script to find memory usage by BG processes

**************************************

Memory usage for backgroung processes

**************************************

SELECT p.program,

p.spid,

pm.category,

pm.allocated,

pm.used,

pm.max_allocated

FROM V$PROCESS p, V$PROCESS_MEMORY pm

WHERE p.pid = pm.pid

AND p.spid = 2587;