Search This Blog

Thursday, December 9, 2010

ASM Creation (Windows)

You can follow this steps and create a ASM diskgroup on your local machine and play with it ( Windows)

1) Creating a dummy disks

F:\>mkdir asmdisks
F:\>cd asmdisks
F:\asmdisks>asmtool -create F:\asmdisks\ disk1 512
F:\asmdisks>asmtool -create F:\asmdisks\ disk2 512
F:\asmdisks>asmtool -create F:\asmdisks\ disk3 512

Now you have 3 disks(dummy) of 512mb each which can be used to create a ASM disk group.

2) Create ASM instance

a) Configure Cluster Synchronization Servie

C:\>c:\oracle\product\ 10.2.0\db_ 1\BIN\localconfi g add

Step 1: stopping local CSS stack
Step 2: deleting OCR repository
Step 3: creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'ap\arogyaa' , privgrp ''..
Operation successful.
Step 4: creating new CSS service
successfully created local CSS service
successfully reset location of CSS setup
b) Create Init pfile
Open notepad edit the following parameters and save file as "C:\oracle\product\ 10.2.0\db_ 1\database\ init+ASM. ora"
INSTANCE_TYPE= ASM
DB_UNIQUE_NAME= +ASM
LARGE_POOL_SIZE= 8M
ASM_DISKSTRING= 'F:\asmdisks\ *'
_ASM_ALLOW_ONLY_ RAW_DISKS= FALSE

c) Create service and password file
oradim will create an ASM instance and start it automatically.
c:\> orapwd file=C:\oracle\ product\10. 2.0\db_1\ database\ PWD+ASM.ora password=asm
c:\> oradim -NEW -ASMSID +ASM -STARTMODE auto

3) Create ASM disk group
a) Create asm disk group
SQL> select path, mount_status from v$asm_disk;
PATH MOUNT_S
------------ --------- --------- --
F:\ASMDISKS\ DISK1 CLOSED
F:\ASMDISKS\ DISK3 CLOSED
F:\ASMDISKS\ DISK2 CLOSED

SQL> create diskgroup data external redundancy disk
2 'F:\ASMDISKS\ DISK1',
3 'F:\ASMDISKS\ DISK2',
4* 'F:\ASMDISKS\ DISK3';
Diskgroup created.
b) Change PFILE to SPFILE, Add ASM Diskgroup parameter and your all set to go and use ASM.
SQL> create spfile from pfile;
SQL> startup force;
SQL> alter system set asm_diskgroups= data scope=spfile;
SQL> startup force;
SQL> startup force
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1247420 bytes
Variable Size 57472836 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL>
Now you can go ahead and use your DBCA and create a database and on step 6 of 13, you can use Automatic Storage management as your Filesystem.

Monday, September 20, 2010

Database Upgrade

Upgrade Oracle Database 10g to 11g Manual Upgrade

We are going to use Manual Upgrade

Install 11g database software in different ORACLE_HOME from source Database



oracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> pwd
/opt/app/oracle/product/11.1/rdbms/admin
oracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> ls -lt utlu111i.sql
-rw-r--r-- 1 oracle oinstall 138636 2007-07-11 09:01 utlu111i.sql
oracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> pwd
/opt/app/oracle/product/11.1/rdbms/admin
oracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Apr 27 09:28:04 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

SQL> spool anujupgrade.lst

SQL> @utlu111i.sql
Oracle Database 11.1 Pre-Upgrade Information Tool 04-27-2010 09:29:25
.
**********************************************************************
Database:
**********************************************************************
--> name: VIHAAN
--> version: 10.2.0.4.0
--> compatible: 10.2.0.3.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 727 MB
.... AUTOEXTEND additional space required: 247 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 473 MB
.... AUTOEXTEND additional space required: 443 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 425 MB
.... AUTOEXTEND additional space required: 175 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 41 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 69 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 672 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
--> "background_dump_dest" replaced by "diagnostic_dest"
--> "user_dump_dest" replaced by "diagnostic_dest"
--> "core_dump_dest" replaced by "diagnostic_dest"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 11g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... OLAPSYS
.... SYSMAN
.... CTXSYS
.... XDB
.... MDSYS
WARNING: --> Database contains INVALID objects prior to upgrade.
.... USER PUBLIC has 1 INVALID objects.
.... USER SYS has 2 INVALID objects.
WARNING: --> Database contains schemas with objects dependent on network
packages.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.

