第8章 理解自動資料庫管理

第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/，如需轉載，請註明出處，否則將追究法律責任。