Log On Scripts and Prevent Users Login frm another Tools

To limiting access of a user to a database based on time interval. We can prevent a specific user to access to a database between 08 and 22.
created a logon script and see how it’s working:

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
 
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 28 14:56:58 2009
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
SQL> create user kan identified by kan;
 
User created.
 
SQL> grant connect, resource to kan;
 
Grant succeeded.
 
SQL> conn kan/kan
Connected.
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
SQL> conn / as sysdba
Connected.
SQL> CREATE OR REPLACE TRIGGER limit_connection
  2     AFTER LOGON ON DATABASE
  3  BEGIN
  4     IF USER = 'KAN' THEN
  5        IF to_number(TO_CHAR (SYSDATE, 'hh24')) BETWEEN 8 AND 22
  6        THEN
  7           RAISE_APPLICATION_ERROR(-20998,' Dear user '||USER||'! You can''t login between 08 and 22');
  8        END IF;
  9     END IF;
10  END limit_connection;
11  / 
 
Trigger created.
 
SQL> select to_char(sysdate,'hh24') from dual;
 
TO
--
23
 
SQL> conn kan/kan
Connected.
SQL> select to_char(sysdate,'hh24') from dual;
 
TO
--
18
 
SQL> conn kan/kan
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20998:  Dear user KAN! You can't login between 08 and 22
ORA-06512: at line 5
 
 
Warning: You are no longer connected to ORACLE.
SQL>

To prevent users from using additional tools to connect to production database. This is the AFTER LOGON trigger create ON DATABASE as follows:

CONNECT / AS SYSDBA;
 
CREATE OR REPLACE TRIGGER block_tools_from_prod
  AFTER LOGON ON DATABASE
DECLARE
  v_prog sys.v_$session.program%TYPE;
BEGIN
  SELECT program INTO v_prog
    FROM sys.v_$session
  WHERE  audsid = USERENV('SESSIONID')
    AND  audsid != 0  -- Don't Check SYS Connections
    AND  ROWNUM = 1;  -- Parallel processes will have the same AUDSID's
 
  IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
     UPPER(v_prog) LIKE '%SQLNAV%' OR     -- SQL Navigator
     UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
     UPPER(v_prog) LIKE '%BUSOBJ%' OR   -- Business Objects
     UPPER(v_prog) LIKE '%EXCEL%'       -- MS-Excel plug-in
  THEN
     RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
  END IF;
END;
/
SHOW ERRORS

DBA_SCHEDULER_JOBs

This query will give you the names and the status of the jobs scheduled by DBMS_SCHEDULER:

SELECT job_name,enabled FROM DBA_SCHEDULER_JOBs;

sql> select job, what from dba_jobs where broken = 'Y' or failures > 0;

sql> select d.job_name,d.job_action from dba_scheduler_jobs d, sys.scheduler$_jobs where d.job_action = s.program_action and s.obj# = &myjobnum;

Then use "exec dbms_scheduler.disable" to disable each one as below:

I created a script "disablejobs.sql" as :

exec dbms_scheduler.disable('SYS.XMLDB_NFS_CLEANUP_JOB');
exec dbms_scheduler.disable('SYS.SM$CLEAN_AUTO_SPLIT_MERGE');
exec dbms_scheduler.disable('SYS.RSE$CLEAN_RECOVERABLE_SCRIPT');
exec dbms_scheduler.disable('SYS.FGR$AUTOPURGE_JOB');
exec dbms_scheduler.disable('SYS.BSLN_MAINTAIN_STATS_JOB');
exec dbms_scheduler.disable('SYS.DRA_REEVALUATE_OPEN_FAILURES');
exec dbms_scheduler.disable('SYS.HM_CREATE_OFFLINE_DICTIONARY');
exec dbms_scheduler.disable('SYS.ORA$AUTOTASK_CLEAN');
exec dbms_scheduler.disable('SYS.FILE_WATCHER');
exec dbms_scheduler.disable('SYS.PURGE_LOG');
exec dbms_scheduler.disable('SYS.MGMT_STATS_CONFIG_JOB');
exec dbms_scheduler.disable('SYS.MGMT_CONFIG_JOB');
exec dbms_scheduler.disable('SYS.RLM$SCHDNEGACTION');
exec dbms_scheduler.disable('SYS.RLM$EVTCLEANUP');



and then executed:

SQL> @disablejobs.sql

PL/SQL procedure successfully completed.

inbound connection timed out (ORA-3136)

WARNING: inbound connection timed out (ORA-3136)



The "WARNING: inbound connection timed out (ORA-3136)" in the alert log indicates that the client was not able to complete it's authentication within the period of time specified by parameter SQLNET.INBOUND_CONNECT_TIMEOUT.

You may also witness ORA-12170 without timeout error on the database server sqlnet.log file.
This entry would also have the clinet address which failed to get authenticated. Some applications or JDBC thin driver applications may not have these details.

