Oracle Day to Day blog

Data warehouse Sizing and Haddop Capacity Planning

2012-02-10T15:06:00.001+02:00

A very nice article called: Sizing for data volume or performance or both?

Hadoop Capacity Planning from Cloudera: Hadoop/HBase Capacity Planning

Oracle Scheduler does not works as expected

2012-02-08T11:08:00.001+02:00

It is possible that the Oracle Scheduler to hang if you change a windows attribute and make a mistake or after upgrades from 10.2.0.3 - > 10.2.0.5.

Usually you will see that shceduled jobs that no executed correct and in the cjq trace files you will find the follwoing:

jsksGetCurWindowId:1 got error 27468
jsksGetCurWindowId:1 got error 27468

So if you fall into this please execute the follwoing as sysdba (Assuming default scheduler windows):

variable v number;

begin
dbms_scheduler.disable('WEEKEND_WINDOW');
dbms_scheduler.disable('WEEKNIGHT_WINDOW');
dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');
commit;
end;
/

select value from v$parameter where name='job_queue_processes';
--Please keep this number (assume J)

alter system set job_queue_processes=0;

begin
dbms_ijob.set_enabled(FALSE);
commit;
end;
/


-- 2 times is a must
alter system flush shared_pool;
alter system flush shared_pool;


declare
cwo number;
begin
select o.obj# into cwo from sys.obj$ o where
o.name = 'CURRENT_OPEN_WINDOW' and o.namespace = 51;


update sys.scheduler$_global_attribute set value = null, attr_tstamp = null,
attr_intv = null, additional_info = null where obj# = cwo;

end;
/

begin

dbms_ijob.set_enabled(TRUE);

commit;

end;

/

begin
 dbms_scheduler.enable('WEEKEND_WINDOW');
 dbms_scheduler.enable('WEEKNIGHT_WINDOW');
 dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');
 dbms_ijob.set_enabled(TRUE);

commit;

end;

/


--Set job_queue_processes to its original setting using J
alter system set job_queue_processes=J;

exit;

Please check the the windows group you have created (MAINTENACE_WINDOW_GROUP) is the default in order to see if your windows are members or need to be added again(Use EM).

References

Oracle Support Note ID: 731678.1

Give permissions to non oracle users to run sqlplus (10gR2)

2011-12-21T16:08:00.000+02:00

After 10.2.0.2 patch set a script that gives more "relaxing" permissions to users other than oracle and not belonging to dba or oinstall group was suspended.

If you want to give permissions for other users to run Oracle Utils run :
As oracle user:
$ORACLE_HOME/install/changePerm.sh

And you are done

Upgrade 10gR2 RAC to 11gR2 RAC on Linux

2011-11-22T12:14:00.000+02:00

Step By Step: Upgrade 10gR2 RAC to 11gR2 RAC on Oracle Enterprise Linux 5 (32bit)

http://www.linkedin.com/news?viewArticle=&articleID=893905840&gid=77941&type=member&item=79060380&articleURL=http%3A%2F%2Fwww%2Eoracledba%2Eorg%2Fupgrade%2FUpgrade_10gR2_RAC_to_11gR2_RAC%2Ehtm&urlhash=CulU&goback=%2Egde_77941_member_79060380

Many thanks to Bhavin Hingu for sharing this document.

DBConsole Login Error Message: Element Not Found

2011-11-15T11:04:00.000+02:00

In DBConsole After click the login button in Internet Explorer (IE) 8 the following error is returned :

Webpage error details

User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 1.1.4322; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022; OfficeLiveConnector.1.5; OfficeLivePatch.1.3; .NET4.0C; .NET4.0E; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; Zune 4.7)
Timestamp: Tue, 15 Nov 2011 07:20:09 UTC
Message: Element not found.

Line: 2815
Char: 1
Code: 0
URI: http://labdb01:1158/em/cabo/jsLibs/Common2_2_24_6.js

FIX :
1. Click Start, Run and type Regedit.exe
2. Backup the registry
3. Navigate to the following location:
HKEY_CLASSES_ROOT\TypeLib\{EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}\1.1\0\win32
4. Double-click (default) and set C:\WINDOWS\system32\ieframe.dll as its data
5. Quit Registry Editor.

References : Metalink Note ID : Note.1093475

Spliting a very large table into pieces using rowid nonverlapping ranges

2011-11-04T14:45:00.001+02:00

Below you will find a script that spliits a table into pieces in order to effectively process the data.
Just replace [OWNER] with the table owner , [TABLE_NAME] with your table and [CHUNK] with the number of pieces you want.

select grp,
dbms_rowid.rowid_create( 1, data_object_id, lo_fno, lo_block, 0 ) min_rid,
dbms_rowid.rowid_create( 1, data_object_id, hi_fno, hi_block, 10000 ) max_rid
from (
select distinct grp,
first_value(relative_fno)
over (partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) lo_fno,
first_value(block_id )
over (partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) lo_block,
last_value(relative_fno)
over (partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) hi_fno,
last_value(block_id+blocks-1)
over (partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) hi_block,
sum(blocks) over (partition by grp) sum_blocks
from (
select relative_fno,
block_id,
blocks,
trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /
(sum(blocks) over ()/[CHUNK]) ) grp
from dba_extents
where segment_name = upper('[TABLE_NAME]')
and owner = '[OWNER]'
order by block_id
)
),
(select data_object_id from dba_objects where owner='[OWNER]' and object_name = upper('[TABLE_NAME]') )
order by grp;

References
Tom Kyte's "Efective Oracle by Design"

Parallel transaction recovery and SMON Monitoring

2011-11-02T13:42:00.003+02:00

A very nice article to read when SMON is the actual blocker on other Database Sessions.

http://www.dba-oracle.com/t_parallel_transaction_recovery_undo_cpu.htm

Now in case the transaction rollback involves index block or when you querying the V$FAST_START_SERVERS you find only one server to be working then you may force disable the parallel rollback.

Follow these steps (If in RAC you must apply to all instances) :
1) Find SMON PID
select pid, program from v$process where program like '%SMON%';
2) on SQLPLUS as sysdba disable SMON transaction rollback/recovery

oradebug setorapid <SMON's Oracle PID>
oradebug event 10513 trace name context forever, level 2

3) Kill the PQ slaves that are doing parallel transaction recovery.

select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate;', 'kill -9 '||spid
from v$session s, (select * from v$process
where pid in (select pid from v$fast_start_servers)) p
where s.paddr=p.addr;

4) Disable parallel rollback
alter system set fast_start_parallel_rollback=false;
5) Enable SMON to do serial recovery again

oradebug setorapid <SMON's Oracle PID>
oradebug event 10513 trace name context off
oradebug wakeup 13
6) Use the follwoing query to verify serial recovery and estimate when it will finish

select
usn,
state,
undoblockstotal "Total",
undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete"
from v$fast_start_transactions;

REFERENCES :

Metalink Note Ids: 414242.1, 238507.1

Change ASM sys password (10gR2)

2011-10-18T16:16:00.000+02:00

In order to change an ASM instance sys password do the following :

1. Set the ORACLE_HOME and ORACLE_SID to the ASM instance
2. connect /as sysdba from sqlplus
3. If the value of the "remote_login_passwordfile" parameter in the pfile or spfile is EXCLUSIVE, you must shutdown your instance
4. RENAME or DELETE the existing password file PWD<SID>.ora( In Windows) / orapw<SID> ( in UNIX)
5. Issue the command:
WINDOWS:
orapwd file=<ORACLE_HOME>/database/PWD<SID>.ora password=<sys_password>

UNIX:
orapwd file=<ORACLE_HOME>/dbs/orapw<SID> password=<sys_password>

R.I.P Steve Jobs

2011-10-06T11:32:00.006+02:00

Even i never had an Apple Product , and sometimes i was against Apple principles on their products , i beleive that its speech on the Standford University gradution on 2005 its the most inspring speach i have ever heard and try to follwoing its priciples.

These are my favorites !!!
"you've got to find what you love

the only way to do great work is to love what you do

if you have'nt find it yet keep lookinf and don't settle
connect the dots looking backwords
death is the destination we all share
have the courage follow your heart and intuition they somehow allready know what you trully want to becomae, everything else is secondary
stay hungry, stay foolish"

R.I.P Steve Jobs

Oracle Database Security

2011-09-21T09:39:00.000+02:00

A very good collection of presentations on database security from Oracle University's instructor Pete Finnigan
http://www.petefinnigan.com/orasec.htm

Very useful.

Troubleshooting ORA-600 & ORA-7445 internal errors : An initial guide

2011-08-31T12:05:00.000+02:00

This is an excellent article written in Oracle Magazine issue for September/Oct 2011 by Tamzin Ascroft, a senior principal support engineer in Oracle Database Support, so check this on http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51support-453463.html

From Arup Nanda Blog : Resolving Gaps in Data Guard Apply Using Incremental RMAN Backup

2011-07-25T09:54:00.000+02:00

A must read blog post of a real life situation for resolving standby gaps !!!

http://arup.blogspot.com/2009/12/resolving-gaps-in-data-guard-apply.html

RMAN 10gR2 : Performing Incomplete Database Recovery (Full Disaster Scenario)

2011-07-20T09:57:00.000+02:00

Scenario :
You have lost your Disks and You have only the last Backup and you do not use a Recovery Catalog.

Solution :
Perfrom Incomplete recovery until the time the last backup fiished
and roll forward to the last availiable archive log in the backup area.

Tips:

1) Always register your DBID

rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Wed Jul 20 08:36:48 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: MYDB (DBID=3255826720)

2) Always keep in to a file the show all command of RMAN in order to know the the controlfile autobackup format.

$ rman target / msglog rman.out
RMAN> show all;
RMAN> exit
$ vi rman.out

3) If you use a Block Change Tracking File (BCF) , after you mount the DB disable the use.
RMAN> sql 'alter database disable block change tracking';

4) You may need to recreat your password file again (Specially in a RAC database if its in the DB Area)
For Signle Instance Database
orapwd file=orapwmydb password=mypass
For RAC Database
orapwd file=/common_location/orapwmydb password=mypass

Recovery (The test has been made on a RAC DB , but its ok for single instance too):

On one node :

$export ORACLE_HOME=/MY_ORA_HOME_DIR
$export ORACLE_SID=MYDB1
$rman target /
RMAN>startup force nomount;
RMAN>set dbid=3255826720;
RMAN>run {
set controlfile autobackup format for device type disk to '/orabackup/mydb/%F';
restore spfile from autobackup;
}
RMAN>startup force nomount;
RMAN>run {
set controlfile autobackup format for device type disk to '/orabackup/mydb/%F';
restore controlfile from autobackup;
alter database mount;
}
RMAN>restore database;
Starting restore at 20-JUL-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3

.......

Finished restore at 20-JUL-11

RMAN> recover database;

Starting recover at 20-JUL-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3

starting media recovery

.....

unable to find archive log
archive log thread=1 sequence=14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/20/2011 08:18:26
RMAN-06054: media recovery requesting unknown log: thread 1 seq 14 lowscn 4955096

# Since you do incomplete recovery ignore the error

RMAN> alter database open resetlogs;

database opened

RMAN>exit;

Recovery Manager complete.

Restart Your database , start any service, dbconsole/agent and take an immediate full backup.

Restoring Voting Disk and OCR files from backup 10gR2

2011-07-18T15:03:00.001+02:00

Use the info from the following post to identify or take a backup of the Voting Disk (CSS File) and OCR:

http://agstamy.blogspot.com/2009/05/backing-up-css-and-ocr-files.html

As oracle user from one node :
$export ORACLE_HOME=DB_HOME
$srvctl stop database -d db_name
$svrctl stop asm -n node_name
$srvctl stop nodeapps -n node_name

On each node:
$emctl stop dbconsole
$export ORACLE_HOME=ORA_CRS_HOME_DIR
$su
#crsctl stop crs

From one node :

For the voting disk oracle do:

Copy the backup to the voting diks locations and set the correct permissions
Use dd if your are in raw devices or simply cp if you are using cluster file system.

e.g

dd if=your_backup_file of=/dev/raw/css

cp -p my_css_backup_file /my_css_mount_point/my_css_backup_file

For the OCR File as root do :
If you are on cluster filesystem then you must create an empty file first with correct permissions
touch /ocr_location_mount/ocr_filename
chown root.oinstall /ocr_location_mount/ocr_filename

Then restore from last system backup or use your latest dumpfile
ocrconfig -restore backup00.ocr
backup00.ocr : Is the last auto backup on $ORA_CRS_HOME/cdata/[cluster_name] the default autobackup configuration location on local disks on all nodes

ocrconfig -import /your_ocr_last_dump_file

Start the crs :

crsctl start crs

Watch to see if all cluster servcies are up and functional !!!

Things to notice :
If you try this for full cluster restore then you must remember the directories ownerhips
For the css file the owner is oracle and for the ocr is root.

References :
Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide
10g Release 2 (10.2), Chapter 3 : Administering Oracle Clusterware Components.

Metalink Note IDs: 1092293.1, 953914.1

OCFS2 Guide Lines for 10gR2 CRS and RAC on Linux x86_64

2011-07-15T12:47:00.000+02:00

If you plan to install Oracle RAC on Linux using OCFS2 for CRS files and RAC DB files the follow the recommendations for the parameter mentioned , Recommendations are for Version 1.4.7

When Creating the OCFS2 Cluster

O2CB_HEARTBEAT_THRESHOLD

It should be set to the timeout value of the io layer. Most multipath solutions have a timeout ranging from 60 secs to 120 secs.
For 60 secs, set it to 31. For 120 secs, set it to 61 according to the follwoing formula : O2CB_HEARTBEAT_THRESHOLD = (((timeout in secs) / 2) + 1)
In general a value of 61 is suitable in most cases

Change the value in the vi /etc/sysconfig/o2cb on all nodes of the OCFS2 Cluster and restart the cluster on all nodes.

When formatting an OCFS2 filesystem.

Cluster Size:

The cluster size is the smallest unit of space allocated to a file to hold file data.
Permissible cluster sizes include; 4, 8, 16, 32, 64, 128, 256, 512, and 1024Kb.
Oracle recommends a cluster size of 128 KB or higher for datafiles and a cluster size of 32 or 64Kb for Oracle Home volumes or for OCRF and CSS Files.

ATTENTION : The cluster size cannot be modified after OCFS2 volume format.

Block Size:

The smallest unit of space addressable by the filesystem.
Permissible block sizes include; 512 bytes (not recommended), 1Kb, 2Kb, or 4Kb (recommended for most volumes).
4Kb Block Size works fine and Oracle Database Block should be equal or multiple to this 8K , 16K etc.

ATTENTION : The block size cannot be modified after OCFS2 volumes format.

When mounting an OCFS2 filesystem

datavolume:

Use this OCFS2 mount option to mount volumes storing Oracle datafiles, control files, redologs, archivelogs, Oracle clusterware files (voting disk and cluster registry), etc.
The datavolume mount option ensures the Oracle database processes open datafiles with the o_direct flag, required by Oracle Real Application Clusters (RAC) to concurrently access shared files.

noatime:

Users should add the "noatime" mount option to the mounts that hold the Oracle datafiles, redologs, archivelogs, voting file, etc. This is because OCFS2 1.4 supports atime (access time) which makes it update inodes even on a read. Like ls -l. As tracking access time is not useful for the database, users are advised to disable it for such mounts.

References :
OCFS2 1.4.7-1 Release Notes (Metalink Note ID 1086231.1)
OCFS2 1.2 - FREQUENTLY ASKED QUESTIONS
Meatalink Note ID : 727866.1

Oracle 10gR2 : Truncating a table causes "Enq: RO - FAST OBJECT REUSE" and DBW High CPU Usage

2011-07-14T12:38:00.000+02:00

