ORACLE 10G OCP 043 笔记(三)

13 Monitoring and Managing Storage
Monitoring Tablespace Storage
Oracle 10g manages the disk space in two ways: reactively and proactively.
In a truly proactive manner, Oracle 10g collects statistics on space usage in the Automatic Workload Repository (AWR) at 30-minute intervals to assist you with tablespace and segment
growth trend analysis and capacity planning.
Space Usage Monitoring
The background process MMON checks for tablespace space problems every 10 minutes; alerts are triggered both when a threshold is exceeded and once again when the space usage for
a tablespace falls back below the threshold.
Dictionary-managed tablespaces do not support server-generated alerts
Editing Thresholds with Enterprise Manager Database Control
略
如果超过空间阀值,MMON进程将会以下列三方式之一通知DBA危急的表空间问题。
via the EM Database Control Home tab, via an e-mail message sent to the e-mail address configured when the database was created, or using the script in the Response
Action column, if one was specified,
Using DBMS_SERVER_ALERT
SET_THRESHOLD
GET_THRESHOLD
EXPAND_MESSAGE
SQL> begin
dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_ge, 90,
dbms_server_alert.operator_ge, 99,
1, 1, null,
dbms_server_alert.object_type_tablespace,'USERS');
end;
 
 

以下出错，已发帖子，why？要看看开发知识。
variable warn_oper number
variable warn_value varchar2
variable crit_oper number
variable crit_value varchar2
variable obs_per number
variable cons_oc number
 
 
begin
dbms_server_alert.get_threshold(
dbms_server_alert.TABLESPACE_PCT_FULL,
:warn_oper, :warn_value, :crit_oper, :crit_value,:obs_per, :cons_oc, null,
dbms_server_alert.object_type_tablespace,'USERS');
end;
 
 
print warn_oper
print warn_value
print crit_oper
print crit_value
print obs_per
print cons_oc
 
 

