揭祕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備份之－－熱備份（也叫聯機備份）Oracle
- 揭祕ORACLE備份之－－邏輯備份（EXP）Oracle
- 揭祕ORACLE備份之－－邏輯備份（EXPDP）Oracle
- oracle備份--離線備份Oracle
- 揭祕ORACLE備份之----RMAN之二（備份方式）Oracle
- Oracle 聯機備份 離線備份 物理備份 恢復Oracle
- oracle的熱備份和冷備份Oracle
- Oracle 冷備份Oracle
- Oracle冷備份和熱備份的處理Oracle
- Oracle 熱備份和冷備份的區別Oracle
- Oracle冷備份(一致備份)指令碼Oracle指令碼
- 揭祕ORACLE備份之----RMAN之五（CATALOG）Oracle
- Oracle冷備份級冷備份的不完全恢復Oracle
- mysql的冷備份與熱備份MySql
- Oracle資料庫的備份方法-冷備份(轉)Oracle資料庫
- 揭祕ORACLE備份之----RMAN之四（塊跟蹤）Oracle
- 【原】Oracle學習系列—資料庫備份—離線備份Oracle資料庫
- Oracle OCP(60)：冷備份Oracle
- Oracle冷備份練習Oracle
- Oracle資料庫冷備份與熱備份操作梳理Oracle資料庫
- 揭祕ORACLE備份之----RMAN之一（引數配置）Oracle
- Oracle資料庫備份與恢復之匯出／匯入（EXP/IMP）、熱備份和冷備份Oracle資料庫
- Oracle資料庫的冷備份及冷備份異地恢復方法Oracle資料庫
- Oracle物理冷備份指令碼Oracle指令碼
- SQL Server 冷備份SQLServer
- MySQL的冷備份和熱備份概念理解（轉）MySql
- oracle資料庫備份之exp增量備份Oracle資料庫
- 備份之控制檔案備份
- oracle自動冷備份指令碼Oracle指令碼
- Oracle冷備份的通常步驟Oracle
- oracle之rman備份Oracle
- MySQL冷備份過程MySql
- 生成冷備份指令碼指令碼
- Oracle裡邏輯備份、物理備份、Rman備份的區別Oracle
- RMAN備份之備份多個備份集到帶庫（三）
- RMAN備份之備份多個備份集到帶庫（二）
- RMAN備份之備份多個備份集到帶庫（一）
- oracle 備份Oracle