在有些时候，我们需要通过systemstate或者hanganalyze来收集数据库hang信息，但是当我们的数据库不能登录的时候，无法通过常规手段来使用该方法来收集信息。ORACLE的开发者也考虑到了类似情况，让我们可以通过dbx或者gdb来收集数据库信息.这里我通过在linux平台上模拟一个新会话，然后通过gdb直接对该会话进行做systemstate,然后操作该会话(证明gdb操作后正常),来实现类似系统hang住，无法登陆的时候怎么做systemstate.
模拟会话
就是假设我们hang住的系统中的数据库进程(为了后面gdb和验证对会话影响而使用,不然可以直接使用oracle 后台进程来完成该操作)

[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 17 18:28:30 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

查找数据库进程
在另外一个shell窗口找出数据库进程

[root@xifenfei trace]# ps -ef|grep LOCAL
oracle    7476  7473  0 18:28 ?        00:00:00 oracleora11g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root      7487  7326  0 18:28 pts/2    00:00:00 grep LOCAL

gdb做systemstate

[oracle@xifenfei trace]$ gdb $ORACLE_HOME/bin/oracle 7476
GNU gdb (GDB) Red Hat Enterprise Linux (7.0.1-37.el5)
Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "i386-redhat-linux-gnu".
…………
Reading symbols from /u01/oracle/oracle/product/11.2.0/db_1/lib/libnque11.so...
Loaded symbols for /u01/oracle/oracle/product/11.2.0/db_1/lib/libnque11.so
0xb789c424 in __kernel_vsyscall ()
(gdb)  print ksudss(10)  <--输入print ksudss(10)
$1 = 0
(gdb) quit
A debugging session is active.
        Inferior 1 [process 7476] will be detached.
Quit anyway? (y or n) y
Detaching from program: /u01/oracle/oracle/product/11.2.0/db_1/bin/oracle, process 7476

查看trace文件

[oracle@xifenfei trace]$ ls -l *7476*.trc
-rw-r----- 1 oracle oinstall 742438 Jan 17 18:28 ora11g_ora_7476.trc
[oracle@xifenfei trace]$ more ora11g_ora_7476.trc
Trace file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_7476.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/oracle/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      xifenfei
Release:        2.6.32-200.13.1.el5uek
Version:        #1 SMP Wed Jul 27 20:21:26 EDT 2011
Machine:        i686
VM name:        VMWare Version: 6
Instance name: ora11g
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 7476, image: oracle@xifenfei (TNS V1-V3)
*** 2013-01-17 18:28:59.225
*** SESSION ID:(143.23) 2013-01-17 18:28:59.225
*** CLIENT ID:() 2013-01-17 18:28:59.225
*** SERVICE NAME:(SYS$USERS) 2013-01-17 18:28:59.225
*** MODULE NAME:(sqlplus@xifenfei (TNS V1-V3)) 2013-01-17 18:28:59.225
*** ACTION NAME:() 2013-01-17 18:28:59.225
===================================================
SYSTEM STATE (level=10)  <---systemstate levle 10
------------
System global information:
     processes: base 0x32bc5b38, size 150, cleanup 0x32bd5990
     allocation: free sessions 0x32085b84, free calls (nil)
     control alloc errors: 0 (process), 0 (session), 0 (call)
     PMON latch cleanup depth: 0
     seconds since PMON's last scan for dead processes: 693
     system statistics:

这里证明使用gdb–>print ksudss(10)对数据库做的是systemstate level 10

验证做gdb的进程

SQL> select * from dual;
D
-
X
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

证明被gdb处理过的进程还是正常(未收到额外破坏),所以我们可以确定在系统hang住，而且新会话不能正常建立连接的时候，我们可以尝试着使用ksudss(10)来对系统做systemstate然后分析

自动诊断存储库(Automatic Diagnostic Repository) 是oracle database 11g的一个新特性,它提供了一个中心化存储trace files, dumps, health monitor reports, alert logs等. 不同的产品和实例都有统一的目录结构, 把各自的诊断数据存储在自己的ADR_HOME里.从11gr1–>11gr2–>12cr1 adrci的功能逐渐增强,也确实需要关注下其最基本的功能,管理alert日志和trace文件.
adrci启动

[oracle@xifenfei ~]$ adrci
ADRCI: Release 12.1.0.0.2        on Sun Dec 16 15:27:51 2012
Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.
ADR base = "/u01/app/oracle"

adrci帮助

adrci> help
 HELP [topic]
   Available Topics:
        CREATE REPORT
        ECHO
        EXIT
        HELP
        HOST
        IPS
        PURGE
        RUN
        SET BASE
        SET BROWSER
        SET CONTROL
        SET ECHO
        SET EDITOR
        SET HOMES | HOME | HOMEPATH
        SET TERMOUT
        SHOW ALERT
        SHOW BASE
        SHOW CONTROL
        SHOW HM_RUN
        SHOW HOMES | HOME | HOMEPATH
        SHOW INCDIR
        SHOW INCIDENT
        SHOW LOG
        SHOW PROBLEM
        SHOW REPORT
        SHOW TRACEFILE
        SPOOL
 There are other commands intended to be used directly by Oracle, type
 "HELP EXTENDED" to see the list

adrci设置home
当adrci中含有了多个home目录的时候,需要手工设置home值才能够很好的运行该工具

adrci> show home
ADR Homes:
diag/tnslsnr/xifenfei/listener
diag/tnslsnr/xifenfei/listener_a
diag/clients/user_oracle/host_2460950761_80
diag/rdbms/yxhe_pitr_xifenfei/yxhE
diag/rdbms/aief_pitr_xifenfei/aief
diag/rdbms/xifenfei/xff
diag/rdbms/xifenfei/xifenfei
diag/rdbms/iwcn_pitr_xifenfei/iwcn
diag/rdbms/abrn_pitr_xifenfei/aBrn
diag/rdbms/efqn_pitr_xifenfei/efqn
diag/rdbms/gwix_pitr_xifenfei/gwix
adrci> show alert -tail 1
DIA-48449: Tail alert can only apply to single ADR home
adrci> set home diag/rdbms/xifenfei/xff
adrci> show home
ADR Homes:
diag/rdbms/xifenfei/xff

adrci help命令具体使用

adrci> help show alert
  Usage: SHOW ALERT [-p <predicate_string>]  [-term]
                    [ [-tail [num] [-f]] | [-file <alert_file_name>] ]
  Purpose: Show alert messages.
  Options:
    [-p <predicate_string>]: The predicate string must be double-quoted.
    The fields in the predicate are the fields:
        ORIGINATING_TIMESTAMP         timestamp
        NORMALIZED_TIMESTAMP          timestamp
        ORGANIZATION_ID               text(65)
        COMPONENT_ID                  text(65)
        HOST_ID                       text(65)
        HOST_ADDRESS                  text(17)
        MESSAGE_TYPE                  number
        MESSAGE_LEVEL                 number
        MESSAGE_ID                    text(65)
        MESSAGE_GROUP                 text(65)
        CLIENT_ID                     text(65)
        MODULE_ID                     text(65)
        PROCESS_ID                    text(33)
        THREAD_ID                     text(65)
        USER_ID                       text(65)
        INSTANCE_ID                   text(65)
        DETAILED_LOCATION             text(161)
        UPSTREAM_COMP_ID              text(101)
        DOWNSTREAM_COMP_ID            text(101)
        EXECUTION_CONTEXT_ID          text(101)
        EXECUTION_CONTEXT_SEQUENCE    number
        ERROR_INSTANCE_ID             number
        ERROR_INSTANCE_SEQUENCE       number
        MESSAGE_TEXT                  text(2049)
        MESSAGE_ARGUMENTS             text(129)
        SUPPLEMENTAL_ATTRIBUTES       text(129)
        SUPPLEMENTAL_DETAILS          text(4000)
        PROBLEM_KEY                   text(65)
    [-tail [num] [-f]]: Output last part of the alert messages and
    output latest messages as the alert log grows. If num is not specified,
    the last 10 messages are displayed. If "-f" is specified, new data
    will append at the end as new alert messages are generated.
    [-term]: Direct results to terminal. If this option is not specified,
    the results will be open in an editor.
    By default, it will open in emacs, but "set editor" can be used
    to set other editors.
    [-file <alert_file_name>]: Allow users to specify an alert file which
    may not be in ADR. <alert_file_name> must be specified with full path.
    Note that this option cannot be used with the -tail option
  Examples:
    show alert
    show alert -p "message_text like '%incident%'"
    show alert -tail 20

adrci查看alert日志
1.使用alert命令为例子
2.这里的tail n并非传统的tail 命令指定的行数结果

--显示tail 1日志
adrci> show alert -tail 1
2012-12-15 23:41:35.571000 +08:00
System state dump requested by (instance=1, osid=3633 (PSP0)), summary=[abnormal instance termination].
2012-12-15 23:41:40.454000 +08:00
Instance terminated by PSP0, pid = 3633
--显示含PSP0行日志
adrci> show alert -p "message_text like '%PSP0%'"
ADR Home = /u01/app/oracle/diag/rdbms/xifenfei/xff:
*************************************************************************
Output the results to file: /tmp/alert_26813_1400_xff_1.ado
2012-12-06 22:47:43.072000 +08:00
Starting background process PSP0
PSP0 started with pid=3, OS id=23605
2012-12-06 23:29:19.362000 +08:00
Starting background process PSP0
PSP0 started with pid=3, OS id=25006
2012-12-08 20:38:43.602000 +08:00
Starting background process PSP0
PSP0 started with pid=3, OS id=3942
2012-12-12 21:24:19.874000 +08:00
Starting background process PSP0
PSP0 started with pid=3, OS id=3673
2012-12-12 21:34:19.647000 +08:00
Starting background process PSP0
PSP0 started with pid=3, OS id=4254
2012-12-15 23:41:35.571000 +08:00
System state dump requested by (instance=1, osid=3633 (PSP0)), summary=[abnormal instance termination].
2012-12-15 23:41:40.454000 +08:00
Instance terminated by PSP0, pid = 3633
--含PSP0 tail 1行记录
adrci> show alert -p "message_text like '%PSP0%'" -tail 1
2012-12-15 23:41:35.571000 +08:00
System state dump requested by (instance=1, osid=3633 (PSP0)), summary=[abnormal instance termination].
2012-12-15 23:41:40.454000 +08:00
Instance terminated by PSP0, pid = 3633

补充其他常用命令

--清理60分钟前alert
purge -age 60 -type  ALERT
--清理120分钟钱trace文件
purge -age 120 -type TRACE
--监控alert日志
SHOW ALERT -tail 1 -f
--查看trace文件
SHOW TRACEFILE -tr/t
--查看特定进程trace文件并排序
show tracefile %log% -tr
--查看特定spid进程
show tracefile %27476%

补充说明:对于adr的进一步使用,可以通过使用help命令来逐步查询并且尝试操作,adrci中的help是非常强大工具.

在日常的数据库维护中，经常出现因为数据库登录审计的功能启动,导致system表空间被用满.从而出现异常,一般建议把aud$相关对象迁移到其他表空间,从而避免system被用完的风险.
人工move aud$相关对象

alter table AUDIT$ move tablespace users;
alter table AUDIT_ACTIONS move tablespace users;
alter table AUD$ move tablespace users;
alter table AUD$ move lob(SQLBIND) store as SYS_IL0000000384C00041$$ (tablespace users);
alter table AUD$ move lob(SQLTEXT) store as SYS_IL0000000384C00041$$ (tablespace users);
alter index I_AUDIT rebuild online tablespace users;
alter index I_AUDIT_ACTIONS rebuild online tablespace users;
--可能修改值(index和lob index)
SQL> select COLUMN_NAME,index_name from dba_lobs where owner='SYS' and table_name='AUD$';
COLUMN_NAME                              INDEX_NAME
---------------------------------------- ------------------------------
SQLBIND                                  SYS_IL0000000384C00040$$
SQLTEXT                                  SYS_IL0000000384C00041$$
SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUD$';
INDEX_NAME
------------------------------
SYS_IL0000000384C00040$$
SYS_IL0000000384C00041$$
SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUDIT$';
INDEX_NAME
------------------------------
I_AUDIT
SQL>  SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUDIT_ACTIONS';
INDEX_NAME
------------------------------
I_AUDIT_ACTIONS

DBMS_AUDIT_MGMT实现迁移

conn / as sysdba
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
audit_trail_location_value => 'USERS');
END;
/

