Oracle 冷备份详解【实战案例】

hongmeigui22027

　　Oracle 冷备份详解
　　--准备工作
　　select * from v$database;
　　select file_name from dba_data_files;

　　create tablespace inv datafile '/oradata/ocm/inv01.dbf'>
　　create user inv>　　grant dba,resource to inv;
　　alter user scott account unlock;
　　SQL> conn scott/tiger
　　select * from tab;
　　select 'create table '||tname||' as select * from scott.'||tname||';' from tab;
　　create table BONUS as select * from scott.BONUS;
　　create table DEPT as select * from scott.DEPT;
　　create table EMP as select * from scott.EMP;
　　create table SALGRADE as select * from scott.SALGRADE;
　　create table te_objects as select * from dba_objects;
　　--oracle冷备份
　　select instance_name,version,status,archiver,database_status from v$instance;
　　select dbid,name,log_mode from v$database;
　　--需要备份文件包括：
　　/*
　　数据文件，联机重做日志文件，控制文件，临时文件，参数文件，密码文件，监听文件，tnsnames文件，sqlnet文件
　　*/
　　--执行语句准备
　　select 'cp '|| name ||' /ggs/backup/cold_back' from v$datafile
　　union all
　　select 'cp '|| name ||' /ggs/backup/cold_back' from v$tempfile
　　union all
　　select 'cp '|| member ||' /ggs/backup/cold_back' from v$logfile
　　union all
　　select 'cp '|| name ||' /ggs/backup/cold_back' from v$controlfile;
　　--注：使用以上SQL生成的Linux命令进行执行，如下：
　　cp /oradata/ocm/system01.dbf /ggs/backup/cold_back
　　cp /oradata/ocm/sysaux01.dbf /ggs/backup/cold_back
　　cp /oradata/ocm/undotbs01.dbf /ggs/backup/cold_back
　　cp /oradata/ocm/users01.dbf /ggs/backup/cold_back
　　cp /oradata/ocm/ogg.dbf /ggs/backup/cold_back
　　cp /oradata/ocm/inv01.dbf /ggs/backup/cold_back
　　cp /oradata/ocm/temp01.dbf /ggs/backup/cold_back
　　cp /oradata/ocm/redo04a.rdo /ggs/backup/cold_back
　　cp /oradata/ocm/redo04b.rdo /ggs/backup/cold_back
　　cp /oradata/ocm/redo03a.rdo /ggs/backup/cold_back
　　cp /oradata/ocm/redo03b.rdo /ggs/backup/cold_back
　　cp /oradata/ocm/redo02a.rdo /ggs/backup/cold_back
　　cp /oradata/ocm/redo02b.rdo /ggs/backup/cold_back
　　cp /oradata/ocm/redo01a.rdo /ggs/backup/cold_back
　　cp /oradata/ocm/redo01b.rdo /ggs/backup/cold_back
　　cp /oradata/ocm/control01.ctl /ggs/backup/cold_back
　　cp /oradata/ocm/control02.ctl /ggs/backup/cold_back
　　cp /oradata/ocm/control03.ctl /ggs/backup/cold_back
　　create pfile='/ggs/backup/cold_back/dbs/initocm.ora' from spfile;
　　cp $ORACLE_HOME/dbs/orapwocm  /ggs/backup/cold_back/dbs/
　　cp $ORACLE_HOME/network/admin/  /ggs/backup/cold_back/admin/
　　--关闭数据库
　　alter system archive log all;
　　shutdown immediate;
　　--执行代码
　　cp /oradata/ocm/system01.dbf /ggs/backup/cold_back
　　cp /oradata/ocm/sysaux01.dbf /ggs/backup/cold_back
　　cp /oradata/ocm/undotbs01.dbf /ggs/backup/cold_back
　　cp /oradata/ocm/users01.dbf /ggs/backup/cold_back
　　cp /oradata/ocm/ogg.dbf /ggs/backup/cold_back
　　cp /oradata/ocm/inv01.dbf /ggs/backup/cold_back
　　cp /oradata/ocm/temp01.dbf /ggs/backup/cold_back
　　cp /oradata/ocm/redo04a.rdo /ggs/backup/cold_back
　　cp /oradata/ocm/redo04b.rdo /ggs/backup/cold_back
　　cp /oradata/ocm/redo03a.rdo /ggs/backup/cold_back
　　cp /oradata/ocm/redo03b.rdo /ggs/backup/cold_back
　　cp /oradata/ocm/redo02a.rdo /ggs/backup/cold_back
　　cp /oradata/ocm/redo02b.rdo /ggs/backup/cold_back
　　cp /oradata/ocm/redo01a.rdo /ggs/backup/cold_back
　　cp /oradata/ocm/redo01b.rdo /ggs/backup/cold_back
　　cp /oradata/ocm/control01.ctl /ggs/backup/cold_back
　　cp /oradata/ocm/control02.ctl /ggs/backup/cold_back
　　cp /oradata/ocm/control03.ctl /ggs/backup/cold_back
　　cp $ORACLE_HOME/dbs/orapwocm  /ggs/backup/cold_back/dbs/
　　cp -r $ORACLE_HOME/network/admin/  /ggs/backup/cold_back/
　　--启动数据库
　　sqlplus / as sysdba
　　startup
　　【冷备份的优点和缺点】
　　01、优点冷备模式下概念易于理解，即将需要备份的文件复制到安全的位置操作比较简单，不需要太多的干预容易恢复到某个时间点上(只需将文件再拷贝回去)能与归档方法相结合，作数据库“最新状态”的恢复。
　　02、缺点 备份时，数据库必须处于一致性关闭状态 只能提供到某一时间点的恢复 备份时速度比较慢，尤其是数据量大性能影响比较大 不能实现基于表和用户级别的数据恢复。