暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 崖山共享集群数据库备份恢复标准化实践

崖山共享集群数据库备份恢复标准化实践

原创 H 2025-01-01
788

首先，我们先来看看崖山数据库备份恢复的相关概念；然后再来熟悉了解对应的命令；之后测试如何使用PLSQL和shell将这些命令封装为方便使用的形式，并使用它们，来备份还原崖山共享集群数据库。

数据库备份恢复相关概念

备份恢复概念：
· 全量备份恢复：一次备份所有数据，备份集可以独立恢复出整个数据库。
· 增量备份恢复：只备份发生过变化的数据块，可减少备份集大小，但需要依赖于其他备份集才能恢复。
· 归档备份恢复：只备份归档日志，一般用于PITR时进行归档日志的恢复。
· PITR：基于时间点的恢复，首先通过备份集恢复出较早时间的数据库，然后恢复和注册归档日志，回放这些归档日志到指定时间点。

备份原理：①备份线程以数据库块的方式，从物理文件读取数据写到备份文件中；②读文件时会对数据块加锁，防止读写并发；③对于data文件，只备份高水位线以下的数据
恢复原理：①将备份集中的文件，以数据块为基本单位拷贝回数据库的文件中；②拷贝结束后，将数据库文件扩展到高水位线；③从RCY点（gv$database 的 rcy_point 字段）开始回放归档日志，回放结束后，数据库OPEN，对外提供服务

全量备份：对某一时间点上的所有数据进行完全复制，不依赖之前的备份集。一个全量备份集可以恢复出所有数据
图片.png

增量备份：在有全量备份基线（level0）的情况下，增量备份（level1）只备份上一次备份后，发生过变化的数据。增量备份可以减少备份时间，减少磁盘占用，但是恢复时，需要按增量备份集链逐个恢复
图片.png

图片.png

备份集只能将数据库恢复到备份时间点，如果备份时间点之后的归档日志文件存在，则可以通过回放这些归档文件，让数据库恢复到任意的时间点，这就是基于时间点的恢复(Point-in-Time Recovery)。
基于时间点的恢复允许数据库恢复到备份时间点到最新时间点的任意时间，可以用来回退误操作，或者修复损坏的数据库。
如图所示，在t1时间段做数据库的全库或增量备份，假设在数据库运行至t3时间点后发生数据库损坏，此时就可以使用t1时间点的备份集和t1至t2时间点之间的归档文件，使用PITR操作回放归档日志至t2时间点，实现修复数据库异常损坏的目的。
图片.png

备份恢复关键命令


-- 备份命令示例：

-- 全量备份：
BACKUP DATABASE INCREMENTAL LEVEL 0  format '/home/y333/yasdb_back/bak_20241231144423_lev0_full';
-- 累积增量：
BACKUP DATABASE INCREMENTAL LEVEL 1 CUMULATIVE format '/home/y333/yasdb_back/bak_20241231144639_lev1_cumu';
-- 普通增量（差异增量）：
BACKUP DATABASE INCREMENTAL LEVEL 1  format '/home/y333/yasdb_back/bak_20241231144710_lev1_diff';
-- 归档备份：
BACKUP ARCHIVELOG SEQUENCE BETWEEN 70 AND 75 THREAD 1 FORMAT '/home/y333/yasdb_back/bak_20241231145130_lev0_arch_inst_1';


-- 恢复示例：
RESTORE DATABASE FROM '/home/y333/yasdb_back/bak_20241231144423_lev0_full' PARALLELISM 2;
RESTORE DATABASE INCREMENTAL FROM '/home/y333/yasdb_back/bak_20241231144639_lev1_cumu' PARALLELISM 2;
RESTORE DATABASE INCREMENTAL FROM '/home/y333/yasdb_back/bak_20241231144710_lev1_diff' PARALLELISM 2;

RESTORE ARCHIVELOG SEQUENCE BETWEEN 70 AND 75 THREAD 1 PARALLELISM 2 FROM SEARCHDIR '/home/y333/yasdb_back/';
RECOVER DATABASE UNTIL TIME TO_DATE('20241231145130','yyyymmddhh24miss');

备份恢复命令封装分析

对于使用者来说，更关注的可能是“业务价值”，所以，这里考虑将数据库的备份还原能力以用户视角进行封装，用户关注的角度可能有：

  • 如何方便地实施某种类型的备份操作。
  • 如何利用备份，进行数据库恢复。
  • 如何管理备份
  • 检查历史备份的报错信息等。

那么接下来，我们逐个看看如何实现。

全量备份和增量备份

我们将备份逻辑写到PLSQL块中，并封装为一个SHELL函数，关键代码如下：


back_dbf(){
echo "$(yasql -S / as sysdba << EOF 
set serveroutput on 
SET FEEDBACK ON 
declare 
cmd_str varchar(500):='BACKUP DATABASE INCREMENTAL LEVEL $backup_lev $cumu_str format ''${backupdir}/bak_${dt}_lev${backup_lev}_${path_str}''';
begin 
DBMS_OUTPUT.PUT_LINE('EXECUTING SQL: '||cmd_str||';');
execute immediate cmd_str;
execute immediate 'alter system switch logfile';
end;
/ 
exit;
EOF
)" | tr -d '\n'
}

这时，我们就可以对共享集群数据库进行全量备份和增量备份了。执行全量备份，普通增量，累积增量备份时输出类似如下，可以看到成功执行了指定类型的备份操作（F代表全量备份；C代表累积增量；D代表普通增量）：


[yashan@yasrac01 yasdb_back]$ sh db_backup.sh F
Backup start time 2024-12-31 23:48:42
enough backup space.
Backup successfully.
EXECUTING SQL: BACKUP DATABASE INCREMENTAL LEVEL 0  format '/home/yashan/yasdb_back/bak_20241231234842_lev0_full';PL/SQL Succeed.
Backup end time 2024-12-31 23:48:47

[yashan@yasrac01 yasdb_back]$ sh db_backup.sh C
Backup start time 2024-12-31 23:48:54
enough backup space.
Backup successfully.
EXECUTING SQL: BACKUP DATABASE INCREMENTAL LEVEL 1 CUMULATIVE format '/home/yashan/yasdb_back/bak_20241231234854_lev1_cumu';PL/SQL Succeed.
Backup end time 2024-12-31 23:48:59

[yashan@yasrac01 yasdb_back]$ sh db_backup.sh D
Backup start time 2024-12-31 23:49:06
enough backup space.
Backup successfully.
EXECUTING SQL: BACKUP DATABASE INCREMENTAL LEVEL 1  format '/home/yashan/yasdb_back/bak_20241231234906_lev1_diff';PL/SQL Succeed.
Backup end time 2024-12-31 23:49:12

归档备份

归档备份的复杂度在于，共享集群有多个节点，所以必须能兼容节点不确定情况下的各个节点分别实施归档备份，核心备份代码如下：



