Restoring an Oracle Backup Properly

backuporacleoracle-10grestorerman

I've been trying to do a proper restore of a database for weeks now and I'm convinced that I'm doing something simple wrong. I took a cold backup of the database before an update to an application using this command:

backup database include current controlfile;

the database has been running in noarchivelog mode if that matters. When I run the following command:

list backup recoverable

I see the backup I took on that day. Then I run the following commands

shutdown abort
startup nomount
restore database

Now I have to open the database. If I run sql 'alter database open' in rman I get the following error

ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '+DATA/db-name/datafile/system.333.741265951'

Earlier I restored the controlfile from the backup and it is no longer listed in output of the list backup recoverable command.

If I run the following command sql 'alter database open resetlogs' the command will work and the database will open, but my application will throw errors that I'm sure are coming from the recent update that was applied.

I think using the resetlogs flag is making it so I'm no longer using a backup but I'm not sure what to do.

I'm new to this DBA stuff especially with oracle. Sorry for the length of this post but I want to get all the details out.

EDIT: currently neither commands alter database open resetlogs or alter database open noresetlogs work in opening the DB.

EDIT: displaying backup output:

RMAN> list backup;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

1 Full 121.27M DISK 00:00:19 29-SEP-11
BP Key: 1 Status: AVAILABLE Compressed: YES Tag: TAG20110929T160134
Piece Name: /u02/backups/dbname/dbname_18mnp8av_1_1_20110929_1
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

2 Full 29134673 29-SEP-11 +DATA/dbname/datafile/sysaux.332.741265959
5 Full 29134673 29-SEP-11 +DATA/dbname/datafile/pluto5.327.741268543
BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

2 Full 186.50M DISK 00:00:25 29-SEP-11
BP Key: 2 Status: AVAILABLE Compressed: YES Tag: TAG20110929T160134
Piece Name: /u02/backups/dbname/dbname_17mnp8av_1_1_20110929_1
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

1 Full 29134673 29-SEP-11 +DATA/dbname/datafile/system.333.741265951
3 Full 29134673 29-SEP-11 +DATA/dbname/datafile/undotbs1.331.741265965
4 Full 29134673 29-SEP-11 +DATA/dbname/datafile/users.329.741265973
BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

3 Full 1.05M DISK 00:00:03 29-SEP-11
BP Key: 3 Status: AVAILABLE Compressed: YES Tag: TAG20110929T160134
Piece Name: /u02/backups/dbname/dbname_19mnp8bp_1_1_20110929_1
Control File Included: Ckp SCN: 29134673 Ckp time: 29-SEP-11
BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

4 Full 9.36M DISK 00:00:01 29-SEP-11
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20110929T160205
Piece Name: /u02/backups/dbname/ctldbname_c-3237489303-20110929-00
SPFILE Included: Modification time: 29-SEP-11
SPFILE db_unique_name: dbname
Control File Included: Ckp SCN: 29134673 Ckp time: 29-SEP-11
BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

5 Full 1.05M DISK 00:00:02 22-DEC-14
BP Key: 5 Status: AVAILABLE Compressed: YES Tag: TAG20141222T172206
Piece Name: /u02/backups/dbname/dbname_1cpqqnpv_1_1_20141222_1
Control File Included: Ckp SCN: 704426960 Ckp time: 22-DEC-14
BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

6 Full 303.91M DISK 00:00:52 22-DEC-14
BP Key: 6 Status: AVAILABLE Compressed: YES Tag: TAG20141222T173133
Piece Name: /u02/backups/dbname/dbname_1fpqqobn_1_1_20141222_1

List of Datafiles in backup set 6

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

1 Full 704427620 22-DEC-14 +DATA/dbname/datafile/system.333.741265951
3 Full 704427620 22-DEC-14 +DATA/dbname/datafile/undotbs1.331.741265965

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

7 Full 1.05M DISK 00:00:01 22-DEC-14
BP Key: 7 Status: AVAILABLE Compressed: YES Tag: TAG20141222T173133
Piece Name: /u02/backups/dbname/dbname_1gpqqode_1_1_20141222_1
Control File Included: Ckp SCN: 704427620 Ckp time: 22-DEC-14

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

8 Full 363.18M DISK 00:01:14 22-DEC-14
BP Key: 8 Status: AVAILABLE Compressed: YES Tag: TAG20141222T173133
Piece Name: /u02/backups/dbname/dbname_1epqqobm_1_1_20141222_1
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

2 Full 704427620 22-DEC-14 +DATA/dbname/datafile/sysaux.332.741265959
4 Full 704427620 22-DEC-14 +DATA/dbname/datafile/users.329.741265973
5 Full 704427620 22-DEC-14 +DATA/dbname/datafile/pluto5.327.741268543

EDIT: more backup output:

RMAN> list backup recoverable;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

---- -- ---- ---------- --------- ----

2 Full 29134673 29-SEP-11 +DATA/dbname/datafile/sysaux.332.741265959
5 Full 29134673 29-SEP-11 +DATA/dbname/datafile/pluto5.327.741268543

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

---- -- ---- ---------- --------- ----

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

6 Full 303.91M DISK 00:00:52 22-DEC-14
BP Key: 6 Status: AVAILABLE Compressed: YES Tag: TAG20141222T173133
Piece Name: /u02/backups/dbname/dbname_1fpqqobn_1_1_20141222_1
List of Datafiles in backup set 6
File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

