ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 第8章 理解自动数据库管理

第8章 理解自动数据库管理

原创 Linux操作系统 作者:zhengbao_jun 时间:2009-02-18 15:27:02 0 删除 编辑
第8章 理解自动数据库管理
AWR的统计信息收集程序
数据库性能统计信息
Oracle 10g中新的统计信息
工作负载储存库
sql>select table_name from dba_tables
where tablespace_name='SYSAUX'
and substr(table_name,1,2)='WR'
and rownum<=20
order by 1;


 启用AWR
AWR空间考虑
活动会话历史
确定ASH的大小
ASH统计信息
ASH视图
ASH和AWR
使用AWR
AWR快照
使用快照
sql>select snap_interval,retention from dba_hist_wr_control;
创建快照
begin
 DBMS_WORKLOAD——REPOSITORY.CREATE_SNAPSHOT();
end;
创建快照频率
BEGIN
 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNANPSHOT_SETTINGS(RETENTION => 14400,INTERVAL => 45);
END;
丢弃快照
Exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(LOW_SNAP_ID =>316,HIGH_SNAP_ID =>320);

AWR基线
使用基线
建立基线
BEGIN
 EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(START_SNANP_ID =>42,END_SNAP_ID => 43, BASELINE_NAME => 'REPORTS');
END
丢弃基线

使用AWR视图
sql>select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot order by 1;

使用AWR报告
SQL>@$ORACLE_HOME/rdbms/admin/awrrpti.sql

管理服务器报警
阀值与非阀值报警
默认报警
报警过程概述
查看度量阀值
编辑度量阀值
设置多阀值

通过PL/SQL设置阀值
EXEC DBMS_SERVER_ALERTS.SET_THRESHHOLD(
 METRICS_ID => CPU_TIME_PER_CALL,
 WARNING_OPERATOR => OPERATOR_GT,
 WARNING_VALUE => '15000',
 CRITICAL_OPERATOR => OPERATOR_GT,
 CRITICAL_VALUE => '30000', OBSERVATION_PERIOD => 10,
 CONSECUTIVE_OCCURRENCES => 3);

查看服务器报警

使用自动例行管理程序
自动统计信息收集
优化器统计信息
sql>create table sales as select * from sh.sales where rownum <6;
sql>exec dbms_stats.gather_table_stats(null,'SALES');
sql>insert into sales select * from sh.sales where rownum <= 40000;
sql>commit;
sql>create index sales_idx on sales(cust_id) tablespace index;

sql>explain plna
for
select * from sales
where cust_id = 123;
sql>@utlxpls
sql>exec dbms_stats.gather_table_stats(null,'SALES');
sql>delete from plan_table;
sql>commit;
sql>explain plan
for
select * from sales
where cust_id = 123;
sql>@utlxpls

字典统计信息
系统统计信息
sql>select statistic#,name,value from v$sysstat where rownum <=20;

操作系统统计信息
用户定义统计信息
收集优化器统计信息
SELECT * FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME='GATHER_STATS_JOB';

BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/
管理易变对象统计信息
sql>exec DBMS_STATS.DELETE_TABLE_STATS('BUTERTB','VALATILE_TABLE');
sql>exec DBMS_DBMS_STATS.LOCK_TABLE_STATS('BUTERTB','VOLATILE_TABLE');
监视DML表变化
改变STATISTICS_LEVEL参数
查看统计信息层次

理解顾问框架(ADVISORY FRAMEWORK)
顾问概况
自动数据库诊断监视器(ADDM)
段顾问
撤销顾问
sql调整顾问
sql访问顾问
内存顾问
调用顾问
DBMS_ADVISORY程序包
顾问视图

自动数据库诊断监视器
ADDM分析
DB_TIME
等待事件的变化
通过EM访问ADDM

SQL调整顾问
自动调整优化器
自动SQL调整
使用SQL调整顾问
DBMS_SQLTUNE程序包

创建任务
DECLARE
 task_name varchar2(30);
 sql_stmt clob;
BEGIN
 sql_stmt := 'select /*+ full(a) use_hash(a) ' ||
