Tuesday, April 2, 2013

"_allow_resetlogs_corruption" recovery....

Please DO NOT use this in any production environment without Oracle Support...

*** Using this parameter should be your LAST option as you do not have any proper backups to bring your database online ***

One of our database was kind of un-opened mode due to system tablespace datafile corruption (for any reason like restored from old backup etc.,)

We are OK to loose some data and some tablespaces which were created later point than the SCN that system tablespace has. So, used the parameter "_allow_resetlogs_corruption" an un-documented (repeat Oracle will not support your database if you recover your database with this option without their support) parameter. Below these errors shows the use of this parameter and I was able to recover database and open.

Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01245: offline file 1 will be lost if RESETLOGS is done
ORA-01110: data file 1: '/oradata/db_name/datafile//system.282.757162799'

SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open

SQL> alter database datafile 1 online;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oradata/db_name/datafile/system.282.757162799'

Use of the parameter:


SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1469792256 bytes
Fixed Size                  2121312 bytes
Variable Size            1107296672 bytes
Database Buffers          352321536 bytes
Redo Buffers                8052736 bytes
Database mounted.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01248: file 19 was created in the future of incomplete recovery
ORA-01110: data file 19: '/oradata/db_name/datafile/file2.dbf'

I know for sure I do not need to worry about this datafile as I have data that I can put back in so marking it as OFFLINE.

SQL> alter database datafile 19 offline;

Database altered.

Now Open with resetlogs and you should be good. Take a FULL Backup ASAP.

SQL> alter database open resetlogs;

Database altered.

SQL>

Once again, Please do not use this parameter in any production or critical databases without Oracle Support and recommendation otherwise you will end up with inconsistency database.

Thursday, March 14, 2013

ORACLE error 29308 encountered

Got this error while doing TSPITR:


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
EXP-00008: ORACLE error 29308 encountered
ORA-29308: view TS_PITR_CHECK failure
ORA-06512: at "SYS.DBMS_PITR", line 889
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
host command complete

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/14/2013 14:08:50
RMAN-03015: error occurred in stored script Memory Script
RMAN-06135: error executing host command: Additional information: 256
Additional information: 11

RMAN>



Basically what it is telling is there are some objects in the tablespace that are dependent on some other tablespace which is not in the TSPITR list.

Running the following query shows me that there are some indexes those belong in SYSTEM.

TSPITR Check Query:
SELECT * 
FROM SYS.TS_PITR_CHECK 
WHERE (
        TS1_NAME IN ('DATA_8K') 
        AND TS2_NAME NOT IN ('DATA_8K')
      )
OR    (
        TS1_NAME NOT IN ('DATA_8K') 
        AND TS2_NAME IN ('DATA_8K')
      ); 

But that is not true because if I run dbms_tts.transport_set_check it wont show any violations:

begin
 sys.dbms_tts.transport_set_check('DATA_8K',TRUE,TRUE);
end;

select * from sys.transport_set_violations; -- returns no rows means DATA_8K tablespace objects are all self contained...

So, what is the issue with RMAN TSPITR!!!!

Its a bug identified and the issue is fixed in 11g. RMAN TSPITR has issues when IOT (Index Organized Tables) are present in the tablespace.

Bug#6620517: DO TSPITR SUPPORT IOT IN 10.2 Database.


Bug Attributes


B - Defect11.2
2 - Severe Loss of Service10.2.0.3.0
80 - Development to QA/Fix Delivered Internal268 - z*OBSOLETE: Microsoft Windows Server 2003 R2 (64-bit AMD64 and Intel EM64T)
Nov 9, 2007
Sep 15, 2011N/A
10.2.0.3.0Generic
Oracle

Related Products


Oracle Database ProductsOracle Database
Oracle Database5 - Oracle Database - Enterprise Edition

Wednesday, March 13, 2013

TNS-04404: no error caused by: oracle.net.config.ConfigException: TNS-04414: File error

Got this error while trying to create a database using DBCA.

TNS-04404: no error caused by: oracle.net.config.ConfigException: TNS-04414: File error caused by: TNS-04612

Nothing to worry or panic. Just check your tnsnames.ora file for entries and am pretty sure it will have some syntax errors like missing closed parenthesis etc.,