2013年11月 – OracleBlog

SQL执行时间受游标影响不准

2013-11-26 没有评论

sql的执行时间，我们往往可以通过ash中的sample时间，减去sql exec start，得出该sql执行的时间。有一个很不错的sql，可以看某个sql的历次执行时间，历次执行计划。

SELECT T.SQL_ID, T.SQL_EXEC_ID,
       CAST(MAX(T.SAMPLE_TIME) AS DATE) EXEC_END_TIME,
       T.SQL_EXEC_START EXEC_START_TIME,
       ROUND((CAST(MAX(T.SAMPLE_TIME) AS DATE) - T.SQL_EXEC_START) * 1440, 2) RUN_MINS,
       T.SQL_PLAN_HASH_VALUE, T.MODULE
  FROM v$active_session_history T
 WHERE T.SQL_ID = '&SQL_ID'
   AND T.SAMPLE_TIME > SYSDATE - 36
 GROUP BY T.SQL_ID, T.SQL_EXEC_ID, T.SQL_EXEC_START, T.SQL_PLAN_HASH_VALUE, T.MODULE
 ORDER BY EXEC_END_TIME DESC;

或者

SELECT T.SQL_ID, T.SQL_EXEC_ID,
       CAST(MAX(T.SAMPLE_TIME) AS DATE) EXEC_END_TIME,
       T.SQL_EXEC_START EXEC_START_TIME,
       ROUND((CAST(MAX(T.SAMPLE_TIME) AS DATE) - T.SQL_EXEC_START) * 1440, 2) RUN_MINS,
       T.SQL_PLAN_HASH_VALUE, T.MODULE
  FROM sys.WRH$_ACTIVE_SESSION_HISTORY T
 WHERE T.SQL_ID = '&SQL_ID'
   AND T.SAMPLE_TIME > SYSDATE - 36
 GROUP BY T.SQL_ID, T.SQL_EXEC_ID, T.SQL_EXEC_START, T.SQL_PLAN_HASH_VALUE, T.MODULE
 ORDER BY EXEC_END_TIME DESC;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

SELECT T.SQL_ID, T.SQL_EXEC_ID,

CAST(MAX(T.SAMPLE_TIME) AS DATE) EXEC_END_TIME,

T.SQL_EXEC_START EXEC_START_TIME,

ROUND((CAST(MAX(T.SAMPLE_TIME) AS DATE) - T.SQL_EXEC_START) * 1440, 2) RUN_MINS,

T.SQL_PLAN_HASH_VALUE, T.MODULE

FROM v$active_session_history T

WHERE T.SQL_ID = '&SQL_ID'

AND T.SAMPLE_TIME > SYSDATE - 36

GROUP BY T.SQL_ID, T.SQL_EXEC_ID, T.SQL_EXEC_START, T.SQL_PLAN_HASH_VALUE, T.MODULE

ORDER BY EXEC_END_TIME DESC;

或者

SELECT T.SQL_ID, T.SQL_EXEC_ID,

CAST(MAX(T.SAMPLE_TIME) AS DATE) EXEC_END_TIME,

T.SQL_EXEC_START EXEC_START_TIME,

ROUND((CAST(MAX(T.SAMPLE_TIME) AS DATE) - T.SQL_EXEC_START) * 1440, 2) RUN_MINS,

T.SQL_PLAN_HASH_VALUE, T.MODULE

FROM sys.WRH$_ACTIVE_SESSION_HISTORY T

WHERE T.SQL_ID = '&SQL_ID'

AND T.SAMPLE_TIME > SYSDATE - 36

GROUP BY T.SQL_ID, T.SQL_EXEC_ID, T.SQL_EXEC_START, T.SQL_PLAN_HASH_VALUE, T.MODULE

ORDER BY EXEC_END_TIME DESC;

但是在使用游标的时候，如，在某个proced […]

..experience, Working case

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： […]

It's my life

Wnnn进程导致buffer busy wait

2013-11-21 没有评论

