Monday, 7 October 2013

Breaking through redo corruption

Today we faced a not very welcome message - ORA-00333: redo log read error block 56010 count 8064. The database is in the NOARCHIVELOG mode and we do not perform backups ;-).
Of course I would not smile so eagerly if the problem would be serious - I must admit at once that there was a bright side of the situation - this is a test environment and rather easily able to reproduce, so take it into consideration, while looking what I did to this database.

At the beginning I must state that the traffic on this database was mainly First of all I started to clear logfiles (ALTER DATABASE CLEAR LOGFILE GROUP x;), but quickly run out of progress - the corrupted redo log was the CURRENT one i.e. the one I can not clear.
As we have no backup, the list of options is quite short - the second (and one of the last resorts is to use some hidden parameters to disable the recovery).
The two parameters I was interested in are _allow_resetlogs_corruption and _allow_read_only_corruption. It seems to me the first one is related more to redologs, while the second to datafiles. At the moment I assume one may try to use them together - the first one to get rid of the corrupted online redo, and the second one to open a database despite of corrupted datafiles.
As I found quite many articles on the subject (but mainly by googling and not on the MOS pages), I firstly realize it is enough to set _allow_resetlogs_corruption to TRUE in order to go forward. However when I did this, I hit the ORA-01139.
So I run recovery (RECOVER DATABASE UNTIL CANCEL), and it required archivelog with the 518 sequence (which is the sequence the corrupted online logfile was opened with) - suprisingly it went well, so I tried to open the database with the RESETLOGS option and it failed. Then I set both the _allow_resetlogs_corruption and _allow_read_only_corruption parameters to TRUE and start the database again. This time the instance terminated with the ORA-00704 bootstrap errors. As You already find out I failed to open it successfully - the next try bring the ORA-600 errors, and at this moment I gave up.
And finally a victory! I tried once again. This time I simply mounted the database and call the database recovery (with both parameters reset) - it failed with an info that I have to open the database, so I call the OPEN operation and it opened.

Wednesday, 2 October 2013

Why one can not connect to SYS with db link?

You may find the helpful article under this link (can-i-connect-a-database-link-as-sysdba).
If anything wrong with the link, try the article 119885 on the MOS.

Friday, 27 September 2013

Cloning 11gR2

In general it seems to be a simple operation.
First one needs to copy ORACLE_HOME content to the new location (local or remote). The documentation suggests the zip utility, but I think rsync is also a right tool (for a local copy switches -av, for a remote -avz).
Second call runInstaller
$ORACLE_HOME/oui/bin/runInstaller -silent -clone ORACLE_HOME="[the new home]" ORACLE_HOME_NAME="[the new home id]" ORACLE_BASE="/oracle"
The docs do not mention the ORACLE_BASE specification, yet this is inevitable as without it the cloning process exits with information about lack of ORACLE_BASE value.

Wednesday, 18 September 2013

Ora-30372 and what to do further

The error emerges at an mview creation. In my particular case this was an mview on a synonym, which indicated a remote view. The remote view is a subject of an access policy.
The whole issue is described on Oracle Support in the article Ora-30372: Fine Grain Access Policy Conflicts With Materialized View (Doc ID 604046.1).