备份与恢复概述

备份的概念和类型

根据备份方式的不同

根据数据库备份时是否关闭数据库

根据数据库备份的规模不同

物理备份与恢复

冷备份

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.

 

导出

[oracle@localhost~]$ 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下创建目录并授权，再次执行问题解决！！！

[oracle@localhost ~]$ mkdir backup

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

Directory created.

SQL> grant write,read on directorydumpdir to scott;

Grant succeeded.

 

再次导出：

 

[oracle@localhost ~]$ 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备份与恢复