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

Job Management with DBMS_SCHEDULER in Oracle (Creation, Monitoring, and Management)

Oracle's DBMS_SCHEDULER package offers a powerful and flexible solution for automating, managing, and monitoring tasks like data backups, report generation, and more. This article will walk you through job management with DBMS_SCHEDULER, explaining how to enable, disable, monitor, and troubleshoot jobs to ensure your system runs smoothly.
Creating a Job with DBMS_SCHEDULER:
Here’s a simple example of how to create a job that runs a PL/SQL procedure on a regular basis. 
BEGIN
   DBMS_SCHEDULER.create_job (
      job_name        => 'BACKUP_DB_JOB',    -- Name of the job
      job_type        => 'PLSQL_BLOCK',      -- Type of job (PLSQL_BLOCK, EXECUTABLE, etc.)
      job_action      => 'BEGIN BACKUP_DATABASE; END;', -- PL/SQL block to execute
      start_date      => SYSTIMESTAMP,       -- Start time (current timestamp)
      repeat_interval => 'FREQ=DAILY; BYHOUR=0; BYMINUTE=0; BYSECOND=0;', -- Job runs daily at midnight
      enabled         => TRUE,               -- Enable the job immediately after creation
      comments        => 'Daily Backup Job for Database'
   );
END;
/
Explanation of the Script:
    job_name: The name of the job. In this case, the job is called BACKUP_DB_JOB.
    job_type: Specifies the type of job. For this example, we are using PLSQL_BLOCK, which means the job will execute a PL/SQL anonymous block. You can also use EXECUTABLE for shell scripts or STORED_PROCEDURE to call a stored procedure directly.
    job_action: This is the PL/SQL block that will be executed when the job runs. In this case, it calls the BACKUP_DATABASE procedure (which could be a procedure you’ve already created for performing a database backup).
    start_date: Defines when the job should start. SYSTIMESTAMP ensures that the job starts immediately upon creation.
    repeat_interval: This defines how often the job should run. In this example, the job is scheduled to run daily at midnight using the cron-like format FREQ=DAILY; BYHOUR=0; BYMINUTE=0; BYSECOND=0;.
    enabled: This parameter, when set to TRUE, means that the job will be enabled immediately after creation. If set to FALSE, the job will be created in a disabled state.
    comments: An optional comment field to provide additional information about the job.

Once the job is created, we can verify job details using DBA_SCHEDULER_JOBS view:
SELECT job_name, enabled, repeat_interval, start_date FROM dba_scheduler_jobs WHERE job_name = 'BACKUP_DB_JOB';

Job Management:
we can perform various task using DBMS_SCHEDULER package such as ENABLE, DISABLE, STOP_JOB, and several others.
Enabling and Disabling Jobs:
Enable job:
BEGIN
    DBMS_SCHEDULER.enable (NAME => 'RMAN_INC');
END;
/
Disable job:
BEGIN
    DBMS_SCHEDULER.disable (NAME => 'RMAN_INC');
END;
/
Disable job forcefully:
BEGIN
    DBMS_SCHEDULER.disable (NAME => 'RMAN_INC', FORCE => TRUE);
END;
/
Monitoring Jobs:  
Finding Jobs Currently Running:
SELECT job_name, session_id, running_instance, elapsed_time,cpu_used FROM dba_scheduler_running_jobs;
 
Viewing Job History:
SELECT job_name, log_date, status, actual_start_date,run_duration, cpu_used FROM dba_scheduler_job_run_details;
Stopping Running Jobs:
BEGIN
    DBMS_SCHEDULER.stop_job (JOB_NAME => 'RMAN_INC');
END;
/
Stop forcefully:
BEGIN
    DBMS_SCHEDULER.stop_job (JOB_NAME => 'RMAN_INC', FORCE => TRUE);
END;

 

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%'; 

ps -ef Command Cheat Sheet

