Bo's Oracle Station

查看: 2052|回复: 0

课程第21次(2018-07-26星期四)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-7-26 19:34:31 | 显示全部楼层 |阅读模式
ADR包含着DRA和HM以及Support WorkBench:
a.png


ADRCI可以看作文本形式的Support Workbench浏览器:
  1. [oracle@station90 bin]$ adrci

  2. ADRCI: Release 12.1.0.2.0 - Production on Thu Jul 26 19:52:44 2018

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

  4. ADR base = "/u01/app/oracle"
  5. adrci> help

  6. HELP [topic]
  7.    Available Topics:
  8.         CREATE REPORT
  9.         ECHO
  10.         EXIT
  11.         HELP
  12.         HOST
  13.         IPS
  14.         PURGE
  15.         RUN
  16.         SET BASE
  17.         SET BROWSER
  18.         SET CONTROL
  19.         SET ECHO
  20.         SET EDITOR
  21.         SET HOMES | HOME | HOMEPATH
  22.         SET TERMOUT
  23.         SHOW ALERT
  24.         SHOW BASE
  25.         SHOW CONTROL
  26.         SHOW HM_RUN
  27.         SHOW HOMES | HOME | HOMEPATH
  28.         SHOW INCDIR
  29.         SHOW INCIDENT
  30.         SHOW LOG
  31.         SHOW PROBLEM
  32.         SHOW REPORT
  33.         SHOW TRACEFILE
  34.         SPOOL
  35.         SELECT

  36. There are other commands intended to be used directly by Oracle, type
  37. "HELP EXTENDED" to see the list

  38. adrci> help SHOW ALERT

  39.   Usage: SHOW ALERT [-p <predicate_string>]  [-term]
  40.                     [ [-tail [num] [-f]] | [-file <alert_file_name>] ]
  41.   Purpose: Show alert messages.

  42.   Options:
  43.     [-p <predicate_string>]: The predicate string must be double-quoted.
  44.     The fields in the predicate are the fields:
  45.         ORIGINATING_TIMESTAMP         timestamp
  46.         NORMALIZED_TIMESTAMP          timestamp
  47.         ORGANIZATION_ID               text(65)
  48.         COMPONENT_ID                  text(65)
  49.         HOST_ID                       text(65)
  50.         HOST_ADDRESS                  text(17)
  51.         MESSAGE_TYPE                  number
  52.         MESSAGE_LEVEL                 number
  53.         MESSAGE_ID                    text(65)
  54.         MESSAGE_GROUP                 text(65)
  55.         CLIENT_ID                     text(65)
  56.         MODULE_ID                     text(65)
  57.         PROCESS_ID                    text(33)
  58.         THREAD_ID                     text(65)
  59.         USER_ID                       text(65)
  60.         INSTANCE_ID                   text(65)
  61.         DETAILED_LOCATION             text(161)
  62.         UPSTREAM_COMP_ID              text(101)
  63.         DOWNSTREAM_COMP_ID            text(101)
  64.         EXECUTION_CONTEXT_ID          text(101)
  65.         EXECUTION_CONTEXT_SEQUENCE    number
  66.         ERROR_INSTANCE_ID             number
  67.         ERROR_INSTANCE_SEQUENCE       number
  68.         MESSAGE_TEXT                  text(2049)
  69.         MESSAGE_ARGUMENTS             text(129)
  70.         SUPPLEMENTAL_ATTRIBUTES       text(129)
  71.         SUPPLEMENTAL_DETAILS          text(4000)
  72.         PROBLEM_KEY                   text(65)

  73.     [-tail [num] [-f]]: Output last part of the alert messages and
  74.     output latest messages as the alert log grows. If num is not specified,
  75.     the last 10 messages are displayed. If "-f" is specified, new data
  76.     will append at the end as new alert messages are generated.

  77.     [-term]: Direct results to terminal. If this option is not specified,
  78.     the results will be open in an editor.
  79.     By default, it will open in emacs, but "set editor" can be used
  80.     to set other editors.

  81.     [-file <alert_file_name>]: Allow users to specify an alert file which
  82.     may not be in ADR. <alert_file_name> must be specified with full path.
  83.     Note that this option cannot be used with the -tail option

  84.   Examples:  
  85.     show alert
  86.     show alert -p "message_text like '%incident%'"
  87.     show alert -tail 20

  88. adrci> show alert -p "message_text like '%1578%'"

  89. Choose the home from which to view the alert log:

  90. 1: diag/tnslsnr/station90/listener
  91. 2: diag/clients/user_oracle/host_3533360404_82
  92. 3: diag/clients/user_oracle/host_3533360404_80
  93. 4: diag/clients/user_oracle/host_3533360404_11
  94. 5: diag/clients/user_root/host_3533360404_80
  95. 6: diag/rdbms/orcl/orcl
  96. 7: diag/rdbms/beqn_tspitr_orcl/beqn
  97. 8: diag/rdbms/qxxa_tspitr_orcl/qxxA
  98. 9: diag/rdbms/rcat/rcat
  99. 10: diag/rdbms/emrep/emrep
  100. 11: diag/rdbms/bcbd_tspitr_orcl/BcBD
  101. 12: diag/rdbms/cdb2/cdb2
  102. 13: diag/rdbms/swfr_tspitr_orcl/swfr
  103. 14: diag/rdbms/dbtest/dbtest
  104. 15: diag/rdbms/agrx_tspitr_orcl/Agrx
  105. 16: diag/asm/+asm/+ASM
  106. Q: to quit

  107. Please select option: 6
  108. Output the results to file: /tmp/alert_12342_1406_orcl_1.ado

  109. Please select option:
