Huge Archive/Redo data generated

Redo log store all changes made to the database. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.

Archive is generated due to log switches.In fact dependent on the redo generated. Cause of redo generation is any change in the database like, insert, delete or update query.

If you commit frequently, the amount of redo you generate goes UP as well (not only that, but it runs slower overall).

If we have issue of huge generation of archive log as immediately we can create enough room in the mount point to accommodate archive logs by compressing or moving the files.

Parallely we can search for the session generating high redo.

Query to find sessions when session is connected:

select s.username, s.osuser, s.status,s.sql_id,  sr.*  from
  (select sid, round(value/1024/1024) as "RedoSize(MB)"
          from v$statname sn, v$sesstat ss
          where sn.name = 'redo size'
                and ss.statistic# = sn.statistic#
          order by value desc) sr,
   v$session s
where sr.sid = s.sid
and   rownum <= 10;

select sid,value from v$sesstat natural join v$statname where name='redo size' order by 2;

If session is not connected at present and wanted to know the details of session which generated more redo we can reach as follows:

1. Take AWR report,check in session "Segments by DB Blocks Changes" for the segment having more db_block_changes.Now search for sql text in session 'Complete List of SQL Text' for corresponding segments.
or
2. Below query can be helpful:

SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
        dhsso.object_name,
        SUM(db_block_changes_delta)
  FROM dba_hist_seg_stat dhss,
         dba_hist_seg_stat_obj dhsso,
         dba_hist_snapshot dhs
  WHERE dhs.snap_id = dhss.snap_id
    AND dhs.instance_number = dhss.instance_number
    AND dhss.obj# = dhsso.obj#
    AND dhss.dataobj# = dhsso.dataobj#
    AND begin_interval_time BETWEEN to_date('2018_06_04 12','YYYY_MM_DD HH24')
                                           AND to_date('2018_04_04 12','YYYY_MM_DD HH24')
  GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
           dhsso.object_name

 
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
        SUM(db_block_changes_delta)
  FROM dba_hist_seg_stat dhss,
         dba_hist_seg_stat_obj dhsso,
         dba_hist_snapshot dhs
  WHERE dhs.snap_id = dhss.snap_id
    AND dhs.instance_number = dhss.instance_number
    AND dhss.obj# = dhsso.obj#
    AND dhss.dataobj# = dhsso.dataobj#
    AND dhsso.object_name = 'OWN_OBJECT'
  GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI')
 
 
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
         dbms_lob.substr(sql_text,4000,1),
         dhss.instance_number,
         dhss.sql_id,executions_delta,rows_processed_delta
  FROM dba_hist_sqlstat dhss,
         dba_hist_snapshot dhs,
         dba_hist_sqltext dhst
  WHERE UPPER(dhst.sql_text) LIKE '%OWN_OBJECT%'
    AND dhss.snap_id=dhs.snap_id
    AND dhss.instance_Number=dhs.instance_number
    AND dhss.sql_id = dhst.sql_id

SELECT instance_number, to_char(sample_time,'yyyy_mm_dd hh24:mi:ss'),
         user_id,
         program
  FROM dba_hist_active_sess_history
  WHERE sql_id = 'jy5i4p6t05v'
    AND snap_id BETWEEN 53234 AND 53245

Related doc id:

Diagnosing excessive redo generation [ID 199298.1]
Excessive Archives / Redo Logs Generation Troubleshooting [ID 832504.1]
Troubleshooting High Redo Generation Issues [ID 782935.1]
How to Disable (Temporary) Generation of Archive Redo Log Files [ID 177218.1]

Excessive Archives / Redo Logs Generation Troubleshooting [ID 832504.1]
NOTE:1035935.6 - Example of How To Resize the Online Redo Logfiles
NOTE:111886.1 - How to Setup LogMiner
NOTE:167492.1 - SQL: How to Find Sessions Generating Lots of Redo or Archive logs
NOTE:188691.1 - How to Avoid Generation of Redolog Entries
NOTE:199298.1 - Diagnosing excessive redo generation
NOTE:300395.1 - Using LogMiner, How to determine the cause of lots of redo generation.
NOTE:45042.1 - Archiver Best Practices
NOTE:461100.1 - STARTUP ERRORS ora-00824 cannot set sga_target with statistics_level=BASIC

