Block developers from using TOAD and other tools on production databases

how to prevent users from using additional tools to connect to production database. This is the AFTER LOGON trigger create ON DATABASE as follows:

CONNECT / AS SYSDBA;
 
CREATE OR REPLACE TRIGGER block_tools_from_prod
  AFTER LOGON ON DATABASE
DECLARE
  v_prog sys.v_$session.program%TYPE;
BEGIN
  SELECT program INTO v_prog
    FROM sys.v_$session
  WHERE  audsid = USERENV('SESSIONID')
    AND  audsid != 0  -- Don't Check SYS Connections
    AND  ROWNUM = 1;  -- Parallel processes will have the same AUDSID's
 
  IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
     UPPER(v_prog) LIKE '%SQLNAV%' OR     -- SQL Navigator
     UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
     UPPER(v_prog) LIKE '%BUSOBJ%' OR   -- Business Objects
     UPPER(v_prog) LIKE '%EXCEL%'       -- MS-Excel plug-in
  THEN
     RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
  END IF;
END;
/
SHOW ERRORS

Ref:

http://www.psoug.org/snippet/Block_TOAD_and_other_tools_516.htm

ora-01536 with unlimited quota

Problem: ora-01536 with unlimited quota

Solution:

ALTER USER  QUOTA UNLIMITED ON ;

select * from dba_ts_quotas where username='ATMUTL';

Oracle Database user privilege information

Database user privilege information

Solution: Following queries will help fantastic 

select * from dba_users order by account_status desc

select * from DBA_SYS_PRIVS order by grantee

select * from DBA_TAB_PRIVS order by grantee

Step by Step Enable Flash Recovery Area In 11g

Enable Flash Recovery Area In 11g

Here are steps to enable the flash recovery area in 11g and then check the existing backup policy is working fine or not.

My goals are:

1) Eanble the flash recovery area
2) Enable the flashback logs for database
3) Only archive logs should go in flash recovery area
Till, now archives are getting generated on normal mountpoint on server. But, to implement the automatic deletion of archives
in physical standby environment, it required to setup FRA at PROD as well as standby site. I will come to standby part in next
post. For this post, i will only be doing FRA implement on PROD.
Currently, archives are generated on normal disk location.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u05/oradata/CORE/
Oldest online log sequence 2088
Next log sequence to archive 2090
Current log sequence 2090


SQL> alter system set db_recovery_file_dest_size=10g scope=both;
System altered.
SQL> alter system set db_recovery_file_dest=’/u05/oradata/CORE’ scope=both;
System altered.
Enabling archivelog mode
SQL> alter database archivelog;
Now, i have enabled the FRA by setting above two parameters. Both parameters are dynamic and doesn’t require a database restart.
Enable database flashback logs as:

SQL> alter database flashback on;
Database altered.

Now, change the location of archive logs, so that archives now generated in FRA.

SQL> alter system set log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CORE’ scope=both;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2089
Next log sequence to archive 2091
Current log sequence 2091
SQL>
SQL>

Now, if i go to FRA location, i will see the directory structure as:

/u05/oradata/CORE
oracore@cph-core-db01-s $ ls -lrt
total 40
drwxr-xr-x 2 oracore oinstall 256 Mar 29 2012 lost+found
drwxr-x--- 4 oracore oinstall 256 Mar 5 11:35 CORE

A new folder will be created with database name (CORE is DB name in my case). If i go to core, i will see following:

oracore@cph-core-db01-s $ cd CORE
oracore@cph-core-db01-s $ ls -lrt
total 0
drwxr-x--- 2 oracore oinstall 256 Mar 5 11:32 flashback
drwxr-x--- 3 oracore oinstall 256 Mar 5 11:35 archivelog