## Basic Syntax and Options
1. ps -ef                    # Basic full format listing
2. ps -ef -ww               # Full format with unlimited width
3. ps -efw                  # Wide format
4. ps -ef --cols 1000       # Set output width to 1000
5. ps -ef --no-headers      # Remove header line

## Output Formatting
6. ps -ef -o pid,ppid,user,cmd    # Custom columns
7. ps -ef -o pid,ppid,user,%cpu,%mem,cmd  # With CPU and memory
8. ps -ef -o pid,ppid,user,cmd --sort=-pid  # Sort by PID
9. ps -ef -o pid,ppid,user,cmd --sort=-%cpu # Sort by CPU usage
10. ps -ef -o pid,ppid,user,cmd --sort=-%mem # Sort by memory usage

## Process Filtering
11. ps -ef | grep <pattern>        # Basic grep
12. ps -ef | grep -i <pattern>     # Case-insensitive
13. ps -ef | grep -v <pattern>     # Exclude pattern
14. ps -ef | grep -A 2 -B 2 <pattern>  # Show context
15. ps -ef | grep -E "pattern1|pattern2"  # Multiple patterns

## Process Tree
16. ps -ef --forest               # Show process tree
17. ps -ef --forest | grep <pattern>  # Filter process tree
18. ps -ef --forest -o pid,ppid,user,cmd  # Custom tree format
19. ps -ef --forest --sort=-%cpu  # Tree sorted by CPU
20. ps -ef --forest --sort=-%mem  # Tree sorted by memory

## User-Specific Commands
21. ps -ef | grep ^root          # Root processes
22. ps -ef | grep ^$USER         # Current user processes
23. ps -ef -u username           # Specific user processes
24. ps -ef -u root,username      # Multiple users
25. ps -ef | grep -E "^root|^$USER"  # Root and current user

## CPU and Memory Monitoring
26. ps -ef --sort=-%cpu | head -n 10  # Top 10 CPU processes
27. ps -ef --sort=-%mem | head -n 10  # Top 10 memory processes
28. ps -ef -o pid,ppid,user,%cpu,%mem,cmd --sort=-%cpu  # Detailed CPU
29. ps -ef -o pid,ppid,user,%cpu,%mem,cmd --sort=-%mem  # Detailed memory
30. ps -ef | awk '$3 > 50 {print}'  # High CPU usage

## Process Timing
31. ps -ef | grep "$(date +%H:%M)"  # Current hour:minute
32. ps -ef | grep "$(date +%H)"     # Current hour
33. ps -ef -o pid,ppid,user,lstart,cmd  # With start time
34. ps -ef -o pid,ppid,user,etime,cmd   # With elapsed time
35. ps -ef | grep -E "$(date +%H):[0-5][0-9]"  # Current hour range

## Process State
36. ps -ef | grep -i "zombie"      # Zombie processes
37. ps -ef | grep -i "defunct"     # Defunct processes
38. ps -ef | grep -i "sleep"       # Sleeping processes
39. ps -ef | grep -i "running"     # Running processes
40. ps -ef | grep -i "stopped"     # Stopped processes

## Port and Network
41. ps -ef | grep <port_number>    # Processes by port
42. ps -ef | grep -i "listen"      # Listening processes
43. ps -ef | grep -i "established" # Established connections
44. ps -ef | grep -i "netstat"     # Network statistics
45. ps -ef | grep -i "tcp"         # TCP processes

## Application Specific
46. ps -ef | grep java            # Java processes
47. ps -ef | grep python          # Python processes
48. ps -ef | grep nginx           # Nginx processes
49. ps -ef | grep apache          # Apache processes
50. ps -ef | grep mysql           # MySQL processes

## Process Relationships
51. ps -ef | grep -E "^[0-9]+ [0-9]+ 1 "  # Direct children of init
52. ps -ef | grep -E "^[0-9]+ [0-9]+ $PPID "  # Children of current process
53. ps -ef | grep -E "^[0-9]+ $PPID "  # Parent of current process
54. ps -ef | grep -E "^[0-9]+ [0-9]+ $PID "  # Children of specific PID
55. ps -ef | grep -E "^[0-9]+ $PID "  # Parent of specific PID