On Oracle 10gR2 (10.2.0.4) a table truncate is taking lots of minutes and your session si waiting on "Enq: RO - FAST OBJECT REUSE" then you probably have hit bug8544896 introduced in 10.2.0.4 and it affects both Single instance and RAC environments, and a patch is available.

In order to confirm the bug as sys do :

select waiting_session,holding_session,lock_type,mode_held,mode_requested
from dba_waiters;

select sid, serial#,program from v$session where sid = 1095;

SID SERIAL# PROGRAM
---------- ---------- ------------------------------------------------
1095 1 oracle@castor (CKPT)

If you see that the CKPT Process is then that's the case.

Workaround
1) From an other session as sys issue :
alter system flush buffer_cache;
2) Set _db_fast_obj_truncate = FALSE to spfile/init.ora

References :
Metalink Note id : 888844.1
http://www.ora-solutions.net/web/2009/01/20/session-waiting-for-enq-ro-fast-object-reuse-dbwr-process-spinning-on-cpu/

Understading th SCN

2011-07-08T13:40:00.002+02:00

A very nice article for the Oracle SCN

http://www.dbapool.com/articles/1029200701.html

Install 10gR2 RAC (CRS,DATABASE) on CentOS Enterprise / RHEL / Oracle Enterprise Linux x86_64 5.x

2011-07-06T21:42:00.000+02:00

For the CRS and OCR files we will use OCFS2 and ASM for DATA and FRA

on node1

[root@node01 ~]# id nobody
uid=99(nobody) gid=99(nobody) groups=99(nobody)

[root@node02 ~]# groupadd -g 501 oinstall
[root@node02 ~]# groupadd -g 502 dba
[root@node02 ~]# useradd -u 501 -g oinstall -G dba oracle
[root@node02 ~]# passwd oracle

[root@node01 ~]# id oracle
uid=501(oracle) gid=501(oinstall) groups=501(oinstall),502(dba)

on node2

[root@node02 ~]# groupadd -g 501 oinstall
[root@node02 ~]# groupadd -g 502 dba
[root@node02 ~]# useradd -u 501 -g oinstall -G dba oracle
[root@node02 ~]# passwd oracle

You must use the same password also for convinience.

Check date on both nodes and guide you sysadmins to install ntp

Configure ssh equivelance

on both nodes
which scp if not in /usr/local/bin create link : cd /usr/local/bin ; ln -s /usr/bin/scp scp
which ssh if not in /usr/local/bin create link : cd /usr/local/bin ; ln -s /usr/bin/ssh ssh

On both nodes login as oracle
mkdir .ssh
chmod 700 .ssh
ssh-keygen -t rsa
ssh-keygen -t dsa

on node01 as oracle:
cd .ssh
ssh node01 cat /home/oracle/.ssh/id_rsa.pub >> authorized_keys
ssh node01 cat /home/oracle/.ssh/id_dsa.pub >> authorized_keys
ssh node02 cat /home/oracle/.ssh/id_rsa.pub >> authorized_keys
ssh node02 cat /home/oracle/.ssh/id_dsa.pub >> authorized_keys
scp authorized_keys node02:/home/oracle/.ssh/

on node02 as oracle:
cd .ssh
ssh node01 cat /home/oracle/.ssh/id_rsa.pub >> authorized_keys
ssh node01 cat /home/oracle/.ssh/id_dsa.pub >> authorized_keys
ssh node02 cat /home/oracle/.ssh/id_rsa.pub >> authorized_keys
ssh node02 cat /home/oracle/.ssh/id_dsa.pub >> authorized_keys
scp authorized_keys node01:/home/oracle/.ssh/

On each node
chmod 600 ~/.ssh/authorized_keys
exec /usr/bin/ssh-agent $SHELL
/usr/bin/ssh-add

vi /home/oracle/.ssh/config ... add
Host *
ForwardX11 no

vi .bashrc ... add
if [ -t 0 ]; then
stty intr ^C
fi

vi .bash_profile
umask 022
export DISPLAY=X.X.X.X:0.0

. ./.bash_profile

Check ssh equiveleance

From node01 as oracle
ssh node01 date
ssh node02 date
from node02 date
ssh node01 date
ssh node02 date

On both commands password must not be asked if all ok.

As root on each node
Check if SELinux is disabled otherwise disable it.
# /usr/sbin/getenforce
If returns Disabled you are ok !!!

Install required packages !!!
Check also : http://agstamy.blogspot.com/2010/11/installation-of-oracle-10gr2-database.html

rpm -qa | grep [PACKAGE NAME] to check
yum install [PACKAGE NAME] to install

Pakcage List according to Metalink note : 421308.1 Requirements For Installing Oracle10gR2 On RHEL 5/OEL 5 (x86_64)
binutils-2.17.50 (x86_64)
compat-db-4.2.52 (x86_64)
compat-libstdc++-296(i386)
compat-libstdc++-33-3.2.3 (x86_64)
compat-libstdc++-33-3.2.3(i386)
control-center-2.16.0 (x86_64)
gcc-4.1.1 (x86_64)
gcc-c++-4.1.1 (x86_64)
glibc-2.5-12 (x86_64)
glibc-2.5-12 (i386)
glibc-common-2.5-12 (x86_64)
glibc-devel-2.5-12 (x86_64)
glibc-devel-2.5-12(i386)
glibc-headers-2.5-12 (x86_64)
ksh-20060214-1.4 (x86_64)
libaio-0.3.96 (x86_64)
libaio-devel-0.3.106-3.2
libgcc-4.1.1(i386)
libgcc-4.1.1(x86_64)
libgnome-2.16.0 (x86_64)
libgnomeui-2.16.0 (x86_64)
libgomp-4.1.1 (x86_64)
libstdc++-4.1.1 (x86_64)
libstdc++-devel-4.1.1 (x86_64)
libXp-1.0.0-8 (i386)
make-3.81 (x86_64)
sysstat-7.0.0 (x86_64)

Add virtual and private ips on /etc/hosts on each node (The hosts file must have the same format as the example)

[root@node01 bin]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost localhost.localdomain
10.9.135.120 node01 node01.internal.upstreamsystems.com
10.9.135.121 node02 node02.internal.upstreamsystems.com
10.9.135.122 node01-vip node01-vip.internal.upstreamsystems.com
10.9.135.123 node02-vip node02-vip.internal.upstreamsystems.com
10.9.222.120 node01-priv
10.9.222.121 node02-priv

Also hostname in /etc/sysconfig/network must be without the domain.
cat /etc/sysconfig/network
NETWORKING=yes
NETWORKING_IPV6=no
HOSTNAME=node01
GATEWAY=10.9.135.8

vi /etc/hosts.equiv
node01 oracle
node02 oracle
node01-priv oracle
node02-priv oracle

Check if firewall is enabled and if is stop it
/etc/rc.d/init.d/iptables status
Firewall is stopped.
/etc/rc.d/init.d/iptables stop
chkconfig iptables off

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 65500
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 = 4194304 4194304 4194304

and run sysctl -p

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

# As per Release Notes
oracle soft memlock 3145728
oracle hard memlock 3145728

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

Create Oracle User base directories and crs home directories

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

# mkdir -p /u01/app/oracle/crs
# chown -R oracle:oinstall /u01/app/oracle/crs
# chmod -R 775 /u01/app/oracle/crs

Configure hangcheck timer
lsmod | grep hang
if not hangcheck_timer configured then
insmod /lib/modules/[Kernel Version]/kernel/drivers/char/hangcheck-timer.ko hangcheck_tick=30 hangcheck_margin=180
add the previous comand in /etc/rc.d/rc.local

Configure partitions on SAN LUNS to be used as crs,voting and ASM Disks
We have the above LUNs
/dev/sde (LUN3): 2G for CRS
/dev/sdf (LUN4): 2G for VOTING
/dev/sdg (LUN5): 60G for DATA
/dev/sdh (LUN6): 30G for FRA

The following procedure must be used for each device

[root@node01 app]# fdisk /dev/sde
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
   e extended
   p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1009, default 1): 1
Last cylinder or +size or +sizeM or +sizeK (1-1009, default 1009): 1009

Command (m for help): p

Disk /dev/sde: 2147 MB, 2147483648 bytes
67 heads, 62 sectors/track, 1009 cylinders
Units = cylinders of 4154 * 512 = 2126848 bytes

   Device Boot Start End Blocks Id System
/dev/sde1 1 1009 2095662 83 Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

At the end run partprobe on each node

Install & Configure Oracle Cluster File System (OCFS2)

Check if ocfs2 is allready installed
rpm -qa | grep ocfs2 | sort

go to http://oss.oracle.com/
and download
OCFS2 Kernel Driver,OCFS2 tools,OCFS2 console for your kernel version (You do not need the kernel-debug or xen module)
And install them on both nodes
rpm -Uhv ocfs2*

on both Oracle RAC nodes
export DISPLAY=10.9.13.252:0
ocfs2console
And do the cluster configuration to create /etc/ocfs2/cluster.conf
on both nodes must be the same and looks like :

node:
   ip_port = 7777
   ip_address = 10.9.135.120
   number = 0
   name = node01
   cluster = ocfs2

node:
   ip_port = 7777
   ip_address = 10.9.135.121
   number = 1
   name = node02
   cluster = ocfs2

cluster:
   node_count = 2
   name = ocfs2

Configure O2CB to Start on Boot and Adjust O2CB Heartbeat Threshold to 61 on both nodes
# /etc/init.d/o2cb offline ocfs2
# /etc/init.d/o2cb unload
# /etc/init.d/o2cb configure
# /etc/init.d/o2cb status to check status !!!

Create directories for Oracle CRS and Votting file
On both nodes
# mkdir -p /u02/oradata/css
# chown -R oracle:oinstall /u02/oradata/css
# chmod -R 775 /u02/oradata/css
mkdir -p /u02/oradata/ocr
chown -R oracle:oinstall /u02/oradata/ocr
chmod -R 775 /u02/oradata/ocr

On first node create filesystems fromated with ocfs2
mkfs.ocfs2 -b 4K -C 32K -N 4 -L cssfiles /dev/sde1
mkfs.ocfs2 -b 4K -C 32K -N 4 -L ocrfiles /dev/sdf1

on each node
Mount the volumes
mount -t ocfs2 -o datavolume,nointr -L "cssfiles" /u02/oradata/css
mount -t ocfs2 -o datavolume,nointr -L "ocrfiles" /u02/oradata/ocr

Add entries to /etc/fstab for auto mount on reboot
LABEL=cssfiles /u02/oradata/css ocfs2 _netdev,datavolume,nointr 0 0
LABEL=ocrfiles /u02/oradata/ocr ocfs2 _netdev,datavolume,nointr 0 0

Check the permisions of the mount points
# ls -ld /u02/oradata/css
drwxr-xr-x 3 root root 4096 Feb 12 13:17 /u02/oradata/css
# chown oracle:oinstall /u02/oradata/css
# chmod 775 /u02/oradata/css
# ls -ld /u02/oradata/css
drwxrwxr-x 3 oracle oinstall 4096 Feb 12 13:17 /u02/oradata/css
# ls -ld /u02/oradata/ocr
drwxr-xr-x 3 root root 4096 Feb 12 13:18 /u02/oradata/ocr
# chown oracle:oinstall /u02/oradata/ocr
# chmod 775 /u02/oradata/ocr
# ls -ld /u02/oradata/ocr
drwxrwxr-x 3 oracle oinstall 4096 Feb 12 13:18 /u02/oradata/ocr

Check if the o2cb is configured to start on reboot 2,3,4,5 must be on

chkconfig --list o2cb
o2cb 0:off 1:off 2:on 3:on 4:on 5:on 6:off

REBOOT BOTH NODES !!!!!

Install & Configure Automatic Storage Management (ASMLib 2.0)
ASMLib Kernel Driver , Userspace Library , Driver Support Files
Go to http://www.oracle.com/technology/software/tech/linux/asmlib/index.html
Check if ASM Lib is allready installed

On both nodes
rpm -qa | grep oracleasm | sort
Install them
rpm -Uvh oracleasm*
Configure asmlib
/etc/init.d/oracleasm configure

Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting <ENTER> without typing an
answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: oinstall
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [ OK ]
Scanning the system for Oracle ASMLib disks: [ OK ]

From the first node
# /etc/init.d/oracleasm listdisks
If there are disk availiable delete them
# /etc/init.d/oracleasm createdisk VOL1 /dev/sdg1
Marking disk "VOL1" as an ASM disk: [ OK ]
# /etc/init.d/oracleasm createdisk VOL2 /dev/sdh1
Marking disk "VOL2" as an ASM disk: [ OK ]

On the second node
#/etc/init.d/oracleasm scandisks

Verify on both nodes that you see the disks

Now begin CRS Installation

Install cvuqdisk
as root on both nodes
on first node
cd /home/oracle/clusterware/rpm/
rpm -ivh cvuqdisk-1.0.1-1.rpm
Move rpm to second node
scp cvuqdisk-1.0.1-1.rpm node02:/root
rpm -ivh cvuqdisk-1.0.1-1.rpm

As oracle run
cd ~/clusterware/cluvfy
./runcluvfy.sh stage -pre crsinst -n node01,node02

./runcluvfy.sh stage -post hwos -n node01,node02
To check for shared storage !!!

Install crs
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/crs
cd clustware
./runInstaller -ignoreSysPrereqs

as root first on node1 and then on node2 do:
/u01/app/oracle/oraInventory/orainstRoot.sh

Before run root.sh
Apply workaround #2 for vipca errors as per Metalink Note:414163.1 (maybe not applying on ppc)
as root first on node1 and then on node2 do:
vi /u01/app/crs/bin/vipca
Find lines :
if [ "$arch" = "i686" -o "$arch" = "ia64" -o "$arch" = "x86_64" ]
then
  LD_ASSUME_KERNEL=2.4.19
  export LD_ASSUME_KERNEL
fi

unset LD_ASSUME_KERNEL <<== Line to be added

run root.sh on first node and then on second node
and if you see
Error 0(Native: listNetInterfaces:[3])
[Error 0(Native: listNetInterfaces:[3])]
do
<CRS_HOME>/bin # ./oifcfg setif -global eth0/192.168.1.0:public
<CRS_HOME>/bin # ./oifcfg setif -global eth1/10.10.10.0:cluster_interconnect
<CRS_HOME>/bin # ./oifcfg getif
eth0 192.168.1.0 global public
eth1 10.10.10.0 global cluster_interconnect

The goal is to get the output of "oifcfg getif" to include both public and cluster_interconnect interfaces, of course you should exchange your own IP addresses and interface name from your environment. To get the proper IPs in your environment run this command:

<CRS_HOME>/bin # ./oifcfg iflist
eth0 192.168.1.0
eth1 10.10.10.0

Run vipca manually from failed node
/u01/app/oracle/oracle/product/10.2.0/racdb

WARNING !!!!
If you use vlans then avoid to have interfaces with names like : eth0.100
Try to use names like vlan100.
Use the following configuration for the ifcfg-[interface] script

VLAN=yes
VLAN_NAME_TYPE=VLAN_PLUS_VID_NO_PAD
DEVICE=vlan100
PHYSDEV=eth0
BOOTPROTO=static
ONBOOT=yes
TYPE=Ethernet
IPADDR=10.254.5.254
NETMASK=255.255.255.0

Do these changes or oifcfg getif will not return the interfaces and vipca never run !!!

Create a file for crs environment on both nodes
e.g .crsenv
unset ORACLE_BASE
unset ORACLE_HOME
unset ORACLE_SID
unset PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/crs
export PATH=$ORACLE_HOME/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

Patch crs to the latest patch set. CRS must always have the same or higher patch level than the oracle database home.
Then install Oracle Software using software only and selecting all Cluster Nodes.
Patch the new oracle home to the latest patch set and then
1) Create the ASM Instance and Configrue the Disk Groups
2) Create your RAC database and choose to manage it with dbconsole.

