整理一些dataguard会用到的一些查询脚本

59 阅读 0 评论 39 点赞

我是靠谱客的博主孤独滑板，最近开发中收集的这篇文章主要介绍整理一些dataguard会用到的一些查询脚本，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1 查询数据库每天产生的日志量（这个不是很准确，只能参考。如果使用了GV视图，则会翻倍，用V视图就可以了。因为当数据库的日志为500M的时候，有时候，没有到500M就切换了，还是按照500M来计算的话，不是很准确）

 select trunc(a.FIRST_TIME),
        trunc(sum(a.BLOCKS * a.BLOCK_SIZE) / 1024 / 1024 / 1024) gbsize
   from v$archived_log a
  where a.FIRST_TIME > sysdate - 30
  group by trunc(a.FIRST_TIME)
  order by trunc(a.FIRST_TIME)

2 在备库上，修复备库的时候，打开备库出现 ORA-01152的时候，这个情况，主要还是归档缺失的问题，用归档日志往前推就行了。具体可以参考之前的文档：

(1条消息) ORA-10458、ORA-01152、ORA-01110 Update20201121_文档搬运工-CSDN博客

ecovery interrupted!
Completed standby crash recovery.
Signalling error 1152 for datafile 1!
Errors in file /u01/app/oracle/diag/rdbms/XXX/XXX/trace/XXX_ora_7149.trc:
ORA-10458: standby database requires recovery
ORA-01152: 文件 1 没有从过旧的备份中还原
ORA-01110: 数据文件 1: '/u01/app/oradata/XXX/system.259.1014112969'
ORA-10458 signalled during: alter database open...
Wed Jun 16 18:42:41 2021
Shutting down instance (abort)
License high water mark = 8

3 当主库和备库的scn一致的时候，查询以下的内容，会显示结果是一样的（备库的两个查询结果一样，主库的两个查询结果一样，主备库的查询结果也是一样的）。这个查询很有用，会告诉你，控制文件和数据文件，那个是新的，然后进行判断处理。

select file#, status, to_char(checkpoint_change#) from v$datafile;   -- 控制文件   
select file#, status, fuzzy, to_char(checkpoint_change#) from v$datafile_header;  -- 数据

4 在备库长，查询归档日志的情况，基本上可以看到是否有归档日志的缺失，虽然archive log list也可以查看，但是archive log list在12c里面，有时候会显示0 .具体可以参考这个文章。

(1条消息) 12c的Dataguard，物理备库archive log list 显示为0_文档搬运工-CSDN博客

select thread#,max(sequence#) from v$archived_log  group by  thread# ;

5 关于修复备库的时候，用到的一些rman备份恢复归档相关的脚本

-- 2021-09-06add ，针对rac的归档日志，可以加上thread 1 ,thread 2 等等。

list backup of archivelog sequence between 1397 and 1397 thread 2
backup as compressed backupset archivelog sequence between 1397 and 1397 thread 2  format '/tmp/arch_%d_%T_%s_%p';

backup as compressed backupset  format '/path/arc_%d_%T_%u_%p_%c' archivelog from sequence 88837;  -- 备份到指定位置


backup archivelog sequence between 94497 and 94503 ;   -- 备份到默认位置，可以指定位置
backup archivelog sequence 94505;

restore archivelog sequence between 94497 and 94503 ;    -- 恢复到默认的位置，如果指定了destion的话，log_archive_dest_1会变化
restore archivelog sequence 94505;

list backup of archivelog sequence 94495;
list backup of archivelog sequence between 1 and 2;

6 在主库上查询，有多少日志没有ship到备库，没有被备库apply等等

select thread#,sequence#,applied from v$archived_log order by thread#,sequence#;

7 查看主备库的同步情况，比如备库和主库有多少差距，media recover追平需要多久。有时候查询v$archive_gap不准确的时候，可以这样查看。

select * from v$dataguard_stats

常用的就这么多。尤其是那个备份和恢复归档的。其他的再补充。

end

2022-03-01 补充

delete archivelog until time 'sysdate - 0.3'; -- 无论是否被备份，都会被删除掉
delete archivelog until time 'sysdate - 0.3' backed up 1 times to disk; -- 删除已经备份过的归档日志，没有备份的归档日志，不会被删除
backup archivelog all not backed up; -- 备份没有经过备份的归档日志

-- 两个归档日志

[oracle@redhat762100 archivelog]$ ls -l
total 451688
-rw-r-----. 1 oracle oinstall      8192 Feb 25 20:26 1_239_1020495413.dbf
-rw-r-----. 1 oracle oinstall 462518784 Feb 28 17:08 1_240_1020495413.dbf
drwxr-x---. 5 oracle oinstall        58 Jan  5 13:04 TEST
[oracle@redhat762100 archivelog]$

-- 不管是否被备份，都会被删除

