Shared NFS mount point for Real application testing (RAT) on RAC

Hi All,

I was trying to test the DB replay process from my 2 node RAC (11gR2) test machine set up on Oracle virtualbox environment . I faced the issue that I don't have a shared mount point to store the capture files.Following points are to be noted.

1) You can't use ASM for storing the DB capture files.ASM was the only shared directory I had at that point.

2) If you run capture from one instance it will capture the workload of all the instances.

So the shared file system has to be on ext3 file system shared by both the instances.Fo this I did following.

Create the disk and associate it with VirtualBox machines rac1 and rac2.

$ VBoxManage createhd --filename nfs.vdi --size 5120 --format VDI --variant Fixed

# Connect it to the rac1 vm.

$ VBoxManage storageattach rac1 --storagectl "SATA Controller" --port 7 --device 0 --type hdd --medium nfs.vdi --mtype shareable

$ # Make shareable.

$ VBoxManage modifyhd nfs.vdi --type shareable

# Connect it to the rac2 vm.

VBoxManage storageattach rac2 --storagectl "SATA Controller" --port 7 --device 0 --type hdd --medium nfs.vdi --mtype shareable

You will see the new disk as /dev/sd* on rac1>i had formatted it wih ext3 format on rac1

1)Format the /dev/sdg using the ext3 format on rac1 .So you will get the /dev/sdg1

2)service nfs start on both the nodes

3)Create a directory to mount the newly created disk.For me it is /nfs on both the nodes.Add following line to the /etc/fstab on node 1

/dev/sdg1               /nfs                    ext3    defaults        1 1

 for the first time mount -t nfs  /dev/sdg1               /nfs 

4)Now you need to mount the disk from node 2 as well .for that  do the following.

mount 192.168.56.101:/nfs /nfs -o rsize=8192,wsize=8192

where 192.168.56.101 is the ip address of rac1 machine accessible from rac2.

Now if you create a directory with ownership oracle:oinstall in /nfs it should be visible from bot the nodes

[root@rac2 RAT]# cd /nfs

[root@rac2 nfs]# ls -lrt

total 20

drwx------ 2 root   root     16384 Nov 16 23:00 lost+found

-rw-r--r-- 1 root   root         0 Nov 17 03:19 1

drwxr-xr-x 4 oracle oinstall  4096 Nov 19 06:54 RAT

Now you can create a database directory on the shared nfs filesystem and  you are good to run the capture process .

DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => 'capture01',dir => 'RAT')

Index issue core dumping

I had seen the following issue in the trace files while it was core dumping every few secs.The offending statement was a delete statement with where clause.On examining the trace files it looked like an issue of key mismatch.Note that this is key mismatch issue not a corrupt index issue so all the tools used for finding the corrupt blocks will not return anything.eg-database Validate,dbverify and analyze on index will come clean since the logical structure of the index is valid.To identify the key mismatch issue proceed as follwing.

From the trace files see following error

oer 8102.2 - obj# 155099, rdba: 0x53037ad2(afn 332, blk# 363218)
kdk key 8102.2:
  ncol: 2, len: 14
  key: (14):  XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
  mask: (4096)

Clearly the delete staement was not able to identify a key which was there in the table but not in the index.Next find the offending index using the object id reported in the trace file


--> select name from obj$ where obj# = 155099 

To identify the keys that are missing from the index we ran the following queries.

Following query will give the keys which are there in the table but not in the index.

SELECT /*+ FULL(t1) */ rowid,ORDER_ID
FROM scott.Task_activity t1
MINUS
SELECT /*+ index(t XIE4TASK_ACTIVITY$C) */ rowid,ORDER_ID
FROM scott.Task_activity t;

Following query will give the keys which are there in the index but not in the table.

SELECT /*+ index(t XIE4TASK_ACTIVITY$C) */ rowid,ORDER_ID
FROM scott.Task_activity t
MINUS
SELECT /*+ FULL(t1) */ rowid,ORDER_ID
FROM scott.Task_activity t1;

The resolution is to drop and recreate the index don't use the online rebuild of the index.

ORA-03113: end-of-file on communication channel

After doing a package install the 2 node RAC database running on 11.1.0.7.0 was brought up .One instance came up fine but the other instance was not coming up and throwing the following error.

ORA-03113: end-of-file on communication channel
Process ID: 20194
Session ID: 1655 Serial number: 5

WARNING: The 'LOG_ARCHIVE_CONFIG' init.ora parameter settings
are inconsistent with another started instance. This may be
caused by the 'DB_UNIQUE_NAME' init.ora parameter being specified
differently on one or more of the other RAC instances; the
DB_UNIQUE_NAME parameter value MUST be identical for all
instances of the database.
Errors in file /u02/diag/rdbms/shas/shas2/trace/swprd2_lgwr_5292.trc:



--After looking at the paratemet log_archive_config it was null and also the DB_UNIQUE_NAME was same across 2 nodes.The we checked the view "Select * from v$dataguard_config" and identified that it has non identical entry from the other node.I used following steps to resolve the issue:


you can reset this parameter and bring the instance.

Just mount the instance - problematic one

alter system set log_archive_config='dg_config=(nodg_config)' scope=both sid='*';

Temporary tablespace tempfile moved accidently at OS level

I saw this issue when the tempfile was moved accidently at the operating system level.It was throwing following error in the alert log.

data file 202: '/udb/u02/ORACLE/shas/temp2.dbf'
ORA-27041: unable to open file
ORA-12012: error on auto execute of job 449748
ORA-01116: error in opening database file ORA-01116: error in opening database file 202
ORA-01110: data file 202: '/udb/u02/ORACLE/shas/temp2.dbf'
ORA-27041: unable to open file
ORA-06512:

The resolution is to drop and add the tempfile but when I tried to drop I recieved the  following error.

ORA-25152: TEMPFILE cannot be dropped at this time

I used the following steps to identify the SID using the tempfile .After killing the SID I was able to drop and recreate the tempfile.

1)  SELECT s.sid, s.username, s.status, u.tablespace, u.segfile#, u.contents, u.extents, u.blocks  
FROM v$session s, v$sort_usage u  
WHERE s.saddr=u.session_addr  
ORDER BY u.tablespace, u.segfile#, u.segblk#, u.blocks;  

2)  select sid, username, osuser, program, machine from v$session where sid in   
();