验证DBMS_AUDIT_MGMT效果

SQL> select segment_name,tablespace_name from dba_segments where
  2  segment_name in('AUD$','SYS_IL0000000384C00040$$','SYS_IL0000000384C00041$$',
  3  'AUDIT$','I_AUDIT','AUDIT_ACTIONS','I_AUDIT_ACTIONS');
SEGMENT_NAME                                                                      TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
AUDIT_ACTIONS                                                                     SYSTEM
AUDIT$                                                                            SYSTEM
AUD$                                                                              SYSTEM
SYS_IL0000000384C00041$$                                                          SYSTEM
SYS_IL0000000384C00040$$                                                          SYSTEM
I_AUDIT_ACTIONS                                                                   SYSTEM
I_AUDIT                                                                           SYSTEM
SQL> BEGIN
  2  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
  3  audit_trail_location_value => 'USERS');
  4  END;
  5  /
PL/SQL procedure successfully completed.
SQL> select segment_name,tablespace_name from dba_segments where
  2   segment_name in('AUD$','SYS_IL0000000384C00040$$','SYS_IL0000000384C00041$$',
  3   'AUDIT$','I_AUDIT','AUDIT_ACTIONS','I_AUDIT_ACTIONS');
SEGMENT_NAME                                                                      TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
AUDIT_ACTIONS                                                                     SYSTEM
AUDIT$                                                                            SYSTEM
AUD$                                                                              USERS
SYS_IL0000000384C00041$$                                                          USERS
SYS_IL0000000384C00040$$                                                          USERS
I_AUDIT_ACTIONS                                                                   SYSTEM
I_AUDIT                                                                           SYSTEM

