ORA-00845: MEMORY_TARGET not supported on this system and ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

ORA-00845: MEMORY_TARGET not supported on this system and ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0


Database not coming up due to below errors (ORA-00845: MEMORY_TARGET not supported on this system and ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0)

Scene 1)
##########################################
SQL> startup nomount;
ORA-00845: MEMORY_TARGET not supported on this system
SQL> shut abort
ORACLE instance shut down.
SQL> exitDisconnected


Scene 2)
#########################################
SQL> startup nomount;
ORA-01078: failure in processing system parameters
ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0
SQL> shut abort;
ORACLE instance shut down.
SQL> exit
Disconnected


Scene 3)
#########################################
SQL> startup nomount
ORA-01078: failure in processing system parameters
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3072M
SQL>
SQL> exit
Disconnected
#########################################


These are all due to memory Memory related parameter which is not specified as it should be.


Solution:
Scene 1) This error always come if /dev/shm FS size is less then memory targetparameter.
To overcome from this situation either increase the /dev/shm to greater than memory target or keep less value of memory target from /dev/shm.
Other way is to use SGA_MAX_SIZE instead of MEMORY_TARGET.


Scene 2) This error only came if SGA_MAX_SIZE is set but aggregate value of individual memory parameter (db_cache_size, shared_pool_size, java_pool_size, stream_pool_size etc..) value is more than SGA_MAX_SIZE
To overcome from this situation either increase the SGA_MAX_SIZE or decrease aggregate value of individual memory parameter.


Scene 3) in case by mistaken SGA_MAX_SIZE and MEMORY_TARGET both has been define, and SGA_MAX_SIZE is greater than MEMORY_TARGET.
To overcome from this situation remove or keep size 0 either SGA_MAX_SIZE or MEMORY_TARGET parameter value


[oracle@abcdbhost01 dbs]$ sqlplus '"as sysdba"


SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 10 20:48:05 2014


Copyright (c) 1982, 2011, Oracle. All rights reserved.


Connected to an idle instance.


SQL> startup nomount
ORACLE instance started.


Total System Global Area 3340451840 bytes
Fixed Size 2232960 bytes
Variable Size 771755392 bytes
Database Buffers 2550136832 bytes
Redo Buffers 16326656 bytes
SQL>

Linux: How to monitor the nproc limit

Linux: How to monitor the nproc limit

You probably know about 'nproc' limits in Linux which are set in /etc/limits.conf and checked with 'ulimit -u'. But do you know how to handle the monitoring and be alerted when you're close the fixed limit?

Nproc and ps
Nproc is defined at OS level to limit the number of processes per user. Oracle 11.2.0.4 documentation recommends the following:

oracle soft nproc 2047
oracle hard nproc 16384
 
But that is often too low, especially when you have the Enterprise Manager agent or other java programs running.

Do you want to check that you are far from the limit? then you can use 'ps'. But beware, 'ps' by default does not show all processes.
 
In Linux, when doing multithreading, each thread is implemented as a light-weight process (LWP). And you must use the '-L' to see all of them.

Let's take an example. I have a system where 'ps -u oracle' returns 243 lines. But including LWPs shows a lot more processes which is near the limit:

$ ps h -Led -o user | sort | uniq -c | sort -n
      1 dbus
      1 ntp
      1 rpc
      1 rpcuser
      2 avahi
      2 haldaemon
      2 postfix
    166 grid
    400 root
   1370 oracle
 
So the 'oracle' user has 1370 processes. That's high. And this is the actual number where the nproc limit applies.

'ps -Lf' can show the detail. And even without '-L' we can display the NLWP which is the number of threads per process:

ps -o nlwp,pid,lwp,args -u oracle | sort -n
NLWP   PID   LWP COMMAND
   1  8444  8444 oracleOPRODP3 (LOCAL=NO)
   1  9397  9397 oracleOPRODP3 (LOCAL=NO)
   1  9542  9542 oracleOPRODP3 (LOCAL=NO)
   1  9803  9803 /u00/app/oracle/product/agent12c/core/12.1.0.3.0/perl/bin/perl /u00/app/oracle/product/agent12c/core/12.1.0.3.0/bin/emwd.pl agent /u00/app/oracle/product/agent12c/agent_inst/sysman/log/emagent.nohup
  19 11966 11966 /u00/app/11.2.0/grid/bin/oraagent.bin
1114  9963  9963 /u00/app/oracle/product/agent12c/core/12.1.0.3.0/jdk/bin/java ... emagentSDK.jar oracle.sysman.gcagent.tmmain.TMMain
 
The Oracle 12c EM agent has started 1114 threads and the grid infrastructure 'oraagent.bin' has 19 threads. In addition to that I've a lot of other monothreaded processes. This is how we reach 1370 which is the exact value to compare to the nproc limit.

So what are the good values to set? About the high number of threads for EM agent 12c, there are a few bugs. And I suspect that 1000 threads is too much, especially when checking them with 'jstack' I see that they are "CRSeOns" threads that should not be used in 11.2.0.2 and higher. But that's another problem which I'm currently investigating. When you reach the nproc limit, the user will not be able to create new processes. clone() calls will return EAGAIN and that is reported by Oracle as:

ORA-27300: OS system dependent operation:fork failed with status: 11
ORA-27301: OS failure message: Resource temporarily unavailable
And that is clearly bad when it concerns an +ASM instance or archiver processes.

The goal of the nproc limit is only to prevent 'fork bombs' where a process forks forever and exhausts all resources. So there is no problem to increase this limit. However if you set it high for some users ('oracle' and 'grid' usually), it can be a good idea to monitor the number of processes with the ps h -L above. Because having too many processes is suspect and increasing the limit just hides a process leak and defer the failure.

In 'ps h -L -o' The argument 'h' is there to remove the header line, and '-L' to show all processes including LWP. Then you can count with 'wc -l'.

The proof
In order to be sure that 'ps h -L' gives the exact number, I have tested it. In case you want to check this on your system, here is how to do it. And please report any difference.

First, set your limit to 1024 processes. This is a limit for my user, and the limit is set for my shell and all its child processes:

[oracle@VM211 ocm]$ ulimit -u 1024
Now you can check it:

[oracle@VM211 ocm]$ ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 15919
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes (-u) 1024
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

[root@VM211 ocm]# ps h -Lu oracle | wc -l
1023
 
Recommended values for Oracle
Currently this is what is set on Oracle linux 6 for 11gR2 by the preinstall package (in /etc/security/limits.conf):