References

Metalink Notes : 414163.1,421308.1

Oracle 10gR2 CRS diagwait to avoid OPROCD node evictions

2011-07-03T15:33:00.000+02:00

If your CRS is from 10.2.0.3 and up is a must to apply the following on each node on our cluster.

Execute as root
#crsctl stop crs #<CRS_HOME>/bin/oprocd stop
Ensure that Clusterware stack is down on all nodes by executing
#ps -ef |egrep "crsd.bin|ocssd.bin|evmd.bin|oprocd"
This should return no processes. If there are clusterware processes running and you proceed to the next step, you will corrupt your OCR. Do not continue until the clusterware processes are down on all the nodes of the cluster.
From one node of the cluster, change the value of the "diagwait" parameter to 13 seconds by issuing the command as root:
#crsctl set css diagwait 13 -force
Check if diagwait is set successfully by executing. the following command. The command should return 13. If diagwait is not set, the following message will be returned "Configuration parameter diagwait is not defined"
#crsctl get css diagwait
Restart the Oracle Clusterware on all the nodes by executing:
#crsctl start crs
Validate that the node is running by executing:
#crsctl check crs

The above procedure had set the diagwait parameter to 13. This parameter according to Oracle gives an additional time of 10 secs to flush the necessary CRS diagnostics on the disk during a node eviction. From my experience it resolves node evictions under heavy load (Specially in AIX) because changes the OPROCD default values that affect scheduling latencies. In most circumstances OPROCD default values can be overly very sensitive , especially under heavy loaded cluster and Oracle stated "To overcome these scheduling latencies, Oracle recommends that you set the Oracle Clusterware parameter DIAGWAIT to the value 13." on note id 567730.1

Personally i set this parameter in every Oracle Clusterware 10gR2 (10.2.0.3 and up) installation.
It does not apply in 11g since the logic has change.

References

Oracle Metalink Notes: 559365.1 ,567730.1

Oracle 10gR2 Secure External Password Store

2011-06-08T11:35:00.002+02:00

Secure External Password Store is an Oracle 10g Release 2 feature that gives you the capability to encrypt password used from client scripts (sqlplus , shell , expdp/impdp exp/imp) that connects to the database, using an Oracle Wallet.

The configuration is very simple so just follow these steps (Unix/Linux):

1) First verify that you have an entry in the tnsnames.ora for your Database by creating a valid db_tns_alias

2) Create the Oracle Wallet
   mkstore -wrl /your_wallet_store_dir -create (It will ask to enter a password twice)

3) Create the database credentials inside the wallet
   mkstore -wrl /your_wallet_store_dir -createCredential tns_alias username password

4) Add the following lines on $ORACLE_HOME/network/admin/sqlnet.ora (or create it if not exists)

WALLET_LOCATION =
  (SOURCE = (METHOD = FILE)
   (METHOD_DATA =
   (DIRECTORY = /your_wallet_store_dir)))

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

5) Just use the /@db_tns_alias when you want to connect to the specified user
   sqlplus /@db_tns_alias
   expdp /@tns_alias parfile=/mydir/mypar.par

6) You have commands to delete, list , and modify the credentials using the mkstore utility
   mkstore -wrl /your_wallet_store_dir -listCredential
   mkstore -wrl /your_wallet_store_dir -deleteCredential db_tns_alias
   mkstore -wrl /your_wallet_store_dir -modifyCredential db_tns_alias username newpassword (Must have been changed on the database side first)

References

Oracle Support Note : Using The Secure External Password Store [ID 340559.1]
Oracle Secure External Password Store White Paper

Installing Oracle Grid Control 10.2.0.5 on RHEL 5.X

2011-05-26T11:00:00.000+02:00

After several tries i managed to install and configure the EM Grid Control 10.2.0.3 and patch it to 10.2.0.5.

This guide will help you bypass any issues.
I will install using an existing database (RAC) and i will also tell you how you will make OMS RAC aware.

1) Pre Installation

Please go to Oracle 10gr2 installation on RHEL5 and follwow the guide until step 11.

Also check
Oracle® Enterprise Manager Grid Control Installation and Basic Configuration 10g Release 3 (10.2.0.3.0)
Part Number B40103-01
Chapter 1 Preinstallation Requirements
Appendix B Platform-Specific Package and Kernel Requirements

Then go into rour software directory and run :
export DISPLAY:YOUR_XHOST_CLIENT:0.0
./runInstaller -ignoreSysPreReqs

2) Instalation
- Choose Enterprise Manager Grid Control Using an Extsing Database.
- Choose your oracle base location. If you have set ORACLE_BASE before installing it will go there.
- Choose your language and click Next.
- Choose an inventory location and user groups.
- Make user verified the installer checks about O/S , O/S Packages and GLIBC package.
- Give database connection details (For RAC DBs also give one node instance connect info, it will be fixed at the end)
  Press the "Prefill Tablespace Locations". If you use ASM it will open a Window that it will tell you to provide the desired Groups.
  If you have allready using DB console (SYSMAN Schema exists) you will get a warning. Do as desired !!
Please ensure that you have aq_tm_processes=1 and session_cached_cursors=200 or you will get another warning
Ensure that even if you use Automatic Memory Management that in the instacne you connect the shared_pool_size is greater than 150M.
Just set it to bypass the installer prob and then reset back.
e.g for a RAC Instance
alter system set shared_pool_size=500M sid='rlabdb1';
alter system reset shared_pool_size sid='rlabdb1';
Run the follwoing script : @?/rdbms/admin/dbmspool.sql on your DB
- Configure your email and your smtp server for all notifications, Metalink Info and Proxy Configuration for patching and staging (You can do it later).
- Set up your passwords and decide if you want the Agents to use SSL for communications with the OMS and Central Agent
- Finally press Install !!!

3) Installation Problems
- If you take a warning that "OPMN Process Manager failed to start .. Check the logs in [ORACLE_BASE]/oms10g/opmn/logs" do the following:
  Go to the dir and vi the ipm.log. If you see "PM state file does not exist: [ORACLE_BASE]/oms10g/opmn/logs/states/.opmndat" touch the file manually
[oracle@vmsrv21 logs]$ touch [ORACLE_BASE]/oms10g/opmn/logs/states/.opmndat
Check that opmn is allready started
[oracle@vmsrv21 logs]$ [ORACLE_BASE]/oms10g/opmn/bin/opmnctl status
If you see the following lines ... you are ok ... Press continue not Retry
Processes in Instance: EnterpriseManager0.vmsrv21.internal.upstreamsystems.com
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
DSA | DSA | N/A | Down
HTTP_Server | HTTP_Server | N/A | Down
LogLoader | logloaderd | N/A | Down
dcm-daemon | dcm-daemon | N/A | Down
OC4J | home | N/A | Down
WebCache | WebCache | N/A | Down
WebCache | WebCacheAdmin | N/A | Down
Otherwise
  [oracle@vmsrv21 logs]$ [ORACLE_BASE]/oms10g/opmn/bin/opmnctl status

4) Configuration Assistant Problems

- OC4J Instance Configuration Assistant
Check the details pane on installer. You may see OC4JDeploy Tool completed but with errors
go to [ORACLE_BASE]/oms10g/cfgtoollogs and check the failed command
vi configToolFailedCommands
The first comand is the failed.
[ORACLE_BASE]/oms10g/jdk/bin/java -Djava.net.preferIPv4Stack=true -Djava.io.tmpdir=/tmp -mx512M -classpath [ORACLE_BASE]/oms10g/dcm/lib/dcm.jar:[ORACLE_BASE]/oms10g/dcm/lib/oc4j_deploy_tools.jar:[ORACLE_BASE]/oms10g/opmn/lib/opmnplugin.jar -Doracle.ias.sysmgmt.logging.logdir=[ORACLE_BASE]/oms10g/j2ee/home/log oracle.j2ee.tools.deploy.Oc4jDeploy -oraclehome [ORACLE_BASE]/oms10g -verbose -inifile [ORACLE_BASE]/oms10g/j2ee/deploy.ini
I resolved it doing a manuall restart of the opmn managed process
1. Check the status
[oracle@vmsrv21 bin]$ ./opmnctl status

Processes in Instance: EnterpriseManager0.vmsrv21.internal.upstreamsystems.com
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
DSA | DSA | N/A | Down
HTTP_Server | HTTP_Server | N/A | Down
LogLoader | logloaderd | N/A | Down
dcm-daemon | dcm-daemon | 10901 | Alive
OC4J | home | N/A | Down
WebCache | WebCache | N/A | Down
WebCache | WebCacheAdmin | N/A | Down

2. Stop and start everything
[oracle@vmsrv21 bin]$ ./opmnctl stopall
opmnctl: stopping opmn and all managed processes...
[oracle@vmsrv21 bin]$ ./opmnctl startall
opmnctl: starting opmn and all managed processes...

3. Check the status again , if this is your case you will see more process in status "Alive"
[oracle@vmsrv21 bin]$ ./opmnctl status

Processes in Instance: EnterpriseManager0.vmsrv21.internal.upstreamsystems.com
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
DSA | DSA | N/A | Down
HTTP_Server | HTTP_Server | 12224 | Alive
LogLoader | logloaderd | N/A | Down
dcm-daemon | dcm-daemon | N/A | Down
OC4J | home | 12225 | Alive
WebCache | WebCache | 12246 | Alive
WebCache | WebCacheAdmin | 12234 | Alive

- OMS Configuration Assistant
  Check which process failed to start
  vi [ORACLE_BASE]/oms10g/cfgtoollogs/cfgfw/
INFO: oracle.sysman.top.oms:PerformSecureCommand:runCmd:Command Output stderr:
'================================================================================
opmn id=vmsrv21.internal.upstreamsystems.com:6200
5 of 6 processes started.

ias-instance id=EnterpriseManager0.vmsrv21.internal.upstreamsystems.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ias-component/process-type/process-set:
HTTP_Server/HTTP_Server/HTTP_Server

Error
--> Process (pid=27421)
failed to start a managed process after the maximum retry limit
Log:[ORACLE_BASE]/oms10g/opmn/logs/HTTP_Server~1

Check the Apache Start Log :
This is the ouput of my Log.
[ORACLE_BASE]/oms10g/Apache/Apache/bin/apachectl startssl: execing httpd
Usually the problem is with the SSL Wallet Configuratiopn.
Edit the template httpd_em.conf on [ORACLE_BASE]/oms10g/sysman/config/httpd_em.conf.template
   Locate the follwoing 2 lines :
   SSLWallet file:&ORACLE_HOME&/sysman/wallets/oms.&THIS_DNS_HOST&
   SSLWalletPassword &OMS_WALLET_PASSWORD&
   Comment the second and make the first as follows in order to have the new output.
   SSLWallet file:&ORACLE_HOME&/Apache/Apache/conf/ssl.wlt/default
   #SSLWalletPassword &OMS_WALLET_PASSWORD&

Retry OMS configuration to proceed

- Agent Configuration Assistan
You may get a message that the agent ca has failed but the central agent is up and running.
Check the status of the central agent : [ORACLE_BASE]/agent10g/bin/emctl status agent
Agent LogFiles Directory : [ORACLE_BASE]/agent10g/sysman/log
Agent Configuration Directory : [ORACLE_BASE]/agent10g/sysman/log

5) Set EM Grid Control to Use RAC DB URL

- Edit the emoms.properities
vi [ORACLE_BASE]/oms10g/sysman/config/emoms.properties
Edit oracle.sysman.eml.mntr.emdRepConnectDescriptor property and put the in the field service_name a RAC configured service.
oracle.sysman.eml.mntr.emdRepConnectDescriptor=(DESCRIPTION\=(LOAD_BALANCE\=on)(FAILOVER\=on)(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=labdb01.internal.upstreamsystems.com)(PORT\=1521))(ADDRESS\=(PROTOCOL\=TCP)(HOST\=labdb02.internal.upstreamsystems.com)(PORT\=1521)))(CONNECT_DATA\=(SERVICE_NAME\=raclabdb))(FAILOVER_MODE\=(TYPE\=select)(METHOD\=basic)))
Restart All Processes !!
[ORACLE_BASE]/oms10g/opmn/bin/opmnctl stopall
[ORACLE_BASE]/oms10g/opmn/bin/opmnctl startall
[ORACLE_BASE]/oms10g/opmn/bin/opmnctl status

6) Apply 10.2.0.5 Patch Set

Follow the README.txt that comes with the pact set and you will not have any problems.

Transport Tablespace and Re-mapping Schemas/Datafiles/Tablespaces 10gR2 (10.2.0.4)

2011-05-24T11:57:00.000+02:00

Recently i tried to do an impdp using the remap_schema,remap_tablespace and remap_datafiles along with the transport_datafiles.

This action hits BUG:6989875 which causes Data Pump Worker process (DW) to hang when using paramter REMAP_DATAFILE. Also specifying REMAP_DATAFILE during a datapump import job when also specifying TRANSPORT_DATAFILES is not allowed, according to Metalink Note: 762160.1

This BUG is fix in 11.2 Release.

The workaround is the only legal way to do the desired renaming of the datafiles:

If you need to use a different filename for the imported tablespace simply copy or rename the affected datafile before starting the import using cp or mv commands for unix. Then in the TRANSPORT_DATAFILES parameter use the new file names.

CleanUp Temporary Segments Occupying Permanent Tablespace

2011-05-11T11:23:00.001+02:00

Recently i did some objects reorganization but i left with some temporary segments occupying space which smon did not drop because the tablespace was READ ONLY. When i made it READ WRITE then the temporary segments dropped.If the problem persists you have by different reason please follow this article :http://askdba.org/weblog/2009/07/cleanup-temporary-segments-in-permanent-tablespace

ORA-39726 tying to drop column on compressed table (10g)

2011-05-10T13:30:00.000+02:00

You get ORA-39726: unsupported add/drop column operation on compressed tables when you try to drop a column on a compressed table. In order to avoid drop and recreate the table and adding all indexes , constraints etc you can set the column unused.alter table mytable set unused (mycol1,mycol2);This statement does not actually remove the target column data or restore the disk space occupied by these columns. However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. All constraints, indexes, and statistics defined on the column are also removed.References:Oracle® Database Administrator's Guide 10g Release 2 (10.2)Chapter 15 -- Managing TablesTopic : Altering ColumnsNote:In 11g you can also drop columns on compressed tables If you enable compression for all operations on a table. If you enable compression for direct-path inserts only, you cannot drop columns.

Constraints : FKs influence in performance , Deferrable Decleration

2011-04-14T13:39:00.000+02:00

Very useful articles about constrains from Tom Kyte.About Fks and the affect on Query Optimizer.http://www.oracle.com/technetwork/issue-archive/2009/09-may/o39asktom-096149.htmlAbout the deferrable status and suggestion when to implement it. (First Part of the Article)http://www.oracle.com/technetwork/issue-archive/2011/11-may/o31asktom-354139.html

Oracle 11gR2 on AIX Memory Issue after Upgrade

2011-03-18T12:36:00.000+02:00

Check this article if you plan or already upgraded to 11gR2 on AIX.

http://www.ora600.be/Memory+Footprint+For+Dedicated+Server+Processes+More+Than+Doubled+After+11g+Upgrade+On+AIX+Platform

Profile and Resource Limit

2011-03-18T09:45:00.006+02:00

Recently i decided to set an idle_time limit for some of my production database users, beacause all the time left their sessions connected to the database after finishing their work consuming resources.

I create a profile limiting idle time to 2 hours , but i found out that its not working.

In order a profile to limit a resource you must set the RESOURCE_LIMIT initialization parameter to TRUE. Fortunately this parameter is dynamic so :

Issue :

For RAC DBs:
alter system set resource_limit = TRUE scope=both SID='*';
create profile tst_profile limit idle_time 120;
alter user my user profile tst_profile;

