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

1. Configure BBET

BBET library files are not provided by default in Oracle11g. If necessary, you can copy the files in 10g to the corresponding directory in 11g and then perform the installation:

$ORACLE_HOME/rdbms/lib/ssbbded.o

$ORACLE_HOME/rdbms/lib/sbbdpt.o

$ORACLE_HOME/rdbms/mesg/bbedus.msb

You will find that there is a default password when you use it for the first time, from which you can see the restriction of oracle on the bbed tool. The default password is blockedit.

SQL > col name for A50

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

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

1/ u01/app/oracle/oradata/satdb/system01.dbf 786432000

2 / u01/app/oracle/oradata/satdb/sysaux01.dbf 618659840

3 / u01/app/oracle/oradata/satdb/undotbs01.dbf 94371840

4 / u01/app/oracle/oradata/satdb/users01.dbf 5242880

5 / u01/app/oracle/oradata/satdb/data01.dbf 104857600

6 / u01/app/oracle/oradata/satdb/fda_tbs01.dbf 524288000

[oracle@orcl ~] $vi dbfiles.txt

1/ u01/app/oracle/oradata/satdb/system01.dbf 786432000

2 / u01/app/oracle/oradata/satdb/sysaux01.dbf 618659840

3 / u01/app/oracle/oradata/satdb/undotbs01.dbf 94371840

4 / u01/app/oracle/oradata/satdb/users01.dbf 5242880

5 / u01/app/oracle/oradata/satdb/data01.dbf 104857600

6 / u01/app/oracle/oradata/satdb/fda_tbs01.dbf 524288000

[oracle@orcl ~] $cat parameter.txt

Blocksize=8192

Listfile=dbfiles.txt

Mode=edit

2. Simulation error

SQL > select file#,status from v$datafile

FILE# STATUS

--

1 SYSTEM

2 ONLINE

3 ONLINE

4 ONLINE

5 ONLINE

6 ONLINE

6 rows selected.

SQL > alter database datafile 5 offline

Database altered.

SQL > select file#,status from v$datafile

FILE# STATUS

--

1 SYSTEM

2 ONLINE

3 ONLINE

4 ONLINE

5 RECOVER

6 ONLINE

6 rows selected.

SQL > select hxfil,fhrba_seq from x$kcvfh

HXFIL FHRBA_SEQ

--

1 1

2 1

3 1

4 1

5 1

6 1

6 rows selected.

SQL > select group#,archived,sequence#,status from v$log

GROUP# ARCHIV SEQUENCE# STATUS

1 YES 1 ACTIVE

2 YES 2 ACTIVE

3 NO 3 CURRENT

SQL > startup force mount

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size 2253664 bytes

Variable Size 989858976 bytes

Database Buffers 603979776 bytes

Redo Buffers 7319552 bytes

Database mounted.

SQL > recover database until cancel

ORA-00279: change 1268630 generated at 10/01/2015 11:36:55 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/arch3/1_1_891948516.dbf

ORA-00280: change 1268630 for thread 1 is in sequence # 1

Specify log: {= suggested | filename | AUTO | CANCEL}

Cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1:'/ u01 *

ORA-01112: media recovery not started

SQL > alter database open resetlogs

Alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1:'/ u01 *

SQL > alter system set "_ allow_resetlogs_corruption" = true scope=spfile

System altered.

SQL > startup force mount

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size 2253664 bytes

Variable Size 989858976 bytes

Database Buffers 603979776 bytes

Redo Buffers 7319552 bytes

Database mounted.

Note: here is a hint that if you open the database with resetlogs, file 13 will be lost. So we add the keyword for drop with the following command to tell the database that this data file may be discarded later and will not be in online.

SQL > alter database datafile 13 offline for drop

Database altered.

SQL > alter database open resetlogs

Database altered.

SQL > select resetlogs_change#, to_char (resetlogs_time,'mm/dd/yyyy hh34:mi:ss') time from v$database

RESETLOGS_CHANGE# TIME

1268631 10/01/2015 11:46:40

SQL > col fhrlc for A50

SQL > set linesize 400

SQL > select hxfil,fhrls change#,fhrlc_i,fhrlc time from x$kcvfh

HXFIL CHANGE# FHRLC_I TIME

-

1 1268631 891949600 10/01/2015 11:46:40

2 1268631 891949600 10/01/2015 11:46:40

3 1268631 891949600 10/01/2015 11:46:40

4 1268631 891949600 10/01/2015 11:46:40

5 1267919 891948516 10/01/2015 11:28:36

6 1268631 891949600 10/01/2015 11:46:40

6 rows selected.

By comparing the resetlogs scn and resetlogs count values of file 5, it is not difficult to find the reason for triggering ora-01190, that is, the mismatch between the resetlogs scn and resetlogs count in the header of the data file and the resetlogs information in the control file. So, if we want to avoid ora-01190 errors, we can modify the data file header resetlogs related values through bbed

