当前位置:数据库 > Oracle >>

oracle数据库恢复与备份

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/
恢复数据文
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,