RMAN scenarios

http://itcareershift.com/blog1/2010/11/25/real-life-oracle-dba-scenarios-using-rman-backups-and-troubleshooting-new-oracle-dba-career/
http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmadvre.htm
http://www.reachdba.com/showthread.php?453-RMAN-All-major-Restoration-and-Recovery-Scenarios


Note: When database is in open state then we can not delete datafiles from OS level (File is being used by another User)


--------------------------------------------------------------DataFile removed physcially from OS level and no Backup of that datafile------------------------------------

1) rman backup taken 01-01-12
2) add datafile and add some tables and data on 02-01-12
3) remove datafile from OS level on 02-01-12 no backup till now
4) alter tablespace USR offline;
ORA-01116: error in opening database file 11
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
5) alter tablespace drtbs offline immediate;
select TABLESPACE_NAME,STATUS from dba_tablespaces;
6) select file_id, file_name from dba_data_files where Tablespace_name='USR';
7) RMAN TARGET
8) rman > list of backup datafile 11;
9) list backup of datafile 12; ---- no exist because no backup
10) RMAN> restore tablespace USR;
11) RMAN> recover tablespace USR;
select file_id, file_name from dba_data_files where Tablespace_name='USR';


--------------------------------------------------------------------------Loss of Controlfile-----------------------------

1) select name from v$controlfile;
2) remove controlfile physcially from OS level
3) alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount;

Since we are not using a RMAN catalog we need to set the DBID

RMAN> set dbid=2415549446;

executing command: SET DBID

Restore the controlfile

RMAN> run {
 restore controlfile from autobackup;
 }

error RMAN-06172 because when check show all; controlfile autoback is not present...also not able to configure again becuase of nomount state

set controlfile autobackup format for device type disk to '/orabkp\%F';
restore controlfile from autobackup;


RMAN> alter database mount;
RMAN> recover database;
SQL> alter database open resetlogs;

-------------------------------------recover and open the database if the archive log required for recovery is miss

SQL> recover database until cancel using backup controlfile;
CANCEL
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent


1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) recover database until cancel using backup controlfile;
4) Alter database open resetlogs.
5) reset undo_management to “manual” in init.ora file.
6) startup database
7) Create new undo tablespace
changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”


------------------------------------------Redo log removed from OS level-----------------------------------

select member from v$Logfile;

rm redo*.log

If one or all of the online redo logfiles are delete then the database hangs and in the alert log file
ARC1: Failed to archive thread 1 sequence 10 (0)

SQL> shutdown immediate;
SQL> startup mount;

SELECT GROUP#,SEQUENCE#,STATUS,FIRST_CHANGE# from v$log;

   GROUP#  SEQUENCE# STATUS           FIRST_CHANGE#
--------- ---------- ---------------- -------------
        1         10 CURRENT             8.6056E+10
        3          9 INACTIVE            8.6056E+10
        2          8 INACTIVE            8.6056E+10

RMAN TARGET /
RMAN> run {
2> set until sequence 10;
3> restore database;
4> recover database;
5>  alter database open resetlogs;
6> }
EXIT

The recovery process creates the online redo logfiles at the operating system level also.
Since we have done an incomplete recover with open resetlogs, we should take a fresh complete backup of the database.

NOTE: Please make sure you remove all the old archived logfiles from the archived area.




------------------------------------------------------Drop tablespace by mistake (drop tablespace test including contents and datafiles;)------Point inTime

DBA realized the mistake;

He will refer alert log for the exact timing when tablespace was dropped.

-------Alert log--------------

Sun Feb  4 10:59:43 2012
drop tablespace test including contents and datafiles
Sun Feb 4 10:59:47 2012
Completed: drop tablespace test including contents and datafiles



SQL> shutdown abort
rman target /

RMAN> RUN
{
STARTUP NOMOUNT
SET UNTIL TIME "TO_DATE ('04-02-07 10:58:00', 'DD-MM-YY HH24:MI:SS')";
RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}

select name from v$tablespace;

-----------------------------------------------------------Complete loss of all database files including SPFILE using RMAN

ls -l
rm *.dbf
ls -l *.dbf
ls: *.dbf: No such file or directory
mv spfileopsdba.ora spfileopsdba.org
ls -lt spfile* *SPFILE REMOVED


Database Details
------------------
Database Name=OPSDBA
Machine Name=ITLINUXDEVBLADE07
DBID=1499754868 (select dbid from v$database)
---------------------

