ORACLE 10G OCP 043 笔记(一)

第1章 配置恢复管理器Configuring Recovery Manager

 

 

组成：GUI或者命令行、可选的恢复目录、RMAN命令和脚本,以及磁带媒介连接
考虑事项：RMAN是一种物理备份方法。其他的备份恢复方法：用户管理和Oracle EXPORT工具（逻辑备份,一般不做为独立得备份方法,而是对RMAN或者用户管理的备份提供附加的备份保护,exp,expdp）

 

 

TABLE 1 . 1
Different Backup Methods Capabilities
Capability RMAN User-Managed EXPORT Utility
Server parameter file backups Supported Supported Not supported
Password file backups Not supported Supported Not supported
Closed database backups Supported Supported Not supported
Open database backups Supported Not supported Not supported
Incremental backups Supported Not supported Not supported
Corrupt block detection Supported Not supported Supported
Automatic backup file Supported Not supported Supported
Backup catalogs Supported Not supported Supported
Media manager Supported Supported Supported
Platform independent Supported Not supported Supported

 

 

可以使用控制文件或者恢复目录作为资料档案库。Oracle建议,中等规模的企业环境,使用恢复目录,这样可以充分发挥RMAN工具的全部功能。如果使用控制文件作为资料档案库,设置参数：CONTROL_FILE_RECORD_KEEP_TIME.
恢复目录必须存储在自己的数据库服务器中,而不是目标数据库所在的服务器中。为安全恢复数据库也备份。为了使目录能够工作,必须建立具有connect,resource和RECOVERY_CATALOG_OWNER权限的账户。新数据库参数如下：
1)SYSTEM 90M;UNDO 5M;TEMP 5M
2)三个日志文件组每组两个成员 1M
3)RECOVERY CATALOG表空间 CATTBS 15M
DBCA建立恢复目录数据库以及,然后实践第3步,创建恢复目录
书中数据库环境：目标数据库ORA101T,恢复目录数据库ORA101RC,恢复目录表空间DATA
(本机测试环境:目标数据库为prod,恢复目录数据库prodrman,恢复目录为CATTBS.
rman target /@prod catalog rman/rman@prodrman )
以下command>表示windows环境命令行。
command> SET ORACLE_SID = ora101rc
command>sqlplus /nolog
sql>connect / as sysdba
sql>create user rman_user identified by rman_user default tablespace data temporary tablespace temp;
sql>grant connect,resource,recovery_catalog_owner to rman_user;
command>rman
rman>connect catalog rman/rman
rman>create catalog tablespace data;
注册数据库
command> SET ORACLE_SID = ora101t
command>rman target /
rman>connect catalog "rman_user/rman_user@ora101rc";
rman>register database;

 

 

for example:备份整个数据库
command>set ORACLE_SID = ora101t
command>sqlplus /nolog
sql>connect / as sysdba
sql>startup mount
command>rman
rman>connect target
rman>connect catalog rman_user/rman_user@ora101rc;
rman>run
{
allocate channel c1 type disk;
backup database format 'G:\RMAN_BAKCUP3\db_%u_%d_%s';
backup format 'G:\RMAN_BAKCUP3\log_t%t_s%s_p%p' (archivelog all);
}
备份完成以后,就可以恢复数据库。数据库必须已安装,但没有打开。选择多个磁盘通道以利用并行恢复进程,这不是必须的,但可以改进重建和恢复时间。
RMAN>run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
restore database;
recover database;
alter database open;
}

 

 

启动和连接到RMAN
从命令行连接:
command>set ORACLE_SID=ora101rc
command>rman target / catalog rman_user/rman_user@ora101rc
从rman工具连接到目标数据库。
command>set ORCLE_SID=ora101t;
command>rman
rman>connect target
rman>connect catalog rman_user/rman_user@ora101rc;

 

 

媒介管理层（MML）界面使应用编程界面（API),它是RMAN与不同的硬件硬件供应商的磁带设备接口。这些磁带设备,是自动化磁带库（ATL),所有希望与ORACLE RMAN 一起工作的硬件供应商都做自己的MNL,因为大部分磁带硬件设备是有专利的,要求不同的程序调用。

 

 

通道分配
自动分配：
_________________________________________________________
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO [COPY |[ COMPRESSED ]BACKUPSET] |clear|parallelism n
CONFIGURE DEFAULT DEVICE TYPE to deviceSpecifier|clear
CONFIGURE CHANNEL DEVICE TYPE disk|equal？？
CONFIGURE CHANNEL n DEVICE TYPE disk|equal
-----------------------线内有些奇怪,略,看 example----------------------------

 

 

RMAN的参数和永久设置
RMAN>configure default device type to disk;
rman>configure default device type to sbt;
rman>configure device type disk backup type to copy;
rman>configure device type disk backup type to backupset;
rman>configure device type disk backup type to compressed backupset;
rman>configure device type sbt backup type to compressed backupset;
rman>configure channel device type disk format 'c:\backup\ora101rc\ora_dev_t%t_s%s_p%p;
rman>configure channel device type sbt PARMS='ENV=mnl_env_settings';
rman>configure device type sbt parallelism 3;

 

 

使用ENTERPRISE MANAGER 配置 RMAN 设置值
HTTP://hostname.domain:5500/em---->login-->Maintenance-->Backup/Recovery

 

 

保留策略
rman>configure retention policy to recovery window of 30 days;
run
{
allocate channel c1 type disk;
backup database format 'db_%u_%d_%s' tag monthly_backup;
backup format 'log_t%t_s%s_p%p' (archivelog all);
}
rman>change backupset tag monthly_backup nokeep;
rman>change backupset tag monthly_backup keep until time '01-DEC-04' logs;
rman>configure controlfile autobackup on;
rman>configure controlfile autobackup format for device type disk to 'c:\oracle\ora101rc\cf%F';

 

 

第2章 Using Recovery Manager
run
{
allocate channel c1 type disk;
backup database format 'db_%u_%d_%s' tag monthly_backup;
backup format 'log_t%t_s%s_p%p'
(archivelog all);
}

 

 

command>rman
rman>connect target
rman>run{
allocate channel c1 type disk;
backup database format 'db_%u_%d_%s';
backup format 'log_t%t_s%s_p%p'
(archivelog all);
}

 

 

rman>run
{
allocate channel ch1 tyep disk;
copy
datafile 1 to 'c:\oracle\staging\ora101t\system01.dbf',
current controlfile to 'c:\oracle\staging\ora101t\control01.ctl';
}

 

 

