临时表空间恢复

1.建立临时表空间

SQL> create temporary tablespace temp123 tempfile '/u01/app/oracle/oradata/orcl/temp123.dbf' size 20M;


2.更改为默认

SQL> alter database default temporary tablespace temp123;


3.SQL> drop tablespace temp;(删除)

==========================================================

日志文件,控制文件恢复


1.删除2个日志文件

[oracle@sq orcl]$ rm -rf redo01.log redo03.log 


删除所有控制文件

[oracle@sq orcl]$ rm -rf *.ctl


2.

SQL> startup force(报错)


3.


CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M,

  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M,

  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M

-- STANDBY LOGFILE

DATAFILE

  '/u01/app/oracle/oradata/orcl/system01.dbf',

  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',

  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',

  '/u01/app/oracle/oradata/orcl/users01.dbf',

  '/u01/app/oracle/oradata/orcl/aaa.dbf'

CHARACTER SET WE8ISO8859P1

;


4.SQL> select open_mode from v$database;

5.SQL> alter database open;(报错)

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;(报错)


SQL> recover database; (报错)


查看恢复目录

SQL> show parameter DB_RECOVERY_FILE_DEST



SQL> recover database using BACKUP CONTROLFILE;

(using backup controlfile.  恢复就不会受“当前controlfile所纪录的SCN”的限制) 成功

提示中输入:  /u01/app/oracle/oradata/orcl/redo02.log (当前就一个日志文件)


SQL> alter database open resetlogs;


==========================================

部分数据库恢复

1.SQL> alter tablespace users begin backup;

  [oracle@sq orcl]$ cp users01.dbf /home/oracle/db_bak/


2.SQL> alter tablespace users end backup; 

  

3.SQL> create table tt as select * from dba_objects;

  SQL> select count(*) from tt;

  SQL> commit;


4.[oracle@sq orcl]$ rm -rf users01.dbf (模拟损坏)


5.SQL> alter system flush buffer_cache;

SQL> conn sys/123456 as sysdba


6.SQL> startup force;(报错 缺少4号文件)


7.SQL> alter database datafile 4 offline;

  [oracle@sq orcl]$ cp /home/oracle/db_bak/users01.dbf .


8.SQL> recover datafile 4;

SQL> alter database datafile 4 online;


9.SQL> startup force;




============================================

完全数据库恢复

步骤跟上面一样

recover database


============================================

归档模式数据文件恢复

1.SQL> create tablespace tbs_recover datafile '/oracle/app/oradata/TEST/tbs_recover.dbf' size 10M;


2.

SQL> create table tab_recover tablespace tbs_recover as select * from dba_objects;

SQL> commit;


3.SQL> alter system switch logfile;

SQL> /


4.[oracle@sq123 TEST]$ rm -rf tbs_recover.dbf 


5.SQL> alter system flush buffer_cache;


6.SQL> conn sys/123456 as sysdba

  SQL> select * from tab_recover;(报错)


7.SQL> alter database datafile 5 offline;


8.SQL> alter database create datafile '/oracle/app/oradata/TEST/tbs_recover.dbf';

(创建出表空间)


9.SQL> alter database datafile 5 online;(报错)

SQL> recover datafile 5;

AUTO


10.SQL> alter database datafile 5 online;(成功)

   SQL> select * from tab_recover;(数据回来了)


=============================================

不完全数据库恢复


日志挖掘技术确定误操作时间与scn


查看当前时间


alter database begin backup;

cp *.dbf /home/oracle/db_bak

alter database end backup;


SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;  2012-04-24 07:20:34

SQL> drop table tab_recover;


1.日志挖掘技术,确定误删除表的时间

[oracle@sq123 ~]$ mkdir logminer


存储目录

SQL> alter system set utl_file_dir='/home/oracle/logminer' scope=spfile;

SQL> shutdown immediate

SQL> startup


2.创建存储数据字典文件的文件

SQL> execute dbms_logmnr_d.build('shwdict.ora','/home/oracle/logminer');


cd /home/oracle/logminer/


3.

SQL> execute dbms_logmnr.add_logfile('/oracel/app/oradtaa/TEST/',dbms_logmnr.new);


4.挖掘数据

SQL> exec dbms_logmnr.start_logmnr (dictfilename=>'/home/oracle/logminer/shwdict.ora');


5.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';



SQL> select SCN,TIMESTAMP,SQL_REDO from v$LOGMNR_CONTENTS where SQL_REDO like '%drop%';

结果:

    716922 2012-03-12 06:12:38

drop table tab_recover AS "BIN$uv7sdpUIi1TgQAB/AQByfw==$0" ;




恢复数据


6.

SQL> shutdown abort;

SQL> startup mount

[oracle@sq123 db_bak]$ cp *.dbf /oracle/app/oradata/TEST/


SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';



7.

SQL> recover database until time '2012-04-24 07:20:34';


SQL> alter database open resetlogs;



8.

SQL> conn sys/123456 as sysdba

SQL> select count(*) from tab_recover; (数据恢复出来了)



=============================================

日志文件恢复

1.[oracle@sq orcl]$ rm *.log

  SQL> startup force(报错)


2.SQL> alter database open resetlogs;(报错)


3.SQL> recover database until cancel;

(执行RECOVER命令，在提示输入归档日志时，应用这些归档日志提示输入归档日志序列号时，不输入这个文件的名称或者不接受建议的名称，而是输入CANCEL)


(恢复不能成功,因为我们没有归档日志了)


4.添加隐藏参数

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

(强制启动数据库，设置此参数之后，在数据库Open过程中，Oracle会跳过某些一致性检查，从而使数据库可能跳过不一致状态，Open打开)


5.SQL> startup force

  SQL> alter database open resetlogs;

  (可以启动,但是刚才没有同步到硬盘的数据丢失了)



--后续操作

SQL> create pfile from spfile

然后vi pfile文件 

删除 *._allow_resetlogs_corruption

SQL> create spfile from pfile;

SQL> startup force


===============================================