Using Segment Management Tools
Segment Shrink
An Overview of Segment Shrink
Segment shrink compresses the data blocks in a table or index and optionally moves the HWM down, making the unused space available for other segments in the tablespace.
Before Oracle 10g, the HWM could be moved down only if the segment was moved or truncated. While online table redefinition or Create Table As Select (CTAS) operations can provide
similar results to segment shrink, those methods must temporarily provide double the amount of space occupied by the table.
Segment Shrink Restrictions and Considerations
During a segment shrink operation, the ROWID may change for a row when it moves between blocks. Therefore, segments that rely on ROWIDs being constant, such as an application that maintains ROWIDs as pointers in another table, cannot be shrunk. In any case, ROW MOVEMENT must be enabled for table segments that are candidates for shrink operations.
Performing Segment Shrink
SQL Commands and Segment Shrink
SQL> alter table hr.employees enable row movement;
SQL> alter table hr.employees shrink space;
将操作分成两个命令可能好些
SQL> alter table hr.employees shrink space compact;
……using the COMPACT clause to compress the rows without moving the HWM,
At a later time, when the database is not as busy, you can complete the rest of the operation by omitting the COMPACT clause.
SQL> alter table hr.employees shrink space;
When CASCADE is specified, all dependent objects, such as indexes, are also shrunk.
SQL> alter table hr.employees shrink space cascade;
 
 
EM Database Control and Segment Shrink
Growth Trend Report
Segment Resource Estimation
 
 
Segment Advisor within PL/SQL
略
 
 
Index Space Monitoring
Monitoring Index Space Usage
sql>connect hr/cxxx@orcl
SQL> analyze index emp_name_ix validate structure;
SQL> select pct_used from index_stats where name = 'EMP_NAME_IX';
Over time, if the PCT_USED value starts to decline, you can either rebuild the index, or drop and re-create the index.
Rebuilding versus Coalescing Indexes
SQL> alter index emp_name_ix coalesce;
Rebuilding versus Coalescing an Index
Rebuilding an Index Coalescing an Index
Can move index to another tablespace Cannot move index to another tablespace
Requires double the amount of disk space if Does not require additional disk space
performed online
Creates a new tree and adjusts tree height if Coalesces index leaf blocks within each
necessary branch
If you decide to rebuild the index, you can do it online, as in this example:
SQL> alter index emp_name_ix rebuild online;
The index is available nearly 100 percent of the time that the rebuild operation is in progress, other than a very short amount of time at the end of the rebuild when
the old index is swapped with the new copy of the index.
Identifying Unused Indexes
SQL> alter index hr.emp_name_ix monitoring usage;
Next, check V$OBJECT_USAGE to make sure the index is being monitored:
SQL> select index_name, table_name, monitoring,used, start_monitoring from v$object_usage where index_name = 'EMP_NAME_IX';
INDEX_NAME TABLE_NAME MON USE START_MONITORING
------------- ---------------- --- --- -------------------
EMP_NAME_IX EMPLOYEES YES NO 06/02/2004 08:57:44
SQL> select employee_id from employees where last_name = 'King';
INDEX_NAME TABLE_NAME MON USE END_MONITORING
------------ ----------------- --- --- -------------------
EMP_NAME_IX EMPLOYEES NO YES 06/02/2004 17:00:40
Understanding Special Table Types
Index-Organized Tables
An Overview of Index-Organized Tables
You can store index and table data together in an IOT. IOTs are suitable for tables in which the data access is mostly through its primary key, such as lookup tables.
An IOT is a b-tree index,much like a traditional stand-alone index. However, instead of storing the ROWID of the table where the row belongs, the entire row is stored as part of the index.
Creating Index-Organized Tables
create table sales_summary_by_date_division
(sales_date date,
division_id number,
total_sales number(20,2),
constraint ssum_dt_div_pk primary key
(sales_date, division_id))
organization index
tablespace users;
IOT Row Overflow
To further improve the performance benefits provided by an IOT, you can create an IOT overflow area. If an IOT row’s data exceeds the threshold of available space in a block, the row’s
data will be dynamically and automatically moved to the overflow area.
create table sales_summary_by_date_division
(sales_date date,
division_id number,
total_sales number(20,2),
constraint ssum_dt_div_pk primary key
(sales_date, division_id))
organization index
tablespace users
pctthreshold 25
overflow tablespace users;
 
 
IOT Mapping Tables
sybex书中概念严重有问题，以下来自concepts
A mapping table is required for creating bitmap indexes on an index-organized table. In both heap-organized and index-organized base tables, a bitmap index is accessed
using a search key. If the key is found, the bitmap entry is converted to a physical rowid. In the case of heap-organized tables, this physical rowid is then used to access
the base table. However, in the case of index-organized tables, the physical rowid is then used to access the mapping table. The access to the mapping table yields a logical
rowid. This logical rowid is used to access the index-organized table.
以下代码创建了IOT表，有映射
create table sales_summary_by_date_division
(sales_date date,
division_id number,
total_sales number(20,2),
constraint ssum_dt_div_pk primary key
(sales_date, division_id))
organization index
tablespace users
pctthreshold 25
overflow tablespace users
mapping table;
 
 
Data Dictionary Views for IOTs
DBA_INDEXES (iot表既是表也是索引)
SQL> select index_name, index_type, tablespace_name from dba_indexes
where table_name = 'SALES_SUMMARY_BY_DATE_DIVISION';
DBA_TABLES
提供iot自身的信息，映射表和相关的溢出段。
SQL> select table_name, iot_name, iot_type, tablespace_name
from dba_tables
where table_name = 'SALES_SUMMARY_BY_DATE_DIVISION'
or iot_name = 'SALES_SUMMARY_BY_DATE_DIVISION';
DBA_SEGMENTS
看看iot相关段的信息可以先从DBA_TABLES中得知段名
SQL> select segment_name, segment_type, tablespace_name
from dba_segments
where segment_name LIKE '%_IOT_%' or
segment_name = 'SSUM_DT_DIV_PK';
.....and owner='HR' 增加约束
Clustered Tables
A cluster consists of a group of two or more tables that share the same data blocks.
Creating a clustered table requires three steps:
1. Create the cluster definition.
2. Create the index on the cluster. (This step is skipped for hash clusters.)
3. Create tables within the cluster.
Cluster Types
Clusters can be divided into two broad categories: index clusters and hash clusters.
Index clusters use a traditional b-tree index to find rows in the cluster, and hash clusters use a hashing function to determine the physical location where a given row is stored.
Index Clusters
Consider an index cluster in these situations:
The tables in the cluster are always queried together and only infrequently on their own.
The tables have little or no insert, update, or delete activity performed on them after the initial
load of the cluster.
The child tables have roughly equal numbers of rows for each parent key in the parent
table.
The following example shows the SQL commands necessary to create a cluster called ORD_ITEM, an index on the cluster called ORD_ITEM_IDX, and tables ORD and ITEM in the cluster:
SQL> create cluster ord_item
(ord_number number) size 500
tablespace app_data;
The parameter SIZE specifies how many cluster keys you expect to have per Oracle block
 
 
SQL> create index ord_item_idx
on cluster ord_item
tablespace app_idx;
 
 
SQL> create table ord
(ord_number number,
cust_last_name varchar2(40),
cust_first_name varchar2(30),
ord_date date,
ord_total number(15,2))
cluster ord_item (ord_number);
 
 
SQL> create table item
(item_id number,
ord_number number,
catalog_number number,
item_price number(7,2))
cluster ord_item (ord_number);