PL/SQL procedure successfully completed.

SQL> select * from v$timezone_file;
If time zone file version is less than 4 then apply time zone patch 5632264 manually

select object_name, owner, object_type from all_objects where status like 'INVALID';


SQL> select object_name, owner, object_type from all_objects where status like 'INVALID';

OBJECT_NAME OWNER
------------------------------ ------------------------------
OBJECT_TYPE
-------------------
DBMS_REGISTRY SYS
PACKAGE BODY

DBA_REGISTRY_DATABASE SYS
VIEW

DBA_REGISTRY_DATABASE PUBLIC
SYNONYM

col COMP_NAME format a50
set linesize 200
set pagesize 200
SQL> r
1* select comp_name,version, status from dba_registry

COMP_NAME VERSION STATUS
-------------------------------------------------- ------------------------------ --------------------------------------------
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Data Mining 10.2.0.4.0 VALID
Oracle Text 10.2.0.4.0 VALID
Oracle XML Database 10.2.0.4.0 VALID
Oracle Rules Manager 10.2.0.4.0 VALID
Oracle interMedia 10.2.0.4.0 VALID
OLAP Analytic Workspace 10.2.0.4.0 VALID
Oracle OLAP API 10.2.0.4.0 VALID
OLAP Catalog 10.2.0.4.0 VALID
Spatial 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID

17 rows selected.



1.7 If you are using spfile, create pfile
SQL> create pfile from spfile ;

This will create pfile in 10g $ORACLE_HOME/dbs/init[SID].ora





a) Remove *.background_dump_dest, *.core_dump_dest, *.user_dump_dest and add
*.diagnostic_dest=’/11g_base’ (11g Base Directory)
b) Change
*.compatible='10.2.0.1.0'
to
*.compatible=’11.1.0′




vihaan.__db_cache_size=377487360
vihaan.__java_pool_size=4194304
vihaan.__large_pool_size=4194304
vihaan.__shared_pool_size=142606336
vihaan.__streams_pool_size=0
*.control_files='/opt/app/oracle/datafile/vihaan/control01.ctl','/opt/app/oracle/datafile/vihaan/control02.ctl','/opt/app/oracle/datafile/vihaan/control03.ctl'
*.db_block_size=8192
*.db_domain='apt-amd-02'
*.db_file_multiblock_read_count=16
*.db_name='vihaan'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=vihaanXDB)'
*.job_queue_processes=10
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=199229440
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=536870912
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.compatible='11.1.0'
*.diagnostic_dest='/opt/app/oracle/admin/vihaan/diagnostic'



oracle@apt-amd-02:/opt/app/oracle/product/10.2/dbs> mkdir -p /opt/app/oracle/admin/vihaan/diagnostic
oracle@apt-amd-02:/opt/app/oracle/product/10.2/dbs> cd /opt/app/oracle/admin/vihaan/
oracle@apt-amd-02:/opt/app/oracle/admin/vihaan> ls -lt
total 8
drwxr-xr-x 2 oracle oinstall 6 2010-04-27 09:43 diagnostic
drwxr-x--- 2 oracle oinstall 4096 2010-04-27 09:29 adump
drwxr-x--- 2 oracle oinstall 4096 2010-04-27 09:20 udump
drwxr-x--- 2 oracle oinstall 141 2010-04-27 09:20 bdump
drwxr-x--- 2 oracle oinstall 35 2010-04-26 13:26 pfile
drwxr-x--- 2 oracle oinstall 6 2010-04-26 13:20 cdump
drwxr-x--- 2 oracle oinstall 6 2010-04-26 13:20 dpdump