For non RAC DBs omit the sid='*'

For more info check the

Oracle® Database Security Guide 10g Release 2 (10.2)
Part Number B14266-06

ORA-00600: internal error code, arguments: [kdsgrp1]

2011-03-14T17:01:00.002+02:00

ORA-00600: internal error code, arguments: [kdsgrp1] , This error may occur on a RAC database when (re)building index online.

The fixes for this bug are in Metalink Note : 285586.1

As a workaround please try to rebuild the index either offline or online with as little as possible activity on the affected table.

If error continues check the table for chained rows.

ORA-00230 operation disallowed: snapshot controlfile enqueue unavailable

2011-03-14T10:46:00.002+02:00

On RMAN backup with automated controlfile backup enabled this error means that another process currently holds the snapshot controlfile enqueue , so the current process cannot backup it.

If you have this error with multiple channels, you may hit a bug. Search Oracle Support.

Oracle Data Types

2011-03-09T12:05:00.000+02:00

A very nice article about the Oracle Data Types.

http://www.asktheoracle.net/oracle-data-types.html

Exchanging Partitions Example

2011-03-02T11:52:00.003+02:00

1) Exchanging a List partition table

The following example will show how to move from one partition table to a new one using an intermediate table.

-- Create our set of tables and populate with some data

CREATE TABLE myschema.my_part_table
(a char(1),
b int
) partition by list(b)
(
partition p_initial values (0),
partition p_part values (1)

);

create index myschema.my_part_table_idx on myschema.my_part_table(b) local;

insert into myschema.my_part_table
select 'A',1
from all_objects;

commit;

create table myschema.my_int_table as select * from myschema.my_part_table where 1=2;
create index myschema.int_my_part_table_idx on myschema.my_int_table(b);

CREATE TABLE myschema.my_new_part_table
(a char(1),
b int
) partition by list(b)
(
partition p_initial values (0),
partition p_part values (1)

);

create index myschema.my_part_table_idx on myschema.my_new_part_table(b) local;

-- Exchange original partition with intermediate table

alter table myschema.my_part_table exchange partition p_part with table myschema.my_int_table
including indexes without validation;

-- Exchange intermediate table with target partition table

alter table myschema.my_new_part_table exchange partition p_part with table myschema.my_int_table
including indexes without validation;

-- Verify the data are in the new partition table
select * from myschema.my_part_table partition (p_part);
select * from myschema.my_new_part_table partition (p_part);
select * from myschema.my_int_table;

ORA-00054 Resource busy and acquire with NOWAIT specified

2011-03-01T12:04:00.000+02:00

A very nice entry for Oracle lock analysis

http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html

ORA-20401: A job with the specified name already exists

2011-02-25T10:29:00.002+02:00

Sometimes in EM Dbconsole 10.2 you try to create a new job and you get the follwoing error:

ORA-20401: A job with the specified name already exists ORA-06512: at "SYSMAN.MGMT_JOBS

First thing is to check the Job and Job library EM screens to check if the job appears there. If so check the status and try to delete from there.

If you cannot change the status please follow :

Stop dbconsole job hung in status Stop Pending on this blog to change the status to stopped.

The if you want to delete it , and you are sure there is other instance of the job scheduled execute :

as sysman:

select job_id, job_name, job_owner from mgmt_job where job_name like '%[JOB_NAME]%';
exec mgmt_job_engine.delete_job('[JOB_ID_FROM_PREVIOUS_STEP]');
commit;

References

Oracle Metalink Note ID 430626.1
Vijay R. Dumpa Oracle Blog

Oracle 9i and 10g Documentation Online

2011-02-16T11:55:00.001+02:00

Here is a link with the Oracle 9iR2 and 10g R1 & R2 online.

http://www.stanford.edu/dept/itss/docs/oracle/

2011-02-09T11:04:00.001+02:00

A very nice article about the 11g Result Cache Feature. It also discusses how RAC benefits from this feature and differences between 11g and 10g.

http://www.oracle.com/technetwork/articles/datawarehouse/vallath-resultcache-rac-284280.html?msgid=3-3351742848

Global Temporary Tables

2011-02-08T12:09:00.002+02:00

A very nice article for Global Temporary Tables from Jonathan Lewis
http://jonathanlewis.wordpress.com/2011/02/06/gtts/

TSPITR Fails with ORA-29308: view TS_PITR_CHECK failure

2011-02-03T16:04:00.006+02:00

Trying to do automated tablespace point in time recovery(TSPITR) containing an IOT with a LOB column and fails to the Export Face of the recovered tablespace with the following :

Export: Release 10.2.0.4.0 - Production on Thu Feb 3 15:18:34 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
EXP-00008: ORACLE error 29308 encountered
ORA-29308: view TS_PITR_CHECK failure
ORA-06512: at "SYS.DBMS_PITR", line 889
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
host command complete

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/03/2011 15:18:38
RMAN-03015: error occurred in stored script Memory Script
RMAN-06135: error executing host command: Additional information: 256
Additional information: 11

Bug 6620517 ORA-29308 from TSPITR with IOT with LOB column
Fixed in 10.2.0.5

ATTENTION !!!!
Bug 6620517 is not only to IOT's with LOB's but more issue with IOT's and TSPITR.

This an except from an Oracle SR i opened :

"
So the option for 10.2.0.4 and 10.2.0.5, when IOT's are involved, is to do the Tablespace Point In Time Recovery manually.

- DUPLICATE DATABASE SKIP TABLESPACE
- Transportable Tablespace export of the related tablespace on the duplicate database
- Transportable Tablespace import of the related tablespace into the source database.

....

Indeed Bug 6620517 is not resolving the 'IOT with NO LOB 'issue in 10.2.0.5.
That is fixed in 11g.
"

And you can use the same workaround for Bug:7016765 as here:
RMAN Tablespace Point-in-Time Recovery and ORA-19602

The bug case can be cheked with the following procedure:

1) the recovery-set of tablespaces (eg 'MYTBS') fully contains all the storage objects of an Index-Organized Table (IOT) with a LOB column.
select
tablespace_name,
segment_type,
owner||'.'||segment_name "OWNER.OBJECT"
from
dba_segments
where
tablespace_name = 'MYTBS'
order by
2,3;

2) Query the TS_PITR_CHECK view shows the IOT-with-LOB consists of storage objects in the recovery set of tablespaces and the IOT also has a storage object in the SYSTEM tablespace, for example if the following query returns at least one row:

select * from
sys.ts_pitr_check
where ( ts1_name in ('MYTBS')
and ts2_name not in ('MYTBS') )
or ( ts1_name not in ('MYTBS')
and ts2_name in ('MYTBS') );

with one object is reported as being a TABLE in the SYSTEM tablespace and the other object is reported as being an INDEX in one of the tablespaces in the recovery-set and the reason is: "Tables and associated indexes not fully contained in the recovery set"

3) Assure that the table is an IOT:
select iot_type from dba_tables where table_name='[Paste the table_name from the above query]';
It must return IOT
Verify the INDEX is a LOB index:
select index_type from dba_indexes where index_name='Paste the table_name from the above query';
It must return LOB
The (bogus) violation reported above is not reported by the following method (correctly):
execute dbms_tts.transport_set_check('MYTBS',TRUE,TRUE);
select * from transport_set_violations;

This method is also (also) used to check if a set of tablespaces are fully contained and for that reason you can export the recovered tablespace from the auxiliary instance as the workround mentioned previous in the article.

TSPITR using restore point on 10gR2 fails

2011-01-25T12:14:00.004+02:00

When try to do RMAN TSPITR using a previous created restore point (no guarantee) rman fails :

RMAN> run {
recover tablespace 'MYTBS' until restore point TEST_RESTORE_POINT2 auxiliary destination = '/data/ttbs/testdb/data';
sql 'alter tablespace "MYTBS" online';
}

Starting recover at 25-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=200 instance=testdb1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=186 instance=testdb1 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/25/2011 12:11:09
RMAN-00600: internal error, arguments [5085] [point] [] [] []

Searching in metalink gave me this :

Bug 8571266 : RMAN TSPITR to restore point fails with RMAN-600 [5085]
Cannot specify TO RESTORE POINT for RMAN TSPITR (tablespace point in time recovery)

Range of versions believed to be affected Versions BELOW 11.2.0.2
11.2.0.1, 11.1.0.7 and i have also tried 10.2.0.4

This issue is fixed in 11.2.0.2 (Server Patch Set)

The documentation states that DB Point in time Recovery can be done using restore points (I have tested and worked fine with RMAN doing incomplete recovery to restore point) and Oracle recommends to create restore point to make point-in-time recovery more manageable.

So i wanted to try using the restore point with TSPITR and its not working. I believe this is a silly RMAN bug because when you create a restore point the database records the scn and time of the restore point !!!

select name, scn , time
from v$restore_point where name='TEST_RESTORE_POINT2';

NAME SCN TIME
-------------------------------------------------------------------
TEST_RESTORE_POINT2 15940639418 25/01/11 10:10:25,000000000

So the workaround for this bug (silly if you imagine) is to using TSPITR using until scn or until time !!!

run {
recover tablespace 'MYTBS' until scn 15940639418 auxiliary destination = '/data/ttbs/testdb/data';
sql 'alter tablespace "MYTBS" online';
}

ORA-01666 When Trying to Activate Standby Database

2011-01-13T11:16:00.002+02:00

When Trying to Activate Standby Database , and you use startup or startup mount you will get ORA-01666: controlfile is for a standby database

You must not forget that since this is the standby you must do :

sqlplus / as sysdba
startup nomount
alter database mount standby database;

Then you can perform any operation you want
e.g
start the automatic recovery session
fix a gap on log sequences
etc

11g RAC Install on Centos VM

2011-01-05T12:30:00.001+02:00

The follwoing link can be used to test 11g RAC on x86_64 Linux using vm machines and CentOs.

http://www.pafumi.net/RAC11g_and_ASM_on_VMware.pdf

Can also be used as a general guide for installing 11gRAC

ORA-19573: cannot obtain exclusive enqueue for datafile

2011-01-03T11:12:00.001+02:00

This errors is usually happens when tried to restore the tablespace and the tablespace was online and the database open.

Solution :

1) Take the tablespace offline before trying to restore it.
2) DO recovery on the datafiles belonging to the tablespace.

Oracle block browser and editor (BBED)

2010-12-21T09:35:00.003+02:00

Oracle BBED is there from Oracle7 to Oracle10g and is designed for internal use only.

Check the above links for more info on this tool:

http://www.dba-oracle.com/t_bbed.htm
http://www.dba-oracle.com/forensics/t_forensics_bbed.htm

http://orafaq.com/papers/dissassembling_the_data_block.pdf
http://www.oracle-internals.com/?p=17

Also check this : http://www.freelists.org/post/oracle-l/is-it-the-bbed

This is a very dangerous tool to use it in production with out Oracle Support guidance and suggestion. Also the tool is password protected so you must find the password to run it.

Building an Oracle RAC 11g R2 on Linux - (RHEL 5.5)

2010-12-16T09:27:00.001+02:00

A very nice article about build Oracle 11gR2 RAC on Linux
http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle11gRAC/CLUSTER_12.shtml

How is 'skip tablespace' handled by the RMAN duplicate command

2010-12-15T15:58:00.001+02:00

Once RMAN opens the Auxilary database it will attempt to drop all tablespaces which are skipped.
RMAN does the drop of these tablespaces in reverse sorted tablespace names to be skipped.
Depending on what is in these tablespaces, or if it is the system default tablespace, the 'drop
tablespace' command can fail.

RMAN Tablespace Point-in-Time Recovery and ORA-19602

2010-12-14T16:35:00.010+02:00

Bug: 7016765 10.2.0.3.0 RDBMS 10.2.0.3.0 RMAN
Abstract: ORA-19602 WHEN RUNNING RMAN'S TSPITR
RMAN TSPITR does not always work using datafilecopies. Not an issue with backupsets.
This is fix in 10.2.0.5

Path 7016765 have been ported for Linux x86_64. You can also check on Metalink patch search for other platforms.

This bug also states for the 10.2.0.4 version and if you hit this there is no workaround until you apply 10.2.0.5.So in order to avoid this bug , you must manual duplicate the database to another instance and applying incomplete recovery and then do a transaportable tablespace set export/import to the target database for tbs you want to perform TSPITR.

My scenario is based on a 3 Node Oracle 10gR2 (10.2.0.4) RAC database.

A tablespace called MYTBS must be recovered back in time before an accidentally "drop user cascade" occurred. The user dropped at 14:00 and itried to use TSPITR but RMAN failed with ORA-19602 as stated in the beginning of the article.

The only valid workaround to this very annoying issue (believe me , i have fall into this bug on my production 24x7 system) is to use the duplicate database feature doing a whole database point in time recovery skipping all the tablespaces you except SYSTEM,SYSAUX,UNDO and the tablespace you want to recover.

In this example ORACLE_BASE=/u01/app/oracle

1) Create an AUX init.ora file like you do in manual TSPITR having the following entries.

# FILE: initAUX.ora
audit_file_dest =/u01/app/oracle/admin/AUX/adump
background_dump_dest =/u01/app/oracle/admin/AUX/bdump
core_dump_dest =/u01/app/oracle/admin/AUX/cdump
user_dump_dest =/u01/app/oracle/admin/AUX/udump
db_name =AUX
instance_name =AUX
db_unique_name=AUX
db_create_file_dest=/data/ttbs
db_recovery_file_dest=/data/ttbs
db_recovery_file_dest_size=100000M # Resize according to the size on the mount point you are duplicating
db_block_size = 8192
compatible=10.2.0.4.0
db_block_size=8192
db_files=1000
large_pool_size=1M
shared_pool_size=110M
remote_login_passwordfile=exclusive
undo_management=AUTO
undo_retention=900
undo_tablespace=UNDO2 # Must be the same as the target instance

I have placed my file in the [oracle@labdb01 AUX]$ORACLE_HOME/dbs in order sqlplus to use it by default.

2) Create the following directories and the password file
[oracle@labdb01 AUX]$ mkdir -p /data/ttbs/AUX
[oracle@labdb01 AUX]$ mkdir -p /u01/app/oracle/admin/AUX/adump
[oracle@labdb01 AUX]$ mkdir -p /u01/app/oracle/admin/AUX/bdump
[oracle@labdb01 AUX]$ mkdir -p /u01/app/oracle/admin/AUX/udump
[oracle@labdb01 AUX]$ mkdir -p /u01/app/oracle/admin/AUX/cdump

[oracle@labdb01 AUX]$ orapwd file=[oracle@labdb01 AUX]$ORACLE_HOME/dbs/orapwAUX password=pass

3) Startup nomount the AUX instance
[oracle@labdb01 AUX]$ export ORACLE_SID=AUX
[oracle@labdb01 AUX]$ sqlplus / as sysdba
SQL> create spfile from pfile;

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 205520896 bytes
Fixed Size 2082752 bytes
Variable Size 146802752 bytes
Database Buffers 50331648 bytes
Redo Buffers 6303744 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@labdb01 AUX]$

4) Create an rman script for the duplication (dup.rcv)
Keep in mind that SYSTEM,SYSAUX and UNDO tablespaces can not be skipped.
Also the recovery set of tablespaces must consist of all dependable objects e.g table and indexes , tables/partitions, lob segments , lob indexes.
Check also the v$archived_log in order to find the exact time to use.

RUN
{
sql 'alter system archive log current';
allocate channel C1 device type disk;
allocate auxiliary channel C2 device type disk;
set until time "to_date('14/12/2010 13:20:00','DD/MM/YYYY HH24:MI:SS')";
DUPLICATE TARGET DATABASE TO AUX
SKIP TABLESPACE USERS;
}

5) Connect to rman and run the script
NOTE: if your target db is using a catalog then add the string catalog user/pass@catalog_sid

