Archive for the ‘oracle’ Category

Error altering tempfile: ORA-00376: file 202 cannot be read at this time

I noticed a lot of ORA-01652 errors while gathering some table statistics (Oracle 10g Release 2 –

BEGIN DBMS_STATS.GATHER_TABLE_STATS('OWNER','TABLE', cascade=>true, estimate_percent=>60); END;

ERROR at line 1:
ORA-01652: Unable to extend temp segment by 256 in tablespace TEMP

ORA-06512: at "SYS.DBMS_STATS", line 13437
ORA-06512: at "SYS.DBMS_STATS", line 13457
ORA-06512: at line 1

When I’m trying to resize the tempfile I get an ORA-00376 error:

SQL> alter database tempfile '/app/oracle/oradata/temp01.dbf' resize 20G;
alter database tempfile '/app/oracle/oradata/temp01.dbf' resize 20G
ERROR at line 1:
ORA-00376: file 202 cannot be read at this time
ORA-01110: datafile 202: '/app/oracle/oradata/temp01.dbf'

I checked the file on the filesystem and it exists and has the correct permissions:

$ ls -lrth /app/oracle/oradata/temp01.dbf
-rw-r----- 1 oracle dba 15G 17 nov 02:01 /app/oracle/oradata/temp01.dbf

After bouncing the database, I try to alter again the tempfile and now I get an ORA-01516:

SQL> alter database tempfile '/app/oracle/oradata/temp01.dbf' resize 20G;
alter database tempfile '/app/oracle/oradata/temp01.dbf' resize 20G
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile '/app/oracle/oradata/temp01.dbf'

But the file is there as I checked before. With this new ORA-01516 error I checked the status of the tempfile and the error was there:

SQL> select tablespace_name , status from dba_tablespaces where tablespace_name = 'TEMP';

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

SQL> select name ,status from v$tempfile where name like '%temp%';

NAME                                          STATUS
/app/oracle/oradata/temp01.dbf                OFFLINE

The tempfile is offline. This is because our server ran out of space this morning, as you can see in the alert log:

Errors in file /app/oracle/admin/db1/udump/db11_ora_18815.trc:
ORA-01114:IO error writing block to file 202 (block # 1218066)
ORA-27072: I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 1218066
Additional information: -1

After freeing disk space, I try to bring back the datafile online:

SQL> alter database datafile '/app/oracle/oradata/temp01.dbf' online;
 alter database datafile '/app/oracle/oradata/temp01.dbf' online
 ERROR at line 1:
 ORA-01516: nonexistent log file, datafile or tempfile '/app/oracle/oradata/temp01.dbf'

I neither can take the TEMP tablespace offline:

SQL> alter tablespace temp offline;
 alter tablespace temp offline
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

The solution is to recreate the tempfile:

  • First, delete the existing tempfile from the database
  • Remove the file from the filesystem (rm)
  • Create a new tempfile.
SQL> alter database tempfile '/app/oracle/oradata/temp01.dbf' drop;

Database modified.

SQL> !ls -lrt /app/oracle/oradata/temp01.dbf

-rw-r----- 1 oracle dba 15728644096 17 nov 02:01 /app/oracle/oradata/temp01.dbf

SQL> !rm /app/oracle/oradata/temp01.dbf

SQL> alter tablespace temp add tempfile '/app/oracle/oradata/temp01.dbf' size 15000M;

Tablespace modified.

SQL> select name, status from v$tempfile;

NAME                                     STATUS
---------------------------------------- -------
/app/oracle/oradata/temp01.dbf           ONLINE

Now the tablespace is online and everithing returns to normal.

Any questions? Please feel free to comment below.

Categories: 10gR2, oracle Tags: ,

Using SQL*Plus copy command in Oracle 11gR2

March 20, 2014 Leave a comment

There’s a unusual command in SQL*Plus that may be useful under certain circumstances: the COPY command (

Although this command will be obsoleted in future releases of SQL*Plus, Oracle has been saying the same since (at least) Oracle 9, and it’s still available in Oracle 11gR2.

I need to copy a table from one database to another database, and I don’t want to do an export/import of the table.

A simple copy of the table is enough for me:

miquel@db_2> copy from otheruser@db_1 create my_table using select * from other_table;
Enter FROM password:

Array fetch/bind size is 1000. (arraysize is 1000)
Will commit when done. (copycommit is 0)
Maximum long size is 2000000000. (long is 2000000000)
Table MY_TABLE created.

55481 rows selected from otheruser@db_1.
 55481 rows inserted into MY_TABLE.
 55481 rows committed into MY TABLE at DEFAULT HOST connection.

It’s that simple!

Feel free to comment below.

Categories: 11gR2, oracle, SQL*Plus Tags:

Corrupt block found – Oracle 11g Release 2

January 31, 2014 Leave a comment

I’m getting errors of block corruption in the alert.log of an Oracle 11g Release 2 test database:

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xFFFFFFFA7DBAF398] [PC:0x896E0BE, kcbgcur()+3026] [flags: 0x0, count: 1]

check value in block header: 0x46c2
 computed block checksum: 0x0
Reading datafile '/app/oracle/oradata/database2/sgirgea_01.dbf' for corruption at rdba: 0x0a808912 (file 42, block 35090)
Reread (file 42, block 35090) found same corrupt data (no logical check)
Corrupt Block Found
 RFN = 42, BLK = 35090, RDBA = 176195858
 SEGMENT OWNER = , SEGMENT TYPE = Temporary Segment
Hex dump of (file 42, block 35218) in trace file /app/oracle/diag/rdbms/database1/database1/trace/sid_w000_23395.trc
Corrupt block relative dba: 0x0a808992 (file 42, block 35218)
Bad header found during buffer read
Data in bad block:
 type: 35 format: 2 rdba: 0x01408992
 last change scn: 0x0000.000b6126 seq: 0x3 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x61262303
 check value in block header: 0xcffd
 computed block checksum: 0x0

In this case it’s not a data corruption,  I’ts due to a silly mistake in the creation of a tablespace.

I have two databases created on the same server (it’s a test server), and the datafiles of the two databases in different folders:

  • /app/oracle/oradata/database1: datafiles of the database 1.
  • /app/oracle/oradata/database2: datafiles of the database 2.

I’m testing an application with different database configurations. So, I created this user in database 1, with its own datafile, but by mistake I created the datafile on /app/oracle/oradata/database2, the folder for the other database’s datafiles.

Then, I did the same for the database 2, created the same user, with its own tablespace, and now, I created the datafile correctly in its own folder (/app/oracle/oradata/database2), with the same name as the other database’s datafile. This is an illustration of this mistake:

Oracle shared datafile

Oracle shared datafile

When I noticed the error in the alert log, I performed a backup of the user on both databases. Then, dropped the user in database 1 to recreate it again, but when I try to delete the tablespace in database 1 I get this error:

SQL@database1> drop tablespace sgirgea;
drop tablespace sgirgea
ERROR at line 1:
ORA-01122: database file 42 failed verification check
ORA-01110: data file 42: '/app/oracle/oradata/database2/sgirgea_01.dbf'
ORA-01210: data file header is media corrupt

Trying to alter the tablespace to bring it offline doesn’t works:

SQL@database1> alter tablespace sgirgea offline;
alter tablespace sgirgea offline
ERROR at line 1:
ORA-01122: database file 42 failed verification check
ORA-01110: data file 42: '/app/oracle/oradata/database2/sgirgea_01.dbf'
ORA-01210: data file header is media corrupt

I try to bring the tablespace offline with the database mounted, but I can not shutdown the database for the same reason:

SQL@database1> shutdown immediate;
ORA-01122: database file 42 failed verification check
ORA-01110: data file 42: '/app/oracle/oradata/database2/sgirgea_01.dbf'
ORA-01210: data file header is media corrupt

Prior to a shutdown abort, I tried to offline the datafile, instead of the tablespace:

SQL@database1> alter database datafile 42 offline;
alter database datafile 42 offline
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

But it’s not possible because the database is in NOARCHIVELOG mode, as this is a test environment.

Finally, I tried an offline drop of the datafile and it worked. Then just dropped the tablespace just including contents, not the datafile (without the “including contents and datafiles” clause, only “including contents“):

SQL@database1> alter database datafile 42 offline drop;

Database altered.

SQL@database1> drop tablespace sgirgea including contents;

Tablespace dropped.

Then I checked for the datafile and it’s still there, and it’s correctly attached to database2, and everything works fine on database 2. Just have to recreate the same user in database 1 to continue testing.

Please, feel free to leave your comments below.

EXP-00008: ORACLE error 1455 encountered

January 31, 2014 1 comment

I’m trying to export a user on a Oracle 11g Relase 2 database ( using my local client, Oracle

With certain users I’m getting this error:

$ exp system@sid file=sgirgeaext_"$(date +%Y%m%d)".dmp log=sgirgeaext_exp_"$(date +%Y%m%d)".log consistent=Y statistics=none owner=sgirgeaext

Export: Release - Production on Fri Jan 31 11:13:33 2014

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning and Real Application Testing options
Export done in UTF8 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SGIRGEAEXT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SGIRGEAEXT
About to export SGIRGEAEXT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SGIRGEAEXT's tables via Conventional Path ...
EXP-00008: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
EXP-00000: Export terminated unsuccessfully

I tried also not to exporting indexes (indexes=N), as some other blogs suggested, but no luck.

This is due to the different versions of my client export and the database target.

I tried the same export using the 11gR2 client and the export finished without errors.

Categories: 10gR2, 11gR2, oracle Tags: , ,

Error when creating oracle 11gR2 database with dbca: ORA-21561: OID generation failed

September 11, 2013 2 comments

I’m creating a new Oracle 11g release 2 database on Red Hat Enterprise Linux Server release 6.1 using the Database Configuration Assistant (dbca).

When I’ve finished all the configuration steps, create a database with dbca and I get this error:

$ dbca

ORA-21561: OID generation failed

The error is in the /etc/hosts file, as this is a virtual machine generated from a template (we’re using VMWare) that we reuse to create new virtual database machines.

I’ve to assign the correct IP and now the database creation is successful.

Categories: linux, oracle Tags:

Errors starting Oracle netca or dbca in Linux

September 2, 2013 6 comments

I’m configuring a new database on a recently created VMWare virtual server.

When I try to launch netca (or dbca, or whatever else), I get this error:

[oracle@sxxx admin]$ netca
 java.lang.NoClassDefFoundError: oracle/sysman/oii/oiil/OiilNativeException

Error: oracle/sysman/oii/oiil/OiilNativeException
 Oracle Net Services configuration failed. The exit code is 1

 [oracle@sxxx admin]$ dbca
 Exception in thread "main" java.lang.NoClassDefFoundError: oracle/sysman/oii/oiil/OiilNativeException
 at oracle.sysman.assistants.util.attributes.InitParamAttributes.initialize(
 at oracle.sysman.assistants.util.attributes.InitParamAttributes.<init>(
 at oracle.sysman.assistants.util.step.StepContext.<init>(
 at oracle.sysman.assistants.dbca.backend.Host.<init>(
 at oracle.sysman.assistants.dbca.ui.UIHost.<init>(
 at oracle.sysman.assistants.dbca.ui.InteractiveHost.<init>(
 at oracle.sysman.assistants.dbca.Dbca.getHost(
 at oracle.sysman.assistants.dbca.Dbca.execute(
 at oracle.sysman.assistants.dbca.Dbca.main(

First of all, I reviewed all my environment variables, then launched xclock to check that my X are well configured, and all that stuff was ok.

The problem is that the filesystem where the oracle binaries were installed were moved to another filesystem by our sysadmins, and the permissions of the entire oracle folder and subfolders were changed to root:root.Simply revert back permissions to oracle:dba solved this silly error.

Never trust on your sysadmins ;-D

Categories: linux, oracle Tags: ,