ORA-25153: Temporary Tablespace is Empty

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. Once while exporting table through Data Pump ora-25153 occurs.

C:\>expdp system/ramtech@noida tables=neer.test dumpfile=neer_test.dmp logfile=exp_neerlog.log
Export: Release 11.1.0.6.0 - Production on Tuesday, 19 April, 2011 12:47:37
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_LOB", line 555
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3487
ORA-06512: at line 1

Cause :: The cause for the ORA-25153 error is because attempt was made to use space in a temporary tablespace with no files (no datafiles defined).

Solution  :: To solve this problem, i just added a temp file (datafiles) to the TEMP tablespace by using ADD TEMPFILE command,

SQL> alter tablespace temp add tempfile 'D:\oracle\oradata\noida\temp02.dbf' size 50m;
Tablespace altered.

If we check and found that TEMP tablespace already has data files, check the default temporary tablespace for all users and your database and set the default temporary tablespace to a valid temporarary tablespace.

To check the default temporary tablespace of the database:
SQL> select property_name, property_value from database_properties;

Performance Tuning of Data Pump

The Data Pump utilities are designed especially for very large databases. If our site has very large quantities of data versus metadata, we should experience a dramatic increase in performance compared to the original Export and Import utilities. 

The Data Pump Export and Import utilities enable us to dynamically increase and decrease resource consumption for each job. This is done using the PARALLEL parameter to specify a degree of parallelism for the job. (The PARALLEL parameter is the only tuning parameter that is specific to Data Pump.) For maximum throughput, do not set PARALLEL to much more than 2x the CPU count.

There are few initialization parameters that affect Data Pump performance. Setting  certain initialization parameters can affect the performance of Data Pump Export and Import. In particular, we can try using the following settings to improve performance, although the effect may not be the same on all platforms.

DISK_ASYNCH_IO=TRUE

DB_BLOCK_CHECKING=FALSE

DB_BLOCK_CHECKSUM=FALSE

Additionally, the following initialization parameters must have values set high enough to allow for maximum parallelism:

PROCESSES

SESSIONS

PARALLEL_MAX_SERVERS

To check the performance by setting one of the parameter say of parallel. Click here 

Loss Of Control-file in various Scenario's

A Control file is a small binary file that is part of an Oracle database. The control file is used to keep       track of the database's status and physical structure. The control file is absolutely crucial to database operation . Here , we will discuss the various scenario's when control file(s) get lost or corrupt.

CASE 1 : If one of the controlfile get lost or corrupted 

when the database is shut down and on  startup we get the following error due to loss of controlfile.

C:\>sqlplus sys/xxxx@noida as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Apr 18 15:41:33 2011

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  318046208 bytes

Fixed Size                  1332920 bytes

Variable Size             239077704 bytes

Database Buffers           71303168 bytes

Redo Buffers                6332416 bytes

ORA-00205: error in identifying control file, check alert log for more info 

Checked the Alert log file and the following information are in  the alert log file.

ALTER DATABASE   MOUNT

Mon Apr 18 15:42:12 2011

ORA-00210: cannot open the specified control file

ORA-00202: control file: 'D:\ORACLE\ORADATA\NOIDA\CONTROL02.CTL'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

Mon Apr 18 15:42:14 2011

Checker run found 1 new persistent data failures

ORA-205 signalled during: ALTER DATABASE   MOUNT

To solve this issue, copy one of the existing control file (say control01.ctl or control03.ctl ) and paste it where the missing  control file was earlier residing and rename the controlfile which one is missing, as in above example, control file (CONTROL02.CTL) is missing and then following the below steps:

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database ;

NAME          OPEN_MODE

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

NOIDA          READ WRITE

CASE  2:  When all the controlfile are lost 

If  we  have  valid  backup and  if  all  the  control files  are  lost  then  we  can  recover  the control  files from autobackup of controlfile or by specifying the location of autobackup control file.

C:\>sqlplus sys/xxxx@noida as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Apr 18 16:21:55 2011

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup nomount

ORACLE instance started.

Total System Global Area  318046208 bytes

Fixed Size                  1332920 bytes

Variable Size             272632136 bytes

Database Buffers           37748736 bytes

Redo Buffers                6332416 bytes

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\>rman target sys/xxxx@noida

Recovery Manager: Release 11.1.0.6.0 - Production on Mon Apr 18 16:33:34 2011

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

connected to target database: NOIDA (not mounted)

RMAN> restore controlfile from 'D:\orcl_bkp\cf\C-1502483083-20110418-01';  (location of controlfile)

Starting restore at 18-APR-11

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

output file name=D:\ORACLE\ORADATA\NOIDA\CONTROL01.CTL

