Technological recipes that I've held to prepare some solutions DBA environment.

9 Jan 2008

Corromper bloque oracle y recuperarlo


Si deseas realizar un test de recovery sobre un bloque Oracle dañado, primero debes dañarlo, valga la redundancia. Para esto sigue estos pasos de prueba:

Obs.1 : Tenga en cuenta que se debe contar con un respaldo rman, de la máquina.

#Abrimos nuestra base de datos
newton:~/Conexiones Remotas # su - oracle oracle@newton:~> sqlplus "/as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 9 09:28:44 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 406847488 bytes Fixed Size 1219688 bytes Variable Size 125830040 bytes Database Buffers 276824064 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options oracle@newton:~>

Vamos a seleccionar algún bloque para dañar:
oracle@newton:~> sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 9 11:11:39 2008

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select tablespace_name, header_file, header_block
2 from dba_segments where
3 segment_name='EMP' and owner='SCOTT';

TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
USERS 4 27

SQL> select file#, name from v$datafile where name like '%users%';

FILE#
----------
NAME
--------------------------------------------------------------------------------
4
/u02/oradata/newton/users01.dbf

Ahora bien a través del comando unix dd vamos a alterar algún bloque del datafile users01.dbf. Modificaremos el bloque a continaución del header_block o sea el bloque número 28. Con este comando dd también debemos utilizar el tamaño del bloque Oracle, en este caso estoy utilizando uno de 8 Kb.

oracle@newton:~> dd if=/dev/zero of=/u02/oradata/newton/users01.dbf bs=8192 conv=notrunc seek=28 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.0432926 s, 189 kB/s

En Este comando con la opción notrunc le decimos que no haga un truncado del archivo. Lo que hace el comando entero en realidad es leer desde /dev/zero y escribir 8 Kilobytes en el bloque 28, sólo en ese bloque ya que especificamos un count=1.

Ahora volvamos a realizar unas consultas y vean lo que sucede (limpiamos el buffer pool, para que oracle no recurra a eĺ frente a las consultas)

oracle@newton:~> sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 9 11:29:37 2008

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter system flush buffer_cache;

System altered.

SQL> alter user scott account unlock;

User altered.

SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-08103: object no longer exists

-- Oracle no ha sido capaz de identificar la tabla. Ahora observen que de todas maneras si consulto por el count de filas Oracle si me entrega un resultado. Obviamente estamos frente a un problema de corrupción.

SQL> select count(*) from emp;

COUNT(*)
----------
14


Ahora bien desde sistema operativo comprobaremos a través del utilitario de Oracle dbv que el datafile tiene un bloque corrupto:

oracle@newton:~> dbv file=/u02/oradata/newton/users01.dbf blocksize=8192

DBVERIFY: Release 10.2.0.1.0 - Production on Wed Jan 9 11:38:47 2008

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

DBVERIFY - Verification starting : FILE = /u02/oradata/newton/users01.dbf
Page 28 is marked corrupt
Corrupt block relative dba: 0x0100001c (file 4, block 28)
Completely zero block found during dbv:



DBVERIFY - Verification complete

Total Pages Examined : 640
Total Pages Processed (Data) : 42
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 63
Total Pages Failing (Index): 0
Total Pages Processed (Other): 133
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 401
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 469302 (0.469302)

Bueno Ahora que ya sabemos que estamos frente a una corupción de bloque podemos recuperar esto con RMAN de la siguiente manera, utilizando el número de bloque y de datafile (Con la base de datos arriba):

oracle@newton:/u03> rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jan 10 17:56:20 2008

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

connected to target database: NEWTON (DBID=2708614694)

RMAN> blockrecover datafile 4 block 28;

Starting blockrecover at 10-JAN-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=137 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 /u03/db_bck_full_01j5ntk4_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/u03/db_bck_full_01j5ntk4_1_1 tag=BCK_DB_FULL
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 10-JAN-08

RMAN> exit

