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

11. Nov. 2008

Sooner or later it will come to a situation in which we have to transfer data between different database. Given the fact we have to do a normal database upgrade Oracle Export/Import will help us to do as long we will go with a full database export and all users, priviledges, permissions and quota will get transfered.

Now given the case that we based on schedules or migration plans just have to overtake single schemas world does look different. Oracle Import will expect theuser schema already exisiting and even try to create all database objects in relation to their old location within the former database.

Now I’m not going to explain you how to change tablespace or storage information within this posting … perhaps in another one … but will try to help you with pre-setting up the schema and additional security information before importing the data.

As for we work with some database system all data can for sure get queried and even stored within directly executable sql statement. Since years I’m working with an easy to use set of scripts collecting these information for me.

Feel free to download it from here and using it for your own pleasure.  When unpacked you can call the script 0_capture_security_information.sql within SQLPLUS and you will get generated another result set of scripts containing all users, roles and granted system and role priviledges stored with the database you are connected too.

Connecting now to the new target database and executing the scripts full or in parts will allow you to set up security there again.

« previousnext »