Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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>







Comments

Popular posts from this blog

PostgreSQL pgBadger

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

Migrating From Oracle to PostgreSQL using ora2pg open source tools

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to configure Replication Manager (repmgr) ?