oracle数据库恢复与备份

一、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视频教程学oracle，上课课家教育IT培训专家。