Since 2010, OraERP is a Oracle Forums, Community of Oracle Professionals including Fusion/Cloud Application Consultants, Enterprise Architects, ERP Cloud, HCM Cloud, CX Cloud and OCI Experts, ERP Experts, Oracle Apps Functional Consultants, Apps DBAs, DBAs, Cloud DBAs, Digital Architect, PaaS Experts, IaaS, OCI Architects, Technical Consultants, Fusion Middleware Experts, SQL, PL/SQL Developers and Project Managers. Welcome to OraERP.com Social Community, a friendly and active community of Oracle Technology Professionals who believe that technology can ‘make the world a better place’. By joining Oracle ERP Community you will have the ability to Post Topics, Receive our Newsletter, subscribe to threads and access many other special features. Registration is Quick and Simple. Get unlimited access to Oracle Tutorials, Articles, eBooks, Tools and Tips .
Thread Rating:
  • 22 Vote(s) - 2.5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Recover database without archivelogs
08-15-2015, 08:42 AM,
#1
Recover database without archivelogs
Inconsistent database recovery
 
Introduction:
 
This document provides high level steps in order to recover the database when all the archivelogs and the redo logs of the database has been lost.
 
Issue details and steps taken to resolve:
 
There might be cases when you end up having no backups and the disks of the database gets crashed or some one has deleted all the archivelogs of the database in a test environment where data might be critical.
 
The below is the error message received when we tried a recovery of the database.
 
SQL> recover database until cancel using backup controlfile;
 
ORA-00279: change 456781 generated at 10/19/2013 14:56:44 needed for thread 1
ORA-00289: suggestion : 234.arc
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘SYSTEM01.dbf’
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘SYSTEM01.dbf’
 
 
We tried getting any of the old backups and the archivelogs copy from the disk mirrors but unfortunately nothing seems to have helped.
 
After doing lot of research, we ended up setting up the below parameters and then started the database which helped in opening the database in an in-consistent state. Update the below parameters in the parameter file (if running in spfile, create a copy of the same).
 
_ALLOW_RESETLOGS_CORRUPTION = TRUE
UNDO_MANAGEMENT = MANUAL
 
Comment out the other parameters related to Undo tablespace, etc so that Oracle will not check the undo / rollback segments.
 
Once the above is done, startup the database in MOUNT state and then open the database with resetlogs.
If this fails with a ORA-600 error, then it might be because of the SCN being not updated correctly in the datafile headers and hence the SCN can be bumped slightly by restarting the database couple of times otherwise setting a numeric value for the “_minimum_giga_scn” parameter.
 
Since the datafiles header is not in a consistent state, the best option is to export and imnport the data into a new database. In our case, it was simple as the size of the database was around 120GB only.
 
 
References : Database team worked with self in this issue. Also, this should not be tried in production environments or big databases.
 
 
Thanks.
 
--------------------------------------------- End of Document ---------------------------------------------------------
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Query- How to check the Oracle database version Zishan Ali 0 3,492 08-17-2015, 12:35 AM
Last Post: Zishan Ali
  Query to Check Tablespace Size and frees pace in Oracle Database Ahmad Mujeeb 0 4,264 06-25-2015, 08:07 PM
Last Post: Ahmad Mujeeb
  Oracle 11gR2 database upgrade in Apps R12.1.1 Sara Khan 0 4,762 12-20-2013, 06:49 AM
Last Post: Sara Khan
  Oracle Database 11g on Enterprise Linux 5 and CentOS 5 Hassanmalik 0 3,763 12-09-2012, 06:44 AM
Last Post: Hassanmalik
  Convert 11gR2 non-RAC database to RAC database using rconfig abdulrehman 0 7,923 01-23-2012, 06:11 PM
Last Post: abdulrehman



Users browsing this thread: 1 Guest(s)