oracle資料庫熱備中的備份和恢複及例子

標籤：startup   output   日誌組   rm -rf   set   value   span   log   挖掘   

手工熱備（開庫狀態）

備份控制檔案：

alter database backup controlfile to ‘/u01/oradata/prod/con.bak1‘;

備份資料檔案（這裡用到pl/sql進行批處理,將查詢結果粘貼運行即可）

begin
for i in (select tablespace_name from dba_tablespaces) loop
dbms_output.put_line(‘alter tablespace ‘||i.tablespace_name||‘ begin backup;‘);
for j in (select file_name from dba_data_files where tablespace_name=i.tablespace_name) loop
dbms_output.put_line(‘host cp ‘||j.file_name||‘ /home/oracle/hot_backup‘);
end loop;
dbms_output.put_line(‘alter tablespace ‘||i.tablespace_name||‘ end backup;‘);
end loop;
end;

類比現場，舉例

1.刪除emp1表 drop table emp1 purge;(不完全恢複)

類比：

drop table emp1 purge;

select * from v$log;

alter system switch logfile;

/

解決：

select name from v$archived_log;(可以看到刪除的日誌條目切換到此歸檔日誌裡了)

/u01/flash_recovery_area/PROD/archivelog/2017_08_13/o1_mf_1_6_drzyyodl_.arc
/u01/flash_recovery_area/PROD/archivelog/2017_08_27/o1_mf_1_7_dt4yy16z_.arc
/u01/flash_recovery_area/PROD/archivelog/2017_08_27/o1_mf_1_8_dt4zlzff_.arc
/u01/flash_recovery_area/PROD/archivelog/2017_08_27/o1_mf_1_9_dt4zow6g_.arc
/u01/flash_recovery_area/PROD/archivelog/2017_08_27/o1_mf_1_1_dt5fpkxt_.arc
/u01/flash_recovery_area/PROD/archivelog/2017_11_12/o1_mf_1_2_f0jh6xf2_.arc
/u01/flash_recovery_area/PROD/archivelog/2017_11_12/o1_mf_1_3_f0jk4n4p_.arc
/u01/flash_recovery_area/PROD/archivelog/2017_11_13/o1_mf_1_1_f0lkgyt6_.arc
/u01/flash_recovery_area/PROD/archivelog/2017_11_13/o1_mf_1_2_f0lo0fom_.arc

因為今天13號所以選這兩條日誌，也可以多切換幾次日誌多產生幾個日誌條目

接下來進行時間的挖掘（基於時間點來恢複）

mkdir -p /home/oracle/logmnr

show parameter utl_file_dir

alter system set utl_file_dir=‘/home/oracle/logmnr‘ scope=spfile;

startup force;

show parameter utl_file_dir;

alter database add supplemental log data;

execute dbms_logmnr_d.build(‘dict.ora‘,‘/home/oracle/logmnr‘,dbms_logmnr_d.store_in_flat_file); 

execute dbms_logmnr.add_logfile(logfilename=>‘/u01/flash_recovery_area/PROD/archivelog/2017_11_13/o1_mf_1_1_f0lkgyt6_.arc‘,options=>dbms_logmnr.new);

execute dbms_logmnr.add_logfile(logfilename=>‘/u01/flash_recovery_area/PROD/archivelog/2017_11_13/o1_mf_1_2_f0lo0fom_.arc‘,options=>dbms_logmnr.addfile);

execute dbms_logmnr.start_logmnr(dictfilename=>‘/home/oracle/logmnr/dict.ora‘,options=>dbms_logmnr.ddl_dict_tracking);

select username,scn,to_char(timestamp,‘yyyy-mm-dd hh24:mi:ss‘) time,sql_redo from v$logmnr_contents WHERE lower(sql_redo) like ‘drop table%‘;
execute dbms_logmnr.end_logmnr;

