热备份(又叫联机备份),数据库必须处于归档模式。
[root@dg ~(19:47:15)]# su - oracle
e[oracle@dg ~(19:47:22)]$ export ORACLE_SID=wailon
[oracle@dg ~(19:47:28)]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 28 19:47:35 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-- 系统是否处于归档模式
19:47:35 SYS@wailon> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 224
Next log sequence to archive 226
Current log sequence 226
19:48:12 SYS@wailon> select ts#,name from v$tablespace;
TS# NAME
---------- ----------------------------------------
4 USERS
2 UNDOTBS1
1 SYSAUX
0 SYSTEM
6 TBS_LRJ
7 TEMP01
6 rows selected.
19:50:37 SYS@wailon> select name,checkpoint_change# from v$database;
NAME CHECKPOINT_CHANGE#
---------------------------------------- ------------------
WAILON 2886703
19:50:59 SYS@wailon> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2886703
2 2886703
3 2886703
4 2886703
5 2886703
19:51:17 SYS@wailon> select ts#,file#,name,checkpoint_change#,status,fuzzy from v$datafile_header;
TS# FILE# NAME CHECKPOINT_CHANGE# STATUS FUZ
---------- ---------- ---------------------------------------- ------------------ ------- ---
0 1 /u01/app/oracle/oradata/system01.dbf 2886703 ONLINE YES
1 2 /u01/app/oracle/oradata/sysaux01.dbf 2886703 ONLINE YES
2 3 /u01/app/oracle/oradata/undotbs01.dbf 2886703 ONLINE YES
4 4 /u01/app/oracle/oradata/users01.dbf 2886703 ONLINE YES
6 5 /u01/app/oracle/oradata/lrj.dbf 2886703 ONLINE YES
19:51:50 SYS@wailon> -- 手工备份时冻结文件的SCN
19:51:50 SYS@wailon> -- 生成备份脚本
19:52:07 SYS@wailon> select 'alter tablespace '||tablespace_name||' begin backup;'||chr(10)||
19:54:30 2 '!cp -v '||file_name||' /home/oracle/dbbackup'||chr(10)||
19:55:10 3 'alter system checkpoint;'||chr(10)||
19:55:58 4 'select ts#,file#,name,checkpoint_change#,status,fuzzy from v$datafile_header;'||chr(10)||
19:56:21 5 'select file#,status,change#,time from v$backup;'||chr(10)||
19:56:39 6 'alter tablespace '||tablespace_name||' end backup;'
19:57:07 7 from dba_data_files;
'ALTERTABLESPACE'||TABLESPACE_NAME||'BEGINBACKUP;'||CHR(10)||'!CP-V'||FILE_NAME||'/HOME/ORACLE/DBBACKUP'||CHR(10)||'ALTE
------------------------------------------------------------------------------------------------------------------------
alter tablespace TBS_LRJ begin backup;
!cp -v /u01/app/oracle/oradata/lrj.dbf /home/oracle/dbbackup
alter system checkpoint;
select ts#,file#,name,checkpoint_change#,status,fuzzy from v$datafile_header;
select file#,status,change#,time from v$backup;
alter tablespace TBS_LRJ end backup;
alter tablespace SYSTEM begin backup;
!cp -v /u01/app/oracle/oradata/system01.dbf /home/oracle/dbbackup
alter system checkpoint;
select ts#,file#,name,checkpoint_change#,status,fuzzy from v$datafile_header;
select file#,status,change#,time from v$backup;
alter tablespace SYSTEM end backup;
alter tablespace SYSAUX begin backup;
!cp -v /u01/app/oracle/oradata/sysaux01.dbf /home/oracle/dbbackup
alter system checkpoint;
select ts#,file#,name,checkpoint_change#,status,fuzzy from v$datafile_header;
select file#,status,change#,time from v$backup;
alter tablespace SYSAUX end backup;
alter tablespace UNDOTBS1 begin backup;
!cp -v /u01/app/oracle/oradata/undotbs01.dbf /home/oracle/dbbackup
alter system checkpoint;
select ts#,file#,name,checkpoint_change#,status,fuzzy from v$datafile_header;
select file#,status,change#,time from v$backup;
alter tablespace UNDOTBS1 end backup;
alter tablespace USERS begin backup;
!cp -v /u01/app/oracle/oradata/users01.dbf /home/oracle/dbbackup
alter system checkpoint;
select ts#,file#,name,checkpoint_change#,status,fuzzy from v$datafile_header;
select file#,status,change#,time from v$backup;
alter tablespace USERS end backup;
-- 只备份USERS表空间
19:58:36 SYS@wailon> alter tablespace USERS begin backup;
!cp -v /u01/app/oracle/oradata/users01.dbf /home/oracle/dbbackup
alter system checkpoint;
select ts#,file#,name,checkpoint_change#,status,fuzzy from v$datafile_header;
select file#,status,change#,time from v$backup;
Tablespace altered.
19:58:41 SYS@wailon> `/u01/app/oracle/oradata/users01.dbf' -> `/home/oracle/dbbackup/users01.dbf'
19:58:44 SYS@wailon> alter system checkpoint;
System altered.
-- 4号文件即USERS表空间的SCN比其他表空间小
19:58:44 SYS@wailon>
TS# FILE# NAME CHECKPOINT_CHANGE# STATUS FUZ
---------- ---------- ---------------------------------------- ------------------ ------- ---
0 1 /u01/app/oracle/oradata/system01.dbf 2886703 ONLINE YES
1 2 /u01/app/oracle/oradata/sysaux01.dbf 2886703 ONLINE YES
2 3 /u01/app/oracle/oradata/undotbs01.dbf 2886703 ONLINE YES
4 4 /u01/app/oracle/oradata/users01.dbf 2887859 ONLINE YES
6 5 /u01/app/oracle/oradata/lrj.dbf 2886703 ONLINE YES
-- v$backup显示正在备份的文件
19:58:44 SYS@wailon> select file#,status,change#,time from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ------------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 ACTIVE 2887859 28-SEP-13
5 NOT ACTIVE 0
19:58:44 SYS@wailon> alter tablespace USERS end backup;
Tablespace altered.
19:58:49 SYS@wailon> select file#,status,change#,time from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ------------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 2887859 28-SEP-13
5 NOT ACTIVE 0
19:59:04 SYS@wailon> select ts#,file#,name,checkpoint_change#,status,fuzzy from v$datafile_header;
TS# FILE# NAME CHECKPOINT_CHANGE# STATUS FUZ
---------- ---------- ---------------------------------------- ------------------ ------- ---
0 1 /u01/app/oracle/oradata/system01.dbf 2886703 ONLINE YES
1 2 /u01/app/oracle/oradata/sysaux01.dbf 2886703 ONLINE YES
2 3 /u01/app/oracle/oradata/undotbs01.dbf 2886703 ONLINE YES
4 4 /u01/app/oracle/oradata/users01.dbf 2887859 ONLINE YES
6 5 /u01/app/oracle/oradata/lrj.dbf 2886703 ONLINE YES
19:59:13 SYS@wailon> alter system checkpoint;
System altered.
19:59:40 SYS@wailon> select ts#,file#,name,checkpoint_change#,status,fuzzy from v$datafile_header;
TS# FILE# NAME CHECKPOINT_CHANGE# STATUS FUZ
---------- ---------- ---------------------------------------- ------------------ ------- ---
0 1 /u01/app/oracle/oradata/system01.dbf 2887895 ONLINE YES
1 2 /u01/app/oracle/oradata/sysaux01.dbf 2887895 ONLINE YES
2 3 /u01/app/oracle/oradata/undotbs01.dbf 2887895 ONLINE YES
4 4 /u01/app/oracle/oradata/users01.dbf 2887895 ONLINE YES
6 5 /u01/app/oracle/oradata/lrj.dbf 2887895 ONLINE YES
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/429786/viewspace-776440/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/429786/viewspace-776440/