Posts Tagged ‘Oracle DBA’

Unix for DBA

May 24th, 2017, posted in Solaris

Below are some of the basic unix commands which will be useful for Oracle DBA.

How to kill all similar processes with single command (in this case opmn)

ps -ef | grep opmn |grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}

Locating Files under a particular directory

find . -print |grep -i test.sql

Using AWK in UNIX

To remove a specific column of output from a UNIX command – for example to determine the UNIX process Ids for all Oracle processes on server (second column)

ps -ef |grep -i oracle |awk ‘{ print $2 }’

Changing the standard prompt for Oracle Users

Edit the .profile for the oracle user


Display top 10 CPU consumers using the ps command

/usr/ucb/ps auxgw | head -11

Show number of active Oracle dedicated connection users for a particular ORACLE_SID

ps -ef | grep $ORACLE_SID|grep -v grep|grep -v ora_|wc -l

Display the number of CPU’s in Solaris

psrinfo -v | grep “Status of processor”|wc -l

Display the number of CPU’s in AIX

lsdev -C | grep Process|wc -l

Display RAM Memory size on Solaris

prtconf |grep -i mem

Display RAM memory size on AIX

First determine name of memory device

lsdev -C |grep mem

then assuming the name of the memory device is ‘mem0’

lsattr -El mem0

Swap space allocation and usage

Solaris : swap -s or swap -l

Aix : lsps -a

Total number of semaphores held by all instances on server

ipcs -as | awk ‘{sum += $9} END {print sum}’

View allocated RAM memory segments

ipcs -pmb

Manually deallocate shared memeory segments

ipcrm -m ‘<ID>’

Show mount points for a disk in AIX

lspv -l hdisk13

Display amount of occupied space (in KB) for a file or collection of files in a directory or sub-directory

du -ks * | sort -n| tail

Display total file space in a directory

du -ks .

Cleanup any unwanted trace files more than seven days old

find . *.trc -mtime +7 -exec rm {} \;

Locate Oracle files that contain certain strings

find . -print | xargs grep rollback

Locate recently created UNIX files (in the past one day)

find . -mtime -1 -print

Finding large files on the server (more than 100MB in size)

find . -size +102400 -print

Crontab :

To submit a task every Tuesday (day 2) at 2:45PM

45 14 2 * * /opt/oracle/scripts/ > /dev/null 2>&1

To submit a task to run every 15 minutes on weekdays (days 1-5)

15,30,45 * 1-5 * * /opt/oracle/scripts/ > /dev/null 2>&1

To submit a task to run every hour at 15 minutes past the hour on weekends (days 6 and 0)

15 * 0,6 * * opt/oracle/scripts/ > /dev/null 2>&1


Installation Steps Of OAF or JDeveloper

May 7th, 2017, posted in Oracle, Windows

If you want to do some OAF Development in JDeveloper for the first time, you will need to do the following things.

1] Download JDeveloper Patch

Based on your instance release level, check out the version of JDeveloper to use using below link.

You can identify the OA Framework version in your instance by activating diagnostics and click the “About This Page” from any OAF page. Click the “Technology Components” tab. The OA Framework version in the top row of the table can then be matched to the JDeveloper Patch.

Download the JDeveloper Patch.

You can also use this query to find about your version :
SELECT release_name FROM fnd_product_groups;

2] Extract the JDeveloper patch in a directory say D:\DevSuiteHome_1\jdev

The patch actually contains below three directories

  • jdevbin – Includes an extended version of the Oracle JDeveloper 10g executable and OA Framework class libraries.
  • jdevhome – Includes the OA Framework Toolbox Tutorial source and developer working area.
  • jdevdoc – Contains documentation.

Installation Steps Of OAF or JDeveloper,Oracle DBA,Apps DBA,Application DBA,Oracle Installation,Installation of OAF,Installation JDeveloper

3] Define an environment variable

Define an environment variable JDEV_USER_HOME for your local machine. This has to be set to jdevhome\jdev directory. In above example it has to be D:\DevSuiteHome_1\jdev\jdevhome\jdev.

My Computer Properties Advanced tab Environment Variables New


Value: D:\DevSuiteHome_1\jdev\jdevhome\jdev

Installation Steps Of OAF or JDeveloper,Oracle DBA,Apps DBA,Application DBA,Oracle Installation,Installation of OAF,Installation JDeveloper,environment file,windows environment file

4] Create the shortcut of jdevW

Their you will find jdev and jdevW icons. The jdev is for UNIX environment and jdevW is for Windows environment.
Create the shortcut of jdevW. And send to desktop

Installation Steps Of OAF or JDeveloper,Oracle DBA,Apps DBA,Application DBA,Oracle Installation,Installation of OAF,Installation JDeveloper

Also can be check by this :

4] Move the DBC file

After creating the environment veriable we have to move the DBC file from Oracle Apps fnd top to jDeveloper patch.

Here we are using WinScp which connects to the server from our desktop system.
After finding where exactly the DBC file located go to that particular path and copy the DBC file.

