..experience – 第 13 页

9i catalog的一个bug

2013-11-22 没有评论

一个rman的catalog库，是9i的版本。在连接这个库做backup的时候，能正常完成，但是在做restore的时候，就报错了：

ecovery Manager: Release 9.2.0.8.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

RMAN>
executing command: SET DBID

RMAN>
connected to target database:  (not mounted)

RMAN>
connected to recovery catalog database

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22>
allocated channel: ch1
channel ch1: sid=11 devtype=SBT_TAPE
channel ch1: Veritas NetBackup for Oracle - Release 7.1 (2011120716)

allocated channel: ch2
channel ch2: sid=12 devtype=SBT_TAPE
channel ch2: Veritas NetBackup for Oracle - Release 7.1 (2011120716)

allocated channel: ch3
channel ch3: sid=13 devtype=SBT_TAPE
channel ch3: Veritas NetBackup for Oracle - Release 7.1 (2011120716)

allocated channel: ch4
channel ch4: sid=14 devtype=SBT_TAPE
channel ch4: Veritas NetBackup for Oracle - Release 7.1 (2011120716)

allocated channel: ch5
channel ch5: sid=15 devtype=SBT_TAPE
channel ch5: Veritas NetBackup for Oracle - Release 7.1 (2011120716)

allocated channel: ch6
channel ch6: sid=16 devtype=SBT_TAPE
channel ch6: Veritas NetBackup for Oracle - Release 7.1 (2011120716)

allocated channel: ch7
channel ch7: sid=17 devtype=SBT_TAPE
channel ch7: Veritas NetBackup for Oracle - Release 7.1 (2011120716)

allocated channel: ch8
channel ch8: sid=18 devtype=SBT_TAPE
channel ch8: Veritas NetBackup for Oracle - Release 7.1 (2011120716)

sent command to channel: ch1
sent command to channel: ch2
sent command to channel: ch3
sent command to channel: ch4
sent command to channel: ch5
sent command to channel: ch6
sent command to channel: ch7
sent command to channel: ch8

Starting restore at 2013-10-28 18:35:47

released channel: ch1
released channel: ch2
released channel: ch3
released channel: ch4
released channel: ch5
released channel: ch6
released channel: ch7
released channel: ch8
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/28/2013 18:35:48
RMAN-06004: ORACLE error from recovery catalog database: ORA-01455: converting column overflows integer datat
ype

RMAN>
Recovery Manager complete.

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

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

ecovery Manager: Release 9.2.0.8.0 - 64bit Production

RMAN>

executing command: SET DBID

RMAN>

connected to target database: (not mounted)

RMAN>

connected to recovery catalog database

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22>

allocated channel: ch1

channel ch1: sid=11 devtype=SBT_TAPE

channel ch1: Veritas NetBackup for Oracle - Release 7.1 (2011120716)

allocated channel: ch2

channel ch2: sid=12 devtype=SBT_TAPE

channel ch2: Veritas NetBackup for Oracle - Release 7.1 (2011120716)

allocated channel: ch3

channel ch3: sid=13 devtype=SBT_TAPE

channel ch3: Veritas NetBackup for Oracle - Release 7.1 (2011120716)

allocated channel: ch4

channel ch4: sid=14 devtype=SBT_TAPE

channel ch4: Veritas NetBackup for Oracle - Release 7.1 (2011120716)

allocated channel: ch5

channel ch5: sid=15 devtype=SBT_TAPE

channel ch5: Veritas NetBackup for Oracle - Release 7.1 (2011120716)

allocated channel: ch6

channel ch6: sid=16 devtype=SBT_TAPE

channel ch6: Veritas NetBackup for Oracle - Release 7.1 (2011120716)

allocated channel: ch7

channel ch7: sid=17 devtype=SBT_TAPE

channel ch7: Veritas NetBackup for Oracle - Release 7.1 (2011120716)

allocated channel: ch8

channel ch8: sid=18 devtype=SBT_TAPE

channel ch8: Veritas NetBackup for Oracle - Release 7.1 (2011120716)

sent command to channel: ch1

sent command to channel: ch2

sent command to channel: ch3

sent command to channel: ch4

sent command to channel: ch5

sent command to channel: ch6

sent command to channel: ch7

sent command to channel: ch8

Starting restore at 2013-10-28 18:35:47

released channel: ch1

released channel: ch2

released channel: ch3

released channel: ch4

released channel: ch5

released channel: ch6

released channel: ch7

released channel: ch8

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 10/28/2013 18:35:48

RMAN-06004: ORACLE error from recovery catalog database: ORA-01455: converting column overflows integer datat

ype

RMAN>

Recovery Manager complete.

根据RMAN-06004和ORA-01455，我们很容易就能发现9i catalog库的一个bug： […]

..experience, Working case

DBMS_AUDIT_MGMT的一些小结

2013-11-14 没有评论

当设置了DBMS_AUDIT_MGMT.init_cleanup之后，aud$的相关行为会发生一些变化。如原来设置了by session的审计，在进行初始化后，by session的记录变得像by access一样，或者更准确的说，变得像11g的行为一样。（详见下面的Q5）在11g之前，by se […]

..experience, Working case

HP的进程优先级和cursor pin S

2013-11-02 没有评论

