ORACLE之rman备份恢复及故障处理

最新推荐文章于 2024-09-30 12:21:59 发布

ok060

最新推荐文章于 2024-09-30 12:21:59 发布

阅读量1.5k

点赞数 24

文章标签： oracle 数据库备份

本文链接：https://blog.csdn.net/ok060/article/details/135478738

版权

一、参数文件恢复
1.1 首先查看归档模式是否打开

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11204/db/dbs/arch
Oldest online log sequence     4
Current log sequence           6

1.2 关闭数据库

SQL> shutdown immediate
Database closed
Database dismounted.
ORACLE instance shut down.

1.3 将数据库启动到mount状态

SQL> startup mount
ORACLE instance started.
Total System Global Area  776646656 bytes
Fixed Size                  2257272 bytes
Variable Size             507514504 bytes
Database Buffers          264241152 bytes
Redo Buffers                2633728 bytes
Database mounted.

1.4 开启归档

SQL> alter database archivelog;
Database altered.

1.5 设置归档路径

SQL> alter system set log_archive_dest_1='location=/home/oracle/arch';
System altered.

1.6 查看归档信息

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/arch
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7

1.7 打开数据库

SQL> alter database open;
Database altered.

1.8 打开控制文件自动备份设置
（备份任意文件都会自动备份控制文件。）

[oracle@ggs1 ~]$ rman target /
connected to target database: ORCL (DBID=1442733234)
RMAN> 
RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11204/db/dbs/snapcf_orcl.f'; # default

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

1.9 备份全库

RMAN> backup database format '/home/oracle/%d_%T_%s_%p.bak';
Recovery Manager complete.

1.10 备份spfile

SQL> create pfile from spfile;

若spfile文件损坏并且无pfile备份
则利用控制文件自动备份生成的参数文件备份恢复。
执行restore spfile 命令，根据备份类型区别使用 from autobackup或from ‘备份集路径’子句。这里使用自动备份。

RMAN> set dbid 1442733234
RMAN> restore spfile from autobackup;
RMAN> exit

1.11 然后重启数据库即可，完成参数文件恢复。
注意：此命令要求先指定数据库的DBID，DBID可以从控制文件自动备份文件名中获得，控制文件自动备份默认保存在 $ORACLE_HOME/dbs 目录。
当控制文件自动备份没有在默认位置
RMAN> CONFIGURECONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/home/oracle/%F’;
那么，数据库在nomount状态rman只能读取默认配置的，不能读取到更改后位置的
当恢复的时候通过如下

Rman>set dbid 1442733234;
Rman>run
{
 SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICETYPE disk to '/home/oracle/%F';
Restore controlfilefrom autobackup;
}

如果在实例运行中发现参数文件损坏，如下:

RMAN>restore spfile to '/tmp/spfileorcl.ora' from autobackup;

$cp  /tmp/spfileorcl.ora $ORACLE_HOME/dbs

二、控制文件损坏
2.1 首先破坏环境（删除控制文件），模拟故障

[oracle@ggs1 orcl]$ ls
control01.ctl  example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
control02.ctl  redo01.log     redo03.log  system01.dbf  undotbs01.dbf
[oracle@ggs1 orcl]$ rm -rf control0*
[oracle@ggs1 orcl]$ ls
example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
redo01.log     redo03.log  system01.dbf  undotbs01.dbf

2、重启数据时，就会报如下错误

SQL> startup force

SQL> startup force
ORACLE instance started.

Total System Global Area  776646656 bytes
Fixed Size    2257272 bytes
Variable Size  507514504 bytes
Database Buffers  264241152 bytes
Redo Buffers    2633728 bytes
ORA-00205: error in identifying control file, check alert log for more info

2.2 根据报错信息发现是控制文件验证出错
进一步查看alert 日志

$ pwd
/u01/app/oracle/diag/rdbms/orcl/orcl/trace

$ tail -20 alert_orcl.log

三、系统表空间数据文件损坏

SQL> startup force

如果数据库在运行过程中发现系统表空间数据文件损坏，要先关闭
实例（shutdown abort），再启动到mount状态下恢复。
在恢复之前确保数据库处于mount状态

RMAN> run{
restore datafile 1;
recover datafile 1;
alter database open;
}

四、Undo表空间数据文件损坏
UNDO表空间数据文件和system表空间数据文件都是数据库的关键数据文件，如果损坏会导致sql执行失败，用户无法登录，甚至实例崩溃等。同样恢复UNDO表空间数据文件也必须在数据库mount状态下进行。
数据库在运行过程中发现UNDO表空间数据文件损坏，此时是正常关闭数据库会报错（SP2-0717: illegal SHUTDOWN option），需要强制关闭。

SQL> shutdown abort
SQL> startup mount
RMAN> run{
 restore datafile '/oradata/orcl/undotbs01.dbf';
recover database;
alter database open;
}

五、应用数据文件损坏(users表空间)
5.1 在启动实例时发现应用数据文件丢失或数据文件头损坏，启动实例会提示如下错误，并只能启动到mount状态。
应用数据文件损坏，在启动数据库时会报如下错误：

SQL> startup
ORACLE instance started.

Total System Global Area  776646656 bytes
Fixed Size    2257272 bytes
Variable Size  507514504 bytes
Database Buffers  264241152 bytes
Redo Buffers    2633728 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/oradata/orcl/users01.dbf'
SQL>

这种情况的恢复方法与关键数据文件恢复方法相同。启动实例到mount状态，在RMAN中执行已下命令：即可完成恢复。

run{
restore datafile 4;
recover database;
alter database open;
}

5.2 如果在数据库运行过程中发现应用数据文件损坏，此时可以在线进行恢复，不需要关闭数据库。步骤如下：
首先查看数据文件信息

