Working with Oracle, using TEMPORARY tablespaces with tempfiles is best practice today. Unfortunately there are two situations in which you may encounter trouble as for the tempfiles are missing and sort operations do no longer succeed.

Curiously and based on the fact that Oracle does not record checkpoint information in tempfiles, Oracle can even start up a database with a missing tempfile.

DBW0 will write to a trace file indicating the tempfile is not found, but the database opens normally and later on only sort operations will fail with some of the Oracle Errors ORA-01116, ORA-01157 and ORA-25153.

Such situation can be caused by simply the OS file related to a tempfile cannot be not found anymore or the logical tempfiles attached to the TEMPORARY tablespace do not exist anymore.

This can happen in case the tempfile was located on a disk that crashed, had a bad controller, or some other type of media failure, a recovery or cloning of the database was done before and/or finally and purely caused by human error.

Solution : Drop the logical tempfile and add a new one

SQL> alter database tempfile ‘/oracle/oradata/SID/tempfile.tmp’ drop;

Database altered.

SQL> select tablespace_name, file_name from dba_temp_files;

no rows selected.

SQL> alter tablespace temp add tempfile ‘/oracle/oradata/SID/tempfile.tmp’ size 100m;

Tablespace altered.

When transferring data from one server to another – maybe for backup reason – most people use tar to create an archive. Then they copy it to the new server and untar it or leave it as a tarfile as backup.

Working with UNIX and using a trick  you can transfer it with compression and speed up the whole transfer time a lot.

Using the output result of  a command directly as the command line input of a second command  using a  Unix Pipe the need of writing it down to disk first no longer exists. Finally accessing the remote system though a ssh tunnel will even transfer the compressed data over the network within one single step.

Invoking the following single command will transfer all your data within sourcedirectory an Server B and immediately store it locally  on Server B within a compress archive:

ssh username@hostname “cd /sourcedirectory/ ; tar czf – .” > /targetdirectory/filename.tgz

While the following variation:

ssh username@hostname “cd /sourcedirectory/ ; tar czf – .”> /targetdirectory/filename.tgz| tar xvf –

will directly extract it again for you on the your local system.

Messing up some setting with the system SPFILE can end us up with a database which does no longer start up. Unfortunately this can happens delayed as for it is possible to set new vales without making them active immediately.

Now a system SPFILE is no real editable file and therefore fixing it becomes difficult:

A typically problem which can occur leaving your database inoperable is specifying LOG_ARCHIVE_DEST within a Oracle Database Version 10g as for syntax has change in this version and it is easily done following good “old” behavior.

You will end up with an database complaining about:

SQL> startup mount force
ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST

Fixing this we have to startup the database with an alternative INIT.ORA file which we usually have from the database creation process. Don’t worry about it is recent or not as for our aim is just to get up the database up and running in mount status:
(if not at hand create an INIT.ORA file from scratch using an universal example)

SQL> startup mount pfile=’C:\oracle10g\admin\SID\pfile\init.ora’;
ORACLE instance started.

Total System Global Area 135339844 bytes
Fixed Size                  454468 bytes
Variable Size            109051904 bytes
Database Buffers          25165824 bytes
Redo Buffers                667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

Having the database up now again, we can create an editable INIT.ORA by reading the values within system SPFILE:

SQL> create pfile=’c:\temp\init.ora’ from spfile=’c:\oracle10g\database\spfileSID.ora’;
File created.

Now with the new editable INIT.ORA file containing all the actual configuration settings of our database (even the wrong one) we can go on fixing our issue and finally convert the INIT.ORA file again back into a system SPFILE:

SQL> create spfile=’c:\oracle92\database\spfileSID.ora’ from pfile=’c:\temp\init.ora’;
File created.

When we now shutdown the database and reopen it again it will start and open gain using the fixed SPFILE as before:

SQL> startup force
ORACLE instance started.

Total System Global Area 139534168 bytes
Fixed Size                  454488 bytes
Variable Size             113246208 bytes
…..