Home » RDBMS Server » Backup & Recovery » Restoring Database from backup made in noarchivelog mode using RMAN
Restoring Database from backup made in noarchivelog mode using RMAN [message #201187] Thu, 02 November 2006 23:02 Go to next message
reena_ch30
Messages: 100
Registered: December 2005
Senior Member
Hi,

Following is the scenario.
1)Backup of database in noarchivelog made using RMAN
rman> run{
allocate channel c1 type disk format 'c:\backup1\%u';
backup database;
}
2)Made some updations in database not backed up. Logfile was overwritten.

3)Restored the database from the backup using "restore database"- Done successfully.

I wish to restore the database to the state where it was at the time of backup.I tried the following options :

1)recover database;
o/p
starting media recovery
unable to find archive log
archive log thread=1 sequence=1
RMAN-00571:
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
RMAN-03002: failure of recover command at 11/03/2006 10:26:33
RMAN-06054: media recovery requesting unknown log: thread 1 scn 5807866

2)recover database open resetlogs;
RMAN> alter database open resetlogs;

RMAN-00571:
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
RMAN-00571:
RMAN-03002: failure of alter db command at 11/03/2006 10:28:07
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

What do i do now? Please suggest.

Thanks
Reena

Re: Restoring Database from backup made in noarchivelog mode using RMAN [message #201233 is a reply to message #201187] Fri, 03 November 2006 04:05 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Read documentation.
without archived logs you cannot do any point in time recovery or incomplete recovery. You can always go back to last full backup (restore).
Re: Restoring Database from backup made in noarchivelog mode using RMAN [message #201238 is a reply to message #201233] Fri, 03 November 2006 04:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmrecov.htm#1008318
Re: Restoring Database from backup made in noarchivelog mode using RMAN [message #201395 is a reply to message #201187] Fri, 03 November 2006 23:16 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi.
How can you take bkp in RMAN with no archive log mode.
no cold bkp ( target database is mount stage ) bkp is usable in noarchive log mode.

You cann't perform media recovery in no archive mode.

On 10.1.0.2.0
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     78
Current log sequence           80
RMAN> run
2> {
3> allocate channel c1 type disk format 'c:\datapump\data\%u';
4> backup database;
5> }

released channel: ORA_DISK_1
allocated channel: c1
channel c1: sid=159 devtype=DISK

Starting backup at 04-NOV-06
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF

input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF

input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DB
F
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DB
F
input datafile fno=00006 name=C:\DATAPUMP\DATA\RMAN01.DBF
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF
channel c1: starting piece 1 at 04-NOV-06
channel c1: finished piece 1 at 04-NOV-06
piece handle=C:\DATAPUMP\DATA\1FI1HQD0 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:06
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current controlfile in backupset
including current SPFILE in backupset
channel c1: starting piece 1 at 04-NOV-06
channel c1: finished piece 1 at 04-NOV-06
piece handle=C:\DATAPUMP\DATA\1GI1HQF3 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:04
Finished backup at 04-NOV-06
released channel: c1

RMAN> exit
SQL> conn hr/hr@orcl
Connected.
SQL> create table bittesttable as select * from cat;

Table created.

SQL> desc bittesttable'
SP2-0565: Illegal identifier.
SQL> desc bittesttable;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLE_TYPE                                         VARCHAR2(11)

SQL> commit;

Commit complete.

Mount the target database and perform restore and recovery operation.
RMAN> run
2> {
3> restore database;
4> recover database;
5> }

Starting restore at 04-NOV-06
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF
restoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBF
restoring datafile 00006 to C:\DATAPUMP\DATA\RMAN01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\DATAPUMP\DATA\1FI1HQD0 tag=TAG20061104T094048
channel ORA_DISK_1: restore complete
Finished restore at 04-NOV-06

Starting recover at 04-NOV-06
using channel ORA_DISK_1

starting media recovery

unable to find archive log
archive log thread=1 sequence=80
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/04/2006 09:47:02
RMAN-06054: media recovery requesting unknown log: thread 1 seq 80 lowscn 196823
9

Above operation want media recovery but in noarchivelog mode it is not possible then
SQL> recover database until cancel;
ORA-00279: change 1968239 generated at 11/04/2006 09:37:34 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_04\O1_MF_1_

80_%U_.ARC
ORA-00280: change 1968239 for thread 1 is in sequence #80


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.
SQL> conn hr/hr@orcl
Connected.
SQL> select * from bittesttable;
select * from bittesttable
              *
ERROR at line 1:
ORA-00942: table or view does not exist





Hope this Helps
Mohammad Taj

[Updated on: Sat, 04 November 2006 00:16]

Report message to a moderator

Re: Restoring Database from backup made in noarchivelog mode using RMAN [message #201440 is a reply to message #201395] Sat, 04 November 2006 04:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>How can you take bkp in RMAN with no archive log mode.
Already explained in the said URL
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmrecov.htm#1008318
Re: Restoring Database from backup made in noarchivelog mode using RMAN [message #201478 is a reply to message #201187] Sat, 04 November 2006 14:45 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
The link Mahesh provided, outlines the recovery procedures for a NOARCHIVELOG database. If the redo logs aren't available or the logs don't contain the "new" data, you can recover simply by specifying NOREDO.

Here is the recovery process for a NOARCHIVELOG database (9.2.0.1), using the NOREDO option:

C:\>rman target /

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area     135338868 bytes

Fixed Size                      453492 bytes
Variable Size                109051904 bytes
Database Buffers              25165824 bytes
Redo Buffers                    667648 bytes

RMAN> set dbid 1566817526

executing command: SET DBID

RMAN> restore controlfile from autobackup;

Starting restore at 04-NOV-06

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
channel ORA_DISK_1: looking for autobackup on day: 20061104
channel ORA_DISK_1: autobackup found: c-1566817526-20061104-01
channel ORA_DISK_1: controlfile restore from autobackup complete
replicating controlfile
input filename=E:\ORACLE\ORADATA\WIN9I\CONTROL01.CTL
output filename=E:\ORACLE\ORADATA\WIN9I\CONTROL02.CTL
output filename=E:\ORACLE\ORADATA\WIN9I\CONTROL03.CTL
Finished restore at 04-NOV-06

RMAN> alter database mount;

database mounted

RMAN> restore database;

Starting restore at 04-NOV-06

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to E:\ORACLE\ORADATA\WIN9I\SYSTEM01.DBF
restoring datafile 00002 to E:\ORACLE\ORADATA\WIN9I\UNDOTBS01.DBF
restoring datafile 00003 to E:\ORACLE\ORADATA\WIN9I\CWMLITE01.DBF
restoring datafile 00004 to E:\ORACLE\ORADATA\WIN9I\DRSYS01.DBF
restoring datafile 00005 to E:\ORACLE\ORADATA\WIN9I\EXAMPLE01.DBF
restoring datafile 00006 to E:\ORACLE\ORADATA\WIN9I\INDX01.DBF
restoring datafile 00007 to E:\ORACLE\ORADATA\WIN9I\ODM01.DBF
restoring datafile 00008 to E:\ORACLE\ORADATA\WIN9I\TOOLS01.DBF
restoring datafile 00009 to E:\ORACLE\ORADATA\WIN9I\USERS01.DBF
restoring datafile 00010 to E:\ORACLE\ORADATA\WIN9I\XDB01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\DB_BACKUPS\08I1IF0N tag=TAG20061104T153239 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 04-NOV-06

RMAN> recover database noredo;

Starting recover at 04-NOV-06
using channel ORA_DISK_1
Finished recover at 04-NOV-06

RMAN> alter database open resetlogs;

database opened

RMAN>
Re: Restoring Database from backup made in noarchivelog mode using RMAN [message #250498 is a reply to message #201187] Tue, 10 July 2007 04:43 Go to previous messageGo to next message
bato
Messages: 6
Registered: July 2007
Location: Republic of Srpska, Bosni...
Junior Member
You can read this blog about recovering database in noarchivelog mode.
http://gnjatovic.blogspot.com/2007/06/no-data-loss-in-noarchivelog-mode.html
Re: Restoring Database from backup made in noarchivelog mode using RMAN [message #250707 is a reply to message #201187] Wed, 11 July 2007 00:44 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Here is The situation That I tested to implement it in my computer but I failed.....

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/oracle/app/oradata/Arju/system01.dbf'


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> !rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jul 11 11:38:37 2007

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

connected to target database: ARJU (DBID=222697548, not open)

RMAN> run{
2> restore database;
3> recover database;
4> }

Starting restore at 11-JUL-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

skipping datafile 1; already restored to file /oracle/app/oradata/Arju/system01.dbf
skipping datafile 2; already restored to file /oracle/app/oradata/Arju/undotbs01.dbf
skipping datafile 3; already restored to file /oracle/app/oradata/Arju/sysaux01.dbf
skipping datafile 4; already restored to file /oracle/app/oradata/Arju/users01.dbf
skipping datafile 5; already restored to file /oradata1/test02.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 11-JUL-07

Starting recover at 11-JUL-07
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 1 is already on disk as file /oracle/app/oradata/Arju/redo02.log
unable to find archive log
archive log thread=1 sequence=3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/11/2007 11:38:58
RMAN-06054: media recovery requesting unknown log: thread 1 seq 3 lowscn 598070

RMAN> ^D

Recovery Manager complete.

SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
Re: Restoring Database from backup made in noarchivelog mode using RMAN [message #250863 is a reply to message #250707] Wed, 11 July 2007 11:27 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Your database was in archivelog mode when you took backup?
did you restore from valid backup?or you have multiple backups.
Re: Restoring Database from backup made in noarchivelog mode using RMAN [message #250935 is a reply to message #250863] Wed, 11 July 2007 21:59 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

DreamzZ wrote on Wed, 11 July 2007 22:27
Your database was in archivelog mode when you took backup?

No.
did you restore from valid backup?

Yes.

or you have multiple backups.
No, Only 1 backup.


Re: Restoring Database from backup made in noarchivelog mode using RMAN [message #252095 is a reply to message #250935] Tue, 17 July 2007 16:51 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Please refer to your other post and in the future, please do not cross-post.
Previous Topic: Archives deletion,
Next Topic: RMAN RETENTION POLICY FOR ORACLE8I
Goto Forum:
  


Current Time: Thu Dec 08 14:30:09 CST 2016

Total time taken to generate the page: 0.08690 seconds