目 录
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进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
