一、oracle數據庫恢復

1.恢復剛才刪除的一條數據

delete from emp e where e.empname='SMITH'

select * from flashback_transaction_query f where f.table_name='EMP'
UNDO_SQL下面的語句為剛才刪除數據的相反操作，執行該語句即可恢復剛才刪除的數據

在11g版本中UNDO_SQL為空值
alter database add supplemental log data

2.恢復剛更新的數據到某一時間點

update emp e set e.job='clerk'

select sysdate from dual;

查看指定時間點時的數據是否是需要恢復前的數據：

select * from emp as of timestamp to_timestamp('2013/3/30 21:10:19', 'yyyy-mm-dd hh24:mi:ss')

alter table emp enable row movement;

恢復到某一時間點：

flashback table emp to timestamp to_timestamp('2013/3/30 21:10:19', 'yyyy-mm-dd hh24:mi:ss')

3.恢復刪除的表

drop table bonus

flashback table bonus to before drop
查看被刪除的表：
select * from user_recyclebin u order by u.droptime desc

二、oracle數據庫備份

1.數據庫的RMAN備份須要在歸檔模式下
查看歸檔模式
$ sqlplus / as sysdba
SQL> archive log list
Database log mode ? ? ? ? ? ? ?No Archive Mode
Automatic archival ? ? ? ? ? ? Disabled
Archive destination ? ? ? ? ? ?USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence ? ? 18

Current log sequence ? ? ? ? ? 20

更改歸檔模式

關閉數據庫
SQL> shutdown immediate
啟動數據庫到mount狀態
SQL> startup mount
更改為歸檔模式（noarchivelog為非歸檔模式）
SQL> alter database archivelog;
再次查看是否為歸模式
SQL> archive log list
Database log mode ? ? ? ? ? ? ?Archive Mode
Automatic archival ? ? ? ? ? ? Enabled
Archive destination ? ? ? ? ? ?USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence ? ? 18
Next log sequence to archive ? 20
Current log sequence ? ? ? ? ? 20

進入RMAN
$ rman target/
查看默認設置
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/app/oracle/product/11.1.0/db_1/dbs/snapcf_ixdba.f'; # default
其中CONFIGURE CONTROLFILE AUTOBACKUP OFF;即默認不備份控制文件，需要修改為默認備份控制文件
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;

數據庫全庫備份
RMAN> backup database;
查看備份的相關信息
RMAN> list backup;
List of Backup Sets
===================

BS Key ?Type LV Size ? ? ? Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 ? ? ? Full ? ?1006.31M ? DISK ? ? ? ?00:08:09 ? ? 31-AUG-13 ? ? ?
? ? ? ? BP Key: 1 ? Status: AVAILABLE ?Compressed: NO ?Tag: TAG20130831T025434
? ? ? ? Piece Name: /app/oracle/flash_recovery_area/ORCL/backupset/2013_08_31/o1_mf_nnndf_TAG20130831T025434_923hbw6f_.bkp
? List of Datafiles in backup set 1
? File LV Type Ckp SCN ? ?Ckp Time ?Name
? ---- -- ---- ---------- --------- ----
? 1 ? ? ? Full 1037059 ? ?31-AUG-13 /app/oracle/oradata/orcl/system01.dbf
? 2 ? ? ? Full 1037059 ? ?31-AUG-13 /app/oracle/oradata/orcl/sysaux01.dbf
? 3 ? ? ? Full 1037059 ? ?31-AUG-13 /app/oracle/oradata/orcl/undotbs01.dbf
? 4 ? ? ? Full 1037059 ? ?31-AUG-13 /app/oracle/oradata/orcl/users01.dbf
? 5 ? ? ? Full 1037059 ? ?31-AUG-13 /app/oracle/oradata/orcl/bank_data01.dbf


BS Key ?Type LV Size ? ? ? Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 ? ? ? Full ? ?9.33M ? ? ?DISK ? ? ? ?00:00:04 ? ? 31-AUG-13 ? ? ?
? ? ? ? BP Key: 2 ? Status: AVAILABLE ?Compressed: NO ?Tag: TAG20130831T030251
? ? ? ? Piece Name: /app/oracle/flash_recovery_area/ORCL/autobackup/2013_08_31/o1_mf_n_824870625_923htgw8_.bkp

? Control File Included: Ckp SCN: 1037059 ? ? ?Ckp time: 31-AUG-13

三、oracle數據庫全庫恢復

數據庫啟動過程
找到初始化spfile或pfile，處于nomount狀態；
根據初始化文件找到控制文件Contral File，處于mount狀態；
根據控制文件找到數據文件Data File、重做日志文件Redo File,處于open狀態；
恢復數據庫的前提是Oracle數據庫的初始化spfile文件、控制文件、重做日志、歸檔日志、備份都可以正常使用

恢復原則：根據丟失的文件情況，啟動數據庫到相應狀態，然后通過RMAN恢復相應文件，再將數據庫啟動到下一狀態

查看是否有備份
RMAN> list backup summary;
1.僅丟失數據文件情況

刪除數據文件
# rm -rf *.dbf
啟動數據庫
SQL> startup
ORACLE instance started.

Total System Global Area ?527290368 bytes
Fixed Size ? ? ? ? ? ? ? ? ?1337660 bytes
Variable Size ? ? ? ? ? ? 318768836 bytes
Database Buffers ? ? ? ? ?201326592 bytes
Redo Buffers ? ? ? ? ? ? ? ?5857280 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/app/oracle/oradata/orcl/system01.dbf'

進入RMAN
$ rman target/
恢復數據文件
RMAN> restore database;

Starting restore at 31-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /app/oracle/oradata/orcl/bank_data01.dbf
channel ORA_DISK_1: reading from backup piece /app/oracle/flash_recovery_area/ORCL/backupset/2013_08_31/o1_mf_nnndf_TAG20130831T025434_923hbw6f_.bkp
channel ORA_DISK_1: piece handle=/app/oracle/flash_recovery_area/ORCL/backupset/2013_08_31/o1_mf_nnndf_TAG20130831T025434_923hbw6f_.bkp tag=TAG20130831T025434
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:58
Finished restore at 31-AUG-13

RMAN> recover database;

Starting recover at 31-AUG-13
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:07

Finished recover at 31-AUG-13
進入SQL將狀態改為open即恢復成功
SQL> alter database open;

2.丟失重做日志文件
# rm -rf *.log
SQL> recover database until cancel;
SQL> alter database open resetlogs;

3.丟失控制文件、重做日志文件、數據文件
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
SQL> recover database using backup controfile until cancel;
SQL> alter database open resetlogs;

4.初始化文件也丟失的情況
SQL> startup fpile='/app/oracle/admin/orcl/pfile/init.ora.2220136918';
RMAN> restore spfile from autobackup;
SQL> startup nomount;
其余步驟同丟失控制文件

?

oracle數據庫恢復與備份