oracle   soft   nproc    16384
oracle   hard   nproc    16384

For 12c, these are set in /etc/security/limits.d/oracle-rdbms-server-12cR1-preinstall.conf which overrides /etc/security/limits.conf:

oracle soft nproc 16384
oracle hard nproc 16384
And just for your information, here is what is set in the ODA X4-2:

oracle soft nproc 131072

So what do you want to set? You probably don't want it too low and experience 'resource temporarily unavailable'. But what you don't want either is 100000 processes on your server. So my recommendation is to set it high but monitor it when the number of processes reaches something that is not sensible. Then you prevent having the system down in case of process leak, but you can detect it and ask for a patch.

Special thanks to Mr. Franck Pachot for such easy illustration.
Regards
Shahzad

ORAchk Health Checks For The Oracle Stack (ORACHK 2.2.4 and above)

ORAchk Health Checks For The Oracle Stack (ORACHK 2.2.4 and above)
Posted: 18/03/2014 | Author: Levi Pereira | Filed under: 10g R1, 10g R2, 11g R1, 11g R2, 12c R1, ASM - Automatic Storage Management, Clusterware / Grid Infrastructure, Database, My Oracle Support (Metalink), Oracle |Leave a comment

Oracle Database 12c (12.0.1.*) and 11g (11.2.0.4) comes with new feature called RACcheck.
Although the RACcheck already exists before these releases. (In 2011 I posted about benefits of RACcheck)

Brief of RACcheck.
.....

RACcheck - The Oracle RAC Configuration Audit Tool
RACcheck is designed to audit vital configuration settings for the Oracle Database, single instance databases, as well as Oracle Real Application Clusters (Oracle RAC) databases. It also includes checks for Oracle Clusterware, Oracle Automatic Storage Management (Oracle ASM) and Oracle Grid Infrastructure.

RACcheck provides best practices recommedations considering the whole stack, including Maximum Availability Architecture (MAA) configurations and is therefore the ideal tool for regular health checks as well as pre- and post-upgrade best practices assessments.
.....

Now Oracle replace/renamed Oracle RACcheck to ORAchk.

ORAchk- Health Checks for the Oracle Stack

ORAchk replaces the popular RACcheck to support a wider range of products. ORAchk version 2.2.4 is now available for download and includes the following key features:

RACcheck renamed to ORAchk

ORAchk daemon auto-start mode after node reboot (init integration)

Merge multiple ORAchk collection reports

Upload of installed patches to database

Collection Manager for ORAchk, RACcheck and Exachk (Document 1602329.1)

ORAchk signature file in /tmp on all nodes to verify last ORAchk run

New checks and bug fixes, including

30 Oracle Ebusiness AP module data integrity checks

12 new Database checks

8 new Solaris system checks

Supported Platforms

Linux x86-64* (Enterprise Linux, RedHat and SuSE 9, SuSE 10 & SuSE 11)

Oracle Solaris SPARC (Solaris 10 and 11)

Oracle Solaris x86-64 (Solaris 10 and 11)

AIX **

HPUX**

* 32-bit platforms not supported, no planned support for Linux Itanium
**Requires BASH Shell 3.2 or higher to be installed

Supported Oracle Releases

10gR2

11gR1

11gR2

12cR1

When to Run ORAchk

After initial Oracle RAC deployment

Before planned system maintenance

After planned system maintenance

At least once every three months

Install/Configure

It is recommended to run the tool as the database software owner (e.g. oracle). The user may run the tool as the Grid Infrastructure software owner (e.g. grid) and it will collect the same data but database credentials must manually be supplied to perform the database related audit checks. Typically when run as oracle the customer will have OS authentication set up for the oracle database software owner and the database login credentials will not be needed.

Download ORAchk

Stage Location:
It is recommended that the kit be staged and operated from a local filesystem on a single database server in order to provide the best performance possible.

$ mkdir -p /u01/app/oracle/orachk

[oracle@node11g01 install]$ cd /u01/app/oracle/orachk

[oracle@node11g01 orachk]$ unzip orachk.zip
Archive: orachk.zip
inflating: raccheck
inflating: rules.dat
inflating: collections.dat
inflating: readme.txt
inflating: orachk
creating: .cgrep/
inflating: .cgrep/ogghc_12101.sql
inflating: .cgrep/lcgrep4
inflating: .cgrep/checkDiskFGMapping.sh
inflating: .cgrep/ogghc_11204.sql
inflating: .cgrep/lcgreps9
inflating: .cgrep/ogghc_11203.sql
inflating: .cgrep/scgrepx86
inflating: .cgrep/acgrep
inflating: .cgrep/oracle-upstarttmpl.conf
inflating: .cgrep/check_reblance_free_space.sql
inflating: .cgrep/CollectionManager_App.sql
inflating: .cgrep/exalogic_zfs_checks.aksh
inflating: .cgrep/hiacgrep
inflating: .cgrep/init.tmpl
inflating: .cgrep/lcgreps10
inflating: .cgrep/preupgrd.sql
inflating: .cgrep/diff_collections.pl
inflating: .cgrep/merge_collections.pl
inflating: .cgrep/ggdiscovery.sh
creating: .cgrep/profiles/
inflating: .cgrep/profiles/DA94919CD0DE0913E04312C0E50A7996.prf
inflating: .cgrep/profiles/D49C0FBF8FBF4B1AE0431EC0E50A0F24.prf
extracting: .cgrep/profiles/F13E11974A282AB3E04312C0E50ABCBF.prf
inflating: .cgrep/profiles/EF6C016813C51366E04313C0E50AE11F.prf
inflating: .cgrep/profiles/D8367AD6754763FEE04312C0E50A6FCB.prf
inflating: .cgrep/profiles/DF65D6117CB41054E04312C0E50A69D1.prf
inflating: .cgrep/profiles/EA5EE324E7E05128E04313C0E50A4B2A.prf
inflating: .cgrep/profiles/E1BF012E8F210839E04313C0E50A7B68.prf
inflating: .cgrep/profiles/D462A6F7E9C340FDE0431EC0E50ABE12.prf
inflating: .cgrep/profiles/D49AD88F8EE75CD8E0431EC0E50A0BC3.prf
inflating: .cgrep/profiles/E2E972DDE1E14493E04312C0E50A1AB1.prf
inflating: .cgrep/profiles/F32F44CE0BCD662FE04312C0E50AB058.prf
inflating: .cgrep/profiles/E8DF76E07DD82E0DE04313C0E50AA55D.prf
inflating: .cgrep/profiles/D49B218473787400E0431EC0E50A0BB9.prf
inflating: .cgrep/profiles/D49C0AB26A6D45A8E0431EC0E50ADE06.prf
inflating: .cgrep/profiles/DFE9C207A8F2428CE04313C0E50A6B0A.prf
inflating: .cgrep/profiles/D49C4F9F48735396E0431EC0E50A9A0B.prf
inflating: .cgrep/profiles/D49BDC2EC9E624AEE0431EC0E50A3E12.prf
inflating: .cgrep/profiles/DF65D0F7FB6F1014E04312C0E50A7808.prf
inflating: .cgrep/scnhealthcheck.sql
inflating: .cgrep/pxhcdr.sql
inflating: .cgrep/lcgrep5
inflating: .cgrep/scgrep
inflating: .cgrep/raw_data_browser.pl
inflating: .cgrep/profiles.dat
inflating: .cgrep/rack_comparison.py
inflating: .cgrep/versions.dat
inflating: .cgrep/create_version.pl
inflating: .cgrep/lcgreps11
inflating: .cgrep/utluppkg.sql
inflating: .cgrep/utlusts.sql
inflating: .cgrep/reset_crshome.pl
inflating: .cgrep/asrexacheck
inflating: .cgrep/lcgrep6
inflating: .cgrep/utlu112i.sql
inflating: UserGuide.txt
Running ORAchk Interactively 

