Printable View
Hi World,
I have a following question that make me very confusing, therefore I need your help.
Question:
I have a full backup (cold backup with shutdown before backup) on Sunday. DB run on archivelog mode. On Friday, the production machine is gone. Can I use the last backup (cold backup) and archive log files to recover point in time before accident?
Thanks for your attention
Xung
Yup, that's the idea.
Hi Marist89,
Thanks for your reply but I do not understand What do you mean?
Xung
Yes, you can recover your database with your last cold backup on Sunday and archive log files provided that you've got all the archived log files since your last cold backup.
You can recover your database to point-in-time provided you have valid cold backup and all archived log files from the time of cold backup to the point of failure. Whenever any datafile get corrupted , you just copy that datafile from cold backup (if it's not system) , make the datafile offline and startup the database..Issue the command
SQL>SET AUTORECOVERY ON;
SQL>RECOVER DATAFILE 'DISK/DATA1.ORA' AUTOMATIC;
if it's System datafile then you have to recover in mount mode.
SQL>RECOVER DATABASE AUTO;
Thanks for your answers.
whole production is gone. I mean redo log files, datafiles, controlfiles are gone... There are only archivelog files and last cold backup. Can I recover to point in time?
Xung
I don't think so. Not without your old control files. Sounds like you can only recover to your last COLD BACKUP point. Since you are using your old CONTROL FILES, they won't recognise that a failure has occured and therefore will not initiate recovery. Confimation?
The old control files won't know diddly about anything after the cold backup, so I confirm grjohnson. That is, of course, assuming Xung made copies of the control files when he/she did the cold backup. No control files at all? Game over.
I too agree with Johnson and stecal. assuming your coldbackup includes the controlfiles, redolog files and datafiles, You can just restore them and startup your database. I guess even you apply recover database kind of commands, you will get something similar to "Media Recovery Not Required". kind of messages.Quote:
Originally posted by Xung
Thanks for your answers.
whole production is gone. I mean redo log files, datafiles, controlfiles are gone... There are only archivelog files and last cold backup. Can I recover to point in time?
Xung
I am not sure, but logically speaking, It may be possible through using backup controlfile clause to rolling the archivelogs forword. Please NOTE I have not tried this. If it works please let me know. I hope some of the DBA Guru's will speak on this.
Hi world,
The Answer is "Yes". We can use old controlfile and roll forward with archivelog file. Following are some steps that I tested. It works actually!
***
This test scenario demonstrates how to apply archivelog files to a clean but
old backup.
Let's first shut the database down in it's current situation:
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
We make a clean cold backup of the database; namely, all files,
controlfiles, datafiles, etc. Then, start the database up again.
Checking the Archive_Dest directory, we see that the last archivelog file for
this test is 'arc84.1'. We'll come back to that later.
SVRMGR> connect internal/password
Connected.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 11907072 bytes
Fixed Size 49152 bytes
Variable Size 10657792 bytes
Database Buffers 1126400 bytes
Redo Buffers 73728 bytes
Database mounted.
Database opened.
As a test we create a new table, insert some rows, and then make some
logswitches:
SVRMGR> create table testtable(a number(2));
Statement processed.
SVRMGR> insert into testtable values(2);
1 row processed.
SVRMGR> insert into testtable values(2);
1 row processed.
SVRMGR> insert into testtable values(2);
1 row processed.
SVRMGR> insert into testtable values(2);
1 row processed.
SVRMGR> insert into testtable values(2);
1 row processed.
SVRMGR> commit;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
Again checking the Archive_Dest directory, we now see the last archivelog file
is arc87.1. (Three onwards from the arc84.1 we had previously).
We now intend to restore the backup we made earlier.
The plan is to apply the three new archivelog files, so first we start the
database up again:
SVRMGR> connect internal/password
Connected.
SVRMGR> startup mount
ORACLE instance started.
Total System Global Area 11907072 bytes
Fixed Size 49152 bytes
Variable Size 10657792 bytes
Database Buffers 1126400 bytes
Redo Buffers 73728 bytes
Database mounted.
And now we issue the command to start the recovery and we apply all the
archivelog files that are newer than any of the control or datafiles that we
restored.
SVRMGR> alter database recover until cancel using backup controlfile;
alter database recover until cancel using backup controlfile
*
ORA-00279: change 216252 generated at 04/09/99 16:39:56 needed for thread 1
ORA-00289: suggestion : D:\ORA80\DATABASE\ARCHIVE\ARC85.1
ORA-00280: change 216252 for thread 1 is in sequence #85
SVRMGR> alter database recover continue default;
alter database recover continue default
*
ORA-00279: change 216265 generated at 04/09/99 16:45:34 needed for thread 1
ORA-00289: suggestion : D:\ORA80\DATABASE\ARCHIVE\ARC86.1
ORA-00280: change 216265 for thread 1 is in sequence #86
ORA-00278: log file 'D:\ORA80\DATABASE\ARCHIVE\ARC85.1' no longer needed for
this recovery
SVRMGR> alter database recover continue default;
alter database recover continue default
*
ORA-00279: change 216266 generated at 04/09/99 16:45:40 needed for thread 1
ORA-00289: suggestion : D:\ORA80\DATABASE\ARCHIVE\ARC87.1
ORA-00280: change 216266 for thread 1 is in sequence #87
ORA-00278: log file 'D:\ORA80\DATABASE\ARCHIVE\ARC86.1' no longer needed for
this recovery
SVRMGR> alter database recover continue default;
alter database recover continue default
*
ORA-00279: change 216267 generated at 04/09/99 16:46:00 needed for thread 1
ORA-00289: suggestion : D:\ORA80\DATABASE\ARCHIVE\ARC88.1
ORA-0028.
0: change 216267 for thread 1 is in sequence #88
ORA-00278: log file 'D:\ORA80\DATABASE\ARCHIVE\ARC87.1' no longer needed for
this recovery
SVRMGR> alter database recover continue default;
alter database recover continue default
*
ORA-00308: cannot open archived log 'D:\ORA80\DATABASE\ARCHIVE\ARC88.1'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
We could have stopped after 87 but there is no harm in continuing until the
system cannot find the required file.
Now we stop the recovery and open the database:
SVRMGR> alter database recover cancel;
alter database recover cancel
Statement processed.
SVRMGR> alter database open resetlogs;
Statement processed.
And, finally, we prove that the new information that was not in the backup
and was only stored in the archivelog files is now recovered:
SVRMGR> select * from testtable;
A
----------
2
2
2
2
2
5 rows selected.
SVRMGR>.
Congrats. That means my logic works. right!
Hmmm, cool. Interesting indeed.
As you've already found out, I disagree. You can find the procedure to do this at http://metalink.oracle.com/metalink/...T&p_id=69468.1Quote:
Originally posted by grjohnson
I don't think so. Not without your old control files. Sounds like you can only recover to your last COLD BACKUP point. Since you are using your old CONTROL FILES, they won't recognise that a failure has occured and therefore will not initiate recovery. Confimation?
Thanks marist89,
Although I see that Metalink article has already been cut and pasted two posts ago. That's fine, but I like to see people reference their material and explain where they got it from.
Cheers,