Oracle 热备份与恢复实验
Oracle 热备份与恢复
一)热备份开启条件
1.启用归档&打开自动归档
mount mode >alter database archivelog
alter system log_archive_start=true
2.数据库未使用，或者使用量很少(热备份需要使文件处于备份模式，在此模式下数据库文件是不可用的，如果操作，会产生大量redo log)
二)热备份步骤
1.设置要备份的文件处于归档模式。
2.备份文件。
3.设置要备份的文件退出归档模式。
4.将当前的联机日志归档。
三)实验
1.查看归档启用情况
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arc_log
Oldest online log sequence 20
Next log sequence to archive 22
Current log sequence 22
2.设置备份文件进入归档模式。
这里备份Scott用户的tbs_scott表空间
SQL> select file_name from dba_data_files where TABLESPACE_NAME='TBS_SCOTT';
FILE_NAME
------------------------------------------------------------
/u01/app/oracle/oradata/DB01/tbs_scott.dbf
Elapsed: 00:00:00.04
3.备份tbs_scott表空间的数据文件
SQL> !cp /u01/app/oracle/oradata/DB01/tbs_scott.dbf /home/oracle/hotback/
SQL> !ls -l /home/oracle/hotback/
total 20488
-rw-r----- 1 oracle oinstall 20979712 May 24 18:28 tbs_scott.dbf
4.设置tbs_scott表空间退出备份模式
SQL>alter tablespace tbs_scott end backup;
Tablespace altered.
Elapsed: 00:00:00.03
SQL>
5.归档当前日志
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------------ ---
3 ONLINE /u01/app/oracle/oradata/DB01/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/DB01/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/DB01/redo01.log NO
Elapsed: 00:00:00.00
SQL> select group#,status,sequence# from v$log;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 INACTIVE 22
2 INACTIVE 23
3 CURRENT 24
Elapsed: 00:00:00.01
SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:00.08
SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:00.03
SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:02.54
SQL> select group#,status,sequence# from v$log;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 INACTIVE 25
2 INACTIVE 26
3 CURRENT 27
Elapsed: 00:00:00.03
SQL>
----备份完成--------
ps：热备份自动脚本
SQL> select 'alter tablespace'||tablespace_name ||' begin backup;'||chr(10)||
2 '!cp -v '||file_name||' /home/oracle/hotback/'||chr(10)||
3 'alter tablespace'||tablespace_name ||' end backup;'
4 from dba_data_files;
'ALTERTABLESPACE'||TABLESPACE_NAME||'BEGINBACKUP;'||CHR(10)||'!CP-V'||FILE_NAME||'/HOME/ORACLE/HOTBACK/'||CHR(10)||'ALTERTABLESPACE'||TABLESPACE_NAME|
------------------------------------------------------------------------------------------------------------------------------------------------------
alter tablespace USERS begin backup;
!cp -v /u01/app/oracle/oradata/DB01/users01.dbf /home/oracle/hotback/
alter tablespace USERS end backup;
alter tablespace UNDOTBS1 begin backup;
!cp -v /u01/app/oracle/oradata/DB01/undotbs01.dbf /home/oracle/hotback/
alter tablespace UNDOTBS1 end backup;
alter tablespace SYSAUX begin backup;
!cp -v /u01/app/oracle/oradata/DB01/sysaux01.dbf /home/oracle/hotback/
alter tablespace SYSAUX end backup;
alter tablespace SYSTEM begin backup;
!cp -v /u01/app/oracle/oradata/DB01/system01.dbf /home/oracle/hotback/
alter tablespace SYSTEM end backup;
alter tablespace TBS_SCOTT begin backup;
!cp -v /u01/app/oracle/oradata/DB01/tbs_scott.dbf /home/oracle/hotback/
alter tablespace TBS_SCOTT end backup;
Elapsed: 00:00:00.04
复制以上命令直接粘贴即可自动备份
------------------------------------------------------------------------------
--------热备份的恢复实验-------
1.删除tbs_scott表空间的文件
SQL> !rm /u01/app/oracle/oradata/DB01/tbs_scott.dbf
SQL> !ls -l /u01/app/oracle/oradata/DB01/
total 1578544
-rw-r----- 1 oracle oinstall 9748480 May 24 18:56 control01.ctl
-rw-r----- 1 oracle oinstall 52429312 May 24 18:39 redo01.log
-rw-r----- 1 oracle oinstall 52429312 May 24 18:39 redo02.log
-rw-r----- 1 oracle oinstall 52429312 May 24 18:56 redo03.log
-rw-r----- 1 oracle oinstall 597696512 May 24 18:55 sysaux01.dbf
-rw-r----- 1 oracle oinstall 775954432 May 24 18:55 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 May 22 19:49 tbs01.dbf
-rw-r----- 1 oracle oinstall 20979712 May 24 18:31 temp01.dbf
-rw-r----- 1 oracle oinstall 104865792 May 20 16:02 temp02.dbf
-rw-r----- 1 oracle oinstall 47194112 May 24 18:55 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 May 24 18:55 users01.dbf
2.对已经损坏的数据文件所包含的表更新并提交数据。
SQL> select owner,table_name from dba_tables where tablespace_name='TBS_SCOTT';
OWNER TABLE_NAME
------------------------------ ------------------------------
SCOTT TB02
SCOTT TB03
Elapsed: 00:00:00.08
SQL> select * from scott.tb03;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
Elapsed: 00:00:00.03
SQL> update scott.tb03 set sal=sal+1;
4 rows updated.
Elapsed: 00:00:00.03
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> select * from scott.tb03;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 801
7499 ALLEN 1601
7521 WARD 1251
7566 JONES 2976
Elapsed: 00:00:00.00
3.关闭数据库并重新启动，看到数据文件6已经丢失。
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 511708808 bytes
Database Buffers 255852544 bytes
Redo Buffers 6828032 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/DB01/tbs_scott.dbf'
4.使6号文件脱机，并打开数据库
SQL> alter database datafile 6 offline;
Database altered.
Elapsed: 00:00:00.05
SQL> alter database open;
Database altered.
Elapsed: 00:00:02.25
5.查询数据文件状态
SQL> select file_id,online_status,file_name from dba_data_files;
FILE_ID ONLINE_ FILE_NAME
---------- ------- ------------------------------------------------------------
4 ONLINE /u01/app/oracle/oradata/DB01/users01.dbf
3 ONLINE /u01/app/oracle/oradata/DB01/undotbs01.dbf
2 ONLINE /u01/app/oracle/oradata/DB01/sysaux01.dbf
1 SYSTEM /u01/app/oracle/oradata/DB01/system01.dbf
6 OFFLINE /u01/app/oracle/oradata/DB01/tbs_scott.dbf
Elapsed: 00:00:00.03
6.拷贝之前备份的文件到数据文件所在地
SQL> !cp /home/oracle/hotback/tbs_scott.dbf /u01/app/oracle/oradata/DB01/tbs_scott.dbf
使用recover命令恢复数据文件，会提示恢复方式，输入auto自动恢复就好。
SQL> recover datafile 6;
ORA-00279: change 1612099 generated at 05/24/2016 18:55:21 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc_log/ARC_LOG_0001_0000000027_0899418370_5b357dcf.dbf
ORA-00280: change 1612099 for thread 1 is in sequence #27
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1612585 generated at 05/24/2016 19:00:07 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc_log/ARC_LOG_0001_0000000028_0899418370_5b357dcf.dbf
ORA-00280: change 1612585 for thread 1 is in sequence #28
Log applied.
Media recovery complete.
7.使6号数据文件联机
SQL> alter database datafile 6 online;
Database altered.
Elapsed: 00:00:00.04
SQL> select file_id,online_status,file_name from dba_data_files ;
FILE_ID ONLINE_ FILE_NAME
---------- ------- ------------------------------------------------------------
4 ONLINE /u01/app/oracle/oradata/DB01/users01.dbf
3 ONLINE /u01/app/oracle/oradata/DB01/undotbs01.dbf
2 ONLINE /u01/app/oracle/oradata/DB01/sysaux01.dbf
1 SYSTEM /u01/app/oracle/oradata/DB01/system01.dbf
6 ONLINE /u01/app/oracle/oradata/DB01/tbs_scott.dbf
Elapsed: 00:00:00.03
8.查询数据，是在数据文件损坏以后且又有更新操作并提交的的数据。
SQL> select * from scott.tb03;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 801
7499 ALLEN 1601
7521 WARD 1251
7566 JONES 2976
Elapsed: 00:00:00.05
-------------------------------------------------------------------------------
沙发空闲中，快来抢！