复制代码
以下是命令行制作Support Workbench包:
  1. adrci> help  SET HOMES

  2.   Usage:  SET HOMES | HOME| HOMEPATH <homepath_str1 homepath_str2 ...>

  3.   Purpose: Set the ADR homes to query in the current ADRCI session.

  4.   Arguments:
  5.     <homepath_str1 homepath_str2 ...>: The paths of the home,
  6.     relative to the ADR base.

  7.   Note:
  8.     The "diag" directory name can be omitted from the homepath_str.
  9.     If the specified path contains multiple homes, for instance, it is
  10.     set to the database ID which is running RAC. All of the ADR homes
  11.     corresponding to the instances of the database will be added to the
  12.     current ADRCI home settings.

  13.   Example:  
  14.     set homepath diag/rdbms/aime3/aime3 diag/rdbms/aime3/aime32
  15.     set homepath rdbms/aime3

  16. adrci> set homepath diag/rdbms/orcl/orcl
  17. adrci> help SHOW PROBLEM

  18.   Usage: SHOW PROBLEM [-p <predicate_string>]
  19.                       [-last <num> | -all]
  20.                       [-orderby (field1, field2, ...) [ASC|DSC]]

  21.   Purpose: Show the problem information. By default, this command will
  22.            only show the last 50 problems.

  23.   Options:
  24.     [-p <predicate_string>]: The predicate string must be double-quoted.
  25.     The field names that users can specify in the predicate are:
  26.         PROBLEM_ID                    number
  27.         PROBLEM_KEY                   text(550)
  28.         FIRST_INCIDENT                number
  29.         FIRSTINC_TIME                 timestamp
  30.         LAST_INCIDENT                 number
  31.         LASTINC_TIME                  timestamp
  32.         IMPACT1                       number
  33.         IMPACT2                       number
  34.         IMPACT3                       number
  35.         IMPACT4                       number
  36.         SERVICE_REQUEST               text(64)
  37.         BUG_NUMBER                    text(64)

  38.     [-last <num> | -all]: This option allows users to either select
  39.     the last <num> of qualified problems to show or to show all the
  40.     qualified problems. If this option is not specified, this command
  41.     will only show 50 incidents.

  42.     [-orderby (field1, field2, ...) [ASC|DSC]]: If specified, the results
  43.     will be ordered by the specified fields' values. By default, it will be
  44.     in the ascending order unless "DSC" is specified. Note that the field
  45.     names that can be specified here are from the "PROBLEM" relation.

  46.   Examples:  
  47.     show problem
  48.     show problem -p "problem_id>123"

  49. adrci> show problem

  50. ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
  51. *************************************************************************
  52. PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                             
  53. -------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
  54. 2                    ORA 603                                                     13506                2014-08-06 17:34:15.747000 +08:00      
  55. 1                    ORA 600 [1433]                                              13281                2014-08-06 17:36:00.102000 +08:00      
  56. 3                    ORA 600 [kdliOpen]                                          89088                2018-07-15 17:23:37.457000 +08:00      
  57. 5                    ORA 445                                                     200364               2018-07-23 09:54:23.496000 +08:00      
  58. 4                    ORA 1578                                                    205036               2018-07-24 21:32:45.753000 +08:00      
  59. 5 rows fetched

  60. adrci> help ips

  61. HELP IPS [topic]
  62.    Available Topics:
  63.         ADD
  64.         ADD FILE
  65.         ADD NEW INCIDENTS
  66.         CHECK REMOTE KEYS
  67.         COPY IN FILE
  68.         COPY OUT FILE
  69.         CREATE PACKAGE
  70.         DELETE PACKAGE
  71.         FINALIZE PACKAGE
  72.         GENERATE PACKAGE
  73.         GET MANIFEST
  74.         GET METADATA
  75.         GET REMOTE KEYS
  76.         PACK
  77.         REMOVE
  78.         REMOVE FILE
  79.         SET CONFIGURATION
  80.         SHOW CONFIGURATION
  81.         SHOW FILES
  82.         SHOW INCIDENTS
  83.         SHOW PACKAGE
  84.         UNPACK FILE
  85.         UNPACK PACKAGE
  86.         USE REMOTE KEYS
  87. adrci> help  CREATE PACKAGE
  88. DIA-48433: Unknown help topic

  89. adrci> help ips CREATE PACKAGE

  90.   Usage:  IPS CREATE PACKAGE
  91.              [INCIDENT <incid> | PROBLEM <prob_id> | PROBLEMKEY <prob_key> |
  92.               SECONDS <seconds> | TIME <start_time> TO <end_time>]
  93.              [CORRELATE BASIC | TYPICAL | ALL]

  94.   Purpose: Create a package, and optionally select contents for the package.

  95.   Arguments:
  96.     <incid>:      ID of incident to use for selecting package contents.
  97.     <prob_id>:    ID of problem to use for selecting package contents.
  98.     <prob_key>:   Problem key to use for selecting package contents.
  99.     <seconds>:    Number of seconds before now for selecting package contents.
  100.     <start_time>: Start of time range to look for incidents in.
  101.     <end_time>:   End of time range to look for incidents in.

  102.   Options:
  103.     CORRELATE BASIC:   The package will include the incident dumps, and the
  104.                        incident process trace files.
  105.                        Additional incidents can be included automatically,
  106.                        if they share relevant correlation keys.
  107.     CORRELATE TYPICAL: The package will include the incident dumps, and all
  108.                        trace files that were modified in a time window around
  109.                        each incident.
  110.                        Additional incidents can be included automatically,
  111.                        if they share relevant correlation keys, or occurred
  112.                        in a time window around the main incidents.
  113.     CORRELATE ALL:     The package will include the incident dumps, and all
  114.                        trace files that were modified between the first
  115.                        selected incident and the last selected incident.
  116.                        Additional incidents can be included automatically,
  117.                        if they occurred in the same time range.

  118.   Notes:
  119.     If no package contents are specified (incident, problem, etc), an empty
  120.     package will be created. Files and incidents can be added later.
  121.     If no correlation level is specified, the default level is used.
  122.     The default is normally TYPICAL, but it can be changed using the command
  123.     IPS SET CONFIGURATION.

  124.   Example:
  125.     ips create package incident 861;
  126.     ips create package time '2006-12-31 23:59:59.00 -07:00' to
  127.         '2007-01-01 01:01:01.00 -07:00';

  128. adrci> ips create package PROBLEM 4;

  129. Created package 2 based on problem id 4, correlation level typical
  130. adrci> adrci>
  131. adrci> help GENERATE PACKAGE
  132. DIA-48433: Unknown help topic

  133. adrci> help ips GENERATE PACKAGE

  134.   Usage:  IPS GENERATE PACKAGE <package_id> [IN <path>]
  135.              [COMPLETE | INCREMENTAL]

  136.   Purpose: Create a physical package (zip file) in target directory.

  137.   Arguments:
  138.     <package_id>: ID of package to create physical package file for.
  139.     <path>:       Path where the physical package file should be generated.

  140.   Options:
  141.     COMPLETE:    The package will include all package files, even if a
  142.                  previous package sequence has been generated.
  143.                  This is the default.
  144.     INCREMENTAL: The package will only include files that have been added
  145.                  or changed since the last package generation.

  146.   Notes:
  147.     If no target path is specified, the physical package file is generated
  148.     in the current working directory.

  149.   Example:
  150.     ips generate package 12 in /tmp

  151. adrci> ips generate package  2 in /home/oracle/
  152. Generated package 2 in file /home/oracle/ORA1578_20180726195647_COM_1.zip, mode complete
  153. adrci>
