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>