http://www.youtube.com/watch?v=Ahg6qcgoay4
Did you get the right answer?
March 31, 2008
March 3, 2008
Block Corruption and blockrecover
Please do not try this unless you can afford to lose the database.
Create table in USERS tablespace.
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> create table t 2 tablespace users as 3 select * from dba_objects; Table created.
Determine block to corrupt.
SQL> select header_block 2 from dba_segments 3 where segment_name='T'; HEADER_BLOCK ------------ 139
Backup database.
JAZZ:/home/oracle/scripts$ rman target / Recovery Manager: Release 10.2.0.1.0 - Production on Sun Mar 2 13:07:30 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: JAZZ (DBID=1044608836) RMAN> backup database; Starting backup at 02-MAR-2008 13:07:35 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=33 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00001 name=/ora_data01/JAZZ/system01.dbf input datafile fno=00002 name=/ora_data02/JAZZ/undotbs01.dbf input datafile fno=00003 name=/ora_data01/JAZZ/sysaux01.dbf input datafile fno=00004 name=/ora_data04/JAZZ/users01.dbf channel ORA_DISK_1: starting piece 1 at 02-MAR-2008 13:07:37 channel ORA_DISK_1: finished piece 1 at 02-MAR-2008 13:08:02 piece handle=/oracle/10gr2/db_1/dbs/01ja8nop_1_1 tag=TAG20080302T130736 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current control file in backupset including current SPFILE in backupset channel ORA_DISK_1: starting piece 1 at 02-MAR-2008 13:08:04 channel ORA_DISK_1: finished piece 1 at 02-MAR-2008 13:08:05 piece handle=/oracle/10gr2/db_1/dbs/02ja8npi_1_1 tag=TAG20080302T130736 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 02-MAR-2008 13:08:05
Corrupt the block.
JAZZ:/home/oracle/scripts$ dd if=/dev/zero of=/ora_data04/JAZZ/users01.dbf bs=8192 conv=notrunc seek=140 count=1 1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied, 9.7241e-05 seconds, 84.2 MB/s
RMAN tablespace backup reveals block corruption.
JAZZ:/home/oracle/scripts$ rman target / Recovery Manager: Release 10.2.0.1.0 - Production on Sun Mar 2 13:08:54 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: JAZZ (DBID=1044608836) RMAN> backup tablespace users; Starting backup at 02-MAR-2008 13:09:03 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=34 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00004 name=/ora_data04/JAZZ/users01.dbf channel ORA_DISK_1: starting piece 1 at 02-MAR-2008 13:09:05 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/02/2008 13:09:06 ORA-19566: exceeded limit of 0 corrupt blocks for file /ora_data04/JAZZ/users01.dbf
Corruption found in alert log but not v$backup_corruption.
JAZZ:/home/oracle/scripts$ tail -10 /ora_admin/JAZZ/bdump/alert_JAZZ.log
Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data
Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data
Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data
Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data
Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data
Sun Mar 2 13:09:46 2008
Shutting down archive processes
Sun Mar 2 13:09:51 2008
ARCH shutting down
ARC2: Archival stopped
SQL> set serverout on
SQL> exec print_table('select * from v$backup_corruption');
PL/SQL procedure successfully completed.
But what if RMAN is not used for backup?
It is still possible to check for block corruption by using
backup validate check logical tablespace users;
SQL> set serverout on
SQL> exec print_table('select * from v$backup_corruption');
.RECID : 1
.STAMP : 648306720
.SET_STAMP : 648306720
.SET_COUNT : 4
.PIECE# : 1
.FILE# : 4
.BLOCK# : 140
.BLOCKS : 1
.CORRUPTION_CHANGE# : 0
.MARKED_CORRUPT : YES
.CORRUPTION_TYPE : ALL ZERO
-----------------
PL/SQL procedure successfully completed.
Perform blockrecover and backup tablespace.
RMAN> blockrecover datafile 4 block 140; Starting blockrecover at 02-MAR-2008 14:26:03 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=35 devtype=DISK channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00004 channel ORA_DISK_1: reading from backup piece /oracle/10gr2/db_1/dbs/05ja8o45_1_1 channel ORA_DISK_1: restored block(s) from backup piece 1 piece handle=/oracle/10gr2/db_1/dbs/05ja8o45_1_1 tag=TAG20080302T131341 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01 starting media recovery media recovery complete, elapsed time: 00:00:03 Finished blockrecover at 02-MAR-2008 14:26:08 RMAN> backup tablespace users; Starting backup at 02-MAR-2008 14:26:15 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00004 name=/ora_data04/JAZZ/users01.dbf channel ORA_DISK_1: starting piece 1 at 02-MAR-2008 14:26:15 channel ORA_DISK_1: finished piece 1 at 02-MAR-2008 14:26:16 piece handle=/oracle/10gr2/db_1/dbs/0aja8sc7_1_1 tag=TAG20080302T142615 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 02-MAR-2008 14:26:16
DEFAULT_PERMANENT_TABLESPACE
Prior to 10g, a created users would have SYSTEM tablespace as default.
In 10g, the database can have a specified default tablespace for created users.
The information is stored in DATABASE_PROPERTIES view.
SQL> select PROPERTY_VALUE 2 from DATABASE_PROPERTIES 3 where PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE'; PROPERTY_VALUE -------------------------------------------------------------------------------- USERS
Thinking Out Loud 