Oracle EBS Housekeeping Jobs

For a long time, I searched for a comprehensive list of housekeeping jobs [Standard EBS jobs provided by Oracle] that should be scheduled periodically on the EBS instance. Since I could not find a single source, came up with my own after a lot of reading... There may be more, but these should be scheduled on the EBS to keep the instance performing healthy... The arguments and run interval may need to be modified to suit the workload and business requirement... but this will get you started... Few jobs may need multiple scheduling with different parameters...

Job       : Gather Schema Statistics 
Executable: FNDGSCST 
Frequency : Daily 
Parameters: ALL, 10, , NOBACKUP, , LASTRUN, GATHER AUTO, , N 
Frequency : Weekly 
Parameters: ALL, 10, 4, NOBACKUP, , LASTRUN, GATHER, , Y  


Job       : JTF Item InterMedia Index Optimizing operation
Executable: JTFOPTI 
Frequency : Weekly 
Parameters: FULL, 2 


Job       : JTF Item InterMedia Index Sync Operation
Executable: JTFSYNC 
Frequency : Weekly 
Parameters: (None) 


Job       : MES InterMedia Index Sync Operation
Executable: AMVSYNC 
Frequency : Weekly 
Parameters: (None) 


Job       : OCM InterMedia Index Synchronizing for Attribute Bundles 
Executable: IBCSYNCATTR 
Frequency : Weekly 
Parameters: (None) 


Job       : Purge Debug Log and System Alerts FNDLGPRG 
Frequency : Weekly 
Parameters: (Date - 30 days)


Job       : Purge FND_STATS History Records 
Executable: FNDPGHST 
Frequency : Weekly 
Parameters: DATE, 01-Jan-00, 30-JUN-11 


Job       : Purge Obsolete Generic File Manager Data 
Executable: FNDGFMPR Daily 
Parameters: Y, , 


Job       : Purge Obsolete Workflow Runtime Data FNDWFPR 
Frequency : Weekly
Parameters: , , 60, PERM, N, 500, N Weekly , , 60, TEMP, N, 500, N 


Job       : Purge Signon Audit data FNDSCPRG 
Frequency : Weekly 
Parameters: (Date - 60 days)


Job       : Workflow Agent Activity Statistics Concurrent Program  
Executable: FNDWFAASTATCC 
Frequency : Daily 
Parameters: (None) 


Job       : Workflow Background Process 
Executable: FNDWFBG 
Frequency : 12 HOURS 
Parameters: , , , N, N, Y 
Frequency : 1 HOUR
Parameters: , , , N, Y, N 
Frequency : 5 MINUTES 
Parameters: , , , Y, N, N 


Job       : Workflow Control Queue Cleanup 
Executable: FNDWFBES_CONTROL_QUEUE_CLEANUP 
Frequency :  12 HOURS 
Parameters: (None) 


Job       : Workflow Directory Services User/Role Validation
Executable: FNDWFDSURV 
Frequency : Daily 
Parameters: 10000, Y, Y, Y 


Job       : Workflow Mailer Statistics Concurrent Program
Executable: FNDWFMLRSTATCC 
Frequency : Daily 
Parameters: (None) 


Job       : Workflow Work Items Statistics Concurrent Program
Executable: FNDWFWITSTATCC 
Frequency : Daily 
Parameters: (None)  


Enjoy!

Tablespace Free Space and Max Size

Earlier, provided a small script to find the maximum growable size of a tablespace... Here is little more comprehensive script that shows more tablespace space info. Current size, growable size based on the autoextensible data files, free space, biggest chunk...All size is in MB.


SELECT a.tablespace_name TSNAME, 
        ROUND(SUM(a.growable)/1048576) growable, 
        ROUND(SUM(a.tots)/1048576) Tot_Size,
        ROUND(SUM(a.sumb)/1048576) Tot_Free,
        ROUND(SUM(a.sumb)*100/sum(a.tots)) Pct_Free,
        ROUND(SUM(a.largest)/1048576) Large_Ext
FROM   (SELECt tablespace_name, 0 tots, SUM(bytes) sumb,
               MAX(bytes) largest, 0 growable
        FROM   dba_free_space a
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0,  SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0))) growable
        FROM   dba_data_files
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0,  SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0))) growable
        FROM   dba_temp_files
        GROUP BY tablespace_name) a, dba_tablespaces b