通过试验证明DBMS_AUDIT_MGMT就是迁移了AUD$表中相关对象,对于和审计相关的其他几个对象并未迁移到其他表空间

aud$相关说明
1.DBMS_AUDIT_MGMT版本支持情况

It is still not supported (but it works) to use it on 10.2.0.4 and 11.1.0.7 for non-Audit Vault Environment.
Using RDBMS with Audit Vault, it is supported  for 10.2.0.4.0 and 11.1.0.7.0
as the DBMS_AUDIT_MGMT Package is provided to be used with an Audit Vault Environment.

2.该包可以实现在线迁移，特别是在高业务的系统中,可以实现在线迁移,而人工的move操作不能实现在线处理
3.对于AUD$对象，如果登录审计数据不是非常重要,可以通过truncate来解决一时的问题，在业务高的系统，可能truncate不能马上操作成功，可以尝试使用11gr2的新特性alter session set ddl_lock_timeout = 10;来实现自动ddl尝试
4.如果确定不需要登录审计功能，可以通过设置audit_trail=none来关闭(需要重启实例)

今天网友问到了v$sgainfo中的Free SGA Memory Available的一些情况,开始我也比较迷糊,为什么会出现Free SGA Memory Available的值不为0,通过查询一些试验和测试,对这个问题进行了有力的说明
数据库版本10G

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

