20170817上课笔记

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;// unarchived 声明自己已知归档文件找不到了

做数据库的全备份

//sql中前边加！表示执行系统命令

丢失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日志组：//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//用来转换文件、设备，类似ghost但功能更强。if代表输入文件of代表输入  输入结束后大写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恢复目录//在其他机器创建1小型数据库专门备份数据

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

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;

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;                失败

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