11g OCM目前的考试环境

2015/02/08 by 1 Comment

在2013年5月13日前，Oracle 11g ocm的考试环境为 Oracle Linux Release 5.4 (64-bit)+Oracle Database Enterprise Edition Release 11.2.0.1.0 (64-bit)+Oracle Enterprise Management Grid Control 10g version 10.2.0.5 (64-bit) with repository database 11.1.0.6。

在2013年5月13日后，Oracle 11g ocm的的考试环境变成了Oracle Linux Release 5.4 (32-bit)+Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 (32-bit)+Oracle Enterprise Manager 11gGrid Control Release 1 (11.1.0.1.0)  (32-bit)

具体可以见下表：

Exam Environment

Current Environment(Till 12th May 2013) New Release Environment(From 13th May 2013 onwards)
Oracle Linux Release 5.4 (64-bit) Oracle Linux Release 5.4 (32-bit)
Oracle Database Enterprise Edition Release11.2.0.1.0 (64-bit) Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 (32-bit)
Oracle Enterprise Management Grid Control 10g version 10.2.0.5 (64-bit) with repository database 11.1.0.6 Oracle Enterprise Manager 11gGrid Control Release 1 (11.1.0.1.0)  (32-bit)

 

在考试专题上新加了下面的环节：

  • Use OPatch to install a patch
  • Use Enterprise Manager Configuration Assistant (EMCA) utility

 

Filed Under: Oracle

不必纠结于session_cached_cursors的内存损耗

2015/02/08 by 3 Comments

session cursor cache 特性旨在减少软解析soft parse的性能损耗。通过将session_cached_cursor参数设置为一个大于0 的值启动会话session缓存游标的机制。

重复的对统一语句的解析调用经过3次以后将该语句的游标缓存移入到session cursor cache中，后续的解析调用将在session 缓存中找到对应的游标并不再需要重复打开该游标(askmac.cn)。

注意仅仅是那些重复引发三次解析调用的语句将被缓存，该机制避免了随机游标和硬绑定的游标浪费缓存。

主要UGA中的KKSUG结构定位缓存中的游标状态。kksug.kksugscc存放了session cached cursor信息数据。kksug.kksugscc.kkssclru 代表游标链表，kksug.kksugscc.kkssccnt代表session cursor cache中的游标数量，kksug.kksugscc.kksschit代表cursor cache的命中次数，即游标在session cursor cache中(askmac.cn)正好被找到的次数。

 

一般不需要特别去关心session_cached_cursors参数的内存损耗，一来这些session cursor cache中的栏位slot是可以被重用的，而且每一个slot也不会占用太多内存，一般在几十个字节左右。

同时如上所述KKSUG是位于UGA中的，所以在dedicated server mode独立服务器模式下session cursor cache在PGA=>UGA中， 在共享服务器模式下shared server mode下载SGA=>UGA中。

 

 

@+-> structkkssc @{
/* Session cached cursors */
 /* open_cursors */ 
/* List of open curdef's */ 
/* Number of cursors open */ 
/* List of closed curdef's */ 
/* Cached frames and binds list */
kkssc kksugscc; ub4 kksugmoc; 
kgglk kksugocl; ub4 kksugopc; 
kgglk kksugccl; 
kgglk kksugcfb;
@ /* front is the MRU side the tail is the LRU side */
@ kgglk kkssclru; 
/* linked list of cursors in session cursor cache */
@ kksccht *kksschst;
 /* hash table of cursors in the session cache */
@ ub4 kksscmcc; 
/* max number of cursors allowed in cache */
@ ub4 kkssccnt; 
/* count of cursors in session cursor cache */
@ ub4 kksschit; 
/* session cache cursor hits */
@ };
Filed Under: Oracle

MongoDB 获得参数和命令行启动选项

2015/02/08 by Leave a Comment 

mongos> db.adminCommand( { getParameter : "*" } )
{
        "authOnPrimaryOnly" : true,
        "enableLocalhostAuthBypass" : true,
        "enableTestCommands" : 0,
        "ignoreInitialVersionFailure" : false,
        "logLevel" : 0,
        "logUserIds" : false,
        "notablescan" : false,
        "quiet" : false,
        "releaseConnectionsAfterResponse" : false,
        "supportCompatibilityFormPrivilegeDocuments" : true,
        "syncdelay" : 60,
        "textSearchEnabled" : false,
        "ok" : 1
}


mongos> db.adminCommand( "getCmdLineOpts" )
{
        "argv" : [
                "mongos",
                "--configdb",
                "192.168.1.161:27019"
        ],
        "parsed" : {
                "configdb" : "192.168.1.161:27019"
        },
        "ok" : 1
}



db.adminCommand( { getParameter : "*" } )
db.adminCommand( "getCmdLineOpts" )
Filed Under: MongoDB

在MACOSX 上安装Docker

2015/02/07 by Leave a Comment

注意MACOSX并不太适合部署DOCKER (在macosx上它似乎依赖于VBOX组件 ，或默认安装一个boot2docker-vm)，如果你仅仅是测试或练习那么OK。

此处安装Docker主要依赖于homebrew,所以建议你的环境能正常访问BREW、GITHUB、SF等网站.


ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
brew update
brew tap phinze/homebrew-cask
brew install brew-cask
brew cask install virtualbox
brew install boot2docker
boot2docker init
boot2docker up


boot2docker up会要求你设置一系列环境变量，照做即可

brew install docker
docker version

docker目前在国内没有太好的mirror，我在这里主要还是依靠docker.com的原版镜像。

Filed Under: 未分类

HADOOP 2.6 WORDCOUNT EXAMPLE

2015/02/06 by 2 Comments

HADOOP 2.6 WORDCOUNT EXAMPLE

 

 


root@hadoop2-VirtualBox:/usr/local/hadoop/share/hadoop/mapreduce# pwd
/usr/local/hadoop/share/hadoop/mapreduce