RMAN> delete datafilecopy 28;

 

 

command>rman
rman>connect target
rman>backup as copy tag "062504_backup" database;

 

 

rman>backup as compressed backupset database;

 

 

rman>configure device type disk backup type to compressed backupset;

 

 

dir *.bkp

 

 

Full and Incremental Backups
Full backups do not mean the complete database was backed up. In other words, a full backup can back up only part of the database and not all datafiles, control files, and logs.
a differential incremental and a cumulative incremental backup.
Performing a Differential Incremental Backup
rman>backup incremental level 0 database;
rman> backup incremental level 1 database;
Performing a Cumulative Incremental Backup
rman>backup incremental level 1 cumulative database;

 

 

备份集得并行化
command>set ORACLE_SID=ora101rc
command>rman
rman>run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup
(datafile 1,2,3 channel c1)
(archivelog all channel c2);
}

 

 

rman>show all;
rman>configure device type disk parallelism 3;
rman>backup
(datafile 1,2)
(datafile 3,4)
(archivelog all);

 

 

rman>backup tablespace users format ='user_bs_%d%p%s';
rman>backup as copy tablespace users format='c:\oracle\backups\ora101rc\users_%d%p%s';
rman>backup database tag weekly_backup;
rman>configure channel device type disk rate 5m;
rman>configure channel device type disk maxsetsize=10g;
rman>backup database maxsetsize=10g;
rman>configure channel device type disk maxpiecesize=2G;

 

 

启动和关闭块变化跟踪
command>sqlplus /nolog
sql>connect / as sysdba
sql>select * from v$block_change_tracking;
sql>alter database enable block change tracking using file 'c:\oracle\block_track\ora101rc_block_track.log';
sql>alter database enable block change tracking using file '/backup/block_track.log';

 

 

sql>select filename,status,bytes from v$block_change_tracking;
sql>alter database disable block change tracking;

 

 

用EM管理备份
监视RMAN备份
sql>connect system/manager@ora101t
sql>select dbid from v$database;
sql>connect rman_user/rman_user@ora101rc
sql>select db_key from rc_database where dbid=1736563848;
sql>select bs_key,backup_type,completion_time
from rc_database_incarnation a,rc_backup_set b
where a.db_key = b.db_key
and a.current_incarnation = 'YES';

 

 

使用list命令。
rman>list backupset by backup summary;
rman>list backupset by file;

 

 

使用report命令
rman>REPORT OBSOLETE;
rman>REPORT SCHEMA;

 

 

使用RMAN作业示例
启用ARCHIVELOG模式
sql>alter system set log_archive_start=true scope=spfile;

 

 

sql>connect / as sysdba
sql>startup mount
sql>alter database archivelog;
sql>alter database open;

 

 

使用RMAN备份归档重执行日志
RUN
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database;
backup (archivelog all);
}

 

 

制定备份工作计划表

 

 

第3章 Recovering From Non-Critical Losses
sql>create temporary tablespace temp2 tempfile 'c:\xxxxxxxx\xx.dbf' size 100m extent management local uniform size 128k;
sql>create temporary tablespace temp2 tempfile '/u02/oradata/orcl/temp02.dbf' size 100m extent management local uniform size 128k;
启动丢失临时文件得数据库
COMMAND>SQLPLUS /NOLOG
sql>connect / as sysdba
sql>startup mount
sql>drop tablespace temp including contents;
sql>create temporary tablespace temp2 tempfile 'c:\xxxxxxxx\xx.dbf' size 100m extent management local uniform size 128k;

 

 

改变数据库得默认临时表空间
sql>alter database default temporary tablespace temp2;

 

 

重建重执行日志文件

 

 

sql>alter database drop logfile member 'c:\oracle\oradata\ora101t\redo01.log';
sql>alter database add logfile member 'c:\oracle\oradata\ora101t\redo01.log' to group 1;

 

 

恢复索引表空间
SQLPLUS /NOLOG
sql>connect / as sysdba
sql>startup
sql>drop tablespace indexes including contents;
sql>create tablespace indexes datafile 'c:\oracle\oradata\ora1010t\index01.dbf' size 20m;

 

 

重建索引
CREATE UNIQUE INDEX example_index_pk
ON EXAMPLE_table
(column_one,
column_two,
column_three,
column_four)
PCTFREE 10
INITRANS 2
MAXTRANX 255
TABLESPACE indexes
STORAGE(
initial 1m
next 1m
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 8192
)
NOLOGGING
PARALLEL (degree 4)
/
sql>@create_example_index_pk

 

 

恢复只读表空军
SQL>ALTER tablespace users read only;
sql>alter system switch logfile;
sql>alter system switch logfile;
sql>alter system switch logfile;
sql>alter system switch logfile;

 

 

sql>shutdown immediate
sql>host
command> cd c:\oracle\oradata\backup\ora101t
command>copy c:\oracle\oradata\ora101t\*

 

 

command cd ..\..\ora101t
command>delete users01.dbf

 

 

sql>startup

 

 

sql>shutdown immediate
sql>host
command>cd c:\oracle\oradata\ora101t
command>copy c:\oracle\oradata\backup\ora101t\users01.dbf users01.dbf
command>exit

 

 

sql>startup

 

 

重建密码文件(远程登陆用) an example of rebuilding a password file:
1. First, shut down the database:
SQL> shutdown immediate
SQL> startup
2. Run the ORAPWD utility to build the password file in the $ORACLE_HOME/dbs directory
in name convention orapw$ORACLE_SID. The entries option determines how many users
can be stored in the password file:
orapwd file=orapworcl password=syspass entries=20
To see what users are utilizing the password file, keep a copy of the following query:
SQL>SELECT * FROM V$PWFILE_USERS;
我的RHEL： $ORACLE_HOME/dbs orapworcl

 

 

第4章 数据库恢复 Database Recovery
Restore 和 Recovery
sql>connect / as sysdba
sql>startup mount

 

 

rman>run
{
allocate channel c1 type disk;
restore database;
recover database;
alter database open;
}

 

 

用户管理恢复
SQL>CONNECT / AS SYSDBA
SQL>STARTUP
command>copy ..\backup\ora101t\users01.dbf
sql>startup mount
sql>recover database;
sql>alter database open;

 

 