WHERE b.tablespace_name = a.tablespace_name
GROUP BY rollup(a.tablespace_name)
ORDER by a.tablespace_name

Enjoy!

SQL Script to find the top 50 tables by size

Here is a SQL to identify the top 50 tables. When I say table, it could be a table, partitioned table or materialized view. Takes into consideration the indexes defined on the table and lob segments in the table, thus gives a complete picture of the table size. When you truncate the table, this much would be the space released...


select top50.owner, top50.table_name, meg, a.num_rows 
from dba_tables a, 
   (Select * from (
SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type like 'TABLE%'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type like 'INDEX%'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore small tables */
ORDER BY SUM(bytes) desc
) where rownum < 51) top50
where top50.owner =a.owner
and top50.table_name = a.table_name
order by meg desc, num_rows desc;

Enjoy!

Script to Grant Privileges and Create Synonyms

Extending the previous post Granting privileges on all or multiple objects to user/role , here is another script.

Often we have a requirement to create read only accounts for a schema. Typically these accounts are named _QUERY. The script when executed will prompt for the user name of the read only account, and the schema name where read privileges on the objects to be granted. It produces output to the screen and writes to file named tmpgrants.sql. Execute the tmpgrants.sql to grant SELECT privilege and to create a synonym under the read only account, so that the query account need not worry about qualifying the table with schema name.

First create the read only user account using similar syntax as below...

create user tdmaa_query identified by tdm123q default tablespace users;
grant create session to tdmaa_query;

Be sure to save all the below lines to a file and run the file in SQL*Plus... Executing this script will produce a script file named tmpgrants.sql. Run that script to grant privileges and create synonyms.


set pages 0 lines 200 trims on verify off feedback off


accept grants_to  prompt 'Enter user to grant privileges: '
accept schema     prompt 'Enter schema on which to grant: '


spool tmpgrants.sql


select 'grant select on '||owner||'.'||table_name ||' to &grants_to;', chr(10),
       'create synonym &grants_to..'||table_name ||' for '||owner||'.'||table_name||';', chr(10)
from  dba_tables
where owner = upper('&schema')
union all
select 'grant select on '||owner||'.'||view_name ||' to &grants_to;', chr(10),
       'create synonym &grants_to..'||view_name ||' for '||owner||'.'||view_name||';', chr(10)
from  dba_views
where owner = upper('&schema')
;


spool off


set pages 99 lines 80 verify on feedback on


prompt "Run tmpgrants.sql if you are satistified with the script..."



Enjoy!

Granting privileges on all or multiple objects to user/role

Granting privileges on all or multiple objects to user/role

Question:
I need to grant SELECT, UPDATE privilege on all tables owned by schema XXVMX to users MSUBBU, SMARTIN. Is there a command in Oracle to grant a privilege on all objects in schema to user?

Answer:
No such privilege in Oracle. You will have to write a script to grant the privilege on individual objects to the users.

If you have to repeat the same to more users, it may be better to create a role and grant the privileges to the role. Then assign the role to the users that need the privilege. Thus when new tables are created under XXVMX, the privilege need to be added to the role once only, do not have to do grant to all the individual users.

Create Role:

CREATE ROLE XXVMX_UPDATE;

Grant Role to Users:

GRANT XXVMX_UPDATE to MSUBBU, SMARTIN;

SQL Script:

set pages 0
set lines 300 trims on feedback on echo off


spool grants.sql


SELECT 'grant select, update on ' || owner ||'.'||table_name|| ' to XXVMX_UPDATE;'
FROM dba_tables
WHERE owner = 'XXVMX';


spool off


set pages 99 lines 80
set feedback on echo on

Execute the script file created to grant the privileges.

@grants.sql

You may replace the "dba_tables" in the query with "dba_objects" and change the WHERE clause appropriately to filter different sets of objects...

Script to Refresh Materialized Views

The following script can be used to refresh materialized views that are capable of FAST (incremental) refresh automatically. Can be used on EBS database as well if you un-comment the commented (REM) lines. The refresh criteria used is any fast refresh-able materialized view that has not been refreshed in the past 24 hours, but was refreshed in the last one month...

REM Uncomment below line if EBS database

REM alter session set current_schema=apps;

set serveroutput on size 9999

Declare

cursor mv2ref is select owner ||'.'||  mview_name mview

       from dba_mviews

       where last_refresh_date between sysdate -30 and sysdate -1

       and fast_refreshable = 'DML';

dummy pls_integer;

