作者 | 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
全文完,希望可以帮到正在阅读的你,如果觉得有帮助,可以分享给你身边的朋友,同事,你关心谁就分享给谁,一起学习共同进步~~~
欢迎关注我的公众号【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
——————————————————————————