Redo logs switch too frequently

1. Observation

I do select :

SELECT dat,num FROM
(SELECT TO_CHAR(FIRST_TIME,'DD-MON-YYYY HH24:Mi') dat,
COUNT(1) num
FROM V$LOGHIST
WHERE trunc(FIRST_TIME) = trunc ( SYSDATE )
GROUP BY TO_CHAR(FIRST_TIME,'DD-MON-YYYY HH24:Mi')
HAVING COUNT(1) >= 8
ORDER BY dat desc;

As a result, I 've found that redo logs switch more than 8 every minutes,

2. Get information about DB configuration

set markup html on spool on

SPOOL logswitch.HTML

set echo on

set pagesize 30;

select systimestamp from dual;

select * from v$version;

select name,LOG_MODE from v$database;

select * from v$instance_recovery;

select OPTIMAL_LOGFILE_SIZE from v$instance_recovery;

show parameter log_buffer;

show parameter log_checkpoint_interval;

show parameter log_checkpoint_timeout;

show parameter fast_start_mttr_target;

show parameter archive_lag_target;

show parameter filesystem

show parameter disk

select name,value from v$parameter where name like '%log_archive%' and value <> 'enable';

select name from v$controlfile;

select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member

from v$logfile lf, v$log lg

where lg.group# = lf.group#

order by 1, 2;

select to_char(first_time,'YYYY-MON-DD') "Date", to_char(first_time,'DY') day,

to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",

to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",

to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",

to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",

to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",

to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",

to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",

to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",

to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",

to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",

to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",

to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",

to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",

to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",

to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",

to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",

to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",

to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",

to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",

to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",

to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",

to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",

to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",

to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23" ,

count(*) Total

from v$log_history

group by to_char(first_time,'YYYY-MON-DD'), to_char(first_time,'DY')

order by to_date(to_char(first_time,'YYYY-MON-DD'),'YYYY-MON-DD')

/

select to_char(COMPLETION_TIME,'DD/MON/YYYY') Day,

trunc(sum(blocks*block_size)/1048576/1024,2) "Size(GB)",count(sequence#) "Total Archives"

from

(select distinct sequence#,thread#,COMPLETION_TIME,blocks,block_size from v$archived_log)

group by to_char(COMPLETION_TIME,'DD/MON/YYYY')

order by to_date(to_char(COMPLETION_TIME,'DD/MON/YYYY'),'DD/MON/YYYY')

;

spool off

set markup html off spool off

3. we got output

SQL> select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member

2 from v$logfile lf, v$log lg

3 where lg.group# = lf.group#

4 order by 1, 2;

GROUP# MB STATUS ARC MEMBER

1 50 ACTIVE YES /data01/oradata/TWCMS/redo01.log

1 50 ACTIVE YES /data01/oradata/TWCMS/redo01a.log

2 50 ACTIVE YES /data01/oradata/TWCMS/redo02a.log

2 50 ACTIVE YES /data01/oradata/TWCMS/redo02.log

3 50 ACTIVE YES /data01/oradata/TWCMS/redo03.log

3 50 ACTIVE YES /data01/oradata/TWCMS/redo03a.log

4 50 ACTIVE YES /data01/oradata/TWCMS/redo04.log

4 50 ACTIVE YES /data01/oradata/TWCMS/redo04a.log

5 50 ACTIVE YES /data01/oradata/TWCMS/redo05.log

5 50 ACTIVE YES /data01/oradata/TWCMS/redo05a.log

6 50 CURRENT NO /data01/oradata/TWCMS/redo06a.log

6 50 CURRENT NO /data01/oradata/TWCMS/redo06.log

As a result, redo logs file is very small (50 mb).

4. action to be taken

Reviewed the uploaded files and found the most of the redo logs are always ACTIVE and the no of log switches are at particular times.

This is due to small redo log file size 50MB. I would request you to resize redo log file size to 500MB and monitor your database.

gather stat

begin
DBMS_STATS.GATHER_DICTIONARY_STATS();
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();
dbms_stats.gather_schema_stats(OWNNAME=>'XXX',
estimate_percent=>100,
METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',
CASCADE=>true, DEGREE=>16);
end;

check indexes status

CREATE OR REPLACE PROCEDURE P$check_ind_status
IS
  v_html  clob   :=' ';
  v_n     NUMBER := 0;

BEGIN
   --1) let loop to get email html
   v_html := '<h4>INDEXES</h4>'
               ||'<table style="border:1px solid lightgrey">'
               ||'<tr style="background-color:#85C1E9; font-family:verdana; font-size:80%;">'
               ||'<th>No</th>'
               ||'<th>Index Name</th>'
               ||'<th>Status</th>'
             ||'</tr>';

   FOR i IN (
              select index_name,status from user_indexes
               where (status <> 'VALID' and status <> 'N/A')
               union
              select index_name||':'||partition_name,status from user_ind_partitions
               where status <> 'USABLE')
   LOOP
         v_n := v_n +1;
         v_html := v_html || '<tr style="font-family:verdana; font-size:80%;">'
                            ||'<TD>'||v_n ||'</TD>'
                            ||'<TD>'|| i.index_name ||'</TD>'
                            ||'<TD>'|| i.status ||'</TD>'
                          ||'</tr>';
   END LOOP;

   --4) require send?
   IF  v_n > 0
   THEN
      v_html := v_html || '</table>';
      UTL_MAIL.send(sender => 'oracle@ababank.com',
                    recipients => '',
                    subject => '[' || SYS_CONTEXT ('USERENV', 'DB_NAME') || '] Index unusable alert',
                    message => v_html,
                    mime_type => 'text/html;charset=us-ascii');
   END IF;
    dbms_output.put_line('html: ' || v_html);
END;

backup pro eod . par

[oracle@garuda-2-17 script]$ cat FCCHOST_FCCLIVE.par
DIRECTORY=DATA_PUMP_DIR3
DUMPFILE=FCCHOST_FCCLIVE_PREEOD_201812112310_%U.DMP
LOGFILE=FCCHOST_FCCLIVE_PREEOD_201812112310.log
SCHEMAS=FCCHOST
FILESIZE=64G
EXCLUDE=statistics
EXCLUDE=grant
CONTENT=ALL
PARALLEL=8
QUERY=FCCHOST.SVTM_CIF_SIG_DET:"WHERE 1=2 "
QUERY=FCCHOST.IFTB_BRN_RECON_HIST:"WHERE 1=2 "
QUERY=FCCHOST.MSTB_ARCHIVE_OUT:"WHERE 1=2 "

pre eod backup and copy to new server

[oracle@garuda-2-17 ~]$ cat /u01/app/script/FCCHOST_FCCLIVE_PREEOD_expdp.sh
#!/bin/bash
. /home/oracle/.bash_profile
export script_dir=/u01/app/script
export DATA_PUMP_DIR=/backup/data_pump_dir
export DEST_DIR=/datastore/FCCLIVE

cd /u01/app/script
datevar=`date +%Y%m%d%H%M`

sed 's/<DATE>/'$datevar'/g' <FCCHOST_FCCLIVE_template.par >FCCHOST_FCCLIVE.par

export ORACLE_SID=FCCLIVE

sqlplus /nolog <<EOF
conn /as sysdba
set feedback off
set head off
set line 200
set pagesize 100
col object_type format a18
col status format a15
select object_type,status,count(*)
from dba_objects
where owner = 'FCCHOST'
group by object_type,status
/
exit;
EOF

expdp \' \/as sysdba \' parfile=FCCHOST_FCCLIVE.par


# Sending mail #
sqlplus /nolog <<EOF
conn /as sysdba
      BEGIN
         UTL_MAIL.send(sender => 'oracle@ababank.com',
                    recipients => 'dba@ababank.com;FlexcubeSupport@ababank.com',
                    subject => '[' || SYS_CONTEXT ('USERENV', 'DB_NAME') || '] Backup PREEOD '|| $(echo $datevar)||' Completed',
                    message => '<PRE>"$(tail -40 ${DATA_PUMP_DIR}/FCCHOST_FCCLIVE_PREEOD_${datevar}.log)"</PRE>',
                    mime_type => 'text/html;charset=us-ascii');
     END;
/
--create restore point BEFORE_RUN_EOD guarantee flashback database;
exit;
EOF

# Copy to Backup 10.152.1.52 #
scp ${DATA_PUMP_DIR}/FCCHOST_FCCLIVE_PREEOD_${datevar}* oracle@10.152.1.52:${DEST_DIR}

# deleting part #
grep "FCCHOST_FCCLIVE" ${DATA_PUMP_DIR}/FCCHOST_FCCLIVE_PREEOD_"$datevar".log|awk -F'/' '{print $4}' > filelist.txt

for filename in $(cat filelist.txt); do
a=`ssh oracle@10.152.1.52 ${DEST_DIR}"/FCCLIVE_dump_exist_check.sh "$filename`
if [ "$a" = "File is existing" ]
then
  rm ${DATA_PUMP_DIR}/$filename
fi
done

touch $script_dir/running_preeod_expdp_done.flg
if [ -f $script_dir/running_preeod_expdp.flg ]
then
  rm $script_dir/running_preeod_expdp.flg
fi

exit 1

Database size

The size of the database is the space the files physically consume on disk. You can find this with: 

select sum(bytes)/1024/1024 size_in_mb from dba_data_files;

But not all this space is necessarily allocated. There could be sections of these files that are not used. 

You can find the total space that is used with: 

select sum(bytes)/1024/1024 size_in_mb from dba_segments;

You can break this down by user by running: 

select owner, sum(bytes)/1024/1024 Size_MB from dba_segments
group  by owner;

Check used and free space:

select

"Reserved_Space(GB)", "Reserved_Space(GB)" - "Free_Space(GB)" "Used_Space(MB)","Free_Space(GB)"

from(

select

(select sum(bytes/(1014*1024*1024)) from dba_data_files) "Reserved_Space(GB)",

(select sum(bytes/(1024*1024*1024)) from dba_free_space) "Free_Space(GB)"

from dual

);

Configuring Clients for Environments with Both Shared and Dedicated Servers

If a shared server is configured on the server side, and a client connection request arrives when no dispatchers are registered, then the request is processed by a dedicated server process. If you want a particular client always to use a dispatcher, then configure (SERVER=shared) in the CONNECT_DATA section of the connect descriptor. For example:

sales=
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
  (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)
     (SERVER=shared)))

If the (SERVER=shared) attribute is configured and a dispatcher is not available, then the client connection request is rejected, and a message is sent to the client.

If the database is configured for a shared server and a particular client requires a dedicated server, then you can configure the client to use a dedicated server in one of the following ways:

• You can configure a network service name with a connect descriptor that contains (SERVER=dedicated) in the CONNECT_DATA section. For example:
  sales=
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
    (CONNECT_DATA=
       (SERVICE_NAME=sales.us.example.com)
       (SERVER=dedicated)))
• You can configure the client profile file, sqlnet.ora, with USE_DEDICATED_SERVER=on. This adds (SERVER=dedicated) to the CONNECT_DATA section of the connect descriptor the client uses.
  Note:
  If USE_DEDICATED_SERVER is set to ON, then existing (SERVER=value) entries in connect descriptors are overwritten with (SERVER=dedicated).