begin

REM Uncomment below line if EBS database

REM select MRP_AP_REFRESH_S.NEXTVAL into dummy from dual;

for rmv2ref in mv2ref loop

begin

    dbms_mview.refresh(rmv2ref.mview, 'F');

    dbms_output.put_line(rmv2ref.mview ||' Refreshed at '|| to_char(systimestamp));

exception

when others then

    dbms_output.put_line(rmv2ref.mview ||' Errored at '|| to_char(systimestamp));

    dbms_output.put_line('    Error: '|| sqlcode ||':'||sqlerrm);

end;

end loop;

end;

/

Enjoy!

Query - Tablespace Max Size

I sometimes see concerned emails from developers that they are running out of space in tablespace and the DBA need to do something immediately. The DBA will start wondering we have monitoring in place, then how come it did not catch an out of space situation, but a user/developer did...

For you developer/user, if you are wondering if your database/tablespace is running out of space, you may need to check if the tablespace has autoextensible data files...

Here is a simple query, that could be used to find the current size of the tablespace and how much it can grow using the autoextensible feature of data file.


  SELECT tablespace_name,
         ROUND (SUM (bytes) / 1048576) curr_size,
         ROUND (SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0)))/ 1048576)  growable
    FROM dba_data_files
GROUP BY tablespace_name
ORDER BY tablespace_name;

Rename Diskgroup in ASM

Rename ASM Diskgroup [11gR2]

In 11gR2 it is possible to rename an ASM diskgroup. This is especially useful when performing database copy using OS level LUN mirroring technologies.

The renamedg command is used to rename diskgroups. It has the following options:

$ renamedg -help

Parsing parameters..

phase                           Phase to execute,

                                (phase=ONE|TWO|BOTH), default BOTH

dgname                          Diskgroup to be renamed

newdgname                       New name for the diskgroup

config                          intermediate config file

check                           just check-do not perform actual operation,

                                (check=TRUE/FALSE), default FALSE

confirm                         confirm before committing changes to disks,

                                (confirm=TRUE/FALSE), default FALSE

clean                           ignore errors,

                                (clean=TRUE/FALSE), default TRUE

asm_diskstring                  ASM Diskstring (asm_diskstring='discoverystring',

                                'discoverystring1' ...)

verbose                         verbose execution,

                                (verbose=TRUE|FALSE), default FALSE

keep_voting_files               Voting file attribute,

                                (keep_voting_files=TRUE|FALSE), default FALSE

To rename a diskgroup, it must be stopped [or unmounted].

$ srvctl stop diskgroup -g oops1

$ renamedg dgname=oops1 newdgname=good1 verbose=true

$ srvctl start diskgroup –g good1

You must manually rename all database files on the diskgroup to reflect the new name using ALTER DATABSE RENAME FILE command.

Even after successful rename command, old diskgroup resources in Oracle Restart must be manually removed using the srvctl command.

To Read More:  http://download.oracle.com/docs/cd/E11882_01/server.112/e16102/asmdiskgrps.htm#CHDJAGHE

Oracle11gR1 - To Read...

 Database Replay - OTN  Documentation

 Interval and Reference Partitions – Paper   Documentation

 SQL Query Result Cache - OTN   Documentation

SQL Performance Analyzer – OTN  Documentation

 SQL Plan Baseline – OTN  Documentation

Automatic SQL Tuning Enhancements - Documentation

Snapshot Standby - Documentation

Active Data Guard (Real-time query) - Documentation

OLTP Table Compression – OTN-OOW   Documentation

AutoTask Infrastructure - Documentation

Virtual Columns - Documentation

Flashback Data Archive (Oracle Total Recall) - OTN Documentation

Flashback Transaction - OTN  Documentation

Automatic Diagnostic Repository & ADRCI - Documentation

Data Recovery Advisor - OTN  Documentation

Active DB Duplication - Documentation

Multi-section backups - Documentation

Lightweight Jobs in Scheduler - Documentation

Fine grained dependency tracking - Documentation

Passwords are Case Sensitive in Oracle11g

The passwords in Oracle11g are case sensitive. All new users created in the 11g database have case sensitive password by default. For databases upgraded from earlier releases, the passwords are not case sensitive for existing accounts. They become case sensitive when you change password. A new column PASSWORD_VERSIONS is added to DBA_USERS view. A value ‘10G 11G’ in this column indicates that case sensitivity is enforced for the account.

