rman备份要求：每周日18点全备，其它日期同时间增备，保留两个全备份
rman target /
configure retention policy to redundancy 2;
configure retention policy to recovery window of 15 days;--要么windows要么redundancy，一般选择windows合理
configure rman output to keep for 15 days;
1.准备足够的备份空间：此次直接备份到nfs异机。(根据表空间占用评估备份空间)
注意两个节点都需要挂接nfs
挂接备份路径
mount -t nfs -o rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,proto=tcp 192.168.0.111:/data /bak
mkdir /bak/rman
确认oracle用户可写 touch /bak/rman/1.txt
修改/etc/fstab保证重启自动挂接：
192.168.0.111:/data /bak nfs rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,proto=tcp,_netdev 0 0
自动挂接测试：
umount /bak
mount -a
df -ht确认可以看到如下截图挂接成功,并且oracle用户可写。
cd /bak
touch 1.txt
2.启用rman备份跟踪
sql> alter database enable block change tracking using file ' data';
sql> select status,filename from v$block_change_tracking;
status
----------
filename
--------------------------------------------------------------------------------
enabled
data/jyc/changetracking/ctf.340.1138815317
3.准备备份脚本并测试：（准确记录备份时间和备份大小，评估应该设定的备份开始和结束时间）
注意在两个节点的相同路径都存放相同脚本。
[oracle@rac1 bak]$ pwd
/home/oracle/bak
[oracle@rac1 bak]$ ll
total 12
-rwxr-xr-x 1 oracle oinstall 745 jun 6 17:57 jycdb_full_jycdb.sh
-rwxr-xr-x 1 oracle oinstall 701 jun 6 18:00 jycdb_incr_jycdb.sh
-rwxr-xr-x 1 oracle oinstall 532 jun 6 17:33 expdp_job.sh
[oracle@rac1 bak]$ more jycdb_incr_jycdb.sh
#!/bin/bash
echo -e '******start********_'$(date ' %y-%m-%d %h:%m:%s')
source /home/oracle/.bash_profile
rman target / log=/bak/rman/jycdb_incr_`date %y%m%d-%h%m%s`.log < ./1.log &
tail -f 1.log
4.查看备份进度(测试时)：
col opname format a32
col target_desc format a32
col perwork format a12
set lines 160
set wrap off
select sid,opname,target_desc,sofar,totalwork,time_remaining,elapsed_seconds from v$session_longops where sofar!=totalwork;
新开窗口观察备份增长
watch -n 1 du -sm rman
新开窗口观察网络流量
sar -n dev 1
新开窗口观察cpu、io压力
sar 1 100000
iostat -dm 1
5.使用oracle任务计划做备份任务(当然也可以在操作系统crontab任务中配置计划，只需在任意1个节点部署运行即可；由于此次在oracle库中配置，所以需要注意应在两节点挂接nfs和存放相同备份脚本)
[oracle@rac1 bak]$ sqlplus / as sysdba
sql*plus: release 19.0.0.0.0 - production on tue jun 6 19:39:24 2023
version 19.18.0.0.0
米乐app官网下载 copyright (c) 1982, 2022, oracle. all rights reserved.
connected to:
oracle database 19c enterprise edition release 19.0.0.0.0 - production
version 19.18.0.0.0
sql> show pdbs;
con_id con_name open mode restricted
---------- ------------------------------ ---------- ----------
2 pdb$seed read only no
3 jycdb read write no
sql> set linesize 500;
sql> col next_run_date format a50
sql> col object_name format a30
sql> select obj#, object_name, next_run_date from sys.scheduler$_job ssj, dba_objects dob where ssj.obj# = dob.object_id and object_name like 'jycdb_bak%';
no rows selected
sql> select obj#, object_name, next_run_date from sys.scheduler$_job ssj, dba_objects dob where ssj.obj# = dob.object_id;
obj# object_name next_run_date
---------- ------------------------------ --------------------------------------------------
19685 purge_log 07-jun-23 03.00.00.654753 am pst8pdt
19688 file_watcher
19694 pmo_deferred_gidx_maint_job 07-jun-23 02.00.00.065217 am pst8pdt
19695 cleanup_non_exist_obj 06-jun-23 10.58.25.141697 pm 08:00
19696 cleanup_online_ind_build 06-jun-23 05.14.27.319985 am -07:00
19697 cleanup_tab_iot_pmo 06-jun-23 05.14.37.287152 am -07:00
19698 cleanup_transient_type 06-jun-23 10.58.25.096625 pm 08:00
19699 cleanup_transient_pkg 06-jun-23 05.14.57.000000 am -07:00
19700 cleanup_online_pmo 06-jun-23 05.15.07.887309 am -07:00
19701 file_size_upd 16-mar-24 10.58.06.209593 am 08:00
19817 ora$autotask_clean 07-jun-23 03.00.00.607016 am pst8pdt
obj# object_name next_run_date
---------- ------------------------------ --------------------------------------------------
19820 hm_create_offline_dictionary
19821 dra_reevaluate_open_failures
20151 ora$preplugin_backup_job
20642 bsln_maintain_stats_job 11-jun-23 12.00.00.417570 am -07:00
20708 fgr$autopurge_job
20709 rse$clean_recoverable_script 07-jun-23 12.00.00.300713 am prc
20710 sm$clean_auto_split_merge 07-jun-23 12.00.00.312885 am prc
20863 load_opatch_inventory
22395 xmldb_nfs_cleanup_job
76100 mgmt_config_job 07-jun-23 01.01.01.068116 am 08:00
76101 mgmt_stats_config_job 01-jul-23 01.01.01.364332 am 08:00
obj# object_name next_run_date
---------- ------------------------------ --------------------------------------------------
91874 load_opatch_inventory_2
91873 load_opatch_inventory_1
24 rows selected.
sql> select job_name, job_type, enabled, state from user_scheduler_jobs where job_name like 'jycdb_bak%';
no rows selected
sql>
在byday参数指定周日（byday=sun），执行时间指定 1 点（byhour=18）：
begin
dbms_scheduler.create_job
(
job_name => 'jycdb_bak_level_0',
job_type => 'executable',
job_action => '/home/oracle/bak/jycdb_full_jycdb.sh',
repeat_interval => 'freq=weekly; byday=sun; byhour=18;byminute=0;bysecond=0',
enabled => true,
comments => 'jycdb backup job level_0'
);
end;
/
增量备份任务，指定每周从周一到周六，每天18点执行；
在byday参数指定周一至周六（byday=mon,tue,wed,thu,fri,sat）：
begin
dbms_scheduler.create_job
(
job_name => 'jycdb_bak_level_1',
job_type => 'executable',
job_action => '/home/oracle/bak/jycdb_incr_jycdb.sh',
repeat_interval => 'freq=weekly; byday=mon,tue,wed,thu,fri,sat; byhour=18;byminute=0;bysecond=0',
enabled => true,
comments => 'jycdb backup job level_1'
);
end;
/
设置属性参考：
exec dbms_scheduler.set_attribute(name => 'jycdb_bak_level_0',attribute => 'repeat_interval',value => 'freq=weekly; byday=sun; byhour=18;byminute=0;bysecond=0');
exec dbms_scheduler.set_attribute(name => 'jycdb_bak_level_1',attribute => 'repeat_interval',value => 'freq=weekly; byday=mon,tue,wed,thu,fri,sat; byhour=18;byminute=0;bysecond=0');
exec dbms_scheduler.set_attribute(name => 'jycdb_bak_level_0',attribute => 'instance_id',value => 1);
sql> select job_name, job_type, enabled, state from user_scheduler_jobs where job_name like 'jycdb_bak%';
job_name job_type enabl state
-------------------------------------------------------------------------------------------------------------------------------- ---------------- ----- --------------------
jycdb_bak_level_0 executable true scheduled
jycdb_bak_level_1 executable true scheduled
sql> select obj#, object_name, next_run_date from sys.scheduler$_job ssj, dba_objects dob where ssj.obj# = dob.object_id and object_name like 'jycdb_bak%';
obj# object_name next_run_date
---------- ------------------------------ --------------------------------------------------
94313 jycdb_bak_level_0 11-jun-23 06.40.46.570439 pm prc
94314 jycdb_bak_level_1 07-jun-23 06.40.59.627431 pm prc
sql> select log_id, log_date, status from user_scheduler_job_run_details where job_name like 'jycdb_bak%';
no rows selected
sql>
6.备份检查：
至少观察一周，确保备份结果符合预期，不影响业务性能。
测试可能用到的命令：
exec dbms_scheduler.drop_job(job_name=>'jycdb_bak_level_1');
exec dbms_scheduler.run_job(job_name=>'jycdb_bak_level_1');
查看执行结果：
set line 180
set wrap off
col job_name for a30
col status for a10
col actual_start_date for a20
col run_duration for a16
select job_name,instance_id,status,error#,actual_start_date,run_duration,additional_info from dba_scheduler_job_run_details where job_name like 'jycdb_bak%' order by actual_start_date desc;
7.常见错误
7.1 ora-27369: job of type executable failed with exit code: 255 execve: exec format error
begin dbms_scheduler.run_job(job_name=>'jycdb_bak_level_1'); end;
*
error at line 1:
ora-27369: job of type executable failed with exit code: 255 execve: exec
format error
ora-06512: at "sys.dbms_isched", line 242
ora-06512: at "sys.dbms_scheduler", line 566
ora-06512: at line 1
处理办法：脚本头增加
#!/bin/bash
7.2 ora-27369: job of type executable failed with exit code: no such file or directory
select job_name,status,error#,actual_start_date,run_duration,additional_info,errors from dba_scheduler_job_run_details where job_name like 'jycdb_bak%';
job_name
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
status error# actual_start_date
------------------------------ ---------- ---------------------------------------------------------------------------
run_duration
---------------------------------------------------------------------------
additional_info
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
errors
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
jycdb_bak_level_1
failed 2 07-jun-23 06.40.59.840001 pm prc
000 00:00:00
job_name
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
status error# actual_start_date
------------------------------ ---------- ---------------------------------------------------------------------------
run_duration
---------------------------------------------------------------------------
additional_info
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
errors
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ora-27369: job of type executable failed with exit code: no such file or directory
检查两个节点的alert日志，发现节点1正常，而节点2的alert日志有报错如下：
errors in file /oracle/app/oracle/diag/rdbms/jyc/jyc2/trace/jyc2_j000_20581.trc:
ora-12012: error on auto execute of job "sys"."jycdb_bak_level_1"
ora-27369: job of type executable failed with exit code: no such file or directory
2023-06-07t22:00:00.081080 08:00
setting resource manager cdb plan default_maintenance_plan via parameter
2023-06-07t22:00:00.152945 08:00
jycdb(3):setting resource manager plan scheduler[0x4d51]:default_maintenance_plan via scheduler window
jycdb(3):setting resource manager plan default_maintenance_plan via parameter
2023-06-08t00:01:13.263519 08:00
table sys.wrp$_reports_time_bands: added interval partition sys_p1434 (4906) values less than (to_date(' 2023-06-08 01:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian'))
2023-06-08t01:00:33.778381 08:00
table sys.wrp$_reports: added interval partition sys_p1435 (4907) values less than (to_date(' 2023-06-09 01:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian'))
table sys.wrp$_reports_details: added interval partition sys_p1436 (4907) values less than (to_date(' 2023-06-09 01:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian'))
2023-06-08t02:00:00.104176 08:00
closing resource manager plan via scheduler window
clearing resource manager cdb plan via parameter
2023-06-08t02:00:00.136410 08:00
jycdb(3):closing scheduler window
jycdb(3):closing resource manager plan via scheduler window
jycdb(3):clearing resource manager plan via parameter
2023-06-08t06:30:33.287999 08:00
thread 2 advanced to log sequence 572 (lgwr switch), current scn: 65077905
current log# 9 seq# 572 mem# 0: data/jyc/onlinelog/group_9.292.1131713213
2023-06-08t06:30:35.597431 08:00
arc1 (pid:13798): archived log entry 2155 added for t-2.s-571 id 0x179d9e75 lad:1
[oracle@rac2 trace]$
[oracle@rac2 trace]$ more /oracle/app/oracle/diag/rdbms/jyc/jyc2/trace/jyc2_j000_20581.trc
trace file /oracle/app/oracle/diag/rdbms/jyc/jyc2/trace/jyc2_j000_20581.trc
oracle database 19c enterprise edition release 19.0.0.0.0 - production
version 19.18.0.0.0
build label: rdbms_19.18.0.0.0dbru_linux.x64_230111
oracle_home: /oracle/app/oracle/product/19c/dbhome_1
system name: linux
node name: rac2
release: 5.4.17-2102.201.3.el7uek.x86_64
version: #2 smp fri apr 23 09:05:55 pdt 2021
machine: x86_64
instance name: jyc2
redo thread mounted by this instance: 2
oracle process number: 236
unix process pid: 20581, image: oracle@rac2 (j000)
*** 2023-06-07t18:40:59.932362 08:00 (cdb$root(1))
*** session id:(3390.19105) 2023-06-07t18:40:59.932413 08:00
*** client id:() 2023-06-07t18:40:59.932419 08:00
*** service name:(sys$users) 2023-06-07t18:40:59.932424 08:00
*** module name:(dbms_scheduler) 2023-06-07t18:40:59.932428 08:00
*** action name:(jycdb_bak_level_1) 2023-06-07t18:40:59.932433 08:00
*** client driver:(server) 2023-06-07t18:40:59.932437 08:00
*** container id:(1) 2023-06-07t18:40:59.932442 08:00
ora-12012: error on auto execute of job "sys"."jycdb_bak_level_1"
at 0x7ffd173fbd90 placed jslv.c@1927
ora-27369: job of type executable failed with exit code: no such file or directory
[oracle@rac2 trace]$ pwd
/oracle/app/oracle/diag/rdbms/jyc/jyc2/trace
上述错误是因为任务在rac2上运行了，而rac2上忘记放置相应的备份脚本以及未挂接nfs目录导致。
7.3 umount.nfs: /bak: device is busy
[root@rac2 ~]# cd /
[root@rac2 /]# umount /bak
umount.nfs: /bak: device is busy
[root@rac2 /]# df -h
filesystem size used avail use% mounted on
devtmpfs 126g 0 126g 0% /dev
tmpfs 126g 2.1g 124g 2% /dev/shm
tmpfs 126g 450m 126g 1% /run
tmpfs 126g 0 126g 0% /sys/fs/cgroup
/dev/mapper/ol-root 495g 104g 392g 21% /
/dev/sr0 4.6g 4.6g 0 100% /media/cdrom
/dev/vda1 1014m 204m 811m 21% /boot
tmpfs 26g 0 26g 0% /run/user/54322
tmpfs 26g 0 26g 0% /run/user/54321
tmpfs 26g 0 26g 0% /run/user/0
192.168.0.111:/data 3.0t 251g 2.8t 9% /bak
[root@rac2 /]# ps -ef|grep rman
root 22777 3561 0 11:02 pts/2 00:00:00 grep --color=auto rman
[root@rac2 /]# umount -f /bak
umount.nfs: /bak: device is busy
[root@rac2 /]# fuser -m -v /bak
user pid access command
/bak: root kernel mount /bak
oracle 30869 ..c.. bash
[root@rac2 /]# ps -ef|grep 30869
root 5496 3561 0 11:03 pts/2 00:00:00 grep --color=auto 30869
oracle 30869 30816 0 09:13 pts/0 00:00:00 -bash
[root@rac2 /]#
[root@rac2 /]# kill -9 30869
[root@rac2 /]# ps -ef|grep 30869
root 9914 3561 0 11:03 pts/2 00:00:00 grep --color=auto 30869
[root@rac2 /]# fuser -m -v /bak
user pid access command
/bak: root kernel mount /bak
[root@rac2 /]# umount /bak
[root@rac2 /]# df -h
filesystem size used avail use% mounted on
devtmpfs 126g 0 126g 0% /dev
tmpfs 126g 2.1g 124g 2% /dev/shm
tmpfs 126g 449m 126g 1% /run
tmpfs 126g 0 126g 0% /sys/fs/cgroup
/dev/mapper/ol-root 495g 104g 392g 21% /
/dev/sr0 4.6g 4.6g 0 100% /media/cdrom
/dev/vda1 1014m 204m 811m 21% /boot
tmpfs 26g 0 26g 0% /run/user/54322
tmpfs 26g 0 26g 0% /run/user/0
[root@rac2 /]# cat /etc/fstab
#
# /etc/fstab
# created by anaconda on mon mar 6 21:54:51 2023
#
# accessible filesystems, by reference, are maintained under '/dev/disk'
# see man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/ol-root / xfs defaults 0 0
uuid=ab5d1dfb-39c6-4b21-bdb5-7ccc185e587e /boot xfs defaults 0 0
/dev/mapper/ol-swap swap swap defaults 0 0
/dev/sr0 /media/cdrom iso9660 ro 0 0
/home/swap/swap_file swap swap defaults 0 0
192.168.0.111:/data /bak nfs rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,proto=tcp,_netdev 0 0
[root@rac2 /]#
[root@rac2 /]# mount -a
[root@rac2 /]# df -ht
filesystem type size used avail use% mounted on
devtmpfs devtmpfs 126g 0 126g 0% /dev
tmpfs tmpfs 126g 2.1g 124g 2% /dev/shm
tmpfs tmpfs 126g 449m 126g 1% /run
tmpfs tmpfs 126g 0 126g 0% /sys/fs/cgroup
/dev/mapper/ol-root xfs 495g 104g 392g 21% /
/dev/sr0 iso9660 4.6g 4.6g 0 100% /media/cdrom
/dev/vda1 xfs 1014m 204m 811m 21% /boot
tmpfs tmpfs 26g 0 26g 0% /run/user/54322
tmpfs tmpfs 26g 0 26g 0% /run/user/0
192.168.0.111:/data nfs 3.0t 251g 2.8t 9% /bak
[root@rac2 /]# su - oracle
last login: thu jun 8 11:00:39 cst 2023 on pts/2
[oracle@rac2 ~]$ cd /bak
[oracle@rac2 bak]$ touch 1.txt
[oracle@rac2 bak]$ rm 1.txt
[oracle@rac2 bak]$
或者不用kill命令，直接fuser -cvk /bak，不显示详细进程则去掉参数v,fuser -ck /bak即可。
[root@rac1 ~]# fuser -m -v /bak
user pid access command
/bak: root kernel mount /bak
root 25535 ..c.. bash
root 32522 ..c.. bash
[root@rac1 ~]# fuser -cv /bak
user pid access command
/bak: root kernel mount /bak
root 25535 ..c.. bash
root 32522 ..c.. bash
[root@rac1 ~]# fuser -mv /bak
user pid access command
/bak: root kernel mount /bak
root 25535 ..c.. bash
root 32522 ..c.. bash
[root@rac1 ~]# ps -ef|grep 25535
root 17759 14821 0 11:13 pts/3 00:00:00 grep --color=auto 25535
root 19557 25535 0 09:16 pts/0 00:00:00 su - oracle
root 25535 22293 0 09:02 pts/0 00:00:00 -bash
[root@rac1 ~]# ps -ef|grep 32522
root 19858 14821 0 11:13 pts/3 00:00:00 grep --color=auto 32522
root 32522 22293 0 10:01 pts/2 00:00:00 -bash
[root@rac1 ~]#
[root@rac1 ~]#
[root@rac1 ~]#
[root@rac1 ~]# fuser -cvk /bak
user pid access command
/bak: root kernel mount /bak
root 25535 ..c.. bash
root 32522 ..c.. bash
[root@rac1 ~]# fuser -mv /bak
user pid access command
/bak: root kernel mount /bak
[root@rac1 ~]# fuser -cv /bak
user pid access command
/bak: root kernel mount /bak
[root@rac1 ~]#
[root@rac1 ~]# fuser -ck /bak
[root@rac1 ~]# fuser -mv /bak
user pid access command
/bak: root kernel mount /bak
[root@rac1 ~]# fuser -cv /bak
user pid access command
/bak: root kernel mount /bak
如上操作记录，处理办法为fuser -m -v /bak找出占用的进程，kill -9掉。
7.4备份主机没权限读取拷贝备份的文件：
如下图，备份的主机用户和属主id不存在实际的oracle用户和属主，无权限读取。
处理办法：
在数据库服务器任意节点，对于已存在的文件，在oracle用户下执行su - oracle
chmod -r 777 /bak/rman，为解决新备份文件的权限问题，则在备份脚本末尾增加赋权命令即可：chmod -r 777 /bak/rman
8.相关参考：
最后修改时间：2023-12-11 16:15:43
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。