June 11, 2005
使用dbv和RMAN检查数据文件中的坏块
作者:eygle
出处:http://blog.eygle.com
1.使用dbv检查 D:\oradata\eygle>dbv file=EYGLE.DBF blocksize=8192 DBVERIFY: Release 10.1.0.4.0 - Production on 星期六 6月 11 17:36:37 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. DBVERIFY - 开始验证: FILE = EYGLE.DBF 页 219 标记为损坏 Corrupt block relative dba: 0x010000db (file 4, block 219) Bad check value found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x010000db last change scn: 0x0000.0005ee6d seq: 0x2 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xee6d0602 check value in block header: 0x9779 computed block checksum: 0x6141 页 1258 标记为损坏 Corrupt block relative dba: 0x010004ea (file 4, block 1258) Bad check value found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x010004ea last change scn: 0x0000.00042681 seq: 0x2 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x26810602 check value in block header: 0x660b computed block checksum: 0x9317 DBVERIFY - 验证完成 检查的页总数: 1280 处理的页总数 (数据): 150 失败的页总数 (数据): 0 处理的页总数 (索引): 127 失败的页总数 (索引): 0 处理的页总数 (其它): 1001 处理的总页数 (段) : 0 失败的总页数 (段) : 0 空的页总数: 0 标记为损坏的总页数: 2 流入的页总数: 0 Highest block SCN : 428223 (0.428223) |
2.使用RMAN检查坏块
D:\oradata\eygle>rman target /
恢复管理器: 版本9.2.0.6.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
连接到目标数据库: EYGLE (DBID=1365961916)
RMAN> backup validate datafile 4;
启动 backup 于 11-6月 -05
正在使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=17 devtype=DISK
通道 ORA_DISK_1: 正在启动 full 数据文件备份集
通道 ORA_DISK_1: 正在指定备份集中的数据文件
输入数据文件 fno=00004 name=D:\ORADATA\EYGLE\EYGLE.DBF
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:03
完成 backup 于 11-6月 -05
RMAN>
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
4 219 1 0 FRACTURED
4 1258 1 0 FRACTURED
|
.
Posted by eygle at 5:40 PM | Comments (0)
应对RMAN-06026错误，使用dbms_backup_restore进行恢复
作者:eygle
出处:http://blog.eygle.com
昨天做一个实验，结果把数据库搞坏了，当试图进行恢复时居然报了RMAN-06026错误。 回想一下，原来在尝试恢复中使用了_allow_resetlogs_corruption参数，resetlogs之后，Oracle使用当前的控制文件不允许从这个历史备份集中进行恢复。由于我没有使用catalog，所以尝试使用dbms_backup_restore进行恢复。
1.错误信息
我们看到虽然list backup可以显示备份集，但是无法进行恢复，错误为RMAN-06026，RMAN-06026。
[oracle@jumper oradata]$ rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: CONNER (DBID=3152029224)
RMAN> restore database;
Starting restore at 11-JUN-05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/11/2005 01:19:01
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13 Full 1G DISK 00:03:20 09-JUN-05
BP Key: 13 Status: AVAILABLE Tag: TAG20050609T173346
Piece Name: /opt/oracle/product/9.2.0/dbs/0ggmiabq_1_1
SPFILE Included: Modification time: 08-JUN-05
List of Datafiles in backup set 13
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 240560269 09-JUN-05 /opt/oracle/oradata/conner/system01.dbf
2 Full 240560269 09-JUN-05 /opt/oracle/oradata/conner/undotbs01.dbf
3 Full 240560269 09-JUN-05 /opt/oracle/oradata/conner/users01.dbf
RMAN> exit
Recovery Manager complete.
|
2.使用dbms_backup_restore进行恢复
dbms_backup_restore是一个非常强大的package，可以在数据库nomount下使用，用于从备份集中读取各类文件。
本例使用如下脚本:
DECLARE devtype varchar2(256); done boolean; BEGIN devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1'); sys.dbms_backup_restore.restoreSetDatafile; sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'/opt/oracle/oradata/conner/system01.dbf'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'/opt/oracle/oradata/conner/undotbs01.dbf'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'/opt/oracle/oradata/conner/users01.dbf'); sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/opt/oracle/product/9.2.0/dbs/0ggmiabq_1_1', params=>null); sys.dbms_backup_restore.deviceDeallocate; END; / |
3.执行恢复
[oracle@jumper conner]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production on Sat Jun 11 01:24:34 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area 101782828 bytes Fixed Size 451884 bytes Variable Size 37748736 bytes Database Buffers 62914560 bytes Redo Buffers 667648 bytes SQL> DECLARE 2 devtype varchar2(256); 3 done boolean; 4 BEGIN 5 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1'); 6 sys.dbms_backup_restore.restoreSetDatafile; 7 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'/opt/oracle/oradata/conner/system01.dbf'); 8 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'/opt/oracle/oradata/conner/undotbs01.dbf'); 9 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'/opt/oracle/oradata/conner/users01.dbf'); 10 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/opt/oracle/product/9.2.0/dbs/0ggmiabq_1_1', params=>null); 11 sys.dbms_backup_restore.deviceDeallocate; 12 END; 13 / PL/SQL procedure successfully completed. SQL> |
至此，从备份集中读取文件完毕。
4.恢复控制文件
由于大意，也没有备份控制文件，所以只好重建控制文件。
SQL> alter database mount; Database altered. SQL> alter database backup controlfile to trace; Database altered. |
找到trace文件，编辑、执行重建控制文件需要部分:
[oracle@jumper oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Jun 11 01:30:50 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 101782828 bytes
Fixed Size 451884 bytes
Variable Size 37748736 bytes
Database Buffers 62914560 bytes
Redo Buffers 667648 bytes
SQL> set echo on
SQL> @ctl
SQL>
SQL> CREATE CONTROLFILE REUSE DATABASE "CONNER" RESETLOGS ARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 5
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 1361
8 LOGFILE
9 GROUP 1 '/opt/oracle/oradata/conner/redo01.log' SIZE 10M,
10 GROUP 2 '/opt/oracle/oradata/conner/redo02.log' SIZE 10M,
11 GROUP 3 '/opt/oracle/oradata/conner/redo03.log' SIZE 10M
12 -- STANDBY LOGFILE
13 DATAFILE
14 '/opt/oracle/oradata/conner/system01.dbf',
15 '/opt/oracle/oradata/conner/undotbs01.dbf',
16 '/opt/oracle/oradata/conner/users01.dbf'
17 CHARACTER SET ZHS16GBK
18 ;
Control file created.
|
5.执行恢复
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 240560269 generated at 06/09/2005 17:33:48 needed for thread 1
ORA-00289: suggestion : /opt/oracle/oradata/conner/archive/1_7.dbf
ORA-00280: change 240560269 for thread 1 is in sequence #7
Specify log: { |
至此恢复完毕。
Posted by eygle at 9:01 AM | Comments (19)
如何使用Oracle全文检索功能?
作者:eygle
出处:http://blog.eygle.com
本文简要说明全文检索功能的使用，不做太多的具体说明。仅供参考使用方法。
1.授权
对具体使用全文检索的用户授权.
$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production on Sun May 15 20:00:10 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production SQL> grant ctxapp to club; Grant succeeded. SQL> connect ctxsys/password Connected. SQL> grant execute on ctx_ddl to club; Grant succeeded. |
2.使用应用用户连接
创建域索引
SQL> connect club/password
Connected.
SQL> begin ctx_ddl.create_preference('club_lexer','chinese_vgram_lexer'); end;
2 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
SQL> create index id_cont_msg
2 on jivemessage ( SUBJECT ) indextype is ctxsys.context parameters('lexer club_lexer');
Index created.
Elapsed: 00:01:22.42
SQL> select count(*) from jivemessage where contains(subject,'幸福')>0;
COUNT(*)
----------
1847
Elapsed: 00:00:00.17
SQL> select count(*) from jivemessage where subject like '%幸福%';
COUNT(*)
----------
1841
Elapsed: 00:00:02.89
|
3.检索比较
以下是一个全文检索的示例，我们检索'abc'，那么全文检索可以把包含'abc'的任何变化组合都查询出来，包括大小写、全半角，而如果用单纯的SQL实现同样的结果，则需要多次like之后union all，其性能差距是不可想象的。
全文检索在类似文本模糊查询上具有极大的优势。当然也可能存在问题，即词法分析器是否准确，会否漏掉部分内容等。
总的说来，满足需求的技术就是好的技术，是否使用一项技术应该通过实践来检验.<br>
SQL> select subject from jivemessage where contains(subject,'abc') >0; SUBJECT -------------------------------------------------------------------------------- 【游戏】 把你的名字的首字母用智能ABC打出，看能出来什么？ 游戏——把你的名字首字母用智能ABC打出来 智能ABC暗藏杀机 ABC ABC ABC ABC ABC ＡＢＣ 哈哇abc abc SUBJECT -------------------------------------------------------------------------------- abc ABC 振奋爱的激情方案ABC 智能ABC的错吗？ ABC全选 瓜果美容ABC 经典英文歌曲ABC，不好你拿版砖砍我，好就回帖顶一下！ 初夜abc Re: abc Re: abc Re: abc SUBJECT -------------------------------------------------------------------------------- abc 测试发贴""abc abc 25 rows selected. Elapsed: 00:00:00.04 |
3.定时优化同步域索引
创建定时任务，定期优化和同步域索引
SQL> create or replace procedure hsp_sync_index as
2 begin
3 ctx_ddl.sync_index('id_cont_msg');
4 end;
5 /
Procedure created.
Elapsed: 00:00:00.08
SQL> VARIABLE jobno number;
SQL> BEGIN
2 DBMS_JOB.SUBMIT(:jobno,'hsp_sync_index();',
3 SYSDATE, 'SYSDATE + (1/24/4)');
4 commit;
5 END;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.27
SQL> create or replace procedure hsp_optimize_index as
2 begin
3 ctx_ddl.optimize_index('id_cont_msg','FULL');
4 end;
5 /
SQL> VARIABLE jobno number;
SQL> BEGIN
2 DBMS_JOB.SUBMIT(:jobno,'hsp_optimize_index();',
3 SYSDATE, 'SYSDATE + 1');
4 commit;
5 END;
6 /
Procedure created.
Elapsed: 00:00:00.03
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
SQL>
|
Posted by eygle at 12:33 AM | Comments (5)
如何手工安装Oracle全文检索工具?
作者:eygle
出处:http://blog.eygle.com
在Oracle9i Rlease2中，Oracle的全文检索技术被称为:Oracle Text，功能十分强大。Oracle Text是Oracle9i采用的新名称，在Oracle8/8i中它被称作Oracle interMedia Text，在Oracle8以前它的名称是Oracle ConText Cartridge。Oracle Text组件可以在安装数据库的时候选择，缺省是安装的，如果没有安装，那么可以按照以下方式手动安装Oracle Text。
1.创建存储表空间
$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production on Sun May 15 19:54:48 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /h2love/oracle/system01.dbf /h2love/oracle/undotbs01.dbf /h2love/oracle/users01.dbf ... 9 rows selected. SQL> create tablespace oratext 2 datafile '/h2love/oracle/oratext01.dbf' size 100m 3 extent management local uniform size 128k 4 ; Tablespace created. |
2.创建相关对象
SQL> spool text.log
SQL> connect sys/oracleHURRAY as sysdba
Connected.
SQL> start ?/ctx/admin/dr0csys password oratext temp
...creating user CTXSYS
...creating role CTXAPP
SQL> connect ctxsys/password
Connected.
SQL> start ?/ctx/admin/dr0inst ?/ctx/lib/libctxx9.so
============== ConText Database Objects Installation ==============
This script must be run as CTXSYS. This script will exit
below if run as any other user.
User is CTXSYS
... creating tables and Oracle object types
... creating table dr$parameter
... creating table dr$class
... creating table dr$object
... creating table dr$object_attribute
... creating table dr$object_attribute_lov
... creating table dr$preference
... creating table dr$preference_value
... creating table dr$index
... creating table dr$index_partition
... creating table dr$index_value
... creating table dr$policy_tab
... creating table dr$sqe
... creating table dr$ths
... creating table dr$ths_phrase
... creating table dr$ths_fphrase
... creating table dr$ths_bt
... creating table dr$section_group
... creating table dr$section
... creating table dr$stoplist
... creating table dr$stopword
... creating table dr$sub_lexer
... creating table dr$index_set
... creating table dr$index_set_index
... creating table dr$server
... creating table dr$pending
... creating table dr$waiting
... creating table dr$online_pending
... creating table dr$delete
... creating table dr$unindexed
... creating table dr$index_error
... creating table dr$parallel
... creating table dr$stats
... creating table dr$part_stats
... creating named data type ctx_feedback_item_type
... creating named data type ctx_feedback_type
... creating safe callout library
... creating CONTEXT interface
drop public synonym contains
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
drop public synonym score
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
... creating CTXCAT interface
drop public synonym catsearch
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
... creating CTXRULE interface
drop public synonym matches
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
... creating CTXXPATH interface
... loading package headers
================== Package Installation ==========================
Install Global Symbols
... loading driobj.pkh
No errors.
... loading dr0def.pkh
No errors.
...loading drig.pkh
No errors.
Install DR Internal package specs
... loading driutl.pkh
No errors.
... loading driacc.pkh
No errors.
... loading driadm.pkh
No errors.
... loading dricon.pkh
No errors.
... loading dridisp.pkh
No errors.
... loading dridml.pkh
No errors.
... loading dridoc.pkh
No errors.
... loading drierr.pkh
No errors.
... loading driddl.pkh
No errors.
... loading driddlp.pkh
No errors.
... loading driddlc.pkh
No errors.
... loading driddlr.pkh
No errors.
... loading driddlx.pkh
No errors.
... loading drilist.pkh
No errors.
... loading driload.pkh
No errors.
... loading driopt.pkh
No errors.
... loading dripipe.pkh
No errors.
... loading dripref.pkh
No errors.
... loading drirec.pkh
No errors.
... loading drirep.pkh
No errors.
... loading drirepm.pkh
No errors.
... loading drireps.pkh
No errors.
... loading drirept.pkh
No errors.
... loading drirepz.pkh
No errors.
... loading driths.pkh
No errors.
... loading drithsc.pkh
No errors.
... loading drithsd.pkh
No errors.
... loading drithsl.pkh
No errors.
... loading drithsx.pkh
No errors.
... loading drival.pkh
No errors.
... loading driexp.pkh
No errors.
... loading driimp.pkh
No errors.
... loading driparse.pkh
No errors.
... loading drixtab.pkh
No errors.
... loading drixtabc.pkh
No errors.
... loading drixtabr.pkh
No errors.
... loading drixtabx.pkh
No errors.
Install ConText public API specs
... loading dr0adm.pkh
No errors.
... loading dr0ddl.pkh
No errors.
... loading dr0doc.pkh
No errors.
... loading dr0out.pkh
No errors.
... loading dr0query.pkh
No errors.
... loading dr0thes.pkh
No errors.
... loading dr0repor.pkh
No errors.
... loading dr0ulex.pkh
No errors.
... loading dr0cls.pkh
No errors.
... loading package bodies
================== Package Installation ==========================
Install DR Internal package bodies
... loading driacc.plb
No errors.
... loading driadm.plb
No errors.
... loading dricon.plb
No errors.
... loading dridisp.plb
No errors.
... loading dridml.plb
No errors.
... loading dridoc.plb
No errors.
... loading drierr.plb
No errors.
... loading driddl.plb
No errors.
... loading driddlp.plb
No errors.
... loading driddlc.plb
No errors.
... loading driddlr.plb
No errors.
... loading driddlx.plb
No errors.
... loading drilist.plb
No errors.
... loading driload.plb
No errors.
... loading dripipe.plb
No errors.
... loading driopt.plb
No errors.
... loading dripref.plb
No errors.
... loading drirec.plb
No errors.
... loading drirep.plb
No errors.
... loading drirepm.plb
No errors.
... loading drireps.plb
No errors.
... loading drirept.plb
No errors.
... loading drirepz.plb
No errors.
... loading driths.plb
No errors.
... loading drithsc.plb
No errors.
... loading drithsd.plb
No errors.
... loading drithsl.plb
No errors.
... loading drithsx.plb
No errors.
... loading driutl.plb
No errors.
... loading drival.plb
No errors.
... loading driexp.plb
No errors.
... loading driimp.plb
No errors.
... loading driparse.plb
No errors.
... loading drixtab.plb
No errors.
... loading drixtabc.plb
No errors.
... loading drixtabr.plb
No errors.
... loading drixtabx.plb
No errors.
... loading driproc.plb
No errors.
Install ConText public API bodies
... loading dr0adm.plb
No errors.
... loading dr0ddl.plb
No errors.
... loading dr0doc.plb
No errors.
... loading dr0out.plb
No errors.
... loading dr0query.plb
No errors.
... loading dr0thes.plb
No errors.
... loading dr0repor.plb
No errors.
... loading dr0cls.plb
No errors.
========================================================
... creating CONTEXT interface body
No errors.
No errors.
... creating CTXCAT interface body
No errors.
... creating CTXRULE interface body
No errors.
... creating CTXXPATH interface body
No errors.
... creating CONTEXT index type
drop public synonym context
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
... creating CTXCAT index type
drop public synonym ctxcat
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
... creating CTXRULE index type
drop public synonym ctxrule
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
... creating CTXXPATH index type
drop public synonym ctxxpath
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
... creating objects
Removing old object definitions...
Creating new object definitions...
...creating default preferences
Create default preferences
System Parameters
========================================================
SQL> start ?/ctx/admin/defaults/drdefus.sql;
Creating lexer preference...
Creating wordlist preference...
Creating stoplist...
Creating default policy...
SQL> spool off
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
|
请注意如果漏掉drdefus.sql脚本，使用过程中将会出现以下类似错误：
ERROR atline 1: ORA-29855: error occurred in the execution of ODCIINDEXCREATEroutine ORA-20000: interMedia Text error: DRG-10700: preference does notexist: CTXSYS.DEFAULT_LEXER ORA-06512: at "CTXSYS.DRUE", line 126 ORA-06512:at "CTXSYS.TEXTINDEXMETHODS", line 54 ORA-06512: at line 1这样就完成了手工安装全文检索工具。
Posted by eygle at 12:17 AM | Comments (6)