Skip to content

Oracle恢复 - 2. page

Oracle bootstrap$ 说明

What is bootstrap?

Bootstrap is a technique for loading the first few instructions of a computer program into active memory and then using them to bring in the rest of the program.

What is bootstrap in Oracle ?

In Oracle, Bootstrap refers to loading of metadata (data dictionary) before we OPEN the database.Bootstrap objects are classified as the objects (tables / indexes / clusters) with the object_id below 56 as bootstrap objects.  These objects are mandatory to bring up an instance, as this contains the most important metadata of the database.

What happens on database startup?

This shall be explained by setting the SQL_TRACE while opening the database.Connect as sysdba and do the following
SQL> startup mount ;
SQL> alter session set events ‘10046 trace name context forever, level 12 ‘ ;
SQL> alter database open ;
SQL>  alter session set events ‘10046 trace name context off ‘ ;
SQL> ORADEBUG SETMYPID
SQL> ORADEBUG TRACEFILE_NAME
The sql_trace of the above process explains the following operations behind startup. The bootstrap operation happens between MOUNT stage and OPEN stage.
1.)  The first SQL after in the above trace shows the creation of the bootstrap$ table. Something similar to the following:
create table bootstrap$ ( line# number not null, obj# number not null, sql_text varchar2(4000) not null) storage (initial 50K objno 56 extents (file 1 block 377))
This sys.bootstrap$ table contains the DDL’s for other bootstrap tables (object_id below 56). Actually these tables were created internally by the time of database creation (by sql.bsq), The create DDL passed between MOUNT and OPEN stage will be executed through different driver routines. In simple words these are not standard CREATE DDLs.
While starting up the database oracle will load these objects into memory (shared_pool), (ie) it will assign the relevant object number and refer to the datafile and the block associated with that. And such operations happen only while warm startup.
 The internals of the above explained in ‘kqlb.c’.
2.)  Now a query executed against the sys.bootstrap$ table, which holds the create sql’s for other base tables.
select line#, sql_text from bootstrap$ where obj# != :1 (56)
Subsequently it will create those objects by running those queries.
Object number 0 – (System Rollback Segment)
Object number 2 to 55 (Other base tables)
Object number 1 is NOT used by any of the objects.
3.) Performs various operations to keep the bootstrap objects in consistent state.
Upon the successful completion of bootstrap the database will do the other tasks like recovery and will open the database.

Which objects are classified as bootstrap objects in oracle database?

Objects with data_object_id less than 56 are classified as core bootstrap objects.The objects are added to the bootstrap. The objects affected are :

hist_head$
histgrm$
i_hh_obj#_col#
i_hh_obj#_intcol#
i_obj#_intcol#
i_h_obj#_col#
c_obj#_intcol#
From 10.1 the following objects have been added:
fixed_obj$
tab_stats$
ind_stats$
i_fixed_obj$_obj#
i_tab_stats$_obj#
i_ind_stats$_obj#
object_usage
These additional objects shall be re-classified (or) ignored by following methods.
1. Opening the database in migrate mode
2. Using event 38003
Event 38003 affects the bootstrap process of loading the fixed cache in  kqlblfc(). Per default certain objects are marked as bootstrap objects (even though they are not defined as such in sys.bootstrap$) but by setting the event they will be left as non-bootstrapped.

What is bootstrap process failure? or  ORA-00704

This ORA-00704 error SERIOUS if reported at startup. This error refers to some problem during bootstrap operation. Any ORA-00704 error on STARTUP / RECOVER is serious, this error normally rose due to some inconsistency with the bootstrap segments (or) data corruption on bootstrap$ (or) any of the base tables below object_id  56. After this error it might not allow to open that database.

When ORA-00704 shall occur?

1. There is a probable of this error when any unsupported operations are tried to force open the database.
2. This error can also occur when system datafile has corrupted blocks. (ORA-01578)
3. In earlier releases of oracle (prior to 7.3.4 and 8.0.3) this issue shall arise due to Bug 434596
The option is to restore it from a good backup and recover it.
-> If the underlying cause is physical corruption that is due to hardware problems then do complete recovery.
-> If the issue is not relating to any physical corruption, then the problem could be due some unsupported actions on Bootstrap, and a Point In Time Recovery would be an option in such cas.

Corruptions on OBJ$ indexes。OBJ$上索引损坏的一种处理方式(数据量不大的时候,只要业务允许停机可以忽略数据量的考虑)

INTERNAL:Corruptions on OBJ$ indexes (Doc ID 39400.1)

 

To export a system when there are corruptions on indexes on OBJ$:

 

–     Take a FULL COLD BACKUP

 

–     Connect internal in SQLDBA and assuming your default tablespace is

SYSTEM and you have some free space:

 

create table pig$ as select * from obj$;

 