flashback : This will contain the file (.flb) containing the flashback logs for database.
archivelog: This will conatin the archivelogs generated in OMF format and a new folder will be creates automatically
for daily archives.
Apart from these folders, few more folders will be created depending upon you put backups in FRA or not.
backupset: This will contain the RMAN backup files.
datafile : This will contain the image copies backups.
autobackup: This will contain the controlfile autobackup.
controlfile: This will contain the controlfile, if you put one of the controlfiles in FRA
onlinelogs : This will contain online redo log member, if one member is put in FRA.
Now, when i switch logfile, archive got generated in FRA and parameter log_archive_format has no effect on file name
generated in FRA.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2089
Next log sequence to archive 2091
Current log sequence 2091
SQL>

how to check database character set in oracle

Note that database character set refers to the term character set encoding and in oracle database the terms character set and character set encoding are often used interchangeably.
The database character set in oracle determines the set of characters can be stored in the database. It is also used to determine the character set to be used for object identifiers and PL/SQL variables and for storing PL/SQL program source.

The database character set information is stored in the data dictionary tables named SYS.PROPS$.
You can get the character set used in the database by SYS.PROPS$ table or any other views (like database_properties/ nls_database_parameters) exist in the database. The parameter NLS_CHARACTERSET value contains the database character set name. Get it from,

SQL> select value$ from sys.props$ where name='NLS_CHARACTERSET';

VALUE$
--------------------------------------------------------------------------------
WE8MSWIN1252

SQL> select property_value from database_properties where property_name=
'NLS_CHARACTERSET';

PROPERTY_VALUE
--------------------------------------------------------------------------------
WE8MSWIN1252

SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

VALUE
----------------------------------------
WE8MSWIN1252

Find Oracle Database Growth Rate

The following scripts is very much useful and tested in my lab

Step:1

create or replace view day_wise_tblspc as
SELECT TO_CHAR (sp.begin_interval_time,’DD-MM-YYYY’) days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN (‘SYSAUX’,'SYSTEM’,'EXAMPLE’)
GROUP BY TO_CHAR (sp.begin_interval_time,’DD-MM-YYYY’), ts.tsname
ORDER BY ts.tsname, days;

Step:2

select  sum(cur_size_mb),days
from day_wise_tblspc
group by days
order by days desc

How to switch the database to a new UNDO tablespace and drop the old one

$ sqlplus / as sysdba

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> 

CREATE UNDO TABLESPACE undotbs2
DATAFILE '/d01/apps/oradata/oraxpo/undotbs201.dbf'
         SIZE 50M AUTOEXTEND ON NEXT 5M;

Tablespace created.

-- We created a new UNDO tablespace named UNDOTBS2

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

System altered.

-- Switch the database to the new UNDO tablespace.

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

-- Try to drop the tablespace but failed. 

SQL>

With the alter system set undo_tablespace=UNDOTBS2, the database UNDO tablespace is changed and any new transaction’s undo data will go to the new tablespace i.e. UNDOTBS2. But the undo data for already pending transaction (e.g. the one initiated by SCOTT before the database UNDO tablespace switch) is still in the old tablespace with a status of PENDING OFFLINE. As far as it is there you cannot drop the old tablespace.

set lines 10000
column name format a10

SELECT a.name,b.status
FROM   v$rollname a,v$rollstat b
WHERE  a.usn = b.usn
AND    a.name IN (
    SELECT segment_name
    FROM dba_segments
    WHERE tablespace_name = 'UNDOTBS1'
   );

NAME       STATUS
---------- ---------------
_SYSSMU8$  PENDING OFFLINE

The above query shows the name of the UNDO segment in the UNDOTBS1 tablespace and its status. Now lets see which users/sessions are running this pending transaction.

column username format a6

SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN (
    SELECT segment_name
    FROM dba_segments
    WHERE tablespace_name = 'UNDOTBS1'
   );

NAME       STATUS          USERNA        SID    SERIAL#
---------- --------------- ------ ---------- ----------
_SYSSMU8$  PENDING OFFLINE SCOTT         147          4

So this is SCOTT with SID=147 and SERIAL#=4. Since we know now the user, we can go to him/her and request to end the transaction gracefully i.e. issue a ROLLBACK or COMMIT. However, if this is not possible (say the user initiated the transaction and left for annual leave and trust me this happens) you may go ahead and kill the session to release the undo segments in the UNDOTBS1 tablespace.

