oracle 10g ocp043 文章1
第1章 配置恢復管理器Configuring Recovery Manager
組成：GUI或者命令列、可選的恢復目錄、RMAN命令和指令碼,以及磁帶媒介連線
考慮事項：RMAN是一種物理備份方法。其他的備份恢復方法：使用者管理和Oracle EXPORT工具（邏輯備份,一般不做為獨立得備份方法,而是對RMAN或者使用者管理的備份提供附加的備份保護,exp,expdp）
考慮事項：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
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;
}
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;
從命令列連線:
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----------------------------
自動分配：
_________________________________________________________
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;
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
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';
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>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;
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);
}
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>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';
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;
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';
監視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>REPORT OBSOLETE;
rman>REPORT SCHEMA;
使用RMAN作業示例
啟用ARCHIVELOG模式
sql>alter system set log_archive_start=true scope=spfile;
啟用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;
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);
}
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 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;
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;
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
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>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\*
sql>host
command> cd c:\oracle\oradata\backup\ora101t
command>copy c:\oracle\oradata\ora101t\*
command cd ..\..\ora101t
command>delete users01.dbf
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>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
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;
}
{
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;
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;
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
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 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;
}
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;
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);
}
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;
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;
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;
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;
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
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;
}
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;
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='';
配置閃回恢復區
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;
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;
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;
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;
(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;
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實現閃回丟棄表
使用閃回丟棄
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
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'
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;
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管理吧？）
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';
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已恢復
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');
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;
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 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
---------------------
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
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.
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
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
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.
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
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
--------------------------------------------------------------------------------
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
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
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
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;
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
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;
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
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;
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.
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>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);
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！
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:
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);
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);
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
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.
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.
檢視度量閥值 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);
(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;
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
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
使用自動例行管理程式
自動統計資訊收集
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 ;
使用者定義統計資訊
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';
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;
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.
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.
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.:
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
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
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.
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;
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;
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;
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調整顧問(略)
建立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調整顧問(略)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/235507/viewspace-557666/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 10g rac 安裝好文章（轉）Oracle 10g
- oracle 10g utilities .pump(1)Oracle 10g
- Oracle 10G恢復實戰1Oracle 10g
- oracle實驗記錄 (oracle 10G dataguard(1)手工搭建)Oracle
- oracle 10g 第1章 配置恢復管理器Oracle 10g
- Oracle Advanced Replication 1 例 9i to 10g MVROracleVR
- Oracle 10G管理1Z0-042 第4章 Oracle Net服務Oracle 10g
- ORACLE 10g下載|ORACLE 10g下載地址|ORACLE 10g官網下載地址Oracle 10g
- Oracle Hint 精華文章Oracle
- oracle 文章趣讀Oracle
- yangtingkun個人BLOG文章索引(Oracle文章)索引Oracle
- oracle 10g patchOracle 10g
- ORACLE 10G AUTOTRACEOracle 10g
- oracle asm 10gOracleASM
- recyclebin for oracle 10gOracle 10g
- Oracle 10g flashbackOracle 10g
- Glossary Oracle 10gOracle 10g
- Oracle遷移文章大全Oracle
- oracle學習文章（IP）Oracle
- ORACLE 10g中ASMCMD使用及ASM檔案XML DB訪問-1Oracle 10gASMXML
- oracle 10g flashback databaseOracle 10gDatabase
- Oracle 10g RAC NFSOracle 10gNFS
- oracle 10g em建立Oracle 10g
- ORACLE 10G 升級Oracle 10g
- Oracle 10g RAC TAFOracle 10g
- oracle 10g RMAN管理Oracle 10g
- ORACLE 10G AWR 速查！Oracle 10g
- Oracle AS 10g 10.1.2.0.2Oracle
- Oracle 10G 跟蹤Oracle 10g
- oracle 10g的程式Oracle 10g
- oracle 10g em patchOracle 10g
- Oracle 10g Scheduler 特性Oracle 10g
- Oracle 10g AS基本管理Oracle 10g
- oracle 10g logmnrOracle 10g
- oracle 10g downloadOracle 10g
- ORACLE 10G expdp/impdpOracle 10g
- oracle 10g logminerOracle 10g
- oracle 10g ADDMOracle 10g