2

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

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

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

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

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

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

`------- ---- -- ---------- ----------- ------------ ---------------

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: Got the db to open properly by running recover database and then opening with resetlogs. but i'm fairly sure this undid the backup. does anyone know what is wrong? – happiness_runs Jan 15 '15 at 19:03
  • When you say "errors that i'm sure are coming from the recent update that was applied", do you mean that you made changes to the database after taking the backup and those changes are gone after the restore? If so, that's exactly what should happen. – mustaccio Jan 15 '15 at 19:42
  • no im saying that those errors are still in the database after the restore. i have an application that worked before an upgrade. after the upgrade changes were made to the DB. which are causing errors. even though im restoring the database im still getting these errors which leads me to believe that im not actually restoring the database. – happiness_runs Jan 15 '15 at 19:47
  • Your belief seems wrong. If restore database completes successfully, you have a copy of the database as of the time of backup. You need to find an objective way of verifying the database contents. – mustaccio Jan 15 '15 at 20:04
  • If you backup the control file after your cold backup, and if the newer backup of the control file is used, then the control file is too new for the backup. Hence you would need to do recovery. For example if you have auto backup of the control file setup, it might backup the control file after your cold backup finishes. Make sure that you restore the correct backup of the control file, then you should not need to recover the database. – Gandolf989 Jan 15 '15 at 20:52
2

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:

  1. You tried to restore the old backup without deleting original files / moving original files somewhere else. (I prefer option 2)
  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

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

Restore the Cold Backup

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

-1

Commands to restore database without needing to apply redo. If you don;t want the database to roll forward make sure that there are no files in the archive log directory. You can tar and delete them or just move them elsewhere.

shutdown abort;
startup nomount;
restore controlfile from '/u02/backups/dbname/dbname_1cpqqnpv_1_1_20141222_1';
alter database mount;
restore database;
alter database open resetlogs;
  • So there is a down vote but no explanation about what I got wrong. Not very helpful. – Gandolf989 Jan 16 '15 at 17:19
  • tried this method and recieved the following errors: RMAN-03002: failure of alter db command at 01/20/2015 09:16:40 ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '+DATA/dbname/datafile/system.333.741265951' – happiness_runs Jan 20 '15 at 14:19
  • Did you restore the '/u02/backups/dbname/dbname_1cpqqnpv_1_1_20141222_1' controlfile? It would be easier if you showed every command that you used and all of the output. What I put above should have worked. Somehow you aren't getting Oracle to do the things that you need it to do to restore the backup without needing redo. – Gandolf989 Jan 20 '15 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.