Missing Password for Database Link Bug

So there I was, working on another database duplication project, the requirement is to save the existing database links.

Sounds pretty easy, right?

SELECT OWNER, DB_LINK, DBMS_METADATA.GET_DDL('DB_LINK',DB_LINK,OWNER) as DDL FROM DBA_DB_LINKS;

Wrong and I know why I am getting bald. Pulling my hair out.

After searching for hours, I found DBMS_METADATA.GET_DDL database link password missing

Another 11.2.0.4 Bug.

I believe the bug was introduced when I modified user’s password as shown below since  everything was working fine just hours ago.

alter user SCOTT identified by values 'S:EDCCC6A91707D978B7D49476BCA228BC7D702C135557F41154ACBF744645;F894844C34402B67';

Got desperate and restored the database which did not help.

Now what and how is one suppose to save database links info?

Find out more here

Automating DataPump Export

Please click link here to read my blog at Pythian.

My Notes on How to Deploy a Four-Node Oracle RAC 12c Cluster

Great post from Oracle on How to Deploy a Four-Node Oracle RAC 12c Cluster in Minutes.

Just a few notes to for me to remember and share.

From the section:

Import the Oracle VM Templates You Downloaded

4.  From the Oracle VM Manager GUI, import the template files by providing both URLs for the same import session. The import process will take several minutes; be patient.

I used the URL below:

http://192.168.56.3/Files/OVM_OL6U4_X86_64_12101DBRAC_PVM-1of2.tbz

http://192.168.56.3/Files/OVM_OL6U4_X86_64_12101DBRAC_PVM-2of2.tbz

I was not able to resolve the error below and resulted in starting over which seems to be a lot easier:

(03/01/2014 11:21:17:639 AM) Async operation failed on server: ovm-srv.oow.com. 
Object: cfgFile_0004fb0000140000695d90a4276bae98, PID: 4592, Server error message: 
Command: (gtar xjf /OVS/Repositories/0004fb00000300008eb0bd4e4e626f9e/Templates/0004fb0000140000695d90a4276bae98/tmp/OVM_OL6U4_X86_64_12101DBRAC_PVM-2of2.tbz --no-same-owner -C 
/OVS/Repositories/0004fb00000300008eb0bd4e4e626f9e/Templates/0004fb0000140000695d90a4276bae98/tmp) failed: (2)

(03/01/2014 12:01:55:918 PM) Async operation failed on server: ovm-srv.oow.com. 
Object: cfgFile_0004fb00001400000b8fcc4468321f9b, PID: 3436, Server error message: 
Command: (gtar xjf /OVS/Repositories/0004fb00000300008eb0bd4e4e626f9e/Templates/0004fb00001400000b8fcc4468321f9b/tmp/OVM_OL6U4_X86_64_12101DBRAC_PVM-2of2.tbz --no-same-owner -C 
/OVS/Repositories/0004fb00000300008eb0bd4e4e626f9e/Templates/0004fb00001400000b8fcc4468321f9b/tmp) failed: (2)

To monitor the progress for Oracle VM Templates import

[root@ovm-srv log]# pwd
/var/log
[root@ovm-srv log]# tail -20f ovs-agent.log
[2014-03-01 13:22:50 4675] INFO (notification:47) Notification sent: {ASYNC_PROC} progress PID 4675
[2014-03-01 13:22:51 3528] INFO (notificationserver:139) Sending notification: {ASYNC_PROC} progress PID 4675
[2014-03-01 13:23:05 4675] DEBUG (base:269) async call complete: func: import_template pid: 4675 status: 0 output: {'ISO': {}, 'VirtualDisk': {'88e5f08a354d4080beb281cb26867189.img': 'Oracle12101DBRAC_x86_64-xvdb.img', 'b20f6be7783e4a30aa54242be750ad28.img': 'System.img'}}
[2014-03-01 13:23:05 4675] INFO (notification:47) Notification sent: {ASYNC_PROC} exit PID 4675
[2014-03-01 13:23:05 3528] INFO (notificationserver:139) Sending notification: {ASYNC_PROC} exit PID 4675
[2014-03-01 13:23:05 5247] DEBUG (service:74) call start: get_template_config('0004fb00000300000341ed14e9d56e82', '0004fb0000140000ed58fccc09717f56')
[2014-03-01 13:23:05 5247] DEBUG (service:76) call complete: get_template_config
[2014-03-01 13:23:06 5249] DEBUG (service:74) call start: set_template_config('0004fb00000300000341ed14e9d56e82', '0004fb0000140000ed58fccc09717f56', {'uuid': '0004fb0000140000ed58fccc09717f56', 'bootloader': '/usr/bin/pygrub', 'vif': ['bridge=xenbr0', 'bridge=xenbr1'], 'guest_os_type': 'default', 'name': '0004fb0000140000ed58fccc09717f56', 'OVM_description': 'Import URLs: [http://192.168.56.3/Files/OVM_OL6U4_X86_64_12101DBRAC_PVM-1of2.tbz, http://192.168.56.3/Files/OVM_OL6U4_X86_64_12101DBRAC_PVM-2of2.tbz]', 'vif_other_config': [], 'OVM_simple_name': 'OVM_OL6U4_X86_64_12101DBRAC_PVM-1of2.tbz', 'on_poweroff': 'destroy', 'vfb': ['type=vnc,vncunused=1,vnclisten=127.0.0.1,keymap=en-us'], 'on_crash': 'restart', 'on_reboot': 'restart', 'vcpus': 1, 'cpu_weight': 27500, 'OVM_os_type': 'None', 'memory': 2048, 'cpu_cap': 0, 'maxvcpus': 1, 'disk': ['file:/OVS/Repositories/0004fb00000300000341ed14e9d56e82/VirtualDisks/b20f6be7783e4a30aa54242be750ad28.img,xvda,w', 'file:/OVS/Repositories/0004fb00000300000341ed14e9d56e82/VirtualDisks/88e5f08a354d4080beb281cb26867189.img,xvdb,w'], 'OVM_domain_type': 'xen_pvm', 'bootargs': ''})
[2014-03-01 13:23:06 5249] DEBUG (service:76) call complete: set_template_config
[2014-03-01 13:23:06 5250] DEBUG (service:74) call start: storage_plugin_list('oracle.ocfs2.OCFS2.OCFS2Plugin', {'status': '', 'admin_user': '', 'admin_host': '', 'uuid': '0004fb00000900004515f6d9e4e54dc2', 'total_sz': 0, 'admin_passwd': '******', 'storage_desc': '', 'free_sz': 0, 'access_host': '', 'storage_type': 'FileSys', 'alloc_sz': 0, 'access_grps': [], 'used_sz': 0, 'name': '0004fb00000900004515f6d9e4e54dc2'}, {'status': '', 'uuid': '0004fb0000050000f1d01e9ab13f1d81', 'backing_device': '/dev/mapper/SATA_VBOX_HARDDISK_VB779d125b-dd512e48_', 'ss_uuid': '0004fb00000900004515f6d9e4e54dc2', 'size': '68719476736', 'free_sz': '64145588224', 'state': 2, 'access_grp_names': [], 'access_path': '/dev/mapper/SATA_VBOX_HARDDISK_VB779d125b-dd512e48_', 'name': 'fs_OVMRepo'}, {'fr_type': 'Directory', 'fs_uuid': '0004fb0000050000f1d01e9ab13f1d81', 'file_sz': 0, 'ondisk_sz': 0, 'file_path': '/OVS/Repositories/0004fb00000300000341ed14e9d56e82/VirtualDisks', 'name_pattern': 'b20f6be7783e4a30aa54242be750ad28.img'}, False)
[2014-03-01 13:23:06 5250] INFO (storageplugin:109) storage_plugin_list(oracle.ocfs2.OCFS2.OCFS2Plugin)
[2014-03-01 13:23:06 5250] DEBUG (service:76) call complete: storage_plugin_list
[2014-03-01 13:23:06 5251] DEBUG (service:74) call start: storage_plugin_list('oracle.ocfs2.OCFS2.OCFS2Plugin', {'status': '', 'admin_user': '', 'admin_host': '', 'uuid': '0004fb00000900004515f6d9e4e54dc2', 'total_sz': 0, 'admin_passwd': '******', 'storage_desc': '', 'free_sz': 0, 'access_host': '', 'storage_type': 'FileSys', 'alloc_sz': 0, 'access_grps': [], 'used_sz': 0, 'name': '0004fb00000900004515f6d9e4e54dc2'}, {'status': '', 'uuid': '0004fb0000050000f1d01e9ab13f1d81', 'backing_device': '/dev/mapper/SATA_VBOX_HARDDISK_VB779d125b-dd512e48_', 'ss_uuid': '0004fb00000900004515f6d9e4e54dc2', 'size': '68719476736', 'free_sz': '64145588224', 'state': 2, 'access_grp_names': [], 'access_path': '/dev/mapper/SATA_VBOX_HARDDISK_VB779d125b-dd512e48_', 'name': 'fs_OVMRepo'}, {'fr_type': 'Directory', 'fs_uuid': '0004fb0000050000f1d01e9ab13f1d81', 'file_sz': 0, 'ondisk_sz': 0, 'file_path': '/OVS/Repositories/0004fb00000300000341ed14e9d56e82/VirtualDisks', 'name_pattern': '88e5f08a354d4080beb281cb26867189.img'}, False)
[2014-03-01 13:23:06 5251] INFO (storageplugin:109) storage_plugin_list(oracle.ocfs2.OCFS2.OCFS2Plugin)
[2014-03-01 13:23:06 5251] DEBUG (service:76) call complete: storage_plugin_list
[2014-03-01 13:23:06 5252] DEBUG (service:74) call start: storage_plugin_listFileSystems('oracle.ocfs2.OCFS2.OCFS2Plugin', {'status': '', 'admin_user': '', 'admin_host': '', 'uuid': '0004fb00000900004515f6d9e4e54dc2', 'total_sz': 0, 'admin_passwd': '******', 'storage_desc': '', 'free_sz': 0, 'access_host': '', 'storage_type': 'FileSys', 'alloc_sz': 0, 'access_grps': [], 'used_sz': 0, 'name': '0004fb00000900004515f6d9e4e54dc2'}, True)
[2014-03-01 13:23:06 5252] INFO (storageplugin:109) storage_plugin_listFileSystems(oracle.ocfs2.OCFS2.OCFS2Plugin)
[2014-03-01 13:23:07 5252] DEBUG (service:76) call complete: storage_plugin_listFileSystems
[2014-03-01 13:23:07 5259] DEBUG (service:76) call start: discover_pool_filesystem
[2014-03-01 13:23:07 5259] DEBUG (service:76) call complete: discover_pool_filesystem

