How to corrupt a datafile manually ASM scenario - oracle 11gR2





RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DB11G1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     +DATA/db11g1/datafile/system.256.997039649
2    550      SYSAUX               ***     +DATA/db11g1/datafile/sysaux.257.997039651
3    65       UNDOTBS1             ***     +DATA/db11g1/datafile/undotbs1.258.997039651
4    5        USERS                ***     +DATA/db11g1/datafile/users.259.997039651
5    345      EXAMPLE              ***     +DATA/db11g1/datafile/example.265.997039919
6    5        MAC                  ***     +DATA/db11g1/datafile/mac.270.1001447963
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/db11g1/tempfile/temp.264.997039897

RMAN> BACKUP AS COPY DATAFILE 6 FORMAT '/var/tmp/mac.270.1001447963';
Starting backup at 28-FEB-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/db11g1/datafile/mac.270.1001447963
output file name=/var/tmp/mac.270.1001447963 tag=TAG20190228T200613 RECID=6 STAMP=1001448374
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 28-FEB-19
[oracle@db11g ~]$ cd /var/tmp
[oracle@db11g tmp]$ ls -lrth
total 166M
-rw-r--r-- 1 root   root      253 Feb  5 14:37 ifcfg-eth0-old
-rw-r----- 1 grid   oinstall 152M Feb 19 21:03 annnf0_TAG20190219T202821_0.289.1000672105
-rw-r----- 1 grid   oinstall  80K Feb 19 21:06 annnf0_TAG20190219T203103_0.293.1000672263
-rw-r----- 1 grid   oinstall 9.4M Feb 19 21:06 ncsnf0_TAG20190219T202850_0.291.1000672259
-rw-r--r-- 1 oracle asmadmin 1.1K Feb 20 20:36 initdb11g20190312.ora
-rw-r--r-- 1 oracle asmadmin 1.4K Feb 23 14:48 initdb11g120190315.ora
-rw-r--r-- 1 oracle asmadmin 1.5K Feb 23 14:57 initdb11g120190315_after.ora
-rw-r----- 1 oracle asmadmin 5.1M Feb 28 20:06 mac.270.1001447963
[oracle@db11g tmp]$
[oracle@db11g tmp]$
[oracle@db11g tmp]$
[oracle@db11g tmp]$
[oracle@db11g tmp]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Feb 28 20:07:32 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DB11G1 (DBID=1931916594)
RMAN> backup tablespace MAC;
Starting backup at 28-FEB-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/db11g1/datafile/mac.270.1001447963
channel ORA_DISK_1: starting piece 1 at 28-FEB-19
channel ORA_DISK_1: finished piece 1 at 28-FEB-19
piece handle=+FRA/db11g1/backupset/2019_02_28/nnndf0_tag20190228t200741_0.298.1001448461 tag=TAG20190228T200741 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 28-FEB-19
RMAN> exit

Recovery Manager complete.
[oracle@db11g tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 28 20:08:01 2019
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select object_name,object_type from dba_segments where owner='MAC';
select object_name,object_type from dba_segments where owner='MAC'
                   *
ERROR at line 1:
ORA-00904: "OBJECT_TYPE": invalid identifier

SQL> select object_name,object_type from dba_objects where owner='MAC';
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
MAC_SAMPLE
TABLE

SQL>
SQL>
SQL>
SQL>
SQL> SELECT header_block FROM dba_segments WHERE segment_name='MAC_SAMPLE';
HEADER_BLOCK
------------
         130
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@db11g tmp]$ dd of=/var/tmp/mac.270.1001447963 bs=8192 conv=notrunc seek=131 << EOF
>
> testing corruption
> EOF
0+1 records in
0+1 records out
20 bytes (20 B) copied, 0.000546703 s, 36.6 kB/s
[oracle@db11g tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 28 20:10:42 2019
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL>
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
SQL> conn MAC/oracle
Connected.
SQL> select * from MAC_SAMPLE;
        ID
----------
       123
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@db11g tmp]$ sqlplus /  as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 28 20:12:23 2019
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/db11g1/datafile/users.259.997039651
+DATA/db11g1/datafile/undotbs1.258.997039651
+DATA/db11g1/datafile/sysaux.257.997039651
+DATA/db11g1/datafile/system.256.997039649
+DATA/db11g1/datafile/example.265.997039919
+DATA/db11g1/datafile/mac.270.1001447963
6 rows selected.
SQL> alter database datafile '+DATA/db11g1/datafile/mac.270.1001447963' offline;
Database altered.
SQL>
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

