Wednesday, August 29, 2012

ORA-02021: DDL operations are not allowed on a remote database

*Cause: An attempt was made to use a DDL operation on a remote database.
 For example, "CREATE TABLE tablename@remotedbname ...".
*Action: To alter the remote database structure, you must connect to the
remote database with the appropriate privileges.

But you can avoide this  Using :

exec dbms_utility.exec_ddl_statement@db_link('your statment');

Thank you
Osama Mustafa

Instance Caging

Instance Caging

Sometimes When you are doing some testing on one machine and have more than one instance with limited hardware resource , Oracle let control that resource by caging its new feature in 11g its method to cage or bound the instance to use a certain number of cpu instead to take all available CPU simple way :

Alter system set CPU_Count = 2 

Just as note this method work with Resource Manager so you need to enable it , and create resource manager plan first before doing Instance Caging .

Instance Caging Benefits :

  1. Useful when you are using multiple instance .
  2. Allowing CPU , Resource allocation be done effectively .
  3. Control CPU Consumption of each Instance .

Thank you
Osama Mustafa

Tuesday, August 28, 2012


While You are trying to install Oracle Database On Windows 2008 R2 or any other Microsoft Os with user not administrator you will get

DIM-00014: Cannot open the Windows NT Service Control Manager.
O/S-Error: (OS 5) Access is denied.
Solution :

Run DBCA as administrator .

Click on start button -> All programs -> Accessories -> right click the command prompt icon > choose run as administrator -> 
invoke dbca in the commandline or oradim can also be used.
Thank you 
Osama mustafa 

ORA-00322 ORA-00312

The Above error Appear In My Alert Log , I have Single Test DB , Sometimes the same error appear In Standby Database you don't follow the same Produce , i didn't find any document related to this error for single database so hope this will be useful 

  ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1 :
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1 :

Step-One :
conn / as sysdba

shutdown immediate ;

startup mount ;

Step-Two :
Recover database using backup controlfile;
 Note :
Provide path where your redo log file locate , in My case "/u01/app/oracle/oradata/ORCL/"

alter database open resetlogs;
shutdown immediate;

Thank you
Osama Mustafa

Monday, August 27, 2012

Get Information About Executed Sql

Check The User who Run Sql :

select sid,
       to_char(logon_time,'MMDDYYYY:HH24:MI') logon_time,
  from v$session
where username is not null

 Active SQL:

select sesion.sid,
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username is not null

Show Full  SQL Executing For Active Session : 

select sesion.sid,
  from v$sqltext sqltext, v$session sesion
 where sesion.sql_hash_value = sqltext.hash_value
   and sesion.sql_address    = sqltext.address
   and sesion.username is not null
 order by sqltext.piece
Show Last executed SQL :

select sesion.sid,
  from v$sqltext sqltext, v$session sesion
 where sesion.sql_hash_value = sqltext.hash_value
   and sesion.sql_address    = sqltext.address
   and sesion.username is not null
 order by sqltext.piece


Flashback data Archive

What Is Flashback Data Archive ?

is has another name called Oracle total Recall , and its ability to track and store all transactional changes to a table over its lifetime.

How Can I Use it ?

Step One

create default flashback archive on an existing tablespace.

Suntax :

create flashback archive  default tablespace retention ;

Example :
 conn / a sysdba
SQL > create flashback archive default Osama tablespace User retention 1 year;

Step Two :

Create Table to Store Information inside it

Conn Osama/Osama
Sql > Create table Store as select * from dba_objects; -- For example 
Step Three:

We Need to enable flash archive on same table by

Sql > alter table Store flashback archive;
-You need to check time by :
select systimestamp from dual;
- Check Created Point By
select timestamp_to_scn(systimestamp) from dual;

The Above is the main Steps To Enable FlashBack data Archive.

Thank you
Osama Mustafa

Example Links :
1-Recall Example 
2-Recall Example 
3-Recall Example
4-Using Flashback Data Archive  

Friday, August 24, 2012

Drop DB Control Repository

I know that i post this topic before , you use emca to drop dbcontrol but what if this command fails 
what should i do ? I post This Topic to show second way to drop dbcontrol let Start :
should be used to drop DB Control repository as follows:
emca -deconfig dbcontrol db -repos drop

Steps :

1.Shutdown database

2.Remove EM job

EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
3.Revoke DBA privilages from SYSMAN user

REVOKE dba FROM sysman;

  SELECT owner, synonym_name name
  FROM dba_synonyms
  WHERE table_owner = 'SYSMAN';
  FOR r1 IN c1 LOOP
    IF r1.owner = 'PUBLIC' THEN
      EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||;
    END IF;
 5.Drop MGMT_VIEW user.

6.Drop MGMT_VIEW role

DROP ROLE mgmt_user;
7.Drop SYSMAN user