Master Note: Troubleshooting Redo Logs and Archiving[Article ID 1507157.1]
SQL: How to Find Sessions Generating Lots of Redo or Archive logs [Article ID 167492.1]
Troubleshooting High Redo Generation Issues[Article ID 782935.1]

=========================================================================High redo generation is always a consequence of certain activity in the database.

The main cause of high redo generation is usually a high DML activity during a certain period of time and it’s a good practice to first examine modifications on either database level (parameters, any maintenance operations,…) and application level (deployment of new application, modification in the code, increase in the users,..).

What we need to examine:

Is supplemental logging enabled? The amount of redo generated when supplemental logging is enabled is quite high when compared to when supplemental logging is disabled.

What Causes High Redo When Supplemental Logging is Enabled (Doc ID 1349037.1)

Are a lot of indexes being used?, reducing the number of indexes or using the attribute NOLOGGING will reduce the redo considerably.

Are all the operation really in need of the use of LOGGING? From application we can reduce redo by making use of the clause NOLOGGING. Note that only the following operations can make use of NOLOGGING mode:

- direct load (SQL*Loader)

- direct-load INSERT

- CREATE TABLE ... AS SELECT

- CREATE INDEX

- ALTER TABLE ... MOVE PARTITION

- ALTER TABLE ... SPLIT PARTITION

- ALTER INDEX ... SPLIT PARTITION

- ALTER INDEX ... REBUILD

- ALTER INDEX ... REBUILD PARTITION

- INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line

To confirm if the table or index has "NOLOGGING" set.

Issue the following statement.

select table_name,logging from all_tables where table_name = <table name>;

-or-

select table_name,logging from all_indexes where index_name = <index name>;

Do tables have triggers that might cause some indirect DML on other tables?

Is Auditing enabled the contributor for this excessive redo generation?

Are tablespaces in hot backup mode?

select a.tablespace_name

from sys.dba_data_files a, sys.v_$backup b

where b.status = ‘ACTIVE’

and b.file# = a.file_id

group by a.tablespace_name;

Examine the log switches:

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')

This will give us an idea of the times when the high peaks of redo are happening

 Examine AWR report:

Next step will be examining the AWR from the hour where we have had the highest number of log switches, and confirm with the redo size that these log switches are actually caused by a lot of redo generation.

In the AWR we can also see the sql with most of the gets/executions to have an idea of the activity that is happening in the database and generating redo and we can also see the segments with the biggest number of block changes and the sessions performing these changes.Another way to find these sessions is described in SQL: How to Find Sessions Generating Lots of Redo or Archive logs (Doc ID 167492.1)

To find these segments we can also use queries:

SELECT to_char(begin_interval_time,'YY-MM-DD HH24') snap_time,

dhso.object_name,

sum(db_block_changes_delta) BLOCK_CHANGED

FROM dba_hist_seg_stat dhss,

dba_hist_seg_stat_obj dhso,

dba_hist_snapshot dhs

WHERE dhs.snap_id = dhss.snap_id

AND dhs.instance_number = dhss.instance_number

AND dhss.obj# = dhso.obj#

AND dhss.dataobj# = dhso.dataobj#

AND begin_interval_time BETWEEN to_date('11-01-28 13:00','YY-MM-DD HH24:MI') <<<<<<<<<<<< Need to modify the time as per the above query where more redo log switch happened (keep it for 1 hour)

AND to_date('11-01-28 14:00','YY-MM-DD HH24:MI') <<<<<<<<<<<< Need to modify the time as per the above query where more redo log switch happened (interval shld be only 1 hour)

GROUP BY to_char(begin_interval_time,'YY-MM-DD HH24'),

dhso.object_name

HAVING sum(db_block_changes_delta) > 0

ORDER BY sum(db_block_changes_delta) desc ;

-- Then : What SQL was causing redo log generation :

SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24') WHEN,

dbms_lob.substr(sql_text,4000,1) SQL,

dhss.instance_number INST_ID,

dhss.sql_id,

executions_delta exec_delta,

rows_processed_delta rows_proc_delta

FROM dba_hist_sqlstat dhss,

dba_hist_snapshot dhs,

dba_hist_sqltext dhst

WHERE upper(dhst.sql_text) LIKE '%<segment_name>%' >>>>>>>>>>>>>>>>>> Update the segment name as per the result of previous query result

AND ltrim(upper(dhst.sql_text)) NOT LIKE 'SELECT%'

AND dhss.snap_id=dhs.snap_id

AND dhss.instance_number=dhs.instance_number

AND dhss.sql_id=dhst.sql_id

AND begin_interval_time BETWEEN to_date('11-01-28 13:00','YY-MM-DD HH24:MI') >>>>>>>>>>>> Update time frame as required

AND to_date('11-01-28 14:00','YY-MM-DD HH24:MI') >>>>>>>>>>>> Update time frame as required

 Finally, to troubleshoot further the issue and know the exact commands are being recorded at that particular time frame we can use log miner and mine the archivelog from the concerned time frame. We can look on v$archived_log and find the archived log generated at that particular time frame.

How To Determine The Cause Of Lots Of Redo Generation Using LogMiner (Doc ID 300395.1)

1) Query V$SESS_IO

This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session.

High values indicate a session generating lots of redo. The query you can use is:

select s.sid, s.serial#, s.username, s.program, i.block_changes

from v$session s, v$sess_io i

where s.sid = i.sid

order by 5 desc, 1,2,3,4;

Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES.

Large deltas indicate high redo generation by the session.

2) Query V$TRANSACTION

This view contains information about the amount of undo blocks and undo records accessed by the transaction

(as found in the USED_UBLK and USED_UREC columns). The query you can use is:

SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec

FROM v$session s, v$transaction t

WHERE s.taddr = t.addr

ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of USED_UBLK and USED_UREC.

Large deltas indicate high redo generation by the session.

Check which session is generating more redo.

set pages 1000

set lines 140

SELECT s.sid, s.serial#, s.username, s.program,

i.block_changes

FROM v$session s, v$sess_io i

WHERE s.sid = i.sid

ORDER BY 5 desc, 1, 2, 3, 4;

 

MAX_STRING_SIZE Parameter In Oracle 12c:

Parameter "MAX_STRING_SIZE" is introduced in Oracle 12c.This controls the maximum size of string size in Oracle database.Parameter value either can be set to  STANDARD or EXTENDED
Default value is STANDARD.

MAX_STRING_SIZE = STANDARD means the maximum size of strings is 4000 bytes for VARCHAR2 and NVARCHAR2.

MAX_STRING_SIZE = EXTENDED means maximum size can be upto 32767 byte .

We can change the parameter value from STANDARD to EXTENDED but not possible from EXTENDED to STANDARD.


If MAX_STRING_SIZE value set to STANDARD and  try to set the length of column more than 4000,will return ORA-00910.

SQL> show parameter MAX_STRING_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD


SQL> create table country ( state_name VARCHAR2(7000));
create table country ( state_name VARCHAR2(7000))
                                                *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

This is how we can convert MAX_STRING_SIZE to EXTENDED:

1. Start database in upgrade mode:

SQL> show parameter MAX_STRING_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>STARTUP UPGRADE

ORACLE instance started.
Total System Global Area 900000 bytes
Fixed Size 666 bytes
Variable Size 271790 bytes
Database Buffers 271790 bytes
Redo Buffers 4140 bytes
Database mounted.
Database opened.

2. Change the value to EXTENDED

SQL>  alter system set MAX_STRING_SIZE='EXTENDED' SCOPE=BOTH;

System altered.

SQL> show parameter MAX_STRING_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED

3.Run script utl32k.sql :

@?/rdbms/admin/utl32k.sql

4.Restart the database:

shutdown immediate;
startup
SQL> show parameter MAX_STRING_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD

