Block Developers Tools on Production Database

I have come across a very good post on how to prevent users from using additional tools to connect to production database. This can also allow us to connect for specific users only. This can be done by creating the a AFTER LOGON trigger create ON DATABASE .Below is the script 

c:\> sqlplus / as sysdba

SQL> 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;
/

Reference : http://psoug.org/snippet.htm/Block_TOAD_and_other_tools_516.htm


Enjoy    :-)

Determine OS block size for Linux and Windows

A block is a uniformly sized unit of data storage for a filesystem. Block size can be an important consideration when setting up a system that is designed for maximum performance. 

Block size in Linux :  If we want to confirm the block size of any filesystem of Ubuntu or any other Linux OS, tune2fs command is here to help:

ubuntu# tune2fs -l /dev/sda1 | grep Block
Block count:              4980736
Block size:                 4096
Blocks per group:       32768

From this example, we can see that the default block size for the filesystem on  /dev/sda1 partition is 4096 bytes, or 4k. That's the default block size for ext3 filesystem.

OS block size in Solaris : 

$perl -e '$a=(stat ".")[11]; print $a'
8192

or 
$df -g | grep 'block size' 

Block size in Window Machine  : If  OS  is using ntfs system use the below command  :

C:\>fsutil fsinfo ntfsinfo D:
NTFS Volume Serial Number :        0x7a141d52141d12ad
Version :                                          3.1
Number Sectors :                            0x00000000036b17d0
Total Clusters :                                0x00000000006d62fa
Free Clusters  :                               0x00000000001ed190
Total Reserved :                             0x0000000000000170
Bytes Per Sector  :                         512
Bytes Per Cluster :                     4096   <<===   (block size)
Bytes Per FileRecord Segment       : 1024
Clusters Per FileRecord Segment   : 0
Mft Valid Data Length :                    0x0000000005b64000
Mft Start Lcn  :                                 0x00000000000c0000
Mft2 Start Lcn :                                0x000000000036b17d
Mft Zone Start :                                0x000000000043c9c0
Mft Zone End   :                               0x000000000044b460

or we can also find the block size as : 

--> Go to "My Computer"   --> manage --> Disk Defragmenter --> select any disk -->  click on "analyze" button , then it will present us a report and in that report "Cluster Size" represents the  OS "Block Size".

Enjoy   :-)

When To Use Database Resident Connection Pooling

Database resident connection pooling is useful when multiple clients access the database and when any of the following apply  :

• A large number of client connections need to be supported with minimum memory usage.
• The client applications are similar and can share or reuse sessions.
• Applications are similar if they connect with the same database credentials and use the same schema.
• The client applications acquire a database connection, work on it for a relatively short duration, and then release it.
• Session affinity is not required across client requests.
• There are multiple processes and multiple hosts on the client side.

Advantages of Database Resident Connection Pooling : Using database resident connection pooling provides the following advantages :

• Enables resource sharing among multiple middle-tier client applications.
• Improves scalability of databases and applications by reducing resource usage.
• Provides pooling for architectures with multi-process, single-threaded application servers.

Enjoy   :-) 

Suspending and Resuming a Database

The ALTER SYSTEM SUSPEND  statement halts  all  input  and  output  (I/O)  to  datafiles (file header and file data)  and  control files. The  suspended  state  lets  us  back  up  a database  without  I/O interference. When  the database  is suspended  all  preexisting I/O operations are  allowed  to complete and any  new  database  accesses  are  placed  in a  queued state. The  suspend   command is  not  specific  to  an  instance. In  an  Oracle  Real  Application  Clusters  environment, when  we issue the  suspend command  on  one  system,  internal  locking  mechanisms  propagate  the  halt request across  instances, thereby  quiescing  all active   instances  in  a  given cluster. However, if someone starts  a  new instance another instance is being suspended, the new instance will not be suspended .

Using  the  ALTER SYSTEM RESUME  statement to resume normal database operations. The SUSPEND and  RESUME commands  can  be  issued  from  different  instances. For example, if instances 1, 2, and 3 are  running, and  we  issue  an  ALTER SYSTEM  SUSPEND  statement  from  instance 1, then  we  can issue  a RESUME  statement from instance 1, 2, or 3 with the same effect. The suspend/resume feature is useful  in systems that allow us to mirror a disk or file  and  then split  the  mirror, providing an alternative  backup  and  restore  solution. If we  use  a system  that is  unable to split a mirrored disk from an existing database while writes are occurring, then we can use the suspend/resume feature to facilitate the split. 

The  suspend/resume  feature is  not a  suitable  substitute  for  normal  shutdown  operations, because  copies  of a  suspended  database can  contain  uncommitted  updates. The  following statements  illustrate suspend and resume usage. The V$INSTANCE view is queried to confirm database status.

SQL> alter system suspend;
System altered

SQL> select database_status from v$instance;
DATABASE_STATUS
------------------------
SUSPENDED

SQL> alter system resume ;
System altered

SQL> select database_status from v$instance ;
DATABASE_STATUS
-------------------------
ACTIVE


Enjoy         :-) 

V$ Views over the years

The  Dynamic  Performance Views are  very  helpful  in  monitoring our  database  for real  time performance. The dynamic  performance  views (we  will call  them the V$ views to shorten  the  name)  are real-time or almost real time views into the guts of Oracle.  

Scripts  are  now  floating  around  which  take  advantage  of  these  views  to  supply  detailed  information about  what  is  going  on  in  the SGA  in  near-real  time. It  is  not  uncommon  to  see  scripts  which  join v$session  to  v$sqlarea  to  v$sqltext  to  get  details  of  what  SQL  is  being  run  by  which  user  right  now  and  how expensive that SQL  is.

The  V$ Views  are  like the  speedometer and  the  tachometer in our car, they  tell  us  how  fast  the  car (or the database) is  going (or not), or  like  the timing  light  that  helps  us  to  adjust  the timing .  They provide  almost  immediate  feedback  as  to  the  condition  of  the  database. Below  are  the  stats  which shows how rapidly oracle dynamic views are increasing. 

Version                 V$ Views          X$ Tables

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

6                              23                    ? (35)

7                              72                      126

8.0                           132                     200

8.1                           185                     271

9.0                           227                     352

9.2                           259                     394

10.1.0.2                   340 (+31%)        543 (+38%)

10.2.0.1                   396                     613

11.1.0.6                   484 (+22%)        798 (+30%)

Enjoy        :-)