3. Modify the header of the data file through bbed to avoid this error

1 location of resetlogs count and resetlogs scn in the header of the data file

Resetlogs count is located at the offset of the header of the data file

Resetlogs scn is located at the offset of the header of the data file

SQL > alter database datafile 5 online

Alter database datafile 5 online

*

ERROR at line 1:

ORA-01190: control file or data file 5 is from before the last RESETLOGS

ORA-01110: data file 5:'/ u01 *

[oracle@orcl ~] $bbed parfile=parameter.txt password=blockedit

BBED: Release 2.0.0.0.0-Limited Production on Thu Oct 1 11:52:29 2015

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

*! For Oracle Internal Use only!! *

BBED > dump / v dba 1 offset 112 count 30

File: / u01/app/oracle/oradata/satdb/system01.dbf (1)

Block: 1 Offsets: 112 to 141 Dba:0x00400001

20122a35 975b1300 00000000 00000000 l. * 5.

00000000 00000000 00000420 cf00 l. ..

BBED > dump / v dba 5JI 1 offfset 112 count 30

BBED-00202: invalid parameter (offfset)

BBED > dump / v dba 5JI 1 offset 112 count 30

File: / u01/app/oracle/oradata/satdb/data01.dbf (5)

Block: 1 Offsets: 112 to 141 Dba:0x01400001

E40d2a35 cf581300 00000000 00000000 l. * 5.X.

00000000 00000000 00000400 7f00 l.

BBED > modify / x 2012

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

File: / u01/app/oracle/oradata/satdb/data01.dbf (5)

Block: 1 Offsets: 112 to 141 Dba:0x01400001

20122a35 cf581300 00000000 00000000 00000000 00000400 7f00

BBED > dump / v dba 1 offset 116count 30

File: / u01/app/oracle/oradata/satdb/system01.dbf (1)

Block: 1 Offsets: 116 to 145 Dba:0x00400001

