使用 Rman 备份恢复 Oracle RAC 到单机文件系统

科技 2024-09-04 07:50 中国

作者 | JiekeXu

来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）

如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)

大家好，我是 JiekeXu,江湖人称“强哥”，荣获 Oracle ACE Pro 称号，墨天轮 MVP，墨天轮年度“墨力之星”，拥有 Oracle 11g OCP/OCM 认证,MySQL 5.7/8.0 OCP 认证以及 PCA、PCTA、OBCA、OGCA、KCP 等众多国产数据库认证证书,今天和大家一起来看看使用 Rman 备份恢复 Oracle RAC 到单机文件系统，欢迎点击最上方蓝字“JiekeXu DBA之路”关注我的微信公众号，然后点击右上方三个点“设为星标”置顶，更多干货文章才能第一时间推送，谢谢！

1、建立存放备份目录
2、原库全量备份
3、新库重建密码文件
4、新建参数文件，按照源库修改
5、新建目录
6、修改 .bash_profile
7、修改 /etc/hosts 文件添加主机名
8、恢复控制文件
9、启动数据库至 mount 状态
10、生成修改路径的语句
11、目标库恢复
    rman 进行恢复
    恢复归档
12、恢复后的清理操作
    删除未使用的 redo
    清除多余的 undo
    添加 redo 日志组
13、验证数据库
14、配置监听和 TNS

15、设置删除归档策略

前言

备份和恢复都是数据库管理中非常重要的任务，用于保护数据库免受故障和灾难的影响。在 Oracle 数据库中，备份和恢复可以通过多种方式实现。一种常见的备份方式是使用 Oracle RMAN（Recovery Manager）工具进行备份。RMAN 可以创建全备份、增量备份和归档日志备份等多种备份类型，可以将备份数据存储在磁盘、磁带等多种介质上。在恢复数据时，可以使用 RMAN 工具执行恢复操作。

如果数据库无法启动，可以使用恢复目录和备份集创建一个临时的控制文件，以便进行数据库恢复。RMAN 还支持基于时间点的恢复，可以将数据库恢复到指定的时间点或SCN（System Change Number）。

这里也先插播一条重磅福利，各位看官，走过路过的进来看一看，瞧一瞧，保证不吃亏，免费抽奖送书，至于抽不抽奖，随意就好，主要是帮忙点进来增加一下阅读量，600 以上阅读才能免费抽取 3 本《DBA实战手记》，点击下方红字浏览一下，万分感谢。

DBA圈号主大联合免费抽奖送《DBA实战手记》https://mp.weixin.qq.com/s/gGF7SM5_zDekQTuroCGw-g

下面是以前做备份恢复的一种通用方式，特此记录下来，以备后续参考需要。

1、建立存放备份目录

su - oracle mkdir -p /backup/backup/backup20200521sqlplus / as sysdba

2、原库全量备份

原库切换日志进行备份

alter system archive log current;

原库利用 rman 生成备份

rman target /run {  allocate channel c1 type disk ;  allocate channel c2 type disk ;  allocate channel c3 type disk ;  allocate channel c4 type disk ;  backup  as compressed backupset database format '/backup/backup/backup20200521/%d_%I_%s_%p.bak';  backup  as compressed backupset archivelog all format '/backup/backup/backup20200521/%d_%I_%s_%p.arc';  backup current controlfile  format '/backup/backup/backup20200521/%d_%I_%s_%p.ctl';  release channel c1;  release channel c2;  release channel c3;  release channel c4;}

3、新库重建密码文件

su - oracle cd $ORACLE_HOME/dbsorapwd file=orapwjiekexu1 password=oracle11g

或者拷贝原库 cp $ORACLE_HOME/dbs/orapw* 到目标库 dbs 目录下。

4、新建参数文件，按照源库修改