[oracle@labdb01 ~]$ rman target sys@testdb1 auxiliary /
RMAN>@dup.rcv

NOTE: You cannot skip the default temporary tablespace from the recovery set.

6) Begin transport tablespace procedure.

on AUX :

SQL>begin
SQL>sys.dbms_tts.transport_set_check('MYTBS',TRUE);
SQL>end;
SQL>/

PL/SQL procedure successfully completed.

SQL>select * from sys.transport_set_violations;

If this query returns rows then fix them before proceed.

SQL>alter tablespace mytbs read only;

SQL> create or replace directory exp_dir as '/data/ttbs/AUX';

Directory created.

SQL> grant read,write on directory exp_dir to system;

Grant succeeded.

SQL>exit;

[oracle@labdb01 ~]$ expdp system dumpfile=mytbs.dmp directory=EXP_DIR TRANSPORT_TABLESPACES=MYTBS TRANSPORT_FULL_CHECK=Y

If the expdp fails then :

SQL>select reason,count(*) from sys.pluggable_set_check where ts1_name like '%MYTBS%' group by reason;

And resolve each reason !!!

Copy the dmp file and the datafile(s) of the tabelspace to the desired target db directory.
[oracle@labdb01 ~]$ cp /data/ttbs/AUX/mytbs.dmp /data/ttbs/testdb/
[oracle@labdb01 ~]$ cp /data/ttbs/AUX/datafile/o1_mf_mytbs_6jgv6obd_.dbf /data/ttbs/testdb/

IF you are recovering form a user error like our scenario drop the tabelspace from the target !!
Re create the dropped user. Remember to grant his roles and give him his object grants.
My user had the dba role granted so.

[oracle@labdb01 ~]$ sqlplus sys@testdb as sysdba
SQL> drop tablespace mytbs including contents and datafiles cascade constraints;
SQL> grant dba to agis identified by agis;
SQL> alter user agis default tablespace users;
SQL> exit;

Go to the directory you copied the files:

[oracle@labdb01 ~]$ cd /data/ttbs/testdb/

I have a directory object TTS_DIR that points to the correct O/S directory. If not create one as in previous section
[oracle@labdb01 testdb]$ export ORACLE_SID=testdb
[oracle@labdb01 testdb]$ impdp system dumpfile=mytbs.dmp logfile=imp.log DIRECTORY=TTS_DIR TRANSPORT_DATAFILES='/data/ttbs/testdb/o1_mf_mytbs_6jgv6obd_.dbf'

If you want you can omit to gather statistics again using : EXCLUDE=TABLE_STATISTICS EXCLUDE=INDEX_STATISTICS on the impdp

At this point the tablespace mytbs has been restored bur its read only and unknown to the control file.

[oracle@labdb01 testdb]$ sqlplus agis/agis

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Dec 14 15:40:17 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
TEST

SQL> select count(*) from test;

COUNT(*)
----------
49643

BUT !!!

[oracle@labdb01 testdb]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Dec 14 15:41:02 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: TESTDB (DBID=2520745547)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 490 SYSTEM *** /data/ttbs/testdb/system01.dbf
2 90 UNDOTBS1 *** /data/ttbs/testdb/undotbs01.dbf
3 360 SYSAUX *** /data/ttbs/testdb/sysaux01.dbf
4 5 USERS *** /data/ttbs/testdb/users01.dbf
5 25 UNDOTBS2 *** /data/ttbs/testdb/undotbs02.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 166 TEMP 32767 /data/ttbs/testdb/temp01.dbf

Now its the time to rename the datafile if you want or make it writable in order to inform the controlfile.

On the target instance
[oracle@labdb01 testdb]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Dec 14 15:43:34 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select file_id,file_name from dba_data_files where tablespace_name='MYTBS';

FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
6
/data/ttbs/testdb/o1_mf_mytbs_6jgv6obd_.dbf

SQL> alter tablespace mytbs offline;
SQL> !mv /data/ttbs/testdb/o1_mf_mytbs_6jgv6obd_.dbf /data/ttbs/testdb/mytbs.dbf
SQL> alter tablespace mytbs rename datafile '/data/ttbs/testdb/o1_mf_mytbs_6jgv6obd_.dbf' to '/data/ttbs/testdb/mytbs.dbf';

Tablespace altered.

SQL> select file_id,file_name from dba_data_files where tablespace_name='MYTBS';

FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
6
/data/ttbs/testdb/mytbs.dbf

SQL> alter tablespace mytbs online;

Tablespace altered.

SQL> alter tablespace mytbs read write;

Tablespace altered.

SQL> exit

Backup your tabelspace imemdiate after the restore !!!

7) Drop the AUX database
[oracle@labdb01 ~]$ export ORACLE_SID=AUX
[oracle@labdb01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Dec 14 15:56:22 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@labdb01 ~]$ rm -fr /data/ttbs/AUX
[oracle@labdb01 ~]$

We keep the password file and the init file in order to use the same configuration for other point int time recovery scenarios.

If you database uses ASM you must put the tabelspace in ASM.

From sqlplus
SQL> alter database tablespace mytbs offline;

Use RMAN to transfer the tablespace to ASM

rman target /
RMAN>
RMAN> backup as copy tablespace mytbs format '+DATA';
RMAN> switch tablespace mytbs to copy;
RMAN> recover tablespace mytbs;

From sqlplus

SQL> alter database tablespace mytbs online;

Database altered.

And you are done !!!

References:

Metalink Documnet IDs:
340848.1
368029.1
153981.1
388424.1
371556.1

Oracle enqueue wait tips

2010-11-26T12:35:00.001+02:00

Oracle enqueue wait tips from Burleson Consulting site

http://www.dba-oracle.com/t_enqueue_wait_types_.htm

Installation of Oracle 10gR2 database on RedHat Enterprise Linux/Oracle Enterprise Linux 5 (x86_64)

2010-11-17T12:37:00.002+02:00

As root

1) Check Hardware Requirements

# grep MemTotal /proc/meminfo >= 1Gb
# grep SwapTotal /proc/meminfo
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

# 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, depending on the installation type
1.2 GB of disk space for a preconfigured database that uses file system storage (optional)
To determine the amount of free disk space on the system, enter the following command

2) Kernel Requirements

# uname -a >= 2.6.18-8

3) Disable SELinux or switch it to "Permissive" mode

#/usr/sbin/sestatus
SELinux status: disabled
or
#/usr/sbin/getenforce
Disabled

If you see the previous output SELinux is disabled.

If SELInux is enabled then run :

Commands, as root:
======================
getenforce (returns "Enforcing")
setenforce 0
getenforce (returns "Permissive")

End to restore it to enforcing
setenforce 1
getenforce (returns "Enforcing")

For more info check Oracle Metalink Note: 454196.1

4) Installing Required packages

Use rpm -qa | grep package_name for the following packages

binutils-2.17.50.0.6-2 (x86_64)
compat-db-4.2.52-5.1 (x86_64)
compat-libstdc++-296-2.96-138 (i386)
compat-libstdc++-33-3.2.3-61(x86_64)
compat-libstdc++-33-3.2.3-61 (i386)
control-center-2.16.0-14 (x86_64)
gcc-4.1.1-52 (x86_64)
gcc-c++-4.1.1-52 (x86_64)
glibc-2.5-12 (x86_64)
glibc-2.5-12 (i686)
glibc-common-2.5-12 (x86_64)
glibc-devel-2.5-12 (x86_64)
glibc-devel-2.5-12 (i386)
glibc-headers-2.5-12 (x86_64)
ksh-20060214-1.4 (x86_64)
libaio-0.3.106-3.2 (x86_64)
libgcc-4.1.1-52 (i386)
libgcc-4.1.1-52 (x86_64)
libgnome-2.16.0-6 (x86_64)
libgnomeui-2.16.0-5 (x86_64)
libgomp-4.1.1-52 (x86_64)
libstdc++-4.1.1-52 (x86_64)
libstdc++-devel-4.1.1-52 (x86_64)
libXp-1.0.0-8 (i386)
make-3.81-1.1 (x86_64)
sysstat-7.0.0-3 (x86_64)

The version of the above packages is the lower needed. So if you are in higher you are OK

e.g

# rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep binutils
binutils-2.17.50.0.6-14.el5 (x86_64)

5) Set the kernel parameters

kernel.shmall = physical RAM size / pagesize (getconf PAGESIZE) -- If the defaults are greater then leave it.
kernel.shmmax = 1/2 of physical RAM, but not greater than 4GB -- If the defaults are greater then leave it.
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 512 x processes (for example 65536 for 128 processes)
net.ipv4.ip_local_port_range =9000 65500
a.) The runInstaller (OUI) checks may expect this to be the old guidance of “1024 65000”. The new guidance from Oracle development is “9000 65500”. Please allow the runInstaller (OUI) to proceed with the new guidance from Oracle development.
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

# vi /etc/sysctl.conf

Set the values and then run :

# sysctl -p

6) Add Oracle user and groups and create the ORACLE_BASE directory for Oracle Software and database.

# groupadd oinstall
# groupadd dba
# useradd -g oinstall -G dba oracle
# passwd oracle

e.g
# mkdir -p /u01/app/oracle
# chown -R oracle:oinstall /u01/app/oracle

7) Set the session limits for Oracle User

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

Set the following to the /etc/pam.d/login
session required pam_limits.so

Add the following to /etc/profile if Oracle user will use the bash shell.

if [ $USER = "oracle" ]; then
ulimit -u 16384
ulimit -n 65536
fi

As oracle user
8) Login as Oracle and change the .bash_profile
Add the following in order to begin the installation
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=orcl # You can not set the ORACLE_SID if you do not want write now
export DISPLAY=YOUR_XTERM_IP:0 in order to you an X session for the installation
unset ORACLE_HOME
$ . ./.bash_profile

to apply the new settings

9) Put the software for 10gR2 to a desired server directory.

10201_database_linux_x86_64.cpio.gz
10201_companion_linux_x86_64.cpio.gz
p6810189_10204_Linux-x86-64.zip

Unzip the database and run
$ gunzip 10201_database_linux_x86_64.cpio.gz
$ cpio -idmv < 10201_database_linux_x86_64.cpio
$ cd database
$./runInstaller –ignoreSysPrereqs

Then unzip the companion and run
$ gunzip 10201_companion_linux_x86_64.cpio.gz
$ cpio -idmv < 10201_companion_linux_x86_64.cpio
$ cd companion
$./runInstaller –ignoreSysPrereqs

Finally unzip the 10.2.0.4 Patch Set and run (At the time this post was written only the 10.2.0.4 patch was available):
$ unzip p6810189_10204_Linux-x86-64.zip
$ cd Disk1
$ ./runInstaller –ignoreSysPrereqs

10) Create the database
Add the following to the .bash_profile
export ORACLE_HOME=[YOUR_ORACLE_HOME]
export PATH=$ORACLE_HOME/bin:.:$PATH

Run
$ . ./.bash_profile
$ dbca

Create your desired database and you finished
After that run

$ netca

To create a listener

ATTENTION if your host name is more than 32 characters then you are about to have the following situation:

If you have choose to install EM and during its creation you get from dbca
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

then apply the following workaround:

1) Ignore error and continue with database creation
2) After successful finish of dbca do :

Verify the case using sqlplus
SQL> select length(host_name) from v$instance where rownum=1;

LENGTH(HOST_NAME)
-----------------
36

Here the length is 36 (>32) and for 10.2 this is a bug and em repository creation fails.
So
a) Make a backup copy of the file

$ cp $ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/self_monitor/self_monitor_post_creation.sql $ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/self_monitor/self_monitor_post_creation.sql_bak

b) Change the l_host_name variable in self_monitor_post_creation.sql to have 128 characters
The l_host_name variable needs to be changed twice in the script.
$ vi $ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/self_monitor/self_monitor_post_creation.sql

c) Drop and recreate the DB Control configuration files and repository
$ $ORACLE_HOME/bin/emca -deconfig dbcontrol db -repos drop
$ $ORACLE_HOME/bin/emca -config dbcontrol db -repos create

Check your em by using the URL mentioned.

REFERENCES
Metalink Note ID 421308.1, 335118.1

Flushing and closing trace files using oradebug

2010-11-11T12:35:00.003+02:00

Lots of times we erase trace files on the $ORACLE_BASE/admin directory but the database is still open. The processes that have created these files have the inode of the file opened and thus removing the file any new information goes to it but we can not see the file until the process rotates it or close its.

We can use the oradebug utility to manual flush and close the file.

e.g

[oracle@labdb01 udump]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Nov 11 12:29:35 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> SELECT
s.sid, s.serial#, p.pid
FROM v$session s, v$process p
WHERE s.paddr=p.addr
and s.program like '%LMD%'; 2 3 4 5

SID SERIAL# PID
---------- ---------- ----------
1101 1 6

SQL> oradebug setorapid 6
Unix process pid: 17652, image: oracle@labdb01 (LMD0)
SQL> oradebug close_trace
Statement processed.
SQL> oradebug flush
Statement processed.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

In the above example we use the query to find the PID for the oracle lmd0 process.
The we use the oradebug in order to flush the data and close the file.

We can also use the O/S PID.

$ ps -ef | grep $ORACLE_SID | grep lmd

Would return the PID for Oracle background process LMD.

Now:

$ sqlplus / as sysdba
SQL>oradebug setospid
SQL>oradebug close_trace
SQL>oradebug flush
SQL>exit
Now check the file in the bdump location, a new file would have been created

Oracle 10G Composite RANGE-LIST partitioning

2010-11-10T10:59:00.002+02:00

Composite RANGE-LIST partitioned table maintenace.

1) Create

create
table mytable
(
PROC varchar2(30 byte),
ACTION varchar2(100 byte),
START_TIME date,
END_TIME date,
)
tablespace USERS enable row movement
partition by range (start_time)
subpartition by list (action)
subpartition template
(
subpartition added values ('ADD') tablespace users ,
subpartition removed values ('REMOVE') tablespace users
)
(
partition nov10 values less than (to_date('01/12/10','DD/MM/YYYY')) tablespace users,
partition dec10 values less than (to_date('01/01/11','DD/MM/YYYY')) tablespace users,
partition jan11 values less than (to_date('01/02/11','DD/MM/YYYY')) tablespace users
);

create index my_idx on mytable (ACTION) local;

All partitions are stored in the same tablespace and a local index is created too.

ORA-01450 maximum key length (num) exceeded

2010-11-10T09:21:00.003+02:00

I faced this error when i was rebuilding and index online but without the online the index rebuilds fine.

This is caused by Bug:2525767. The online rebuild of the index creates a journal table and index and the table contains more columns in its index with total length greater than number reported in ORA-01450 error message.This is a feature of online rebuild.

Maximum key length is calculated with respect to the database block size. It
means that current value of the initialization parameter db_block_size is not
large enough so that the internal journal IOT can be created without errors.

Workaround to rebuild the index offline.

Oracle Event Codes

2010-11-01T16:00:00.001+02:00

In the following link you can find all Oracle event Codes.

http://www.kevinloney.com/free/events.htm

WARNING: inbound connection timed out (ORA-3136)

2010-11-01T12:27:00.002+02:00

The "WARNING: inbound connection timed out (ORA-3136)" in the alert log indicates that the client was not able to complete it's authentication within the SQLNET.INBOUND_CONNECT_TIMEOUT time.(Defaults to 60 secs after 10.2)

Take look of the following

1. Check whether local connection on the database server is successful & quick.
2. If local connections are quick ,then check for underlying network delay with the help of your network administrator.
3. Check whether your Database performance has degraded in anyway.
4. Check alert log for any critical errors for eg, ORA-600 or ORA-7445 and get them resolved first.
These critical errors might have triggered the slowness of the database server.

Oracle 10g External Table Example

2010-10-13T16:08:00.002+02:00

