Oracle SESSIONS Parameter derived and explained.

Default parameter values are great, as long as it is possible to predict the default values, and the defaults are appropriate for the environment in which the defaults are present.  It is sometimes a challenge to remember all of the rules, and exceptions to those rules, that determine the defaults. There is an interesting formula which Oracle uses to determine the sessions parameter value which is derived from processes.

SQL> show parameter sessions

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
sessions                             integer     1522

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
processes                            integer     1000

The values shown above looks different from what I had set in the spfile i.e 1500. The reason for this is explained by Oracle Document on SESSIONS Parameter

                                        Default : Derived (1.1*PROCESSES)+5

“… You should always set this parameter explicitly to a value equivalent to your estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions.

Oracle uses the default value of this parameter as its minimum. Values between 1 and the default do not trigger errors, but Oracle ignores them and uses the default instead.”

Wait, if my PROCESSES are set to 1000 then my sessions value should be 1.1*1000+5=1105, but its showing me 1522. Turns out the formula changes for 11.2.0  and the above formula works for 10.2.0 databases.

The new formula which comes closest to the session values I have seen is

                                         Sessions= (1.5 * PROCESSES) + 22

So any value set for Sessions lower than the formulated value is ignored by Oracle and the default value will be set based on the above formula. 

ORA-02396 tips

Getting an ORA-02396 error when we set a resource profile.

Sample :

SQL> alter system set resource_limit = true;

System altered.

SQL> create profile time_out_profile limit idle_time 1;

Profile created.

SQL> create user test_idle_time identified by test profile time_out_profile;

User created.

SQL> grant connect to test_idle_time;

Grant succeeded.

SQL> connect test_idle_time/test
Connected.

SQL> set timing on

01:39:12 SQL> select username from user_users;

USERNAME
------------------------------
TEST_IDLE_TIME

-- **********************************************************
-- We have now waited longer than the idle_time (one minute)
-- **********************************************************
01:43:14 SQL> select username from user_users;

select username from user_users
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again

How do I get rid of the ORA-02396 error?  Is there an unlimited value for idle_time?

Answer:  The ORA-02396 error is thrown due to exceeding the idle_time setting on the database server.  This error happens when you have a resource profile enabled and you should enable resource_limit and create a profile whose idle_time will be limited (in minutes).
If you omit a resource profile then you will have an unlimited idle_time and you will never get an ORA-02396 error.

Note that Oracle periodically tests whether the connection is still active, and if it is inactive, you will get an ORA-02396 error, provided that the user resource profile is set as shown above.

Resolving the problem involves checking the idle time-out value at the database end (SQL*Plus resource profile), and increase this value of the idle_time.

Redo Log Count

1. Rego Log generated by day

select trunc(completion_time) rundate
,count(*)  logswitch
,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)"
from v$archived_log
group by trunc(completion_time)
order by 1;

2.Redo Log generation by hour

prompt
prompt "Morning .........."
select to_char(first_time,'DD/MON') day,
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'000')"07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'000')"08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'000')"09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'000')"10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'000')"11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'000')"12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'000')"13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'000')"14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'000')"15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'000')"16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'000')"17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'000')"18"
from v$log_history
WHERE TRUNC(FIRST_TIME) > TRUNC(SYSDATE) - 7
group by to_char(first_time,'DD/MON');
prompt
prompt
Prompt "Evening ........"
prompt
select to_char(first_time,'DD/MON') day,
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'000')"19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'000')"20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'000')"21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'000')"22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'000')"23",
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'000') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'000')"01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'000')"02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'000')"03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'000')"04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'000')"05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'000')"06"
from v$log_history
WHERE TRUNC(FIRST_TIME) > TRUNC(SYSDATE) - 7
group by to_char(first_time,'DD/MON');

Increase Redo Log Size

# First add new redo groups after checking the current redo logs and groups

SQL> select * from v$log;


    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         26 6596591616        512          2 NO  INACTIVE                699445 23-FEB-15       719471 23-FEB-15
         2          1         27 6596591616        512          2 NO  CURRENT                 719471 23-FEB-15   2.8147E+14
         3          1         12 6596591616        512          2 YES INACTIVE                699401 23-FEB-15       699404 23-FEB-15

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  /opt/app/dbname/oraredo01/dbname/redo_1a.log   NO
         1         ONLINE  /opt/app/dbname/oraredo02/dbname/redo_1b.log   NO
         2         ONLINE  /opt/app/dbname/oraredo01/dbname/redo_2a.log   NO
         2         ONLINE  /opt/app/dbname/oraredo02/dbname/redo_2b.log   NO
         3         ONLINE  /opt/app/dbname/oraredo01/dbname/redo_3a.log   NO


#### adding new group ; add the same number of groups ie 3 in this case or add more if you want to increase the number of groups
alter database add logfile group 4 ('/opt/app/dbname/oraredo01/dbname/redo_4a.log','/opt/app/dbname/oraredo02/dbname/redo_4b.log') size 2048m reuse;
alter database add logfile group 5 ('/opt/app/dbname/oraredo01/dbname/redo_5a.log','/opt/app/dbname/oraredo02/dbname/redo_5b.log') size 2048m reuse;
alter database add logfile group 6 ('/opt/app/dbname/oraredo01/dbname/redo_6a.log','/opt/app/dbname/oraredo02/dbname/redo_6b.log') size 2048m reuse;
alter database add logfile group 7 ('/opt/app/dbname/oraredo01/dbname/redo_7a.log','/opt/app/dbname/oraredo02/dbname/redo_7b.log') size 2048m reuse;

##Make the new redo log CURRENT

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE
         4 UNUSED
         5 UNUSED
         6 UNUSED
         7 UNUSED
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 ACTIVE
         3 INACTIVE
         4 ACTIVE
         5 CURRENT
         6 UNUSED
         7 UNUSED

7 rows selected.

#Drop old redo logs ; Make sure their status is inactive before dropping it.
SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;

## make some logswitches to make all the redo logs used.