Oracle 每天归档量大小查看

最新推荐文章于 2024-07-25 09:13:26 发布

富士康质检员张全蛋

最新推荐文章于 2024-07-25 09:13:26 发布

阅读量9.1k

点赞数 1

分类专栏： Oracle 日常管理

本文链接：https://blog.csdn.net/qq_34556414/article/details/85070436

版权

Oracle 日常管理专栏收录该内容

97 篇文章 27 订阅

订阅专栏

归档量查看大小

（1）进入归档所在的目录，使用du命令查看整个归档目录的大小
SQL> select group#,member from v$logfile;

GROUP# MEMBER
---------- ------------------------------------------------------------
1 +DATADG/szyb/onlinelog/group_1.266.897932185
1 +FRADG/szyb/onlinelog/group_1.260.897932187
2 +DATADG/szyb/onlinelog/group_2.258.897932191
2 +FRADG/szyb/onlinelog/group_2.258.897932193
5 +DATADG/szyb/onlinelog/group_5.262.897931655
3 +DATADG/szyb/onlinelog/group_3.265.897932395
3 +FRADG/szyb/onlinelog/group_3.259.897932397
4 +DATADG/szyb/onlinelog/group_4.257.897932399
4 +FRADG/szyb/onlinelog/group_4.257.897932401
5 +FRADG/szyb/onlinelog/group_5.261.897931659
6 +DATADG/szyb/onlinelog/group_6.272.897931661

GROUP# MEMBER
---------- ------------------------------------------------------------
6 +FRADG/szyb/onlinelog/group_6.262.897931663
7 +DATADG/szyb/onlinelog/group_7.273.897931665
7 +FRADG/szyb/onlinelog/group_7.263.897931669
8 +DATADG/szyb/onlinelog/group_8.274.897931671
8 +FRADG/szyb/onlinelog/group_8.264.897931673
9 +DATADG/szyb/onlinelog/group_9.275.897931675
9 +FRADG/szyb/onlinelog/group_9.265.897931677
10 +DATADG/szyb/onlinelog/group_10.276.897931681
10 +FRADG/szyb/onlinelog/group_10.266.897931683
11 +DATADG/szyb/onlinelog/group_11.404.932069479
11 +FRADG/szyb/onlinelog/group_11.287.932069481

GROUP# MEMBER
---------- ------------------------------------------------------------
12 +DATADG/szyb/onlinelog/group_12.405.932069493
12 +FRADG/szyb/onlinelog/group_12.376.932069497
13 +DATADG/szyb/onlinelog/group_13.406.932069575
13 +FRADG/szyb/onlinelog/group_13.364.932069577
14 +DATADG/szyb/onlinelog/group_14.407.932069581
14 +FRADG/szyb/onlinelog/group_14.366.932069585
15 +DATADG/szyb/onlinelog/group_15.408.932069589
15 +FRADG/szyb/onlinelog/group_15.379.932069591

30 rows selected.

SQL> select GROUP#,MEMBERS,BYTES/1024/1024,THREAD# from v$log;

GROUP# MEMBERS BYTES/1024/1024 THREAD#
---------- ---------- --------------- ----------
1 2 500 1
2 2 500 1
3 2 500 2
4 2 500 2
5 2 500 1
6 2 500 1
7 2 500 1
8 2 500 2
9 2 500 2
10 2 500 2
11 2 512 3

GROUP# MEMBERS BYTES/1024/1024 THREAD#
---------- ---------- --------------- ----------
12 2 512 3
13 2 512 3
14 2 512 3
15 2 512 3

SQL> show parameter cluster;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 3
cluster_interconnects string
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 34750
Next log sequence to archive 34754
Current log sequence 34754
SQL> show parameter USE_DB_RECOVERY_FILE_DEST;
SQL> show parameter recover;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRADG
db_recovery_file_dest_size big integer 700G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0

szybdb2:/home/grid$asmcmd
ASMCMD> ls
CRSDG/
DATA1DG/
DATADG/
FRADG/
ASMCMD> cd FRADG
ASMCMD> ls
SZYB/
ASMCMD> cd SZYB
ASMCMD> ls
ARCHIVELOG/
CHANGETRACKING/
CONTROLFILE/
DATAFILE/
ONLINELOG/

ASMCMD> cd ARCHIVELOG
ASMCMD> ls
2018_12_12/
2018_12_13/

ASMCMD> ls
thread_1_seq_57793.1683.994723223
thread_1_seq_57794.1080.994727673
thread_1_seq_57795.1248.994733287
thread_1_seq_57796.585.994739275
thread_1_seq_57797.473.994749959
thread_1_seq_57798.1243.994752415
thread_1_seq_57799.1237.994753613
thread_1_seq_57800.1247.994754639
thread_1_seq_57801.1240.994755645
thread_1_seq_57802.1137.994756373
thread_1_seq_57803.1133.994757259
thread_1_seq_57804.1126.994758195
thread_1_seq_57805.1340.994759071
thread_1_seq_57806.1197.994759941
thread_1_seq_57807.1234.994760775
thread_1_seq_57808.1082.994761729
..................................................................................

ASMCMD> du
Used_MB Mirror_used_MB
89349 89349

（2）使用语句来查询每天产生归档的大小

SQL> Select to_char(completion_time,'yyyy-mm-dd') as date1,count(0) as cnt,round(sum((blocks *block_size)/1024/1024)) as mb from v$archived_log
group by to_char(completion_time,'yyyy-mm-dd') order by date1 desc;

DATE1				      CNT	  MB                   --CNT为每天归档次数，MB为每天的归档量
------------------------------ ---------- ----------
2019-04-22			       18      17471
2019-04-21			       54      49580
2019-04-20			       52      49069
2019-04-19			       54      51279
2019-04-18			      389     366716
2019-04-17			       56      53025
2019-04-16			       52      49259
2019-04-15			       56      52957
2019-04-14			       52      49145
2019-04-13			       52      49145
2019-04-12			       52      48889