Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Restore Tablespace

Posted by Hendry chinnapparaj on August 3, 2010

RESTORE TESTING FOR READ ONLY TABLESPACE
——————————————————————-

oracle@uatu009 backup>./oracle_main.ksh fixtest DDBOBK01
ORACLE_HOME = [/u01/oracle] ? /u01/oracle/product/10.2/db_1
RMAN> 2> 3> 4> 5> 6> 7>

Shutdown immediate

oracle@uatu009 fixtest>mv CB004_01.DBF CB004_01.DBF.ORI
oracle@uatu009 fixtest>mv CB004_02.DBF CB004_02.DBF.ORI

QL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  2085392 bytes
Variable Size             176164336 bytes
Database Buffers          352321536 bytes
Redo Buffers                6299648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 29 – see DBWR trace file
ORA-01110: data file 29: ‘/u02/oradata/fixtest/CB004_02.DBF’

SQL> show parameter db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      fixtest

Recovery Manager: Release 10.2.0.4.0 – Production on Tue Aug 3 12:24:36 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: fixtest (DBID=258344671, not open)
connected to recovery catalog database

RMAN> restore tablespace CB004;

Starting restore at 03-08-2010 12:26:17
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=539 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=538 devtype=DISK

channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00029 to /u02/oradata/fixtest/CB004_02.DBF
channel ORA_SBT_TAPE_1: reading from backup piece 1clkdlo6_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=1clkdlo6_1_1 tag=TAG20100803T111327
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:08
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00030 to /u02/oradata/fixtest/CB004_01.DBF
channel ORA_SBT_TAPE_1: reading from backup piece 1glkdlrc_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=1glkdlrc_1_1 tag=TAG20100803T111327
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:08
Finished restore at 03-08-2010 12:26:49

-rw-r—–    1 oracle   dba         8396800 Aug  3 12:26 CB004_02.DBF
-rw-r—–    1 oracle   dba         8396800 Aug  3 12:26 CB004_01.DBF

SQL> show parameter db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      fixtest
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 29 needs media recovery
ORA-01110: data file 29: ‘/u02/oradata/fixtest/CB004_02.DBF’

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
——— ———-
fixtest   READ WRITE

RESTORE TESTING FOR READ WRITE TABLESPACE

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

oracle@uatu009 fixtest>pwd
/u02/oradata/fixtest
oracle@uatu009 fixtest>ls -lrt users01.dbf
-rw-r—–    1 oracle   dba         5251072 Aug  3 12:32 users01.dbf

oracle@uatu009 fixtest>mv users01.dbf users01.dbf.ori

SQL> startup
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  2085392 bytes
Variable Size             176164336 bytes
Database Buffers          352321536 bytes
Redo Buffers                6299648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/u02/oradata/fixtest/users01.dbf’

Recovery Manager: Release 10.2.0.4.0 – Production on Tue Aug 3 12:37:16 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: fixtest (DBID=258344671, not open)
connected to recovery catalog database

RMAN> restore tablespace users;

Starting restore at 03-08-2010 12:37:32
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=539 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=538 devtype=DISK

channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u02/oradata/fixtest/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 0tlkdlbh_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=0tlkdlbh_1_1 tag=TAG20100803T111327
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:07
Finished restore at 03-08-2010 12:37:51

RMAN> recover tablespace users;

Starting recover at 03-08-2010 12:38:29
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 03-08-2010 12:38:32

SQL> alter database open;

Database altered.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: