揭祕ORACLE備份之－－冷備份（也叫離線備份）
在本章開始之前，很多文章都是用實驗來展示，相關的理論知識有所欠缺。
因為我覺得理論的東西，首先我不擅長，還是建議想詳細瞭解的同學去查閱官方文件，那是每個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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle OCP(60)：冷備份Oracle
- mysql的冷備份與熱備份MySql
- 熱備份/冷備份/ 冷啟動/熱啟動
- oracle資料庫備份之exp增量備份Oracle資料庫
- oracle自動冷備份指令碼Oracle指令碼
- MySQL的冷備份和熱備份概念理解（轉）MySql
- ORACLE 備份表Oracle
- MySQL冷備份過程MySql
- Oracle 備份恢復之 FlashbackOracle
- oracle 如何不備份已經備份的歸檔Oracle
- ORACLE備份策略(轉)Oracle
- ORACLE備份指令碼Oracle指令碼
- Networker備份oracle racOracle
- RAC備份恢復之Voting備份與恢復
- 備份集和備份片之間的關係
- 揭秘ORACLE備份之----RMAN之五（CATALOG）Oracle
- ORACLE備份策略二(轉)Oracle
- Oracle RMAN備份實戰Oracle
- Oracle OCP(60)：RMAN 備份Oracle
- Oracle OCP(62)：熱備份Oracle
- Oracle 備份恢復篇之RMAN catalogOracle
- oracle邏輯備份之--資料泵Oracle
- 初探MySQL資料備份及備份原理MySql
- Mysql備份與恢復(1)---物理備份MySql
- alias 備份
- 備份dockerDocker
- 備份命令
- Oracle 備份 與 恢復 概述Oracle
- Oracle磁帶備份模擬Oracle
- ORACLE備份&恢復案例(轉)Oracle
- Networker備份oracle單節點Oracle
- Mysql備份與恢復(2)---邏輯備份MySql
- ManagerDB 備份檔案管理與異地備份
- 群暉NAS備份建議及備份方式
- GitLab的自動備份、清理備份與恢復Gitlab
- redis不重啟，切換RDB備份到AOF備份Redis
- 【RMAN】同時建立多個備份（建立多重備份）
- MySQL備份與主備配置MySql
- DBV：冷備份的校驗和恢復