Active Standby Database In Oracle 11g

A Standby Database is an exact binary copy of an operational database on a remote server, ready to be used for backup, replication, disaster recovery, analysis, shadow environment and reporting, to name a few applications.

The most exiting feature of Active Standby Database is that we can open the standby database  in read only mode and at the sometime MRP process will be active, so we can redirect users to connect standby to perform select operations for reporting purpose. So that we can control much load on production database and there are plenty of option for active dataguard .

Here we will setup the standby database with active duplicate database feature available in 11g where we can create standby database without having any rman backup.In this setup,there is no need to copy  the datafiles manually. Datafiles are copeid over the network . As i have setup the standby database on same machine in my earlier POST. Now i will step the standby database on two different machine.

Lets have the details of setup :

Primary Database :
Machine   ==>  tech-199
Database  ==>  NOIDA

Standby Database :
Machine   ==>  tech-284
Database  ==>  RED(standby)

Platform used is WINDOW XP 

While configuring the standby database lets' have a look on the directory structure to avoid any confusion .On Primary database all the datafiles and redologs file in directory C:\app\neerajs\oradata\noida\'  and archive destination  is in directory "D:\archive" on machine tech-199  where as in case of the Standby database all the datafile,redologs and control files are in directory 'D:\app\standby\oradata\'   i.e, on machine tech-284 . In standby database, i have set the archive destination in  'D:\archive\'  . Let us configure standby database step-by-step.

Step 1 : Enable force logging on the Primary database :

SQL> alter database force logging ;
Database altered.

Following steps are performed on Standby database Machine (i.e; tech-284)
Step 2 :  Create Oracle Instance : 

C:\> oradim -new -sid red -intpwd xxxx -startmode m
instance created

Note :  Password should be same as that of  user "sys" of production database.
Step 3 : Update Listener.ora on standby Machine 

(SID_DESC =
      (GLOBAL_DBNAME = noida)
      (ORACLE_HOME = D:\app\Bishwanath\product\11.2.0\dbhome_1)
      (SID_NAME = red)
)
Stop and start the listener on standby 

Step 4 : Update the tnsnames.ora file on standby database : 

red =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tech-284)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = red)
    )
  )

Step 5  : Create pfile for standby database 
Add just one parameter in pfile. i.e;
db_name=Noida 
save the pfile as initred.ora in $ORACLE_HOME\database\   folder.

Step 6 : Startup standby Instance in nomount state

C:\>sqlplus sys/xxxx@red as sysdba
SQL>startup nomount

ORACLE instance started.
Total System Global Area      263639040 bytes
Fixed Size                             1373964 bytes
Variable Size                         213911796 bytes
Database Buffers                   41943040 bytes
Redo Buffers                         6410240 bytes

Step 7 : On production database ,connect with RMAN and establish connection with auxiliary i.e; to standby  database 

SQL> host rman target sys/xxxx@noida auxiliary sys/xxxx@red
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 1 16:56:17 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database:       NOIDA (DBID=1515011070)
connected to auxiliary database:   NOIDA (not mounted)

RMAN> DUPLICATE  TARGET  DATABASE
 FOR  STANDBY
 FROM  ACTIVE  DATABASE
 NOFILENAMECHECK
 DORECOVER
 SPFILE
SET  DB_UNIQUE_NAME='red'
SET  LOG_ARCHIVE_DEST_2='service=noida LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)'
 Set  STANDBY_FILE_MANAGEMENT='AUTO'
SET  FAL_SERVER='noida'
SET  FAL_CLIENT='RED'
SET  CONTROL_FILES='D:\app\standby\oradata\CONTROL01.CTL'
SET  DB_FILE_NAME_CONVERT 'C:\app\neerajs\oradata\noida\','D:\app\standby\oradata\'
SET  LOG_FILE_NAME_CONVERT 'C:\app\neerajs\oradata\noida\','D:\app\standby\oradata\' 

set  log_archive_dest_1='location=D:\archive\' 

set diagnostic_dest='D:\app\standby\diag\'

set db_recovery_file_dest='D:\app\standby\FRA\' ;

