ORA-06512 and ORA-29283 in RAC

Came across an interesting scenario in UTL_FILE operation, here it goes 

Following error happens 

ERROR at line 1:

ORA-29283: invalid file operation

ORA-06512: at "SYS.UTL_FILE", line 536

ORA-29283: invalid file operation

ORA-06512: at line 5

only when

1. You have created a directory to which the file operation should happen,  under a different user, 
2. Permission is granted to oracle aka rdbms owner,  via addition of group,  which has write permission to the above directory  
3. DB is on Grid Infrastructure
4. Database instance and listener is managed via srvctl/ and started via srvctl. 
5. Cluster has not been bounced since #2 
6. Connection is made from a remote client.

--- > Creating the directory 

[root@myexaadm011 ~]# dcli -g dbs_group -l root mkdir -p /pncsni/apps

[root@myexaadm011 ~]# dcli -g dbs_group -l root chown apps:oinstall /pncsni/apps

[root@myexaadm011 ~]# dcli -g dbs_group -l root chmod -R 770 /pncsni/apps

[root@myexaadm011 ~]# dcli -g dbs_group -l root usermod -g dborgrid -G oinstall,dba,dborgrid  orpncsni
[root@myexaadm011 ~]# dcli -g dbs_group -l root id orpncsni
myexaadm011: uid=3982(orpncsni) gid=11001(dborgrid) groups=11001(dborgrid),10005(oinstall),8500(dba)
myexaadm012: uid=3982(orpncsni) gid=11001(dborgrid) groups=11001(dborgrid),10005(oinstall),8500(dba)

myexaadm013: uid=3982(orpncsni) gid=11001(dborgrid) groups=11001(dborgrid),10005(oinstall),8500(dba)

myexaadm014: uid=3982(orpncsni) gid=11001(dborgrid) groups=11001(dborgrid),10005(oinstall),8500(dba)

[root@myexaadm011 ~]# su - orpncsni

--- > Verifying  we can write to this Directory 

[orpncsni@myexaadm011 ]$  cd /pncsni/apps

[orpncsni@myexaadm011 ]$  touch a.txt; cat a.txt 

[orpncsni@myexaadm011 ]$  

--- > Bouncing  Database and Listener using srvctl 

[orpncsni@myexaadm011 ]$  srvctl stop database -d PNCSNI

[orpncsni@myexaadm011 ]$  srvctl stop listener -l PNCSNI

[orpncsni@myexaadm011 ]$  srvctl start listener -l PNCSNI

[orpncsni@myexaadm011 ]$  srvctl start database -d PNCSNI

--- >  Creating the directory object in the Database 

SQL> show con_name

CON_NAME

------------------------------

PDBCST001

SQL> create directory TESTLIST as  '/pncsni/apps'; 

SQL> grant read,write on directory TESTLIST to public; 

--> Connected locally from DB Server itself 

SQL> DECLARE

  f1 utl_file.file_type;

  buf_out VARCHAR2(20000) := 'abcdefg';

BEGIN

  f1 := utl_file.fopen('TESTLIST','navwriting1.txt','w',32767);

  utl_file.put_line (f1, buf_out);

  utl_file.fclose(f1);

END;

/  SQL>   2    3    4    5    6    7    8    9

PL/SQL procedure successfully completed.

SQL>

--> Usually DBAs are done here and they leave it for Developers / Application Admins / Users 

--> Connected from remote client (Developers or Users) 

SQL> DECLARE

  f1 utl_file.file_type;

  buf_out VARCHAR2(20000) := 'abcdefg';

BEGIN

  f1 := utl_file.fopen('TESTLIST','navwriting1.txt','w',32767);

  utl_file.put_line (f1, buf_out);

  utl_file.fclose(f1);

END;

/   2    3    4    5    6    7    8    9

DECLARE

*

ERROR at line 1:

ORA-29283: invalid file operation

ORA-06512: at "SYS.UTL_FILE", line 536

ORA-29283: invalid file operation

ORA-06512: at line 5

-- > now its the time for endless email chains, and tickets :) 

Usual things that DBA checks here - 

1. Physical path / directory exists on all DB nodes , 
2. oracle aka rdbms owner can write to it on all nodes in RAC 
3. Database and Listeners are bounced many a times 
4. Do the same execution from different DB Tier nodes multiple times  - All Success !!  but still clients cannot use the code  

Its been observed while doing all this troubleshooting  that it works fine when the listeners are started manually by lsnrctl on each node. 

Lets look the listener process

