ORACLE_HOME and ORACLE_BASE

ORACLE_HOME specifies the directory containing the Oracle software for a given release. It corresponds to the environment in which Oracle Database products run. This environment includes the location of installed product files, the PATH variable pointing to the binary files of installed products, registry entries, net service names, and program groups.The Optimal Flexible Architecture (OFA) recommended value is: $ORACLE_BASE/product/release/db_1/ .    

For example :     /u01/app/oracle/product/10.2.0/db_1.

ORACLE_BASE specifies the directory at the top of the Oracle software and administrative file structure. The value recommended for an OFA configuration is software_mount_point/app/oracle.

For example: /u01/app/oracle.

If  we are not using an OFA-compliant system, then we do not have to set  ORACLE_BASE, but it is highly recommended that we should set it.We can find the ORACLE_HOME  from below steps : 

Oracle  9i /10g/11g
SQL> select NVL(SUBSTR(FILE_SPEC, 1, INSTR(FILE_SPEC, '\', -1, 2) -1) , SUBSTR(FILE_SPEC, 1, INSTR(FILE_SPEC, '/', -1, 2) -1))  FOLDER  FROM DBA_LIBRARIES  WHERE LIBRARY_NAME = 'DBMS_SUMADV_LIB' ;
Output : 
FOLDER
-------------------------------------
C:\app\neerajs\product\11.2.0\dbhome_1


Oracle 10g
SQL > VAR OHM VARCHAR2(100);
SQL > EXEC DBMS_SYSTEM.GET_ENV('ORACLE_HOME', :OHM) ;
SQL > PRINT OHM ;


Linux/Unix echo $ORACLE_HOME
If we  fire command  ps -ef | grep tns
then tns entry details and also shows full path of oracle home.Because if env is not set then echo$ORACLE_HOME does not work.


Windows :
1. Click Start > Run 
2. Type "regedit" and press Return/Enter
3. The registry will now be displayed
4. Expand the folder as HKEY_LOCAL_MACHINE – SOFTWARE – ORACLE


Enjoy       J J J

SYS A "Magic" USER

SYS is default users, created with the creation of the database. Although sys have much power as it is granted the DBA role but still an ordinary users. Because SYS owns the data dictionary, it is considered a bit more special . 

Anytime, someone connects as SYSDBA, it turns out it's being SYS. That is, if SYSDBA is granted to HR and hr connects as SYSDBA and select user from dual, it reveals he's actually SYS.  SYS is also special  that it is not possible to create a trigger in the sys schema. Also, a logon trigger is not executed when sys connects to the database.

Never ever create objects in the SYS schema.The objects belonging to SYS cannot be exported.We can’t do below database work with any other  user except SYS (SYSDBA).

1.) Database Full Recovery (FULL, UNTIL CANCEL, UNTIL TIME, UNTIL SCN)

2.) Change Database Character Set 

3.) Create Database

4.) Drop Database

5.) Startup and Shutdown
Note : Sysdba privileges cannot be revoked from "sys" user.

Enjoy          J J J

Identify Your Oracle Database Software Release

To understand the release nomenclature used by Oracle, examine the following example of an Oracle Database server labeled "Release 10.1.0.1.0".

Oracle Database continues to evolve and can require maintenance, Oracle periodically produces new releases. Not all customers initially subscribe to a new release or require specific maintenance for their existing release. As a result, multiple releases of the product exist simultaneously.As many as five numbers may be required to fully identify a release. The significance of these numbers is

Release Number Format

                      

Note:

Starting with release 9.2, maintenance releases of Oracle Database are denoted by a change to the second digit of a release number. In previous releases, the third digit indicated a particular maintenance release.

Major Database Release Number :  The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.

Database Maintenance Release Number :  The second digit represents a maintenance release level. Some new features may also be included.

Application Server Release Number :  The third digit reflects the release level of the Oracle Application Server (OracleAS) .

Component-Specific Release Number : The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.

Platform-Specific Release Number :  The fifth digit identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this digit will be the same across the affected platforms.

Checking The Current Release Number :  To identify the release of Oracle Database that is currently installed and to see the release levels of other database components we are using, query the data dictionary view product_component_version. A sample query follows.(We can also query the v$version view to see component-level information.) Other product release levels may increment independent of the database server.

SQL> select * from product_component_version;

PRODUCT                                                  VERSION     STATUS

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

NLSRTL                                                     10.2.0.1.0   Production

Oracle Database 10g Enterprise Edition           10.2.0.1.0   Production

PL/SQL                                                     10.2.0.1.0   Production

It is important to convey to Oracle the results of this query when we report problems with the software .

Enjoy          J J J