8.Disable restricted mode


 Thank you

    Thursday, August 23, 2012

    Database Vulnerabilities

    in this post you will find 10 ways make your database easy to hack ,Special thank for Team Shatter , I post online Article about Database security before and as reminder read it again it . LETS START


    We All know What we mean by Defaults and weak password (which not complex)

    To create a strong password:
    • Don’t use words that can be easily guessed or found in the dictionary
    • Use a combination of letters, numbers and characters
    • Create a complex sentence instead of a word
    • Do not share your password with anyone or write it down and leave it in your desk drawer

    sql injection : attack vector in the DBMS through Web applications because of a failure to sanitize user inputs.
    SQL Injection in the DBMS exploits passing SQL commands as a parameter of a function or stored procedure. This will then execute the malicious SQL commands in the context of the component that provides the called function. This is often done using components with system or admin privileges resulting in privilege escalation.


    avoid extensive user and group privileges:
    • Map Job Functions to Privileges on IT assets
    • Never Assign Privileges Directly to Guest Accounts or Public
    • Untangle The Web of User Entitlements
    • Implement Compensating Controls for What You Can’t Fix

    4-Unnecessary Enabled Database Features.

    These features provide database application developers with a lot more power when working with a DBMS.  The flipside of the coin is, the more power you give a developer, the more attack vectors you potentially expose to the bad guys.

    5-check-your-database-configurations with your company Goal.

    6-Buffer Overflows 

    A buffer overflow is when the input to a function contains more data than the input buffer can hold. If the size of the input is not checked during the copy to that buffer, adjacent memory that is used for other purposes might get overwritten.

     In most cases, this will be more or less random and can lead to unpredictable behavior, like crashing the server. However, if an attacker is able to also change the code execution pointer to the location of the overwritten memory, it is possible to execute any kind of malicious code using the context of the DBMS process.

     This could lead to a potential total compromise of the system, resulting in loss of sensitive information and overall security.
    To protect against these types of attacks, it is important to always keep your DBMS updated with the latest security patches available from the vendor, as well as monitoring for known attack signatures.

     7-Privilege Escalation 

    privilege escalation attack is when the attacker is exploiting a known vulnerability in a DBMS that allows a user account with restricted privileges to execute instructions or query data that that typically requires higher privileges. Thus unlocking the locks in the candy store.

    There are different common vulnerabilities that allow for privilege escalation. Sometimes misusing a function that runs under a sysdba, sa or similar security context. In other cases it is done by exploiting vulnerabilities that allow a low-privileged account to grant itself more rights.

    To protect against these types of attacks, it is important to always keep your DBMS updated with the latest security patches available from the vendor, as well as continuously monitor for known attack signatures.

    8-Denial of Service Attack DoS

    Think of a washing machine. The more clothes you put in it, the more work it needs to do. If you overfill it with clothes, it gets overwhelmed and stops working.

    The same thing happens with a Denial of Service attack. It creates so much traffic on a site, a server or even sections of the internet that it cannot function and shuts down.

    The most infamous DoS involving database servers was in 2003 when a computer worm called the SQL Slammer compromised more than 75,000 servers and slowed internet traffic to a halt.

    9-Unpatched Databases

     Many organizations don’t implement patches right away – some even wait a year or more. The most common excuses are the downtime involved with implementing patches and the time involved with testing these patches to make sure they don’t affect the production software. Whatever the excuse may be, organizations should use database activity monitoring to manage the gap between patches on its databases containing critical information PII data.

    10-Unencrypted sensitive data

    Encryption is an important part of housing sensitive data. Network traffic should also be encrypted to ensure that the passwords used to access sensitive, critical data cannot be seen by traffic.

    Any information that goes over the network or stored in the database should be encrypted and kept from prying eyes. Some network configurations and database management systems might allow for critical information to be sent in clear text. To ensure this doesn’t occur, make sure you have the latest version of software and turn off text indexing.

    Remember to back to my online article .
    Thank you

    Useful Link :
    1-Team Shatter

    Osama Mustafa

    Oracle Password Security

    As Certified Ethical hacker and Penetration  Testing Always people Asked me about if the Oracle Password can be Cracked or not ? You need to know that if the hacker want to get into your database and he will all you can do is make it harder for him , so don't choose Easy password to crack

    I post these topics not to use it in wrong way , No as DBA you need to know about Securing you database
    and How to make it unbreakable.

    For example check the below tools that used to crack Oracle Password

    And Others Tools Found for free On Internet , for example Red database security (which is amazing company and website provide you with article/topics about oracle security ) provide some of these tools for free.

    Thank you
    Osama Mustafa

    Oracle security Function for password changing

    Check this function that is used for changing user password , you need to watch out from functions like that i post this function as an example

    L_STMT VARCHAR2(255);
    L_STMT:= ‘ALTER USER “‘ || P_USER || ‘” IDENTIFIED BY “‘ || P_PWD||’”‘;

    Thank you

    I will Post More and More Topics about Oracle security

    Tuesday, August 21, 2012

    Check Database Features

    Some feature is not enabled in oracle database depend on you version Standard or Enterprise .
    You need to know what of these features are enables such as flashback its not enabled in standard

    SQL> SELECT Parameter,Value FROM V$OPTION Where Value = 'TRUE';
     PARAMETER                                                        VALUE                                                           
    Objects                                                          TRUE                                                            
    Connection multiplexing                                          TRUE                                                            
    Connection pooling                                               TRUE                                                            
    Database queuing                                                 TRUE                                                            
    Incremental backup and recovery                                  TRUE                                                            
    Instead-of triggers                                              TRUE                                                            
    Parallel load                                                    TRUE                                                            
    Proxy authentication/authorization                               TRUE                                                            
    Plan Stability                                                   TRUE                                                            
    Transparent Application Failover                                 TRUE                                                            
    Sample Scan                                                      TRUE                                                            
    Java                                                             TRUE                                                            
    OLAP Window Functions                                            TRUE

    The above example was taken for standard database , you can see the features for this database type .

    Thank you
    Osama Mustafa

    Thursday, August 16, 2012

    Change SYSMAN Password

    SYSMAN : User in database used for Enterprise manager .

    Be notice any failed with these steps you to recreate EM again .


    SQL> conn sysman/sysman1@oem
    SQL> password
    Changing password for SYSMAN
    Old password:
    New password:
    Retype new password:
    Password changed


    emctl stop dbconsole


    edit ($ORACLE_HOME/localhost.domainname_sid/sysman/config/)


    FROM :
    oracle.sysman.eml.mntr.emdRepPwd="c7021fd3720a0f18" replace with PASSWORD
    oracle.sysman.eml.mntr.emdRepPwdEncrypted="TRUE" replace with FALSE

    edit targets.xml files ($ORACLE_HOME/localhost.domainname_sid/sysman/emd/)

    FROM :
    [Property NAME="UserName" VALUE="80ec9025e45b2d20" ENCRYPTED="TRUE"/]
    [Property NAME="password" VALUE="94124d177df7c5d9" ENCRYPTED="TRUE"/]

    TO :
    Replace username value with “SYSMAN” and password value with “SYSMAN PASSWORD”

    [Property NAME="UserName" VALUE="SYSMAN" ENCRYPTED="TRUE"/]
    [Property NAME="password" VALUE="ORACLE" ENCRYPTED="TRUE"/]

    emctl start dbconsole 

    Step-5 (in case sysman is locked)

    Select Username , account_status from dba_users where username=upper('sysman');

    If its locked then
    alter user sysman account unlock ;

    Thank you
    Osama Mustafa 

    Locking In Oracle

    Locking in Oracle is one of the most common problem we will face as database administrator.

     is the locking Effect on Database performance ?

    Yes . impede a transaction from finishing , since the Lock query Take long time running .

    When the Locking Happened ?

    I will Give you example :

    Let assume that we have two Users Each Of them Update on the same table like the following :

    User 1 :

    SQL> update test set name='lock' where id=1;

    1 row updated.

    User didn't commit here .

    User 2 :

    SQL> update test set name='lock2' where id=1;

    User 2 will be waiting

    Inforamtion about locks :
    1-Locks scripts One.
    2-Locks Scripts Two

    Another way to Lock :

    performing a DDL (alter,create....) and get an ORA-00054 error.

    ORA-00054: resource busy and acquire with NOWAIT specified
     to solve this issue

    SQL> select object_id from dba_objectswhere owner='Username'  and object_name='Table';

    SELECT c.owner,
    FROM v$locked_object a, v$session b, dba_objects c
    WHERE b.sid = a.session_id AND a.object_id = c.object_id
    and a.object_id=;

    Refer also to :

    Osama Mustafa

    Wednesday, August 15, 2012

    Remove Oracle / Uninstall

    I post this topics for people who want to learn how to remove Oracle For Window/Linux 

    Windows :

    Its easy common steps you have to follow :

    • Uninstall all Oracle components using the Oracle Universal Installer (OUI).
    • Run regedit.exe and delete the HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE key. This contains registry entires for all Oracle products.
    • Delete any references to Oracle services left behind in the following part of the registry (HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Ora*). It should be pretty obvious which ones relate to Oracle.
    • Reboot your machine.
    • Delete the "C:\Oracle" directory, or whatever directory is your ORACLE_BASE.
    • Delete the "C:\Program Files\Oracle" directory.
    • Empty the contents of your "C:\temp" directory.
    • Empty your recycle bin.

    Unix :

    • Uninstall all Oracle components using the Oracle Universal Installer (OUI).
    • Stop All Oracle Process .
    lsnrctl stop
    emctl stop dbconsole
    emctl stop agent.
    • Delete the files and directories below the $ORACLE_HOME.
    # cd $ORACLE_HOME
    # rm -Rf *
    •  delete directories below the $ORACLE_BASE.
     # cd $ORACLE_BASE
    # rm -Rf admin
    •  Delete the /etc/oratab file

    Done .

    Osama Mustafa

    Sunday, August 12, 2012

    PL/SQL developer: Dynamic Performance Tables not accessible


    Login into Pl/sql developer (software)


    1.grant select any dictionary to username
    2.disable this function by

    Tools --> Preference --> option --> uncheck option "Automatic statistics"

    isnumeric function from mysql to oracle

    isnumeric : is predefined function in mysql to check if the value data type is number.

    but what if you need to check the value is number in oracle Use :

    LENGTH(TRIM(TRANSLATE(, ' +-.0123456789', ' '))) is null


    Osama mustafa

    Friday, August 10, 2012

    Oracle On Windows vs Linux

    Today i will talk about Compare Oracle Installation On Windows vs Linux , and just to let you know oracle recommended Linux .

    1-Installation Oracle on Windows / Linux

    For installation of Oracle on windows doesn't require any other user creation, we can perform oracle installation using "administrator" superuser of windows. For installation of Oracle on Unix/linux required to creating separate operating system user account. Using super user "root" we doesn't require to perform Oracle installation.

    For installation of Oracle on windows, if we create separate operating system then it should be group of super user administrator. For installation of Oracle on Unix/Linux, when we create operating system user then it should be not part of super user group.

    2-Default Location Of Windows , Linux :

    Default location of password file and parameter file for Windows is ORACLE_HOME\database folder.Default location of password file and parameter file for Unix/Linux is ORACLE_HOME/dbs folder.

    ORACLE_BASE,ORACLE_HOME,ORACLE_SID are defined in registry of Windows as HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0. ORACLE_BASE,ORACLE_HOME,ORACLE_SID are defined as user's environment variables in Unix/Linux.

    3-Symbolic Links windows , Linux

    Symbolic links are NOT supported for user's environment variables or registry parameter in Windows. Symbolic links are supported for user's environment variables in Unix/Linux.

    4-Enviorment variable in Windows, Linux 

    In windows we should need to set environment variable using "set" command and it doesn't save in user profile. In Unix and Linux we should need to set environment variable using "export" command and it can save using .profile (in Unix) and .bash_profile (in Linux).

    5-Shared Library DLL windows , Linux 

    Oracle's shared libraries are called as shared DLL in windows. Oracle's shared libraries are available in Unix/Linux.

    6-Relinking On Windows , Linux

    Relinking of Oracle executable is not available in Windows. Relinking of Oracle executable is available in Unix/Linux.

    7-Shared memory Segment Windows, Linux

    Shared memory , shared segments and semaphores are NOT adjustable in Windows. Shared memory segment(SHMMAX), shared segments (SHMMNI) and semaphores (SEMMNS) are adjustable using kernel parameters in Unix/Linux.

    8-Memory and processes In Windows , Linux

    Oracle's SGA locking in real memory doesn't possible in Windows. Oracle's SGA locking in real memory is possible in Unix/Linux.

    Each background process of Oracle is implementing as Thread inside single process in Windows. Each background process of Oracle is a process in Unix/Linux.

    9-GUI , CLUI Windows , Linux 

    Windows called as GUI because it provides Graphical User Interface. Unix and Linux called as CLUI called Command Line User Interface. Due to this reason Unix and Linux provides more performance than Windows due to resource utilization.

    10-File System , Security Windows , Linux

    Windows is flat file system. Unix and Linux is hierarchical model file system. Windows kernel stores in couple of files like Registry. Unix and Linux kernel stores in many files which are hierarchy. It is very easy to understand Unix and Linux file systems in any version.

    Earlier FAT and FAT32 file system has no security in Windows. Using NTFS file system windows use file permission based security. In Unix and Linux has traditional file permission security with owner,group and other users.Unix has greater built-in security and permissions features than Windows. Linux contains also same type of security and permissions logic like Unix.

    11-Mointer Oracle In Windows, Linux

    There are very few utilities available in Windows for performance monitoring and administration. There are lot of command line utilities are available in Unix/Linux for performance monitoring and administration.

    12-Source Code Windows , Linux 

    Source code of Operating system doesn't available in Windows. Source code of Operating system is available in some of Linux flavors, means we can modify source code of operating system.

    13-Skills Windows , Linux 

    Oracle on Windows magnetize because easy to understand, easy to maintain, easy to develop, resource availability and with good support. Oracle on Unix/Linux is not easy to understand,easy to maintain or easy to develop because it requires high skill set and depth knowledge.

    Oracle deployment is very easy in Windows because not need to more knowledge or special skill sets. Oracle deployment is not easy in Unix/Linux because it requires special skill sets.

    Windows is user friendly operating system. Unix and Linux doesn't user friendly operating system.

    14-Virus Windows , Linux 

    There is high risk of virus attacks on Windows. Because majority of windows users run as Administrator and virus can be affecting on any of files of kernel due to super user account. There is minimum risk for virus attacks on Unix and Linux. Because most of Unix box or Linux box is being run by user interface not using "root" super user. Due to this reason virus attacker cannot able to modify kernel of operating system. 

    Thank you
    Osama Mustafa

    RMAN-20001: target database not found in recovery catalog

    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
    RMAN-00571: ===================================================
    RMAN-03002: failure of list command at 11/13/2011 03:26:18
    RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

    1. Connect to RMAN Catalog

    $ rman catalog rman/rman@rmancat target rmanbkup/rmanbkup@orcl
    Recovery Manager: Release - Production on Sun Nov 13 03:25:49 2011

    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

    connected to target database: orcl (DBID=5858479612)
    connected to recovery catalog database
    1. Unregister the database
    RMAN>unregister database noprompt;

    1. Register Database to RMAN catalog
    RMAN> register database;

    database registered in recovery catalog
    starting full resync of recovery catalog
    full resync complete

    Thursday, August 9, 2012

    Scripts / part 1

    Find Below Links for two scripts show information about
    1-Table information .
    2-User Information .

    Osama mustafa

    Script to Extract Code to Recreate Materialized

    This function will extract the DDL for an existing materialized view

    SET serveroutput on
    SET feedback off
    UNDEF v_sql
       v_task_name       VARCHAR2 (100);
       v_mview_owner     VARCHAR2 (30)   := UPPER ('&&ENTER_MVIEW_OWNER');
       v_mview_name      VARCHAR2 (30)   := UPPER ('&&ENTER_MVIEW_NAME');
       v_mview_sql       VARCHAR2 (4000);
       v_mview_log_sql   VARCHAR2 (4000);
       -- get mview text from data dictionary
       SELECT DBMS_METADATA.get_ddl ('MATERIALIZED_VIEW', mview_name, owner)
         INTO v_mview_sql
         FROM dba_mviews
        WHERE owner = v_mview_owner AND mview_name = v_mview_name;
         INTO v_mview_log_sql
         FROM dba_dependencies
        WHERE referenced_type = 'TABLE'
          AND referenced_name != v_mview_name
          AND owner = v_mview_owner
          AND NAME = v_mview_name;
       DBMS_OUTPUT.put_line ('MVIEW SQL Is: ' || v_mview_sql);
       DBMS_OUTPUT.put_line ('MVIEW LOG SQL Is: ' || v_mview_log_sql);

    Wednesday, August 8, 2012

    My Old Email

    The biggest mistake in my life cost me my email . 

    Please i write this topics to notify people that my old email "" has been hacked and no more used by me . ignore any email from it .

    Thank you
    Osama mustafa

    Tuesday, August 7, 2012

    OER 7451 in Load Indicator

    The Error Appear in Alertlog like :

    Mon Aug 06 03:57:54 2012
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    Mon Aug 06 03:58:04 2012
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    Mon Aug 06 03:58:14 2012
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    Mon Aug 06 03:58:24 2012
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    Mon Aug 06 03:58:34 2012
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    Mon Aug 06 03:58:44 2012
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
    O/S-Error: (OS 1) Incorrect function. !
    Mon Aug 06 03:59:05 2012
    O/S-Error: (OS 1) Incorrect function. !

    Oracle Support points to Doc. ID 1060806.1

    Installed 32-bit Oracle database software on a 64-bit MS Windows OS which is not supported.Note: For the Database software, you can ONLY install the x64 version on MS Windows (x64).  You can NOT install the 32-bit version Database software on MS Windows (x64).

    Install 32-bit Oracle database software only on 32-bit MS Windows OS.

    Monday, August 6, 2012

    Article / Data Guard

    Today while I am Browsing i read this amazing article about Data Guard and want to share it .

    What Is Data Guard 

    Oracle Data Guard delivers high availability, data protection, and disaster recovery for the enterprise data. 

    Data Guard configuration consists of one production database, having primary role, and one or more standby databases, having standby roles.

    Standby database can take over the production service either for planned or unplanned outages.
    Apart from that it can be used for offloading the reporting and backup operations from production.

    The databases in a Data Guard configuration are connected by Oracle Net and might be arranged geographically. 

    There are no strict network requirements for the databases to be in the same subnet and etc. It could be LAN, WAN or internet connection.

    Data Guard configuration and control could be managed via:
    • SQL command line interface;
    • Data Guard Broker interface: using DGMRL for command line interface and GUI delivered via Oracle Enterprise Manager. Data Guard broker tool is included in Enterprise edition license. This tool provides simplified and automatic data guard maintenance and configuration procedures. How to use it I will describe later in this article.
    Oracle Data Guard could be deployed among different platforms(x86, Power, Itanium, SPARC), Operating systems(32/64 bit) and Oracle software versions (32/64bit) with limitations.

    Check This notes : 413484.1 and 1085687.1 On MOS.

    Data Guard deployment examples


    Examples provided here are using fast start failover technology for automatic failover operations.
    Without fast start failover in place all failover operations must be performed manually.

     The depiction of solution above demonstrates high level HA ready deployment leveraging fast start fail-over configuration and active data guard option on physical standby for reporting&queries. 

    Here observer is data guard broker executable utility, used in fast start failover configurations, which  could run on either laptop, server or workstation.

    Disaster ready  setup

    The depiction of solution below demonstrates high level DR ready deployment leveraging fast start failover configuration and logical standby for reporting&queries. As depicted it involves 3 different sites to provide desired functionality. Although it is not intended to provide reporting services in case of Site A outage, it could be done by putting additional standby server in site B though.


    Standby databases overview

    Data Guard provides three different types of standby databases:
    • Physical standby. This is physically identical copy of primary database compared on block level. All database physical and logical structures like data files, schemas are the same. Standby database is synchronized through Redo Apply mechanism, which delivers redo data and applies it on the physical standby database. For such purpose so called standby redo logs are used in standby which are providing similar functionality as online redo logs.
    • Logical standby. This type of database contains only the same logical information as the production database, but the physical structure  of the data can be different, like different data file organization and etc.  The logical standby database is synchronized through SQL Apply mechanism, which first transforms the data, in the redo received, into SQL statements – DML's and DDL's – and then executes them on the standby. Here LogMiner component of SQL Apply is used. Standby databases are functioning in read write mode.  
    • Snapshot standby. This type of physical standby database appeared first with 11g release. It is more like a data guard mode than a different configuration. The only difference from latter ones, when enabled it is fully operational – read and write mode –  production database copy, where users can access and perform any data manipulations. Meantime redo data is being received from the primary database, archived, but not applied until snapshot standby database is reverted back to the physical standby database.
    Physical standby
    • Starting with Oracle database release 11g there is new active data guard feature which allows reporting and queries while redo apply is active on physical standby. This data guard option must be purchased as add-on to Enterprise edition license. 
    • Can be used to offload backup operations from production server. 
    • Supports all database datatypes, types of tables, DDL and DML operations
    * Logical standby  
    • Typically is used for reporting and queries while sql apply is active on logical standby.
    • Could be used for database upgrades with minimal downtime.
    • Can maintain other database objects not belonging to primary database. 
    • Logical standby database has limitation on support of datatypes, types of tables, DDL and DML operations.

    Data Guard protection modes

    Databases running with Data Guard implemented could be configured to act differently when primary server outage is taking place

    Maximum performance
    Default protection mode. This protection mode provides maximum protection without affecting primary database performance. Here transactions on primary are committed as soon as redo log is filled with all relevant redo data without waiting for write accomplishment from standby databases – it is being done asynchronously. Therefore such protection mode does not guarantee complete data restore until last transaction.
    Maximum availability
    This protection mode guarantees that no data loss will occur if primary database fails. Transactions do not commit until all relevant redo data is written in redo and standby redo logs of standby database. In such configuration standby database is kept completely synchronized with primary. In case of standby database outage or whatever reasons preventing to write/send redo data into standby locations, primary database is kept running to preserver availability.
    Maximum protection
    This protection mode guarantees that no data loss will occur if primary database fails. The only difference from the Maximum Availability mode is that if primary database cannot write/send redo data into standby database locations it will be shutdown. Since such configuration prioritizes data protection over primary database's availability, the recommended deployment is to have at least several standby databases.

    How to enable, change protection modes

    from SQLPlus on primary : 


    from Data Guard broker command line interface :


     Thank you
    Osama Mustafa

    ORA-29701: Unable to connect to Cluster Manager

    ORA-29701: Unable to connect to Cluster Manager.

    The reason is Oracle cssd daemon process was not running.

    As Root User :

    Step 1: Go to the /etc folder
    Step 2: Execute the following command.
    # init.cssd start
    startup will be queued to init within 30 seconds

    Step 3: Wait for 2 minutes and execute the following commands
    # cd $ORACLE_HOME/bin
    # ./localconfig delete
    Stopping CSSD
    Failure in CSS initialization opening OCR.
    Shutdown has begun. The daemons should exit soon.

    Step 4: Execute the following command

    # ./localconfig add
    Successfully accumulated necessary OCR keys.
    Creating OCR keys for user root, privgrp 'system'
    Operation Successful
    Configuration for local css has been initialized.
    Adding to inittab
    Startup will be queued to init within 30 seconds
    Checking the status of new oracle init process..
    Expecting the CRS daemons to be up within 600 seconds
    CSS is active on these nodes
    CSS is active on all nodes
    Oracle CSS service is installed and running under init(1M)

    Step 5: Start the ASM instance now.

    Sunday, August 5, 2012

    Drop all Triggers On Database

    The Below Scripts Used fro Drop Database Trigger By user or all Database. Please Don't Use them Until you make sure what you are doing

    First One : ( For Specific User)

      FOR i in (select trigger_name,owner 
                  from dba_triggers 
                 where trigger_name like '%_BI%' and owner = 'myTesting' ) LOOP  
        EXECUTE IMMEDIATE 'DROP TRIGGER '||i.owner||'.'||i.trigger_name;  
      END LOOP;  END;  

    Second One : (For All Database)

      FOR i in (select trigger_name,owner 
                  from dba_triggers ) LOOP  
        EXECUTE IMMEDIATE 'DROP TRIGGER '||i.owner||'.'||i.trigger_name;  
      END LOOP;  END;  

    Thank you
    Osama Mustafa

    ORA-1652: unable to extend temp segment by % in tablespace TEMP

    What does that error means:

    This error is fairly self explanatory - we cannot get enough space for a temporary segment. The size reported in the error message is the number of contiguous free Oracle blocks that cannot be found in the listed tablespace.

    How to approach the ORA-1652 error

    There are two views in the database that help keep track of the free space in the temporary tablespace: v$sort_usage and v$tempseg_usage (from 9i onwards).

    ------------------------------- ------------ ----------- -----------
    TEMP                                 1310592           0     1310592

    If USED_BLOCKS = TOTAL_BLOCKS, find which user and statement is using the temporary sort segment by following;

     SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
    FROM v$session a, v$tempseg_usage b, v$sqlarea c
    WHERE a.saddr = b.session_addr
    AND c.address= a.sql_address
    AND c.hash_value = a.sql_hash_value
    ORDER BY b.tablespace, b.blocks


    There are two ways of solving this error:
    1. Add more tempfiles, increase the size of the current ones or enable auto extend and the most important:
    2. Tune the queries/statements so that the sort operations are done in memory and not on the disk.
     Thank you
    Osama Mustafa

    Get Database Parameters even Hidden One

    You can use this Scripts to check the init.ora in Oracle but not from v$parameter this Time

    First Query : (display all init.ora parameter including the hidden parameters):

    SET linesize 235
    col Parameter FOR a50
    col SESSION FOR a28
    col Instance FOR a55
    col S FOR a1
    col I FOR a1
    col D FOR a1
    col Description FOR a90

      a.ksppinm  "Parameter",
      decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
      c.ksppstvl "Instance",
      decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
      decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
      decode(p.isdefault,'FALSE','F','TRUE','T') "D",
      a.ksppdesc "Description"
    FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
    WHERE a.indx = b.indx AND a.indx = c.indx
      AND = a.ksppinm
      AND UPPER(a.ksppinm) LIKE UPPER('%&1%')
    ORDER BY a.ksppinm;
    Second Query (list of parameter which are not default):

    SET linesize 235 pagesize 200
    col Parameter FOR a50
    col SESSION FOR a28
    col Instance FOR a55
    col S FOR a1
    col I FOR a1
    col D FOR a1
    col Description FOR a90

      a.ksppinm  "Parameter",
      decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
      c.ksppstvl "Instance",
      decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
      decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
      decode(p.isdefault,'FALSE','F','TRUE','T') "D",
      a.ksppdesc "Description"
    FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
    WHERE a.indx = b.indx AND a.indx = c.indx
      AND = a.ksppinm
      AND UPPER(a.ksppinm) LIKE UPPER('%&1%')
    ORDER BY a.ksppinm) WHERE d='F';

    Scripts : Find Oracle Parameters

    Find User with unlimited Tablespace Quota

    First find the user with direct quota on tablespace SYSTEM.

    SELECT username,tablespace_name, bytes, max_bytes
    FROM dba_ts_quotas
    WHERE max_bytes = -1 AND tablespace_name ='SYSTEM';

    USERNAME                  TABLESPACE_NAME                BYTES  MAX_BYTES
    ------------------------- ------------------------- ---------- ----------
    SCOTT                     SYSTEM                             0         -1
    TEST                      SYSTEM                             0         -1

    why do we want to know who has unlimited quota on the SYSTEM tablespace?

    User who have unlimited quota on SYSTEM as well the privilege to create tables or clusters could do some kind of denial of service attack to the database. The just have to fill up the free space in the SYSTEM tablespace. If there default tablespace is as well SYSTEM the could even do this without intention.

    Find user with system privilege UNLIMITED TABLESPACE.

    SELECT * FROM dba_sys_privs WHERE privilege = 'UNLIMITED TABLESPACE'

    GRANTEE                        PRIVILEGE                      ADM
    ------------------------------ ------------------------------ ---
    WMSYS                          UNLIMITED TABLESPACE           NO
    RRDOMREG                       UNLIMITED TABLESPACE           NO
    HR                             UNLIMITED TABLESPACE           NO
    OE                             UNLIMITED TABLESPACE           NO
    SYS                            UNLIMITED TABLESPACE           NO
    SCOTT                          UNLIMITED TABLESPACE           NO
    BI                             UNLIMITED TABLESPACE           NO
    OUTLN                          UNLIMITED TABLESPACE           NO
    DBSNMP                         UNLIMITED TABLESPACE           NO
    IX                             UNLIMITED TABLESPACE           NO
    SH                             UNLIMITED TABLESPACE           NO
    DBA                            UNLIMITED TABLESPACE           YES
    SYSTEM                         UNLIMITED TABLESPACE           YES

    What about cascaded roles?

    Mmh, but since Oracle 11g it is possible to grant UNLIMITED TABLESPACE to a role and this can be granted to an other role which is granted again to an other role. It could be a role concept or somebody who want to hide a little bit some privileges. To test it I’ve created three roles DBA3, DBA2 and DBA1, granted UNLIMITED TABLESPACE to DBA3.

      DECODE(p,'=>'||grantee,'direct',p) path
    FROM (
        SYS_CONNECT_BY_PATH(grantee, '=>') p
      FROM (
        FROM dba_sys_privs
        UNION ALL
          granted_role privilege
      CONNECT BY PRIOR grantee = privilege )
      (grantee IN (SELECT username FROM dba_users)
      OR grantee = 'PUBLIC');

    GRANTEE   PRIVILEGE               PATH
    --------- ----------------------- -------------------------------
    BI        UNLIMITED TABLESPACE    direct
    SYS       DBA                     =>DBA=>SYS
    SYSTEM    DBA                     =>DBA=>SYSTEM
    SCOTT     DBA1                    =>DBA3=>DBA2=>DBA1=>SCOTT
    SYS       DBA1                    =>DBA3=>DBA2=>DBA1=>SYS
    SYS       DBA2                    =>DBA3=>DBA2=>SYS
    SYS       DBA3                    =>DBA3=>SYS
    HR        UNLIMITED TABLESPACE    direct
    IX        UNLIMITED TABLESPACE    direct
    OE        UNLIMITED TABLESPACE    direct
    SH        UNLIMITED TABLESPACE    direct
    SYS       UNLIMITED TABLESPACE    direct

    18 ROWS selected.
    create one to find user’s with direct quotas as well through a system privilege will give something like this.

    FROM (
        grantee username, 'Any Tablespace' tablespace_name, privilege
      FROM (
        -- first get the users with direct grants
          p1.grantee grantee, privilege
          dba_sys_privs p1
          p1.privilege='UNLIMITED TABLESPACE'
        UNION ALL
        -- and then the ones with UNLIMITED TABLESPACE through a role...
          r3.grantee, granted_role privilege
          dba_role_privs r3
          START WITH r3.granted_role IN (
                DISTINCT p4.grantee
                dba_role_privs r4, dba_sys_privs p4
                AND p4.privilege = 'UNLIMITED TABLESPACE')
        CONNECT BY PRIOR grantee = granted_role)
        -- we just whant to see the users not the roles
      WHERE grantee IN (SELECT username FROM dba_users) OR grantee = 'PUBLIC'
      -- list the user with unimited quota on a dedicated tablespace
        username,tablespace_name,'DBA_TS_QUOTA' privilege
        max_bytes = -1 )
    WHERE tablespace_name LIKE UPPER('SYSTEM')
        OR tablespace_name = 'Any Tablespace';

    USERNAME                  TABLESPACE_NAME           PRIVILEGE
    ------------------------- ------------------------- ------------------------------
    SYSTEM                    Any Tablespace            UNLIMITED TABLESPACE
    SYS                       Any Tablespace            DBA
    SYSTEM                    Any Tablespace            DBA
    SCOTT                     Any Tablespace            DBA1
    SYS                       Any Tablespace            DBA1
    SYS                       Any Tablespace            DBA2
    SYS                       Any Tablespace            DBA3
    SYS                       Any Tablespace            LOGSTDBY_ADMINISTRATOR
    TEST                      SYSTEM                    DBA_TS_QUOTA

    19 ROWS selected.
     You Can Download Script from here : Tablespace-scripts
    Links :
    1-Find TableSpace Quota .
     Thank you
    Osama Mustafa