Backup and restore of Database before upgrade using RMAN

Make sure you always take Database backup before any upgrades and patches.It is always good idea to do this. In the below example I am showing how to take a backup of full database to some location and how to restore it back from backup in-case of any failures.

backup database before upgrade:

rman "target / nocatalog"

RUN
{
ALLOCATE CHANNEL C1 TYPE DISK;
BACKUP DATABASE FORMAT '/u02/backup/backup_%U' TAG "before_upgrade";
BACKUP CURRENT CONTROLFILE FORMAT '/u02/backup/controlfilebkp.ctl';
}



Restoring database from backup in-case of upgrade fail:

RMAN> restore controlfile from '/u02/backup/controlfilebkp.ctl';

Starting restore at 18-JUL-12
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output filename=/u01/app/oracle/oradata/test/control01.ctl
output filename=/u01/app/oracle/oradata/test/control02.ctl
output filename=/u01/app/oracle/oradata/test/control03.ctl
Finished restore at 18-JUL-12

copy controlfile to location control02, control03 :

[oracle@linux5 test]$ cp control01* control02.ctl
[oracle@linux5 test]$ cp control01* control03.ctl
[oracle@linux5 test]$ pwd
/u01/app/oracle/oradata/test
[oracle@linux5 test]$


RMAN> startup mount;

database is already started
database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 18-JUL-12
Starting implicit crosscheck backup at 18-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 18-JUL-12

Starting implicit crosscheck copy at 18-JUL-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-JUL-12

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/TEST/archivelog/2013_07_18/o1_mf_1_8_8yj3n3ww_.arc

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 /u01/app/oracle/oradata/test/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/test/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/test/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/test/example01.dbf
channel ORA_DISK_1: reading from backup piece /u02/backup/backup_06of1qf9_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/backup/backup_06of1qf9_1_1 tag=BEFORE_UPGRADE
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 18-JUL-12


RMAN> recover database;

Starting recover at 18-JUL-12
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 8 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2013_07_18/o1_mf_1_8_8yj3n3ww_.arc
archive log thread 1 sequence 9 is already on disk as file /u01/app/oracle/oradata/test/redo03.log
archive log filename=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2013_07_18/o1_mf_1_8_8yj3n3ww_.arc thread=1 sequence=8
archive log filename=/u01/app/oracle/oradata/test/redo03.log thread=1 sequence=9
media recovery complete, elapsed time: 00:00:02
Finished recover at 18-JUL-12


Now login as sysdba and reset the logs before you can start using the database:

sql>sqlplus /"As sysdba

SQL> alter database open resetlogs;

ORA-01113: file 13 needs media recovery

oracle@Linux01:[/u01/app/oracle/datafiles/orcl] $ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 21 10:09:56 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> ALTER DATABASE
DATAFILE '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf'
ONLINE  2 ;

*
ERROR at line 3:
ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected


SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf' ONLINE;
ALTER DATABASE DATAFILE '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf' ONLINE
*
ERROR at line 1:
ORA-01113: file 13 needs media recovery
ORA-01110: data file 13: '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf'


SQL> recover datafile '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf';
Media recovery complete.
SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf' ONLINE;

Database altered.

SQL>

Recover data using Flashback Query

How to restore the old data using flashback query
My intention is , I want to get back past data of database after erroneously updated and committed.

We know that committed data can never be flashed back. But with 10g new flashback feature we can get back past data even they are committed. 

Before proceed ensure that,

•The UNDO_RETENTION initialization parameter is set to a value so that you can back your data far in the past that you might want to query.

•UNDO_MANAGEMENT is set to AUTO.

•In your UNDO TABLESPACE you have enough space.

With an example I will demonstrate the whole procedure.

1)I have created a table named test_flash_table with column name and salary.

SQL> create table test_flash_table(name varchar2(10), salary number);
Table created.

SQL> insert into test_flash_table values('ABCD',10);
1 row created.

SQL> commit;
Commit complete.

The table contains one row.

2)I erroneously updated column salary of Arju and commited data.

SQL> update test_flash_table set salary=20 where name='ABCD';
1 row updated.

SQL> commit;
Commit complete.

3)After some moments I found that I have made wrong update. Now be sure to query. Also select that time SCN by TIMESTAMP_TO_SCN.

SQL> select name, salary,systimestamp, TIMESTAMP_TO_SCN(SYSTIMESTAMP-interval '8' minute) SCN from test_flash_table as of timestamp (SYSTIMESTAMP-interval '8' Minute);

NAME SALARY SYSTIMESTAMP SCN
---------- ---------- ---------------------------------------- ----------
ABCD 10 29-APR-12 12.34.03.452330 AM -04:00 869222

4)Now update the data based on the SCN.

SQL> update test_flash_table set salary=(select salary from test_flash_table as of scn 869222 where name='ABCD') where name='ABCD';
1 row updated.

SQL> select * from test_flash_table where name='Arju';
NAME SALARY
---------- ----------
ABCD 10

RMAN Backup archielogs from SCN

If you want to backup from SCN until the latest one generated use below as below:

oracle@Linux01:[/u01/app/oracle] $ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 19 11:49:42 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=33581878474)


RMAN> BACKUP ARCHIVELOG FROM SEQUENCE 15 format '/u01/app/oracle/backup/ORCL_ARCHBKP_%U' tag 'ARCH_BKP';

Starting backup at 19-JAN-13
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=15 STAMP=901018555
input archived log thread=1 sequence=16 RECID=16 STAMP=901025590
input archived log thread=1 sequence=17 RECID=17 STAMP=901032775
input archived log thread=1 sequence=18 RECID=18 STAMP=901103089
input archived log thread=1 sequence=19 RECID=19 STAMP=901189465
input archived log thread=1 sequence=20 RECID=20 STAMP=901443077
input archived log thread=1 sequence=21 RECID=21 STAMP=901471668
input archived log thread=1 sequence=22 RECID=22 STAMP=901479677
input archived log thread=1 sequence=23 RECID=23 STAMP=901540229
channel ORA_DISK_1: starting piece 1 at 19-JAN-13
channel ORA_DISK_1: finished piece 1 at 19-JAN-13
piece handle=/u01/app/oracle/backup/ORCL_ARCHBKP_01qroqc6_1_1 tag=ARCH_BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:28
Finished backup at 19-JAN-13


IF you need to backup from SCN until SCN use below cmd:

Example :

RMAN> 

BACKUP ARCHIVELOG FROM SEQUENCE 288 UNTIL SEQUENCE 388 ;