查看Oracle 数据库的每天归档量大小

 1、日志位置

SQL>  select group#,member from v$logfile;

    GROUP# MEMBER

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

1 +DATA/mhdb/onlinelog/group_1.256.982084575

1 +FRA/mhdb/onlinelog/group_1.438.982084575

2 +DATA/mhdb/onlinelog/group_2.262.982084577

2 +FRA/mhdb/onlinelog/group_2.260.982084577

3 +DATA/mhdb/onlinelog/group_3.266.982084577

3 +FRA/mhdb/onlinelog/group_3.259.982084579

4 +DATA/mhdb/onlinelog/group_4.261.982086153

4 +FRA/mhdb/onlinelog/group_4.258.982086153

5 +DATA/mhdb/onlinelog/group_5.260.982086155

5 +FRA/mhdb/onlinelog/group_5.257.982086155

6 +DATA/mhdb/onlinelog/group_6.259.982086155

    GROUP# MEMBER

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

6 +FRA/mhdb/onlinelog/group_6.408.982086157

10 +DATA/mhdb/onlinelog/group_10.283.1004716311

10 +FRA/mhdb/onlinelog/group_10.331.1004716313

11 +DATA/mhdb/onlinelog/group_11.282.1004716329

11 +FRA/mhdb/onlinelog/group_11.387.1004716329

12 +DATA/mhdb/onlinelog/group_12.275.1004716333

12 +FRA/mhdb/onlinelog/group_12.330.1004716333

13 +DATA/mhdb/onlinelog/group_13.274.1004716371

13 +FRA/mhdb/onlinelog/group_13.326.1004716371

14 +DATA/mhdb/onlinelog/group_14.289.1004716371

14 +FRA/mhdb/onlinelog/group_14.332.1004716373

    GROUP# MEMBER

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

15 +DATA/mhdb/onlinelog/group_15.288.1004716377

15 +FRA/mhdb/onlinelog/group_15.483.1004716385

24 rows selected.

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

    GROUP#    MEMBERS BYTES/1024/1024 THREAD#

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

1    2  512       1

2    2  512       1

3    2  512       1

4    2  512       2

5    2  512       2

6    2  512       2

10    2  512       1

11    2  512       1

12    2  512       1

13    2  512       2

14    2  512       2

15    2  512       2

12 rows selected.

2、日志信息

SQL> archive log list;

Database log mode       Archive Mode

Automatic archival       Enabled

Archive destination       USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1001

Next log sequence to archive   1006

Current log sequence       1006

SQL> show parameter recover;

NAME     TYPE VALUE

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

db_recovery_file_dest     string +FRA

db_recovery_file_dest_size     big integer 57270M

db_unrecoverable_scn_tracking     boolean TRUE

recovery_parallelism     integer 0

3、每天产生归档的大小

SQL> Select to_char(completion_time,'yyyy-mm-dd') as date1,count(0) as cnt,round(sum((blocks *block_size)/1024/1024)) as m

b from v$archived_loggroup by to_char(completion_time,'yyyy-mm-dd') order by date1 desc;  

DATE1  CNT      MB

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

2019-06-27    4     268

2019-06-26    6    1599

2019-06-25    6    1658

2019-06-24    4     460

2019-06-23    4     453

2019-06-22    4     398

2019-06-21   16     751

2019-06-20    6    1024

2019-06-19    6    1073

2019-06-18    6     992

2019-06-17    5    1142

DATE1  CNT      MB

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

2019-06-16    4     672

2019-06-15    4     710

2019-06-14    4     643

2019-06-13    6    1534

2019-06-12    6    1497

2019-06-11    6    1397

2019-06-10    6    1546

2019-06-09    4     675

2019-06-08    4     661

2019-06-07    1       7

21 rows selected.

SQL>  select sum(a.BLOCK_SIZE*a.BLOCKS)/1024/1024 from v$archived_log a ; 

SUM(A.BLOCK_SIZE*A.BLOCKS)/1024/1024

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

  19157.2739

【注】查看数据库数据大小信息：

 1、查看所有表空间及表空间大小：

SQL> select tablespace_name ,(sum(bytes) / 1024 / 1024) as MB from dba_data_files group by tablespace_name;

TABLESPACE_NAME        MB

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

HTTBS_MESPRD        36863.9844

SYSAUX     14336

UNDOTBS1      1024

USERS      2000

HTTBS_ES_DBA      2000

SYSTEM      4096

HTTBS_MESPRD_IDX     10240

MHDB     24576

UNDOTBS2      1024

9 rows selected.

2、查看所有表空间对应的数据文件：

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME        FILE_NAME

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

SYSTEM        +DATA/mhdb/datafile/system.265.982084581

SYSAUX        +DATA/mhdb/datafile/sysaux.258.982084619

UNDOTBS1        +DATA/mhdb/datafile/undotbs1.257.982084655

UNDOTBS2        +DATA/mhdb/datafile/undotbs2.271.982084669

USERS        +DATA/mhdb/datafile/users.270.982084671

HTTBS_MESPRD        +DATA/mhdb/datafile/httbs_mesprd.269.982084937

HTTBS_ES_DBA        +DATA/mhdb/datafile/httbs_es_dba.268.982085079

HTTBS_MESPRD_IDX        +DATA/mhdb/datafile/httbs_mesprd_idx.264.982085081

MHDB        +DATA/mhdb/datafile/mhdb.263.982085179

SYSAUX        +DATA/mhdb/datafile/sysaux.284.1004630203

HTTBS_MESPRD        +DATA/mhdb/datafile/httbs_mesprd.273.1004630353

MHDB        +DATA/mhdb/datafile/mhdb.279.1004630391

12 rows selected.