ORACLE DATABASE PROBLEM AND SOLUTIONS

Saturday, May 24, 2025

How delete obsolete rman backup ?

RMAN> delete obsolete ;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 1

using channel ORA_DISK_1

Deleting the following obsolete backups and copies:

Type Key Completion Time Filename/Handle

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

Archive Log 1 06-MAR-25 /u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_03_06/o1_mf_1_6_mwm73otb_.arc

Backup Set 1 06-MAR-25

Backup Piece 1 06-MAR-25 /u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_03_06/o1_mf_s_1195045112_mwm6zsr4_.bkp

Archive Log 2 22-MAR-25 /u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_03_22/o1_mf_1_7_mxwwwloz_.arc

Archive Log 3 28-APR-25 /u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_04_28/o1_mf_1_8_n0y2ykq0_.arc

Archive Log 4 29-APR-25 /u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_04_29/o1_mf_1_9_n10thgfr_.arc

Backup Set 2 29-APR-25

Backup Piece 2 29-APR-25 /u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_04_29/o1_mf_s_1199689267_n10y9nxy_.bkp

Backup Set 3 29-APR-25

Backup Piece 3 29-APR-25 /u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_04_29/o1_mf_s_1199689871_n10ywkbc_.bkp

Archive Log 5 30-APR-25 /u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_04_30/o1_mf_1_10_n13lcxjj_.arc

Backup Set 4 29-APR-25

Backup Piece 4 29-APR-25 /u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_04_29/o1_mf_s_1199690476_n10zhg00_.bkp

Archive Log 6 01-MAY-25 /u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_05_01/o1_mf_1_11_n162t9hh_.arc

Archive Log 7 21-MAY-25 /u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_05_21/o1_mf_1_12_n2v9ht9q_.arc

Archive Log 8 21-MAY-25 /u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_05_21/o1_mf_1_13_n2vbzlxk_.arc

Archive Log 9 21-MAY-25 /u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_05_21/o1_mf_1_14_n2vbznnl_.arc

Archive Log 10 21-MAY-25 /u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_05_21/o1_mf_1_15_n2vbzzdo_.arc

Archive Log 11 21-MAY-25 /u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_05_21/o1_mf_1_16_n2vhpmo0_.arc

Backup Set 5 21-MAY-25

Backup Piece 5 21-MAY-25 /u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_05_21/o1_mf_s_1201689578_n2vccdph_.bkp

Backup Set 10 21-MAY-25

Backup Piece 10 21-MAY-25 /u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_05_21/o1_mf_s_1201695424_n2vk22pc_.bkp

Do you really want to delete the above objects (enter YES or NO)? YES

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_03_06/o1_mf_1_6_mwm73otb_.arc RECID=1 STAMP=1195045240

deleted backup piece

backup piece handle=/u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_03_06/o1_mf_s_1195045112_mwm6zsr4_.bkp RECID=1 STAMP=1195045113

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_03_22/o1_mf_1_7_mxwwwloz_.arc RECID=2 STAMP=1196411027

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_04_28/o1_mf_1_8_n0y2ykq0_.arc RECID=3 STAMP=1199595729

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_04_29/o1_mf_1_9_n10thgfr_.arc RECID=4 STAMP=1199685359

deleted backup piece

backup piece handle=/u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_04_29/o1_mf_s_1199689267_n10y9nxy_.bkp RECID=2 STAMP=1199689268

deleted backup piece

backup piece handle=/u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_04_29/o1_mf_s_1199689871_n10ywkbc_.bkp RECID=3 STAMP=1199689873

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_04_30/o1_mf_1_10_n13lcxjj_.arc RECID=5 STAMP=1199775359

deleted backup piece

backup piece handle=/u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_04_29/o1_mf_s_1199690476_n10zhg00_.bkp RECID=4 STAMP=1199690477

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_05_01/o1_mf_1_11_n162t9hh_.arc RECID=6 STAMP=1199944138

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_05_21/o1_mf_1_12_n2v9ht9q_.arc RECID=7 STAMP=1201687679

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_05_21/o1_mf_1_13_n2vbzlxk_.arc RECID=8 STAMP=1201689203

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_05_21/o1_mf_1_14_n2vbznnl_.arc RECID=9 STAMP=1201689204

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_05_21/o1_mf_1_15_n2vbzzdo_.arc RECID=10 STAMP=1201689215

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2025_05_21/o1_mf_1_16_n2vhpmo0_.arc RECID=11 STAMP=1201694035

deleted backup piece

backup piece handle=/u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_05_21/o1_mf_s_1201689578_n2vccdph_.bkp RECID=5 STAMP=1201689580

deleted backup piece

backup piece handle=/u01/app/oracle/fast_recovery_area/DB1/autobackup/2025_05_21/o1_mf_s_1201695424_n2vk22pc_.bkp RECID=10 STAMP=1201695426

Deleted 17 objects

RMAN>

ANURAG PANDEY

Contact me for all kind of database support >>>>>>>>>>>>>>>>>>>>>>>>>>>>>> I am Anurag Pandey, working as Cloud Database Architect | Migration & Modernization Expert . Database Administration and Implementation. Provide technical guidance and work with global team. Extensive Experience in Database Installation and Configuration, Real Application Cluster (RAC), ASM, Dataguard , Patching, Upgrade, RMAN, Performance Tuning, Backup and Recovery, Azure, Google, AWS Cloud, AWS RDS, MySQL, Database Migration Service(DMS),CloudEndure - MGN, Azure Site Recovery, Azure Migrate, Datapump, terraform automation, Ansible

How to check retention policy ?

[oracle@orcl 2025_05_21]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat May 24 12:41:22 2025

Version 19.3.0.0.0

connected to target database: DB1 (DBID=1788905834)

RMAN>

RMAN> show retention policy ;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name DB1 are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

RMAN>

ANURAG PANDEY

How to check controlfile autobackup is ON in oracle database using rman?

First login as oracle (software owner) and connect to rman

[oracle@orcl ~]$

[oracle@orcl ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat May 24 12:28:45 2025

Version 19.3.0.0.0

connected to target database: DB1 (DBID=1788905834)

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name DB1 are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_db1.f'; # default

RMAN>

ANURAG PANDEY

Backup Controlfile in oracle database

SQL>

SQL> select name from v$controlfile;

NAME

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

/u01/app/oracle/oradata/DB1/control01.ctl

/u01/app/oracle/fast_recovery_area/DB1/control02.ctl

/u01/app/oracle/oradata/DB1/control03.ctl

SQL>

SQL> alter database backup controlfile to trace ;

Database altered.

SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

2025-05-24T08:23:47.505162+00:00

alter database backup controlfile to trace

2025-05-24T08:23:47.508044+00:00

Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_ora_11837.trc

Completed: alter database backup controlfile to trace

2025-05-24T08:23:49.290103+00:00

ANURAG PANDEY