[oracle@node11g01 orachk]$ ./orachk

CRS stack is running and CRS_HOME is not set. Do you want to set CRS_HOME to /u01/app/11.2.0/grid?[y/n][y]

Checking ssh user equivalency settings on all nodes in cluster

Node node11g02 is configured for ssh user equivalency for oracle user

Searching for running databases . . . . .

. .
List of running databases registered in OCR
1. dborcl
2. None of above

Select databases from list for checking best practices. For multiple databases, select 1 for All or comma separated number like 1,2 etc [1-2][1].1
. .

Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
-------------------------------------------------------------------------------------------------------
Oracle Stack Status
-------------------------------------------------------------------------------------------------------
Host Name CRS Installed ASM HOME RDBMS Installed CRS UP ASM UP RDBMS UP DB Instance Name
-------------------------------------------------------------------------------------------------------
node11g01 Yes Yes Yes Yes Yes Yes dborcl_1
node11g02 Yes Yes Yes Yes Yes Yes dborcl_2
-------------------------------------------------------------------------------------------------------

Copying plug-ins

. . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . .

17 of the included audit checks require root privileged data collection . If sudo is not configured or the root password is not available, audit checks which require root privileged data collection can be skipped.

1. Enter 1 if you will enter root password for each host when prompted

2. Enter 2 if you have sudo configured for oracle user to execute root_orachk.sh script

3. Enter 3 to skip the root privileged collections

4. Enter 4 to exit and work with the SA to configure sudo or to arrange for root access and run the tool later.

Please indicate your selection from one of the above options for root access[1-4][1]:- 2

*** Checking Best Practice Recommendations (PASS/WARNING/FAIL) ***

Collections and audit checks log file is
/u01/app/oracle/orachk/orachk_node11g01_dborcl_031814_170552/log/orachk.log
.
.
.
.
---------------------------------------------------------------------------------
CLUSTERWIDE CHECKS
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------

Detailed report (html) - /u01/app/oracle/orachk/orachk_node11g01_dborcl_031814_170552/orachk_node11g01_dborcl_031814_170552.html

UPLOAD(if required) - /u01/app/oracle/orachk/orachk_node11g01_dborcl_031814_170552.zip

Below the report generated in this test.
Oracle RAC Assessment Report

orachk usage options 

[oracle@node11g01 orachk]$ ./orachk -h
Usage : ./orachk [-abvhpfmsuSo:c:t:]
-a All (Perform best practice check and recommended patch check)
-b Best Practice check only. No recommended patch check
-h Show usage
-v Show version
-p Patch check only
-m exclude checks for Maximum Availability Architecture (MAA) scorecards(see user guide for more details)
-u Run orachk to check pre-upgrade or post-upgrade best practices for 11.2.0.3,11.2.0.4.0 and 12.1.0.1
-o pre or -o post is mandatory with -u option like ./orachk -u -o pre
-f Run Offline.Checks will be performed on data already collected from the system
-o Argument to an option. if -o is followed by v,V,Verbose,VERBOSE or Verbose, it will print checks which passs on the screen
if -o option is not specified,it will print only failures on screen. for eg: orachk -a -o v

-clusternodes
Pass comma separated node names to run orachk only on subset of nodes.
-dbnames
Pass comma separated database names to run orachk only on subset of databases
-localonly
Run orachk only on local node.
-debug
Run orachk in debug mode. Debug log will be generated.
eg:- ./orachk -debug
-nopass
Skip PASS'ed check to print in orachk report and upload to database.

-noscore
Do not print healthscore in HTML report.

-diff [-outfile ]
Diff two orachk reports. Pass directory name or zip file or html report file as &

-exadiff
Compare two different Exalogic rack and see if both are from the same release.Pass directory name or zip file as & (applicable for Exalogic only)

-c Used only under the guidance of Oracle support or development to override default components

-
initsetup : Setup auto restart. Auto restart functionality automatically brings up orachk daemon when node starts
initrmsetup : Remove auto restart functionality
initcheck : Check if auto restart functionality is setup or not
initpresetup : Sets root user equivalency for COMPUTE, STORAGE and IBSWITCHES.(root equivalency for COMPUTE nodes is mandatory for setting up auto restart functionality)

-d
start : Start the orachk daemon
start_debug : Start the orachk daemon in debug mode
stop : Stop the orachk daemon
status : Check if the orachk daemon is running
info : Print information about running orachk daemon
stop_client : Stop the orachk daemon client
nextautorun : print the next auto run time
-daemon
run orachk only if daemon is running
-nodaemon
Dont use daemon to run orachk
-set
configure orachk daemon parameter like "param1=value1;param2=value2... "

Supported parameters are:-

AUTORUN_INTERVAL :- Automatic rerun interval in daemon mode.Set it zero to disable automatic rerun which is zero.

