| Restoring Database from backup made in noarchivelog mode using RMAN [message #201187] |
Thu, 02 November 2006 23:02  |
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 #201395 is a reply to message #201187] |
Fri, 03 November 2006 23:16  |
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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
