Shulou(Shulou.com)06/01 Report--

1. Analog table, and table space

SQL > create tablespace test datafile'/ u01ActionPlacement oradataPlus DBdbUniverse test01.dbf' size 30m

Tablespace created.

SQL > create table bbed tablespace test as select * from dba_tables

Table created.

SQL > select file_id,tablespace_name,file_name,status from dba_data_files where tablespace_name='TEST'

FILE_ID TABLESPACE_NAME FILE_NAME STATUS

8 TEST / u01/app/oracle/oradata/DBdb/test01.dbf AVAILABLE

SQL > select count (*) from bbed

COUNT (*)

-

2877

SQL > col segment for A10

SQL > select segment_name,file_id,block_id from dba_extents where segment_name='BBED'

SEGMENT_NAME FILE_ID BLOCK_ID

BBED 8 128

BBED 8 136

BBED 8 144

BBED 8 152

BBED 8 160

BBED 8 168

BBED 8 176

BBED 8 184

BBED 8 192

BBED 8 200

BBED 8 208

BBED 8 216

BBED 8 224

BBED 8 232

14 rows selected.

SQL >

two。 Use bbed to generate bad blocks:

2.1 generate a filelist file:

SQL > select file# | |''| | name | |''| | bytes from v$datafile

FILE# | |''| | NAME | |''| | BYTES

-

1/ u01/app/oracle/oradata/DBdb/system01.dbf 2936012800

2 / u01/app/oracle/oradata/DBdb/sysaux01.dbf 723517440

3 / u01/app/oracle/oradata/DBdb/undotbs01.dbf 2710568960

4 / u01/app/oracle/oradata/DBdb/users01.dbf 3207331840

5 / u01/app/oracle/oradata/DBdb/example01.dbf 355205120

6 / u01/app/oracle/oradata/DBdb/tbs.dbf 12582912

7 / u01/app/oracle/oradata/DBdb/tbs_tmp.dbf 12582912

8 / u01/app/oracle/oradata/DBdb/test01.dbf 31457280

8 rows selected.

2.2 paste the above required contents into the filelist record file, as follows

[oracle@wang ~] $cat filelist.txt

6 / u01/app/oracle/oradata/DBdb/tbs.dbf 12582912

7 / u01/app/oracle/oradata/DBdb/tbs_tmp.dbf 12582912

8 / u01/app/oracle/oradata/DBdb/test01.dbf 31457280

2.3 Edit the parfile file for bbed as follows:

[oracle@wang ~] $cat bbed.par

Blocksize=8192

Listfile=/home/oracle/filelist.txt

Mode=edit

[oracle@wang ~] $

[oracle@wang ~] $

2.4 use parfile files for bbed interface:

[oracle@wang lib] $bbed parfile=/home/oracle/bbed.par

Password:

BBED: Release 2.0.0.0.0-Limited Production on Thu Feb 1 12:58:16 2018

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

*! For Oracle Internal Use only!! *

BBED > show

FILE# 6

BLOCK# 1

OFFSET 0

DBA 0x01800001 (25165825 6)

FILENAME / u01/app/oracle/oradata/DBdb/tbs.dbf

BIFILE bifile.bbd

LISTFILE / home/oracle/filelist.txt

BLOCKSIZE 8192

MODE Edit

EDIT Unrecoverable

IBASE Dec

OBASE Dec

WIDTH 80

COUNT 512

LOGFILE log.bbd

SPOOL No

-- switch to file 8:

BBED > set file 8

FILE# 8

BBED > show

FILE# 8

BLOCK# 1

OFFSET 0

DBA 0x02000001 (33554433 8. 1)

FILENAME / u01/app/oracle/oradata/DBdb/test01.dbf

BIFILE bifile.bbd

LISTFILE / home/oracle/filelist.txt

BLOCKSIZE 8192

MODE Edit

EDIT Unrecoverable

IBASE Dec

OBASE Dec

WIDTH 80

COUNT 512

LOGFILE log.bbd

SPOOL No

BBED >

2.5 rollback misoperation: if misoperation occurs during the operation, you can use the revert command to roll back

