今天看啥
热点:

--======================

-- Oracle 冷备份

--======================


一、冷备份

数据库在关闭状态下完成所有物理系统文件拷贝的过程,也称脱机备份

适合于非归档模式下,数据库处于一致性状态

二、步骤

首先在运行的库中得到数据库运行的所有的物理文件位置,然后在计划内关闭数据库(shutdown)

再执行拷贝物理文家到备份路径或备份设备

备份完成后立即启动数据库让其提供正常的服务


三、冷备脚本的写法

首先应该在相关视图里查出数据库的数据文件,日志文件,控制文件,临时文件所在的位置

注意:不要直接把oradata下的cp就行了,因为生产库里各个文件通常分布在不同的磁盘,不同的地方,所以在去视图里获得真实路径


--查看实例和数据库的相关信息

SQL> select instance_name,version,status,archiver,database_status from v$instance;


INSTANCE_NAME VERSION STATUS ARCHIVE DATABASE_STATUS

---------------- ----------------- ------------ ------- -----------------

orcl 10.2.0.1.0 OPEN STOPPED ACTIVE


SQL> select dbid,name,log_mode from v$database;


DBID NAME LOG_MODE

---------- --------- ------------

1242732291 ORCL NOARCHIVELOG


--查看数据文件及状态信息

SQL> select file_name,tablespace_name,status,online_status from dba_data_files;


FILE_NAME TABLESPACE STATUS ONLINE_

------------------------------------------------------- ---------- --------- -------

/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1 AVAILABLE ONLINE

/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM AVAILABLE SYSTEM

/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX AVAILABLE ONLINE

/u01/app/oracle/oradata/orcl/users01.dbf USERS AVAILABLE ONLINE

/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE AVAILABLE ONLINE

/u01/app/oracle/oradata/orcl/tbs1_1.dbf TBS1 AVAILABLE ONLINE

/u01/app/oracle/oradata/orcl/tbs1_2.dbf TBS1 AVAILABLE ONLINE


--查看数据文件

SQL> select name from v$datafile;


NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/system01.dbf

/u01/app/oracle/oradata/orcl/undotbs01.dbf

/u01/app/oracle/oradata/orcl/sysaux01.dbf

/u01/app/oracle/oradata/orcl/users01.dbf

/u01/app/oracle/oradata/orcl/example01.dbf

/u01/app/oracle/oradata/orcl/tbs1_1.dbf

/u01/app/oracle/oradata/orcl/tbs1_2.dbf


--查看临时文件

SQL> select name from v$tempfile;


NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/temp01.dbf


--查看日志文件

SQL> select member from v$logfile;


MEMBER

------------------------------------------------------------

/u01/app/oracle/oradata/orcl/redo2a.rdo

/u01/app/oracle/oradata/orcl/redo2b.rdo

/u01/app/oracle/oradata/orcl/redo1a.rdo

/u01/app/oracle/oradata/orcl/redo3a.rdo

/u01/app/oracle/oradata/orcl/redo3b.rdo

/u01/app/oracle/oradata/orcl/redo1b.rdo


--查看控制文件

SQL> select name from v$controlfile;


NAME

------------------------------------------------------------

/u01/app/oracle/oradata/orcl/control01.ctl

/u01/app/oracle/oradata/orcl/control02.ctl


--创建备份目录

SQL> ho mkdir /u01/app/oracle/coolbak

--使用连接符生成复制文件命令

SQL> select 'ho cp ' || name || ' /u01/app/oracle/coolbak' from v$controlfile;


'HOCP'||NAME||'/U01/APP/ORACLE/COOLBAK'

----------------------------------------------------------------------------------

ho cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/coolbak

ho cp /u01/app/oracle/oradata/orcl/control02.ctl /u01/app/oracle/coolbak


SQL> save /tmp/tmpbak.sql; --将上面的输入保存为tmpbak.sql

Created file /tmp/tmpbak.sql


SQL> ho vim /tmp/tmpbak.sql --编辑tmpbak.sql,将下面的内容输入到tmpbak.sql


set feedback off

set heading off

set verify off

set trimspool off

set pagesize 0

set linesize 200

define dir = '/u01/app/oracle/coolbak'

define script = '/tmp/coolbak.sql'

spool &script

select 'ho cp ' || name || ' &dir' from v$controlfile

union all

select 'ho cp ' || name || ' &dir' from v$datafile

union all

select 'ho cp ' || member || ' &dir' from v$logfile

union all

select 'ho cp ' || name || ' &dir' from v$tempfile

/

create pfile = '&dir/initorcl.ora' from spfile;

ho cp /u01/app/oracle/10g/dbs/orapworcl &dir

spool off

shutdown immediate

start &script

ho rm &script

startup


--执行tmpbak.sql

SQL> @/tmp/tmpbak.sql;


--执行过程及数据库启动略

--启动后查看备份的文件

SQL> ho ls /u01/app/oracle/coolbak

control01.ctl orapworcl redo2b.rdo system01.dbf users01.dbf

control02.ctl redo1a.rdo redo3a.rdo tbs1_1.dbf

example01.dbf redo1b.rdo redo3b.rdo tbs1_2.dbf

initorcl.ora redo2a.rdo sysaux01.dbf undotbs01.dbf


四、总结

优点

冷备模式下概念易于理解,即将需要备份的文件复制到安全的位置

操作比较简单,不需要太多的干预

容易恢复到某个时间点上(只需将文件再拷贝回去)

能与归档方法相结合,作数据库“最新状态”的恢复。

缺点

备份时,数据库必须处于一致性关闭状态

只能提供到某一时间点的恢复

备份时速度比较慢,尤其是数据量大性能影响比较大

不能实现基于表和用户级别的数据恢复


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html

www.bkjia.comtruehttp://www.bkjia.com/sjkjc/618639.htmlTechArticle--====================== -- Oracle 冷备份 --====================== 一、冷备份 数据库在关闭状态下完成所有物理系统文件拷贝的过程,也称脱机备份 适...

相关文章

相关搜索: Oracle Oracle冷备份

帮客评论

视觉看点