For error Unable to launch the application:

Modify Security Level to Medium

Add to the Exception Site List: https://192.168.56.3

Try again:

Is that database user really read only?

So there I was, working on a request to determine if users in the database are read only or read write. Sounds pretty simple, right?

Someone once said, “Good artists copy, great artists steal.” In my case, there was nothing to steal.

Basically, there are system, role, table, and column privileges which can be granted to role or user.

In general, a user is considered to be read only if the user only has SELECT|SESSION|QUERY REWRITE|READ|REFERENCES and certain predefined EXECUTE on stored procedure.

If a user’s schema contains tables, is this user read only or read write?

Demo here

Did I missed anything and what you would have included or excluded?

Cloning Database without ASM

So there I was, working on a project to duplicate a database from a volume copy at the storage level with the database being shutdown.

Sounds pretty simple right? Wrong! Put the database on ASM and it becomes complicated and convoluted.

Volume1 has ASM disk group in the fashion +DG1/db01/datafile, +DG1/db01/onlinelog +DG1/db01/tempfile

Volume1 will be copied to Volume2 at the storage level. What happens to all the ASM disk group?

The ASM disk group will need to be renamed using renamdg. Great! (with a little sarcasm)

So now the ASM disk groups will be +DG2/db01/datafile, +DG2/db01/onlinelog +DG2/db01/tempfile

What’s wrong with that picture?

Given I am an ASM noob, I did reach out to other and still waiting for a respond I can be confident with.

Now, if the database was not on ASM, I would be done with the project already.

1 hour to create a test case without using ASM, 8 hours too create action plan using ASM and still not completed.

Create and Edit Control File SQL

SQL> alter database backup controlfile to trace as '/tmp/cf.sql';

Parameter Copied from Source (DB01)

[oracle@arrow:db01]/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs
$ cat initdb02.ora
*.audit_file_dest='/u01/app/oracle/admin/adump'
*.audit_trail='none'
*.compatible='11.2.0.3.0'
*.db_block_size=8192
*.db_create_file_dest='/oradata'
*.control_files='/oradata/DB02/controlfile/o1_mf_9bb5brjc_.ctl','/oradata/fra/DB02/controlfile/o1_mf_9bb5brxx_.ctl'
*.db_domain=''
*.db_name='db02'
*.db_recovery_file_dest='/oradata/fra'
*.db_recovery_file_dest_size=4g
*.diagnostic_dest='/u01/app/oracle'
*.event='10795 trace name context forever, level 2'
*.fast_start_mttr_target=300
*.java_pool_size=0
*.local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1531))'
*.pga_aggregate_target=268435456
*.processes=100
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=805306368
*.undo_tablespace='UNDOTBS'
[oracle@arrow:db01]/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs

Shutdown database from Source (DB01) and perform copy

$ cd /oradata/
[oracle@arrow:db01]/oradata
$ ls *
DB01:
controlfile  datafile  onlinelog

fra:
DB01

[oracle@arrow:db01]/oradata
$ cp -rp DB01/ DB02/

[oracle@arrow:db01]/oradata
$ cd fra/
[oracle@arrow:db01]/oradata/fra
$ cp -rp DB01/ DB02/

Clone database from Source (DB01)

[oracle@arrow:db01]/oradata/fra
$ db02
The Oracle base remains unchanged with value /u01/app/oracle

IPC Resources for ORACLE_SID "db02" :
Shared Memory
ID              KEY
No shared memory segments used
Semaphores:
ID              KEY
No semaphore resources used
Oracle Instance not alive for sid "db02"
[oracle@arrow:db02]/oradata/fra
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 12 17:04:23 2013

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

Connected to an idle instance.

SQL> set echo on
SQL> @/tmp/cf.sql
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area  801701888 bytes
Fixed Size                  2232640 bytes
Variable Size             222301888 bytes
Database Buffers          570425344 bytes
Redo Buffers                6742016 bytes
SQL> CREATE CONTROLFILE REUSE SET DATABASE "DB02" RESETLOGS     ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 2
  4      MAXDATAFILES 30
  5      MAXINSTANCES 1
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 (
  9      '/oradata/DB02/onlinelog/o1_mf_1_9bb5bt5g_.log',
 10      '/oradata/fra/DB02/onlinelog/o1_mf_1_9bb5btk6_.log'
 11    ) SIZE 100M BLOCKSIZE 512,
 12    GROUP 2 (
 13      '/oradata/DB02/onlinelog/o1_mf_2_9bb5btq3_.log',
 14      '/oradata/fra/DB02/onlinelog/o1_mf_2_9bb5bv4g_.log'
 15    ) SIZE 100M BLOCKSIZE 512,
 16    GROUP 3 (
 17      '/oradata/DB02/onlinelog/o1_mf_3_9bb5bvc1_.log',
 18      '/oradata/fra/DB02/onlinelog/o1_mf_3_9bb5cny5_.log'
 19    ) SIZE 100M BLOCKSIZE 512
 20  -- STANDBY LOGFILE
 21  DATAFILE
 22    '/oradata/DB02/datafile/o1_mf_system_9bb5cx2c_.dbf',
 23    '/oradata/DB02/datafile/o1_mf_sysaux_9bb5ff55_.dbf',
 24    '/oradata/DB02/datafile/o1_mf_undotbs_9bb5gsjs_.dbf',
 25    '/oradata/DB02/datafile/o1_mf_users_9bb5j5wx_.dbf'
 26  CHARACTER SET AL32UTF8
 27  ;

