Friday, December 19, 2008

Oracle v$ views info

A nice link that have info about the oracle v$ views. Use it as a quick refference.

http://www.adp-gmbh.ch/ora/misc/dynamic_performance_views.html

Rman full backup examples

Full database backup plus archivelog backup (9i,10g)

rman>
backup as compressed backupset format '/[mount_point]/[backup_name]__%Y-%M-%D-%t_s%s_p%p' database plus archivelog not backed up delete all input;

Fast incremental startegy by having the last backup full recovered every day (10g and up)

rman>
backup incremental level 1 cumulative for recover of copy with tag 'FULL_DB'device type disk filesperset = 10 tag 'INC_DB' database;
recover copy of database with tag 'FULL_DB';
backup filesperset = 25 tag 'ARCH_BACKUP' archivelog all not backed up delete all input;

Wednesday, December 17, 2008

Execute immediate and commit

Some tips for the execute immediate pl/sql command and when it dows commit.

When you use execute immediate for dml statements then you must explicity commit.

But when you use execute immediate for ddl statements then all previous dnml statements in a transaction are explicity commited.

Friday, December 12, 2008

Number Conversion Routines (By Thomas Kyte)

This set of routines is useful to convert between various 'bases' in Oracle. Once you install these functions, you will be able to perform operations such as

select to_bin( 123 ) bin, to_hex( 123 ) hex, to_oct( 123 ) oct from dual;

Just follow the links:

for examples -> http://asktom.oracle.com/tkyte/hexdec/index.html

for the code -> http://asktom.oracle.com/tkyte/hexdec/hexdec.sql

Using the above functions you can do right shifting
e.g

select lpad( to_bin( 3123 ), 15, '0' ) bin1,
lpad( to_bin( trunc(3123/power(2,(rownum-1))) ), 15, '0' ) bin2,
rownum-1 shift
from all_users
where rownum <= 10;



Thursday, December 4, 2008

AIX 5L Recommended VMM Settings for Oracle

Oracle database servers on IBM Power@AIX can have a really performance boost when VMM tuned upon the default IBM Settings.

AIX 5.2 VMM Parameters Tunning

vmtune -c8 -s1 -r32 -R64 -p5 -P20 -t20 -f128 -F144 -W32
setting maxfree to 144
Setting minfree to 128
Setting minperm% to 5
Setting maxperm% to 20
Setting maxclient% to 20
Setting minpgahead to 32
Setting maxpgahead to 64
Setting maxrandwrt to 32
Setting numclust to 8
Setting sync_release_ilock to 1
async io servers : maxservers = 10 * CPU, minservers=maxservers/2

The above parameters are a good starting point. You can tune a little bit by monitoring performance.
e.g
If your database is an OLTP system , using lots of db_sequential_reads (index scans) you can put minpgahead to 512 and maxpgahead to 1024 on JFS and JFS2 volume groups.

For async io you can use the disks rule of thumb: maxservers=10*[total_disks_for_oracle_files],minservers=maxservers/2

On AIX 5.3 there is a slight diference in the way the VMM works so you can use this settings:
vmo -r -o lgpg_size=16777216 lgpg_regions=128
vmo -p -o minperm%=5
vmo -p -o maxperm%=90
vmo -p -o maxclient%=90
vmo -p -o lru_file_repage=0
vmo -p -o minfree=960
vmo -p -o maxfree=1088

For async io as for AIX 5.2.

Oracle Parameters for 9iR2(9.2.0.3) and up :
filesystem_options=SETALL Enables both ASYNC and DIRECT I/O or CONCURRENT I/O(JFS2)
It is advisable not to use direct I/O because degrades performance. If you have only JFS filesystems then use filesystem_options=ASYNC which is the default setting.

NOTE
There is no "built-in" support for CIO or DIO in Oracle Database 9iR2 (9.2.0) or lower, though you can force the use of CIO (JFS2) or DIO (JFS) with filesystem mount options.

Wednesday, November 26, 2008

Using DataPump Export/Import examples

I will present some expamples using DataPump.

1) Full export
expdp system directory=export_dir dumpfile=mydump.dmp logfile=mydump.log ESTIMATE=statistics full=y

- Excluding a schema or a list of schemas

expdp system directory=export_dir dumpfile=mydump.dmp logfile=mydump.log ESTIMATE=statistics full=y EXCLUDE=SCHEMA:"in('MYSCHEMA')"


expdp system directory=export_dir dumpfile=mydump.dmp logfile=mydump.log ESTIMATE=statistics full=y EXCLUDE=SCHEMA:"in('MYSCHEMA','MYOTHERSCHEMA')"