arc_all(){
echo "$(echo $(yasboot cluster status -c $cluster_name -d)|awk '{{vl=$0;gsub(/-{2,}/,"+",vl);gsub(/\+{1,}/,"+",vl);split(vl,l,/\+/)} } END{for (a in l){if (length(l[a])!=0) {print l[a]}}}'|awk '
BEGIN {"echo ${sys_pwd}"|getline sp;"echo ${cluster_name}"|getline cn;}
{l=$0;gsub(" ","",l);lc=split(l,ll,"|");for (i=1;i<=lc;i++) 
if(NR==1){if (ll[i]=="nodeid"){o=i};if (ll[i]=="instance_status"){p=i};if (ll[i]=="database_status"){q=i};
if (ll[i]=="database_role"){r=i}};if(NR>1&&ll[p]=="open"&&ll[q]=="normal"&&ll[r]=="primary")
{l1=substr(ll[o],1,index(ll[o],":")-1);print "yasboot sql -n "l1" -c "cn" -s '\''ALTER SYSTEM ARCHIVE LOG CURRENT'\'' -u sys -p '\''""\""sp"\"""'\''"";echo $?"}}'|sh |grep -v 'Succeed'|grep -v ^$ | tr -d '\n')"
}


back_arc(){
r=$(arc_all)
if [[ $r =~ ^[0-9]+$ && $(($r)) == 0 ]];then
echo "$(yasql -S / as sysdba << EOF 
SET SERVEROUTPUT ON 
SET FEEDBACK ON 
DECLARE 
TYPE REC IS RECORD(INST_ID INTEGER,N_B INTEGER,B_B INTEGER,B_E INTEGER);
rec_1 REC;
cursor cmd_cur is select a.inst_id,case when (c.SEQUENCE_END#+1) <= b.back_end then 1 else 0 end as nb,
case (select count(*) from GV\$DATABASE) when (select count(*) from V\$DATABASE) then c.SEQUENCE_END#+1 else 
case c.TYPE when 'INCREMENTAL' then c.SEQUENCE_END#+1 else c.SEQUENCE_END# end end as bb,b.back_end as be from 
(select a.inst_id,case ${full_pitr} when 2 then nvl(nvl(a.MA,a.MF),a.MI) when 1 then nvl(a.MF,a.MI) when 0 then a.MI end as bt from 
(select a.inst_id,max(b.START_TIME) as ma,(select max(b.START_TIME) from dba_backup_set b where b.type='INCREMENTAL' and 
b.INCREMENT_LEVEL=0) as mf,(select max(b.START_TIME) from dba_backup_set b where b.type='INCREMENTAL' and b.INCREMENT_LEVEL=1) as mi 
from gv\$database a left join DBA_ARCHIVE_BACKUPSET b on a.inst_id=b.INSTANCE_NUMBER# and b.type='ARCHIVE' group by a.inst_id) a) a 
JOIN (SELECT THREAD#,MAX(SEQUENCE#) back_end FROM V\$ARCHIVED_LOG GROUP BY THREAD#) b ON a.inst_id=b.THREAD# left join 
DBA_ARCHIVE_BACKUPSET c on c.START_TIME=a.bt and a.inst_id=c.INSTANCE_NUMBER# ; 
cmd_str clob;
temp_str clob;
skip_str clob:='';
TYPE cln_tab IS TABLE OF clob INDEX BY INTEGER;
v_cln_tab cln_tab;
begin 
open cmd_cur;
loop 
fetch cmd_cur into REC_1;
exit when cmd_cur%NOTFOUND;
begin 
select 'BACKUP ARCHIVELOG SEQUENCE BETWEEN '|| rec_1.B_B || ' AND '|| rec_1.B_E ||' THREAD '|| rec_1.INST_ID || 
' FORMAT ''${backupdir}/bak_${dt}_lev${backup_lev}_${path_str}_inst_'|| rec_1.INST_ID ||'''' into cmd_str FROM dual where rec_1.N_B=1;
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('EXECUTING SQL: '||cmd_str||';');
execute immediate cmd_str;
EXCEPTION 
WHEN NO_DATA_FOUND THEN 
null;
end;
begin 
SELECT 'ALTER DATABASE DELETE ARCHIVELOG UNTIL SEQUENCE '||LEAST(rec_1.B_E,
(select SPLIT(c.RCY_POINT,'-',2)-1 from GV\$DATABASE c where c.INST_ID=rec_1.INST_ID),
ifnull((select min(d.RECEIVED_SEQ#) from GV\$ARCHIVE_DEST_STATUS d where d.INST_ID=rec_1.INST_ID),rec_1.B_E)) 
||' THREAD '||rec_1.INST_ID into temp_str FROM dual where case ${delete_input} when 2 then (select 
(SELECT VALUE from V\$PARAMETER where name ='ARCH_CLEAN_IGNORE_MODE') in ('BACKUP','BOTH') from dual) when 1 then TRUE end 
and rec_1.N_B=1;
v_cln_tab(v_cln_tab.count()+1):=temp_str;
EXCEPTION 
WHEN NO_DATA_FOUND THEN 
null;
end;
begin 
select NVL2(skip_str,skip_str||';','')||'backup set '||a.TAG ||' include '||'instance number '||a.INSTANCE_NUMBER#||' sequence '|| a.SEQUENCE_END#  || '(last sequence '||rec_1.B_E||' )' into skip_str from DBA_ARCHIVE_BACKUPSET a where rec_1.N_B=0 and rec_1.B_B-1 between SEQUENCE_BEGIN# and SEQUENCE_END# and rec_1.INST_ID=INSTANCE_NUMBER# order by case when TYPE='ARCHIVE' then 1 else 0 end desc,a.START_TIME desc limit 1;
EXCEPTION 
WHEN NO_DATA_FOUND THEN 
null;
end;
end loop;
IF v_cln_tab.count() > 0 and ${delete_input} >= 1 THEN 
for i in 1..v_cln_tab.count() LOOP 
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('EXECUTING SQL: '||v_cln_tab(i)||';');
execute immediate v_cln_tab(i);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE(skip_str);
close cmd_cur;
end;
/ 
exit;
EOF
)" | tr -d '\n'
else
echo 'switch logfile failed'
fi

使用代码，能正常备份共享集群数据库，输出类似如下，可以看到2个节点的归档均能被正常备份：


[yashan@yasrac01 yasdb_back]$ sh db_backup.sh A
Backup start time 2024-12-31 23:54:01
enough backup space.
Backup successfully.
EXECUTING SQL: BACKUP ARCHIVELOG SEQUENCE BETWEEN 54 AND 56 THREAD 2 FORMAT '/home/yashan/yasdb_back/bak_20241231235401_lev0_arch_inst_2';EXECUTING SQL: BACKUP ARCHIVELOG SEQUENCE BETWEEN 86 AND 91 THREAD 1 FORMAT '/home/yashan/yasdb_back/bak_20241231235401_lev0_arch_inst_1';PL/SQL Succeed.
Backup end time 2024-12-31 23:54:04

[yashan@yasrac01 yasdb_back]$ sh db_backup.sh A
Backup start time 2024-12-31 23:54:10
enough backup space.
Backup successfully.
EXECUTING SQL: BACKUP ARCHIVELOG SEQUENCE BETWEEN 91 AND 92 THREAD 1 FORMAT '/home/yashan/yasdb_back/bak_20241231235410_lev0_arch_inst_1';EXECUTING SQL: BACKUP ARCHIVELOG SEQUENCE BETWEEN 56 AND 57 THREAD 2 FORMAT '/home/yashan/yasdb_back/bak_20241231235410_lev0_arch_inst_2';PL/SQL Succeed.
Backup end time 2024-12-31 23:54:13

恢复指引

使用 sh db_backup.sh pitr <恢复时间> 生成恢复指引：

[yashan@yasrac01 yasdb_back]$ sh db_backup.sh pitr 20241231235400


#--  检测到异机恢复或恢复到YAC。须设置db_file_name_convert等参数
yasql / as sysdba -c "alter system set db_file_name_convert='+DG0/dbfiles','/home/yashan/yasdb_data/ce-1-1/dbfiles' scope=both";
yasql / as sysdba -c "alter system set redo_file_name_convert='+DG0/dbfiles','/home/yashan/yasdb_data/ce-1-1/dbfiles' scope=both";
yasql / as sysdba -c "alter system set db_bucket_name_convert='+DG0/dbfiles','/home/yashan/yasdb_data/ce-1-1/local_fs' scope=both";
#--  如果要将数据库恢复到共享集群，可以将共享集群convert参数路径设置为： +DG0/dbfiles/ (以DG0为例)
#  yasql / as sysdba -c "alter system set db_file_name_convert='+DG0/dbfiles','+DG0/dbfiles' scope=both";
#  yasql / as sysdba -c "alter system set redo_file_name_convert='+DG0/dbfiles','+DG0/dbfiles' scope=both";
#  yasql / as sysdba -c "alter system set db_bucket_name_convert='+DG0/dbfiles','+DG0/dbfiles' scope=both";
#--  请在操作前设置好数据库相关环境变量：$YASDB_HOME $YASDB_DATA $PATH $LD_LIBRARY_PATH
#--  清理 rm -rf /home/yashan/yasdb_data/ce-1-1/dbfiles/* 下的所有文件（建议保留redo文件），并将单实例启动到nomount状态下，准备应用全备。
#--  同机恢复做rm操作时，注意保留redo文件，redo文件可能需要用来进行recover。
#--  如需应用累积增备，可能需要关闭数据库并重新启动到nomount，相关命令：shutdown immediate;  yasdb nomount &  
#--  yasboot cluster restart -c yashandb -m nomount   ## ycsctl stop instance 
#--  yasql / as sysdba -c "shutdown immediate;";  
#--  如有需要，可以使用全备'/home/yashan/yasdb_back/bak_20241231234842_lev0_full'路径下的spfile文件
   nohup yasdb nomount &
yasql / as sysdba -c "RESTORE DATABASE FROM '/home/yashan/yasdb_back/bak_20241231234842_lev0_full' PARALLELISM 2";
   yasql / as sysdba -c "shutdown immediate";
   nohup yasdb nomount &
yasql / as sysdba -c "RESTORE DATABASE INCREMENTAL FROM '/home/yashan/yasdb_back/bak_20241231234854_lev1_cumu' PARALLELISM 2";
yasql / as sysdba -c "RESTORE DATABASE INCREMENTAL FROM '/home/yashan/yasdb_back/bak_20241231234906_lev1_diff' PARALLELISM 2";
   yasql / as sysdba -c "alter database mount";
yasql / as sysdba -c "RESTORE ARCHIVELOG SEQUENCE BETWEEN 86 AND 91 THREAD 1 PARALLELISM 2 FROM SEARCHDIR '/home/yashan/yasdb_back/'";
   yasql / as sysdba -c "alter database mount";
yasql / as sysdba -c "RESTORE ARCHIVELOG SEQUENCE BETWEEN 54 AND 56 THREAD 2 PARALLELISM 2 FROM SEARCHDIR '/home/yashan/yasdb_back/'";
#--  如果 ?/archive 空间紧张，则可以在语句末尾加上 TO <destpath> 子句。参数 <destpath> 须使用真实存在，空间足够的路径代替
#--  备份目录已包含所有所需归档，无需手动注册其他归档，可以直接进行RECOVER DATABASE
#--  并行度参数 PARALLELISM 可选值[1,8]，默认为2 。
#--  还原归档前，可能需要手动切换数据库到mount状态，相关命令：alter database mount; 
#--  手动补充归档后，执行以下命令，应用归档：  -- 如实施完全恢复，使用：  RECOVER DATABASE;
yasql / as sysdba -c "RECOVER DATABASE UNTIL TIME TO_DATE('20241231235400','yyyymmddhh24miss')";
#--  打开数据库  -- 完全恢复使用此命令（或省去NORESETLOGS） ： yasql / as sysdba -c "ALTER DATABASE OPEN NORESETLOGS";
yasql / as sysdba -c "ALTER DATABASE OPEN RESETLOGS";
#--  如果无需应用任何归档，则跳过以上注册归档的过程，并执行以下命令生成下一步 recover database until 的命令：
yasql / as sysdba -c "select 'yasql / as sysdba -c \"'||'recover database until scn '||to_char(CURRENT_SCN+1)||';\"' from v\$database;"


[yashan@yasrac01 yasdb_back]$

按指引顺序，选取合适的命令，执行即可恢复到指定时间点。

备份错误巡检

使用 sh db_backuup.sh lasterr 3 显示最近的3次错误。

output.png

备份管理

使用 sh db_backup.sh cleanwith 1 清理1天之前的数据库备份

图片.png

脚本与配置说明

将脚本保存为 db_backup.sh 并配置脚本内置配置后即可进行测试。

配置说明可以参考脚本内的参数描述。
由于我们这里测试的是手工备份，所以我们仅需要配置数据库免密登录和备份保存目录即可。
另外我们使用了归档备份功能，所以还需要补充配置 cluster_name 和 sys_pwd 参数。



#!/bin/bash

# 版本 V23.2.4.100

#配置说明：
#修改 变量 backupdir 指向的目录
#修改 变量 backuplog 指向的文件
#修改 变量 full_backup_weekday -- 星期几进行全备
#修改 变量 diff_backup_weekday -- 星期几进行普通增备，如果不需要普通增备，则此变量括号留空  ()
#修改 变量 cumu_backup_weekday -- 星期几进行累积增备，如果不需要累积增备，则此变量括号留空  ()
#修改 变量 arch_backup_weekday -- 星期几进行归档增备，如果不需要归档备份，则此变量括号留空  ()
#可以根据情况修改 exit 0分支，去掉注释可以在未定义的时间跳过备份任务  
#    比如定义星期五当天不进行任何备份，那么应该在定义时，不定义周五的备份行为，并修改脚本，使得exit 0 生效。
#修改 变量 free_space_exit ，设置不为 1 可以在不满足空间建议要求时，强制进行备份，默认为1。
#修改 back_keep_days 变量为合适的天数（注意如果删除全备，之后的增备也会失效，如果要避免此种情况，可适当扩大相应值 back_keep_days ）
#设置合适的crontab命令
#进行备份测试，检查备份任务和备份文件是否有效。如不存在全备基线，则无法进行增备，全备可以参考此命令生成：sh backup.sh F
#使用 pitr_adviser 能力， full_pitr 必须设置为2，否则给出的提示命令在某些情形下不能恢复数据库到指定时间。
#备份数据库归档文件时，依赖yasboot工具和sys密码。且必须配置免密登录。


# Crontab Syntax
# * * * * * command(s)
# - - - - -
# | | | | |
# | | | | ----- Day of week (0 - 7) (Sunday=0 or 7)
# | | | ------- Month (1 - 12)
# | | --------- Day of month (1 - 31)
# | ----------- Hour (0 - 23)
# ------------- Minute (0 - 59)
# Cron Jobs Examples
# 0 15 * * 1-5 command                        Run a command at 15:00 on every day from Monday through Friday
# */5 * * * * /path/to/script.sh > /dev/null  Run a script every 5 minutes and redirected the standard output to dev null
# 30 0 * * 1 nohup sh /data/yasdb_backup/backup/db_backup.sh > /data/yasdb_backup/backup/full_backup.log &
# 30 0 * * * nohup sh /data/yasdb_back/backup/db_backup.sh &

source ~/.bashrc
dt=`date +%Y%m%d%H%M%S`


backupdir='/data/yasdb_back/backup'
#backupdir='/home/y222/yashandb/yasdb_back/backup'
backuplog='backup.log'

# 如果星期几出现了重复定义，脚本会选择第一次定义的变量对应的分支。
# 不需要的备份类型，留空即可，如 arch_backup_weekday=()
# 多个变量以空格分隔，如 diff_backup_weekday=(1 2 3 5 7)
# 使用内置配置进行备份时，会使用到这些参数，如 sh backup.sh
full_backup_weekday=(6)
diff_backup_weekday=(1 2 3 5 7)
cumu_backup_weekday=(4)
arch_backup_weekday=()


# F=full back ; D=diff incr ; C=cumu incr ; A=arch back; not define backup operation will be as the last element defined.
# 备份相关类型时，如果对应的备份顺序为空，会报错逻辑错误。
# 使用内置配置进行备份时，会使用到这些参数，如 sh backup.sh
full_day_backup_order=(F)
diff_day_backup_order=(D)
cumu_day_backup_order=(C)
arch_day_backup_order=(A)


# 备份保留天数
# 使用内置配置进行备份时，会使用到这些参数，如 sh backup.sh
back_keep_days=31

# 设置为1，空间不足时会直接退出，设置为0，则会继续备份行为
# 使用内置配置进行备份，以及手工进行备份调试，都受此参数控制。
free_space_exit=1

# 在配置了归档备份的前提下：
# 设置为0，只会备份数据库任何备份以来的部分归档，更节约空间
# 设置为1，会备份全备或者归档备份以来全部归档（PITR 范围更宽）；
# 设置为2，则归档备份会备份上次归档备份以来的所有归档（PITR范围最宽，也最占用空间）。
# 未配置归档备份时，此参数不影响备份行为。
# 使用内置配置进行备份，以及手工进行备份调试，都受此参数控制。（仅归档备份时会使用到此参数）
full_pitr=2

# 测试性参数
# 在配置了归档备份的前提下：在归档备份之后，是否删除 archive 文件夹中归档。设置为1，会清理此次归档备份备份过的归档；设置为0，则不会清理。
# 由于数据库当前暂不支持自定义归档清理范围，所以，设置清理归档后，在满足清理策略参数 ARCH_CLEAN_IGNORE_MODE 的前提下，
#     会清理 archive 文件夹中的全部归档。
# delete_input 选择2，则会自动考虑 ARCH_CLEAN_IGNORE_MODE ，在它为 BACKUP 和 BOTH 时，会清理归档，否则不进行清理。
# 使用内置配置进行备份，以及手工进行备份调试，都受此参数控制。（仅归档备份时会使用到此参数）
delete_input=2


# yasboot中的cluster名称。比如 yasboot cluster status -c yashandb -d ，那么名称 为 yashandb
# 使用内置配置进行备份，以及手工进行备份调试，都受此参数控制。（仅归档备份时会使用到此参数）
cluster_name='yashandb'

# sys 密码 ，如果密码是 yasdb_123 ，则配置为 'yasdb_123' ，不要使用单双引号作为密码。
# 注意，yac环境下，各个实例的sys密码可能不同，请配置各个节点密码相同。
# 使用内置配置进行备份，以及手工进行备份调试，都受此参数控制。（仅归档备份时会使用到此参数）
sys_pwd='`~!@#$%^&*()a1=+[]{}\|;:,<>/?'



#######################################################
# 定义脚本内部使用的变量与函数，一般无需修改以下的配置。
#######################################################
export today_back_order
export backup_lev
export path_str
export cumu_str
export exe_result
export backupdir=$backupdir
export backuplog=$backuplog
export bstr="Backup start time "
export estr="Backup end time "
export bs="^"$bstr
export es="^"$estr
export np=$(cd $(dirname "$0") && pwd -P)"/"
export cluster_name=$cluster_name
export sys_pwd=$sys_pwd


#旧备份清理
back_clean(){
echo "run backup clean task.inpara: $1 $2  " 
if [ "$1" = "cleanwith" ] && [ "$2" -ge "0" ] ; then
where_str_o=" START_TIME<sysdate-"$2
elif [ "$1" = "cleanfirst" ] && [ "$2" -gt "0" ] ; then
where_str_o=" START_TIME<sysdate ORDER BY START_TIME ASC LIMIT "$2
elif [ "$1" = "cleanlast" ] && [ "$2" -gt "0" ] ; then
where_str_o=" START_TIME<sysdate ORDER BY START_TIME DESC LIMIT "$2
elif [ "$1" = "cleandir" ] && [ "$2"z != "z" ] ; then
where_str_o=" REGEXP_LIKE(REGEXP_REPLACE(PATH,'/{2,}','/',1,0), RTRIM(REGEXP_REPLACE('"${backupdir}"','/{2,}','/',1,0),'/')||'/{1,}'||'"$2"') "
fi
echo "$(yasql -S / as sysdba  << EOF 
set serveroutput on
SET FEEDBACK ON
declare
cmd_str varchar(500);
cursor cmd_cur is select concat('BACKUP DATABASE DELETE BACKUPSET IF EXISTS TAG ','''',TAG,'''') from dba_backup_set where ${where_str_o};
begin
open cmd_cur;
loop
fetch cmd_cur into cmd_str;
exit when cmd_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('EXECUTING SQL: '||cmd_str||';');
execute immediate cmd_str;
end loop;
close cmd_cur;
end;
/
exit;
EOF
)" | tr -d '\n'
}


run_clean(){
exe_result=$(back_clean $1 $2)
echo "$exe_result" | tee -a ${backupdir}/${backuplog}
if [[ ! $exe_result =~ "Succeed." ]]; then
time_end
exit 1
fi
}



#备份空间确认
free_space_check(){
dbsize=$(yasql -S / as sysdba  << EOF
SET HEADING OFF
SET FEED OFF
select trunc((select sum(BLOCK_SIZE*BLOCK_COUNT)/1024 from v\$logfile) + (select sum(BYTES)/1024 from dba_data_files)) from dual;
exit;
EOF
)
dbsize=$(echo $dbsize)
max_bak_size=`du -k --max-depth=1 $backupdir | sort -h | tail -n 2 |head -n 1 |awk -F ' ' '{print $1}'`
avail_size=`df -k $backupdir |tail -n1|awk -F ' ' '{print $4}'`
max_bak_size=$(($max_bak_size + $max_bak_size))
if [[ ! $dbsize =~ ^[0-9]+$ ]]; then
echo $dbsize| tee -a ${backupdir}/${backuplog}
time_end
exit 1
fi
dbsize=$(($dbsize + 5242880))
# 5242880 KB = 5GB
# 10485760 KB = 10GB
# 需满足以下条件：
# 1，可用空间比最大备份集的2倍更大；
# 2，可用空间大于10GB；
# 3，可用空间比数据文件大小总和大5GB 。默认需满足以上3点才能触发备份动作。
if [ $avail_size -lt $max_bak_size ] || [ $avail_size -lt 10485760 ] || [ $avail_size -lt $dbsize ]
then
 echo "not enough backup space."| tee -a ${backupdir}/${backuplog}
 if [ "$free_space_exit" -eq "1" ];then
 time_end
 exit 1
 fi
else
 echo "enough backup space."| tee -a ${backupdir}/${backuplog}
fi
}




#备份类型确定
back_count(){
backup_count=$(echo $(yasql -S / as sysdba  << EOF
SET HEADING OFF
SET FEED OFF
select count(*) from dba_backup_set where START_TIME>=trunc(sysdate,'dd') and PATH like '${backupdir}%' ;
exit;
EOF
)| awk '{print $NF}')
if [[ $backup_count =~ ^[0-9]+$ ]]; then
echo $backup_count
fi
return
}
back_switch_choose() {  
    local index=$1  
    local order_length=${#today_back_order[@]}
	echo  "-- Today back order : ${today_back_order[@]} -- exist back count :  $1"  >> ${backupdir}/${backuplog}
	if [ "0" -eq "$order_length" ]||[ "$index""z" == "z" ]; then  
	echo "input parameter logic error." >> ${backupdir}/${backuplog}
	return
	fi
    if [ "$index" -ge 0 ] && [ "$index" -lt "$order_length" ]; then  
        echo "${today_back_order[$index]}"  
    else  
        echo "${today_back_order[$((order_length-1))]}"  
    fi  
}  
back_dbf(){
echo "$(yasql -S / as sysdba << EOF 
set serveroutput on 
SET FEEDBACK ON 
declare 
cmd_str varchar(500):='BACKUP DATABASE INCREMENTAL LEVEL $backup_lev $cumu_str format ''${backupdir}/bak_${dt}_lev${backup_lev}_${path_str}''';
begin 
DBMS_OUTPUT.PUT_LINE('EXECUTING SQL: '||cmd_str||';');
execute immediate cmd_str;
execute immediate 'alter system switch logfile';
end;
/ 
exit;
EOF
)" | tr -d '\n'
}

arc_all(){
echo "$(echo $(yasboot cluster status -c $cluster_name -d)|awk '{{vl=$0;gsub(/-{2,}/,"+",vl);gsub(/\+{1,}/,"+",vl);split(vl,l,/\+/)} } END{for (a in l){if (length(l[a])!=0) {print l[a]}}}'|awk '
BEGIN {"echo ${sys_pwd}"|getline sp;"echo ${cluster_name}"|getline cn;}
{l=$0;gsub(" ","",l);lc=split(l,ll,"|");for (i=1;i<=lc;i++) 
if(NR==1){if (ll[i]=="nodeid"){o=i};if (ll[i]=="instance_status"){p=i};if (ll[i]=="database_status"){q=i};
if (ll[i]=="database_role"){r=i}};if(NR>1&&ll[p]=="open"&&ll[q]=="normal"&&ll[r]=="primary")
{l1=substr(ll[o],1,index(ll[o],":")-1);print "yasboot sql -n "l1" -c "cn" -s '\''ALTER SYSTEM ARCHIVE LOG CURRENT'\'' -u sys -p '\''""\""sp"\"""'\''"";echo $?"}}'|sh |grep -v 'Succeed'|grep -v ^$ | tr -d '\n')"
}


back_arc(){
r=$(arc_all)
if [[ $r =~ ^[0-9]+$ && $(($r)) == 0 ]];then
echo "$(yasql -S / as sysdba << EOF 
SET SERVEROUTPUT ON 
SET FEEDBACK ON 
DECLARE 
TYPE REC IS RECORD(INST_ID INTEGER,N_B INTEGER,B_B INTEGER,B_E INTEGER);
rec_1 REC;
cursor cmd_cur is select a.inst_id,case when (c.SEQUENCE_END#+1) <= b.back_end then 1 else 0 end as nb,
case (select count(*) from GV\$DATABASE) when (select count(*) from V\$DATABASE) then c.SEQUENCE_END#+1 else 
case c.TYPE when 'INCREMENTAL' then c.SEQUENCE_END#+1 else c.SEQUENCE_END# end end as bb,b.back_end as be from 
(select a.inst_id,case ${full_pitr} when 2 then nvl(nvl(a.MA,a.MF),a.MI) when 1 then nvl(a.MF,a.MI) when 0 then a.MI end as bt from 
(select a.inst_id,max(b.START_TIME) as ma,(select max(b.START_TIME) from dba_backup_set b where b.type='INCREMENTAL' and 
b.INCREMENT_LEVEL=0) as mf,(select max(b.START_TIME) from dba_backup_set b where b.type='INCREMENTAL' and b.INCREMENT_LEVEL=1) as mi 
from gv\$database a left join DBA_ARCHIVE_BACKUPSET b on a.inst_id=b.INSTANCE_NUMBER# and b.type='ARCHIVE' group by a.inst_id) a) a 
JOIN (SELECT THREAD#,MAX(SEQUENCE#) back_end FROM V\$ARCHIVED_LOG GROUP BY THREAD#) b ON a.inst_id=b.THREAD# left join 
DBA_ARCHIVE_BACKUPSET c on c.START_TIME=a.bt and a.inst_id=c.INSTANCE_NUMBER# ; 
cmd_str clob;
temp_str clob;
skip_str clob:='';
TYPE cln_tab IS TABLE OF clob INDEX BY INTEGER;
v_cln_tab cln_tab;
begin 
open cmd_cur;
loop 
fetch cmd_cur into REC_1;
exit when cmd_cur%NOTFOUND;
begin 
select 'BACKUP ARCHIVELOG SEQUENCE BETWEEN '|| rec_1.B_B || ' AND '|| rec_1.B_E ||' THREAD '|| rec_1.INST_ID || 
' FORMAT ''${backupdir}/bak_${dt}_lev${backup_lev}_${path_str}_inst_'|| rec_1.INST_ID ||'''' into cmd_str FROM dual where rec_1.N_B=1;
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('EXECUTING SQL: '||cmd_str||';');
execute immediate cmd_str;
EXCEPTION 
WHEN NO_DATA_FOUND THEN 
null;
end;
begin 
SELECT 'ALTER DATABASE DELETE ARCHIVELOG UNTIL SEQUENCE '||LEAST(rec_1.B_E,
(select SPLIT(c.RCY_POINT,'-',2)-1 from GV\$DATABASE c where c.INST_ID=rec_1.INST_ID),
ifnull((select min(d.RECEIVED_SEQ#) from GV\$ARCHIVE_DEST_STATUS d where d.INST_ID=rec_1.INST_ID),rec_1.B_E)) 
||' THREAD '||rec_1.INST_ID into temp_str FROM dual where case ${delete_input} when 2 then (select 
(SELECT VALUE from V\$PARAMETER where name ='ARCH_CLEAN_IGNORE_MODE') in ('BACKUP','BOTH') from dual) when 1 then TRUE end 
and rec_1.N_B=1;
v_cln_tab(v_cln_tab.count()+1):=temp_str;
EXCEPTION 
WHEN NO_DATA_FOUND THEN 
null;
end;
begin 
select NVL2(skip_str,skip_str||';','')||'backup set '||a.TAG ||' include '||'instance number '||a.INSTANCE_NUMBER#||' sequence '|| a.SEQUENCE_END#  || '(last sequence '||rec_1.B_E||' )' into skip_str from DBA_ARCHIVE_BACKUPSET a where rec_1.N_B=0 and rec_1.B_B-1 between SEQUENCE_BEGIN# and SEQUENCE_END# and rec_1.INST_ID=INSTANCE_NUMBER# order by case when TYPE='ARCHIVE' then 1 else 0 end desc,a.START_TIME desc limit 1;
EXCEPTION 
WHEN NO_DATA_FOUND THEN 
null;
end;
end loop;
IF v_cln_tab.count() > 0 and ${delete_input} >= 1 THEN 
for i in 1..v_cln_tab.count() LOOP 
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('EXECUTING SQL: '||v_cln_tab(i)||';');
execute immediate v_cln_tab(i);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE(skip_str);
close cmd_cur;
end;
/ 
exit;
EOF
)" | tr -d '\n'
else
echo 'switch logfile failed'
fi

}
back_para_gen_run(){
if [[ "$1" == "F" ]]; then
export backup_lev=0
export path_str='full'
export cumu_str=''
back_dbf
save_spfile
elif [[ "$1" == "D" ]]; then
export backup_lev=1
export path_str='diff'
export cumu_str=''
back_dbf
save_spfile
elif [[ "$1" == "C" ]]; then
export backup_lev=1
export path_str='cumu'
export cumu_str='CUMULATIVE'
back_dbf
save_spfile
elif [[ "$1" == "A" ]]; then
export backup_lev=0
export path_str='arch'
export cumu_str=''
back_arc
else
echo "input value is not a valid backup type : $1"
fi
}
run_back(){
if [[ "${full_backup_weekday[@]}" =~ "`date +%u`" ]]; then
today_back_order=("${full_day_backup_order[@]}")
exe_result=$(back_para_gen_run $(back_switch_choose $(back_count)))
elif [[ "${diff_backup_weekday[@]}" =~ "`date +%u`" ]]; then
today_back_order=("${diff_day_backup_order[@]}")
exe_result=$(back_para_gen_run $(back_switch_choose $(back_count)))
elif [[ "${cumu_backup_weekday[@]}" =~ "`date +%u`" ]]; then
today_back_order=("${cumu_day_backup_order[@]}")
exe_result=$(back_para_gen_run $(back_switch_choose $(back_count)))
elif [[ "${arch_backup_weekday[@]}" =~ "`date +%u`" ]]; then
today_back_order=("${arch_day_backup_order[@]}")
exe_result=$(back_para_gen_run $(back_switch_choose $(back_count)))
else
:
#  exit 0 解除注释后，可以在未指定的备份时间跳过备份任务。也可以通过增加语句来修改默认行为。
time_end
exit 0
fi
}

check_back(){
if [[ $exe_result =~ "Succeed." ]];
then
echo "Backup successfully." | tee -a ${backupdir}/${backuplog}
echo "$exe_result" | tee -a ${backupdir}/${backuplog}
else
echo "Backup failed." | tee -a ${backupdir}/${backuplog}
echo "$exe_result" | tee -a ${backupdir}/${backuplog}
time_end
exit 1
fi
}
save_spfile(){
cp ${YASDB_DATA}/config/yasdb.ini ${backupdir}/bak_${dt}_lev${backup_lev}_${path_str}/yasdb.ini  > /dev/null 2>&1 
}
time_begin(){
echo  ${bstr}`date +'%Y-%m-%d %H:%M:%S'` | tee -a ${backupdir}/${backuplog}
}
time_end(){
echo -e ${estr}`date +'%Y-%m-%d %H:%M:%S'`"\n" | tee -a ${backupdir}/${backuplog}
}
helper_func(){
cat << EOF
#
#
#使用说明：
# A、使用内置参数进行备份
#      1. 配置脚本中的内置参数，环境变量为适当值。
#      2. 使用crontab调用，或者手工执行 sh backup.sh 
# B、手工进行备份调试
#      1. 配置脚本中的内置参数，环境变量为适当值。
#      2. 进行备份调试
#             sh backup.sh F     #立即进行数据库全备
#             sh backup.sh C     #立即进行数据库累积增备
#             sh backup.sh D     #立即进行数据库差异增备
#             sh backup.sh A     #立即进行数据库归档备份
# C、手工清理备份
#         sh db_backup.sh cleanwith 3
#              # 清理3天之前备份的所有备份，支持小数入参和0入参；
#                可以使用cleanwith 0.1 清理2.4小时前开始的所有备份；
#                cleanwith 0 清理数据库所有备份
#         sh db_backup.sh cleanfirst 3
#              # 清理掉最旧的3个备份，清理范围：整个数据库
#         sh db_backup.sh cleanlast 3
#              # 清理掉最新的3个备份，清理范围：整个数据库
#         sh db_backup.sh cleandir dirname
#              # 清理掉备份目录下的特定备份文件夹。dirname 使用实际文件夹名称代替。
#                只会在脚本配置目录清理特定文件夹，其他路径下的同名文件夹不会被清理。
# D、使用还原提示助手
#         sh db_backup.sh pitr 20250102112233
#              # 根据备份情况，生成还原到 2025年1月2日11时22分33秒的命令提示。
#
#
#
EOF
:
}


dbf(){
awk 'BEGIN {"echo ${bs}"|getline bs;"echo ${es}"|getline es;"echo ${rp}"|getline rp;"cat ${np}/${backuplog}|wc -l"|getline tl;sp="0"} bs,es {
if ($0 ~ bs) {s_s = substr($0,length(bs)); gsub("-","",s_s);  gsub(" ","",s_s);gsub(":","",s_s);s_p=NR;next} 
if ($0 ~ /BACKUP DATABASE INCREMENTAL LEVEL/ && $0 ~ /^EXECUTING SQL/ && $0 ~ /PL\/SQL Succeed\.$/ && index($0,fs) > 0) 
{d_p = substr(substr($0,index($0,"format")+7),1,index(substr($0,index($0,"format")+7),";")-1);mh=1;next}
if ($0 ~ es && mh == 1 ) {e_s = substr($0,length(es));gsub("-","",e_s);  gsub(" ","",e_s);gsub(":","",e_s);
if ( index(d_p,fs)>0 && (e_s+0)<(rp+0) && (s_s+0)>(sp+0) && fs!="_lev1_diff" ) {s_t=s_s;d_t=d_p;e_t=e_s;p_t=s_p}; 
if ( index(d_p,fs)>0 && (e_s+0)<(rp+0) && (s_s+0)>(sp+0) && fs=="_lev1_diff" ) {path[s_s]=d_p;end[s_s]=e_s;posi[s_s]=s_p;sp=e_s};mh=0;next}
if (NR >= tl - 1 && fs != "_lev1_diff") {path[s_t]=d_t;end[s_t]=e_t;posi[s_t]=p_t;sp=e_t}
} END {for (a in path){rc++};i=1;for (a in path) {print a,end[a],path[a],i,rc,posi[a];i++}
}' fs="_lev0_full" ${np}/${backuplog} fs="_lev1_cumu" ${np}/${backuplog} fs="_lev1_diff" ${np}/${backuplog}
}

arc(){
awk 'BEGIN {"echo ${bs}"|getline bs;"echo ${es}"|getline es;"echo ${rp}"|getline rp;i=1;k=1;f=-1;"echo ${me}"|getline me} bs,es {
if ($0 ~ bs) {s_s = substr($0,length(bs)); gsub("-","",s_s);  gsub(" ","",s_s);gsub(":","",s_s);s_p=NR} 
if ($0 ~ /BACKUP ARCHIVELOG SEQUENCE / && $0 ~ /^EXECUTING SQL/ && $0 ~ /PL\/SQL Succeed\.$/ && $0 ~ /_lev0_arch_inst_/) 
{d_p = $0;gsub("PL/SQL Succeed.","",d_p);d_m=1;split(d_p,mpath,"EXECUTING SQL:");
for (m in mpath) {if (length(mpath[m])==0 || mpath[m] ~ /ALTER DATABASE DELETE ARCHIVELOG UNTIL SEQUENCE/) {delete mpath[m]} else {gsub(";","",mpath[m])}}}
if ($0 ~ es && d_m == 1 ) {e_s = substr($0,length(es));gsub("-","",e_s);  gsub(" ","",e_s);gsub(":","",e_s);
for (m in mpath) {apath[i]=mpath[m];ss[i]=s_s;end[i]=e_s;posi[i]=s_p;i++;d_m=0}}
} END {for (a in apath){rc++};for (a in apath) {if ((ss[a]+0)>=(me+0) && (f<0 || (ss[a]+0)<=(f+0))) {print k,rc,ss[a],end[a],apath[a],posi[a];k++;if ((ss[a]+0)>(rp+0)){f=ss[a]}}};if (f==1){print 0,-1}
}' ${np}/${backuplog} 
}

rec(){
fp=$(dbf|sort -k1|awk 'BEGIN{t="'\''"} {if (NR==1){l=$3;gsub(t,"",l);n=split(l,a,"/");print a[n]}}')
if [ ! -e "$np/$fp/backup_profile" ]; then
echo '当前目录下，获取全备信息错误：找不到全备或者全备损坏'
exit 0
fi
strings $np/$fp/backup_profile |egrep "^[+,/]" |awk 'BEGIN{rn=1;r1=0;"echo ${YASDB_DATA}"|getline yd;"echo ${backupdir}"|getline bd;"echo ${np}"|getline np;sub(/\/$/,"",bd);sub(/\/$/,"",np)} 
{if(NR==1){next};if(match($0,"/.{1,}/")!=0) {if(rn==1 && substr($0,RSTART+RLENGTH)=="sysaux"){a[1]=substr($0,1,RSTART+RLENGTH-2);rn++};
if(rn>1){if(substr($0,1,RSTART+RLENGTH-2)==a[rn-1]){next}};a[rn]=substr($0,1,RSTART+RLENGTH-2);rn++}
} END{if(a[rn-2]==""){a[rn-2]=a[rn-1]}; if (a[1]==yd"/dbfiles" && a[rn-2]==yd"/dbfiles" && a[rn-1]==yd"/local_fs" && np==bd)
{print "#--  检测到同机恢复，无需设置db_file_name_convert等参数，应用累积增备和差异增备时，可直接应用最后一个"} 
else {print "#--  检测到异机恢复或恢复到YAC。须设置db_file_name_convert等参数";
print "yasql / as sysdba -c ""\"""alter system set db_file_name_convert='\''"a[1]"'\',''\''"yd"/dbfiles'\'' scope=both""\""";";
print "yasql / as sysdba -c ""\"""alter system set redo_file_name_convert='\''"a[rn-2]"'\',''\''"yd"/dbfiles'\'' scope=both""\""";";
print "yasql / as sysdba -c ""\"""alter system set db_bucket_name_convert='\''"a[rn-1]"'\',''\''"yd"/local_fs'\'' scope=both""\""";";
print "#--  如果要将数据库恢复到共享集群，可以将共享集群convert参数路径设置为： +DG0/dbfiles/ (以DG0为例)";
print "#  yasql / as sysdba -c ""\"""alter system set db_file_name_convert='\''"a[1]"'\',''\''""+DG0/dbfiles'\'' scope=both""\""";";
print "#  yasql / as sysdba -c ""\"""alter system set redo_file_name_convert='\''"a[rn-2]"'\',''\''""+DG0/dbfiles'\'' scope=both""\""";";
print "#  yasql / as sysdba -c ""\"""alter system set db_bucket_name_convert='\''"a[rn-1]"'\',''\''""+DG0/dbfiles'\'' scope=both""\""";"
}
}'
}


pitr_adviser(){
export rp=$1
export me=$(dbf|awk 'BEGIN{e="0"} {if ($2>e){e=$2}} END{print e}')
if [ "$YASDB_DATA"z = "z" ] ; then
echo '#-- 环境变量$YASDB_DATA未配置 '
exit 0
fi
if [ "$me" -le "1" ]; then   
echo ''
echo ''
echo "#--  匹配不到可用的全备，No available full backup"
echo ''
echo ''
exit 0
fi
echo ''
echo ''
rec
echo '#--  请在操作前设置好数据库相关环境变量：$YASDB_HOME $YASDB_DATA $PATH $LD_LIBRARY_PATH'
echo "#--  清理 rm -rf $YASDB_DATA/dbfiles/* 下的所有文件（建议保留redo文件），并将单实例启动到nomount状态下，准备应用全备。"
echo "#--  同机恢复做rm操作时，注意保留redo文件，redo文件可能需要用来进行recover。"
echo "#--  如需应用累积增备，可能需要关闭数据库并重新启动到nomount，相关命令：shutdown immediate;  yasdb nomount &  "
echo "#--  yasboot cluster restart -c $cluster_name -m nomount   ## ycsctl stop instance "
echo '#--  yasql / as sysdba -c "shutdown immediate;";  '
dbf|sort -k8|awk 'BEGIN {"echo ${np}"|getline np} {p=$3;n=split(p,a,"/");
if (p ~ /_lev0_full/) {print "#--  如有需要，可以使用全备'\''"np""a[n]"路径下的spfile文件";print "   nohup yasdb nomount &";
print "yasql / as sysdba -c ""\"""RESTORE DATABASE FROM '\''"np""a[n]" PARALLELISM 2""\""";"} 
if (p ~ /_lev1_cumu/) {print "   yasql / as sysdba -c ""\"""shutdown immediate""\""";";print "   nohup yasdb nomount &";
print "yasql / as sysdba -c ""\"""RESTORE DATABASE INCREMENTAL FROM '\''"np""a[n]" PARALLELISM 2""\""";"}
if (p ~ /_lev1_diff/) {print "yasql / as sysdba -c ""\"""RESTORE DATABASE INCREMENTAL FROM '\''"np""a[n]" PARALLELISM 2""\""";"}} '
arc |awk 'BEGIN {"echo ${np}"|getline np} {a[$1]=$0;if($2!=-1){m=$2}else{l=1}} END{rc=m+l;n=1;
for (i in a) {cn=split(a[i],b," ");lb=b[13]" x";hb=b[13]" y";if(cn>2){if(c[lb]>b[9]||c[lb]==0){c[lb]=b[9]};if(c[hb]<b[11]||c[hb]==0){c[hb]=b[11]}};
if(cn==2){ne=1};delete a[i]};mi=1;
for (i=1;i<=mi;i++){for (j in c) {split(j,z," ");if (z[1]==i && z[2]=="x"){lb=c[j]};if (z[1]==i && z[2]=="y"){hb=c[j]};ii=i;if(mi<z[1]){mi=z[1]}};
print "   yasql / as sysdba -c ""\"""alter database mount""\""";";
print "yasql / as sysdba -c ""\"""RESTORE ARCHIVELOG SEQUENCE BETWEEN "lb" AND "hb" THREAD "ii" PARALLELISM 2 FROM SEARCHDIR '\''"np"'\''""\""";"};
print "#--  如果 ?/archive 空间紧张，则可以在语句末尾加上 TO <destpath> 子句。参数 <destpath> 须使用真实存在，空间足够的路径代替"
if(ne==1){print "#--  备份目录未包含PITR终点的所有归档，请手动将需要补充的归档或者redo放到 ?/archive 以及 ?/dbfiles 目录下。"}
else {print "#--  备份目录已包含所有所需归档，无需手动注册其他归档，可以直接进行RECOVER DATABASE"};
print "#--  并行度参数 PARALLELISM 可选值[1,8]，默认为2 。";
print "#--  还原归档前，可能需要手动切换数据库到mount状态，相关命令：alter database mount; "
}'
rp=$(echo $rp|awk '{printf("%014d\n",$0)}')
echo '#--  手动补充归档后，执行以下命令，应用归档：  -- 如实施完全恢复，使用：  RECOVER DATABASE;'
echo 'yasql / as sysdba -c "'"RECOVER DATABASE UNTIL TIME TO_DATE('"${rp}"','yyyymmddhh24miss')"'";'
echo '#--  打开数据库  -- 完全恢复使用此命令（或省去NORESETLOGS） ： yasql / as sysdba -c "ALTER DATABASE OPEN NORESETLOGS";'
echo 'yasql / as sysdba -c "ALTER DATABASE OPEN RESETLOGS";'
echo '#--  如果无需应用任何归档，则跳过以上注册归档的过程，并执行以下命令生成下一步 recover database until 的命令：'
echo 'yasql / as sysdba -c "select '"'"'yasql / as sysdba -c \"'"'||'recover database until scn '||to_char(CURRENT_SCN+1)||';"'\"'"'"' from v\$database;"'
echo ''
echo ''
}

lasterr(){
export c=$1
awk 'BEGIN{f=0;m=1;n=1;h=1;o=1;"echo ${c}"|getline c} {
if ($0 ~ /^Backup start time /) {a[m]=$0;m++;f=1;next};
if (f==1 && $0!="") {a[m]=$0;m++;if($0 ~ /Backup successfully./){h=0}};
if (f==1 && $0 ~ /^Backup end time /) {a[m]="";m++;f=0;if(a[m-3]~/Succeed\.$/){h=0};
if(h==1){n=m;c--}else{m=n;h=1};
while (c<0) {delete a[o];o++;if(a[o]==""){delete a[o];o++;c++}}}
}END{k=1;for (i=o;i<n;i++){if (i==o){print "ERROR "k;k++};print "  "a[i];if (a[i]=="" && i+1!=n){print "ERROR "k;k++}}}' ${np}/${backuplog}
}

backdirchk(){
if [ -d "$backupdir" ]; then
:
else
echo "备份目录 ${backupdir} 不存在"
exit
fi
}


if [ "$#" -eq 0 ]; then
backdirchk
time_begin
run_clean cleanwith $back_keep_days
free_space_check
run_back 
check_back
time_end
elif [ "$#" -eq 1 ]; then
if [ "$1" = "F" ] || [ "$1" = "D" ] || [ "$1" = "C" ] || [ "$1" = "A" ]; then
backdirchk
time_begin
#run_clean cleanwith $back_keep_days
free_space_check
exe_result=$(back_para_gen_run $1)
check_back
time_end
else
helper_func
fi
elif [ "$#" -eq 2 ]; then
if [ "$1" = "cleanwith" ] && [ "$2" -ge "0" ] ; then
time_begin
run_clean cleanwith $2
time_end
elif [ "$1" = "cleanfirst" ] && [ "$2" -gt "0" ] ; then
time_begin
run_clean cleanfirst $2
time_end
elif [ "$1" = "cleanlast" ] && [ "$2" -gt "0" ] ; then
time_begin
run_clean cleanlast $2
time_end
elif [ "$1" = "cleandir" ] && [ "$2"z != "z" ] ; then
backdirchk
time_begin
run_clean cleandir $2
time_end
elif [[ "$1" = "pitr" && $2 =~ ^[0-9]+$ ]] ; then
pitr_adviser $2
elif [[ "$1" = "lasterr" && $2 =~ ^[0-9]+$ ]] ; then
lasterr $2
else
helper_func
fi
else
helper_func
:
fi

使用示例

备份数据库

[yashan@yasrac01 yasdb_back]$ sh db_backup.sh F
Backup start time 2024-12-31 23:48:42
enough backup space.
Backup successfully.
EXECUTING SQL: BACKUP DATABASE INCREMENTAL LEVEL 0  format '/home/yashan/yasdb_back/bak_20241231234842_lev0_full';PL/SQL Succeed.
Backup end time 2024-12-31 23:48:47

[yashan@yasrac01 yasdb_back]$ sh db_backup.sh C
Backup start time 2024-12-31 23:48:54
enough backup space.
Backup successfully.
EXECUTING SQL: BACKUP DATABASE INCREMENTAL LEVEL 1 CUMULATIVE format '/home/yashan/yasdb_back/bak_20241231234854_lev1_cumu';PL/SQL Succeed.
Backup end time 2024-12-31 23:48:59

[yashan@yasrac01 yasdb_back]$ sh db_backup.sh D
Backup start time 2024-12-31 23:49:06
enough backup space.
Backup successfully.
EXECUTING SQL: BACKUP DATABASE INCREMENTAL LEVEL 1  format '/home/yashan/yasdb_back/bak_20241231234906_lev1_diff';PL/SQL Succeed.
Backup end time 2024-12-31 23:49:12

[yashan@yasrac01 yasdb_back]$ 
[yashan@yasrac01 yasdb_back]$ 
[yashan@yasrac01 yasdb_back]$ sh db_backup.sh A
Backup start time 2024-12-31 23:54:01
enough backup space.
Backup successfully.
EXECUTING SQL: BACKUP ARCHIVELOG SEQUENCE BETWEEN 54 AND 56 THREAD 2 FORMAT '/home/yashan/yasdb_back/bak_20241231235401_lev0_arch_inst_2';EXECUTING SQL: BACKUP ARCHIVELOG SEQUENCE BETWEEN 86 AND 91 THREAD 1 FORMAT '/home/yashan/yasdb_back/bak_20241231235401_lev0_arch_inst_1';PL/SQL Succeed.
Backup end time 2024-12-31 23:54:04

[yashan@yasrac01 yasdb_back]$ sh db_backup.sh A
Backup start time 2024-12-31 23:54:10
enough backup space.
Backup successfully.
EXECUTING SQL: BACKUP ARCHIVELOG SEQUENCE BETWEEN 91 AND 92 THREAD 1 FORMAT '/home/yashan/yasdb_back/bak_20241231235410_lev0_arch_inst_1';EXECUTING SQL: BACKUP ARCHIVELOG SEQUENCE BETWEEN 56 AND 57 THREAD 2 FORMAT '/home/yashan/yasdb_back/bak_20241231235410_lev0_arch_inst_2';PL/SQL Succeed.
Backup end time 2024-12-31 23:54:13

恢复数据库的命令提示

注意，由于我拿到的版本暂时未实现数据库增量备份的恢复，所以下面的演示，只演示了全量备份和归档备份的使用。（在支持增量备份的新版本上，可以使用同样的脚本，进行增量备份的恢复）

图片.png

实施数据库恢复

注意，这里省去了环境清理相关命令，比如 rm +DG0/dbfiles/system 等清理命令，自行补充即可。

图片1.png

实施完成后可以看到共享集群数据库实例恢复正常。

yashandb体验官yashandb
最后修改时间：2025-01-02 10:26:32
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论