' parallel(a,8) full(b) use_hash(b) parallel(b,8) ' ||
' */ a.type,sum(a.amt_paid) ' ||
' from large_table a,large_table2 b ' ||
' where a.key = b.key ' ||
' and state_id = :bnd';

task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
 sql_text   =>sql_stmt,
 bind_list  => sql_binds(anydata.ConvertNumber(32));
 user_name  => 'BUTERTB',
 scope      => 'COMPREHENSIVE',
 time_limit => 45,
 task_name  => 'large_table_task',
 description=>'Tune state totals query');

dbms_output.put_line('Task ' || task_name || ' has been created.');
END;
/

执行任务
BEGIN
dbms_sqltune.execute_tuning_task( task_name => 'large_table_task');
END;
select status from dba_advisor_log
where task_name = 'large_table_task';
BEGIN
 DBMS_SQLTUNE.INTERRUPT_TUNING_TASK( task_name => 'large_table_task');
END;

set long 1000
set longchunksize 1000
set linesize 132
select dbms_sqltune.report_tuning_task('large_table_task') from dual;

管理SQL_Profile
DECLARE
 sqlprofile_name varchar2(30);
BEGIN
 sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
task_name => 'large_table_task',
profile_name => 'large_table_profile');
END;
改变SQL PROFILE
begin
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'large_table_profile',
Atribute_name =>'STATUS',
Value => 'ENABLES');
END;

丢弃SQL PROFILE
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(
Name => 'large_table_profile');
END;

管理SQL调整集
创建SQL调整集
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
Sqlset_name => 'load_proc_set',
Description => 'SQL used in load procedure');
END;

加载SQL调整集
DECLARE
sql_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin_snap number := 1; /* beginning snanpshot id
end_snap number :=5; /*end snapshot id*/
BEGIN
open sql_cursor for
select value(p)
from table (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
begin_snap,end_snap) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => 'load_proc_set',
populate_cursor =>sql_cursor);
END;
/

SQL调整视图
通过EM Database Control 使用SQL调整顾问

第9章 理解自动存储管理
ASM体系介绍
理解ASM实例
创建一个ASM实例
连接到一个ASM实例
打开或关闭一个ASM实例
SQL>alter system enable restricted session;
sql>alter system disable restricted session;
定义ASM初始化参数
SQL>show parameter asm_diskstring

对ASM动态性能视图进行分类
使用ASM文件名
sql>select file3,name,blocks from v$datafile;
完整路径的ASM文件名
+group/dbname/file type/tag.file.incarnation

数字化的名称
+DATA2.256.1

别名
sql>alter diskgroup data2 add directory '+data2/redempt';
sql>alter diskgroup data2
add alias '+data2/redempt/users.dbf'
for '+data2/rac0/datafile/users3.256.1';
使用模版的别名
sql>create tablespace users4 datafile
'+data2/uspare(datafile)';
不完全名称
sql>create tablespace users5 datafile '+data1';

使用模板的不完全名
sql>create tablespace users6 datafile '+|data1(tempfile)';

理解ASM类型和模板
管理ASM磁盘组
理解磁盘组体系结构
理解故障组和磁盘组镜像
理解磁盘组动态重平衡
创建和删除磁盘组
sql>select group_number,disk_number,name,failgroup,create_date,path from v$asm_disk;
sql>select group_number,name,type,total_mb,free_mb from v$asm_diskgroup;
sql>create diskgroup data2 high redundancy
failgroup fg1 disk '/dev/raw/raw3' name d2a
failgroup fg2 disk '/dev/raw/raw4' name d2b
failgroup fg3 disk '/dev/raw/raw5' name d2c
failgroup fg4 disk '/dev/raw/raw6' name d2d;

sql>select group_name,name,type,total_mb,free_mb from v$asm_diskgroup;
sql>select group_number,disk_number,name,failgroup,create_date,path from v$asm_disk;

sql>create diskgroup data2 high redundancy
failgroup fg1 disk '/dev/raw/raw3' name d2a
failgroup fg2 disk '/dev/raw/raw4' name d2b force
failgroup fg3 disk '/dev/raw/raw5' name d2c
failgroup fg4 disk '/dev/raw/raw6' name d2d;

