Oracle ADG 备库停启维护步骤及增量恢复

原创 JiekeXu All China Database Union 2022-02-08

3610

对于 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 备库停启维护步骤

下面来看一下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;

图片.png

6、验证主备同步性

主库切换日志检查备库日志是否会立即同步

alter system switch logfile;

alter system archive log current;

备库查看 Alert 日志更新情况
tail -500f alert_jiekedb.log

备库基于 SCN 增量恢复

备库由于前面提到的更改主库 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

图片.png

查看 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

图片.png

既然这三个数据文件有问题，那么就按照新增数据文件或者修改数据文件的方式去处理，则在主库备份这几个新增数据文件然后在备库恢复，在接着做增量恢复就行。

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');

图片.png

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
————————————————————————————

图片.png

最后修改时间：2022-02-10 14:57:52

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

2人已赞赏

文章被以下合辑收录

JiekeXu DBA之路（共220篇）

JiekeXu：来自西北，90 后，获得 11g OCP、11g OCM 认证，墨天轮 MVP，墨天轮 2021 十大突出贡献人物之一，此专栏记录一个菜鸟 DBA 学习成长之路，欢迎关注公众号 JiekeXu DBA之路，一起学习进步。

Oracle ADG 备库停启维护步骤及增量恢复

ADG 备库停启维护步骤

1、停备库应用

2、先停监听,杀会话

3、ADG备库检查、停备库

4.主机关机维护，等待完毕后开机

5.启动 ADG 备库

6、验证主备同步性

备库基于 SCN 增量恢复

1、查看备库当前 SCN

2、主库备份控制文件传到备库恢复

3、重启备库

4、备库 rman 恢复控制文件

5、备库启动到 mount

6、主库基于 SCN 增量备份 11381638776

7、增量备份传到备库 /data/backup/ 并注册 catalog

8、recover 恢复备库

排查问题及解决

1、主库查看 基于 SCN 后是否有新增文件

2、主库备份新增数据文件26 27 28

3、scp 传到备库恢复

4、恢复完数据文件重新注册 catalog 增量恢复备库

5、开库并启动 mrp0 应用日志

6、ADG 其他常用 SQL

文章被以下合辑收录

评论

1、主库查看基于 SCN 后是否有新增文件