吟诗一首
千里冰封万里雪飘，望长城内外，惟余莽莽
上才艺
[root@localhost ~]# su - oracle
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 9 11:09:45 2020
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL>
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7
Current log sequence 9
SQL>
SQL>
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test.dbf' size 10m;
Tablespace created.
SQL> create table testt(id int) tablespace test;
Table created.
SQL> insert into testt values (1);
1 row created.
SQL> insert into testt values (2);
1 row created.
SQL> insert into testt values (3);
1 row created.
SQL> insert into testt values (4);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> select file#,name from v$datafile;
FILE#
----------
NAME
--------------------------------------------------------------------------------
1
/u01/app/oracle/oradata/orcl/system01.dbf
2
/u01/app/oracle/oradata/orcl/sysaux01.dbf
3
/u01/app/oracle/oradata/orcl/undotbs01.dbf
FILE#
----------
NAME
--------------------------------------------------------------------------------
4
/u01/app/oracle/oradata/orcl/users01.dbf
5
/u01/app/oracle/oradata/orcl/example01.dbf
6
/u01/app/oracle/oradata/orcl/test.dbf
6 rows selected.
SQL> alter database datafile 6 offline drop;
Database altered.
SQL>
SQL> set lines 200 pages 200
SQL> col NAME for a50
select file#,name,STATUS from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- --------------
1 /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/orcl/users01.dbf ONLINE
5 /u01/app/oracle/oradata/orcl/example01.dbf ONLINE
6 /u01/app/oracle/oradata/orcl/test.dbf RECOVER
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> select file#,name,STATUS from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- --------------
1 /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/orcl/users01.dbf ONLINE
5 /u01/app/oracle/oradata/orcl/example01.dbf ONLINE
6 /u01/app/oracle/oradata/orcl/test.dbf RECOVER
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> select file#,name,STATUS from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- --------------
1 /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/orcl/users01.dbf ONLINE
5 /u01/app/oracle/oradata/orcl/example01.dbf ONLINE
6 /u01/app/oracle/oradata/orcl/test.dbf RECOVER
6 rows selected.
SQL>
SQL> alter database datafile 6 online;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/test.dbf'
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
-----此处开始配置bbed
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1120807
2 1120807
3 1120807
4 1120807
5 1120807
6 1119164
6 rows selected.
SQL> set lines 200 pages 200
SQL> col error for a20
SQL> select file#,online_status,error,change# from v$recover_file;
FILE# ONLINE_STATUS ERROR CHANGE#
---------- -------------- -------------------- ----------
6 OFFLINE 1119164
SQL> select file#,status,error,fuzzy from v$datafile_header;
FILE# STATUS ERROR FUZZY
---------- -------------- -------------------- ------
1 onLINE YES
2 onLINE YES
3 onLINE YES
4 onLINE YES
5 onLINE YES
6 OFFLINE YES
6 rows selected.
SQL>
SQL> col CHECKPOINT_CHANGE# for 9999999999999999999999
SQL> select file#,status,error,fuzzy,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS ERROR FUZZY CHECKPOINT_CHANGE#
---------- -------------- -------------------- ------ -----------------------
1 onLINE YES 1120807
2 onLINE YES 1120807
3 onLINE YES 1120807
4 onLINE YES 1120807
5 onLINE YES 1120807
6 OFFLINE YES 1119164
6 rows selected.
SQL> set lines 100 pages 200
SQL> col name for a50
SQL> col bytes for 999999999999999999
SQL> select file#,name,bytes from v$datafile;
FILE# NAME BYTES
---------- -------------------------------------------------- -------------------
1 /u01/app/oracle/oradata/orcl/system01.dbf 713031680
2 /u01/app/oracle/oradata/orcl/sysaux01.dbf 545259520
3 /u01/app/oracle/oradata/orcl/undotbs01.dbf 104857600
4 /u01/app/oracle/oradata/orcl/users01.dbf 5242880
5 /u01/app/oracle/oradata/orcl/example01.dbf 104857600
6 /u01/app/oracle/oradata/orcl/test.dbf 10485760
6 rows selected.
--如上检查发现6号文件有问题是offline的
开始bbed （最好把库关闭在开始用bbed防止块被覆盖，也可以设置为维护模式）
BBED> d /v dba 4,1 offset 484 查看一个好的文件的scn地址（这是打印下第4号文件第一个块偏移484位置是scn
）
File: /u01/app/oracle/oradata/orcl/users01.dbf (4)
Block: 1 Offsets: 484 to 995 Dba:0x01000001
-------------------------------------------------------
271a1100 00000000 7f4e253e 01000000 l '........N%>....
0d000000 34000000 10000000 02000000 l ....4...........
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
0d000d00 0d000100 00000000 00000000 l ................
00000000 02000001 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
<16 bytes per line>
BBED> d /v dba 6,1 offset 484 ---查看问题文件的scn
File: /u01/app/oracle/oradata/orcl/test.dbf (6)
Block: 1 Offsets: 484 to 995 Dba:0x01800001
-------------------------------------------------------
27271100 00000000 5543253e 01000000 l ''......UC%>....
09000000 e8280000 1000059b 02000000 l .....(..........
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
0d000d00 0d000100 00000000 00000000 l ................
00000000 02008001 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
<16 bytes per line>
BBED> modify /x 271a dba 6,1 offset 484 (填写部分，字母前的)
File: /u01/app/oracle/oradata/orcl/test.dbf (6)
Block: 1 Offsets: 484 to 995 Dba:0x01800001
------------------------------------------------------------------------
271a1100 00000000 5543253e 01000000 09000000 e8280000 1000059b 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
0d000d00 0d000100 00000000 00000000 00000000 02008001 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> set file 6 block 1
FILE# 6
BLOCK# 1
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x00111a27
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x3e254355
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000009
ub4 kcrbabno @504 0x000028e8
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED> set file 1 block 1
FILE# 1
BLOCK# 1
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x00111a27
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x3e254e7f
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x0000000d
ub4 kcrbabno @504 0x00000034
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED> sum dba 6,1 apply
Check value for File 6, Block 1:
current = 0x51c2, required = 0x51c2
BBED> d /v dba 6,1 offset 484
File: /u01/app/oracle/oradata/orcl/test.dbf (6)
Block: 1 Offsets: 484 to 995 Dba:0x01800001
-------------------------------------------------------
271a1100 00000000 5543253e 01000000 l '.......UC%>....
09000000 e8280000 1000059b 02000000 l .....(..........
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
0d000d00 0d000100 00000000 00000000 l ................
00000000 02008001 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
<16 bytes per line>
BBED> d /v dba 4,1 offset 484
File: /u01/app/oracle/oradata/orcl/users01.dbf (4)
Block: 1 Offsets: 484 to 995 Dba:0x01000001
-------------------------------------------------------
271a1100 00000000 7f4e253e 01000000 l '........N%>....
0d000000 34000000 10000000 02000000 l ....4...........
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
0d000d00 0d000100 00000000 00000000 l ................
00000000 02000001 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
<16 bytes per line>
BBED>
再开一个窗口
SQL> select file#,status,error,fuzzy,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS ERROR FUZZY CHECKPOINT_CHANGE#
---------- -------------- -------------------- ------ -----------------------
1 onLINE YES 1120807
2 onLINE YES 1120807
3 onLINE YES 1120807
4 onLINE YES 1120807
5 onLINE YES 1120807
6 OFFLINE YES 1120807
6 rows selected.
SQL> select count(*) from testt;
select count(*) from testt
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/test.dbf'
SQL> alter database datafile 6 online;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/test.dbf'
SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile 6 online;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01531: a database already open by the instance
SQL> select count(*) from testt;
COUNT(*)
----------
4
起来后发现检查点不一致，做一次检查点就行
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
-----------------------
1120807
1120807
1120807
1120807
1120807
1127418
6 rows selected.
SQL> alter system checkpoint;
System altered.
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
-----------------------
1135591
1135591
1135591
1135591
1135591
1135591
6 rows selected.
SQL>
