课程第21次（2018-07-26星期四）

botang

ADR包含着DRA和HM以及Support WorkBench：



ADRCI可以看作文本形式的Support Workbench浏览器：

1. [oracle@station90 bin]$ adrci
   
2. 
   
3. ADRCI: Release 12.1.0.2.0 - Production on Thu Jul 26 19:52:44 2018
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. ADR base = "/u01/app/oracle"
   
8. adrci> help
   
9. 
   
10. HELP [topic]
    
11.    Available Topics:
    
12.         CREATE REPORT
    
13.         ECHO
    
14.         EXIT
    
15.         HELP
    
16.         HOST
    
17.         IPS
    
18.         PURGE
    
19.         RUN
    
20.         SET BASE
    
21.         SET BROWSER
    
22.         SET CONTROL
    
23.         SET ECHO
    
24.         SET EDITOR
    
25.         SET HOMES | HOME | HOMEPATH
    
26.         SET TERMOUT
    
27.         SHOW ALERT
    
28.         SHOW BASE
    
29.         SHOW CONTROL
    
30.         SHOW HM_RUN
    
31.         SHOW HOMES | HOME | HOMEPATH
    
32.         SHOW INCDIR
    
33.         SHOW INCIDENT
    
34.         SHOW LOG
    
35.         SHOW PROBLEM
    
36.         SHOW REPORT
    
37.         SHOW TRACEFILE
    
38.         SPOOL
    
39.         SELECT
    
40. 
    
41. There are other commands intended to be used directly by Oracle, type
    
42. "HELP EXTENDED" to see the list
    
43. 
    
44. adrci> help SHOW ALERT
    
45. 
    
46.   Usage: SHOW ALERT [-p <predicate_string>]  [-term]
    
47.                     [ [-tail [num] [-f]] | [-file <alert_file_name>] ]
    
48.   Purpose: Show alert messages.
    
49. 
    
50.   Options:
    
51.     [-p <predicate_string>]: The predicate string must be double-quoted.
    
52.     The fields in the predicate are the fields:
    
53.         ORIGINATING_TIMESTAMP         timestamp
    
54.         NORMALIZED_TIMESTAMP          timestamp
    
55.         ORGANIZATION_ID               text(65)
    
56.         COMPONENT_ID                  text(65)
    
57.         HOST_ID                       text(65)
    
58.         HOST_ADDRESS                  text(17)
    
59.         MESSAGE_TYPE                  number
    
60.         MESSAGE_LEVEL                 number
    
61.         MESSAGE_ID                    text(65)
    
62.         MESSAGE_GROUP                 text(65)
    
63.         CLIENT_ID                     text(65)
    
64.         MODULE_ID                     text(65)
    
65.         PROCESS_ID                    text(33)
    
66.         THREAD_ID                     text(65)
    
67.         USER_ID                       text(65)
    
68.         INSTANCE_ID                   text(65)
    
69.         DETAILED_LOCATION             text(161)
    
70.         UPSTREAM_COMP_ID              text(101)
    
71.         DOWNSTREAM_COMP_ID            text(101)
    
72.         EXECUTION_CONTEXT_ID          text(101)
    
73.         EXECUTION_CONTEXT_SEQUENCE    number
    
74.         ERROR_INSTANCE_ID             number
    
75.         ERROR_INSTANCE_SEQUENCE       number
    
76.         MESSAGE_TEXT                  text(2049)
    
77.         MESSAGE_ARGUMENTS             text(129)
    
78.         SUPPLEMENTAL_ATTRIBUTES       text(129)
    
79.         SUPPLEMENTAL_DETAILS          text(4000)
    
80.         PROBLEM_KEY                   text(65)
    
81. 
    
82.     [-tail [num] [-f]]: Output last part of the alert messages and
    
83.     output latest messages as the alert log grows. If num is not specified,
    
84.     the last 10 messages are displayed. If "-f" is specified, new data
    
85.     will append at the end as new alert messages are generated.
    
86. 
    
87.     [-term]: Direct results to terminal. If this option is not specified,
    
88.     the results will be open in an editor.
    
