Note:146599.1
1-Check for shared_pool params
SELECT SUBSTR(NAME,1,30) ,SUBSTR(VALUE,1,50),ISSES_MODIFIABLE ISSYS_MODIFIABLE FROM V$PARAMETER where name like 'shared_pool%';
2-Check free space in the shared_pool
select pool,to_char(to_number(v$parameter.value),'999,999,999,999,999,999') "shared pool size", to_char(v$sgastat.bytes,'999,999,999,999,999,999') "free bytes ", (v$sgastat.bytes/v$parameter.value)*100 "Percent Free"from v$sgastat,v$parameter where v$sgastat.name ='free memory' and v$parameter.name =decode(pool,'shared pool','shared_pool_size', 'large pool','large_pool_size','java pool','java_pool_size');
3-Check SHARED_POOL_RESERVED_MIN_ALLOC
select nam.ksppinm NAME,val.KSPPSTVL VALUE from x$ksppi nam,x$ksppsv val where nam.indx = val.indx and nam.ksppinm like '%shared%'order by 1;
4-Check V$SHARED_POOL_RESERVED
Inadequate Sizing of (shared_pool_size ,shared_pool_reserved_size )
If REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC or REQUEST_FAILURES is 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC Fragmentation if REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is > SHARED_POOL_RESERVED_MIN_ALLOC
5-Check X$KSMSP for freable memory
SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')'k' "AVG SIZE" FROM X$KSMSP GROUP BY KSMCHCLS;
a) if free memory (SIZ) is low (less than 5mb or so) you may need to increase the shared_pool_size and shared_pool_reserved_size
b) if perm continually grows then it is possible you are seeing system memory leak.
c) if freeabl and recr are always huge, this indicates that you have lots of cursor info stored that is not releasing
d) if free is huge but you are still getting 4031 errors, (you can correlate that with the reloads and invalids causing fragmentation)
6-Check for literal sql
select substr(sql_text,1,40),count(*) from v$sqlareagroup by substr(sql_text,1,40) having count(*)>5order by count(*)
/
7-Check V$SHARED_POOL_ADVICE for shared_pool size
select * from V$SHARED_POOL_ADVICE ;
8-check V$SHARED_POOL_RESERVED (Is my Reserved Area sized properly)
select free_space, avg_free_size, free_count, max_free_size, request_misses, request_failures,LAST_FAILURE_SIZEfrom v$shared_pool_reserved
;
Request Misses = 0 can mean the Reserved Area is too big. Request Misses always increasing but Request Failures not increasing can mean the Reserved Area is too small. In this case flushes in the Shared Pool satisfied the memory needs. Request Misses and Request Failures always increasing can mean the Reserved Area is too small and flushes in the Shared Pool are not helping (likely got an ORA-04031).
9-check which pool had error
select INDX,kghlurcr,kghlutrn,kghlufsh,kghluops,kghlunfu,kghlunfs
from sys.x$kghlu
whereinst_id = userenv('Instance');
10-Check Memory chunks
col sga_heap format a15
col size format a10
select KSMCHIDX "SubPool", 'sga heap('KSMCHIDX',0)'sga_heap,ksmchcom ChunkComment,
decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K',
4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,
'8-9k', 9,'9-10k','> 10K') "size",
count(*),ksmchcls Status, sum(ksmchsiz) Bytes
from x$ksmsp
where KSMCHCOM = 'free memory'
group by ksmchidx, ksmchcls,
'sga heap('KSMCHIDX',0)',ksmchcom, ksmchcls,decode(round(ksmchsiz/1000),0,'0-1K',
1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6,
'6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K');
Wednesday, January 9, 2008
How to Automate Pinning Objects in Shared Pool at Database Startup
1-I create the tables and jobs under user BAKIM you can create it where ever you want
2- If DBMS_SHARED_POOL is not exist then create it
select * from dba_objects where object_name='DBMS_SHARED_POOL';
sqlplus '/ as sysdba'
@?/rdbms/admin/dbmspool.sql
grant execute on dbms_shared_pool to bakim;
3- Create the tables that will keep the “WILL BE KEPT” objects
create table bakim.keep_in_shared_pool (OWNER VARCHAR2(30),OBJECT_NAME VARCHAR2(128),OBJECT_TYPE VARCHAR2(18))
tablespace tools storage (initial 1m next 1m)
/
alter table bakim. keep_in_shared_pool add constraints keep_in_shared_pool_pk primary key (owner,object_name,object_type)using index storage (initial 128k next 128k)
/
4-Create SPK_KEEP_ISLEM package under SYS which will used for finding and keeping the objects
create or replace package sys.spk_keep_islem is procedure keep_all_schema_procedures(ps_owner varchar2);
procedure keep_all_predefined_objects ;
procedure find_keep_in_shared_pool ;
end;
/
create or replace package body sys.spk_keep_islem is
-----------------------------------------------------------------------
procedure keep_all_schema_procedures(ps_owner varchar2) is
cursor cr_procedures is select 'SYS.dbms_shared_pool.keep('''owner'.'object_name''',''P'')' komut ,
owner'.'object_name oname , object_type
from dba_objects
where owner=ps_owner and object_type in ('PROCEDURE','PACKAGE','FUNCTION');
begin
for l in cr_procedures
loop SYS.dbms_shared_pool.keep(l.oname);
end loop ;
end;
procedure keep_all_predefined_objects is
cursor cr_keep_objects is
select owner'.'object_name oname,
decode(object_type,'PACKAGE','P','PACKAGE BODY','P','PROCEDURE','P','FUNCTION','P','TRIGGER','R','SEQUENCE','Q') otype
from bakim.keep_in_shared_pool k
where exists (select 1 from dba_objects o where o.owner=k.owner and o.object_name=k.object_name and o.object_type=k.object_type );
begin
for l in cr_keep_objects loop
if l.otype='P' then SYS.dbms_shared_pool.keep(l.oname);
else SYS.dbms_shared_pool.keep(l.oname,l.otype);
end if;
end loop ;
end;
procedure find_keep_in_shared_pool is
begin
delete bakim.keep_in_shared_pool k where not exists (select 1 from dba_objects o where k.owner=o.owner and
o.object_name=k.object_name and o.object_type=k.object_type);
insert into bakim.keep_in_shared_pool ( SELECT owner,name,type FROM v$db_object_cache c WHERE 1=1 and type in ('PROCEDURE','PACKAGE BODY','FUNCTION') and not exists (select 1 from bakim.keep_in_shared_pool k where k.owner=c.owner and c.name=k.object_name and c.type=k.object_type) and owner not in ('PKGBACKUP','SERDAR','ABDULLAH','ZIYA','ERKAN','DR0000'));
commit;
end;
end;
/
----------------------------------------------------------------
grant execute on sys.spk_keep_islem to bakim,dba
/
5- If it is the first time then keep the KEEPED objects in the table
insert into bakim.keep_in_shared_pool SELECT OWNER,NAME,TYPE FROM v$db_object_cache WHERE type in ('PROCEDURE','PACKAGE BODY') and kept='YES'
/
exec sys.spk_keep_islem.find_keep_in_shared_pool;
exec sys.spk_keep_islem.keep_all_predefined_objects;
6-Create trigger that will pin the objects in bakim.keep_in_shared_pool for every startup
select 'DROP TRIGGER ' owner'.'trigger_name';'from dba_triggers where trigger_name like '%KEEP%';
create or replace TRIGGER SYS.db_startup_keep
AFTER STARTUPON DATABASE
BEGIN
sys.spk_keep_islem.keep_all_predefined_objects;
end;
/
7-you can submit a job finding the shared_pool objects for every hour or we can do it while closing the database by the help of a trigger or we can do it both
CREATE OR REPLACE TRIGGER SYS.db_shutdown_keep
BEFORE SHUTDOWN ON DATABASE
BEGIN
sys.spk_keep_islem.find_keep_in_shared_pool;
END;
/
OR /AND
Conn BAKIM
declare
ln_jobno NUMBER;
ls_job VARCHAR2(2000);
begin
ls_job := 'sys.spk_keep_islem.find_keep_in_shared_pool;';
dbms_job.isubmit(1300, ls_job, sysdate, 'TRUNC(sysdate+1)+1/24',false);
dbms_output.put_line('job 'ln_jobno' started..');
commit;
end;
/
8- We can check the pinned objects
select count(*) from bakim.keep_in_shared_pool;
select type,KEPT,COUNT(*),sum(SHARABLE_MEM) FROM v$db_object_cache GROUP BY TYPE,KEPT;
select owner,count(*) from bakim.keep_in_shared_pool group by owner;
SELECT SUBSTR(owner,1,10) Owner,SUBSTR(type,1,12) Type,SUBSTR(name,1,30) Name,executions,sharable_mem Mem_used,SUBSTR(kept' ',1,4) "Kept?"
FROM v$db_object_cache
WHERE 1=1and type in ('PROCEDURE','PACKAGE BODY','PACKAGE')
ORDER BY sharable_mem
/
Note: 101627 is a usefull document
2- If DBMS_SHARED_POOL is not exist then create it
select * from dba_objects where object_name='DBMS_SHARED_POOL';
sqlplus '/ as sysdba'
@?/rdbms/admin/dbmspool.sql
grant execute on dbms_shared_pool to bakim;
3- Create the tables that will keep the “WILL BE KEPT” objects
create table bakim.keep_in_shared_pool (OWNER VARCHAR2(30),OBJECT_NAME VARCHAR2(128),OBJECT_TYPE VARCHAR2(18))
tablespace tools storage (initial 1m next 1m)
/
alter table bakim. keep_in_shared_pool add constraints keep_in_shared_pool_pk primary key (owner,object_name,object_type)using index storage (initial 128k next 128k)
/
4-Create SPK_KEEP_ISLEM package under SYS which will used for finding and keeping the objects
create or replace package sys.spk_keep_islem is procedure keep_all_schema_procedures(ps_owner varchar2);
procedure keep_all_predefined_objects ;
procedure find_keep_in_shared_pool ;
end;
/
create or replace package body sys.spk_keep_islem is
-----------------------------------------------------------------------
procedure keep_all_schema_procedures(ps_owner varchar2) is
cursor cr_procedures is select 'SYS.dbms_shared_pool.keep('''owner'.'object_name''',''P'')' komut ,
owner'.'object_name oname , object_type
from dba_objects
where owner=ps_owner and object_type in ('PROCEDURE','PACKAGE','FUNCTION');
begin
for l in cr_procedures
loop SYS.dbms_shared_pool.keep(l.oname);
end loop ;
end;
procedure keep_all_predefined_objects is
cursor cr_keep_objects is
select owner'.'object_name oname,
decode(object_type,'PACKAGE','P','PACKAGE BODY','P','PROCEDURE','P','FUNCTION','P','TRIGGER','R','SEQUENCE','Q') otype
from bakim.keep_in_shared_pool k
where exists (select 1 from dba_objects o where o.owner=k.owner and o.object_name=k.object_name and o.object_type=k.object_type );
begin
for l in cr_keep_objects loop
if l.otype='P' then SYS.dbms_shared_pool.keep(l.oname);
else SYS.dbms_shared_pool.keep(l.oname,l.otype);
end if;
end loop ;
end;
procedure find_keep_in_shared_pool is
begin
delete bakim.keep_in_shared_pool k where not exists (select 1 from dba_objects o where k.owner=o.owner and
o.object_name=k.object_name and o.object_type=k.object_type);
insert into bakim.keep_in_shared_pool ( SELECT owner,name,type FROM v$db_object_cache c WHERE 1=1 and type in ('PROCEDURE','PACKAGE BODY','FUNCTION') and not exists (select 1 from bakim.keep_in_shared_pool k where k.owner=c.owner and c.name=k.object_name and c.type=k.object_type) and owner not in ('PKGBACKUP','SERDAR','ABDULLAH','ZIYA','ERKAN','DR0000'));
commit;
end;
end;
/
----------------------------------------------------------------
grant execute on sys.spk_keep_islem to bakim,dba
/
5- If it is the first time then keep the KEEPED objects in the table
insert into bakim.keep_in_shared_pool SELECT OWNER,NAME,TYPE FROM v$db_object_cache WHERE type in ('PROCEDURE','PACKAGE BODY') and kept='YES'
/
exec sys.spk_keep_islem.find_keep_in_shared_pool;
exec sys.spk_keep_islem.keep_all_predefined_objects;
6-Create trigger that will pin the objects in bakim.keep_in_shared_pool for every startup
select 'DROP TRIGGER ' owner'.'trigger_name';'from dba_triggers where trigger_name like '%KEEP%';
create or replace TRIGGER SYS.db_startup_keep
AFTER STARTUPON DATABASE
BEGIN
sys.spk_keep_islem.keep_all_predefined_objects;
end;
/
7-you can submit a job finding the shared_pool objects for every hour or we can do it while closing the database by the help of a trigger or we can do it both
CREATE OR REPLACE TRIGGER SYS.db_shutdown_keep
BEFORE SHUTDOWN ON DATABASE
BEGIN
sys.spk_keep_islem.find_keep_in_shared_pool;
END;
/
OR /AND
Conn BAKIM
declare
ln_jobno NUMBER;
ls_job VARCHAR2(2000);
begin
ls_job := 'sys.spk_keep_islem.find_keep_in_shared_pool;';
dbms_job.isubmit(1300, ls_job, sysdate, 'TRUNC(sysdate+1)+1/24',false);
dbms_output.put_line('job 'ln_jobno' started..');
commit;
end;
/
8- We can check the pinned objects
select count(*) from bakim.keep_in_shared_pool;
select type,KEPT,COUNT(*),sum(SHARABLE_MEM) FROM v$db_object_cache GROUP BY TYPE,KEPT;
select owner,count(*) from bakim.keep_in_shared_pool group by owner;
SELECT SUBSTR(owner,1,10) Owner,SUBSTR(type,1,12) Type,SUBSTR(name,1,30) Name,executions,sharable_mem Mem_used,SUBSTR(kept' ',1,4) "Kept?"
FROM v$db_object_cache
WHERE 1=1and type in ('PROCEDURE','PACKAGE BODY','PACKAGE')
ORDER BY sharable_mem
/
Note: 101627 is a usefull document
Thursday, November 15, 2007
Installing Oracle Configuration Manager (Disconnect Mode )
1.Oracle Configuration Manager Installation
After downloading the OCR zip file from Metalink
1.1-Log in as the ORACLE_HOME owner.
1.2-Unzip the Oracle Configuration Manager into the ORACLE_HOME directory
unzip -d $ORACLE_HOME ccr-Production-10.2.5.0.0--.zip
1.3-setup ccrcd
$ORACLE_HOME/ccr/binsetupCCR [-s] [-d -p] [] [][]
setupCCR -s -d 15626546 serdar.turgut@fortis.com.tr TR
After downloading the OCR zip file from Metalink
1.1-Log in as the ORACLE_HOME owner.
1.2-Unzip the Oracle Configuration Manager into the ORACLE_HOME directory
unzip -d $ORACLE_HOME ccr-Production-10.2.5.0.0-
1.3-setup ccrcd
$ORACLE_HOME/ccr/binsetupCCR [-s] [-d -p
setupCCR -s -d 15626546 serdar.turgut@fortis.com.tr TR
- The -s parameter indicates the acceptance of the Oracle Configuration Manager license agreement
- If you use the -d parameter, Oracle Configuration Manager will be installed in Disconnected Mode.
- The -d and -p parameters are mutually exclusive and cannot be used togetherCSI is the Customer Support Identifier.
- Your CSI number can be found in theProfile section of Oracle MetaLink under the Licenses link
- Country-Code is the customer’s country code (TR for Turkey)
- The -p parameter is used to specify the proxy server information needed to connect to the Internet
Note: If you are using a Unix system and you do not have access to CRON, you may encounter problems when installing Oracle Configuration Manager on Unix systems. To work around this problem, set the environment variable CCR_DISABLE_CRON_ENTRY to any value and retry the installation. You need to do this only if CRONTAB is a restricted operation
2.Oracle Configuration Manager database installation
2.1.If you run on 9.2 database you must add $ORACLE_HOME/ccr/state directory to UTL_FILE_DIR parameter and restart it
2.2.Configure the database by running installCCRSQL.sh (Unix) or installCCRSQL.exe (Windows)
$ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh collectconfig -s
By default, the connection to the database is through OS authentication, "/as sysdba." To specify a different SYSDBA user and password, you can use these options
$ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh collectconfig -s CRCDT
Note:If the Oracle Configuration Manager account already exists, when you run the installCCRSQL.sh script, it will be dropped and re-created.
Note: If you are upgrading from a 9.x database version to a 10.x version, you must run the installCCRSQL.sh script again to record the upgraded version.
2.3 Additional Step for E-Business Suites
$ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh ebs_collectconfig -u
2.4 Additional Step for Oracle Enterprise Manager Grid Control$ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh collectemrep
3.Uninstalling Oracle Configuration Manager
3.1-If the ORACLE_HOME directory contains a database, remove the Oracle
Configuration Manager user and the associated objects from the database as
follows:
SQL> @ccr/admin/scripts/dropocm.sql;
3.2. If the database is a repository for the Oracle E-Business Suite, log in to the
database as an SYSDBA user and remove the additional objects from the database
as follows:
SQL> @ccr/admin/scripts/ebs dropccr.sql
3.3. If the database is a repository for Oracle Grid Control, log in to the database as the
SYSMAN user and remove the additional objects from the database as follows:
SQL> @ccr/admin/scripts/dropemrep_collect.sql;
3.4. To stop the Scheduler and remove the service or the crontab entry, enter the
following command:
$ORACLE_HOME/ccr/bin/deployPackages -d $ORACLE_HOME/ccr/inventory/core.jar
3.5. Delete the ccr directory by entering the following command:
$rm -rf $ORACLE_HOME/ccr (On Unix)
>rmdir /s/q %ORACLE_HOME%\ccr (On Windows)
4.Collecting Configuration Data (Disconnected Mode)
4.1Collect data
$ORACLE_HOME/ccr/bin/emCCR collect
4.2 Upload data
we can upload $ORACLE_HOME/ccr/state/upload/ocmconfig.jar in SR
also a set of files are generated and stored in the "$ORACLE_HOME/ccr/state/review" directory. These files are XML and have a style sheet
Thursday, November 1, 2007
Can one generate HTML reports from SQL*Plus?
One can generate static HTML pages from SQL*Plus (8.1.6 and above) by setting the MARKUP option to HTML ON. This can be done by specifying -MARKUP "HTML ON" from command line, or with the "SET MARKUP HTML ON" command. Look at this example SQL Script:
set markup HTML on
spool index.html
select * from tab;
spool off
set markup HTML off
You can deploy this file on your web site or edit it in an HTML editor (like FrontPage or Dreamweaver). Another good idea is to develop a CSS to present the data more elegantly. One can also embed HTML tags in the select statement to create hyperlinks and add more HTML features.
Ref:http://www.orafaq.com/faqplus.htm
set markup HTML on
spool index.html
select * from tab;
spool off
set markup HTML off
You can deploy this file on your web site or edit it in an HTML editor (like FrontPage or Dreamweaver). Another good idea is to develop a CSS to present the data more elegantly. One can also embed HTML tags in the select statement to create hyperlinks and add more HTML features.
Ref:http://www.orafaq.com/faqplus.htm
Monday, October 15, 2007
Oracle users default password
Check the Oracle users default passwords(check_default_password.sql)
- version <11g br="">select username "User(s) with Default Password!", account_status "Status"
from dba_users where password in(
'E066D214D5421CCC', -- dbsnmp
'24ABAB8B06281B4C', -- ctxsys
'72979A94BAD2AF80', -- mdsys
'9AAEB2214DCC9A31', -- mdsys
'C252E8FA117AF049', -- odm
'A7A32CD03D3CE8D5', -- odm_mtr
'88A2B2C183431F00', -- ordplugins
'7EFA02EC7EA6B86F', -- ordsys
'9B616F5489F90AD7', -- ordcommon
'4A3BA55E08595C81', -- outln
'F894844C34402B67', -- scott
'3F9FBD883D787341', -- wk_proxy
'79DF7A1BD138CF11', -- wk_sys
'7C9BA362F8314299', -- wmsys
'88D8364765FCE6AF', -- xdb
'F9DA8977092B7B81', -- tracesvr
'9300C0977D7DC75E', -- oas_public
'A97282CE3D94E29E', -- websys
'AC9700FD3F1410EB', -- lbacsys
'E7B5D92911C831E1', -- rman
'AC98877DE1297365', -- perfstat
'66F4EF5650C20355', -- exfsys
'84B8CBCA4D477FA3', -- si_informtn_schema
'D4C5016086B2DC6A', -- sys
'5638228DAF52805F', -- sys
'D4DF7931AB130E37') -- system
/ - versin >=11g
select username "User(s) with Default Password!", account_status "Status" from dba_users where username in (select username from dba_users_with_defpwd)
/
Wednesday, October 3, 2007
Oracle Database Listener Security Guide
STEP 1 – SET THE LISTENER PASSWORD
Set the Listener password to stop most attacks and security issues. This is usually a simple process. You should set the password using lsnrctl, which will encrypt the password stored in listener.ora. Setting the password manually in listener.ora using the PASSWORDS_ parameter will result in the password being stored in cleartext.
LSNRCTL> set current_listener
LSNRCTL> change_password
Old password:
New password:
Reenter new password:
LSNRCTL> set password
Password:
LSNRCTL> save_config
Check the listener.ora file to see if there is now a parameter PASSWORDS_. It is important to remember that the actual encrypted password can be used in place of the actual password prior to Oracle 10g.
STEP 2 – TURN ON LOGGING [MANDATORY]
Turn on logging for all listeners in order to capture Listener commands and brute force password attacks.
LSNRCTL> set current_listener
LSNRCTL> set password
Password:
LSNRCTL> set log_directory/network/admin
LSNRCTL> set log_file.log
LSNRCTL> set log_status on
LSNRCTL> save_config
STEP 3 – SET ADMIN_RESTRICTIONS IN LISTENER.ORA [MANDATORY]
All runtime modifications to the Listener can be disabled by setting the parameter ADMIN_RESTRICTIONS_ to ON in the listener.ora file. This parameter stops all set commands from being executed either locally or remotely. All changes must be made manually to the listener.ora file.
LISTENER.ORA
ADMIN_RESTRICTIONS_ = ON
Restart the listener using the reload command in lsnrctl for this change to take effect. Any future changes must be made in the listener.ora file, not using set commands in lsnrctl. After making any changes to the listener.ora file, use the reload command (or stop and start) in lsnrctl.
if Local OS Authentication is disabled also It must be set for 10g listener otherwise there is no need .
STEP 4 – APPLY LISTENER SECURITY PATCHES [MANDATORY]
Apply at least the January 2006 Critical Patch Update for the latest Listener security patches (as of March 2007). Critical Patch Updates are cumulative, therefore, the latest patch will contain all previous security patches for the Listener.
STEP 5 – BLOCK SQL*NET ON FIREWALLS
SQL*Net traffic should not be allowed to pass through firewalls unless absolutely necessary. Firewall filters should be designed to only allow SQL*Net traffic from known application and web servers. SQL*Net traffic from application servers in the DMZ should be permitted only to access specific database servers.
Few applications require direct SQL*Net connections to a database from the Internet. SQL*Net performs poorly over high latency networks, thus is seldom used in Internet applications. If applications do require direct SQL*Net access, configure firewall filters based on a specific host and port number.
STEP 6 – SECURE THE $TNS_ADMIN DIRECTORY [MANDATORY]
The Listener password is stored in the listener.ora file. Manually editing the file, the password can easily be removed or changed. If the password was manually added to the file, it is stored in clear text. When added through lsnrctl, it will be stored as an encrypted string.
The permissions on the listener.ora, sqlnet.ora, and protocol.ora files in the $TNS_ADMIN directory (usually $ORACLE_HOME/network/admin) should be read/write/execute for only the primary oracle account and no permissions for any other account (for UNIX and Linux 0600). The tnsnames.ora file permissions should be set to 0644 on UNIX and Linux.
chmod 644 $ORACLE_HOME/network/admin/tnsnames.ora
chmod 600 $ORACLE_HOME/network/admin
STEP 7 – SECURE TNSLSNR AND LSNRCTL [OPTIONAL]
The tnslsnr and lsnrctl executables in the $ORACLE_HOME/bin directory should be protected and file permissions should be set to 0751 on UNIX and Linux as recommended by Oracle. It is possible to change the file permissions to 0700 which would be more secure, although this should be thoroughly tested in your environment.
chmod 700 $ORACLE_HOME/bin/tnslsnr
chmod 700 $ORACLE_HOME/bin/lsnrctl
STEP 8 – REMOVE UNUSED SERVICES [MANDATORY]
Many default installations have a listener entry for PL/SQL External Procedures (ExtProc). The entry name is usually ExtProc or PLSExtProc. Often ExtProc is installed by default, but is not used. Check with your application development team or application documentation to determine if ExtProc is used.
If ExtProc is not used, remove it from the listener.ora file. There are several exploits directed at ExtProc.
Since listener.ora files are sometimes copied between instances, they may contain old and unused entries. Check all the other services to determine if they are used. Delete any services not actively used.
STEP 9 – CHANGE THE TNS PORT NUMBER FROM 1521 [OPTIONAL]
In order to help stop automated attacks and detection of the Listener in networks, the default NTS port number should be changed from 1521 to a port outside of the 1521-1550 and 1600-1699 ranges. This will provide only minimal additional security through obscurity, but may thwart an automated attack or simply scanning for Oracle Databases on port 1521.
The port number can be changed using Oracle Net Manager (netmgr) or editing the listener.ora file directly. All tnsnames.ora files on the database server and any clients must be updated to reflect the change in the port number. The database initialization parameter LOCAL_LISTENER must be set so that the database is able to dynamically register with the Listener. See Metalink Note ID 359277.1 "Changing Default Listener Port Number" for more information.
STEP 10 – SETUP VALID NODE CHECKING [OPTIONAL]
Depending on the type of application and network configuration, valid node checking can be a powerful tool to restrict most traffic from the Listener. Most web applications only require access to the Listener from the application servers and a limited number of clients for administration.
The simplest method to determine valid IP addresses for node checking is through database auditing. We recommended you always have session level auditing enabled.
For Oracle 9i/10g, the valid node checking lines are added to the $ORACLE_HOME/network/admin/sqlnet.ora file. For Oracle 8/8i, the lines are added to the $ORACLE_HOME/network/admin/protocol.ora file.
tcp.validnode_checking = yes
tcp.invited_nodes = (x.x.x.x name, x.x.x.x name)
tcp.excluded_nodes=( x.x.x.x name, x.x.x.x name)
Include either the invited_nodes or excluded_nodes, but do not use both. Wildcards, subnets, etc. are not valid, only individual IP addresses or host names are allowed. For more sophisticated checking, use Oracle Connection Manager.
The Listener must be stopped and started for valid node checking to become active. There is no hard limit on the number of nodes that can be included, but for a large number of entries using Oracle Connection Manager may be a better solution. If many clients require direct SQL*Net access to the database, it is often difficult to use valid node checking due to constantly changing network configurations.
STEP 11 – MONITOR THE LOGFILE [OPTIONAL]
The logfile in Step 2 may contain TNS-01169, TNS-01189, TNS-01190, or TNS-12508 errors, which may signify attacks or inappropriate activity. Using a simple shell script or management tools, monitor the logfile and generate an alert whenever these errors reaches are encountered.
Set the Listener password to stop most attacks and security issues. This is usually a simple process. You should set the password using lsnrctl, which will encrypt the password stored in listener.ora. Setting the password manually in listener.ora using the PASSWORDS_
LSNRCTL> set current_listener
LSNRCTL> change_password
Old password:
New password:
Reenter new password:
LSNRCTL> set password
Password:
LSNRCTL> save_config
Check the listener.ora file to see if there is now a parameter PASSWORDS_
STEP 2 – TURN ON LOGGING [MANDATORY]
Turn on logging for all listeners in order to capture Listener commands and brute force password attacks.
LSNRCTL> set current_listener
LSNRCTL> set password
Password:
LSNRCTL> set log_directory
LSNRCTL> set log_file
LSNRCTL> set log_status on
LSNRCTL> save_config
STEP 3 – SET ADMIN_RESTRICTIONS IN LISTENER.ORA [MANDATORY]
All runtime modifications to the Listener can be disabled by setting the parameter ADMIN_RESTRICTIONS_
LISTENER.ORA
ADMIN_RESTRICTIONS_
Restart the listener using the reload command in lsnrctl for this change to take effect. Any future changes must be made in the listener.ora file, not using set commands in lsnrctl. After making any changes to the listener.ora file, use the reload command (or stop and start) in lsnrctl.
if Local OS Authentication is disabled also It must be set for 10g listener otherwise there is no need .
STEP 4 – APPLY LISTENER SECURITY PATCHES [MANDATORY]
Apply at least the January 2006 Critical Patch Update for the latest Listener security patches (as of March 2007). Critical Patch Updates are cumulative, therefore, the latest patch will contain all previous security patches for the Listener.
STEP 5 – BLOCK SQL*NET ON FIREWALLS
SQL*Net traffic should not be allowed to pass through firewalls unless absolutely necessary. Firewall filters should be designed to only allow SQL*Net traffic from known application and web servers. SQL*Net traffic from application servers in the DMZ should be permitted only to access specific database servers.
Few applications require direct SQL*Net connections to a database from the Internet. SQL*Net performs poorly over high latency networks, thus is seldom used in Internet applications. If applications do require direct SQL*Net access, configure firewall filters based on a specific host and port number.
STEP 6 – SECURE THE $TNS_ADMIN DIRECTORY [MANDATORY]
The Listener password is stored in the listener.ora file. Manually editing the file, the password can easily be removed or changed. If the password was manually added to the file, it is stored in clear text. When added through lsnrctl, it will be stored as an encrypted string.
The permissions on the listener.ora, sqlnet.ora, and protocol.ora files in the $TNS_ADMIN directory (usually $ORACLE_HOME/network/admin) should be read/write/execute for only the primary oracle account and no permissions for any other account (for UNIX and Linux 0600). The tnsnames.ora file permissions should be set to 0644 on UNIX and Linux.
chmod 644 $ORACLE_HOME/network/admin/tnsnames.ora
chmod 600 $ORACLE_HOME/network/admin
STEP 7 – SECURE TNSLSNR AND LSNRCTL [OPTIONAL]
The tnslsnr and lsnrctl executables in the $ORACLE_HOME/bin directory should be protected and file permissions should be set to 0751 on UNIX and Linux as recommended by Oracle. It is possible to change the file permissions to 0700 which would be more secure, although this should be thoroughly tested in your environment.
chmod 700 $ORACLE_HOME/bin/tnslsnr
chmod 700 $ORACLE_HOME/bin/lsnrctl
STEP 8 – REMOVE UNUSED SERVICES [MANDATORY]
Many default installations have a listener entry for PL/SQL External Procedures (ExtProc). The entry name is usually ExtProc or PLSExtProc. Often ExtProc is installed by default, but is not used. Check with your application development team or application documentation to determine if ExtProc is used.
If ExtProc is not used, remove it from the listener.ora file. There are several exploits directed at ExtProc.
Since listener.ora files are sometimes copied between instances, they may contain old and unused entries. Check all the other services to determine if they are used. Delete any services not actively used.
STEP 9 – CHANGE THE TNS PORT NUMBER FROM 1521 [OPTIONAL]
In order to help stop automated attacks and detection of the Listener in networks, the default NTS port number should be changed from 1521 to a port outside of the 1521-1550 and 1600-1699 ranges. This will provide only minimal additional security through obscurity, but may thwart an automated attack or simply scanning for Oracle Databases on port 1521.
The port number can be changed using Oracle Net Manager (netmgr) or editing the listener.ora file directly. All tnsnames.ora files on the database server and any clients must be updated to reflect the change in the port number. The database initialization parameter LOCAL_LISTENER must be set so that the database is able to dynamically register with the Listener. See Metalink Note ID 359277.1 "Changing Default Listener Port Number" for more information.
STEP 10 – SETUP VALID NODE CHECKING [OPTIONAL]
Depending on the type of application and network configuration, valid node checking can be a powerful tool to restrict most traffic from the Listener. Most web applications only require access to the Listener from the application servers and a limited number of clients for administration.
The simplest method to determine valid IP addresses for node checking is through database auditing. We recommended you always have session level auditing enabled.
For Oracle 9i/10g, the valid node checking lines are added to the $ORACLE_HOME/network/admin/sqlnet.ora file. For Oracle 8/8i, the lines are added to the $ORACLE_HOME/network/admin/protocol.ora file.
tcp.validnode_checking = yes
tcp.invited_nodes = (x.x.x.x name, x.x.x.x name)
tcp.excluded_nodes=( x.x.x.x name, x.x.x.x name)
Include either the invited_nodes or excluded_nodes, but do not use both. Wildcards, subnets, etc. are not valid, only individual IP addresses or host names are allowed. For more sophisticated checking, use Oracle Connection Manager.
The Listener must be stopped and started for valid node checking to become active. There is no hard limit on the number of nodes that can be included, but for a large number of entries using Oracle Connection Manager may be a better solution. If many clients require direct SQL*Net access to the database, it is often difficult to use valid node checking due to constantly changing network configurations.
STEP 11 – MONITOR THE LOGFILE [OPTIONAL]
The logfile in Step 2 may contain TNS-01169, TNS-01189, TNS-01190, or TNS-12508 errors, which may signify attacks or inappropriate activity. Using a simple shell script or management tools, monitor the logfile and generate an alert whenever these errors reaches are encountered.
Thursday, September 27, 2007
Oracle Database Listener Concepts
The Listener is comprised of two binaries: (1) tnslsnr which is the Listener itself and (2) the Listener Control Utility (lsnrctl) which is used to administer the Listener on the server or remotely
The relevant files for the Listener are as follows
$ORACLE_HOME/bin/lsnrctl Listener control program
$ORACLE_HOME/network/admin/listener.ora Configuration file for the Listener
$ORACLE_HOME/network/admin/sqlnet.ora Configuration file for the Listener
$ORACLE_HOME/bin/tnslnsr Server Listener process
Listener Modes :The Listener can be configured in one of three modes (as configured in listener.ora) –
· Database Provides network access to an Oracle database instance
· PLSExtProc Method for PL/SQL packages to access operating system executables
· Executable Provides network access to operating system executables
LISTENER REMOTE MANAGEMENT
DBAs are not aware that the Listener in Oracle 8i/9i can be remotely managed using lsnrctl or a similar program from a remote machine. The Oracle 10g Listener by default cannot be remotely managed unless local OS authentication is disabled.
1-The simplest method to remotely issue commands to a Listener is to use lsnrctl with command-line parameters as such
– lsnrctl:
– lsnrctl status 192.168.1.100
– lsnrctl stop 192.168.1.100:1522
2- To set up a computer to remotely administer a Listener
- Configure the local listener.ora to resolve to the remote Listener
= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (Host = )(Port = ) ) )
- Start from the command line lsnrctl and specify the Listener name
lsnrctl
LSNRCTL> set current_listener
The following are some examples of possible attacks against an Oracle 8i/9i Listener which has a default configuration and is not properly secured.
Execute SQL as DBA: It is possible to overwrite the ORACLE_HOME/sqlplus/admin/glogin.sql by changing the location of the log file and then sending SQL statements in Listener commands to the file. When the SQL*Plus is executed locally on the server (usually by a DBA), then the SQL statements are executed during the SQL*Plus startup.
Allow Login via rlogin:The Listener log can be used to overwrite an .rlogin file with additional host information, thus allowing an attacker access the server using rlogin.
Denial of Service (DoS) An attacker is able to –
· Stop the Listener
· Set a Listener password so that the Listener ca not be started without a password, although the DBA simply has to edit the listener.ora file and remove the password line
Denial of Service (DoS):Undermine the stability of the server and database by overwriting arbitrary files by changing the directory and filename of the log and trace files to any location accessible by the operating system account that owns the database (usually "oracle").
Denial of Service (DoS) Setting the Listener trace level to "support" may cause performance degradation on a heavily accessed database server.
Information Disclosure:Obtain detailed information on the Listener configuration and database installation such as –
· Database Service Names (e.g., SIDs)
· Database and Listener versions
· Log and trace settings including directory and file names
· Security settings
· Database server operating system
· Oracle environmental variables (ORACLE_HOME, etc.)
LISTENER EXPLOITS
1- If a password is not set on the Listener, someone who knows just a hostname and port number (default port is 1521) has full control over the Listener
2- Oracle Security Alerts:
Check the Oracle security alerts
3- Brute Forcing Listener Password:
The Listener password can easily be brute forced, since there is no automatic lockout facility and no requirements for strong passwords. Repetitive set password commands can be sent to the listener using a hacking program. If logging is enabled (set log_status on), invalid password attempts will appear with an error code of TNS-01169.
4- Passwords Transmitted in Clear Text:
Using the set password command remotely will transmit the password across the network in clear text with every command. If encryption is setup for the listener using the Advanced Security Option (ASO), then the passwords will be sent encrypted across the network. The change password command does encrypt the password when the lsnrctl program is used
ORACLE LISTENER PASSWORD
The password for the Listener is stored in the listener.ora file. If the PASSWORDS_ parameter is manually set, then the password remains in plain-text. If set using lsnrctl and the change_password command, then the password is encrypted as 8-byte string. Unlike the database, the Listener password is case-sensitive.
Prior to Oracle 10g, the encrypted password string could be substituted for the actual password when issuing the set password command. This is useful in executing scripts to stop the Listener. If a password is set for the Oracle 10g Listener, scripts must use the actual password rather than the encrypted string.
If the Listener password is set to "mypassword", then the listener.ora file will have the encrypted string. The following lsnrctl commands using either the plain-text password or encrypted string will both work prior to Oracle 10g.
Listener.ora
PASSWORDS_LISTENER = F4BAA4A006C26134
LSNRCTL> set password
Password: mypassword
LSNRCTL> set password Password: F4BAA4A006C26134
ORACLE 10G LOCAL OS AUTHENTICATION
A major change to Listener security in Oracle 10g (10.1 and 10.2) was the introduction of Local OS Authentication. By default, the Listener cannot be remotely managed and can only be managed locally by the owner of the tnslsnr process (usually oracle).
If another operating system user attempts to manage the Listener, the following message will be displayed in the Listener log file –
TNS-01190: The user is not authorized to execute the requested listener command
If someone attempts to managed the Listener remotely, the following message will be displayed in the Listener log file –
TNS-01189: The listener could not authenticate the user
Local OS Authentication can be disabled by setting the LOCAL_OS_AUTHENTICATION_ parameter in listener.ora file as such –
LOCAL_OS_AUTHENTICATION_ = OFF
When Local OS Authentication is disabled, the Listener behaves exactly as in Oracle 8i/9i. Thus, it should have a password set and ADMIN_RESTRICTIONS set to On.
LOGGING
By default, logging is not enabled (LOG_STATUS=OFF). When logging is enabled, the default directory is $ORACLE_HOME/network/admin and the log file default is.log. The logfile contains a history of listener commands issued both locally and remotely. The logfile shows a timestamp, command issued, and result code. If an Oracle error is returned, it will include the error message. The logfile does not contain passwords or other significant information. The logfile does NOT show any information related to IP address, client name, or other identifying information for remote connections. It may show the client’s current user name, but this can easily be spoofed or not provided.
The following are TNS errors that may signify an attack or inappropriate activity
TNS-01169:An attempt was made to issue a command, but a password is set
TNS-01189:Oracle 10g – Local OS Authentication is enabled and attempt was made to manage the Listener remotely or locally by another user
TNS-01190:Oracle 10g – Local OS Authentication is enabled and attempt was made to manage the Listener locally by another user
TNS-12508:This error occurs when an invalid command is issue (e.g., statusx instead of status) or when a set command is issued and ADMIN_RESTRICTIONS is set to no.
The relevant files for the Listener are as follows
$ORACLE_HOME/bin/lsnrctl Listener control program
$ORACLE_HOME/network/admin/listener.ora Configuration file for the Listener
$ORACLE_HOME/network/admin/sqlnet.ora Configuration file for the Listener
$ORACLE_HOME/bin/tnslnsr Server Listener process
Listener Modes :The Listener can be configured in one of three modes (as configured in listener.ora) –
· Database Provides network access to an Oracle database instance
· PLSExtProc Method for PL/SQL packages to access operating system executables
· Executable Provides network access to operating system executables
LISTENER REMOTE MANAGEMENT
DBAs are not aware that the Listener in Oracle 8i/9i can be remotely managed using lsnrctl or a similar program from a remote machine. The Oracle 10g Listener by default cannot be remotely managed unless local OS authentication is disabled.
1-The simplest method to remotely issue commands to a Listener is to use lsnrctl with command-line parameters as such
– lsnrctl
– lsnrctl status 192.168.1.100
– lsnrctl stop 192.168.1.100:1522
2- To set up a computer to remotely administer a Listener
- Configure the local listener.ora to resolve to the remote Listener
- Start from the command line lsnrctl and specify the Listener name
lsnrctl
LSNRCTL> set current_listener
The following are some examples of possible attacks against an Oracle 8i/9i Listener which has a default configuration and is not properly secured.
Execute SQL as DBA: It is possible to overwrite the ORACLE_HOME/sqlplus/admin/glogin.sql by changing the location of the log file and then sending SQL statements in Listener commands to the file. When the SQL*Plus is executed locally on the server (usually by a DBA), then the SQL statements are executed during the SQL*Plus startup.
Allow Login via rlogin:The Listener log can be used to overwrite an .rlogin file with additional host information, thus allowing an attacker access the server using rlogin.
Denial of Service (DoS) An attacker is able to –
· Stop the Listener
· Set a Listener password so that the Listener ca not be started without a password, although the DBA simply has to edit the listener.ora file and remove the password line
Denial of Service (DoS):Undermine the stability of the server and database by overwriting arbitrary files by changing the directory and filename of the log and trace files to any location accessible by the operating system account that owns the database (usually "oracle").
Denial of Service (DoS) Setting the Listener trace level to "support" may cause performance degradation on a heavily accessed database server.
Information Disclosure:Obtain detailed information on the Listener configuration and database installation such as –
· Database Service Names (e.g., SIDs)
· Database and Listener versions
· Log and trace settings including directory and file names
· Security settings
· Database server operating system
· Oracle environmental variables (ORACLE_HOME, etc.)
LISTENER EXPLOITS
1- If a password is not set on the Listener, someone who knows just a hostname and port number (default port is 1521) has full control over the Listener
2- Oracle Security Alerts:
Check the Oracle security alerts
3- Brute Forcing Listener Password:
The Listener password can easily be brute forced, since there is no automatic lockout facility and no requirements for strong passwords. Repetitive set password commands can be sent to the listener using a hacking program. If logging is enabled (set log_status on), invalid password attempts will appear with an error code of TNS-01169.
4- Passwords Transmitted in Clear Text:
Using the set password command remotely will transmit the password across the network in clear text with every command. If encryption is setup for the listener using the Advanced Security Option (ASO), then the passwords will be sent encrypted across the network. The change password command does encrypt the password when the lsnrctl program is used
ORACLE LISTENER PASSWORD
The password for the Listener is stored in the listener.ora file. If the PASSWORDS_
Prior to Oracle 10g, the encrypted password string could be substituted for the actual password when issuing the set password command. This is useful in executing scripts to stop the Listener. If a password is set for the Oracle 10g Listener, scripts must use the actual password rather than the encrypted string.
If the Listener password is set to "mypassword", then the listener.ora file will have the encrypted string. The following lsnrctl commands using either the plain-text password or encrypted string will both work prior to Oracle 10g.
Listener.ora
PASSWORDS_LISTENER = F4BAA4A006C26134
LSNRCTL> set password
Password: mypassword
LSNRCTL> set password Password: F4BAA4A006C26134
ORACLE 10G LOCAL OS AUTHENTICATION
A major change to Listener security in Oracle 10g (10.1 and 10.2) was the introduction of Local OS Authentication. By default, the Listener cannot be remotely managed and can only be managed locally by the owner of the tnslsnr process (usually oracle).
If another operating system user attempts to manage the Listener, the following message will be displayed in the Listener log file –
TNS-01190: The user is not authorized to execute the requested listener command
If someone attempts to managed the Listener remotely, the following message will be displayed in the Listener log file –
TNS-01189: The listener could not authenticate the user
Local OS Authentication can be disabled by setting the LOCAL_OS_AUTHENTICATION_
LOCAL_OS_AUTHENTICATION_
When Local OS Authentication is disabled, the Listener behaves exactly as in Oracle 8i/9i. Thus, it should have a password set and ADMIN_RESTRICTIONS set to On.
LOGGING
By default, logging is not enabled (LOG_STATUS=OFF). When logging is enabled, the default directory is $ORACLE_HOME/network/admin and the log file default is
The following are TNS errors that may signify an attack or inappropriate activity
TNS-01169:An attempt was made to issue a command, but a password is set
TNS-01189:Oracle 10g – Local OS Authentication is enabled and attempt was made to manage the Listener remotely or locally by another user
TNS-01190:Oracle 10g – Local OS Authentication is enabled and attempt was made to manage the Listener locally by another user
TNS-12508:This error occurs when an invalid command is issue (e.g., statusx instead of status) or when a set command is issued and ADMIN_RESTRICTIONS is set to no.
Subscribe to:
Posts (Atom)