Step 1: RECOVERY OF SPFILE

Create spfile.rcv as:
set dbid= 1499754868
run {
startup nomount force  ;
 };

rman target / catalog rman10/rman10@rman10p cmdfile=spfile.rcv

Now restore the spfile

set dbid=1499754868
run {
allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';
restore spfile ;
release channel ch1 ;
}


Step 2: RESTORE OF CONTROLFILES

Same Steps as spfile with the restore command changed. So the new script is

set dbid=1499754868
run {
allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';
restore controlfile ;
release channel ch1 ;
}

Step 3: RESTORE OF DATABASE
SQL> conn sys as sysdba
SQL> alter database mount;

Now get the log sequence number of the database from the catalog database:

select sequence# from rc_backup_redolog where db_name=’OPSDBA’;

RMAN> run {
2> allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';
3> restore database ;
4> recover database until logseq=6; -- GOT FROM THE ABOVE QUERY
5> release channel ch1 ;
6> }
7>


Step 4: alter database open resetlogs;



--------------------------- SYSTEM / SYSAUX / UNDO tablespace is missing

In this case complete recovery is performed, only the system tablespace is missing, so the database can be opened without resetlogs option.

$ rman target /
RMAN> startup mount;
RMAN> restore tablespace SYSTEM;
RMAN> recover tablespace SYSTEM;
RMAN> alter database open;

$ rman target /
RMAN> startup mount;
RMAN> restore tablespace SYSAUX;
RMAN> recover tablespace SYSAUX;
RMAN> alter database open;



------------------------------Non system tablespace is missing, database is up

$ rman target /
RMAN> sql ‘alter tablespace <tbs> offline immediate’ ;
RMAN> restore tablespace <tbs> ;
RMAN> recover tablespace <tbs> ;
RMAN> sql ‘alter tablespace <tbs> online’ ;

To restore/recover only datafile(s)
$ rman target /

RMAN>. sql 'alter database datafile <file#> offline';
RMAN> restore datafile <file#>;
RMAN> recover datafile <file#>;
RMAN> sql 'alter database datafile <file#> online' ;

-------------------------------Non system tablespace is missing,database is closed
sqlplus “/ as sysdba “
startup mount
alter database datafile <file#> offline;
alter database open;
exit;

$rman target /
RMAN> restore datafile <file#>;
RMAN> recover datafile <file#>;
RMAN> sql 'alter tablespace <tablespace_name> online';


-----------------------restore a tablespace to a new location
 rman target / catalog rman/rman@rcat
run {
allocate channel ch1 type disk;
sql 'ALTER TABLESPACE USERS OFFLINE IMMEDIATE';
set newname for datafile '/disk1/oracle/users_1.dbf' to '/disk2/oracle/users_1.dbf';
restore tablespace users;
# make the control file recognize the restored file as current
switch datafile all;
}

RMAN> recover tablespace USERS;
RMAN> sql 'alter tablespace USERS online';

-------------------Recovery of a Datafile that has no backups (database is up)
If a non system datafile that was not backed up since the last backup, is missing, recovery can be performed if all archived logs since the creation of the missing datafile exist.

Pre requisites: All relevant archived logs.

$ rman target /
RMAN> sql ‘alter database datafile <file#> offline’;
RMAN> restore datafile <file#> ;
-- no need to create a blank file, restore command takes care of that.
RMAN> recover datafile <file#>;
RMAN> sql 'alter database datafile <file#> online';

-------------------------------Recovering After the Loss of All Members of an Online Redo Log Group

If a media failure damages all members of an online redo log group, then different scenarios can occur depending on the type of online redo log group affected by the failure and the archiving mode of the database.
If the damaged log group is inactive, then it is not needed for crash recovery; if it is active, then it is needed for crash recovery.
SQL> startup mount

Case-1 If the group is INACTIVE
Then it is not needed for crash recovery
Clear the archived or unarchived group. (For archive status, check in v$log)

1.1 Clearing Inactive, Archived Redo

alter database clear logfile group 1 ;
alter database open ;

1.2 Clearing Inactive, Not-Yet-Archived Redo

alter database clear unarchived logfile group 1 ;
OR
(If there is an offline datafile that requires the cleared log to bring it online, then the keywords UNRECOVERABLE DATAFILE are required. The datafile and its entire tablespace have to be dropped because the redo necessary to bring it online is being cleared, and there is no copy of it. )

alter database clear unarchived logfile group 1 unrecoverable datafile;

