Month: September 2011

Export Data Bump , Import Data Bump In Oracle

Posted on by Osama Mustafa in Database
Oracle Data Pump is a newer, faster and more flexible alternative to the “exp” and “imp” utilities used in previous Oracle versions. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables.

Steps :

1- Create Oracle Directory 

In Sql Plus (Conn /as sysdba)

CREATE OR REPLACE DIRECTORY "Dir-name"  AS "Dir-path";
GRANT READ, WRITE ON DIRECTORY "Dir-path" TO "username";
2- Some Examples : 
CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
 
 
The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax.

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR 
dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR 
dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log
 
 
The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax.

expdp system/password@db10g full=Y directory=TEST_DIR 
dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR 
dumpfile=DB10G.dmp logfile=impdpDB10G.log

You can Use For more information : 

"expdp help=y"



Or 




"impdb help=y" 








 



	

	

	




	
	

		
Upgrade Oracle from 10.2.0.1 To 10.2.0.4
				

					Posted on  by Osama Mustafa in Database				


				

	

		


1. Patch Set Overview
Patch set release 10.2.0.4. Before installing this patch set you must be need 10.2.0.1 version.
2. Requirements
Oracle DB : Oracle 10.2.0.1 (later)Operating System: Any Platform


3. Pre – Installation Tasks
1. Identify prior installation
Before installing this patch you must install oracle 10.2.0.1 (or later version)
2. Download Patch set
Download 6810189 patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.

3. Shutdown oracle database.
  export oracle_sid= ORCL
sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Nov 13 10:49:26 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys@ORCL as sysdbaEnter password:Connected.
SQL> SHUTDOWNDatabase closed.Database dismounted.ORACLE instance shut down.


4. Stop all services





Export ORACLE_SID=ORCL





LSNRCTL STOP
EMCTL STOP DBCONSOLE




4. Backup your database (Just In Case).
Oracle recommends that you create a backup of the Oracle 10g installation before you install the patch set.
5. Check Tablespace Sizes and Set Parameter Values 


Review the following sections before upgrading a database. 


6. Upgrade the Database 


After you install the patch set, you must perform the following  steps on every database associated with the upgraded Oracle home: 


1. Start all services
2. Connect sys user
  sqlplus /NOLOG
CONNECT SYS/SYS_password AS SYSDBA
3. Enter the following SQL*Plus commands:
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sql
SQL> SPOOL OFF
Review the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script. 
This list provides the version and status of each SERVER component in the database. If necessary, rerun the catupgrd.sql script after correcting any problems. 
4. Restart the database: 


 


SQL> SHUTDOWNSQL> STARTUP
5. Compile Invalid Objects
Run the utlrp.sql script to recompile all invalid PL/SQL packages  now instead of when the packages are accessed for the first time. This  step is optional but recommended. SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql
SQL> select * from v$version;
BANNER—————————————————————-Oracle Database 10g Release 10.2.0.4.0 – ProductionPL/SQL Release 10.2.0.4.0 – ProductionCORE 10.2.0.4.0 ProductionTNS for 64-bit Windows: Version 10.2.0.4.0 – ProductionNLSRTL Version 10.2.0.4.0 – Production



	

	

	




	
	

		
Oracle Scripts
				

					Posted on  by Osama Mustafa in Scripts				


				

	

		
Some Oracle Scripts maybe will be useful for all of us :


 1-Active_session_wait.sql


 — ———————————————————————————–
— File Name    : http://osamamustafa.blogspot.com
— Author       : Osama Mustafa 
— Description  : Displays information on the current wait states for all active database sessions.
— Requirements : Access to the V$ views.
— Call Syntax  : @active_session_waits
— Last Modified: 02/05/2011
— ———————————————————————————–
SET LINESIZE 250
SET PAGESIZE 1000


COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN wait_class FORMAT A15
COLUMN state FORMAT A19
COLUMN logon_time FORMAT A20