The PASSWORD column is still available in DBA_USERS view, it is not populated anymore. For external authenticated or global accounts, the PASSWORD column indicates such.

select username, password_versions, password from dba_users;

USERNAME                       PASSWORD PASSWORD

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

MGMT_VIEW                      10G 11G

SYS                            10G 11G

SYSTEM                         10G 11G

SAMUEL                                  EXTERNAL

BTHOMAS                        10G 11G

ANONYMOUS

XDB                            10G 11G

Disable Case Sensitive Passwords

Oracle11g has a new parameter to disable the password case sensitivity – SEC_CASE_SENSITIVE_LOGON. This parameter is set to TRUE by default. Change to FALSE for pre-Oracle11g password behavior. This parameter can be changed using ALTER SYSTEM.

SYSDBA/SYSOPER password

The password file created using orapwd utility is also case sensitive by default. To make the password for SYS and SYSDBA/SYSOPER case insensitive, include flag ingnorecase=y in the command line.

$ orapwd file=L11GR1.ora ignorecase=y

Enter password for SYS:

DB Links

When connecting to an Oracle11g database with default SEC_CASE_SENSITIVE_LOGON using a database link from pre-11g database, make sure the database password in Oracle11g database is set up as ALL UPPERCASE. Pre-Oracle11g databases send password in uppercase for db link connections. For 11g to 11g, the password case must be the same; for 11g to pre-11g database, the password case does not matter.

	To Pre-Oracle11g	To Oracle11g
From Pre-Oracle11g	Case does not matter	Uppercase
From Oracle11g	Case does not matter	Same case

Users with default passwords

Oracle11g has a new very useful view to list the database accounts that have default password – DBA_USERS_WITH_DEFPWD. This view has only one column – USERNAME. By default the Oracle system accounts and example accounts are locked in Oracle11g.

select * from dba_users_with_defpwd;

USERNAME

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

DIP

MDSYS

WK_TEST

CTXSYS

HR

OUTLN

EXFSYS

SCOTT

MDDATA

ORDPLUGINS

ORDSYS

XDB

SI_INFORMTN_SCHEMA

WMSYS

Read Only Tables [Orig Published Apr 2000]

In Oracle (all versions including 10g) you can make a tablespace read only, but not a single table. To make a table read only, you need to have that table in its own tablespace. If the tablespace has many tables, then you can either make all the tables read only or not. For large or critical tables, you can have the tables that need to be made read only grouped together in tablespaces. This is especially important when you're are partitioning large tables. For example, if you partition a table that loads historical data, you can make the older partitions read only by making their corresponding tablespace read only.
To make the tablespace read only, issue the command:

ALTER TABLESPACE READ ONLY;

To make the tablespace back to read write mode, issue the command:

ALTER TABLESPACE READ WRITE;

FYI, you cannot make the SYSTEM tablespace to READ ONLY. You can drop a table from the read only tablespace, but not truncate, update, delete or insert.
So, what do you do if you need to make just one table read only, not all the tables in the tablespace.... Well, you can use triggers. Create a trigger which fires for all insert, update and delete operations on the table... Here is an example:

SQL> create or replace trigger emp_read_only
2 before insert or update or delete
3 on emp
4 begin
5 raise_application_error (-20001, 'Table EMP is read only, you cannot modify data.');
6 end;
7 /

Trigger created.

SQL> delete from emp;
delete from emp
*
ERROR at line 1:
ORA-20001: Table EMP is read only, you cannot modify data.
ORA-06512: at "BIJU.EMP_READ_ONLY", line 2
ORA-04088: error during execution of trigger 'BIJU.EMP_READ_ONLY'

Update:

As of Oracle11g, a new clause is introduced in the ALTER TABLE statement, that can make a table to read-only.

ALTER TABLE READ ONLY;

If you try to perform insert or update or delete on read-only table, you get an ORA-12081 error. The nice part about this feature is you are not able to do TRUNCATE operation on a read-only table. Remember, even if the tablespace is read-only, you can truncate a table. You can perform operations on index associated with read-only tables.

To resume normal update activity on the table, perform 

ALTER TABLE READ WRITE;

A new column READ_ONLY is added to DBA_TABLES. To list all the read-only tables in the database, you could do

select owner, table_name, tablespace_name

from dba_tables

where read_only = 'YES'; 

SQL*Net Configuration [Orig Published - Apr 1999]