当前SGA各组件信息

SQL> select name,bytes/1024/1024 from v$sgainfo;
NAME                             BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size                        1.20853043
Redo Buffers                           6.7890625
Buffer Cache Size                            192
Shared Pool Size                              88
Large Pool Size                                4
Java Pool Size                                 4
Streams Pool Size                              8
Granule Size                                   4
Maximum SGA Size                             304
Startup overhead in Shared Pool               36
Free SGA Memory Available                      0
11 rows selected.

sga配置

SQL> show parameter sga;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 304M
sga_target                           big integer 304M

在10g中，当采用asmm管理时,如果sga_max_size=sga_target,则Free SGA Memory Available为0

修改sga_target

SQL> alter system set sga_target=290M;
System altered.

再次查询v$sgainfo

SQL> select name,bytes/1024/1024 from v$sgainfo;
NAME                             BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size                        1.20853043
Redo Buffers                           6.7890625
Buffer Cache Size                            180
Shared Pool Size                              88
Large Pool Size                                4
Java Pool Size                                 4
Streams Pool Size                              8
Granule Size                                   4
Maximum SGA Size                             304
Startup overhead in Shared Pool               36
Free SGA Memory Available                     12
11 rows selected.

再次查看sga_target值

SQL> show parameter sga;
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 304M
sga_target                           big integer 292M

