Monday 29 November 2021

rman backup failed with error RMAN-03009: failure of REFAF command on 19c standby database

Production database backups which are running on standby side are failing with errors, 

lets troubleshoot rman failure and fix  


RMAN Backup Status:

START_TIME       END_TIME         OUTPUT_DEVICE_TYP INPUT_TYPE   STATUS       TIME_TAKEN_D INPUT_BYTES_ OUTPUT_BYTES

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

11/28/21 20:01   11/28/21 21:51   DISK              DB INCR      FAILED       01:50:12       643.48G      555.74G


Check rman log file:

Starting backup at Nov 28 2021 21:51:31

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=7524 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=8229 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/DB_BACKUP/prddb211/incr/post_bkup_.bak tag=TAG20211128T215131 RECID=75 STAMP=1089841892

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at Nov 28 2021 21:51:32

Starting Control File and SPFILE Autobackup at Nov 28 2021 21:51:32

piece handle=/DB_BACKUP/prddb211/arch/c-1132445964-20211128-17 comment=NONE

Finished Control File and SPFILE Autobackup at Nov 28 2021 21:51:36

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of REFAF command on default channel at 11/28/2021 21:51:36

ORA-01804: failure to initialize timezone information


Note: as per log , error related to timezone information


Lets verify timezone version on both primary  and standby databases 

TZ_VERSION on Source and target:

SQL> SELECT tz_version FROM registry$database;

TZ_VERSION

----------

        35

Timezone version is same across primary & standby databases, 


Verify patch details on primary  and standby servers

On Primary db:

$opatch lspatches

31335037;RDBMS - DSTV35 UPDATE - TZDATA2020A

32876380;OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)

32916816;OCW RELEASE UPDATE 19.12.0.0.0 (32916816)

32904851;Database Release Update : 19.12.0.0.210720 (32904851)

OPatch succeeded.


On Standby db:

$ opatch lspatches

32876380;OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)

32916816;OCW RELEASE UPDATE 19.12.0.0.0 (32916816)

32904851;Database Release Update : 19.12.0.0.210720 (32904851)


Note: Timezone patch not applied on standby side, we have to match patch level on standby with primary 


Apply timezone patch on standby

Patch apply steps on Standby:

Copy patch p31335037_190000_Linux-x86-64.zip 

unzip p31335037_190000_Linux-x86-64.zip

$ cd 31335037/

Patch Precheck:

$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.27

Copyright (c) 2021, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/19.8.0_64

Central Inventory : /app/oraInventory

   from           : /u01/app/oracle/product/19.8.0_64/oraInst.loc

OPatch version    : 12.2.0.1.27

OUI version       : 12.2.0.7.0

Log file location : /u01/app/oracle/product/19.8.0_64/cfgtoollogs/opatch/opatch2021-11-28_22-49-25PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.


Shutdown standby db before patch apply and start db , mrp after patch 


$ opatch apply 

Oracle Interim Patch Installer version 12.2.0.1.27

Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19.8.0_64

Central Inventory : /app/oraInventory

   from           : /u01/app/oracle/product/19.8.0_64/oraInst.loc

OPatch version    : 12.2.0.1.27

OUI version       : 12.2.0.7.0

Log file location : /u01/app/oracle/product/19.8.0_64/cfgtoollogs/opatch/opatch2021-11-28_22-49-55PM_1.log


Verifying environment and performing prerequisite checks...

OPatch continues with these patches:   31335037  


Do you want to proceed? [y|n]

y

User Responded with: Y

All checks passed.

Backing up files...

Applying interim patch '31335037' to OH '/u01/app/oracle/product/19.8.0_64'

Patching component oracle.oracore.rsf, 19.0.0.0.0...

Patch 31335037 successfully applied.

Log file location: /u01/app/oracle/product/19.8.0_64/cfgtoollogs/opatch/opatch2021-11-28_22-49-55PM_1.log

OPatch succeeded.


Standby patch list matched with primary side now

$ opatch lspatches

31335037;RDBMS - DSTV35 UPDATE - TZDATA2020A

32876380;OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)

32916816;OCW RELEASE UPDATE 19.12.0.0.0 (32916816)

32904851;Database Release Update : 19.12.0.0.210720 (32904851)

OPatch succeeded.



rman backup completed successfully after applying timezone patch on standby database


START_TIME       END_TIME         OUTPUT_DEVICE_TYP INPUT_TYPE   STATUS       TIME_TAKEN_D INPUT_BYTES_ OUTPUT_BYTES

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

11/28/21 20:01   11/28/21 21:51   DISK              DB INCR      FAILED       01:50:12       643.48G      555.74G

11/28/21 23:00   11/28/21 23:07   DISK              DB INCR      COMPLETED    00:07:14        49.67G       29.97G


Thursday 11 November 2021

Rman backup commands

Usefull rman backup commands listed below , hope it will help

DB full backup include Archive backup:
RUN
{
  ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;
  BACKUP
  FORMAT '/bkp_location/%d_D_%T_%u_s%s_p%p'
  DATABASE
  PLUS ARCHIVELOG
  FORMAT 'bkp_location/%d_A_%T_%u_s%s_p%p';
  RELEASE CHANNEL ch11;
}

Incremental backup:

RUN
{
CONFIGURE DEVICE TYPE disk PARALLELISM 2;
ALLOCATE CHANNEL disk1 DEVICE TYPE disk  FORMAT 'D:\Bkp_Standby\ForStandby_%U' maxpiecesize 40 G;
ALLOCATE CHANNEL disk2 DEVICE TYPE disk  FORMAT 'C:\db_inc_bkp\ForStandby_%U' maxpiecesize 30 G;
BACKUP INCREMENTAL FROM SCN 32079657 DATABASE;
}

Archive Backup:
RUN {
     CONFIGURE DEVICE TYPE disk PARALLELISM 4;
     set archivelog destination to 'bkp_location';
     BACKUP  ARCHIVELOG  from logseq 56782 until logseq
56789 thread 1   format '/bkp_location/%d_D_%T_%u_s%s_p%p';
    }
     
Decrypted encrypted backup:
RMAN> set DECRYPTION identified by 'password';

Point In Time restore and recovery:
RMAN> run {
allocate channel ch01 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
set until time="to_date('2017/10/19 18:00:00', 'yyyy/mm/dd hh24:mi:ss')";
restore database;
recover database;
}

Estimate RMAN Duplicate or Clone Timings:

alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,

   sysdate + TIME_REMAINING/3600/24 end_at

    from gv$session_longops

    where totalwork > sofar

    AND opname NOT LIKE '%aggregate%'

    AND opname like 'RMAN%';