If you have some temporary data to import to the database you can use an external table instead.

Lets amuse that you have the following file.

myfile.txt
1
2
3
....

1) You must create a directory and grant read writes to your user.

create or replace directory my_dir as '/home/oracle';

grant read on directory my dir to my_user;

2) Put your file there and

create table mytable_load
(
a int
)
organization external
(
type oracle_loader default directory my_dir
access parameters (RECORDS DELIMITED BY NEWLINE FIELDS)
location ('myfile.txt')
);

... and you have your table.

For more options and details check the Concepts Guide.

ORA-600 [17018] And ORA-21522

2010-10-07T08:30:00.002+02:00

In your alert log you find out : You have

ORA-00600: internal error code, arguments: [17018], [0], [], [], [], [], [], []

In the trace file you find out that :

Error on rollback: ORA-22303: type "SYS"."AQ$_RECIPIENTS" not found
ORA-21522: attempted to use an invalid connection in OCI (object mode only)
*** 2010-10-05 07:23:34.517
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17018], [0], [], [], [], [], [], []

The call stack has one of the following:

kglrls kxsReleaseLookupLoc kxsUnlock kksCloseCursor opicca
or
kglrls kxsReleaseParentLoc kxsFreeXsc kksCloseCursor opicca

The cause of this problem has been identified and verified in unpublished Bug 5444620, which is a duplicate of unpublished Bug 5675310.

There is no known workaround for this bug which is fixed in 11.1
According to development, it is not feasible to fix this issue in the 10.2

Un Indexed Foreign Keys

2010-09-28T12:15:00.002+03:00

Un indexed foreign keys is meaning a bad schema design. If you do not have index on foreign keys you will probably face situations of
1) deadlocks (http://agstamy.blogspot.com/2010/09/oracle-deadlocks.html)
2) But query plans when joining parent/child table
3) Full table scans causing locks on child tables while updating the parent table pk
4) Problem with cascade delete

There is always an overhead for too many indexes specially on tables that may be high transactional but you will not face the above situations. Bellow are too interesting scripts.

First one you can find the foreign keys referencing a list of parent tables.

select table_name,constraint_name
from dba_constraints
where owner='MY_OWNER'
and r_constraint_name in (
select constraint_name from dba_constraints
where owner='MY_OWNER'
and table_name in ('MY_TABLE','MY_TABLE2',......)
and constraint_type in ('P'));

You can then go and add indexes on the tables FKs columns.

A more automated script by Tomas Kyte.

SELECT
'create index '||owner||'.'||SUBSTR(table_name,INSTR(table_name,'_',1,1)+1)||'_idx'||TO_CHAR(rnk)||' on '||owner||'.'||table_name||'('||col1||');'
FROM (
SELECT
status,owner,table_name,col1,col2
,rnk+1 rnk
FROM(
SELECT status,owner,table_name,col1,col2
,ROW_NUMBER() OVER (PARTITION BY owner ORDER BY owner) rnk
FROM (
SELECT DECODE( b.table_name, NULL, '****', 'ok' ) Status, owner,
a.table_name, a.COLUMNS col1, b.COLUMNS col2
FROM
( SELECT SUBSTR(a.table_name,1,30) table_name, a.owner,
SUBSTR(a.constraint_name,1,30) constraint_name,
MAX(DECODE(position, 1, SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position, 2,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position, 3,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position, 4,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position, 5,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position, 6,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position, 7,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position, 8,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position, 9,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position,10,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position,11,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position,12,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position,13,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position,14,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position,15,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position,16,', '||SUBSTR(column_name,1,30),NULL)) COLUMNS
FROM dba_cons_columns a, dba_constraints b
WHERE a.owner LIKE 'MY_OWNER'
AND a.owner=b.owner
AND a.constraint_name = b.constraint_name
AND b.constraint_type = 'R'
GROUP BY a.owner,SUBSTR(a.table_name,1,30), SUBSTR(a.constraint_name,1,30) ) a,
( SELECT SUBSTR(table_name,1,30) table_name, SUBSTR(index_name,1,30) index_name,
MAX(DECODE(column_position, 1, SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 2,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 3,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 4,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 5,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 6,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 7,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 8,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 9,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,10,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,11,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,12,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,13,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,14,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,15,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,16,', '||SUBSTR(column_name,1,30),NULL)) COLUMNS
FROM dba_ind_columns
WHERE index_owner LIKE 'MY_OWNER'
GROUP BY SUBSTR(table_name,1,30), SUBSTR(index_name,1,30) ) b
WHERE a.table_name = b.table_name (+)
AND b.COLUMNS (+) LIKE a.COLUMNS || '%'
) WHERE status !='ok' ORDER BY 2
))
;

Any one can use its own naming conversion for the indexes by altering the first statement and the x on the rnk+x rnk column of the second select.

Oracle Deadlocks

2010-09-28T11:46:00.002+03:00

Recently , i had a j2ee application that suddenly caused deadlocks ORA-00060 on a test environment. After that i had to refresh my memory about deadlocks.

In general a deadlock occurs when a session (A) wants a resource held by another session (B) , but that session also wants a resource held by the first session (A). There can be more than 2 sessions involved but the idea is the same.

You will find an entry in the alert.log pointing to the trace file that has all the info regarding the deadlock , the deadlock graph,, the sessions involved , the sql statements , the rwoid of the objects waited and other info.

The most common lock types seen in deadlock graphs are TX and TM locks.It is the lock type and modes which help determine what situation has caused the deadlock.

Lock Mode
Type Requested Probable Cause
~~~~ ~~~~~~~~~ ~~~~~~~~~~~~~~
TX X (mode 6) Application row level conflict.
Avoid by recoding the application to ensure
rows are always locked in a particular order.

TX S (mode 4) There are a number of reasons that a TX lock
may be requested in S mode usually caused by waits
E.g

Waits due to Row being locked by an active Transaction
Waits due to Unique or Primary Key Constraint enforcement
Waits due to Insufficient 'ITL' slots in the Block
Waits due to rows being covered by the same BITMAP index fragment

TM SSX (mode 5) This is usually related to the existence of
or foreign key constraints where the columns
S (mode 4) are not indexed on the child table.

ID1 of a TM lock indicates which object is being locked.
The TM lock id in the form TM-AAAAAAAA-BBBBBBBB by converting AAAAAAAA from hexadecimal to a decimal number DDDD we have the object_id. Then locate the object using SELECT * FROM dba_objects WHERE object_id= DDDD;

We also have deadlocks that concerns library cache locks with error ora-04020. The first thing is to check if they are invalid objects used, and try to recompile/validate them.
Secondly, check if DDL commands are executed by different sessions at the same time, and serialize this processing.

Great info in the web
http://yong321.freeshell.org/computer/deadlocks.txt
http://www.oratechinfo.co.uk/deadlocks.html

Always
asktom.oracle.com

And
Metalink Note IDs : 62365.1 , 62354.1, 33453.1, 166924.1

Getting Milliseconds from Oracle Timestamps

2010-09-09T16:46:00.001+02:00

http://www.dba-oracle.com/t_timestamp_math_elapsed_times.htm

An example procedure based on the link calculations.

create table test (a int, b int, primary key (a,b));

declare
startt timestamp;
endt timestamp;
ms int;
begin
dbms_output.enable(1000000);
startt:=systimestamp;
for i in 1 .. 1001
loop
insert into test values (i,i+1);
commit;
end loop;
endt:=systimestamp;
select sum(
(extract(hour from endt)-extract(hour from startt))*3600+
(extract(minute from endt)-extract(minute from startt))*60+
extract(second from endt)-extract(second from startt))*1000 into ms from dual;
dbms_output.put_line(ms);
end;
/

drop table test purge;

Creating materialized view in other schema reports error ORA-01031

2010-09-09T16:09:00.001+02:00

While creating materialized view in other schema reports error ORA-01031.

The user you are using has the SELECT ANY TABLE,CREATE MATERIALIZED VIEW,CREATE ANY VIEW,CREATE ANY MATERIALIZED VIEW,CREATE ANY TABLE,CREATE SESSION.

This is due a bug. Grant explicitly the create table privilege to the user has the tables the materialized view relies.

Metalink Note Id: 749112.1

Reload Oracle JVM Oracle 10g

2010-09-03T16:11:00.003+02:00

In order to reload the Oracle JVM in an Oracle Database 10g do the following:

1) shutdown the database

2) create the following script to remove the installed JVM:

spool full_rmjvm.log
set echo on
connect / as sysdba
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter system enable restricted session;
alter database open;
@?/rdbms/admin/catnoexf.sql
@?/rdbms/admin/catnojav.sql
@?/xdk/admin/rmxml.sql
@?/javavm/install/rmjvm.sql
truncate table java$jvm$status;
select * from obj$ where obj#=0 and type#=0;
delete from obj$ where obj#=0 and type#=0;
commit;
select owner, count(*) from all_objects
where object_type like '%JAVA%' group by owner;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
select o1.name from obj$ o1,obj$ o2
where o1.type#=5 and o1.owner#=1 and o1.name=o2.name and o2.type#=29;
shutdown immediate
set echo off
spool off
exit

3) create the following script to reinstall JVM
spool full_jvminst.log;
set echo on
connect / as sysdba
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter database open;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
@?/javavm/install/initjvm.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/xdk/admin/initxml.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/xdk/admin/xmlja.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/rdbms/admin/catjava.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/rdbms/admin/catexf.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
shutdown immediate
set echo off
spool off
exit

4) Compile all invalid objects

@?/rdbms/admin/utlrp.sql

5) Check for the jvm installed

select version, status from dba_registry where comp_id='JAVAVM';

and / or

select owner, count(*) from all_objects where object_type like '%JAVA%' group by owner;

6) If you previously have installed InterMedia (ORDSYS), UltraSearch (WKSYS) Data Mining (DMSYS), Spatial (MDSYS) or (EXFSYS) then create and runt the following script

spool jvm_refresh.log
connect / as sysdba
@?/ord/im/admin/iminst.sql;
connect / as sysdba
@?/dm/admin/dminst1.sql SYSAUX TEMP ;
connect / as sysdba
@?/md/admin/mdinst.sql;
connect / as sysdba
@?/dm/admin/dminst2.sql;
connect / as sysdba
@?/ultrasearch/admin/wk0deinst.sql SYS change_on_install "";
connect / as sysdba
@?/ultrasearch/admin/wk0install.sql SYS change_on_install change_on_install
SYSAUX TEMP "" PORTAL false;
connect / as sysdba
alter user WKSYS account unlock identified by change_on_install;
@?/ultrasearch/admin/wk0config.sql change_on_install
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST=)(PORT=1521))
(CONNECT_DATA=(SERVER=DEDICATED)
(SERVICE_NAME=.)))
false " ";
spool off

7) Recompile all invalid objects as in step 4

Oracle Database 11g: The Top New Features for DBAs and Developers

2010-09-01T10:46:00.000+02:00

Oracle Database 11g:
The Top New Features for DBAs and Developers

http://www.oracle.com/technetwork/articles/sql/index-099021.html

The best series in order to understand the new features of 11g Database.

RMAN Tablespace Point-in-Time Recovery

2010-07-29T13:01:00.002+03:00

Recovery Manager (RMAN) automatic tablespace point-in-time recovery (commonly abbreviated TSPITR) enables you to quickly recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the tablespaces and other objects in the database.

Check online Oracle documentation for TSPITR limitations and how tos.

Known bugs :

TS_PITR_CHECK SHOWS MISLEADING MESSAGES WHEN STATISTICS ARE LOCK (BUG 5192382)

Workaround

1) Apply patch 5192382
2) Unlock affected table statistics.
exec DBMS_STATS.UNLOCK_TABLE_STATS ('table_owner','table_name');

TS_PITR_CHECK REPORTS "MASTER TABLE USED FOR SNAPSHOTS/REPLICATION NOT "

No workaorund

Apply patch 5192382

Ora-30078 Error Creating A Partition Table With Timestamp As The Range

2010-07-21T14:21:00.002+03:00

Partitions on column with datatype timestamp with or without tiem zone information on Oracle 9i and up(If this capability is implemented in the 11g i will edit the article).

You have the following table test and you want to partition it on the d column.
You write the following SQL:

create table test(
a varchar2(5) not null,
d timestamp not null
) partition by range (d) (
partition p1 values less than (to_timestamp('2010-08-15','YYYY-MM-DD')),
partition p_max values less than (maxvalue)
)
;

ORA-30078: partition bound must be TIME/TIMESTAMP WITH TIME ZONE literals

In order to create the table that you must write the SQL as follows:

create table test(
a varchar2(5) not null,
d timestamp not null
) partition by range (d) (
partition p1 values less than (timestamp '2010-08-15 00:00:00'),
partition p_max values less than (maxvalue)
)
;

create table succeeded.

Be careful of the nls_timestamp_format setting of your database.

If your column d was timestamp with time zone the statement would fail with :

ORA-03001: unimplemented feature

In 11g

But if you use the local time zone for column d

create table test(
a varchar2(5) not null,
d timestamp with local time zone not null
) partition by range (d) (
partition p1 values less than (timestamp '2010-08-15 00:00:00 +00:00'),
partition p_max values less than (maxvalue)
)
;

create table succeeded.

Be carefull that when you use the local time zone :
1 Data is normalized to the database time zone when it is stored in the database.
2 When the data is retrieved, users see the data in the session time zone.

ORA-12012

2010-07-14T13:08:00.003+03:00

ORA-12012: error on auto execute of job [num]

If this is a job created with the dbms_job package then you can find information using the following select

select * from dba_jobs where job=[num];

If you have created the job using the dbms_scheduler then execute the following:

select obj# , object_name
from sys.scheduler$_job a, dba_objects b
where a.obj# = b.object_id
and a.obj#=[num];

The using the EM Dbconsole you can find more details about the job.

ORA-959 Tablespace '_$deleted$0$0' Does Not Exist or users has '_$deleted$0$0' as default tablespace.

2010-06-04T16:53:00.007+03:00

You definitely have seen this if you use OEM tablespace reorganize feature using rename tablespace method.

The case is that a tablespace is renamed to one that use to exist on the database. Here are the steps that will cause this problem.

create tablespace aa
create tablespace bb
drop tablespace aa
rename bb to aa

Then why it occurs is because when a tablespace is dropped, its ts$ entry is set to an invalid state, but it is not deleted. When a rename is done, if the target name already has a ts$ entry, then during the rename, because we cannot have two ts$ entries with the same name, the _$deleted$ construct is introduced. The change is not taken care of properly, and is flushed to other dictionary tables causing this problem.

It also counts for temporary tbs.

Confirmed on to 10.2.0.3 and 10.2.0.4

Solution

1) Apply Patch 5769963

2) Using SQL

Alter the user to have as default/temp tablespace the new tbs
If an object is offended such and index partition or table partition default tbs then alter the object.
Check
http://agstamy.blogspot.com/2009/12/ora-00959-tablespace-deleted00-does-not.html

References
Oracle Metalink Notes 604648.1, 789023.1

IBM Technical Articles for AIX

2010-06-01T17:54:00.001+03:00

IBM Technical Articles for AIX

http://www.ibm.com/developerworks/views/aix/library.jsp

Backup Job fails with Agent unreachable when agent in one of rac nodes is down.

2010-05-27T13:39:00.001+03:00

Please run the below procedure connecting as SYSMAN to the repository database

BEGIN
EMD_RAC.REGISTER_AVAILABILITY( 'rac_database', 'Response', 'Status', 'oracle_database','3.1');
COMMIT;
END;

Bounce the DB Control.

The Cpu & Active Sessions Charts Show "No Data Is Currently Available" For Cluster Database

2010-05-27T12:49:00.003+03:00

On the EM dbconsole home page the Cpu & Active Sessions Charts Show "No Data Is Currently Available" For Cluster Database. In emagent.trc you find the following lines:

