oracle 热备及恢复

 热备：

 在热备时，oracle会控制scn不发生变化，冻结块头。然后拷贝物理文件，之后解冻块头，然scn可以变化。

 

 

热备备份

首先确认是归档模式并且可用 SQL> archive log list Database log mode              Archive Mode Automatic archival             Enabled Archive destination            +DATA/orcl/ Oldest online log sequence     5 Next log sequence to archive   7 Current log sequence           7 SQL> 手动切换日志确认是否切换成功 SQL> alter system switch logfile;   System altered.   SQL> archive log list Database log mode              Archive Mode Automatic archival             Enabled Archive destination            +DATA/orcl/ Oldest online log sequence     6 Next log sequence to archive   8 Current log sequence          8  8SQL>   如果不是归档模式，将数据库一致性关闭shutdown immediate; 启动到mount状态startup mount； 然后开启归档模式 alter database archivelog； 最后确认是否成功 archive log list

 

备份前的查询

SQL> show user USER is "SYS" 当前库名 SQL> select name from v$database;   NAME --------- ORCL 实例名 SQL> select instance_name from v$instance;   INSTANCE_NAME ---------------- orcl 当前所有数据文件及状态   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   SQL> 查看数据文件位置  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   临时文件位置 SQL> select name from v$tempfile;   NAME --------------------------------------------------------------------------------  /u01/app/oracle/oradata/orcl/temp01.dbf SQL> 日志文件位置 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   6 rows selected.   SQL> 控制文件位置 SQL> select name from v$controlfile;   NAME --------------------------------------------------------------------------------   /u01/app/oracle/oradata/orcl/control01.ctl     /u01/app/oracle/oradata/orcl/control02.ctl   SQL> 参数文件可以直接通过创建指定一个目录 SQL> create pfile='/u01/app/....'  from spfile;   密码文件位置 SQL> ho ls $ORACLE_HOME/dbs/orapw$ORACLE_SID /u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl   SQL>

 

冻结块头 进行数据库级别备份，然后创建完整备份脚本

冻结块头 SQL> alter database begin backup;            创建备份存放路径 SQL> ho mkdir /u02/horcl   连接符生成备份数据文件命令   SQL> select 'ho cp '||name||' /u02/horcl' from v$datafile;   'HOCP'||NAME||'/U02/HORCL' -------------------------------------------------------------------------------- ho cp  /u01/app/oracle/oradata/orcl/system01.dbf    /u02/horcl ho cp  /u01/app/oracle/oradata/orcl/undotbs01.dbf   /u02/horcl ho cp  /u01/app/oracle/oradata/orcl/sysaux01.dbf     /u02/horcl ho cp  /u01/app/oracle/oradata/orcl/users01.dbf      /u02/horcl ho cp  /u01/app/oracle/oradata/orcl/example01.dbf   /u02/horcl 拷贝命令直接执行 SQL> ho cp  /u01/app/oracle/oradata/orcl/system01.dbf    /u02/horcl ho cp  /u01/app/oracle/oradata/orcl/undotbs01.dbf   /u02/horcl ho cp  /u01/app/oracle/oradata/orcl/sysaux01.dbf     /u02/horcl ho cp  /u01/app/oracle/oradata/orcl/users01.dbf      /u02/horcl ho cp  /u01/app/oracle/oradata/orcl/example01.dbf   /u02/horcl   解冻块头 SQL> alter database end backup;               备份控制文件 SQL> alter database backup controlfile to '/u02/horcl/control01.ctl'; 备份参数文件 SQL> create pfile='/u02/horcl/initorcl.ora' from spfile;   创建备份脚本 [oracle@yang ~]$ vi /u02/hotbak.sql             spool /tmp/hot.sql select 'ho cp '||name||' /u02/horcl' from v$datafile; spool off ho sed -n '/^ho cp/p' /tmp/hot.sql >/tmp/hotorcl.sql alter database begin backup; start /tmp/hotorcl.sql alter database end backup; ho rm /tmp/hot*.sql alter database backup  controlfile to '/u02/horcl/control01.ctl'; create pfile='/u02/horcl/initorcl.ora' from spfile;

 