Control file created.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/DB02/datafile/o1_mf_temp_9bb5j4rf_.tmp'
  2       SIZE 268435456  REUSE AUTOEXTEND ON NEXT 268435456  MAXSIZE 8193M;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

Check DBID for Cloned DB  (Same as Source)

[oracle@arrow:db01]/oradata
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 12 17:34:06 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB01 (DBID=1464916248)

RMAN>

[oracle@arrow:db02]/oradata/fra
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 12 17:05:44 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB02 (DBID=1464916248)

RMAN>

Change DBID for cloned DB

[oracle@arrow:db02]/oradata/fra
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 12 17:05:54 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  801701888 bytes
Fixed Size                  2232640 bytes
Variable Size             222301888 bytes
Database Buffers          570425344 bytes
Redo Buffers                6742016 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

[oracle@arrow:db02]/oradata/fra
$ nid target=sys dbname=DB02 logfile=/tmp/nid.log
Password:

[oracle@arrow:db02]/oradata/fra
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 12 17:08:14 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB02 (DBID=1464916248, not open)

RMAN> exit

Recovery Manager complete.

[oracle@arrow:db02]/oradata/fra
$ cat /tmp/nid.log

DBNEWID: Release 11.2.0.3.0 - Production on Thu Dec 12 17:08:06 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database DB02 (DBID=1464916248)

Connected to server version 11.2.0

Control Files in database:
    /oradata/DB02/controlfile/o1_mf_9bb5brjc_.ctl
    /oradata/fra/DB02/controlfile/o1_mf_9bb5brxx_.ctl

NID-00144: New name for database DB02 is the same as current name DB02

Change of database name and ID failed during validation - database is intact.
DBNEWID - Completed with validation errors.

Change DBID omitting DBNAME (changed from CF create)

[oracle@arrow:db02]/oradata/fra
$ nid target=sys logfile=/tmp/nid.log
Password:

[oracle@arrow:db02]/oradata/fra
$ cat /tmp/nid.log

DBNEWID: Release 11.2.0.3.0 - Production on Thu Dec 12 17:10:58 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database DB02 (DBID=1464916248)

Connected to server version 11.2.0

Control Files in database:
    /oradata/DB02/controlfile/o1_mf_9bb5brjc_.ctl
    /oradata/fra/DB02/controlfile/o1_mf_9bb5brxx_.ctl

Changing database ID from 1464916248 to 1581437733
    Control File /oradata/DB02/controlfile/o1_mf_9bb5brjc_.ctl - modified
    Control File /oradata/fra/DB02/controlfile/o1_mf_9bb5brxx_.ctl - modified
    Datafile /oradata/DB02/datafile/o1_mf_system_9bb5cx2c_.db - dbid changed
    Datafile /oradata/DB02/datafile/o1_mf_sysaux_9bb5ff55_.db - dbid changed
    Datafile /oradata/DB02/datafile/o1_mf_undotbs_9bb5gsjs_.db - dbid changed
    Datafile /oradata/DB02/datafile/o1_mf_users_9bb5j5wx_.db - dbid changed
    Datafile /oradata/DB02/datafile/o1_mf_temp_9bb5j4rf_.tm - dbid changed
    Control File /oradata/DB02/controlfile/o1_mf_9bb5brjc_.ctl - dbid changed
    Control File /oradata/fra/DB02/controlfile/o1_mf_9bb5brxx_.ctl - dbid changed
    Instance shut down

Database ID for database DB02 changed to 1581437733.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

Check DBID for Cloned DB

[oracle@arrow:db02]/oradata/fra
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 12 17:11:16 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     801701888 bytes

Fixed Size                     2232640 bytes
Variable Size                222301888 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6742016 bytes

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DB02     1581437733       CURRENT 360639     12-DEC-2013 17:04:50

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/12/2013 17:12:14
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Open database resetlogs

RMAN> alter database open resetlogs;

database opened

RMAN> exit

Recovery Manager complete.
[oracle@arrow:db02]/oradata/fra
$

Happy Thanksgiving

So there I was, one Thanksgiving I came across Randy Pausch’s Last Lecture and wanted to share with you.

Playing with ASM Online Migration

“Everything should be made as simple as possible, but not simpler.” – Albert Einstein

I am curious as to why anyone would use ASM for a standalone database as it introduce more complexity of having to install, maintain, and upgrade Grid Infrastructure.

I asked the DBAs and here are the some responses:

ASM always bypass the OS caching layer (direct IO) which can be difficult and inconsistent using traditional FS.
ASM allows move to another type of storage in the future.
ASM provides transparent balance and migration between any type of block storage.
ASM provides online operations like adding/removing/resizing disks/LUNs from one disk array to another.

I asked a former manager who knows EMC storage and Solaris and here are his responses:

1. OS Caching: this was solved a long time ago. As you know, even plain old UFS on Solaris has been able to do this for years with Oracle.
2. Moving to another type of storage. That’s a function of either the volume manager on the OS or the array itself (both are possible and depend on your setup).
Oracle isn’t bringing anything new to the table with ASM.
3. Balance and migration. Again, a function of the volume manager and/or the array. Nothing new being done by ASM.
4. Online operations and migrations. Again, a function of the volume manager and/or the array. ASM brings nothing new.

It seems to me that ASM does, really, one thing: it eliminates the OS volume manager but at the same time expands the scope of the DBA to now include storage problems. That sounds like a distraction to me.

I would love to see a live debate on this.

Still not 100% satisfied, I started to research and explore ASM online operations.

ASM disks VENDOR101,VENDOR102,VENDOR103 are mapped to /dev/sdb1,/dev/sdc1,/dev/sdd1

[root@looney ~]# ls -l /dev/sd*
brw-rw---- 1 root disk 8,  0 Nov 23 09:23 /dev/sda
brw-rw---- 1 root disk 8,  1 Nov 23 09:23 /dev/sda1
brw-rw---- 1 root disk 8,  2 Nov 23 09:23 /dev/sda2
brw-rw---- 1 root disk 8, 16 Nov 23 09:23 /dev/sdb
brw-rw---- 1 root disk 8, 17 Nov 23 09:25 /dev/sdb1
brw-rw---- 1 root disk 8, 32 Nov 23 09:23 /dev/sdc
brw-rw---- 1 root disk 8, 33 Nov 23 09:27 /dev/sdc1
brw-rw---- 1 root disk 8, 48 Nov 23 09:23 /dev/sdd
brw-rw---- 1 root disk 8, 49 Nov 23 09:27 /dev/sdd1
brw-rw---- 1 root disk 8, 64 Nov 23 09:23 /dev/sde
brw-rw---- 1 root disk 8, 65 Nov 23 09:23 /dev/sde1
brw-rw---- 1 root disk 8, 80 Nov 23 09:23 /dev/sdf
brw-rw---- 1 root disk 8, 81 Nov 23 09:23 /dev/sdf1
brw-rw---- 1 root disk 8, 96 Nov 23 09:23 /dev/sdg
brw-rw---- 1 root disk 8, 97 Nov 23 09:23 /dev/sdg1

[root@looney ~]# ls -l /dev/oracleasm/disks/
total 0
brw-rw---- 1 oracle dba 8, 17 Nov 23 09:25 VENDOR101
brw-rw---- 1 oracle dba 8, 33 Nov 23 09:27 VENDOR102
brw-rw---- 1 oracle dba 8, 49 Nov 23 09:27 VENDOR103

[root@looney ~]# /etc/init.d/oracleasm listdisks
VENDOR101
VENDOR102
VENDOR103