AUTORUN_SCHEDULE * * * * :- Automatic run at specific time in daemon mode.
- - - -
▒ ▒ ▒ ▒
▒ ▒ ▒ +----- day of week (0 - 6) (0 to 6 are Sunday to Saturday)
▒ ▒ +---------- month (1 - 12)
▒ +--------------- day of month (1 - 31)
+-------------------- hour (0 - 23)

example: orachk -set "AUTORUN_SCHEDULE=8,20 * * 2,5" will schedule runs on tuesday and friday at 8 and 20 hour.

AUTORUN_FLAGS : orachk flags to use for auto runs.

example: orachk -set "AUTORUN_INTERVAL=12h;AUTORUN_FLAGS=-profile sysadmin" to run sysadmin profile every 12 hours

orachk -set "AUTORUN_INTERVAL=2d;AUTORUN_FLAGS=-profile dba" to run dba profile once every 2 days.

NOTIFICATION_EMAIL : Comma separated list of email addresses used for notifications by daemon if mail server is configured.

PASSWORD_CHECK_INTERVAL : Interval to verify passwords in daemon mode

COLLECTION_RETENTION : Purge orachk collection directories and zip files older than specified days.

-unset
unset the parameter
example: orachk -unset "AUTORUN_SCHEDULE"

-get
Print the value of parameter

-excludeprofile
Pass specific profile.
List of supported profiles is same as for -profile.

-merge
Pass comma separated collection names(directory or zip files) to merge collections and prepare single report.
eg:- ./orachk -merge orachk_hostname1_db1_120213_163405.zip,orachk_hostname2_db2_120213_164826.zip

-vmguest
Pass comma separated filenames containing exalogic guest VM list(applicable for Exalogic only)

-hybrid [-phy]
phy :Pass comma separated physical compute nodes(applicable for Exalogic only)
eg:- ./orachk -hybrid -phy phy_node1,phy_node2

-profile Pass specific profile.
List of supported profiles:

asm asm Checks
clusterware Oracle clusterware checks
compute_node Compute Node checks (Exalogic only)
control_VM Checks only for Control VM(ec1-vm, ovmm, db, pc1, pc2). No cross node checks
dba dba Checks
ebs Oracle E-Business Suite checks
el_extensive Extensive EL checks
el_lite Exalogic-Lite Checks(Exalogic Only)
el_rackcompare Data Collection for Exalogic Rack Comparison Tool(Exalogic Only)
goldengate Oracle GoldenGate checks
maa Maximum Availability Architecture Checks
obiee obiee Checks(Exalytics Only)
storage Oracle Storage Server Checks
switch Infiniband switch checks
sysadmin sysadmin checks
timesten timesten Checks(Exalytics Only)
virtual_infra OVS, Control VM, NTP-related and stale VNICs check (Exalogic Only)
zfs ZFS storage appliances checks (Exalogic Only)

-cells
Pass comma separated storage server names to run orachk only on selected storage servers.

-ibswitches
Pass comma separated infiniband switch names to run orachk only on selected infiniband switches.

-zfsnodes
Pass comma separated ZFS storage appliance names to run orachk only on selected storage appliances.

ORAchk Other Useful Options Not Covered Here

Using ORAchk Silently

ORAchk can be optionally run in “silent” or “non-interactive” mode in order to enable scheduling and automation
Is required only if customer does not want to use orachk daemon functionality.

Using ORAchk Daemon Mode Operation

This functionality permit non-interactive (batch or silent mode) execution on a regular interval.

When running ORAchk in daemon mode, the most recent and next most recent (if any) collection reports are automatically compared. If the mail address is configured a summary will be emailed along with attachments for the reports and the comparison report.

Report Comparisons with ORAchk

ORAchk has the ability to perform report comparisons between 2 ORAchk reports.
This allows for trending of Success Factor and Best Practice changes over time, after planned maintenance, etc within a user friendly HTML report.

ORAchk in Upgrade Readiness Mode

ORAchk can be used to obtain an automated 11.2.0.3 (or above) Upgrade Readiness Assessment.
The goal of the ORAchk Upgrade Readiness Assessment is to make the process of upgrade planning for Oracle RAC and Oracle Clusterware target versions 11.2.0.3 and above as smooth as possible by automating many of the
manual pre and post checks detailed in various upgrade related documents.

Refer MoS Notes for more details:
ORAchk - Oracle Configuration Audit Tool (Doc ID 1268927.2)

ORAchk Users Guide
For details instructions on how to run ORAchk including troubleshooting steps, available options, etc.

Master Note For Oracle Database Server Installation (Doc ID 1156586.1)

Master Note For Oracle Database Server Installation (Doc ID 1156586.1)

-------------------------------------------------------------------------------------------

Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 11.2.0.2.0 - Release: 8.1.7 to 11.2
Information in this document applies to any platform.

Purpose
This Master Note is intended to provide an index and references to the most frequently used My Oracle Support Notes with respect to Oracle Database Installation.This Master Note is subdivided into categories to allow for easy access and reference to notes that are applicable to your area of interest, within INSTALLATION.This includes the following categories:

* Certification

* Installation Guides

* OS Requirements

* Scripts

* Silent Install

* Diagnostic Tools

* Known Issues

* General Information

* Using My Oracle Support Effectively

* Generic Link



Scope and Application
This note guides you through the process of Oracle Database Installation.

This note applies to the following versions of these products:

Oracle Server - Enterprise Edition - Version: 8.1.7.1 to 8.1.7.x
Oracle Server - Enterprise Edition - Version: 9.0.1.1 to 9.0.1.x
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 9.2.0.x
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.x
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.1.0.x
Oracle Server - Enterprise Edition - Version: 11.2.0.1 to 11.2.0.x



Master Note For Oracle Database Server Installation
Certification
For the latest Database Certification always refer to My Oracle Support (MOS) Certify section.

At the same time the documentation listed below help you in understanding the Oracle Database Certification:

Note.415166.1 Oracle Database Client Certification Notes 10g Release 2 (10.2.0.3) for Microsoft Windows Vista
Note.161818.1 Oracle Database (RDBMS) Releases Support Status Summary
Note.207303.1 Client / Server / Interoperability Support Between Different Oracle Versions
Note.77627.1 Oracle Database Server product support Matrix for Windows 2000
Note.266043.1 Support of Linux and Oracle Products on Linux
Note.464754.1 Certified Software on Oracle VM
Note.753601.1 SUPPORT FOR ORACLE DATABASE ON WPARS UNDER AIX 6.1

