对于 Oracle Oracle ADG 备库重启时有些人都会有一个小问题，那就是没有及时应用日志，导致主备库不同步。一般情况下主备库不同步的原因有：
主库 SYS 密码改变未同步密码文件到备库
主库或者备库归档空间满无法归档
主库或者备库监听异常，无法通过网络连接到主备库
主库相关参数改变导致无法和备库正常同步
主备库 tnsnames sqlnet listener 等文件改变
备库未开启日志应用进程
备库磁盘空间不足等等其他原因。
而这些原因大多数都可以通过查看主备库的 alter 告警日志展现出来，或者在主库查看视图 V$ARCHIVE_DEST。
SELECT DEST_ID, STATUS, APPLIED_SCN,ERROR FROM V$ARCHIVE_DEST WHERE TARGET='STANDBY';复制
下面来看一下ADG 备库停启维护流程(以 11g 为例)。
1、停备库应用
如果备库有查询等的只读业务，最好可以先停止相关业务。
查看备库保护模式
select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database; DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL ---------------- -------------------- -------------------- PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE复制
Maximum performance：最大性能模式
默认的保护模式；在不影响主库性能的情况下，提供最高级别的保护模式。
Maximum protection ：最大保护模式
保证备库的内容和主库的内容完全一致，不能有丝毫的差别；保证当主库出现问题，不会有任何的数据丢失；为了达到这样的保护级别，redo data 必须同时写入主库的 online redo log和备库的 standby redo log；一旦备库的日志写入不成功，那么，主库 hang 住，超过某个时间长度，主库自动停止实例；
Maximum availability：最大可用模式
介于最大保护和最大性能之间的一种模式：一般使用最大保护模式保护主库，一旦达不到最大保护模式的条件，转为最大性能模式；要求所有事务在提交前必须保障 redo 数据至少在一个 standby 数据库可用，不过与之不同的是，如果出现故障导入无法同时写入standby 数据库 redo log，primary 数据库并不会 shutdown，而是自动转为最大性能模式，等 standby 数据库恢复正常之后，它又会再自动转换成最大可用性模式。
2、先停监听,杀会话
lsnrctl status lsnrctl stop ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}' |wc -l ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}' | xargs kill -9 ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}' |wc -l复制
3、ADG备库检查、停备库
sqlplus / as sysdba --查看同步情况 set linesize 150; set pagesize 9999; column name format a13; column value format a20; column unit format a30; column TIME_COMPUTED format a30; select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag'); select open_mode from v$database; select process,status,sequence# from v$managed_standby;复制
主备库查看归档路径
column destination format a50 column process format a7 column ID format 99 column mid format 99 SELECT thread#, dest_id, destination, gvad.status, target, schedule, process, mountid mid FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id; THREAD# DEST_ID DESTINATION STATUS TARGET SCHEDULE PROCESS MID ---------- ---------- -------------------------------------------------- --------- ------- -------- ------- --- 1 1 +FRA VALID PRIMARY ACTIVE ARCH 0 1 2 jieket4dg VALID STANDBY ACTIVE LGWR 0 1 3 jiekeyun DEFERRED STANDBY PENDING LGWR 0 2 1 +FRA VALID PRIMARY ACTIVE ARCH 0 2 2 jieket4dg VALID STANDBY ACTIVE LGWR 0 2 3 jiekeyun DEFERRED STANDBY PENDING LGWR 0 3 1 +FRA VALID PRIMARY ACTIVE ARCH 0 3 2 jieket4dg VALID STANDBY ACTIVE LGWR 0 3 3 jiekeyun DEFERRED STANDBY PENDING LGWR 0 4 1 +FRA VALID PRIMARY ACTIVE ARCH 0 4 2 jieket4dg VALID STANDBY ACTIVE LGWR 0 4 3 jiekeyun DEFERRED STANDBY PENDING LGWR 0 12 rows selected. --备库查看 THREAD# DEST_ID DESTINATION STATUS TARGET SCHEDULE PROCESS MID ---------- ---------- -------------------------------------------------- --------- ------- -------- ------- --- 1 1 /data/jxrtt4dg/arch VALID LOCAL ACTIVE ARCH 0 1 2 jxr2p VALID REMOTE PENDING LGWR 0 1 32 /data/jxrtt4dg/arch VALID LOCAL ACTIVE RFS 0复制
关库
sqlplus / as sysdba shutdown immediate; exit; --查看 alter 日志 tail -50f alert_jiekedb.log复制
4.主机关机维护，等待完毕后开机
shutdown -h now
注意：如果主机关机维护过程时间比较长，导致主库的归档日志已经被删除了而且也没有备份归档，则只能全量恢复或者增量恢复备库了,所以维护前需要调整归档删除策略来避免归档被删除。
5.启动 ADG 备库
先查看是否已启动
su - oracle ps -ef | grep smon lsnrctl status sqlplus / as sysdba复制
如果已启动，检查数据库启动到哪个阶段
col HOST_NAME for a30 select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from gv$instance;复制
如果没有启动,则先启动到 mount
sqlplus / as sysdba startup mount select open_mode from v$database;复制
检查 mrp0 进程是否开启
select process,status,sequence#,THREAD# from v$managed_standby where PROCESS='MRP0'; PROCESS STATUS SEQUENCE# THREAD# ------- ------------ ---------- ---------- MRP0 APPLYING_LOG 4764 4复制
检查 ADG 同步情况
set linesize 150; set pagesize 9999; column name format a13; column value format a20; column unit format a30; column TIME_COMPUTED format a30; select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag'); 如果没有开启则先开启 mrp0 进程，使用如下语句应用日志进程 mrp0 alter database recover managed standby database using current logfile disconnect;复制
再次查看等如下 value 值为0
set linesize 150; set pagesize 9999; column name format a13; column value format a20; column unit format a30; column TIME_COMPUTED format a30; select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');复制
取消日志应用
alter database recover managed standby database cancel;复制
然后 open 数据库实例
alter database open; select open_mode from v$database;复制
然后需要继续应用日志进程 mrp0
alter database recover managed standby database using current logfile disconnect; --检查 mrp0 进程 select process,status,sequence#,THREAD# from v$managed_standby; --查看日志应用时间，注意 apply lag 即可。 set linesize 150; set pagesize 9999; column name format a13; column value format a20; column unit format a30; column TIME_COMPUTED format a30; select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');复制
SELECT AL.THRD "Thread", ALMAX "Last Seq Received", LHMAX "Last Seq Applied" FROM (SELECT THREAD# THRD, MAX(SEQUENCE#) ALMAX FROM V$ARCHIVED_LOG WHERE RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE# FROM V$DATABASE) GROUP BY THREAD#) AL, (SELECT THREAD# THRD, MAX(SEQUENCE#) LHMAX FROM V$LOG_HISTORY WHERE RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE# FROM V$DATABASE) GROUP BY THREAD#) LH WHERE AL.THRD = LH.THRD;复制
6、验证主备同步性
主库 切换日志检查备库日志是否会立即同步
alter system switch logfile; alter system archive log current;复制
备库查看 Alert 日志更新情况
tail -500f alert_jiekedb.log
备库由于前面提到的更改主库 SYS 密码忘记同步密码文件到备库导致备库已经十多天不同步了，由于此库属于非生产库，没有备份更没有备份归档，恢复归档的方法这里就不能用了，故只能选择全库恢复或者基于 SCN 增量恢复，这里选择增量恢复。关于 ADG 备库恢复的前面也有一份手册，需要的可点此查看。
1、查看备库当前 SCN
以备库当前 SCN 为时间点，在主库上基于此 SCN 做增量备份，然后恢复到备库，再应用当前日志即可正常同步备库。
sqlplus / as sysdba select to_char(current_scn) from v$database; TO_CHAR(CURRENT_SCN) ---------------------------------------- 11381638776复制
这里查到备库的 SCN 之后，需要到主库查看基于此 SCN 号之后是否有新增数据文件，新增的数据文件无法通过增量恢复，必须先恢复数据文件之后方可增量恢复。
2、主库备份控制文件传到备库恢复
alter database create standby controlfile as '/tmp/t4_standby.ctl'; scp /tmp/t4_standby.ctl 到备库 /tmp/t4_standby.ctl。复制
3、重启备库
shu immediate startup nomount复制
4、备库 rman 恢复控制文件
rman target / restore controlfile from '/tmp/t4_standby.ctl';复制
5、备库启动到 mount
RMAN> sql 'alter database mount';复制
6、主库基于 SCN 增量备份 11381638776
run{ allocate channel c1 type disk; allocate channel c2 type disk; backup INCREMENTAL from scn 11381638776 database format '/nfs/backup/incre_%U'; release channel c1; release channel c2; }复制
7、增量备份传到备库 /data/backup/ 并注册 catalog
scp -r /nfs/backup/incre* 到备库 /data/backup/ RMAN> catalog start with '/data/backup/';复制
8、recover 恢复备库
RMAN> recover database noredo; Starting recover at 2022-01-24 16:25:58 using channel ORA_DISK_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 01/24/2022 16:25:59 RMAN-06094: datafile 26 must be restored RMAN> recover database noredo; Starting recover at 2022-01-24 16:30:45 using channel ORA_DISK_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 01/24/2022 16:30:45 RMAN-06094: datafile 26 must be restored复制
1、主库查看 基于 SCN 后是否有新增文件
select file#,name from v$datafile where creation_change#> =11381638776; SQL> select file#,to_char(creation_time,'yyyy-mm-dd hh24:mi:ss' ) creation_time,status,last_time,name from v$datafile where creation_change#>11381638776; no rows selected复制
但是通过这个 SQL 查不到，证明基于此 SCN 没有新增数据文件。但前面 26 号却是实实在在得报错了，百思不得其解，大于 26 号文件的还有 27 、28 号文件。
查看 alter 日志
grep -A 4 "alter database datafile 27" alert_jie2p1.log alter database datafile 27 resize 30g Completed: alter database datafile 27 resize 30g Thu Dec 30 14:47:56 2021 alter database datafile 27 resize 32767m Completed: alter database datafile 27 resize 32767m --添加 28 号数据文件记录。 alter tablespace JIEKE_DATA add datafile '+DATA' size 32767M Wed Jan 05 19:46:36 2022 Completed: alter tablespace JIEKE_DATA add datafile '+DATA' size 32767M Wed Jan 05 19:47:52 2022复制
查看 alter 日志此三个数据文件也是在修改主库密码文件前添加的，所以不属于新增的数据文件。
查看这三个文件创建时间
col name for a69 select file#,name,CREATION_TIME from v$datafile where file#>=26; FILE# NAME CREATION_TIME ---------- --------------------------------------------------------------------- ------------------- 26 +DATA/jie2p/datafile/t4_ccdb_data.313.1083605453 2021-09-18 17:31:09 27 +DATA/jie2p/datafile/jieke_data.314.1087482763 2021-11-01 14:32:57 28 +DATA/jie2p/datafile/jieke_data.323.1093203957 2022-01-05 19:46:35复制
查看生成的备库控制文件中确实存在如上的三个数据文件，说明控制文件恢复的也没有问题。
strings t4standby.ctl | grep jieke_data.323.1093203957复制
备库通过 report schema; 查看 Size 大小为 0，“Datafile Name” 列的路径下没有数据文件，说明这三个文件存在问题。
RMAN> report schema; RMAN-06139: WARNING: control file is not current for REPORT SCHEMA Report of database schema for database with db_unique_name JIEKET4DG List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 30720 SYSTEM *** /data/jieket4dg/datafile/system.260.1016468519 2 32704 SYSAUX *** /data/jieket4dg/datafile/sysaux.306.1016468763 3 10240 UNDOTBS1 *** /data/jieket4dg/datafile/undotbs1.270.1016468699 4 3557 USERS *** /data/jieket4dg/datafile/users.264.1016468597 5 32704 JIEKE_DATA *** /data/jieket4dg/datafile/jieke_data.261.1016468517 6 20480 JIEKE_INDEX *** /data/jieket4dg/datafile/jieke_index.262.1016468595 7 5120 JIEKE_DAT *** /data/jieket4dg/datafile/jieke_dat.256.1016468521 8 1024 PROD_YD_DATA *** /data/jieket4dg/datafile/prod_yd_data.257.1016468697 9 1024 PROD_YD_INDEX *** /data/jieket4dg/datafile/prod_yd_index.305.1016468765 10 5120 PROD_MOBISCF_DATA *** /data/jieket4dg/datafile/prod_mobiscf_data.269.1016468595 11 5120 PROD_MOBISCF_INDEX *** /data/jieket4dg/datafile/prod_mobiscf_index.304.1016468767 12 5120 PROD_DBSZ_DATA *** /data/jieket4dg/datafile/prod_dbsz_data.295.1016468521 13 5120 PROD_DBSZ_INDEX *** /data/jieket4dg/datafile/prod_dbsz_index.277.1016468701 14 29696 SYSAUX *** /data/jieket4dg/datafile/sysaux.265.1016468591 15 29696 SYSAUX *** /data/jieket4dg/datafile/sysaux.274.1016468701 16 10240 UNDOTBS2 *** /data/jieket4dg/datafile/undotbs02.dbf 17 10240 UNDOTBS3 *** /data/jieket4dg/datafile/undotbs03.dbf 18 10240 UNDOTBS4 *** /data/jieket4dg/datafile/undotbs04.dbf 19 1400 PROD_SCFOP_TBS *** /data/jieket4dg/datafile/prod_scfop_tbs.300.1037881143 20 2048 USERS *** /data/jieket4dg/datafile/users.307.1045325715 21 100 TEST_PY_DATA *** /data/jieket4dg/datafile/test_py_data.308.1057426717 22 32767 JIEKE_DATA *** /data/jieket4dg/datafile/jieke_data.309.1067980347 23 500 ITSM_AUTO_TEST1_DATA *** /data/jieket4dg/datafile/itsm_auto_test1_data.310.1072006145 24 500 ITSM_AUTO_TEST2_DATA *** /data/jieket4dg/datafile/itsm_auto_test2_data.311.1072006461 25 1824 OGG_TBS *** /data/jieket4dg/datafile/ogg_tbs.312.1080748083 26 0 T4_ccdb_DATA *** /data/jieket4dg/datafile/t4_ccdb_data.313.1083605453 27 0 JIEKE_DATA *** /data/jieket4dg/datafile/jieke_data.314.1087482763 28 0 JIEKE_DATA *** /data/jieket4dg/datafile/jieke_data.323.1093203957 List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 2 5120 TEMP 5120 /data/jieket4dg/tempfile/temp.273.1016480467复制
事后本想通过前面查询到的 SCN 反查当时时间，但遗憾的是在主备库查询均报错了。这是由于最小的 SCN 号也已经超过了此 SCN，故无法进行相互转换了，如下所示：
SQL> select to_char(scn_to_timestamp('11381638776'),'yyyy-mm-dd hh24:mi:ss') scndate from dual; select to_char(scn_to_timestamp('11381638776'),'yyyy-mm-dd hh24:mi:ss') scndate from dual * ERROR at line 1: ORA-08181: specified number is not a valid system change number ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1 SQL> col min_scn for 99999999999999999999 SQL> select min(SCN) min_scn from sys.smon_scn_time; MIN_SCN --------------------- 11735387827 Elapsed: 00:00:00.00 SQL> select to_char(scn_to_timestamp(11735387827), 'yyyy-mm-dd hh24:mi:ss') scndate from dual; SCNDATE ------------------- 2022-02-03 00:25:10复制
既然这三个数据文件有问题，那么就按照新增数据文件或者修改数据文件的方式去处理，则在主库备份这几个新增数据文件然后在备库恢复，在接着做增量恢复就行。
2、主库备份新增数据文件26 27 28
RMAN> run{ allocate channel c1 type disk; 2> 3> allocate channel c2 type disk; 4> backup datafile 26,27,28 format '/nfs/backup/incre_dbfile_%U'; 5> release channel c1; 6> release channel c2; } 7>复制
3、scp 传到备库恢复
scp /nfs/backup/incre_dbfile* 到备库 /data/backup/dbfile rman target / catalog start with '/data/backup/dbfile';复制
RMAN> run{ 2> set newname for datafile 26 to '/data/jieket4dg/datafile/t4_ccdb_data.313.1083605453'; 3> set newname for datafile 27 to '/data/jieket4dg/datafile/jieke_data.314.1087482763'; 4> set newname for datafile 28 to '/data/jieket4dg/datafile/jieke_data.323.1093203957'; 5> restore datafile 26,27,28;} executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 2022-01-24 17:44:42 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00026 to /data/jieket4dg/datafile/t4_ccdb_data.313.1083605453 channel ORA_DISK_1: restoring datafile 00027 to /data/jieket4dg/datafile/jieke_data.314.1087482763 channel ORA_DISK_1: reading from backup piece /data/backup/dbfile/incre_dbfile_1p0k3nsi_1_1 channel ORA_DISK_1: piece handle=/data/backup/dbfile/incre_dbfile_1p0k3nsi_1_1 tag=TAG20220124T170834 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:05:35 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00028 to /data/jieket4dg/datafile/jieke_data.323.1093203957 channel ORA_DISK_1: reading from backup piece /data/backup/dbfile/incre_dbfile_1q0k3nsi_1_1 channel ORA_DISK_1: piece handle=/data/backup/dbfile/incre_dbfile_1q0k3nsi_1_1 tag=TAG20220124T170834 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:02:05 Finished restore at 2022-01-24 17:52:23复制
RMAN> report schema; RMAN-06139: WARNING: control file is not current for REPORT SCHEMA Report of database schema for database with db_unique_name jiekeT4DG List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 30720 SYSTEM *** /data/jieket4dg/datafile/system.260.1016468519 2 32704 SYSAUX *** /data/jieket4dg/datafile/sysaux.306.1016468763 3 10240 UNDOTBS1 *** /data/jieket4dg/datafile/undotbs1.270.1016468699 4 3557 USERS *** /data/jieket4dg/datafile/users.264.1016468597 5 32704 jieke_DATA *** /data/jieket4dg/datafile/jieke_data.261.1016468517 6 20480 jieke_INDEX *** /data/jieket4dg/datafile/jieke_index.262.1016468595 7 5120 jieke_DAT *** /data/jieket4dg/datafile/jieke_dat.256.1016468521 8 1024 PROD_YD_DATA *** /data/jieket4dg/datafile/prod_yd_data.257.1016468697 9 1024 PROD_YD_INDEX *** /data/jieket4dg/datafile/prod_yd_index.305.1016468765 10 5120 PROD_MOBISCF_DATA *** /data/jieket4dg/datafile/prod_mobiscf_data.269.1016468595 11 5120 PROD_MOBISCF_INDEX *** /data/jieket4dg/datafile/prod_mobiscf_index.304.1016468767 12 5120 PROD_dbsz_DATA *** /data/jieket4dg/datafile/prod_dbsz_data.295.1016468521 13 5120 PROD_dbsz_INDEX *** /data/jieket4dg/datafile/prod_dbsz_index.277.1016468701 14 29696 SYSAUX *** /data/jieket4dg/datafile/sysaux.265.1016468591 15 29696 SYSAUX *** /data/jieket4dg/datafile/sysaux.274.1016468701 16 10240 UNDOTBS2 *** /data/jieket4dg/datafile/undotbs02.dbf 17 10240 UNDOTBS3 *** /data/jieket4dg/datafile/undotbs03.dbf 18 10240 UNDOTBS4 *** /data/jieket4dg/datafile/undotbs04.dbf 19 1400 PROD_SCFOP_TBS *** /data/jieket4dg/datafile/prod_scfop_tbs.300.1037881143 20 2048 USERS *** /data/jieket4dg/datafile/users.307.1045325715 21 100 TEST_PY_DATA *** /data/jieket4dg/datafile/test_py_data.308.1057426717 22 32767 jieke_DATA *** /data/jieket4dg/datafile/jieke_data.309.1067980347 23 500 ITSM_AUTO_TEST1_DATA *** /data/jieket4dg/datafile/itsm_auto_test1_data.310.1072006145 24 500 ITSM_AUTO_TEST2_DATA *** /data/jieket4dg/datafile/itsm_auto_test2_data.311.1072006461 25 1824 OGG_TBS *** /data/jieket4dg/datafile/ogg_tbs.312.1080748083 26 10240 T4_ccdb_DATA *** /data/jieket4dg/datafile/t4_ccdb_data.313.1083605453 27 32767 jieke_DATA *** /data/jieket4dg/datafile/jieke_data.314.1087482763 28 32767 jieke_DATA *** /data/jieket4dg/datafile/jieke_data.323.1093203957 List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 2 5120 TEMP 5120 /data/jieket4dg/tempfile/temp.273.1016480467复制
4、恢复完数据文件重新注册 catalog 增量恢复备库
RMAN> catalog start with '/data/backup'; using target database control file instead of recovery catalog searching for all files that match the pattern /data/backup no files found to be unknown to the database RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 JXR2P 3857716255 PARENT 1 2013-08-24 11:37:30 2 2 JXR2P 3857716255 PARENT 925702 2017-10-16 14:48:01 3 3 JXR2P 3857716255 CURRENT 671821825 2019-08-16 19:27:10 RMAN> recover database noredo; Starting recover at 2022-01-24 18:00:21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=572 device type=DISK channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00001: /data/jieket4dg/datafile/system.260.1016468519 destination for restore of datafile 00006: /data/jieket4dg/datafile/jieke_index.262.1016468595 destination for restore of datafile 00011: /data/jieket4dg/datafile/prod_mobiscf_index.304.1016468767 destination for restore of datafile 00013: /data/jieket4dg/datafile/prod_dbsz_index.277.1016468701 destination for restore of datafile 00014: /data/jieket4dg/datafile/sysaux.265.1016468591 destination for restore of datafile 00017: /data/jieket4dg/datafile/undotbs03.dbf destination for restore of datafile 00019: /data/jieket4dg/datafile/prod_scfop_tbs.300.1037881143 destination for restore of datafile 00020: /data/jieket4dg/datafile/users.307.1045325715 destination for restore of datafile 00021: /data/jieket4dg/datafile/test_py_data.308.1057426717 destination for restore of datafile 00023: /data/jieket4dg/datafile/itsm_auto_test1_data.310.1072006145 destination for restore of datafile 00024: /data/jieket4dg/datafile/itsm_auto_test2_data.311.1072006461 channel ORA_DISK_1: reading from backup piece /data/backup/incre_1m0k3ibg_1_1 channel ORA_DISK_1: piece handle=/data/backup/incre_1m0k3ibg_1_1 tag=TAG20220124T153407 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:04:35 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00002: /data/jieket4dg/datafile/sysaux.306.1016468763 destination for restore of datafile 00003: /data/jieket4dg/datafile/undotbs1.270.1016468699 destination for restore of datafile 00004: /data/jieket4dg/datafile/users.264.1016468597 destination for restore of datafile 00005: /data/jieket4dg/datafile/jieke_data.261.1016468517 destination for restore of datafile 00007: /data/jieket4dg/datafile/jieke_dat.256.1016468521 destination for restore of datafile 00008: /data/jieket4dg/datafile/prod_yd_data.257.1016468697 destination for restore of datafile 00009: /data/jieket4dg/datafile/prod_yd_index.305.1016468765 destination for restore of datafile 00010: /data/jieket4dg/datafile/prod_mobiscf_data.269.1016468595 destination for restore of datafile 00012: /data/jieket4dg/datafile/prod_dbsz_data.295.1016468521 destination for restore of datafile 00015: /data/jieket4dg/datafile/sysaux.274.1016468701 destination for restore of datafile 00016: /data/jieket4dg/datafile/undotbs02.dbf destination for restore of datafile 00018: /data/jieket4dg/datafile/undotbs04.dbf destination for restore of datafile 00022: /data/jieket4dg/datafile/jieke_data.309.1067980347 destination for restore of datafile 00025: /data/jieket4dg/datafile/ogg_tbs.312.1080748083 channel ORA_DISK_1: reading from backup piece /data/backup/incre_1n0k3ibh_1_1 channel ORA_DISK_1: piece handle=/data/backup/incre_1n0k3ibh_1_1 tag=TAG20220124T153407 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:05:35 Finished recover at 2022-01-24 18:10:32 RMAN>复制
5、开库并启动 mrp0 应用日志
alter database open; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; set linesize 150; set pagesize 9999; column name format a13; column value format a20; column unit format a30; column TIME_COMPUTED format a30; select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');复制
6、ADG 其他常用 SQL
--1.查询主备库的同步情况 set linesize 150; set pagesize 20; column name format a13; column value format a20; column unit format a30; column TIME_COMPUTED format a30; select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag'); --备库查询 alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; select THREAD#,count(FIRST_TIME) from v$archived_log where APPLIED='NO' group by THREAD#; select THREAD#,min(FIRST_TIME) from v$archived_log where APPLIED='NO' group by THREAD#; select THREAD#,min(SEQUENCE#) from v$archived_log where APPLIED='NO' group by THREAD#; select BACKUP_COUNT from v$archived_log where THREAD#=&1 and SEQUENCE#=&2; --2.查询备库的进程状态 SELECT PROCESS, STATUS,SEQUENCE#,thread# FROM V$MANAGED_STANDBY; select MESSAGE_NUM,ERROR_CODE,TIMESTAMP,MESSAGE from v$dataguard_status; --3.查询备库的角色 set linesize 160; column DBNAME format a8; column DBUNAME format a10; column cftype format a8; column OPEN_MODE format a25; column DATABASE_ROLE format a18; select name dbname,db_unique_name dbuname,controlfile_type cftype,database_role,open_mode from v$database; --4.查询备库的日志应用模式 SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=1; RECOVERY_MODE --------------------------------------------------------------------- MANAGED REAL TIME APPLYs --5.开启日志应用进程： --应用stanby 实时同步 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; alter database recover managed standby database using current logfile disconnect from session; --6.取消日志应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; set lines 320 col message for a88 col timestamp for a20 SELECT ERROR_CODE, SEVERITY, MESSAGE, TO_cHAR(TIMESTAMP, 'DD-MON-RR HH24:MI:SS') TIMESTAMP FROM V$DATAGUARD_STATUS WHERE CALLOUT='YES' AND TIMESTAMP > SYSDATE-1; select THREAD#,sequence#, first_time, next_time, applied from v$archived_log order by 3; select name,database_role,switchover_status from v$database; select sequence#, first_time, next_time, applied from v$archived_log order by sequence#; col type for a15 set lines 220 set pages 330 col item for a20 col units for a15 select to_char(start_time, 'DD-MON-RR HH24:MI:SS') start_time, type, item, units, sofar, total, timestamp from v$recovery_progress; --查看当前归档日志应用情况 select a.thread#, b.max_available, a.max_applied, b.max_available-a.max_applied from (select thread#, max(sequence#) max_applied from gv$archived_log where applied='YES' group by thread# ) a, (select thread#, max(sequence#) max_available from gv$archived_log group by thread# ) b where a.thread#=b.thread#; select name,value,datum_time from v$dataguard_stats;复制
全文完，希望可以帮到正在阅读的你~~~
————————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————
最后修改时间：2022-02-10 14:57:52
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
筱悦星辰
11月前
评论
0爱读书的人，从容通达
不偏执、不固执
书能让你认识这个世界
也能认识自己
11月前
点赞 评论
猫瞳映月
3年前
评论
0强哥出的文章都是精品，点赞收藏投币一键三连~
3年前
点赞 1
JiekeXu
3年前
回复
0这还得多谢炮哥捧场→_→^O^
3年前
点赞 回复
墨天轮福利君
3年前
评论
1您好，您的文章已入选合格奖，10墨值奖励已经到账请查收！
❤️我们还会实时派发您的流量收益。
3年前
1 评论