复制代码

aaa.png


HM的两种形式:1.应激性, 2.手工的
c.png

  1. select  t.tablespace_name,t.segment_space_management
  2.   from dba_tablespaces t;
  3.   
  4.   ---pct_used
  5.   
  6.   create tablespace tbs1 datafile size 5M  segment space management manual;
  7.   
  8.   select t.owner, t.table_name , t.pct_free , t.pct_used  , t.pct_increase
  9.    from dba_tables t
  10.    where t.owner='HR' and t.table_name  like 'T05207%';
  11.    
复制代码
  1. [root@station90 ~]# su - oracle
  2. [oracle@station90 ~]$ sqlplus /nolog

  3. SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 26 20:56:33 2018

  4. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  5. SQL> conn hr/oracle_4U
  6. Connected.
  7. SQL> create table t05207_auto ( a number )  tablespace users;

  8. Table created.

  9. SQL> create table t05207_manual ( a number )  tablespace  tbs1;

  10. Table created.

  11. SQL> alter table t05207_auto  pctfree  20;

  12. Table altered.

  13. SQL> alter table t05207_manual pctfree  20   pctused 50 ;

  14. Table altered.

  15. SQL> alter table t05207_auto  pctfree  20  pctused 50 ;

  16. Table altered.

  17. SQL>
