rman restore from consistent backup
markg...@yahoo.com
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
Production
Could someone help me please.
We are starting to use RMAN.
We have a test database running in noarchivelog mode.
We took a consistent backup (mount) on Feb 5 20:55
(backup as compressed backupset database include current controlfile),
and another consistent backup on Feb 6 11:05.
There was some database activity since then.
This morning, Feb 9, we restored database:
(restore database).
When we tried to open database, we get:
RMAN> alter database open;
RMAN-00571:
===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571:
===========================================================
RMAN-03002: failure of alter db command at 02/09/2009 10:32:06
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u08/oradata/tst/system01.dbf'
We thought that you could open a consistent backup, without recovery.
Is that incorrect ?
Is there any way to do that ?
Thank you.
ddf
Without seeing what you used as a backup command to RMAN for this
database it's difficult to say what, exactly, has gone awry. Post
your backup script and someone may possibly be able to assist you
further.
David Fitzjarrell
markg...@yahoo.com
>
> - Show quoted text -
The backup script was just:
backup as compressed backupset database include current controlfile
Thank you.
hpuxrac
If you are running in noarchivelog mode the only way to use rman is to
take a backup when the database is shutdown ( not open ).
Think about buying Robert Freeman's most recent ( 10g ) RMAN book.
Charles Hooper
Hpuxrac,
Some clarification. When you state shutdown (not open), do you mean
completely shut down, or just that the database is mounted? I have
that RMAN book by Robert Freeman. Page 27 indicates that for a
database in NOACHIVELOG mode, the correct way to backup the database
is to shut down the database, backup the files, and then restart the
database. Page 245 states "RMAN will not prevent you from doing a
tablespace or datafile backup in NOARCHIVELOG mode (as long as the
database is not open). However, these backups are not really all that
usable when the database is in NOARCHIVELOG mode (unless you back up
all the tablespaces and datafiles at the same time)."
Contrast that advice to the advice shown here in "RMAN Recipes for
Oracle Database 11g" (another book that I own):
http://books.google.com/books?id=qISYkSBV2hgC&pg=PA10
"If you're running the database in noarchivelog mode, the recommended
approach to backing up the database is to shut down the database
cleanly first and then to back up all the datafiles. If you're using
RMAN to perform an offline backup, the database must be mounted before
you can actually perform the RMAN backup. This is because RMAN needs
to update the target control file."
To help the OP, what is the correct way to back up the database in
noarchivelog mode? The error reported indicates that an inconsistent
backup was captured. I wonder if the backup was captured after a
shutdown abort, or a failed shutdown immediate - see the timeout note
at the bottom of this page:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/start003.htm
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
hpuxrac
snip
> > If you are running in noarchivelog mode the only way to use rman is to
> > take a backup when the database is shutdown ( not open ).
>
> > Think about buying Robert Freeman's most recent ( 10g ) RMAN book.
>
> Hpuxrac,
>
> Some clarification. When you state shutdown (not open), do you mean
> completely shut down, or just that the database is mounted?
Answer:
shutdown immediate followed by a starup mount then the RMAN backup ...
I have
> that RMAN book by Robert Freeman. Page 27 indicates that for a
> database in NOACHIVELOG mode, the correct way to backup the database
> is to shut down the database, backup the files, and then restart the
> database. Page 245 states "RMAN will not prevent you from doing a
> tablespace or datafile backup in NOARCHIVELOG mode (as long as the
> database is not open). However, these backups are not really all that
> usable when the database is in NOARCHIVELOG mode (unless you back up
> all the tablespaces and datafiles at the same time)."
See the complete example on page 285 in Chapter 10 "Recover your
NOARCHIVELOG Mode database" steps your thru both the backup procedure
and recovery procedures.
You seem to be mixing in 2 separate things ( well at least separate
pages ) from the author as different that are really the same. In
NOARCHIVELOG mode using RMAN ( or not ) you really want to get all the
database at the same time ... not some tablespaces at one point and
others at a different point ... which you can do ( but often reaches a
point of diminishing return ) in ARCHIVELOG mode.
> Contrast that advice to the advice shown here in "RMAN Recipes for
> Oracle Database 11g" (another book that I own):http://books.google.com/books?id=qISYkSBV2hgC&pg=PA10
> "If you're running the database in noarchivelog mode, the recommended
> approach to backing up the database is to shut down the database
> cleanly first and then to back up all the datafiles. If you're using
> RMAN to perform an offline backup, the database must be mounted before
> you can actually perform the RMAN backup. This is because RMAN needs
> to update the target control file."
??? It is the same advice ... see the example from Freeman where you
start by recovering the controlfile in nomount ... then mount ... the
restore database ... then recover database noredo ... then alter
database open resetlogs ... ( then take a backup immediately ).
> To help the OP, what is the correct way to back up the database in
> noarchivelog mode? The error reported indicates that an inconsistent
> backup was captured. I wonder if the backup was captured after a
> shutdown abort, or a failed shutdown immediate - see the timeout note
> at the bottom of this page:http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/start0...
See above. For the OP ... consider implementing a procedure where you
at least get the database in archivelog mode briefly before during
after briefly after the RMAN backup it reduces some of the
complexity. Better yet keep it in archivelog mode gives you much more
flexibility with recovery scenarios.
Charles Hooper
> On Feb 9, 12:08 pm, Charles Hooper <hooperc2...@yahoo.com> wrote:
> > > If you are running in noarchivelog mode the only way to use rman is to
> > > take a backup when the database is shutdown ( not open ).
>
> > > Think about buying Robert Freeman's most recent ( 10g ) RMAN book.
>
> > Hpuxrac,
>
> > Some clarification. When you state shutdown (not open), do you mean
> > completely shut down, or just that the database is mounted?
>
> Answer:
> shutdown immediate followed by a starup mount then the RMAN backup ...
Thanks for clarifying that statement.
> > I have
> > that RMAN book by Robert Freeman. Page 27 indicates that for a
> > database in NOACHIVELOG mode, the correct way to backup the database
> > is to shut down the database, backup the files, and then restart the
> > database. Page 245 states "RMAN will not prevent you from doing a
> > tablespace or datafile backup in NOARCHIVELOG mode (as long as the
> > database is not open). However, these backups are not really all that
> > usable when the database is in NOARCHIVELOG mode (unless you back up
> > all the tablespaces and datafiles at the same time)."
>
> See the complete example on page 285 in Chapter 10 "Recover your
> NOARCHIVELOG Mode database" steps your thru both the backup procedure
> and recovery procedures.
That appears to be a very good walk through which was not listed in
the index at the back of the book under the topic of NOARCHIVELOG
mode, which is how I found the other references listed above. Page
286 shows the following command used to recover the noarchivelog mode
database after a restore:
RECOVER DATABASE NOREDO;
I wonder if NOREDO is the key for the OP? Page 281 states this about
the NOREDO parameter:
"Following this command, recover database noredo instructs RMAN to
perform final recovery operations in preparation for opening the
database. Since the database is in NOARCHIVELOG mode, and there are
no archived redo logs to apply and the online redo logs are missing,
the noredo parameter is required."
> You seem to be mixing in 2 separate things ( well at least separate
> pages ) from the author as different that are really the same. In
> NOARCHIVELOG mode using RMAN ( or not ) you really want to get all the
> database at the same time ... not some tablespaces at one point and
> others at a different point ... which you can do ( but often reaches a
> point of diminishing return ) in ARCHIVELOG mode.
Yes, I believe that is correct that I was mixing 2 separate things
regarding NOARCHIVELOG mode. The first method, I believe, is
describing an operating system level offline copy of the database
files. The second method is describing an RMAN backup with the
database mounted, but not opened. The portion of the paragraph
beginning with "However" was the start of another under-developed
thought in my reply, possibly serving as a potential source of the
problem for the OP.
> > Contrast that advice to the advice shown here in "RMAN Recipes for
> > Oracle Database 11g" (another book that I own):http://books.google.com/books?id=qISYkSBV2hgC&pg=PA10
> > "If you're running the database in noarchivelog mode, the recommended
> > approach to backing up the database is to shut down the database
> > cleanly first and then to back up all the datafiles. If you're using
> > RMAN to perform an offline backup, the database must be mounted before
> > you can actually perform the RMAN backup. This is because RMAN needs
> > to update the target control file."
>
> ??? It is the same advice ... see the example from Freeman where you
> start by recovering the controlfile in nomount ... then mount ... the
> restore database ... then recover database noredo ... then alter
> database open resetlogs ... ( then take a backup immediately ).
It is the same advice, captured in a single paragraph, rather than
being partially stated on several pages, as seems to happen with
several topics in the "Oracle Database 10g RMAN Backup & Recovery"
book.
> > To help the OP, what is the correct way to back up the database in
> > noarchivelog mode? The error reported indicates that an inconsistent
> > backup was captured. I wonder if the backup was captured after a
> > shutdown abort, or a failed shutdown immediate - see the timeout note
> > at the bottom of this page:http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/start0...
>
> See above. For the OP ... consider implementing a procedure where you
> at least get the database in archivelog mode briefly before during
> after briefly after the RMAN backup it reduces some of the
> complexity. Better yet keep it in archivelog mode gives you much more
> flexibility with recovery scenarios.
To the OP, a final note, which Sybrand among others have repeatedly
stated (and I don't think too many people would want to disagree
with), from "RMAN Recipes for Oracle Database 11g" Page 7:
"If you are running a production database - or if you want to make
sure all the data changes made to any database, for that matter, are
always protected - you must operate your database in archivelog mode."
Noons
Did you restore the backed up controlfiles before starting the restore?
If not, then the current controlfiles will not like a restored database from a
few days ago.
Noons
> that RMAN book by Robert Freeman. Page 27 indicates that for a
> database in NOACHIVELOG mode, the correct way to backup the database
> is to shut down the database, backup the files, and then restart the
> database. Page 245 states "RMAN will not prevent you from doing a
> tablespace or datafile backup in NOARCHIVELOG mode (as long as the
> database is not open). However, these backups are not really all that
> usable when the database is in NOARCHIVELOG mode (unless you back up
> all the tablespaces and datafiles at the same time)."
>
> Contrast that advice to the advice shown here in "RMAN Recipes for
> Oracle Database 11g" (another book that I own):
> http://books.google.com/books?id=qISYkSBV2hgC&pg=PA10
> "If you're running the database in noarchivelog mode, the recommended
> approach to backing up the database is to shut down the database
> cleanly first and then to back up all the datafiles. If you're using
> RMAN to perform an offline backup, the database must be mounted before
> you can actually perform the RMAN backup. This is because RMAN needs
> to update the target control file."
>
> To help the OP, what is the correct way to back up the database in
> noarchivelog mode? The error reported indicates that an inconsistent
> backup was captured. I wonder if the backup was captured after a
> shutdown abort, or a failed shutdown immediate - see the timeout note
> at the bottom of this page:
> http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/start003.htm
I've been using rman to backup noarchivelog dbs for a coupla yeras now, with the
db in mount mode. The restore is very simple: restore first the controlfile
taken during the backup and then restore the db.
Works everytime, tried many, many times.
10.2.0.3 here.
Charles Hooper
> I've been using rman to backup noarchivelog dbs for a coupla yeras now, with the
> db in mount mode. The restore is very simple: restore first the controlfile
> taken during the backup and then restore the db.
> Works everytime, tried many, many times.
> 10.2.0.3 here.
Thanks, I appreciate the feedback.
markg...@yahoo.com
>
> - Show quoted text -
Thank you.
Restoring controlfile worked.
Thanks again.
joel garry
> To the OP, a final note, which Sybrand among others have repeatedly
> stated (and I don't think too many people would want to disagree
> with), from "RMAN Recipes for Oracle Database 11g" Page 7:
> "If you are running a production database - or if you want to make
> sure all the data changes made to any database, for that matter, are
> always protected - you must operate your database in archivelog mode."
>
The second part, yes. The "If you are running a production
database..." as stated with no context, no.
What is most important is to be explicit about what service level is
expected. There are plenty of DW and DSS examples that are not
transaction dependent, as well as more obscure distributive setups.
For example, I've spit stuff out to various XE databases where all
sorts of weird things can happen, no need to backup those at all. I
find it common for users of ERP systems to be able to want to test or
learn things, they only need restoration and perhaps recovery, logical
backups if any. Is that production? Depends on their expectations.
For development databases described as production, it is just as
important to be clear about SLA, and much more likely to go down in
flames as a matter of course.
Just because the banner says "Production" doesn't mean the db needs
archivelog mode. On the other hand, we still see silly people placing
their eggs in a SAN basket. It all depends.
jg
--
@home.com is bogus. "It may be strange for those not in the
networking field, but when things really go bad, the only place to be
is physically in the data center."
"Heh. I've heard that in the old day you could find broken Token ring
hardware by listening after a high pitched whining noise. Guess one
really has to be there for stuff like that."
"Was there, and confirm true. Whining noise normally came from IBM SE
who was trying to fix problem."
Slashdot slashdotted: http://news.slashdot.org/article.pl?no_d2=1&sid=09/02/10/044221
Charles Hooper
Just to round out this topic, a little more information:
(Search for NOREDO in the following links):
http://download.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmrecov.htm
http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmadvre.htm
Pages 373-374 of the book "RMAN Recipes for Oracle Database 11g"
offers two other approaches, one using a backed up control file, and
the other using current control file:
Using the backed up control file:
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
RMAN> alter database open resetlogs;
Using the current control file:
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database;
RMAN> alter database open resetlogs;
(the above is expected to return ORA-01139, switch to SQL*Plus)
SQL> recover database until cancel;
SQL> cancel
SQL> alter database open resetlogs;
hpuxrac
snip
> Pages 373-374 of the book "RMAN Recipes for Oracle Database 11g"
> offers two other approaches, one using a backed up control file, and
> the other using current control file:
snip ... not familiar with that book
> Using the current control file:
> RMAN> connect target /
> RMAN> startup mount;
> RMAN> restore database;
> RMAN> alter database open resetlogs;
> (the above is expected to return ORA-01139, switch to SQL*Plus)
> SQL> recover database until cancel;
> SQL> cancel
> SQL> alter database open resetlogs;
Starting to lose track probably of the point you are trying to make
Charles.
If you are still talking about trying to recover a database that was
in noarchivelog that had an rman backup of it taken while it was in
MOUNT ... ( I think you are ) ... then I can think of no good reason
to not restore the controlfile.
It may work in certain cases using the current one but other ones can
be constructed where it won't. The idea if intentionally causing an
ORA error via RMAN then bypassing by sqlplus ... more than a little
ugly.
Better idea but still probably not a good one in this specific case is
to use RMAN for the restore but do all the recover thru sqlplus in the
first place.
I think a better recommendation is to just go ahead and restore the
controlfile.
Moving outside of that specific focus ( if we were there in the first
place ... personally I always follow any RMAN backup with my own
backup of the controlfile done in 2 ways. I use the rman copy
controlfile command ( in it's own session not associated with any
backup commands ) afterwards and also use sqlplus to get a copy of the
spfile and backup controlfile to trace. It may be overkill and it is
old school but personally I don't want to depend on any autobackup
provided "probably can get this stuff back" dependencies.