DBPITR and ORA-16005 Error: database requires recovery.
gba...@ravenpack.com
I am trying to test database point in time recovery scenarios in
which I want to Open read-only the database before using RESETLOGS
clause. The test was ran on a Oracle 10gR2 single instance.
1) Perform backup using RMAN default channel, no recovery catalog and
having control file autobackup enabled.
RUN {
BACKUP DATABASE;
BACKUP ARCHIVELOG ALL NOT BACKED UP DELETE ALL INPUT;
}
2) Get the current SCN from V$DATABASE. Call it SCN1.
3) DROP a table. Shutdown Immediate and mount the database.
4) Perform a DBPITR up to SCN1:
RUN {
SET UNTIL SCN [SCN1];
RESTORE DATABASE;
RECOVER DATABASE;
}
5) When trying to open the database in read only mode
RMAN> SQL 'ALTER DATABASE OPEN READ ONLY'
The command fails with ORA-16005: database requires recovery.
I can only open the database read-write using RESETLOGS clause, but
this leave me without the chance of verifying the data before creating
a new database incarnation.
Does anyone have any clue about why is this happening?
Thanks for your help.
G. Bahut.
ora...@msn.com
wrote:
You're probably using CHECKPOINT_CHANGE# for your SCN; try using
RESETLOGS_CHANGE# instead, since that is the SCN Oracle will find
after an ALTER DATABASE OPEN RESETLOGS; has been executed.
David Fitzjarrell
gba...@ravenpack.com
I don't think I understand. I was using the value of CURRENT_SCN from
V$DATABASE to identify the target SCN in the DBPITR process.
If I query my database :
SQL> SELECT CURRENT_SCN, CHECKPOINT_CHANGE# , RESETLOGS_CHANGE# FROM V
$DATABASE;
CURRENT_SCN CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
----------- ------------------ -----------------
8279191460 8279155540 8279155539
So CHECKPOINT_CHANGE# and RESETLOGS_CHANGE# are almost the same and
lower than CURRENT_SCN.
If I force a checkpoint global and query again:
SQL> alter system checkpoint global;
System altered.
SQL> SELECT CURRENT_SCN, CHECKPOINT_CHANGE# , RESETLOGS_CHANGE# FROM V
$DATABASE;
CURRENT_SCN CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
----------- ------------------ -----------------
8279192016 8279192003 8279155539
Now, CHECKPOINT_CHANGE# and CURRENT_SCN are almost the same while
RESETLOGS_CHANGE# remains constant to the SCN were the last RESETLOGS
took place.
I am afraid that using RESETLOGS_CHANGE# in a DBPITR where I want to
take the database up to a recent SCN will not be very helpful. Maybe I
miss something, could you please explain what you mean?
Thanks,
Gonzalo.
ora...@msn.com
wrote:
I will admit I am flying somewhat 'blind' with this,. as I have no
access to a database with which I can effect a PITR. Thus, I admit to
being wrong in my original suggestion.
I'll need to look further into this situation before I attempt to
provide any further comment.
David Fitzjarrell
vital...@gmail.com
wrote:
Where have you read you can open the database in READONLY mode without
any resetlogs in this situation?
gba...@ravenpack.com
> Where have you read you can open the database in READONLY mode without
> any resetlogs in this situation?
Oracle® Database Backup and Recovery Basics:
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/toc.htm
Sections 7.6.4 and 7.6.5.
7.6.4 Database Point-in-Time Recovery Within the Current Incarnation.
"If the operation completes without errors, then your DBPITR has
succeeded. You can open the database read-only and perform queries as
needed to ensure that the effects of the logical corruption have been
reversed. If not, you may have chosen the wrong target SCN."
7.6.5 Options After Database Point-in-Time Recovery.
"After a successful DBPITR, your choices are:
* Export one or more objects from your database using an Oracle
export utility such as Data Pump Export. You can then recover the
database to the current point in time and re-import the exported
objects, as a way to return these objects to their state prior to the
unwanted change without abandoning all other changes.
* Open your database for read-write, abandoning all changes
after the target SCN. In such a case, you must open the database with
the RESETLOGS option, as shown here:
RMAN> ALTER DATABASE OPEN RESETLOGS; "
vital...@gmail.com
wrote:
Thanks. That was a new feature of RMAN 10g apparently.
I haven't tested it so I have not any clue about your problem.
tim2...@gmail.com
wrote:
I am not sure where your system stands at current. It would seem to
me that you followed the instructions perfectly as described in the
Database Backup and Recovery Basics.
A few things to check.
1) Before you can do ALTER DATABASE OPEN READ ONLY, your database
needs to be in a mounted state.
2) Have you tried to do ALTER DATABASE OPEN READ ONLY from a sql
prompt and not from within RMAN (I know it sounds crazy but I have
seen stranger things work.)
Lastly I don't think the documentation is correct. I really believe
that you have to tell it to restore using a backup controlfile (even
if it is the "current" controlfile) RECOVER DATABASE USING BACKUP
CONTROLFILE. If you are using "RECOVER DATABASE" then RMAN is
expecting the end of recovery marker. Well if you are doing a point
in time recovery then you are doing and "incomplete" recovery and
therefore it can not reach the recovery marker. I may be wrong but
that is my view point.
Regards
Tim
joel garry
Like David, I'm also flying blind here, but I'm not so sure the docs
are wrong, as they do specifically state using the current
controlfile. I'm guessing the OP's problem comes from using the
current SCN, he needs to try an older one. There must be some
conceptual fuzziness required, henc the docs say "...you may have
chosen the wrong target SCN. In such a case, investigate the unwanted
change further and determine a new target SCN, then repeat the DBPITR
process." Makes me wonder if there are various system-related SCN's
that get posted, which are kind of iffy in the sense of being a
complete transaction in the sense we are used to. I know how weird
that sounds and don't even want to speculate on what I mean.
Maybe one of the authors of books on RMAN might be the one to ask.
jg
--
@home.com is bogus.
http://timesofindia.indiatimes.com/China_mounts_cyber_attacks_on_Indian_sites/articleshow/3010288.cms
tim2...@gmail.com
>
> - Show quoted text -
Actually I did some research later on METALINK and it verifies what I
said.
Note:399276.1
"Solution
It is possible to open a database READ ONLY in the middle of recovery
to check the data content
before deciding to proceed with recovery but with certain
restrictions:
1. Minimal recovery must be done before the database can be opened
READ ONLY ie you must at least
apply the log that was current at the time the backup ended and the
database must be consistent.
2. Recovery must be done using the 'backup controlfile' option, even
if the controlfile in use is
actually the CURRENT controlfile otherwise the database will fail to
open with :"
joel garry
>
> > - Show quoted text -
>
> Actually I did some research later on METALINK and it verifies what I
> said.
>
> Note:399276.1
>
> "Solution
> It is possible to open a database READ ONLY in the middle of recovery
> to check the data content
> before deciding to proceed with recovery but with certain
> restrictions:
>
> 1. Minimal recovery must be done before the database can be opened
> READ ONLY ie you must at least
> apply the log that was current at the time the backup ended and the
> database must be consistent.
> 2. Recovery must be done using the 'backup controlfile' option, even
> if the controlfile in use is
> actually the CURRENT controlfile otherwise the database will fail to
Thanks for that, it makes sense now. controlfile_change# of v
$database = checkpoint_change# of v$datafile_header (close gets no
cigar, I think)
Looks like the O11 DBPITR makes it somewhat easier.
jg
--
@home.com is bogus.
gba...@ravenpack.com
I have continued my tests using your info, specially the Metalink
documents.
When I tried the statement:
RMAN> recover database using backup controlfile until cancel;
......
RMAN-01009: syntax error: found "using": expecting one of:
"archivelog, auxiliary, allow, check, delete, from, high, noredo,
noparallel, parallel, ;, skip, tablespace, test, until, undo"
I went to the backup and recovery reference guide and check the syntax
for RECOVER command on 10gR2 library:
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta044.htm
I was surprised that "RECOVER DATABASE USING" is NOT a valid construct
although the metalink document states the document apply to DB
versions 9.0.1.4 to 10.2.0.3.
Reading down I get to RESTORE EXAMPLES, specifically the one titled:
Performing DBPITR with a Backup Control File and Recovery Catalog:
Example
The example restores the control file in NOMOUNT state before mounting
the database and start restore and recovery.
I changed the RUN block to specify the SCN number instead of the log
sequence in the SET UNTIL clause. Also removed Tablespace Skip
clauses:
RMAN> run {
set until SCN [scn1];
restore controlfile;
alter database mount;
restore database;
recover database;
}
When executed I got this error:
RMAN-03002: failure of set command at 05/06/2008 10:21:22
ORA-01507: database not mounted
Probably this is because the example assumes the target DB is using a
recovery catalog? ....
vital...@gmail.com
wrote:
> Hi, many thanks for your responses.
>
> I have continued my tests using your info, specially the Metalink
> documents.
>
> When I tried the statement:
>
> RMAN> recover database using backup controlfile until cancel;
> ......
> RMAN-01009: syntax error: found "using": expecting one of:
> "archivelog, auxiliary, allow, check, delete, from, high, noredo,
> noparallel, parallel, ;, skip, tablespace, test, until, undo"
That is an SQL*Plus command, not RMAN.
> RMAN> run {
> set until SCN [scn1];
> restore controlfile;
> alter database mount;
> restore database;
> recover database;
> }
>
> When executed I got this error:
> RMAN-03002: failure of set command at 05/06/2008 10:21:22
> ORA-01507: database not mounted
>
> Probably this is because the example assumes the target DB is using a
> recovery catalog? ....
Yes. Add "... from autobackup;" at the end of "restore controlfile".
If you still have problems getting RMAN read the right autobackup, try
"... from /path/to/the/proper/autobackup;"
gba...@ravenpack.com
I will write down how I did:
1) Backup the database including the control file.
2) Get the CURRENT_SCN from V$Database; [scn1]
3) Drop table T1 and added some rows to table T2.
4) Perform DBPITR up to SCN1:
>RMAN / target
RMAN> shutdown immediate;
RMAN> startup nomount;
RMAN> SET DBID [My database Identifier];
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN> ALTER DATABASE MOUNT;
RMAN> RUN {
SET UNTIL SCN [scn1];
RESTORE DATABASE;
RECOVER DATABASE;
}
RMAN> SQL 'ALTER DATABASE OPEN READ ONLY';
The database successfully opens in read only mode and I verify that
the operations after SCN1 are reversed, these are, T1 dropped table
and the inserts on T2.
I can copy T1 to another DB using a DB link and perform complete
recovery of the database:
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> RECOVER DATABASE;
RMAN> SQL 'ALTER DATABASE OPEN RESETLOGS';
With the database open I can copy T1 backup back to my target database
not losing the INSERTS I made on T2.
So, It looks like the control file must be restored in the first
place, as many of you suggested, so that RMAN can perform a consistent
incomplete recovery and let the DB be opened in READ ONLY mode.
Thanks again for your comments.
Gonzalo.