89.     By default, it will open in emacs, but "set editor" can be used
    
90.     to set other editors.
    
91. 
    
92.     [-file <alert_file_name>]: Allow users to specify an alert file which
    
93.     may not be in ADR. <alert_file_name> must be specified with full path.
    
94.     Note that this option cannot be used with the -tail option
    
95. 
    
96.   Examples:  
    
97.     show alert
    
98.     show alert -p "message_text like '%incident%'"
    
99.     show alert -tail 20
    
100. 
     
101. adrci> show alert -p "message_text like '%1578%'"
     
102. 
     
103. Choose the home from which to view the alert log:
     
104. 
     
105. 1: diag/tnslsnr/station90/listener
     
106. 2: diag/clients/user_oracle/host_3533360404_82
     
107. 3: diag/clients/user_oracle/host_3533360404_80
     
108. 4: diag/clients/user_oracle/host_3533360404_11
     
109. 5: diag/clients/user_root/host_3533360404_80
     
110. 6: diag/rdbms/orcl/orcl
     
111. 7: diag/rdbms/beqn_tspitr_orcl/beqn
     
112. 8: diag/rdbms/qxxa_tspitr_orcl/qxxA
     
113. 9: diag/rdbms/rcat/rcat
     
114. 10: diag/rdbms/emrep/emrep
     
115. 11: diag/rdbms/bcbd_tspitr_orcl/BcBD
     
116. 12: diag/rdbms/cdb2/cdb2
     
117. 13: diag/rdbms/swfr_tspitr_orcl/swfr
     
118. 14: diag/rdbms/dbtest/dbtest
     
119. 15: diag/rdbms/agrx_tspitr_orcl/Agrx
     
120. 16: diag/asm/+asm/+ASM
     
121. Q: to quit
     
122. 
     
123. Please select option: 6
     
124. Output the results to file: /tmp/alert_12342_1406_orcl_1.ado
     
125. 
     
126. Please select option:
     

复制代码

以下是命令行制作Support Workbench包：

1. adrci> help  SET HOMES
   
2. 
   
3.   Usage:  SET HOMES | HOME| HOMEPATH <homepath_str1 homepath_str2 ...>
   
4. 
   
5.   Purpose: Set the ADR homes to query in the current ADRCI session.
   
6. 
   
7.   Arguments:
   
8.     <homepath_str1 homepath_str2 ...>: The paths of the home,
   
9.     relative to the ADR base.
   
10. 
    
11.   Note:
    
12.     The "diag" directory name can be omitted from the homepath_str.
    
13.     If the specified path contains multiple homes, for instance, it is
    
14.     set to the database ID which is running RAC. All of the ADR homes
    
15.     corresponding to the instances of the database will be added to the
    
16.     current ADRCI home settings.
    
17. 
    
18.   Example:  
    
19.     set homepath diag/rdbms/aime3/aime3 diag/rdbms/aime3/aime32
    
20.     set homepath rdbms/aime3
    
21. 
    
22. adrci> set homepath diag/rdbms/orcl/orcl
    
23. adrci> help SHOW PROBLEM
    
24. 
    
25.   Usage: SHOW PROBLEM [-p <predicate_string>]
    
26.                       [-last <num> | -all]
    
27.                       [-orderby (field1, field2, ...) [ASC|DSC]]
    
28. 
    
29.   Purpose: Show the problem information. By default, this command will
    
30.            only show the last 50 problems.
    
31. 
    
32.   Options:
    
33.     [-p <predicate_string>]: The predicate string must be double-quoted.
    
34.     The field names that users can specify in the predicate are:
    
35.         PROBLEM_ID                    number
    
36.         PROBLEM_KEY                   text(550)
    
37.         FIRST_INCIDENT                number
    
38.         FIRSTINC_TIME                 timestamp
    
39.         LAST_INCIDENT                 number
    
40.         LASTINC_TIME                  timestamp
    
41.         IMPACT1                       number
    
42.         IMPACT2                       number
    
43.         IMPACT3                       number
    
44.         IMPACT4                       number
    
45.         SERVICE_REQUEST               text(64)
    