SELECT NVL(a.username, ‘(oracle)’) AS username,
       a.osuser,
       a.sid,
       a.serial#,
       d.spid AS process_id,
       a.wait_class,
       a.seconds_in_wait,
       a.state,
       a.blocking_session,
       a.blocking_session_status,
       a.module,
       TO_CHAR(a.logon_Time,’DD-MON-YYYY HH24:MI:SS’) AS logon_time
FROM   v$session a,
       v$process d
WHERE  a.paddr  = d.addr
AND    a.status = ‘ACTIVE’
ORDER BY 1,2;


SET PAGESIZE 14


 2-Analys_All .sql


— ———————————————————————————–
— File Name    : http://osamamustafa.blogspot.com
— Author       : Osama Mustafa 
— Description  : Outdated script to analyze all tables for the specified schema.
— Comment      : Use DBMS_UTILITY.ANALYZE_SCHEMA or DBMS_STATS.GATHER_SCHEMA_STATS if your server allows it.
— Call Syntax  : @ananlyze_all (schema-name)
— Last Modified: 02/11/2010
— ———————————————————————————–
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF


SPOOL temp.sql


SELECT ‘ANALYZE TABLE “‘ || table_name || ‘” COMPUTE STATISTICS;’
FROM   all_tables
WHERE  owner = Upper(‘&1’)
ORDER BY 1;


SPOOL OFF


— Comment out following line to prevent immediate run
@temp.sql


SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON




3-Lock_tree.sql


— ———————————————————————————–
— File Name    : http://osamamustafa.blogspot.com
— Author       : Osama Mustafa 
— Description  : Displays information on all database sessions with the username
—                column displayed as a heirarchy if locks are present.
— Requirements : Access to the V$ views.
— Call Syntax  : @lock_tree
— Last Modified: 03/05/2011
— ———————————————————————————–
SET LINESIZE 500
SET PAGESIZE 1000


COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20


SELECT LPAD(‘ ‘, (level-1)*2, ‘ ‘) || NVL(s.username, ‘(oracle)’) AS username,
       s.osuser,
       s.sid,
       s.serial#,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,’DD-MON-YYYY HH24:MI:SS’) AS logon_time
FROM   v$session s
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;


SET PAGESIZE 14


I will upload more Scripts Just copy & Paste Save it as SQL .. Enjoy 🙂 





	

	

	




	
	

		
Reconfigure Oracle EM
				

					Posted on  by Osama Mustafa in Database				


				

	

		
Sometimes when you do something wrong  and problems start you need tons of works to recreate what you did ?
But is this mean its impossible to fix it , Oh No
Today i am going to talk about how to reconfigure Oracle enterprise manager using command line :


1- set ORACLE_SID=orcl
2-emca -deconfig dbcontrol db -repos drop
3- You’ll receive the following prompts :


STARTED EMCA at AUG 10, 2011 8:26:42 AM
EM Configuration Assistant, Version 10.2.0.5.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y





But if you already have Sysman schema the following error will be appear :




CONFIG: ORA-20001: SYSMAN already EXISTS..
ORA-06512: at line 17



4-Drop User Sysman in the following steps (sqlplus /as sysdba)


DROP USER sysman CASCADE;
DROP PUBLIC SYNONYM setemviewusercontext;
DROP ROLE mgmt_user;
DROP PUBLIC SYNONYM mgmt_target_blackouts;
DROP USER mgmt_view;



5-emca -config dbcontrol db -repos create


STARTED EMCA at AUG 10, 2011 8:28:48 AM
EM Configuration Assistant, Version 10.2.0.5.0 Production
Copyright (c) 2003, 2005, Oracle.  ALL rights reserved.
 
Enter the following information:
DATABASE SID: orcl
Listener port NUMBER: 1521
Password FOR SYS USER:
Password FOR DBSNMP USER:
Password FOR SYSMAN USER:
Password FOR SYSMAN USER: Email address FOR notifications (optional):
Outgoing Mail (SMTP) server FOR notifications (optional):
-----------------------------------------------------------------
 