Know Issues:

1. If you try to restart the database without running utl32k.sql script, got below error.

Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14695: MAX_STRING_SIZE migration is incomplete
Process ID: 18669
Session ID: 401 Serial number: 16419

So start the database again in UPGRADE mode and execute utlk32.sql script.

2. If you try to set the value to EXTENDED , when database is not in UPGRADE mode.


SQL> alter system set MAX_STRING_SIZE=EXTENDED;
alter system set MAX_STRING_SIZE=EXTENDED
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration

before running this alter statement, need to start database in UPGRADE mode.

ADOP Phase: abort,Restart and Abandon

adop defaults to abandon=no restart=yes if not specified,

To restart from beginning,  use restart=no abandon=yes

adop phase=apply patches=11111,11111 abandon=yes          (new patch)

To restart a patch you need to use restart=yes abandon=no .

adop phase=apply patches=11111 restart=yes [abandon=no is implied and not required]

eg. adop restart=no abandon=yes  phase=apply
If unable to restart, check for these two AD_ADOP_SESSIONS and AD_ADOP_SESSION_PATCHES tables and analyze the o/p:
---------------------------------------------------------------------------------------

column id format 99
column nn format a10
column nt format a6
select adop_session_id id, prepare_status pr, apply_status ap,
finalize_status fi, cutover_status cu, cleanup_status cl, abort_status ab, status st, node_name nn, node_type nt
from ad_adop_sessions
order by adop_session_id desc;


column id format 99
column bn format a12
column pr format 99999999
column afs format a40
column pfs format a40
column cs format a9
column nn format a10
column ed format a28
column drv format a28
column pt format a35

select adop_session_id id, bug_number bn, patchrun_id pr, status st,
node_name nn, cast(end_date as timestamp) ed, driver_file_name drv, patch_top  pt
from ad_adop_session_patches
order by end_date desc;

To diagnostic more for the root cause why patch application failed, check for adpatch log for the failed patch 1111.
cd  $APPL_TOP_NE/../log/adop/<session id>


If we wants to start from a fresh page and considering the state of these tables, making a manual modification to the  AD_ADOP_SESSIONS table just so it would not hold up ADOP execution due to an  incomplete hotpatch session.

update ad_adop_sessions set status='C' where adop_session_id=<from above o/p>;

restart adop and retest.

=================================

Aborting an Online Patching Cycle:

If a patching cycle is failing and the issue cannot be resolved quickly, it is possible to abort the patching cycle and return to normal runtime operation. The patch edition will be dropped. You can abandon a patching cycle (without applying any patches) by running the command:

$ adop phase=abort

Important: This abort command can only be used before successful completion of the cutover phase. After cutover, the system is running on the new edition, and abort is no longer possible for that patching cycle. Aborting a patching cycle will drop the patch edition, but you must then run the cleanup and fs_clone phases before starting a new patching cycle. The cleanup must be a full cleanup.

For example:

$ adop phase=prepare

$ adop phase=apply patches=123456

[Patch application encounters problems and you want to abort]

$ adop phase=abort

$ adop phase=cleanup cleanup_mode=full

$ adop phase=fs_clone

Optionally, you can combine the abort and cleanup commands as follows:

$ $ adop phase=abort,cleanup cleanup_mode=full

Note: You cannot abort application of a patch applied in hotpatch mode or downtime mode.

ADOP apply phase options ABANDON and RESTART

Restarting a failed worker :

When adop is using parallel processing and an error occurs, the job fails. Review the main adop log file and the adworkxxx.log file to determine the source of the error, resolve the issues and continue. Restart adop using the adctrl command.

Restarting adop :

If you have shut down the workers, or if adop quits while performing processing actions, it saves all the actions completed up to that point in restart files. Investigate and resolve the problem that caused the failure, then restart adop.

restart options,

restart=(yes|no)  [default: no]

Use restart=yes to resume the previous failed apply command from where processing terminated.  If an apply command fails, check the log files for further information.  If the problem can be corrected, you can then restart the apply command where it left off using the restart parameter.