From 10.2 onwards the default value of this parameter is 60 seconds, hence if the client is not able authenticate within 60 secs , the warning would appear in the alert log and the client connection will be terminated.

This timeout restriction was introduced to combat Denial of Service (DoS) attack whereby malicious clients attempt to flood database servers with connect requests that consumes resources.

There can be three main reasons for this error
Server gets a connection request from a malicious client which is not supposed to connect to the database , in which case the error thrown is the correct behavior. You can get the client address for which the error was thrown via sqlnet log file.
The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.
The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.

The default value of 60 seconds is good enough in most conditions for the database server to authenticate a client connection. If its taking longer period, then its worth checking all the below points before going for the workadound:

1. Check whether local connection on the database server is sucessful & quick.

2. If local connections are quick ,then check for underlying network delay with the help of your network administrator.

3. Check whether your Database performance has degraded by anyway.

4. Check alert log for any critical errors for eg, ORA-600 or ORA-7445 and get them resolved first.
These critical errors might have triggered the slowness of the database server.



As a workaround to avoid only this warning messages, you can set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername
to the value more than 60.

In server side sqlnet.ora file add SQLNET.INBOUND_CONNECT_TIMEOUT


SQLNET.INBOUND_CONNECT_TIMEOUT = 120In listener.ora file INBOUND_CONNECT_TIMEOUT_listenername


INBOUND_CONNECT_TIMEOUT_LISTENER = 110



From Oracle version 10.2.0.3 onwards the default value of INBOUND_CONNECT_TIMEOUT_
is 60 seconds. For previous releases it is zero by default.

Resizing / Recreating Online Redo Log Files

One of the best ways I have found to resize or recreate online redo log files and keep the current sequence is to perform it online. In this example, we will resize all online redo logs from 100MB to 250MB while the database is running and use SQL*Plus to drop/recreate them in stages.
Before looking at the tasks involved to perform the resize, let's look at the current online redo log groups and their sizes:

SQL> SELECT a.group#, a.member, b.bytes 2 FROM v$logfile a, v$log b WHERE a.group# = b.group#; GROUP# MEMBER BYTES ---------- ---------------------------------------- ------------ 1 /u03/app/oradata/ORA920/redo_g01a.log 104,857,600 1 /u04/app/oradata/ORA920/redo_g01b.log 104,857,600 1 /u05/app/oradata/ORA920/redo_g01c.log 104,857,600 2 /u03/app/oradata/ORA920/redo_g02a.log 104,857,600 2 /u04/app/oradata/ORA920/redo_g02b.log 104,857,600 2 /u05/app/oradata/ORA920/redo_g02c.log 104,857,600 3 /u03/app/oradata/ORA920/redo_g03a.log 104,857,600 3 /u04/app/oradata/ORA920/redo_g03b.log 104,857,600 3 /u05/app/oradata/ORA920/redo_g03c.log 104,857,600 9 rows selected.

Now let's take a look at the steps involved to resize / recreate all online redo log groups:

1. Make the last redo log CURRENT
   Force a log switch until the last redo log is marked "CURRENT" by issuing the following command:
   

   SQL> select group#, status from v$log; GROUP# STATUS ---------- ---------------- 1 CURRENT 2 INACTIVE 3 INACTIVE SQL> alter system switch logfile; SQL> alter system switch logfile; SQL> select group#, status from v$log; GROUP# STATUS ---------- ---------------- 1 INACTIVE 2 INACTIVE 3 CURRENT

2. Drop first redo log
   After making the last online redo log file the CURRENT one, drop the first online redo log:
   

   SQL> alter database drop logfile group 1; Database altered.

   As a DBA, you should already be aware that if you are going to drop a logfile group, it cannot be the current logfile group. I have run into instances; however, where attempting to drop the logfile group resulted in the following error as a result of the logfile group having an active status: SQL> ALTER DATABASE DROP LOGFILE GROUP 1; ALTER DATABASE DROP LOGFILE GROUP 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance ORA920 (thread 1) ORA-00312: online log 1 thread 1: '<file_name>' Easy problem to resolve. Simply perform a checkpoint on the database: SQL> ALTER SYSTEM CHECKPOINT GLOBAL; System altered. SQL> ALTER DATABASE DROP LOGFILE GROUP 1; Database altered.

3. Re-create dropped online redo log group
   Re-create the dropped redo log group with different size (if desired):
   

   SQL> alter database add logfile group 1 ( 2 '/u03/app/oradata/ORA920/redo_g01a.log', 3 '/u04/app/oradata/ORA920/redo_g01b.log', 4 '/u05/app/oradata/ORA920/redo_g01c.log') size 250m reuse; Database altered.

