OraFAQ Forum: Backup & Recovery » Full Restore after reset logs

Home » RDBMS Server » Backup & Recovery » Full Restore after reset logs (Windows 2003, 11.1.0.7..)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Full Restore after reset logs [message #449583]

Tue, 30 March 2010 12:16

db33
Messages: 21
Registered: July 2009

Junior Member

Hi,
I want the ability to use rman to do a full backup from my noarchivelog database:

run {
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
allocate channel d1 type disk;
allocate channel d2 type disk;
backup AS COMPRESSED BACKUPSET full
tag full_offline_backup
format 'e:\backups3\db_t%t_s%s_p%p'
(database);
release channel d1;
release channel d2;
alter database open;
}

then restore it at a later date:

run {
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
allocate channel d1 type disk format 'e:\backups2\db_t%t_s%s_p%p';
allocate channel d2 type disk format 'e:\backups2\db_t%t_s%s_p%p';
RESTORE database;
release channel d1;
release channel d2;
alter database open;
}

However, I always seem to have problems on the restore. Usually I get an errors like "ORA-01190: control file or data file 1 is from before the last RESETLOGS".

Is there anyway just to reset to the control file? If not, can I pull the old control file from the full backup? I can't see to find those commands anywhere.

Thanks.

Report message to a moderator

Re: Full Restore after reset logs [message #449585 is a reply to message #449583]

Tue, 30 March 2010 12:38

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Just use "recover database;" after your restore.

Regards
Michel

Report message to a moderator

Re: Full Restore after reset logs [message #449587 is a reply to message #449585]

Tue, 30 March 2010 13:18

db33
Messages: 21
Registered: July 2009

Junior Member

When I try that, I get:

RMAN-03002: failure of alter db command at 03/30/2010 14:01:20
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Which might be fine, I can just add the reset logs to the database open statement. But, that causes older backups not to be able to be imported:

ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: 'E:\APP\LG\ORADATA\TS\SYSTEM01.DBF'

Any suggestions? Thanks.

Report message to a moderator

Re: Full Restore after reset logs [message #449588 is a reply to message #449587]

Tue, 30 March 2010 13:30

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Post the whole session, with just an abstract it is hard to know what you've done and to do now.

Regards
Michel

Report message to a moderator

Re: Full Restore after reset logs [message #449590 is a reply to message #449587]

Tue, 30 March 2010 13:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Any suggestions? Thanks.
The restore is not complete; by definition based upon errors.

The source DB opens without error & intervention afterwards.

The target DB should behave like the source DB; assuming all the necessary files exist for the restored DB.

Report message to a moderator

Re: Full Restore after reset logs [message #449593 is a reply to message #449588]

Tue, 30 March 2010 13:41

db33
Messages: 21
Registered: July 2009

Junior Member

Here is the output:

RMAN> run {
2> SHUTDOWN IMMEDIATE;
3> STARTUP MOUNT;
4> allocate channel d1 type disk format 'e:\backups\db_t%t_s%s_p%p';
5> allocate channel d2 type disk format 'e:\backups\db_t%t_s%s_p%p';
6> RESTORE database;
7> release channel d1;
8> release channel d2;
9> recover database;
10> alter database open RESETLOGS;
11> }
12>
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    2087780352 bytes

Fixed Size                     2116536 bytes
Variable Size                469763144 bytes
Database Buffers            1593835520 bytes
Redo Buffers                  22065152 bytes

allocated channel: d1
channel d1: SID=2189 device type=DISK

allocated channel: d2
channel d2: SID=2188 device type=DISK

Starting restore at 30-MAR-10

channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 00002 to E:\APP\LG\ORADATA\TS\SYSAUX01.DBF
channel d1: restoring datafile 00005 to E:\APP\LG\ORADATA\TS\DATA01.DBF
channel d1: restoring datafile 00006 to E:\APP\LG\ORADATA\TS\INDEXES01.DBF
channel d1: reading from backup piece E:\BACKUPS4\DB_T715010368_S6_P1
channel d2: starting datafile backup set restore
channel d2: specifying datafile(s) to restore from backup set
channel d2: restoring datafile 00001 to E:\APP\LG\ORADATA\TS\SYSTEM01.DBF
channel d2: restoring datafile 00003 to E:\APP\LG\ORADATA\TS\UNDOTBS01.DBF
channel d2: restoring datafile 00004 to E:\APP\LG\ORADATA\TS\USERS01.DBF
channel d2: reading from backup piece E:\BACKUPS4\DB_T715010364_S5_P1
channel d1: piece handle=E:\BACKUPS4\DB_T715010368_S6_P1 tag=FULL_OFFLINE_BACKUP
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:00:25
channel d2: piece handle=E:\BACKUPS4\DB_T715010364_S5_P1 tag=FULL_OFFLINE_BACKUP
channel d2: restored backup piece 1
channel d2: restore complete, elapsed time: 00:00:35
Finished restore at 30-MAR-10

released channel: d1

released channel: d2

Starting recover at 30-MAR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2189 device type=DISK

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file C:\REDO\ORADATA\TS\REDO01.LOG
RMAN-08187: WARNING: media recovery until SCN 918643 complete
Finished recover at 30-MAR-10

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/30/2010 14:07:29
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: 'E:\APP\LG\ORADATA\TS\SYSTEM01.DBF'

Recovery Manager complete.

I do notice with this restore that I am specifying 'e:\backups', but it is pulling it from 'e:\backups4'. Don't know why.

Report message to a moderator

Re: Full Restore after reset logs [message #449594 is a reply to message #449590]

Tue, 30 March 2010 13:44

db33
Messages: 21
Registered: July 2009

Junior Member

BlackSwan wrote on Tue, 30 March 2010 13:40

>Any suggestions? Thanks.
The restore is not complete; by definition based upon errors.

The source DB opens without error & intervention afterwards.

The target DB should behave like the source DB; assuming all the necessary files exist for the restored DB.

Nope. The destination DB doesn't open without errors. Either I have to reset logs, making the older backups not work, or don't reset and I can't use my current backup.

Report message to a moderator

Re: Full Restore after reset logs [message #449599 is a reply to message #449593]

Tue, 30 March 2010 14:31

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Are you sure you made a backup after a clean shutdown?

Please post the whole session starting from the backup until the open after recover.

Regards
Michel

Report message to a moderator

Re: Full Restore after reset logs [message #449601 is a reply to message #449599]

Tue, 30 March 2010 15:00

db33
Messages: 21
Registered: July 2009

Junior Member

Thanks for your help. Here is the output... but just to give you a summary:
1.) create database
2.) back up to e:\backups5
3.) back up to e:\backups6
4.) restored from e:\backups6
5.) Failed trying to restore to previous e:\backups5

C:\Documents and Settings\LG\Desktop>
C:\Documents and Settings\LG\Desktop>rman TARGET / @mybackup5.rman

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Mar 30 15:38:26 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TS (DBID=1044150117)

RMAN> run {
2> SHUTDOWN IMMEDIATE;
3> STARTUP MOUNT;
4> allocate channel d1 type disk;
5> allocate channel d2 type disk;
6> backup AS COMPRESSED BACKUPSET full
7> tag full_offline_backup
8> format 'e:\backups5\db_t%t_s%s_p%p'
9> (database);
10> release channel d1;
11> release channel d2;
12> alter database open;
13> }
14>
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    2087780352 bytes

Fixed Size                     2116536 bytes
Variable Size                469763144 bytes
Database Buffers            1593835520 bytes
Redo Buffers                  22065152 bytes

allocated channel: d1
channel d1: SID=2189 device type=DISK

allocated channel: d2
channel d2: SID=2188 device type=DISK

Starting backup at 30-MAR-10
channel d1: starting compressed full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00001 name=E:\APP\LG\ORADATA\TS\SYSTEM01.DBF
input datafile file number=00004 name=E:\APP\LG\ORADATA\TS\USERS01.DBF
input datafile file number=00003 name=E:\APP\LG\ORADATA\TS\UNDOTBS01.DBF
channel d1: starting piece 1 at 30-MAR-10
channel d2: starting compressed full datafile backup set
channel d2: specifying datafile(s) in backup set
input datafile file number=00002 name=E:\APP\LG\ORADATA\TS\SYSAUX01.DBF
input datafile file number=00005 name=E:\APP\LG\ORADATA\TS\DATA01.DBF
input datafile file number=00006 name=E:\APP\LG\ORADATA\TS\INDEXES01.DBF
channel d2: starting piece 1 at 30-MAR-10
channel d2: finished piece 1 at 30-MAR-10
piece handle=E:\BACKUPS5\DB_T715016332_S2_P1 tag=FULL_OFFLINE_BACKUP comment=NONE
channel d2: backup set complete, elapsed time: 00:00:15
channel d2: starting compressed full datafile backup set
channel d2: specifying datafile(s) in backup set
including current control file in backup set
channel d2: starting piece 1 at 30-MAR-10
channel d2: finished piece 1 at 30-MAR-10
piece handle=E:\BACKUPS5\DB_T715016351_S3_P1 tag=FULL_OFFLINE_BACKUP comment=NONE
channel d2: backup set complete, elapsed time: 00:00:01
channel d2: starting compressed full datafile backup set
channel d2: specifying datafile(s) in backup set
including current SPFILE in backup set
channel d2: starting piece 1 at 30-MAR-10
channel d2: finished piece 1 at 30-MAR-10
piece handle=E:\BACKUPS5\DB_T715016357_S4_P1 tag=FULL_OFFLINE_BACKUP comment=NONE
channel d2: backup set complete, elapsed time: 00:00:01
channel d1: finished piece 1 at 30-MAR-10
piece handle=E:\BACKUPS5\DB_T715016328_S1_P1 tag=FULL_OFFLINE_BACKUP comment=NONE
channel d1: backup set complete, elapsed time: 00:00:33
Finished backup at 30-MAR-10

released channel: d1

released channel: d2

database opened

Recovery Manager complete.

C:\Documents and Settings\LG\Desktop>rman TARGET / @mybackup6.rman

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Mar 30 15:41:22 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TS (DBID=1044150117, not open)

RMAN> run {
2> SHUTDOWN IMMEDIATE;
3> STARTUP MOUNT;
4> allocate channel d1 type disk;
5> allocate channel d2 type disk;
6> backup AS COMPRESSED BACKUPSET full
7> tag full_offline_backup
8> format 'e:\backups6\db_t%t_s%s_p%p'
9> (database);
10> release channel d1;
11> release channel d2;
12> alter database open;
13> }
14>
using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    2087780352 bytes

Fixed Size                     2116536 bytes
Variable Size                469763144 bytes
Database Buffers            1593835520 bytes
Redo Buffers                  22065152 bytes

allocated channel: d1
channel d1: SID=2189 device type=DISK

allocated channel: d2
channel d2: SID=2188 device type=DISK

Starting backup at 30-MAR-10
channel d1: starting compressed full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00001 name=E:\APP\LG\ORADATA\TS\SYSTEM01.DBF
input datafile file number=00004 name=E:\APP\LG\ORADATA\TS\USERS01.DBF
input datafile file number=00003 name=E:\APP\LG\ORADATA\TS\UNDOTBS01.DBF
channel d1: starting piece 1 at 30-MAR-10
channel d2: starting compressed full datafile backup set
channel d2: specifying datafile(s) in backup set
input datafile file number=00002 name=E:\APP\LG\ORADATA\TS\SYSAUX01.DBF
input datafile file number=00005 name=E:\APP\LG\ORADATA\TS\DATA01.DBF
input datafile file number=00006 name=E:\APP\LG\ORADATA\TS\INDEXES01.DBF
channel d2: starting piece 1 at 30-MAR-10
channel d2: finished piece 1 at 30-MAR-10
piece handle=E:\BACKUPS6\DB_T715016504_S8_P1 tag=FULL_OFFLINE_BACKUP comment=NONE
channel d2: backup set complete, elapsed time: 00:00:15
channel d2: starting compressed full datafile backup set
channel d2: specifying datafile(s) in backup set
including current control file in backup set
channel d2: starting piece 1 at 30-MAR-10
channel d2: finished piece 1 at 30-MAR-10
piece handle=E:\BACKUPS6\DB_T715016523_S9_P1 tag=FULL_OFFLINE_BACKUP comment=NONE
channel d2: backup set complete, elapsed time: 00:00:01
channel d2: starting compressed full datafile backup set
channel d2: specifying datafile(s) in backup set
including current SPFILE in backup set
channel d2: starting piece 1 at 30-MAR-10
channel d2: finished piece 1 at 30-MAR-10
piece handle=E:\BACKUPS6\DB_T715016529_S10_P1 tag=FULL_OFFLINE_BACKUP comment=NONE
channel d2: backup set complete, elapsed time: 00:00:01
channel d1: finished piece 1 at 30-MAR-10
piece handle=E:\BACKUPS6\DB_T715016500_S7_P1 tag=FULL_OFFLINE_BACKUP comment=NONE
channel d1: backup set complete, elapsed time: 00:00:33
Finished backup at 30-MAR-10

released channel: d1

released channel: d2

database opened

Recovery Manager complete.

C:\Documents and Settings\LG\Desktop>rman TARGET / @myrestore5.rman

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Mar 30 15:43:30 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TS (DBID=1044150117)

RMAN> run {
2> SHUTDOWN IMMEDIATE;
3> STARTUP MOUNT;
4> allocate channel d1 type disk format 'e:\backups5\db_t%t_s%s_p%p';
5> allocate channel d2 type disk format 'e:\backups5\db_t%t_s%s_p%p';
6> RESTORE database;
7> release channel d1;
8> release channel d2;
9> recover database;
10> alter database open RESETLOGS;
11> }
12>
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    2087780352 bytes

Fixed Size                     2116536 bytes
Variable Size                469763144 bytes
Database Buffers            1593835520 bytes
Redo Buffers                  22065152 bytes

allocated channel: d1
channel d1: SID=2189 device type=DISK

allocated channel: d2
channel d2: SID=2188 device type=DISK

Starting restore at 30-MAR-10

channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 00002 to E:\APP\LG\ORADATA\TS\SYSAUX01.DBF
channel d1: restoring datafile 00005 to E:\APP\LG\ORADATA\TS\DATA01.DBF
channel d1: restoring datafile 00006 to E:\APP\LG\ORADATA\TS\INDEXES01.DBF
channel d1: reading from backup piece E:\BACKUPS6\DB_T715016504_S8_P1
channel d2: starting datafile backup set restore
channel d2: specifying datafile(s) to restore from backup set
channel d2: restoring datafile 00001 to E:\APP\LG\ORADATA\TS\SYSTEM01.DBF
channel d2: restoring datafile 00003 to E:\APP\LG\ORADATA\TS\UNDOTBS01.DBF
channel d2: restoring datafile 00004 to E:\APP\LG\ORADATA\TS\USERS01.DBF
channel d2: reading from backup piece E:\BACKUPS6\DB_T715016500_S7_P1
channel d1: piece handle=E:\BACKUPS6\DB_T715016504_S8_P1 tag=FULL_OFFLINE_BACKUP
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:00:16
channel d2: piece handle=E:\BACKUPS6\DB_T715016500_S7_P1 tag=FULL_OFFLINE_BACKUP
channel d2: restored backup piece 1
channel d2: restore complete, elapsed time: 00:00:36
Finished restore at 30-MAR-10

released channel: d1

released channel: d2

Starting recover at 30-MAR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2189 device type=DISK

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file C:\REDO\ORADATA\TS\REDO01.LOG
archived log file name=C:\REDO\ORADATA\TS\REDO01.LOG thread=1 sequence=1
media recovery complete, elapsed time: 00:00:01
Finished recover at 30-MAR-10

database opened

Recovery Manager complete.

C:\Documents and Settings\LG\Desktop>rman TARGET / @myrestore6.rman

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Mar 30 15:45:34 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TS (DBID=1044150117)

RMAN> run {
2> SHUTDOWN IMMEDIATE;
3> STARTUP MOUNT;
4> allocate channel d1 type disk format 'e:\backups6\db_t%t_s%s_p%p';
5> allocate channel d2 type disk format 'e:\backups6\db_t%t_s%s_p%p';
6> RESTORE database;
7> release channel d1;
8> release channel d2;
9> recover database;
10> alter database open RESETLOGS;
11> }
12>
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    2087780352 bytes

Fixed Size                     2116536 bytes
Variable Size                469763144 bytes
Database Buffers            1593835520 bytes
Redo Buffers                  22065152 bytes

allocated channel: d1
channel d1: SID=2189 device type=DISK

allocated channel: d2
channel d2: SID=2188 device type=DISK

Starting restore at 30-MAR-10

channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 00002 to E:\APP\LG\ORADATA\TS\SYSAUX01.DBF
channel d1: restoring datafile 00005 to E:\APP\LG\ORADATA\TS\DATA01.DBF
channel d1: restoring datafile 00006 to E:\APP\LG\ORADATA\TS\INDEXES01.DBF
channel d1: reading from backup piece E:\BACKUPS6\DB_T715016504_S8_P1
channel d2: starting datafile backup set restore
channel d2: specifying datafile(s) to restore from backup set
channel d2: restoring datafile 00001 to E:\APP\LG\ORADATA\TS\SYSTEM01.DBF
channel d2: restoring datafile 00003 to E:\APP\LG\ORADATA\TS\UNDOTBS01.DBF
channel d2: restoring datafile 00004 to E:\APP\LG\ORADATA\TS\USERS01.DBF
channel d2: reading from backup piece E:\BACKUPS6\DB_T715016500_S7_P1
channel d1: piece handle=E:\BACKUPS6\DB_T715016504_S8_P1 tag=FULL_OFFLINE_BACKUP
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:00:26
channel d2: piece handle=E:\BACKUPS6\DB_T715016500_S7_P1 tag=FULL_OFFLINE_BACKUP
channel d2: restored backup piece 1
channel d2: restore complete, elapsed time: 00:00:36
Finished restore at 30-MAR-10

released channel: d1

released channel: d2

Starting recover at 30-MAR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2189 device type=DISK

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file C:\REDO\ORADATA\TS\REDO01.LOG
RMAN-08187: WARNING: media recovery until SCN 918787 complete
Finished recover at 30-MAR-10

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/30/2010 15:46:36
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: 'E:\APP\LG\ORADATA\TS\SYSTEM01.DBF'

Recovery Manager complete.

C:\Documents and Settings\LG\Desktop>

Report message to a moderator

Re: Full Restore after reset logs [message #450893 is a reply to message #449601]

Sat, 10 April 2010 07:55

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

archived log for thread 1 with sequence 1 is already on disk as file C:\REDO\ORADATA\TS\REDO01.LOG
RMAN-08187: WARNING: media recovery until SCN 918787 complete
Finished recover at 30-MAR-10

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/30/2010 15:46:36
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: 'E:\APP\LG\ORADATA\TS\SYSTEM01.DBF'

I think REDO files already available in "C:\REDO\ORADATA\TS". Are you sure before restore/recover have you removed redo from C:\REDO\ORADATA\TS ??

- Babu

Report message to a moderator

Previous Topic:	Autobackup location
Next Topic:	compress backup in incremental

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 20:31:59 CST 2024