SQL> alter system kill session '147,4';

System altered.

SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN (
    SELECT segment_name
    FROM dba_segments
    WHERE tablespace_name = 'UNDOTBS1'
   );

no rows selected

As we can see once the session is kills we don’t see anymore segments occupied in the UNDOTBS1
tablespace. Lets drop UNDOTBS1.

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

If you are retaining undo data then you still won’t be able to drop the tablespace because it is still in use by undo_retention. Let the UNDO_RETENTION time pass and then try to drop the tablespace. In my case it is 900 seconds i.e. 15 minutes.

-- After 15 minutes.
SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Moving Datafile in NOARCHIVELOG mode

Moving Datafile ( NOARCHIVELOG mode )

SQL> select name,bytes,status from v$datafile;
NAME BYTES STATUS
———- ——-
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\SYSTEM01.DBF 513802240 SYSTEM
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\UNDOTBS01.DBF 41943040 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\SYSAUX01.DBF 419430400 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\USERS01.DBF 5242880 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\EXAMPLE01.DBF 104857600 ONLINE

SQL> Alter tablespace users offline;

Tablespace altered.

SQL> select name,bytes,status from v$datafile;

NAME BYTES STATUS
———- ——-
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\SYSTEM01.DBF 513802240 SYSTEM
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\UNDOTBS01.DBF 41943040 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\SYSAUX01.DBF 419430400 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\NEW\USERS01.DBF 0 OFFLINE 
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\EXAMPLE01.DBF 104857600 ONLINE

Now copy the datafile to the new location using OS command or manually and follow the below steps:

SQL>ALTER TABLESPACE USERS RENAME DATAFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\USERS01.DBF’ TO ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\NEW\USERS01.DBF’;
Tablespace altered.
SQL>
SQL> Alter tablespace users online;
Tablespace altered.
SQL> select name,bytes,status from v$datafile;
NAME BYTES STATUS
———- ——-
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\SYSTEM01.DBF 513802240 SYSTEM
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\UNDOTBS01.DBF 41943040 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\SYSAUX01.DBF 419430400 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\NEW\USERS01.DBF 5242880 ONLINE 
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\EXAMPLE01.DBF 104857600 ONLINE
SQL> spool off;

Oracle DB Lock Related Queries

—-Active Table Locks—

SELECT SUBSTR(a.object,1,25) TABLENAME,
SUBSTR(s.username,1,15) USERNAME,
SUBSTR(p.pid,1,5) PID,
SUBSTR(p.spid,1,10) SYSTEM_ID,
DECODE(l.type,
‘RT’,'Redo Log Buffer’,
‘TD’,'Dictionary’,
‘TM’,'DML’,
‘TS’,'Temp Segments’,
‘TX’,'Transaction’,
‘UL’,'User’,
‘RW’,'Row Wait’,
l.type) LOCK_TYPE
FROM gv$access a, gv$process p, gv$session s, gv$lock l
WHERE s.sid = a.sid
AND s.paddr = p.addr
AND l.sid = p.pid
GROUP BY a.object, s.username, p.pid, l.type, p.spid
ORDER BY a.object, s.username;

——Active Locks———–

