Tuesday, 3 June 2014

restore UNDO tablespace from RMAN

[oracle@shiven instance]$ sqlplus sys/sys as sysdba


SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 3 11:01:08 2014


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




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


SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /data1/shiven01/dbs/arch
Oldest online log sequence     1
Current log sequence           3
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.


Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             163580064 bytes
Database Buffers          364904448 bytes
Redo Buffers                5832704 bytes
Database mounted.
SQL> alter database archivelog;


Database altered.


SQL> alter database open;


Database altered.


SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /data1/shiven01/dbs/arch
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
You have new mail in /var/spool/mail/oracle
[oracle@shiven instance]$ rman target /


Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jun 3 11:02:28 2014


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


connected to target database: SHIVEN01 (DBID=900949679)


RMAN> backup database;


Starting backup at 03-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/data1/shiven01/dbs/sysaux01.dbf
input datafile file number=00001 name=/data1/shiven01/dbs/system01.dbf
input datafile file number=00003 name=/data1/shiven01/dbs/undotbs01.dbf
input datafile file number=00004 name=/data1/shiven01/dbs/users01.dbf
input datafile file number=00005 name=/data1/shiven01/dbs/indx01.dbf
input datafile file number=00006 name=/data1/shiven01/dbs/tools01.dbf
channel ORA_DISK_1: starting piece 1 at 03-JUN-14
channel ORA_DISK_1: finished piece 1 at 03-JUN-14
piece handle=/data1/shiven01/flash_recovery_area/SHIVEN01/backupset/2014_06_03/o1_mf_nnndf_TAG20140603T110238_9rtqhqqc_.bkp tag=TAG20140603T110238 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 03-JUN-14
channel ORA_DISK_1: finished piece 1 at 03-JUN-14
piece handle=/data1/shiven01/flash_recovery_area/SHIVEN01/backupset/2014_06_03/o1_mf_ncsnf_TAG20140603T110238_9rtqjkty_.bkp tag=TAG20140603T110238 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-JUN-14


RMAN> exit




Recovery Manager complete.
[oracle@shiven instance]$ rm -f /data1/shiven01/dbs/undotbs01.dbf
[oracle@shiven instance]$ sqlplus sys/sys as sysdba


SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 3 11:03:26 2014


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




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


SQL> shutdown immediate
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/data1/shiven01/dbs/undotbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.


Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             163580064 bytes
Database Buffers          364904448 bytes
Redo Buffers                5832704 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
You have new mail in /var/spool/mail/oracle
[oracle@shiven instance]$ rman target /


Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jun 3 11:04:25 2014


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


connected to target database: SHIVEN01 (DBID=900949679, not open)


RMAN> restore tablespace UNDOTBS1;


Starting restore at 03-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK


channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /data1/shiven01/dbs/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /data1/shiven01/flash_recovery_area/SHIVEN01/backupset/2014_06_03/o1_mf_nnndf_TAG20140603T110238_9rtqhqqc_.bkp
channel ORA_DISK_1: piece handle=/data1/shiven01/flash_recovery_area/SHIVEN01/backupset/2014_06_03/o1_mf_nnndf_TAG20140603T110238_9rtqhqqc_.bkp tag=TAG20140603T110238
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 03-JUN-14


RMAN> recover tablespace UNDOTBS1;


Starting recover at 03-JUN-14
using channel ORA_DISK_1


starting media recovery
media recovery complete, elapsed time: 00:00:01


Finished recover at 03-JUN-14


RMAN> exit




Recovery Manager complete.
You have new mail in /var/spool/mail/oracle
[oracle@shiven instance]$ sqlplus sys/sys as sysdba


SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 3 11:05:43 2014


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




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


SQL> alter database open;


Database altered.