expdp system include=SCHEMA:\"like 'TST%'\" directory=tmp dumpfile=tstexpuser.dmp full=y content=metadata_only


2) Exporting Schema to a dump file

expdp system DIRECTORY=EXPORT_DIR DUMPFILE=mydump.dmp ESTIMATE=statistics SCHEMAS=MYSCHEMA

3) Exporting Metadata Only

- Full Database
expdp system DIRECTORY=EXPORT_DIR DUMPFILE=mydump.dmp content=metadata_only full=y

-- Schema's

expdp system DIRECTORY=EXPORT_DIR DUMPFILE=mydump.dmp content=metadata_only schemas=myschema

-- Excluding DBA Scheduler JOBS

expdp system directory=exp_dir content=metadata_only "exclude=procobj:\"in(SELECT NAME FROM sys.OBJ$ WHERE TYPE# IN (47,48,66,67,68,69,71,72,74))\"" schemas=STAGING,STARSAMA,STARFIN dumpfile=dwh_schemas_metadata.dmp logifle=dwh_schemas_metadata.log


4) Importing and remaping schema using dump file
impdp system DIRECTORY=export_dir SCHEMAS=MYSCHEMA REMAP_SCHEMA=MYSCHEMA:OTHERSCHEMA DUMPFILE=mydump.dmp

5) Importing schemas directly from an other database using dblink

a) impdp system DIRECTORY=export_dir SCHEMAS=MYSCHEMA,MYSCHEMA1 NETWORK_LINK=MYDBLINK

b) impdp system DIRECTORY=MYDIR SCHEMAS=MYSCHEMA NETWORK_LINK=MYLINK REMAP_SCHEMA=MYSCHEMA:MYSCHEMA_NEW CONTENT=METADATA_ONLY remap_tablespace=MYTBS1:MYNEWTBS,MYTBS2:MYNEWTBS

6) Export Schemas and import with remaping tablespace (Some more parameters are used)

a) Take the export

expdp system directory=EXPORT_DIR dumpfile=mydump.dmp logfile=mydump.log estimate=statistics parallel=2 job_name=mydump_exp schemas=MYSCHEMA1,MYSCHEMA2

b) Import and remaping default tablespace and index tablespaces

impdp system directory=export_dir schemas=MYSCHEMA1,MYSCHEMA2 dumpfile=mydump.dmp logfile=mydump.log job_name=mydump_imp parallel=2 remap_tablespace=myschema1_data:users,myschema1_index:users,myschema2_data:users,myschema2_index:users

7) Export table from a schema and import to an other schema

expdp system directory=export_dir dumpfile=mytable.dmp logfile=mytable.log ESTIMATE=statistics tables=myschema.mytable

impdp system directory=export_dir tables=myschema.mytable remap_schema=myschema:mynewschema dumpfile=mytable.dmp

8) Import Transportable Tablespace with Remapping Schema also objects stats are excluded during the import

impdp system dumpfile=mydump.dmp DIRECTORY=my_dir remap_schema=ORIG_SCHEMA:NEW_SCHEMA EXCLUDE=TABLE_STATISTICS EXCLUDE=INDEX_STATISTICS TRANSPORT_DATAFILES='/u02/oradata/myfile.dbf'

Unsecuring dbconsole(10gR2)

After applying 10gR2 Patch Set 3 (10.2.0.4) the dbconsole is always configured as secured. If you have problem with https then you must unsecure the dbconsole.

emctl unsecure dbconsole

Warning : Unsecuring the dbconsole does not change the prot number to the unsecure default (5500). It remains as the https port (1158).

E.g

https://hostname:1158/em/console -> http://hostname:1158/em/console

10gR2 EMCA FAILS WITH ERROR ORA-06502

There is a bug during the create of the EM Repository.
When hostname exceeds 32 characters emca will fail with
ORA-06502
PL/SQL: numeric or value error: character string buffer too small

Verify if this is the case.

sqlplus / as sysdba
SQL> set line 200
SQL> select host_name,length(host_name),instance_name from v$instance where rownum=1;
SQL>exit;

If length hostname is more than 32 chars you hitting this bug.

In order to bypass this bug apply the following steps:

1) Drop the repository with emca
emca -deconfig dbcontrol db -repos drop

2) cd to $ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/self_monitor/

3) Make a copy of self_monitor_post_creation.sql
cp self_monitor_post_creation.sql self_monitor_post_creation.sql.bak

4) Edit the script and change the l_host_name from varchar2(32) to varchar2(128).
Note: The l_host_name variable is declared 2 times in the script.