SQL> select file#,name,status from v$datafile;
     FILE# NAME       STATUS
---------- -------------------------------------------------- -------
 1 /oradata/orcl/system01.dbf      SYSTEM
 2 /oradata/orcl/sysaux01.dbf      ONLINE
 3 /oradata/orcl/undotbs01.dbf      ONLINE
 4 /oradata/orcl/users01.dbf      ONLINE
 5 /oradata/orcl/example01.dbf      ONLINE

然后查看哪些用户使用表空间users表空间

SQL> select username,default_tablespace from dba_users where username='HR';
USERNAME       DEFAULT_TABLESPACE
--------------------    ------------------------------
HR                       USERS

此时模拟故障，删除user01.dbf,连接到hr用户，因为hr用户使用users表空间，在操作时会报错如下：

SQL>conn hr/hr
Connected.
SQL> create table t1 as select * from employees;
create table t1 as select * from employees
                                 *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

a、offline 损坏的数据文件

SQL> alter database datafile '/oradata/orcl/users01.dbf' offline;
Database altered.
SQL>

此时查询v$datafile视图status，该数据文件状态会变成RECOVER

SQL> select file#,name,status from v$datafile;

     FILE# NAME       STATUS
---------- -------------------------------------------------- -------
 1 /oradata/orcl/system01.dbf      SYSTEM
 2 /oradata/orcl/sysaux01.dbf      ONLINE
 3 /oradata/orcl/undotbs01.dbf      ONLINE
 4 /oradata/orcl/users01.dbf      RECOVER
 5 /oradata/orcl/example01.dbf      ONLINE
SQL>

b、在RMAN中，还原并恢复损坏的数据文件

RMAN> run{
restore datafile 4;
recover datafile 4;
}

c、查看数据文件状态，此时为OFFLINE

SQL>  select file#,name,status from v$datafile;
     FILE# NAME       STATUS
---------- -------------------------------------------------- -------
 1 /oradata/orcl/system01.dbf      SYSTEM
 2 /oradata/orcl/sysaux01.dbf      ONLINE
 3 /oradata/orcl/undotbs01.dbf      ONLINE
 4 /oradata/orcl/users01.dbf      OFFLINE
 5 /oradata/orcl/example01.dbf      ONLINE

d、online 数据文件并查看数据文件状态

SQL> select file#,name,status from v$datafile;

     FILE# NAME       STATUS
---------- -------------------------------------------------- -------
 1 /oradata/orcl/system01.dbf      SYSTEM
 2 /oradata/orcl/sysaux01.dbf      ONLINE
 3 /oradata/orcl/undotbs01.dbf      ONLINE
 4 /oradata/orcl/users01.dbf      ONLINE
 5 /oradata/orcl/example01.dbf      ONLINE

e、连接hr用户验证，此时创建表不再报错

SQL> conn hr/hr

SQL> create table t1 as select * from employees;

六、临时文件损坏
临时数据文件的损坏或丢失会造成需要使用临时表空间的命令执行失败，但不会造成实例崩溃。由于临时表空间存放的是临时数据，rman不会对其进行备份，一旦损坏采用的恢复方法是重建或替换。
如果在数据库运行过程中，发现临时数据文件损坏或丢失，可以采用替换的方法恢复，不需要重启数据库：

6.1 查看临时数据文件

select tablespace_name,file_id,file_name,bytes/1024/1024 Mbytes from dba_temp_files;

或者

SQL> select file#,name,bytes/1024/1024 Mb from v$tempfile;

     FILE# NAME       MB
---------- -------------------------------------------------- ----------
 1 /oradata/orcl/temp01.dbf      29

6.2、添加新的临时文件

SQL> alter tablespace temp add tempfile '/oradata/orcl/temp02.dbf' size 30M;

SQL> select file#,name,bytes/1024/1024 Mb from v$tempfile;
     FILE# NAME       MB
---------- -------------------------------------------------- ----------
 1 /oradata/orcl/temp01.dbf      29
 2 /oradata/orcl/temp02.dbf      30

6.3 删除旧的临时文件

SQL> alter tablespace temp drop tempfile '/oradata/orcl/temp01.dbf';

七、全库备份脚本

#!/bin/bash
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11204/db_1
PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin
ORACLE_SID=hankey
NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export ORACLE_BASE ORACLE_HOME PATH ORACLE_SID  NLS_DATE_FORMAT 

echo '----------------------------------------'>>/bak/rman_hankey_full.log
echo 'Start FULL Backup at '`date +%Y-%m-%d:%H:%M:%S` >>/bak/rman_hankey_full.log
echo '----------------------------------------'>>/bak/rman_hankey_full.log

rman target / nocatalog log /bak/rman_hankey_full.log append<<EOF
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
backup  database format '/bak/full_%d_%T_%s_%p.bak';
SQL 'alter system archive log current';
SQL 'alter system archive log current';
backup archivelog  all delete input  format '/bak/arch_%d_%T_%s_%p.bak'; 
backup current controlfile format '/bak/ctl_%d_%T_%s_%p.bak';
backup spfile format '/bak/%d_%U.spfile.bak';
}
EOF

echo '----------------------------------------'>>/bak/rman_hankey_full.log
echo 'End Full Database Backup at '`date +%Y-%m-%d:%H:%M:%S` >>/bak/rman_hankey_full.log
echo '----------------------------------------'>>/bak/rman_hankey_full.log

rman target / nocatalog log /bak/rman_delete.log append<<EOF
allocate channel  for maintenance type disk;
crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete;
delete noprompt expired backup;
delete noprompt expired archivelog all;
EOF
exit