46.         BUG_NUMBER                    text(64)
    
47. 
    
48.     [-last <num> | -all]: This option allows users to either select
    
49.     the last <num> of qualified problems to show or to show all the
    
50.     qualified problems. If this option is not specified, this command
    
51.     will only show 50 incidents.
    
52. 
    
53.     [-orderby (field1, field2, ...) [ASC|DSC]]: If specified, the results
    
54.     will be ordered by the specified fields' values. By default, it will be
    
55.     in the ascending order unless "DSC" is specified. Note that the field
    
56.     names that can be specified here are from the "PROBLEM" relation.
    
57. 
    
58.   Examples:  
    
59.     show problem
    
60.     show problem -p "problem_id>123"
    
61. 
    
62. adrci> show problem
    
63. 
    
64. ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
    
65. *************************************************************************
    
66. PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                             
    
67. -------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
    
68. 2                    ORA 603                                                     13506                2014-08-06 17:34:15.747000 +08:00      
    
69. 1                    ORA 600 [1433]                                              13281                2014-08-06 17:36:00.102000 +08:00      
    
70. 3                    ORA 600 [kdliOpen]                                          89088                2018-07-15 17:23:37.457000 +08:00      
    
71. 5                    ORA 445                                                     200364               2018-07-23 09:54:23.496000 +08:00      
    
72. 4                    ORA 1578                                                    205036               2018-07-24 21:32:45.753000 +08:00      
    
73. 5 rows fetched
    
74. 
    
75. adrci> help ips
    
76. 
    
77. HELP IPS [topic]
    
78.    Available Topics:
    
79.         ADD
    
80.         ADD FILE
    
81.         ADD NEW INCIDENTS
    
82.         CHECK REMOTE KEYS
    
83.         COPY IN FILE
    
84.         COPY OUT FILE
    
85.         CREATE PACKAGE
    
86.         DELETE PACKAGE
    
87.         FINALIZE PACKAGE
    
88.         GENERATE PACKAGE
    
89.         GET MANIFEST
    
90.         GET METADATA
    
91.         GET REMOTE KEYS
    
92.         PACK
    
93.         REMOVE
    
94.         REMOVE FILE
    
95.         SET CONFIGURATION
    
96.         SHOW CONFIGURATION
    
97.         SHOW FILES
    
98.         SHOW INCIDENTS
    
99.         SHOW PACKAGE
    
100.         UNPACK FILE
     
101.         UNPACK PACKAGE
     
102.         USE REMOTE KEYS
     
103. adrci> help  CREATE PACKAGE
     
104. DIA-48433: Unknown help topic
     
105. 
     
106. adrci> help ips CREATE PACKAGE
     
107. 
     
108.   Usage:  IPS CREATE PACKAGE
     
109.              [INCIDENT <incid> | PROBLEM <prob_id> | PROBLEMKEY <prob_key> |
     
110.               SECONDS <seconds> | TIME <start_time> TO <end_time>]
     
111.              [CORRELATE BASIC | TYPICAL | ALL]
     
112. 
     
113.   Purpose: Create a package, and optionally select contents for the package.
     
114. 
     
115.   Arguments:
     
116.     <incid>:      ID of incident to use for selecting package contents.
     
117.     <prob_id>:    ID of problem to use for selecting package contents.
     
118.     <prob_key>:   Problem key to use for selecting package contents.
     
119.     <seconds>:    Number of seconds before now for selecting package contents.
     
120.     <start_time>: Start of time range to look for incidents in.
     
121.     <end_time>:   End of time range to look for incidents in.
     
122. 
     
123.   Options:
     
124.     CORRELATE BASIC:   The package will include the incident dumps, and the
     
125.                        incident process trace files.
     
126.                        Additional incidents can be included automatically,
     
127.                        if they share relevant correlation keys.
     
128.     CORRELATE TYPICAL: The package will include the incident dumps, and all
     
129.                        trace files that were modified in a time window around
     
130.                        each incident.
     
131.                        Additional incidents can be included automatically,
     
132.                        if they share relevant correlation keys, or occurred
     
133.                        in a time window around the main incidents.
     
