for Oracle Database Administrators
Performing Block Recovery Without RMAN Backups
SQL> select segment_name, segment_type from dba_segments where tablespace_name='USERS';
SEGMENT_NAME SEGMENT_TYPE
---------------------------------------- ------------------
DEPT TABLE
EMP TABLE
SALGRADE TABLE
EMP2 TABLE
TAB1 TABLE
TAB1 TABLE
SPM_TEST_TAB TABLE
PRUEBAOUT TABLE
PK_DEPT INDEX
PK_EMP INDEX
TAB1_PK INDEX
TAB1_PK INDEX
SPM_TEST_TAB_IDX INDEX
ID_P INDEX
14 rows selected.
SQL> SELECT * FROM PRUEBAOUT;
N
----------
9
SET LINE 300
COL SEGMENT_NAME FORMAT A20
COL NAME FORMAT A60
SQL> select segment_name, a.tablespace_name, b.name
from dba_segments a, v$datafile b
where a.header_file=b.file# and a.segment_name='PRUEBAOUT'; 2 3
SEGMENT_NAME TABLESPACE_NAME NAME
-------------------- ------------------------------ ------------------------------------------------------------
PRUEBAOUT USERS /u01/app/oracle/oradata/rman/users01.dbf
SQL> ALTER TABLESPACE USERS BEGIN BACKUP;
Tablespace altered.
SQL> col name format a25
SELECT b.file#, t.name, b.status
FROM v$backup b, v$tablespace t
where b.file#= t.TS#
order by 3;SQL> 2 3 4
FILE# NAME STATUS
---------- ------------------------- ------------------
4 USERS ACTIVE
2 UNDOTBS1 NOT ACTIVE
5 RMAN NOT ACTIVE
1 SYSAUX NOT ACTIVE
3 TEMP NOT ACTIVE
oracle@ol7-122 ~]$ ls -la /u01/app/oracle/oradata/rman/users01.dbf
-rw-r-----. 1 oracle oinstall 5251072 Nov 9 16:35 /u01/app/oracle/oradata/rman/users01.dbf
[oracle@ol7-122 ~]$ cp -P /u01/app/oracle/oradata/rman/users01.dbf /u01/app/oracle/oradata/rman/users01.dbf.2
[oracle@ol7-122 ~]$ ls -la /u01/app/oracle/oradata/rman/users01.dbf*
-rw-r-----. 1 oracle oinstall 5251072 Nov 9 16:35 /u01/app/oracle/oradata/rman/users01.dbf
-rw-r-----. 1 oracle oinstall 5251072 Nov 9 16:37 /u01/app/oracle/oradata/rman/users01.dbf.2
SQL> ALTER TABLESPACE USERS end BACKUP;
Tablespace altered.
SQL> col name format a25
SELECT b.file#, t.name, b.status
FROM v$backup b, v$tablespace t
where b.file#= t.TS#
order by 3;SQL> 2 3 4
FILE# NAME STATUS
---------- ------------------------- ------------------
1 SYSAUX NOT ACTIVE
2 UNDOTBS1 NOT ACTIVE
5 RMAN NOT ACTIVE
4 USERS NOT ACTIVE
3 TEMP NOT ACTIVE
SQL> select header_block from dba_segments
where segment_name='PRUEBAOUT'; 2
HEADER_BLOCK
------------
290
[oracle@localhost admin]$ dd
of=/u01/app/oracle/oradata/rman/users01.dbf
bs=8192 conv=notrunc seek=291 <<EOF
> corruption
> EOF
0+1 records in
0+1 records out
RMAN> blockrecover datafile 4 block 60;
RMAN-00571: ===========================================================
RMAN-00569: =============== error message stack follows ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 03/09/2010 03:36:13
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN> catalog datafilecopy
'/u01/oracle/product/10.2.0/db_1/oradata/newdb/users01_backup.dbf';
RMAN> blockrecover datafile 4 block 60;
<......output trimmed ......>
<......output trimmed ......>
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished blockrecover at 09-MAR-10
RMAN> exit