Installation Guides
Oracle Database documentation, 11g Release 2 (11.2).
Installation manuals and platform-specific books are included in this link.

Oracle Database documentation, 11g Release 1 (11.1).
Installation manuals and platform-specific books are included in this link.

Oracle Database documentation, 10g Release 2 (10.2).
Installation manuals and platform-specific books are included in this link.

Oracle Database documentation, 10g Release 1 (10.1).
Installation manuals and platform-specific books are included in this link.

Oracle9i documentation, Release 2 (9.2).
Oracle9i documentation, Release 1 (9.0.1).
Installation manuals and platform-specific books are included in this search, for 9.2 only.

OS Requirements
Most of the installation and relinking issues appear if you don't satisfy the minimum requirements for the installation. Therefore if you are raising any SR for installation issue it is better to ensure that you have all these requirements mentioned in the below articles before raising the SR.

The documentation listed below help you in understanding the OS Requirements for Oracle Database Installation:
Generic
Note.169706.1 Oracle Database on Unix AIX,HP-UX,Linux,Mac OS X,Solaris,Tru64 Unix Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)
Note.268025.1 Server Installation - Unix Knowledge Browser Product Page
Note.750540.1 What are the requirements for an Oracle Home Name in the Inventory?
Note.389678.1 Are Compilers Necessary for Oracle RDBMS Installation?

Linux
Note.376183.1 Defining a "default RPMs" installation of the RHEL OS
Note.851598.1 Master Note of Linux OS Requirements for Database Server
Note.761261.1 Recommended metalink articles for Oracle Database 10gR2 installation on zLinux (s390x)
Note.415182.1 DB Install Requirements Quick Reference - zSeries based Linux
Note.341507.1 Oracle Database Server on Linux on IBM POWER

AIX
Note.889464.1 Is Oracle 10g And 11g Database Compatible With AIX Version 6.1
Note.859856.1 Oracle 10g R2 database server software for AIX 6.1
Note.417451.1 How To Determine Whether an APAR has been Fixed in AIX Version/Maintenance Level
Note.201019.1 AIX Quick Start Guide - 9.2.0 RDBMS Installation
Note.182037.1 AIX Quick Start Guide - 9.0.1 RDBMS Installation
Note.109843.1 AIX Quick Start Guide - 8.1.X RDBMS Installation
Note.109842.1 AIX Quick Start Guide - 8.0.X RDBMS Installation

Solaris
Note.201021.1 SOLARIS Quick Start Guide - 9.2.0 RDBMS Installation
Note.148673.1 SOLARIS Quick Start Guide - 9.0.x RDBMS Installation
Note.108413.1 SOLARIS Quick Start Guide - 8.0.X RDBMS Installation
Note.108306.1 SOLARIS Quick Start Guide - 8.1.X RDBMS Installation
Note.743042.1 Requirements for Installing Oracle 11gR1 RDBMS on Solaris 10 SPARC 64-bit
Note 971464.1 FAQ - 11gR2 requires Solaris 10 update 6 or greater

HP-UX
Note.201023.1 HP-UX Quick Start Guide - 9.2.0 RDBMS Installation
Note.154360.1 HP-UX Quick Start Guide - 9.0.x RDBMS Installation
Note.108387.1 HP-UX Quick Start Guide - 8.1.X RDBMS Installation
Note.108381.1 HP-UX Quick Start Guide - 8.0.X RDBMS Installation

Scripts
Note.296665.1 Pre-Install checks for 10gR1 RDBMS (10.1.x) - Linux AMD64/EM64T Platforms
Note.283749.1 Pre-Install checks for 10gR1 RDBMS (10.1.x) - Linux Itanium 64 (IA64) Platforms
Note.283748.1 Pre-Install checks for 10gR1 RDBMS (10.1.x) - Linux x86 Platforms
Note.189256.1 UNIX Script to Verify Installation Requirements for Oracle 8.0.5 to 9.2 versions of RDBMS

Silent Install
Note.136660.1 Working Response files for a silent install of Oracle Server on MS-Windows- Part 1 of 3
Note.136661.1 Database Configuration Response file for performing silent install - Part 2 of 3
Note 136662.1 Network Configuration Reponse file for performing silent install - Part 3 of 3
Note 388451.1 How to create a Response File for Silent Installation?
Note 885643.1 How to install 11gR2 Database/Client Software in Silent mode without using response file
Note 808275.1 How to install Oracle Database Patchset software in Silent mode without using response file
Note 782919.1 How to install Oracle Database Server software silently with customized listener configuration
Note 782918.1 How to install/deinstall Oracle Database Server software silently without response file
Note 435680.1 Response File Installation of Database Server 10.2 (Suppressed or Silent mode)
Note 434803.1 Silent Installation for Oracle Server 10.1
Note 434315.1 How Can You Install Oracle Without Using The Oracle Universal Installer( runInstaller) GUI and Xserver especially When Doing A Remote Installation ?
Note 405124.1 How to Prevent a Silent Install from Prompting for User Input to Run the 'root.sh' Script
Note 228916.1 How to Troubleshoot a Silent Installation

Diagnostic Tools
Note.181459.1 How To Troubleshoot an Oracle Database Software Installation on UNIX
Note.780551.1 How To Diagnose Linking Errors During Base Install of Oracle RDBMS
Note.757964.1 Oracle Universal Installer ( OUI ) Error Messages and Solution Reference List

