ITPub博客

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

揭秘ORACLE备份之--冷备份(也叫脱机备份)

原创 Linux操作系统 作者:wailon 时间:2013-11-12 21:10:48 0 删除 编辑

在本章开始之前,很多文章都是用实验来展示,相关的理论知识有所欠缺。
因为我觉得理论的东西,首先我不擅长,还是建议想详细了解的同学去查阅官方文档,那是每个DBA必读的,甚至要读N遍。
从下面这一节开始,我会以实验展示ORACLE数据库所有的备份方法及注意事项,尤其以RMAN为主。
其实从备份的实用性来讲,不是说哪个方法最好,哪个方法不好,根据环境及需求的不同,而选择最合适的就是好。
废话不多说,进入正题。揭秘ORACLE备份之--冷备份(也叫脱机备份)

冷备份最主要的特征就是需要关闭数据库,手工备份相关的参数文件、控制文件、日志文件以及控制文件。

[root@dg ~(18:49:57)]# su - oracle
[oracle@dg ~(18:50:38)]$ export ORACLE_SID=wailon
[oracle@dg ~(18:50:43)]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 28 18:50:46 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

-- 检查实例、数据文件及表空间状态,确保所有状态都正常
18:50:46 SYS@wailon>  select status from v$Instance;

STATUS
------------
OPEN

18:51:04 SYS@wailon>  select file#,name,status from v$datafile;

     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /u01/app/oracle/oradata/system01.dbf     SYSTEM
         2 /u01/app/oracle/oradata/sysaux01.dbf     ONLINE
         3 /u01/app/oracle/oradata/undotbs01.dbf    ONLINE
         4 /u01/app/oracle/oradata/users01.dbf      ONLINE
         5 /u01/app/oracle/oradata/lrj.dbf          ONLINE

18:51:13 SYS@wailon>  select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
USERS                          ONLINE
TBS_LRJ                        ONLINE
TEMP01                         ONLINE

6 rows selected.

18:51:26 SYS@wailon>  host
[oracle@dg ~(18:51:29)]$ -- 创建备份所需的目录
bash: --: command not found
[oracle@dg ~(18:52:05)]$ mkdir dbbackup
[oracle@dg ~(18:52:36)]$ exit
exit

-- 生成相关文件的复制脚本
18:54:36 SYS@wailon>  select 'cp -v '||name||' /home/oracle/dbbackup'
18:55:01   2  from (select name from v$controlfile
18:55:13   3  union all
18:55:16   4  select name from v$datafile
18:55:22   5  union all
18:55:25   6  select member from v$logfile);

'CP-V'||NAME||'/HOME/ORACLE/DBBACKUP'
------------------------------------------------------------------------------------------------------------------------
cp -v /u01/app/oracle/oradata/wailon/control01.ctl /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/wailon/control02.ctl /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/system01.dbf /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/sysaux01.dbf /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/undotbs01.dbf /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/users01.dbf /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/lrj.dbf /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/wailon/redo02.log /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/wailon/redo01.log /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/wailon/redo03.log /home/oracle/dbbackup

10 rows selected.

18:55:29 SYS@wailon>  host
[oracle@dg ~(18:55:40)]$ cd dbbackup
[oracle@dg dbbackup(18:56:29)]$ -- 生成备份脚本
[oracle@dg dbbackup(19:00:10)]$ more shutdown.sql
connect / as sysdba
shutdown immediate;
exit
[oracle@dg dbbackup(19:00:17)]$ more startup.sql
connect / as sysdba
startup;
exit
                                                                      
[oracle@dg dbbackup(19:05:11)]$ more dbbackup.sh
sqlplus /nolog @/home/oracle/dbbackup/shutdown.sql                -- 备份前先关闭数据库
cp -v $ORACLE_HOME/dbs/orapw$ORACLE_SID /home/oracle/dbbackup
cp -v $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/wailon/control01.ctl /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/wailon/control02.ctl /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/system01.dbf /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/sysaux01.dbf /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/undotbs01.dbf /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/users01.dbf /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/lrj.dbf /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/wailon/redo02.log /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/wailon/redo01.log /home/oracle/dbbackup
cp -v /u01/app/oracle/oradata/wailon/redo03.log /home/oracle/dbbackup
sqlplus /nolog @/home/oracle/dbbackup/startup.sql            -- 备份完成后启动数据库