2. Upgrade Database

Shut down source database (10g) - Your downtime starts here

SQL> connect sys/sys as sysdba
Connected.
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.



2.2 Set your environment variables to Oracle Database 11g Release 1 (11.1) :

export ORACLE_HOME=/u01/oracle/11gbase/11.1.0
export ORACLE_SID=TEST
export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=/opt/app/oracle/product/10.2
#export ORACLE_HOME=/opt/app/oracle/product/11.1
export ORACLE_SID=vihaan
export PATH=$ORACLE_HOME/bin:$PATH:.
export LIBXCB_ALLOW_SLOPPY_LOCK=1
export TZ=GMT

comment the oracle 10g home and uncomment 11g




2.3 Start Upgrade
oracle@apt-amd-02:~> pwd
/home/oracle
oracle@apt-amd-02:~> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 27 09:50:25 2010

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

Connected to an idle instance.

SQL> startup upgrade pfile='/tmp/initvihaan.ora' ;
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2146112 bytes
Variable Size 150995136 bytes
Database Buffers 377487360 bytes
Redo Buffers 3833856 bytes
Database mounted.
Database opened.


Check shared_pool & java_pool size, to set new values



SQL> spool anujupgrade.log
SQL> @?/rdbms/admin/catupgrd.sql

after catupgrd.sql database will be shutdown .


SQL> /*****************************************************************************/
SQL> /* Step 10 - SHUTDOWN THE DATABASE..!!!!!
SQL> */
SQL> /*****************************************************************************/
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above sql script is the final step of the upgrade. Please
DOC> review any errors in the spool log file. If there are any errors in
DOC> the spool file, consult the Oracle Database Upgrade Guide for
DOC> troubleshooting recommendations.
DOC>
DOC> Next restart for normal operation, and then run utlrp.sql to
DOC> recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> Rem *********************************************************************
SQL> Rem END catupgrd.sql
SQL> Rem *********************************************************************
SQL>

=====


SQL> startup pfile='/tmp/initvihaan.ora' ;
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2146112 bytes
Variable Size 150995136 bytes
Database Buffers 377487360 bytes
Redo Buffers 3833856 bytes
Database mounted.
Database opened.

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




Oracle Database 11.1 Post-Upgrade Status Tool 04-27-2010 12:11:48
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.1.0.6.0 00:42:49
JServer JAVA Virtual Machine
. VALID 11.1.0.6.0 00:20:34
Oracle Workspace Manager
. VALID 10.2.0.4.3 00:00:01
OLAP Analytic Workspace
. VALID 11.1.0.6.0 00:00:55
OLAP Catalog
. VALID 11.1.0.6.0 00:01:40
Oracle OLAP API
. VALID 11.1.0.6.0 00:00:26
Oracle Enterprise Manager
. ORA-06550: line 5, column 35:
. PL/SQL: ORA-00942: table or view does not exist
. ORA-06550: line 5, column 1:
. PL/SQL: SQL Statement ignored
. ORA-00001: unique constraint (SYSMAN.PARAMETERS_PRIMARY_KEY) violated
. ORA-06512: at "SYSMAN.MGMT_TIME_SYNC", line 108
. ORA-06512: at "SYSMAN.MGMT_TIME_SYNC", line 166
. ORA-06512: at line 2
. VALID 11.1.0.6.0 00:20:30
Oracle XDK
. VALID 11.1.0.6.0 00:01:37
Oracle Text
. VALID 11.1.0.6.0 00:01:41
Oracle XML Database
. VALID 11.1.0.6.0 00:13:43
Oracle Database Java Packages
. VALID 11.1.0.6.0 00:00:42
Oracle Multimedia
. VALID 11.1.0.6.0 00:09:04
Spatial
. VALID 11.1.0.6.0 00:06:41
Oracle Expression Filter
. VALID 11.1.0.6.0 00:00:15
Oracle Rules Manager
. VALID 11.1.0.6.0 00:00:13
Gathering Statistics
. 00:05:25
Total Upgrade Time: 02:06:28