[root@looney ~]# /etc/init.d/oracleasm querydisk -p VENDOR101
Disk "VENDOR101" is a valid ASM disk
/dev/sdb1: LABEL="VENDOR101" TYPE="oracleasm"

[root@looney ~]# /etc/init.d/oracleasm querydisk -p VENDOR102
Disk "VENDOR102" is a valid ASM disk
/dev/sdc1: LABEL="VENDOR102" TYPE="oracleasm"

[root@looney ~]# /etc/init.d/oracleasm querydisk -p VENDOR103
Disk "VENDOR103" is a valid ASM disk
/dev/sdd1: LABEL="VENDOR103" TYPE="oracleasm"

Create ASM disks for VENDOR201,VENDOR202,VENDOR203 using /dev/sde1,/dev/sdf1,/dev/sdg1

[root@looney ~]# /etc/init.d/oracleasm createdisk 'vendor201' /dev/sde1
Marking disk "vendor201" as an ASM disk:                   [  OK  ]

[root@looney ~]# /etc/init.d/oracleasm createdisk 'vendor202' /dev/sdf1
Marking disk "vendor202" as an ASM disk:                   [  OK  ]

[root@looney ~]# /etc/init.d/oracleasm createdisk 'vendor203' /dev/sdg1
Marking disk "vendor203" as an ASM disk:                   [  OK  ]
[root@looney ~]#

Perform ASM ONLINE Migration

Query ASM disks:

SYS@+ASM> @asmdisk

   INST_ID PATH                 DISK_NAME       GROUP_NUMBER GROUP_NAME      STATE      TYPE         TOTAL_GB    FREE_GB
---------- -------------------- --------------- ------------ --------------- ---------- ---------- ---------- ----------
         1 ORCL:VENDOR101       VENDOR101                  1 DATA1           MOUNTED    EXTERN          14.98      13.27
         1 ORCL:VENDOR102       VENDOR102                  1 DATA1           MOUNTED    EXTERN          14.98      13.27
         1 ORCL:VENDOR103       VENDOR103                  1 DATA1           MOUNTED    EXTERN          14.98      13.27
         1 ORCL:VENDOR201
         1 ORCL:VENDOR202
         1 ORCL:VENDOR203

6 rows selected.

Add new disks / Drop old disks from ASM diskgroup:

ASM power limit ranges from 1-11.
In the demonstration, power limit 1 was used to be able to query progress from v$asm_operation

SYS@+ASM> alter diskgroup DATA1
add disk  'ORCL:VENDOR201','ORCL:VENDOR202','ORCL:VENDOR203'
drop disk 'VENDOR101','VENDOR102','VENDOR103'
rebalance power 1;
  2    3    4

Diskgroup altered.

SYS@+ASM> SYS@+ASM> @asmop

GROUP_NUMBER OPERA STATE           POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---------- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
           1 REBAL RUN                 1          1         58       1756         60          28

SYS@+ASM> r
  1* select * from v$asm_operation

GROUP_NUMBER OPERA STATE           POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---------- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
           1 REBAL RUN                 1          1        178       1758        677           2

SYS@+ASM> r
  1* select * from v$asm_operation

GROUP_NUMBER OPERA STATE           POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---------- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
           1 REBAL RUN                 1          1        298       1759        697           2

SYS@+ASM> r
  1* select * from v$asm_operation

no rows selected

SYS@+ASM> @asmdisk

   INST_ID PATH                 DISK_NAME       GROUP_NUMBER GROUP_NAME      STATE      TYPE         TOTAL_GB    FREE_GB
---------- -------------------- --------------- ------------ --------------- ---------- ---------- ---------- ----------
         1 ORCL:VENDOR101
         1 ORCL:VENDOR102
         1 ORCL:VENDOR103
         1 ORCL:VENDOR201       VENDOR201                  1 DATA1           MOUNTED    EXTERN          14.98      13.27
         1 ORCL:VENDOR202       VENDOR202                  1 DATA1           MOUNTED    EXTERN          14.98      13.27
         1 ORCL:VENDOR203       VENDOR203                  1 DATA1           MOUNTED    EXTERN          14.98      13.27

6 rows selected.

SYS@+ASM>

Delete ASM disks for /dev/sdb1, dev/sdd1, /dev/sdd1

[root@looney ~]# /etc/init.d/oracleasm deletedisk /dev/sdb1
Removing ASM disk "/dev/sdb1":                             [  OK  ]

[root@looney ~]# /etc/init.d/oracleasm deletedisk /dev/sdc1
Removing ASM disk "/dev/sdc1":                             [  OK  ]

[root@looney ~]# /etc/init.d/oracleasm deletedisk /dev/sdd1
Removing ASM disk "/dev/sdd1":                             [  OK  ]

[root@looney ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]

[root@looney ~]# /etc/init.d/oracleasm listdisks
VENDOR201
VENDOR202
VENDOR203

SYS@+ASM> @asmdisk

   INST_ID PATH                 DISK_NAME       GROUP_NUMBER GROUP_NAME      STATE      TYPE         TOTAL_GB    FREE_GB
---------- -------------------- --------------- ------------ --------------- ---------- ---------- ---------- ----------
         1 ORCL:VENDOR201       VENDOR201                  1 DATA1           MOUNTED    EXTERN          14.98      13.27
         1 ORCL:VENDOR202       VENDOR202                  1 DATA1           MOUNTED    EXTERN          14.98      13.27
         1 ORCL:VENDOR203       VENDOR203                  1 DATA1           MOUNTED    EXTERN          14.98      13.27

SYS@+ASM>

Would you used ASM for single instance database? Why or Why not?

5 C’s for Career Change

I have shared this with a few friends and colleagues here and there and thought I would blog about them.

Challenge – bored to death is not a good way to die.

Commute – driving 2 hours to work in traffic kills a lot of time.

Compensation – total compensation package including benefits and vacations.

Colleagues – how long can you work with people who don’t respect you?

Culture – corporate culture and team culture. Are they a fit?

If you cannot satisfied 3 out of 5, then consider a career (job) change.

What’s RMAN Backup Size

So there I was, looking for a script to calculate the backup size.  After several thoughts, it was not exactly what I was looking for.

Here’s the one I created.

SELECT TO_CHAR(completion_time, 'YYYY-MON-DD') completion_time, type, round(sum(bytes)/1048576) MB, round(sum(elapsed_seconds)/60) min
FROM 
(
SELECT 
  CASE
    WHEN s.backup_type='L' THEN 'ARCHIVELOG' 
    WHEN s.controlfile_included='YES' THEN 'CONTROLFILE'
    WHEN s.backup_type='D' AND s.incremental_level=0 THEN 'LEVEL0'
    WHEN s.backup_type='I' AND s.incremental_level=1 THEN 'LEVEL1'
  END type,
  TRUNC(s.completion_time) completion_time,
  p.bytes,
  s.elapsed_seconds
FROM v$backup_piece p, v$backup_set s
WHERE p.status='A'
AND p.recid=s.recid
UNION ALL
SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details
)
group by TO_CHAR(completion_time, 'YYYY-MON-DD'), type
order by 1 asc,2,3
;

Note that the backup size did not have any calculation for LEVEL1.

A closer look at backup by file shows that BS Key 731 is a controlfile backup and not a datafile backup.

LAX:(SYS@db01)> SELECT TO_CHAR(completion_time, 'YYYY-MON-DD') completion_time, type, round(sum(bytes)/1048576) MB, round(sum(elapsed_seconds)/60) min
FROM
  2    3  (
  4  SELECT
  5    CASE
  6      WHEN s.backup_type='L' THEN 'ARCHIVELOG'
  7      WHEN s.controlfile_included='YES' THEN 'CONTROLFILE'
  8      WHEN s.backup_type='D' AND s.incremental_level=0 THEN 'LEVEL0'
  9      WHEN s.backup_type='I' AND s.incremental_level=1 THEN 'LEVEL1'
 10    END type,
 11    TRUNC(s.completion_time) completion_time,
 12    p.bytes,
 13    s.elapsed_seconds
 14  FROM v$backup_piece p, v$backup_set s
 15  WHERE status='A'
 16  AND p.recid =s.recid
 17  UNION ALL
 18  SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details
 19  )
 20  group by TO_CHAR(completion_time, 'YYYY-MON-DD'), type
 21  order by 1 asc,2,3
 22  ;