BBED > help modify

Modem [/ x | d | u | o | c] numeric/character string

[DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | * symbol]

BBED > modify / x 0x02000001

File: / u01/app/oracle/oradata/DBdb/test01.dbf (8)

Block: 1 Offsets: 0 to 511 Dba:0x02000001

02000001 01000002 00000000 00000104 bc1d0000 00000000 0004200b 440badc3

44424442 00000000 142e0000 000f0000 00200000 08000300 00000000 00000000

00000000 00000000 00000000

00000000 5a683a00 00000000 1e77a239 c7912c38 06200e00 00000000 00000000

00000000 00000000 00000400 02000000 00000000 01000000 00000000 00000000

00000000 00000000 00000000

00000000 00000000 00000000

00000000 00000000 00000000

00000000 00000000 00000000

00000000 00000000 00000000

00000000 00000000 00000000 09000000 04005445 53540000 00000000 00000000

00000000 00000000 00000000 08000000 00000000 00000000 00000000

00000000 00000000 00000000

7ac92131 01000000 00000000 00000000 00000000

00000000 00000000 00000000

00000000 5b683a00 00000000 1f77a239 01000000 50030000 91150000 10000000

BBED > revert

All changes made in this session will be rolled back. Proceed? (Ybig N) y

Reverted file'/ u01 block block

Warning: contents of previous BIFILE will be lost. Proceed? (Ybig N) y

2.6 rewrite data blocks (simulate bad blocks)

BBED > modify 1000 file 8 block

File: / u01/app/oracle/oradata/DBdb/test01.dbf (8)

Block: 144 Offsets: 0 to 511 Dba:0x02000090

03e80000 90000002 87693a00 00000204 16cc0000 00000000 00000000 00000000

00000000 00000000 00000000

00000000 00000000 00000000 04000000 ffffffff 00000000 01000000 10000000

02000100 00000000 00000000 00000000 10000000 00000000 00000000

00000000 01001c00 2e350000 02000000 81000002 01000000 00000000 00000000

00000000 00000000 00000000

2c5f0100 08200e00 00000000 90000002 08000000 00000000 98000002 08000000

08000000 00000000 00000000 00000000

00000000 00000000 00000000

00000000 00000000 00000000

00000000 00000000 00000000

00000000 00000000 00000000

00000000 00000000 00000000 11111111 11111111 00000000 00000000 00000000

00000000 00000000 00000000

00000000 00000000 00000000

00000000 00000000 00000000

2.6 check for block damage

Using the verify command, you can find that the file 8 block 144that you just modified has been marked as corrupt.

BBED > help verify

VERIFY [DBA | FILE | FILENAME | BLOCK]

BBED > verify

DBVERIFY-Verification starting

FILE = / u01/app/oracle/oradata/DBdb/test01.dbf

BLOCK = 144,

Block 144 is corrupt

Corrupt block relative dba: 0x02000090 (file 0, block 144)

Bad header found during verification

Data in bad block:

Type: 3 format: 0 rdba: 0x02000090

Last change scn: 0x0000.003a6987 seq: 0x2 flg: 0x04

Spare1: 0x0 spare2: 0x0 spare3: 0x0

Consistency value in tail: 0x69872002

Check value in block header: 0xcc16

Computed block checksum: 0x4a23

DBVERIFY-Verification complete

Total Blocks Examined: 1

Total Blocks Processed (Data): 0

Total Blocks Failing (Data): 0

Total Blocks Processed (Index): 0

Total Blocks Failing (Index): 0

Total Blocks Empty: 0

Total Blocks Marked Corrupt: 1

Total Blocks Influx: 0

Message 531 not found; product=RDBMS; facility=BBED

BBED >

2.7 use the dbv tool to verify if there are bad blocks

[oracle@wang lib] $dbv file=/u01/app/oracle/oradata/DBdb/test01.dbf

DBVERIFY: Release 11.2.0.4.0-Production on Thu Feb 1 13:09:03 2018

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

DBVERIFY-Verification starting: FILE = / u01/app/oracle/oradata/DBdb/test01.dbf