PL/SQL procedure successfully completed.

SQL>
SQL> SET SERVEROUTPUT OFF
SQL> SET VERIFY ON



Workaround:

These errors do not result in any data loss. Therefore, you can ignore these errors.


===============================
or apply Patch 7449757

oracle@apt-amd-02:~> unzip p7449757_111070_Generic.zip
Archive: p7449757_111070_Generic.zip
creating: 7449757/
creating: 7449757/etc/
creating: 7449757/etc/xml/
inflating: 7449757/etc/xml/ShiphomeDirectoryStructure.xml
inflating: 7449757/etc/xml/GenericActions.xml
creating: 7449757/etc/config/
inflating: 7449757/etc/config/actions.xml
inflating: 7449757/etc/config/inventory.xml
inflating: 7449757/README.txt
creating: 7449757/files/
creating: 7449757/files/rdbms/
creating: 7449757/files/rdbms/admin/
inflating: 7449757/files/rdbms/admin/i1002000.sql

oracle@apt-amd-02:~> cd 7449757/
oracle@apt-amd-02:~/7449757> pwd
/home/oracle/7449757
oracle@apt-amd-02:~/7449757> /opt/app/oracle/product/11.1/OPatch/opatch apply
Invoking OPatch 11.1.0.6.0


====


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


SQL> create spfile from pfile='/tmp/initvihaan.ora' ;

File created.


Check invalid objects
SQL> select count(*) from dba_objects where status like 'INVALID';

COUNT(*)
----------
0


Post Upgrade steps

Check status of database components
SQL>select comp_name,version, status from dba_registry;

SQL> col COMP_NAME format a40
SQL> set pagesize 200
SQL> set linesize 200
SQL> col STATUS format a15
SQL> select comp_name,version, status from dba_registry

COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ ---------------
Oracle Enterprise Manager 11.1.0.6.0 VALID
OLAP Catalog 11.1.0.6.0 VALID
Spatial 11.1.0.6.0 VALID
Oracle Multimedia 11.1.0.6.0 VALID
Oracle XML Database 11.1.0.6.0 VALID
Oracle Text 11.1.0.6.0 VALID
Oracle Data Mining 11.1.0.6.0 VALID
Oracle Expression Filter 11.1.0.6.0 VALID
Oracle Rules Manager 11.1.0.6.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
Oracle Database Catalog Views 11.1.0.6.0 VALID
Oracle Database Packages and Types 11.1.0.6.0 VALID
JServer JAVA Virtual Machine 11.1.0.6.0 VALID
Oracle XDK 11.1.0.6.0 VALID
Oracle Database Java Packages 11.1.0.6.0 VALID
OLAP Analytic Workspace 11.1.0.6.0 VALID
Oracle OLAP API 11.1.0.6.0 VALID

17 rows selected.




Copy tnsnames.ora, listener.ora, sqlnet.ora and include file from source (10g)
oracle_home to target (11g) oracle_home


===
useful link

# 429825.1 Complete Checklist for Manual Upgrades to 11gR1
# 744693.1 ORA-00001 For SYS.DIANA_VERSION$ During RDBMS Upgrade From 11.1.0.6 To 11.1.0.7
# 413671.1 Applying version 4 Time Zone Files on an Oracle Database
# 396387.1 Workarounds when Database time zone patches are not available for your patchset
# 396671.1 Usage of utltzuv2.sql before updating time zone files in Oracle 10
# 730057.1 Upgrading to 11g Fails with ORA-01722: invalid number

Thursday, May 27, 2010

couple of 2 day new books added to Oracle docs.

I am sure every one consult/refer to Oracle documentation very frequently.
In addition to 2 Day DBA, today, I have found couple of 2 day books added to the Docs. Library of 10gR2.

These books provide background information for new users:

2 Day + Real Application Clusters Guide
2 day + Performance Tuning Guide.

This is really a good idea for the new users as instead of going across many pages to find the information, now, they can simply get digest concepts in few pages.