1 Full 704427620 22-DEC-14 +DATA/dbname/datafile/system.333.741265951
3 Full 704427620 22-DEC-14 +DATA/dbname/datafile/undotbs1.331.741265965

BS Key Type LV Size Device Type Elapsed Time Completion Time

`——- —- — ———- ———– ———— —————

---- -- ---- ---------- --------- ----

Best Answer

a)

The command alter database open showed:

ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '+DATA/db-name/datafile/system.333.741265951'

This can have several reasons but in your case it means that the database found redologs which do not fit the controlfile/datafiles. This probably happened like this:

You tried to restore the old backup without deleting original files / moving original files somewhere else. (I prefer option 2)
You come to the point where you should open the database with resetlogs option but you don't do it. You try to open the database normal and the database finds the redologs of the previous database which have the correct DBID but a different incarnation. Luckily the database realizes the fault and does not continue otherwise you would crash your fresh restored database.

b)

You issued alter database open resetlogs:

After this command finished your database was open but your application still had problems. Well, first let's make this clear: At this point your database had the exact same data as before. Each block, each byte, each bit is now in the same place as before when you issued the backup. So the errors are not related to the database.

c)

This is the procedure you have to follow. (I would not recommend offline backups but this is a different story.)

RMAN cold backup just works the way you did it. Here is my guide I wrote some time ago:

Cold Backup

shutdown the database
startup in mount mode (nomount does not work)
backup the database with backup database; and backup current controlfile;

Restore the Cold Backup

get a SPFILE
startup the database in nomount mode
restore the controlfile with restore controlfile from '<controlfile backup>';
make sure the controlfile knows all available backups. If not run a catalog start with '<path>'; to search for backups.
restore the database with restore database;
In case of an online backup you would run a recovery now. But not with a cold backup.
Open the database with resetlogs.

Related Solutions

Failure Of Backup Due To Limit Exceeded For Recovery Files Since Cannot Reclaim Disk Space [RMAN-03009,ORA-19809,ORA-19804]

The db_recovery_file_dest includes a variety of files, including online AND archived redo log files; take a look at the Oracle Backup and Recovery User's Guide chapter 5 to find out the best solution; disabling db_recovery_file_dest is not a good idea. You can try

RMAN> report obsolete;

RMAN command to see what can be deleted. Then do a delete obsolete to delete them. Do you have any guaranteed restore points defined?

RMAN> list restore point all;

If you see any results with type GUARANTEED, if they are no longer needed, remove them:

SQL> DROP RESTORE POINT before_app_upgrade;

Is flashback database enabled?

RMAN – no datafile found during recovery

Asking the database to do the impossible.

You changed the TAG, so you created a new initial set of image copies.

As diskspace was becoming increasingly tight, I changed the backup script to use a different TAG to try and force a new set of backup images to be create on the new disk. This seemed to work - the new set of backup images were created okay on the new disk that night.

Lets say this happened on 2016-12-13 (just assuming, based on this):

This has now been failing for the last 6 nights

Next day, the backup script on 2016-12-14 reached this point:

RECOVER COPY OF DATABASE WITH TAG 'r2cig_incr' UNTIL TIME 'SYSDATE-7';

So it wanted to recover the image copies created on 2016-12-13 to an earlier state: SYSDATE-7 = 2016-12-07. That does not work like that. You can not rewind image copies to earlier states. When you try to do something like this, RMAN will look for an earlier image copy, which it can recover (forward, not backward), but there is no earlier image copy with the specified TAG, hence: no copy of datafile 1 found to recover.

So this happened all day until today. Today, this script ran again:

RECOVER COPY OF DATABASE WITH TAG 'r2cig_incr' UNTIL TIME 'SYSDATE-7';

Which means, recover the image copy created on 2016-12-13 to a state: SYSDATE-7 = 2016-12-12. Still won't work.

Tomorrow SYSDATE-7 will mean 2016-12-13, the day the new initial image copies were created. I suspect this will also fail. Lets say, your script starts at the same time every day, 00:00. It started at 2016-12-13 00:00, finished creating the image copies at 2016-12-13 01:00. The next time the script starts (2016-12-20 00:00), SYSDATE-7 will still mean an earlier time: 2016-12-13 00:00.

But the day after tomorrow, when SYSDATE - 7 = 2016-12-14, it will start recovering the image copies.

If you run the above script every day, it will not recover anything on the first 7 days because of SYSDATE-7. It will start recovering on the 8th day.

Or, you could try this right now by running the script with the PREVIEW option. This will not do any recovery, just a preview. This, I suspect, will still print the same errors as in the last 6 days:

RECOVER COPY OF DATABASE WITH TAG 'r2cig_incr' UNTIL TIME 'SYSDATE-7' preview;

But this should list the image copies that would be recovered, the starting and ending SCN, and the logfile used for recovery:

RECOVER COPY OF DATABASE WITH TAG 'r2cig_incr' UNTIL TIME 'SYSDATE-5' preview;

Best Answer

a)

b)

c)

Cold Backup

Restore the Cold Backup

Related Solutions

Failure Of Backup Due To Limit Exceeded For Recovery Files Since Cannot Reclaim Disk Space [RMAN-03009,ORA-19809,ORA-19804]

RMAN – no datafile found during recovery

Related Question