Oracle热备份与实例分析
1、概念
Oracle热备份是指数据库处于open状态下,对数据库的数据文件、控制文件、参数文件、密码文件等进行一系列备份操作。热备是基于用户管理备份恢复的一种方式,也是除了RMAN备份之外较为常用的一种备份方式。热备份较冷备份的主要区别就是不会丢失在备份后更新的数据。
2、热备的过程
冻结块头-->控制SCN在备份时不发生变化
进行物理拷贝
解冻块头-->让SCN可以变化(当对SCN解冻后,系统会自动更新SCN至最新的状态)
3、基于数据库的热备
alter database begin backup;
拷贝所有的datafile到备份目录
alter database end backup;
4、基于表空间的热备
alter tablespace tablespace_name begin backup;
拷贝tablespace_name表空间的数据文件到备份目录
alter tablespace tablespace_name end backup;
说明:alter tablespace tablespace_name begin backup时完成的任务:
(1)、检查点事件发生,检查点通知DBWn将该表空间上所有的脏数据被写入到磁盘
(2)、在数据文件头部冻结当前检查点事件发生时的SCN号
(3)、所有发生变化数据块的完整镜像(修改前后)被写入到redo log中
(4)、允许该表空间内数据的正常读写
5、控制文件的热备
alter database backup controlfile to '<dir>'; --控制文件的完整备份
alter database backup controlfile to trace as '<dir>' --用于创建控制文件的语句,丢失了部分信息
控制文件发生变化情况
alter database [add |drop] logfile
alter database [add |drop] logfile member
alter database [add |drop] logfile group
alter database [archivelog |noarchivelog]
alter database rename file
create tablespace
alter tablespace [add | rename] datafile
alter tablespace [read write | read only]
drop tablespace
6、参数文件的热备
create pfile = '<dir>' from spfile;
7、用户管理的热备份的原理分析
首先要确保数据库是处于归档模式。
SQL> conn / as sysdba; Connected. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 90 Next log sequence to archive 92 Current log sequence 92 SQL> alter tablespace users begin backup; Tablespace altered. SQL>
SQL> col filename for a45; SQL> col ts_name for a15; SQL> col status for a15; SQL> select d.file_name filename,d.tablespace_name ts_name,b.status from dba_data_files d,v$backup b where d.file_id=b.file# ; FILENAME TS_NAME STATUS --------------------------------------------- --------------- --------------- /u01/oracle/oradata/general10g/users01.dbf USERS ACTIVE /u01/oracle/oradata/general10g/sysaux01.dbf SYSAUX NOT ACTIVE /u01/oracle/oradata/general10g/undotbs01.dbf UNDOTBS1 NOT ACTIVE /u01/oracle/oradata/general10g/system01.dbf SYSTEM NOT ACTIVE /u01/oracle/oradata/general10g/example01.dbf EXAMPLE NOT ACTIVE /u01/oracle/oradata/general10g/cold_test_file COLD_TEST NOT ACTIVE .dbf 6 rows selected. SQL>可以看到users表空间是处于备份模式的"ACTIVE"状态的。
下面简单分析一下USERS表空间现在处于backup模式的时候,具体的信息:
在我们alter tablespace users begin backup 的时候是锁定了users表空间对应的数据文件头的change scn,首先考虑一下数据库怎么用日志文件做恢复:查找不一致的数据文件(根据文件头中旧的scn),如果锁定了文件头,这个文件头中的scn就不会改变(当然了数据块还是会变化的,还可以做读写),然后就会应用这个scn到现在的日志。backup开始后就锁定了scn,不管你后边怎么修改,总之做恢复的时候是应用锁定的时候的scn一直到现在的日志(完全恢复的话) 。
接下来看看数据文件头的change scn:
SQL> select NAME,TABLESPACE_NAME,STATUS,CHECKPOINT_CHANGE# from v$datafile_header; NAME TABLESPACE_NAME STATUS CHECKPOINT_CHANGE# -------------------------------------------------------------------------------- ------------------------------ ------- ------------------ /u01/oracle/oradata/general10g/system01.dbf SYSTEM ONLINE 4155367 /u01/oracle/oradata/general10g/undotbs01.dbf UNDOTBS1 ONLINE 4155367 /u01/oracle/oradata/general10g/sysaux01.dbf SYSAUX ONLINE 4155367 /u01/oracle/oradata/general10g/users01.dbf USERS ONLINE 4155270 /u01/oracle/oradata/general10g/example01.dbf EXAMPLE ONLINE 4155367 /u01/oracle/oradata/general10g/cold_test_file.dbf COLD_TEST ONLINE 4155367 6 rows selected SQL> alter system checkpoint; System altered SQL> select NAME,TABLESPACE_NAME,STATUS,CHECKPOINT_CHANGE# from v$datafile_header; NAME TABLESPACE_NAME STATUS CHECKPOINT_CHANGE# -------------------------------------------------------------------------------- ------------------------------ ------- ------------------ /u01/oracle/oradata/general10g/system01.dbf SYSTEM ONLINE 4155674 /u01/oracle/oradata/general10g/undotbs01.dbf UNDOTBS1 ONLINE 4155674 /u01/oracle/oradata/general10g/sysaux01.dbf SYSAUX ONLINE 4155674 /u01/oracle/oradata/general10g/users01.dbf USERS ONLINE 4155270 /u01/oracle/oradata/general10g/example01.dbf EXAMPLE ONLINE 4155674 /u01/oracle/oradata/general10g/cold_test_file.dbf COLD_TEST ONLINE 4155674 6 rows selected SQL>显然,在将users表空间置于backup状态的时候,相应的datafile的文件头的scn就不会再发生改变,发生检查点也不会改变。
接下来,end backup,看看scn
SQL> alter tablespace users end backup; Tablespace altered SQL> select NAME,TABLESPACE_NAME,STATUS,CHECKPOINT_CHANGE# from v$datafile_header; NAME TABLESPACE_NAME STATUS CHECKPOINT_CHANGE# -------------------------------------------------------------------------------- ------------------------------ ------- ------------------ /u01/oracle/oradata/general10g/system01.dbf SYSTEM ONLINE 4155674 /u01/oracle/oradata/general10g/undotbs01.dbf UNDOTBS1 ONLINE 4155674 /u01/oracle/oradata/general10g/sysaux01.dbf SYSAUX ONLINE 4155674 /u01/oracle/oradata/general10g/users01.dbf USERS ONLINE 4155674 /u01/oracle/oradata/general10g/example01.dbf EXAMPLE ONLINE 4155674 /u01/oracle/oradata/general10g/cold_test_file.dbf COLD_TEST ONLINE 4155674 6 rows selected SQL>可以看到,scn已经是最新的了!
注:看不到scn号,请点击‘源代码’进行查看
8、一个简单例子
SQL> create table hotbak_table(a int) tablespace users; Table created SQL> insert into hotbak_table values(11); 1 row inserted SQL> insert into hotbak_table values(12); 1 row inserted SQL> commit; Commit complete SQL> select * from hotbak_table; A --------------------------------------- 11 12 SQL>
SQL> ALTER TABLESPACE USERS BEGIN BACKUP; Tablespace altered.
SQL> !cp /u01/oracle/oradata/general10g/*.dbf /u01/oracle/hotbackup/ SQL> ALTER TABLESPACE USERS END BACKUP; Tablespace altered. SQL>
SQL> ALTER SYSTEM SWITCH LOGFILE; System altered. SQL> insert into hotbak_table values(13); 1 row created. SQL> commit; Commit complete. SQL>
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> !rm -f /u01/oracle/oradata/general10g/users01.dbf SQL> startup; ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1218316 bytes Variable Size 71305460 bytes Database Buffers 92274688 bytes Redo Buffers 2973696 bytes Database mounted. ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: '/u01/oracle/oradata/general10g/users01.dbf' SQL> ALTER DATABASE DATAFILE 4 OFFLINE DROP; Database altered. SQL> ALTER DATABASE OPEN; Database altered.
SQL> !cp /u01/oracle/hotbackup/*.dbf /u01/oracle/oradata/general10g/ SQL> RECOVER DATAFILE 4; Media recovery complete. SQL>如果上一步出现异常情况,需要shutdown abort,然后starup即可
SQL> ALTER DATABASE DATAFILE 4 ONLINE; Database altered. SQL> select * from hotbak_table; A ---------- 13 11 12 SQL>可以看到,后来插入的‘13’值也没有丢失!
附件:备份脚本
(1)、基于表空间热备的脚本
[oracle@linuxForOra hotbackup]$ vi hotbackup.sql
set feedback off set heading off set verify off set trimspool off set pagesize 0 set linesize 200 define dir = '/u01/oracle/hotbackup' define script = '/tmp/hotbak.sql' host rm &script host rm &dir/* spool &script select 'alter tablespace '|| tablespace_name ||' begin backup ;' || chr(10)||'host cp ' || file_name || ' &dir ' || chr(10)||'alter tablespace '|| tablespace_name || ' end backup;' from dba_data_files order by tablespace_name; spool off start &script alter database backup controlfile to '&dir/controlbak.ctl'; create pfile = '&dir/initgeneral.ora' from spfile; spool off;
SQL> start hotbackup.sql(2)、基于数据库热备的脚本
set feedback off set heading off set verify off set trimspool off set pagesize 0 set linesize 200 define dir = '/u01/oracle/hotbackup' define script = '/tmp/hotbak.sql' spool &script select 'ho cp ' ||name|| ' &dir' from v$datafile; spool off alter database begin backup; start &script alter database end backup; alter database backup controlfile to '&dir/controlbak.ctl'; create pfile = '&dir/initgeneral.ora' from spfile;