Example - Restart a apply command

adop phase=apply patches=123456 restart=yes

When restarting a failed apply it is important to use the same parameters as the failed command, with only the addition of the restart=yes parameter.

abandon=(yes|no)  [default: no]

Use abandon=yes to abandon the previous failed apply command and start a new apply command.  Note that any changes made to the system by the failed command will remain in effect. The abandon flag is most useful when applying a replacement patch for the failing patch.

Example - Abandon previous apply command and apply replacement

adop phase=apply patches=223456 abandon=yes

If a patch fails to apply and there is no replacement patch, you may also abort the complete online patching cycle. 

You have several options when restarting (or abandoning) application of individual patches, as follows :

• If you want to restart a failed patch from where it left off, you only need to specify restart=yes on the command line:

adop phase=apply patches=1234 restart=yes

• If you want to restart a failed patch from the very beginning, you need to specify abandon=yes on the command line:

adop phase=apply patches=1234 abandon=yes

• If you want to ignore a previously failed patch and apply a different one instead, you need to specify the new patch number and abandon=yes on the command line:

adop phase=apply patches=5678 abandon=yes

================================================================================

ADOP Useful Options 12.2

To restart worker where failed, while applying patch using "adop" in 12.2

wait_on_failed_job=(yes|no)  [default: no]

        Controls whether adop apply command exits when all workers have

        failed.  Instead of exiting, you can force adop to wait, and use

        the "adctrl" to retry failed jobs.

adop phase=apply patches=123456 wait_on_failed_job=yes

NB: This works only for Worker jobs, Not for objects compilation fails, like forms, pll's.

For these you have to use "flags=autoskip"

adop phase=apply patches=123456 flags=autoskip

 skipsyncerror=(yes|no)  [default: no]

        Specifies whether to ignore errors that may occur during incremental

        file system synchronization.  This might happen if you applied

        a patch in the previous patching cycle that had errors but decided

        to continue with the cutover.  When the patch is synchronized on

        the next patching cycle, the apply errors may occur again, but

        can be ignored.

        Example:

            adop phase=prepare skipsyncerror=yes

Aborting an online patching cycle:

        If an online patching cycle encounters problems that cannot be

        fixed immediately you can abort the patching cycle and return

        to normal runtime operation.

        Example - Aborting a failed online patching cycle:

            adop phase=prepare

            adop phase=apply patches=123456

            ### serious unfixable error reported

            adop phase=abort

            adop phase=cleanup cleanup_mode=full

            adop phase=fs_clone

        The abort command drops the database patch edition and

        returns the system to normal runtime state.  Immediately

        following abort, you must also run a full cleanup and

        fs_clone operation to fully remove effects of the failed

        online patching cycle.

adop cutover hang

If an adop cutover hangs or a server has crash or reboot issues in the middle of the adop cutover phase, execute the following steps to fix the issue and then proceed with the patch process.

SOLUTION

Make sure no services or processes are running from the PATCH file system.

Ensure that the Weblogic Admin Server and Node Manager are running on the run file system. Execute the following commands to check the status:

 $ adadminsrvctl.sh status

 $ adnodemgrctl.sh status

Execute the following commands:

 $ adop phase=abort

 $ adop phase=cleanup cleanup_mode=full

 $ adop phase=fs_clone force=yes

Run an empty adop cycle to make sure there is no issue in the adop cutover by executing the following command:

 $adop phase=prepare, finalize, cutover, cleanup cleanup_mode=full

Start a fresh adop prepare and apply patches.

After the apply step, complete the rest of the adop phases including finalize, cutover, and cleanup.

Trying to resume a failed cutover session giving Invalid Credentials in Oracle Apps R12.2

If you attempt to resume a failed session after cutover exits with cutover_status=3, I receive an 'Invalid Credentials' error.

To check the status you can use the query as earlier posted.

This will be because the database patch edition has already been promoted to be the new run edition. To resume and complete cutover successfully, run the command:

$ adop phase=cutover action=nodb

http://soban-dba.blogspot.com/2017/04/adop-options-availble-in-r122.html