Ahora podemos hacer la siguiente prueba.

oracle@newton:/u03> sqlplus "scott/tiger"

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 10 18:04:23 2008

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set pagesize 3000
SQL> set linesize 132
SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL>


Corrupción de bloque solucionado.
Origen de la información:

FDB
Share:

59 Comments:

Anonymous said...

buy tramadol online cheap tramadol without rx - tramadol overdose risk

Anonymous said...

xanax online xanax and alcohol detox - cost generic xanax walmart

Anonymous said...

generic xanax ambien vs xanax high - xanax drug effects

Anonymous said...

xanax online buy xanax 1mg online - xanax bars numbers

Anonymous said...

buy tramadol online tramadol hcl 50 mg street value - buy tramadol now online

Anonymous said...

best place to buy xanax online xanax generic india - buy xanax online real

Anonymous said...

generic xanax order xanax internet - xanax bars extended release

Anonymous said...

where can i buy xanax online much does generic xanax cost walmart - many 1mg xanax overdose

Anonymous said...

carisoprodol 350 mg buy carisoprodol cod - carisoprodol y naproxeno

Anonymous said...

buy tramadol online buy tramadol online with paypal - tramadol 50mg my dog

Anonymous said...

buy tramadol online tramadol online no prescription cheap - where to buy tramadol online usa

Anonymous said...

buy tramadol online buy tramadol for dogs online - cheap tramadol usa

Anonymous said...

xanax online buy xanax without prescriptions - xanax .25 effects

Anonymous said...

generic tramadol tramadol no prescription 100mg - order tramadol online mastercard

Anonymous said...

generic xanax xanax drug class pregnancy - alprazolam 0.5mg para que sirve

Anonymous said...

buy tramadol online could you overdose tramadol - tramadol dosage 50 lb dog

Anonymous said...

buy carisoprodol carisoprodol soma 350 mg - carisoprodol 350 mg generic

Anonymous said...

cialis online canadian pharmacy cialis daily - cheap generic cialis australia

Anonymous said...

cialis online once daily cialis - cialis 5mg. price in usa

Anonymous said...

cialis online cialis discount card - cialis daily use buy online

Anonymous said...

xanax online xanax 2mg yellow bar - mexican xanax online

Anonymous said...

buy cialis online cialis coupon voucher - cialis benefits reviews

Anonymous said...

buy cialis professional cialis online for sale - low price cialis

Anonymous said...

online xanax xanax generic images - xanax dosage men

Anonymous said...

cialis 10mg cheap cialis online pharmacy - where to buy cialis no prescription

Anonymous said...

buy tramadol in florida buy tramadol overnight - tramadol withdrawal symptoms

Anonymous said...

http://landvoicelearning.com/#97734 buy tramadol online echeck - tramadol hcl 50mg 627

Anonymous said...

http://buytramadolonlinecool.com/#96430 tramadol dosage men - buy tramadol hydrochloride 50mg

Anonymous said...

learn how to buy tramdadol buy tramadol online illegal - tramadol hcl 93 58

Anonymous said...

order tramadol no prescription cheap tramadol overnight delivery - tramadol hcl for dogs

Anonymous said...

buy tramadol tramadol dosage mg - tramadol extended release

Anonymous said...

http://landvoicelearning.com/#97734 tramadol 50 mg side effects - tramadol 100mg online

Anonymous said...

buy tramadol online tramadol for dogs can humans take it - buy tramadol online australia no prescription

Anonymous said...

buy tramadol tramadol hcl 50 mg maximum dosage - tramadol 10mg

Anonymous said...

http://landvoicelearning.com/#38471 buy tramadol online mastercard - tramadol hcl picture

Anonymous said...

buy tramadol online tramadol no prescription overnight shipping - buy tramadol extended release

Anonymous said...

buy tramadol tramadol addiction after 1 week - uss cheap tramadol

Anonymous said...