jiekexu1.__db_cache_size=6174015488jiekexu1.__java_pool_size=16777216jiekexu1.__large_pool_size=33554432jiekexu1.__oracle_base='/app/oracle'#ORACLE_BASE set from environmentjiekexu1.__pga_aggregate_target=2483027968jiekexu1.__sga_target=7449083904jiekexu1.__shared_io_pool_size=0jiekexu1.__shared_pool_size=1174405120jiekexu1.__streams_pool_size=0*.audit_file_dest='/app/oracle/admin/jiekexu/adump'*.audit_trail='NONE'*.compatible='11.2.0.4.0'*.control_file_record_keep_time=39*.control_files='/oradata/jiekexu/controlfile/control01.ctl'*.db_block_size=8192*.db_create_file_dest='/oradata'*.db_domain=''*.db_files=1024*.db_name='JIEKEXU'*.diagnostic_dest='/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=jiekexuXDB)'*.enable_ddl_logging=TRUEjiekexu1.instance_number=1*.log_archive_dest_1='LOCATION=/oradata/arch'*.log_archive_format='%t_%s_%r.dbf'*.open_cursors=500*.pga_aggregate_target=2474639360*.processes=2000*.query_rewrite_enabled='TRUE'*.query_rewrite_integrity='TRUSTED'*.remote_login_passwordfile='exclusive'*.session_cached_cursors=100*.sessions=2205*.sga_max_size=7449083904*.sga_target=7449083904jiekexu1.thread=1*.undo_retention=1440jiekexu1.undo_tablespace='UNDOTBS1'*.local_listener=''

5、新库新建目录

以上参数文件中出现的目录均需要创建

mkdir -p /app/oracle/admin/jiekexu/adumpmkdir -p /oradata/jiekexu/controlfilemkdir -p /oradata/arch

6、修改 .bash_profile

拷贝原环境 .bash_profile 进行修改。

umask 022  ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/   ORACLE_SID=jiekexu1#NLS_LANG="SIMPLIFIED CHINESE_CHINA".UTF8 PATH=$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_HOME/bin    #LANG=zh_CN.UTF-8
export LANG=en_US.UTF8export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"export PATH LANG NLS_LANG ORACLE_BASE ORACLE_HOME ORACLE_SID  #使环境生效alias sys='sqlplus / as sysdba'export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$OGG_HOME:/lib/usr/lib:/usr/local/lib
--source 生效source .bash_profile

7、修改 /etc/hosts 文件添加主机名

127.0.0.1       localhost127.0.0.1       jiekexu192.168.75.32   jiekexu

8、恢复控制文件

rman target /
restore controlfile from '/tmp/sk_backup/jieke/full_controlfile_lqv0rlil_1_1';
jiekxu1:/tmp/sk_backup/jieke$rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sun May 24 14:47:50 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: jiekexu (not mounted)
RMAN> restore controlfile from '/tmp/sk_backup/jieke/full_controlfile_lqv0rlil_1_1';
Starting restore at 2020-05-24 14:49:56using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=1893 device type=DISK
channel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:01output file name=/oradata/jiekexu/controlfile/current01.ctlFinished restore at 2020-05-24 14:49:57

9、启动数据库至 mount 状态

sql'alter database mount';
RMAN> sql'alter database mount';
sql statement: alter database mountreleased channel: ORA_DISK_1
RMAN>

10、生成修改路径的语句

原库执行（生成替换数据文件的语句）：