975b1300 00000000 00000000 00000000 l. [.

00000000 00000420 cf000000 8811 l. .

BBED > dump / v dba 5 offset 116count 30

File: / u01/app/oracle/oradata/satdb/data01.dbf (5)

Block: 1 Offsets: 116 to 145 Dba:0x01400001

Cf581300 00000000 00000000 00000000 l .X.

00000000 00000400 7f000000 780d l .x.

BBED > modify / x 975b

File: / u01/app/oracle/oradata/satdb/data01.dbf (5)

Block: 1 Offsets: 116 to 145 Dba:0x01400001

975b1300 00000000 00000000 00000000 00000400 7f000000 780d

BBED > sum apply

Check value for File 5, Block 1:

Current = 0xefbf, required = 0xefbf

SQL > select hxfil,fhrls change#,fhrlc_i,fhrlc time from x$kcvfh

HXFIL CHANGE# FHRLC_I TIME

-

1 1268631 891949600 10/01/2015 11:46:40

2 1268631 891949600 10/01/2015 11:46:40

3 1268631 891949600 10/01/2015 11:46:40

4 1268631 891949600 10/01/2015 11:46:40

5 1268631 891949600 10/01/2015 11:46:40

6 1268631 891949600 10/01/2015 11:46:40

6 rows selected.

Note below that although we have adjusted the restlogs scn and resetlogs count of the data file header with bbed to keep it the same as the control file, we will continue to report the need for media recovery when we online the data file. As follows:

SQL > alter database datafile 5 online

Alter database datafile 5 online

*

ERROR at line 1:

ORA-01113: file 5 needs media recovery

ORA-01110: data file 5:'/ u01 *

4. Adjust the data file header checkpoint and scn related values with bbed.

We should also change the following offset

Ub4 kcvfhcpc @ 140 0x00000308-checkpoint count

Ub4 kcvfhccc @ 148 0x00000307-always 1 less than checkpoint calculation

Ub4 kcvcptim @ 492 0x2f9af923-checkpoint time

Low bit of ub4 kscnbas @ 484 0x8013ea80-scn

Ub2 kscnwrp @ 488 0x0000-High bit of scn

BBED > dump / v dba 1 offset 140 count 30

File: / u01/app/oracle/oradata/satdb/system01.dbf (1)

Block: 1 Offsets: 140 to 169 Dba:0x00400001

Cf000000 88112a35 ce000000 00000000 l. * 5.

00000000 00000000 00000000 0000 l.

BBED > dump / v dba 5 offset 140 count 30

File: / u01/app/oracle/oradata/satdb/data01.dbf (5)

Block: 1 Offsets: 140 to 169 Dba:0x01400001

7f000000 780d2a35 7e000000 00000000 l. X. 5 years.

00000000 00000000 00000000 0000 l.

BBED > modify / x cf

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

File: / u01/app/oracle/oradata/satdb/data01.dbf (5)

Block: 1 Offsets: 140 to 169 Dba:0x01400001

Cf000000 780d2a35 7e000000 00000000 00000000 00000000 0000

BBED > dump / v dba 1 offset 148count 30

File: / u01/app/oracle/oradata/satdb/system01.dbf (1)

Block: 1 Offsets: 148 to 177 Dba:0x00400001

Ce000000 00000000 00000000 00000000 l.

00000000 00000000 00000000 0000 l.

BBED > dump / v dba 5 offset 148count 30

File: / u01/app/oracle/oradata/satdb/data01.dbf (5)

Block: 1 Offsets: 148 to 177 Dba:0x01400001

7e000000 00000000 00000000 00000000 l.

00000000 00000000 00000000 0000 l.

BBED > modify / x ce

File: / u01/app/oracle/oradata/satdb/data01.dbf (5)

Block: 1 Offsets: 148 to 177 Dba:0x01400001

Ce000000 00000000 00000000 00000000 0000

BBED > dump / v dba 1 offset 492 count 30

File: / u01/app/oracle/oradata/satdb/system01.dbf (1)

Block: 1 Offsets: 492 to 521 Dba:0x00400001

23122a35 01000000 01000000 02000000 l #. * 5.

10000000 02000000 00000000 0000 l.

BBED > dump / v dba 5JI 1 offset 492 count 30

File: / u01/app/oracle/oradata/satdb/data01.dbf (5)

Block: 1 Offsets: 492 to 521 Dba:0x01400001

D70f2a35 01000000 01000000 2b020000 l. * 5.

100085a6 02000000 00000000 0000 l.

BBED > modify / x 2312

File: / u01/app/oracle/oradata/satdb/data01.dbf (5)

Block: 1 Offsets: 492 to 521 Dba:0x01400001

23122a35 01000000 01000000 2b020000 100085a6 02000000 00000000 0000

BBED > dump / v dba 1 offset 484 count 30

File: / u01/app/oracle/oradata/satdb/system01.dbf (1)

Block: 1 Offsets: 484 to 513 Dba:0x00400001

9a5b1300 00000000 23122a35 01000000 l. [. #. * 5.

01000000 02000000 10000000 0200 l.

BBED > dump / v dba 5J 1 offset 484 count 30

File: / u01/app/oracle/oradata/satdb/data01.dbf (5)

Block: 1 Offsets: 484 to 513 Dba:0x01400001

975b1300 00000000 23122a35 01000000 l. [. #. * 5.

01000000 2b020000 100085a6 0200 l. +.

BBED > modify / x 9a5b

File: / u01/app/oracle/oradata/satdb/data01.dbf (5)

Block: 1 Offsets: 484 to 513 Dba:0x01400001

9a5b1300 00000000 23122a35 01000000 01000000 2b020000 100085a6 0200

BBED > dump / v dba 1 offset 488 count 30

File: / u01/app/oracle/oradata/satdb/system01.dbf (1)

Block: 1 Offsets: 488 to 517 Dba:0x00400001

00000000 23122a35 01000000 01000000 l. #. * 5.

02000000 10000000 02000000 0000 l.

BBED > dump / v dba 5J 1 offset 488 count 30

File: / u01/app/oracle/oradata/satdb/data01.dbf (5)

Block: 1 Offsets: 488 to 517 Dba:0x01400001

00000000 23122a35 01000000 01000000 l. #. * 5.

2b020000 100085a6 02000000 0000 l +.

BBED > sum apply

Check value for File 5, Block 1:

Current = 0xf246, required = 0xf246

BBED > exit

SQL > alter database datafile 5 online

Alter database datafile 5 online

*

ERROR at line 1:

ORA-01113: file 5 needs media recovery

ORA-01110: data file 5:'/ u01 *

SQL > recover datafile 5

Media recovery complete.

SQL > alter database datafile 5 online

Database altered.

5. Check data and status

SQL > select file#,status from v$datafile

FILE# STATUS

--

1 SYSTEM

2 ONLINE

3 ONLINE

4 ONLINE

5 ONLINE

6 ONLINE

6 rows selected.

SQL > conn lineqi/lineqi

Connected.

SQL > select table_name,tablespace_name from user_tables

SQL > col tablespace_name for 50

SQL > set linesize 400

SQL > /

TABLE_NAME TABLESPACE_NAME

-

EMP1 DATA

TEMP2 DATA

TEST DATA

SYS_FBA_DDL_COLMAP_87367 FDA_TBS1

SYS_FBA_TCRV_87367 FDA_TBS1

SYS_FBA_HIST_87367

SYS_TEMP_FBT

TTT DATA

8 rows selected.

SQL > select * from ttt

ID NAME

1 aa

6. Reference materials:

Http://jiujian.blog.51cto.com/444665/1127404

Http://blog.chinaunix.net/uid-20124596-id-1734425.html

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.