root@hadoop2-VirtualBox:/usr/local/hadoop/share/hadoop/mapreduce# cat f3.txt >> f2.txt
root@hadoop2-VirtualBox:/usr/local/hadoop/share/hadoop/mapreduce# wc -l f2.txt 
7395228 f2.txt
root@hadoop2-VirtualBox:/usr/local/hadoop/share/hadoop/mapreduce# hdfs dfs -put f2.txt /home/hadoop/input/f2.txt
15/02/06 21:06:50 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable





root@hadoop2-VirtualBox:/usr/local/hadoop/share/hadoop/mapreduce# hadoop jar hadoop-mapreduce-examples-2.6.0.jar wordcount /home/hadoop/input /home/hadoop/output4
15/02/06 21:07:25 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
15/02/06 21:07:26 INFO client.RMProxy: Connecting to ResourceManager at /0.0.0.0:8032
15/02/06 21:07:27 INFO input.FileInputFormat: Total input paths to process : 2
15/02/06 21:07:27 INFO mapreduce.JobSubmitter: number of splits:2
15/02/06 21:07:27 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1423212832128_0004
15/02/06 21:07:28 INFO impl.YarnClientImpl: Submitted application application_1423212832128_0004
15/02/06 21:07:28 INFO mapreduce.Job: The url to track the job: http://hadoop2-VirtualBox:8088/proxy/application_1423212832128_0004/
15/02/06 21:07:28 INFO mapreduce.Job: Running job: job_1423212832128_0004
15/02/06 21:07:36 INFO mapreduce.Job: Job job_1423212832128_0004 running in uber mode : false
15/02/06 21:07:36 INFO mapreduce.Job:  map 0% reduce 0%
15/02/06 21:07:47 INFO mapreduce.Job:  map 50% reduce 0%
15/02/06 21:07:52 INFO mapreduce.Job:  map 58% reduce 0%
15/02/06 21:07:55 INFO mapreduce.Job:  map 63% reduce 0%
15/02/06 21:07:58 INFO mapreduce.Job:  map 65% reduce 0%
15/02/06 21:08:02 INFO mapreduce.Job:  map 68% reduce 0%
15/02/06 21:08:05 INFO mapreduce.Job:  map 71% reduce 0%
15/02/06 21:08:06 INFO mapreduce.Job:  map 71% reduce 17%
15/02/06 21:08:08 INFO mapreduce.Job:  map 77% reduce 17%
15/02/06 21:08:11 INFO mapreduce.Job:  map 80% reduce 17%
15/02/06 21:08:14 INFO mapreduce.Job:  map 100% reduce 17%
15/02/06 21:08:16 INFO mapreduce.Job:  map 100% reduce 100%
15/02/06 21:08:16 INFO mapreduce.Job: Job job_1423212832128_0004 completed successfully
15/02/06 21:08:16 INFO mapreduce.Job: Counters: 50
	File System Counters
		FILE: Number of bytes read=269
		FILE: Number of bytes written=317703
		FILE: Number of read operations=0
		FILE: Number of large read operations=0
		FILE: Number of write operations=0
		HDFS: Number of bytes read=125719110
		HDFS: Number of bytes written=49
		HDFS: Number of read operations=9
		HDFS: Number of large read operations=0
		HDFS: Number of write operations=2
	Job Counters 
		Killed map tasks=1
		Launched map tasks=3
		Launched reduce tasks=1
		Data-local map tasks=3
		Total time spent by all maps in occupied slots (ms)=65341
		Total time spent by all reduces in occupied slots (ms)=26179
		Total time spent by all map tasks (ms)=65341
		Total time spent by all reduce tasks (ms)=26179
		Total vcore-seconds taken by all map tasks=65341
		Total vcore-seconds taken by all reduce tasks=26179
		Total megabyte-seconds taken by all map tasks=66909184
		Total megabyte-seconds taken by all reduce tasks=26807296
	Map-Reduce Framework
		Map input records=7395229
		Map output records=14790458
		Map output bytes=184880720
		Map output materialized bytes=65
		Input split bytes=222
		Combine input records=14790470
		Combine output records=16
		Reduce input groups=4
		Reduce shuffle bytes=65
		Reduce input records=4
		Reduce output records=4
		Spilled Records=20
		Shuffled Maps =2
		Failed Shuffles=0
		Merged Map outputs=2
		GC time elapsed (ms)=714
		CPU time spent (ms)=14010
		Physical memory (bytes) snapshot=560402432
		Virtual memory (bytes) snapshot=2402254848
		Total committed heap usage (bytes)=378994688
	Shuffle Errors
		BAD_ID=0
		CONNECTION=0
		IO_ERROR=0
		WRONG_LENGTH=0
		WRONG_MAP=0
		WRONG_REDUCE=0
	File Input Format Counters 
		Bytes Read=125718888
	File Output Format Counters 
		Bytes Written=49


root@hadoop2-VirtualBox:/usr/local/hadoop/share/hadoop/mapreduce# hdfs dfs -ls  /home/hadoop/output4
15/02/06 21:16:46 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Found 2 items
-rw-r--r--   1 root supergroup          0 2015-02-06 21:08 /home/hadoop/output4/_SUCCESS
-rw-r--r--   1 root supergroup         49 2015-02-06 21:08 /home/hadoop/output4/part-r-00000
root@hadoop2-VirtualBox:/usr/local/hadoop/share/hadoop/mapreduce# 

root@hadoop2-VirtualBox:/usr/local/hadoop/share/hadoop/mapreduce# hdfs dfs -cat  /home/hadoop/output4/part-r-00000
15/02/06 21:17:18 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
FKDSFJKS	7395228
HFLESFL	7395228
hello	1
world	1
Filed Under: bigdata

Ubuntu启用 root账号

2015/02/06 by Leave a Comment

Ubuntu启用 root账号

主要使用2条命令

 


sudo passwd root
sudo passwd -u root