Known Issues
Note.833732.1 How To quick Deal with Relink, make, Failed on Linux ? Errors bin/sh /usr/bin/cc cannot execute binary file. or undefined reference to `dl_iterate_phdr@GLIBC_x.x.y'.
Note.848700.1 11gR1 on Solaris failing at relink phase with errors like "ld fatal relocation error" and then "ld fatal library -lclntsh not found"

Note.820135.1 What Does -ignoreSysPrereqs Ignore When Invoking runInstaller?
Note.867562.1 11gR1 OUI Error "fatal libCrun.so.1 open failed No such file or directory"
Note.852241.1 Installation of 11G fails on relink with INFO ld fatal library -lumem not found
Note.849336.1 Unable to launch OUI
Note.832074.1 AIX 6.1 "Dependent module /usr/lib/libXpm.a(shr_64.o) could not be loaded" Error Attempting To Execute 11.1.0 runInstaller

Note.1083353.1 11gR2 Pre-requirement APAR's check fail for AIX 6.1
Note.879102.1 Stand Alone 11.1.0.6 Silent Installation on AIX 5.3 Fails On Prerequisite Check For rsct.basic.rte(0.0) and rsct.compat.clients.rte(0.0)
NOTE:966929.1 - 11gR2 INSTALL ERRORS on Solaris 10
NOTE:989727.1 - 11GR2 INSTALL ON SOLARIS FAILS MANY PREREQUISITE CHECKS

General Information
Note.168607.1 Oracle Server Installation FAQ
Note.787705.1 How to identify correct Oracle Database software to install on Linux platform
Note.317257.1 Running Oracle Database in Solaris 10 Containers - Best Practices
Note.131321.1 How to Relink Oracle Database Software on UNIX
Note.738771.1 How to Log Good Service Request for Relink issues?
Note.458893.1 Oracle Universal Installer (OUI) FAQ
Note.736819.1 How to Log Good Service Request for Oracle Universal Installer (OUI) issues?
Note.429074.1 Can You Run Different Releases Of The Oracle Database On The Same Computer At The Same Time?

Using My Oracle Support Effectively
Note 374370.1 New Customers Start Here
Note 747242.5 My Oracle Support Configuration Management FAQ
Note 868955.1 My Oracle Support Health Checks Catalog
Note 166650.1 Working Effectively With Global Customer Support
Note 199389.1 Escalating Service Requests with Oracle Support Services

Generic Links
Note 854428.1 Patch Set Updates for Oracle Products
Note 1061295.1 Patch Set Updates - One-off Patch Conflict Resolution
Note 268895.1 Oracle Database Server Patchset Information, Versions: 8.1. 7 to 11.2.0
Note 161549.1 Oracle Database Server and Networking Patches for Microsoft Platforms
Note 161818.1 Oracle Database (RDBMS) Releases Support Status Summary
Information on Critical Patch Updates (CPU's)
About Oracle
Oracle Corporation, an enterprise software company, engages in the development, manufacture, distribution, servicing, and marketing of database, middleware, and application software worldwide.
The company was founded in 1977 and is headquartered in Redwood City, California.

OBIA: How to Find Software Versions and Patches in an Oracle Business Intelligence Applications Environment

OBIA: How to Find Software Versions and Patches in an Oracle Business Intelligence Applications Environment

MOS 1519745.1

The Products versions can be found as below:

Oracle Business Intelligence Enterprise Server (OBIEE):

Provide the version.txt file from the following directory

for OBIEE 10g <BI_Home>\OracleBI\ 

for OBIEE 11g <BI_Home>\Oracle_BI1\bifoundation

OBIEE Applied Patches:

Patches applied can be found through

for OBIEE 10g, please review Note 1135013.1 - How to Keep Track of OBIEE 10g Patches Applied? How To Handle Version.Txt Files When You Have Multiple Patches? Are Patches Cumulative?

for OBIEE 11g, use "opatch lsinventory". For more information, review Note 1220799.1 - OBIEE 11g: How to Apply Patches Using Opatch

Oracle Business Intelligence Applications (OBIA):

Provide the version_apps.txt file from the following directory

for OBIEE 10g <BI_Home>\OracleBI\Document

for OBIEE 11g <FMW_Home>\Oracle_BI1\bifoundation\biapps

This can be obtained only from the machine where you have installed Oracle Business Intelligence Administration Tool and where you ran the Oracle Business Intelligence Applications installer to install the Oracle Business Intelligence Applications metadata.

Informatica Admin Console:

Start > Programs > Informatica PowerCenter > Server > Launch Admin Console

Click on the 'About' link

Informatica Designer:

Start > Programs > Informatica PowerCenter > Client > PowerCenter Designer

From the menu options; select Help > Informatica Designer

Provide the 'Version' information from this dialog window

For example:

  Product: Informatica PowerCenter Administration Console

  Version:

  Build Date:

  Build Number: 

Informatica Server:

Provide the version.txt from your <INFA_HOME>\PowerCenterxxx\server directory.

For example:

  Version:

  HotFixNumber:

  Product: Informatica PowerCenter Server

  PreviousInstalledVersion:

Informatica Java:

Provide the Java version installed for Informatica from <INFA_HOME>/java/bin directory and run "./java -version"

Data Warehouse Administration Console (DAC) Client:

Provide the DAC version (DAC Client > Help > About DAC)

DAC Server:

On the DAC server machine, execute "serverinfo" (.bat or .sh) and provide results.

DAC Applied Patches:

DAC Build version usually looks like "DAC Build # 10.1.3.4.1.patch.20120711.0516"

This Shows the DAC version "10.1.3.4.1" and also shows the Build date of the Patch applied "20120711".

"20120711" shows that DAC Patch 14306642 - "DAC 10.1.3.4.1 CUMULATIVE PATCH FOR BI APPS - JUL 2012" is applied.

DAC Container:

The DAC Container (DAC Client > Design > which container in drop down list)

Also confirm if this is a Custom DAC Container.

Application Source System Type:

Provide the Application Source System Type and Version.

For example: EBS R12.1.3 or Siebel 8.x or PeopleSoft

Source OLTP Database:

Provide the type and full database version (including fix packs and Patches) of the Source OLTP system.

Target OLAP Database:

Provide the type and full database version (including fix packs and Patches) of the Target OLAP system.

Reference - MOS 1519745.1

FRM-92095: Oracle JInitiator version too low. Please install version 1.1.8.2 or higher

ERROR :

FRM-92095: Oracle JInitiator version too low. Please install version 1.1.8.2 or higher

Solution:-

Create System Environment variable in that PC with following name and value :

Name : JAVA_TOOL_OPTIONS
Value : -Djava.vendor="Sun Microsystems Inc."

Troubleshooting ORA-27300 ORA-27301 ORA-27302 Errors

Troubleshooting ORA-27300 ORA-27301 ORA-27302 Errors

NOTE:848387.1 - With NUMA Enabled, Database Fails To Open With ORA-600[ksbmoveme4], ORA-27300, ORA-27301, ORA-27302 Errors Reported
NOTE:300956.1 - Ora-27302: sskgxpsnd1 - Starting Instance
NOTE:314179.1 - Instance Startup Fails With Error ORA-27154,ORA-27300,ORA-27301,ORA-27302
NOTE:3411021.8 - Bug 3411021 - Oracle process may die due to ORA-27300/ORA-27301/ORA-27302 at skgxppost1
NOTE:746888.1 - ORA-27302: Failure Occurred at: skgxpvfymmtu Signalled in the Alert.log
BUG:7620133 - HIGH NUMBER OF DBW* PROCESSES SPAWNED IN 11.1.0.7
NOTE:458442.1 - 10.2.0.3 PMON Crashes on Startup on AIX 5L 5.3 ML05 -- Works on ML06
NOTE:277399.1 - DBMS_SCHEDULER Fails To Execute Jobs With Program_type EXECUTABLE On HP-UX
NOTE:392006.1 - Ora-27300 OS system dependent operation:fork failed with status: 11
NOTE:295832.1 - Unable To Create Database Ora-00603, ORA-27300
NOTE:1252265.1 - ORA-27300 ORA-27301 ORA-27302: failure occurred at: skgpalive1
NOTE:438205.1 - ORA-27300 ORA-27301 ORA-27302 ORA-27157 Database Crash
NOTE:453959.1 - Cannot Connect As "/ as sysdba" ORA-27140 ORA-27300 ORA-27301 ORA-27302 And ORA-27303 In Trace File
NOTE:356640.1 - ORA-27300, ORA-27301, ORA-27302: Failure Occurred At: Skgpalive1 During 'Shutdown'
NOTE:465002.1 - Database Crash With Error ORA-00490
NOTE:466370.1 - ORA-7445 [ACCESS_VIOLATION] [unable_to_trans_pc] [UNABLE_TO_WRITE] ORA-27301: OS failure message: Not enough storage ORA-27300 ORA-27302
NOTE:225349.1 - Implementing Address Windowing Extensions (AWE) or VLM on 32-bit Windows Platforms
NOTE:371074.1 - ORA-27300 ORA-27301 ORA-27302 in alert log. Cannot connect to database.
BUG:7232946 - ORA-600[KSKRECONFIGNUMA2] CAUSES INSTANCE CRASH
NOTE:580552.1 - Database Crashes With ORA-04030 ORA-07445 ORA-27300 ORA-27301 ORA-27302
NOTE:557153.1 - ORA-27370 ORA-27301 (Permission denied) When Running Job Of Type EXECUTABLE
BUG:3411021 - LMD PROCESS CRASH DUE TO ORA-27300/ORA-27301/ORA-27302 AT SKGXPPOST1
NOTE:560309.1 - Database Cannot Start Due to Lack of Memory
BUG:6991131 - ORA-27300, ORA-27301 AND ORA-27302 HAPPENED DURING SHUTDOWN USING SRVCTL
NOTE:364353.1 - ORA-00603 ORA-27504 ORA-27300 ORA-27504 in the Alert Log
NOTE:1274030.1 - Startup Instance Failed with ORA-27140 ORA-27300 ORA-27301 ORA-27302 and ORA-27303 on skgpwinit6
NOTE:949468.1 - Database Startup Fails with ORA-27300: OS system dependent operation:semget failed with status: 28
NOTE:6629265.8 - Bug 6629265 - Intermittent ORA-27504 / ORA-27300 ioctl error 11
BUG:9059053 - ORA-00603 ORA-27504 ORA-27300 ORA-27301 ORA-27302 IN ALERT.LOG
NOTE:1038058.6 - LIST OF UNIX ERRORS AND EXPLANATIONS
NOTE:297498.1 - Resolving Instance Evictions on Windows Platforms
BUG:10010310 - ORA-27300: INVALID_PROCESS_ID FAILED WITH STATUS: 0; ORA-27302: SKGPALIVE1
NOTE:6441119.8 - Bug 6441119 - Instance crash due to ORA-27300 / ORA-27152

How to configure VNCSERVER

How to configure VNCSERVER
 
Configuring VNC Server Access on a Redhat Linux5.8

I will show how to configure VNC server on Red Hat server and using VNC Viewer connect the server remotely from Windows XP.

1. Introduction

VNC allows you to open a remote GUI session to your
server and thus providing you with a full graphical
interface accessible from any remote location.

VNC allows you to open a remote GUI session to your
server and thus providing you with a full graphical interface accessible from any remote location.


2. Configure steps:-

Edit /etc/sysconfig/vncservers, and add the following to the end of the file.Hear my port=4
VNC User=root
save it.


[root]# vi /etc/sysconfig/vncservers


# VNCSERVERS="2:myusername"
# VNCSERVERARGS[2]="-geometry 800x600 -nolisten tcp -nohttpd -localhost"


VNCSERVERS="4:root"
VNCSERVERARGS[4]="-geometry 1600x1200"

or

If you want multiple VNC session than you can use below lines add /etc/sysconfig/vncservers file.

VNCSERVERARGS[1]="-geometry 640x480" VNCSERVERARGS[2]="-geometry 640x480" VNCSERVERARGS[3]="-geometry 800x600"

3. Reset VNC User password.

Use below command-

[root]# vncpasswd
Password: XXXXX
Verify: XXXXX

This password you can use connection time.


4. firewall setting

If you have firewall enable on Red Hat, be sure that your firewall configuration won’t block connection from remote computer by open port TCP 5901 for remote access. Open Applications -> System Settings -> Security Level. Add ’5901:tcp’ on Other ports.
Note: VNC uses TCP protocol on port 5901.




5. start the vncserver.

[root]#
service vncserver start
[root]#
service vncserver stop

[root]#

service vncserver status


service vncserver restart

Now you can connect Red Hat server from remote computer. On my Windows XP computer, open VNC Viewer on Windows XP, type IP Address of Red Hat server with number as a username specify. In this example, I want to remote to Red Hat server as ‘root’ user which I assign as number 2 in step 2 and my Red Hat server is 10.110.141.220. So I type ’10.110.141.220:1′.
Note: You can download VNC Viewer for free at Download VNC® Viewer

Type your password for ‘root’ user which has been assign in step 3.
 

Now you have connect to Red Hat server remotely. But you’ll see that the interface looks different. You have to do a little thing more.




6. If You want to execs Server Desktop Than do next steps.

On Red Hat server, open terminal and type ‘vi /home/username/.vnc/xstartup’.

In this example,
I type ‘vi /home/admin/.vnc/xstartup’.


Note: If you going to enable remote access for user ‘root’, the file would be at ‘/root/.vnc/xstartup’.

[root@sujeet ~]# vi /root/.vnc/xstartup

UN-commit below 2 lines after that save it.

unset SESSION_MANAGER
exec /etc/X11/xinit/xinitrc

7. Restart VNCSERVER

[root@sujeet ~]# service vncserver restart

Shutting down VNC server: 4:root [ OK ]
Starting VNC server: 4:root
New 'sonu.oracle.com:4 (root)' desktop is sujeet.oracle.com:4

Starting applications specified in /root/.vnc/xstartup
Log file is /root/.vnc/chintels.chintels.com:4.log [ OK ]

8. Now you can connect Red Hat server from remote computer. On my Windows XP computer, open VNC Viewer on Windows XP, type IP Address of Red Hat server with number as a username specify. In this example, I want to remote to Red Hat server as ‘root’ user which I assign as number 2 in step 2 and my Red Hat server is 10.110.141.220. So I type ’10.110.141.220:1′.

Note: You can download VNC Viewer for free at Download VNC® Viewer


Type your password for ‘root’ user which has been assign in step 3.




Find vnc processes id.
[root]# ps -ef | grep vnc | grep root

GRANT REVOKE PRIVILEGES

GRANT REVOKE PRIVILEGES 

This Oracle tutorial explains how to grant and revoke privileges in Oracle with syntax and examples.

DESCRIPTION

You can GRANT and REVOKE privileges on various database objects in Oracle. We'll first look at how to grant and revoke privileges on tables and then how to grant and revoke privileges on functions and procedures in Oracle.

GRANT PRIVILEGES ON TABLE

You can grant users various privileges to tables. These privileges can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.
Syntax

The syntax for granting privileges on a table in Oracle is:

GRANT privileges ON object TO user;

privileges can be any of the following values:

Privilege	Description
SELECT	Ability to perform SELECT statements on the table.
INSERT	Ability to perform INSERT statements on the table.
UPDATE	Ability to perform UPDATE statements on the table.
DELETE	Ability to perform DELETE statements on the table.
REFERENCES	Ability to create a constraint that refers to the table.
ALTER	Ability to perform ALTER TABLE statements to change the table definition.
INDEX	Ability to create an index on the table with the create index statement.
ALL	All privileges on table.

Object is the name of the database object that you are granting privileges for. In the case of granting privileges on a table, this would be the table name.

user is the name of the user that will be granted these privileges.
Example

Let's look at some examples of how to grant privileges on tables in Oracle.

For example, if you wanted to grant SELECT, INSERT, UPDATE, and DELETE privileges on a table called suppliers to a user name smithj, you would run the following GRANT statement:

GRANT SELECT, INSERT, UPDATE, DELETE ON suppliers TO smithj;

You can also use the ALL keyword to indicate that you wish ALL permissions to be granted for a user named smithj. For example: 

GRANT ALL ON suppliers TO smithj;

If you wanted to grant only SELECT access on your table to all users, you could grant the privileges to the public keyword. For example:

GRANT SELECT ON suppliers TO public;

REVOKE PRIVILEGES ON TABLE

Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can run a revoke command. You can revoke any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.
Syntax

The syntax for revoking privileges on a table in Oracle is:

REVOKE privileges ON object FROM user;

privileges can be any of the following values:

Privilege	Description
SELECT	Ability to perform SELECT statements on the table.
INSERT	Ability to perform INSERT statements on the table.
UPDATE	Ability to perform UPDATE statements on the table.
DELETE	Ability to perform DELETE statements on the table.
REFERENCES	Ability to create a constraint that refers to the table.
ALTER	Ability to perform ALTER TABLE statements to change the table definition.
INDEX	Ability to create an index on the table with the create index statement.
ALL	All privileges on table.

Object is the name of the database object that you are revoking privileges for. In the case of revoking privileges on a table, this would be the table name.

user is the name of the user that will have these privileges revoked.
 
Example
Let's look at some examples of how to revoke privileges on tables in Oracle.

For example, if you wanted to revoke DELETE privileges on a table called suppliers from a user named anderson, you would run the following REVOKE statement:

REVOKE DELETE ON suppliers FROM anderson;

If you wanted to revoke ALL privileges on a table for a user named anderson, you could use the ALL keyword as follows:

REVOKE ALL ON suppliers FROM anderson;

If you had granted ALL privileges to public (all users) on the suppliers table and you wanted to revoke these privileges, you could run the following REVOKE statement:

REVOKE ALL ON suppliers FROM public;

GRANT PRIVILEGES ON FUNCTIONS/PROCEDURES

When dealing with functions and procedures, you can grant users the ability to EXECUTE these functions and procedures.
Syntax

The syntax for granting EXECUTE privileges on a function/procedure in Oracle is:

GRANT EXECUTE ON object TO user;

EXECUTE means the following:

Privilege : EXECUTE
Description : Ability to compile the function/procedure.
Ability to execute the function/procedure directly.
 

Object is the name of the database object that you are granting privileges for. In the case of granting EXECUTE privileges on a function or procedure, this would be the function name or the procedure name.

user is the name of the user that will be granted the EXECUTE privileges.
Example

Let's look at some examples of how to grant EXECUTE privileges on a function or procedure in Oracle.

For example, if you had a function called Find_Value and you wanted to grant EXECUTE access to the user named smithj, you would run the following GRANT statement:

GRANT EXECUTE ON Find_Value TO smithj;

If you wanted to grant ALL users the ability to EXECUTE this function, you would run the following GRANT statement:

GRANT EXECUTE ON Find_Value TO public;

REVOKE PRIVILEGES ON FUNCTIONS/PROCEDURES

Once you have granted EXECUTE privileges on a function or procedure, you may need to REVOKE these privileges from a user. To do this, you can execute a REVOKE command.
Syntax

The syntax for the revoking privileges on a function or procedure in Oracle is:

REVOKE EXECUTE ON object FROM user;

Privilege : EXECUTE
Description : 
EXECUTE Ability to compile the function/procedure.
Ability to execute the function/procedure directly.
 
Object is the name of the database object that you are revoking privileges for. In the case of revoking EXECUTE privileges on a function or procedure, this would be the function name or the procedure name.

user is the name of the user that will be revoked the EXECUTE privileges.
Example
Let's look at some examples of how to revoke EXECUTE privileges on a function or procedure in Oracle.

If you wanted to revoke EXECUTE privileges on a function called Find_Value from a user named anderson, you would run the following REVOKE statement:

REVOKE execute ON Find_Value FROM anderson;

If you had granted EXECUTE privileges to public (all users) on the function called Find_Value and you wanted to revoke these EXECUTE privileges, you could run the following REVOKE statement:

REVOKE EXECUTE ON Find_Value FROM public;