output file name=D:\ORACLE\ORADATA\NOIDA\CONTROL02.CTL

output file name=D:\ORACLE\ORADATA\NOIDA\CONTROL03.CTL

Finished restore at 18-APR-11

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 18-APR-11

Starting implicit crosscheck backup at 18-APR-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=153 device type=DISK

Crosschecked 7 objects

Finished implicit crosscheck backup at 18-APR-11

Starting implicit crosscheck copy at 18-APR-11

using channel ORA_DISK_1

Finished implicit crosscheck copy at 18-APR-11

searching for all files in the recovery area

cataloging files...

no files cataloged

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 19 is already on disk as file D:\ORACLE\ORADATA\NOIDA\RE

archived log file name=D:\ORACLE\ORADATA\NOIDA\REDO01.LOG thread=1 sequence=19

media recovery complete, elapsed time: 00:00:05

Finished recover at 18-APR-11

RMAN> alter database open resetlogs;

database opened

CASE 3 :   When we donot have any backup and and all control files are lost or corrupted

SQL> startup nomount

ORACLE instance started.

Total System Global Area  318046208 bytes

Fixed Size                  1332920 bytes

Variable Size             281020744 bytes

Database Buffers           29360128 bytes

Redo Buffers                6332416 bytes

Now we create the controlfile manually on command prompt 

SQL> CREATE CONTROLFILE REUSE DATABASE  "NOIDA"   NORESETLOGS archivelog

MAXLOGFILES 5

MAXLOGMEMBERS 3

MAXDATAFILES 10

MAXINSTANCES 1

MAXLOGHISTORY 113

LOGFILE

GROUP 1 'D:\oracle\oradata\noida\REDO01.LOG' SIZE 50M,

GROUP 2 'D:\oracle\oradata\noida\REDO02.LOG' SIZE 50M,

GROUP 3 'D:\oracle\oradata\noida\REDO03.LOG' SIZE 50M

DATAFILE

'D:\oracle\oradata\noida\SYSTEM01.DBF' ,

'D:\oracle\oradata\noida\USERS01.DBF' ,

'D:\oracle\oradata\noida\EXAMPLE01.DBF' ,

'D:\oracle\oradata\noida\SYSAUX01.DBF' ,

'D:\oracle\oradata\noida\TRANS.DBF' ,

'D:\oracle\oradata\noida\UNDOTBS01.DBF'   ;

Control file created.

SQL> archive log list

Database log mode                              Archive Mode

Automatic archival                               Disabled

Archive destination                              D:\archive\

Oldest online log sequence                  1

Next log sequence to archive              1

Current log sequence                          1

SQL> select first_change# ,group# from v$log;

FIRST_CHANGE#     GROUP#

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

      1313491          1

            0                3

            0               2

SQL> alter database open;

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

NOIDA     READ WRITE

Enjoy   J J J

Create Control file Manually. When and How ?

The control files of a database store the status of the physical structure of the database. The control file is absolutely crucial to database operation .

Control File contains

> Database information (RESETLOGS SCN and their time stamp)

> Archive log history

> Tablespace and datafile records

(filenames, datafile checkpoints, read/write status, offline or not)

> Redo Logs (current online redo log)

> Database’s creation date

> database name

> current archive log mode

> Log records (sequence numbers, SCN range in each log)

> RMAN catalog

> Database block corruption information

> Database ID, which is unique to each DB

If the controlfile is lost, it is somewhat difficult to do a recovery because the database cannot be mounted for a recovery. The controlfile must be recreated. So We can Manually create a new control file for a database using the CREATE CONTROLFILE statement. The following statement creates a new control file for the  database (a database that formerly used a different database name) .

When to Create New Control Files :

It is necessary for us to create new control files in the following situations:

1.) All control files for the database have been permanently damaged and we do not have a control file backup.

2.) We want to change the database name. For example, we would change a database name if it conflicted with another database name in a distributed environment.

3.) The compatibility level is set to a value that is earlier than 10g, and we must make a change to an area of database configuration that relates to any of the following parameters from the CREATE DATABASE or CREATE CONTROLFILE commands: MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES. If compatibility is 10g or later, we do not have to create new control files when we make such a change; the control files automatically expand, if necessary, to accommodate the new configuration information.

For example, assume that when we created the database or recreated the control files, we set MAXLOGFILES to 3. Suppose that now we want to add a fourth redo log file group to the database with the ALTER DATABASE command. If compatibility is set to 10g or later, we can do so and the controlfiles automatically expand to accommodate the new logfile information. However, with compatibility set earlier than 10g, our ALTER DATABASE command would generate an error, and we would have to first create new control files .

