Incomplete Recovery / Resetlogs On Just One Restored Datafile?
Feb 4, 2013
Basic situation is this: Oracle 10g database, good RMAN backup from a few days ago. We lost one datafile due to storage corruption. I restored the datafile from RMAN, no problem, and started to roll forward with our archive logs. Problem: one log is corrupted, can't be read, no other copies of it. We are stuck, cannot roll forward any further. The database is up (all activity suspended for now) -- only this one file is offline. Everything else is fine.
My question: Can i do something similar to "open resetlogs" with just the one datafile? Most of what was in the file was indexes, which can be easily dropped & recreated. But a few extents were data which I want to retain. It's old data and the archive logs subsequent to the one on which we are stuck should be pretty much irrelevant. So if i can 'force' the datafile to open up, i believe we should be OK with the little bit of data that's in there, even if we don't apply the remaining archive logs. If it's not 100% consistent we can live with that.
SQL> alter database mount; Database altered. SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01139: RESETLOGS option only valid after an incomplete database recovery
restored from tape a backup then issued rman command
RMAN> restore database;
Starting restore at 21-MAY-13 Finished restore at 21-MAY-13
RMAN> alter database open;
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 05/21/2013 15:36:15 ORA-01190: control file or data file 1 is from before the last RESETLOGS ORA-01110: data file 1: '/u02/oradata/system01.dbf' [code]....
We have cloned the db and after opening the db with resetlog option we came to know that one of the user db file is not part of the db and is showing the below error while checking the status and attempting to online it. When checking the size and availability of the dbfile it shows everything without errors.
SQL> alter database datafile 50 online; alter database datafile 50 online * ERROR at line 1: ORA-01122: database file 50 failed verification check ORA-01110: data file 50: '+DATA/orcl/orcldata.291.730828357.dbf' ORA-01251: Unknown File Header Version read for file number 50
I am performing testing on the database recovery after upgrading my backup solution software. I attempted to restore from tape media that is dated back at April 1st. My colleague have perform the same recovery from the same tape media during middle of April.
After restore, I encounter some problem and I could no longer up the database anymore.
These are the steps I have taken to perform recovery. It is being perform when the database in mount mode
SQL> recover database using backup controlfile until TIME '2012-04-01:21:55:00' ORA-00283: recovery session canceled due to errors ORA-19909: datafile 1 belongs to an orphan incarnation ORA-01110: data file 1: '/oracle/AP1/sapdata1/system_1/system.data1'
SQL> shutdown immediate; ORA-01109: database not open
SQL> alter database open resetlogs; alter database open resetlogs *
ERROR at line 1: ORA-01190: control file or data file 1 is from before the last RESETLOGS ORA-01110: data file 1: '/oracle/AP1/sapdata1/system_1/system.data1'
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: 'F:APPADMINISTRATORORADATATHIRDBSYSTEM01.DBF'
To get rid of this , I tried following,
SQL> recover database using backup controlfile until cancel; ORA-00279: change 1004739 generated at 09/26/2011 10:31:34 needed for thre ORA-00289: suggestion : F:APPADMINISTRATORFLASH_RECOVERY_AREATHIRDBARCHIVELOG2011_09_27O1_M
I have the RMAN full database backup of oracle 10g (10.2.0.3) on Sun Solaris OS which i want to restore on oracle 11g (11.2.0.3) on Linux OS. The backup pieces was transferred to oracle 11g server manually in binary mode.
the backup is taken in above format. I know the ORACLE_SID and dbid of the database from which the backup has been taken. whenever i tried the following command
mepc@tcstctmatson:/mepc_backup/May22fullbkp$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jul 31 12:14:54 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: MEPC (DBID=1595278680)
RMAN> shutdown; using target database control file instead of recovery catalog database closed database dismounted Oracle instance shut down
RMAN> startup nomount;
connected to target database (not started) Oracle instance started
i have an issue with RMAN incremental level 0 backup.it start by connecting RMAN and then RMAN catalog and then input datafile but when it reach to allocate channel 1 so there it just stop and no more log generated , no message of failure or anything else.it just says "allocating channel c1.. and then no more log.fol command is there in the our script
connect target / connect catalog rman/passwd@backup run (allocate channel c1 type disk format '/export/home/oracle/dump/${ORACLE_SID}... .rman';backup tag=full_backup incremental level 0 database; release channel c1; ) exit EOF
I have noticed a block corruption issue in my RAC database. Its an index , how to recover a corrupted index in system datafile.
SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = 5 3 and 147551 between block_id AND block_id + blocks - 1; TABLESPACE_NAME SEGMENT_TYPE OWNER
I have no backup taken in my oracle server.Today morning i created a data file and unknowingly deleted it. Now i need to restore the datafile with its contents without using RMAN.
I have one database which is fully restored from client backup (RMAN). Database was up and running perfectly. By mistake I have deleted one schema/user and its tablespace using drop command. I have RMAN backup so want to restore that schema's datafile from the backup but when I run below statement it throwing error :
run { allocate channel c1 device type disk ; set newname for datafile 45 to '/ora/oradata/JSIP1/data.dbf'; RESTORE datafile 45; switch datafile 45; recover datafile 45; SQL ' alter database datafile 45 online ' ; } RMAN-20201: datafile 45 not found in recovery catalog
or If I go and try to restore the tablespace of that schemas using below block it throws error:
run { allocate channel c1 device type disk ; RESTORE TABLESPACE DATA; RECOVER TABLESPACE DATA; SQL 'ALTER TABLESPACE DATA ONLINE'; } RMAN - 20202 Tablespace not found in the recovery catalog RMAN -06019 could not transalate tablespace name "DATA"
how to restored drop users and tablespace data from the RMAN backup.
Someone(a non DBA) removed the only sysaux datafile from the server using the rm command Obviously, the DB is still up and none of the schemas in the database have their objects right now.
This is not a critical database as you can see when I say that I have NO backup whatsoever. Even though I have the option of deleting and recreating the database, i want to know if I can avoid doing that.
Creating a file on the server with the same name OR creating a new datafile for the tablespace OR recreate the sysaux tablespace ?But how to do any of these? What else should be done ?
RMAN-06169: could not read file header for datafile 40 error reason 1 RMAN-06169: could not read file header for datafile 41 error reason 1 RMAN-06169: could not read file header for datafile 40 error reason 1 released channel: oem_disk_backup RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup command at 06/14/2012 07:54:43 [code]...
Could we skip the backup of the above missing files using RMAN -> backup database skip inaccessible;On further research, this would compromise tablespace SYSAUX recoverability...Questions:
1) What caused the above missing files to get created? 2) What is recommended: Recover the datafiles?
how i can change backup datafile name into its original file name,bcoz at time backup datafile name is different and how i can change that name into its original datafile name so i apply recovery command in user managaed recovery..
I'm new to Oracle. I took cold backup of my database. Then, i removed my whole database while database was down.Then i restored the whole database from backup taken earlier. Now, when i try to open the database. it does not open up instead gives the below message.
Total System Global Area 1288949760 bytes Fixed Size 1336288 bytes Variable Size 754977824 bytes [code]....
I have this Error in PL /SQL procedure ORA-06550: line 6, column 12:PLS-00320: the declaration of the type of this expression is incomplete or malformedORA-06550: line 6, column 12:PL/SQL: Item ignoredwhere this is my procedure
CREATE OR REPLACE PROCEDURE MOAMALAT."IO_EMP_REP" (P_FROMDATE IN NUMBER,P_TODATE IN NUMBER,P_EMPID IN NUMBER,RCT1 OUT GLOBALPKG.RCT1)ASBEGINOPEN RCT1 FOR SELECT COUNT (I.CORRESPONDENCENUMBER) cont,EMP.FULLNAME empname,D.DEPARTMENTNAME deptnameFROM MOAMALAT.IO_INCOMING i,MOAMALAT.IO_EMPLOYEES emp,MOAMALAT.IO_DEPARTMENTS dWHERE I.RECEIVEDBY = (SELECT EM.USERIDFROM MOAMALAT.IO_EMPLOYEES emWHERE EM.EMPLOYEEID = P_EMPID)AND I.RECEIVEDBY LIKE EMP.USERID--and EMP.DEPARTMENTID=1900AND I.RECEIVEDBYDEPARTMENTID = D.DEPARTMENTIDAND I.CORRESPONDENCEDATE BETWEEN
I have a database in which two table contains millions of data and the whole database size is getting more bigger. is there any option to archive the old data which has stored before 2012 and can be restored when the data required for 2011 or 2010 needed.
I have 2 seperate rman backup one backup is of only datafile ,spfile and controlfile which i am able to restore and recover without any problem say bkp1 taken at 10 am
other set of backup is of only archive log files of same day but later time than datafile backup say bkp2 taken at 8pm...if i restore and recover bkp1 and try to restore bkp2 it gives error datafile exist (possibly due to fact both backup have control file)
if i just restore bkp1 and try to restore bkp2 so that i can do one recover at time it gives error datafile permission issue (possibly due to fact both backup have control file)
I want to restore database upto 8pm time how can i use both bkp1 and bkp2 to do it(restore datafiles and apply all archive logs on it)
On database A we have full backups (not incrementals) every morning at 3:00 AM including archive logs. When I query rman (list backup) I see the full at 3 AM (in two backup pieces, each in its own backup set) as well as the backup piece containing archive logs through 9:00 AM.
Due to a data issue at around 10AM, I attempted to do an incomplete recovery to 8:00 AM (data loss is not a concern) - once this was complete I opened the database with resetlogs.
I subsequently discovered that the data issue had occurred closer to 6:00 AM and I am now trying to restore to 5:00 AM. This is failing with the 'UNTIL TIME IS BEFORE RESETLOGS' error. I read up on incarnations, but my previous incarnation is from November, and when I reset the database to this incarnation and attempt a restore I get datafile 1 (2,3) is not available for restore.
how do I basically just restore the full 3:00 AM backup and apply the archive logs through 5:00 AM? Do I need to do a recover until cancel or something? And should I reset the incarnation?
I am running 10.1.0 and the database went down and I have been trying to get it started again. I have worked through several errors but stuck on this one.When trying to run startup I get this error ORA-01589 must use RESETLOGS or NORESETLOGS option for database open.
I am trying to create a clone database using hot backup of a database .
STEPS THAT I FOLLOWED ------------------------------------
LET ----- >CURRENT_DB NAME=DEV CLONE DATABASE NAME=DEVCLONE
steps PERFORMED FORM DEV DB ----------------------------------------------- - put the database in backup mode using 'alter database begin backup' - copy all the data files to a different folder - during copy i have performed some operations on the DB (creating users, tables, dmls etc...) - in between copying i also performed log switch - after completion of copy , "alter database end backup" - created a backup control file in a human readable format (alter database backup controlfile to trace as ........)
steps performed for clone DB side ((DEVCLONE) ------------------------------------------------------------------- - created a parameter file for the database . - modified the backup control file so that it will point to the location of copied destination of datafiles - set the ORACLE_SID - then 'sqlplus / as sysdba - starup nomount - run the modified control file ( created a control file for the clone database) - recover the database using "recover database using backup controlfile" I have provided the archive files that it was asking for (archive logs that has been generated in DEV DB) then i canceled the recovery by typing "cancel" - recover database using backup controlfile until cancel; then typed "cancel" - then try to open the database with open resetlogs but it showed below error
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: 'D:DATA_GUARDDEVHOTSYSTEM01.DBF'
Errors in file /oracle/BWP/saptrace/usertrace/bwp_ora_2728058.trc: ORA-01114: IO error writing block to file 1030 (block # 602122) ORA-27063: number of bytes read/written is incorrect IBM AIX RISC System/6000 Error: 28: No space left on device Additional information: -1 Additional information: 180224
But this file_id i don't have in my database, i am making these queries:
SQL> select FILE_ID from dba_temp_files order by FILE_ID;
FILE_ID ---------- 1 2 3 4 5 6 7 8 9 [code]....
I don't have this file_id, why alert.log is showing me it? Of course, nobody has created this datafile and nobody has removed it too.
I am using 11GR2 and looking to find out a way , where in I able to extract the data files name in such a manner , where it lists data file in order of mount points. say data1 first and then so on.. Snippet from my data files
like /db/ptmtrain/data1/system01.dbf/db/ptmtrain/data1/undotbs01.dbf/db/ptmtrain/data2/sysaux01.dbf/db/ptmtrain/data2/rbs03.dbf/db/ptmtrain/data2/rbs01.dbf/db/ptmtrain/data3/tools01.dbf/db/ptmtrain/data3/rbs02.dbf/db/ptmtrain/data23/sans01.dbf/db/ptmtrain/data24/users01.dbf/db/ptmtrain/data25/users02.dbf/db/ptmtrain/data26/users03.dbf