emagent.trc shows:-
2008-07-02 09:06:10 Thread-4124015504 WARN TargetManager: Query returned 0 rows (only one expected) for the dynamic property DGPrimaryDBName
2008-07-02 09:06:10 Thread-4124015504 WARN TargetManager: The execution of the query for DGPrimaryDBName in {RACDB, rac_database} is missing the following required property values : DGPrimaryDBName

This is can happen after a RAC node reboot.

To fix this issue you must apply patch for BUG:6005707

The following workaround is available:

$ORACLE_HOME/bin/emctl reload agent dynamicproperties [targetname]:[targettype]

eg.

emctl reload agent dynamicproperties RACDB:rac_database

Wait 5 minutes , after this the charts should be visible.

10gR2 RAC ORA-00600 kgeade_is_0

2010-05-20T10:18:00.002+03:00

Full Error on alert.log :
ORA-00600: internal error code, arguments: [kgeade_is_0], [], [], [], [], [], [], []

Line from trace file :
select tablespace_id, rfno, allocated_space, file_size, file_maxsize, changescn_base, changescn_wrap, flag from GV$FILESPACE_USAGE where inst_id != :inst and (changescn_wrap >= :
w or (changescn_wrap = :w and changescn_base >= :b))

Having the initialization parameter PARALLEL_EXECUTION_MESSAGE_SIZE set to a different value on one or more instances in the cluster could cause an error when querying the gv$ views.

Solution

Make the parameter same across all RAC instances.

ALTER SYSTEM SET parallel_execution_message_size=2148 SCOPE=SPFILE SID='*';

Restart instance having this errors !!!

Oracle Blogs on the WEB

2010-05-18T17:21:00.001+03:00

One of the coolest blogs about Oracle in the Web.

http://jarneil.wordpress.com/

Oracle 10g RAC articles

2010-05-13T15:38:00.001+03:00

A nice collection of articles concerning Oracle 10g RAC from database journal.

http://www.databasejournal.com/article.php/3559996

CRS-1006 : No more members to consider

2010-04-22T10:28:00.002+02:00

When trying to start a service that in crs_stat -t seems offline.

ora.DB.MYSRV.DB1.srv ONLINE OFFLINE on node1

Trying to stop the service saids that the service is stopped.
Trying to remove the service saids that the service is running.

The you have to stop the service manually from each instance.

On each instance do.

sqlplus / as sysdba
show parameter service

-- If you see you service listed there then do

exec DBMS_SERVICE.STOP_SERVICE('MYSERVICE','MYINSTANCE');

show parameter service

-- The service must have been disappeared now.

After you complete the following procedure then start the service again on the preferred instance.

References

http://forums.oracle.com/forums/thread.jspa?messageID=3626152
http://surachartopun.com/2009/03/srvctl-start-service-prkp-1030-failed.html

11g new features Articles

2010-04-15T14:26:00.002+02:00

A very nice collection of Articles for the 11g new features in ORACLE-BASE.

http://www.oracle-base.com/articles/11g/Articles11g.php

ORA-00005

2010-03-09T18:20:00.003+02:00

Usually this error comes from Application side in the form :

ORA-00005:
Message 5 not found; product=RDBMS; facility=ORA
ORA-06512: at "SYS.DBMS_UTILITY", line 70
ORA-06512: at line 1

and its related with the timezones that are supported on the database.
Oracle has 2 Timezone files on the $ORACLE_HOME/oracore/zoneinfo/ directory.

timezone.dat -- small file
timezlrg.dat -- large file

From 9.2.0.5 onwards, $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat is the default timezone file and will be used if ORA_TZFILE parameter is not set.

if you use on the application side a timezone that is not in the timezlrg.dat then this error is raised.

You can query this view by to see the timezones with:

select *
from v$timezone_names;

Finding CRS Master Node

2010-02-11T17:11:00.002+02:00

As Metalink Document 220970.1(RAC: Frequently Asked Questions) states:
"For the cluster synchronization service (CSS), the master can be found by searching $ORACLE_HOME/log/cssd/ocssd.log" Where ORACLE_HOME is the CRS Home.

e.g
Use olsnodes -n to find out the node numbers:

nodea 1
nodeb 2
nodec 3

Then on the directory mentioned check for each node the latest entry :
e.g from nodec (3)
grep MASTER ocsdd.log
ocssd.log:[ CSSD]2010-02-05 03:26:03.320 [3861] >TRACE: clssgmEstablishMasterNode: MASTER for 63 is node(2) birth(58)

This tell as that for incarnation 63 the master node is 2

But is this the last incarnation ??? You must also check the other nodes to see.

For more accurate information (always combined with the previous method) check the crsd.log

cd $ORACLE_HOME/log/crsd/crsd.log

2010-02-05 02:32:32.139: [ OCRMAS][3348]th_master:13: I AM THE NEW OCR MASTER at incar 67. Node Number 3

This means that we had another incarnation and the master node is 2.

Confusing a little beat. This is happening because the logs rotate or are write in a matter of events happening between the nodes.

So for my opinion please check the crsd.log from each node and the master is the one in the latest incarnation.

e.g

From node1 crsd.log :
2010-02-02 13:05:44.424: [ OCRMAS][3348]th_master:13: I AM THE NEW OCR MASTER at incar 62. Node Number 1

From node2 crsd.log :
2010-01-18 09:58:19.932: [ OCRMAS][3352]th_master:13: I AM THE NEW OCR MASTER at incar 53. Node Number 2

From node3 crsd.log :
2010-02-05 02:32:32.139: [ OCRMAS][3348]th_master:13: I AM THE NEW OCR MASTER at incar 67. Node Number 3

So the master node is 3 !!!

You can also check with :

ocrconfig -showbackup

where the latest backup is, that's the master node. But remember that by default ocrbackups are taken every 4 hours.

Collecting CRS diagnostic files 1OgR2

2010-02-05T18:26:00.003+02:00

In order to collect all crs all at once ou can run the following command :

As root :

diagcollection.pl --collect --crs --crshome [$ORA_CRS_HOME]

e.g

diagcollection.pl --collect --crs --crshome /u01/app/oracle/product/crs

ORA-27061: waiting for async I/Os failed on AIX

2010-01-19T15:46:00.006+02:00

When you see this error and the database crashes , hangs or does not shutdown/startup then there is a problem with Async I/O servers on AIX.

Syptomps

The following messages in the alert log
-------------------------------------------------------------------
.............
Mon Oct 8 22:57:05 2007
Errors in file /dwrac/BDUMP/dwhp_p009_1941690.trc:
ORA-27061: waiting for async I/Os failed <<<<
IBM AIX RISC System/6000 Error: 22: Invalid argument

Users affected:
* systems running aix 5.3 with the bos.rte.aio fileset at the 5.3.0.60 or 5.3.0.61 level.
* this problem is known to affect oracle.

Problem description:
When the maximum requests (maxreqs) is exceeded, lio_listio will update the control blocks, but may not return an error to indicate that some i/os were not started. this results in an unexpected einval from aio_nwait later when these i/os are waited for.

* errors similar to the following may be logged by oracle:
* ora-27061: waiting for async i/os failed
* ibm aix risc system/6000 error: 22: invalid argument

There are 3 actions to try :

1) Increase the number of aios and the maxreqs

Check aio the current settings:
aioo -a
or
lsattr –El aio0

Set maxreqs at least 8192.

chdev -P -l aio0 -a maxreqs=8192
or
aioo -p -o maxreqs=8192 (AIX 5.3 and up)

If this does not resolve the problem increase the number of min/max aio servers

chdev -P -l aio0 -a maxservers=[new value] -a minservers=[new value]

To make changes persistance on reboot

aioo -p -o maxservers=[new value] -o minservers=[new value]

To apply new values online (AIX 5.3 and up)

2) Decrease the parallel_min_servers and parallel_max_servers on the failing instance.

FOR RAC

ALTER SYSTEM SET parallel_min_servers = [NUM] SCOPE=BOTH SID='MYINSTANCE1';
ALTER SYSTEM SET parallel_max_servers = [NUM] SCOPE=BOTH SID='MYINSTANCE1';

FOR SINGLE DB
ALTER SYSTEM SET parallel_min_servers = [NUM] SCOPE=BOTH;
ALTER SYSTEM SET parallel_max_servers = [NUM] SCOPE=BOTH;

3) IBM FIX for AIX 5.3 ML6 and UP
Install fix for apar iz03260

REFERENCES
Metalink Note 464768.1
http://www-01.ibm.com/support/docview.wss?uid=isg1IZ03260

Oracle 10g REDO LOGS sizing

2010-01-15T11:56:00.003+02:00

The size of the redo log files can influence performance, because the behavior of
the dbw and arc processes depend on their sizes.
Larger redo log files provide better performance but instance recovery time is increased. Sou you must find a good balance depending the type of your system and your instance recovery time needed.

Also be careful that undersized log files increase checkpoint activity and increase CPU usage.Checkpoint frequency is affected by several factors, including log file size and
the setting of the FAST_START_MTTR_TARGET.

If the FAST_START_MTTR_TARGET parameter is set to limit the instance recovery time,
Oracle automatically tries to checkpoint as frequently as necessary.
Under this condition, the size of the log files should be large enough to avoid
additional checkpointing due to under sized log files.

In Oracle 10g the redo logfile size can tuned as per recommendation specified by column optimal_logfile_size of v$instance_recovery or in the EM Dbconsole under Administration Page under Storage on the Redo Log Groups Link on the Actions List.

In order to enable this feature you must set the parameter "fast_start_mttr_target" in order to activate the advisory and the population of the column optimal_logfile_size.

The key columns in v$instance_recovery view are :

ACTUAL_REDO_BLKS
The current actual number of redo blocks required for recovery.

TARGET_MTTR
Effective MTTR (mean time to recover) target value in seconds. The TARGET_MTTR value is calculated based on the value of the FAST_START_MTTR_TARGET parameter and is usually an approximation of the parameter's value. However, if the FAST_START_MTTR_TARGET parameter value is very small (for example, one second), or very large (for example, 3600 seconds), the calculation will produce a target value dictated by system limitations. In such cases, the TARGET_MTTR value will be the shortest calculated time, or the longest calculated time that recovery is expected to take.

ESTIMATED_MTTR
The current estimated mean time to recover (MTTR) based on the number of dirty buffers and log blocks (0 if FAST_START_MTTR_TARGET is not specified). Basically, this value tells you how long you could expect recovery to take based on the work your system is doing right now.

OPTIMAL_LOGFILE_SIZE
The recommended optimal redolog file size for the current setting of FAST_START_MTTR_TARGET.

select ACTUAL_REDO_BLKS,TARGET_MTTR,ESTIMATED_MTTR,OPTIMAL_LOGFILE_SIZE
from v$instance_recovery;

The target is to reduce ACTUAL_REDO_BLKS by setting an appropriate value of FAST_START_MTTR_TARGET and finding the OPTIMAL_LOGFILE_SIZE for the work your system does.

Setting db_file_multiblock_read_count

2009-12-23T16:27:00.002+02:00

A very nice procedure to see the effects on a full table scan query and to estimate the value for db_file_multiblock_read_count.

DECLARE
l_count PLS_INTEGER;
l_time PLS_INTEGER;
l_starting_time PLS_INTEGER;
l_ending_time PLS_INTEGER;
BEGIN
dbms_output.put_line('dbfmbrc seconds');
FOR l_dbfmbrc IN 1..32
LOOP
EXECUTE IMMEDIATE 'ALTER SESSION SET db_file_multiblock_read_count='||l_dbfmbrc;
l_starting_time := dbms_utility.get_time();
SELECT /*+ full(t) */ count(*) INTO l_count FROM big_table t;
l_ending_time := dbms_utility.get_time();
l_time := round((l_ending_time-l_starting_time)/100);
dbms_output.put_line(l_dbfmbrc||' '||l_time);
END LOOP;
END;
/

Table must be enough big in order not to be cached !!!

Taken by an excellent book (Apress:Troubleshooting Oracle Performance)

ORA-19588

2009-12-07T11:22:00.001+02:00

RMAN backup of archivelogs fails:

ORA-19588: archived log recid xxxx stamp xxxxxxxx is no longer valid

The rman backup job had been started TWICE.

The initial resync for each job identified the same set of logs to be backed up.
Both jobs were trying to backup and delete the SAME archivelog set, one of which failed eventually as the other job had already backed up and deleted the log.

Make sure that concurrent backups of the archivelogs are not run.

CTAS and column default values

2009-12-04T12:28:00.002+02:00

Affecting 8i up to 10gR2

If you use CTAS ( Create Table As Select ) to copy a table, the new duplicate table does not contain the default values of the original table. As a result, if you use DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS, it does not copy column's default values.

To implement the solution, please execute the following steps:

1) Do not use CTAS to copy the table, use another method.

2) Use CTAS and then modify columns giving them their default values where necessary.

3) If you want the column's default values to be copied when using DBMS_REDEFINITION, the table has to be pre-created with the default column values before getting re-organized with DBMS_REDEFINITION.

Manually purge scheduler jobs logs

2009-12-04T11:44:00.000+02:00

From sqlplus as sys or system run :

exec DBMS_SCHEDULER.PURGE_LOG();

ORA-00959: tablespace '_$deleted$0$0' does not exist

2009-12-04T11:35:00.003+02:00

If this error occurs when you try to add a partition on partition table then all the table partitions had once been moved to another tablespace and the original tablespace has now been dropped or new tablespace where partitons now reside has been renamed to the original tablespace name.

Issue

alter table [table_name] modify default attributes tablespace [tablespace name];

Oracle Security Options

2009-12-02T15:43:00.001+02:00

A nice FAQ for Oracle Security

http://www.orafaq.com/wiki/Oracle_database_Security_FAQ

Oracle 10g : View bind variables values without tracing

2009-12-02T13:25:00.002+02:00

In 10g there is a new view called: V$SQL_BIND_CAPTURE

V$SQL_BIND_CAPTURE displays information on bind variables used by SQL cursors.Each row in the view contains information for one bind variable defined in a cursor such as :

1) Reference to the cursor defining the bind variable
(hash_value, address) for the parent cursor and (hash_value, child_address) for the child cursor.
2) Bind metadata
Name, position, datatype, character set ID, precision, scale, and maximum length of the bind variable.

3) Bind data
One of the bind values used for the bind variable during a past execution of its associated SQL statement.

Bind values are not always captured for this view. Bind values are displayed by this view only when the type of the bind variable is simple (this excludes LONG, LOB, and ADT datatypes) and when the bind variable is used in the WHERE or HAVING clauses of the SQL statement( This excludes update and insert statements).

You can query this view using sql_id or hash_value for an sql statement.

e.g

1) Find has_value for your statement

select sql_text,sql_id,hash_value from v$sqlarea
where lower(sql_text) like '%[my statement]%';

2) FInd bind variables values

SELECT name,datatype_string,was_captured,last_captured,value_string,value_anydata
FROM v$sql_bind_capture
WHERE hash_value='[hash_value_from_previous_sql]';

ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key

2009-11-27T14:13:00.003+02:00

As Oracle documentation states that "none of the columns in the unique/primary key can be of LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, OBJECT, REF, TIMESTAMP WITH TIME ZONE, or user-defined type. However, the unique/primary key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE".

So if you have a table that has a TIMESTAMP WITH TIME ZONE column this column cant have or be part of a unique/primary key constraint. In order to bypass this you can create a unique index including this column.

e.g

create table test (a int primary key,b int, c timestamp with time zone);

alter table test add constraint test_u01 unique (b,c);

ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key

create unique index test_u01 on test(b,c);

Index created !!!

Date and time arithmetics with interval

2009-11-26T11:22:00.001+02:00

-- Add five years to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '5' year FROM dual;

-- Add five months to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '5' month
FROM dual;

