Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

RECOVER DATAFILE USING RMAN EXAMPLE datafile removed from disk by rm command



SQL> select file_name from dba_data_Files;

FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf

SQL> desc test;
ERROR:
ORA-04043: object test does not exist

SQL> create table test (col_1 number(10)) tablespace users;

Table created.

Now remove users01.dbf datafile with rm command to simulate a disk crash.

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 arch]$ cd /opt/oracle/oradata/dgtest9i
[oracle@itlinuxdevblade07 dgtest9i]$ rm users01.dbf
[oracle@itlinuxdevblade07 dgtest9i]$sql

SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/opt/oracle/oradata/dgtest9i/users01.dbf'
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 dgtest9i]$SQL> shutdown abort;
ORACLE instance shut down.
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> 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 dgtest9i]$ rman target / catalog rman/rman@rmanp

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> SQL 'ALTER DATABASE DATAFILE 3 offline';
3> restore datafile 3;
4>  recover datafile 3;
5> SQL 'ALTER DATABASE DATAFILE 3 online';
6> }

sql statement: ALTER DATABASE DATAFILE 3 offline

Starting restore at 21-APR-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=12 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/1mhh3cms_1_1 tag=TAG20060421T180059 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 21-APR-06

Starting recover at 21-APR-06
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 6 is already on disk as file /opt/oracle/dgtest9i/arch/arch6.log
archive log thread 1 sequence 7 is already on disk as file /opt/oracle/dgtest9i/arch/arch7.log
archive log thread 1 sequence 8 is already on disk as file /opt/oracle/dgtest9i/arch/arch8.log
archive log thread 1 sequence 9 is already on disk as file /opt/oracle/dgtest9i/arch/arch9.log
archive log thread 1 sequence 10 is already on disk as file /opt/oracle/dgtest9i/arch/arch10.log
archive log thread 1 sequence 11 is already on disk as file /opt/oracle/dgtest9i/arch/arch11.log
archive log thread 1 sequence 12 is already on disk as file /opt/oracle/dgtest9i/arch/arch12.log
archive log thread 1 sequence 13 is already on disk as file /opt/oracle/dgtest9i/arch/arch13.log
archive log thread 1 sequence 14 is already on disk as file /opt/oracle/dgtest9i/arch/arch14.log
archive log thread 1 sequence 15 is already on disk as file /opt/oracle/dgtest9i/arch/arch15.log
archive log thread 1 sequence 16 is already on disk as file /opt/oracle/dgtest9i/arch/arch16.log
archive log thread 1 sequence 17 is already on disk as file /opt/oracle/dgtest9i/arch/arch17.log
archive log thread 1 sequence 18 is already on disk as file /opt/oracle/dgtest9i/arch/arch18.log
archive log thread 1 sequence 19 is already on disk as file /opt/oracle/dgtest9i/arch/arch19.log
archive log thread 1 sequence 20 is already on disk as file /opt/oracle/dgtest9i/arch/arch20.log
archive log thread 1 sequence 21 is already on disk as file /opt/oracle/dgtest9i/arch/arch21.log
archive log thread 1 sequence 22 is already on disk as file /opt/oracle/dgtest9i/arch/arch22.log
archive log thread 1 sequence 23 is already on disk as file /opt/oracle/dgtest9i/arch/arch23.log
archive log thread 1 sequence 24 is already on disk as file /opt/oracle/dgtest9i/arch/arch24.log
archive log filename=/opt/oracle/dgtest9i/arch/arch6.log thread=1 sequence=6
archive log filename=/opt/oracle/dgtest9i/arch/arch7.log thread=1 sequence=7
archive log filename=/opt/oracle/dgtest9i/arch/arch8.log thread=1 sequence=8
archive log filename=/opt/oracle/dgtest9i/arch/arch9.log thread=1 sequence=9
archive log filename=/opt/oracle/dgtest9i/arch/arch10.log thread=1 sequence=10
archive log filename=/opt/oracle/dgtest9i/arch/arch11.log thread=1 sequence=11
archive log filename=/opt/oracle/dgtest9i/arch/arch12.log thread=1 sequence=12
archive log filename=/opt/oracle/dgtest9i/arch/arch13.log thread=1 sequence=13
archive log filename=/opt/oracle/dgtest9i/arch/arch14.log thread=1 sequence=14
archive log filename=/opt/oracle/dgtest9i/arch/arch15.log thread=1 sequence=15
archive log filename=/opt/oracle/dgtest9i/arch/arch16.log thread=1 sequence=16
archive log filename=/opt/oracle/dgtest9i/arch/arch17.log thread=1 sequence=17
archive log filename=/opt/oracle/dgtest9i/arch/arch18.log thread=1 sequence=18
archive log filename=/opt/oracle/dgtest9i/arch/arch19.log thread=1 sequence=19
archive log filename=/opt/oracle/dgtest9i/arch/arch20.log thread=1 sequence=20
archive log filename=/opt/oracle/dgtest9i/arch/arch21.log thread=1 sequence=21
archive log filename=/opt/oracle/dgtest9i/arch/arch22.log thread=1 sequence=22
media recovery complete
Finished recover at 21-APR-06

sql statement:  ALTER DATABASE DATAFILE 3 online

RMAN>

[oracle@itlinuxdevblade07 dgtest9i]$ sql

SQL> alter database open;

Database altered.

SQL> select * from test;

no rows selected

SQL>


Comments

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

What's is the difference between streaming replication Vs hot standby vs warm standby ?

Script to kill ALL IDLE Connection In postgreSQL