4. Force another log switch
   After re-creating the online redo log group, force a log switch. The online redo log group just created should become the "CURRENT" one:
   

   SQL> select group#, status from v$log; GROUP# STATUS ---------- ---------------- 1 UNUSED 2 INACTIVE 3 CURRENT SQL> alter system switch logfile; SQL> select group#, status from v$log; GROUP# STATUS ---------- ---------------- 1 CURRENT 2 INACTIVE 3 ACTIVE

5. Loop back to Step 2 until all logs are rebuilt
   After re-creating an online redo log group, continue to re-create (or resize) all online redo log groups until all of them are rebuilt.

After rebuilding (resizing) all online redo log groups, here is a snapshot of all physical files:

SQL> SELECT a.group#, a.member, b.bytes 2 FROM v$logfile a, v$log b WHERE a.group# = b.group#; GROUP# MEMBER BYTES ---------- ---------------------------------------- ------------ 1 /u03/app/oradata/ORA920/redo_g01a.log 262,144,000 1 /u04/app/oradata/ORA920/redo_g01b.log 262,144,000 1 /u05/app/oradata/ORA920/redo_g01c.log 262,144,000 2 /u03/app/oradata/ORA920/redo_g02a.log 262,144,000 2 /u04/app/oradata/ORA920/redo_g02b.log 262,144,000 2 /u05/app/oradata/ORA920/redo_g02c.log 262,144,000 3 /u03/app/oradata/ORA920/redo_g03a.log 262,144,000 3 /u04/app/oradata/ORA920/redo_g03b.log 262,144,000 3 /u05/app/oradata/ORA920/redo_g03c.log 262,144,000 9 rows selected.

Configuring Kernel Parameters For Oracle 10g Installation

This section documents the checks and modifications to the Linux kernel that should be made by the DBA to support Oracle Database 10g. Before detailing these individual kernel parameters, it is important to fully understand the key kernel components that are used to support the Oracle Database environment.



The kernel parameters and shell limits presented in this section are recommended values only as documented by Oracle. For production database systems, Oracle recommends that we tune these values to optimize the performance of the system.


Verify that the kernel parameters shown in this section are set to values greater than or equal to the recommended values.


Shared Memory : Shared memory allows processes to access common structures and data by placing them in a shared memory segment. This is the fastest form of Inter-Process Communications (IPC) available - mainly due to the fact that no kernel involvement occurs when data is being passed between the processes. Data does not need to be copied between processes .


Oracle makes use of shared memory for its Shared Global Area (SGA) which is an area of memory that is shared by all Oracle backup and foreground processes. Adequate sizing of the SGA is critical to Oracle performance since it is responsible for holding the database buffer cache, shared SQL, access paths, and so much more.


To determine all current shared memory limits, use the following :


# ipcs -lm
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 4194303
max total shared memory (kbytes) = 1073741824
min seg size (bytes) = 1


The following list describes the kernel parameters that can be used to change the shared memory configuration for the server:


1.) shmmax - Defines the maximum size (in bytes) for a shared memory segment. The Oracle SGA is comprised of shared memory and it is possible that incorrectly setting shmmax could limit the size of the SGA. When setting shmmax, keep in mind that the size of the SGA should fit within one shared memory segment. An inadequate shmmax setting could result in the following:
ORA-27123: unable to attach to shared memory segment


We can determine the value of shmmax by performing the following :


# cat /proc/sys/kernel/shmmax
4294967295


For most Linux systems, the default value for shmmax is 32MB. This size is often too small to configure the Oracle SGA. The default value for shmmax in CentOS 5 is 4GB which is more than enough for the Oracle configuration. Note that this value of 4GB is not the "normal" default value for shmmax in a Linux environment inserts the following two entries in the file /etc/sysctl.conf:


# Controls the maximum shared segment size, in bytes
kernel.shmmax = 4294967295


2.) shmmni : This kernel parameter is used to set the maximum number of shared memory segments system wide. The default value for this parameter is 4096. This value is sufficient and typically does not need to be changed. We can determine the value of shmmni by performing the following:


# cat /proc/sys/kernel/shmmni
4096


3.) shmall : This parameter controls the total amount of shared memory (in pages) that can be used at one time on the system. The value of this parameter should always be at least: We can determine the value of shmall by performing the following :


# cat /proc/sys/kernel/shmall
268435456


For most Linux systems, the default value for shmall is 2097152 and is adequate for most configurations. The default value for shmall in CentOS 5 is 268435456 (see above) which is more than enough for the Oracle configuration described in this article. Note that this value of 268435456 is not the "normal" default value for shmall in a Linux environment , inserts the following two entries in the file /etc/sysctl.conf:


# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 268435456


4.) shmmin : This parameter controls the minimum size (in bytes) for a shared memory segment. The default value for shmmin is 1 and is adequate for the Oracle configuration described in this article.We can determine the value of shmmin by performing the following:


# ipcs -lm | grep "min seg size"
min seg size (bytes) = 1


Semaphores :
After the DBA has configured the shared memory settings, it is time to take care of configuring the semaphores. The best way to describe a semaphore is as a counter that is used to provide synchronization between processes (or threads within a process) for shared resources like shared memory. Semaphore sets are supported in System V where each one is a counting semaphore. When an application requests semaphores, it does so using "sets". To determine all current semaphore limits, use the following:


# ipcs -ls
------ Semaphore Limits --------
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 32
semaphore max value = 32767


We can also use the following command:
# cat /proc/sys/kernel/sem
250 32000 32 128


The following list describes the kernel parameters that can be used to change the semaphore configuration for the server:


i.) semmsl - This kernel parameter is used to control the maximum number of semaphores per semaphore set. Oracle recommends setting semmsl to the largest PROCESS instance parameter setting in the init.ora file for all databases on the Linux system plus 10. Also, Oracle recommends setting the semmsl to a value of no less than 100.


ii.) semmni - This kernel parameter is used to control the maximum number of semaphore sets in the entire Linux system. Oracle recommends setting semmni to a value of no less than 100.


iii.) semmns - This kernel parameter is used to control the maximum number of semaphores (not semaphore sets) in the entire Linux system. Oracle recommends setting the semmns to the sum of the PROCESSES instance parameter setting for each database on the system, adding the largest PROCESSES twice, and then finally adding 10 for each Oracle database on the system. Use the following calculation to determine the maximum number of semaphores that can be allocated on a Linux system. It will be the lesser of:
SEMMNS -or- (SEMMSL * SEMMNI)


iv.) semopm - This kernel parameter is used to control the number of semaphore operations that can be performed per semop system call. The semop system call (function) provides the ability to do operations for multiple semaphores with one semop system call. A semaphore set can have the maximum number of semmslsemaphores per semaphore set and is therefore recommended to set semopm equal to semmsl in some situations. Oracle recommends setting the semopm to a value of no less than 100.


File Handles :
When configuring the Linux server, it is critical to ensure that the maximum number of file handles is large enough. The setting for file handles denotes the number of open files that you can have on the Linux system. Use the following command to determine the maximum number of file handles for the entire system:


# cat /proc/sys/fs/file-max
102312


Oracle recommends that the file handles for the entire system be set to at least 65536. We can query the current usage of file handles by using the following :


# cat /proc/sys/fs/file-nr
3072 0 102312


The file-nr file displays three parameters:
• Total allocated file handles
• Currently used file handles
• Maximum file handles that can be allocated


If we need to increase the value in /proc/sys/fs/file-max, then make sure that the ulimit is set properly. Usually for Linux 2.4 and 2.6 it is set to unlimited. Verify theulimit setting my issuing the ulimit command :


# ulimit
unlimited


IP Local Port Range :
Oracle strongly recommends to set the local port range ip_local_port_range for outgoing messages to "1024 65000" which is needed for systems with high-usage. This kernel parameter defines the local port range for TCP and UDP traffic to choose from.
The default value for ip_local_port_range is ports 32768 through 61000 which is inadequate for a successful Oracle configuration. Use the following command to determine the value of ip_local_port_range:


# cat /proc/sys/net/ipv4/ip_local_port_range
32768 61000


Networking Settings :
With Oracle 9.2.0.1 and later, Oracle makes use of UDP as the default protocol on Linux for inter-process communication (IPC), such as Cache Fusion and Cluster Manager buffer transfers between instances within the RAC cluster.


Oracle strongly suggests to adjust the default and maximum receive buffer size (SO_RCVBUF socket option) to 1MB and the default and maximum send buffer size (SO_SNDBUF socket option) to 256KB.The receive buffers are used by TCP and UDP to hold received data until it is read by the application. The receive buffer cannot overflow because the peer is not allowed to send data beyond the buffer size window.


This means that datagrams will be discarded if they don't fit in the socket receive buffer, potentially causing the sender to overwhelm the receiver. Use the following commands to determine the current buffer size (in bytes) of each of the IPC networking parameters:


# cat /proc/sys/net/core/rmem_default
109568


# cat /proc/sys/net/core/rmem_max
131071


# cat /proc/sys/net/core/wmem_default
109568


# cat /proc/sys/net/core/wmem_max
131071


Setting Kernel Parameters for Oracle
If the value of any kernel parameter is different to the recommended value, they will need to be modified. For this article, I identified and provide the following values that will need to be added to the /etc/sysctl.conf file which is used during the boot process.


kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144


After adding the above lines to the /etc/sysctl.conf file, they persist each time the system reboots. If we would like to make these kernel parameter value changes to the current system without having to first reboot, enter the following command:


# /sbin/sysctl –p

SHMMAX and SHMALL for Oracle in Linux

SHMMAX and SHMALL are two key shared memory parameters that directly impact’s the way by which Oracle creates an SGA. Shared memory is nothing but part of Unix IPC System (Inter Process Communication) maintained by kernel where multiple processes share a single chunk of memory to communicate with each other.

