上一篇讲到，手工备份可分为冷备与热备，这章详细讲解冷备与热备的原理，且有相关的案例展示。
一、手工冷备份
其原理很简单，就是在数据库正常关库的情况下，对控制文件、数据文件或参数文件（可选）进行拷贝。
--查看控制文件、数据文件及参数文件信息
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/disk3/system01.dbf
/u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
/u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf
/u01/app/oracle/oradata/PROD/disk3/users01.dbf
SQL> select name from v$controlfile;
NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/disk1/control01.ctl
/u01/app/oracle/oradata/PROD/disk2/control01.ctl
/u01/app/oracle/oradata/PROD/disk3/control01.ctl
二、手工热备份
其原理实际也是copy的过程，只是语法不同，如手工热备份表空间A，如下：
alter tablespace A begin backup;--将要复制的数据文件头部scn号锁定
host cp;
alter tablespace A end backup;--先恢复（copy过程中，会产生dml操作，根据日志记录对copy的文件进行恢复）再解锁scn号。
热备演练（lxtbs表空间热备）
--创建lxtbs表空间
SQL> create tablespace lxtbs datafile '/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf' size 50m;
Tablespace created.
SQL> col name for a50;
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf
2 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
3 /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf
4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf
5 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
--设置lxtbs表空间为只读
SQL> alter tablespace lxtbs read only;
Tablespace altered.
--查看数据文件scn号，lxtbs对应的scn号与其它不同
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 425811
2 425811
3 425811
4 425811
5 429349
--生成全局检查点，查看scn号（lxtbs对应数据文件scn号不变）
SQL> alter system checkpoint;
System altered.
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 429391
2 429391
3 429391
4 429391
5 429349
--执行lxtbs热备
SQL> alter tablespace lxtbs begin backup;
alter tablespace lxtbs begin backup
*
ERROR at line 1:
ORA-01642: begin backup not needed for read-only tablespace 'LXTBS'
注意：以上操作可知只读的数据文件无法做热备操作，因为只读文件scn号不变，你无法对数据文件头部scn号加锁，因此无法执行begin backup操作。
--修改lxtbs对应数据文件为读写状态，并执行热备操作
SQL> alter tablespace lxtbs begin backup;
Tablespace altered.
--热备中尝试正常关库（无法正常关库）
SQL> shutdown immediate;
ORA-01149: cannot shutdown - file 5 has online backup set
ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf'
--热备中非法关库，启库时会失败
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
ORA-10873: file 5 needs to be either taken out of backup mode or media recovered
ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf'
--恢复lxtbs表空间，并开库
SQL> recover tablespace lxtbs;
Media recovery complete.
SQL> alter database open;
Database altered.
正式执行lxtbs热备演练
--执行lxtbs开始热备，并查看对应的数据文件信息
SQL> alter tablespace lxtbs begin backup;
Tablespace altered.
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf
2 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
3 /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf
4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf
5 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
--创建备份目录，并复制要热备的数据文件
[oracle@gc1 dbs]$ cd ~
[oracle@gc1 ~]$ ls
afiedt.buf database Desktop ed.hup oradiag_oracle PROD
[oracle@gc1 ~]$ mkdir tbs_bak
[oracle@gc1 ~]$ cd tbs_bak
[oracle@gc1 tbs_bak]$ pwd
/home/oracle/tbs_bak
[oracle@gc1 tbs_bak]$ cp /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf /home/oracle/tbs_bak
[oracle@gc1 tbs_bak]$ ls
lxtbs01.dbf
--结束lxtbs热备
SQL> alter tablespace lxtbs end backup;
Tablespace altered.
热备恢复演练
--非法关库，并破坏数据文件
SQL> alter tablespace lxtbs end backup;
Tablespace altered.
SQL> shutdown abort;
ORACLE instance shut down.
[oracle@gc1 tbs_bak]$ rm /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
--启库（失败）
SQL> startup
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf'
--查看数据库中错误数据文件
SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
1 UNKNOWN ERROR
2 UNKNOWN ERROR
3 UNKNOWN ERROR
4 UNKNOWN ERROR
5 FILE NOT FOUND
--转储备份的数据文件（其实就是反向复制的过程）
[oracle@gc1 tbs_bak]$ cp lxtbs01.dbf /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
--恢复数据文件，并开库
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database open;
Database altered.
三、冷备脚本
口绝：uncle's 布 head feed very fat. his son is 霹雳贝贝. they fell into the spool. sos
1、创建目录，存放冷热备份文件
[oracle@gc1 ~]$ mkdir prod_bak
[oracle@gc1 ~]$ cd prod_bak
[oracle@gc1 prod_bak]$ mkdir cold_bak hot_bak
[oracle@gc1 prod_bak]$ ls
cold_bak hot_bak
[oracle@gc1 prod_bak]$ pwd
/home/oracle/prod_bak
--编写冷备脚本
[oracle@gc1 prod_bak]$ vi cold_bak.sql
set echo off trimspool off heading off feedback off verify off time off
set pagesize 0 linesize 200
define bakdir='/home/oracle/prod_bak/cold_bak'
define bakscp='/home/oracle/prod_bak/cold_cmd.sql'
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
shutdown immediate
@&bakscp
startup
~
"cold_bak.sql" [New] 11L, 394C written
--查看冷备脚本
[oracle@gc1 prod_bak]$ more cold_bak.sql
set echo off trimspool off heading off feedback off verify off time off
set pagesize 0 linesize 200
define bakdir='/home/oracle/prod_bak/cold_bak'
define bakscp='/home/oracle/prod_bak/cold_cmd.sql'
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
shutdown immediate
@&bakscp
startup
[oracle@gc1 prod_bak]$
--执行冷备脚本
SQL> @/home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk3/system01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk3/users01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk1/control01.ctl /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk2/control01.ctl /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk3/control01.ctl /home/oracle/prod_bak/cold_bak
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
Database opened.
--查看冷备文件信息
[oracle@gc1 prod_bak]$ more cold_cmd.sql
host cp /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk3/system01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk3/users01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk1/control01.ctl /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk2/control01.ctl /home/oracle/prod_bak/cold_bak
host cp /u01/app/oracle/oradata/PROD/disk3/control01.ctl /home/oracle/prod_bak/cold_bak
[oracle@gc1 prod_bak]$ cd cold_bak
[oracle@gc1 cold_bak]$ ls
control01.ctl lxtbs01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
四、热备脚本
--编写热备脚本
[oracle@gc1 prod_bak]$ vi hot_bak.sql
set echo off trimspool off heading off feedback off verify off time off
set pagesize 0 linesize 200
define bakdir='/home/oracle/prod_bak/hot_bak'
define bakscp='/home/oracle/prod_bak/hot_cmd.sql'
set serveroutput on
spool &bakscp
prompt alter system switch logfile;;
declare
cursor cur_tablespace is
select tablespace_name from dba_tablespaces where contents not like 'TEMP%' and status='ONLINE';
cursor cur_datafile(v_name varchar2) is
select file_name from dba_data_files where tablespace_name=v_name;
begin
for i in cur_tablespace loop
dbms_output.put_line('alter tablespace '||i.tablespace_name||' begin backup;');
for j in cur_datafile(i.tablespace_name) loop
dbms_output.put_line('host cp '||j.file_name||' &bakdir');
end loop;
dbms_output.put_line('alter tablespace '||i.tablespace_name||' end backup;');
end loop;
dbms_output.put_line('alter database backup controlfile to trace;');
dbms_output.put_line('alter database backup controlfile to ''&bakdir/control01.dbf'';');
end;
/
prompt alter system switch logfile;;
spool off
@&bakscp
~
~
~
"hot_bak.sql" [New] 27L, 1074C written
--查看热备脚本
[oracle@gc1 prod_bak]$ more hot_bak.sql
set echo off trimspool off heading off feedback off verify off time off
set pagesize 0 linesize 200
define bakdir='/home/oracle/prod_bak/hot_bak'
define bakscp='/home/oracle/prod_bak/hot_cmd.sql'
set serveroutput on
spool &bakscp
prompt alter system switch logfile;;
declare
cursor cur_tablespace is
select tablespace_name from dba_tablespaces where contents not like 'TEMP%' and status='ONLINE';
cursor cur_datafile(v_name varchar2) is
select file_name from dba_data_files where tablespace_name=v_name;
begin
for i in cur_tablespace loop
dbms_output.put_line('alter tablespace '||i.tablespace_name||' begin backup;');
for j in cur_datafile(i.tablespace_name) loop
dbms_output.put_line('host cp '||j.file_name||' &bakdir');
end loop;
dbms_output.put_line('alter tablespace '||i.tablespace_name||' end backup;');
end loop;
dbms_output.put_line('alter database backup controlfile to trace;');
dbms_output.put_line('alter database backup controlfile to ''&bakdir/control01.dbf''
reuse;
');
end;
/
prompt alter system switch logfile;;
spool off
@&bakscp
[oracle@gc1 prod_bak]$
注意：当存在多次备份时，控制文件copy时会报错，可加上上面脚本红色字体部分内容reuse，表示有就覆盖。
--执行热备脚本
SQL> @/home/oracle/prod_bak/hot_bak
alter system switch logfile;
alter tablespace SYSTEM begin backup;
host cp /u01/app/oracle/oradata/PROD/disk3/system01.dbf /home/oracle/prod_bak/hot_bak
alter tablespace SYSTEM end backup;
alter tablespace SYSAUX begin backup;
host cp /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf /home/oracle/prod_bak/hot_bak
alter tablespace SYSAUX end backup;
alter tablespace UNDOTBS begin backup;
host cp /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf /home/oracle/prod_bak/hot_bak
alter tablespace UNDOTBS end backup;
alter tablespace USERS begin backup;
host cp /u01/app/oracle/oradata/PROD/disk3/users01.dbf /home/oracle/prod_bak/hot_bak
alter tablespace USERS end backup;
alter tablespace LXTBS begin backup;
host cp /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf /home/oracle/prod_bak/hot_bak
alter tablespace LXTBS end backup;
alter database backup controlfile to trace;
alter database backup controlfile to '/home/oracle/prod_bak/hot_bak/control01.dbf';
alter system switch logfile;
SQL>
--查看热备文件信息
[oracle@gc1 prod_bak]$ cd hot_bak
[oracle@gc1 hot_bak]$ ls
control01.dbf lxtbs01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
[oracle@gc1 prod_bak]$ more hot_cmd.sql
alter system switch logfile;
alter tablespace SYSTEM begin backup;
host cp /u01/app/oracle/oradata/PROD/disk3/system01.dbf /home/oracle/prod_bak/hot_bak
alter tablespace SYSTEM end backup;
alter tablespace SYSAUX begin backup;
host cp /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf /home/oracle/prod_bak/hot_bak
alter tablespace SYSAUX end backup;
alter tablespace UNDOTBS begin backup;
host cp /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf /home/oracle/prod_bak/hot_bak
alter tablespace UNDOTBS end backup;
alter tablespace USERS begin backup;
host cp /u01/app/oracle/oradata/PROD/disk3/users01.dbf /home/oracle/prod_bak/hot_bak
alter tablespace USERS end backup;
alter tablespace LXTBS begin backup;
host cp /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf /home/oracle/prod_bak/hot_bak
alter tablespace LXTBS end backup;
alter database backup controlfile to trace;
alter database backup controlfile to '/home/oracle/prod_bak/hot_bak/control01.dbf';
alter system switch logfile;
[oracle@gc1 prod_bak]$
--冷热备文件大小比较
--冷备
[oracle@gc1 cold_bak]$ ls -lht
total 1.4G
-rw-r----- 1 oracle oinstall 7.5M Mar 29 20:54 control01.ctl
-rw-r----- 1 oracle oinstall 201M Mar 29 20:54 undotbs01.dbf
-rw-r----- 1 oracle oinstall 326M Mar 29 20:53 sysaux01.dbf
-rw-r----- 1 oracle oinstall 501M Mar 29 20:53 users01.dbf
-rw-r----- 1 oracle oinstall 326M Mar 29 20:52 system01.dbf
-rw-r----- 1 oracle oinstall 51M Mar 29 20:49 lxtbs01.dbf
--热备
[oracle@gc1 hot_bak]$ ls -lht
total 1.4G
-rw-r----- 1 oracle oinstall 7.5M Mar 29 21:55 control01.dbf
-rw-r----- 1 oracle oinstall 51M Mar 29 21:55 lxtbs01.dbf
-rw-r----- 1 oracle oinstall 501M Mar 29 21:55 users01.dbf
-rw-r----- 1 oracle oinstall 201M Mar 29 21:53 undotbs01.dbf
-rw-r----- 1 oracle oinstall 326M Mar 29 21:52 sysaux01.dbf
-rw-r----- 1 oracle oinstall 326M Mar 29 21:50 system01.dbf
由此可见，冷热备生成的文件大小一样。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21251711/viewspace-1154584/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/21251711/viewspace-1154584/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