After copying, in your desktop system go to
Jdevhome–> jdev–> dbc_files–> secure
Here we will not find any files or documents the folder is empty now we need to paste the DBC file which we copied from the Oracle Apps Instance Server.

5] Now Test Database

After moving the DBC file now open the JDeveloper and create the Data Base Connection.

After opening the file it will ask for Configure File Type Associations, on check box check all the available types to associate with JDeveloper.

Go to Connections tab after that Right Click on Data Base folder and then select New Database Connection…


2 3 4 5

6] Set The Default Project Properties

After creating the DataBase Connections, Set the default project properties.

Go to Tools -> Default Project Properties



In that select Runtime connection, adjust the runtime connection information, necessary for the database and Oracle E-Business Suite.

Brows the DBC file from the jDeveloper patch where we moved from the server.
(Jdevhome–> jdev–> dbc_files–> secure)

Username: The user name is Oracle Apps instance User Name
Password:  Password is Oracle Apps instance Password.

Application Short Name: In which application u want to run give the Application Short Name of that application.
Responsibility key: Responsibility key of that application short name.

 Application DBA, APPS DBA, Installation JDeveloper, Installation of OAF, Installation Steps Of OAF or JDeveloper, Oracle DBA, Oracle Installation


How To Upload Excel/Text File Data Into Oracle Table Using TOAD

February 27th, 2017, posted in Oracle

n this we will explain how to put Excel or text file data into an Oracle table. You can do this easily in SQL Server with a Copy (Ctrl+C) of the data from Excel then open the target table into edit mode and Paste (Ctrl+V). Done.

But this will not work in Oracle.

In an Oracle database you can do it using the import command. I will show you each and every step with snapshots.

Step 1 : Open TOAD.

Step 2 : Write the following query to create a test table:

  1. Create table TESTTABLE
  2. (
  3. NAME varchar2(20),
  4. Address varchar2(100),
  5. RollNo integer
  6. )

Step 3 : Now my table is ready. It’s time to create the data in an Excel file.

Step 4 : Go to Database -> Import -> Table Data.

Step 5 : Select the table you want to import the Excel data to.

Step 6 : Click on show data. Now execute the data button to enable the use. Click on that.

Step 7 : After clicking on Execute Wizard the following screen will open:

Next ->

Step 8 : Now, browse to your Excel file – > Next ->

Next ->

Step 9 : Choose the sheet and click on AutoMap. The AutoMap button will automatically bind your Excel sheet column data with the Oracle table column.

Next ->

Next ->

Here your Excel data has been bound with the table columns. Next ->

Step 10 : Click on Execute to finish the process. It may ask for sheet selection. Click on Okay with the desired sheet.

Here is your uploaded data:

Step 11 : Commit the Task by clicking on the commit button.

Using the preceding procedure you can easily import Excel data. You can also import text flat files data. All steps are nearly the same. The only change is the following step. In this import I need to choose the separator for the flat file. Apart from any separators you can use fixed width. In the attached example I used a comma separator.

I think the procedure is very clear with snapshots. If you have found any mistake in concept, please do comment.

Your comments will make me perfect in the future.

Thanks for reading.



Refresh Materialized View

February 16th, 2017, posted in Oracle

Problem sys@standby> startup mount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2217952 bytes Variable Size 490735648 bytes Database Buffers 335544320 bytes Redo Buffers 6606848 bytes Database mounted. sys@standby> alter database recover managed standby database using current logfile disconnect; alter database recover managed standby database using current logfile disconnect * ERROR at line 1: ORA-01153: an incompatible media recovery is active Cause This indicates a currently running media recovery process. Action sys@standby> alter database recover managed standby database cancel; sys@standby> alter database recover managed standby database using current logfile disconnect; Note When shutting down physical standby database, firstly turn off media recovery process. Otherwise the next time when starting up redo apply again, you will encounter error ORA-01153.

Refresh Materialized View

NAME: Refresh Materialized View
MODULE: Oracle Trade Management

Description: Refresh Materialized Views for Promotional Payments and Indirect Inventorty Tracking
Navigation: Oracle Trade Management Responsibility -> View -> Requests -> Submit a new request -> Select Single Request -> Click ‘OK’ -> Select Name of concurrent program / report.

Report Parameters:

  • mv_name : Name of the Materialized View

ORA-0131 Insufficient privileges

December 11th, 2016, posted in Oracle

ORA-0131: Insufficient privileges,ORA-0131,Insufficient privileges,ORA 0131,ORA Error, Insufficient, privileges,DEBUG CONNECT SESSION,system privilege,Debugging requires the DEBUG CONNECT,ORA Debugging,ORA Debugging,ORA Debugging requires,Oracle APPS DBA,ORACLE DBA,ORACLE DB,
Title :
Getting “ORA-01031: insufficient privileges” error when trying to Debug.

Description :
When trying to debug a procedure, package, etc., the following error messages are received:

ORA-01031: insufficient privileges

Cause :
The Oracle user or schema account does not have the required Oracle rights to debug an object.

Resolution :
Ensure the User or Schema has all the required Oracle privileges granted for debugging objects on that database.

1. connect as sysdba
2. exec the sql:
 grant debug connect session to XXXXXX;