You have specified the following settings
 
DATABASE ORACLE_HOME ................ C:\app\Administrator\product\11.1.0\db_1
 
LOCAL hostname ................ mclaughlin11g
Listener port NUMBER ................ 1521
DATABASE SID ................ orcl
Email address FOR notifications ...............
Outgoing Mail (SMTP) server FOR notifications ...............
 
-----------------------------------------------------------------
 
Do you wish TO continue? [yes(Y)/no(N)]: y



	

	

	




	
	

		
Manually Install/De-Install Database Options in Oracle Database 10gR2
				

					Posted on  by Osama Mustafa in Database				


				

	

		
Hi,


When a new database is created using DBCA, by default some of the common  database options are installed like Oracle JVM and Oracle Text. But,  when you manually create the database none of them get installed. You  need to manually install them.   I thought of sharing Metalink Note Id’s for some of the Common Database  Options which you can manually install/de-install once database has been  created:


Oracle JVM
Note:276554.1         How to Reload the JVM in 10.1.0.X and 10.2.0.X





Oracle Text
Note:280713.1         Manual installation, deinstallation of Oracle Text 10gR1 and 10gR2





Oracle XML DB
Note:243554.1         How to Deinstall and Reinstall XML Database (XDB)


Oracle Multimedia
Installation, Upgrade and Downgrade information can be found in the following Manual:      Oracle® interMedia User’s Guide 10g Release 2 (10.2)          Appendix B Installing and Upgrading Oracle interMedia 





Oracle OLAP
Note:296187.1         How To Manually Install Oracle OLAP into a 9i or 10g Database After the DB Has Been Created 


Oracle Spatial
Note:270588.1         Steps for Manual Installation / Verification of Spatial 10g


Oracle Ultra Search
Note:337992.1         How to Manually Install and Uninstall Ultra Search 10gR2 Server Components 


Oracle Label Security
Note:171155.1         How to Install / Deinstall Oracle Label Security Oracle9i/10g


Sample Schemas
   Note:340112.1         How To Install Sample Schemas in 10g?


Enterprise Manager Repository
Note:114763.1         How to Create Enterprise Manager 2.1 Repository Using EMCA


Oracle Application Express
Note:445205.1         How to download and install Application Express / HTMLDB


Oracle Warehouse Builder
Note:459961.1         How to Install Warehouse Builder Repository(10.2.0.1.31) on a RAC Database on Windows and UNIX 


Oracle Database Vault
Note:445092.1         How to Install Database Vault Patches on top of 10.2.0.3





Oracle Database Extensions for .NET
Note:374820.1         How to Install the Oracle Database Extensions for .NET (ODE.NET)   Hope you find this document helpful 🙂


Thanks For our Friend Momen For this Artical its was posted in his blog.
and i share for more useful information . 

	

	

	




	
	

		
Most Beautiful Datacenter i ever seen
				

					Posted on  by Osama Mustafa in Database				


				

	

		
Hi


This is the first time i put something not related to oracle , but when i saw the professionalization in this work and What human can do ?


i said to myself you must put this picture here to share it with people and network guys maybe they will learn something from this amazing art .


I need to clear my point , we all see Data center or supercomputers But do you think is what you see will be like this :






















































What you think Now please let me you know your opinion  by your comment ? 


Thank you 



	

	

	




	
	

		
OC4J issue in Enterprise Manager
				

					Posted on  by Osama Mustafa in Database				


				

	

		
Hi Friends, recently i tried to configure Oracle enterprise manager in one of my database and issued below command


$ emctl status dbconsole


i got the following ouput
OC4J Configuration issue. /opt/oracle/oracle/product/10.2.0/DB/oc4j/j2ee/OC4J_DBConsole_iss.dk_PAS not found.
Then i found that we need to have a directory in the specified location with specified name and i found a directory called OC4J_DBConsole. Then i renamed it to OC4J_DBConsole_iss.dk_PAS which solved the issue.
Hope this may help you and in next post i will present different EM commands.
maybe you need to kill the process :