SELECT s.username, s.sid, s.serial#, s.osuser, k.ctime, o.object_name
object, k.kaddr, DECODE(l.locked_mode,
1, ‘No Lock’,
2, ‘Row Share’,
3, ‘Row Exclusive’,
4, ‘Shared Table’,
5, ‘Shared Row Exclusive’,
6, ‘Exclusive’) locked_mode,
DECODE(k.type,
‘BL’,'Buffer Cache Management (PCM lock)’,
‘CF’,'Controlfile Transaction’,
‘CI’,'Cross Instance Call’,
‘CU’,'Bind Enqueue’,
‘DF’,'Data File’,
‘DL’,'Direct Loader’,
‘DM’,'Database Mount’,
‘DR’,'Distributed Recovery’,
‘DX’,'Distributed Transaction’,
‘FS’,'File Set’,
‘IN’,'Instance Number’,
‘IR’,'Instance Recovery’,
‘IS’,'Instance State’,
‘IV’,'Library Cache Invalidation’,
‘JQ’,'Job Queue’,
‘KK’,'Redo Log Kick’,
‘LA’,'Library Cache Lock’,
‘LB’,'Library Cache Lock’,
‘LC’,'Library Cache Lock’,
‘LD’,'Library Cache Lock’,
‘LE’,'Library Cache Lock’,
‘LF’,'Library Cache Lock’,
‘LG’,'Library Cache Lock’,
‘LH’,'Library Cache Lock’,
‘LI’,'Library Cache Lock’,
‘LJ’,'Library Cache Lock’,
‘LK’,'Library Cache Lock’,
‘LL’,'Library Cache Lock’,
‘LM’,'Library Cache Lock’,
‘LN’,'Library Cache Lock’,
‘LO’,'Library Cache Lock’,
‘LP’,'Library Cache Lock’,
‘MM’,'Mount Definition’,
‘MR’,'Media Recovery’,
‘NA’,'Library Cache Pin’,
‘NB’,'Library Cache Pin’,
‘NC’,'Library Cache Pin’,
‘ND’,'Library Cache Pin’,
‘NE’,'Library Cache Pin’,
‘NF’,'Library Cache Pin’,
‘NG’,'Library Cache Pin’,
‘NH’,'Library Cache Pin’,
‘NI’,'Library Cache Pin’,
‘NJ’,'Library Cache Pin’,
‘NK’,'Library Cache Pin’,
‘NL’,'Library Cache Pin’,
‘NM’,'Library Cache Pin’,
‘NN’,'Library Cache Pin’,
‘NO’,'Library Cache Pin’,
‘NP’,'Library Cache Pin’,
‘NQ’,'Library Cache Pin’,
‘NR’,'Library Cache Pin’,
‘NS’,'Library Cache Pin’,
‘NT’,'Library Cache Pin’,
‘NU’,'Library Cache Pin’,
‘NV’,'Library Cache Pin’,
‘NW’,'Library Cache Pin’,
‘NX’,'Library Cache Pin’,
‘NY’,'Library Cache Pin’,
‘NZ’,'Library Cache Pin’,
‘PF’,'Password File’,
‘PI’,'Parallel Slaves’,
‘PR’,'Process Startup’,
‘PS’,'Parallel Slave Synchronization’,
‘QA’,'Row Cache Lock’,
‘QB’,'Row Cache Lock’,
‘QC’,'Row Cache Lock’,
‘QD’,'Row Cache Lock’,
‘QE’,'Row Cache Lock’,
‘QF’,'Row Cache Lock’,
‘QG’,'Row Cache Lock’,
‘QH’,'Row Cache Lock’,
‘QI’,'Row Cache Lock’,
‘QJ’,'Row Cache Lock’,
‘QK’,'Row Cache Lock’,
‘QL’,'Row Cache Lock’,
‘QM’,'Row Cache Lock’,
‘QN’,'Row Cache Lock’,
‘QO’,'Row Cache Lock’,
‘QP’,'Row Cache Lock’,
‘QQ’,'Row Cache Lock’,
‘QR’,'Row Cache Lock’,
‘QS’,'Row Cache Lock’,
‘QT’,'Row Cache Lock’,
‘QU’,'Row Cache Lock’,
‘QV’,'Row Cache Lock’,
‘QW’,'Row Cache Lock’,
‘QX’,'Row Cache Lock’,
‘QY’,'Row Cache Lock’,
‘QZ’,'Row Cache Lock’,
‘RT’,'Redo Thread’,
‘SC’,'System Commit number’,
‘SM’,'SMON synchronization’,
‘SN’,'Sequence Number’,
‘SQ’,'Sequence Enqueue’,
‘SR’,'Synchronous Replication’,
‘SS’,'Sort Segment’,
‘ST’,'Space Management Transaction’,
‘SV’,'Sequence Number Value’,
‘TA’,'Transaction Recovery’,
‘TM’,'DML Enqueue’,
‘TS’,'Table Space (or Temporary Segment)’,
‘TT’,'Temporary Table’,
‘TX’,'Transaction’,
‘UL’,'User-defined Locks’,
‘UN’,'User Name’,
‘US’,'Undo segment Serialization’,
‘WL’,'Writing redo Log’,
‘XA’,'Instance Attribute Lock’,
‘XI’,'Instance Registration Lock’) type
FROM gv$session s, sys.gv$lock c, sys.gv$locked_object l,
dba_objects o, sys.gv$lock k, gv$lock v
WHERE o.object_id = l.object_id
AND l.session_id = s.sid
AND k.sid = s.sid
–AND s.saddr = c.saddr
AND k.kaddr = c.kaddr
AND k.kaddr = v.kaddr
–AND v.saddr = s.saddr
AND k.lmode = l.locked_mode
AND k.lmode = c.lmode
AND k.request = c.request
ORDER BY object;

Database Lock Related Queries

The following queries are very useful

—–List Locks—–

SELECT session_id,lock_type, mode_held, mode_requested, blocking_others, lock_id1
FROM dba_lock l
WHERE lock_type NOT IN (‘Media Recovery’, ‘Redo Thread’);
—-Locked Objects——
SELECT oracle_username USERNAME, owner OBJECT_OWNER,
object_name, object_type, s.osuser,
DECODE(l.block,
0, ‘Not Blocking’,
1, ‘Blocking’,
2, ‘Global’) STATUS,
DECODE(v.locked_mode,
0, ‘None’,
1, ‘Null’,
2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share’,
5, ‘S/Row-X (SSX)’,
6, ‘Exclusive’, TO_CHAR(lmode)
) MODE_HELD
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
and v.session_id = s.sid
ORDER BY oracle_username, session_id;

—-Objects that have been lock for 2 minutes or more —–
SELECT SUBSTR(TO_CHAR(w.session_id),1,5) WSID, p1.spid WPID,
SUBSTR(s1.username,1,12) “WAITING User”,
SUBSTR(s1.osuser,1,8) “OS User”,
SUBSTR(s1.program,1,20) “WAITING Program”,
s1.client_info “WAITING Client”,
SUBSTR(TO_CHAR(h.session_id),1,5) HSID, p2.spid HPID,
SUBSTR(s2.username,1,12) “HOLDING User”,
SUBSTR(s2.osuser,1,8) “OS User”,
SUBSTR(s2.program,1,20) “HOLDING Program”,
s2.client_info “HOLDING Client”,
o.object_name “HOLDING Object”
FROM gv$process p1, gv$process p2, gv$session s1,
gv$session s2, dba_locks w, dba_locks h, dba_objects o
WHERE w.last_convert > 120
AND h.mode_held != ‘None’
AND h.mode_held != ‘Null’
AND w.mode_requested != ‘None’
AND s1.row_wait_obj# = o.object_id
AND w.lock_type(+) = h.lock_type
AND w.lock_id1(+) = h.lock_id1
AND w.lock_id2 (+) = h.lock_id2
AND w.session_id = s1.sid (+)
AND h.session_id = s2.sid (+)
AND s1.paddr = p1.addr (+)
AND s2.paddr = p2.addr (+)
ORDER BY w.last_convert desc;

Setting listener password in Oracle

Setting listener password in Oracle

There are cases where “oracle” user credential are known to many. At this times we may want the authentication process to work as it was in 8i, 9i. Like ask for credential for any user even for “oracle”. In this case you need to set a parameter in listener.ora i.e. LOCAL_OS_AUTHENTICATION_=OFF. This will disable OS authentication for listener.

# bash-3.00$ cat listener.ora
# listener.ora Network Configuration File: /oracle/ora10203/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER_DBA =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =*.*.*.*)(PORT = 1523))
)
)
)

