ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 揭秘ORACLE备份之--热备份(也叫联机备份)

揭秘ORACLE备份之--热备份(也叫联机备份)

Linux操作系统 作者:Selena229 时间:2013-11-13 15:33:56 0 删除 编辑

热备份(又叫联机备份),数据库必须处于归档模式。

[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/28911897/viewspace-776506/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2013-11-13

  • 博文量
    3
  • 访问量
    6066