134.     CORRELATE ALL:     The package will include the incident dumps, and all
     
135.                        trace files that were modified between the first
     
136.                        selected incident and the last selected incident.
     
137.                        Additional incidents can be included automatically,
     
138.                        if they occurred in the same time range.
     
139. 
     
140.   Notes:
     
141.     If no package contents are specified (incident, problem, etc), an empty
     
142.     package will be created. Files and incidents can be added later.
     
143.     If no correlation level is specified, the default level is used.
     
144.     The default is normally TYPICAL, but it can be changed using the command
     
145.     IPS SET CONFIGURATION.
     
146. 
     
147.   Example:
     
148.     ips create package incident 861;
     
149.     ips create package time '2006-12-31 23:59:59.00 -07:00' to
     
150.         '2007-01-01 01:01:01.00 -07:00';
     
151. 
     
152. adrci> ips create package PROBLEM 4;
     
153. 
     
154. Created package 2 based on problem id 4, correlation level typical
     
155. adrci> adrci>
     
156. adrci> help GENERATE PACKAGE
     
157. DIA-48433: Unknown help topic
     
158. 
     
159. adrci> help ips GENERATE PACKAGE
     
160. 
     
161.   Usage:  IPS GENERATE PACKAGE <package_id> [IN <path>]
     
162.              [COMPLETE | INCREMENTAL]
     
163. 
     
164.   Purpose: Create a physical package (zip file) in target directory.
     
165. 
     
166.   Arguments:
     
167.     <package_id>: ID of package to create physical package file for.
     
168.     <path>:       Path where the physical package file should be generated.
     
169. 
     
170.   Options:
     
171.     COMPLETE:    The package will include all package files, even if a
     
172.                  previous package sequence has been generated.
     
173.                  This is the default.
     
174.     INCREMENTAL: The package will only include files that have been added
     
175.                  or changed since the last package generation.
     
176. 
     
177.   Notes:
     
178.     If no target path is specified, the physical package file is generated
     
179.     in the current working directory.
     
180. 
     
181.   Example:
     
182.     ips generate package 12 in /tmp
     
183. 
     
184. adrci> ips generate package  2 in /home/oracle/
     
185. Generated package 2 in file /home/oracle/ORA1578_20180726195647_COM_1.zip, mode complete
     
186. adrci>
     

复制代码

HM的两种形式：1.应激性， 2.手工的

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. 
   
4. SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 26 20:56:33 2018
   
5. 
   
6. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   
7. 
   
8. SQL> conn hr/oracle_4U
   
9. Connected.
   
10. SQL> create table t05207_auto ( a number )  tablespace users;
    
11. 
    
12. Table created.
    
13. 
    
14. SQL> create table t05207_manual ( a number )  tablespace  tbs1;
    
15. 
    
16. Table created.
    
17. 
    
18. SQL> alter table t05207_auto  pctfree  20;
    
19. 
    
20. Table altered.
    
21. 
    
22. SQL> alter table t05207_manual pctfree  20   pctused 50 ;
    
23. 
    
24. Table altered.
    
25. 
    
26. SQL> alter table t05207_auto  pctfree  20  pctused 50 ;
    
27. 
    
28. Table altered.
    
29. 
    
30. SQL>
    

复制代码

	OWNER	TABLE_NAME	PCT_FREE	PCT_USED	PCT_INCREASE
1	HR	T05207_MANUAL	20	50
2	HR	T05207_AUTO	20

段的第一个块和段头块：

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';

复制代码

	OWNER	SEGMENT_NAME	PARTITION_NAME	SEGMENT_TYPE	TABLESPACE_NAME	EXTENT_ID	FILE_ID	BLOCK_ID	BYTES	BLOCKS	RELATIVE_FNO