Recovery Manager complete.
[oracle@db11g tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 28 20:14:30 2019
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select TAG,NAME from v$datafile_copy;
TAG
--------------------------------
NAME
--------------------------------------------------------------------------------

TAG20190205T155647
/tmp/db11g_stby.ctl
TAG20190219T203159
/tmp/db11f1_stby.ctl

TAG
--------------------------------
NAME
--------------------------------------------------------------------------------
TAG20190220T205952

TAG20190223T153242
/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_DB11G1.f
TAG20190228T200613
/var/tmp/mac.270.1001447963

6 rows selected.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@db11g tmp]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Feb 28 20:15:09 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DB11G1 (DBID=1931916594)
RMAN> set maxcorrupt for datafile 6 to 1;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03031: this option of set command needs to be used inside a run block
RMAN> RUN
{
SET MAXCORRUPT FOR DATAFILE 6 TO 1;
RESTORE DATAFILE 6 FROM TAG ‘TAG20190228T200613’;
RECOVER DATAFILE 6;
}2> 3> 4> 5> 6>
executing command: SET MAX CORRUPT
using target database control file instead of recovery catalog
Starting restore at 28-FEB-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/28/2019 20:17:03
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 6 found to restore
RMAN> RUN
{
SET MAXCORRUPT FOR DATAFILE 6 TO 1;
RESTORE DATAFILE 6 FROM TAG 'TAG20190228T200613';
RECOVER DATAFILE 6;
}2> 3> 4> 5> 6>
executing command: SET MAX CORRUPT
Starting restore at 28-FEB-19
using channel ORA_DISK_1
channel ORA_DISK_1: restoring datafile 00006
input datafile copy RECID=6 STAMP=1001448374 file name=/var/tmp/mac.270.1001447963
destination for restore of datafile 00006: +DATA/db11g1/datafile/mac.270.1001447963
channel ORA_DISK_1: copied datafile copy of datafile 00006
output file name=+DATA/db11g1/datafile/mac.270.1001447963 RECID=0 STAMP=0
Finished restore at 28-FEB-19
Starting recover at 28-FEB-19
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 28-FEB-19
RMAN> exit

Recovery Manager complete.

[oracle@db11g tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 28 20:17:59 2019
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> alter database datafile 6 online;
Database altered.
SQL>
SQL>
SQL> select * from v$database_block_corruption;
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         6        131          1                  0 CORRUPT
SQL>
SQL>
SQL>
SQL> conn MAC/oracle
Connected.
SQL> select * from MAC_SAMPLE;
select * from MAC_SAMPLE
              *
ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 6, block # 131)
ORA-01110: data file 6: '+DATA/db11g1/datafile/mac.270.1001447963'

Comments

Post a Comment

Popular posts from this blog

Repair Corrupt Blocks using RMAN Data Recovery Advisor Scenario - Oracle 11gR2

SQL> show parameter db_n NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_name                              string      DB11G1 SQL> select * from v$database_block_corruption;      FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ ---------          6        131          1                  0 CORRUPT Sample corrupted block I created manually SQL> select * from MAC.MAC_SAMPLE; select * from MAC.MAC_SAMPLE                   * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 6, block # 131) ORA-01110: data file 6: '+DATA/db11g1/datafile/mac.270.1001447963' SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [oracle@db11g ~]$ rman target / Reco
Read more

Recover Manually Dropped Datafile in ASM Scenario - Oracle 11gR2

Create a test tablespace and user eg:-MAC SQL> show parameter db_n NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_name                              string      DB11G1 SQL> show parameter create NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ create_bitmap_area_size              integer     8388608 create_stored_outlines               string db_create_file_dest                  string      +DATA db_create_online_log_dest_1          string db_create_online_log_dest_2          string db_create_online_log_dest_3          string db_create_online_log_dest_4          string db_create_online_log_dest_5          string SQL> create tablespace MAC datafile '+DATA' size 5M; Tablespace created. SQL> create user MAC identified by oracle default tablespace MAC quota unlimited on MAC; User
Read more