**导读**
> 作者:杨漆
> 16年关系型数据库管理,从oracle 9i 、10g、11g、12c到Mysql5.5、5.6、5.7、8.0 到TiDB获得3个OCP、2个OCM;运维路上不平坦,跌过不少坑、熬过许多夜。把工作笔记整理出来分享给大伙儿,希望帮到大家少走弯路、少熬夜。
提问:
如果您的生产环境为3节点的Rac+ASM,10T以上数据,业务对主库有极高性能要求,不能占用主库资源、通道、带宽,且主库归档日志每4小时会定时删除一次(归档存储空间有限,且无法扩容),现有备库上的归档每小时不定期自动删除(第三方软件,且要求不能停),在这种极端情况下怎样才能再新部署一套Adg ?
答:
1.Duplicate Target?方式是彻底无望了!
2.传统的Rman备份出全量数据传输到备库后启动Adg从主库自动拉归档方式也不可能(10T数据备份时间?+?传输时间?+?恢复时间?> 4小时)
3.Rman备份出全量数据传到备库恢复,备份同时主库定时转储归档传到备库register (主库当前环境为三节点Rac,归档存放在ASM上,你能知道归档的正确顺序,并按顺序正确register到新的dg端?)
4.Rman备份出全量数据传到备库恢复,备份同时从现有一备库端定时转储归档传到备库register(第三方软件每小时内不定期删除归档日志,你怎么确保高频产生的归档全部转储成功,一个不丢失不损坏?)
以上的四种方案全无法通过!
可上级的要求无法违背。怎样在这样极端恶劣的环境下新部署一套Adg库?
解决方案如下:
1.在主库端制作standby控制文件
2.在现有备库端备份全量数据
3.搭建第二新备库并用备库端传来的全量数据恢复
4.read only方式开打新备库读出Gap点的scn
5.根据Gap点scn在旧有备库上做好增量备份
6.用增备恢复第二新备库
7.启用dg让新备库自动抓取主库端4小时内的归档,恢复追平主库
8.启动Adg验证有效性
步骤:
? ?1.保持与主库操作系统一致,在Adg Server上部署Redhat7.2的OS,本机存储空间分配足够(20T),保证主备库间带宽畅通;
? ?2.在standby部署好Oracle12C的software、空库并启动到nomount状态?;
? ?3.将旧有备库上的密码文件、静态参数文件传输到新的Adg端(参数文件根据主/备环境做对应修改配置);
? ?4.选择非业务高峰时段对主库spfile用命令动态修改;
? ?5.在主库上创建备库控制文件并传输到备库对应目录;
? ?6.旧备库外挂NAS存储(移动式),rman备份完全量数据后将此NAS快速搬迁到异地机房并挂载到新备库端;
? ?7.用主库制作的standby controlfile启动新备库到mount状态;
? ?8.用外挂NAS存储上的全量备份集恢复新备库;
? ?9.只读方式开打新备库读出Gap点的scn(找最小点的scn);
? ?10.根据Gap点scn在旧有备库上做好增量备份,用增备恢复第二新备库;
? ?11.启用dg让新备库自动抓取主库端4小时内的归档,恢复追平主库;
? ?12.启动Adg?验证主、备库数据一致性;
##################?以下为具体执行手顺
主库执行:
TNS:
orcldg2 =
? (DESCRIPTION =
? (ADDRESS_LIST =
? (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.5)(PORT = 1521))
? )?
?(CONNECT_DATA =
?(SERVICE_NAME = orcldg2)
?(UR=A)
?)
?)
#!/bin/bash
sqlplus `sys/ as sysdba`>result.log<<EOF
##alter database create standby controlfile as '/home/oracle/control01_sty.ctl';
alter system set log_archive_config='DG_CONFIG=(orcl,orcldg1,orcldg2)';
alter system set log_archive_dest_4='SERVICE=orcldg2 LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg2' scope=both sid='*';
alter system set log_archive_dest_state_4=enable;
alter system set standby_file_management='AUTO';
exit
EOF
##在主库上创建备库控制文件并传输到备库对应目录?
sqlplus `sys/ as sysdba`>result.log<<EOF ? ?
alter database create standby controlfile as '/mnt/backup/control01_sty.ctl'
exit
EOF
scp -r /mnt/backup/control01_sty.ctl ? 10.10.10.18:/home/oracle/
##?全备Standby库+归档、并行
旧备库端执行:
su - oracle
vi rman_database_backup.sh
#!/bin/sh
echo ?"start full database backup !"
rman target / ? log /mnt/backup/datbase_backup.log <<EOF
run {
configure channel device type disk format '/mnt/backup/full_%U_%d';
configure device type disk parallelism 10; ? ? ? ? ? ? ? ? ? ? ? ? ? ?##?自动分配10路并行备份通道,无需再手动指定
delete backupset all completed before 'sysdate-7';
crosscheck archivelog all;
delete noprompt expired archivelog all;
backup database plus archivelog;
}
exit;
EOF
echo ?"finished full database backup !!!"
##备份集的实际路径、备份集名需按实际情况调整
scp -r ?/mnt/backup/full_database ? 10.10.10.18:/u01/backup/full_database
##?全量恢复
新备库执行:
rman target / log /home/oracle/rman_recover.log<<END
run{?
startup nomount;
restore controlfile from '/home/oracle/control01_sty.ctl';
sql 'alter database mount standby database';
catalog start with '/mnt/backup/'; ? ? ? ? ##备份集的实际路径、备份集名需按实际情况调整?? ? ? ? ??
configure device type disk parallelism 10;
restore database;
recover database;
}?
exit;
END
###基于gap点拉增量备份
select * from v$archive_gap;
##查看standby端最小?scn
col min(checkpoint_change#) for 999999999999
col current_scn for 999999999999
set numwidth 20
## select min(checkpoint_change#) ?from ?v$datafile_header; ?##?两条sql?二选一就好,用下面这条更省力
select min(fhscn) from x$kcvfh;
MIN(FHSCN)
--------------------
20172508017
select current_scn from v$database;
? CURRENT_SCN
-------------
? 20203151960
选择较小SCN号(20172508017)去主库拉增备
##?查出主库在standby端current_scn后产生的新数据文件,根据文件号(file#)在主库端做Rman数据文件备份
## select file#,name from v$datafile where creation_change#>=(select current_scn from v$database); ?##?极端情况下使用,大多数时候不用这样做
##################################?拉增备的scn号用下面查出来最小的号
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
--------------
2164433
SQL> select min(checkpoint_change#) from v$datafile_header
where file# not in (select file# from v$datafile where enabled = 'READ ONLY');
MIN(F.FHSCN)
----------------
2162298
comment:上面一个为控制文件中记录的SCN号,另一个为数据文件头记录的SCN号,?我们需要选择较小SCN号(2162298)的来备份。
###################################
##?主库上再次制作standby控制文件
主库执行:
sqlplus `sys/ as sysdba`>result.log<<EOF ? ?
alter database create standby controlfile as '/mnt/backup/control02_sty.ctl'
exit
EOF
##将主库上新创建的备库控制文件传输到新备库对应目录下(10.10.10.18使用)?
scp -r /mnt/backup/control02_sty.ctl ? 10.10.10.18:/home/oracle/
##?在旧备库拉增量+归档、并行
旧备库执行:
su - oracle
vi rman_increment_DB_backup.sh
#!/bin/sh
echo ?"start increment DB backup !"
rman target / ? log /mnt/backup/increment_backup.log <<EOF
run {
configure channel device type disk format '/mnt/backup/incre_dir/incre_DB_%U_%d';
configure device type disk parallelism 10;
crosscheck archivelog all;
delete noprompt expired archivelog all;
## backup as compressed backupset datafile 5; ? ##?极端情况下才拉单独的datafile备份
## backup current controlfile for standby format '/mnt/backup/incre_dir/control02_sty.ctl';
## backup as compressed backupset INCREMENTAL from scn 20172508017 database include current controlfile for standby; ? ?## ASM的RAC端拉会报错,仅适用非ASM
backup as compressed backupset INCREMENTAL from scn 20172508017 database plus archivelog; ? ? ? ? ? ## standby端?MIN(FHSCN)
}
exit
EOF
echo ?"finished increment DB backup !!!"
##传输增量备份到standby端(备份集的路径、名字需按实际情况调整)
scp -r ?/mnt/backup/incre_dir/incre_DB_xxx ? 10.10.10.18:/home/oracle/
新备库端执行:
rman target / log /home/oracle/rman_recover.log<<END
run{?
shutdown;
startup nomount;
restore controlfile from '/mnt/backup/incre_dir/control02_sty.ctl';
sql 'alter database mount standby database';
## alter database mount;
catalog start with '/mnt/backup/incre_dir/'; ? ? ? ? ? ? ##?备份集的路径、名字需按实际情况调整? ? ? ? ??
configure device type disk parallelism 10;
##restore datafile 5; ? ? ? ? ? ? ? ? ? ? ? ? ? ?##?恢复数据文件名按实际情况调整
restore database;
recover database noredo parallel 10;
}?
exit
END
##?清理所有standby log
sqlplus `sys/ as sysdba`>>clear_standbylog.log<<EOF
ALTER DATABASE clear LOGFILE group ?40;
ALTER DATABASE clear LOGFILE group ?41;
ALTER DATABASE clear LOGFILE group ?42;
ALTER DATABASE clear LOGFILE group ?43;
ALTER DATABASE clear LOGFILE group ?44;
ALTER DATABASE clear LOGFILE group ?45;
ALTER DATABASE clear LOGFILE group ?46;
ALTER DATABASE clear LOGFILE group ?47;
ALTER DATABASE clear LOGFILE group ?48;
ALTER DATABASE clear LOGFILE group ?49;
ALTER DATABASE clear LOGFILE group ?50;
ALTER DATABASE clear LOGFILE group ?51;
ALTER DATABASE clear LOGFILE group ?52;
ALTER DATABASE clear LOGFILE group ?53;
ALTER DATABASE clear LOGFILE group ?54;
ALTER DATABASE clear LOGFILE group ?55;
ALTER DATABASE clear LOGFILE group ?56;
ALTER DATABASE clear LOGFILE group ?57;
ALTER DATABASE clear LOGFILE group ?58;
EOF
##?如果standby log错误直接删除重建
alter database drop standby logfile group 40;
alter database drop standby logfile group 41;
alter database drop standby logfile group 42;
.......
##?增加?standby logfile:
sqlplus `sys/ as sysdba`>>result.log<<EOF
ALTER DATABASE ADD standby LOGFILE group ?40 '/u01/app/oracle/standby/stdy40.dbf' size 2G;
ALTER DATABASE ADD standby LOGFILE group ?41 '/u01/app/oracle/standby/stdy41.dbf' size 2G;
ALTER DATABASE ADD standby LOGFILE group ?42 '/u01/app/oracle/standby/stdy42.dbf' size 2G;
ALTER DATABASE ADD standby LOGFILE group ?43 '/u01/app/oracle/standby/stdy43.dbf' size 2G;
ALTER DATABASE ADD standby LOGFILE group ?44 '/u01/app/oracle/standby/stdy44.dbf' size 2G;
ALTER DATABASE ADD standby LOGFILE group ?45 '/u01/app/oracle/standby/stdy45.dbf' size 2G;
ALTER DATABASE ADD standby LOGFILE group ?46 '/u01/app/oracle/standby/stdy46.dbf' size 2G;
ALTER DATABASE ADD standby LOGFILE group ?47 '/u01/app/oracle/standby/stdy47.dbf' size 2G;
ALTER DATABASE ADD standby LOGFILE group ?48 '/u01/app/oracle/standby/stdy48.dbf' size 2G;
ALTER DATABASE ADD standby LOGFILE group ?49 '/u01/app/oracle/standby/stdy49.dbf' size 2G;
ALTER DATABASE ADD standby LOGFILE group ?50 '/u01/app/oracle/standby/stdy50.dbf' size 2G;
ALTER DATABASE ADD standby LOGFILE group ?51 '/u01/app/oracle/standby/stdy51.dbf' size 2G;
ALTER DATABASE ADD standby LOGFILE group ?52 '/u01/app/oracle/standby/stdy52.dbf' size 2G;
ALTER DATABASE ADD standby LOGFILE group ?53 '/u01/app/oracle/standby/stdy53.dbf' size 2G;
ALTER DATABASE ADD standby LOGFILE group ?54 '/u01/app/oracle/standby/stdy54.dbf' size 2G;
ALTER DATABASE ADD standby LOGFILE group ?55 '/u01/app/oracle/standby/stdy55.dbf' size 2G;
ALTER DATABASE ADD standby LOGFILE group ?56 '/u01/app/oracle/standby/stdy56.dbf' size 2G;
ALTER DATABASE ADD standby LOGFILE group ?57 '/u01/app/oracle/standby/stdy57.dbf' size 2G;
ALTER DATABASE ADD standby LOGFILE group ?58 '/u01/app/oracle/standby/stdy58.dbf' size 2G;
EOF
## 10.10.10.18?新standby端的listener?和?tns
SID_LIST_LISTENER =
? (SID_LIST =
? ? (SID_DESC =
? ? ? (GLOBAL_DBNAME = ORCL)
? ? ? (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
? ? ? (SID_NAME = orcldg2)
? ? )
? )
LISTENER =
? (DESCRIPTION_LIST =
? ? (DESCRIPTION =
? ? ? (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.18)(PORT = 1521))
? ? )
? )
ADR_BASE_LISTENER=/u01/app/oracle
cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
? (DESCRIPTION =
? ? (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.5)(PORT = 1521))
? ? (CONNECT_DATA =
? ? ? (SERVER = DEDICATED)
? ? ? (SERVICE_NAME = orcl)
? ? )
? )
ORCLDG2 =
? (DESCRIPTION =
? ? (ADDRESS_LIST =
? ? ? (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.18)(PORT = 1521))
? ? )
? ? (CONNECT_DATA =
? ? ? (SERVICE_NAME = orcldg2)
? ? ? (UR=A)
? ? )
? )
##?在新备库自动拉取主库归档并追平数据
sqlplus `sys/ as sysdba`>>result.log<<EOF
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
EOF
##?在新备库启动Adg
sqlplus `sys/ as sysdba`>>result.log<<EOF
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect from session;
EOF
##?查看归档日志gap问题是否解决,及归档日志的应用情况。?
新备库端执行:
set pages 300;
set linesize 300;
col name for a80;?
col FIRST_CHANGE# for 9999999999999 ;
col NEXT_CHANGE# for 9999999999999;
select * from v$archive_gap;
##?测算追archivelog的时间?
select count(*),to_char(min(first_time),'yyyymmdd hh24:mi:ss') from v$archived_log where applied='NO';
? COUNT(*) TO_CHAR(MIN(FIRST
---------- -----------------
? ? ? ?111 20210630 15:21:20
select count(*),to_char(min(first_time),'yyyymmdd hh24:mi:ss') from v$archived_log where applied='YES';
? COUNT(*) TO_CHAR(MIN(FIRST
---------- -----------------
? ? ? 1355 20210630 11:39:14
##?验证Adg?有效性(新备库上执行)
select recid,name, sequence#, STANDBY_DEST, ARCHIVED, APPLIED from v$archived_log ;
select process,status from v$managed_standby;
select sequence#,applied from v$archived_log order by sequence# asc;
select * from v$dataguard_stats;
备注:追平后APPLIED?全为Yes,只有最后一个在备库端显示为?In Memory
成功运行!?
环境为?Oracle12cR2 RAC+ ASM + 2个Adg(一个同城、一个异地)
###?自动清除已应用的归档,释放磁盘空间,避免爆盘
cat delete_archivelog.sh
#!/bin/sh
## find /u01/app/oracle/arhivelog -name "*.arc" ?-mtime +1 |xargs rm -rf
cd
. ./.bash_profile
export ORACLE_SID=orcl
echo "">/home/oracle/delete_archivelog.log
/u01/app/oracle/product/bin/rman target / msglog=/home/oracle/delete_archivelog.log <<EOF
run{
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
DELETE NOPROMPT FORCE ARCHIVELOG ALL COMPLETED BEFORE 'sysdate - 1';
}
exit;
EOF
## ?rman删除三天前的备份(备库若每天做了备份,可选择性添加)
?delete archivelog all completed before 'sysdate-3';
?delete backupset completed before 'sysdate-3';
##?添加到计划与任务中
?crontab -l
1 3,17 * * * ?sh /home/oracle/delete_archivelog.sh
#####?常发生的故障情况总结:
1.由于归档在主库保留时间太短,可能会导致多次查询Gap点scn并到旧备库拉增备传输到新备库进行恢复
此时常会导致二次增量恢复后数据文件头的scn不向前,停留在第一次增备状态。
解决方案:一次拉增备,尽量避免二次增备
2. incarnation号识别错误导致,备库无法找到备份集中正确的位置
##?主、备库上执行:
list incarnation;
##?重置备库incarnation号
reset database to incarnation 2;
原因:因为resetlogs以后重置了scn,数据库实体发生了变化,使用的实体编号是以前的,使用该备份集无法完成恢复,需将实体编号改回跟主库一致的情况就OK
3.拉取增备后传输新备库Rman?恢复报datafile1,3,4,7缺失
现象:
restore database;
Starting restore at 30-JUN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1326 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=2082 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=2460 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=2836 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=3 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=193 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=381 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=570 device type=DISK
allocated channel: ORA_DISK_9
channel ORA_DISK_9: SID=760 device type=DISK
allocated channel: ORA_DISK_10
channel ORA_DISK_10: SID=949 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/30/2021 15:50:06
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 7 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
## datafile 7,4,3,1?在备份集中经如下命令逐个查询,全有,但restore?报无法识别
RMAN> ?list ?backup of datafile 7 ;
List of Backup Sets
===================
BS Key ?Type LV Size ? ? ? Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24759 ? Incr ? ?1.62G ? ? ?DISK ? ? ? ?01:13:16 ? ? 30-JUN-21
? ? ? ? BP Key: 25095 ? Status: AVAILABLE ?Compressed: YES ?Tag: TAG20210630T115
? ? ? ? Piece Name: /mnt/backup/incre_dir2/incre_hk02mqgg_1_1_ORCL
? List of Datafiles in backup set 24759
? File LV Type Ckp SCN ? ?Ckp Time ?Abs Fuz SCN Sparse Name
? ---- -- ---- ---------- --------- ----------- ------ ----
? 7 ? ? ? Incr 50551234614 30-JUN-21 50552785159 ?NO ? ?/u01/oradata/datafile/us
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key ?Inc Key DB Name ?DB ID ? ? ? ? ? ?STATUS ?Reset SCN ?Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 ? ? ? 1 ? ? ? ORCL ? ? 1513741333 ? ? ? PARENT ?1 ? ? ? ? ?26-JAN-17
2 ? ? ? 2 ? ? ? ORCL ? ? 1513741333 ? ? ? CURRENT 1408558 ? ?04-SEP-18
3 ? ? ? 3 ? ? ? ORCL ? ? 1513741333 ? ? ? ORPHAN ?21989683576 15-NOV-19
4 ? ? ? 4 ? ? ? ORCL ? ? 1513741333 ? ? ? ORPHAN ?21990161547 15-NOV-19
5 ? ? ? 5 ? ? ? ORCL ? ? 1513741333 ? ? ? ORPHAN ?21990380526 15-NOV-19
6 ? ? ? 6 ? ? ? ORCL ? ? 1513741333 ? ? ? ORPHAN ?21990478997 15-NOV-19
7 ? ? ? 7 ? ? ? ORCL ? ? 1513741333 ? ? ? ORPHAN ?21991031747 16-NOV-19
?incarnation?也与主库一致
解决方案:将全备和增备文件合并(catalog start with '/mnt/backup/')后再执行restore
##?合并增量备份不用担心会导致重复全量恢复,Rman会自动跳过已restore的文件
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=3 device type=DISK
allocated channel: ORA_DISK_9
channel ORA_DISK_9: SID=193 device type=DISK
allocated channel: ORA_DISK_10
channel ORA_DISK_10: SID=381 device type=DISK
skipping datafile 1; already restored to file /u01/oradata/datafile/system.293.985960459
skipping datafile 7; already restored to file /u01/oradata/datafile/users.296.985960529
skipping datafile 15; already restored to file /u01/oradata/datafile/efs_dat.346.985966197
skipping datafile 25; already restored to file /u01/oradata/datafile/loan_dat.357.985966745
skipping datafile 35; already restored to file /u01/oradata/datafile/etl_dat.547.987163079
skipping datafile 40; already restored to file /u01/oradata/datafile/cust_dat.555.986811747
skipping datafile 47; already restored to file /u01/oradata/datafile/newaas_dat.490.986813125
skipping datafile 58; already restored to file /u01/oradata/datafile/loan_dat.921.989062301
skipping datafile 72; already restored to file /u01/oradata/datafile/efs_dat.356.994861109
skipping datafile 76; already restored to file /u01/oradata/datafile/etl_dat.505.1000197047
skipping datafile 118; already restored to file /u01/oradata/datafile/efsw_dat.639.1054555063
skipping datafile 121; already restored to file /u01/oradata/datafile/efs_dat.1024.1059123051
skipping datafile 134; already restored to file /u01/oradata/datafile/efs_dat.1002.1060360853
skipping datafile 149; already restored to file /u01/oradata/datafile/efs_dat.987.1069265261
skipping datafile 159; already restored to file /u01/oradata/datafile/loan_index.977.10721793
?这里可以明显看到?datafile 1,3,4,7已在新备库中存在无需恢复。
channel ORA_DISK_4: restoring datafile 00099 to /u01/oradata/datafile/ORCLSTB2/datafile/o1_mf_efsw_dat_gwxdgv5c_.dbf
channel ORA_DISK_4: reading from backup piece /mnt/backupnew/2/full_0e0244jv_1_1_ORCL
channel ORA_DISK_5: starting datafile backup set restore
channel ORA_DISK_5: specifying datafile(s) to restore from backup set
channel ORA_DISK_5: restoring datafile 00080 to /u01/oradata/datafile/ORCLSTB2/datafile/o1_mf_efsw_dat_gwxc5m8x_.dbf
channel ORA_DISK_5: restoring datafile 00085 to /u01/oradata/datafile/ORCLSTB2/datafile/o1_mf_loan_ind_gktms1pq_.dbf
channel ORA_DISK_5: restoring datafile 00091 to /u01/oradata/datafile/ORCLSTB2/datafile/o1_mf_etl_dat2_gwq99n41_.dbf
channel ORA_DISK_5: reading from backup piece /mnt/backupnew/2/full_0d0244jv_1_1_ORCL
channel ORA_DISK_6: starting datafile backup set restore
channel ORA_DISK_6: specifying datafile(s) to restore from backup set
channel ORA_DISK_6: restoring datafile 00079 to /u01/oradata/datafile/ORCLSTB2/datafile/o1_mf_etl_dat_g7gd31rz_.dbf
channel ORA_DISK_6: restoring datafile 00098 to /u01/oradata/datafile/ORCLSTB2/datafile/o1_mf_efsw_dat_gwxddxdj_.dbf
channel ORA_DISK_6: restoring datafile 00104 to /u01/oradata/datafile/ORCLSTB2/datafile/o1_mf_efsw_dat_h3pzmksk_.dbf
channel ORA_DISK_6: restoring datafile 00105 to /u01/oradata/datafile/ORCLSTB2/datafile/o1_mf_ftp_dat_h4n8obgr_.dbf
channel ORA_DISK_6: restoring datafile 00106 to /u01/oradata/datafile/ORCLSTB2/datafile/o1_mf_etlw_dat_h8wvyfvz_.dbf
channel ORA_DISK_6: reading from backup piece /mnt/backupnew/2/full_0g0244jv_1_1_ORCL
channel ORA_DISK_7: starting datafile backup set restore
channel ORA_DISK_7: specifying datafile(s) to restore from backup set
channel ORA_DISK_7: restoring datafile 00081 to /u01/oradata/datafile/ORCLSTB2/datafile/o1_mf_efsw_dat_gwxclp2d_.dbf
channel ORA_DISK_7: restoring datafile 00086 to /u01/oradata/datafile/ORCLSTB2/datafile/o1_mf_efs_dat_grv97ohb_.dbf
channel ORA_DISK_7: restoring datafile 00092 to /u01/oradata/datafile/ORCLSTB2/datafile/o1_mf_etl_dat2_gwq9plfy_.dbf
?这里可以看到?Rman仅恢复基于Scn拉取的增量数据
4.控制文件中有太多旧的过期备份集记录,导致恢复时间太长
解决方案:做交叉校验,删除过期备份集
5.rman中删除过期备份集命令报错
## delete obsolete失效时,执行
report obsolete;
crosscheck archivelog all;
crosscheck backup;
allocate channel for maintenance type disk;
DELETE FORCE NOPROMPT OBSOLETE DEVICE TYPE DISK;
6.若nohup方式错误调用Rman,杀掉前端进程,后端仍旧在执行
##?查看运行的rman?进程
SELECT sid, spid, client_info?
?FROM v$process p, v$session s?
?WHERE p.addr = s.paddr
?AND client_info LIKE '%rman%';
## kill掉?spid号就好
############################?仅做知识参考,本案例中未采用
##?注册归档日志脚本(当主备间归档未应用太多时)
#!/bin/bash
echo "" > /u01/arch/apply.sql
for i in {75..94}
do
echo "alter database register logfile '/u01/arch/old/1_${i}_996353475.arc';" >> /u01/arch/apply.sql
done
sqlplus / as sysdba<<EOF
@/u01/arch/apply.sql
exit
EOF
## backup format='/home/oracle/standby.ctl' as copy current controlfile for standby; ? ?## rman中制作standby控制文件的第三中命令方式
## alter database set standby database to maximize availability;
######### ?怎样将ASM存储中的归档日志转化成平面文件,以便于拷出
##?怎样将ASM上的文件转换为平面拷贝出
create or replace directory source_dir as '+DATA/ORCL/ARCHIVELOG/';
create or replace directory dest_dir as '/mnt/backup/archivelog';
begin
? ? dbms_file_transfer.copy_file(
? ? source_directory_object => 'source_dir',
? ? source_file_name => 'thread_1_seq_287478.1010.1006490273',
? ? destination_directory_object => 'dest_dir',
? ? destination_file_name => 'thread_1_seq_287478'
? ? );
end;
/
######### ?DUPLICATE TARGET?是最快、最省力的,不适用于本案例的极端场景。仅在环境允许时使用参考
主库执行?:
添加TNS:
orcldg2 =
? (DESCRIPTION =
? (ADDRESS_LIST =
? (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.18)(PORT = 1521))
? )?
?(CONNECT_DATA =
?(SERVICE_NAME = orcldg2)
?(UR=A)
?) )
#!/bin/bash
sqlplus `sys/ as sysdba`>result.log<<EOF
alter system set log_archive_config='DG_CONFIG=(orcl,orcldg,orcldg2)';
alter system set log_archive_dest_3='SERVICE=orcldg2 LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg2' scope=both sid='*';
alter system set log_archive_dest_state_3=enable;
alter system set standby_file_management='AUTO';
exit
EOF
## alter system set log_archive_max_processes=30 scope=both sid='*';?填加提高并行度,加速,可根据情况选择性加
## RMAN>configure channel device type disk rate 1k ; ?rman通道限速开关,不一定加
rman target sys/oracle@orcl auxiliary sys/oracle@orcldg2<<EOF
run{?
DUPLICATE TARGET DATABASE ?FOR STANDBY ?FROM ACTIVE DATABASE;
}?
EOF
备库上执行:
sqlplus `sys/ as sysdba`>>result.log<<EOF
alter database recover managed standby database cancel;
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;
exit
EOF
######################## ?万般皆下品,唯有duplicate target?方式最Easy!??哈哈哈!
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。