sql>drop diskgroup data2;
sql>create diskgroup data2 high redundancy
failgroup fg1 disk '/dev/raw/raw3' name d2a
failgroup fg2 disk '/dev/raw/raw4' name d2b
failgroup fg3 disk '/dev/raw/raw5' name d2c;

sql>select group_number,disk_number,name,
failgroup,create_date,path from v$asm_disk;

修改磁盘组
sql>alter diskgroup data1
add failgroup d1fg3 disk '/dev/raw/raw6' name d1c;
sql>select group_number,operation,state,power,actual,
sofar,est_work,est_rate,est_minutes
from v$asm_operation;

sql>alter diskgroup data1 rebalance pwoer 8;
sql>select group_number,operation,state,power,actual,
sofar,est_work,est_rate,est_minutes
from v$asm_operation;

sql>select group_number,disk_number,name
failgroup,create_date,path,from v$asm_disk;
sql>select group_number,name,type,total_mb,free_mb
from v$asm_diskgroup;
sql>alter diskgroup data1
add failgroup d1fg3 disk '/dev/raw/*' name d1c;

使用ALTER DISKGROUP DROP DISK
sql>alter diskgroup data2 drop disk d2d;

使用ALTER DISKGROUP UNDROP DISKS
sql>alter diskgroup data2 add failgroup fg4
disk '/dev/raw/raw6' name d2d;
sql>select adg.name DG_NAME,
ad.name FG_NAME,path from v$asm_disk ad
right outer join v$asm_diskgroup adg
on ad.group_number = adg.group_number
where adg.name = 'DATA2';
sql>alter diskgroup data2 drop disk d2d;
sql>alter diskgroup data2 undrop disks;
sql>select adg.name DG_NAME,
ad.name FG_NAME,path from v$asm_disk ad
right outer join v$asm_diskgroup adg
on ad.group_number = adg.group_number
where adg.name = 'DATA2';

sql>alter diskgroup data2 drop disk d2d;
sql>select group_number,operation,state,power,actual,sofar,est_work,est_rate,est_minutes from v$asm_operation;
sql>select adg.name DG_NAME
ad.name FG_NAME,path from v$asm_disk ad
right outer join v$asm_diskgroup adg
on ad.group_number = adg.group_number
where adg.name = 'DATA2';

使用ALTER DISKGROUP REBALANCE POWER n
使用ALTER DISKGROUP DROP ADD
sql>select adg.name DG_NAME
ad.name FG_NAME,path from v$asm_disk ad
right outer join v$asm_diskgroup adg
on ad.group_number = adg.group_number
where adg.name = 'DATA2';
sql>alter diskgroup data2
add failgroup fg4
disk '/dev/raw/raw6' name d2d
drop disk d2c;
sql>select adg.name DG_NAME
ad.name FG_NAME,path from v$asm_disk ad
right outer join v$asm_diskgroup adg
on ad.group_number = adg.group_number
where adg.name = 'DATA2';

使用ALTER DISKGROUP DISMOUNT
sql>alter diskgroup data2 dismount;

使用ALTER DISKGROUP MOUNT
SQL>ALTER DISKGROUP data2 MOUNT;

使用ALTER DISKGROUP CHECK ALL
SQL>alter diskgroup dta2 check all;

将EM DATABASE CONTROL用于ASM磁盘组
使用RMAN是实现将数据库迁移到ASM
STARTUP NOMOUNT;
RESTORE CONTROLFILE FROM '';
ALTER DATABASE MOUNT;
BACKUP AS COPY DATABASE FORMAT
'+';
SWITCH DATABASE TO COPY;
SQL "ALTER DATABASE RENAME TO '+' ";
#repeat for all log file members
ALTER DATABASE OPEN RESETLOGS;

第10章 理解全球化支持
全球化支持概述
全球化支持特性
全球化支持结构
支持多语言应用程序
解析客户/服务器设置
sql>select sysdate from dual;
sql>alter sysdate from dual;
sql>alter session set NLS_LANGUAGE=Italian;
sql>select sysdate from dual;

在多语言数据库中使用
使用NLS参数

 

 

 

 

 


 
%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.
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/235507/viewspace-555225/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-08-08

  • 博文量
    209
  • 访问量
    871188