ORA-02065 Changing Max_Dump_File_Size Parameter


Yesterday I was generating some traces (1046) in a database in version 10g, when I went to see the dump, in row mode I appeared the hateful message


Of course, it’s my fault for not looking at it previously, to solve it you can extend it at the session or system level. In my case as I was tracing several sessions, I would like to change it to the system level, so I run;

Alter system set max_dump_file_size = 100m;

This gave me back the following error

ORA-02065: illegal option for ALTER SYSTEM

Doing some memory remember that it is a string of text and has to go in quotes, as in the following example;

alter system set max_dump_file_size= ‘100m’;




ORA-19870 ORA-19504 ORA-17502 ORA-15041 When Restoring Backup

I was restoring a backup to Flex ASM instance when I get the next errorstack;

channel t4: restoring datafile 00069 to +CLOUD_PROD_DATA_DISKG
channel t4: restoring datafile 00075 to +CLOUD_PROD_DATA_DISKG
channel t4: restoring datafile 00077 to +CLOUD_PROD_DATA_DISKG
channel t4: reading from backup piece dbwebdb_c0s464nj_1_1
channel t1: ORA-19870: error while restoring backup piece dbclout_c1f432tc_1_1
ORA-19504: failed to create file "+CLOUD_PROD_DATA_DISKG"
ORA-17502: ksfdcre:4 Failed to create file +CLOUD_PROD_DATA_DISKG
ORA-15041: diskgroup "CLOUD_PROD_DATA_DISKG" space exhausted

I checked the space. Usually before start the restore look at the filesystem or ASM to check free space. From asmcmp (I like with -p parameter);

ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  4194304      5116       52                0              52              0             N  ACFS_CLOUD_PROD_TRZ_DG/
MOUNTED  EXTERN  N         512   4096  4194304      1020       52                0              52              0             N  ACFS_CLOUD_PROD_UDD_DG/
MOUNTED  NORMAL  N         512   4096  1048576     30717    21271            10239            5516              0             Y  OCRVTD_DG/
MOUNTED  EXTERN  N         512   4096  4194304     71676    71544                0           71544              0             N  CLOUD_PROD_ARCCLD1_DG/
MOUNTED  EXTERN  N         512   4096  4194304     71676    71548                0           71548              0             N  CLOUD_PROD_ARCCLD2_DG/
MOUNTED  EXTERN  Y         512   4096  4194304    747476   425468                0          425468              0             N  CLOUD_PROD_DATA_DISKG/
MOUNTED  EXTERN  N         512   4096  4194304      5116     4372                0            4372              0             N  CLOUD_PROD_RC1_DG/
MOUNTED  EXTERN  N         512   4096  4194304      5116     4372                0            4372              0             N  CLOUD_PROD_RC2_DG/

Of course, the column Rebal(ance) has value Y, ummm I suppose a new disk was added to the diskgroup but no rebalanced. With the nexts commands you can rebalance all the DG.

Execute the rebalance of my diskgroup;


To monitoring rebalancing;


I tryed the restores again with a happy end.


ORA-27102 When starting Database On Solaris

Last morning I got the next error when I was restarting a database;

ORA-27102: out of memory
SVR4 Error: 12: Not enough space
Additional information: 1671
Additional information: 16106127360
Additional information: 6291456000

Oracle try to allocate all memory in an unique chunck but there is not a chunck of this size avaliable. Operating system report this error to Oracle and Oracle show it us;

I have red about it is a bug (20635316) in ML/MOS. You have like workaround the hidden parameter

_use_osm = false

Of course, it is a hidden parameter and you must be carefull with it. Don’t set up in production enviroment.


ORA-01157, ORA-01110 Errors, When Query DBA_TEMP_FILES

Last morning I was migrating a database from filesystem to ASM, when I was quering the dba_temp_files get the next error.


ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/pludev/pludev_temporal_01.dbf'

Ok, the CF file reference old temporary files on filesystem. You can check this by the next query (I have added news tempfiles on ASM);

SQL> select ts#, name from v$tempfile;

       TS# NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         3 /pludev/pludev_temporal_01.dbf
         5 /pludev/pludev_temporal_users01.dbf
         3 /pludev/pludev_temporal_02.dbf
         3 +PLU_DEV_DAT_DG/pludeV/tempfile/temp.298.955652617
         5 +PLU_DEV_DAT_DG/pludeV/tempfile/temp_users.299.943353021
         3 +PLU_DEV_DAT_DG/pludeV/tempfile/temp.300.945664717

6 rows selected.

In this case the easy way is drop old tempfiles (in filesystem format) as show in the next script;

alter tablespace temp1 drop tempfile '/pludev/pludev_temporal_01.dbf'; 
alter tablespace temp2 drop tempfile '/pludev/pludev_temporal_users01.dbf'; 
alter tablespace temp3 drop tempfile '/pludev/pludev_temporal_02.dbf'; 

After that, if you repeat the query on dba_temp_files;

SQL> select file_name from dba_temp_files;




How To Find Info About ORA-600

Last morning a coworker ask me about the best way of find documentation for ora-00600 errors. He told me Google (or anothers search engines) is the best option but I don’t agree with him. Of course Google and others are a easy ang faster way of find results but in this case I think the only way (if you have support) is Metalink/MOS. The not you must to use is;

Note 153788.1

It is  a simple web form and maybe the only option you have in this kind of problems.

HTH – Antonio NAVARRO.


ORA-24247 And ORA-29273 Errors

I have been reported the next stack error ;

ERROR en línea 1:
ORA-29273: fallo de la solicitud HTTP
ORA-24247: acceso de red denegado por la lista de control de acceso (ACL)
ORA-06512: en "SYS.UTL_HTTP", línea 1491
ORA-06512: en línea 1

The developer team send me the query that produces the error, it is the next;

set def off
select substr(UTL_HTTP.request('http://validate.sandbox.test2.es/services/sendmailing.phpmailingcode=M00241755&audienceid[CustomerID]=Email&fields[EMAILS]=xxxx@xxxxxxx.xx') ,1,4000) from dual;
set def on

In this case the problem is the acl privileges, the user executing the sentence hasn’t enough privileges, in this version ( it can be solve by grant connect permission like show below;

  DBMS_NETWORK_ACL_ADMIN.append_host_ace (                                       
    host       => 'validate.sandbox.test2.es',                         
    lower_port => 1,                                                             
    upper_port => 1500,                                                          
    ace        => xs$ace_type(privilege_list => xs$name_list('connect'),         
                              principal_name => 'USER_STEVEN',                         
                              principal_type => xs_acl.ptype_db));               


TNS-03505 Error

Today a coworker ask me about the next error;

ora12c@motaf-1:~$ tnsping cloud.buey.com

TNS Ping Utility for Solaris: Version - Production on 10-APR-2017 10:28:02

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

TNS-03505: Failed to resolve name

In this case the problem was Oracle cann’t find the tnsnames.ora, it was moved to another path and TNS_ADMIN had not value. Set TNS_ADMIN enviroment variable solved the problem.