ADR初级介绍

发表于 2012 年 12 月 22 日 由 惜分飞

自动诊断存储库(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是非常强大工具.

发表在 Oracle | 评论关闭

ORACLE 12C可以通过expdp导出view数据

发表于 2012 年 12 月 20 日 由 惜分飞

有时候,多么的希望ORACLE能够导出某个视图中的数据,然后通过这个视图来迁移需要的数据,现在ORACLE 12C通过expdp的views_as_tables来实现了该功能,把视图当作一个普通表从而导出数据,导入的时候直接和一个正常表一样,通过视图的导出,表的导入来实现相关需求
准备测试环境

SQL> SELECT * FROM V$VERSION;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                

SQL> create table t_lx as select * from v$log; 

Table created.

SQL> create table t_xl as select * from v$logfile;

Table created.

SQL> create view v_xifenfei  as
  2   SELECT thread#,
  3           a.sequence#,
  4           a.group#,
  5           TO_CHAR (first_change#, '9999999999999999') "SCN",
  6           a.status,
  7           MEMBER
  8      FROM t_lx a, t_xl b
  9     WHERE a.group# = B.GROUP#
 10  ORDER BY a.sequence# DESC;

View created.

SQL> col member for a50    
SQL> set lines 134
SQL> select * from v_xifenfei;

   THREAD#  SEQUENCE#     GROUP# SCN               STATUS           MEMBER
---------- ---------- ---------- ----------------- ---------------- -------------------------------------------
         1         30          3            391892 CURRENT          /u01/app/oracle/oradata/xifenfei/redo03.log
         1         29          2            377363 INACTIVE         /u01/app/oracle/oradata/xifenfei/redo02.log
         1         28          1            374892 INACTIVE         /u01/app/oracle/oradata/xifenfei/redo01.log

expdp结合VIEWS_AS_TABLES导出视图

[oracle@xifenfei ~]$ expdp xff/xifenfei views_as_tables=v_xifenfei directory=data_pump_dir dumpfile=xifenfei.dmp

Export: Release 12.1.0.0.2        on Sun Dec 16 07:56:48 2012

Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit     
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "XFF"."SYS_EXPORT_TABLE_01":xff/******** views_as_tables=v_xifenfei directory=data_pump_dir
 dumpfile=xifenfei.dmp 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 16 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "XFF"."V_XIFENFEI"                          7.390 KB       3 rows
Master table "XFF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XFF.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/xifenfei/dpdump/xifenfei.dmp
Job "XFF"."SYS_EXPORT_TABLE_01" successfully completed at Sun Dec 16 07:58:17 2012 elapsed 0 00:00:56

impdp导入数据

[oracle@xifenfei ~]$ impdp xff/xifenfei remap_table=v_xifenfei:v_xff directory=data_pump_dir dumpfile=xifenfei.dmp

Import: Release 12.1.0.0.2        on Sun Dec 16 08:06:06 2012

Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit     
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":xff/******** remap_table=v_xifenfei:v_xff directory=data_pump_dir
 dumpfile=xifenfei.dmp 
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "XFF"."V_XFF"                               7.390 KB       3 rows
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sun Dec 16 08:06:20 2012 elapsed 0 00:00:10

验证数据

SQL> COL OBJECT_NAME FOR A20
SQL> select object_type,object_name from user_objectS where object_name like 'V_X%';

OBJECT_TYPE             OBJECT_NAME
----------------------- --------------------
VIEW                    V_XIFENFEI
TABLE                   V_XFF

SQL> col member for a50
SQL> set lines 134
SQL> select * from v_XFF;

   THREAD#  SEQUENCE#     GROUP# SCN               STATUS           MEMBER
---------- ---------- ---------- ----------------- ---------------- --------------------------------------------
         1         30          3            391892 CURRENT          /u01/app/oracle/oradata/xifenfei/redo03.log
         1         29          2            377363 INACTIVE         /u01/app/oracle/oradata/xifenfei/redo02.log
         1         28          1            374892 INACTIVE         /u01/app/oracle/oradata/xifenfei/redo01.log

通过测试证明在12C中ORACLE的expdp/impdp可以实现导出视图数据,进入导入到[其他库]其他表中

发表在 ORACLE 12C | 标签为 | 3 条评论

ORCLE 12C 增加列,无默认值

发表于 2012 年 12 月 19 日 由 惜分飞

对11gR2比较熟悉的朋友应该比较清楚,在该版本中引入了一个新的特性,能够快速的增加一个新列,具体见Oracle 11g增加列,并带默认值的新特性,但是这个功能在该版本中总有个不足,需要设置默认值,在有些情况下,有些列就是不需要默认值,在12C的版本中,解决了这个鸡肋,能够快速增加一个列而且可以是不指定默认值(默认值为NULL)
数据库12C版本

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0

创建模拟表

SQL> create table t_xifenfei(id number,name varchar2(20));

Table created.

SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(20)

SQL> insert into t_xifenfei values (1,'www.xifenfei.com');

1 row created.

SQL> insert into t_xifenfei values (2,'www.xifenfei.com');

1 row created.

SQL> insert into t_xifenfei values (3,'www.xifenfei.com');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_xifenfei;

        ID NAME
---------- --------------------
         1 www.xifenfei.com
         2 www.xifenfei.com
         3 www.xifenfei.com

第一次dump block

SQL> select   rowid, 
  2   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3   dbms_rowid.rowid_block_number(rowid)blockno,
  4   dbms_rowid.rowid_row_number(rowid) rowno
  5   from t_xifenfei ;

ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAAEy3AAEAAAAGGAAA          4        390          0
AAAEy3AAEAAAAGGAAB          4        390          1
AAAEy3AAEAAAAGGAAC          4        390          2

SQL> alter system dump datafile 4 block 390;

System altered.

--dump block 
block_row_dump:
tab 0, row 0, @0x1f81
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 1, @0x1f6a
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 2, @0x1f53
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
end_of_block_dump

增加不含默认值列

SQL> alter table t_xifenfei add c_xff varchar2(100);

Table altered.

SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(20)
 C_XFF                                              VARCHAR2(100)
--证明增加列无默认值

SQL> insert into t_xifenfei values(4,'www.xifenfei.com','www.orasos.com');

1 row created.

SQL> commit;

Commit complete.

第二次dump block 

SQL> alter system checkpoint;

System altered.

SQL>  alter system dump datafile 4 block 390;

System altered.

--block dump 
block_row_dump:
tab 0, row 0, @0x1f81
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 1, @0x1f6a
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 2, @0x1f53
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 3, @0x1f2d
tl: 38 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 05
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
col  2: [14]  77 77 77 2e 6f 72 61 73 6f 73 2e 63 6f 6d
end_of_block_dump

从这里可以明显的看出来,前面的三条记录只有2列,但是四条记录有3列,证明使用了11gR2的新特性,这里可以使用null的默认值,证明比以往版本新特性增强.

发表在 ORACLE 12C | 标签为 | 一条评论