Wednesday, December 21, 2011

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

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

Tuesday, November 15, 2011

DBConsole Login Error Message: Element Not Found

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


Friday, November 4, 2011

Spliting a very large table into pieces using rowid nonverlapping ranges

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"

Wednesday, November 2, 2011

Parallel transaction recovery and SMON Monitoring

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




Tuesday, October 18, 2011

Change ASM sys password (10gR2)

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>

Thursday, October 6, 2011

R.I.P Steve Jobs

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

Wednesday, September 21, 2011

Oracle Database Security

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

Very useful.

Wednesday, August 31, 2011

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

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


Wednesday, July 20, 2011

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


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.

Monday, July 18, 2011

Restoring Voting Disk and OCR files from backup 10gR2


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

Friday, July 15, 2011

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


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

Thursday, July 14, 2011

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

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/


Wednesday, July 6, 2011

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


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

Sunday, July 3, 2011

Oracle 10gR2 CRS diagwait to avoid OPROCD node evictions

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


  1. Execute as root
    #crsctl stop crs
    #<CRS_HOME>/bin/oprocd stop
  2. 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.
  3. 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
  4. 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
  5. Restart the Oracle Clusterware on all the nodes by executing:
    #crsctl start crs
  6. 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

Wednesday, June 8, 2011

Oracle 10gR2 Secure External Password Store

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

Thursday, May 26, 2011

Installing Oracle Grid Control 10.2.0.5 on RHEL 5.X


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.

Tuesday, May 24, 2011

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

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.





Wednesday, May 11, 2011

CleanUp Temporary Segments Occupying Permanent Tablespace

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

Tuesday, May 10, 2011

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

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 Tables Topic : Altering Columns Note: 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.

Thursday, April 14, 2011

Constraints : FKs influence in performance , Deferrable Decleration

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.html About 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

Friday, March 18, 2011

Oracle 11gR2 on AIX Memory Issue after Upgrade

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

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

Monday, March 14, 2011

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

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

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.

Wednesday, March 2, 2011

Exchanging Partitions Example

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;

Tuesday, March 1, 2011

ORA-00054 Resource busy and acquire with NOWAIT specified

A very nice entry for Oracle lock analysis

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

Friday, February 25, 2011

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

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

Wednesday, February 9, 2011

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

Tuesday, February 8, 2011

Thursday, February 3, 2011

TSPITR Fails with ORA-29308: view TS_PITR_CHECK failure

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.

Tuesday, January 25, 2011

TSPITR using restore point on 10gR2 fails

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';
}

Thursday, January 13, 2011

ORA-01666 When Trying to Activate Standby Database

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

Wednesday, January 5, 2011

11g RAC Install on Centos VM

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

Monday, January 3, 2011

ORA-19573: cannot obtain exclusive enqueue for datafile

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.