New users, who are keen to learn about RAC and Performnace Tuning, they simply can start with those books.

http://www.oracle.com/pls/db102/homepage


Happy reading,
Ajay

Oracle11g Memory feature

Oracle is enhancing the memory management in each version starting from oracle9i. Oracle9i introduced pga_aggregate_target to dynamically manage the memory for PGA. Oracle went one step further in 10g and introduced sga_target parameter to dynamically allocate the SGA memory. Oracle introduced memory_target parameter in oracle11g and dynamically allocate both SGA and PGA memory. This is one of exciting features that takes care of SGA as well as PGA in one parameter. We let Oracle to adjust the memory as the workload needs it.

I would like to write how this memory parameter works in oracle11g. How the memory is configured on each sub component when memory_target parameter is set to non zero?

Oracle11g introduced two memory parameters(memory_target, memory_max_target). oracle has become smart as in exchanging memory between SGA and PGA. This feature helps DBA to allocate chunk of memory to a particular instance without worrying about the subcateogary allocations of different components.

Question and Answers :
1. What is the advantage of memory_target parameter? I do not see any reason to not to use this memory management parameter. If we set this parameter, then we do not need to size all other sub component of this parameter. Sub component parameters are like SGA_TARGET, PGA_AGGREGATE_TARGET etc. These memory area can be managed dynamically.
2. What is the best place to leverage this feature? If your database running in mixed load, Oracle will adjust all your memory setting accordingly to DB load. Let us say, your database is running on OLTP mode in the day time, and running batch job during the night time. DBA can not adjust the different component of the memory according to Database activity. This memory_target parameter balance the memory between shared_pool_size, redolog_buffer, large_pool_size, java_pool_size etc. This is ideal scenario to use this feature.
3. How do we enable AMM feature ? This feature can be enabled by setting memory_target and max_memory_target to non-zero values. LOCK_SGA parameter should be set to FALSE to enable this feature.

4. How do we disable AMM feature ? This feature can be disabled by setting zero for memory_target parameter. Please remember, sga_target, pga_aggregate_target parameter should be sized according to DB activities.

5. Do we need to check the Shared memory in server before implementing AMM? Yes. We need to check the current size of your shared memory file system before we set the AMM feature. On linux, we need to use the below command to check the shared memory.
[oracle@rate9812~]$ df -k /dev/shm
Filesystem 1k-blocks Used Available Use% Mounted on tmpfs
2023256 1065000 958256 53% /dev/shm
[oracle@rate9812~]$
Here the shared memory is around 2GB. So the memory target can be more than 2GB. If it is more than 2GB, the we get the below error.
ORA-00845: MEMORY_TARGET not supported on this system
6. Do we have any new data dictionary views to monitor AMM? Yes. we do have new views. Here are some. These views helps to monitor the Memory utilization.
V$MEMORY_CURRENT_RESIZE_OPS
V$MEMORY_DYNAMIC_COMPONENTS
V$MEMORY_RESIZE_OPS
V$MEMORY_TARGET_ADVICE
6. How does this feature differ from Oracle10g memory feature? This parameter covers both SGA and PGA. In Oracle10g, we use individual parameter for SGA and PGA. Now it is not required to define or size the memory for SGA and PGA seperately.
7. Do we need to take care of any memory parameters after AMM feature is enabled? Log buffer and the non-default block size caches not part of dynamic memory allocation. These memory should be taken care manually.
The new data dictionary V$MEMORY_DYNAMIC_COMPONENTS will give the list of components which is taken care by database when we enable AMM feature.

Memory Parameter in Oracle10g

Oracle10g new feature on Memory Management :

Oracle10g introduced memory management feature to make DBA's life much easy. Automatic shared memory management(ASMM) is another self management enhancement in oracle10g. In previous release of oracle, we had to manually configure the shared pool size, java pool size, large pool size and data base buffer cache. It was often challenge to optimally configure these components because sizing them too small could cause memory errors and sizing them too large could lead to waste of memory.

