USERS TABLESPACE RECOVERY USING RMAN

REQUIREMENT: USERS TABLESPACE RECOVERY USING RMAN:
PRE-REQUISITE: RMAN BACKUP and database in ARCHIVELOG mode.

If there is a requirement to do a USERS RECOVERY test using RMAN backup.

Copy loop.sql from  /opt/oracle/scripts on ITLINUXDEVBLADE07 to $SCRPT on new machine.

cd $SCRPT

sql

SQL> create tablespace users datafile '/opt/oracle/oradata/dgtest9i/users01.dbf' size 10m;

Tablespace created.

SQL> create table test (col_1 number(10), col_2 date) tablespace users;

Table created.

SQL> @loop

PL/SQL procedure successfully completed.

SQL> select count(*) from test;

  COUNT(*)
----------
     50000

Simulate users by removing datafile belonging to USERS tablespace

cd /opt/oracle/oradata/dgtest9i
[oracle@itlinuxdevblade07 dgtest9i]$ ls -lrt users*
-rw-r-----    1 oracle   dba      10493952 May  7 10:09 users01.dbf
[oracle@itlinuxdevblade07 dgtest9i]$ rm users01.dbf
[oracle@itlinuxdevblade07 dgtest9i]$cd $SCRPT

SQL> @loop
declare
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/opt/oracle/oradata/dgtest9i/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-06512: at line 7

SQL> select count(*) from test;

  COUNT(*)
----------
     50682

SQL>

Now RECOVER the USERS tablespace.

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

SQL> startup mount;
ORACLE instance started.

Total System Global Area  236422544 bytes
Fixed Size                   742800 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 798720 bytes
Database mounted.
SQL>

create recovertbs.rcv file

run{
restore tablespace users;
recover tablespace users;
sql' alter database open';
sql 'alter tablespace users online';
}

$> rman target / catalog rman/rman@rmanp  cmdfile=recovertbs.rcv

Recovery Manager: Release 9.2.0.4.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: DGTEST9I (DBID=227642821)
connected to recovery catalog database

RMAN> run{
2> restore tablespace users;
3> recover tablespace users;
4> sql' alter database open';
5> sql 'alter tablespace users online';
6> }
7>
Starting restore at 07-MAY-06

allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=11 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.2.4.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /opt/oracle/oradata/dgtest9i/users01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/product9204/dbs/7thif4vn_1_1 tag=TAG20060507T081903 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 07-MAY-06

Starting recover at 07-MAY-06
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

starting media recovery

archive log filename=/opt/oracle/dgtest9i/arch/arch39.log thread=1 sequence=39
archive log filename=/opt/oracle/dgtest9i/arch/arch40.log thread=1 sequence=40
archive log filename=/opt/oracle/dgtest9i/arch/arch41.log thread=1 sequence=41
media recovery complete
Finished recover at 07-MAY-06

sql statement:  alter database open

sql statement: alter tablespace users online

Recovery Manager complete.
[oracle@itlinuxdevblade07 scripts]$sql

SQL> select count(*) from test;

  COUNT(*)
----------
     50682

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[oracle@itlinuxdevblade07 scripts]$ sql

SQL*Plus: Release 9.2.0.4.0 - Production on Sun May 7 10:05:02 2006


Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> @loop

PL/SQL procedure successfully completed.

SQL>