数据库要运行在归档模式下:
archive log list
shutdown immediate
startup mount
alter database archivelog;
alter database open;
archive log list
热备份users表空间:
(1)实验环境相关信息查看
创建备份路径
mkdir-p/home/oracle/hotbk/
这里为了测试更改归档文件的路径
mkdir/home/oracle/arc_orcl_dest1/ altersystemsetlog_archive_dest_1='location=/home/oracle/arc_orcl_dest1/'; selectsequence#,namefromv$archived_log; altersystemswitchlogfile; altersystemswitchlogfile; altersystemswitchlogfile; selectsequence#,namefromv$archived_log;
查看数据文件
sql>selectnamefromv$datafile; NAME ---------------------------------------------------------------------------------------------------- /u01/app/oracle/oradata/orcl/system01.dbf /u01/app/oracle/oradata/orcl/sysaux01.dbf /u01/app/oracle/oradata/orcl/undotbs01.dbf /u01/app/oracle/oradata/orcl/users01.dbf Elapsed:00:00:00.00
查看表空间
sql>selectnamefromv$tablespace; NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 USERS TEMP Elapsed:00:00:00.00
查看备份信息
sql>select*fromv$backup; FILE#STATUSCHANGE#TIME ----------------------------------------------- 1NOTACTIVE0 2NOTACTIVE0 3NOTACTIVE0 4NOTACTIVE102772627-SEP-16 Elapsed:00:00:00.00
sql>selectname,file#fromv$datafile 2; NAME ---------------------------------------------------------------------------------------------------- FILE# ---------- /u01/app/oracle/oradata/orcl/system01.dbf 1 /u01/app/oracle/oradata/orcl/sysaux01.dbf 2 /u01/app/oracle/oradata/orcl/undotbs01.dbf 3 /u01/app/oracle/oradata/orcl/users01.dbf 4 Elapsed:00:00:00.00
查看文件号及其检查点的编号
sql>selectfile#,checkpoint_change#fromv$datafile; FILE#CHECKPOINT_CHANGE# ---------------------------- 11027476 21027476 31027476 41027726 Elapsed:00:00:00.01
sql>selectfile#,checkpoint_change#fromv$datafile_header; FILE#CHECKPOINT_CHANGE# ---------------------------- 11027476 21027476 31027476 41027726 Elapsed:00:00:00.01
(2)真正开始备份
这里备份users这个表空间。
altertablespaceusersbeginbackup; !cp-v/u01/app/oracle/oradata/orcl/users01.dbf/home/oracle/hotbk/ altertablespaceusersendbackup;
查看备份点
sql>select*fromv$backup; FILE#STATUSCHANGE#TIME ----------------------------------------------- 1NOTACTIVE0 2NOTACTIVE0 3NOTACTIVE0 4NOTACTIVE102845427-SEP-16 Elapsed:00:00:00.00
(3)模拟数据修改
sql> select owner,table_name from dba_tables where tablespace_name='USERS';
create table scott.ob2 as select * from dba_objects;
select count(*) from scott.ob2;
commit;
alter system switch logfile;
查看数据变化情况
sql>selectfile#,checkpoint_change#fromv$datafile; FILE#CHECKPOINT_CHANGE# ---------------------------- 11028188 21028188 31028188 41028454 Elapsed:00:00:00.00 sql>selectfile#,checkpoint_change#fromv$datafile_header; FILE#CHECKPOINT_CHANGE# ---------------------------- 11028188 21028188 31028188 41028454 Elapsed:00:00:00.00
和之前的比较,发现checkpoint_change发生了变化。
查看有没有修复的数据块:
sql>select*fromv$recover_file; norowsselected Elapsed:00:00:00.00
模拟数据文件损坏
sql>!rm-f/u01/app/oracle/oradata/orcl/users01.dbf
###### alter system flush buffer_cache;这里不要执行
## startup force
sql>shutdownimmediate; Databaseclosed. Databasedismounted. ORACLEinstanceshutdown. sql>startup ORACLEinstancestarted. TotalSystemGlobalArea4041949184bytes FixedSize2259520bytes VariableSize889193920bytes DatabaseBuffers3137339392bytes RedoBuffers13156352bytes Databasemounted. ORA-01157:cannotidentify/lockdatafile4-seeDBWRtracefile ORA-01110:datafile4:'/u01/app/oracle/oradata/orcl/users01.dbf'
select * from v$recover_file;
sql>select*fromv$recover_file; FILE#ONLINEONLINE_ERROR ----------------------------------------------------------------------------------------- CHANGE#TIME ------------------- 4ONLINEONLINEFILENOTFOUND 0 Elapsed:00:00:00.01
cp/home/oracle/hotbk/users01.dbf/u01/app/oracle/oradata/orcl/users01.dbf
再查看一下情况:
sql> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR
---------- ------- ------- -----------------------------------------------------------------
CHANGE# TIME
---------- ---------
4 ONLINE ONLINE
1028454 27-SEP-16
报ERROR错误
恢复:使用备份之后的日志对数据文件进行前滚(把数据修改重现)
recoverdatafile4;
sql> recover datafile 4;
Media recovery complete.
sql> select * from v$recover_file;
no rows selected
Elapsed: 00:00:00.00
sql> alter database open;
Database altered.
Elapsed: 00:00:01.43
sql> select count(*) from scott.ob2;
COUNT(*)
----------
86344
Elapsed: 00:00:00.02
到这里已经搞定了!!!!
写得不好,如果有什么错误请指出,谢谢!!!!