如果alter database begin backup; 执行失败可以以表空间形式备份

SQL> select tablespace_name,file_name from dba_data_files;   TABLESPACE_NAME ------------------------------ FILE_NAME -------------------------------------------------------------------------------- USERS /u01/app/oracle/oradata/orcl/users01.dbf SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf   UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf     TABLESPACE_NAME ------------------------------ FILE_NAME -------------------------------------------------------------------------------- SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf   EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf     SQL> 示例： SQL> alter tablespace  system begin backup; SQL> ho cp /u01/app/oracle/oradata/orcl/system01.dbf  /u02/horcl/ SQL> alter tablespace  system end backup;   编写脚本 [oracle@yang ~]$ vi /u02/hotbak2.sql set heading off spool /tmp/hot.sql select 'alter tablespace  '||tablespace_name||' begin backup; '||chr(10)||'ho cp '||file_name||' /u02/horcl'||chr(10)||'alter tablespace '||tablespace_name||' end backup;' from dba_data_files order by tablesoace_name; spool off start /tmp/hot.sql ho rm /tmp/hot.sql alter database backup  controlfile to '/u02/horcl/control01.ctl'; create pfile='/u02/horcl/initorcl.ora' from spfile;   ~

 

最简单的还是RMAN热备

确认默认存放备份目录 SQL> show parameter db_rec   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest                string        /u01/app/oracle/flash_recover_area/ db_recovery_file_dest_size           big integer 2G db_recycle_cache_size                big integer 0 SQL> 登录RMAN 进行全库热备 [oracle@yang ~]$ rman target /   Recovery Manager: Release 10.2.0.1.0 - Production on Mon May 12 02:33:15 2014   Copyright (c) 1982, 2005, Oracle.  All rights reserved.   connected to target database: ORCL (DBID=1374419528)   RMAN> backup as copy database;     使用上面命令会自动备份所有数据文件、spfile与controlfile到/u01/app/oracle/flash_recover_area/$ORACLE_SID/下

 

热备恢复

控制文件全部丢失

只要有一个存在就不需要恢复，直接cp或者修改参数即可

 