USERNAME SCN TIME
------------------------------ ---------- -------------------
SQL_REDO
------------------------------------------------------------
UNKNOWN 954522 2017-11-13 16:03:53
drop table emp1 purge;

（記錄此時間2017-11-13 16:03:53）

shutdown abort;

時間挖掘完成。

cd /home/oradata/prod

rm *.dbf

cp /home/oracle/hot_backup/*.dbf ./

startup;

recover database until time ‘2017-11-13 16:03:53‘;auto

alter database open resetlogs;

驗證查看

select * from emp1;

成功找回此表，同樣可以通過scn號尋找恢複，但是不能用閃回表因為我是purge徹底刪除，只能不完全恢複。

2.類比user01.dbf 介質損壞

rm -rf /u01/oradata/prod/users01.dbf

例如shutdown immediate

[email protected]>shutdown immediate
ORA-01116: 開啟資料庫檔案 4 時出錯
ORA-01110: 資料檔案 4: ‘/u01/oradata/prod/users01.dbf‘
ORA-27041: 無法開啟檔案

解決：

alter system flush buffer_cache;

alter tablespace users offline immediate;

cp /home/oracle/hot_backup/users01.dbf /u01/oradata/prod/

recover tablespace users;auto

alter tablespace users online;

3.類比undotbs1 介質損壞(恢複的方法多種我只示範根據檔案號恢複)

select name  from v$datafile;

rm -rf /u01/oradata/prod/undotbs01.dbf

shutdown abort

解決：

[email protected]>select FILE#,ERROR from v$recover_file;

FILE# ERROR
---------- -----------------------------------------------------------------
1 UNKNOWN ERROR
2 UNKNOWN ERROR
3 FILE NOT FOUND

[email protected]>select FILE#,NAME from v$datafile;

FILE#                                     NAME

--------------------------------------------------

1                                         /u01/oradata/prod/system01.dbf

2                                         /u01/oradata/prod/sysaux01.dbf

3                                         /u01/oradata/prod/undotbs01.dbf

4                                         /u01/oradata/prod/users01.dbf

5                                         /u01/oradata/prod/example01.dbf

[email protected]>select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1            975649
2            975649
3            0
4            975649
5            975649

確定3號檔案損壞

cp /home/oracle/hot_backup/undotbs01.dbf /u01/oradata/prod/

recovere datafile 3;auto

alter database open;

4.當前日誌，介質損壞

create table t2(id int);

insert into t2 values(100);

commit;

alter system archive log current;

insert into t2 values(200);

commit;

select * from scott.t2;

select group#,sequence#,status from v$log;

select GROUP#,MEMBER from v$logfile;

查出當前的日誌是組號是1對應的redo01.log

類比 rm -rf /u01/oradata/prod/redo01.log

shutdown abort

startup

資料庫裝載完畢。
ORA-00313: 無法開啟日誌組 1 (用於線程 1) 的成員 ORA-00312:
聯機日誌 1 線程 1: ‘/u01/oradata/prod/redo01.log‘
ORA-27037: 無法獲得檔案狀態 Linux
Error: 2: No such file or directory
Additional information: 3

解決:

cd /u01/oradata/prod/

rm *.dbf

cp /home/oracle/hot_backup/*.dbf ./

startup

recover database until cancel;auto

recover database until cancel;cancel

alter database open resetlogs;

select * from scott.t1;

ID
----------
100

5.控制檔案損壞

rm -rf /u01/oradata/prod/control01.ctl

rm -rf /u01/flash_recovery_area/prod/control02.ctl

shutdown abort

startup

ORA-00205: ?????????, ??????, ???????

select status from v$instance;

解決：

cp /home/oracle/control01.ctl /u01/oradata/prod/

cp /home/oracle/control02.ctl /u01/flash_recovery_area/prod/

recover database using backup controlfile;auto

recover database using backup controlfile;當前的日誌

alter database open resetlogs;

 

oracle資料庫熱備中的備份和恢複及例子