RMAN Recover database noredo commands
When our db running in noarchivelog mode, Online redolog file may failure occur during startup your db, how do you recover if you have a consistent cold backup avilable for your db ( No ArchiveLog Mode).
” We can perform limited recovery of changes to a database running in NOARCHIVELOG mode by applying incremental backups. The incremental backups must be consistent, like all backups of a database run in NOARCHIVELOG mode, so you cannot back up the database when it is open.
Assume that you run database prod in NOARCHIVELOG mode with a recovery catalog. You shut down the database consistently and make a level 0 backup of database prod to tape on Sunday afternoon. You shut down the database consistently and make a level 1 differential incremental backup to tape at 3:00 a.m. on Wednesday and Friday.
On Saturday, a media failure destroys half of the datafiles as well as the online redo logs. Because the online logs are lost, you must specify the NOREDO option in the RECOVER command. Otherwise, RMAN searches for the redo logs after applying the Friday incremental backup and issues an error message when it does not find them. ”
Just a Example :
Step 1: Create user and table
Enter user-name: / as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create tablespace testfor datafile 'c:\oracle\testfor01.dbf' size 50m;
Tablespace created.
SQL> create user testme identified by testme default tablespace testfor;
User created.
SQL> grant connect,resource to testme;
Grant succeeded.
SQL> conn testme/testme;
Connected.
SQL> create table test(emp varchar2(20));
Table created.
SQL> insert into test values('azar');
1 row created.
SQL> commit;
Commit complete.
Step 2 : Shutdown and Startup mount
SQL> conn / as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1247900 bytes Variable Size 75498852 bytes Database Buffers 88080384 bytes Redo Buffers 2945024 bytes Database mounted.
Step 3: I took RMAN Backup
SQL> host Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\Documents and Settings\Administrator>rman target / Recovery Manager: Release 10.2.0.1.0 - Production on Sun Feb 13 12:52:14 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: TESTDB (DBID=2526327566, not open) RMAN> backup incremental level 0 database; Starting backup at 13-FEB-11 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental level 0 datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSTEM01.D BF input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSAUX01.D BF input datafile fno=00005 name=C:\ORACLE\TESTFOR01.DBF input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\UNDOTBS01. DBF input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\USERS01.DB F channel ORA_DISK_1: starting piece 1 at 13-FEB-11 channel ORA_DISK_1: finished piece 1 at 13-FEB-11 piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2011_ 02_13\O1_MF_NNND0_TAG20110213T125556_6OHBODSD_.BKP tag=TAG20110213T125556 commen t=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45 Finished backup at 13-FEB-11 Starting Control File and SPFILE Autobackup at 13-FEB-11 piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\AUTOBACKUP\2011 _02_13\O1_MF_S_742999909_6OHBPTCT_.BKP comment=NONE Finished Control File and SPFILE Autobackup at 13-FEB-11 RMAN> exit Recovery Manager complete.
Step 4: I just added more rows for test table.
C:\Documents and Settings\Administrator>exit
SQL> alter database open;
Database altered.
SQL> conn testme/testme;
Connected.
SQL> insert into test values('kareem');
1 row created.
SQL> insert into test values('idrees');
1 row created.
SQL> insert into test values('jabar');
1 row created.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 75498852 bytes
Database Buffers 88080384 bytes
Redo Buffers 2945024 bytes
Database mounted.
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
Step 5: Now I just took Incremantal backup
C:\Documents and Settings\Administrator>rman target / Recovery Manager: Release 10.2.0.1.0 - Production on Sun Feb 13 12:59:41 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: TESTDB (DBID=2526327566, not open) RMAN> backup incremental level 1 database; Starting backup at 13-FEB-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=155 devtype=DISK channel ORA_DISK_1: starting incremental level 1 datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSTEM01.D BF input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSAUX01.D BF input datafile fno=00005 name=C:\ORACLE\TESTFOR01.DBF input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\UNDOTBS01. DBF input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\USERS01.DB F channel ORA_DISK_1: starting piece 1 at 13-FEB-11 channel ORA_DISK_1: finished piece 1 at 13-FEB-11 piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2011_ 02_13\O1_MF_NNND1_TAG20110213T125954_6OHBWTRJ_.BKP tag=TAG20110213T125954 commen t=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 Finished backup at 13-FEB-11 Starting Control File and SPFILE Autobackup at 13-FEB-11 piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\AUTOBACKUP\2011 _02_13\O1_MF_S_743000354_6OHBXFQT_.BKP comment=NONE Finished Control File and SPFILE Autobackup at 13-FEB-11 RMAN>
Step 6: Again I just added one row for TEST table
SQL> alter database open;
Database altered.
SQL> conn testme/testme;
Connected.
SQL> insert into test values('norecover');
1 row created.
SQL> commit;
Commit complete.
Step 7: Log group status
SQL> conn / as sysdba Connected. SQL> select group#,status,thread#,sequence#,first_change# from v$log; GROUP# STATUS THREAD# SEQUENCE# FIRST_CHANGE# ---------- ---------------- ---------- ---------- ------------- 1 CURRENT 1 2 545486 2 UNUSED 1 0 0 3 INACTIVE 1 1 534907
Step 8: I just moved logile to another location
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. C:\oracle\product\10.2.0\oradata\testdb>move redo01.log c:\oracle\ SQL> startup mount ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1247900 bytes Variable Size 75498852 bytes Database Buffers 88080384 bytes Redo Buffers 2945024 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\REDO01.LOG' SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
Step 9: Now Time to recover using recover database NOREDO command.
RMAN> startup nomount connected to target database (not started) Oracle instance started Total System Global Area 167772160 bytes Fixed Size 1247900 bytes Variable Size 75498852 bytes Database Buffers 88080384 bytes Redo Buffers 2945024 bytes RMAN> restore controlfile from autobackup; Starting restore at 13-FEB-11 using channel ORA_DISK_1 recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area database name (or database unique name) used for search: TESTDB channel ORA_DISK_1: autobackup found in the recovery area channel ORA_DISK_1: autobackup found: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AR EA\TESTDB\AUTOBACKUP\2011_02_13\O1_MF_S_743000354_6OHBXFQT_.BKP channel ORA_DISK_1: control file restore from autobackup complete output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\CONTROL01.CTL output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\CONTROL02.CTL output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\CONTROL03.CTL Finished restore at 13-FEB-11 RMAN> alter database mount; database mounted released channel: ORA_DISK_1 RMAN> restore database; Starting restore at 13-FEB-11 Starting implicit crosscheck backup at 13-FEB-11 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK Crosschecked 3 objects Finished implicit crosscheck backup at 13-FEB-11 Starting implicit crosscheck copy at 13-FEB-11 using channel ORA_DISK_1 Finished implicit crosscheck copy at 13-FEB-11 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\AUTOBACKUP\2011_0 2_13\O1_MF_S_743000354_6OHBXFQT_.BKP 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 C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSTEM01.DBF restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\UNDOTBS01.DB F restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSAUX01.DBF restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\USERS01.DBF restoring datafile 00005 to C:\ORACLE\TESTFOR01.DBF channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\FLASH_REC OVERY_AREA\TESTDB\BACKUPSET\2011_02_13\O1_MF_NNND0_TAG20110213T125556_6OHBODSD_. BKP channel ORA_DISK_1: restored backup piece 1 piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2011_ 02_13\O1_MF_NNND0_TAG20110213T125556_6OHBODSD_.BKP tag=TAG20110213T125556 channel ORA_DISK_1: restore complete, elapsed time: 00:01:16 Finished restore at 13-FEB-11 RMAN> recover database; Starting recover at 13-FEB-11 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00001: C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST DB\SYSTEM01.DBF destination for restore of datafile 00002: C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST DB\UNDOTBS01.DBF destination for restore of datafile 00003: C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST DB\SYSAUX01.DBF destination for restore of datafile 00004: C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST DB\USERS01.DBF destination for restore of datafile 00005: C:\ORACLE\TESTFOR01.DBF channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\FLASH_REC OVERY_AREA\TESTDB\BACKUPSET\2011_02_13\O1_MF_NNND1_TAG20110213T125954_6OHBWTRJ_. BKP channel ORA_DISK_1: restored backup piece 1 piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2011_ 02_13\O1_MF_NNND1_TAG20110213T125954_6OHBWTRJ_.BKP tag=TAG20110213T125954 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 starting media recovery unable to find archive log archive log thread=1 sequence=2 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 02/13/2011 14:51:58 RMAN-06054: media recovery requesting unknown log: thread 1 seq 2 lowscn 547146 RMAN> recover database noredo; Starting recover at 13-FEB-11 using channel ORA_DISK_1 Finished recover at 13-FEB-11 RMAN> alter database open resetlogs; database opened RMAN>
Now database Opened, I’m going to check Wheter my last added row avilable (Step 6) or not because It added after taken incremental backup.
SQL> conn testme/testme; Connected. SQL> select * from test; EMP -------------------- azar kareem idrees jabar SQL>
Now There is no last added row avilable, because ” The recovered database reflects only changes up through the time of the Friday incremental backup. Because there are no archived redo logs, there is no way to recover changes made after the incremental backup ”
Ref :-
http://www.comp.dit.ie/btierney/oracle11gdoc/backup.111/b28273/rcmsynta030.htm#CHDHFBFI
DBA 