REQUIREMENT: TO DO A POINT-IN-TIME 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>

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download