ASSM in Oracle 11gR2

DB setting before changing the memory in the Database

Below Configuration is Automatic Shared Memory Management – For the SGA

If you want to exercise more direct control over the size of the SGA, you can disable automatic memory management and enable automatic shared memory management. This feature was introduced in 10g with a parameter known as SGA_TARGET. When automatic SGA memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of current workload without requiring any additional configuration.

Automatic Memory Management – For Both the SGA and Instance PGA

Beginning with Oracle Database 11g, Oracle Database can manage the SGA memory and instance PGA memory completely automatically. You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. This capability is referred to as automatic memory management. With this memory management method, the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs.

To achieve this, two new parameters have been introduced named MEMORY_MAX_TARGET and MEMORY_TARGET. To do so (on most platforms), you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET).

Add the values of PGA_AGGREGATE_TARGET and SGA_TARGET from the first section . In our case it would sum to 2746MB ( 2000+746).

ALTER SYSTEM SET memory_max_target=2746M SCOPE=SPFILE;

ALTER SYSTEM SET memory_target=2746M SCOPE=SPFILE;

ALTER SYSTEM SET sga_target=0 SCOPE=SPFILE;

ALTER SYSTEM SET pga_aggregate_target=0 SCOPE=SPFILE;

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2104479744 bytes

Fixed Size                         2229984 bytes

Variable Size                 1006635296 bytes

Database Buffers          1090519040 bytes

Redo Buffers                    5095424 bytes

Database mounted.

Database opened.

SQL> show parameter target;

NAME                                            TYPE           VALUE

------------------------------------ ----------- ------------------------------

archive_lag_target                          integer         0

db_flashback_retention_target        integer         1440

fast_start_io_target                        integer         0

fast_start_mttr_target                     integer         0

memory_max_target                       big integer   2752M

memory_target                               big integer   2752M

parallel_servers_target                    integer         32

pga_aggregate_target                    big integer   0

sga_target                                     big integer   0

Note: In case you do not specify any value for MEMORY_MAX_TARGET and only use MEMORY_TARGET then database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET

Refer the Oracle Meta link for More details  443746.1

Opening the database steps

In NOMOUNT stage

In nomount stage, as your instance is started by searching the parameter file spfile<SID>.ora, you should be able to see the background processes associated with the instance and  SGA size created.


In MOUNT stage

In mount stage you can do the following
   1. Rename data files,
   2. Enable/disable archive mode of database
   3. perform media recovery
   4. View v$log,v$logfile,v$datafile

In OPEN stage

In open mode, Oracle will check the availability of data files and online redo log files for consistency and instance recovery will happen if DB not shutdown properly

Monitor Data Guard Script


SET LINESIZE 140
SET PAGESIZE 124
SET HEAD OFF
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL RECEIVED_TIME FORMAT A20
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A20

SELECT TO_CHAR (SYSDATE, 'mm-dd-yyyy hh24:mi:ss') "Current Time" FROM DUAL;

SELECT DB_NAME,
       HOSTNAME,
       APPLIED_TIME,
       RECEIVED_TIME,
       LOG_ARCHIVED,
       LOG_APPLIED,
       LOG_ARCHIVED - LOG_APPLIED LOG_GAP,
       (CASE
           WHEN (   (    APPLIED_TIME IS NOT NULL
                     AND (LOG_ARCHIVED - LOG_APPLIED) IS NULL)
                 OR (    APPLIED_TIME IS NULL
                     AND (LOG_ARCHIVED - LOG_APPLIED) IS NOT NULL)
                 OR ( (LOG_ARCHIVED - LOG_APPLIED) > 2))
           THEN
              'Error in Log Gap'
           ELSE
              'No Log GAP'
        END)
          Status
  FROM (SELECT INSTANCE_NAME DB_NAME
          FROM GV$INSTANCE
         WHERE INST_ID = 1),
       (SELECT MAX (SEQUENCE#) LOG_ARCHIVED
          FROM V$ARCHIVED_LOG
         WHERE DEST_ID = 1 AND ARCHIVED = 'YES' AND THREAD# = 1),
       (SELECT MAX (SEQUENCE#) LOG_APPLIED
          FROM V$ARCHIVED_LOG
         WHERE DEST_ID = 2 AND APPLIED = 'YES' AND THREAD# = 1),
       (SELECT TO_CHAR (MAX (COMPLETION_TIME), 'DD-MON-YY:HH24:MI:SS') APPLIED_TIME
                       FROM V$ARCHIVED_LOG
         WHERE DEST_ID = 2 AND APPLIED = 'YES' AND THREAD# = 1),
          (SELECT TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') RECEIVED_TIME
                  FROM v$archived_log
                 WHERE sequence# =
                          (SELECT MAX (sequence#) FROM v$archived_log) AND DEST_ID = 2 AND THREAD# = 1   )  RECEIVED_TIME,
            (SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),
(INSTR(HOST_NAME,'.')-1))))) HOSTNAME

FROM V$INSTANCE)


Output looks like below

06-26-2014 13:59:10
1 row selected.

PEMSAPMS DEVDATABASE  26-JUN-14:11:02:09   26-JUN-14:13:26:07           1162        1161       1 No Log GAP      

1 row selected.

ref : http://www.oracle-ckpt.com/script-to-monitor-primary-and-standby-databases/

DB in archivelog mode

DB in archivelog mode

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

Total System Global Area  272629760 bytes
Fixed Size                   788472 bytes
Variable Size             103806984 bytes
Database Buffers          167772160 bytes
Redo Buffers                 262144 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.

Using Below SQL, you can check the size of the archive log

SELECT substr(Name,1,15) Name, (SPACE_LIMIT/1024/1024/1024) Space_Limit_GB,
       SPACE_USED/1024/1024/1024 Space_Used_GB,
       SPACE_RECLAIMABLE, NUMBER_OF_FILES
FROM V$RECOVERY_FILE_DEST;