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

小周的数据库进阶之路 2025-02-11

210

作者 | 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/backup20200521
sqlplus  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/dbs
orapwd file=orapwjiekexu1 password=oracle11g

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

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

jiekexu1.__db_cache_size=6174015488
jiekexu1.__java_pool_size=16777216
jiekexu1.__large_pool_size=33554432
jiekexu1.__oracle_base='/app/oracle'#ORACLE_BASE set from environment
jiekexu1.__pga_aggregate_target=2483027968
jiekexu1.__sga_target=7449083904
jiekexu1.__shared_io_pool_size=0
jiekexu1.__shared_pool_size=1174405120
jiekexu1.__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=TRUE
jiekexu1.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=7449083904
jiekexu1.thread=1
*.undo_retention=1440
jiekexu1.undo_tablespace='UNDOTBS1'
*.local_listener=''

5、新库新建目录

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

mkdir -p app/oracle/admin/jiekexu/adump
mkdir -p oradata/jiekexu/controlfile
mkdir -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.UTF8
export 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       localhost
127.0.0.1       jiekexu
192.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:56
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1893 device type=DISK


channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/jiekexu/controlfile/current01.ctl
Finished restore at 2020-05-24 14:49:57

9、启动数据库至 mount 状态

sql'alter database mount';


RMAN> sql'alter database mount';


sql statement: alter database mount
released channel: ORA_DISK_1


RMAN>

10、生成修改路径的语句

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

set pagesize  200 linesize 200
select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";'
from v$datafile a
union all
select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";'
from v$tempfile a
union all
SELECT '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/datafile
mkdir -p oradata/jiekexu/tempfile
mkdir -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 a8
select thread#,instance,status,enabled from v$thread;


select group#,thread#,archived,status from v$log;


-- 禁用线程 2
alter 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=test
rman 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';
exit
EOF


chmod +x clear_arch.sh

root 用户设置

crontab -e
22 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

文章转载自小周的数据库进阶之路，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。