SID_LIST_LISTENER_DBA =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DMS)
(ORACLE_HOME = /oracle/ora10g)
(SID_NAME = DMS)
)
)

Setting password for listener

bash-3.00$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production on 20-JUN-2011 19:54:01

Copyright (c) 1991, 2006, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set current_listener listener_dba
Current Listener is listener_dba

LSNRCTL> show current_listener
Current Listener is listener_dba

LSNRCTL> set password
Password:
The command completed successfully

LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=*.*.*.*)(PORT=1523)))
No changes to save for listener_dba.
The command completed successfully

LSNRCTL> change_password
Old password:
New password:
Reenter new password:
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=*.*.*.*)(PORT=1523)))
Password changed for listener_dba
The command completed successfully

LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=*.*.*.*)(PORT=1523)))
Saved listener_dba configuration parameters.
Listener Parameter File   /oracle/ora10g/network/admin/listener.ora
Old Parameter File   /oracle/ora10g/network/admin/listener.bak
The command completed successfully

Lets check the authentication.

LSNRCTL> stop listener_dba
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=*.*.*.*)(PORT=1523)))
The command completed successfully

LSNRCTL> start listener_dba
Starting /oracle/ora10g/bin/tnslsnr: please wait...
TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production
System parameter file is /oracle/ora10g/network/admin/listener.ora
Log messages written to /oracle/ora10g/network/log/listener_dba.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=*.*.*.*)(PORT=1523)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=*.*.*.*)(PORT=1523)))