COMPLETION_TIME      TYPE                 MB        MIN
-------------------- ------------ ---------- ----------
2013-OCT-30          ARCHIVELOG            2          0
2013-OCT-30          CONTROLFILE          36          0
2013-OCT-30          DATAFILECOPY       1696          0
2013-OCT-30          LEVEL0               63          0

LAX:(SYS@db01)>

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
722     B  0  A DISK        30-OCT-2013 20:47:32 1       1       YES        DINC0_WED
723     B  0  A DISK        30-OCT-2013 20:47:35 1       1       YES        DINC0_WED
724     B  0  A DISK        30-OCT-2013 20:47:45 1       1       YES        DINC0_WED
725     B  0  A DISK        30-OCT-2013 20:47:58 1       1       YES        DINC0_WED
726     B  0  A DISK        30-OCT-2013 20:48:01 1       1       YES        DINC0_WED
728     B  A  A DISK        30-OCT-2013 20:48:11 1       1       YES        AINC0_WED
729     B  F  A DISK        30-OCT-2013 20:48:20 1       1       NO         TAG20131030T204813
730     B  A  A DISK        30-OCT-2013 21:50:09 1       1       YES        MIB_UPDATE
731     B  1  A DISK        30-OCT-2013 21:53:46 1       1       YES        MIB_UPDATE
732     B  A  A DISK        30-OCT-2013 21:53:54 1       1       YES        MIB_UPDATE
733     B  F  A DISK        30-OCT-2013 21:54:05 1       1       NO         TAG20131030T215358

RMAN> list backup by file;

List of Datafile Backups
========================

File Key     TY LV S Ckp SCN    Ckp Time             #Pieces #Copies Compressed Tag
---- ------- -  -- - ---------- -------------------- ------- ------- ---------- ---
1    724     B  0  A 1899045    30-OCT-2013 20:47:38 1       1       YES        DINC0_WED
2    725     B  0  A 1899051    30-OCT-2013 20:47:53 1       1       YES        DINC0_WED
3    722     B  0  A 1899041    30-OCT-2013 20:47:31 1       1       YES        DINC0_WED
4    723     B  0  A 1899043    30-OCT-2013 20:47:34 1       1       YES        DINC0_WED
5    726     B  0  A 1899055    30-OCT-2013 20:48:01 1       1       YES        DINC0_WED

List of Archived Log Backups
============================

Thrd Seq     Low SCN    Low Time             BS Key  S #Pieces #Copies Compressed Tag
---- ------- ---------- -------------------- ------- - ------- ------- ---------- ---
1    13      1899034    30-OCT-2013 20:47:26 728     A 1       1       YES        AINC0_WED
1    14      1899064    30-OCT-2013 20:48:09 730     A 1       1       YES        MIB_UPDATE
1    15      1901013    30-OCT-2013 21:50:05 732     A 1       1       YES        MIB_UPDATE

List of Control File Backups
============================

CF Ckp SCN Ckp Time             BS Key  S #Pieces #Copies Compressed Tag
---------- -------------------- ------- - ------- ------- ---------- ---
1901124    30-OCT-2013 21:53:58 733     A 1       1       NO         TAG20131030T215358
1901091    30-OCT-2013 21:53:38 731     A 1       1       YES        MIB_UPDATE
1899073    30-OCT-2013 20:48:14 729     A 1       1       NO         TAG20131030T204813
List of SPFILE Backups
======================

Modification Time    BS Key  S #Pieces #Copies Compressed Tag
-------------------- ------- - ------- ------- ---------- ---
30-OCT-2013 20:01:18 733     A 1       1       NO         TAG20131030T215358
30-OCT-2013 20:01:18 729     A 1       1       NO         TAG20131030T204813

RMAN> list backup of database;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
722     Incr 0  1.37M      DISK        00:00:01     30-OCT-2013 20:47:32
        BP Key: 722   Status: AVAILABLE  Compressed: YES  Tag: DINC0_WED
        Piece Name: /oracle/backup/DB01_1452485914_20131030_9qonns33_1_1.inc0
  List of Datafiles in backup set 722
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  3    0  Incr 1899041    30-OCT-2013 20:47:31 /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-UNDOTBS_FNO-3_p9onf3nk.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
723     Incr 0  1.05M      DISK        00:00:01     30-OCT-2013 20:47:35
        BP Key: 723   Status: AVAILABLE  Compressed: YES  Tag: DINC0_WED
        Piece Name: /oracle/backup/DB01_1452485914_20131030_9ronns36_1_1.inc0
  List of Datafiles in backup set 723
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  4    0  Incr 1899043    30-OCT-2013 20:47:34 /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-USER_DATA_FNO-4_paonf3on.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
724     Incr 0  38.05M     DISK        00:00:07     30-OCT-2013 20:47:45
        BP Key: 724   Status: AVAILABLE  Compressed: YES  Tag: DINC0_WED
        Piece Name: /oracle/backup/DB01_1452485914_20131030_9sonns3a_1_1.inc0
  List of Datafiles in backup set 724
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 1899045    30-OCT-2013 20:47:38 /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSTEM_FNO-1_pbonf3p6.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
725     Incr 0  21.10M     DISK        00:00:05     30-OCT-2013 20:47:58
        BP Key: 725   Status: AVAILABLE  Compressed: YES  Tag: DINC0_WED
        Piece Name: /oracle/backup/DB01_1452485914_20131030_9tonns3p_1_1.inc0
  List of Datafiles in backup set 725
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 1899051    30-OCT-2013 20:47:53 /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSAUX_FNO-2_pconf3pm.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
726     Incr 0  1.02M      DISK        00:00:00     30-OCT-2013 20:48:01
        BP Key: 726   Status: AVAILABLE  Compressed: YES  Tag: DINC0_WED
        Piece Name: /oracle/backup/DB01_1452485914_20131030_9uonns41_1_1.inc0
  List of Datafiles in backup set 726
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  5    0  Incr 1899055    30-OCT-2013 20:48:01 /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97261fmr_.dbf

RMAN> list datafilecopy all;

List of Datafile Copies
=======================

Key     File S Completion Time      Ckp SCN    Ckp Time
------- ---- - -------------------- ---------- --------------------
133     1    A 30-OCT-2013 21:53:02 1901076    30-OCT-2013 21:52:45
        Name: /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSTEM_FNO-1_a5onnvtd.dbf
        Tag: MIB_UPDATE

134     2    A 30-OCT-2013 21:53:28 1901086    30-OCT-2013 21:53:10
        Name: /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSAUX_FNO-2_a6onnvu6.dbf
        Tag: MIB_UPDATE

131     3    A 30-OCT-2013 21:51:54 1901024    30-OCT-2013 21:50:14
        Name: /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-UNDOTBS_FNO-3_a3onnvom.dbf
        Tag: MIB_UPDATE

132     4    A 30-OCT-2013 21:52:35 1901058    30-OCT-2013 21:51:59
        Name: /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-USER_DATA_FNO-4_a4onnvrv.dbf
        Tag: MIB_UPDATE

135     5    A 30-OCT-2013 21:53:38 1901090    30-OCT-2013 21:53:36
        Name: /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-CORRUPT_FNO-5_a7onnvv0.dbf
        Tag: MIB_UPDATE

RMAN>

$ du -scm /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-*FNO-*_a*
17      /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-CORRUPT_FNO-5_a7onnvv0.dbf
201     /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSAUX_FNO-2_a6onnvu6.dbf
201     /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSTEM_FNO-1_a5onnvtd.dbf
1025    /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-UNDOTBS_FNO-3_a3onnvom.dbf
257     /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-USER_DATA_FNO-4_a4onnvrv.dbf
1697    total
[oracle@lax:db01]/home/oracle
$

