Jimmy He – 第 35 页

9i和10g的plan table

2009-02-12 没有评论

在oracle 10g中,plan table的基表已经改变，在9i的时候，还是一个table类型，在10g的时候，已经是temporary table类型了。在10g中，如果通过dblink访问9i的库，那么explain table将无法显示。会报错： [crayon-67bc0002bad8 […]

Study note

将global的索引改成分区索引

2009-02-07 一条评论

今天有人在QQ上问，如何将global的索引改成分区索引？由于在同一个列上不能建不同名的索引，不然会报错：

SQL> create index ind_tab2_1 on tab2(a);

Index created.

Elapsed: 00:00:01.20
SQL>

SQL> create index ind_tab2_pindex on tab2(a)
  2  local (partition ip1,partition ip2,partition ip3);
create index ind_tab2_pindex on tab2(a)
                                     *
ERROR at line 1:
ORA-01408: such column list already indexed

1

2

3

4

5

6

7

8

9

10

11

12

13

SQL> create index ind_tab2_1 on tab2(a);

Index created.

Elapsed: 00:00:01.20

SQL>

SQL> create index ind_tab2_pindex on tab2(a)

2 local (partition ip1,partition ip2,partition ip3);

create index ind_tab2_pindex on tab2(a)

*

ERROR at line 1:

ORA-01408: such column list already indexed

除了删除重建索引外，我们其实可以用在线重定义的方法来重构索引。下面是一个例子： 1、原表和其索引的建立： [crayon-6 […]

It's my life

公司DBA团队博客开张！

2009-02-06 一条评论

公司dba团队博客终于华丽丽的闪亮登场！访问地址是：http://www.aspire-dba.com 订阅地址是：http://www.aspire-dba.com/feed/ 博客嘛，本来就是分享交流的地方，欢迎大家前去捧场交流！建这个博客，主要也是想整合我们公司dba团队的技术力量，提供一 […]

Study note

ctas为何报错不能扩展temp segment

2009-01-25 没有评论

在一个表空间上建表的时候，报错了：

SQL> create tablespace tbs_test datafile '/oracle/tbs_test.dbf' size 2m;
Tablespace created.
SQL> create table test.t2 tablespace tbs_test as select * from dba_objects;
create table test.t2 tablespace tbs_test as select * from dba_objects
                                                          *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TBS_TEST

1

2

3

4

5

6

7

SQL> create tablespace tbs_test datafile '/oracle/tbs_test.dbf' size 2m;

Tablespace created.

SQL> create table test.t2 tablespace tbs_test as select * from dba_objects;

create table test.t2 tablespace tbs_test as select * from dba_objects

*

ERROR at line 1:

ORA-01652: unable to extend temp segment by 128 in tablespace TBS_TEST

在这边比较奇怪，为什么是不能扩展temp segment，而不是报错：

ORA-01653: unable to extend table TEST.T2 by 128 in tablespace TBS_TEST

1	ORA-01653: unable to extend table TEST.T2 by 128 in tablespace TBS_TEST

老白说，这是因为CTAS的时候，创建的表的BLOCK首先被标 […]

..experience, Working case

用_minimum_giga_scn解决无法启动的数据库

2009-01-22 4条评论

今天遇到一个数据库无法启动，看到alertlog中主要是ora 600和[2662]的报错：

SQL> startup
ORACLE instance started.

Total System Global Area 1076850392 bytes
Fixed Size                   736984 bytes
Variable Size             536870912 bytes
Database Buffers          536870912 bytes
Redo Buffers                2371584 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced


SQL>

1

2

3

4

5

6

7

8

9

10

11

12

13

SQL> startup

ORACLE instance started.

Total System Global Area 1076850392 bytes

Fixed Size 736984 bytes

Variable Size 536870912 bytes

Database Buffers 536870912 bytes

Redo Buffers 2371584 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

SQL>

其中alertlog中报错：

Completed: ALTER DATABASE   MOUNT
Thu Jan 22 13:05:08 2009
ALTER DATABASE OPEN
Thu Jan 22 13:05:09 2009
Beginning crash recovery of 1 threads
Thu Jan 22 13:05:09 2009
Started first pass scan
Thu Jan 22 13:05:09 2009
Completed first pass scan
 0 redo blocks read, 0 data blocks need recovery
Thu Jan 22 13:05:09 2009
Started recovery at
 Thread 1: logseq 2, block 3, scn 0.43536037
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
  Mem# 0 errs 0: /oracle/oradata/ora9i/redo01.log
Thu Jan 22 13:05:09 2009
Ended recovery at
 Thread 1: logseq 2, block 3, scn 0.43556038
 0 data blocks read, 0 data blocks written, 0 redo blocks read
Crash recovery completed successfully
Thu Jan 22 13:05:10 2009
Thread 1 advanced to log sequence 3
Thread 1 opened at log sequence 3
  Current log# 2 seq# 3 mem# 0: /oracle/oradata/ora9i/redo02.log
Successful open of redo thread 1.
Thu Jan 22 13:05:10 2009
SMON: enabling cache recovery
Thu Jan 22 13:05:10 2009
Errors in file /oracle/admin/ora9i/udump/ora9i_ora_12968.trc:
ORA-00600: internal error code, arguments: [2662], [0], [43556042], [261], [2396789971], [4194729], [], []

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

Completed: ALTER DATABASE MOUNT

Thu Jan 22 13:05:08 2009

ALTER DATABASE OPEN

Thu Jan 22 13:05:09 2009

Beginning crash recovery of 1 threads

Thu Jan 22 13:05:09 2009

Started first pass scan

Thu Jan 22 13:05:09 2009

Completed first pass scan

0 redo blocks read, 0 data blocks need recovery

Thu Jan 22 13:05:09 2009

Started recovery at

Thread 1: logseq 2, block 3, scn 0.43536037

Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0

Mem# 0 errs 0: /oracle/oradata/ora9i/redo01.log

Thu Jan 22 13:05:09 2009

Ended recovery at

Thread 1: logseq 2, block 3, scn 0.43556038

0 data blocks read, 0 data blocks written, 0 redo blocks read

Crash recovery completed successfully

Thu Jan 22 13:05:10 2009

Thread 1 advanced to log sequence 3

Thread 1 opened at log sequence 3

Current log# 2 seq# 3 mem# 0: /oracle/oradata/ora9i/redo02.log

Successful open of redo thread 1.

Thu Jan 22 13:05:10 2009

SMON: enabling cache recovery

Thu Jan 22 13:05:10 2009

Errors in file /oracle/admin/ora9i/udump/ora9i_ora_12968.trc:

ORA-00600: internal error code, arguments: [2662], [0], [43556042], [261], [2396789971], [4194729], [], []

遇到ora-600和2662的问题，我们一般 […]

..experience, Working case

flashback_on被自动改回no

2009-01-21 没有评论

oracle对flashback log采用了自动管理的方式，并且不允许手工去删除flashback log。当flashback log写满时，会自动的覆盖掉最早的一个flashback日志。并且值得注意的是，如果手工删除了flashback log，数据库将自动把flashback设置成no！ […]

..experience, Working case

HP的查看打开文件数工具:crashinfo

2009-01-17 5条评论

一天，在一个业务高峰期，某省的一个数据库突然宕机了，根据alertlog中发现的报错：

Mon Jan 12 09:49:42 2009
ARC0: Evaluating archive   log 5 thread 1 sequence 142032
ARC0: Beginning to archive log 5 thread 1 sequence 142032
Creating archive destination LOG_ARCHIVE_DEST_1: '/oraarchlog/arch_1_142032.arc'
Mon Jan 12 09:49:56 2009
ARC0: Completed archiving  log 5 thread 1 sequence 142032
Mon Jan 12 09:57:57 2009
Errors in file /oracle/app/oracle/admin/orasid/bdump/orasid_lgwr_8267.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/dev/vg_ora3/rredo_256m_21'
ORA-27041: unable to open file
HP-UX Error: 23: File table overflow
Additional information: 2
ORA-00312: online log 1 thread 1: '/dev/vg_ora2/rredo_256m_11'
ORA-27041: unable to open file
HP-UX Error: 23: File table overflow
Additional information: 2
ORA-00312: online log 1 thread 1: '/dev/vg_ora1/rredo_256m_01'
ORA-27041: unable to open file
HP-UX Error: 23: File table overflow
Additional information: 2
Mon Jan 12 09:57:57 2009
Errors in file /oracle/app/oracle/admin/orasid/bdump/orasid_lgwr_8267.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/dev/vg_ora3/rredo_256m_21'
ORA-27041: unable to open file
HP-UX Error: 23: File table overflow
Additional information: 2
ORA-00312: online log 1 thread 1: '/dev/vg_ora2/rredo_256m_11'
ORA-27041: unable to open file
HP-UX Error: 23: File table overflow
Additional information: 2

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

Mon Jan 12 09:49:42 2009

ARC0: Evaluating archive log 5 thread 1 sequence 142032

ARC0: Beginning to archive log 5 thread 1 sequence 142032

Creating archive destination LOG_ARCHIVE_DEST_1: '/oraarchlog/arch_1_142032.arc'

Mon Jan 12 09:49:56 2009

ARC0: Completed archiving log 5 thread 1 sequence 142032

Mon Jan 12 09:57:57 2009

Errors in file /oracle/app/oracle/admin/orasid/bdump/orasid_lgwr_8267.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/dev/vg_ora3/rredo_256m_21'

ORA-27041: unable to open file

HP-UX Error: 23: File table overflow

Additional information: 2

ORA-00312: online log 1 thread 1: '/dev/vg_ora2/rredo_256m_11'

ORA-27041: unable to open file

HP-UX Error: 23: File table overflow

Additional information: 2

ORA-00312: online log 1 thread 1: '/dev/vg_ora1/rredo_256m_01'

ORA-27041: unable to open file

HP-UX Error: 23: File table overflow

Additional information: 2

Mon Jan 12 09:57:57 2009

Errors in file /oracle/app/oracle/admin/orasid/bdump/orasid_lgwr_8267.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/dev/vg_ora3/rredo_256m_21'

ORA-27041: unable to open file

HP-UX Error: 23: File table overflow

Additional information: 2

ORA-00312: online log 1 thread 1: '/dev/vg_ora2/rredo_256m_11'

ORA-27041: unable to open file

HP-UX Error: 23: File table overflow

Additional information: 2

看来是打开文件数太多，导致了此次数据库宕机。通过监控打开文件数发现，其实在前一天的时候，打开文件数已经到98%，在上午8点多的时候，打开文件数已 […]

..experience, Working case

裸设备的划分

2009-01-08 没有评论

在三少的blog上看到了这篇《spfile啥都没了》。在这边和大家分享一下裸设备的划分的经验。 1.vg的属性建议用root。因为vg的宿主如果是oracle，那么在create tablespace的时候，如果没有找对正确的lv，比如我的lv名字是\dev\vg_ora1\aaa，那么在creat […]

..experience, Working case

修改sys密码导致legato无法正常备份

2009-01-06 没有评论

今天接到说某省的legato备份无法执行，查看legato的monitor窗口，发现没有明显报错，直接就出备份fail了信息了。由于monitor中说“……Hostname(s) Unresolved,1 Failed,1 Succeeded(xj_db Failed)”，一开始是怀疑hostna […]

..experience, Working case

基于catalog库进行部分表空间的还原

2009-01-04 7条评论

应某省的局方要求，在双机热备的备机上，将主机数据库的system、undo、temp和某个应用的表空间DATA_STATIC进行还原。环境如下：（1）HA主机：xj_db01 （2）HA备机：xj_db02 （3）备份软件：LEGATO 且legato的备份脚本为： [crayon-67bc00 […]

..experience, Working case