STATUS of the LISTENER

------------------------
Alias                     listener_dba
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production
Start Date                20-JUN-2011 19:55:10
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Password or Local OS Authentication
SNMP                      ON
Listener Parameter File   /oracle/ora10g/network/admin/listener.ora
Listener Log File         /oracle/ora10g/network/log/listener_dba.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=*.*.*.*)(PORT=1523)))
Services Summary...
Service "DMS" has 1 instance(s).
Instance "DMS", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

I can see that “oracle” as OS user set the listener password and was the one to stop and start the listener. But it didn’t asked for password. Well lets set the parameter and then check.

bash-3.00$ vi listener.ora
"listener.ora" 48 lines, 1011 characters
# listener.ora Network Configuration File: /oracle/ora10203/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER_DBA =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =*.*.*.*)(PORT = 1523))
)
)
)

SID_LIST_LISTENER_DBA =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DMS)
(ORACLE_HOME = /oracle/ora10g)
(SID_NAME = DMS)
)
)

#----ADDED BY TNSLSNR 20-JUN-2011 19:42:57---
PASSWORDS_listener_dba = FC996BE8FB638140
LOCAL_OS_AUTHENTICATION_LISTENER_DBA=OFF
SAVE_CONFIG_ON_STOP_LISTENER = ON
#--------------------------------------------

Here listener_dba is the current listener.

bash-3.00$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production on 20-JUN-2011 19:57:22

Copyright (c) 1991, 2006, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> stat listener_dba
Starting /oracle/ora10g/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production
System parameter file is /oracle/ora10g/network/admin/listener.ora
Log messages written to /oracle/ora10g/network/log/listener_dba.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=*.*.*.*)(PORT=1523)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=*.*.*.*)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     listener_dba
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production
Start Date                20-JUN-2011 19:57:48
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Password
SNMP                      ON
Listener Parameter File   /oracle/ora10g/network/admin/listener.ora
Listener Log File         /oracle/ora10g/network/log/listener_dba.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=*.*.*.*)(PORT=1523)))
Services Summary...
Service "DMS" has 1 instance(s).
Instance "DMS", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

LSNRCTL> stop listener_dba
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=*.*.*.*)(PORT=1523)))
TNS-01169: The listener has not recognized the password
LSNRCTL>

ref: Setting Listener Passwords With an Oracle 10g or Newer Listener [ID 260986.1]