While trying to create an SGA during a database startup, Oracle chooses from one of the 3 memory management models a) one-segment or b) contiguous-multi segment or c) non-contiguous multi segment. Adoption of any of these models is dependent on the size of SGA and values defined for the shared memory parameters in the linux kernel, most importantly SHMMAX.

So what are these parameters - SHMMAX and SHMALL?


SHMMAX is the maximum size of a single shared memory segment set in “bytes”.

silicon:~ #  cat /proc/sys/kernel/shmmax

536870912

SHMALL is the total size of Shared Memory Segments System wide set in “pages”.

silicon:~ #  cat /proc/sys/kernel/shmall

1415577

The key thing to note here is the value of SHMMAX is set in "bytes" but the value of SHMMALL is set in "pages".

What’s the optimal value for SHMALL?

As SHMALL is the total size of Shard Memory Segments System wide, it should always be less than the Physical Memory on the System and should also be less than sum of SGA’s of all the oracle databases on the server. Once this value (sum of SGA’s) hit the limit, i.e. the value of shmall, then any attempt to start a new database (or even an existing database with a resized SGA) will result in an “out of memory” error (below). This is because there won’t be any more shared memory segments that Linux can allocate for SGA.

ORA-27102: out of memory

Linux-x86_64 Error: 28: No space left on device.

So above can happen for two reasons. Either the value of shmall is not set to an optimal value or you have reached the threshold on this server.

Setting the value for SHMALL to optimal is straight forward. All you want to know is how much “Physical Memory” (excluding Cache/Swap) you have on the system and how much of it should be set aside for Linux Kernel and to be dedicated to Oracle Databases.

For e.g. Let say the Physical Memory of a system is 6GB, out of which you want to set aside 1GB for Linux Kernel for OS Operations and dedicate the rest of 5GB to Oracle Databases. Then here’s how you will get the value for SHMALL.

Convert this 5GB to bytes and divide by page size. Remember SHMALL should be set in “pages” not “bytes”.

So here goes the calculation.

Determine Page Size first, can be done in two ways. In my case it’s 4096 and that’s the recommended and default in most cases which you can keep the same. 

silicon:~ # getconf PAGE_SIZE

4096

or

silicon:~ # cat /proc/sys/kernel/shmmni

4096

Convert 5GB into bytes and divide by page size, I used the linux calc to do the math.

silicon:~ # echo "( 5 * 1024 * 1024 * 1024 ) / 4096 " | bc -l

1310720.00000000000000000000

Reset shmall and load it dynamically into kernel

silicon:~ # echo "1310720" > /proc/sys/kernel/shmall

silicon:~ # sysctl –p

Verify if the value has been taken into effect.

silicon:~ # sysctl -a | grep shmall

kernel.shmall = 1310720

Another way to look this up is

silicon:~ # ipcs -lm

------ Shared Memory Limits --------

max number of segments = 4096                          /* SHMMNI  */

max seg size (kbytes) = 524288                  /* SHMMAX  */

max total shared memory (kbytes) = 5242880      /* SHMALL  */

min seg size (bytes) = 1

To keep the value effective after every reboot, add the following line to /etc/sysctl.conf

echo “kernel.shmall = 1310720” >> /etc/sysctl.conf

Also verify if sysctl.conf is enabled or will be read during boot.

silicon:~ # chkconfig boot.sysctl

boot.sysctl  on

If returns “off”, means it’s disabled. Turn it on by running

silicon:~ # chkconfig boot.sysctl on

boot.sysctl  on

What’s the optimal value for SHMMAX?

Oracle makes use of one of the 3 memory management models to create the SGA during database startup and it does this in following sequence. First Oracle attempts to use the one-segment model and if this fails, it proceeds with the next one which's the contiguous multi-segment model and if that fails too, it goes with the last option which is the non-contiguous multi-segment model.

So during startup it looks for shmmax parameter and compares it with the initialization parameter *.sga_target. If shmmax > *.sga_target, then oracle goes with one-segment model approach where the entire SGA is created within a single shared memory segment.

But the above attempt (one-segment) fails if SGA size otherwise *.sga_target  > shmmax, then Oracle proceeds with the 2^nd option – contiguous multi-segment model. Contiguous allocations, as the name indicates are a set of shared memory segments which are contiguous within the memory and if it can find such a set of segments then entire SGA is created to fit in within this set. 


But if cannot find a set of contiguous allocations then last of the 3 option’s is chosen – non-contiguous multi-segment allocation and in this Oracle has to grab the free memory segments fragmented between used spaces.

So let’s say if you know the max size of SGA of any database on the server stays below 1GB, you can set shmmax to 1 GB. But say if you have SGA sizes for different databases spread between 512MB to 2GB, then set shmmax to 2Gigs and so on.