-- > When started via srvctl 
[orpncsni@myexaadm011 ]$ ps -ef |grep tns|grep PNCSNI
orpncsni  43740      1  0 18:32 ?        00:00:00 /pncsni/oracle/product/12102/bin/tnslsnr PNCSNI -no_crs_notify -inherit

[orpncsni@myexaadm011 ]$ grep Groups /proc/43740/status

Groups: 8500 11001

--> When started manually by lsnrctl 

[orpncsni@myexaadm011 ]$ ps -ef |grep tns|grep PNCSNI
orpncsni 111991      1  0 19:18 ?        00:00:00 /pncsni/oracle/product/12102/bin/tnslsnr PNCSNI -inherit

[orpncsni@myexaadm011 ]$ grep Groups /proc/111991/status

Groups: 8500 10005 11001

Here you can see that when listener is started via srvctl, the process does not have the group attributes which was recently added.  Hence the server processes it spawns also wont have the same and it cannot write to the directory
If the listener is started using SRVCTL then it does not inherit groupid 10005 (oinstall)  which has the write permission to the directory . If the listener is started using LSNRCTL then it does , and everything works.

The reasoning for thi is that the CRSD processes caches all the user information about all its resource owners at startup and is not refreshed in the middle.

This issue can be fixed via a quick CRSD bounce

[orgrid@myexaadm011 ~]$ crsctl stop resource ora.crsd -init
[orgrid@myexaadm011 ~]$ crsctl start resource ora.crsd -init

This can be done rolling and very quickly , does not affect other resources running on the server.  This bounce repopulates the user information about all  its resource owners.

Deleted Oracle flashback logs ?

Deleted Oracle flashback logs ?

Nothing will until you try to flashback database or bounce instance.
You cannot bounce , nor flashback the DB,  But following is a way to start your database with minimal effort.


My Test case