在10g中，当采用asmm管理时,如果sga_max_size>sga_target,则Free SGA Memory Available为sga_max_size-sga_target

找出sga_target修改为292M而不是290M原因

SQL> select component, granule_size from v$sga_dynamic_components;
COMPONENT                                                        GRANULE_SIZE
---------------------------------------------------------------- ------------
shared pool                                                           4194304
large pool                                                            4194304
java pool                                                             4194304
streams pool                                                          4194304
DEFAULT buffer cache                                                  4194304
KEEP buffer cache                                                     4194304
RECYCLE buffer cache                                                  4194304
DEFAULT 2K buffer cache                                               4194304
DEFAULT 4K buffer cache                                               4194304
DEFAULT 8K buffer cache                                               4194304
DEFAULT 16K buffer cache                                              4194304
DEFAULT 32K buffer cache                                              4194304
ASM Buffer Cache                                                      4194304
13 rows selected.
SQL> select 4194304/1024/1024 from dual;
4194304/1024/1024
-----------------
                4
SQL> select 292/4 from dual;
     292/4
----------
        73

因为sga的内存分配是按照GRANULE为单位进行的,而在该库中sga对应组件的GRANULE为4M,所以我们修改的290M的最近的GRANULE整数倍为292M

sga_target为0的情况

SQL> show parameter sga_target;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
sga_target                           big integer 0
SQL> select name,bytes/1024/1024 from v$sgainfo;
NAME                             BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size                        1.20846176
Redo Buffers                           6.7890625
Buffer Cache Size                            180
Shared Pool Size                              88
Large Pool Size                                4
Java Pool Size                                 4
Streams Pool Size                              8
Granule Size                                   4
Maximum SGA Size                             292
Startup overhead in Shared Pool               36
Free SGA Memory Available                      0
11 rows selected.

在10g中，如果不采用asmm内存管理模式,Free SGA Memory Available为0

11g数据库版本

SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 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

查询v$sgainfo信息

SQL> select name,bytes/1024/1024 from v$sgainfo;
NAME                             BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size                        1.28236008
Redo Buffers                          6.03515625
Buffer Cache Size                             20
Shared Pool Size                             116
Large Pool Size                                4
Java Pool Size                                 4
Streams Pool Size                              8
Shared IO Pool Size                            0
Granule Size                                   4
Maximum SGA Size                      299.320313
Startup overhead in Shared Pool       52.2684898
Free SGA Memory Available                    140

oracle内存相关参数设置

SQL> show parameter memory;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 300M
memory_target                        big integer 300M
shared_memory_address                integer     0
SQL> show parameter pga;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
pga_aggregate_target                 big integer 0

数据库动态组件内存分配

SQL> select COMPONENT,CURRENT_SIZE/1024/1024 CURRENT_SIZE from V$MEMORY_DYNAMIC_COMPONENTS;
COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool                                                               116
large pool                                                                  4
java pool                                                                   4
streams pool                                                                8
SGA Target                                                                160
DEFAULT buffer cache                                                       20
KEEP buffer cache                                                           0
RECYCLE buffer cache                                                        0
DEFAULT 2K buffer cache                                                     0
DEFAULT 4K buffer cache                                                     0
DEFAULT 8K buffer cache                                                     0
DEFAULT 16K buffer cache                                                    0
DEFAULT 32K buffer cache                                                    0
Shared IO Pool                                                              0
PGA Target                                                                140
ASM Buffer Cache                                                            0
16 rows selected.

初步怀疑:在11g的amm内存管理模式下Free SGA Memory Available和PGA Target相等

尝试修改pga_aggregate_target值

SQL> alter system set pga_aggregate_target=150M;
System altered.

再次查询v$sgainfo