sudo vi /etc/ssh/sshd_config

修改 PermitRootLogin without-password 为PermitRootLogin yes

之后

sudo service ssh restart

 


hadoop2@hadoop2-VirtualBox:~$  sudo passwd  root
[sudo] password for hadoop2: 
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully
hadoop2@hadoop2-VirtualBox:~$ 
hadoop2@hadoop2-VirtualBox:~$    sudo passwd  -u root
passwd: password expiry information changed.
hadoop2@hadoop2-VirtualBox:~$ 
hadoop2@hadoop2-VirtualBox:~$ su - root
Password: 


vi /etc/ssh/sshd_config

修改 PermitRootLogin without-password  为PermitRootLogin yes


service ssh restart
Filed Under: Linux

MongoDB生成测试数据脚本

2015/02/05 by Leave a Comment

MongoDB生成测试数据脚本

 

 

for (var i = 1; i <= 9000000; i++) {
   db.testData.insert( { x : i , name: "MACLEAN" , name1:"MACLEAN", name2:"MACLEAN", name3:"MACLEAN"} )
}


db.testData.find().count();

x小于0
db.testData.find({x: {$lt : 0}});

x大于400
db.testData.find({x: {$gt : 400}});

> db.testData.find({x: {$gt : 400}}).explain();




{
	"cursor" : "BasicCursor",
	"isMultiKey" : false,
	"n" : 5860637,
	"nscannedObjects" : 5861039,
	"nscanned" : 5861039,
	"nscannedObjectsAllPlans" : 5861039,
	"nscannedAllPlans" : 5861039,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 45789,
	"nChunkSkips" : 0,
	"millis" : 2598,
	"server" : "macleandeMacBook-Pro.local:27017",
	"filterSet" : false
}
> 
> 
> 
> 
> db.testData.ensureIndex({x:1});




{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}
> 
> 
> 
> 
> db.testData.find({x: {$gt : 400}}).explain();
{
	"cursor" : "BtreeCursor x_1",
	"isMultiKey" : false,
	"n" : 5860637,
	"nscannedObjects" : 5860637,
	"nscanned" : 5860637,
	"nscannedObjectsAllPlans" : 5860637,
	"nscannedAllPlans" : 5860637,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 45786,
	"nChunkSkips" : 0,
	"millis" : 6165,
	"indexBounds" : {
		"x" : [
			[
				400,
				Infinity
			]
		]
	},
	"server" : "macleandeMacBook-Pro.local:27017",
	"filterSet" : false
}
> db.testData.find({x: {$lt : 0}}).explain();
{
	"cursor" : "BtreeCursor x_1",
	"isMultiKey" : false,
	"n" : 0,
	"nscannedObjects" : 0,
	"nscanned" : 0,
	"nscannedObjectsAllPlans" : 0,
	"nscannedAllPlans" : 0,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"x" : [
			[
				-Infinity,
				0
			]
		]
	},
	"server" : "macleandeMacBook-Pro.local:27017",
	"filterSet" : false
}
> db.testData.find({x: {$lt : 0}}).explain();
{
	"cursor" : "BtreeCursor x_1",
	"isMultiKey" : false,
	"n" : 0,
	"nscannedObjects" : 0,
	"nscanned" : 0,
	"nscannedObjectsAllPlans" : 0,
	"nscannedAllPlans" : 0,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"x" : [
			[
				-Infinity,
				0
			]
		]
	},
	"server" : "macleandeMacBook-Pro.local:27017",
	"filterSet" : false
Filed Under: MongoDB

MongoDB的Namespace

2015/02/05 by Leave a Comment

 

MongoDB的Namespace

Namespace的长度

 

每一个namespace包含的信息中：数据库和collection名字加起来不能超过123字节

 

Namespace的数量

namespace的数量限制是namespace文件大小除以628。

一个16MB大小的namespace 文件可以支持24000个namespace。 每一个collection或索引对应使用一个namespace。

Namespace文件的大小

Namespace文件大小截止目前不能超过2047MB。 默认情况下namespace文件是16MB，可以通过nsSize选项来指定其大小。

storage.nsSize

Type: integer

Default: 16

Specifies the default size for namespace files, which are files that end in .ns. Each collection and index counts as a namespace.

Use this setting to control size for newly created namespace files. This option has no impact on existing files. The maximum size for a namespace file is 2047 megabytes. The default value of 16 megabytes provides for approximately 24,000 namespaces.

The storage.nsSize option is available only for mongod.

 

Filed Under: MongoDB

升级到ORACLE 11G OCM 一些思索

2015/02/02 by Leave a Comment

 

我最近通过了ORACLE 11G OCM升级考试，从10g OCM升级到11g OCM。因为在考试前我签署了保密协议，所以在这里我不能透露过多过于考试内容的细节，但我可以共享部分无关考试内容而可以助力大家考试的信息点，以及如何更好地准备11g OCM考试。

 

11g OCM考试是2天的，该考试针对没有获得过9i 、10g OCM的考生，而对于拥有9i、10g OCM的考生而言只需要参加为期一天的upgrade考试即可。 升级考试的一天考试时间大约为8~10个小时，因此你要在体力上有所准备。

如下图的考试提纲，实际你可以在官方的OTN上找到这张图。当你在做准备时，尝试复习每一个提纲范围，这些提纲范围绝对覆盖了考试内容，且有少部分其实是不考的。

考试主要的焦点在于你的熟练度以及你的速度，而对于知识点的深入掌握这些几乎很少考察。简单来说你必须掌握考纲内容，并能很快操作完成要求。同时你要考虑到在考试环境下你是背着巨大压力在答题，所以复习过程中绝对要掐着秒表实践！

每一个考试环节最后会要求你的数据库处于某个状态； 注意：不管你最后有没有彻底完成该环节，最后几分钟都要留下来，保证将数据库处于某个状态，因为在最后评分是通过在你的考试机上执行脚本收集数据来实现的，如果收不到数据，可能意味着该环节全部白做。