Let's set up SQL*Net (known stuff for most DBAs, but I get many questions on this subject often, so here I just provide some examples/templates for the configuration files) on the UNIX server and client PC using the TCP/IP communication protocol. In Oracle8 SQL*Net is called Net8. SQL*Net enables client-server and server-server communication across any network. When a client or server makes a connection request, SQL*Net receives the request and, if more than one machine is involved, passes the request to its underlying layer, the transparent network substrate (TNS), to be transmitted over the appropriate communications protocol to the appropriate server. On the server, SQL*Net receives the request from TNS and passes it to the database as a network message with one or more parameters (that is, a SQL statement). For more information on SQL*Net technology, please refer to "Understanding SQL*Net" manual from Oracle corp.

SQL*Net software

SQL*Net and required protocol adapters come with the Oracle RDBMS CD and the Oracle client CD. Install SQL*Net software and TCP/IP protocol adapters along with server installation. SQL*Net on the server would be installed to $ORACLE_HOME/network. The executables are under $ORACLE_HOME/bin. Oracle7 client software would be installed under $ORACLE_HOME/network and Oracle8 Net8 client software would be installed under $ORACLE_HOME/net80. It is possible to access a Oracle7 database using Net8, also to access a Oracle8 database using SQL*Net (Oracle8 specific features may not be available).

Configuration

SQL*Net is mainly configured using 3 ascii files, which contain information about the database, the protocol, the host name and connection port. The default location for the configuration files is $ORACLE_HOME/network/admin. You may specify a different location by specifying the enviornment variable TNS_ADMIN on the server side. For client side, set this variable in the client side Windows Registry (for 32 bit) or in the oracle.ini file (for 16 bit). The following are the files associated.

listener.ora

The listener configuration file. This file is set up in the server. Specify the type of protocol (we discuss TCP/IP), the host name, the database instance name (SID) and oracle home directory for this instance. You can have multiple databases configured under the same listener name. The standard and default and recommended listener name is LISTENER and most of the machines need only one listener. You can have a Oracle7 listener and Oracle8 listener running with the same name but listening to different ports. Here is a sample listener.ora file:

USE_PLUG_AND_PLAY_LISTENER = OFF
USE_CKPFILE_LISTENER = OFF
CONNECT_TIMEOUT_LISTENER = 40
LISTENER=
	(ADDRESS_LIST=
	(ADDRESS=
		(PROTOCOL=IPC)
		(KEY=PRODDB)
	)
	(ADDRESS=
		(COMMUNITY = tcp.world)
		(PROTOCOL=tcp)
		(HOST=prodserver)
		(PORT=1521)
	)
	)
