目 录
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）。
下面是以前做备份恢复的一种通用方式，特此记录下来，以备后续参考需要。
1、建立存放备份目录
su - oracle mkdir -p /backup/backup/backup20200521 sqlplus / as sysdba复制
2、原库全量备份
原库切换日志进行备份
alter system archive log current;复制
原库利用 rman 备份片生成备份（由于不知道主库 sys 密码，数据量也较小，使用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 sqlplus='/usr/local/bin/rlwrap sqlplus' alias rman='/usr/local/bin/rlwrap rman' 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 200M ; 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 200M ; ---节点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;复制
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之路】，一起学习新知识！
——————————————————————————
公众号：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
——————————————————————————
最后修改时间：2024-09-04 10:28:51
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
筱悦星辰
6月前
评论
0真正自律的人，能够找准方向，规划阶段性目标，让自己的每一步都走得坚实有力。
6月前
点赞 评论
鲁鲁
6月前
评论
0使用 Rman 备份恢复 Oracle RAC 到单机文件系统
6月前
点赞 评论
星星之火
6月前
评论
0真理星辰，指引迷航。
正义之剑，斩断虚妄。
6月前
点赞 评论
相关阅读
【专家有话说第五期】在不同年龄段，DBA应该怎样规划自己的职业发展？
墨天轮编辑部
1214次阅读
2025-03-13 11:40:53
Oracle RAC ASM 磁盘组满了，无法扩容怎么在线处理？
Lucifer三思而后行
733次阅读
2025-03-17 11:33:53
Oracle+Deepseek+Dify 实现数据库数据实时分析
bicewow
643次阅读
2025-03-06 09:41:49
【ORACLE】ORACLE19C在19.13版本前的一个严重BUG-24761824
DarkAthena
538次阅读
2025-03-04 14:33:31
Oracle避坑指南｜同名表导出难题：如何精准排除指定用户下的表？
szrsu
501次阅读
2025-03-05 00:42:34
Ogg23ai高手必看-MySQL Innodb Cluster跟oracle的亲密接触
曹海峰
436次阅读
2025-03-04 21:56:13
2月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
429次阅读
2025-03-13 14:38:19
【ORACLE】char类型和sql优化器发生的“错误”反应
DarkAthena
396次阅读
2025-03-04 23:05:01
什么，oracle 主机用户被删了？原来是虚惊一场！
Lucifer三思而后行
389次阅读
2025-03-03 21:12:09
Oracle 如何修改 db_unique_name？强迫症福音！
Lucifer三思而后行
320次阅读
2025-03-12 21:27:56
