Tuesday, September 6, 2011

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        :-) 

Monday, September 5, 2011

Flashback Data Archive (FBDA) In Oracle 11g


Flashback Data Archive (Oracle Total Recall) provides the ability to track and store all transactional changes to a table over its lifetime. It is no longer necessary to build this intelligence into our application. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.

Prior to oracle 11g, Flashback technology to a large part was based on the availability of undo data or flashback logs and both the undo data as well as flashback logs are subject to recycling when out of space pressure exists. The UNDO tablespace in Oracle was primarily meant for transaction consistency and not data archival. A Flashback Data Archive is configured with retention time. Data archived in the Flashback Data Archive is retained for the retention time.Let’s look at an example

Creating a Flashback Data Archive :

SQL> create flashback archive near_term
           tablespace users
           retention 1 month ;
Flashback archive created.

The archive is created in the tablespace Users. Assume we have to record changes to a table called employees which is in "HR" schema . All we need to do is enable the Flashback Data Archive status of the table to start recording the changes in that archive .

SQL> alter table hr.employees flashback archive near_term;
Table altered.

This puts the table into the Flashback Data Archive mode. All the changes to the rows of the table will be now tracked permanently. 

SQL> select salary,job_id from hr.employees where employee_id=121;
SALARY            JOB_ID
---------             ----------
 8200                 ST_MAN

SQL> update hr.employees set salary=50000 where employee_id=121;
1 row updated.

SQL> commit;
Commit complete.

Now, if we select the row, it will always display 50000 in this column. To find out the older value as of a certain time, we can use the Flashback query as shown below

SQL> select salary from hr.employees as of timestamp to_timestamp ('09/5/2011 10:55:00','mm/dd/yyyy hh24:mi:ss')  where employee_id =121;
SALARY
---------
8200

Now, after some time, when the undo data has been purged out of the undo segments, query the flashback data again:

SQL> select salary from hr.employees as of timestamp to_timestamp ('09/5/2010 10:55:00','mm/dd/yyyy hh24:mi:ss')  where  employee_id =121 ;
SALARY
---------
8200

It comes back with the result :500000 The undo is gone, so where did the data come from .We can do that using autotrace and see the execution plan:

SQL> set autotrace traceonly explain

SQL> select salary from hr.employees as of timestamp to_timestamp ('09/5/2010 10:55:00','mm/dd/yyyy hh24:mi:ss')  where employee_id =121;

Check the explain Plan detail by clicking below link :
http://www.4shared.com/document/WXMMFOS8/fda_explain_tab.html

This output answers the riddle  “Where did the data come from?”;  it came from the table  SYS_FBA_HIST_68909,  which is a location in the Flashback Archive we defined earlier for that table. We can check the table but it’s not  supported by Oracle to directly peek at that data there. Anyway, I don’t see a reason we would want to do that. 
The data inside the archive is retained but until how long? This is where the retention period comes into play. It’s retained up to that period. After that, when new data comes in, the older data will be purged. We can also purge it ourself,
 e.g. 
SQL> alter flashback archive near_term purge before scn xxxxxxxx;

Disable flashback :  Disable flashback archiving for the table employee :
SQL> ALTER TABLE hr.employee NO FLASHBACK ARCHIVE;

Remove Flashback Data Archive and all its historical data, but not its tablespaces:
SQL> DROP FLASHBACK ARCHIVE near_term ;

Use Cases :  
Flashback Data Archive is handy for many purposes. Here are some ideas:
• To audit for recording how data changed
• To enable an application to undo changes (correct mistakes)
• To debug how data has been changed
• To comply with some regulations that require data must not be changed after some time. Flashback Data
Archives are not regular tables so they can’t be changed by typical users.
• Recording audit trails on cheaper storage thereby allowing more retention at less cost

For more detail visit :
http://www.oracle.com/pls/db111/search?remark=quick_search&word=flashback+data+archive


Enjoy     :-) 

Saturday, September 3, 2011

Difference Between Upgradation and Migration in Oracle


Upgradation   :  Upgrade is the process of replacing our existing software with a newer version of the same product. For example, replacing oracle 9i release to oracle 10g release . Upgrading our applications usually does not require special tools. Our existing reports should look and behave the same in both products. However, sometimes minor changes may be seen in product .Upgradation is done at Software level.

Migration   :   Migration is the process of replicating applications from one product in another product, for example, transforming existing oracle 9i  applications to oracle 10g applications.A migration is any change that transforms our hardware and/or software architecture to a new state. Migration is done as database level(say  migrating from DB2 to Oracle).


Enjoy  :-)