Hash Clusters
Hash clusters work best for queries with equivalence operators, as in this example:
select cust_last_name from ord where ord_number = 681107;
 
 
Here are the attributes of a table that make it suitable for use in a hash cluster:
The tables in the cluster have a relatively uniform distribution of values in the indexed column.
The tables have little or no insert, update, or delete activity performed on them after the initial
load of the cluster.
The tables in the cluster have a predictable number of values in the indexed column.
The queries against the clustered table almost exclusively use an equality operator to
retrieve the desired row.
create cluster ord_item2
(ord_number number(6,0)) size 500
hash is ord_number hashkeys 100000
tablespace users;
Sorted Hash Clusters
In heap-organized tables and traditional hash clusters, the order in which rows are returned is not under user control and depends on internal algorithms and the relative
physical location of data blocks on disk. For each hash cluster key, Oracle maintains a list of rows sorted by one or more sort columns.
创建
The first step is to create a single table sorted hash cluster, as follows:
create cluster order_cluster
(customer_number number,
order_timestamp timestamp sort)
hashkeys 1000
single table hash is customer_number
size 500;
The next step is to create the order table itself:
create table orders
(cust_number number,
order_date timestamp,
order_number number,
spec_instr varchar2(1000))
cluster order_cluster(cust_number, order_date);
 
 
Next, add a few orders with the following INSERT statements.
insert into orders values(3045,timestamp'2004-05-05 15:04:14',405584,'Reorder from last month');
insert into orders values(1958,timestamp'2004-05-05 15:05:01',348857,'New customer');
insert into orders values(3045,timestamp'2004-05-04 9:26:59',938477,'GGT Promotion');
insert into orders values(3045,timestamp'2004-05-07 12:33:23',703749,'');
insert into orders values(3045,timestamp'2004-05-02 19:47:09',389233,'Needs order in time for Mothers Day');
 
 
SQL> select cust_number,
to_char(order_date,'yyyy-mm-dd hh:mi pm')
order_date,
order_number, spec_instr
from orders where cust_number = 3045;
 
 
Execution Plan
---------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS
(Cost=0 Card=4 Bytes=2164)
1 0 TABLE ACCESS (HASH) OF 'ORDERS'
(CLUSTER (HASH))
 
 
以下如果用>= 将需要order by 字句。
SQL> select cust_number,
to_char(order_date,'yyyy-mm-dd hh:mi pm')
order_date,
order_number, spec_instr
from orders where cust_number >= 3045;
 
 
其他空间管理工具略。
Leveraging Resumable Space Allocation
Using Resumable Space Allocation
ALTER SESSION ENABLE RESUMABLE [TIMEOUT timeout] [NAME name];
the user must be granted the RESUMABLE system privilege
SQL> alter session enable resumable timeout 600 name 'Short Timeout';
SQL> alter session disable resumable;
 
 
DBMS_RESUMABLE Package
The procedures within DBMS_RESUMABLE are as follows:
ABORT .
GET_SESSION_TIMEOUT
GET_TIMEOUT
SET_SESSION_TIMEOUT
SET_TIMEOUT
SPACE_ERROR_INFO
SQL> select dbms_resumable.get_timeout() from dual;
SQL> exec dbms_resumable.set_timeout(1200);
SQL> select dbms_resumable.get_timeout() from dual;
 
 
Using the AFTER SUSPEND System Event
A system event called AFTER SUSPEND, first introduced in Oracle 9i, is triggered at the database or schema level when a correctable Resumable Space Allocation error occurs.
 
 
CREATE OR REPLACE TRIGGER resumable_default_timeout
AFTER SUSPEND ON DATABASE
BEGIN
/* set timeout to 8 hours */
DBMS_RESUMABLE.SET_TIMEOUT(28800);
/* prepare e-mail to DBA on call */
. . .
UTL_MAIL.SEND (. . .);
END;
 
 
Resumable Space Allocation Data Dictionary Views
The data dictionary views DBA_RESUMABLE and USER_RESUMABLE display the set of Resumable Space Allocation statements in the system.
SQL> select user_id, session_id, name, status,
timeout, sql_text
from dba_resumable;
 
 