11g OCM 升级考试是没有RAC和ASM环节的，而11g OCM非升级考试则有这2个环节。 如果你准备的是升级考试 那么只需要准备单机的环节，环境上不需要集群也就简单地过了。 而11g OCM非升级考试 则需要为11g RAC和ASM准备环境。

对Grid Control的GUI也要由一定的熟练度，大量的考试环节可以通过11g Grid Control来辅助完成，可以极大提高效率，所以注意熟练掌握 Grid Control各功能的入口；

非常重要- 要熟悉不同的备份和恢复场景，考试过程中存在某个环节 某些数据库文件被故意破坏了，需要你恢复。

 

11g OCM 升级考试的考纲

ocm_exam_topic

 

 

Filed Under: Oracle

Oracle ORA-00704 ORA-00604 ORA-01502 SYS.I_DEPENDENCY1.+損害あるいは使えなくなった

2015/02/01 by Leave a Comment

If you cannot recover data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.

Parnassusdata Software Database Recovery Team

Service Hotline:  +86 13764045638 E-mail: service@parnassusdata.com

 

 

ORA-00704 ORA-00604 ORA-01502: index ‘SYS.I_DEPENDENCY1′ or partition of such index is in unusable stateエラはどうやって対応できるかと友に聞かれた。

11.2でI_DEPENDENCY1損害の対応
I_DEPENDENCY2損害の対応ｆ

SYS.I_DEPENDENCY1が壊されたあるいは使えなくなったときに、以下のようなエラになる：

Mon Dec 22 09:13:32 2014

Errors in file /home/oracle/oracle/product/admin/orcl/udump/orcl_ora_19783_lunar.trc:

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-01502: index ‘SYS.I_DEPENDENCY1’ or partition of such index is in unusable state

まずは10gでの対応策で、二つがある：
1，10gで直にリカバリする
2，11.2でデータベースをアップグレードモードに調整して、リカバリする

まずはデータベースに依頼関係があるテーブルは何があるかを見てみよう：

[oracle@lunar oracle]$ ss

 

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Dec 22 08:10:07 2014

 

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

 

SYS@lunar>select owner,object_id,object_name,object_type from dba_objects where object_name like ‘%DEPENDENCY%’ and owner=’SYS’;

 

OWNER            OBJECT_ID OBJECT_NAME                    OBJECT_TYPE

————— ———- —————————— ——————-

SYS                     92 DEPENDENCY$                    TABLE

SYS                  46827 GENDEPENDENCYBLOCKSEQUENCE     TYPE

SYS                  46828 GENDEPENDENCYBLOCKSEQUENCESEQU TYPE

SYS                  46824 GENDEPENDENCYBLOCKSTRUCT       TYPE

SYS                  46826 GENDEPENDENCYBLOCKUNION        TYPE

SYS                  46825 GENINCLUDEDDEPENDENCYBLOCKSTRU TYPE

SYS                   1691 GV_$OBJECT_DEPENDENCY          VIEW

SYS                    122 I_DEPENDENCY1                  INDEX

SYS                    123 I_DEPENDENCY2                  INDEX

SYS                   3950 PUBLIC_DEPENDENCY              VIEW

SYS                  47048 SQLDEPENDENCYBLOCKSEQ          TYPE

SYS                  47049 SQLDEPENDENCYBLOCKSEQUENCE     TYPE

SYS                  47050 SQLDEPENDENCYBLOCKSEQUENCESEQ  TYPE

SYS                  47051 SQLDEPENDENCYBLOCKSEQUENCESEQU TYPE

SYS                  47045 SQLDEPENDENCYBLOCKSTRUCT       TYPE

SYS                  47047 SQLDEPENDENCYBLOCKUNION        TYPE

SYS                  47046 SQLINCLUDEDDEPENDENCYBLOCKSTRU TYPE

SYS                   1066 V_$OBJECT_DEPENDENCY           VIEW

 

18 rows selected.

 

SYS@lunar>

ここで、大切なのはDEPENDENCY$のインディクス情報である。DEPENDENCY$テーブルに二つのインディクスがあることを察知できる。
ここの二つのインディクスが壊された影響も異なっている。その意味は以下の通り：

SYS@lunar>select owner,table_name,index_name,tablespace_name,status from dba_indexes where table_name=’DEPENDENCY$’;

 

OWNER           TABLE_NAME                     INDEX_NAME                     TABLESPACE_NAME                STATUS

————— —————————— —————————— —————————— ——–

SYS             DEPENDENCY$                    I_DEPENDENCY1                  SYSTEM                         VALID

SYS             DEPENDENCY$                    I_DEPENDENCY2                  SYSTEM                         VALID

 

SYS@lunar>

9i、10g、11.1の前に、一部のコアオブジェクトの定義は$ORACLE_HOME/rdbms/admin/sql.bsqにある。
例えばtab$,obj$,ind$など。
11.2から、一部の大事なコアベーステーブルを作成するスクリプトは依然としてsql.bsqであるが、いろんなオブジェクトに異なったスクリプトに分類された：

dcore.bsq

dsqlddl.bsq

dmanage.bsq

dplsql.bsq

dtxnspc.bsq

dfmap.bsq

denv.bsq

drac.bsq

dsec.bsq

doptim.bsq

dobj.bsq

djava.bsq

dpart.bsq

drep.bsq

daw.bsq

dsummgt.bsq

dtools.bsq

dexttab.bsq

ddm.bsq

dlmnr.bsq

ddst.bsq

dependency$，i_dependency1とi_dependency1の定義は以下の通り（10.2と11.2に相違がない）：

create table dependency$                                 /* dependency table */