某日，在一个数据库中，发现大量buffer busy wait的进程，经检查，是监控等查询表空间使用率的进程处于buffer busy wait。而其blocking session wnnnn进程。 (1)看到监控程序被1013进程阻塞，blocking session是1013 [crayon- […]

Study note

控制文件丢失的恢复

2013-11-21 没有评论

1. rman设置自动备份的情况（CONFIGURE CONTROLFILE AUTOBACKUP ON ），可以注：11g自动备份的控制文件是放在FRA区：

RMAN> restore controlfile from autobackup;
 
Starting restore at 15-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
 
recovery area destination: e:\ora11g\app\oracleuser\flash_recovery_area
database name (or database unique name) used for search: ORA11G
channel ORA_DISK_1: AUTOBACKUP E:\ORA11G\APP\ORACLEUSER\FLASH_RECOVERY_AREA\ORA11G\AUTOBACKUP\2013_11_15\O1_MF_S_831574477_98CRBG12_.BKP found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP E:\ORA11G\APP\ORACLEUSER\FLASH_RECOVERY_AREA\ORA11G\AUTOBACKUP\2013_11_15\O1_MF_S_831574477_98CRBG12_.BKP
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\CONTROL01.CTL
output file name=E:\ORA11G\APP\ORACLEUSER\FLASH_RECOVERY_AREA\ORA11G\CONTROL02.CTL
Finished restore at 15-NOV-13
 
RMAN>
RMAN> restore database;
 
Starting restore at 15-NOV-13
Starting implicit crosscheck backup at 15-NOV-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=191 device type=DISK
Crosschecked 20 objects
Finished implicit crosscheck backup at 15-NOV-13
 
Starting implicit crosscheck copy at 15-NOV-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-NOV-13
 
searching for all files in the recovery area
cataloging files...
cataloging done
 
List of Cataloged Files
=======================
File Name: E:\ORA11G\APP\ORACLEUSER\FLASH_RECOVERY_AREA\ORA11G\ARCHIVELOG\2013_11_07\O1_MF_1_509_97POOJ85_.ARC
File Name: E:\ORA11G\APP\ORACLEUSER\FLASH_RECOVERY_AREA\ORA11G\AUTOBACKUP\2013_11_15\O1_MF_S_831574477_98CRBG12_.BKP
 
using channel ORA_DISK_1
 
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to E:\USERS2_FOR_TEMP_USE.DBF
channel ORA_DISK_1: reading from backup piece C:\FULL_9OOP1K9R_1_312.RMN
channel ORA_DISK_1: piece handle=C:\FULL_9OOP1K9R_1_312.RMN tag=TAG20131115T165211
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:35
Finished restore at 15-NOV-13
 
RMAN> recover database;
 
Starting recover at 15-NOV-13
using channel ORA_DISK_1
 
starting media recovery
 
archived log for thread 1 with sequence 25 is already on disk as file E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\REDO25.LOG
archived log file name=E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\REDO25.LOG thread=1 sequence=25
media recovery complete, elapsed time: 00:00:02
Finished recover at 15-NOV-13
 
RMAN> alter database open resetlogs;
 
database opened
 
RMAN>
 
RMAN>

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

RMAN> restore controlfile from autobackup;

Starting restore at 15-NOV-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=63 device type=DISK

recovery area destination: e:\ora11g\app\oracleuser\flash_recovery_area

database name (or database unique name) used for search: ORA11G

channel ORA_DISK_1: AUTOBACKUP E:\ORA11G\APP\ORACLEUSER\FLASH_RECOVERY_AREA\ORA11G\AUTOBACKUP\2013_11_15\O1_MF_S_831574477_98CRBG12_.BKP found in the recovery area

AUTOBACKUP search with format "%F" not attempted because DBID was not set

channel ORA_DISK_1: restoring control file from AUTOBACKUP E:\ORA11G\APP\ORACLEUSER\FLASH_RECOVERY_AREA\ORA11G\AUTOBACKUP\2013_11_15\O1_MF_S_831574477_98CRBG12_.BKP

channel ORA_DISK_1: control file restore from AUTOBACKUP complete

output file name=E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\CONTROL01.CTL

output file name=E:\ORA11G\APP\ORACLEUSER\FLASH_RECOVERY_AREA\ORA11G\CONTROL02.CTL

Finished restore at 15-NOV-13

RMAN>

RMAN> restore database;

Starting restore at 15-NOV-13

Starting implicit crosscheck backup at 15-NOV-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=191 device type=DISK

Crosschecked 20 objects

Finished implicit crosscheck backup at 15-NOV-13

Starting implicit crosscheck copy at 15-NOV-13

using channel ORA_DISK_1

Finished implicit crosscheck copy at 15-NOV-13

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: E:\ORA11G\APP\ORACLEUSER\FLASH_RECOVERY_AREA\ORA11G\ARCHIVELOG\2013_11_07\O1_MF_1_509_97POOJ85_.ARC

File Name: E:\ORA11G\APP\ORACLEUSER\FLASH_RECOVERY_AREA\ORA11G\AUTOBACKUP\2013_11_15\O1_MF_S_831574477_98CRBG12_.BKP

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\SYSTEM01.DBF

channel ORA_DISK_1: restoring datafile 00002 to E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\SYSAUX01.DBF

channel ORA_DISK_1: restoring datafile 00003 to E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\UNDOTBS01.DBF

channel ORA_DISK_1: restoring datafile 00004 to E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\USERS01.DBF

channel ORA_DISK_1: restoring datafile 00005 to E:\USERS2_FOR_TEMP_USE.DBF

channel ORA_DISK_1: reading from backup piece C:\FULL_9OOP1K9R_1_312.RMN

channel ORA_DISK_1: piece handle=C:\FULL_9OOP1K9R_1_312.RMN tag=TAG20131115T165211

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:35

Finished restore at 15-NOV-13

RMAN> recover database;

Starting recover at 15-NOV-13

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 25 is already on disk as file E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\REDO25.LOG

archived log file name=E:\ORA11G\APP\ORACLEUSER\ORADATA\ORA11G\REDO25.LOG thread=1 sequence=25

media recovery complete, elapsed time: 00:00:02

Finished recover at 15-NOV-13

RMAN> alter database open resetlogs;

database opened

RMAN>

2.rman一般会配置snapshot（CONFIGURE SNAPSH […]

..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的等待 […]