恢复控制文件
1. First, you must configure RMAN to perform a control file autobackup:
rman>connect target
SQL> select dbid from v$database; 1125643918
rman>configure controlfile autobackup on;
rman>show all;
2. Next, perform a backup with the control file autobackup enabled:
rman>run
{
backup database;
backup (archivelog all);
}
3. Next, you simulate the missing control files by deleting all the control files.
(The database will need to be shut down to perform this simulated failure.)
command>delete *.ctl
4. Next, start the database in NOMOUNT mode,
command>sqlplus /nolog
sql>connect / as sysdba
sql>startup nomount
5. Next, connect to RMAN and the target database. You will also need to specify the DBID to identify the database you are connecting to, because the control file contains this information and failure causes the control file to be unavailable. The DBID was obtained in step 1 from connecting to the target database before the failure was introduced:
rman>connect target /
rman>set dbid 1125643918; (没有设置dbid,也搞定,不过是在本地运行dbid,如果恢复数据库有多个数据库的备份,可能需要设置dbid,控制文件里存储了dbid信息)
6. Next, restore the control file from backup:
rman>restore controlfile from autobackup;
7. Next, mount the database and begin to recover the database:
rman>alter database mount;
rman>recover database;
8. Finally, open the database with RESETLOGS option for normal operations:
rman>alter database open resetlogs;

 

 

重建控制文件 Re-creating a Control File
SQL>sqlplus /nolog
sql>connec / as sysdba
sql>alter database backup controlfile to trace;
command>edit ora101t_ora_3428.trc ---->存储为 backup_controlfile_noreset.txt
command>delete *.ctl
command>sqlplus /nolog
sql>connect / as sysdba
@backup_controlfile_noreset.txt
command>dir *.ctl

 

 

进行不完全恢复 Performing an Incomplete Recovery
RMAN Incomplete Recovery
SQL>STARTUP MOUNT
command:set NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS
rman>
run
{
set until time '06-SEP-2004 11:25:00';
restore database;
recover database;
}

 

 

rman>alter database open resetlogs;

 

 

sql>select * from v$log_history;
sql>startup mount
rman>
run
{
set until sequence 3 thread 1;
restore database;
recover database;
}

 

 

rman>alter database open resetlogs;

 

 

完成用户管理得不完全恢复
command>set NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS
command>delete USERS01.DBF
command>copy c:\oracle\backup\ora101t\users01.dbf
sql>startup mount
sql>recover database until time '06-SEP-2004 15:15:00';
SQL>ALTER database open resetlogs;

 

 

1 . c:\oradata\oracle\ora101t\>copy c:\oracle\backup\ora101t\*.dbf
2 . 查看 控制文件创建脚本
3 . SQL>CONNECT / AS SYSDBA;
SQL>backup_control_reset.txt
4 .sql>recover database until cancel using backup controlfile;
sql>alter database open resetlogs;

 

 

使用EM得数据恢复

 

 

在RESETLOGS操作之后完成恢复
Performing a Recovery after a RESETLOGS Operation
1. 1. Perform a backup if you do not have a good whole database backup:
RMAN>CONNECT target
rman>run
{
allocate channel c1 type disk;
backup database;
backup (archivelog all);
}

 

 

2. Force all the redo log information to archive logs by executing ALTER SYSTEM SWITCH LOGFILE:
sql>alter system switch logfile;
sql>alter system switch logfile;
sql>alter system switch logfile;
sql>alter system switch logfile;

 

 

3. Verify the redo log sequence and thread number in the V$LOG_HISTORY table so that you
can perform incomplete recovery to a redo log sequence:
SQL> select * from v$log_history;

 

 

4. Simulate a failure by shutting down the database and deleting the USERS01.DBF:
command>sqlplus /nolog
sql>connect / as sysdba
sql>shutdown immediate
sql>host
command>del users01.dbf
5. Begin the recovery process by starting the database in MOUNT mode:
sql>startup mount
6. Perform an incomplete recovery using the SET UNTIL SEQUENCE clause:
rman>connect target
rman>
run
{
set until sequence 5 thread 1; (恢复到sequence 4)
restore database;
recover database;
}
RMAN> alter database open resetlogs;

 

 

7. Simulate database activity by creating a table T1 and forcing this activity to the archived redo logs:
sql>connect test/test
sql>create table t1(c1 char(20));
sql>connect / as sysdba
sql>alter system switch logfile;
sql>alter system switch logfile;
sql>alter system switch logfile;
sql>alter system switch logfile;

 

 

8. Shut down the database and simulate a database failure by deleting the USERS01.DBF file:
sql>shutdown immediate
C:\oracle\oradata\ora101t\> del USERS01.DBF

 

 

9. Start the database in MOUNT mode and then perform a complete recovery in RMAN:
sql>startup mount
command>rman
rman>connect target
rman>
run
{
restore database;
recover database;
}

 

 

10. Validate that you have recovered through the last RESETLOGS by verifying that the current V$LOG_HISTORY table shows the log sequence 6 and thread 1 followed by new redo logs files:
sql>select * from v$log_history;

 

 

第5章 闪回数据库Understanding the Flashback Database
配置闪回恢复区
sql>alter system set db_recovery_file_dest_size = 6g scope=both;
sql>alter system set db_recovery_file_dest='c:\oracle\flash_recovery_area\ora101t';
sql>alter system set db_recovery_file_dest_size=2000m;
aql>alter system set db_recovery_file_dest='';

 

 

使用闪回恢复区Using the Flash Recovery Area
Let's walk through performing an automated disk-based backup and recovery:
1.First, connect to the target database:
command>set ORACLE_SID=ora101rc
command>rman
rman>connect target
2.Next, perform a backup of the target database:
rman>backup as copy database;
3. Finally, query the V$RECOVERY_FILE_DEST to determine if the SPACE_USED column value has increased.
sql>select * from v$recovery_file_dest;

 

 

备份闪回恢复区
rman>backup recovery area;
rman>backup recovery files;

 

 

配置闪回恢复区
1. Start the database in MOUNT mode:
sql>connect / as sysdba
sql>startup mount
2. Set the DB_FLASHBACK_RETENTION_TARGET parameter to the desired value. This value can be set as an initialization parameter if you're not using the SPFILE. This value is in minutes, which equates to three days:
SQL> alter system set db_flashback_retention_target=4320;
3. Enable the flashback capability:
sql>alter database flashback on;
4. Now the database can be opened for normal use:
sql>alter database open;

 

 