Starting Duplicate Db at 01-OCT-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=5 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  'C:\app\neerajs\product\11.2.0\dbhome_1\DATABASE\PWDnoida.ORA' auxiliary format
 'D:\app\Bishwanath\product\11.2.0\dbhome_1\DATABASE\PWDred.ORA'   targetfile
 'C:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILENOIDA.ORA' auxiliary format
 'D:\APP\BISHWANATH\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILERED.ORA'   ;
   sql clone "alter system set spfile= ''D:\APP\BISHWANATH\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILERED.ORA''";
}
executing Memory Script
Starting backup at 01-OCT-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
Finished backup at 01-OCT-11
sql statement: alter system set spfile= ''D:\APP\BISHWANATH\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILERED.ORA''
contents of Memory Script:
{
   sql clone "alter system set  db_unique_name =
 ''red'' comment=
 '''' scope=spfile";
   sql clone "alter system set  LOG_ARCHIVE_DEST_2 =
 ''service=noida LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  STANDBY_FILE_MANAGEMENT =
 ''AUTO'' comment=
 '''' scope=spfile";
   sql clone "alter system set  FAL_SERVER =
 ''noida'' comment=
 '''' scope=spfile";
   sql clone "alter system set  FAL_CLIENT =
 ''RED'' comment=
 '''' scope=spfile";
   sql clone "alter system set  CONTROL_FILES =
 ''D:\app\standby\oradata\CONTROL01.CTL'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''C:\app\neerajs\oradata\noida\'', ''D:\app\standby\oradata\'' comment=
 '''' scope=spfile";
   sql clone "alter system set  LOG_FILE_NAME_CONVERT =
 ''C:\app\neerajs\oradata\noida\'', ''D:\app\standby\oradata\'' comment=
 '''' scope=spfile";

sql clone "alter system set  log_archive_dest_1 =
 ''location=D:\archive\'' comment=
 '''' scope=spfile";
   sql clone "alter system set  diagnostic_dest =
 ''D:\app\standby\diag\'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_recovery_file_dest =
 ''D:\app\standby\FRA\'' comment=
 '''' scope=spfile";

   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_unique_name =  ''red'' comment= '''' scope=spfile
sql statement: alter system set  LOG_ARCHIVE_DEST_2 =  ''service=noida LGWR SYNC REGISTER
VALID_FOR=(online_logfile,primary_role)'' comment= '''' scope=spfile
sql statement: alter system set  STANDBY_FILE_MANAGEMENT =  ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set  FAL_SERVER =  ''noida'' comment= '''' scope=spfile
sql statement: alter system set  FAL_CLIENT =  ''RED'' comment= '''' scope=spfile
sql statement: alter system set  CONTROL_FILES =  ''D:\app\standby\oradata\CONTROL01.CTL'' comment= ''''
scope=spfile
sql statement: alter system set  db_file_name_convert =  ''C:\app\neerajs\oradata\noida\'',
''D:\app\standby\oradata\'' comment= '''' scope=spfile
sql statement: alter system set  LOG_FILE_NAME_CONVERT =  ''C:\app\neerajs\oradata\noida\'',
''D:\app\standby\oradata\'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''location=D:\archive\'' comment= '''' scope=spfile
sql statement: alter system set  diagnostic_dest =  ''D:\app\standby\diag\'' comment= '''' scope=spfile
sql statement: alter system set  db_recovery_file_dest =  ''D:\app\standby\FRA\'' comment= '''' scope=spfile

Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area     263639040 bytes
Fixed Size                     1373964 bytes
Variable Size                192940276 bytes
Database Buffers              62914560 bytes
Redo Buffers                   6410240 bytes
contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  'D:\APP\STANDBY\ORADATA\CONTROL01.CTL';
}
executing Memory Script
Starting backup at 01-OCT-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=C:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFNOIDA.ORA tag=TAG20111001T165811
RECID=11 STAMP=763405095
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 01-OCT-11
contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
   set newname for tempfile  1 to
 "D:\APP\STANDBY\ORADATA\TEMP01.DBF";
   switch clone tempfile all;
   set newname for datafile  1 to
 "D:\APP\STANDBY\ORADATA\SYSTEM01.DBF";
   set newname for datafile  2 to
 "D:\APP\STANDBY\ORADATA\SYSAUX01.DBF";
   set newname for datafile  3 to
 "D:\APP\STANDBY\ORADATA\UNDOTBS01.DBF";
   set newname for datafile  4 to
 "D:\APP\STANDBY\ORADATA\USERS01.DBF";
   set newname for datafile  5 to
 "D:\APP\STANDBY\ORADATA\EXAMPLE01.DBF";
   set newname for datafile  6 to
 "D:\APP\STANDBY\ORADATA\TEST01.DBF";
   backup as copy reuse
   datafile  1 auxiliary format
 "D:\APP\STANDBY\ORADATA\SYSTEM01.DBF"   datafile
 2 auxiliary format
 "D:\APP\STANDBY\ORADATA\SYSAUX01.DBF"   datafile
 3 auxiliary format
 "D:\APP\STANDBY\ORADATA\UNDOTBS01.DBF"   datafile
 4 auxiliary format
 "D:\APP\STANDBY\ORADATA\USERS01.DBF"   datafile
 5 auxiliary format
 "D:\APP\STANDBY\ORADATA\EXAMPLE01.DBF"   datafile
 6 auxiliary format
 "D:\APP\STANDBY\ORADATA\TEST01.DBF"   ;
   sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to D:\APP\STANDBY\ORADATA\TEMP01.DBF in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 01-OCT-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=C:\APP\NEERAJS\ORADATA\NOIDA\SYSTEM01.DBF
output file name=D:\APP\STANDBY\ORADATA\SYSTEM01.DBF tag=TAG20111001T165829
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=C:\APP\NEERAJS\ORADATA\NOIDA\SYSAUX01.DBF
output file name=D:\APP\STANDBY\ORADATA\SYSAUX01.DBF tag=TAG20111001T165829
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=C:\APP\NEERAJS\ORADATA\NOIDA\EXAMPLE01.DBF
output file name=D:\APP\STANDBY\ORADATA\EXAMPLE01.DBF tag=TAG20111001T165829
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=C:\APP\NEERAJS\ORADATA\NOIDA\UNDOTBS01.DBF
output file name=D:\APP\STANDBY\ORADATA\UNDOTBS01.DBF tag=TAG20111001T165829
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=C:\APP\NEERAJS\ORADATA\NOIDA\TEST01.DBF
output file name=D:\APP\STANDBY\ORADATA\TEST01.DBF tag=TAG20111001T165829
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=C:\APP\NEERAJS\ORADATA\NOIDA\USERS01.DBF
output file name=D:\APP\STANDBY\ORADATA\USERS01.DBF tag=TAG20111001T165829
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-OCT-11
sql statement: alter system archive log current
contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "D:\ARCHIVE\ARC0000000053_0761068614.0001" auxiliary format
 "D:\ARCHIVE\ARC0000000053_0761068614.0001"   ;
   catalog clone archivelog  "D:\ARCHIVE\ARC0000000053_0761068614.0001";
   switch clone datafile all;
}
executing Memory Script
Starting backup at 01-OCT-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=53 RECID=38 STAMP=763405284
output file name=D:\ARCHIVE\ARC0000000053_0761068614.0001 RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:07
Finished backup at 01-OCT-11
cataloged archived log
archived log file name=D:\ARCHIVE\ARC0000000053_0761068614.0001 RECID=1 STAMP=763405200
datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=763405201 file name=D:\APP\STANDBY\ORADATA\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=12 STAMP=763405201 file name=D:\APP\STANDBY\ORADATA\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=763405201 file name=D:\APP\STANDBY\ORADATA\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=763405201 file name=D:\APP\STANDBY\ORADATA\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=15 STAMP=763405201 file name=D:\APP\STANDBY\ORADATA\EXAMPLE01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=16 STAMP=763405201 file name=D:\APP\STANDBY\ORADATA\TEST01.DBF
contents of Memory Script:
{
   set until scn  2184111;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 01-OCT-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=130 device type=DISK
starting media recovery
archived log for thread 1 with sequence 53 is already on disk as file
D:\ARCHIVE\ARC0000000053_0761068614.0001
archived log file name=D:\ARCHIVE\ARC0000000053_0761068614.0001 thread=1 sequence=53
media recovery complete, elapsed time: 00:00:02
Finished recover at 01-OCT-11
Finished Duplicate Db at 01-OCT-11
RMAN> **end-of-file**

Step 8  :  On Primary database 

SQL> alter system set standby_file_management=AUTO  scope=both;
System altered.

SQL> alter system set fal_server=red scope=both;
System altered.

SQL>  alter system set fal_client=noida scope=both;
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=red LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=red' scope=both;
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=D:\archive\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=noida' ;
System altered.

Step 9 : On standby database : shutdown the Standby and enable managed recovery (active standby mode) 

C:\>sqlplus sys/xxxx@red as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 1 17:14:12 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;
OPEN_MODE
-----------------
MOUNTED

SQL> alter system set standby_file_management=AUTO scope=both;
System altered.

SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area       263639040 bytes
Fixed Size                              1373964 bytes
Variable Size                          205523188 bytes
Database Buffers                    50331648 bytes
Redo Buffers                          6410240 bytes
Database mounted.
Database opened.
SQL>  recover managed standby database using current logfile disconnect;
ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs

Standby redo logs are required to enable real time apply of redo data onto the standby.This standby redo logs are populated with redo information as fast as the primary redo logs, rather than waiting for the redo log to be archived and shipped to the standby.  This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins.Oracle recommends the below formula to calculate the number of Standby redo logs file as 

(maximum number of logfiles for each thread + 1) * maximum number of threads

Since , I have three redo logs file so i will create four standby redo logs file. Oracle recommends that we should create standby redo logs on both i.e,primary and standby database so that we can safely switchover in future . Here, i am creating standby redo logs on standby database only .

SQL> alter database add standby logfile group 4 'D:\APP\STANDBY\ORADATA\REDO04.LOG' size 50m;
Database altered.
SQL> alter database add standby logfile group 5 'D:\APP\STANDBY\ORADATA\REDO05.LOG' size 50m;
Database altered.
SQL> alter database add standby logfile group 6 'D:\APP\STANDBY\ORADATA\REDO06.LOG' size 50m;
Database altered.
SQL>recover managed standby database using current logfile disconnect ;
Media recovery complete .

( On standby database )

SQL> select open_mode from V$database ; 
OPEN_MODE
--------------------------
READ ONLY WITH APPLY

(The above output "read only with apply"  shows the active mode is activated )

Following is the Command Used for Active Duplication

FROM ACTIVE DATABASE : (This is supplied if we want to do active database duplication) Specifies that the files for the standby database should be provided directly from the source database and not from a backup of the source database

NOFILENAMECHECK:  Prevents RMAN from checking whether datafiles of the source database share the same names as the standby database files that are in use.

The NOFILENAMECHECK option is required when the standby and primary datafiles and online redo logs have identical filenames. Thus, if we want the duplicate database filenames to be the same as the source database filenames, and if the databases are in different hosts, then we must specify NOFILENAMECHECK

SPFILE: Copies the server parameter file from the source database to the operating system-specific default location for this file on the standby database.

RMAN uses the server parameter file to start the auxiliary instance for standby database creation. Any remaining options of the DUPLICATE command are processed after the database instance is started with the server parameter file.

If we execute DUPLICATE with the SPFILE clause, then the auxiliary instance must already be started with a text-based initialization parameter file. In this case, the only required parameter in the temporary initialization parameter file is DB_NAME, which can be set to any arbitrary value. RMAN copies the binary server parameter file, modifies the parameters based on the settings in the SPFILE clause, and then restarts the standby instance with the server parameter file. When we specify SPFILE, RMAN never uses the temporary text-based initialization parameter file to start the instance.

DORECOVER: Specifies that RMAN should recover the standby database after creating it. If we  specify an until Clause, then RMAN recovers to the specified SCN or time and leaves the database mounted.

RMAN leaves the standby database mounted after media recovery is complete, but does not place the standby database in manual or managed recovery mode. After RMAN creates the standby database, we must resolve any gap sequence before placing it in manual or managed recovery mode, or opening it in read-onlymode.For more detail about parameters  click here 

Enjoy    :-) 

External OS User Authentication in Oracle

Oracle users can be authenticated in different ways,We generally login into our database with two ways . i.e, either as

C:\> sqlplus / as sysdba        or
C:\> sqlplus user/password@SID as sysdba.

This is only valid when we are the member of  "ORA_DBA"  OS  group(window) . If we are not the member of the "ORA_DBA" group,then we cannot login into database . Let have a look :  Here i have created a osuser  "oraext" and login with this user and try to connect with database.

Step 1 : Create OSuser 

C:\>net user oraext orapass /add
The command completed successfully.

Check the domain
C:\> echo %userdomain%
TECH-199

Step 2:  Login with "oraext"  user in  window machine and try to connect the database as:

c:\> sqlplus sys/sys@noida as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 30 14:46:39 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR: ORA-01017:  invalid username/password; logon denied
Enter user-name:

Here,we are getting invalid username error,this is because the os user "oraext" is not the member of  the ORA_DBA group. Therefore to handle such case Oracle provide OS authentication method to connect database without authenticating any database username and password. Let's check 

In order to create the external user authentication we perform the following as :

1.) Check the values of the parameter "os_authentication_prefix"

SQL> SHOW PARAMETER os_authent_prefix
NAME                                 TYPE                       VALUE
---------------------             -----------                 -------------
os_authent_prefix            string                         OPS$

As we can see, the default value is "ops$". If this is not appropriate it can be changed using the alter system command.

2.) Create a database user with same name as the OS user which is prefixed by os_authent_prefix values followed by domain name. On Windows platforms we would expect an Oracle username of   "OPS$DOMAIN\xxxx"  for the Windows user "xxxx".

Now we know the OS authentication prefix, we can create a database user to allow an OS authenticated  connection. To do this, we create an Oracle user in the normal way, but the username must be the prefix value concatenated to the domain-name and OS username . Therefore the username seems like "ops$tech-199\oraext"

SQL> create user "ops$tech-199\oraext" identified externally;

3.) Grant connect privileges to them .

SQL> grant connect to "ops$tech-199\oraext";

Now Login as user "oraext" in window  and open the cmd and connect as  :

C:\>sqlplus / 
SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 30 17:27:31 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Note: The parameter in sqlnet.ora file be SQLNET.AUTHENTICATION_SERVICES= (NTS)

Advantage of the OS authentication:

1.) Without OS Authentication applications must store passwords in a variety of applications each with their own security model and vulnerabilities.

2.) Domain authentication already has to be secure because if it is not then database security just slows down access to the database, but cannot prevent it.

3.) Users that only have to remember one domain password can be made to create more secure domain passwords more easily than they can be made to create even less secure database passwords as the number of different databases they must connect to increases.

Enjoy  :-) 

Oracle Session Information From SYS_CONTEXT

SYS_CONTEXT is a standard Oracle Database function used to retrieve session-level information. SYS_CONTEXT  allow us to retrieve a set of session parameters via the namespace parameter ‘USERENV’.Basically, these are global variables that Oracle stores on the current session/user. Here is a sample of some session and user-specific information that can be obtained in one function call:

SQL> select sys_context('USERENV','AUTHENTICATION_TYPE') 
          ,sys_context('USERENV','CURRENT_SCHEMA') 
          ,sys_context('USERENV','CURRENT_SCHEMAID') 
          ,sys_context('USERENV','CURRENT_USER') 
          ,sys_context('USERENV','CURRENT_USERID') 
          ,sys_context('USERENV','DB_DOMAIN') 
          ,sys_context('USERENV','DB_NAME') 
          ,sys_context('USERENV','INSTANCE') 
         ,sys_context('USERENV','IP_ADDRESS') 
         ,sys_context('USERENV','ISDBA') 
         ,sys_context('USERENV','LANG') 
         ,sys_context('USERENV','LANGUAGE') 
         ,sys_context('USERENV','NETWORK_PROTOCOL') 
         ,sys_context('USERENV','NLS_CALENDAR') 
         ,sys_context('USERENV','NLS_CURRENCY') 
         ,sys_context('USERENV','NLS_DATE_FORMAT') 
        ,sys_context('USERENV','NLS_DATE_LANGUAGE') 
        ,sys_context('USERENV','NLS_TERRITORY') 
        ,sys_context('USERENV','OS_USER') 
        ,sys_context('USERENV','SESSION_USER') 
        ,sys_context('USERENV','SESSION_USERID') 
       ,sys_context('USERENV','SESSIONID') 
       ,sys_context('USERENV','TERMINAL') from dual ;

The output of the above sample is :

Enjoy      :-) 

Identify IP Addresses and Host Names

We can identify the IP addresses of all the client connect to server. Below the command to identified  the IP address ,machine name,sid  .

SQL>   select   sid,   machine,     UTL_INADDR.get_host_address (substr(machine,instr(machine,'\')+1))  ip  from   v$session   where   type='USER'   and    username is not null   order  by   sid;

Oracle provides the method to identifying and relating to the IP addresses and host names  for Oracle clients and servers. The few methods  are  : 

1.) UTL_INADDR
2.) SYS_CONTEXT
3.) V$INSTANCE
4.) V$SESSION

1.) UTL_INADDR  :  The UTL_INADDR package provides a PL/SQL procedures to support internet addressing. It provides an API to retrieve host names and IP addresses of local and remote hosts.The utl_inaddr like the two function  : 

1.) get_host_address and 
2.) get_host_name.

Now we check the description of UTL_INADDR 

SQL> desc UTL_INADDR

FUNCTION  GET_HOST_ADDRESS  RETURNS  VARCHAR2
 Argument Name                Type                In/Out         Default?
 -----------------          ------------           --------      -------------
 HOST                         VARCHAR2            IN           DEFAULT
FUNCTION GET_HOST_NAME RETURNS VARCHAR2
 Argument Name               Type                    In/Out             Default?
 ----------------           ---------------        --------          ------------
 IP                                 VARCHAR2            IN                DEFAULT

I.) Get_host_address: The get_host_address function like the argument name "HOST" , data types "varchar2" and by default NULL. The get_host_address get local IP address and remote host given its IP address.

II.) Get_host_name: The get_host_name function like the argument name "IP" , data types "varchar2" and by default NULL. The get_host_name get local host name and remote host given its name.

Let see some example : 

The GET_HOST_ADDRESS function returns the IP address of the specified host name

SQL>select  UTL_INADDR.get_host_address('TECH-284') from  dual;

UTL_INADDR.GET_HOST_ADDRESS('TECH-284')
-----------------------------------------------------------
192.100.0.85

The GET_HOST_NAME function returns the host name of the specified IP address.

SQL> SELECT UTL_INADDR.get_host_name('192.168.52.127') FROM dual;

UTL_INADDR.GET_HOST_NAME('192.168.52.127')
----------------------------------------------------------
tech-199

The host name of the database server is returned if the specified IP address is NULL or omitted.

2.) SYS_CONTEXT : Oracle has a very useful built-in function called SYS_CONTEXT. The SYS_CONTEXT function is able to return the following host and IP address information for the current session.SYS_CONTENT has the following options as 

TERMINAL - An operating system identifier for the current session. This is often the client machine name.
HOST - The host name of the client machine.
IP_ADDRESS - The IP address of the client machine.
SERVER_HOST - The host name of the server running the database instance.

What makes this function more interesting is the fact that Oracle provides a built-in namespace called USERENV with predefined parameters, which describes the current session.

The syntax of this function goes like this:
SYS_CONTEXT ( 'namespace' , 'parameter' [, length] )

Let see some example :

SQL> SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual;
SYS_CONTEXT('USERENV','TERMINAL')
---------------------------------------------------
TECH-199

SQL> SELECT SYS_CONTEXT('USERENV','HOST') FROM dual;
SYS_CONTEXT('USERENV','HOST')
------------------------------------------
TECH\TECH-199

SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;
SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------
192.100.0.112

SQL> SELECT SYS_CONTEXT('USERENV','SERVER_HOST') FROM dual;
SYS_CONTEXT('USERENV','SERVER_HOST')
-----------------------------------------------------
tech-199

For more about sys_context check the below link .
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/functions150.htm
http://www.adp-gmbh.ch/ora/sql/sys_context.html

3.) V$INSTANCE :  The host_name column of the V$INSTANCE view contains the host name of the server running the instance.

SQL> SELECT host_name FROM v$instance;
HOST_NAME
-----------------
TECH-199

4.) V$SESSION  :  V$SESSION displays session host information for each current session. The column such as terminal and machine give the following details.
TERMINAL: The operating system terminal name for the client.This is often set to the client machine name.
MACHINE :The operating system name for the client machine.This may include the domain name if present.

SQL> SELECT terminal, machine FROM v$session WHERE username = 'HR';
TERMINAL           MACHINE
-------------       --------------------
TECH-199          TECH\TECH-199


Enjoy    :-)

Grant privileges on all tables in particular schema

In oracle, we cannot grant the privileges on schemas level .If we have to grant the privileges on all the tables of a particular schemas, then it is very tedious to grant privileges on all the tables one-by-one to a particular user. This task can be  performed by using a simple pl/sql procedure. Here is a Demo for this : 

Suppose we have to grant "select" privileges on all the tables to user  then we need to do something like this .

SQL>FOR x IN (SELECT * FROM user_tables)
          LOOP
          EXECUTE  IMMEDIATE  'GRANT  SELECT  ON  ' || your.table_names || '  TO <<user>>' ;
           END LOOP ;




Enjoy    :-)