Oracle 冷备份

1、-=- Oracle 冷备份-=一、冷备份数据库在关闭状态下完成所有物理系统文件拷贝的过程,也称脱机备份适合于非归档模式下,数据库处于一致性状态二、步骤首先在运行的库中得到数据库运行的所有的物理文件位置,然后在计划内关闭数据库 (shutdown 再执行拷贝物理文家到备份路径或备份设备备份完成后立即启动数据库让其提供正常的服务三、冷备脚本的写法首先应该在相关视图里查出数据库的数据文件, 日志文件, 控制文件, 临时文件所在的位置 注意:不要直接把 oradata 下的 cp 就行了, 因为生产库里各个文件通常分布在不同的磁盘, 不同的地方,所以在去视图里获得真实路径-查看实例和数据库的相关信息

2、SQL>select instance_name, version , status , archiver , database_status from v$instan ce ;INSTANCE_NAME VERSION STATUS ARCHIVE DATABASE_STATUS- - - - -SQL>select dbid, name , log_mode from v$database;DBID NAME LOG_MODE- - -1242732291 ORCL NOARCHIVELOG-查看数据文件及状态信息SQL>select file_name, tables

3、pace_name, status , online_status from dba_data_files;FILE_NAME TABLESPACESTATUS 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 AVAILABL

4、E 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

5、 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. d

6、bf/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 /orada

7、ta /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. ct

8、l-创建备份目录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 /coolbakho cp /u01/

9、app /oracle /oradata /orcl /control02. ctl /u01/app /oracle /coolbakSQL>save /tmp /tmpbak . sql ; -将上面的输入保存为 tmpbak.sqlCreated file /tmp /tmpbak . sqlSQL> ho vim /tmp /tmpbak . sql -编辑 tmpbak.sql, 将下面的内容输入到 tmpbak.sqlset feedback offset heading offset verify offset trimspool offset pagesize 0s

10、et linesize 200define dir ='/u01/app/oracle/coolbak'define script ='/tmp/coolbak.sql'spool &scriptselect 'ho cp '|name |' &dir'from v$controlfileunion allselect 'ho cp '|name |' &dir'from v$datafileunion allselect 'ho cp '| member |

11、' &dir'from v$logfileunion allselect 'ho cp '|name |' &dir'from v$tempfile/create pfile ='&dir/initorcl.ora'from spfile;ho cp /u01/app /oracle /10g /dbs /orapworcl &dirspool offshutdown immediatestart &scriptho rm &scriptstartup-执行 tmpbak.sqlSQL> /tmp/tmpbak . sql ;-执行过程及数据库启动略-启动后查看备份的文件SQL> ho ls /u01/app /oracle /coolbakcontrol01. ctl orapworcl redo2b. rdo system01. dbf users01. dbf control02. ctl redo1a. rdo redo3a. rdo tbs1_1. dbfexample01. dbf redo1b. rdo redo3b. rdo tbs1_2. dbfinitorcl. ora redo2a. rdo sysaux01. db