SQL>startup           ORA-00205： error in identifying control  file,check alter log for more info   首先确认文件是否丢失 SQL> show parameter control_   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time        integer     7 control_files                        string      /u01/app/oracle/oradata/orcl/                                                  control01.ctl, /u01/app/oracle/                                                  oradata/orcl/control02.ctl,                                                  /u01/app/oracle/oradata/orcl/                                                  Control03.ctl SQL> ho ls /u01/app/oracle/oradata/orcl/control* ls: /u01/app/oracle/oradata/orcl/control*: No such file or directory   还原控制文件 SQL> ho cp /u02/horcl/control01.ctl  /u01/app/oracle/oradata/orcl/control01.ctl  SQL> ho cp /u02/horcl/control01.ctl  /u01/app/oracle/oradata/orcl/control02.ctl SQL> ho cp /u02/horcl/control01.ctl  /u01/app/oracle/oradata/orcl/control03.ctl 将数据库启动到mount状态 SQL> alter database mount; 然后open数据库根据提示操作 SQL> alter database open; ERROR at line 1： ORA-01589：must use RESETLOGS or NORESETLOGS option for database open 一般情况下是需要进行介质恢复 SQL> recover database using backup controlfile;   SQL> Specify log: auto                  ---自动查找需要的归档日志 如果未找到需要指定日志文件路径 SQL> recover database using backup controlfile;   SQL> Specify log: /u01/app/oracle/oradata/orcl/redo01.log        --一次指定一个直到找到为止 /u01/app/oracle/oradata/orcl/redo02.log /u01/app/oracle/oradata/orcl/redo03.log   恢复完成 resetlogs方式打开数据库 SQL> alter database open resetlogs;   完成之后记得重新全备 [oracle@yang ~]$ vi /u02/hotbak.sql ho rm /u02/horcl/*             ---以前备份无效了，可以直接删除     spool /tmp/hot.sql select 'ho cp '||name||' /u02/horcl' from v$datafile; spool off ho sed -n '/^ho cp/p' /tmp/hot.sql >/tmp/hotorcl.sql alter database begin backup; start /tmp/hotorcl.sql alter database end backup; ho rm /tmp/hot*.sql alter database backup  controlfile to '/u02/horcl/control01.ctl'; create pfile='/u02/horcl/initorcl.ora' from spfile;

 

数据文件丢失

分为：

1、可脱机文件 -------可以在线还原与恢复

2、不可脱机文件 ----需启动到mount状态还原恢复

 

1、可脱机文件丢失 SQL> select * from scott.dept; select * from scott.dept;                  * ERROR at line 1: ORA-01116:error in opening database file 4 ORA-01110: data file 4: ‘/u01/app/oracle/oradata/orcl/users01.dbf’     -----(users表空间丢失) ORA-27041: unable to open file   首先查看数据文件的状态 SQL> select file#,status,enabled from v$datafile;        FILE# STATUS  ENABLED ---------- ------- ----------          1 SYSTEM  READ WRITE          2 ONLINE  READ WRITE          3 ONLINE  READ WRITE          4 ONLINE  READ WRITE          5 ONLINE  READ WRITE SQL> 将文件号为4的脱机在查看状态 SQL> alter database datafile 4 offline; SQL> select file#,status,enabled from v$datafile;        FILE# STATUS  ENABLED ---------- ------- ----------          1 SYSTEM   READ WRITE          2 ONLINE   READ WRITE          3 ONLINE   READ WRITE          4 RECOVER  READ WRITE          5 ONLINE   READ WRITE RECOVER :说明此文件时不正常的需要恢复 SQL>   进行数据文件还原，将备份的数据文件拷贝到数据文件原路径 SQL> ho cp /u02/horcl/users01.dbf  /u01/app/oracle/oradata/orcl/users01.dbf 将数据文件联机查看是否需要media recover SQL> alter database datafile 4 online; ORA-01113： file 4 needs media recover ORA-01110: data file 4: ‘/u01/app/oracle/oradata/orcl/users01.dbf’ 恢复数据文件4 SQL> recover datafile 4; 最后将数据文件联机 SQL> alter database datafile 4 online; 确认是否恢复成功   SQL> select * from scott.dept;       DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK         20 RESEARCH       DALLAS         30 SALES          CHICAGO         40 OPERATIONS     BOSTON   SQL> 2、不能脱机的数据文件丢失   删除系统表空间数据文件 SQL> ho rm /u01/app/oracle/oradata/orcl/system01.dbf     SQL> select tablespace_name,file_name from dba_data_files; ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01116: error in opening database file 1 ORA-01110: data file 1: ‘/u01/app/oracle/oradata/orcl/system01.dbf’ ORA-27041: unable to open file 系统表空间是不能脱机的 SQL> alter database datafile 1 offline; alter database datafile 1 offline * ERROR at line 1: ORA-01541: system tablespace cannot be brought offline; shut down if necessary   关闭数据库一致性关闭数据库是无效的 可以 SQL>shutdown abort SQL>startup  mount 或者 SQL> startup force mount 将备份的数据文件拷贝到原数据文件路径 SQL> ho cp /u02/horcl/system01.dbf   /u01/app/oracle/oradata/orcl/system01.dbf 恢复数据文件1 SQL> recover datafile 1;   启动数据库到open阶段 SQL>alter database open; 验证 SQL> select tablespace_name,file_name from dba_data_files;   TABLESPACE_NAME ------------------------------ FILE_NAME -------------------------------------------------------------------------------- USERS /u01/app/oracle/oradata/orcl/users01.dbf SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf   UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf     TABLESPACE_NAME ------------------------------ FILE_NAME -------------------------------------------------------------------------------- SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf   EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29532781/viewspace-1174683/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29532781/viewspace-1174683/