[oracle@dg dbbackup(19:00:21)]$ chmod +x dbbackup.sh

-- 执行脚本备份所有相关文件
[oracle@dg dbbackup(19:00:21)]$ ./dbbackup.sh

SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 28 19:37:28 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected.
Database closed.
Database dismounted.
ORACLE instance shut down.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
----------------------------开始备份文件前先关闭数据库实例------------------------------------------------------------

`/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwwailon' -> `/home/oracle/dbbackup/orapwwailon'
`/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilewailon.ora' -> `/home/oracle/dbbackup/spfilewailon.ora'
`/u01/app/oracle/oradata/wailon/control01.ctl' -> `/home/oracle/dbbackup/control01.ctl'
`/u01/app/oracle/oradata/wailon/control02.ctl' -> `/home/oracle/dbbackup/control02.ctl'
`/u01/app/oracle/oradata/system01.dbf' -> `/home/oracle/dbbackup/system01.dbf'
`/u01/app/oracle/oradata/sysaux01.dbf' -> `/home/oracle/dbbackup/sysaux01.dbf'
`/u01/app/oracle/oradata/undotbs01.dbf' -> `/home/oracle/dbbackup/undotbs01.dbf'
`/u01/app/oracle/oradata/users01.dbf' -> `/home/oracle/dbbackup/users01.dbf'
`/u01/app/oracle/oradata/lrj.dbf' -> `/home/oracle/dbbackup/lrj.dbf'
`/u01/app/oracle/oradata/wailon/redo02.log' -> `/home/oracle/dbbackup/redo02.log'
`/u01/app/oracle/oradata/wailon/redo01.log' -> `/home/oracle/dbbackup/redo01.log'
`/u01/app/oracle/oradata/wailon/redo03.log' -> `/home/oracle/dbbackup/redo03.log'
----------------------------文件备份完成后启动数据库实例------------------------------------------------------------

SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 28 19:40:03 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             310381872 bytes
Database Buffers           96468992 bytes
Redo Buffers                8466432 bytes
Database mounted.
Database opened.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 -- 查看备份的文件
[oracle@dg dbbackup(19:05:14)]$ ll
total 3695796
-rw-r----- 1 oracle oinstall    9781248 Sep 28 19:01 control01.ctl
-rw-r----- 1 oracle oinstall    9781248 Sep 28 19:01 control02.ctl
-rwxr-xr-x 1 oracle oinstall        894 Sep 28 19:05 dbbackup.sh
-rw-r--r-- 1 oracle oinstall  961224704 Sep 28 19:03 lrj.dbf
-rw-r----- 1 oracle oinstall       2048 Sep 28 19:01 orapwwailon
-rw-r----- 1 oracle oinstall   52429312 Sep 28 19:04 redo01.log
-rw-r----- 1 oracle oinstall   52429312 Sep 28 19:04 redo02.log
-rw-r----- 1 oracle oinstall   52429312 Sep 28 19:04 redo03.log
-rw-r--r-- 1 oracle oinstall         45 Sep 28 18:57 shutdown
-rw-r--r-- 1 oracle oinstall         34 Sep 28 18:57 startup
-rw-r----- 1 oracle oinstall  681582592 Sep 28 19:02 sysaux01.dbf
-rw-r----- 1 oracle oinstall  754982912 Sep 28 19:01 system01.dbf
-rw-r----- 1 oracle oinstall 1127227392 Sep 28 19:03 undotbs01.dbf
-rw-r----- 1 oracle oinstall   82583552 Sep 28 19:03 users01.dbf

-- 检验备份的数据文件,确保备份可用。这才是备份最重要的!

[oracle@dg dbbackup(19:05:16)]$ dbv file=system01.dbf blocksize=8192

DBVERIFY: Release 11.2.0.3.0 - Production on Sat Sep 28 19:06:01 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /home/oracle/dbbackup/system01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 92160
Total Pages Processed (Data) : 60066
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 13459
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 3488
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 15147
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2884946 (0.2884946)


-- 检验所有数据文件

