備份與恢複概述

備份的概念和類型

根據備份方式的不同

根據資料庫備份時是否關閉資料庫

根據資料庫備份的規模不同

實體備份與恢複

冷備份

1、拷貝檔案到新目錄

可寫成一個sql腳本（select_file.sql）

select'cp '||name||'/opt/oracle/oradata/orcl/'fromv$datafile

unionall

select'cp '||name||' /opt/oracle/oradata/orcl/'fromv$controlfile

unionall

select'cp '||member||'/opt/oracle/oradata/orcl/'fromv$logfile

unionall

select'cp '||name||' /opt/oracle/oradata/orcl/'fromv$tempfile;

2、shutdown 資料庫（幹淨關閉）

Shutdown immediate

3、拷貝資料檔案到新目錄

可建立一個shell腳本(cp_file.sh)

cp /home/oracle/orcl_backup/system01.dbf /opt/oracle/oradata/orcl2/

cp /home/oracle/orcl_backup/rugao01.dbf /opt/oracle/oradata/orcl2/

cp /home/oracle/orcl_backup/sysaux01.dbf /opt/oracle/oradata/orcl2/

cp /home/oracle/orcl_backup/users01.dbf /opt/oracle/oradata/orcl2/

cp /home/oracle/orcl_backup/test01.dbf /opt/oracle/oradata/orcl2/

cp /home/oracle/orcl_backup/test0101.dbf /opt/oracle/oradata/orcl2/

cp /home/oracle/orcl_backup/undotbs02.dbf /opt/oracle/oradata/orcl2/

cp /home/oracle/orcl_backup/test01A.dbf  /opt/oracle/oradata/orcl2/

cp /home/oracle/orcl_backup/control01.ctl /opt/oracle/oradata/orcl2/

cp /home/oracle/orcl_backup/redo04a.log /opt/oracle/oradata/orcl2/

cp /home/oracle/orcl_backup/redo04b.log  /opt/oracle/oradata/orcl2/

cp /home/oracle/orcl_backup/redo04c.log /opt/oracle/oradata/orcl2/

cp /home/oracle/orcl_backup/redo01a.log /opt/oracle/oradata/orcl2/

cp /home/oracle/orcl_backup/redo01b.log /opt/oracle/oradata/orcl2/

4、修改參數檔案

Control_files改到新目錄

修改pfile

5、啟動資料庫到mount狀态

Startup mount

6、修改控制檔案中資料檔案和日志檔案的位置

資料檔案重定向：

alterdatabase rename file '/opt/oracle/oradata/lcro.bak/system01.dbfto/home/oracle/oradata/orcl.bak/system01.dbf';

alter database rename file '/opt/oracle/oradata/lcro.bak/haoxiaoyu001.dbf'to '/home/oracle/oradata/orcl.bak/haoxiaoyu001.dbf';

alter database rename file '/opt/oracle/oradata/lcro.bak/sysaux01.dbf'to '/home/oracle/oradata/orcl.bak/sysaux01.dbf';

alter database rename file '/opt/oracle/oradata/lcro.bak/users01.dbf' to'/home/oracle/oradata/orcl.bak/users01.dbf';

alter database rename file '/opt/oracle/oradata/lcro.bak/orcltbs1.dbf'to '/home/oracle/oradata/orcl.bak/orcltbs1.dbf';

alter database rename file '/opt/oracle/oradata/lcro.bak/data01.dbf' to'/home/oracle/oradata/orcl.bak/data01.dbf';

重做日志檔案重定向：

alter database rename file '/opt/oracle/oradata/lcro.bak/redo01.log' to/home/oracle/oradata/orcl.bak/redo01.log;

alter database rename file '/opt/oracle/oradata/lcro.bak/redo001.log' to'/home/oracle/oradata/orcl.bak/redo001.log';

alter database rename file '/opt/oracle/oradata/lcro.bak/redo04.rdo' to'/home/oracle/oradata/orcl.bak/redo04.rdo';

alter database rename file '/opt/oracle/oradata/lcro.bak/hxy02.log' to'/home/oracle/oradata/orcl.bak/hxy02.log';

