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

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

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

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

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