进程的优先级往往会不可思议的影响数据库的性能，在很多时候，可能突发性的（持续时间只有几秒钟到几分钟）、很难确认原因的latch，都和进程优先级有关。下面就是这样一个例子，在没有正确设置进程优先级的情况下，客户的系统常常时不时的出现高CPU的情况，持续时间几分钟，且伴随着cursor pin S的等待 […]

..experience, Working case

当scheduler超过时间窗口时停止job

2013-10-24 没有评论

要在scheduler的window结束的时候，也让job停止，需要对job加’stop_on_window_close’的属性。测试如下： 1.建立测试表t1：

drop table t1;
 
create table t1 as select 'First record at: '||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as mycol from dual;

1

2

3

drop table t1;

create table t1 as select 'First record at: '||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as mycol from dual;

2. 建立需要定期执行的存储过程： [cra […]

..experience, Working case

impdp时报错Ora-28031

2013-08-14 没有评论

今天一客户说在做impdp导入时，报错Ora-28031。我们看看ora-28031的报错是什么意思：

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-31625: Schema SYS is needed to import this object, but is unaccessible
ORA-28031: maximum of 148 enabled roles exceeded

1

2

3

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-31625: Schema SYS is needed to import this object, but is unaccessible

ORA-28031: maximum of 148 enabled roles exceeded

从报错信息看，是SYS用户的role超过148个，一开始，我们用

select count(*) from dba_role_privs where grantee = 'SYS';

1	select count(*) from dba_role_privs where grantee = 'SYS';

[…]

..experience, Working case

runInstaller的debug

2013-07-28 没有评论

收到一个请求，客户说安装11.2.0.2 的RAC数据库的时候，到prerequisite check这一步报错PRVF-5300和PRVF-5434。我们可以运行下面的命令来追踪：

./runInstaller -debug -logLevel finest >inst1.out 2>inst2.out

1	./runInstaller -debug -logLevel finest >inst1.out 2>inst2.out

运行debug后，我们还在OUI中操作，到了pr […]

..experience, Working case

谈谈死事务的回滚

2013-07-24 没有评论

死事务，一般是事务正在跑的时候，被kill掉了，或者数据库shutdown abort了，那么当数据库再次启动的时候，这些事务就需要做回滚。可以通过下面的语句查到回滚的事务：

select * from v$fast_start_transactions;
或者
select * from x$ktuxe where KTUXECFL='DEAD' AND KTUXESTA!='INACTIVE'

1

2

3

select * from v$fast_start_transactions;

或者

select * from x$ktuxe where KTUXECFL='DEAD' AND KTUXESTA!='INACTIVE'

根据上面的语句，我们可以查到事务的undo的segmen […]

..experience, Working case

大量会话处于CSS initialization等待

2013-07-23 2条评论

接到一个问题，客户的某个系统突然CPU冲高，一个小时内CPU从5%冲到60%以上，在数据库中发现大量的会话在等待CSS initialization。该系统是非RAC非ASM的系统，一般来说，不会出现CSS（cluster synchronization service）的等待，只有在访问voti […]

..experience, It's my life, Working case

安装cluster时报错 PRVF-5636

2013-07-10 没有评论

之前写过一篇为rac建dns server的文章，今天在安装11.2.0.3的grid的时候报错 PRVF-5636 : The DNS response time for an unreachable node exceeded “15000” ms on followin […]

..experience, Working case

记一次cursor pin s wait on X的处理

2013-07-05 没有评论

今天遇到个问题，客户说某天的11:45开始，系统遇到了大量的cursor pin s wait on X，经历一个小时后自动消失，需要查找原因。这报错一般是某个会话需要申请S模式的mutex，而mutex被其他会话以X模式占有了。查holder也很容易，11g版本前看p2raw的前8位，将16进制 […]

..experience, Working case

在异常捕获时显示全部的堆栈信息

2013-07-02 没有评论

今天收到一个请求，问如何才能看到异常时所有的信息，其实我们可以测试一把：

CREATE OR REPLACE PROCEDURE proc1 IS
BEGIN
   DBMS_OUTPUT.put_line ('running proc1');
   RAISE NO_DATA_FOUND;
END;
/



CREATE OR REPLACE PROCEDURE proc2 IS
   l_str VARCHAR2 (30) := 'calling proc1';
BEGIN
   DBMS_OUTPUT.put_line (l_str);
   proc1;
END;
/



CREATE OR REPLACE PROCEDURE proc3_a IS
BEGIN
   DBMS_OUTPUT.put_line ('calling proc2');
   proc2;
EXCEPTION
   WHEN OTHERS
   THEN
     DBMS_OUTPUT.put_line ('Error stack at top level:');
       DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
END;
/

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

CREATE OR REPLACE PROCEDURE proc1 IS

BEGIN

DBMS_OUTPUT.put_line ('running proc1');

RAISE NO_DATA_FOUND;

END;

/

CREATE OR REPLACE PROCEDURE proc2 IS

l_str VARCHAR2 (30) := 'calling proc1';

BEGIN

DBMS_OUTPUT.put_line (l_str);

proc1;

END;

/

CREATE OR REPLACE PROCEDURE proc3_a IS

BEGIN

DBMS_OUTPUT.put_line ('calling proc2');

proc2;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Error stack at top level:');

DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);

END;

/

通过DBMS_UTILITY.format_error_backtrace，我们可以看到出错的行号， [crayon-678c01e45da2b63037070 […]

..experience, Working case

密码保护：Exadata存储节点root用户密码重置

2013-06-15 一条评论

无法提供摘要。这是一篇受保护的文章。