5) Create repository with emca
emca -config dbcontrol db -repos create

And you are done.

Oracle 10gR2 installation on RHEL5

Oracle 10gR2 installation on RHEL5.X

1) System Memory
# grep MemTotal /proc/meminfo -- At least 512Mb

2) Swap Space
# grep SwapTotal /proc/meminfo
Up to 512 MB 2 times the size of RAM
Between 1024 MB and 2048 MB 1.5 times the size of RAM
Between 2049 MB and 8192 MB Equal to the size of RAM
More than 8192 MB 0.75 times the size of RAM

3) Disk space Requirement
# df -h
400 MB of disk space in the /tmp directory
Between 1.5 GB and 3.5 GB of disk space for the Oracle software

4) Kernel Version
# uname -r
The system must be running 2.6.18-8 kernel version or a later.

5) Required Packages

i) gcc-c++-4.1.1-52.el5.x86_64.rpm and all its dependent packages.
The dependent packages are:
libstdc++-devel-4.1.1-52.el5.x86_64.rpm
glibc-headers-2.5-12.x86_64.rpm
glibc-devel-2.5-12.x86_64.rpm
libgomp-4.1.1-52.el5.x86_64.rpm
gcc-4.1.1-52.el5.x86_64.rpm
ii) glibc-devel-2.5-12.i386.rpm
iii) compat-libstdc++-33-3.2.3-61.x86_64.rpm
iv) compat-libstdc++-33-3.2.3-61.i386.rpm
v) compat-libstdc++-296-2.96-138.i386.rpm
vi) libXp-1.0.0-8.i386.rpm
vii) sysstat-7.0.0-3.el5.x86_64.rpm
NOTE: i386 packages might require the --force option during installation if the 64-bit version of the same package is already installed. For example, "rpm -ivh --force glibc-devel-2.5-12.i386.rpm" may be required.

6) Set the kernel parameters

Add the following the lines in the file /etc/sysctl.conf
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
net.ipv4.tcp_wmem = 262144 262144 262144
net.ipv4.tcp_rmem = 262144 262144 262144

To place these changes into effect, execute the command
# sysctl -p

7) Create Oracle user and groups
# groupadd oinstall
# groupadd dba
# useradd -g oinstall -G dba oracle
# passwd oracle

8) Create the required directories for Oracle database and change the ownership

# mkdir -p /u01/app/oracle -- ORACLE_BASE
# chown -R oracle:oinstall /u01/app/oracle

9) Set the session limits for Oracle user

Add the following lines to the /etc/security/limits.conf file
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

Add the following line in the /etc/pam.d/login file
session required pam_limits.so

Add the following lines to the /etc/profile.
-- This assumes that oracle user is using bash shell which is given by default.
-- Change this accordingly if the oracle user has a different shell
if [ $USER = "oracle" ]; then
ulimit -u 16384
ulimit -n 65536
fi

10) Configure the Oracle user's environment

For example, if oracle user is using bash shell, and has the home directory as /home/oracle,
The following lines to be added to /home/oracle/.bash_profile
export ORACLE_BASE=/u01/app/oracle

11) Invoking OUI to install database software
-- Install Software Only
Login as Oracle User
$ ./runInstaller -ignoreSysPrereqs

12) Invoking OUI to install companion products on the same ORACLE_HOME
$ ./runInstaller -ignoreSysPrereqs

13) Invoking OUI to install Latest Patch Set on the ORACLE_HOME
$ ./runInstaller -ignoreSysPrereqs

14) Set ORACLE_HOME and ORACLE_SID on _bash_profile

15) Run dbca to proceed with database creation


Tuesday, November 11, 2008

Creating numbers range

Ordered range of numbers

select rownum r from dual connect by level <=max_number order by 1

Randomly ordered range of numbers

select rownum r from dual connect by level <=max_number order by dbms_random.value;

Friday, October 24, 2008

Resync Standby Using Automatic Gap Sequence fix

Shutdown standby and startup mount.
Do :

SELECT * FROM V$ARCHIVE_GAP;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 4115 4123

On primary if archives are not in the default path then use rman to restore them:
restore archivelog from sequence 4115 until sequence 4123;

Then on standby
alter database recover managed standby database disconnect;
To start the gap sequence fix and to apply all archives.

Wednesday, October 22, 2008

TNS-12516, ORA-12516,ORA-12519, TNS-12519 , ORA-12520

TNS-12516 TNS:listener could not find instance with matching protocol stack
ORA-12516, TNS:listener could not find available handler with matching protocol stack
TNS-12519 TNS: no appropriate service handler found