SQL>  select name,bytes/1024/1024 from v$sgainfo;
NAME                             BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size                        1.28236008
Redo Buffers                          6.03515625
Buffer Cache Size                              8
Shared Pool Size                             116
Large Pool Size                                4
Java Pool Size                                 4
Streams Pool Size                              8
Shared IO Pool Size                            0
Granule Size                                   4
Maximum SGA Size                      299.320313
Startup overhead in Shared Pool       52.2684898
Free SGA Memory Available                    152

查询动态组件内存分布

SQL> select COMPONENT,CURRENT_SIZE/1024/1024 CURRENT_SIZE from V$MEMORY_DYNAMIC_COMPONENTS;
COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool                                                               116
large pool                                                                  4
java pool                                                                   4
streams pool                                                                8
SGA Target                                                                148
DEFAULT buffer cache                                                        8
KEEP buffer cache                                                           0
RECYCLE buffer cache                                                        0
DEFAULT 2K buffer cache                                                     0
DEFAULT 4K buffer cache                                                     0
DEFAULT 8K buffer cache                                                     0
DEFAULT 16K buffer cache                                                    0
DEFAULT 32K buffer cache                                                    0
Shared IO Pool                                                              0
PGA Target                                                                152
ASM Buffer Cache                                                            0
16 rows selected.

进一步证明:在11g的amm内存管理模式下Free SGA Memory Available和PGA Target相等

设置pga_aggregate_target为150M,PGA Target为152M原因分析

SQL> select COMPONENT,GRANULE_SIZE from V$MEMORY_DYNAMIC_COMPONENTS;
COMPONENT                                                        GRANULE_SIZE
---------------------------------------------------------------- ------------
shared pool                                                           4194304
large pool                                                            4194304
java pool                                                             4194304
streams pool                                                          4194304
SGA Target                                                            4194304
DEFAULT buffer cache                                                  4194304
KEEP buffer cache                                                     4194304
RECYCLE buffer cache                                                  4194304
DEFAULT 2K buffer cache                                               4194304
DEFAULT 4K buffer cache                                               4194304
DEFAULT 8K buffer cache                                               4194304
DEFAULT 16K buffer cache                                              4194304
DEFAULT 32K buffer cache                                              4194304
Shared IO Pool                                                        4194304
PGA Target                                                            4194304
ASM Buffer Cache                                                      4194304
16 rows selected.

原理同上述10g中的sga_target分析，不再重复,主要就是:150M不能被4M整除，所以取最近的152M

整体总结
1.如果不采用asmm和amm,Free SGA Memory Available为0
2.如果采用asmm,当sga_max_size=sga_target,则Free SGA Memory Available为0
3.如果采用asmm,当sga_max_size>sga_target,则Free SGA Memory Available为sga_max_size-sga_target
4.如果采用amm,Free SGA Memory Available和PGA Target相等

ORA-06502/ORA-06512错误

SQL> @?/rdbms/admin/awrload.sql
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will load the AWR data from a dump file. The   ~
~  script will prompt users for the following information:    ~
~     (1) name of directory object                            ~
~     (2) name of dump file                                   ~
~     (3) staging schema name to load AWR data into           ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR                  /u01/oracle/admin/ora11g/dpdump/
ORACLE_OCM_CONFIG_DIR          /u01/oracle/oracle/product/11.2.0/db_1/ccr/state
XMLDIR                         /u01/oracle/oracle/product/11.2.0/db_1/rdbms/xml
Choose a Directory Name from the list above (case-sensitive).
Enter value for directory_name: DATA_PUMP_DIR
Using the dump directory: DATA_PUMP_DIR
Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:
Enter value for file_name: awrdat_70441_70885-vpos-primary
Loading from the file name: awrdat_70441_70885-vpos-primary.dmp
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 2

因为导出来的awr数据库的编码是AL32UTF8,而现在的编码是ZHS16GBK,所以解决方法是设置NLS_LANG为合适编码

[oracle@xifenfei ~]$ env|grep NLS_LANG
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@xifenfei ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

ORA-31640