alter database rename file '/opt/oracle/oradata/lcro.bak/hxy03.log' to'/home/oracle/oradata/orcl.bak/hxy03.log';

alter database rename file '/opt/oracle/oradata/lcro.bak/log05.log' to'/home/oracle/oradata/orcl.bak/log05.log';

alter database rename file '/opt/oracle/oradata/lcro.bak/log04.log' to'/home/oracle/oradata/orcl.bak/log04.log';

alter database rename file '/opt/oracle/oradata/lcro.bak/hxy.log' to'/home/oracle/oradata/orcl.bak/hxy.log';

alter database rename file '/opt/oracle/oradata/lcro.bak/redo02.log' to'/home/oracle/oradata/orcl.bak/redo02.log';

alter database rename file '/opt/oracle/oradata/lcro.bak/redo002.log' to'/home/oracle/oradata/orcl.bak/redo002.log';

熱備份

邏輯備份與恢複

資料泵技術

建立directory目錄

SQL>create or replace directory dumpdir as '/opt/oracle/product/10.2.0/backup';

Directorycreated

給使用者授予讀寫權限

SQL>grant read,write on directory dumpdir to scott;

Grantsucceeded.

導出

[[email protected]~]$ expdp scott/tiger directory=dumpdir dumpfile=scott.dmp logfile=scott.logschemas=sccott         

Export:Release 10.2.0.1.0 - Production on Tuesday, 15 January, 2013 21:50:17

Copyright(c) 2003, 2005, Oracle. All rightsreserved.

Connectedto: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

Withthe Partitioning, OLAP and Data Mining options

ORA-39002: invalidoperation

ORA-39070: Unable to openthe log file.

ORA-29283: invalid fileoperation

ORA-06512: at"SYS.UTL_FILE", line 475

ORA-29283: invalid fileoperation

告警日志檔案内容：

Tue Jan 15 21:52:23 2013

The value (30) of MAXTRANS parameterignored.

kupprdp: master process DM00 started withpid=25, OS id=10901

        to execute - SYS.KUPM$MCP.MAIN('SYS_EXPORT_SCHEMA_01', 'SCOTT','KUPC$C_1_20130115215223', 'KUPC$S_1_20130115215223', 0);

解決方法與原因：

資料庫中雖然建立了目錄，但是檢查作業系統中實際上沒有這個目錄，是以出現找不到

隻要先在作業系統中建立好目錄然後再在sql下建立目錄并授權，再次執行問題解決！！！

[[email protected] ~]$ mkdir backup

SQL> create or replace directory dumpdiras '/home/oracle/backup';

Directory created.

SQL> grant write,read on directorydumpdir to scott;

Grant succeeded.

再次導出：

[[email protected] ~]$ expdp scott/tigerdirectory=dumpdir logfile=scott0116.log dumpfile=scott0116.dmp schemas=scott;

Export: Release 10.2.0.1.0 - Production onTuesday, 15 January, 2013 22:11:56

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10gEnterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Miningoptions

Starting"SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=dumpdir logfile=scott0116.logdumpfile=scott0116.dmp schemas=scott

Estimate in progress using BLOCKS method...

Processing object typeSCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 192KB

Processing object typeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object typeSCHEMA_EXPORT/TABLE/TABLE

Processing object typeSCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object typeSCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object typeSCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object typeSCHEMA_EXPORT/TABLE/COMMENT

Processing object typeSCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object typeSCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported"SCOTT"."DEPT"                              5.656 KB       4 rows

. . exported"SCOTT"."EMP"                               7.820 KB      14 rows

. . exported"SCOTT"."SALGRADE"                          5.585 KB       5 rows

. . exported"SCOTT"."BONUS"                                 0 KB       0 rows

Master table "SCOTT"."SYS_EXPORT_SCHEMA_01"successfully loaded/unloaded

******************************************************************************

Dump file set forSCOTT.SYS_EXPORT_SCHEMA_01 is:

 /home/oracle/backup/scott0116.dmp

Job"SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at22:12:34

成功！

rman備份與恢複