ORA-12519 TNS: no appropriate service handler found
ORA-12520 TNS:listener could not find available handler for requested type of server

If you see any of these errors you must increase the processes parameter.

Explanation

PMON is responsible for updating the listener with information about a particular instance such as load and dispatcher information. Maximum load for dedicated connections is determined by the PROCESSES parameter. The frequency at which PMON provides SERVICE_UPDATE is maximum at 10 minutes.

Since SERVICE_UPDATE can take as long as 10 minutes, there can be a difference between the current instance load according to the listener and the actual instance load.

When the listener believes the current number of connections has reached maximum
load, it may set the state of the service handler for an instance to "blocked"
and begin refusing incoming client connections with either of the following
errors:

TNS-12516 TNS:listener could not find instance with matching protocol stack
TNS-12519 TNS:no appropriate service handler found

Additionally, an ORA-12520 error may appear in the listener log.

The output of the LSNRCTL services command will likely show that the service handler is "blocked".

Tuesday, October 14, 2008

Index block splits

A nice article about index block split

http://www.confio.com/English/Tips/Index_Block_Split.php

Manually recreate dbconsole 10gR2

First remove repository and files:

Single Instance : $ORACLE_HOME/bin/emca -deconfig dbcontrol db -repos drop

RAC Database : $ORACLE_HOME/bin/emca -deconfig dbcontrol db -repos drop -cluster

Then create again :

Single Instance : $ORACLE_HOME/bin/emca -config dbcontrol db -repos create
RAC Database : $ORACLE_HOME/bin/emca -config dbcontrol db -repos create -cluster

Oracle 10g - Manually Create a Physical Standby Database Using Data Guard

A very nice link on OraFaq.

http://www.orafaq.com/node/2030

Friday, October 10, 2008

Hi to all again

I have months to update my blog. I had thoughts to change it from only Oracle blog to Systems & Oracle blog. 

Now plans have changed again and i will post Oracle issues again and more frequently i believe.

Friday, March 14, 2008

Resize undo tablespace 10gR2 RAC

On RAC configuration each instance has assigned its own UNDO tablespace. So in order to resize the UNDO you must create a new one for each instance and assign it to the instance then you cane drop the old ones.

For each instance :

-- Create new undo tablespace with smaller size.
SQL> create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf' size 100m;

-- Set new tablespace as undo_tablespace
SQL> alter system set undo_tablespace=undo_rbs1;

-- Drop the old tablespace.
SQL> drop tablespace undo_rbs0 including contents.


NOTE: Dropping the old tablespace may give ORA-30013 : undo tablespace '%s' is currently in use. This error indicates you must wait for the undo tablespace to become unavailable. In other words, you must wait for existing transaction to commit or rollback.

Recreate temp tablespace 10g

SQL> create temporary tablespace temp1 tempfile '/DATA/oradata/rdtwh/temp1.dbf' size 100M;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

SQL> CREATE SMALLFILE TEMPORARY TABLESPACE TEMP
2 TEMPFILE '/DATA/oradata/rdtwh/temp01.dbf' SIZE 1000M
3 AUTOEXTEND ON NEXT 150M MAXSIZE 32767M
4 ,'/DATA/oradata/rdtwh/temp02.dbf' SIZE 1000M
5 AUTOEXTEND ON NEXT 150M MAXSIZE 32767M
6 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> drop tablespace temp1 including contents and datafiles;

Tablespace dropped.

SQL>

Tables with chain rows

Tables with chain rows are usually potentially performance bottlenecks , as oracle when needs to fetch rows from these tables my scan more than one block to get one row.
These tables are tables with rows that are highly updated changing row length. The above query finds and rebuild tables with chained rows and corresponding indexes when the chained rows count are above 10% of the table's total rows. It also gathers statistics after the rebuild.

select
  'alter table '||owner||'.'||table_name||' move pctfree 20;'
from
  dba_tables
where
  chain_cnt > 0
and owner = 'MYUSER'
and table_name='TEST'
and round((chain_cnt/num_rows)*100) >=10
union all
select
  'alter index '||owner||'.'||index_name||' rebuild'||
  case when index_type='NORMAL'
    then ' online;'
    else ';'
  end
from
  dba_indexes
where (table_owner,table_name) in
  (
    select
      owner,
      table_name
    from
      dba_tables
    where
      chain_cnt > 0
    and owner = 'MYUSER'
    and table_name='TEST'
    and round((chain_cnt/num_rows)*100) >=10
  )