Command to Create Controlfile Manually 

C:\>sqlplus sys/ramtech@noida as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Apr 18 17:31:50 2011

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> STARTUP NOMOUNT

SQL> CREATE CONTROLFILE REUSE DATABASE  "NOIDA"

NORESETLOGS archivelog

MAXLOGFILES 5 

MAXLOGMEMBERS 3 

MAXDATAFILES 10 

MAXINSTANCES 1 

MAXLOGHISTORY 113

LOGFILE 

GROUP 1 'D:\oracle\oradata\noida\REDO01.LOG' SIZE 50M,

GROUP 2 'D:\oracle\oradata\noida\REDO02.LOG' SIZE 50M,

GROUP 3 'D:\oracle\oradata\noida\REDO03.LOG' SIZE 50M

DATAFILE 

'D:\oracle\oradata\noida\SYSTEM01.DBF' , 

'D:\oracle\oradata\noida\USERS01.DBF' , 

'D:\oracle\oradata\noida\EXAMPLE01.DBF' , 

'D:\oracle\oradata\noida\SYSAUX01.DBF' ,

'D:\oracle\oradata\noida\TRANS.DBF' ,

'D:\oracle\oradata\noida\UNDOTBS01.DBF' ;

Specify RESETLOGS if we want Oracle to ignore the contents of the files listed in the LOGFILE clause. The log files do not have to exist but each redo_log_file_spec in the LOGFILE clause must specify the SIZE parameter. Oracle will assign all online redo log file groups to thread 1 and will enable this thread for public use by any instance. We must then open the database using ALTER DATABASE RESETLOGS.

NORESETLOGS will use all files in the LOGFILE clause as they were when the database was last open. These files must exist and must be the current online redo log files rather than restored backups.Oracle will reassign the redo log file groups to re-enabled threads as previously assigned.

Enjoy       J J J 

How to Determine the Name of the Trace File to be Generated

In many cases we need to find out the name of the latest trace file generated in the USER_DUMP_DEST directory. What we usually do is, that we physically go to the USER_DUMP_DEST location with the operating system browser and sort all the files by date and look for latest files. We can remove this hassle easily if we know what would be the trace file name in advance. Let's have a look ...

Demo : 1 
C:\>sqlplus sys/xxxx@noida as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Apr 18 17:44:49 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database backup controlfile to trace;
Database altered.

The above Command will generate the trace file inside USER_DUMP_DEST. Let's check the location of USER_DUMP_DEST. If we are using Sql*plus then issue,

SQL> show parameter user_dump_dest
NAME                         TYPE           VALUE
--------------                --------     -----------------------------------------------------
user_dump_dest        string       d:\oracle\diag\rdbms\noida\noida\trace

Here the latest files are for latest trace . Sometimes, we may not get the right trace file .Now it would be quite easy task if we knew the name of the trace file to be generated by ALTER DATABASE command. In advance we can get the trace file name as 

SQL> SELECT s.sid, s.serial#, pa.value || '\' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
          '_ora_' || p.spid || '.trc'  AS trace_file        FROM   v$session s,  v$process p, v$parameter pa
   WHERE  pa.name = 'user_dump_dest'     AND    s.paddr = p.addr
   AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
   SID            SERIAL#                     TRACE_FILE
---------        ----------           -----------------------------------------------------------------------
 110             312                  d:\oracle\diag\rdbms\noida\noida\trace\noida_ora_3552.trc
  
the trace file to be generated now will be named as noida_ora_3552.trc . So now issuing, "alter database backup controlfile to trace" will generate the file named d:\oracle\diag\rdbms\noida\noida\trace\noida_ora_3552.trc

Demo : 2 
This method is much simple and easy to identify the trace file. Let's have a look on another demo .

C:\>sqlplus sys/xxxx@noida as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Apr 18 17:49:49 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show   parameter    user_dump_dest
NAME                             TYPE                    VALUE
---------------------           --------       ----------------------------------------
user_dump_dest          string           d:\oracle\diag\rdbms\noida\noida\trace

SQL> alter session set tracefile_identifier='mytracefile' ;
Session altered.
SQL>  alter database backup controlfile to trace; 
Session altered.


Now, go to the user_dump_dest location and find the trace file having name "mytracefile" . In mycase the name is   "noida_ora_3552_mytracefile.trc"

The difference between the two demo is that first demo is on system level so it will give all the trace file generated by different session whereas in second case , it will show  the trace file for particular session only . The another difference between is that in first demo we have to fire the command and then check the tracefile but in second demo we have to set the trace file name so that we can easily identify the correct trace file .

Enjoy      :-)