作者 | 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、配置监听和 TNS15、设置删除归档策略
前 言
备份和恢复都是数据库管理中非常重要的任务，用于保护数据库免受故障和灾难的影响。在 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/arch6、修改 .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_profile7、修改 /etc/hosts 文件添加主机名
127.0.0.1 localhost
127.0.0.1 jiekexu
192.168.75.32 jiekexu8、恢复控制文件
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:579、启动数据库至 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/onlinelogrman 进行恢复
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:18resetlogs 打开数据库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.shroot 用户设置
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全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~
欢迎关注我的公众号【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
——————————————————————————
623
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