[oracle@dg dbbackup(19:06:19)]$ for i in $(find -name "*.dbf");
> do dbv file=$i blocksize=8192;
> done

DBVERIFY: Release 11.2.0.3.0 - Production on Sat Sep 28 19:07:38 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /home/oracle/dbbackup/undotbs01.dbf
Page 80000 is marked corrupt
Corrupt block relative dba: 0x00c13880 (file 3, block 80000)
Bad header found during dbv:
Data in bad block:
 type: 2 format: 2 rdba: 0x00c141b0
 last change scn: 0x0000.001b858a seq: 0x59 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x858a0259
 check value in block header: 0x62bb
 computed block checksum: 0x0

Page 80001 is marked corrupt
Corrupt block relative dba: 0x00c13881 (file 3, block 80001)
Bad header found during dbv:
Data in bad block:
 type: 2 format: 2 rdba: 0x00c141b1
 last change scn: 0x0000.001b858a seq: 0x8 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x858a0208
 check value in block header: 0xb940
 computed block checksum: 0x0

Page 80002 is marked corrupt
Corrupt block relative dba: 0x00c13882 (file 3, block 80002)
Bad header found during dbv:
Data in bad block:
 type: 2 format: 2 rdba: 0x00c141b2
 last change scn: 0x0000.001b8589 seq: 0x51 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x85890251
 check value in block header: 0xbd2e
 computed block checksum: 0x0

-- 此处省略部分

DBVERIFY - Verification complete

Total Pages Examined         : 137600
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 137183
Total Pages Processed (Seg)  : 10
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 129
Total Pages Marked Corrupt   : 288
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2885252 (0.2885252)

DBVERIFY: Release 11.2.0.3.0 - Production on Sat Sep 28 19:08:01 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /home/oracle/dbbackup/sysaux01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 83200
Total Pages Processed (Data) : 17047
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 11851
Total Pages Failing   (Index): 0
Total Pages Processed (Lob)  : 9340
Total Pages Failing   (Lob)  : 0
Total Pages Processed (Other): 20944
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 24018
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2884925 (0.2884925)

DBVERIFY: Release 11.2.0.3.0 - Production on Sat Sep 28 19:08:16 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /home/oracle/dbbackup/lrj.dbf
Block Checking: DBA = 20972547, Block Type = KTB-managed data block
data header at 0x7f2cb15be064
kdbchk: the amount of space used is not equal to block size
        used=33 fsc=11 avsp=8055 dtl=8088
Page 1027 failed with check code 6110
Page 52160 is marked corrupt
Corrupt block relative dba: 0x0140cbc0 (file 5, block 52160)
Bad header found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0140d5e0
 last change scn: 0x0000.0013cf03 seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xcf030602
 check value in block header: 0xe88e
 computed block checksum: 0x0

Page 52161 is marked corrupt
Corrupt block relative dba: 0x0140cbc1 (file 5, block 52161)
Bad header found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0140d5e1
 last change scn: 0x0000.0013cf03 seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xcf030602
 check value in block header: 0x93e6
 computed block checksum: 0x0

-- 此处省略部分

DBVERIFY - Verification complete

Total Pages Examined         : 117336
Total Pages Processed (Data) : 80078
Total Pages Failing   (Data) : 1
Total Pages Processed (Index): 29927
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1527
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 5516
Total Pages Marked Corrupt   : 288  -- 该文件用BBED多次修改,已有问题
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2867052 (0.2867052)

DBVERIFY: Release 11.2.0.3.0 - Production on Sat Sep 28 19:08:38 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /home/oracle/dbbackup/users01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 10080
Total Pages Processed (Data) : 8798
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 294
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 453
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 535
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2882606 (0.2882606)

DBVERIFY: Release 11.2.0.3.0 - Production on Sat Sep 28 19:08:40 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /home/oracle/dbbackup/system01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 92160
Total Pages Processed (Data) : 60066
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 13459
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 3488
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 15147
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2884946 (0.2884946)

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/429786/viewspace-776434/,如需转载,请注明出处,否则将追究法律责任。

上一篇: LogMiner使用
请登录后发表评论 登录
全部评论

注册时间:2013-11-08

  • 博文量
    51
  • 访问量
    295048