create unique index p_obj1 on pig$(obj#);

 

create unique index p_obj2 on pig$(owner#, name, namespace,

remoteowner, linkname);

 

 

–     Now edit the catexp.sql script (take a safe copy first).

This is generally located in the RDBMS/ADMIN directory.

 

Edit this to change ALL references to the table ‘OBJ$’

to now reference ‘PIG$’.

 

–     Connect internal in SQLDBA and run this new CATEXP.SQL

 

–     Perform a FULL export.

 

–     Shutdown the database and delete it.

 

–     Recreate the database from scratch with a CREATE DATABASE command.

Ensure you set up a second rollback segment for non-system

tablespace use on the import.

 

–     Perform a FULL import.

sysaux表空间损坏导致db无法open的故障修复–重建sysaux 表空间

某政府机关核心数据库sysaux表空间由于光纤链路问题出现损坏导致数据库无法open,通过特殊的办法修复,后续补上.

 

该问题为重建sysaux的过程,主要sysaux表空被截断了,文件大小不对.所以采用重建的办法.主要步骤如下:

 

1.用exp基于表空间的方式备份数据

2.查询当前数据库有多少组件

3.删掉sysaux表空间

4.卸载相关组件

5.重建sysaux表空间

6.重装组件

7.expdp导出全库重建整个库.

 

将该SYSAUX 数据文件 offline 后启动实例到 open 状态

 

SQL> alter database datafile 3 offline drop;Database altered.
SQL> alter database open;

Database altered.

SQL> select status from dba_tablespaces where tablespace_name=’SYSAUX’;

STATUS ——— ONLINE

SQL> select file_name,status,online_status from tablespace_name=’SYSAUX’;

FILE_NAME ——————————————————————————–STATUS ——— ——-

===该部分作者加密===

SQL> alter session set events ‘25475 trace name context off’; Session altered.

安装该组件

SQL> @?/rdbms/admin/owminst.plb

测试 exp 导出已经可执行

 

重建 DB CONTROL

 

SYSMAN需要删掉。

 

SQL>alter session set events ‘25475 trace name context forever, level 2’;
Session altered.
SQL> drop user sysman cascade;

User dropped.
SQL> alter session set events ‘25475 trace name context off’;
Session altered.
SQL> @?/sysman/admin/emdrep/sql/emreposcre ?/ SYSMAN luda TEMP ON;

 

重建 OLAP

[oracle@rac ~]$ cd $ORACLE_HOME/olap/admin

SQL> @catnoamd.sql

SQL> @olapidrp.plb

SQL> @catnoaps.sql

SQL> @catnoxoq.sql

SQL> @cwm2drop.sql

SQL> @?/olap/admin/olap.sql SYSAUX TEMP;

安装 Oracle Spatial

SQL> @?/md/admin/mdinst.sql;

安装 TX

SQL> @?/ctx/admin/catctx luda SYSAUX TEMP NOLOCK;

SQL> conn CTXSYS/luda
SQL> @?/ctx/admin/defaults/dr0defin.sql “AMERICAN”

安装 XML

SQL> conn / as sysdba
SQL> @?/rdbms/admin/catqm.sql luda SYSAUX TEMP;

SQL> @?/rdbms/admin/catxdbj.sql;
SQL> @?/rdbms/admin/catrul.sql;

安装 TEXT

SQL> @?/ctx/admin/catctx aaa SYSAUX TEMP NOLOCK; SQL> conn CTXSYS/luda
SQL> @?/ctx/admin/defaults/dr0defin.sql “AMERICAN”

安装 Oracle Intermedia
SQL> @?/ord/admin/ordinst.sql SYSAUX SYSAUX;

SQL> @?/ord/im/admin/iminst.sql;

 

unused column 和 drop column操作实质

unused column和drop column的操作从本质上讲是不一样的,unused是通过变更数据字典的信息让sql无法访问到column,而drop是直接在物理数据层做了变动。这里的操作后台跟踪可以用event 10046去验证,这里不做描述.

下面通过实验的方式验证unused和drop column的操作对比情况:

1.创建测试表

SQL> grant dba to luda;

Grant succeeded.

SQL> conn luda/luda
Connected.


SQL> create table luda_t1 as  select * from dba_objects;

Table created.

SQL> set timing on
SQL> set serverout on
SQL> exec showspace('LUDA_T1','LUDA')
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................53
Unused Bytes............................434176
Last Used Ext FileId....................4
Last Used Ext BlockId...................2953
Last Used Block.........................75
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................695
Total bytes.............................5693440

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name='redo size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             80916

Elapsed: 00:00:00.01
SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name='undo change vector size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
undo change vector size                                               21012

Elapsed: 00:00:00.00

2.设置object_name为unused

SQL> alter table luda_t1 set unused column object_name;

Table altered.

Elapsed: 00:00:00.02
SQL> exec showspace('LUDA_T1','LUDA')  ---- 对比操作前,可以发现luda_t1表存储信息未有变动
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................53
Unused Bytes............................434176
Last Used Ext FileId....................4
Last Used Ext BlockId...................2953
Last Used Block.........................75
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................695
Total bytes.............................5693440

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name='redo size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             92176

Elapsed: 00:00:00.00

---产生了少量redo日志

SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name='undo change vector size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
undo change vector size                                               25212

Elapsed: 00:00:00.00

--产生了少量undo

3.执行drop unused column

SQL> alter table luda_t1 drop unused column;

Table altered.

Elapsed: 00:00:00.26
SQL> exec showspace('LUDA_T1','LUDA') --对比操作前可以发现在freespace层面25%-50%多出了642的block
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................53
Unused Bytes............................434176
Last Used Ext FileId....................4
Last Used Ext BlockId...................2953
Last Used Block.........................75
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............642
25% -- 50% free space bytes.............5259264
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................53  --总占用block降低为53个,总块数不变642+53=695
Total bytes.............................434176

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>  select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name='redo size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          12393932

--产生大量的redo日志相对比上一次操作的redo量
Elapsed: 00:00:00.01
SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name='undo change vector size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
undo change vector size                                             5128064
--产生大量的undo信息相对比上一次操作的undo量
Elapsed: 00:00:00.00
SQL>

4.执行drop column的测试

SQL> alter table luda_t1 drop column object_type;

Table altered.

Elapsed: 00:00:00.25
SQL> exec showspace('LUDA_T1','LUDA') -- drop 操作效果与drop unused一致,释放空间,降低高水位
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................53
Unused Bytes............................434176
Last Used Ext FileId....................4
Last Used Ext BlockId...................2953
Last Used Block.........................75
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............664
25% -- 50% free space bytes.............5439488
50% -- 75% free space blocks............1
50% -- 75% free space bytes.............8192
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................30
Total bytes.............................245760

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>
SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name='redo size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          23902388
--产生大量的redo日志相对比上一次操作的redo量
Elapsed: 00:00:00.01
SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name='undo change vector size';
--产生大量的undo信息相对比上一次操作的undo量
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
undo change vector size                                             9439452

Elapsed: 00:00:00.00
SQL>

这里验几个情况:

1.unused column只产生少量的redo和undo,真实在表存储部分并未做变动,高水位线没有变动.真实数据部分并未被oracle处理,而根据trace信息可以发现unused column是在数据字典层面做的变动,对被unused操作的字段打上对于的flag.

2.drop unused column 操作会对被标记为unused flag的数据进行rewrite(trace可以发现)并释放空间,降低高水位,同时产生大量的redo和undo.

3.drop column操作会对整个字段物理数据部分直接进行删除(bbed可以发现),并更新table entries.同时降低高水位产生大量的redo和undo.相当于一次数据重组.

以下的set unused 以及drop column的trace结果图作为参考:

unused_column

drop_column

检测坏块处于对象上面还是空闲空间的脚本

该脚本的作用为查找损坏的块是在对象上面还是在空闲空间上.

set lines 200 pages 10000
col segment_name format a30

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
ORDER BY file#, corr_start_block#;

测试案例:

构造坏块

RMAN>  blockrecover datafile 4 block 20 clear;

Starting blockrecover at 28-SEP-15
using channel ORA_DISK_1
Finished blockrecover at 28-SEP-15

RMAN>  blockrecover datafile 4 block 22 clear;

Starting blockrecover at 28-SEP-15
using channel ORA_DISK_1
Finished blockrecover at 28-SEP-15

使用rman检测

RMAN> backup validate datafile 4;

Starting backup at 28-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/luda/oracle/data/user01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: -01:59:59
Finished backup at 28-SEP-15

执行该脚本查询坏块对应的对象

SQL> select * from v$database_block_corruption ;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         4         22          1                  0 CHECKSUM
         4         20          1                  0 CHECKSUM


SQL> set lines 200 pages 10000
SQL> col segment_name format a30
SQL>
SQL> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2  , greatest(e.block_id, c.block#) corr_start_block#
  3  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
  4  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5  - greatest(e.block_id, c.block#) + 1 blocks_corrupted
  6  , null description
  7  FROM dba_extents e, v$database_block_corruption c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id < = c.block# + c.blocks - 1
 10  AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13  , header_block corr_start_block#
 14  , header_block corr_end_block#
 15  , 1 blocks_corrupted
 16  , 'Segment Header' description
 17  FROM dba_segments s, v$database_block_corruption c
 18  WHERE s.header_file = c.file#
 19  AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22  , greatest(f.block_id, c.block#) corr_start_block#
 23  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
 24  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25  - greatest(f.block_id, c.block#) + 1 blocks_corrupted
 26  , 'Free Block' description
 27  FROM dba_free_space f, v$database_block_corruption c
 28  WHERE f.file_id = c.file#
 29  AND f.block_id < = c.block# + c.blocks - 1
 30  AND f.block_id + f.blocks - 1 >= c.block#
 31  ORDER BY file#, corr_start_block#;

OWNER                          SEGMENT_TYPE       SEGME PARTITION_NAME                      FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
------------------------------ ------------------ ----- ------------------------------ ---------- ----------------- --------------- ---------------- --------------
SYS                            TABLE              LUDA                                          4                20              20                1
SYS                            TABLE              LUDA                                          4                22              22                1