Tag: Oracle Blog .

ORA-39083/ORA-02304 during impdp

Posted on by Osama Mustafa in Database
Error :

ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal

Cause:
 OID should be unique in a database. the OID in the impdp create statemene was used by old schema. 

solution:

impdp again with parameter transform=OID:n

Example :

impdp username/password@SID DIRECTORY=DMP LOGFILE=impdp.log transform=OID:n
DUMPFILE=%.dmp

Query To give Dba Object for specific User

Posted on by Osama Mustafa in Database
Simple Topic let you know what is the database object for specific user

SELECT object_name, object_type
FROM   all_objects
WHERE  owner = 'SYS'
AND    object_type IN ('PACKAGE', 'PACKAGE BODY')
ORDER BY object_name, object_type;

Thank you
Osama Mustafa

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

Posted on by Osama Mustafa in 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

Posted on by Osama Mustafa in Database
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

dim-00014

Posted on by Osama Mustafa in Database
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

Posted on by Osama Mustafa in Database
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

Posted on by Osama Mustafa in Database
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_value = sqltext.hash_value
   and sesion.sql_address    = sqltext.address
   and sesion.username is not null
 order by sqltext.piece


 

Drop DB Control Repository

Posted on by Osama Mustafa in Database
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 SYSTEM DISABLE RESTRICTED SESSION;

 Thank you
Osama