Thursday, February 28, 2008

ORA-4030 on 10gR1 RAC instance at Windows 2003 Server Part1

This error indicates that the oracle server process is unable to allocate more memory from the operating system.This memory consists of the PGA (Program Global Area) and its contents depend upon the server configuration.For dedicated server processes it contains the stack and the UGA (User Global Area) which holds user session data, cursor information and the sort area. In a multithreaded configuration (shared server), the UGA is allocated in the SGA (System Global Area) and will not be responsible for ORA-4030 errors.

Diagnostic queries

1) TOTAL ORACLE MEMORY ALLOCATED

select sum(bytes)/1024/1024 Mb from
(select bytes from v$sgastat
union
select value bytes from
v$sesstat s,
v$statname n
where
n.STATISTIC# = s.STATISTIC# and
n.name = 'session pga memory'
);

2) PGA MEMORY
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- -------------
pga_aggregate_target big integer 150M

PGA Memory allocated

select
sum(value)/1024/1024 Mb
from
v$sesstat s, v$statname n
where
n.STATISTIC# = s.STATISTIC# and
name = 'session pga memory';

3) Which process requesting too much memory
select
sid,name,value
from
v$statname n,v$sesstat s
where
n.STATISTIC# = s.STATISTIC# and
name like 'session%memory%'
order by 3 asc;

If all of your queries are in memory parameters limits then in windows try the following :

1) Find if any job running wants more memory. if this job is necessary to run , move it in an other time or proceed to the next steps.

2) Increase PGA

3) Increase SGA

ATTENTION : SGA + PGA must me less than 2Gb on 32bit Windows System.

If you by pass this system you must set windows to allow 3Gb.

Here is the boot.ini on the system drive

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
[operating systems] multi(0)disk(0)rdisk(0)partition(2)\WINNT="Windows NT Server Version 4.00" /3GB
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Windows NT Server Version 4.00 [VGA mode]" /basevideo /sos

Note the /3GB setting added to the 4th line above

Friday, February 22, 2008

ORA-00257: archiver error. Connect internal only, until freed

In order to start archiver after having no space do the following :

Remove all archives;

SQL> alter system archive log stop;

System altered.

SQL> alter system archive log start;

System altered.

SQL> alter system switch logfile;

System altered.


then resync rman catalog

RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;

AND TAKE FULL BACKUP !!!!!!

Thursday, February 14, 2008

Ranges of Dates

In order to fill the range between 2 dates use the following query

select to_date('01/04/2008','DD/MM/YYYY')+rownum -1 dt
from user_tables
where rownum <= (to_date('01/05/2008','DD/MM/YYYY') - to_date('01/04/2008','DD/MM/YYYY'));

Fills dates from 01/04/2008 to 30/04/2008 !!!!

Wednesday, February 13, 2008

ORA-600 [4400] on 10.2.0.3

ORA-600 [4400] [a] [b] [c] [d] [e]

DESCRIPTION:

Internal error 4400 means that we are trying to delete a transaction (for
example at logoff time) but the transaction has not yet been marked
completed.

This can happen at the remote site in a distributed transaction if the
first part of the first stage of a two phase commit gets an error before
it really starts the protocol.


FUNCTIONALITY:
TRANSACTION CONTROL

IMPACT:
PROCESS FAILURE - but only at logoff so minimal impact
NON CORRUPTIVE - No underlying data corruption.

ORA-600 [18095] on 10.2.0.3

ORA-600 [18095]

If the local foreground is killed while in a distributed autonomous
transaction ORA-600[18095] is likely to occur.

Workaround:
Do not kill the foreground
Ignore this internal error (an involved session was killed anyway)

FIXED in 10.2.0.4

Search alert.log for errors (UNIX)

sed -n '/Mon Feb 11.*2008/,/Thu Feb 14.*2008/p' alert_ispb.log | grep ORA

This command searches alert_ispb.log from 11/02/2008 to the end of 13/02/2008 and reports Oracle errors.

Friday, February 8, 2008

ORA-14402

ORA-14402: updating partition key column would cause a partition change

In order to avoid this error you should do :

alter table [TABLE_NAME] enable row movement;

Versions 8i and up.

Wednesday, February 6, 2008

Gather Table Statistics (8i,9i,10g,11g)

From 10G and up this is far more better: begin dbms_stats.gather_schema_stats( ownname=>; 'MYSCHEMA' , cascade=>; TRUE, estimate_percent=>; 5, block_sample=>; TRUE, degree=>; null, no_invalidate=>; DBMS_STATS.AUTO_INVALIDATE, granularity=>; 'ALL', method_opt=>; 'FOR ALL INDEXED COLUMNS SIZE SKEWONLY', options=>; 'GATHER AUTO'); end; / For 8i and 9i Use begin dbms_stats.gather_table_stats(ownname=>;'OWNER',tabname=>;'TABLE_NAME' ,cascade=>;true,estimate_percent=>;5); end; /

Friday, February 1, 2008

Adding an MVIEW on a Refresh Group

BEGIN
DBMS_REFRESH.ADD(
name => '[OWNER].[REFRESH_GROUP]',
list => '[OWNER].[MVIEW]',
lax => TRUE);
commit;
END;
/