Showing posts from March, 2012

Oracle Real Application Cluster Lesson # 1

Sometimes we need Solutions to keep our database Available all the time, There are lot of solutions one of these solutions called  Oracle Real Application Cluster (RAC)/High Availability

As Lesson Number One i will take on Oracle Real Application Cluster Basics .

Lets Start :

Oracle RAC allows multiple computers to run Oracle RDBMS software simultaneously while accessing a single database, thus providing a clustered database. In a non-RAC Oracle database, a single instance accesses a single database. The database consists of a collection of data files, control files, and redo logs located on disk. The instance comprises the collection of Oracle-related memory and operating system processes that run on a computer system. In an Oracle RAC environment, two or more computers (each with an instance) concurrently access a single database. This allows an application or user to connect to either computer and have access to a single coordinated set of data.

Assume the  installation of Orac…

Prevent developers from using TOAD,other tools on production databases

When I was Browsing On internet Today i saw Amazing Article Talking about how to prevent Developers From using Toad and other tools on production Database its Small script (After Logon Trigger ) On database level :


    CREATE OR REPLACE TRIGGER block_tools_from_prod
      v_prog sys.v_$session.program%TYPE;
      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…

compile invalid objects in an APPS (EBS)

You need to know Count of Invalid Object for your APPS :


For a more detailed query, use the following script :

To recompile an individual object, connect to SQL*PLUS as the owner of the object (generally apps) and use one of the following depending on the object type :

alter package compile; (package specification)
alter package compile body; (package body)
alter view compile; (view)
If the object compiles with warnings, use either of the following to see the errors that caused the warnings :

show errors OR

select * from user_errors where name = '';
Another way to correct invalid objects is to run the adadmin utility as follows:

1. Log in as APPS User : /

2. Start the adadmin-Utility from the Unix prompt with this command :

The utility will then ask you a series of questions.

3. Under the Maintain App…

waiting for dictionary redo first scn

Capture status : Streams Waiting for dictionary first scn

Example :

Capture is waiting on redo log file with SCN 5611274208824

The First SCN 5611441137818
Start SCN 5611441137818

Applied Scn 5611441373264
Required SCN 5611441137818

You can get the above information from enterprise manager
Maintenance -> Stream -> management -> capture (choose capture name and edit).

Note : 
1-Applied Scn +  Required SCN : you can't change them (read only).

2-Set First SCN + Start SCN same as Required SCN .

 [Document 313279.1] Master Note for Troubleshooting Streams capture 'WAITING For REDO' or INITIALIZING

ACTION Plan #1 :

A) Restore archived redo logs starting with sequence
B) If you can not restore logs, then capture must be droppend and recreated.
Note 471695.1 - Required Steps to Recreate a Capture Process

You should be running on a regular basis to allow you to rebuild capture without having to resync.


Replicating data via materialized views

Sometimes We Need The Same Data For specific Tables On Other Database , What Should I Do ?

As We Know There's Oracle Solution For this issue :
1- Oracle Stream.
2- Golden Gate.
3-Standby Database.

The Above Solution Depends On what you need . What If I need One Table Or Two Or Three Table on another Database Some will say export/import , Sql Loader .

But why we don't Use Materialized Views ,The below Steps Shows how to Replicating data via materialized views

The main difference between regular and materialized view is that the latter does not query the original tables for every user request. The materialized view holds a copy of their data instead.

Source Database : Source
Target Database: Target

1-create the USERS table and add some sample records inside.

SQL> CONNECT SYSTEM/*********@Source Connected. SQL> CREATE USER APP1 IDENTIFIED BY APP1; User created. SQL> GRANT CONNECT,RESOURCE TO APP1; Grant succeeded. SQL> CONNECT APP1/APP1@Source Connected.SQL> C…