复制代码
   OWNERTABLE_NAMEPCT_FREEPCT_USEDPCT_INCREASE
1HRT05207_MANUAL2050
2HRT05207_AUTO20

段的第一个块和段头块:
  1. select  t.tablespace_name,t.extent_management,t.allocation_type
  2.   from dba_tablespaces t;
  3.   
  4.   select  * from dba_extents e where e.owner='HR' and e.segment_name='T04209_UNAME';
复制代码
   OWNERSEGMENT_NAMEPARTITION_NAMESEGMENT_TYPETABLESPACE_NAMEEXTENT_IDFILE_IDBLOCK_IDBYTESBLOCKSRELATIVE_FNO
1HRT04209_UNAME TABLEEXAMPLE054886553685
2HRT04209_UNAME TABLEEXAMPLE156966553685
3HRT04209_UNAME TABLEEXAMPLE257046553685
4HRT04209_UNAME TABLEEXAMPLE357126553685
5HRT04209_UNAME TABLEEXAMPLE4514086553685
6HRT04209_UNAME TABLEEXAMPLE5514166553685
7HRT04209_UNAME TABLEEXAMPLE6514246553685
8HRT04209_UNAME TABLEEXAMPLE7514326553685
9HRT04209_UNAME TABLEEXAMPLE8514406553685
10HRT04209_UNAME TABLEEXAMPLE9514486553685
11HRT04209_UNAME TABLEEXAMPLE10514566553685
12HRT04209_UNAME TABLEEXAMPLE11514646553685
13HRT04209_UNAME TABLEEXAMPLE12514726553685
14HRT04209_UNAME TABLEEXAMPLE135103926553685
15HRT04209_UNAME TABLEEXAMPLE145104006553685
16HRT04209_UNAME TABLEEXAMPLE155104086553685
17HRT04209_UNAME TABLEEXAMPLE16551210485761285

  1.   select  * from dba_segments s where s.owner='HR' and s.segment_name='T04209_UNAME';
