Wednesday, August 20, 2008
How do you move a partitionned index from one TS to another
alter index INDEXNAME rebuild partition PARTITIONNAME tablespace TABLESPACENAME;
Wednesday, January 9, 2008
Check ORA-04031
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');
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');
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.
Subscribe to:
Posts (Atom)