1-ps -ef | grep oc4j
2-Kill -9 PID



	

	

	




	
	

		
Oracle Enterprise Manager 10.2.0.4 Unable to start
				

					Posted on  by Osama Mustafa in Database				


				

	

		
Hi All


we gonna talk about Bug in oracle 10.2.0.4 , 10.2.0.5 since its unable to start enterprise manager after run
“emctl start dbconsole”


CONFIG: Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
https://myhost:5501/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control
................................................ failed


This error appear because the SSL certification has been end .
But the question is what can i do ?


Forget Enterprise manager and start using command line


Not just kidding


What if we try to recreate it again ?


emca -config dbcontrol db -repos recreate


STARTED EMCA at Jan 11, 2011 4:11:01 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.


Enter the following information:
Database unique name: catest
Database Control is already configured for the database catest
You have chosen to configure Database Control for managing the database catest


This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: Y
Listener port number: 1521
Cluster name: mycluster
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):


........


Jan 11, 2011 4:18:05 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Jan 11, 2011 4:19:31 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jan 11, 2011 4:28:38 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Error starting Database Control
Refer to the log file at /myhost/oracle/product/10.2.0/db_1/cfgtoollogs/emca/catest/emca_2011-01-11_
04-11-01-PM.log for more details.
Could not complete the configuration. Refer to the log file at /myhost/oracle/product/10.2.0/db_
1/cfgtoollogs/emca/catest/emca_2011-01-11_04-11-01-PM.log for more details.


So what can i do in this case ? i try to recreate what more i can do ?


./emctl status dbconsole


Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
https://myhost:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is not running.


 ./emctl status agent


Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent is Not Running


Refer to Oracle Metlink Doc  [1222603.1]


we need to do the following :


1- Ignore any errors and continue with the installation or upgrade. The database will be created without errors.


2- Apply Patch 8350262 to your Oracle Home installation using OPatch


To apply the patch :
A-change to Patch directory .
B-export ORACLE_SID = ORCL
C-export ORACLE_HOME= /u01/oracle/product/10.2.0/db_1/
D-$ORACLE_HOME/OPatch/Opatch apply


Invoking OPatch 10.2.0.4.2


Oracle Interim Patch Installer version 10.2.0.4.2
Copyright (c) 2007, Oracle Corporation. All rights reserved.


Oracle Home : /myhost
Central Inventory : /scratch/pchebrol/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.4.2
OUI version : 10.2.0.4.0
OUI location : /myhost/oui
Log file location : /myhost/cfgtoollogs/opatch/opatch2011-01-02_11-00-00AM.log


ApplySession applying interim patch '8350262' to OH '/myhost'


Running prerequisite checks...


OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.


Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '8350262' for restore. This might take a while...
Backing up files affected by the patch '8350262' for rollback. This might take a while...


Patching component oracle.sysman.agent.core, 10.2.0.4.0a...
Updating jar file "/myhost/sysman/jlib/emCORE.jar" with
"/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/fsc/FSWalletUtil.class"
Updating jar file "/myhost/sysman/jlib/emCORE.jar" with
"/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/rep/RepWalletUtil.class"
Updating jar file "/myhost/sysman/jlib/emCORE.jar" with
"/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/util/RootCert.class"
Updating jar file "/myhost/sysman/jlib/emCORE.jar" with
"/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/util/SecConstants.class"
Updating jar file "/myhost/sysman/jlib/emd_java.jar" with "/sysman/jlib/emd_
java.jar/oracle/sysman/eml/sec/fsc/FSWalletUtil.class"
Updating jar file "/myhost/sysman/jlib/emd_java.jar" with "/sysman/jlib/emd_
java.jar/oracle/sysman/eml/sec/rep/RepWalletUtil.class"
Updating jar file "/myhost/sysman/jlib/emd_java.jar" with "/sysman/jlib/emd_
java.jar/oracle/sysman/eml/sec/util/RootCert.class"
Updating jar file "/myhost/sysman/jlib/emd_java.jar" with "/sysman/jlib/emd_
java.jar/oracle/sysman/eml/sec/util/SecConstants.class"
ApplySession adding interim patch '8350262' to inventory