Update based on Coskan comment.  This could be because my database is small, but it does not have the granularity. The timing is much better.

LAX:(SYS@db01)> set echo on
LAX:(SYS@db01)> @bk
LAX:(SYS@db01)> set pages 1000
LAX:(SYS@db01)> select TO_CHAR(end_time, 'YYYY-MON-DD') dt, input_type, round(sum(output_bytes)/1048576) MB, round(sum(elapsed_seconds)/60) min
  2  from v$rman_backup_job_details where status='COMPLETED' and end_time>trunc(sysdate)
  3  group by TO_CHAR(end_time, 'YYYY-MON-DD'), input_type
  4  order by 1, 2;

DT                   INPUT_TYPE            MB        MIN
-------------------- ------------- ---------- ----------
2013-NOV-01          DB INCR             1784          6

LAX:(SYS@db01)> SELECT TO_CHAR(completion_time, 'YYYY-MON-DD') completion_time, type, round(sum(bytes)/1048576) MB, round(sum(elapsed_seconds)/60) min
  2  FROM
  3  (
  4  SELECT
  5    CASE
  6      WHEN s.backup_type='L' THEN 'ARCHIVELOG'
  7      WHEN s.controlfile_included='YES' THEN 'CONTROLFILE'
  8      WHEN s.backup_type='D' AND s.incremental_level=0 THEN 'LEVEL0'
  9      WHEN s.backup_type='I' AND s.incremental_level=1 THEN 'LEVEL1'
 10    END type,
 11    TRUNC(s.completion_time) completion_time,
 12    p.bytes,
 13    s.elapsed_seconds
 14  FROM v$backup_piece p, v$backup_set s
 15  WHERE p.status='A'
 16  AND p.recid=s.recid
 17  UNION ALL
 18  SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details
 19  )
 20  group by TO_CHAR(completion_time, 'YYYY-MON-DD'), type
 21  order by 1 asc,2,3
 22  ;

COMPLETION_TIME      TYPE                 MB        MIN
-------------------- ------------ ---------- ----------
2013-NOV-01          ARCHIVELOG            2          0
2013-NOV-01          CONTROLFILE          36          0
2013-NOV-01          DATAFILECOPY       1696          0
2013-NOV-01          LEVEL0               63          1

LAX:(SYS@db01)> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lax:db01]/home/oracle
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Nov 1 07:23:57 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB01 (DBID=1452485914)

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
749     B  0  A DISK        01-NOV-2013 06:52:38 1       1       YES        DINC0_FRI
750     B  0  A DISK        01-NOV-2013 06:52:47 1       1       YES        DINC0_FRI
751     B  0  A DISK        01-NOV-2013 06:53:05 1       1       YES        DINC0_FRI
752     B  0  A DISK        01-NOV-2013 06:53:47 1       1       YES        DINC0_FRI
753     B  0  A DISK        01-NOV-2013 06:53:51 1       1       YES        DINC0_FRI
755     B  A  A DISK        01-NOV-2013 06:54:07 1       1       YES        AINC0_FRI
756     B  F  A DISK        01-NOV-2013 06:54:18 1       1       NO         TAG20131101T065411
757     B  A  A DISK        01-NOV-2013 07:06:49 1       1       YES        MIB_UPDATE
758     B  1  A DISK        01-NOV-2013 07:10:26 1       1       YES        MIB_UPDATE
759     B  A  A DISK        01-NOV-2013 07:10:32 1       1       YES        MIB_UPDATE
760     B  F  A DISK        01-NOV-2013 07:10:42 1       1       NO         TAG20131101T071035

RMAN> exit

Playing with Oracle Free Block Corruption

First and foremost, thank you to all the bloggers sharing their knowledge.

My previous post on corruption is here
The corrupt.sql script was stolen from here
exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(35) was stolen from here

Free Block Corruption is from a data file block which is now Free but used to be occupied by a corrupted segment.

How to Format Corrupted Block Not Part of Any Segment (Doc ID 336133.1) is a good reference.

So how did I get here?

The objective is to remove rows from v$database_block_corruption and this can be done by running exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(35).

The caveat is when the next corruption check, v$database_block_corruption is populated again.

This appears to only treat the symptoms and not the root cause.

Here’s my test case:

LAX:(SYS@db01)> create tablespace corrupt datafile size 16m;

Tablespace created.

LAX:(SYS@db01)> create index hr.x on hr.departments_old(last_update) tablespace corrupt;

Index created.

LAX:(SYS@db01)> exit

$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Oct 29 22:45:58 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB01 (DBID=1452485914)

RMAN> validate tablespace corrupt;

Starting validate at 29-OCT-2013 22:46:05
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=37 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              1917         2048            1833133
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              1
  Other      0              130

Finished validate at 29-OCT-2013 22:46:07

RMAN> exit

LAX:(SYS@db01)> @corrupt.sql

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=1051 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=1043 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=1047 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=1045 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=1034 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2308 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2274 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2258 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2292 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2362 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2244 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2390 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2283 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2228 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2304 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2234 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=2238 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=827 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=843 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=835 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=131 << EOF 
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt 
EOF 

21 rows selected. 

LAX:(SYS@db01)> exit

[oracle@lax:db01]/home/oracle
$ dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf bs=8192 conv=notrunc seek=131 << EOF 
> CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
> EOF
0+1 records in
0+1 records out
112 bytes (112 B) copied, 0.000109233 s, 1.0 MB/s


RMAN> validate tablespace corrupt;

Starting validate at 29-OCT-2013 22:46:42
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=16 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1917         2048            1833132
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      1              131

validate found one or more corrupt blocks

See trace file /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_ora_3351.trc for details
Finished validate at 29-OCT-2013 22:46:46

RMAN> exit

LAX:(SYS@db01)> @corrupt_seg.sql
LAX:(SYS@db01)> col OWNER for a20
LAX:(SYS@db01)> col FILE# for 999
LAX:(SYS@db01)> col BLOCK# for 99999
LAX:(SYS@db01)> col BLOCKS for 99999
LAX:(SYS@db01)> col s_blk# for 99999
LAX:(SYS@db01)> col e_blk# for 99999
LAX:(SYS@db01)> col CORRUPTION_CHANGE# for 999999999999
LAX:(SYS@db01)> col SEGMENT_NAME for a25
LAX:(SYS@db01)> col PARTITION_NAME for a25
LAX:(SYS@db01)> select instance_name from v$instance
  2  ;

INSTANCE_NAME
----------------
db01

LAX:(SYS@db01)> select * from v$database_block_corruption order by 1,2,3,4
  2  ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
----- ------ ------ ------------------ ---------
    5    131      1                  0 CORRUPT