Like SHMALL, SHMMAX can be defined by one of these methods..

Dynamically reset and reload it to the kernel..

silicon:~ #  echo "536870912" >  /proc/sys/kernel/shmmax

silicon:~ #  sysctl –p           -- Dynamically reload the parameters.

Or use sysctl to reload and reset ..

silicon:~ #  sysctl -w kernel.shmmax=536870912

To permanently set so it’s effective in reboots…

silicon:~ #  echo "kernel.shmmax=536870912" >>  /etc/systctl.conf

Install doc for 11g recommends the value of shmmax to be set to "4GB – 1byte" or half the size of physical memory whichever is lower. I believe “4GB – 1byte” is related to the limitation on the 32 bit (x86) systems where the virtual address space for a user process can only be little less than 4GB. As there’s no such limitation for 64bit (x86_64) bit systems, you can define SGA’s larger than 4 Gig’s. But idea here is to let Oracle use the efficient one-segment model and for this shmmax should stay higher than SGA size of any individual database on the system.

mknod operation

Implement the mknod operation using the following example :

File Name: exppipe.sh
#!/bin/sh
. $HOME/.bash_profile
cd /home/oracle/mknode/
mknod exp_pipe p
gzip -cNf exp_data.dmp.gz &
exp demo/demo file=exp_pipe log=exp_data.log owner=demo statistics=none
rm -f exp_pipe p
fi

_kgl_large_heap_warning_threshold -- oracle 10.2 hidden parameter

Today in my environment i faced the below error message

Memory Notification: Library Cache Object loaded into SGA
Heap size 9940K
exceeds notification threshold (8192K)

for that i found the solution

In 10.2.0.2, the threshold was increased to 50MB after regression tests, so this should be a reasonable and recommended value.

So, you may try to increase the parameter  _kgl_large_heap_warning_threshold= 52428800

SQL> alter system set "_kgl_large_heap_warning_threshold"=52428800 scope=spfile ;
SQL> shutdown immediate
SQL> startup

Strongly recommend you to upgrade the database to the latest patchset which is 10.2.0.5,
where you can avoid such problems.

CONTROLFILE AUTOBACKUP should be TURNED ON??

RMAN is one of the very useful utility provided by Oracle for backup and recovery Purpose. Oracle online backups were introduced with Version 6, where tablespace must be kept in backup mode in order to take online backups.

RMAN was first introduced with Version 8 through which database server database can be backup. Since then, Oracle has enhanced RMAN features drastically by every release/version. I am not going to discuss neither the RMAN history nor the backup.

I would like to mention here, how enabling few default parameters of RMAN can save the life of dba significantly.

RMAN has following default parameters and its default values:

RMAN> show all;

using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO /app/oracle/dbs/snapcf_EHEALTH.f'; # default
I would be talking the benefits of CONTROLFILE AUTOBACKUP.

By default CONTROLFILE AUTOBACKUP is OFF. I would strongly recommend enabling CONTROLFILE AUTOBACKUP ON.

I remember in my early days of DBA job, a lot of notes/articles and many people use to suggest backup the controlfile immediately after any maintenance on the tablespaces, which was a good suggestion.

RMAN just takes away this head ache of backing controlfile after any maintenance on tablespace, when you turn ON CONTROLFILE AUTOBACKUP feature.

Benefits:

With a control file autobackup, RMAN can recover the database even if the current control file, recovery catalog, and server parameter file are inaccessible.

A control file autobackup lets you restore the RMAN repository contained in the control file when the control file is lost and you have no recovery catalog. You do not need a recovery catalog or target database control file to restore the control file autobackup.

Whenever you add a datafile/s, resize, increase/decrease the size of datafile/s or etc, controlfile is automatically backed up.

If CONFIGURE CONTROLFILE AUTOBACKUP is ON, then RMAN automatically backs up the control file and the current server parameter file (if used to start up the database) in one of two circumstances: when a successful backup must be recorded in the RMAN repository, and when a structural change to the database affects the contents of the control file which therefore must be backed up.


Conclusion:
You can turn the autobackup feature on or off by running the following commands through RMAN utility:CONFIGURE CONTROLFILE AUTOBACKUP ON;CONFIGURE CONTROLFILE AUTOBACKUP OFF;

How to show user profile and change its limit ?

1. First see the profile which are using my user ;
SELECT *
FROM dba_users
WHERE USERNAME=’ISLBAS’;
2. For showing the specific profiles property
select * from dba_profiles
where profile=’DEFAULT’
–profile attribute
select * from profile$
—profile names
select * from profname$
SELECT name, lcount
FROM user$
WHERE lcount <> 0;
3.In order to track password related profile limits, Oracle stores the history
of passwords for a user in user_history$.
select * from user_history$
4. To change profile limit use
ALTER PROFILE default limit
failed_login_attempts UNLIMITED;

