oracle备份方式汇总

来源：互联网发布：美科8652电子琴编程编辑：程序博客网时间：2024/05/29 23:21

----陆续更新

一、冷备份方式下的数据恢复

冷备份脚本：

set pagesize 0 linsize 32767 feedback off verify off trimspool on termout off trimout on serveroutput ondefine fil=/home/oracle/cdbk.sqldefine log=/home/oracle/cdbk.logspool &filprompt connect sys/oracle@orcl as sysdba;prompt shutdown immediate;select 'host cp '||file_name||' /rmanbak/'||substr(file_name,instr(file_name,'/',-1,1)) from dba_data_files;prompt startup;spool offspool &log@&fil;spool off

1、非system数据文件和undo tablespace文件损坏恢复

1）、删除非system数据文件模拟数据文件损坏

[oracle@bbk0110g orcl]$ rm yl01.dbf -f

--当前数据文件及状态

SQL> select name,status from v$datafile;

NAME STATUS
---------------------------------------- -------
/u01/oradata/orcl/system01.dbf SYSTEM
/u01/oradata/orcl/undotbs01.dbf ONLINE
/u01/oradata/orcl/sysaux01.dbf ONLINE
/u01/oradata/orcl/users01.dbf ONLINE
/u01/oradata/orcl/example01.dbf ONLINE
/u01/oradata/orcl/undotbs02.dbf ONLINE
/u01/oradata/orcl/baktest01.dbf ONLINE
/u01/oradata/orcl/yl01.dbf ONLINE

8 rows selected.

--执行offline和online让数据库检查到数据文件已经损坏，需恢复

SQL> alter tablespace yl offline;

Tablespace altered.

SQL> select name,status from v$datafile;

8 rows selected.

SQL> alter tablespace yl online;
alter tablespace yl online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/u01/oradata/orcl/yl01.dbf'

--恢复数据文件语句

SQL> recover datafile 8;

2、system数据文件损坏恢复

1）、删除system数据文件模拟数据文件损坏

[oracle@bbk0110g orcl]$ rm system01.dbf -f

2）、关闭数据库并启动到mount状态，system数据文件损坏需要在mount状态下恢复

--必须要强制关闭
SQL> shutdown immediate;
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/orcl/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.

--启动到mount状态
SQL> startup mount;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 121636432 bytes
Database Buffers 159383552 bytes
Redo Buffers 2973696 bytes
Database mounted.

3）恢复数据库
SQL> recover database;

3、控制文件损坏不完全恢复

1）、使用rm命令直接删除控制文件后

SQL> conn /as sysdba;
Connected.
SQL> select * from v$datafile;
select * from v$datafile
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

2）、创建控制文件

--手工创建控制文件

SQL> alter database backup controlfile to trace;

Database altered.

--编辑后保存为ctl.sql

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 '/u01/oradata/orcl/redo01.log' SIZE 50M,
GROUP 2 '/u01/oradata/orcl/redo02.log' SIZE 50M,
GROUP 3 '/u01/oradata/orcl/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/oradata/orcl/system01.dbf',
'/u01/oradata/orcl/undotbs01.dbf',
'/u01/oradata/orcl/sysaux01.dbf',
'/u01/oradata/orcl/users01.dbf',
'/u01/oradata/orcl/example01.dbf',
'/u01/oradata/orcl/undotbs02.dbf',
'/u01/oradata/orcl/baktest01.dbf',
'/u01/oradata/orcl/yl01.dbf'
CHARACTER SET WE8ISO8859P1
;

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 104859216 bytes
Database Buffers 176160768 bytes
Redo Buffers 2973696 bytes
SQL> @/u01/oracle/ctl.sql

3）、恢复数据库

在mount状态，利用联机日志恢复数据库

SQL> recover database using backup controlfile ;

4）、打开数据库

SQL> alter database open resetlogs;

Database altered.
此为一种不完全恢复

4、临时文件损坏

1）、模拟临时文件损坏

rm /u01/oradata/orcl/temp01.dbf

SQL> explain plan for select * from big_table order by owner;
explain plan for select * from big_table order by owner
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/u01/oradata/orcl/temp01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

2）创建一个新的临时文件用于恢复

SQL> conn /as sysdba;
Connected.
SQL> alter tablespace temp drop tempfile 1;

Tablespace altered.

alter tablespace temp add tempfile '/u01/oradata/orcl/temp02.dbf' size 100m autoextend on;

5、联机重做日志文件损坏恢复方法

分为inactive日志损坏的完全恢复

--查看日志的status为inactive的group

SQL> select * from v$log;

GROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUSFIRST_CHANGE# FIRST_TIM

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

11827524288001 YES ACTIVE104866595 28-APR-13

21828524288001 NOCURRENT104866661 28-APR-13

31826524288001 YES INACTIVE104866547 28-APR-13

--查看group 3日志存放路径

select * from v$logfile

GROUP# STATUSTYPEMEMBERIS_

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

3ONLINE/u01/oradata/orcl/redo03.logNO

2ONLINE/u01/oradata/orcl/redo02.logNO

1ONLINE/u01/oradata/orcl/redo01.logNO

--删除3号在线重做日志

[root@bbk0110g ~]# rm -f /u01/oradata/orcl/redo03.log

--重新启动数据库

SQL> startup

ORACLE instance started.

Total System Global Area285212672 bytes

Fixed Size1218992 bytes

Variable Size104859216 bytes

Database Buffers176160768 bytes

Redo Buffers2973696 bytes

Database mounted.

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/u01/oradata/orcl/redo03.log'

只能启动到mount状态

SQL> select instance_name,status from v$instance;

INSTANCE_NAMESTATUS

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

orclMOUNTED

2）、恢复方法

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database open;

Database altered.

active的日志损坏不完全恢复

SQL> select * from v$log;

GROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUSFIRST_CHANGE# FIRST_TIM

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

11827524288001 YES INACTIVE104866595 28-APR-13

21828524288001 YES INACTIVE104866661 28-APR-13

31829524288001 NOCURRENT104867241 28-APR-13

SQL> select * from v$logfile;

GROUP# STATUSTYPEMEMBERIS_

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

3ONLINE/u01/oradata/orcl/redo03.logNO

2ONLINE/u01/oradata/orcl/redo02.logNO

1ONLINE/u01/oradata/orcl/redo01.logNO

--删除当前联机重做日志group3

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area285212672 bytes

Fixed Size1218992 bytes

Variable Size104859216 bytes

Database Buffers176160768 bytes

Redo Buffers2973696 bytes

Database mounted.

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/u01/oradata/orcl/redo03.log'

2）、恢复方法，注意与inactive的区别

SQL> alter database clear unarchived logfile group 3;

alter database clear unarchived logfile group 3

ERROR at line 1:

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/u01/oradata/orcl/redo03.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL> recover database until cancel

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

rman全备脚本：

run {
allocate channel d1 device type disk;
backup as compressed backupset
format='/rmanbak/full_%d_%U'
tag='fullbak'
channel=d1
database plus archivelog delete input;
}