LAX:(SYS@db01)>
LAX:(SYS@db01)> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2         , greatest(e.block_id, c.block#) s_blk#
  3         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) e_dblk#
  4         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5         - greatest(e.block_id, c.block#) + 1 blk_corrupt
  6        , null description
  7  FROM dba_extents e, v$database_block_corruption c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id <= c.block# + c.blocks - 1  10  AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13         , header_block s_blk#
 14         , header_block e_blk#
 15         , 1 blk_corrupt
 16         , 'Segment Header' description
 17  FROM dba_segments s, v$database_block_corruption c
 18  WHERE s.header_file = c.file#
 19  AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22         , greatest(f.block_id, c.block#) s_blk#
 23         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) e_blk#
 24         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25         - greatest(f.block_id, c.block#) + 1 blk_corrupt
 26         , 'Free Block' description
 27  FROM dba_free_space f, v$database_block_corruption c
 28  WHERE f.file_id = c.file#
 29  AND f.block_id <= c.block# + c.blocks - 1  30  AND f.block_id + f.blocks - 1 >= c.block#
 31  order by file#, s_blk#
 32  ;

OWNER                SEGMENT_TYPE       SEGMENT_NAME              PARTITION_NAME            FILE# S_BLK#    E_DBLK# BLK_CORRUPT DESCRIPTION
-------------------- ------------------ ------------------------- ------------------------- ----- ------ ---------- ----------- --------------
HR                   INDEX              X                                                       5    131        131           1

LAX:(SYS@db01)> drop index hr.x;

Index dropped.

LAX:(SYS@db01)>

RMAN> validate tablespace corrupt;

Starting validate at 29-OCT-2013 22:49:56
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=37 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1917         2048            1833264
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      1              131

validate found one or more corrupt blocks

See trace file /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_ora_3364.trc for details
Finished validate at 29-OCT-2013 22:50:00

RMAN>

LAX:(SYS@db01)> @corrupt_seg.sql
LAX:(SYS@db01)> col OWNER for a20
LAX:(SYS@db01)> col FILE# for 999
LAX:(SYS@db01)> col BLOCK# for 99999
LAX:(SYS@db01)> col BLOCKS for 99999
LAX:(SYS@db01)> col s_blk# for 99999
LAX:(SYS@db01)> col e_blk# for 99999
LAX:(SYS@db01)> col CORRUPTION_CHANGE# for 999999999999
LAX:(SYS@db01)> col SEGMENT_NAME for a25
LAX:(SYS@db01)> col PARTITION_NAME for a25
LAX:(SYS@db01)> select instance_name from v$instance
  2  ;

INSTANCE_NAME
----------------
db01

LAX:(SYS@db01)> select * from v$database_block_corruption order by 1,2,3,4
  2  ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
----- ------ ------ ------------------ ---------
    5    131      1                  0 CORRUPT

LAX:(SYS@db01)>
LAX:(SYS@db01)> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2         , greatest(e.block_id, c.block#) s_blk#
  3         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) e_dblk#
  4         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5         - greatest(e.block_id, c.block#) + 1 blk_corrupt
  6        , null description
  7  FROM dba_extents e, v$database_block_corruption c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id <= c.block# + c.blocks - 1  10  AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13         , header_block s_blk#
 14         , header_block e_blk#
 15         , 1 blk_corrupt
 16         , 'Segment Header' description
 17  FROM dba_segments s, v$database_block_corruption c
 18  WHERE s.header_file = c.file#
 19  AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22         , greatest(f.block_id, c.block#) s_blk#
 23         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) e_blk#
 24         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25         - greatest(f.block_id, c.block#) + 1 blk_corrupt
 26         , 'Free Block' description
 27  FROM dba_free_space f, v$database_block_corruption c
 28  WHERE f.file_id = c.file#
 29  AND f.block_id <= c.block# + c.blocks - 1  30  AND f.block_id + f.blocks - 1 >= c.block#
 31  order by file#, s_blk#
 32  ;

OWNER                SEGMENT_TYPE       SEGMENT_NAME              PARTITION_NAME            FILE# S_BLK#    E_DBLK# BLK_CORRUPT DESCRIPTION
-------------------- ------------------ ------------------------- ------------------------- ----- ------ ---------- ----------- --------------
                                                                                                5    131        131           1 Free Block

LAX:(SYS@db01)>
LAX:(SYS@db01)> exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(35);

PL/SQL procedure successfully completed.

LAX:(SYS@db01)> @corrupt_seg.sql
LAX:(SYS@db01)> set lines 200 pages 10000 echo on
LAX:(SYS@db01)> col OWNER for a20
LAX:(SYS@db01)> col FILE# for 999
LAX:(SYS@db01)> col BLOCK# for 99999
LAX:(SYS@db01)> col BLOCKS for 99999
LAX:(SYS@db01)> col s_blk# for 99999
LAX:(SYS@db01)> col e_blk# for 99999
LAX:(SYS@db01)> col CORRUPTION_CHANGE# for 999999999999
LAX:(SYS@db01)> col SEGMENT_NAME for a25
LAX:(SYS@db01)> col PARTITION_NAME for a25
LAX:(SYS@db01)> select instance_name from v$instance
  2  ;

INSTANCE_NAME
----------------
db01

LAX:(SYS@db01)> select * from v$database_block_corruption order by 1,2,3,4
  2  ;

no rows selected

LAX:(SYS@db01)>
LAX:(SYS@db01)> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2         , greatest(e.block_id, c.block#) s_blk#
  3         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) e_dblk#
  4         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5         - greatest(e.block_id, c.block#) + 1 blk_corrupt
  6        , null description
  7  FROM dba_extents e, v$database_block_corruption c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id <= c.block# + c.blocks - 1  10  AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13         , header_block s_blk#
 14         , header_block e_blk#
 15         , 1 blk_corrupt
 16         , 'Segment Header' description
 17  FROM dba_segments s, v$database_block_corruption c
 18  WHERE s.header_file = c.file#
 19  AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22         , greatest(f.block_id, c.block#) s_blk#
 23         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) e_blk#
 24         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25         - greatest(f.block_id, c.block#) + 1 blk_corrupt
 26         , 'Free Block' description
 27  FROM dba_free_space f, v$database_block_corruption c
 28  WHERE f.file_id = c.file#
 29  AND f.block_id <= c.block# + c.blocks - 1  30  AND f.block_id + f.blocks - 1 >= c.block#
 31  order by file#, s_blk#
 32  ;

no rows selected

LAX:(SYS@db01)>
LAX:(SYS@db01)>

RMAN> validate tablespace corrupt;

Starting validate at 29-OCT-2013 22:51:54
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=16 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1917         2048            1833264
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      1              131

validate found one or more corrupt blocks

See trace file /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_ora_3382.trc for details
Finished validate at 29-OCT-2013 22:51:58

RMAN>

LAX:(SYS@db01)> @corrupt_seg.sql
LAX:(SYS@db01)> col OWNER for a20
LAX:(SYS@db01)> col FILE# for 999
LAX:(SYS@db01)> col BLOCK# for 99999
LAX:(SYS@db01)> col BLOCKS for 99999
LAX:(SYS@db01)> col s_blk# for 99999
LAX:(SYS@db01)> col e_blk# for 99999
LAX:(SYS@db01)> col CORRUPTION_CHANGE# for 999999999999
LAX:(SYS@db01)> col SEGMENT_NAME for a25
LAX:(SYS@db01)> col PARTITION_NAME for a25
LAX:(SYS@db01)> select instance_name from v$instance
  2  ;

INSTANCE_NAME
----------------
db01

LAX:(SYS@db01)> select * from v$database_block_corruption order by 1,2,3,4
  2  ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
----- ------ ------ ------------------ ---------
    5    131      1                  0 CORRUPT

LAX:(SYS@db01)>
LAX:(SYS@db01)> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2         , greatest(e.block_id, c.block#) s_blk#
  3         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) e_dblk#
  4         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5         - greatest(e.block_id, c.block#) + 1 blk_corrupt
  6        , null description
  7  FROM dba_extents e, v$database_block_corruption c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id <= c.block# + c.blocks - 1  10  AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13         , header_block s_blk#
 14         , header_block e_blk#
 15         , 1 blk_corrupt
 16         , 'Segment Header' description
 17  FROM dba_segments s, v$database_block_corruption c
 18  WHERE s.header_file = c.file#
 19  AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22         , greatest(f.block_id, c.block#) s_blk#
 23         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) e_blk#
 24         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25         - greatest(f.block_id, c.block#) + 1 blk_corrupt
 26         , 'Free Block' description
 27  FROM dba_free_space f, v$database_block_corruption c
 28  WHERE f.file_id = c.file#
 29  AND f.block_id <= c.block# + c.blocks - 1  30  AND f.block_id + f.blocks - 1 >= c.block#
 31  order by file#, s_blk#
 32  ;

OWNER                SEGMENT_TYPE       SEGMENT_NAME              PARTITION_NAME            FILE# S_BLK#    E_DBLK# BLK_CORRUPT DESCRIPTION
-------------------- ------------------ ------------------------- ------------------------- ----- ------ ---------- ----------- --------------
                                                                                                5    131        131           1 Free Block

LAX:(SYS@db01)>
LAX:(SYS@db01)>

Update:

Team mate has suggested the following article:

How to Clean entries from V$DATABASE_BLOCK_CORRUPTION when listing file/block corruption for a Datafile/Tablespace that was already dropped (Doc ID 1484189.1)

Following the note above to perform backup check logical does not solve the issue.

RMAN> backup check logical validate tablespace corrupt;

Starting backup at 30-OCT-2013 06:55:09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=30 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1917         2048            1833264
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      1              131

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_ora_2183.trc for details
Finished backup at 30-OCT-2013 06:55:13

RMAN> backup check logical validate datafile 5;

Starting backup at 30-OCT-2013 06:55:37
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1917         2048            1833264
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97177krz_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      1              131

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_ora_2183.trc for details
Finished backup at 30-OCT-2013 06:55:40

RMAN>

LAX:(SYS@db01)> set lines 200 pages 10000 echo on
LAX:(SYS@db01)> col OWNER for a20
LAX:(SYS@db01)> col FILE# for 999
LAX:(SYS@db01)> col BLOCK# for 99999
LAX:(SYS@db01)> col BLOCKS for 99999
LAX:(SYS@db01)> col s_blk# for 99999
LAX:(SYS@db01)> col e_blk# for 99999
LAX:(SYS@db01)> col CORRUPTION_CHANGE# for 999999999999
LAX:(SYS@db01)> col SEGMENT_NAME for a25
LAX:(SYS@db01)> col PARTITION_NAME for a25
LAX:(SYS@db01)> select instance_name from v$instance
  2  ;

INSTANCE_NAME
----------------
db01

LAX:(SYS@db01)> select * from v$database_block_corruption order by 1,2,3,4
  2  ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
----- ------ ------ ------------------ ---------
    5    131      1                  0 CORRUPT

LAX:(SYS@db01)>
LAX:(SYS@db01)> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2         , greatest(e.block_id, c.block#) s_blk#
  3         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) e_dblk#
  4         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5         - greatest(e.block_id, c.block#) + 1 blk_corrupt
  6        , null description
  7  FROM dba_extents e, v$database_block_corruption c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id <= c.block# + c.blocks - 1  10  AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13         , header_block s_blk#
 14         , header_block e_blk#
 15         , 1 blk_corrupt
 16         , 'Segment Header' description
 17  FROM dba_segments s, v$database_block_corruption c
 18  WHERE s.header_file = c.file#
 19  AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22         , greatest(f.block_id, c.block#) s_blk#
 23         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) e_blk#
 24         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25         - greatest(f.block_id, c.block#) + 1 blk_corrupt
 26         , 'Free Block' description
 27  FROM dba_free_space f, v$database_block_corruption c
 28  WHERE f.file_id = c.file#
 29  AND f.block_id <= c.block# + c.blocks - 1  30  AND f.block_id + f.blocks - 1 >= c.block#
 31  order by file#, s_blk#
 32  ;

OWNER                SEGMENT_TYPE       SEGMENT_NAME              PARTITION_NAME            FILE# S_BLK#    E_DBLK# BLK_CORRUPT DESCRIPTION
-------------------- ------------------ ------------------------- ------------------------- ----- ------ ---------- ----------- --------------
                                                                                                5    131        131           1 Free Block

LAX:(SYS@db01)> drop tablespace corrupt including contents and datafiles;

Tablespace dropped.

LAX:(SYS@db01)> @corrupt_seg.sql
LAX:(SYS@db01)> set lines 200 pages 10000 echo on
LAX:(SYS@db01)> col OWNER for a20
LAX:(SYS@db01)> col FILE# for 999
LAX:(SYS@db01)> col BLOCK# for 99999
LAX:(SYS@db01)> col BLOCKS for 99999
LAX:(SYS@db01)> col s_blk# for 99999
LAX:(SYS@db01)> col e_blk# for 99999
LAX:(SYS@db01)> col CORRUPTION_CHANGE# for 999999999999
LAX:(SYS@db01)> col SEGMENT_NAME for a25
LAX:(SYS@db01)> col PARTITION_NAME for a25
LAX:(SYS@db01)> select instance_name from v$instance
  2  ;

INSTANCE_NAME
----------------
db01

LAX:(SYS@db01)> select * from v$database_block_corruption order by 1,2,3,4
  2  ;

no rows selected

LAX:(SYS@db01)>

Does anyone have or know of a short cut for this?

SOLVED:

I am getting good at corrupting data.

OWNER                SEGMENT_TYPE       SEGMENT_NAME              PARTITION_NAME            FILE# S_BLK#    E_DBLK# BLK_CORRUPT DESCRIPTION
-------------------- ------------------ ------------------------- ------------------------- ----- ------ ---------- ----------- --------------
                                                                                                5    827        827           1 Free Block
                                                                                                5    835        835           1 Free Block

LAX:(SYS@db01)>
LAX:(SYS@db01)> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lax:db01]/home/oracle
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 30 07:34:39 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB01 (DBID=1452485914)

RMAN> validate tablespace corrupt;

Starting validate at 30-OCT-2013 07:34:50
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=44 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97261fmr_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1915         2048            1859108
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97261fmr_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              1
  Other      2              132

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_ora_2608.trc for details
Finished validate at 30-OCT-2013 07:34:54

RMAN> list backup summary;

specification does not match any backup in the repository

RMAN> backup check logical tablespace corrupt;

Starting backup at 30-OCT-2013 07:35:48
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97261fmr_.dbf
channel ORA_DISK_1: starting piece 1 at 30-OCT-2013 07:35:49
channel ORA_DISK_1: finished piece 1 at 30-OCT-2013 07:35:52
piece handle=/oracle/backup/bkup_8qonmdml_1_1 tag=TAG20131030T073548 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 30-OCT-2013 07:35:52

Starting Control File and SPFILE Autobackup at 30-OCT-2013 07:35:52
piece handle=/oracle/flashrecovery/LAX_DB01/autobackup/2013_10_30/o1_mf_s_830158552_9726bdgf_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 30-OCT-2013 07:35:59

RMAN> blockrecover datafile 5 block 827,835;

Starting recover at 30-OCT-2013 07:39:09
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /oracle/backup/bkup_8qonmdml_1_1
channel ORA_DISK_1: piece handle=/oracle/backup/bkup_8qonmdml_1_1 tag=TAG20131030T073548
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

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

Finished recover at 30-OCT-2013 07:39:17

RMAN> validate tablespace corrupt;

Starting validate at 30-OCT-2013 07:39:25
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97261fmr_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              1915         2050            1859108
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_97261fmr_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              1
  Other      0              132

Finished validate at 30-OCT-2013 07:39:27

RMAN> exit

Recovery Manager complete.
[oracle@lax:db01]/home/oracle
$

New Rman Blockrecover command in 11g (Recover corruption list) (Doc ID 1390759.1)

Note: For 11g, recover corruption list can be used to recovery all corruption versus listing them one by one.

I am not sure if blockrecover corruption list would have worked on 10g as well. May be you can try it and let me know?

RMAN> recover corruption list;

Starting recover at 30-OCT-2013 07:22:56
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /oracle/backup/bkup_4gonmcsj_1_1
channel ORA_DISK_1: piece handle=/oracle/backup/bkup_4gonmcsj_1_1 tag=TAG20131030T072154
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:03

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

Finished recover at 30-OCT-2013 07:23:09

RMAN> validate tablespace corrupt;

Starting validate at 30-OCT-2013 07:23:20
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9724xr04_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
5 OK 0 1916 2050 1856947
File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9724xr04_.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 0
Index 0 0
Other 0 132

Finished validate at 30-OCT-2013 07:23:22

RMAN>