DBA_Oracle冷备份案例脚本本法(案例)

  1. 云栖社区>
  2. 博客>
  3. 正文

DBA_Oracle冷备份案例脚本本法(案例)

东方瀚海鲍 2014-09-17 09:56:00 浏览481
展开阅读全文

2014-08-10 Created By BaoXinjian

一、摘要


1. 冷备份

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

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

2. 步骤   

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

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

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

3. 总结 - 优点

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

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

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

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

4. 总结 - 缺点

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

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

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

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

 

二、冷备份


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

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

 

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

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

 

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

 

2. 查看数据文件及状态信息

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

 

3. 查看数据文件

SQL> select name from v$datafile;



4. 查看临时文件

SQL> select name from v$tempfile;

 

5. 查看日志文件

SQL> select member from v$logfile;

 

6. 查看控制文件

SQL> select name from v$controlfile;

 

7. 创建备份目录

SQL> ho mkdir /opt/oracle/coolbak

  

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

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
/

 

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

     [gavinsit.com]> vi /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 = '/opt/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/spfilegavinsit.ora' from spfile;
    ho cp /opt/oracle/product/11.2.0/gavinsit/dbs/orapwgavinsit &dir

spool off

shutdown immediate
start &script
ho rm &script
startup

 

8. 执行过程及数据库启动略

     SQL> @/tmp/tmpbak.sql;

SQL> @/tmp/tmpbak.sql
ho cp /opt/oracle/oradata/gavinsit/control01.ctl /opt/oracle/coolbak
ho cp /opt/oracle/flash_recovery_area/gavinsit/control02.ctl /opt/oracle/coolbak
ho cp /opt/oracle/oradata/gavinsit/system01.dbf /opt/oracle/coolbak
ho cp /opt/oracle/oradata/gavinsit/sysaux01.dbf /opt/oracle/coolbak
ho cp /opt/oracle/oradata/gavinsit/undotbs01.dbf /opt/oracle/coolbak
ho cp /opt/oracle/oradata/gavinsit/users01.dbf /opt/oracle/coolbak
ho cp /opt/oracle/oradata/gavinsit/example01.dbf /opt/oracle/coolbak
ho cp /opt/oracle/oradata/gavinsit/bxj_emp_data01.dbf /opt/oracle/coolbak
ho cp /opt/oracle/oradata/gavinsit/bxj_emp_data02.dbf /opt/oracle/coolbak
ho cp /opt/oracle/oradata/gavinsit/redo03.log /opt/oracle/coolbak
ho cp /opt/oracle/oradata/gavinsit/redo02.log /opt/oracle/coolbak
ho cp /opt/oracle/oradata/gavinsit/redo01.log /opt/oracle/coolbak
ho cp /opt/oracle/oradata/gavinsit/temp01.dbf /opt/oracle/coolbak

Database closed.
Database dismounted.
ORACLE instance shut down.

 

9. 启动后查看备份的文件

ls /opt/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

 

Thanks and Regards

ERP技术讨论群: 288307890
技术交流,技术讨论,欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建

网友评论

登录后评论
0/500
评论
东方瀚海鲍
+ 关注