在RMAN中使用闪回数据库
(example演示了闪回某个时刻（能闪回的最旧scn）,但我在这中间曾经resetlogs也几次,也更改了控制文件,能成吗,以后测试,估计要先恢复控制文件)
1. First, query the V$FLASHBACK_DATABASE_LOG view to retrieve the OLDEST_FLASHBACK_SCN:
command>sqlplus /nolog
sql>connect / as sysdba
sql>select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
2. Next, shut down and start the database in MOUNT mode:
sql>shutdown
sql>startup mount
3. Next, issue the Flashback Database recovery from RMAN:
command>rman
rman>connect target
rman>flashback database to scn=689316;
4. Finally, open the database with the RESETLOGS option,
sql>alter database open resetlogs;

 

 

监视闪回数据库
sql>select flashback_on from v$database;
sql>select oldest_flashback_scn,oldest_flashback_time,retention_target,estimated_flashback_size from v$flashback_database_log;
sql>select * from v$flashback_database_stat;

 

 

EM使用闪回数据库（略）

 

 

第6章 从用户错误恢复Recovering from User Errors
使用闪回丢弃
sql>flashback table t1 to before drop;
了解回收站
sql>select * from t1;
sql>drop table t1;
sql>show recyclebin;
sql>select object_name as recycle_name ,original_name,object_name from recyclebin;
BIN$HbesBq6flungQAB/AQBm7A==$0 TB01
sql>select * from "BIN$0ZVR8xxxxxxxxxxxxxxx"
sql>flashback table "BIN$0Zxxxxxxxxxxxxxxx" to before drop rename to t2;
sql>select * from t2;
sql>show recyclebin;
回收站和空间利用
sql>purge table "BIN$0ZVR8eDEQbK4sxxxxxxxxxxx==$0"
sql>purge tablespace users user test;
The PURGE RECYCLEBIN command is used to purge your own Recycle Bin.
The PURGE DBA_RECYCLEBIN command removes all objects from all users' Recycle Bins.
sql>purge recyclebin;
sql>connect / as sysdba;
sql>purge dba_recyclebin;
闪回丢弃和回收站的局限性（略）
通过EM实现闪回丢弃表

 

 

使用闪回版本查询Using Flashback Versions Query
1.
sql>update t1 set salary=18000 where employee='JONES';
sql>commit;
sql>update t1 set salary=21000 where employee='JONES';
sql>commit;
sql>update t1 set salary=25000 where employee='JONES';
sql>commit;
2.显示不同时刻的value
sql>select salary from t1 versions between scn minvalue and maxvalue where employee = 'JONES';
sql>select salary from t1
versions between timestamp
to_timestamp('2004-10-26 11:37:01','YYYY-MM-DD HH:MI:SS') and
to_timestamp('2004-10-26 11:43:01','YYYY-MM-DD HH:MI:SS')
where employee = 'JONES';
sql>select current_scn,scn_to_timestamp(current_scn) from v$database;
There are several new pseudocolumns that help you work with the Flashback Versions
Query:
VERSIONS_STARTTIME
VERSIONS_STARTSCN
VERSIONS_ENDTIME
VERSIONS_ENDSCN
VERSIONS_XID
VERSIONS_OPERATION

 

 

SQL>select to_char(versions_starttime,'DD-MON HH:MI') "START DATE",
to_char (versions_endtime, 'DD-MON HH:MI') "END DATE",
versions_xid,
versions_operation,
employee,
salary
from test.t1
versions between scn
minvalue and maxvalue
where employee = 'JONES'

 

 

select to_char(versions_starttime,'DD-MON HH:MI:SS') "START DATE",
to_char (versions_endtime, 'DD-MON HH:MI:SS') "END DATE",
versions_xid,
versions_operation,
salary
from t1
versions between scn
minvalue and maxvalue
where employee_id =197;

 

 

使用闪回事务查询 Using Flashback Transaction Query
The Flashback Transaction Query is designed to be a diagnostic tool to help identify changes made to the database at the transaction level.
The Flashback Transaction Query is based on undo data 。
Using the FLASHBACK_TRANSACTION_QUERY view can help identify the table and operation that is performed against the table. This view can be large, so it is helpful to use a filter like the transaction identifier, which is in the column XID. The XID value was identified in the previous
The privilege required to use the Flashback Transaction Query is the system privilege FLASHBACK ANY TABLE. （用sysdba管理吧？）

 

 

Flashback Versions Query example.
SQL>SELECT table_name,operation,undo_sql from flashback_transaction_query
where xid='020018001F030000';
sql>select table_name,operation,undo_sql from flashback_transaction_query
where start_timestamp >= to_timestamp('2004-10-26 06:45:00','YYYY-MM-DD HH:MI:SS')
and table_owner='TEST';

 

 

使用闪回表Using Flashback Table
Flashback Table is a Flashback Technology that allows you to recover a table or set tables to a specific point-in-time without performing an incomplete recovery.
There are two main clauses that are used with the Flashback Table:
The TO SCN clause can recover the Flashback Table to a certain SCN.
The TO TIMESTAMP clause can recover the Flashback Table to a certain point-in-time.
Let's walk through performing a Flashback Table with SCN:
1.Enable ROW MOVEMENT on table T1:
sql>alter table t1 enable row movement;
2.Retrieve the current SCN before you modify the table:
sql>select current_scn from v$database; 771511
3.
sql>update t1 set salary=50000 where employee = 'JONES';
sql>commit;
4.
sql>flashback table t1 to scn 771511;
5.
sql>select * from t1 where employee='JONES';
OK已恢复

 

 

