浅一下oracle热备份users表空间

数据库要运行在归档模式下:
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

备注：这里4号文件找不到


还原：使用备份的文件代替丢失的文件

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

到这里已经搞定了！！！！

写得不好，如果有什么错误请指出，谢谢！！！！

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!