第四章：手工备份
1、备份的分类: 物理备份：备份database的数据文件、控制文件、参数文件等（备份database的物理结构），可以用于任何形式的failure。 1) 手工备份：通过OS 的命令，对要备份的文件拷贝 2） rman 备份：利用oracle 的备份工具rman 备份（或其他备份软件） 逻辑备份：只备份database 的object的数据结构和数据（如对单个表的备份），不能用于media failure ，只能恢复到备份点。 1) exp/imp 2)expd/impd -------完整的备份应该以物理备份为主，逻辑备份辅助（用于备份一些重要的表）2、手工备份：
1）数据库全备：备份database的所有数据块（datafiles、controlfile） 2）部分备份：只备份单个表空间或datafile(archivelog 模式） 3）一致性备份（冷备份）：在数据库正常关闭情况下做备份，数据库处于一致性状态。（可以用于archive和noarchive） 4）非一致性备份（热备份）：database 在open状态下备份（用于archive 模式）3、一致性备份和非一致性备份的区别
4、手工备份和恢复的命令
1）备份用os 命令 2）恢复用sql命令:recover5、备份之前对数据库的检查:v$datafile\v$controlfile\v$logfile\dba_tablespaces\dba_data_files
1)检查需要备份的数据文件 10:50:10 SQL> select name from v$datafile;NAME
------------------------------------------------------------------------------------------------------------------------ /u01/app/oracle/oradata/prod/system01.dbf /u01/app/oracle/oradata/users01.dbf /u01/app/oracle/oradata/prod/sysaux01.dbf /u01/app/oracle/oradata/prod/users01.dbf /u01/app/oracle/oradata/prod/example01.dbf /u01/app/oracle/oradata/prod/test01.dbf /u01/app/oracle/oradata/prod/undo_tbs01.dbf /u01/app/oracle/oradata/users02.dbf /u01/app/oracle/oradata/users03.dbf /u01/app/oracle/oradata/users04.dbf /u01/app/oracle/oradata/prod/index01.dbf11 rows selected.
10:49:47 SQL> col file_name for a50
10:49:57 SQL> select file_id,file_name,tablespace_name from dba_data_files;FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------ 4 /u01/app/oracle/oradata/prod/users01.dbf USERS 3 /u01/app/oracle/oradata/prod/sysaux01.dbf SYSAUX 2 /u01/app/oracle/oradata/users01.dbf USER01 1 /u01/app/oracle/oradata/prod/system01.dbf SYSTEM 5 /u01/app/oracle/oradata/prod/example01.dbf EXAMPLE 6 /u01/app/oracle/oradata/prod/test01.dbf TEST 7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf UNDO_TBS 8 /u01/app/oracle/oradata/users02.dbf USER02 9 /u01/app/oracle/oradata/users03.dbf USER03 10 /u01/app/oracle/oradata/users04.dbf USER04 11 /u01/app/oracle/oradata/prod/index01.dbf INDEXES11 rows selected.
2） 检查要备份控制文件
11:00:07 SQL> select name from v$controlfile;NAME
------------------------------------------------------------------------------------------------------------------------ /u01/app/oracle/oradata/prod/control02.ctl /u01/app/oracle/oradata/prod/control03.ctl-------redo日志不需要做备份
------- 查看日志信息 11:00:43 SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 32 52428800 1 NO CURRENT 1128307 15-AUG-11 2 1 30 52428800 1 YES INACTIVE 1082762 15-AUG-11 3 1 31 52428800 1 YES INACTIVE 1108037 15-AUG-1111:01:20 SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------------------------------------------ /u01/app/oracle/oradata/prod/redo03.log /u01/app/oracle/oradata/prod/redo02.log /u01/app/oracle/oradata/prod/redo01.log 3）查看database模式 11:01:26 SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /disk1/arch/prod Oldest online log sequence 30 Next log sequence to archive 32 Current log sequence 32 11:01:53 SQL>6、归档和非归档模式备份的区别
1）归档模式可以做一致性备份和非一致性备份，恢复时可做完全恢复和不完全恢复。 2）非归档模式只能用于一致性完全备份，恢复时，只能恢复到最后一次完全备份状态7、非一致性备份（热备份）的执行方式及热备份的监控（v$backup）
---------对只读的表空间不能做热备份，临时表空间不需要备份1）在备份前执行begin backup （在数据文件上生成检查点，写入scn ，将来恢复的时候以此scn 为起点）
11:01:26 SQL> atler database begin backup ； //对整个库做热备份 alter database end backup； alter tablespace users begin backup；//对表空间做备份 alter tablespace users end backup； 2）备份期间利用v$backup 监控 11:14:33 SQL> alter tablespace test begin backup;Tablespace altered.
11:14:42 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------ 1 1128308 2 1128308 3 1128308 4 1128308 5 1128308 6 1130194 //在备份期间 ，scn 不发生变化 7 1128308 8 1128308 9 1128308 10 1128308 11 112830811 rows selected.
11:14:56 SQL> desc v$backup;
Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- FILE# NUMBER STATUS VARCHAR2(18) CHANGE# NUMBER TIME DATE11:15:04 SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- --------- 1 NOT ACTIVE 0 2 NOT ACTIVE 0 3 NOT ACTIVE 0 4 NOT ACTIVE 0 5 NOT ACTIVE 0 6 ACTIVE 1130194 15-AUG-11 7 NOT ACTIVE 0 8 NOT ACTIVE 0 9 NOT ACTIVE 0 10 NOT ACTIVE 0 11 NOT ACTIVE 011 rows selected.
11:15:08 SQL>
-----------备份完毕，执行end backup
11:15:08 SQL> alter tablespace test end backup;Tablespace altered.
11:15:54 SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- --------- 1 NOT ACTIVE 0 2 NOT ACTIVE 0 3 NOT ACTIVE 0 4 NOT ACTIVE 0 5 NOT ACTIVE 0 6 NOT ACTIVE 1130194 15-AUG-11 7 NOT ACTIVE 0 8 NOT ACTIVE 0 9 NOT ACTIVE 0 10 NOT ACTIVE 0 11 NOT ACTIVE 011 rows selected.
11:15:57 SQL>
8、控制文件备份
1） trace 文件：用于控制文件的重建（记录database的物理架构） 11:15:57 SQL> alter database backup controlfile to trace;Database altered.
--------生成的trace 文件保存在udump的目录下
2） 二进制文件：用于database不完全恢复（或控制文件的恢复） 11:20:16 SQL> alter database backup controlfile to '/home/oracle/prod_control.bak';Database altered.
9、spfile 备份 （pfile）
1）手工时可以不做备份，可以通过pfile 生成
23:16:11 SQL> create pfile from spfile; File created. 2）rman 自动备份10、dbv检查数据文件是否有坏块
1）在手工备份前，应该检查datafile 是否有坏块，备份完后对备份也做检查 [oracle@work ~]$ dbvDBVERIFY: Release 10.2.0.1.0 - Production on Mon Aug 15 11:23:45 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Keyword Description (Default)
---------------------------------------------------- FILE File to Verify (NONE) START Start Block (First Block of File) END End Block (Last Block of File) BLOCKSIZE Logical Block Size (8192) LOGFILE Output Log (NONE) FEEDBACK Display Progress (0) PARFILE Parameter File (NONE) USERID Username/Password (NONE) SEGMENT_ID Segment ID (tsn.relfile.block) (NONE) HIGH_SCN Highest Block SCN To Verify (NONE) (scn_wrap.scn_base OR scn) [oracle@work ~]$ dbv file=/u01/app/oracle/oradata/prod/system01.dbfDBVERIFY: Release 10.2.0.1.0 - Production on Mon Aug 15 11:24:21 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/prod/system01.dbf
DBVERIFY - Verification completeTotal Pages Examined : 61440
Total Pages Processed (Data) : 36837 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 6859 Total Pages Failing (Index): 0 Total Pages Processed (Other): 1695 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 16049 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Highest block SCN : 1130761 (0.1130761) [oracle@work ~]$ 2）rman备份时，会自动检查 11、手工备份脚本 1）一致性备份（冷备份） #cold backcup remark set sql*plus variable to manipulate output set feedback off heading off verify off trimspool off echo off time off set pagesize 0 linesize 200 remark set sql*plus user variable used in this script define bakdir='/disk1/backup/prod/close_bak' //备份文件的存放位置 define bakscp='/disk1/backup/prod/close_cmd.sql' //执行备份的脚本，自动生成 prompt *** Spooling to &bakscp remark create a command file with file backup commands spool &bakscp select 'host cp '|| name ||' &bakdir ' from v$datafile order by 1; select 'host cp '|| name ||' &bakdir ' from v$controlfile order by 1; spool off; remark shutdown the database cleanly shutdown immediate; remark run the copy file commands form the operating system @&bakscp remark start the database again startup;2）非一致性备份（热备份）
set feedback off pagesize 0 heading off verify off linesize 100 trimspool on echo off time off define bakdir='/disk1/backup/prod/hot_bak' define bakscp='/disk1/backup/prod/hot_cmd.sql' define spo='&bakdir/hot_bak.lst' prompt ***spooling to &bakscp set serveroutput on spool &bakscp prompt spool &spo prompt alter system switch logfile;; declare cursor cur_tablespace is select tablespace_name from dba_tablespaces where status <>'READ ONLY' and contents not like '%TEMP%'; cursor cur_datafile (tn varchar2) is select file_name from dba_data_files where tablespace_name=tn; begin for ct in cur_tablespace loop dbms_output.put_line('alter tablespace '||ct.tablespace_name ||' begin backup; '); for cd in cur_datafile(ct.tablespace_name) loop dbms_output.put_line('host cp '||cd.file_name||' &bakdir'); end loop; dbms_output.put_line('alter tablespace '||ct.tablespace_name||' end backup;'); end loop; end; / prompt archive log list;; prompt spool off;; spool off; @&bakscp------手工备份会备份database里datafile的所有数据块
06:37:33 SQL> select file#,name,bytes/1024/1024 "Size" from v$datafile;
FILE# NAME Size
---------- -------------------------------------------------- ---------- 1 /u01/app/oracle/oradata/prod/system01.dbf 480 2 /u01/app/oracle/oradata/prod/users01.dbf 100 3 /u01/app/oracle/oradata/prod/sysaux01.dbf 250 4 /u01/app/oracle/oradata/prod/index01.dbf 100 5 /u01/app/oracle/oradata/prod/example01.dbf 100 6 /u01/app/oracle/oradata/prod/test01.dbf 10 7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf 100 8 /u01/app/oracle/oradata/prod/test02.dbf 10 9 /u01/app/oracle/oradata/prod/cuug01.dbf 109 rows selected.
06:37:47 SQL> !
[oracle@work ~]$ ls -lth /disk1/backup/prod/close_bak total 1.2G -rw-r----- 1 oracle oinstall 6.8M Aug 17 04:38 control02.ctl -rw-r----- 1 oracle oinstall 6.8M Aug 17 04:38 control03.ctl -rw-r----- 1 oracle oinstall 6.8M Aug 17 04:38 control01.ctl -rw-r----- 1 oracle oinstall 101M Aug 17 04:38 users01.dbf -rw-r----- 1 oracle oinstall 101M Aug 17 04:38 undo_tbs01.dbf -rw-r----- 1 oracle oinstall 11M Aug 17 04:38 test01.dbf -rw-r----- 1 oracle oinstall 11M Aug 17 04:38 test02.dbf -rw-r----- 1 oracle oinstall 481M Aug 17 04:38 system01.dbf -rw-r----- 1 oracle oinstall 251M Aug 17 04:37 sysaux01.dbf -rw-r----- 1 oracle oinstall 101M Aug 17 04:37 index01.dbf -rw-r----- 1 oracle oinstall 101M Aug 17 04:37 example01.dbf -rw-r----- 1 oracle oinstall 11M Aug 17 04:37 cuug01.dbf