Let's walk through performing a Flashback Table with SCN: 测试时间点闪回表
FLASHBACK TABLE employee TO TIMESTAMP TO_TIMESTAMP('2003-04-04 09:30:00', `YYYY-MM-DD HH24:MI:SS');

 

 

Triggers are disabled by default during the Flashback Table process.
sql>flashback table table_name to scn 771551 enable triggers;

 

 

第7章 处理块损坏Handling BlockCorruption
The detection of block corruption involves using multiple diagnostic logs to identify that corruption has occurred.These methods include monitoring and reading the log files from theoperating system, application, and database level. If audit functions are enabled at the operating system level, these audit logs can be reviewed as well.

In a Windows environment, the Event Viewer shows the contents of the system log.
In a Unix environment, the SYSLOG file contains the system log events. This is a file located in /var/adm/syslog
in many Unix environments.
Application logs can vary for each application that is installed.
Database logs and trace file references are located in the ALERT.LOG file and the associated trace files in the UDUMP
or BDUMP directories.

 

 

The following is an example of a trace file created in the UDUMP directory:
Dump file c:\oracle\admin\ora101\udump\ora101_ora_2236.trc
...........
data_block_dump
===============
tsiz: 0x6b8
hsiz: 0x18
pbl: 0x38088044
bdba: 0x01800008
flag=-----------
ntab=1
nrow=5
哪个块怀了 ？5
---------------------

 

 

Using Various Methods to Detect and Resolve Corruption
There are four methods for detecting corruption:
The ANALYZE TABLE table_name VALIDATE STRUCTURE command
The Oracle DBVERIFY utility used against the offline data files
The init.ora parameter DB_BLOCK_CHECKING, which checks data and index blocks each time they are created or modified
The DBMS_REPAIR package used against a table, index, or partition

 

 

使用ANALYZE命令Using the ANALYZE Utility
The ANALYZE TABLE table_name VALIDATE STRUCTURE command validates the integrity of the structure of the object being analyzed.
sql>analyze table test.t3 validate structure;
ERROR at line 1:
ORA-01498: block check failure - see trace file
This is a good starting point for identifying a database object that is corrupt.

 

 

使用DBVERIFY命令
DBVERIFY is an Oracle utility that is used to see whether corruption exists in a particular datafile. This utility is most often used on a backup of the database or when the database is not running
参数：FILE START END BLOCKSIZE LOGFILE FEEDBACK PARAFILE USERID SEGMENT_ID
C:\oracle\product\10.1.0\db_1\database>dbv help=y
command>dbv blocksize=8192 file=users01.dbf
command>dbv blocksize=8192 file=users01.dbf logfile=c:\temp\users01.log

 

 

使用DB_BLOCK_CHECKING Using DB_BLOCK_CHECKING
The default is set to FALSE for all non-system tablespaces. The SYSTEM tablespace is enabled by default.
This parameter forces checks for corrupt blocks each time blocks are modified at the tablespace level.
sql>show parameter db_block_checking

 

 

使用DBMS_REPAIR程序包
用bbed模拟一个坏块
sql>create table cxy.t3 as select * from tb01;
sql>select segment_name,file_id,block_id,blocks from dba_extents where segment_name = 'T3'; 表T3占用的块
bbed>modify 1000 file 4 block 1941; 书中是5

 

 

The general process for resolving physical corruption is to verify that you have corrupt data blocks and to determine their extent. Next, you need to put the list of corrupt data blocks in a holding table so the corrupt blocks can be identified. These blocks are then marked as corrupt
so that they can be skipped over in a query or during normal usage of the table. We will also demonstrate how to fix an index and freelists that could be impacted by physical corruption in a table.

 

 

Let's walk through an example of how to detect and mark corrupt blocks:
1. Generate a trace file of the corrupt block, which is automatically created by the ANALYZE command.
sql>connect / as sysdba
sql>analyze table test.t3 validate structure;
ERROR at line 1:
ORA-01498: block check failure - see trace file

 

 

2.View the trace file to determine bad block information.
In this example, the bad block is 5. This is indicated by the output line nrow=5, highlighted at the end of this code listing.
_________________________________________________________________________________
Dump file c:\oracle\admin\ora101\udump\ora101_ora_2236.trc
……
flag=-----------
ntab=1
nrow=5
--------------------------------------------------------------------------------

 

 

3.Create the repair tables to store and retrieve information from running the DBMS_REPAIR package
sql>host repair_tab.sql
-- Create DBMS Repair Table
declare
begin
dbms_repair.admin_tables
(table_name => 'REPAIR_TABLE',
table_type => dbms_repair.repair_table,
action => dbms_repair.create_action,
tablespace => 'USERS');
end;
SQL>
SQL> @repair_tab
PL/SQL procedure successfully completed.
SQL> select owner, object_name, object_type from dba_objects
where object_name like '%REPAIR_TABLE';
-------------- ---------------- -------------------
SYS DBA_REPAIR_TABLE VIEW
SYS REPAIR_TABLE TABLE

 

 

4.Check the object, or table T3, to determine whether there is a corrupt block in the table. Even though you know this from the ANALYZE TABLE table_name VALIDATE STRUCTURE command
sql>host edit check_obj.sql
--determine what block is corrupt in a table
set serveroutput on size 100000;
declare
rpr_count int;
begin
rpr_count := 0;
dbms_repair.check_object(
schema_name => 'CXY',
object_name => 'T3',
repair_table_name => 'REPAIR_TABLE',
corrupt_count => rpr_count);
dbms_output.put_line('repair block count: '
||to_char(rpr_count));
end;
SQL> @check_obj.sql
Server Output ON
PL/SQL procedure successfully completed.
repair block count: 1

 

 

5.Verify that REPAIR_TABLE contains information about table T3 and the bad block. This query has been broken into three queries for display purposes:
SQL> select object_name, block_id, corrupt_type, marked_corrupt,
corrupt_description, repair_description
from repair_table;
OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
----------- -------- ------------ ----------
T3 3 1 FALSE(我的是true)
SQL> select object_name, corrupt_description
from repair_table;
OBJECT_NAME CORRUPT_DESCRIPTION
----------- -------------------------------------------
T3 kdbchk: row locked by non-existent transaction
table=0 slot=0
lockid=44 ktbbhitc=1
SQL> select object_name, repair_description from repair_table;
OBJECT_NAME REPAIR_DESCRIPTION
----------- ---------------------------
T3 mark block software corrupt

 

 

6. A backup of the table should be created before any attempts are made to fix the block or mark the block as corrupt. Therefore, you should attempt to salvage any good data from the corrupted block before marking it as corrupt.
sql>connect test/test
sql>create table cxy.t3_bak as select * from cxy.t3 where dbms_rowid.rowid_block_number(rowid) <> 1941 and dbms_rowid.rowid_to_absolute_fno (rowid,'CXY','T3') = 4;
读取不了,是否因为已经标记为坏块了,为什么我用 bbed修改的块,默认就标记为坏块了
sql>select c1 from t3_bak;

 

 

7.Mark block 5 as corrupt, but note that full table scans will still generate an ORA-1578 error.
sql>host edit fixblocks.sql

 

 

declare
fix_block_count int;
begin
fix_block_count := 0;
dbms_repair.fix_corrupt_blocks (
schema_name => 'CXY',
object_name => 'T3',
object_type => dbms_repair.table_object,
repair_table_name => 'REPAIR_TABLE',
fix_count => fix_block_count);
dbms_output.put_line('fix blocks count: ' ||
to_char(fix_block_count));
end;

 

 

sql>@fix_blocks
sql>select object_name,block_id,marked_corrupt from repair_table;
sql>select * from test.t3;

 

 

8.Use the DUMP_ORPHAN_KEYS procedure to dump the index entries that point to the corrupt
rows in the corrupt data blocks.
sql>host more orphan.sql
-- Create the orphan_table
declare
begin
dbms_repair.admin_tables
(table_name => 'ORPHAN_KEY_TABLE',
table_type => dbms_repair.orphan_table,
action => dbms_repair.create_action,
tablespace => 'USERS');
end;
SQL> @orphan_tab

 

 

9 Once the ORPHAN_KEY_TABLE is created, you can then dump the orphaned keys into this
table. The following example dumps the data into the ORPHAN_KEY_TABLE:
sql>host more orphan_dump.sql
sql>@orphan_dump
-- Create DBMS Dump orphan/Index entries
declare
orph_count int;
begin
orph_count:= 0;
dbms_repair.dump_orphan_keys (
schema_name => 'CXY',
object_name => 'INDEX1',
object_type => dbms_repair.index_object,
repair_table_name => 'REPAIR_TABLE',
orphan_table_name => 'ORPHAN_KEY_TABLE',
key_count => orph_count);
dbms_output.put_line('orphan-index entries: ' || to_char(orph_count));
end;
SQL>
SQL> @orphan_dump
orphan-index entries: 3
PL/SQL procedure successfully completed
sql>select index_name,count(*) from orphan_key_table group by index_name;

 

 

10.Mark the corrupt block as skip enabled.
sql>host more corrupt_block_skip.sql
-- Skips the corrupt blocks in the tables.
declare
begin
dbms_repair.skip_corrupt_blocks (
schema_name => 'CXY',
object_name => 'T3',
object_type => dbms_repair.table_object,
flags => dbms_repair.skip_flag);
end;
/
SQL> @corrupt_block_skip
PL/SQL procedure successfully completed.

 

 

11.Rebuild the freelists so that the corrupt block is never added to freelists of blocks.
sql>host more rebuild_freelists.sql
-- Removes the bad block from the freelist of blocks
declare
begin
dbms_repair.rebuild_freelists (
schema_name => 'CXY',
object_name => 'T3',
object_type => dbms_repair.table_object);
end;
/
SQL> @rebuild_freelists
PL/SQL procedure successfully completed.
我的出错：ORA-10614: Operation not allowed on this segment ,查文档,要自动段空间管理,可我已经=是自动段空间管理。

 

 

sql>@rebuild_freelists

 

 

12.
sql>drop index t3_pk;
sql>create index t3_pk on t3(c1);

 

 

块媒介恢复Block Media Recovery
command>rman
rman>connect target
rman>blockrecover datafile 5 block 6;
OK！

 

 

第8章Understanding Automatic Database Management
Composed of the Automatic Workload Repository (AWR), server-generated alerts, automated routine maintenance features, and the advisory framework, the components of the CMI simplify database administration through automation and intelligent design.
The AWR collects and processes performance statistics and metrics to be used for problem detection and self-tuning purposes. It also acts as the central storage repository for all elements of the CMI.

 

 

Using the Automatic Workload Repository (AWR)
AWR的两个组件：统计信息收集程序和工作负载储存库
The AWR consists of both the statistics collection facility and the workload repository. For simplicity, the general term AWR will be used when referring to the workload repository element.
统计信息收集程序：
The statistics collection facility of AWR resides in memory and is responsible for the collection of dynamic performance statistics.
Don't confuse AWR's statistics collection facility with Oracle's automatic statistics collection feature. AWR collects dynamic performance statistics. The automatic statistics collection feature collects optimizer statistics.
Unlike optimizer statistics, database performance statistics are not utilized by the query optimizer.Instead, they are used to measure the performance of the database over time.
数据库性能统计信息
Database performance statistics fall into one of three categories:
Cumulative values are statistics that accumulate over a period of time through continuous updating.
Metrics Metrics are statistics that represent the rate of change in a cumulative statistics category.
Sampled data Sampled data represents a sampling of the current state of all active sessions. These statistics are collected by the ASH sampler,
Oracle 10g中新的统计信息
Time model statistics, V$SYS_TIME_MODEL view:
Wait statistics, V$SERVICE_STATS view:
Operating system statistics, V$OSSTAT view:

 

 

工作负载储存库：
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 To enable AWR, the STATISTICS_LEVEL initialization parameter must be set to TYPICAL or ALL.
AWR空间考虑 略
活动会话历史 Active Session History
确定ASH的大小
ASH is actually a first-in, first-out (FIFO) buffer in memory that collects statistics on current session activity
ASH resides in the System Global Area (SGA) and its size is fixed for the lifetime of the instance.
Its size is calculated using the following calculation:The lesser of:
Total number of CPUs × 2MB of memory
5 percent of the Shared Pool size
ASH统计信息 略
ASH视图 V$ACTIVE_SESSION_HISTORY
ASH和AWR Oracle captures some of the ASH statistics to the workload repository for persistent storage
使用AWR
CREATE_SNAPSHOT Creates manual snapshots
DROP_SNAPSHOT_RANGE Drops a range of snapshots at once
CREATE_BASELINE Creates a single baseline
DROP_BASELINE Drops a single baseline
MODIFY_SNAPSHOT_SETTINGS Changes the RETENTION and INTERVAL settings
AWR快照
查看当前AWR设置
sql>select snap_interval,retention from dba_hist_wr_control;
创建快照
begin
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
end;
修改快照频率
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
RETENTION => 14400, INTERVAL => 45);
END;
丢弃快照
Exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(LOW_SNAP_ID =>157,HIGH_SNAP_ID =>170);

 

 

AWR基线
A baseline is the definition of a pair of snapshots that denote a significant workload period.
使用基线,只能人工进行基线丢弃,并同时删除相关的快照。
AWR baselines also make an excellent tool for application performance and scalability testing.
For example, a payroll application may be baselined with a small amount of test data to begin with. Then, as the test bed is increased, comparisons can be made to determine how well the application is scaling.
查询基线
sql>select * from dba_hist_snapshot;
建立基线
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(START_SNAP_ID =>180,
END_SNAP_ID =>181,
BASELINE_NAME =>'REPORTS1');
END;
/
丢弃基线
Exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE('REPORTS1',FALSE);

 

 

使用AWR视图
DBA_HIST_ACTIVE_SESS_HISTORY Displays session statistics gathered from ASH.
DBA_HIST_BASELINE Displays information on baselines in the repository.
DBA_HIST_DATABASE_INSTANCE Displays database environment data.
DBA_HIST_SQL_PLAN Displays SQL execution path data.
DBA_HIST_WR_CONTROL Displays current AWR settings.
DBA_HIST_SNAPSHOT Displays information regarding snapshots stored in the AWR.
当需要为给定的时间段建立基线时,可以查询DBA_HIST_SNAPSHOT视图
sql>select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot order by 1;
使用AWR报告
The report is run through one of two SQL*Plus scripts:
awrrpt.sql, which generates a text file report
awrrpti.sql, which generates an HTML version of the report
These scripts reside in $ORACLE_HOME/rdbms/admin (on Unix systems) or %ORACLE_HOME%\rdbms\admin (on Windows systems).
The scripts will prompt for several options:
SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql

 

 

Managing Server-Generated Alerts
Threshold alerts leverage the metrics computed by the MMON process to determine potential
performance problems. This is accomplished by comparing the current metrics to preset
threshold levels. If the threshold is exceeded, an alarm is generated.
阀值与非阀值报警
Non-threshold alerts are generated based on specific database events, rather than on threshold settings.
默认报警 oracle提供一组预定义报警
报警过程概述
The MMON process automatically computes metric values from in-memory performance statistics once every minute.

 

 

Using Enterprise Manager to Configure Thresholds
查看度量阀值 EM DATABASE CONTROL--MAMAGE METRICS 编辑度量阀值,设置多阀值
通过PL/SQL设置阀值
http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10802/d_salt.htm#1000747
TABLE 8 . 7 Comparison Operator Constants
Comparison Operator Description
OPERATOR_CONTAINS Metrics value is contained in the list of threshold values.
OPERATOR_DO_NOT_CHECK Will not apply default threshold to a specified object type.
OPERATOR_EQ Metrics value is equal to the threshold value.
OPERATOR_GE Metrics value is greater than or equal to the threshold value.
OPERATOR_GT Metrics value is greater than the threshold value.
OPERATOR_LE Metrics value is less than or equal to the threshold value.
OPERATOR_LT Metrics value is less than the threshold value.
OPERATOR_NE Metrics value is not equal to the threshold value.

 

 

exec DBMS_SERVER_ALERT.SET_THRESHOLD
(9000, --Metric Identifier, METRIC_ID in V$METRICNAME
DBMS_SERVER_ALERT.OPERATOR_GE, --Operator for Warning Threshold
'60', --Warning Threshold
DBMS_SERVER_ALERT.OPERATOR_GE, --Operator for Critical Threshold
'80', --Critical Threshold
1, --Observation Period in Minutes
1, --Occurrences
NULL, --Instance Name (NULL implies ORACLE_SID value)
DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE, ??Object Type
'KITCHEN' --Name of the Object);

 

 

BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
METRICS_ID => DBMS_SERVER_ALERT.CPU_TIME_PER_CALL,
WARNING_OPERATOR => DBMS_SERVER_ALERT.OPERATOR_GT,
WARNING_VALUE => '15000',
CRITICAL_OPERATOR => DBMS_SERVER_ALERT.OPERATOR_GT,
CRITICAL_VALUE => '30000',
OBSERVATION_PERIOD => 10,
CONSECUTIVE_OCCURRENCES => 3,
instance_name=>'ORCL',
object_type=>DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE,
object_name=>'ORCL.COM'
);
END;

 

 

 

 

catalrt.sql和dbmsslrt.sql在{ORACLE_HOME}/rdbms/admin下 安装缺少的包

 

 

查看服务器报警
TABLE 8 . 9 Server Alert Views
View Description
DBA_OUTSTANDING_ALERTS Current alerts awaiting resolution
DBA_ALERT_HISTORY Alerts that have been cleared
DBA_THRESHOLDS Threshold settings defined for the instance
V$ALERT_TYPES Alert type and group information
V$METRIC System-level metric values in memory
V$METRIC_NAME Names, identifiers, and other information about system metrics
V$METRIC_HISTORY Historical system-level metric values in memory

 

 

Using Automatic Routine Administration Tasks
使用自动例行管理程序
自动统计信息收集
Though optimizer statistics can be generated manually using the DBMS_STATS package, Oracle can do the job for you automatically through the use of its automatic statistics collection functionality. This is Oracle's recommended method for optimizer statistics collection.
The different types of optimizer statistics include the following:
Dictionary statistics,System statistics,Operating system statistics,User-defined statistics

 

 

优化器统计信息Optimizer Statistics
The availability of accurate and up-to-date optimizer statistics significantly increases the chances that the query optimizer will generate an efficient execution plan.
Let's look at a simple example of this.
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 plan for select * from sales where cust_id = 123;
sql>@utlxpls 或 SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql 是full扫描
(我的电脑上需完整路径,是path要修改吗？),
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; 利用index 访问
sql>@utlxpls 或者 @$ORACLE_HOME/rdbms/admin/utlxpls.sql
字典统计信息
系统统计信息
It also needs to know something about the hardware on which it is running (in other words, CPU and I/O performance).
System statistics can be viewed using either the V$SYSSTAT or V$SESSTAT views, as shown here:
sql>select statistic#,name,value from v$sysstat where rownum <=20;
操作系统统计信息
sql>select * from V$OSSTAT ;
用户定义统计信息

 

 

收集优化器统计信息Collecting Optimizer Statistics
When a database is created in Oracle 10g, Oracle creates a job called GATHER_STATS_JOB in the scheduler. This job runs whenever the Maintenance window (defined in the
scheduler) is opened.
If the job is still running when the Maintenance window closes, it will continue to run until completion. By default, the Maintenance window is open weeknights from 10:00 P.M. until
6:00 A.M., and all day long on the weekends.
DBMS_STATS classifies statistics as “stale” when the number of rows in the object
has been modified by more than 10 percent since the last statistics were gathered.
sql>SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME='GATHER_STATS_JOB';

 

 

BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;

 

 

管理易变对象统计信息Managing Volatile Object Statistics
Oracle 10g offers several options for dealing with volatile objects. The first, and most obvious, is to simply gather the statistics manually using DBMS_STATS. Beyond that however, there
are two other options.
The first option is to set statistics to NULL.
sql>exec DBMS_STATS.DELETE_TABLE_STATS('BUTERTB','VALATILE_TABLE'); ------ schema ,table_name
sql>exec DBMS_STATS.LOCK_TABLE_STATS('BUTERTB','VOLATILE_TABLE');
The second option is to set statistics to values that are typical for the table and lock them.
To achieve this, gather statistics when the table is at a typical size. When complete, lock the table's statistics, as shown in the preceding example.

 

 

监视DML表变化Monitoring DML Table Changes
The DML monitoring feature keeps track of all DML activity (INSERT, UPDATE, DELETE) against a table since statistics were last gathered.
*_TAB_MODIFICATIONS
改变STATISTICS_LEVEL参数
BASIC(不建议)
TYPICAL This setting ensures collection of all major statistics that the database needs for selfmanagement.
ALL This setting results in the gathering of all of the TYPICAL statistics, as well as the operating system and plan execution statistics.

 

 

Understanding the Advisory Framework
顾问概况
The Advisory Framework offers a consistent, uniform interface across all advisors allowing them to interact with each other as needed.
自动数据库诊断监视器（ADDM)
ADDM represents the pinnacle of automatic database performance tuning. Without any human interaction, it proactively identifies performance issues and bottlenecks within the database.
段顾问The Segment Advisor
The Segment Advisor analyzes space fragmentation within segments and identifies the segments that are good candidates for the new online shrink operation.
撤销顾问The Undo Advisor
he Undo Advisor helps determine appropriate sizing for Undo tablespaces and helps determine optimal UNDO_RETENTION settings.
sql调整顾问The SQL Tuning Advisor
The SQL Tuning Advisor is used to analyze individual SQL statements and provides recommendations to increase performance.
sql访问顾问The SQL Access Advisor
The SQL Access Advisor is used to analyze a SQL workload (which can consist of one or more SQL statements) and recommend appropriate access structures to improve the performance of the workload. These access structures include, but are not limited to, materialized views and indexes
内存顾问The Memory Advisor
The Memory Advisor helps you to tune the size of the different Oracle memory structures.
SGA Advisor PGA Advisor Buffer Cache Advisor Library Cache Advisor
Oracle strongly encourages the use of ASMM over the use of the Memory Advisor.

 

 

调用顾问Invoking Advisors
DBMS_ADVISORY程序包DBMS_ADVISOR Package
To highlight the usage of the DBMS_ADVISOR package, let's look at the high-level steps that would occur during a typical tuning session:
1. CREATE_TASK:
2. SET_TASK_PARAMETER:
3. EXECUTE_TASK:
4. CREATE_TASK_REPORT:
5. MARK_RECOMMENDATION:
6. CREATE_TASK_SCRIPT:

 

 

顾问视图Advisor Views
TABLE 8 . 1 5 Advisor Views
View Description
DBA_ADVISOR_DEFINITIONS Advisor properties
DBA_ADVISOR_TASKS Global information about tasks
DBA_ADVISOR_LOG Current status tasks
DBA_ADVISOR_PARAMETERS Task parameters
DBA_ADVISOR_COMMANDS Advisor commands and associated actions
DBA_ADVISOR_OBJECTS Objects referenced by tasks
DBA_ADVISOR_FINDINGS Advisor findings
DBA_ADVISOR_RECOMMENDATIONS Advisor recommendations
DBA_ADVISOR_ACTIONS Actions associated to the recommendations
DBA_ADVISOR_RATIONALE Reasons for the recommendations
DBA_ADVISOR_USAGE Usage for each advisor

 

 

 

 

自动数据库诊断监视器Automatic Database Diagnostic Monitor (ADDM)
ADDM分析
ADDM is automatically invoked by the MMON process after each AWR snapshot is performed.
DB_TIME
The DB_TIME statistic is the most important of the time model statistics. DB_TIME captures total time spent in database calls for all components. DB_TIME represents an aggregation of CPU and non-idle wait event time.
Because DB_TIME is common across database components, the goal of tuning can be simplified to “reducing DB_TIME.”
Time model statistics at the session level can be viewed using the V$SESS_TIME_MODEL view.
For time model statistics at the system level, use the V$SYS_TIME_MODEL view.
等待事件的变化Wait Event Changes
ADDM also benefits greatly from the changes made in the Wait Event model in Oracle 10g. The first of these changes is the enhanced granularity of the statistics.
The second change to the Wait Event model is the classification of wait events into highlevel classes for ease of identification.
通过EM访问ADDM
SQL调整顾问
自动调整优化器 Automatic Tuning Optimizer (ATO)
In its normal mode, the optimizer accepts a SQL statement and generates a reasonable execution plan based on the available statistics.
When placed in tuning mode, the query optimizer is referred to as the Automatic Tuning Optimizer (ATO), and its emphasis is on generating a superior execution plan
自动SQL调整 Automatic SQL Tuning
The Automatic SQL Tuning functionality analyzes SQL using four distinct methods:
Statistics Analysis
SQL Profiling
Access Path Analysis Access Path Analysis examines whether the addition of one or more new indexes would significantly increase performance for the query.
SQL Structure Analysis SQL Structure Analysis looks at the syntax, semantics, and design of a query and identifies common problems associated with each.
使用SQL调整顾问Using the SQL Tuning Advisor

 

 

DBMS_SQLTUNE程序包 DBMS_SQLTUNE Package
Utilizing the DBMS_SQLTUNE package requires the ADVISOR privilege.
The DBMS_SQLTUNE package exposes the API to directly access the SQL Tuning Advisor.

 

 

创建任务
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;
The status of the executing task can be monitored by querying the DBA_ADVISOR_LOG view or V$SESSION_LONGOPS:
select status from dba_advisor_log where task_name = 'large_table_task';
BEGIN
DBMS_SQLTUNE.INTERRUPT_TUNING_TASK( task_name => 'large_table_task');
END;
If you want to interrupt a tuning task that is currently executing, use the INTERRUPT_
TUNING_TASK procedure:
BEGIN
DBMS_SQLTUNE.INTERRUPT_TUNING_TASK( task_name => 'large_table_task');
END;

 

 

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

 

 

管理SQL_Profile
Accepting a 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 snapshot 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 DatabaseControl 使用SQL调整顾问(略)