-- Add five days to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '5' DAY
FROM dual;

-- Add five hours to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '5' hour
FROM dual;

-- Add five hours and 10 minutes to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '5:10' hour to minute
FROM dual;

-- Add 10 minutes to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '10' MINUTE
FROM dual;

-- Add 10 minutes and 30 seconds to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '10:30' MINUTE TO SECOND
FROM dual;

-- Add 30 seconds to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '30' SECOND
FROM dual;

Oracle 10g flashback table feature

2009-11-25T11:47:00.003+02:00

FLASHBACK TABLE enables to recover a table to a point in time in the past without restoring a backup.The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system.You cannot restore a table to an earlier state across any DDL operations that change the structure of the table.You cannot roll back a FLASHBACK TABLE statement but you can issue another FLASHBACK TABLE statement and specify a time just prior to the current time. Its crucial to record the current SCN before issuing a FLASHBACK TABLE clause.

In order a user to flash back a table to an earlier SCN or timestamp, or before drop you must have either :
1.FLASHBACK object privilege on the table or the FLASHBACK ANY TABLE system privilege.
2. SELECT, INSERT, DELETE, and ALTER object privileges on the table.
3.Row movement must for tables in the Flashback list.

e.g
create table test (a int);
alter table test enable row movement;
insert into test values(1);
insert into test values(2);
insert into test values(3);
commit;

select dbms_flashback.get_system_change_number from dual;

8657840224 -- SCN before delete

delete from test where a=3;

commit;

select dbms_flashback.get_system_change_number from dual;

8657840556 -- SCN after delete

select * from test;
1
2

flashback table test to scn 8657840224; -- Restore table to before delete !!!

select * from test;

1
2
3

flashback table test to scn 8657840556; -- Restore table to after delete !!!

select * from test;

1
2

flashback table test to timestamp(systimestamp - interval '3' minute);

select * from test;

1
2
3

Now we will drop the table and flashback from recycle bin.

drop table test;

flashback table test to before drop;

select * from test;

1
2

And as you can see we can go back again !!!

flashback table test to scn 8657974029;

select * from test;

1
2
3

If you specify the user-specified name, and if the recycle bin contains more
than one object of that name, then the database retrieves the object that
was moved to the recycle bin most recently.

drop table test;

create table test (a int);
alter table test enable row movement;
insert into test values(1);
commit;

select * from test;
1

drop table test;

flashback table test to before drop;

select * from test;
1

flashback table test to before drop rename to test1;

select * from test1;
1
2
3

Now lets take a look information recycle bin has.

drop table test1;

select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';

owner object_name original_name operation can_undrop can_purge
--------------------------------------------------------------------
AGIS BIN$eTA7dNdz5FfgQAkKeIc6fw==$0 TEST1 DROP YES YES

purge recyclebin; -- Clear recycle bin

select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';

no rows selected

flashback table test1 to before drop;

ORA-38305 Object not in RECYCLE BIN.

drop table test; -- Put table to recycle bin

select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';

OWNER OBJECT_NAME ORIGINAL_NAME OPERATION CAN CAN
------------------------------ ------------------------------ -------------------------------- --------- --- ---
AGIS BIN$eTA7dNd05FfgQAkKeIc6fw==$0 TEST DROP YES YES

flashback table test to before drop;

select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';

no rows selected

drop table test purge; -- Permanently drop table

select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';

no rows selected

Amazing 10g recovering features with no backup recover !!!!!

Reorginize table online using primary key

2009-11-24T18:09:00.002+02:00

From Oracle9i forward, table re-organizations and redefinitions can be performed online.

What can be Redefined ONLINE on a Table?

1) A non-partitioned table can be converted into a partitioned table, and
vice versa
2) The organization of a table can be changed from a heap based to IOTs and vice versa
3) Non-primary key columns can be dropped,although this can be also done with sql
4) New columns can be added to a table,although this can be also done with sql
5) Existing columns can be renamed,although this can be also done with sql
6) Parallel support can be added or removed,although this can be also done with sql
7) Storage parameters can be modified,although this can be also done with sql

Restrictions

The table to be re-organized:

1) Cannot have User-defined data types
2) Cannot have FILE or LONG columns
3) Cannot be clustered
4) Cannot be in the SYS or SYSTEM schema
5) Cannot have materialized view logs and/or materialized views defined on them
6) Must be re-organized within the same schema

Example

Orig table : Table with hash 256 partitions , with pk , unique index , and fk
Target table : Non partitioned table

Connect as user with the privileges mentioned above.

1) Check if ORIG_TABLE can be redifined online
exec DBMS_REDEFINITION.can_redef_table('MYSCHEMA','ORIG_TABLE');
If procedure fails check ora error and apply solution if possible.

2) Create interim table

create table I_ORIG_TABLE as select * from ORIG_TABLE where 1=2;

3) Start online redefinition

exec DBMS_REDEFINITION.START_REDEF_TABLE('MYSCHEMA','ORIG_TABLE','I_ORIG_TABLE');

4) Add constraints and indexes

ALTER TABLE MYSCHEMA.i_ORIG_TABLE ADD (
CONSTRAINT I_ORIG_TABLE_PK
PRIMARY KEY
(PK_COLUMN));

ALTER TABLE MYSCHEMA.i_ORIG_TABLE ADD (
CONSTRAINT i_ORIG_TABLE_FK
FOREIGN KEY (REF_COL)
REFERENCES MYSCHEMA.OTHER_TABLE (PK_COL)
DEFERRABLE INITIALLY IMMEDIATE);

alter table i_ORIG_TABLE MODIFY CONSTRAINT i_ORIG_TABLE_FK
DISABLE KEEP INDEX;

CREATE UNIQUE INDEX MYSCHEMA.i_ORIG_TABLE_IDX01 ON MYSCHEMA.ORIG_TABLE
(COL1, COL2);

5) Sync interim with original table

exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('MYSCHEMA','ORIG_TABLE','I_ORIG_TABLE');

Do this a lot of times before finish redefinition in order to apply dml during reorganization.

6) Find orig_table grants

select * from DBA_TAB_PRIVS where table_name='ORIG_TABLE';

Give the same grants to the users

e.g

grant select on I_ORIG_TABLE to user;

7) Finish redefinition

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('MYSCHEMA','ORIG_TABLE','I_ORIG_TABLE');

8) Drop old table and rename constraints and indexes.

drop table I_ORIG_TABLE cascade constraints purge; -- At this point old ORIG_TABLE has been renamed to interim table name.

alter table ORIG_TABLE rename constraint I_ORIG_TABLE_PK to ORIG_TABLE_PK;

alter index I_ORIG_TABLE_PK rename to ORIG_TABLE_PK;

alter table ORIG_TABLE rename constraint i_ORIG_TABLE_FK to ORIG_TABLE_FK;

alter index I_ORIG_TABLE_IDX01 rename to ORIG_TABLE_IDX01;

9) Gather statistics if needed

begin
dbms_stats.gather_table_stats(ownname=>'MYSCHEMA',tabname=>'ORIG_TABLE',cascade=>true,estimate_percent=>5);
end;
/

Now you have an new unpartitioned table with all constraints and indexes and without partitions.

ORA-27477 Creating a scheduler job.

2009-11-17T11:34:00.002+02:00

When the name of the job is the same with an object name in the same schema of the creator of the job you get the ORA-27477 %s.%s already exists error in the EM job page.

Change the job name in order not to match an existing schema object name.

Oracle row migration and chaining

2009-11-12T12:39:00.000+02:00

A very nice article about row migration and chaining.

http://www.akadia.com/services/ora_chained_rows.html

enq: TX - allocate ITL

2009-11-11T18:12:00.003+02:00

When you see this enqueue wait event in the dbconsole performance page or in the top 5 timed waits event seems that too many concurrent DML transactions are competing for the same data block.

Usually an increase in the value of initrans or maxtrans for the table and the indexes fixes the problem.

You can also try to move the table to a tablespace with smaller block size and reduce the parallel degree of the table and indexes.

Oracle memory usage AIX

2009-11-11T15:07:00.003+02:00

The following link contains a collection of scripts that calculate oracle memory usage on AIX.

http://rapidshare.com/files/305471341/mem.rar

The file mem.rar contains:

instance_mem_use.sh
This script is used to measure memory usage of oracle instance asm instance , background processes , dedicated processes and also dbconsole processes.

Usage: instance_mem_use.sh dbsid dbname

In most cases dbsid=dbname but if you have a RAC node the dbsid differs from dbname.

Sample output :

/home/oracle $./instance_mem_use.sh RMINTPRD2 RMINTPRD
Memory Analysis for RMINTPRD2

Oracle Instance Memory
-----------------------------------------------------
System Global Area Memory Usage :5008MB
Program Global Area Memory Usage :500MB
-----------------------------------------------------

Oracle Processes Memory
-----------------------------------------------------
ORACLE Background Processes : 29
Memory Usage : 435MB

ORACLE Dedicated Processes : 154
Memory Usage : 1318MB

ORACLE dbconsole Processes : 1
Memory Usage : 5MB
-----------------------------------------------------

#####################################################
RMINTPRD2 Total Memory Usage :7268MB
#####################################################

other_mem_use.sh

This script is used to measure LISTENER,RMAN or CRS processes.

Usage: other_mem_use.sh LISTENER or CRS or RMAN

Sample otuput :

/home/oracle $ ./other_mem_use.sh CRS

-----------------------------------------------------
ORACLE CRS Processes : 18
CRS Total Memory Usage :202MB
-----------------------------------------------------

These scripts are used for the caclucations.

mem.awk
mem_calc.awk

References

Metalink note 123754.1 : AIX: Determining Oracle memory usage on AIX

Oracle on AIX Memory Issues

2009-11-10T12:16:00.003+02:00

When using Oracle on AIX and while time passes database gets slower you must look the amount of memory that AIX uses for filesystem cache. This is not the fact if you user raw devices or filesystem accessed by CIO or DIO.

Examples from AIX 5.3

As root :

#svmon -G

svmon -G
size inuse free pin virtual
memory 4096000 3483045 612955 426604 3312529
pg space 2064384 62882

work pers clnt
pin 426507 0 97
in use 3306854 22 176169

PageSize PoolSize inuse pgsp pin virtual
s 4 KB - 1297893 62882 361900 1127377
m 64 KB - 136572 0 4044 136572

Here the memory used for filessystem cache is : ((176169+22)/4096000)*100 = 4%

My settings on this system are :

maxperm% = 10
minperm% = 5
strict_maxperm = 1
maxclient% = 10
strict_maxclient = 1
lru_file_repage = 0
v_pinshm = 0
maxpin% = 80

And LOCK_SGA set to false.

These settings was proposed by sysadmin having experience on AIX 5.3.
Although i was recommend to use v_pinshm=1 and LOCK_SGA=true we are testing the previous settings and we are having no problems.

So my recomendation is to start with the settings i post in discuss with your sysadmin and if you do not have the optimal performance try v_pinshm=1 and LOCK_SGA=true.

Server configuration :

3 Node RAC using shared RAW Devices and ASM plus GPFS and NFS for backup.
Each server has 16Gb of RAM and 2 RAC database instances each.

You can also check the following IBM resources.

VMM Tuning Tip: Protecting Computational Memory
http://www.ibm.com/developerworks/wikis/download/attachments/53871915/VMM+Tuning+Tip+-+Proctecting+Comp+Memory.pdf?version=2

AIX 5.3 Peformance Mangement Guide
http://publib.boulder.ibm.com/infocenter/pseries/v5r3/topic/com.ibm.aix.prftungd/doc/prftungd/prftungd.pdf

Configuring IBM General Parallel File System (GPFS) with Oracle RAC http://www.ibm.com/servers/enable/site/peducation/wp/zb726/zb726.pdf

Optimizing AIX 5L performance: Tuning your memory settings
http://www.ibm.com/developerworks/views/aix/libraryview.jsp?search_by=Tuning+your+memory+settings

Support for pinned memory
http://publib.boulder.ibm.com/infocenter/systems/index.jsp?topic=/com.ibm.aix.prftungd/doc/prftungd/support_pinned_mem.htm

Oracle lock_sga parameter tips

2009-10-29T12:44:00.001+02:00

A nice article by Burleson Consulting

http://www.dba-oracle.com/p_lock_sga.htm

Stop dbconsole job hung in status Stop Pending

2009-10-26T14:56:00.002+02:00

Some times a job run from EM Console usually backup when you try to stop it it has a status : "Stop Pending". Until this status changes you can not delete the job and the job may hung on this status. If this happens do the following :

Loging as sysman :

select job_id, job_name, job_owner from mgmt_job where job_name like '%%';

exec mgmt_job_engine.stop_all_executions_with_id('');

select EXECUTION_ID, status from MGMT_JOB_EXEC_SUMMARY
where job_id ='' ;

Stopped status=8 , but you may see other statuses. If so check for EM the job status. If its still Stop Pending then force the stop

exec mgmt_job_engine.stop_all_executions_with_id('',TRUE);

Now check the status either by running the query or using the EM and the delete the job from EM console

Clearing dbconsole agent state

2009-10-22T09:22:00.002+02:00

Some times dbconsole fails to start if it has crashed unexpected. Mostly this is an agent issue that can not start or can not upload some files. Then you can try to clearstate agent.

export ORACLE_HOME=AGENT_HOME. This is usually the db home if you are using dbconsole.
If you are using Grid Control Agent has its own home.

emctl stop agent.

cd $ORACLE_HOME/host_SID/sysman/emd

rm agntstmp.txt lastupld.xml
rm upload/*
rm state/*
emctl clearstate agent
emctl start agent

After few minutes issue

emctl status agent

and you must see a similar output :

Last successful upload : 2009-10-22 07:31:47
Total Megabytes of XML files uploaded so far : 4.42
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 24.94%

And you are ok.

Primary Keys , Numbers or Characters

2009-10-19T14:54:00.001+02:00

A very good article about this battle on ASK Tom Site.

Surrogate versus Natural Keys
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:689240000346704229

Identifying a Primary Key
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:708398700346544598

Oracle 10gR2 and XML

2009-10-15T11:47:00.001+02:00

A very interesting article on OTN about XML features.

http://www.oracle.com/technology/pub/articles/quinlan-xml.html

Stop mview from auto refreshing

2009-10-07T10:17:00.002+02:00

A materialized view create with the automatic refresh can not be alter to stop refreshing. In order to do that you must broke the dbms_job that was created in order to refresh the view.

Connect as mview owner.

select * from user_jobs where broken ='N';

In the WHAT column for the mview refresh job you will see :

dbms_refresh.refresh('"[OWNER]"."[MVIEW_NAME]"');

Take the JOB_ID form the job column and execute:

begin
dbms_job.broken(JOB_ID,TRUE);
commit;
end;
/

And the mview will stop refreshing automatically.

If you want the mview to start refreshing again just run the job.

begin
dbms_job.run(JOB_ID);
commit;
end;
/

Then the mview will refresh and continue refreshing by the interval.

ORA-600 [32695] [hash aggregation can't be done] 10.2.0.3

2009-09-14T12:37:00.002+02:00

Bug 5893340 : ORA-600 [32695] [hash aggregation can't be done] can occur
for a GROUP BY query if hash aggregation is chosen.
Fixed on 10.2.0.4 and up.

But

Bug 6471770 :

Group-By queries can fail with ora-32690 or
ORA-600 [32695] [hash aggregation can't be done]
when operating on a large volume of data if hash group by
aggregation is used.

Also, it is possible for hash group-bys to over allocate memory beyond
the limit impose by the auto-memory manager.

Fixed in 10.2.0.5 and up

In order to avoid the erros from both bugs :

1) Disable Hash group-by by setting
"_gby_hash_aggregation_enabled" to FALSE,

2) Use NO_USE_HASH_AGGREGATION hint on your query