Tag: osama mustafa oracle

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

Dataguard on Different Operating Systems

Posted on by Osama Mustafa in Database
In 10g, dataguard started to support different binaries on primary and standby database servers with the same OS family.

  For example :

Microsoft Windows 64-bit on primary and Microsoft Windows 32-bit
Microsoft Windows 64-bit for AMD on standby database server.

However with 11g, dataguard also supports different OS on primary and standby servers. I wanted to share the support matrix for this feature.

 Check this Blog which is talking about this topics and post some Notes From My Oracle Support :

Emre Baransel.



Thank you
Osama Mustafa

Copy Command in Sqlplus

Posted on by Osama Mustafa in Database
Today as any Database administrator i wake up on new task , they want import some tables from Developer environment to Production , So I used Copy Command .

its usual used for copy tables from database to Another  , but you have to know this way is very useful specially if you have long Data type in your tables , i will explain it , and Define Two Database in your TNS.

create table osama as select * from long_table ;

You will Get this error :

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

copy from user1/password@orcl  to user2/password@orcl2 create

using ;

The Output :

Array fetch/bind size is 30. (arraysize is 30)
Will commit when done. (copycommit is 0)
Maximum long size is 40. (long is 40)

Table osama created.

Simple !!!

Useful Link : 
1-Oracle Document .
2-Examples. 

Thank you 
Osama Mustafa 

Oracle Databases from 11.2.0.1 to 11.2.0.2

Posted on by Osama Mustafa in Database
Basic Steps : 

1. Install 11.2.0.2 into a separate ORACLE_HOME.
2. Take a full backup of the database.
3. Always shutdown the database cleanly

After Installing 11.2.0.2 , Go to 11.2.0.1 database and run :

Connect as sys
SQL> spool pre_upgrade.log
SQL> @ORACLE_HOME/rdbms/admin/utlu112i.sql
SQL> spool off 

 To check Data Dictionary 

Connect as sys
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
Note: Ignore if dbms_stats.gather_dictionary_stats was run once.

To Fix invalid Object in the database :

connect as sys
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Run pre-upgrade diagnostic utility dbupgradiag.sql

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus “/ as sysdba”
SQL> spool pre_dbupgdiag.log
SQL>@dbupgdiag.sql
SQL>spool off 

 if the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql 

Configure the 11.2.0.1 database environment for 11.2.0.2 Upgrade 

Copy the following Files to New Home 11.2.0.2 :

  1. Spfile/Pfile/init.ora
  2. orapwd
  3. tnsname.ora
  4. Listener.ora
  5. OC4J Home (ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_) Only if you have enterprise manager

Set The environment to 11.2.0.2

export ORACLE_HOME=/u01/app/oracle/prouct/11.2.0.2
export ORACLE_SID=ORCL

Note : Replace the Path , LD_Library_Path to new Home .

Upgrade 11.2.0.1 database to 11.2.0.2

sqlplus  / as sysdba
 spool upgrade.log
startup upgrade
set echo on
@?/rdbms/admin/catupgrd.sql;
spool off
shutdown immediate

Restart the database in normal mode

sqlplus  / as sysdba
 @/opt/app/oracle/product/11.2.0.2/rdbms/admin/catuppst.sql;
@/opt/app/oracle/product/11.2.0.2/rdbms/admin/utlrp.sql;
select comp_name,version,status from dba_registry;
select owner,count(*) from dba_objects where status != ‘VALID’ group by owner;

Change the compatibility parameter

Change the compatible parameter and restart the database.

SQL> alter system set compatible=’11.2.0.2.0′ scope=spfile;
SQL> shutdown immediate;
SQL> startup;

And Please Make Sure the listener has been started from the new home not the old one .

I will Post Upgrade to 11.2.0.3 “Enshalla”

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