http://blog.dawn.com/dblog/buy/#56932 buy tramadol overnight - can you buy tramadol over counter usa

Anonymous said...

buy tramadol online ordering tramadol online legal - tramadol hcl vs norco

Anonymous said...

buy tramadol online no prescription tramadol for dogs panting - addiction potential of tramadol

Anonymous said...

http://landvoicelearning.com/#57594 klonopin and tramadol high - tramadol 750 mg

Anonymous said...

buy tramadol tramadol 50mg tablets get you high - do people buy tramadol

Anonymous said...

buy tramadol tramadol withdrawal anxiety - prednisone and tramadol for dogs

Anonymous said...

buy tramadol tramadol online no prescription usa - tramadol 50mg tablets what is it used for

Anonymous said...

buy tramadol without rx tramadol 50mg dosage dogs - tramadol hcl 100 mg tablets

Anonymous said...

ways to buy ativan online lorazepam 1mg anwendung - lorazepam 1 mg what does it look like

Anonymous said...

http://ranchodelastortugas.com/#72895 effects xanax vicodin - street price of 2 mg xanax

Anonymous said...

buy xanax online buy alprazolam - xanax drug

Anonymous said...

buy tramadol online buy tramadol online for cheap - tramadol 50 mg purchase

Anonymous said...

http://ranchodelastortugas.com/#71453 long does 2mg xanax stay urine - xanax withdrawal success stories

Anonymous said...

http://ranchodelastortugas.com/#71453 xanax withdrawal restless leg - xanax 2 mg apteka

Anonymous said...

http://staam.org/#74560 tramadol hcl extended release - buy tramadol online visa

Anonymous said...

http://staam.org/#92453 tramadol withdrawal after one week - tramadol qt

Anonymous said...

buy xanax online shooting up xanax bars - can u buy xanax online

Anonymous said...

xanax generic order xanax pills online - recreational use of xanax

Anonymous said...

hermano,mi nombre es robinson loreto,soy de Venezuela..queria consultarte como hago para recuperar o extraer la data de una base de datos oracle la cual solo tengo los archivos de dato dbf ??? intente copiando esos archivos en un servidor de prueba pero nada :-( tenemos perdido 1 mes de informacion. Mil Gracias por la ayuda..mi correo es robinsonjoseloretoruiz@gmail.com y loretoruiz@hotmail.com

xumeiqing said...

coach factory outlet
nmd adidas
ray ban sunglass,ray ban sunglasses,ray ban outlet,cheap ray bans,cheap ray ban sunglasses,cheap ray bans,ray bans
stan smith adidas
cartier
polo ralph lauren
yeezy boost 350
fitflops
instyler max
white converse
ray bans
michael kors outlet
sac longchamp pliage
calvin klein dresses
ralph lauren
michael kors outlet online
fitflops shoes
burberry outlet canada
reebok classic
pandora jewelry
adidas gazelle
supra store
omega speedmaster
nike roshe run
burberry outlet
air jordan shoes
under armour
fitflops sale clearance
coach outlet store online clearance
mlb jerseys wholesale
michael kors handbgas
nike shoes for cheap
oakley sunglasses outlet
michael kors outlet
fitflops sale clearance
reebok outlet store
adidas pure boost black
dior sunglasses 2016
michael kors outlet online
bottega veneta shoes
chenzhen20160511

dada24 Xu said...

canada goose parka
uggs outlet
michael kors outlet online
polo ralph lauren outlet
michael kors
michael kors purse
longchamp sale
air jordans
ugg australia outlet
nike outlet
zhi20161219

caiyan said...

fred perry
red bottoms
coach factory outlet
nike air max
michael kors handbags
michael kors handbags
nike air max
parada handbags
michael kors
cheap nfl jerseys
0424shizhong

Copyright © Felipe Donoso DBA TIPS | Powered by Blogger
Design by SimpleWpThemes | Blogger Theme by NewBloggerThemes.com | Free Blogger Templates