■■Configuring the Database for Backup and Recovery
■The three areas of a service level agreement relevant to backup and recovery are:
①the mean time between failures (MTBF)---refers to how frequently the database becomes unavailable.
Oracle provides two advanced options that can contribute to 100 percent availability: RAC and Streams
②the mean time to recover(MTTR)--refers to the length of downtime following a failure
③loss of data--Data Guard
In a Data Guard system the live database,known as the primary, is protected by one or more standby databases.
■Categories of Failures
①Statement Failure
・invalid data
・logic errors in the application eg:A deadlock
・Space management problems--A good DBA will monitor space usage proactively and take action before problems arise.
・insufficient privileges
②User Process Failure
If the session was in the middle of a transaction, PMON will roll back the transaction and release any locks.
③Network Failure
The three points to consider are:
・listeners
・network interface cards(operating system and hardware levels)
・routes
④User Errors
flashback query;flashback drop;the Log Miner;incomplete recovery;the Flashback Database
⑤Media Failure
⑥Instance Failure
eg:
■flashback query:
SQL> create table t (ha number);
表已创建。
SQL> begin
2 for i in 1..10
3 loop
4 insert into t values(i);
5 end loop;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
SQL> select * from t;
HA
----------
1
2
3
4
5
6
7
8
9
10
已选择10行。
SQL> delete from t;
已删除10行。
SQL> commit;
提交完成。
SQL> select * from t;
未选定行
SQL> select * from t as of timestamp(sysdate-2/24/60);
HA
----------
1
2
3
4
5
6
7
8
9
10
已选择10行。
■flashback drop：
SQL> drop table t;
表已删除。
SQL> select * from t;
select * from t
*
第 1 行出现错误:
ORA-00942: table or view does not exist
SQL> flashback table t to before drop;
闪回完成。
SQL> select * from t;
未选定行
SQL> select * from t as of timestamp(sysdate- 1/24/60);
未选定行
SQL>
■Instance Recovery
Tuning Instance Recovery
・
alter system set fast_start_mttr_target=0;
select RECOVERY_ESTIMATED_IOS, ACTUAL_REDO_BLKS, ESTIMATED_MTTR from v$instance_recovery;
alter system checkpoint;
■controlfile
・The moment that Oracle detects that a controlfile is damaged or missing, the instance will terminate immediately with an instance failure.
・To move or add a controlfile:
①shut down the database
②use an operating system command to move or copy the controlfile
③edit the CONTROL_FILES parameter to point to the new locations
④open the database as normal
■Online Redo Log Files
・You may need to add more groups for performance reasons, but two are required. Each group consists of one or more members,which are the physical files.
・If a member of a redo logfile group is damaged or missing, the database will remain open if there is a surviving member.
・groups can be added or removed and members of groups can be added or moved while the database is open,as long as there are always at least two groups, and each group has at least one valid member.
・
alter system switch logfile;
alter system checkpoint;
■Archivelog Mode and the Archiver Process
・The transition to archivelog mode can be done only while the database is in mount mode after a clean shutdown, and it must be done by a user with a SYSDBA connection.
eg:
[oracle@centos5 10.2.0.1]$ cd $ORACLE_HOME
[oracle@centos5 10.2.0.1]$ pwd
/u01/app/oracle/product/10.2.0.1
[oracle@centos5 10.2.0.1]$ mkdir ./archive1
[oracle@centos5 10.2.0.1]$ mkdir ./archive2
SQL> alter system set log_archive_dest_1 ='location=/u01/app/oracle/product/10.2.0.1/archive1' scope=spfile;
System altered.
SQL> alter system set log_archive_dest_2 ='location=/u01/app/oracle/product/10.2.0.1/archive2' scope=spfile;
System altered.
SQL> alter system set log_archive_format='arch_%d_%t_%r_%s.log' scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 385875968 bytes
Fixed Size 1219568 bytes
Variable Size 113247248 bytes
Database Buffers 268435456 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL>
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL>
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> select archiver from v$instance;
ARCHIVE
-------
STARTED
SQL> alter system switch logfile;
System altered.
SQL> select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/10.2.0.1/archive1/arch_a7315657_1_693615771_45.log
/u01/app/oracle/product/10.2.0.1/archive2/arch_a7315657_1_693615771_45.log
SQL>