复制代码
   OWNERSEGMENT_NAMEPARTITION_NAMESEGMENT_TYPESEGMENT_SUBTYPETABLESPACE_NAMEHEADER_FILEHEADER_BLOCKBYTESBLOCKSEXTENTSINITIAL_EXTENTNEXT_EXTENTMIN_EXTENTSMAX_EXTENTSMAX_SIZERETENTIONMINRETENTIONPCT_INCREASEFREELISTSFREELIST_GROUPSRELATIVE_FNOBUFFER_POOLFLASH_CACHECELL_FLASH_CACHE
1HRT04209_UNAME TABLEASSMEXAMPLE5490209715225617655361048576121474836452147483645 5DEFAULTDEFAULTDEFAULT

OLTP密集的系统,建议创建uniform size的表空间:
  1. create tablespace tbs2 datafile size 5M uniform size 512K;
  2.   
  3.   alter table hr.t04209_uname move tablespace tbs2;
  4.   
  5.    
  6.   select  * from dba_extents e where e.owner='HR' and e.segment_name='T04209_UNAME';
复制代码
   OWNERSEGMENT_NAMEPARTITION_NAMESEGMENT_TYPETABLESPACE_NAMEEXTENT_IDFILE_IDBLOCK_IDBYTESBLOCKSRELATIVE_FNO
1HRT04209_UNAME TABLETBS208128524288648
2HRT04209_UNAME TABLETBS218192524288648
3HRT04209_UNAME TABLETBS228256524288648
4HRT04209_UNAME TABLETBS238320524288648


  1.   alter tablespace tbs2 add datafile size 5M;
  2.   
  3.   select  * from dba_data_files where tablespace_name='TBS1';
  4.   
  5.   alter database datafile '+DATA/orcl/datafile/tbs1.267.982529779' resize 10M;
  6.   
  7.   --------------------------
  8.   create bigfile tablespace tbs3 datafile size 5M autoextend on maxsize 4T;
复制代码
permenet空间用量:
  1. select  

  2. (select   sum(bytes)/1024/1024 from dba_data_files  
  3. where tablespace_name='SYSTEM')
  4. -

  5. ( select   sum(bytes)/1024/1024 from dba_free_space
  6. where tablespace_name='SYSTEM')  from dual;
复制代码
undo空间用量:
  1. select  sum(u.bytes)/1024/1024
  2.   from dba_undo_extents u
  3.   where u.status <> 'EXPIRED';
复制代码
temp:

  1. select   sum(m.BYTES)/1024/1024
  2.   from v_$temp_extent_map   m;



  3. select  sum(p.BLOCKS_CACHED)/1024/1024  ,
  4.       sum(p.EXTENTS_USED  )/1024/1024
  5. from v_$temp_extent_pool  p;
复制代码







回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 09:38 , Processed in 0.040400 second(s), 27 queries .

快速回复 返回顶部 返回列表