Month: May 2014

Leave a comment Posted on Oracle, Rac

ORA-12514, TNS:listener does not.. in 11.2.0.2 and 11.2.0.3 Rac env.

This morning in the Dutch environment we have been bitten by that bug again on one of the databases:

Bug 11772838 – oraagent modifies LOCAL_LISTENER with an incorrect value, when the parameter was set in spfile (Doc ID 11772838.8)

So clearly a case of Monday again and both 11.2.0.2 and 11.2.0.3 Rac environments might be affected!

Customer complained that his connection to the instance was not working:
oracle.javatools.db.DBException: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

I logged into the  instances and due to the fact that customer could tell me connection to second instance working I compared local_listener again.
And indeed  that one had been altered as is described in the note by the cluster agent last friday  :

SQL> show parameter listener

## wrong
NAME TYPE VALUE
———————————— ———– ——————————
listener_networks string
local_listener string (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=195.233.333.77)(PORT=1521))))

## correct

local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=mysrvr36r-vip)(PORT=33007))))

The fix is straight forward  from the note ( and the workaround  used is  indeed  to add a blank ( grins saved by a blank  !)

ALTER SYSTEM SET local_listener='(DESCRIPTION= (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mysrvr35r-vip)(PORT=33007))))’ SCOPE=BOTH SID=’MYDBWHP1′;

ALTER SYSTEM SET local_listener='(DESCRIPTION= (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mysrvr36r-vip)(PORT=33007))))’ SCOPE=BOTH SID=’MYDBWHP2′;

 

I searched the alert file of the instance on the 35r and indeed found  in the alert file:

Fri May 16 21:47:01 2014
ALTER SYSTEM SET local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=195.233.333.77)(PORT=1521))))’ SCOPE=MEMORY SID=’MYDBWHP1′;

Note:
I have checked the cluster logfiles but I cannot find a true issue there .

and both Customer and Dba happy again.

happy reading and best of luck,

Mathijs.

Leave a comment Posted on Oracle

When ORA-01144: File size (6553600 blocks) exceeds … hits you

Introduction.

In February  i had set up a database ( 11.2.02.) on Linux with ASM.  One funny thing there was that the only data tablespace was requested to be in size of a 4.5 TB  just for holding data and indexes. Recently I was asked by the the Application team to break up the tablespace into two tablespaces. In itself that is not that big a deal of course but during recreate of the tablespace i got a nice error messages which triggers me to this mini post :

Details.

This is how I had set up the create / alter of the tablespace at first:

create tablespace MY_DATA datafile  '+MYTS_DATA01' size 50G autoextend off
 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO autoallocate;

create tablespace MY_DATA
*
ERROR at line 1:
ORA-01144: File size (6553600 blocks) exceeds maximum of 4194303 blocks

After  using Google ( always happy to find blogs that point into the right direction . I found out that the error message was triggered by the db_block_size was only 8k in my database.  (you can also take a look at  ORA-01144 on Create Tablespace or Resize of Datafile (Doc ID 104280.1).

After that the fix  for this issue was that datafile size simply needed to be smaller for my action to become succesfull:

My example for one of the tablespaces now looks like this :
#!/bin/ksh
#

${ORACLE_HOME}/bin/sqlplus /nolog << EOF
set echo on
set feedback on
spool CreaAdd.lst
--
connect / as sysdba
set echo on
set feedback on
set timing on

drop tablespace MY_DATA including contents and datafiles;

create tablespace MY_DATA
  datafile  '+MYTS_DATA01' size 30G autoextend off
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO autoallocate;
alter database backup controlfile to trace;
EOF
x=1
while [ $x -le 68 ]
do
 $ORACLE_HOME/bin/sqlplus /nolog << EOF
 set echo on
 set feedback on
 set timing on
 spool step6_MY_DATA_$x.lst
 connect / as sysdba
 alter tablespace MY_DATA add datafile  '+MYTS_DATA01' size 30G autoextend off;
 quit 
EOF
x=$(( $x + 1 ))
done

exit

Happy end .. now i only need wait till the datafiles are added .

Happy reading ,

 

Mathijs.