oracle课堂笔记---第二十七天

丢失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 3;

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

闪回

 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