Verifying the update...
Inventory check OK: Patch ID 8350262 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8350262 are present in Oracle Home.


OPatch succeeded.


3-If you using linux you need to kill all dbconsole process by “KillDbconsole” you will find it in the same patch directory , if you are using windows no need to kill anything .


$ /killDBConsole
ORACLE_HOME=/myhost/db_1
ORACLE_SID=caem31
State directory = /myhost/db_1/staxd10_caem31
WatchDog PID = 802932
DBconsole PID = 577716
EMAgent PID = 512156
Killing WatchDog (pid=802932) ...
Successfully killed process 802932
Killing DBConsole (pid=577716) ...
Successfully killed process 577716
Killing EMAgent (pid=512156) ...
Successfully killed process 512156


4-This not optional steps you need to do it :


./emctl secure dbconsole -reset


Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
https://myhost:5501/em/console/aboutApplication
Enter Enterprise Manager Root Password :
DBCONSOLE already stopped... Done.
Agent is already stopped... Done.
Securing dbconsole... Started.
Checking Repository... Done.
Checking Em Key... Done.
Checking Repository for an existing Enterprise Manager Root Key...
WARNING! An Enterprise Manager Root Key already exists in
the Repository. This operation will replace your Enterprise
Manager Root Key.
All existing Agents that use HTTPS will need to be
reconfigured if you proceed. Do you wish to continue and
overwrite your Root Key
(Y/N) ?
Y
Are you sure ? Reset of the Enterprise Manager Root Key
will mean that you will need to reconfigure each Agent
that is associated with this OMS before they will be
able to upload any data to it. Monitoring of Targets
associated with these Agents will be unavailable until
after they are reconfigured.
(Y/N) ?
Y
Generating Enterprise Manager Root Key (this takes a minute)... Done.Fetching Root Certificate from
the Repository... Done.
Updating HTTPS port in emoms.properties file... Done.
Generating Java Keystore... Done.
Securing OMS ... Done.
Generating Oracle Wallet Password for Agent.... Done.
Generating wallet for Agent ... Done.
Copying the wallet for agent use... Done.
Storing agent key in repository... Done.
Storing agent key for agent ... Done.
Configuring Agent...
Configuring Agent for HTTPS in DBCONSOLE mode... Done.
EMD_URL set in /myhost/myhost/sysman/config/emd.properties
Done.
Configuring Key store.. Done.
Securing dbconsole... Sucessful.


Sometimes reset secure its not enough the same error will be appear , I know its lot of steps but i told you use command line Ok Ok what i can do just recreate enterprise manager again :


emca -config dbcontrol db -repos recreate


Ok are you sure its working ??!!


emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
 Oracle Enterprise Manager 10g is running.
——————————————————————
Logs are generated in directory N:\oracle\product\10.2.0\db/klpdbscph601.klpcph.local_KLPSTAGE01/sys
man/log


Thank you all
hope this topic will be useful  .






	

	

	




	
	

		
Oracle Application server 10g unable to start HTTP
				

					Posted on  by Osama Mustafa in Application				


				

	

		
Hi all


Today When I was Supporting for some customer , i faces issue in application server 10gthe solution is so simple 


Let describe the problem :


when we use


./opmnctl startall


all service is up expect Http_server is down 

Check Logs nothing , Check Application server logs nothing 


And when you try to shutdown IAS again it will give you :



RCV : Permission denied 


For first i was thought its permission issue but who can change the permission .




the solution like the following :


On Os 


ps -ef | grep oracle 


you must find 2 process 
Oracle PID $PATH/opmn.d


kill them both 
Kill PID PID
or
Kill -9 PID
kill -9 PID


After this 
./opmnctl startall