Lets start with a simple scenario for performing backup,restore and recovery using RMAN when our database is running in NOARCHIVELOG mode.
Oracle doc says that recovery cannot be performed in NOARCHIVELOG mode. But following scenarios explains how can we perform complete as well as incomplete recovery in NOARCHIVELOG mode. Different scenarios has been given to show how to recover database to a point in time as well as upto the recent time when the disaster occur. Each scenario has been tested in Oracle 10g xe hoping that each will also work with oracle 10g enterprise edition. Each scenario has been tested in NOARCHIVELOG mode.
Complete recovery can be performed in NOARCHIVEOG mode if all the changes since the last backup exists in the redo logs(including the redo logs that exists during the backup.)
Incomplete recovery can be performed if all the changes since the last backup up to a point in time exists in the redo log.
We can perform incomplete recovery until scn,until time and until sequence using RMAN.
SCENARIO I:Complete recovery: a datafile is lost
1. I am using Oracle 10g express edition.
2. Database is running in NOARCHIVELOG mode.
3. Cold backup has been performed after clean shutdown of the database.
4. One of the datafiles ,say users.dbf, has been lost.
5. All the redologs including one with the same log seq# as during the backup is available.
6. Thus here we have to restore only the effected datafiles,not the whole database.
Use the following sequence of steps:
1. RMAN> shutdown
2. RMAN> startup mount
3. RMAN> restore datafile 4; or restore tablespace users;
4. RMAN> recover datafile 4; or recover tablespace users;
5. RMAN> alter database open;
Instead of step 3 and 4, we can also use
RMAN> restore database; and
RMAN> recover database, respectively.
But we do not need to restore and recover the whole database in this scenario. Important point is that No data loss is guaranteed in NOARCHIVELOG mode if all the redo logs exist since the last backup. Thus complete recovery has been performed in NOARCHIVELOG mode.
SCENARIO II : Incomplete recovery
Keeping everything same as scenario I, some redo log seq no# since the last backup is not available say, redo log has been switched. Users.dbf has been lost. Here, to restore the lost datafile following steps should be taken:
1. RMAN>startup force nomount;
If database is already shutdown, then don't use force option.
2. RMAN>restore controlfile from autobackup; or restore controlfile from 'autobackup_location';
3. RMAN>alter database mount;
4. RMAN>restore database;
5. RMAN>recover database noredo;
6. RMAN> alter database open resetlogs;
Here restore of whole database is must.
You can also follow following link:
SCENARIO III: Incomplete recovery - until scn-when a table is dropped
SQL> CREATE TABLE T1(C1 NUMBER);
SQL> INSERT INTO T1 VALUES(10);
SQL>/
SQL>/
SQL> COMMIT;
SQL> select current_scn from v$database;
It generated 810390;
SQL> drop table t1;
Check v$log. If all the scns since the last backup upto the point you want to recover to (in our case it is, 810390, because table is dropped after that time) exists , then we can recover table t1 even in noarchivelog mode.
Use following operations:
RMAN> run
{
shutdown;
startup mount;
restore database;
recover database until scn 810390;
alter database open resetlogs;
}
Now you will find recovered table t1.
SCENARIO IV- incomplete recovery -until time: when a table is dropped.
1. Yesterday night, you have taken cold,consistent whole database backup.
2. In the next morning, some user error caused employees table t1 to drop.
3. If you know the approximate time of dropping the table, you can recover the table even to the point of recent changes made in the table. There is no need to panic even if your database is running in NOARCHIVElog mode.
4. Check v$log. If all the changes since the last backup is present in the redo log groups, we can recover the dropped table. Use the following procedure. Suppose the table is dropped at 10:30 in the morning.
RMAN> RUN {
2> SET UNTIL TIME = '2011-02-22:10:28:00';
3> RESTORE DATABASE;
4> RECOVER DATABASE;
5> ALTER DATABASE OPEN RESETLOGS;
}
For until time to function you must set $NLS_LANG and $NLS_DATE_FORMAT appropriately. In my case $NLS_LANG has been set to american and NLS_DATE_FORMAT has been set to 'YYYY-MM-DD:HH24:MI:SS';
SCENARIO V-INCOMPLETE RECOVERY : SQLPLUS UNTIL CANCEL:DATAFILES AND ACTIVE REDO LOG FILE IS LOST
Suppose two datafiles as well as active redo log group is lost. Here to recover database, use following procedure:
RMAN> ALTER DATABASE MOUNT;
RMAN> RESTORE DATABASE;
SQL> RECOVER DATABASE UNTIL CANCEL;
ORA-00279: change 828771 generated at 02/23/2011 11:38:39 needed for thread 1
ORA-00289: suggestion :
/usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/archivelog/2011_02_23/o1_mf
_1_1_%u_.arc
ORA-00280: change 828771 for thread 1 is in sequence #1
Specify log: {
cancel
Media recovery cancelled.
RMAN> ALTER DATABASE OPEN RESETLOGS;
Since one of the redolog is lost ,we do not have all the redo logs available after the last backup. That is why we have to use sql command UNTIL CANCEL. Type cancel when prompted. We will get changes only upto the last backup that is restored.
Here we will suppose that along with one datafile, active redo log file has also been lost. Yes, of course, it is the same scenario as scenario VI. But read out, difference will be clear.
1. SQL> truncate table t1;
Table truncated.
SQL> inert into t1 values(0);
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
C1
----------
0
0
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 1 52428800 1 NO ACTIVE
1183659 14-MAR-11
2 1 2 52428800 1 NO CURRENT
1184383 14-MAR-11
Group# 2 is current. We will delete log file that belongs to group# 2. Changes since the last backup are stored in log seq#1 which belongs to group# 1. Only the changes that are stored in log seq#2 will be lost. We will get back our changes that we made in table t1. Note the low scn (1184383,here)that is listed in Log seq#2. We will need it
Get the name and location of the redo log group# 2 by listing the contents of v$logfile.
SQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
2 ONLINE
/usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_2_6qwfdq7v_
.log
YES
1 ONLINE
/usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_1_6qwm5xz0_
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
.log
YES
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
oracle@geet:~$ mv /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_2_6qwfdq7v_.log m.log
oracle@geet:~$ mv oradata/XE/users.dbf uu.bak
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore database UNTIL SCN 1184383;
RMAN> recover databse UNTIL SCN 1184383;
RMAN> alter database open resetlogs;
SQL> select * from t1;
C1
----------
0
0
See the output above, we got the modification of our table back.
Gud article
ReplyDelete