( d_obj#        number not null,                  /* dependent object number */

d_timestamp   date not null,   /* dependent object specification timestamp */

order#        number not null,                             /* order number */

p_obj#        number not null,                     /* parent object number */

p_timestamp   date not null,      /* parent object specification timestamp */

d_owner#      number,                           /*  dependent owner number */

property      number not null,                   /* 0x01 = HARD dependency */

/* 0x02 = REF  dependency */

/* 0x04 = FINER GRAINED dependency */

d_attrs       raw(“M_CSIZ”), /* Finer grain attr. numbers if finer grained */

d_reason      raw(“M_CSIZ”))  /* Reason mask of attrs causing invalidation */

storage (initial 10k next 100k maxextents unlimited pctincrease 0)

/

 

create unique index i_dependency1 on

dependency$(d_obj#, d_timestamp, order#)

storage (initial 10k next 100k maxextents unlimited pctincrease 0)

/

 

create index i_dependency2 on

dependency$(p_obj#, p_timestamp)

storage (initial 10k next 100k maxextents unlimited pctincrease 0)

/

次に、どんなブロックを使ったかを探ってみよう：

SYS@lunar>select owner,segment_name,segment_type,extent_id,file_id,block_id from DBA_EXTENTS where segment_name like ‘%DEPENDENCY%’;

 

OWNER           SEGMENT_NAME                   SEGMENT_TYPE        EXTENT_ID    FILE_ID   BLOCK_ID

————— —————————— —————— ———- ———- ———-

SYS             DEPENDENCY$                    TABLE                       0          1        689

SYS             DEPENDENCY$                    TABLE                       1          1       6329

SYS             DEPENDENCY$                    TABLE                       2          1       6665

SYS             DEPENDENCY$                    TABLE                       3          1       6945

SYS             DEPENDENCY$                    TABLE                       4          1       8481

SYS             DEPENDENCY$                    TABLE                       5          1      10505

SYS             DEPENDENCY$                    TABLE                       6          1      12825

SYS             DEPENDENCY$                    TABLE                       7          1      13425

SYS             DEPENDENCY$                    TABLE                       8          1      14753

SYS             DEPENDENCY$                    TABLE                       9          1      15513

SYS             DEPENDENCY$                    TABLE                      10          1      17169

SYS             DEPENDENCY$                    TABLE                      11          1      19209

SYS             DEPENDENCY$                    TABLE                      12          1      19897

SYS             DEPENDENCY$                    TABLE                      13          1      26689

SYS             DEPENDENCY$                    TABLE                      14          1      28153

SYS             DEPENDENCY$                    TABLE                      15          1      29377

SYS             DEPENDENCY$                    TABLE                      16          1      31241

SYS             DEPENDENCY$                    TABLE                      17          1      45321

SYS             DEPENDENCY$                    TABLE                      18          1      51465

SYS             DEPENDENCY$                    TABLE                      19          1      55433

SYSMAN          MGMT_INV_DEPENDENCY_RULE       TABLE                       0          3      24009

SYSMAN          MGMT_METRIC_DEPENDENCY_DEF     TABLE                       0          3      26193

SYSMAN          MGMT_METRIC_DEPENDENCY         TABLE                       0          3      26209

SYSMAN          MGMT_METRIC_DEPENDENCY_DETAILS TABLE                       0          3      26225

SYS             I_DEPENDENCY1                  INDEX                       0          1        929

SYS             I_DEPENDENCY1                  INDEX                       1          1       6313

SYS             I_DEPENDENCY1                  INDEX                       2          1       6777

SYS             I_DEPENDENCY1                  INDEX                       3          1       8905

SYS             I_DEPENDENCY1                  INDEX                       4          1      12185

SYS             I_DEPENDENCY1                  INDEX                       5          1      13433

SYS             I_DEPENDENCY1                  INDEX                       6          1      14761

SYS             I_DEPENDENCY1                  INDEX                       7          1      15537

SYS             I_DEPENDENCY1                  INDEX                       8          1      18425

SYS             I_DEPENDENCY1                  INDEX                       9          1      19273

SYS             I_DEPENDENCY1                  INDEX                      10          1      26705

SYS             I_DEPENDENCY1                  INDEX                      11          1      28297

SYS             I_DEPENDENCY1                  INDEX                      12          1      30609

SYS             I_DEPENDENCY1                  INDEX                      13          1      32297

SYS             I_DEPENDENCY1                  INDEX                      14          1      34353

SYS             I_DEPENDENCY1                  INDEX                      15          1      37129

SYS             I_DEPENDENCY1                  INDEX                      16          1      38665

SYS             I_DEPENDENCY1                  INDEX                      17          1      47113

SYS             I_DEPENDENCY1                  INDEX                      18          1      51721

SYS             I_DEPENDENCY2                  INDEX                       0          1        937

SYS             I_DEPENDENCY2                  INDEX                       1          1       6337

SYS             I_DEPENDENCY2                  INDEX                       2          1       6657

SYS             I_DEPENDENCY2                  INDEX                       3          1       6961

SYS             I_DEPENDENCY2                  INDEX                       4          1       9545

SYS             I_DEPENDENCY2                  INDEX                       5          1      11481

SYS             I_DEPENDENCY2                  INDEX                       6          1      13281

SYS             I_DEPENDENCY2                  INDEX                       7          1      14369

SYS             I_DEPENDENCY2                  INDEX                       8          1      14841

SYS             I_DEPENDENCY2                  INDEX                       9          1      16617

SYS             I_DEPENDENCY2                  INDEX                      10          1      18409

SYS             I_DEPENDENCY2                  INDEX                      11          1      19889

SYS             I_DEPENDENCY2                  INDEX                      12          1      26681

SYS             I_DEPENDENCY2                  INDEX                      13          1      28129

SYS             I_DEPENDENCY2                  INDEX                      14          1      29369

SYS             I_DEPENDENCY2                  INDEX                      15          1      30649

SYS             I_DEPENDENCY2                  INDEX                      16          1      32137

SYS             I_DEPENDENCY2                  INDEX                      17          1      45449

SYS             I_DEPENDENCY2                  INDEX                      18          1      51593

SYS             I_DEPENDENCY2                  INDEX                      19          1      59785

SYSMAN          PK_MGMT_METRIC_DEPENDENCY_DEF  INDEX                       0          3      26201

SYSMAN          PK_MGMT_METRIC_DEPENDENCY      INDEX                       0          3      26217

 

65 rows selected.

 

SYS@lunar>

では10.2でi_dependency1が失効になった場合をシミュレーションする（テスト環境は10.2.0.1）：

[oracle@lunar oracle]$ ss

 

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Dec 22 08:10:07 2014

 

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

 

SYS@lunar>alter index i_dependency1 unusable;

 

Index altered.

 

SYS@lunar>select owner,table_name,index_name,tablespace_name,status from dba_indexes where table_name=’DEPENDENCY$’;

 

OWNER           TABLE_NAME                     INDEX_NAME                     TABLESPACE_NAME                STATUS

————— —————————— —————————— —————————— ——–

SYS             DEPENDENCY$                    I_DEPENDENCY1                  SYSTEM                         UNUSABLE

SYS             DEPENDENCY$                    I_DEPENDENCY2                  SYSTEM                         VALID

 

SYS@lunar>select obj#,flags,to_char(flags,’xxxxxxxxxxxxx’) from ind$ where obj# in (92,122,123);

 

OBJ#      FLAGS TO_CHAR(FLAGS,

———- ———- ————–

122       2051            803   —— 2000+10+40+1=====>/* unusable (dls) : 0x01 ，这里也可以看出来被为unusable了

123       2050            802

 

SYS@lunar>

alter index i_dependency1 unusableを実行して、alert.logの情報は以下の通り：

Mon Dec 22 09:08:37 2014

Index SYS.I_DEPENDENCY1 or some [sub]partitions of the index have been marked unusable

 

[oracle@lunar bdump]$ date

Mon Dec 22 09:11:29 CST 2014

[oracle@lunar bdump]$

ind$のflagsフィールドの定義（その定義がベーステーブル定義を検索できる。10.2でsql.bsq，11.1の後dcore.bsq）：

flags         number not null,

/* mutable flags: anything permanent should go into property */

/* unusable (dls) : 0x01 */

/* analyzed       : 0x02 */

/* no logging     : 0x04 */

/* index is currently being built : 0x08 */

/* index creation was incomplete : 0x10 */

/* key compression enabled : 0x20 */

/* user-specified stats : 0x40 */

/* secondary index on IOT : 0x80 */

/* index is being online built : 0x100 */

/* index is being online rebuilt : 0x200 */

/* index is disabled : 0x400 */   ———注意这里，设置一个index失效的标示（转换成10进制是1024）

/* global stats : 0x800 */

/* fake index(internal) : 0x1000 */

/* index on UROWID column(s) : 0x2000 */

/* index with large key : 0x4000 */

/* move partitioned rows in base table : 0x8000 */

/* index usage monitoring enabled : 0x10000 */

/* 4 bits reserved for bitmap index version : 0x1E0000 */

/* Delayed Segment Creation: 0x4000000 */

以上の結果によって、実際にind$.flagsに対する定義が変わっていないが、内部操作が変更されただけ。
このマークもつかえる。例えばorcacle 9204にSYSのXXXインディクスbugによって、データベースが起動できなくなる場合に、このマークでリカバリできる。
例えば、10g前に、onlineでインディクスを作成したら、中止するにはとってもめんどくさくなるので、この場合にも利用できる。
10.2のあとdbms_repair.online_index_cleanでインディクス状態をクリンアップできるが、その機能は10.2の公式ファイルに記されていない。（desc dbms_repairなら、その機能を見られる）。
Oracleはこれについての説明は以下の通りBug 3965042 : NEW FUNCTION DBMS_REPAIR.ONLINE_INDEX_CLEAN NEEDS TO BE DOCUMENTED）
ALTER TABLE MOVE ONLINE一个IOTテーブルにも似たようなトラブルもある。対応策も同じようになる。

この時、データベースを起動して、ORA-00704 ORA-00604 ORA-01502: index ‘SYS.I_DEPENDENCY1′ or partition of such index is in unusable stateエラが報告される：

SYS@lunar>shutdown abort

ORACLE instance shut down.

SYS@lunar>startup mount

ORACLE instance started.

 

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size              71305460 bytes

Database Buffers           92274688 bytes

Redo Buffers                2973696 bytes

Database mounted.

SYS@lunar>oradebug setmypid

alter session set db_file_multiblocK_read_count=1;

alter session set tracefile_identifier=’lunar’;

Statement processed.

SYS@lunar>

Session altered.

 

SYS@lunar>oradebug event 10046 trace name context forever,level 12;

 

Session altered.

 

SYS@lunar>Statement processed.

SYS@lunar>oradebug tracefile_name

/home/oracle/oracle/product/admin/orcl/udump/orcl_ora_19783_lunar.trc

SYS@lunar>alter database open;

alter database open

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

 

 

SYS@lunar>

alert.logに情報は以下の通り

Mon Dec 22 09:13:29 2014

SMON: enabling cache recovery

Mon Dec 22 09:13:29 2014

ARC2: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

ARC0: Becoming the heartbeat ARCH

ARC2 started with pid=17, OS id=19789

Mon Dec 22 09:13:32 2014

Deleted Oracle managed file /home/oracle/oracle/product/flash_recovery_area/ORCL/archivelog/2009_11_21/o1_mf_1_42_5jg30x9m_.arc

Mon Dec 22 09:13:32 2014

Errors in file /home/oracle/oracle/product/admin/orcl/udump/orcl_ora_19783_lunar.trc:

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-01502: index ‘SYS.I_DEPENDENCY1’ or partition of such index is in unusable state

Mon Dec 22 09:13:32 2014

Error 704 happened during db open, shutting down database

USER: terminating instance due to error 704

Instance terminated by USER, pid = 19783

ORA-1092 signalled during: alter database open…

Traceファイル情報：

PARSING IN CURSOR #5 len=179 dep=1 uid=0 oct=3 lid=0 tim=1385948058756927 hv=2812844157 ad=’29b07834′

select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#

END OF STMT

PARSE #5:c=6999,e=70263,p=8,cr=40,cu=0,mis=1,r=0,dep=1,og=4,tim=1385948058756919

=====================

PARSING IN CURSOR #2 len=84 dep=2 uid=0 oct=3 lid=0 tim=1385948058757830 hv=2686874206 ad=’29b0652c’

select o.name, u.name from obj$ o, user$ u  where o.obj# = :1 and o.owner# = u.user#

END OF STMT

PARSE #2:c=1000,e=266,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1385948058757826

BINDS #2:

kkscoacd

Bind#0

oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0

kxsbbbfp=b72f40bc  bln=22  avl=03  flg=05

value=122  ———》》obj#=122的正是I_DEPENDENCY1

EXEC #2:c=0,e=658,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1385948058758543

WAIT #2: nam=’db file sequential read’ ela= 15160 file#=1 block#=98 blocks=1 obj#=-1 tim=1385948058773774

WAIT #2: nam=’db file sequential read’ ela= 481 file#=1 block#=90 blocks=1 obj#=-1 tim=1385948058774587

FETCH #2:c=2000,e=16086,p=2,cr=5,cu=0,mis=0,r=1,dep=2,og=4,tim=1385948058774648

STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op=’NESTED LOOPS  (cr=5 pr=2 pw=0 time=16090 us)’

STAT #2 id=2 cnt=1 pid=1 pos=1 obj=18 op=’TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=28 us)’

STAT #2 id=3 cnt=1 pid=2 pos=1 obj=36 op=’INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=16 us)’

STAT #2 id=4 cnt=1 pid=1 pos=2 obj=22 op=’TABLE ACCESS CLUSTER USER$ (cr=2 pr=2 pw=0 time=16050 us)’

STAT #2 id=5 cnt=1 pid=4 pos=1 obj=11 op=’INDEX UNIQUE SCAN I_USER# (cr=1 pr=1 pw=0 time=15264 us)’

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-01502: index ‘SYS.I_DEPENDENCY1’ or partition of such index is in unusable state

EXEC #1:c=252962,e=5328707,p=155,cr=730,cu=0,mis=0,r=0,dep=0,og=1,tim=1385948059778098

ERROR #1:err=1092 tim=432829200

ここで、次のアーカイブSQLのエラによって、データベースが起動できなくなるかもしれない：
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs
from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#

ここのバインド変数は122で、I_DEPENDENCY1である。

今データベースを起動して、upgradeし、flags=1024を修正する。（つまり、そのインディクスが無効とマークする）。では、やってみよう：

[oracle@lunar oracle]$ ss

 

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Dec 22 09:22:06 2014

 

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

 

Connected to an idle instance.

 

SYS@lunar>startup mount

ORACLE instance started.

 

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size              71305460 bytes

Database Buffers           92274688 bytes

Redo Buffers                2973696 bytes

Database mounted.

SYS@lunar>oradebug setmypid

Statement processed.

SYS@lunar>alter session set db_file_multiblocK_read_count=1;

 

Session altered.

 

SYS@lunar>alter session set tracefile_identifier=’lunar’;

 

Session altered.

 

SYS@lunar>oradebug event 10046 trace name context forever,level 12;

Statement processed.

SYS@lunar>oradebug tracefile_name

/home/oracle/oracle/product/admin/orcl/udump/orcl_ora_19834_lunar.trc

SYS@lunar>alter database open upgrade;

 

Database altered.

 

SYS@lunar>

alert.log情報は以下の通り：

Mon Dec 22 09:23:02 2014

Successfully onlined Undo Tablespace 1.

Mon Dec 22 09:23:02 2014

SMON: enabling tx recovery

Mon Dec 22 09:23:03 2014

Database Characterset is ZHS16GBK

Mon Dec 22 09:23:06 2014

Stopping background process MMNL

Mon Dec 22 09:23:06 2014

Stopping background process MMON

Starting background process MMON

MMON started with pid=10, OS id=19842

Mon Dec 22 09:23:06 2014

Starting background process MMNL

MMNL started with pid=11, OS id=19844

Mon Dec 22 09:23:06 2014

ALTER SYSTEM enable restricted session;

Mon Dec 22 09:23:07 2014

ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;

Mon Dec 22 09:23:07 2014

ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;

Mon Dec 22 09:23:07 2014

ALTER SYSTEM SET resource_manager_plan=” SCOPE=MEMORY;

replication_dependency_tracking turned off (no async multimaster replication found)

kwqiconfy: Warning AQ Kchunk open notifier failed with 1502

XDB UNINITIALIZED: XDB$SCHEMA not accessible

Completed: alter database open upgrade

1

 

 

 

データベースがアップグレードモードを起動した。先のエラ文を探し出して、アップグレードモードにいると見つけた

1

=====================

PARSING IN CURSOR #4 len=221 dep=2 uid=0 oct=3 lid=0 tim=1385948615586776 hv=1926936385 ad=’29b04538′

select /* ordered use_nl(o) index(d) index(o) */ owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#

END OF STMT

PARSE #4:c=2999,e=3438,p=4,cr=35,cu=0,mis=1,r=0,dep=2,og=4,tim=1385948615586774

=====================

PARSING IN CURSOR #2 len=84 dep=3 uid=0 oct=3 lid=0 tim=1385948615587498 hv=2686874206 ad=’29b0396c’

select o.name, u.name from obj$ o, user$ u  where o.obj# = :1 and o.owner# = u.user#

END OF STMT

PARSE #2:c=0,e=279,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=4,tim=1385948615587494

BINDS #2:

kkscoacd

Bind#0

oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0

kxsbbbfp=b725753c  bln=22  avl=03  flg=05

value=122

EXEC #2:c=1000,e=534,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=4,tim=1385948615588087

FETCH #2:c=0,e=56,p=0,cr=5,cu=0,mis=0,r=1,dep=3,og=4,tim=1385948615588159

STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op=’NESTED LOOPS  (cr=5 pr=0 pw=0 time=60 us)’

STAT #2 id=2 cnt=1 pid=1 pos=1 obj=18 op=’TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=26 us)’

STAT #2 id=3 cnt=1 pid=2 pos=1 obj=36 op=’INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=15 us)’

STAT #2 id=4 cnt=1 pid=1 pos=2 obj=22 op=’TABLE ACCESS CLUSTER USER$ (cr=2 pr=0 pw=0 time=23 us)’

STAT #2 id=5 cnt=1 pid=4 pos=1 obj=11 op=’INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=6 us)’

具体的なSQL:

select /* ordered use_nl(o) index(d) index(o) */ owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs

from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#

dba_objectsを検索し、ORA-00604 ORA-01502: index ‘SYS.I_DEPENDENCY1′ or partition of such index is in unusable stateエラになる。
原因は以上のように、インディクスが壊された。けどOracleは依然としそのインディクスを使うから、エラになる：

SYS@lunar>select owner,object_id,object_name,object_type from dba_objects where object_id in (92,122,123);

select owner,object_id,object_name,object_type from dba_objects where object_id in (92,122,123)

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01502: index ‘SYS.I_DEPENDENCY1’ or partition of such index is in unusable state

SYS@lunar>

では、なぜOracleが起動できるか？
これはOracleがUpgradeモードにいれば、いくつか普通の起動と違った操作がある。
正常にデータベースを起動した時に、アーカイブsqlでOBJ#とUSER#を獲得すると、実行計画はI_DEPENDENCY1でDEPENDENCY1をスキャンする。
インディクスが使えなくなったからデータベースも起動できなくなった。それにupgradeモードでデータベースを起動すると、/* ordered use_nl(o) index(d) index(o) */ hintをつかう。
OBJ#を獲得したときに、oid$を使ってobj#を獲得するから、使えなくなった。SYS.I_DEPENDENCY1インディクスも使っていないから、upgradeモードでデータベースを起動できる。
tkprofでこのアーカイブsqlの実行計画が見られる：

select /* ordered use_nl(o) index(d) index(o) */ owner#,name,namespace,

remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs

from

dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#

 

 

call     count       cpu    elapsed       disk      query    current        rows

——- ——  ——– ———- ———- ———- ———-  ———-

Parse        5      0.00       0.00          0          0          0           0

Execute      0      0.00       0.00          0          0          0           0

Fetch        0      0.00       0.00          0          0          0           0

——- ——  ——– ———- ———- ———- ———-  ———-

total        5      0.00       0.00          0          0          0           0

 

Misses in library cache during parse: 5

Optimizer mode: CHOOSE

Parsing user id: SYS   (recursive depth: 2)

********************************************************************************

 

select obj#

from

oid$ where user#=:1 and oid$=:2

 

 

call     count       cpu    elapsed       disk      query    current        rows

——- ——  ——– ———- ———- ———- ———-  ———-

Parse        1      0.00       0.00          0          0          0           0

Execute      2      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.01          3          5          0           1

——- ——  ——– ———- ———- ———- ———-  ———-

total        5      0.00       0.02          3          5          0           1

 

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: CHOOSE

Parsing user id: SYS   (recursive depth: 1)

 

Rows     Row Source Operation

——-  —————————————————

0  TABLE ACCESS BY INDEX ROWID OID$ (cr=2 pr=2 pw=0 time=7433 us)

0   INDEX UNIQUE SCAN I_OID1 (cr=2 pr=2 pw=0 time=7419 us)(object id 179)

 

 

Elapsed times include waiting on following events:

Event waited on                             Times   Max. Wait  Total Waited

—————————————-   Waited  ———-  ————

db file sequential read                         3        0.00          0.01

oid$の定義は以下の通り：

create table oid$                    /* OID mapping table for schema objects */

(

user#         number not null,   /* user this mapping is for (user$.user#) */

oid$          raw(16) not null,        /* OID for typed table/view or type */

obj#          number not null)         /* target object number (obj$.obj#) */

/* key: (user#, oid$) */

/

ここで以下のようにリカバリする：

[oracle@lunar oracle]$ ss

 

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Dec 22 08:10:07 2014

 

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

 

SYS@lunar>select obj# from obj$ where name=’I_DEPENDENCY1′;

 

OBJ#

———-

122

 

 

SYS@lunar>update ind$ set flags=1024 where obj#=122;

 

1 row updated.

 

SYS@lunar>select flags,obj# from ind$ where obj#=122;

 

FLAGS       OBJ#

———- ———-

1024        122

 

SYS@lunar>commit;

 

Commit complete.

 

SYS@lunar>

 

制約モードでデータベースを再起動する：

SYS@lunar>alter index i_dependency1 rebuild

2  ;

 

Index altered.

 

SYS@lunar>analyze table dependency$ validate structure cascade;

 

Table analyzed.

 

SYS@lunar>select owner,table_name,index_name,tablespace_name,status from dba_indexes where table_name=’DEPENDENCY$’;

 

OWNER           TABLE_NAME                     INDEX_NAME                     TABLESPACE_NAME                STATUS

————— —————————— —————————— —————————— ——–

SYS             DEPENDENCY$                    I_DEPENDENCY1                  SYSTEM                         VALID

SYS             DEPENDENCY$                    I_DEPENDENCY2                  SYSTEM                         VALID

 

SYS@lunar>

ここで、データベースを完璧にリカバリした

 

Filed Under: Oracle, Oracle数据恢复、数据库恢复、灾难恢复

沪ICP备14014813号-2

沪公网安备 31010802001379号