In oracle10g, you need to specify only the SGA_TARGET parameter, which specifies the total size of the SGA. Individual components of the SGA are automatically allocated by the database based on the workload and history information. so during the normal online operations,the buffer cache and java pool may be bigger. During the batch window, the database can automatically increase the large pool and reduce the buffer cache.

When ASMM is enabled, then the following memory pools are automatically sized:

1 .Buffer cache (DB_CACHE_SIZE)
2. Shared pool (SHARED_POOL_SIZE)
3. Large pool (LARGE_POOL_SIZE)
4. Java pool (JAVA_POOL_SIZE)


If above automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

The following pools are manually sized components and are not affected by ASMM.

1. DB_KEEP_CACHE_SIZE
2. DB_RECYCLE_CACHE_SIZE
3. DB_nK_CACHE_SIZE (non-default block size)
3. STREAMS_POOL_SIZE
4. LOG_BUFFER


For instance, SGA_TARGET is 400MB, LOG_BUFFER is 1M, DB_KEEP_CACHE_SIZE is 50M, then the memory available for automatically configured components is 349MB.

Questions and Answers :

1. How does ASMM work? ASMM automatically allocate the memory based on the work load and type of DB activities. We do not need to size the SGA parameters. The sga_target parameter will not dynamically manage all the sub component of the SGA memory. Some of the memory areas need to be sized by DBA as i explained above.

2. How do we switch the DB from non-ASMM to ASMM? Switching to ASSM can be done by changing SGA_TARGET parameter to non-zero value. STATISTICS_LEVEL should be TYPICAL or ALL. We need to allocate proper memory size for SGA_TARGET parameter. But this should not be greater then SGA_MAX_SIZE. Again, we need to reset the automatically tunable memory parameter to zero or minimum values. If we set minimum values for automatically tuned parameters, then oracle always maintain the minimum values all the time.

Here my DB is running in Non-ASMM. Let us switch this to ASMM.

SQL> select name,value from v$parameter where
2 name in('shared_pool_size','large_pool_size','java_pool_size','db_cache_size','sga_target');

NAME VALUE
------------------------------ ------------------------------
shared_pool_size 163577856
large_pool_size 4194304
java_pool_size 4194304
db_cache_size 432013312
sga_target 0

SQL> alter system set sga_target=1000M scope=both;

System altered.

SQL> alter system set shared_pool_size=500M scope=both;

System altered.

SQL> show sga

Total System Global Area 1048576000 bytes
Fixed Size 1252976 bytes
Variable Size 553648528 bytes
Database Buffers 486539264 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.

SQL> select target_size from v$sga_resize_ops
2 where component='shared pool';

TARGET_SIZE
-----------
528482304

SQL>

Now SGA parameters will be automatically tuned based on the workload and DB activities. But it always retain the minimum values, since we set shared_pool_size, large_pool_size, java_pool_size, db_cache_size set to non-zero values.
3. How do we disable ASMM? Disabling ASSM can be done by chaning SGA_TARGET parameter to zero value. But we need to set the below parameter with proper sizing when we disable ASMM
1. shared_pool_size
2. large_pool_size
3. java_pool_size
4. db_cache_size

SQL> alter system set sga_target=0 scope=both;

System altered.

4. When do we think that, ASMM should be disabled in oracle10g? We can disable ASMM if there is significant amount of memory resizing activity that cause the performance issue in database. This frequent memory resizing might happen when the environment is mixed of DSS and OLTP environment.

5. What is the best environment to enable the ASMM feature? Based on my knowledge, i would recommend to go for ASMM feature when DB is running on OLTP mode during the day time and DB is running on DSS more during the night time. In this scenario, ASMM feature will resize the memory based on the DB activities. DBA does not need to resize the memory for day time and night time differently.

Memory Parameters in oracle9i


Oracle9i new features on Memory Management :

