Posts

Showing posts from August, 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 :
Useful when you are using multiple instance .Allowing CPU , Resource allocation be done effectively .Control CPU Consumption of each Instance .
Thank you
Osama Mustafa

dim-00014

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 :
'/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_79fz3gx8_.log'
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1 :
'/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_79fz3g1v_.log'
Solution

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/"

Step-three:
alter database open resetlogs;
shutdown immediate;
startup;


Thank you
Osama Mustafa

Get Information About Executed Sql

Check The User who Run Sql :

select sid, to_char(logon_time,'MMDDYYYY:HH24:MI') logon_time, username, type, status, process, sql_address, sql_hash_value from v$session where username is not null
 Active SQL:

select sesion.sid, sesion.username, optimizer_mode, hash_value, address, cpu_time, elapsed_time, sql_text 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, sql_text 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, sql_text from v$sqltext sqltext, v$session sesion where sesion.sql_hash_val…

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 Archi…

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 :

emca
should be used to drop DB Control repository as follows:
emca -deconfig dbcontrol db -repos drop

Steps :

1.Shutdown database


SHUTDOWN IMMEDIATE; 2.Remove EM job

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

REVOKE dba FROM sysman;4.Run 

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

DROP USER MGMT_VIEW CASCADE;6.Drop MGMT_VIEW role

DROP ROLE mgmt_user;7.Drop SYSMAN user

DROP USER sysman CASCADE;8.Disable restricted mode

ALTER …

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


1-default-blank-and-weak-username-and-passwords

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 dictionaryUse a combination of letters, numbers and charactersCreate a complex sentence instead of a wordDo not share your password with anyone or write it down and leave it in your desk drawer2-sql-injections-in-the-database

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…

Oracle Password Security

Image
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


FUNCTION CHGPWD (
P_USER VARCHAR2,
P_PWD VARCHAR2)
RETURN BOOLEAN IS
L_STMT VARCHAR2(255);
BEGIN
L_STMT:= ‘ALTER USER “‘ || P_USER || ‘” IDENTIFIED BY “‘ || P_PWD||’”‘;
EXECUTE IMMEDIATE L_STMT;
RETURN TRUE;
END;
Thank you

I will Post More and More Topics about Oracle security

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…

Change SYSMAN Password

SYSMAN : User in database used for Enterprise manager .

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

Step-1
SQL> conn sysman/sysman1@oem
Connected.
SQL> password
Changing password for SYSMAN
Old password:
New password:
Retype new password:
Password changed
Step-2
emctl stop dbconsole
Step-3
edit emoms.properties ($ORACLE_HOME/localhost.domainname_sid/sysman/config/)
**emoms.properties:

FROM : oracle.sysman.eml.mntr.emdRepPwd="c7021fd3720a0f18" replace with PASSWORD
oracle.sysman.eml.mntr.emdRepPwdEncrypted="TRUE" replace with FALSE
TO: oracle.sysman.eml.mntr.emdRepPwd=ORACLE
oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE
edit targets.xml files ($ORACLE_HOME/localhost.domainname_sid/sysman/emd/)
**targets.xml FROM : [Property NAME="UserName" VALUE="80ec9025e45b2d20" ENCRYPTED="TRUE"/]
[Property NAME="password" VALUE="94124d177df7c5d9" ENCRYPTED="TRUE"/]

TO : Replace username value with “SYSMAN” and pa…

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 andacquire withNOWAIT specified  to solve this issue

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

 OBJECT_ID
----------

SELECT c.owner,
 c.object_name,
 c.object_type,
 b.sid,
 b.serial#,
 b.status…

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_…

PL/SQL developer: Dynamic Performance Tables not accessible

Image
ERROR HAPPENED WHEN :

Login into Pl/sql developer (software)





Solution:

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

Simple

Osama mustafa

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/Linu…

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


Connect to RMAN Catalog
$ rman catalog rman/rman@rmancat target rmanbkup/rmanbkup@orcl Recovery Manager: Release 11.2.0.2.0 - 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 Unregister the databaseRMAN>unregister database noprompt;
Register Database to RMAN catalogRMAN> register database;
database registered in recovery catalog starting full resync of recovery catalog full resync complete

Scripts / part 1

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

Enjoy
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 DECLARE 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); BEGIN -- 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; SELECT DBMS_METADATA.get_dependent_ddl ('MATERIALIZED_VIEW_LOG', referenced_name, referenced_owner ) INTO v_mview_log_sql FROM dba_dependencies WHERE referenced_type = 'TABLE' AND refe…

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 "Osama.mustafa@yahoo.com" has been hacked and no more used by me . ignore any email from it .


Thank you
Osama mustafa

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: il…

Article / Data Guard

Image
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 Ma…

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
nodelist
CSS is active on all nodes
Oracle CSS service is installed and running under init(1M)

Step 5: Start the ASM in…

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)

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

BEGINFOR 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_usageand v$tempseg_usage(from 9i onwards).

select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;
TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
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
AN…

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 SESSIONFOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90

SELECT
  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 p.name(+)= a.ksppinm
ANDUPPER(a.ksp…

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 =-1AND 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                  …