CREATE USER dcranney
    IDENTIFIED BY bedrock
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp_ts
    QUOTA unlimited ON users;






Listing All System Privilege Grants

The 
following query returns all system privilege grants made to roles and users:

SELECT * FROM DBA_SYS_PRIVS;

GRANTEE            PRIVILEGE                         ADM
--------------     --------------------------------- ---
SECURITY_ADMIN     ALTER PROFILE                     YES
SECURITY_ADMIN     ALTER USER                        YES
SECURITY_ADMIN     AUDIT ANY                         YES
SECURITY_ADMIN     AUDIT SYSTEM                      YES
SECURITY_ADMIN     BECOME USER                       YES
SECURITY_ADMIN     CREATE PROFILE                    YES
SECURITY_ADMIN     CREATE ROLE                       YES
SECURITY_ADMIN     CREATE USER                       YES
SECURITY_ADMIN     DROP ANY ROLE                     YES
SECURITY_ADMIN     DROP PROFILE                      YES
SECURITY_ADMIN     DROP USER                         YES
SECURITY_ADMIN     GRANT ANY ROLE                    YES
SWILLIAMS          CREATE SESSION                    NO
JWARD              CREATE SESSION                    NO






Listing All Role Grants

The 
following query returns all the roles granted to users and other roles:

SELECT * FROM DBA_ROLE_PRIVS;

GRANTEE            GRANTED_ROLE                         ADM
------------------ ------------------------------------ ---
SWILLIAMS          SECURITY_ADMIN                       NO






Listing Object Privileges Granted to a User

The following query returns all object privileges (not including 
column-specific privileges) granted to the specified user:

SELECT TABLE_NAME, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS
    WHERE GRANTEE = 'JWARD';

TABLE_NAME   PRIVILEGE    GRANTABLE
-----------  ------------ ----------
EMP          SELECT       NO
EMP          DELETE       NO


To list all the column-specific privileges that have been granted, use the 
following query:

SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE
    FROM DBA_COL_PRIVS;

GRANTEE      TABLE_NAME     COLUMN_NAME      PRIVILEGE
-----------  ------------   -------------    --------------
SWILLIAMS    EMP            ENAME            INSERT
SWILLIAMS    EMP            JOB              INSERT
JWARD        EMP            NAME             INSERT
JWARD        EMP            JOB              INSERT






Listing the Current Privilege Domain of Your Session

The following query lists all roles 
currently enabled for the issuer:

SELECT * FROM SESSION_ROLES;


If swilliams has enabled the security_admin role 
and issues this query, then Oracle Database returns the following 
information:

ROLE
------------------------------
SECURITY_ADMIN


The following query lists all system privileges currently available in the 
security domain of the issuer, both from explicit privilege grants and from 
enabled roles:

SELECT * FROM SESSION_PRIVS;


If swilliams has the security_admin role enabled 
and issues this query, then Oracle returns the following results:

PRIVILEGE
----------------------------------------
AUDIT SYSTEM
CREATE SESSION
CREATE USER
BECOME USER
ALTER USER
DROP USER
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
AUDIT ANY
CREATE PROFILE
ALTER PROFILE
DROP PROFILE


If the security_admin role is disabled for 
swilliams, then the first query would return no rows, while the 
second query would only return a row for the CREATE SESSION 
privilege grant.




Listing Roles of the Database

The DBA_ROLES data 
dictionary view can be used to list all roles of a database and the 
authentication used for each role. For example, the following query lists all 
the roles in the database:

SELECT * FROM DBA_ROLES;

ROLE                  PASSWORD
----------------      --------
CONNECT               NO
RESOURCE              NO
DBA                   NO
SECURITY_ADMIN        YES






Listing Information About the Privilege Domains of Roles

The ROLE_ROLE_PRIVS, 
ROLE_SYS_PRIVS, and ROLE_TAB_PRIVS data dictionary 
views contain information on the privilege domains of roles. For example, the 
following query lists all the roles granted to the system_admin 
role:

SELECT GRANTED_ROLE, ADMIN_OPTION
   FROM ROLE_ROLE_PRIVS
   WHERE ROLE = 'SYSTEM_ADMIN';

GRANTED_ROLE              ADM
----------------          ----
SECURITY_ADMIN            NO


The following query lists all the system privileges granted to the 
security_admin role:

SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'SECURITY_ADMIN';

ROLE                    PRIVILEGE                      ADM
----------------------- -----------------------------  ---
SECURITY_ADMIN           ALTER PROFILE                 YES
SECURITY_ADMIN           ALTER USER                    YES
SECURITY_ADMIN           AUDIT ANY                     YES
SECURITY_ADMIN           AUDIT SYSTEM                  YES
SECURITY_ADMIN           BECOME USER                   YES
SECURITY_ADMIN           CREATE PROFILE                YES
SECURITY_ADMIN           CREATE ROLE                   YES
SECURITY_ADMIN           CREATE USER                   YES
SECURITY_ADMIN           DROP ANY ROLE                 YES
SECURITY_ADMIN           DROP PROFILE                  YES
SECURITY_ADMIN           DROP USER                     YES
SECURITY_ADMIN           GRANT ANY ROLE                YES


