Tablespace recovery

 

Contributor: Arjun Raja

 

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>