RMAN> delete archivelog until time 'sysdate - 0.3';

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
List of Archived Log Copies for database with db_unique_name TEST
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
252     1    239     A 25-FEB-22
        Name: /u01/archivelog/1_239_1020495413.dbf

253     1    240     A 25-FEB-22
        Name: /u01/archivelog/1_240_1020495413.dbf


Do you really want to delete the above objects (enter YES or NO)? no

-- 只删除已经备份过的归档日志，没有备份的归档日志，不会被删除（发现只有归档日志239被删除，240不会被删除）

RMAN> delete archivelog until time 'sysdate - 0.3' backed up 1 times to disk;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
RMAN-08138: warning: archived log not deleted - must create more backups
archived log file name=/u01/archivelog/1_240_1020495413.dbf thread=1 sequence=240
List of Archived Log Copies for database with db_unique_name TEST
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
252     1    239     A 25-FEB-22
        Name: /u01/archivelog/1_239_1020495413.dbf


Do you really want to delete the above objects (enter YES or NO)? no

RMAN>

-- 查看归档日志239，是否被备份 ,发现只有239被备份

list backup of archivelog sequence between 239 and 240;
RMAN> list backup of archivelog sequence between 239 and 240;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
67      10.50K     DISK        00:00:00     25-FEB-22
        BP Key: 71   Status: AVAILABLE  Compressed: YES  Tag: TAG20220225T202647
        Piece Name: /u01/rmanbackup/arc_TEST_20220225_260mofg7_2318641842_1_1

  List of Archived Logs in backup set 67
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    239     10832268   25-FEB-22 10832308   25-FEB-22

RMAN>

-- 备份未备份的归档日志，已经备份过的归档日志，不进行备份，可以发现240，241之前没有被备份，现在开始备份，再次执行，发下240 241不会再被备份

RMAN> backup archivelog all not backed up;

Starting backup at 01-MAR-22
current log archived
using channel ORA_DISK_1
skipping archived log of thread 1 with sequence 239; already backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=240 RECID=253 STAMP=1097860087
input archived log thread=1 sequence=241 RECID=254 STAMP=1098200962
channel ORA_DISK_1: starting piece 1 at 01-MAR-22
channel ORA_DISK_1: finished piece 1 at 01-MAR-22
piece handle=/u01/archivelog/TEST/backupset/2022_03_01/o1_mf_annnn_TAG20220301T154922_k1vn034p_.bkp tag=TAG20220301T154922 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 01-MAR-22

Starting Control File and SPFILE Autobackup at 01-MAR-22
piece handle=/u01/archivelog/TEST/autobackup/2022_03_01/o1_mf_s_1098200978_k1vn0lgh_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 01-MAR-22

RMAN>

-- 再次备份，会发现被skip掉

RMAN> backup archivelog all not backed up;

Starting backup at 01-MAR-22
current log archived
using channel ORA_DISK_1
skipping archived logs of thread 1 from sequence 239 to 241; already backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=242 RECID=255 STAMP=1098201061
channel ORA_DISK_1: starting piece 1 at 01-MAR-22
channel ORA_DISK_1: finished piece 1 at 01-MAR-22
piece handle=/u01/archivelog/TEST/backupset/2022_03_01/o1_mf_annnn_TAG20220301T155101_k1vn35cf_.bkp tag=TAG20220301T155101 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 01-MAR-22

Starting Control File and SPFILE Autobackup at 01-MAR-22
piece handle=/u01/archivelog/TEST/autobackup/2022_03_01/o1_mf_s_1098201062_k1vn36h7_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 01-MAR-22

RMAN>

END

-- 2022-06-17 add

查看归档日志的应用情况，看是否有延迟等等

select process,CLIENT_PROCESS,thread#,sequence#,status,DELAY_MINS from v$managed_standby; 

SQL> select process,CLIENT_PROCESS,thread#,sequence#,status,DELAY_MINS from v$managed_standby;

PROCESS   CLIENT_P    THREAD#  SEQUENCE# STATUS       DELAY_MINS
--------- -------- ---------- ---------- ------------ ----------
ARCH      ARCH              2     125000 CLOSING               0
ARCH      ARCH              1     136375 CLOSING               0
ARCH      ARCH              2     120229 OPENING               0
ARCH      ARCH              1     136376 CLOSING               0
RFS       UNKNOWN           0          0 IDLE                  0
RFS       UNKNOWN           0          0 IDLE                  0
RFS       UNKNOWN           0          0 IDLE                  0
RFS       LGWR              1     136377 IDLE                  0
RFS       UNKNOWN           0          0 IDLE                  0
RFS       UNKNOWN           0          0 IDLE                  0
MRP0      N/A               1     136377 APPLYING_LOG          0

PROCESS   CLIENT_P    THREAD#  SEQUENCE# STATUS       DELAY_MINS
--------- -------- ---------- ---------- ------------ ----------
RFS       UNKNOWN           0          0 IDLE                  0
RFS       LGWR              2     125001 IDLE                  0

13 rows selected.

SQL>

END