11g new feature

11g新特性:X$DBGALERTEXT一个很酷的内部视图

2012/01/06 by admin Leave a Comment

以往我们为了管理数据库的便利，总是希望能够利用SQL查询Oracle关键的Alert.log告警日志，在11g以前可以通过创建Alert.log内容的外部表来实现查询日志内容的目的的。

从11g 开始引入了新的ADR(Automatic Diagnostic Repository ADR is a file-based repository for database diagnostic data, such as traces, incident dumps and packages, the alert log, Health Monitor reports, core dumps, and more. It has a unified directory structure across multiple instances and multiple products stored outside of any database. It is,therefore, available for problem diagnosis when the database is down.)自动诊断仓库特性，默认的Alert.log转换为LOG.XML的形式，默认存放在$ADR_HOME/alert&trace目录下，并且为日志条目增加了如Level之类的属性，使得告警日志能够为Oracle Support提供更多有用的信息。

得益于XML格式的日志，可以更方便地将数据库告警日志内容转换为行列数。所以从11g开始，我们甚至于不需要去手动创建外部表了，Oracle 直接提供了X$DBGALERTEXT 这一内部视图来方便我们访问Alert文本：

SQL>desc X$DBGALERTEXT;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 ORIGINATING_TIMESTAMP                              TIMESTAMP(3) WITH TIME ZONE
 NORMALIZED_TIMESTAMP                               TIMESTAMP(3) WITH TIME ZONE
 ORGANIZATION_ID                                    VARCHAR2(64)
 COMPONENT_ID                                       VARCHAR2(64)
 HOST_ID                                            VARCHAR2(64)
 HOST_ADDRESS                                       VARCHAR2(46)
 MESSAGE_TYPE                                       NUMBER
 MESSAGE_LEVEL                                      NUMBER
 MESSAGE_ID                                         VARCHAR2(64)
 MESSAGE_GROUP                                      VARCHAR2(64)
 CLIENT_ID                                          VARCHAR2(64)
 MODULE_ID                                          VARCHAR2(64)
 PROCESS_ID                                         VARCHAR2(32)
 THREAD_ID                                          VARCHAR2(64)
 USER_ID                                            VARCHAR2(64)
 INSTANCE_ID                                        VARCHAR2(64)
 DETAILED_LOCATION                                  VARCHAR2(160)
 PROBLEM_KEY                                        VARCHAR2(64)
 UPSTREAM_COMP_ID                                   VARCHAR2(100)
 DOWNSTREAM_COMP_ID                                 VARCHAR2(100)
 EXECUTION_CONTEXT_ID                               VARCHAR2(100)
 EXECUTION_CONTEXT_SEQUENCE                         NUMBER
 ERROR_INSTANCE_ID                                  NUMBER
 ERROR_INSTANCE_SEQUENCE                            NUMBER
 VERSION                                            NUMBER
 MESSAGE_TEXT                                       VARCHAR2(2048)
 MESSAGE_ARGUMENTS                                  VARCHAR2(128)
 SUPPLEMENTAL_ATTRIBUTES                            VARCHAR2(128)
 SUPPLEMENTAL_DETAILS                               VARCHAR2(128)
 PARTITION                                          NUMBER
 RECORD_ID                                          NUMBER

SQL> set linesize 100 pagesize 1400
SQL> select originating_timestamp, message_group, problem_key, message_text
  2    from X$DBGALERTEXT
  3   where message_text like '%ORA-00600%'
  4     and rownum < 10;

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_GROUP
----------------------------------------------------------------
PROBLEM_KEY
----------------------------------------------------------------
MESSAGE_TEXT
----------------------------------------------------------------------------------------------------
10-NOV-11 03.15.52.025 AM -05:00
Generic Internal Error
ORA 600
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_5547.trc  (incident=11106):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []

10-NOV-11 03.15.54.882 AM -05:00
Generic Internal Error
ORA 600
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_5547.trc  (incident=11108):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.13.07.918 AM -05:00
Generic Internal Error
ORA 600 [4194]
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_smon_4689.trc  (incident=16965):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.13.11.260 AM -05:00

Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_smon_4689.trc:
ORA-01595: error freeing extent (3) of rollback segment (10))
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.16.56.765 AM -05:00
Generic Internal Error
ORA 600 [4194]
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_mmon_4935.trc  (incident=16997):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.17.00.852 AM -05:00
Generic Internal Error
ORA 600 [ORA-00600: internal error code, arguments: [4194], [],
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_mmon_4935.trc  (incident=16998):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.20.07.859 AM -05:00
Generic Internal Error
ORA 600 [4194]
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_mmon_5029.trc  (incident=16999):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.20.09.654 AM -05:00
Generic Internal Error
ORA 600 [ORA-00600: internal error code, arguments: [4194], [],
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_mmon_5029.trc  (incident=17000):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.22.43.632 AM -05:00
Generic Internal Error
ORA 600 [4194]
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_m000_5137.trc  (incident=17141):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

X$DBGALERTEXT的内容极其丰富，包括message记录发生的日期、发生问题的进程号、trace日志的位置、问题的关键(problem key)，可以为我们分析数据库日志提供极为强大的接口，节约大量的时间。

X$DBGALERTEXT, that allows the log.xml file to be queried like a table. The X$DBGALERTEXT table is effectively indexed by the date the alert was raised so queries against this table based on a date range, which we are doing here, is efficient and performant.

In 11g, X$DBGALERTEXT now exists as a table. You can query the table for the specific columns needed.

但是请注意X$DBGALERTEXT的数据实际来源于ADR中的log.xml文件，若xml被删除或者内容缺失均会影响到X$DBGALERTEXT的信息完整性：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn & www.askmac.cn

SQL> select count(*) from X$DBGALERTEXT;

  COUNT(*)
----------
     51740

SQL> ! echo > /s01/orabase/diag/rdbms/vprod/VPROD1/alert/log.xml

SQL> select count(*) from X$DBGALERTEXT;

  COUNT(*)
----------
     34005

SQL> ! echo > /s01/orabase/diag/rdbms/vprod/VPROD1/alert/log_1.xml      

SQL>  select count(*) from X$DBGALERTEXT;

  COUNT(*)
----------
         0

可以看到X$DBGALERTEXT的数据来源于 log.xml及其归档，在11g中当log.xml的大小超过10MB时，Oracle会将其内容归档到如log_$N.xml这样的归档文件中，并清空当前的log.xml的内容。

通过X$DBGALERTEXT内部视图接口，我们可以实现一些原本想都不敢想的日志分析目的, 例如我要找出最近30天内出现过的Internal Errors：

SQL> select originating_timestamp, message_text
  2    from X$DBGALERTEXT
  3   where originating_timestamp > sysdate - 30
  4     and message_group = 'Generic Internal Error';

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
08-DEC-11 03.12.10.998 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_smon_23422.trc
 (incident=27673):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

08-DEC-11 03.12.13.105 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_smon_23422.trc
 (incident=27674):

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

08-DEC-11 03.12.14.107 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_smon_23422.trc
 (incident=27675):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
08-DEC-11 03.12.16.017 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_smon_23422.trc
 (incident=27676):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

08-DEC-11 03.40.25.562 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_mmon_30374.trc
 (incident=29076):

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

08-DEC-11 03.40.26.989 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_mmon_30374.trc
 (incident=29077):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
08-DEC-11 03.40.29.020 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_m001_30561.trc
 (incident=29244):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

08-DEC-11 03.40.31.595 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_m001_30561.trc
 (incident=29245):

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

08-DEC-11 03.41.17.338 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_m001_30640.trc
 (incident=29260):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
08-DEC-11 03.41.18.668 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_m001_30640.trc
 (incident=29261):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

10-DEC-11 01.45.51.556 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_ckpt_1026.trc
(incident=33838):

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [], [],
[], [], [], [], [], [], []

10-DEC-11 01.45.52.304 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_ckpt_1026.trc
(incident=33839):
ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [],
[], [], [], []

或者我要审计最近2天内管理员执行过的DDL语句:

SQL> select originating_timestamp, message_text
  2    from X$DBGALERTEXT
  3   where originating_timestamp > sysdate - 2
  4     and message_group = 'admin_ddl';

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
07-JAN-12 02.05.48.770 AM -05:00
alter database open

07-JAN-12 02.05.48.781 AM -05:00
ORA-1531 signalled during: alter database open...

Metalink文档How to – Monitor Non Critical 11g Database Alert Log Errors Using a SQL UDM [ID 961682.1] 介绍了一种利用X$DBGALERTEXT实现计算告警日志信息度量的方法。

Oracle Freelist上对于X$DBGALERTEXT在近期也有精彩的讨论，值得移步一看。

11g新特性

2012/01/04 by admin 2 Comments

以下是Maclean Liu所编写的Oracle 11g新特性的文章汇总列表：

11g新特性:A useful View V$DIAG_INFO
11gR2新特性:LMHB Lock Manager Heart Beat后台进程
 给11gR2 RAC添加LISTENER监听器并静态注册
 11.2 中Oracle Cluster Registry(OCR)可选的存储设备
 11.2.0.3 实例启动现在提供Large Pages Information大内存页信息了
 Does Duplicate Target Database need Pre-existing DB backup?
11gR2新特性:STANDBY_MAX_DATA_DELAY
11g 新特性IGNORE_ROW_ON_DUPKEY_INDEX提示
 11g新特性SQL执行计划管理(SQL Plan Management)
11g新特性-在线实施补丁online patching
11g新特性:Rolling Upgrade With Physical Standby
11g新特性-SQL PLUS 错误日志
 11g新特性:Note raised when explain plan for create index
11g compression 新特性
 11g新特性之闪回事务处理取消
 11g新动态性能视图V$SQL_MONITOR,V$SQL_PLAN_MONITOR
11g默认审计选项
 Examine 11g automatic block Corruption recovery
Duplicate standby database from active database
11g Database Installation flow
Setup Oracle Direct NFS Client
Recreate failovered primary database using Flashback Database
11g中的db_block_checking参数
 11.2.0.2补丁集安装体验
 Oracle 11g中数据库能有多大？
undo backup optimization does not work on 11.2.0.1?
11g新特性之IO校准(IO Calibration)
11g中AWR新快照视图
 11g r2中对闪回数据归档的增强
 11g Release 2 enhanced Tablespace Point In Time Recovery
11g新特性:Streams同步捕获
 Applying online patch on 11gr2
How to recover from root.sh on 11.2 Grid Infrastructure Failed
Uninstall/Remove 11.2.0.2 Grid Infrastructure & Database in Linux
为11.2.0.2 Grid Infrastructure添加节点
 Find password cracker in 11g
11g Multi-Column Correlation Stats and Dynamic Sampling
11g内存管理新特性的internal表现
 Oracle database 11g release2发布
 How to check and disable Adaptive Cursor Sharing in 11g
How does SGA/PGA allocate on 11g AMM?
crsctl status resource -t -init in 11.2.0.2 grid infrastructure
Learning 11g New Background Processes

Filed Under: Oracle, Oracle 11g新特性 Tagged With: 11g new feature, Oracle 11g新特性

Does Duplicate Target Database need Pre-existing DB backup?

2011/09/23 by admin Leave a Comment

之前有网友问我在10g中通过RMAN 的duplicate target database命令复制数据库时是否需要先完成全库的备份。

实际我在10g中并不常用duplicate target database 来帮助创建DataGuard Standby Database，所以虽然记忆中仍有些印象，却不能十分确定地回答了。

今天查了一下资料，发现原来Active database duplication 和 Backup-based duplication 是11g才引入的特性，换句话说10g中duplication是要求预先完成数据库的RMAN backup备份的。

具体关于以上2个特性见文档<RMAN ‘Duplicate Database’ Feature in 11G>,引文如下：

RMAN 'Duplicate Database' Feature in 11G

You can create a duplicate database using the RMAN duplicate command.
The duplicate database has a different DBID from the source database and functions
entirely independently.Starting from 11g you can do duplicate database in 2 ways.

1. Active database duplication
2. Backup-based duplication

Active database duplication copies the live target database over the network to the
auxiliary destination and then creates the duplicate database.Only difference is that you
don't need to have the pre-existing RMAN backups and copies.

The duplication work is performed by an auxiliary channel.
This channel corresponds to a server session on the auxiliary instance on the auxiliary host.

As part of the duplicating operation, RMAN automates the following steps:

1. Creates a control file for the duplicate database
2. Restarts the auxiliary instance and mounts the duplicate control file
3. Creates the duplicate datafiles and recovers them with incremental backups and archived redo logs.
4. Opens the duplicate database with the RESETLOGS option

For the active database duplication, RMAN does one extra step .i.e. copy the
target database datafiles over the network to the auxiliary instance

A RAC TARGET database can be duplicated as well. The procedure is the same as below.
If the auxiliary instance needs to be a RAC-database as well,
than start the duplicate procedure for to a single instance and convert
the auxiliary to RAC after the duplicate has succeeded.

而在10g 中不仅需要对目标数据库进行备份，还需要手动将备份集(backupset)拷贝至目标主机上，这确实过于繁琐了：

Oracle10G RMAN Database Duplication
 If you are using a disk backup solution and duplicate to a
remote node you must first copy the backupsets from the original hosts backup
location to the same mount and path on the remote server. Because duplication
uses auxiliary channels the files must be where the IO pipe is allocated. So the
IO will take place on the remote node and disk backups must be locally available.

Filed Under: Oracle, Oracle 11g新特性, Oracle数据恢复、数据库恢复、灾难恢复 Tagged With: 11g new feature, Backup, duplicate target database

/dev/shm Filled Up With Files In Format JOXSHM_EXT_xxx_SID_xxx

2011/04/25 by admin Leave a Comment

Oracle Server – Enterprise Edition – Version: 11.1.0.6 to 11.1.0.7 – Release: 11.1 to 11.1
Symptoms

/dev/shm is being filled up with files in the format JOXSHM_EXT_???_SID_???? and eventually causing the DB to restart. Oracle is not automatically cleaning those files.

Please note that it is OS specific whether such segments are visible on a filesystem or not, and if so where they get placed. eg: On Solaris shm_open() internally creates a file in /tmp with the prefix .SHMD and less the leading “/” from the shm_open argument whereas on Linux it creates an entry in /dev/shm

Changes
Most probably the database has not been closed cleanly.
Cause
This issue has been described in
<< Bug.6820987 >> /DEV/SHM IS NOT BEING CLEANED UP ON NODE 1
which was closed as duplicate of
Unpulished Bug.6662381 JOXSHM_EXT LEFT AFTER SHUTDOWN IMMEDIATE
which was closed as duplicate of
Unpublished Bug 9021155 APPSST GSI 11G: NATIVE PL/SQL CACHE FILES MAKES /TMP SLOW AND UNUSABLE

It has been determined by development that the cause of such issue is due to the fact that the database has not be closed cleanly at some point in time.

This has also been documented in the following documentation:
http://download.oracle.com/docs/cd/E17116_01/doc/readmes.112/e11015/toc.htm#BABBCFHJ
Solution
The shared object files are only freed up by the oracle executable if you do a normal shutdown.

They will build up if you shutdown abort or shutdown immediate.

This behaviour is NOT changed in any version – or changed by the fix for unpublished Bug 9021155.

1. Check if the one off Patch 9021155 is available on My Oracle Support for your Oracle version and platform. The fix for Bug 902115 allows you to specify a directory so that if you have more then one instance on a machine you are able to identify the shared files associated with a particular instance, so that you do not inadvertently clean up files from another running instance.

2. Use one of the workarounds provided below:

a. Rebooting the server from time to time as this clears those files
OR
b. In 11.1.0.7 the “id” part of the name is the shared memory id of the shared memory for the instance.
eg: “ipcs -ma” ID column value is the “id” part of the name. This is not documented / guaranteed but does
give a way to see if a file corresponds to a running instance. For the files that you have you should first check
if the tail number matches to a valid SHM ID (as reported by ipcs -ma) .If not then the files are probably stale old copies and you can go ahead and delete those files. If so then those files are related to a currently running instance and deleting those files can lead to unpredictable results. Instead you can add the following code before you startup or after you shutdown the instance:

rm -f /dev/shm/JOXSHM_EXT_*_<instance name>_*
rm -f /dev/shm/PESHM_EXT_*_<instance name>_*

Again please note that the directories referenced above are OS specific and should be modified according to your OS. The above applies to Linux.

Filed Under: Oracle, Oracle 11g新特性 Tagged With: /dev/shm, 11g new feature

11g新动态性能视图V$SQL_MONITOR,V$SQL_PLAN_MONITOR

2010/09/16 by admin Leave a Comment

11g中引入了新的动态性能视图V$SQL_MONITOR，该视图用以显示Oracle监视的SQL语句信息。SQL监视会对那些并行执行或者消耗5秒以上cpu时间或I/O时间的SQL语句自动启动，同时在V$SQL_MONITOR视图中产生一条记录。当SQL语句正在执行，V$SQL_MONITOR视图中的统计信息将被实时刷新，频率为每秒1次。SQL语句执行完成后，监视信息将不会被立即删除，Oracle会保证相关记录保存一分钟(由参数_sqlmon_recycle_time所控制，默认为60s)，最终这些记录都会被删除并被重用。这一新的SQL性能监视特性仅在CONTROL_MANAGEMENT_PACK_ACCESS为DIAGNOSTIC+TUNING和STATISTICS_LEVEL为ALL|TYPICAL时被启用。
[Read more…]

Filed Under: Oracle, Oracle 11g新特性 Tagged With: 11g new feature, autotune, dynamic performance view, hidden parameter, optimizer, sql monitor, SQL tuning, statistics