Take a complete backup of database.

And now open database:
alter database open ;


Case-2 If the group is ACTIVE

Restore backup and perform an incomplete recovery.
And open database using resetlogs
alter database open resetlogs;

Case-3 If the group is CURRENT

Restore backup and perform an incomplete recovery.
And open database using resetlogs
alter database open resetlogs;


--------------------------
1) OPEN REDO01.LOG (INACTIVE) AND EDIT AND CORRUPT IT

SQL> ALTER SYSTEM SWITCH LOGFILE; -------HANG
alter database clear logfile group 1 ;

ORA-00350: log 1 of instance rkangel (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: 'G:\RKANGEL\REDO01.LOG'

SQL> SHUT IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
SQL> ALTER DATABASE OPEN;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;

-------------2) OPEN REDO02.LOG (ACTIVE) AND EDIT AND CORRUPT IT
SQL> ALTER SYSTEM SWITCH LOGFILE; -------HANG
ERROR at line 1:
ORA-03113: end-of-file on communication channel

SQL> STARTUP
ORACLE instance started.

Total System Global Area 6413680640 bytes
Fixed Size                  2267184 bytes
Variable Size            4563404752 bytes
Database Buffers         1828716544 bytes
Redo Buffers               19292160 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'G:\RKANGEL\REDO02.LOG'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 52429318)

ALTER DATABASE CLEAR LOGFILE GROUP 2;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
alter database clear logfile group 2 unrecoverable datafile;
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance rkangel (thread 1)
ORA-00312: online log 2 thread 1: 'G:\RKANGEL\REDO02.LOG'


-----note: For ACTIVE or CURRENT logfile
Restore backup and perform an incomplete recovery.
And open database using resetlogs
alter database open resetlogs;


1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) recover database until cancel using backup controlfile;
4) Alter database open resetlogs.
5) reset undo_management to “manual” in init.ora file.
6) startup database
7) Create new undo tablespace
changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”



--------------------------------------Recovering a NOARCHIVELOG Database
Restore of a database running in NOARCHIVELOG mode is similar to restore of a database in ARCHIVELOG mode. The main differences are:

Only consistent backups can be used in restoring a database in NOARCHIVELOG mode.

Media recovery is not possible because no archived redo logs exist.

When recovering a NOARCHIVELOG database, specify the NOREDO option on the RECOVER command to indicate that RMAN should not attempt to apply archived redo logs.



----------------------------------------------------------------Remove Temporary datafile from OS level---------------------------------

shut immediate
Remove Temporary datafile from OS level
startup
auotmatically create temp

shut immediate
startup mount
Remove Temporary datafile from OS level
alter database open;
auotmatically create temp


----------------------------------------------------------------Remove UNDO datafile from OS level----------------------------------------

shut immediate
startup mount
Remove UNDO datafile from OS level
alter database open;

ERROR at line 1:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'G:\RKANGEL\UNDOTBS01.ORA'

RMAN> restore tablespace UNDOTBS1;
RMAN> recover tablespace UNDOTBS1;
RMAN> alter database open;


----------------------------------------How to recover dropped / deleted table using RMAN backup----------------------

restore backup on to the UAT machine
export the table from UAT
import into LIVE

1) incomplete recovery ( just before to the drop table) can recover your table from RMAN backup.
or
2) You can recover /duplcate the database to another database with RMAN backup
Export the concerned table from the restored/duplicated database to the database you want.

3) use flashback (in oracle 10g) for indivisual table recovery purpose.
Oracle 10g makes life easier with the ability to recover a dropped table similar to recovering a file from a Windows Recycle Bin.
And in case if this on 10g,
Eg: You dropped the DEPT table, which belongs to the USER_DATA tablespace
You can use the following command to recover a dropped table:

FLASHBACK TABLE <table_name> TO BEFORE DROP;



4) RMAN TSPITR is most useful for recovering the following:
An erroneous DROP TABLE or TRUNCATE TABLE statement
A table that has become logically corrupted
An incorrect batch job or other DML statement that has affected only a subset of the database
A logical schema to a point different from the rest of the physical database when multiple schemas exist in separate tablespaces of one physical database

--------------------------------------------------------Point in time recovery using RMAN (until a log sequence number) ----------------delete some data from table by mistake

Someone delete all data from a table at 1:47 PM (delete from myobjects) SELECT * FROM DBA_AUDIT_TRAIL;