set pagesize  200 linesize 200select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";'from v$datafile aunion allselect 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";'from v$tempfile aunion allSELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || '''''  to  ''''' ||a.MEMBER || ''''' ";'FROM v$logfile a;

将查询出来的结果做相应替换

set newname for datafile 1 to "+DATA/jiekexu/datafile/system.256.990206091";set newname for datafile 2 to "+DATA/jiekexu/datafile/sysaux.257.990206091";set newname for datafile 3 to "+DATA/jiekexu/datafile/undotbs1.258.990206091";set newname for datafile 4 to "+DATA/jiekexu/datafile/users.259.990206091";set newname for datafile 5 to "+DATA/jiekexu/datafile/undotbs2.264.990206217";set newname for datafile 6 to "+DATA/jiekexu/datafile/jiekeusr.268.990531275";set newname for datafile 7 to "+DATA/jiekexu/datafile/jiekeusr.269.990531399";set newname for datafile 8 to "+DATA/jiekexu/datafile/jiekeusr.270.990531567";set newname for tempfile 1 to "+DATA/jiekexu/tempfile/temp.263.990206169";SQL "ALTER DATABASE RENAME FILE ''+DATA/jiekexu/onlinelog/group_2.262.990206165''  to  ''+DATA/jiekexu/onlinelog/group_2.262.990206165'' ";SQL "ALTER DATABASE RENAME FILE ''+DATA/jiekexu/onlinelog/group_1.261.990206165''  to  ''+DATA/jiekexu/onlinelog/group_1.261.990206165'' ";SQL "ALTER DATABASE RENAME FILE ''+DATA/jiekexu/onlinelog/group_3.265.990206285''  to  ''+DATA/jiekexu/onlinelog/group_3.265.990206285'' ";SQL "ALTER DATABASE RENAME FILE ''+DATA/jiekexu/onlinelog/group_4.266.990206285''  to  ''+DATA/jiekexu/onlinelog/group_4.266.990206285'' ";

11、目标库恢复

备库创建数据文件目录：

mkdir -p /oradata/jiekexu/datafilemkdir -p /oradata/jiekexu/tempfilemkdir -p /oradata/jiekexu/onlinelog

rman 进行恢复

rman target /run{set newname for datafile 1 to "/oradata/jiekexu/datafile/system.256.990206091";set newname for datafile 2 to "/oradata/jiekexu/datafile/sysaux.257.990206091";set newname for datafile 3 to "/oradata/jiekexu/datafile/undotbs1.258.990206091";set newname for datafile 4 to "/oradata/jiekexu/datafile/users.259.990206091";set newname for datafile 5 to "/oradata/jiekexu/datafile/undotbs2.264.990206217";set newname for datafile 6 to "/oradata/jiekexu/datafile/jiekeusr.268.990531275";set newname for datafile 7 to "/oradata/jiekexu/datafile/jiekeusr.269.990531399";set newname for datafile 8 to "/oradata/jiekexu/datafile/jiekeusr.270.990531567";set newname for tempfile 1 to "/oradata/jiekexu/tempfile/temp.263.990206169";SQL "ALTER DATABASE RENAME FILE ''+DATA/jiekexu/onlinelog/group_2.262.990206165''  to  ''/oradata/jiekexu/onlinelog/group_2.262.990206165'' ";SQL "ALTER DATABASE RENAME FILE ''+DATA/jiekexu/onlinelog/group_1.261.990206165''  to  ''/oradata/jiekexu/onlinelog/group_1.261.990206165'' ";SQL "ALTER DATABASE RENAME FILE ''+DATA/jiekexu/onlinelog/group_3.265.990206285''  to  ''/oradata/jiekexu/onlinelog/group_3.265.990206285'' ";SQL "ALTER DATABASE RENAME FILE ''+DATA/jiekexu/onlinelog/group_4.266.990206285''  to  ''/oradata/jiekexu/onlinelog/group_4.266.990206285'' ";restore database;switch datafile all;switch tempfile all;}

恢复归档

查看归档进程号

list backupset of archivelog all;

恢复完数据文件后，直接 list backupset of archivelog all;

找到最新的归档号进行恢复

恢复归档：

run{set until sequence 3219 thread 1;set until sequence 3219 thread 2;recover database;} 
  List of Archived Logs in backup set 6830  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time  ---- ------- ---------- ------------------- ---------- ---------  1    3202    67048837   2020-05-21 17:58:09 67086372   2020-05-21 23:58:10  1    3203    67086372   2020-05-21 23:58:10 67105740   2020-05-22 04:39:05  1    3204    67105740   2020-05-22 04:39:05 67105758   2020-05-22 04:39:10  1    3205    67105758   2020-05-22 04:39:10 67110100   2020-05-22 05:58:11  1    3206    67110100   2020-05-22 05:58:11 67139583   2020-05-22 11:58:10  1    3207    67139583   2020-05-22 11:58:10 67160819   2020-05-22 16:49:57  1    3208    67160819   2020-05-22 16:49:57 67160828   2020-05-22 16:50:00  1    3209    67160828   2020-05-22 16:50:00 67160858   2020-05-22 16:50:12  1    3210    67160858   2020-05-22 16:50:12 67160866   2020-05-22 16:50:15  2    3211    67048841   2020-05-21 17:58:10 67086375   2020-05-21 23:58:11  2    3212    67086375   2020-05-21 23:58:11 67105736   2020-05-22 04:39:04  2    3213    67105736   2020-05-22 04:39:04 67105763   2020-05-22 04:39:13  2    3214    67105763   2020-05-22 04:39:13 67110097   2020-05-22 05:58:11  2    3215    67110097   2020-05-22 05:58:11 67139587   2020-05-22 11:58:12  2    3216    67139587   2020-05-22 11:58:12 67160815   2020-05-22 16:49:57  2    3217    67160815   2020-05-22 16:49:57 67160843   2020-05-22 16:50:06  2    3218    67160843   2020-05-22 16:50:06 67160854   2020-05-22 16:50:12  2    3219    67160854   2020-05-22 16:50:12 67160870   2020-05-22 16:50:18

resetlogs 打开数据库

alter database open resetlogs;

12、恢复后的清理操作

删除未使用的 redo

col instance format a8select thread#,instance,status,enabled from v$thread;
select group#,thread#,archived,status from v$log;
-- 禁用线程 2alter database disable thread 2;

SQL> select member from v$logfile;
MEMBER--------------------------------------------------------------------------------/oradata/jiekexu/onlinelog/group_2.262.990206165/oradata/jiekexu/onlinelog/group_1.261.990206165/oradata/jiekexu/onlinelog/o1_mf_3_hdnrcfoy_.log/oradata/jiekexu/onlinelog/o1_mf_4_hdnrdk70_.log
SQL> select group#,thread#,archived,status,bytes/1024/1024 from v$log;
    GROUP#    THREAD# ARC STATUS           BYTES/1024/1024---------- ---------- --- ---------------- ---------------         1          1 NO  CURRENT                      200         2          1 YES INACTIVE                     200         3          1 YES INACTIVE                     200         4          1 YES UNUSED                       200
SQL> alter database drop  logfile group 3;
Database altered.
SQL> alter database drop  logfile group 4;
--重新添加第 3、4 组日志alter database add  logfile ('/oradata/jiekexu/onlinelog/group_3.263.dbf')  size 200M ;alter database add  logfile ('/oradata/jiekexu/onlinelog/group_4.264.dbf')  size 200M ;
SQL> select group#,thread#,archived,status ,bytes/1024/1024 from v$log;
    GROUP#    THREAD# ARC STATUS           BYTES/1024/1024---------- ---------- --- ---------------- ---------------         1          1 YES INACTIVE                     200         2          1 YES INACTIVE                     200         3          1 NO  CURRENT                      200         4          1 YES INACTIVE                     200
SQL> SQL> select member from v$logfile;
MEMBER--------------------------------------------------------------------------------/oradata/jiekexu/onlinelog/group_2.262.990206165/oradata/jiekexu/onlinelog/group_1.261.990206165/oradata/jiekexu/onlinelog/group_3.263.dbf/oradata/jiekexu/onlinelog/group_4.264.dbf

清除多余的 undo

select name from v$tablespace where name like 'UNDO%';show parameter undo_tablespace;
drop tablespace undotbs2 including contents and datafiles;

添加 redo 日志组

SQL> select bytes/1024/1024 from v$log;
BYTES/1024/1024---------------            200            200            200            200
----- 添加日志组 
SQL> alter database add  logfile THREAD 1 group 5 size 512M ;
Database altered.
SQL>  select bytes/1024/1024 from v$log;
BYTES/1024/1024---------------            200            200            200            200            200
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
    GROUP#    THREAD# ARC STATUS---------- ---------- --- ----------------         1          1 NO  CURRENT         2          1 YES UNUSED         3          2 YES INACTIVE         4          2 YES UNUSED         5          1 YES UNUSED
SQL> select group#,thread#,archived,status,bytes/1024/1024,FIRST_CHANGE#,FIRST_TIME from v$log;
    GROUP#    THREAD# ARC STATUS           BYTES/1024/1024---------- ---------- --- ---------------- ---------------         1          1 NO  CURRENT                      200         2          1 YES UNUSED                       200         3          2 YES INACTIVE                     200         4          2 YES UNUSED                       200         5          1 YES UNUSED                       200

-- 如果为非正在应用的 active 状态 standby 日志组，先 clear，再删除重建
select group#,thread#,status,bytes/1024/1024  from v$standby_log order by thread#;GROUP#    THREAD# STATUS---------- ---------- ----------         5          1 UNASSIGNED         6          1 UNASSIGNED         7          1 UNASSIGNED         8          2 UNASSIGNED         9          2 UNASSIGNED        10          2 UNASSIGNED
alter database clear logfile group 8;alter database drop standby logfile group 8;
alter database add  logfile THREAD 1 group 3 size 512M ;  ---节点1 添加一组日志
alter database clear logfile group 3;  --删除节点 2 日志组alter database drop  logfile group 3;               

SQL> alter database clear logfile group 3;
Database altered.
SQL> alter database clear logfile group 4;    
Database altered.
SQL> alter database drop  logfile group 3; 
Database altered.
SQL> alter database drop  logfile group 4;       
Database altered.

13、验证数据库

重启数据库验证：

shutdown immediate;startup;--查看 test 表数据select count(*) from test;

14、配置监听和 TNS

手动配置监听

vi $ORACLE_HOME/network/admin/listener.ora 
LISTENER=  (DESCRIPTION=    (ADDRESS_LIST=      (ADDRESS=(PROTOCOL=tcp)(HOST=jiekexu1)(PORT=1521))      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (SID_NAME = PLSExtProc)      (ORACLE_HOME = /app/product/11.2.0/db)      (PROGRAM = extproc)    )    (SID_DESC =      (GLOBAL_DBNAME = jiekexu1)      (ORACLE_HOME = /app/product/11.2.0/db)      (SID_NAME = jiekexu1)    )  )

启动监听

lsnrctl start

手动配置 tns

jiekexu1 =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = jiekexu1)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = jiekexu1)    )  )

15、设置删除归档策略

vi clear_arch.sh
#!/bin/ksh#export ORACLE_SID=testrman target / log=/home/oracle/clear_arch.log<<EOF## delete noprompt force archivelog all completed before 'sysdate-3';delete noprompt archivelog all completed before 'sysdate-5';exitEOF
chmod +x clear_arch.sh

root 用户设置

crontab -e22 2 * * * su - oracle -c /home/oracle/clear_arch.sh

查看定时任务

crontab -l

执行验证

su - oracle -c /home/oracle/clear_arch.sh
cat /home/oracle/clear_arch.log

全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~

欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！

分享几个数据库备份脚本

一文搞懂 Oracle 统计信息

我的 Oracle ACE 心路历程
MOP 系列|MOP 三种主流数据库索引简介
Oracle 主流版本不同架构下的静默安装指南
关机重启导致 ASM 磁盘丢失数据库无法启动
Oracle SQL 性能分析(SPA)原理与实战演练

Oracle 11g 升级到 19c 需要关注的几个问题
Windows 10 环境下 MySQL 8.0.33 安装指南
SQL 大全（四）|数据库迁移升级时常用 SQL 语句
OGG|使用 OGG19c 迁移 Oracle11g 到 19C(第二版)
Oracle 大数据量导出工具——sqluldr2 的安装与使用
从国产数据库调研报告中你都能了解哪些信息及我的总结建议
使用数据泵利用 rowid 分片导出导入 lob 大表及最佳实践
在归档模式下直接 rm dbf 数据文件并重启数据库还有救吗？

欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！
——————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
ITPUB：https://blog.itpub.net/69968215
腾讯云：https://cloud.tencent.com/developer/user/5645107
——————————————————————————

JiekeXu DBA之路

JiekeXu:Oracle ACE-Pro，获 Oracle OCP/OCM 及 MySQL OCP 认证，墨天轮 MVP，利用闲时间记录菜鸟 DBA 学习成长之路，所发布文字属于个人观点和学习笔记，如有错误及不当之处，敬请批评指正！