Page 144 is marked corrupt

Corrupt block relative dba: 0x02000090 (file 8, block 144)

Bad header found during dbv:

Data in bad block:

Type: 3 format: 0 rdba: 0x02000090

Last change scn: 0x0000.003a6987 seq: 0x2 flg: 0x04

Spare1: 0x0 spare2: 0x0 spare3: 0x0

Consistency value in tail: 0x69872002

Check value in block header: 0xcc16

Computed block checksum: 0x4a23

DBVERIFY-Verification complete

Total Pages Examined: 3840

Total Pages Processed (Data): 100

Total Pages Failing (Data): 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 135

Total Pages Processed (Seg): 0

Total Pages Failing (Seg): 0

Total Pages Empty: 3604

Total Pages Marked Corrupt: 1

Total Pages Influx: 0

Total Pages Encrypted: 0

Highest block SCN: 3828105 (0.3828105)

[oracle@wang lib] $

3. The data block is damaged and needs to be recovered.

3.1 in this case, if there is a backup, it needs to be restored from the backup; if there is no backup, then the bad block part of the database will be lost. Check for damaged objects:

SELECT owner, segment_name, segment_type, relative_fno, tablespace_name

FROM dba_extents

WHERE file_id = & AFN

And & BL between block_id AND block_id + blocks-1

SQL > SELECT owner, segment_name, segment_type, relative_fno, tablespace_name

2 FROM dba_extents

3 WHERE file_id = & AFN

4 and & BL between block_id AND block_id + blocks-1

Enter value for afn: 8

Old 3: WHERE file_id = & AFN

New 3: WHERE file_id = 8

Enter value for bl: 144

Old 4: and & BL between block_id AND block_id + blocks-1

New 4: and 144between block_id AND block_id + blocks-1

OWNER SEGMENT_NAME SEGMENT_TYPE RELATIVE_FNO TABLESPACE_NAME

SYS BBED TABLE 8 TEST

3.2 set the event10231 event

If data is lost, internal events can be set to cause full table scans to skip corrupted block

SQL > alter system set events='10231 trace name context forever,level 10'

3.3 Export the data under the user, delete it, and then rebuild the table

Export data: exp system/oracle file=bbed.dmp log=bbed.log tables=sys.bbed

[oracle@wang ~] $exp system/oracle file=bbed.dmp log=bbed.log tables=sys.bbed

Export: Release 11.2.0.4.0-Production on Thu Feb 1 13:38:13 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path...

Current user changed to SYS

. . Exporting table BBED 2877 rows exported

Export terminated successfully without warnings.

[oracle@wang ~] $

-- delete the table

SQL > drop table bbed purge

Table dropped.

SQL >

-- perform import:

Imp\'/ as sysdba\ 'file=bbed.dmp log=bbedimp.log TABLES=bbed

[oracle@wang ~] $imp\'/ as sysdba\ 'file=bbed.dmp log=bbedimp.log TABLES=bbed

Import: Release 11.2.0.4.0-Production on Thu Feb 1 13:49:48 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SYSTEM, not by you

Import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. Importing SYSTEM's objects into SYS

. Importing SYS's objects into SYS

. . Importing table "BBED" 2877 rows imported

Import terminated successfully without warnings.

[oracle@wang ~] $

-- query:

SQL > select count (*) from bbed

COUNT (*)

-

2877

3.4 cancel the 10231 event:

SQL > alter system set events='10231 trace name context off'

System altered.

3.5 verify that there are no bad blocks:

RMAN > validate datafile 8

Starting validate at 01-FEB-18

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting validation of datafile

Channel ORA_DISK_1: specifying datafile (s) for validation

Input datafile file number=00008 name=/u01/app/oracle/oradata/DBdb/test01.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

-

8 OK 0 3601 3840 3830998

File Name: / u01/app/oracle/oradata/DBdb/test01.dbf

Block Type Blocks Failing Blocks Processed

Data 0 103

Index 0 0

Other 0 136

Finished validate at 01-FEB-18

RMAN >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.