SID_LIST_LISTENER=(SID_LIST=
	(SID_DESC=
	(GLOBAL_DBNAME = proddb1.world)
	(SID_NAME = PRODDB1)
	(ORACLE_HOME = /ora0/app/oracle/product/8.0.5)
	(PRESPAWN_MAX = 500)
	(PRESPAWN_LIST=
		(PRESPAWN_DESC=
		(PROTOCOL=TCP) 
		(POOL_SIZE=10)
		(TIMEOUT = 2)
	)
	)
	(SID_DESC=
	(GLOBAL_DBNAME = proddb2.world)
	(SID_NAME = PRODDB2)
	(ORACLE_HOME = /ora0/app/oracle/product/8.0.5)
	)
	)

Please note that since the listener name is LISTENER, the parameters also end with LISTENER, if your listener name is DALLAS, the parameters would be SID_LIST_DALLAS, CONNECT_TIMEOUT_DALLAS, etc.
There are two instances defined in this listener file. Note that the second entry does not have any PRESPAWN entries (optional). For a description of these parameters and other control parameters, please refer to the manual "Understanding SQL*Net".

tnsnames.ora

This file is used to identify the destinations (databases). The file has information for the connect string the user specify, such as, the port number, host name, SID name, etc. This file should be available locally from where the connection is made (at the client PC as well as server). You can have different alias names (connect strings) for the same database defined. For each alias name, define an entry in this file. Here is an example:

DALPROD.world =
	(DESCRIPTION =
	(ADDRESS_LIST =
		(ADDRESS =
		(COMMUNITY = tcp.world)
		(PROTOCOL = TCP)
		(HOST = prodserver)
		(PORT = 1521)
		)
	)
	(CONNECT_DATA = (SID = PRODDB1)
	)
	)
PRODDB1.world =
	(DESCRIPTION =
	(ADDRESS_LIST =
		(ADDRESS =
		(COMMUNITY = tcp.world)
		(PROTOCOL = TCP)
		(HOST = prodserver)
		(PORT = 1521)
		)
	)
	(CONNECT_DATA = (SID = PRODDB1)
	)
	)
DALDEV.world =
	(DESCRIPTION =
	(ADDRESS_LIST =
		(ADDRESS =
		(COMMUNITY = tcp.world)
		(PROTOCOL = TCP)
		(HOST = devserver)
		(PORT = 1521)
		)
	(ADDRESS =
		(COMMUNITY = tcp.world)
		(PROTOCOL = TCP)
		(HOST = devserver)
		(PORT = 1525)
		)
	)
	(CONNECT_DATA = (SID = TESTDB1)
	)
	)

Make sure you have the HOST, PORT and SID defined correctly. You can also provide the IP address of the HOST instead of name. Notice the entry for DALDEV, there are two port addresses, this is to randomize connections between listners, if there are two listeners on the server (listener load balancing). All the lines are self explanatory, for more information, refer to "Understanding SQL*Net".

sqlnet.ora

This is a configuration file, which may be present in the server as well as the client. Basically this file is used to cofigure the following:

	Dead connections detection
	Tracing and logging parameters
	Default domain (notice that in the tnsnames.ora file we have .world in the connect string names, you can specify default domain as world in this file, so the users need not specity it while connecting)
	Other optional parameters

A sample file would be like this:

AUTOMATIC_IPC = OFF
SQLNET.EXPIRE_TIME = 0
NAMES.DEFAULT_DOMAIN = world
NAME.DEFAULT_ZONE = world
# DAEMON.TRACE_MASK = (106)
# DAEMON.TRACE_DIRECTORY = /ora_dump/network/trace
# DAEMON.TRACE_LEVEL = OFF
# Tracing Client Activity
# TRACE_LEVEL_CLIENT = USER
# TRACE_FILE_CLIENT = sqlnetc.trc
# TRACE_DIRECTORY_CLIENT = /ora_dump/network/trace
# Tracing Server Activity
# TRACE_LEVEL_SERVER = 16
# TRACE_FILE_SERVER = sqlnets.trc
# TRACE_DIRECTORY_SERVER = /ora_dump/network/trace
# Logging Client Activity
# LOG_FILE_CLIENT = sqlnetc.log
# LOG_DIRECTORY_CLIENT = /ora_dump/network/log
# Logging Server Activity
# LOG_FILE_SERVER = sqlnets.log
# LOG_DIRECTORY_SERVER = /ora_dump/network/log

Some parameters are specific to the server and some are specific to the client.

Managing the listener(UNIX):

Make sure you have ORACLE_HOME set, also if the config files are not in ORACLE_HOME/network/admin, set TNS_ADMIN to the directory.
To start the listener use the utility lsnrctl

lsnrctl start

If you have a listener name other than LISTENER then specify that name as

lsnrctl LISTENERNAME start

To stop the listener,

lsnrctl stop

If you have made changes to the listener, but do not want to stop and start, you may do

lsnrctl reload

To see the listener status,

lsnrctl status

You can also specify password for the listener.

Notes:

If you have many clients, it may be a maintenance problem to have tnsnames.ora and sqlnet.ora on each client PC. You can install SQL*Net on the network and everyone can share the same information. For 32 bit SQL*Net, make sure the client PC windows registry points to the network and for 16 bit SQL*Net set up the oracle.ini file with appropriate parameters. The oracle.ini file is mentioned in the win.ini file underORA_CONFIG=Drive:\path\oracle.ini. The parameters you have to look for are ORACLE_HOME, NLS_LANG, TNS_ADMIN, RDBMS73, ORA_NLS32 etc.

Retiring Biju's Oracle DBA Page

Hello,

I started the www.bijoos.com/oracle back in the late 90's and was relevant at that time... [I least I thought]... Then got sidetracked and got busy with various walks of life, and seldom updated the site since 2002... Also, a lot of DBA sites popped up and were much better than what was bijoos could offer... So, that is the past.

Now, I think a little blog site would be more relevant than having full blown hosted site... Hence this blog... Hope I will be able to share my technical experiences that could be of use to someone somewhere...

Similar to my Certification books, this blog will be for beginner DBAs.

As always, please provide your feedback, errors, omissions, etc...

Thanks!