1	HR	T04209_UNAME		TABLE	EXAMPLE	0	5	488	65536	8	5
2	HR	T04209_UNAME		TABLE	EXAMPLE	1	5	696	65536	8	5
3	HR	T04209_UNAME		TABLE	EXAMPLE	2	5	704	65536	8	5
4	HR	T04209_UNAME		TABLE	EXAMPLE	3	5	712	65536	8	5
5	HR	T04209_UNAME		TABLE	EXAMPLE	4	5	1408	65536	8	5
6	HR	T04209_UNAME		TABLE	EXAMPLE	5	5	1416	65536	8	5
7	HR	T04209_UNAME		TABLE	EXAMPLE	6	5	1424	65536	8	5
8	HR	T04209_UNAME		TABLE	EXAMPLE	7	5	1432	65536	8	5
9	HR	T04209_UNAME		TABLE	EXAMPLE	8	5	1440	65536	8	5
10	HR	T04209_UNAME		TABLE	EXAMPLE	9	5	1448	65536	8	5
11	HR	T04209_UNAME		TABLE	EXAMPLE	10	5	1456	65536	8	5
12	HR	T04209_UNAME		TABLE	EXAMPLE	11	5	1464	65536	8	5
13	HR	T04209_UNAME		TABLE	EXAMPLE	12	5	1472	65536	8	5
14	HR	T04209_UNAME		TABLE	EXAMPLE	13	5	10392	65536	8	5
15	HR	T04209_UNAME		TABLE	EXAMPLE	14	5	10400	65536	8	5
16	HR	T04209_UNAME		TABLE	EXAMPLE	15	5	10408	65536	8	5
17	HR	T04209_UNAME		TABLE	EXAMPLE	16	5	512	1048576	128	5

1.   select  * from dba_segments s where s.owner='HR' and s.segment_name='T04209_UNAME';

复制代码

OWNER

SEGMENT_NAME

PARTITION_NAME

SEGMENT_TYPE

SEGMENT_SUBTYPE

TABLESPACE_NAME

HEADER_FILE

HEADER_BLOCK

BYTES

BLOCKS

EXTENTS

INITIAL_EXTENT

NEXT_EXTENT

MIN_EXTENTS

MAX_EXTENTS

MAX_SIZE

RETENTION

MINRETENTION

PCT_INCREASE

FREELISTS

FREELIST_GROUPS

RELATIVE_FNO

BUFFER_POOL

FLASH_CACHE

CELL_FLASH_CACHE

1

HR

T04209_UNAME

TABLE

ASSM

EXAMPLE

5

490

2097152

256

17

65536

1048576

1

2147483645

2147483645

5

DEFAULT

DEFAULT

DEFAULT

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';

复制代码

	OWNER	SEGMENT_NAME	PARTITION_NAME	SEGMENT_TYPE	TABLESPACE_NAME	EXTENT_ID	FILE_ID	BLOCK_ID	BYTES	BLOCKS	RELATIVE_FNO
1	HR	T04209_UNAME		TABLE	TBS2	0	8	128	524288	64	8
2	HR	T04209_UNAME		TABLE	TBS2	1	8	192	524288	64	8
3	HR	T04209_UNAME		TABLE	TBS2	2	8	256	524288	64	8
4	HR	T04209_UNAME		TABLE	TBS2	3	8	320	524288	64	8

1. 
   
2.   alter tablespace tbs2 add datafile size 5M;
   
3.   
   
4.   select  * from dba_data_files where tablespace_name='TBS1';
   
5.   
   
6.   alter database datafile '+DATA/orcl/datafile/tbs1.267.982529779' resize 10M;
   
7.   
   
8.   --------------------------
   
9.   create bigfile tablespace tbs3 datafile size 5M autoextend on maxsize 4T;

复制代码

permenet空间用量：

1. select  
   
2. 
   
3. (select   sum(bytes)/1024/1024 from dba_data_files  
   
4. where tablespace_name='SYSTEM')
   
5. -
   
6. 
   
7. ( select   sum(bytes)/1024/1024 from dba_free_space
   
8. 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. 
   
2. select   sum(m.BYTES)/1024/1024
   
3.   from v_$temp_extent_map   m;
   
4. 
   
5. 
   
6. 
   
7. select  sum(p.BLOCKS_CACHED)/1024/1024  ,
   
8.       sum(p.EXTENTS_USED  )/1024/1024
   
9. from v_$temp_extent_pool  p;

复制代码