SQL> @?/rdbms/admin/awrload.sql
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will load the AWR data from a dump file. The   ~
~  script will prompt users for the following information:    ~
~     (1) name of directory object                            ~
~     (2) name of dump file                                   ~
~     (3) staging schema name to load AWR data into           ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR                  /u01/oracle/admin/ora11g/dpdump/
ORACLE_OCM_CONFIG_DIR          /u01/oracle/oracle/product/11.2.0/db_1/ccr/state
XMLDIR                         /u01/oracle/oracle/product/11.2.0/db_1/rdbms/xml
Choose a Directory Name from the list above (case-sensitive).
Enter value for directory_name: DATA_PUMP_DIR
Using the dump directory: DATA_PUMP_DIR
Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:
Enter value for file_name: awrdat_70441_70885-vpos-primary  <--注意输入
Loading from the file name: awrdat_70441_70885-vpos-primary.dmp
…………
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  Loading the AWR data from the following
|  directory/file:
|   /u01/oracle/admin/ora11g/dpdump/
|   awrdat_70441_70885-vpos-primar.dmp    <--提示的文件名
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Load Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR load operation can be
|  monitored in the following directory/file:
|   /u01/oracle/admin/ora11g/dpdump/
|   awrdat_70441_70885-vpos-primar.log
|
DBMS_DATAPUMP.ADD_FILE(dump file)
ORA-39001: invalid argument value
Exception encountered in AWR_LOAD
begin
*
ERROR at line 1:
ORA-20115: datapump import encountered error:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file
"/u01/oracle/admin/ora11g/dpdump/awrdat_70441_70885-vpos-primar.dmp" for read
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 1717
ORA-06512: at line 3
begin
*
ERROR at line 1:
ORA-20106: AWR tables do not exist for the 'AWR_STAGE' user
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 2920
ORA-00942: table or view does not exist
ORA-06512: at line 3
... Dropping AWR_STAGE user
End of AWR Load

这里我们可以看到,dmp文件名为31个字符，而在提示文件名的时候是30个字符，从而出现了dmp文件不存在而导致的相关错误，解决方案重命名dmp文件，名称不超过30个字符

对ORACLE比较熟悉的人都知道v$datafile.CREATION_TIME和v$datafile_header.CREATION_TIME这两个列都是表示数据文件的创建时间,而根据我们的经验可以知道几点:
1.当v$datafile.CREATION_TIME与v$datafile_header.CREATION_TIME不一致时数据库不能正常启动
2.v$datafile.CREATION_TIME的值来源于v$datafile_header.CREATION_TIME
3.而v$datafile_header.CREATION_TIME的值来源于数据文件头的块中的信息
现在就出现一个问题,数据块中的kcvfhcrt是一个16进制的数,如何实现在v$datafile和v$datafile_header中转为为了数据文件创建的日期
数据文件中存储创建数据文件日期内容

ub4 kcvfhcrt                             @108      0x2c67319c

v$datafile.CREATION_TIME值

SQL> select to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') xifenfei
   2 from v$datafile where file#=1;
XIFENFEI
-------------------
2011-03-05 05:26:52

如何通过kcvfhcrt值推算出来CREATION_TIME或者通过CREATION_TIME推断出来kcvfhcrt的值规则:
熟悉数据库SCN计数原理的人都知道,我们现在使用的数据库是从1988/01/01 00:00:00开始记录SCN,也就是说我们的数据库的使用最早时间只能是从1988年元旦凌晨开始，那么也就是说数据库记录的创建时间可以采用这个时间点为起点,然后每增加一秒,数据库的kcvfhcrt就增加1,但是ORACLE为了计算简便,每个月按照31天计算
通过时间推算出来kcvfhcrt值

--数据库记录时间起点
1988/01/01 00:00:00
--当前数据文件创建日志
2011/03/05 05:26:52
--两者相差时间
23年02月04日05时26分52秒
--计算相差秒
23*12*31*24*60*60+2*31*24*60*60+4*24*60*60+5*60*60+26*60+52=744960412
--kcvfhcrt值转换
2c67319c(16进制)=744960412(10进制)

通过kcvfhcrt计算CREATION_TIME值

SQL> select to_number('2c67319c','xxxxxxxxxxx') from dual;
TO_NUMBER('2C67319C','XXXXXXXXXXX')
-----------------------------------
                          744960412
