22. Nov. 2008

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.

11. Nov. 2008

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

…..

11. Nov. 2008

Fixing block corruptions within a Database is always a messy thing. Not because it is not do-able, but it always means some service disruptions combined with a recovery activity of parts of your database.

Regardless you have to analyse and fix the real issue e.q. a dying controller or harddisk, or perhaps a messy memory modul first, in some cases you can speed up time for fixing the issue by just reading out your corrupted table and recreating it.

Connect as a SYSDBA user you can mark the table as needing to skip corrupt blocks with the following command:

execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘schema’,’tablename’);

This will tell the Oracle Kernel to just read over all corrupted blocks and go on with serving your query until the end.

After you have issued the command about you should be able to issue a CREATE TABLE AS SELECT operation against the corrupt table to extract data from all non-corrupt blocks and/or EXPORT the remaining healty content of the table.

CREATE TABLE salvage_table AS SELECT * FROM corrupt_table;

But be aware about you will for sure loose data, which is stored within those corrupt blocks!!!

Once you salvaged the date you should now drop the corrupt table and recreate it from scratch. If for any reason you should wish to keep the damaged table though, you can clear the attribute for a table with this command:

execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘schema’,’tablename’, flags=>dbms_repair.noskip_flag);

But you will end up with some no longer query able table again!!!

To get a feeling about how much data and blocks you have lost, you can have a look into your trace files. Whenever a session skips a corrupt block due to SKIP_CORRUPT being set then a message is written to the trace file (not the alert log) for each block skipped in the form like this:

table scan: segment: file# 6 block# 11
skipping corrupt block file# 6 block# 12

« previousnext »