Please click link here to read my blog at Pythian.
March 5, 2014
March 1, 2014
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:
December 21, 2013
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?
December 13, 2013
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 $
November 28, 2013
Happy Thanksgiving
So there I was, one Thanksgiving I came across Randy Pausch’s Last Lecture and wanted to share with you.
November 24, 2013
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 selectedSYS@+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?
November 13, 2013
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.
October 31, 2013
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
October 30, 2013
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_2channel 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:03starting media recovery
media recovery complete, elapsed time: 00:00:04Finished 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 132Finished validate at 30-OCT-2013 07:23:22
RMAN>
October 27, 2013
Playing with RMAN Merged Incremental Backup
Here are 2 good documents for reference:
Merged Incremental Backup Strategies (Doc ID 745798.1)
Oracle Backup and Recovery for a VLDB
You can find my playing around here
I did find the results of switch database to copy awkward with the data file name.
LAX:(SYS@db01)> show parameter db_create NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /oracle/oradata db_create_online_log_dest_1 string /oracle/oradata db_create_online_log_dest_2 string /oracle/oradata db_create_online_log_dest_3 string db_create_online_log_dest_4 string db_create_online_log_dest_5 string LAX:(SYS@db01)> alter system set db_create_file_dest='/oracle/datafilecopy'; System altered. LAX:(SYS@db01)> create tablespace testing datafile size 16m; Tablespace created. LAX:(SYS@db01)> select name from v$datafile; NAME -------------------------------------------------------------------------------- /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSTEM_FNO-1_pbonf3p6.dbf /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-SYSAUX_FNO-2_pconf3pm.dbf /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-UNDOTBS_FNO-3_p9onf3nk.dbf /oracle/datafilecopy/data_D-DB01_I-1452485914_TS-USER_DATA_FNO-4_paonf3on.dbf /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_testing_96v0nk2p_.dbf LAX:(SYS@db01)>
Is this something that can be lived with?