[orpncsni@testserver ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 1 09:34:18 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
SQL> select status from v$instance;
STATUS
------------------------------------------------
MOUNTED


########--> Mounted


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database


########--> Cannot open


SQL> alter database flashback off;
Database altered.


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
########--> Still cannot open


########--> Check what is the flashback status


SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------------------------------------------------------------
RESTORE POINT ONLY

SQL>  select * from v$restore_point;
 select * from v$restore_point
               *
ERROR at line 1:
ORA-38701:  Flashback database log 1024 seq 1285 thread 1:
"/pncsni/arch/flashback/PNCSNI/flashback/o5_az_8tb7frtb_.flb"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

########--> I did remember the  GRP NAME, if not get it from Alert log
 Also use (Doc ID 1288189.1)

SQL> DROP RESTORE POINT NAVEENGRP;
DROP RESTORE POINT NAVEENGRP
*
ERROR at line 1:
ORA-38701: Flashback database log 1024 seq 1285 thread 1:
"/pncsni/arch/flashback/PNCSNI/flashback/o5_az_8tb7frtb_.flb"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


(Doc ID 1554596.1) - Did not work



SQL> alter database backup controlfile to trace as '/tmp/control.ctl';


Database altered.


SQL> alter database backup controlfile to trace as '/tmp/naveencontrol.ctl';


Database altered.


Edit this file
[orpncsni@testserver ~]$ vi '/tmp/control.ctl';


Keep only following
CREATE CONTROLFILE REUSE DATABASE "PNCSNI" NORESETLOGS  ARCHIVELOG
..........
...........
CHARACTER SET XXXXXX
;


SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.


SQL> startup nomount
ORACLE instance started.
....
...


-- > Backup existing control file
-- > Create new control files and open database


SQL> @/tmp/control.ctl;
Control file created.


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> alter database open;


Database altered.




Database has been started and opened - all done.


Then do all the rest of the activities like flashback on / archive log ,
block change tracking or other RMAN settings. 
These statements can be seen form the '/tmp/naveencontrol.ctl';


Like
ALTER TABLESPACE TEMP1 ADD TEMPFILE '/pncsni/oradata/data02/temp01.dbf' REUSE;
ALTER TABLESPACE TEMP2 ADD TEMPFILE '/pncsni/oradata/data02/temp02.dbf' REUSE;

EHCC Analysis

-- http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_compress.htm#BEICEJED
-- Script analyse the EHCC benefit 

set serverout on verify off feed off
SET PAGESIZE 50000
ACCEPT myScrTbs CHAR DEFAULT 'EHCCSCRATCHPAD' PROMPT 'ScratchPad Tablespace [EHCCSCRATCHPAD] : ';
ACCEPT myCntTbls NUMBER DEFAULT 10 PROMPT 'Number of Top Tables to be Analysed  [10] : ';
create directory ehcc_estimate_dir as '/tmp';
grant read, write on directory ehcc_estimate_dir to sys

declare 
dyn_stmt varchar2(500); 
oradata varchar2(10); 
tbs_num number; 
v_blkcnt_cmp     pls_integer;
v_blkcnt_uncmp   pls_integer;
v_row_cmp        pls_integer;
v_row_uncmp      pls_integer;
v_cmp_ratio      number;
v_comptype_str   varchar2(60);
fileHandler UTL_FILE.FILE_TYPE;

begin
fileHandler := UTL_FILE.FOPEN('EHCC_ESTIMATE_DIR', 'EHCC_ESTIMATE_DATA.txt', 'W');
UTL_FILE.PUTF(fileHandler, 'Copy this Data to an Xls file for better analysis');
UTL_FILE.FCLOSE(fileHandler);

if '&myScrTbs' is not null and &myCntTbls is not null
then
dbms_output.put_line('');
dbms_output.put_line('#################');
dbms_output.put_line('Tablespace is  : &myScrTbs');
dbms_output.put_line('Count of tables  is : &myCntTbls' );
dbms_output.put_line('#################');
select count(1) into tbs_num from dba_tablespaces where tablespace_name='&myScrTbs'; 
IF  tbs_num > 0  then 
dbms_output.put_line('Tablespace  &myScrTbs already exisits'); 
 else 
select value into oradata from v$parameter2 where name ='db_create_file_dest';
dbms_output.put_line('Creating Tablespace &myScrTbs with files in '|| oradata);
EXECUTE IMMEDIATE 'create tablespace &myScrTbs';  
EXECUTE IMMEDIATE 'alter tablespace &myScrTbs add datafile'; 
end if; 
-- Find Object Names 
FOR tbls IN (SELECT OWNER, SEGMENT_NAME , SIZE_GB
FROM (SELECT OWNER, SEGMENT_NAME, BYTES/1024/1024/1024 SIZE_GB 
     FROM DBA_SEGMENTS 
     WHERE SEGMENT_TYPE = 'TABLE'
     ORDER BY     BYTES/1024/1024/1024  DESC )
WHERE ROWNUM <= &myCntTbls
order by SIZE_GB DESC)
LOOP
  DBMS_OUTPUT.PUT_LINE ('Owner = ' || tbls.OWNER || ', Table = ' || tbls.SEGMENT_NAME || ', Size is  = ' ||tbls.SIZE_GB );
  dyn_stmt := 'alter user '||tbls.OWNER||'  QUOTA UNLIMITED ON  &myScrTbs '; 
  EXECUTE IMMEDIATE dyn_stmt ; 
  
  dbms_compression.get_compression_ratio(
scratchtbsname   => upper('&myScrTbs'),      
ownname          => tbls.OWNER,           
tabname          => upper(tbls.SEGMENT_NAME),   
partname         => NULL,           
comptype         => dbms_compression.comp_for_query_high,    
blkcnt_cmp       => v_blkcnt_cmp,    
blkcnt_uncmp     => v_blkcnt_uncmp,  
row_cmp          => v_row_cmp,    
row_uncmp        => v_row_uncmp,  
cmp_ratio        => v_cmp_ratio,  
comptype_str     => v_comptype_str);
-- dbms_output.put_line('Estimated Compression Ratio: '||to_char(v_cmp_ratio));
-- dbms_output.put_line('Blocks used by compressed sample: '||to_char(v_blkcnt_cmp));
-- dbms_output.put_line('Blocks used by uncompressed sample: '||to_char(v_blkcnt_uncmp));
dbms_output.put_line( tbls.OWNER||','|| tbls.SEGMENT_NAME||','|| to_char(v_cmp_ratio)||','||to_char(v_blkcnt_uncmp)||','||to_char(v_blkcnt_cmp)) ;
fileHandler := UTL_FILE.FOPEN('EHCC_ESTIMATE_DIR', 'EHCC_ESTIMATE_DATA.txt', 'A');
UTL_FILE.PUTF(fileHandler, tbls.OWNER||','|| tbls.SEGMENT_NAME||','|| to_char(v_cmp_ratio)||','||to_char(v_blkcnt_uncmp)||','||to_char(v_blkcnt_cmp));
UTL_FILE.FCLOSE(fileHandler);
END LOOP;
dbms_output.put_line('Dropping Tablespace &myScrTbs');
EXECUTE IMMEDIATE 'drop  tablespace &myScrTbs'; 
EXECUTE IMMEDIATE 'drop DIRECTORY ehcc_estimate_dir';
else
dbms_output.put_line('#################');
dbms_output.put_line('#################');
dbms_output.put_line('ERR : at least one of the variables is null ! Cannot proceed ');
dbms_output.put_line('#################');
dbms_output.put_line('#################');
end if;
dbms_output.put_line('Check file /tmp/EHCC_ESTIMATE_DATA.txt for data');
end;
/
exit; 

Oracle 12c RDBMS Software installation

Start from a VNC or X Client

Prepare your file system for Oracle Home

Provide the MOS credentials if you would like to register for OCM ,

else skip the OCM options by unchecking the check box , (it never worked for me)

Check for any release updates if you would like to be on greatest and latest ,

I mostly don't use this option, as I decided which patches to go in at a later stage.

Do you want o create a started DB / seeded DB, I guess not, most of the cases you want to upgrade the existing DB ,

Or otherwise explore the dbca options , I would go for install software only

chose install software only

Select RAC or Single Node

Select language options

Select which edition, (refer your Licenses document for info about what you can select)

Select ORACLE_BASE and ORACLE_HOME

Select the various groups

runInstaller checks for a pre-requisites

If there are issues it will provide a fix up script

as root run the fixup  script to fix various pre-requisites and click ok

I have to ignore the swap space , (doesn't have abundant availability of space)

Here is a summary of installation.

Watch the progress.

Asks you to run root .sh

Once root.sh is complete click ok.

You are done with the Oracle 12c RDBMS Software.

Starting DBCA and DB creation to follow.

-->

My VM server Oracle VM Server release 3.2.2 - On an IBM x3850

My VM server

Oracle VM Server Oracle VM server release 3.2.2 - On an IBM x3850

Oracle VM Manager Version: 3.2.2.520 - on a Custom Made Desktop
(more on this, coming soon !)

Extending partitions on a Linux virtual machine hosted in Oracle VM Server

Here is how I did it , 
I use 
Oracle VM Server Oracle VM server release 3.2.2
Oracle VM Manager Version: 3.2.2.520

VMs on OEL  - Oracle Linux Server release 5.9

On an IBM x3850

Shutdown the VM
Navigate to VM Machines Oracle VM Home > Servers and VMs > Server Pools > > Stop
Edit VM  
Navigate to VM Machines Oracle VM Home > Servers and VMs > Server Pools > > Edit 

On the Disk Tab - Select the hard disk you would like to extend 
Update the required size,  Click OK
Start  the VM
ssh to vm and Log in as root
Use frisk to find out the info   At the command prompt type fdisk -l
[root@vmgdbwlnchs01 ~]# fdisk -l 

Disk /dev/hda: 69.7 GB, 69793218560 bytes
255 heads, 63 sectors/track, 8485 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

  Device Boot      Start         End      Blocks   Id  System
/dev/hda1   *           1          13      104391   83  Linux
/dev/hda2              14        6527    52323705   8e  Linux LVM

Disk /dev/dm-0: 47.3 GB, 47311749120 bytes
255 heads, 63 sectors/track, 5751 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/dm-0 doesn't contain a valid partition table

Disk /dev/dm-1: 6241 MB, 6241124352 bytes
255 heads, 63 sectors/track, 758 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/dm-1 doesn't contain a valid partition table

The response should say something like Disk /dev/hda: 69.7 GB, 69793218560 bytes

At the command prompt type fdisk /dev/hda. 
Type p to print the partition table and press Enter 
Type n to add a new partition
Type p again to make it a primary partition
Now you'll be prompted to pick the first cylinder which will most likely come at the end of your last partition 
Type w to save these changes
[root@vmgdbwlnchs01 ~]# fdisk /dev/hda

The number of cylinders for this disk is set to 8485.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
  (e.g., DOS FDISK, OS/2 FDISK)

Command (m for help): p

Disk /dev/hda: 69.7 GB, 69793218560 bytes
255 heads, 63 sectors/track, 8485 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

  Device Boot      Start         End      Blocks   Id  System
/dev/hda1   *           1          13      104391   83  Linux
/dev/hda2              14        6527    52323705   8e  Linux LVM

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

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

Calling ioctl() to re-read partition table.

WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table.
The new table will be used at the next reboot.
Syncing disks.

Restart the VM - Must 
Log back in as root

At the command prompt type fdisk -l. You'll notice another partition is present.  initialize this new partition as a physical volume using pvcreate 
[root@vmgdbwlnchs01 ~]# fdisk -l 

Disk /dev/hda: 69.7 GB, 69793218560 bytes
255 heads, 63 sectors/track, 8485 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

  Device Boot      Start         End      Blocks   Id  System
/dev/hda1   *           1          13      104391   83  Linux
/dev/hda2              14        6527    52323705   8e  Linux LVM
/dev/hda3            6528        8485    15727635   83  Linux

Disk /dev/dm-0: 47.3 GB, 47311749120 bytes
255 heads, 63 sectors/track, 5751 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/dm-0 doesn't contain a valid partition table

Disk /dev/dm-1: 6241 MB, 6241124352 bytes
255 heads, 63 sectors/track, 758 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/dm-1 doesn't contain a valid partition table

[root@vmgdbwlnchs01 ~]# pvcreate /dev/hda3
 Writing physical volume data to disk "/dev/hda3"
 Physical volume "/dev/hda3" successfully created
  
Add the physical volume to the existing volume group using the vgextend command. use  df -h to find the name of the volume group.  
[root@vmgdbwlnchs01 ~]# df -hP
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00   43G   34G  7.4G  82% /
/dev/hda1              99M   49M   46M  52% /boot
tmpfs                 3.8G     0  3.8G   0% /dev/shm
192.168.1.101:/u01/software  643G  328G  282G  54% /u01/software

[root@vmgdbwlnchs01 ~]# vgextend VolGroup00 /dev/hda3 
 Volume group "VolGroup00" successfully extended


Get the free space available on the physical volume type vgdisplay [volume group]  and use pvdisplay to check if everything is fine or not . 
[root@vmgdbwlnchs01 ~]# vgdisplay VolGroup00 
 --- Volume group ---
 VG Name               VolGroup00
 System ID             
 Format                lvm2
 Metadata Areas        2
 Metadata Sequence No  4
 VG Access             read/write
 VG Status             resizable
 MAX LV                0
 Cur LV                2
 Open LV               2
 Max PV                0
 Cur PV                2
 Act PV                2
 VG Size               64.84 GB
 PE Size               32.00 MB
 Total PE              2075
 Alloc PE / Size       1596 / 49.88 GB
 Free  PE / Size       479 / 14.97 GB
 VG UUID               vwAxOp-LL9a-xkvk-BYbt-OmLd-intU-kNpjW2
   
[root@vmgdbwlnchs01 ~]# pvdisplay 
 --- Physical volume ---
 PV Name               /dev/hda2
 VG Name               VolGroup00
 PV Size               49.90 GB / not usable 25.37 MB
 Allocatable           yes (but full)
 PE Size (KByte)       32768
 Total PE              1596
 Free PE               0
 Allocated PE          1596
 PV UUID               0djEdP-G0t9-1BOH-L4rN-wwa8-XM5L-RqtJBz
  
 --- Physical volume ---
 PV Name               /dev/hda3
 VG Name               VolGroup00
 PV Size               15.00 GB / not usable 31.02 MB
 Allocatable           yes 
 PE Size (KByte)       32768
 Total PE              479
 Free PE               479
 Allocated PE          0
 PV UUID               GbbNYH-Ii2B-dh4P-A2nt-zoo2-uRCQ-xtM3MR
   


Extend the logical volume by the amount of free space (on the command line use a digit less, else you might see Insufficient free space Here i am using 14.95, though it shows me 14.97 above)) 
[root@vmgdbwlnchs01 ~]# lvextend -L+14.95G /dev/VolGroup00/LogVol00
 Rounding up size to full physical extent 14.97 GB
 Extending logical volume LogVol00 to 59.03 GB
 Logical volume LogVol00 successfully resized

Expand the  file system in the logical volume using resize2fs 
[root@vmgdbwlnchs01 ~]# resize2fs /dev/VolGroup00/LogVol00
resize2fs 1.39 (29-May-2006)
Filesystem at /dev/VolGroup00/LogVol00 is mounted on /; on-line resizing required
Performing an on-line resize of /dev/VolGroup00/LogVol00 to 15474688 (4k) blocks.
The filesystem on /dev/VolGroup00/LogVol00 is now 15474688 blocks long.



Check the space using df -hP 
[root@vmgdbwlnchs01 ~]# df -hP
Filesystem             Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00   58G   34G   22G  62% /
/dev/hda1               99M   49M   46M  52% /boot
tmpfs                  3.8G     0  3.8G   0% /dev/shm
192.168.1.101:/u01/software   643G  328G  282G  54% /u01/software
none                   3.8G  104K  3.8G   1% /var/lib/xenstored

This machine ill use to install 12c DB, will update soon.