《备份恢复3》
redo log
丢失一个成员：
SQL> select GROUP#, MEMBERS from v$log;
SQL> select GROUP#, MEMBER from v$logfile;
SQL> alter database add logfile member '/home/oracle/redo01b.log' to group 1; //添加新日志
SQL> alter database add logfile member '/home/oracle/redo02b.log' to group 2;
SQL> alter database add logfile member '/home/oracle/redo03b.log' to group 3;
SQL> alter system switch logfile; //切换日志
SQL> alter system switch logfile;
SQL> alter system switch logfile;
故障：
SQL> select group#, status from v$log; 确认current组
$ rm -f /home/oracle/redo02b.log 删除current组成员
SQL> alter system switch logfile;
$ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
恢复：
SQL> alter database drop logfile member '/home/oracle/redo02b.log';//将之前的删除
SQL> alter database add logfile member '/home/oracle/redo02b.log' reuse to group 2; //重新添加
如果是当前日志组，不能删除成员，只能先切换再修改
丢失inactive日志组：
故障：
SQL> alter system checkpoint;
SQL> select group#, status from v$log; 确认inactive组
SQL> shutdown abort
$ rm -f /home/oracle/redo03b.log /u01/app/oracle/oradata/orcl/redo03.log
$ startup 报错
恢复：
SQL> startup mount
SQL> select group#, status, archived from v$log;
SQL> alter database clear logfile group 3;
SQL> alter database open;
如果日志未归档：
SQL> alter database clear unarchived logfile group 3;
做数据库的全备份
丢失current日志组（正常关闭数据库）：
故障：
SQL> select group#, status from v$log; 确认current组
SQL> shutdown immediate //正常关闭
$ rm -f /home/oracle/redo02b.log /u01/app/oracle/oradata/orcl/redo02.log
SQL> startup 报错
恢复：
SQL> startup mount
SQL> select group#, status , archived from v$log;
SQL> alter database clear unarchived logfile group 2; //不归档
SQL> alter database open;
之后一定必须做数据库的全备份
丢失current日志组（非正常关闭数据库）：
前期准备：
RMAN> backup database;
SQL> create table t1(x varchar2(50));
SQL> insert into t1 values ('after backup, before archived');
SQL> commit;
SQL> alter system switch logfile;
SQL> insert into t1 values ('after backup, after archived, current');
SQL> commit;
SQL> insert into t1 values ('after backup, after archived, current, uncommitted');
SQL> alter system checkpoint;
故障：
SQL> shutdown abort //非正常关闭数据库
$ rm -f /home/oracle/redo03b.log /u01/app/oracle/oradata/orcl/redo03.log
SQL> startup 报错
SQL> select group#, sequence#, status, archived from v$log; 确认日志序号
恢复：
RMAN> run {
startup force mount;
set until sequence 10;
restore database;
recover database;
alter database open resetlogs;}
SQL> select * from t1; 丢失数据
丢失active日志组：
恢复数据块
故障：
SQL> create tablespace tbs01 datafile '/home/oracle/tbs01.dbf' size 5M;
SQL> create table t1 tablespace tbs01 as select * from dba_objects where rownum<=30000;
RMAN> backup tablespace tbs01;
SQL> alter system flush buffer_cache;
$ dd of=/home/oracle/tbs01.dbf bs=8k conv=notrunc seek=300<<EOF
SQL> select count(*) from t1; 报错
$ dbv file='/home/oracle/tbs01.dbf'
恢复：
SQL> select file#, block# from v$database_block_corruption;
RMAN> recover datafile 6 block 300;
RMAN> recover corruption list;
DBMS_REPAIR包隔离数据块
rman恢复目录
SQL> show parameter control_file_record_keep_time
用dbca创建数据库rc（不配置em、fra，200M内存，字符集unicode）
或者：
用netca创建主机连接字符串rc指向自身。
rc：
$ sqlplus sys/password@rc as sysdba
SQL> create tablespace rc_tbs datafile '/home/oracle/rc_tbs.dbf' size 50M;
SQL> create user rcowner identified by password default tablespace rc_tbs quota unlimited on rc_tbs;
SQL> grant recovery_catalog_owner to rcowner;
$ rman catalog rcowner/password@rc
RMAN> create catalog;
$ rman target sys/password@orcl catalog rcowner/password@rc
或
$ rman target / catalog rcowner/password@rc
RMAN> register database;
dbca删除rc
Flashback 闪回
功能 依赖组件 相关参数 典型错误
query undo tbs undo_retention dml
version query undo tbs undo_retention dml
flashback table undo tbs undo_retention dml
flashback drop recyclebin recyclebin, freespace drop table
transaction query supplemental log dml
fda flashback archive dml
database flashback log db_flashback_retention_target ddl
sys不允许闪回，创建新用户
SQL> create user user01 identified by password;
SQL> grant dba to user01;
SQL> conn user01/password
物理结构出问题用rman, 逻辑结构出问题用flashback
flashback query
user01:
SQL> create table t1(x int);
SQL> create index t1_x_idx on t1(x);
SQL> insert into t1 values (1);
SQL> commit;
SQL> select sysdate from dual;
SQL> select dbms_flashback.get_system_change_number from dual;
SQL> delete t1;
SQL> commit;
SQL> select * from t1;
SQL> select * from t1 as of scn 1446069; //闪回查询scn为1446069时的数据
SQL> select * from t1 as of timestamp to_timestamp('2015-10-28 10:31:54', 'yyyy-mm-dd hh24:mi:ss');//闪回查询某时间点时的数据
SQL> truncate table t1;或alter table t1 move;或收缩数据文件
SQL> select * from t1 as of scn 1446069; 物理结构变化，闪回失败
logminer
flashback version query
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL> update t1 set x=2;
SQL> commit;
SQL> update t1 set x=3;
SQL> commit;
SQL> update t1 set x=4;
SQL> commit;
SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation, x
from t1
versions between scn minvalue and maxvalue
order by versions_starttime;
versions between timestamp to_timestamp('2015-10-28 9:00:00', 'yyyy-mm-dd hh24:mi:ss') and to_timestamp('2015-10-28 10:00:00', 'yyyy-mm-dd hh24:mi:ss')
SQL> truncate table t1; 物理结构改变，查询失败
flashback table
SQL> conn user01/password
SQL> create table my_dept(deptno int primary key, dname varchar2(20));
SQL> create table my_emp(empno int primary key, deptno int references my_dept);
SQL> insert into my_dept values (10, 'sales');
SQL> insert into my_emp values (100, 10);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;
SQL> delete my_emp;
SQL> delete my_dept;
SQL> commit;
SQL> alter table my_dept enable row movement;
SQL> alter table my_emp enable row movement;
SQL> flashback table my_emp to scn 1451706; //失败,先闪回dept，再闪回emp
SQL> flashback table my_dept to scn 1451706;
SQL> flashback table my_emp to scn 1451706;
SQL> select INDEX_NAME, STATUS from user_indexes where table_name='MY_EMP';自动维护索引
SQL> select INDEX_NAME, STATUS from user_indexes where table_name='MY_DEPT';
SQL> truncate table my_emp;
SQL> flashback table my_emp to scn 1451706; 失败
flashback drop
SQL> show parameter recyclebin
SQL> purge recyclebin;
SQL> create tablespace tbs01 datafile '/home/oracle/tbs01.dbf' size 5M;
SQL> create table t1 tablespace tbs01 as select * from dba_objects where rownum<=20000;
SQL> create index t1_object_id_idx on t1(object_id) tablespace tbs01;
SQL> select INDEX_NAME from user_indexes where TABLE_NAME='T1';
SQL> drop table t1;
SQL> select table_name from user_tables;
SQL> show recyclebin
SQL> select object_name, original_name, type, droptime from user_recyclebin; 包含index
SQL> select count(*) from "BIN$IyKOcy5jPo7gUwEAqMCBEg==$0";
SQL> flashback table t1 to before drop;
SQL> select INDEX_NAME from user_indexes where TABLE_NAME='T1';
SQL> alter index "BIN$LRyc7hA1JaPgUwEAqMDzWw==$0" rename to T1_OBJECT_ID_IDX; 恢复index名称
重名的处理：
SQL> flashback table "BIN$IyKOcy5jPo7gUwEAqMCBEg==$0" to before drop;
SQL> flashback table t1 to before drop rename to t2;
SQL> drop table t1;
SQL> show recyclebin 在回收站中
SQL> create table t2 tablespace tbs01 as select * from dba_objects where rownum<=30000;
SQL> show recyclebin t1被覆盖
SQL> drop table t2 purge;
SQL> purge recyclebin