14 Securing the Oracle Listener, Diagnostic Sources, and Memory
Securing the Oracle Listener
An Overview of the TNS Listener
The listener is not limited to database connections, however. It can also be used to access executable programs on the database server.
Managing the Listener Remotely（安全隐患，需受到合适的保护）
Remote management can be accomplished by simply following these steps:
1.Install Oracle software (either client or server) on a remote machine. The machine needs to have Oracle Net Services connectivity, as well as the
lsnrctl executable.
2.On the remote machine, configure the listener.ora file so that it can resolve to the address of the database server.
REMOTE_LISTENER =
ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST =192.168.1.200)
(PORT = 1521)
)
)
3.Start the lsnrctl program and specify the alias defined in step 2, as shown here:
lsnrctl remote_listener
 
 
Setting the Listener Password
Setting the password for the listener can be accomplished using several different methods.
The first method is to manually edit the listener.ora file and add the following line:
PASSWORDS_LISTENER = shazam
lsnrctl>reload
second method of setting the listener password is to use the CHANGE_PASSWORD command from the lsnrctl prompt:
LSNRCTL>change_password
LSNRCTL> set password
LSNRCTL> save_config
上面的方法无论输入什么密码都可以管理，估计是操作系统验证惹得祸
The third method of setting the listener password is to use one of Oracle’s graphical tools such as Net Manager (netmgr),Network Creation Assistant (netca), or the Oracle Enterprise
Manager (EM) Database Control application.
Controlling Database Access
DMZ：？
by implementing valid node checking, only requests coming from the application servers would be accepted, and all others would be denied.
netmgr GUI下可更改，书中是更改sqlnet.ora文件，果然外部机器登陆不上。
 
 
Using Listener Logging
netmgr可启用 ，或者用下面的方法
LSNRCTL> set log_directory /oralogs
LSNRCTL> set log_file listener_lnx1.log
LSNRCTL> set log_status on
Enabling Listener Logging Using EM Database Control
略
 
 
Removing Unneeded External Procedure Services
The listener can do more than just listen for database requests. It can also be used to run binary executable programs on the database server and to return the output back to the caller. This functionality is referred to as external procedure support.
By default, Oracle creates a service for external procedures in the listener. For the majority of users, this service is never used. Therefore, the service should be removed from the listener.
Manually Removing External Procedure Services
listener.ora Network Configuration File:
/apps/oracle/oracle10gR1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /apps/oracle/oracle10gR1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = lnx1)
(ORACLE_HOME = /apps/oracle/oracle10g)
(SID_NAME = lnx1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = ICP)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =lnx1)
(PORT = 1521))
)
)
)
改变之后
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = lnx1)
(ORACLE_HOME = /apps/oracle/oracle10g)
(SID_NAME = lnx1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =lnx1)
(PORT = 1521))
)
)
)
 
 
Removing External Procedure Services Using EM Database Control
略
 
 
Creating a Separate Listener for External Procedures
可以单独设置一个严格处理外部过程请求的监听器。这个监听器由一个具有非常局限的操作系统权限的用户来执行或限制从哪个库执行过程。
Executing the Listener with a Restricted Account
执行该新监听器的帐号具有读LISTENER.ORA的权限。其他权限不需要了。
Limiting the Available Libraries
By default, the extproc agent can execute procedures from any DLL or shared object library stored in the following locations:
$ORACLE_HOME/lib directory in Unix
%ORACLE_HOME%\bin directory in Windows
但是可以限制监听器只访问LISTENER.ORA文件中明显指定的库。
1，编辑文件创建新的listener.ora略
2，用oem创建 更直观。测试之
 
 
Using the ADMIN_RESTRICTIONS Parameter
禁止图形下执行set，故只有人工编辑，fuck。不熟悉
ADMIN_RESTRICTIONS = on
 
 
Securing the Listener Directory
listenr.ora,sqlnet.ora 文件的目录只有管理级帐户可以读写。
 
 
Applying Oracle Listener Patches
 
 
Blocking SQL*NET Traffic on Firewalls
 
 
Diagnostic Sources
The alert log is an excellent source of diagnostic data and is generally considered the first place to check when an error occurs.
看alert文件 ，oem看，或者利用外部表特性查询。
Using Server-Generated Alerts
Using Oracle Trace Files
Background processes use this format: sid_process_name_process_id.trc
User processes use this format: sid_ora_process_id.trc
The TRACEFILE_IDENTIFIER parameter allows you specify a text string that will be appended to the names of user session trace files.
ALTER SESSION SET SQLTRACE = TRUE
ALTER SESSION SET TIMED_STATISTICS = TRUE
ALTER SESSION SET TRACEFILE_IDENTIFIER = ‘TIM’
 
 
Automatic Memory Management
Oracle Memory Usage
Within each of these structures, Oracle divides available memory into smaller structures known as pools, each designated to satisfy a particular class of memory allocation request.
 
 
An Overview of the SGA
Database Buffer Cache
Memory in the database buffer cache is tracked by two internal lists: the write list and the least recently used (LRU) list.
The LRU list is a FIFO
The exception to this rule is when a full table scan operation is performed. Blocks retrieved from a full table scan are added to the least recently used end of the LRU list, so they will be aged out quickly.
you can add the CACHE clause to the table (either via ALTER TABLE or in the CREATE TABLE statements) to circumvent this behavior. You can also choose to pin the table in memory.
Shared Pool
The shared pool contains the library cache and the data dictionary cache, as well as buffers for parallel execution messages and other control structures.
Library Cache
When a SQL statement is parsed, the resultant parse tree and execution plan are stored (along with the statement itself) in a shared SQL area.
Data Dictionary Cache
Redo Log Buffer
Java Pool
Large Pool
 
 
Streams Pool
Fixed SGA
 
 
An Overview of the PGA
A Program Global Area (PGA) stores data and control information for a single server process. Therefore, every session and background process has an associated PGA.
 
 
An Overview of Automatic Memory Management
ASMM divides the SGA memory pools into two groups: automatically sized components and manually sized components.
从参数中的非零值表示最小内存分配，
人工设置大小的组成部分：
These components are
Streams pool
Keep buffer cache
Recycle buffer cache
Other caches
These parameters include
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_nK_CACHE_SIZE (where n = 2, 4, 8, 16, or 32)
STREAMS_POOL_SIZE
 
 
Using APMM
APMM functionality is implemented through the use of the PGA_AGGREGATE_TARGET initialization parameter.
Historically, PGA memory allocation has been done through the use of the following initialization parameters:
SORT_AREA_SIZE
HASH_AREA_SIZE
BITMAP_MERGE_AREA_SIZE
CREATE_BITMAP_AREA_SIZE
以上参数与WORKAREA_SIZE_POLICY 结合使用。
如果WORKAREA_SIZE_POLICY 为AUTO，忽略以上内存参数，
如果WORKAREA_SIZE_POLICY 为MANUAL ，使用以上内存参数。
Implementing APMM
PGA_AGGREGATE_TARGET=2G
SQL> alter system set PGA_AGGREGATE_TARGET = 2G;
Implementing APMM Through EM Database Control
略
 
 
 
 
 
 
 
 
 
 