windows dir d:\archive
20/09/2012  01:44 PM             1,024 ARC0000000010_0794497245.0001
20/09/2012  01:47 PM        41,461,248 ARC0000000011_0794497245.0001


archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/ORACLE/opsdba/arch
Oldest online log sequence     21
Next log sequence to archive   23
Current log sequence           23
--Note the current log sequence number (22)

We need to determine the log sequence we need to recover until
select sequence#,first_change#, to_char(first_time,'HH24:MI:SS') from v$log order by 3

SEQUENCE# FIRST_CHANGE# TO_CHAR(
--------- ------------- --------
       21    8.6056E+10 13:48:16
       22    8.6056E+10 13:48:22
       23    8.6056E+10 13:48:29

Log sequence 21 was first written to at 1:48 PM so we should recover to a log sequence before this – i.e sequence# 10 (from window dir )


SQL> shutdown immediate;
SQL> startup mount;
RMAN> run {
set until sequence=10;  >>> add one to the sequence number we have to recover until
restore database;
recover database;
}
alter database open resetlogs;

ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: 'G:\RKANGEL\SYSTEM01.ORA'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/20/2012 14:15:38
ORA-01147: SYSTEM tablespace file 1 is offline
ORA-01110: data file 1: 'G:\RKANGEL\SYSTEM01.ORA'

SQL> ALTER DATABASE DATAFILE 1 ONLINE;
SQL> RECOVER DATABASE;

ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.


select count(*) from myobjects;


---------------------------------------------------Recover Dropped Table from Recyclebin using Flashback Table-------------------------

Oracle Flashback Table enables you to restore a table to its state as of a previous point in time. It provides a fast, online solution for recovering a table that has been accidentally modified or deleted by a user or application. In many cases, Oracle Flashback Table eliminates the need for you to perform more complicated point-in-time recovery operations.

Oracle Flashback Table:

Restores all data in a specified table to a previous point in time described by a timestamp or SCN.

Performs the restore operation online.

Automatically maintains all of the table attributes, such as indexes, triggers, and constraints that are necessary for an application to function with the flashed-back table.

Maintains any remote state in a distributed environment. For example, all of the table modifications required by replication if a replicated table is flashed back.

Maintains data integrity as specified by constraints. Tables are flashed back provided none of the table constraints are violated. This includes any referential integrity constraints specified between a table included in the FLASHBACK TABLE statement and another table that is not included in the FLASHBACK TABLE statement.

Even after a flashback operation, the data in the original table is not lost. You can later revert to the original state.

FLASHBACK TABLE <table_name> TO BEFORE DROP;

Some other variations of the flashback database command include.

FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn;
FLASHBACK DATABASE TO BEFORE SCN my_scn;

--------------------------------------------------Recover deleted Table Data from Recyclebin using Flashback Table---------------------

On Oracle Database 11g (10gR2...), we can rewind one or more tables back to their contents at a previous time without affecting other database objects.

Before we use Flashback Table, We must enable row movement on the table. because rowids will change after the flashback.

Example: Flashback the table back to previous time using SCN


select count(*) from LDBO.test;
COUNT(*)
----------
68781

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
1584494

SQL> delete from LDBO.test where rownum <= 50000;

50000 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from LDBO.test;

COUNT(*)
----------
18781

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
1587106

Enable row movement:

SQL> alter table LDBO.test enable row movement;

Table altered.

SQL> FLASHBACK TABLE LDBO.test to scn 1584494;

Flashback complete.

SQL> select count(*) from LDBO.test;


QL> alter table LDBO.test disable row movement;

Table altered.


We can rewind the table back to previous time using timestamp:

SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2009/08/30 17:01:09

SQL> delete from LDBO.test ;

68781 rows deleted.

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE
-------------------
2009/08/30 17:03:18

SQL> select count(*) from LDBO.test;

COUNT(*)
----------
0

SQL> alter table LDBO.test enable row movement;

Table altered.

SQL> flashback table LDBO.test to timestamp TO_TIMESTAMP('2009/08/30 17:01:09','YYYY/MM/DD HH24:MI:SS');

Flashback complete.

SQL> select count(*) from LDBO.test;

COUNT(*)
----------
68781

--------------------------------------------------------------------------------------------Flashback Table recover table to different table----------------------

FLASHBACK TABLE test TO BEFORE DROP RENAME TO test2;

flashback table LDBO.test to timestamp TO_TIMESTAMP('2009/08/30 17:01:09','YYYY/MM/DD HH24:MI:SS') RENAME TO test2;