1. Oracle9i introduced new parameter PGA_AGGREGATE_TARGET to tune portion of the memory in PGA. This new parameter dynamically adjust sort_area_size, hash_area_size, create_bitmap_area_size and bitmap_merge_area_size. The WORK_AREA_SIZE_POLICY should be set to AUTO to enable this feature.

2. Prior to oracle9i, shared pool size was static parameter. DBA has to restart the instance if shared pool size parameter needs to be changed. But in oracle9i introduced a new capability to change the shared_pool_size parameter dynamically without restarting the instance. But this parameter size should be less than or equal to SGA_MAX_SIZE.

3. Oracle9i introduced new approach to determine how the buffer cache is being using and how much memory has to be added or released from the buffer cache for optimal performance. Here are the steps to enable to feature.

a) Set the DB_CACHE_ADVICE parameter to ON. It gather statistics on the buffer cache by setting the value of this parameter. This is dynamic paramter and can be changed by using ALTER SYSTEM command.

b) Display the statistics gathered by querying the new dynamic performance view, V$DB_CACHE_ADVICE. This view contains information about the physical reads for the different cache sizes.

select id, name, block_size,buffers_for_estimate, estd_physical_reads, estd_physical_read_factor from v$db_cache_advice

c) We can change the buffer cache size based on the observation in step b.
First let me explain about each memory component in oracle. Oracle Memory is divided into two portion. One is System Global Area(SGA) and another one is Program Global Area(PGA). Let us now discuss about SGA now. Then we will talk about PGA in the later portion of this article.

System Global Area consists of following Memory component.

1. Fixed size
2. Variable size
3. DB Buffer cache
4. Redo log buffer

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning option

SQL> show sga

Total System Global Area 1654013952 bytes
Fixed Size 2103048 bytes
Variable Size 1342179576 bytes
Database Buffers 301989888 bytes
Redo Buffers 7741440 bytes
SQL>

SGA = Fixed size + Variable size + DB buffers + Redo buffers

What is Fixed size?

The size of the fixed portion is constant for a release and a platform of Oracle, that is, it cannot be changed through any means such as altering the initialization parameters. This memory varies in size from platform to platform and release to release. This is something over which we have no control and it is generally very small. Think of this area as a bootstrap section of the SGA, something Oracle uses internally to find the other bits and pieces of the SGA.

What is Variable size?

Variable size is sum of shared pool, java pool, large pool and streams pool. The streams pool is added in oracle10g.

Shared pool = library cache + dictionary cache

Variable size = Shared pool + Java pool + large pool + streams pool

Let us briefly talk about each component in variable size.

Shared pool : It consists of Library cache and Dictonary cache.

The library cache stores shared SQL, caching the parse tree and the execution plan for every unique SQL statement. If multiple applications issue the same SQL statement, each application can access the shared SQL area. This reduces the amount of memory needed and reduces the processing-time used for parsing and execution planning.

The dictionary cache stores dictionary information in order to parse the SQL statement. It also contains user information, integrity constraints defined for tables etc.

Java pool : It is used for caching parsed java programs.

Large pool : is used in shared/MTS server systems for session memory, by parallel execution for message buffers, and by RMAN backup processes for disk I/O buffers.

Stream pool : is used for streams.

What is DB buffer cache? It stores the recently executed database blocks. When other user fires the same query, oracle reads from buffer cache instead of accessing physical files.

What is Redo log buffer? It stores all the changes made in the database. It will be stored in archive log files for recovery purpose.

Please click to know more info about SGA

Program Global Area(PGA) A PGA is allocated by Oracle when a user connects to an Oracle database and a session is created. PGA contains session information and Private SQL area.

Content of PGA :

1. Session information. For instance, Logon info, session related info.

2. Private SQL area. Please remember, this is part of PGA only if the DB is dedicated server. This memory will be part of SGA if the DB is shared server. Private SQL area contains informatoin about the cursor, query execution status info, sql work area(sort area). Please click here to know more about PGA memory.

Oracle keep introducing new features in the memory management side to make DBA's life easy.

Let us talk about new oracle memory management features in each new version since from Oracle9i.