對于 Oracle Oracle ADG 備庫重啟時有些人都會有一個小問題，那就是沒有及時應(yīng)用日志，導(dǎo)致主備庫不同步。一般情況下主備庫不同步的原因有：

主庫 SYS 密碼改變未同步密碼文件到備庫
主庫或者備庫歸檔空間滿無法歸檔
主庫或者備庫監(jiān)聽異常，無法通過網(wǎng)絡(luò)連接到主備庫
主庫相關(guān)參數(shù)改變導(dǎo)致無法和備庫正常同步
主備庫 tnsnames sqlnet listener 等文件改變
備庫未開啟日志應(yīng)用進程
備庫磁盤空間不足等等其他原因。

而這些原因大多數(shù)都可以通過查看主備庫的 alter 告警日志展現(xiàn)出來，或者在主庫查看視圖 V$ARCHIVE_DEST。

SELECT DEST_ID, STATUS, APPLIED_SCN,ERROR FROM V$ARCHIVE_DEST WHERE TARGET='STANDBY';

下面來看一下ADG 備庫停啟維護流程(以 11g 為例)。

1、停備庫應(yīng)用

如果備庫有查詢等的只讀業(yè)務(wù)，最好可以先停止相關(guān)業(yè)務(wù)。

查看備庫保護模式

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 ：最大保護模式
保證備庫的內(nèi)容和主庫的內(nèi)容完全一致，不能有絲毫的差別；保證當(dāng)主庫出現(xiàn)問題，不會有任何的數(shù)據(jù)丟失；為了達到這樣的保護級別，redo data 必須同時寫入主庫的 online redo log和備庫的 standby redo log；一旦備庫的日志寫入不成功，那么，主庫 hang 住，超過某個時間長度，主庫自動停止實例；
Maximum availability：最大可用模式
介于最大保護和最大性能之間的一種模式：一般使用最大保護模式保護主庫，一旦達不到最大保護模式的條件，轉(zhuǎn)為最大性能模式；要求所有事務(wù)在提交前必須保障 redo 數(shù)據(jù)至少在一個 standby 數(shù)據(jù)庫可用，不過與之不同的是，如果出現(xiàn)故障導(dǎo)入無法同時寫入standby 數(shù)據(jù)庫 redo log，primary 數(shù)據(jù)庫并不會 shutdown，而是自動轉(zhuǎn)為最大性能模式，等 standby 數(shù)據(jù)庫恢復(fù)正常之后，它又會再自動轉(zhuǎn)換成最大可用性模式。

2、先停監(jiān)聽,殺會話

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	 

關(guān)庫

sqlplus / as sysdba
shutdown immediate;
exit;

--查看 alter 日志
tail -50f alert_jiekedb.log

4.主機關(guān)機維護，等待完畢后開機

shutdown -h now

注意：如果主機關(guān)機維護過程時間比較長，導(dǎo)致主庫的歸檔日志已經(jīng)被刪除了而且也沒有備份歸檔，則只能全量恢復(fù)或者增量恢復(fù)備庫了,所以維護前需要調(diào)整歸檔刪除策略來避免歸檔被刪除。

5.啟動 ADG 備庫

先查看是否已啟動

su - oracle
ps -ef | grep smon 
lsnrctl status
sqlplus / as sysdba

如果已啟動，檢查數(shù)據(jù)庫啟動到哪個階段

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 進程，使用如下語句應(yīng)用日志進程 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');

取消日志應(yīng)用

alter database recover managed standby database cancel;

然后 open 數(shù)據(jù)庫實例

alter database open;

select open_mode from v$database;

然后需要繼續(xù)應(yīng)用日志進程 mrp0

alter database recover managed standby database using current logfile disconnect;

--檢查 mrp0 進程
select process,status,sequence#,THREAD# from v$managed_standby;

--查看日志應(yīng)用時間，注意 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 密碼忘記同步密碼文件到備庫導(dǎo)致備庫已經(jīng)十多天不同步了，由于此庫屬于非生產(chǎn)庫，沒有備份更沒有備份歸檔，恢復(fù)歸檔的方法這里就不能用了，故只能選擇全庫恢復(fù)或者基于 SCN 增量恢復(fù)，這里選擇增量恢復(fù)。關(guān)于 ADG 備庫恢復(fù)的前面也有一份手冊，需要的可點此查看。

1、查看備庫當(dāng)前 SCN

以備庫當(dāng)前 SCN 為時間點，在主庫上基于此 SCN 做增量備份，然后恢復(fù)到備庫，再應(yīng)用當(dāng)前日志即可正常同步備庫。

sqlplus / as sysdba
select  to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
11381638776

這里查到備庫的 SCN 之后，需要到主庫查看基于此 SCN 號之后是否有新增數(shù)據(jù)文件，新增的數(shù)據(jù)文件無法通過增量恢復(fù)，必須先恢復(fù)數(shù)據(jù)文件之后方可增量恢復(fù)。

2、主庫備份控制文件傳到備庫恢復(fù)

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 恢復(fù)控制文件

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 恢復(fù)備庫

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 沒有新增數(shù)據(jù)文件。但前面 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 號數(shù)據(jù)文件記錄。

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 日志此三個數(shù)據(jù)文件也是在修改主庫密碼文件前添加的，所以不屬于新增的數(shù)據(jù)文件。

查看這三個文件創(chuàng)建時間

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

查看生成的備庫控制文件中確實存在如上的三個數(shù)據(jù)文件，說明控制文件恢復(fù)的也沒有問題。

strings t4standby.ctl | grep jieke_data.323.1093203957		

備庫通過 report schema; 查看 Size 大小為 0，“Datafile Name” 列的路徑下沒有數(shù)據(jù)文件，說明這三個文件存在問題。

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 反查當(dāng)時時間，但遺憾的是在主備庫查詢均報錯了。這是由于最小的 SCN 號也已經(jīng)超過了此 SCN，故無法進行相互轉(zhuǎn)換了，如下所示：

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

既然這三個數(shù)據(jù)文件有問題，那么就按照新增數(shù)據(jù)文件或者修改數(shù)據(jù)文件的方式去處理，則在主庫備份這幾個新增數(shù)據(jù)文件然后在備庫恢復(fù)，在接著做增量恢復(fù)就行。

2、主庫備份新增數(shù)據(jù)文件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 傳到備庫恢復(fù)

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、恢復(fù)完數(shù)據(jù)文件重新注冊 catalog 增量恢復(fù)備庫

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 應(yīng)用日志

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.查詢備庫的進程狀態(tài)

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.查詢備庫的日志應(yīng)用模式

SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=1;   

RECOVERY_MODE

---------------------------------------------------------------------

MANAGED REAL TIME APPLYs



--5.開啟日志應(yīng)用進程：

--應(yīng)用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.取消日志應(yīng)用

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;



--查看當(dāng)前歸檔日志應(yīng)用情況

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;