## Resource Monitoring
56. ps -ef | awk '$3 > 50 {print}'  # High CPU usage
57. ps -ef | awk '$4 > 50 {print}'  # High memory usage
58. ps -ef | awk '$3 > 50 || $4 > 50 {print}'  # High CPU or memory
59. ps -ef | awk '$3 > 50 && $4 > 50 {print}'  # High CPU and memory
60. ps -ef | awk '$3 > 50 {print $2}'  # PIDs with high CPU

## Process Cleanup
61. ps -ef | grep -i "defunct" | awk '{print $2}'  # Defunct PIDs
62. ps -ef | grep -i "zombie" | awk '{print $2}'   # Zombie PIDs
63. ps -ef | grep -i "stopped" | awk '{print $2}'  # Stopped PIDs
64. ps -ef | grep -i "sleep" | awk '{print $2}'    # Sleeping PIDs
65. ps -ef | grep -i "running" | awk '{print $2}'  # Running PIDs

## Process Analysis
66. ps -ef | grep -i "cron"        # Cron jobs
67. ps -ef | grep -i "daemon"      # Daemon processes
68. ps -ef | grep -i "systemd"     # Systemd processes
69. ps -ef | grep -i "kernel"      # Kernel processes
70. ps -ef | grep -i "init"        # Init processes

## Process Monitoring
71. watch -n 1 'ps -ef | grep <pattern>'  # Real-time monitoring
72. ps -ef | grep <pattern> | wc -l  # Count matching processes
73. ps -ef | grep <pattern> | awk '{print $2}'  # Get PIDs
74. ps -ef | grep <pattern> | awk '{print $1}'  # Get users
75. ps -ef | grep <pattern> | awk '{print $3}'  # Get PPIDs

## Process Statistics
76. ps -ef | awk '{print $1}' | sort | uniq -c  # Process count by user
77. ps -ef | awk '{print $3}' | sort | uniq -c  # Process count by PPID
78. ps -ef | awk '{print $2}' | sort | uniq -c  # Process count by PID
79. ps -ef | awk '{print $5}' | sort | uniq -c  # Process count by TTY
80. ps -ef | awk '{print $6}' | sort | uniq -c  # Process count by time

## Process Debugging
81. ps -ef | grep -i "error"       # Error processes
82. ps -ef | grep -i "fail"        # Failed processes
83. ps -ef | grep -i "crash"       # Crashed processes
84. ps -ef | grep -i "hang"        # Hung processes
85. ps -ef | grep -i "dead"        # Dead processes

## Process Management
86. ps -ef | grep -i "kill"        # Kill processes
87. ps -ef | grep -i "stop"        # Stop processes
88. ps -ef | grep -i "start"       # Start processes
89. ps -ef | grep -i "restart"     # Restart processes
90. ps -ef | grep -i "reload"      # Reload processes

## Process Security
91. ps -ef | grep -i "root"        # Root processes
92. ps -ef | grep -i "sudo"        # Sudo processes
93. ps -ef | grep -i "su"          # Su processes
94. ps -ef | grep -i "ssh"         # SSH processes
95. ps -ef | grep -i "telnet"      # Telnet processes

## Process Optimization
96. ps -ef | grep -i "high"        # High resource usage
97. ps -ef | grep -i "low"         # Low resource usage
98. ps -ef | grep -i "normal"      # Normal resource usage
99. ps -ef | grep -i "critical"    # Critical processes
100. ps -ef | grep -i "important"  # Important processes

## Best Practices
- Always use -ww or --cols for full command lines
- Use less or more for long outputs
- Use grep --color=auto for better visibility
- Save output to file for analysis
- Use --forest to understand process relationships
- Use --sort to organize output by different criteria
- Use awk for complex filtering
- Use watch for real-time monitoring
- Use custom formats for specific needs
- Use process trees for relationship analysis %