union all
select
  'exec dbms_stats.gather_table_stats(ownname=>'''||owner||''',tabname=>'''||
  table_name||''',cascade=>true,estimate_percent=>5);'
from
  dba_tables
where
  chain_cnt > 0
and owner = 'MYUSER'
and table_name='TEST'
and round((chain_cnt/num_rows)*100) >=10 ;

Thursday, March 6, 2008

Remote SQL Statements Tuning

If you have a remote sql statement and the execution elapsed time is not the expected, the plan on the remote site is not the desirable. Especially if you using hints to tune your query on the remote site , this may not work on the actual execution because of Oracle behavior.
E.g

If the entire SQL statement is sent to the remote database, the optimizer uses table aliases A1, A2, and so on, for all tables and columns in the query, in order to avoid possible naming conflicts. For example:

On remote site you tune the query as follows ans runs ok:

SELECT /*+ index(a pk1) index(b pk2) */
DNAME, ENAME
FROM DEPT a, EMP b
WHERE DEPT.DEPTNO = EMP.DEPTNO;

And on your site you do

insert into table mytable
SELECT /*+ index(a pk1) index(b pk2) */
DNAME, ENAME
FROM DEPT@REMOTE a, EMP@REMOTE b
WHERE DEPT.DEPTNO = EMP.DEPTNO;

The query is sent to the remote database as:

   SELECT /*+ index(a pk1) index(b pk2) */
A2.DNAME, A1.ENAME
FROM DEPT A2, EMP A1
WHERE A1.DEPTNO = A2.DEPTNO;

So the hint is not working.

Best way is to create a view on remote site with the tuned query
and then use the view in the local insert.

REMOTE SITE :
create or replace myview as
SELECT /*+ index(a pk1) index(b pk2) */
DNAME, ENAME
FROM DEPT a, EMP b
WHERE DEPT.DEPTNO = EMP.DEPTNO;

LOCAL SITE :

insert into table mytable select * from myview@remote;





Wednesday, March 5, 2008

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

The ORA-4030 error indicates that an Oracle process could not allocate the amount of memory
required for an operation.
On Windows 32-bit servers the total address space for a single process is 2 GB, regardless of the amount of available physical memor
y.
The 32-bit architecture limits the addressing range to 4 GB, and as 2 of these GBs are reserved of the kernel, a user process cannot be more than 2 GB.

When a client connects to the database a shadow process is created.
This is where the PGA memory belongs to. On Windows systems, this is not created as a new proces
s, but as a thread under the ORACLE.EXE executable. This means that the Oracle d
atabase and all its connections are treated as one single process (ORACLE.EXE) w
ith a large number of threads. In other words, the total address space for the d
atabase and all connections to it are restricted to 2 GB. In real-life the limit
is at appr. 1.7 GB.

As you know SGA + max alloc. PGA + oracle mem structures = total size of the ORACLE.EXE process.

We need to get more memory for max alloc. PGA.

1. If your server has got up to 3GB RAM then you should decrease the value of SGA components (shared_pool, db_cache);
2. If your server has got +/- 4Gb RAM then it's important to use the /3Gb switch in the boot.ini.

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;
/

Thursday, January 31, 2008

Altering a JOB

In oracle 9i in order to alter a job you must enter all the parameters of the job even they remain the same.

e.g

BEGIN
SYS.DBMS_JOB.CHANGE
(
job => [job_number]
,what => 'begin myproc; end;'
,next_date => to_date('31/01/2008 10:40:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'sysdate+1/24'
);
commit;
END;
/

Here i changed only the "what" parameter but i included in my script the other 2.

Tuesday, January 22, 2008

Update with join and ORA-01779

A very fast update when we want to update 2 tables T1 ,T2 having a join on a column and unique values on the join.(One row form T1,matches only with one row from T2)

update (
select a.ordering a1,b.ind a2
from T1 a,T2 b
where a.cmp_id=139
and a.cmp_key=b.k)
set a1=a2;

For the given cmp_id any cmp_key is unique on table T1.
Also for any k on T2 there must be only one value for ind.
So we need a primary key on T2.k in order to work the query and avoid the ORA-01779 error.


Tuesday, January 15, 2008

Merging partitions

This week i have to merge a huge number of tables partition into one yearly partition per table.

Steps :

1) Create new tablespace for yearly partition.

2) Merge all tables partitions

3) Rebuild unusable local indexes

4) Gather new partition statistics

Tuesday, January 8, 2008

unix timestamp to date

select to_date('01/01/1970', 'dd/mm/yyyy HH24:MI:SS')+ (1197889557.848 / ( 60*60*24)) from dual;

Returns gmt time

HAPPY NEW YEAR

I just got back from my holidays.

Happy new year to all