%d
Specifies the name of the database.

%f
Specifies the absolute file number.

%h
Specifies the archived redo log thread number.

%I
Specifies the DBID.

%M
Specifies the month in the Gregorian calendar in format MM.

%N
Specifies the tablespace name.

%n
Specifies the name of the database, padded on the right with x characters to a total length of eight characters. For example, if the prod1 is the database name, then the padded name is prod1xxx.

%p
Specifies the piece number within the backup set. This value starts at 1 for each backup set and is incremented by 1 as each backup piece is created.
 
 
Note: If you specify PROXY, then the %p variable must be included in the FORMAT string either explicitly or implicitly within %U.

%s
Specifies the backup set number. This number is a counter in the control file that is incremented for each backup set. The counter value starts at 1 and is unique for the lifetime of the control file. If you restore a backup control file, then duplicate values can result. Also, CREATE CONTROLFILE initializes the counter back to 1.

%t
Specifies the backup set time stamp, which is a 4-byte value derived as the number of seconds elapsed since a fixed reference time. The combination of %s and %t can be used to form a unique name for the backup set.

%T
Specifies the year, month, and day in the Gregorian calendar in this format: YYYYMMDD.

%u
Specifies an 8-character name constituted by compressed representations of the backup set or image copy number and the time the backup set or image copy was created.
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database
 
 
 
 
 
 
 
 
10g RMAN FORMAT 选项的格式规范
%d 规定数据库的名字
%D 规定格里高里日历表示的当前月日
%F 组合数据库标识符（DBID）、日、月、年和序列号为一个独特的、可重复生成的名字
%n 规定数据库名,右端用n个字符填补,总长度8个字符
%p 规定备份集的块号
%s 备份集号
%t 备份集时间戳
%T 规定格里高里日历表示的年、月、日
%u 规定8个字符的名字,该名字使通过备份集的压缩表示或图象副本号而生成
%U 规定系统生成的唯一文件名(默认)
 
疑问：
select recid,name,thread#,sequence# from v$archived_log