浅一下oracle热备份users表空间

ooioiiuu

数据库要运行在归档模式下:
archive log list
shutdown immediate
startup mount
alter database archivelog;
alter database open;
archive log list

热备份users表空间：

(1)实验环境相关信息查看

创建备份路径

1	mkdir -p /home/oracle/hotbk/

这里为了测试更改归档文件的路径

1 2 3 4 5 6 7

mkdir /home/oracle/arc_orcl_dest1/ alter system set log_archive_dest_1='location=/home/oracle/arc_orcl_dest1/'; select sequence#,name from v$archived_log; alter system switch logfile; alter system switch logfile; alter system switch logfile; select sequence#,name from v$archived_log;

查看数据文件

1 2 3 4 5 6 7 8

SQL> select name from v$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

查看表空间

1 2 3 4 5 6 7 8 9

SQL> select name from v$tablespace; NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 USERS TEMP Elapsed: 00:00:00.00

查看备份信息

1 2 3 4 5 6 7 8

SQL> select * from v$backup;      FILE# STATUS         CHANGE# TIME ---------- ------------------ ---------- ---------      1 NOT ACTIVE               0      2 NOT ACTIVE               0      3 NOT ACTIVE               0      4 NOT ACTIVE         1027726 27-SEP-16 Elapsed: 00:00:00.00

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SQL> select name,file# from v$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

查看文件号及其检查点的编号

1 2 3 4 5 6 7 8

SQL>  select file#,checkpoint_change# from v$datafile;      FILE# CHECKPOINT_CHANGE# ---------- ------------------      1          1027476      2          1027476      3          1027476      4          1027726 Elapsed: 00:00:00.01

1 2 3 4 5 6 7 8

SQL>  select file#,checkpoint_change# from v$datafile_header;      FILE# CHECKPOINT_CHANGE# ---------- ------------------      1          1027476      2          1027476      3          1027476      4          1027726 Elapsed: 00:00:00.01

(2)真正开始备份
这里备份users这个表空间。

1 2 3

alter tablespace users begin backup; !cp -v /u01/app/oracle/oradata/orcl/users01.dbf /home/oracle/hotbk/ alter tablespace users end backup;

查看备份点

1 2 3 4 5 6 7 8

SQL> select * from v$backup;      FILE# STATUS         CHANGE# TIME ---------- ------------------ ---------- ---------      1 NOT ACTIVE               0      2 NOT ACTIVE               0      3 NOT ACTIVE               0      4 NOT ACTIVE         1028454 27-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;
查看数据变化情况

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> select file#,checkpoint_change# from v$datafile;      FILE# CHECKPOINT_CHANGE# ---------- ------------------      1          1028188      2          1028188      3          1028188      4          1028454 Elapsed: 00:00:00.00 SQL>  select file#,checkpoint_change# from v$datafile_header;      FILE# CHECKPOINT_CHANGE# ---------- ------------------      1          1028188      2          1028188      3          1028188      4          1028454 Elapsed: 00:00:00.00

和之前的比较，发现checkpoint_change发生了变化。

查看有没有修复的数据块：

1 2 3

SQL> select * from v$recover_file; no rows selected Elapsed: 00:00:00.00

模拟数据文件损坏

1	SQL> !rm -f /u01/app/oracle/oradata/orcl/users01.dbf

###### alter system flush buffer_cache;这里不要执行
## startup force

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 4041949184 bytes Fixed Size            2259520 bytes Variable Size          889193920 bytes Database Buffers     3137339392 bytes Redo Buffers           13156352 bytes Database mounted. ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'

select * from v$recover_file;

1 2 3 4 5 6 7 8

SQL> select * from v$recover_file;      FILE# ONLINE  ONLINE_ ERROR ---------- ------- ------- -----------------------------------------------------------------    CHANGE# TIME ---------- ---------      4 ONLINE  ONLINE  FILE NOT FOUND      0 Elapsed: 00:00:00.01

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


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

1	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错误

恢复：使用备份之后的日志对数据文件进行前滚（把数据修改重现）

1	recover datafile 4;

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

到这里已经搞定了！！！！
写得不好，如果有什么错误请指出，谢谢！！！！