The following query lists all the object privileges granted to the 
security_admin role:

SELECT TABLE_NAME, PRIVILEGE FROM ROLE_TAB_PRIVS
    WHERE ROLE = 'SECURITY_ADMIN';

TABLE_NAME                     PRIVILEGE
---------------------------    ----------------
AUD$                           DELETE
AUD$                           SELECT

23.717000 90.367000

change archivelog all crosscheck in rman

This command is very useful, where your RMAN does not take the sequence number into consider

Here is a simple example script to see whether all of the registered archived redo logs still exist; if not, RMAN changes their status in the metadata to expired:
crosscheck archivelog all;

allocate channel for maintenance type disk;
change archivelog all crosscheck;
release channel;

ORA-01555 Error - Why we are receiving ?

Undo data is stored in the undo tablespace, the tablespace nominated by the UNDO_TABLESPACE instance parameter. This tablespace should be sized according to the rate at which undo data is being generated and the length of the queries running in the database.

But even if the undo tablespace is inadequately sized and therefore comes under space pressure, automatic undo retention tuning will tend to minimize problems.

Undo data generated by a transaction must always be kept until the transaction commits. This is an absolute; under no circumstances will Oracle ever overwrite undo data that might be needed to rollback a transaction. This data is known as “active” undo data.

Once a transaction has committed, its undo data is no longer active, but it could still be needed to support long running queires that began before the transaction. Data that may be needed for this purpose is known as “unexpired” undo data.

 ”Expired” undo is data that is no longer needed either to rollback a transaction or to be read by queries.. Active undo will never be overwritten, and ideally expired undo can be safely overwritten at any time.

Unexpired undo can be overwritten, but at the risk of causing queries to fail with the “ORA-01555 : Snapshot too old” error.

The point at which data transitions from “unexpired” to “expired” is controlled by the instance parameter UNDO_RETENTION.

With release 9i of the Oracle database,Oracle would overwrite any expired undo. This meant that if the UNDO_RETENTION parameter were not set appropriately or (not set at all, in which case it defaults to 9oo seconds), there would be great danger of ORA-1555 errors.

Oracle 10g relaeas of the database effectively ignores the UNDO_RETENTION parameter. It will always overwrite the oldest bit of undo data.This means that is a sense there is no longer any difference between expired and unexpired undo and that the UNDO_RETENTION instance parameter is redundant, because undo retention is automatically tuned for the longest possible query.

To monitor the automatic undo retention tuning ,query V$UNDOSTAT this query will show,

SQL> SELECT BEGIN_TIME,END_TIME,TUNED_UNDORETENTION,ACTIVEBLKS,UNEXPIREDBLKS,EXP IREDBLKS FROM V$UNDOSTAT;

Iin fivteen minutes intervals, how old(in seconds) the oldest block of inactive undo data was. provided that no query started earlier than that, you will never receive a snapshot too old error. The larger the undo tablespace is and the less the transaction workload is, the further back the TUNED_UNDORETENTION will be.

Optimize Oracle UNDO Parameters 
--------------------------------------------------------------------------------
Starting in Oracle9i, rollback segments are re-named undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was made available for overlaying. 


Best of all, automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing "snapshot too old" errors on  long running queries. 


This is done by setting the UNDO_RETENTION parameter.  The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time. 
Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy.  All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO. 


However it is worth to tune the following important parameters 


The size of the UNDO tablespace 
The UNDO_RETENTION parameter 
Calculate UNDO_RETENTION  for given UNDO Tabespace 


You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the 


database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The 


following query will help you to optimize the UNDO_RETENTION parameter: 


 OPTIMAL UNDO RETENTION = ACTUAL undo size / db_block_size * undo_block_per_sec.

Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time! 




Actual Undo Size 


SELECT SUM(a.bytes) "UNDO_SIZE"
  FROM v$datafile a,
       v$tablespace b,
       dba_tablespaces c
 WHERE c.contents = 'UNDO'
   AND c.status = 'ONLINE'
   AND b.name = c.tablespace_name
   AND a.ts# = b.ts#; 


 UNDO_SIZE
----------
  209715200 




Undo Blocks per Second 


SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
      "UNDO_BLOCK_PER_SEC"
  FROM v$undostat; 


UNDO_BLOCK_PER_SEC
------------------
        3.12166667 


DB Block Size 


SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
 FROM v$parameter
WHERE name = 'db_block_size'; 




DB_BLOCK_SIZE [Byte]
--------------------
                4096 


Optimal Undo Retention 


209'715'200 / (3.12166667 * 4'096) = 16'401 [Sec]