SQL> select 744960412/(12*31*24*60*60) from dual;
744960412/(12*31*24*60*60)
--------------------------
                23.1780295
SQL> select mod(744960412,(12*31*24*60*60)) from dual;
MOD(744960412,(12*31*24*60*60))
-------------------------------
                        5722012
SQL> select 5722012/(31*24*60*60) from dual;
5722012/(31*24*60*60)
---------------------
           2.13635454
SQL> select mod(5722012,(31*24*60*60)) from dual;
MOD(5722012,(31*24*60*60))
--------------------------
                    365212
SQL> select 365212/(24*60*60) from dual;
365212/(24*60*60)
-----------------
       4.22699074
SQL> select mod(365212,(24*60*60)) from dual;
MOD(365212,(24*60*60))
----------------------
                 19612
SQL> select 19612/(60*60) from dual;
19612/(60*60)
-------------
   5.44777778
SQL> select mod(19612,(60*60)) from dual;
MOD(19612,(60*60))
------------------
              1612
SQL> select 1612/60 from dual;
   1612/60
----------
26.8666667
SQL> select mod(1612,60) from dual;
MOD(1612,60)
------------
          52
从这里可以得出23年2月4天5时26分52秒，与1988年01月01日00时00分00秒相加得到
2011年03月05日 5:26:52
SQL> select to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') from v$datafile where file#=1;
TO_CHAR(CREATION_TI
-------------------
2011-03-05 05:26:52

生成Statspack报告时候发现Snap Id为”#####”

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
  631690435        1 VODAPP       vodapp       T5440-1
Enter value for dbid: 631690435
Using 631690435 for database Id
Enter value for inst_num: 1
Using 1 for instance number
Completed Snapshots
                           Snap                    Snap
Instance     DB Name         Id   Snap Started    Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
vodapp       VODAPP       ##### 16 Oct 2012 17:00     5
                          ##### 16 Oct 2012 18:00     5
                          ##### 16 Oct 2012 19:00     5
                          ##### 16 Oct 2012 20:00     5
                          ##### 16 Oct 2012 21:00     5
                          ##### 16 Oct 2012 22:00     5
                          ##### 16 Oct 2012 23:00     5
                          …………
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:

因为没有办法定位到Snap Id,所以暂时无法准确的输入对应值,当然可以通过如下sql查询相应的Snap Id

select SNAP_ID,to_char(SNAP_TIME,'yyyy-mm-dd hh24:mi:ss') SNAP_TIME
from STATS$SNAPSHOT order by SNAP_ID;

虽然可以通过这个方面来曲线解决这个问题,但是还有比较完善一点的解决方法,我们阅读spcreate.sql相关脚本,修改相关程序来实现

--通过spcreate.sql发现
spcreate.sql调用@@sprepins
--编辑sprepins.sql
column instart_fmt noprint;
column inst_name   format a12  heading 'Instance';
column db_name     format a12  heading 'DB Name';
column snap_id     format 9990 heading 'Snap|Id';
-->(修改为)column snap_id     format 999990 heading 'Snap|Id';
column snapdat     format a17  heading 'Snap Started' just c;
column lvl         format 99   heading 'Snap|Level';
column commnt      format a22  heading 'Comment';

再次生成sp报告

SQL> @?/rdbms/admin/sprepins.sql
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
  631690435        1 VODAPP       vodapp       T5440-1
Enter value for dbid: 631690435
Using 631690435 for database Id
Enter value for inst_num: 1
Using 1 for instance number
Completed Snapshots
                             Snap                    Snap
Instance     DB Name           Id   Snap Started    Level Comment
------------ ------------ ------- ----------------- ----- ----------------------
vodapp       VODAPP         58916 16 Oct 2012 17:00     5
                            58917 16 Oct 2012 18:00     5
                            58918 16 Oct 2012 19:00     5
                            58919 16 Oct 2012 20:00     5
                            58920 16 Oct 2012 21:00     5
                            58921 16 Oct 2012 22:00     5
                            58922 16 Oct 2012 23:00     5
                            58923 17 Oct 2012 00:00     5