Resolving ORA-19502, ORA-16038 and ORA-27072 Errors in Oracle Database

We recently encountered errors below and there are several common causes.

 ORA-19502: write error on file "/oraarch/TESTDB/1_432678_12436018.dbf", block number 182272 (block size=512)
 ORA-16038: log 2 sequence# 432678 cannot be archived
 ORA-19502: write error on file "", block number (block size=)
 ORA-00312: online log 2 thread 1: '/redo2/TESTDB/TESTDB_1B.rdo'
 ORA-27072: File I/O error

The “ORA-27072: File I/O error” , can occur due to below are common reasons

• Disk issue – This error can also occur if the disk or storage is inaccessible. It might be due to hardware related issues
• File corruption- The file system where database resides might have corrupted.
• Permission issue – If the database user does not have enough permissions, you will get this error.
• Mount failures – when Filesystem not mounted properly

The “ORA-16038” error mainly occurs when archive log file cannot be archived. In this case if the database cannot be able to reuse redo log files, logs cannot switch, the database may hung.

The “ORA-19502” error mainly caused by insufficient disk space or file system full.

In our case, the issue was caused by a full archive log filesystem. 

When archive log file system got full, the redo log archiving failed triggering ORA-16038 and ORA-19502 errors. This eventually resulted ORA-27072 due to failed write attempts

Recommended steps

1. Check the archive log and db_recovery_file_dest destinations

SHOW PARAMETER log_archive_dest;
SHOW PARAMETER db_recovery_file_dest;

If using FRA:

SHOW PARAMETER db_recovery_file_dest_size;

 

2. User should use “df-h” to check the diskspace

User should Pay special attention to:

• Archive destination mount point
• FRA mount point

3. If the file system is full Increase size by extending lun or increasing FRA size.

4. Make sure user run the backup and delete old archive logs

rman target /
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-x';

Here X means number of days

5. In case FRA is full then user should increase the db_recovery_file_dest_size using below example

Check FRA usage using below query

SELECT name, space_limit/1024/1024 MB_LIMIT,
               space_used/1024/1024 MB_USED,
               space_reclaimable/1024/1024 MB_RECLAIMABLE

FROM   v$recovery_file_dest;  

ALTER SYSTEM SET db_recovery_file_dest_size = 200G;

6. Always check for alert.log to review errors and find the root cause.

Look for:

• ARCn errors
• Log switch failures
• Repeated I/O messages

To Avoid this issue in future user can take below measures:

• Monitor FRA usage regularly
• Set up alerting when disk usage exceeds 80%
• Configure proper RMAN retention policy
• Automate archive log deletion after backup
• Separate archive logs from other mount points
• Monitor log switch frequency

In our environment, the archive log filesystem became completely full.

This caused:
ORA-19502 (write failure)
ORA-16038 (cannot archive log)
ORA-27072 (I/O error)

Once disk space was cleared, archiving resumed automatically and the database returned to normal operation.

Thanks & Regards,

https://oracleracexpert.com

RMAN-03009 - RMAN Backup Failure After Applying DB RU Patch: A Resolution Guide

After applying a Database Release Update (DB RU) patch, we encountered an RMAN backup failure. The error messages provided were as follows:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 08/09/2025 14:54:45
ORA-01403: no data found

Issue: Failure While Registering Database
When attempting to register the database with RMAN, the following output was observed:

RMAN> register database;
database registered in recovery catalog
Creating and using snapshot control file for resync
starting full resync of recovery catalog
Control file used records for BACKUP REDOLOG = 6880
Control file used records for DELETED OBJECT = 6544
Control file used records for BACKUP SET = 2720
Control file used records for ARCHIVED LOG = 2384
Control file used records for LOG HISTORY = 2336
Control file used records for BACKUP DATAFILE = 2288
Control file used records for RMAN STATUS = 2256
Control file used records for BACKUP PIECE = 2096
Control file used records for BACKUP SPFILE = 524
Resync in progress: 11000 RMAN OUTPUTrecords resynced
RMAN Command Id : 2025-08-11T14:58:25
RMAN Command Id : 2025-08-11T14:58:25
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03008: error while performing automatic resync of recovery catalog
ORA-02291: integrity constraint (RMANCAT.RLH_F1) violated - parent key
not found
RMAN Client Diagnostic Trace file :
/oracle/diag/clients/user_oracle/RMAN_3026693161_110/trace/ora_
rman_37312_1.trc
RMAN Server Diagnostic Trace file :
/oracle/diag/rdbms/ORCL/ORCL/trace/ORCL_ora_37319.trc

Root Cause: Recovery Catalog Not Current
Upon attempting to connect to the RMAN catalog, we received the following message:

$ rman catalog rmancatusr@CATDB

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Aug 09 01:38:51 2025
Version 19.28.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

recovery catalog database Password:
connected to recovery catalog database
PL/SQL package RMANCAT.DBMS_RCVCAT version 19.10.00.00. in RCVCAT database is not current
PL/SQL package RMANCAT.DBMS_RCVMAN version 19.10.00.00 in RCVCAT database is not current

This indicates that the RMAN catalog was not upgraded and was out of sync with the new database release.

Solution: Upgrade the RMAN Catalog
To resolve the issue, we needed to upgrade the RMAN catalog. The solution was to run the following command:

$ rman catalog <catalog user/passwd> @catdb
RMAN> upgrade catalog ;

The issue has been RESOLVED after upgrading the database. The user able to register database and able to run backups without any issues.

Conclusion

This issue was caused by the RMAN catalog being out of sync after applying a DB RU patch. Upgrading the catalog resolved the error, allowing successful database registration and backup operations.

Physical Standby Recovery or Sync Using Recover from Service from 18c

The RECOVER FROM SERVICE is used to recover data guard database which is out of sync or lagging standby. Before 12c the user needs to determine the current SCN, take a backup and apply, start managed recovery of standby and but in 12c they automated these steps but user still needs to perform few manual steps such as control file update. 

This feature has been improved in Oracle 18c and covers all the steps that DBA used to perform manually

Primary : DBPRI
Standby: DBSTB

Steps if the database is Oracle 18c or above

1. Check the Data Guard Broker configuration.

DGMGRL> show configuration
Configuration – dg_config
Protection Mode: MaxPerformance

Members:
dbpri - Primary database
Error: ORA-16724: cannot resolve gap for one or more members

  dbstb - Physical standby database
  Warning: ORA-16809: multiple warnings detected for the member

Fast-Start Failover: Disabled

Configuration Status:
ERROR (status updated 5 seconds ago)

We can see that both Primary and Physical standby are out of sync

2. Stop Recovery Managed process on the standby

DGMGRL> edit database DBSTB set state=APPLY-OFF;

Or

RMAN> alter database recover managed standby database cancel;

3. Run roll forward command

$rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Mar 11 12:12:48 202
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: DBSTB (DBID=xxxxxxxx)
RMAN> recover standby database from service DBPRI
Starting recover at 11-MAR-22
using target database control file instead of recovery catalog
Oracle instance started…….

media recovery complete, elapsed time: 00:00:00
Finished recover at 05-MAR-22
Executing: alter system set standby_file_management=auto
Finished recover at 05-MAR-22

4. Enable the Recovery Managed process on the standby

DGMGRL> edit database DBSTB set state=APPLY-ON;

Or

RMAN> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


5. Verify the Data Guard configuration status

DGMGRL> show configuration

Configuration - dg_config
Protection Mode: MaxPerformance

Members:
dbpri - Primary database
  dbstb - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 56 seconds ago)

Now standby Database has been recovered and in sync with the primary.

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

Webinar: Oracle Data Guard New Features in Oracle 18c & 19c

This Webinar helps you to understand the benefits of Oracle Data Guard, Standby Database types, protection modes and new features in Oracle 18c and 19c.

Date and time: May 5th 2022 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.

• Introduction to Oracle Data Guard
• Oracle Data Guard Benefits
• Standby Database types
• Data Guard Protection Modes
• What's new in Oracle 18c
• What's new in Oracle 19c
• References
• Contact Info
• Q&A

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com

Note that registrations are limited and first come and first serve basis.
You will receive an email confirmation with meeting session link.

For presentation link Click here

Thanks & Regards,
http://www.oracleracexpert.com

How to roll forward image copies using RMAN

Recovery Manager (RMAN) is a utility that can be used to backup and recovery your database and it simplifies backing up, restoring, and recovering database or database files

Oracle RMAN can be used to roll forward the copy to the point in time of the most recent level 1 incremental backup by applying level 1 incremental backups to an older image copy. This will help reducing recovery time. To roll forward processes all changes between SCN of original image and incremental backup are applied to the image copy.

When using Roll forward image no need to restore the database instead just switch to updated image copy this will reduce restore time and recovery will be minimal.

run {
allocate channel c1 device type disk;
recover copy of database with tag 'rman_incr_backup';
backup incremental level 1 for recover of copy with tag ‘rman_incr_backup' database;
}

The TAG is used to identify which incremental backups are applying to which image copies.

Note that when the backup runs first time there is no level 0 exists to apply incremental backups. Therefore, it will create level 0 image copy of the database. Going forward it will create level 1 incremental backup and will update previous image copy.

You can also use compression during roll forward image copies using below example

run {
allocate channel c1 device type disk;
recover copy of database with tag ‘rman_incr_backup';
backup as compressed backupset incremental level 1 for recover of copy with tag ‘rman_incr_backup' database;
}

In case if you need to switch the database to image copy you then “shutdown database” and “startup mount”

SQL> shutdown immediate;
SQL> startup nomount;

By using below RMAN command, you can switch the database to image copy

run {
allocate channel c1 device type disk;
switch database to copy;
recover database;
alter database open;
}

When using FRA (Fast Recovery Area) make sure you have has enough space to keep take full image copy database backup.

The Block change tracking will help to improve performance of the incremental backups and you can check the status and enable using below command. 

SQL> select status from v$block_change_tracking;
SQL> alter database enable block change tracking using file '/oracle/oradata/TESTDB/block_change_tracking_file.dbf' reuse;

You can use below command to disable Block change tracking

SQL> alter database disable block change tracking;

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

Oracle RMAN backup using DataDomain-Boost

Data Domain boost plug-in enables database to communicate with Data domain systems in an optimized way and use of DD Boost for RMAN backup improves performance while reducing the data transfer.

You need to install RMAN pug-in on each DB server and these binaries communicate with Data domain systems. When taking backup using RMAN, the configuration channels should use media manager SBT_LIBRARY parameter to provide the path of the media management library of data domain.

RMAN issues an ORA-27211 error and exits if the media management library not able to locate.

Admin needs to enable DD Boost on a Data Domain System before we proceeding.

Steps:-  

1. Download the required pulg-in and install
For Ex:- RMAN_1.1.1.0-378849_RMAN_linux_64.tar

For Windows Download the RMAN plugin installer “libDDobkSetup.exe”

2. Installation: Set the Oracle Home then run the install.sh script
$ ./install.sh
Installing the Data Domain plugin for RMAN ...
Copying libraries to /oracle/home/11204/lib
cp libddobk.so /oracle/home/11204/lib/libddobk.so
cp libDDBoost.so /oracle/home/11204/lib/libDDBoost.so
Successfully installed the Data Domain plugin for RMAN

/orasnb/oracle_ddbda.cfg will have all configuration details

3. Verify: Verify the DDBoost install using below command , make the changes as needed

RUN {
ALLOCATE CHANNEL C1 TYPE SBT_TAPE PARMS 'SBT_LIBRARY=/oracle/home/11204/lib/libddobk.so';
send 'set username <username> password <password> servername <data domain/backup host server>';
RELEASE CHANNEL C1;
}

4. Run backup using DD Boost Media management library

You can run manual backup

run {
allocate channel dd1 type 'sbt_tape' parms='BLKSIZE=1048576,SBT_LIBRARY=/oracle/home/11204/lib/libddobk.so,ENV=(STORAGE_UNIT=<storage_unit>,BACKUP_HOST=<backup host>)';
backup filesperset 1 database format '%u_%p';
release channel dd1;
}

Configure BRTools using DDBoost: - Add/Change the below parameter in .sap file of database and comment out other unwanted parameters. Note that DDBoost will call RMAN internally to backup and restore.

backup_type = online
backup_dev_type = rman_disk
rman_channels = 10 <or any number of channels desired>
rman_filesperset = 1
rman_parms = "BLKSIZE=1048576,SBT_LIBRARY=<path-to-libddobk.so>,ENV=(STORAGE_UNIT=<LSU-name>,BACKUP_HOST=<DDR-FQDN>,ORACLE_HOME=<path-to-ORACLE_HOME>)"
rman_compress = no
backup_mode = all


RMAN Changes: Make below rman configuration changes
CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%F';

Perform Backup: 

Backup command:
brbackup -m all -p /oracle/home/11204 /dbs/initORCL.sap -u / -t online -d rman_disk
"-t online" option is the same as specifying "backup_type = online" in the configuration file
"-d rman_disk" option is to force "backup_dev_type = rman_disk", this is the observed behavior
Complete list of Command Options for BRBACKUP : http://help.sap.com/saphelp_nw70/helpdata/en/c7/c90e6b7cfd44309bd90b2b7a8a89fd/content.htm

 
Archive Log backup:
brarchive -s -c -p /oracle/home/11204/dbs/initORCL.sap -u /

Restore command:
brrestore -m all -p initKBW.disk.sap -b belcjoiv.anr -c force
We never tested restore using above command but we tested thoroughly with BRTools and RMAN

Steps to Enable DDBoost on a Data domain system: Unix Admin will execute these steps. The below are high-level steps and few other steps involved to configure as per the best practice.

1. Login as Administrator

 
2. Verify file sytem is enable dand running
# filesys status
Enable the file system, if diabled.
#filesys enable

 
3. Apply license key if not installed
# license add <license key>

 
4. Establish username and password for Data domain system
#user add <username> password <password>
# ddboost set user-name <username>

 
5. Enable DDBoost
# ddboost enable

 
6. Create the storage unit on the data domain system
# ddboost storage-unit create <storage_unit_name>

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

Configure Oracle RMAN Backups using Media Manager

In order to backup and restore from sequential media such as tape you must integrate a media manager with your Oracle database. Note that media manager is not an Oracle product and must be obtained and licensed fom a third-party vendor.

1. Prerequisites for a Media Manager 

Before using any media manager with your Oracle database you need to install the required plug in and make sure that RMAN can communicate with it. For Instructions, refer vendor documentation. The third-party media management module contains the media management library that the Oracle database loads and uses when accessing the media manager..

2. Locating the Media Management Library

User should use SBT_LIBRARY parameter when allocating or configuring channels for RMAN to use to communicate with a media manager, SBT_LIBRARY parameter to provide the path to the media management library and this library will be loaded during the communication with media manager

On UNIX, the default library location is $ORACLE_HOME/lib/libobk.so, with the extension name varying according to platform: .so, .sl, .a, and so forth.

On Windows, the default library location is %ORACLE_HOME%\bin\orasbt.dll.

If the database is unable to locate a media library specified by the SBT_LIBRARY parameter RMAN issues an ORA-27211 error and exits. Note that there is no default media management library file as part of standard database installation.

When channel allocation fails, the database writes a trace file to the USER_DUMP_DEST directory.

For ex:- SBT Initialize failed for /oracle/home/lib/libobk.so

 
3. Media Manager Library Integration

You need to define the media manager library , Media manager server, storage system, configure user…etc to configure. The PARMS parameter will be used to ALLOCATE or CONFIGURE CHANNEL and FORMAT and BACKUP commands will be used to backup. To limit backup piece sizes, use the parameter MAXPIECESIZE,

Example 1:-
RUN {
ALLOCATE CHANNEL C1 TYPE SBT_TAPE PARMS='SBT_LIBRARY=<media manager > lib/libddobk.so'; ENV=(STORAGE_UNIT=<storage_unit>,BACKUP_HOST=<backup host>)'
}

Example 2: -
RUN {
ALLOCATE CHANNEL C1 TYPE SBT_TAPE PARMS='SBT_LIBRARY=<media manager > lib/libddobk.so'; ENV=(NSR_SERVER=<Media Manager server>,NSR_CLIENT=<oracle Client> ,NSR_GROUP=<Tapes>)’
}

Note that the parameters will vary based upon Media Manager vendor

If the media manager could not be located user will receive below error message
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on c1 channel at 12/10/2020 10:11:20
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 25

Once you configure the channel, you can run below commands to backup the database files.
To check SBT configuration
RMAN> SHOW CHANNEL FOR DEVICE TYPE SBT_TAPE;
To backup the control file to SBT_TAPE
RMAN> BACKUP DEVICE TYPE SBT_TAPE CURRENT CONTROLFILE;
Backup archived redo log backup to tape
RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE

You can configure the default device to SBT_TAPE and FORMAT so that RMAN sends all backups to the media manager in specified format

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO SBT_TAPE;
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT = '?/bkup_%U';

Once you configuring the default device all the backups should go to media manager:

RMAN> BACKUP CURRENT CONTROLFILE;
RMAN> BACKUP DATABASE;

Please see the below link to configure Oracle Backups using DDBoost Media Manager
“Oracle RMAN backup using DataDomain-Boost”

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

Create Recovery Catalog for RMAN

Recovery catalog schema used by RMAN to store one or more Oracle databases metadata. This info is already stored in control file of each Target Database and using Recovery catalog provides redundancy. In case, if target control file and all backups are lost then you can still get the info from Recovery catalog.

RMAN Recovery catalog also helps as centralized metadata location for all your databases and it helps easily report from once location. In addition, you can store metadata history longer than Control file.

You must register database in order to store metadata in Recovery catalog. it is highly recommended to use enable ARCHIVELOG mode for Recovery Catalog database

· Recovery Catalog metadata has following database file info
· Data file and archived redo log backup sets and pieces
· Data file and Archive log copies
· Database structure such as tablespaces and data files
· Stored scripts
· RMAN configuration settings

Creating Recovery Catalog

1. Connect to Recovery catalog DB as admin and create the Schema.

SQL> CREATE USER RCAT IDENTIFIED BY password
DEFAULT TABLESPACE RCATTBS
TEMPORARY TABLESPACE temp;

2. The RECVEROY_CATALOG_OWNER role is required to maintain and query, so grant the role to the Schema

SQL> GRANT RECOVERY_CATALOG_OWNER TO RCAT;

3. Connect to the database as the recovery catalog owner and run CREATE CATALOG command to create the catalog.

RMAN> CREATE CATALOG

You can also specify tableaspace name for the catalog.
RMAN> CREATE CATALOG TABLESPACE RCATTBS;

4. Connect to recovery Catalog and Register Target Database using below nelow command

$ rman TARGET / CATALOG RCAT@RCATDB;
RMAN> REGISTER DATABASE;

Examples for Data file copy, Archive log backup and backup pieces
RMAN>CATALOG DATAFILECOPY '/bkp/dbfile/testuser01.dbf';
RMAN>CATALOG ARCHIVELOG '/arc/arc_DB_1_559.dbf', '/arc/arc_DB_1_560.dbf',
RMAN>CATALOG BACKUPPIECE '/disk1/backups/backup_820.bkp';

Catalog multiple backup files in a directory by using one single command
RMAN> CATALOG START WITH '/bkp/dbfile/';

Refer below link for Virtual Private Catalogs
https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/managing-recovery-catalog.html#GUID-DA362C55-5B94-4AB8-A8A1-163BF08FE594

Thanks & Regards,

http://oracleracpert.com, Oracle ACE

Oracle RAC and Grid new features in 19c

Oracle Database 19c has many exciting new features and in order to take advantage of these features you need to upgrade the databases from older versions to Oracle 19c

Join the Webinar to learn New Features in Oracle 19c RAC and Grid

Date and time: Nov 25th 2020 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)


To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com. Note that registrations are limited, first come and first serve basis only. You will receive an email confirmation with meeting session link.

For Presentation, link "Click here"

Thanks & Regards
http://www.oracleracexpert.com

Oracle Data Guard New Features in Oracle 18c & 19c

This Webinar helps you to understand the benefits of Oracle Data Guard, Standby Database types, protection modes and new features in Oracle 18c and 19c.

Date and time: Nov 17th 2020 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.

• Introduction to Oracle Data Guard
• Oracle Data Guard Benefits
• Standby Database types
• Data Guard Protection Modes
• What's new in Oracle 18c
• What's new in Oracle 19c
• References
• Contact Info
• Q&A

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com

Note that registrations are limited and first come and first serve basis.
You will receive an email confirmation with meeting session link.

For presentation link Click here

Thanks & Regards,
http://www.oracleracexpert.com

Duplicating On-premise Database to Oracle Cloud in Oracle 18c

From Oracle 18c, by using DUPLICATE command you can duplicate an on-prem database to Oracle Could. Oracle databases on Oracle Cloud are always encrypted even if no encryption clause is specified during duplication.

Using Oracle RMAN you to perform two main types of database duplications.

• Backup-based duplication – In this method we will use pre-existing RMAN backups or copies of the source database. 

• Active database duplication - The database will be duplicated by copying the live source database over the network to the auxiliary instance. 

Follow the steps to migrate on-prem database to Cloud:

1. Ensure the prerequisites for the DUPLICATION technique are met, see Prerequisites for Duplicating a Database".

2. Configure Recovery Manager to use Oracle Database Backup Cloud Service as the backup destination. Use CONFIGURE command. Pls refer Oracle Cloud Using Oracle Database Backup Service for more details.

Syntax:-
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt
PARMS='SBT_LIBRARY= SBT-library-location-for-backup-module,
SBT_PARMS=(OPC_PFILE=location-of-the-configuration file)';
Ex:-
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt
PARMS='SBT_LIBRARY=/oracle/18c/lib/libopc.so,
SBT_PARMS=(OPC_PFILE=/oracle/18c/dbs/emp.ora)';
3. Complete the planning tasks, as described in "Planning to Duplicate a Database" 

4. Prepare the auxiliary instance, as described in "Preparing the Auxiliary Instance"

• You must create auxiliary instance as CDB and start instance with enable_pluggable_database=TRUE in the initialization parameter file
• When instructed to create an initialization parameter file for the auxiliary instance, user must copy the file from the source database. This ensures that the auxiliary instance is also a CDB. After you copy the file you need to perform the following steps:
   – Modify the DB_NAME parameter
   – Modify the various destination/location parameters
• Start the auxiliary instance in NOMOUNT mode.

5. Start RMAN and connect to the root as a common user with the SYSBACKUP privilege or SYSDBA.

6. If the source CDB uses encryption, then open the Oracle keystore that contains the master key on the source CDB.

7. Configure RMAN channels, if necessary, as described in "Configuring RMAN Channels for Use in Duplication".

8. On the destination CDB, open the Oracle keystore from the source CDB. If the destination CDB uses a password-based software keystore, then you must specify the password used to open this keystore

SET DECRYPTION WALLET OPEN IDENTIFIED BY 'password';

9. Use the DUPLICATE command to duplicate the source CDB.

Use one of the following options of the DUPLICATE command:

• DUPLICATE DATABASE or DUPLICATE...ACTIVE DATABASE - Use this command for duplicating non-CDBs and CDBs.
• DUPLICATE DATABASE ... FOR STANDBY - Use this command create a standby database by duplicating the source.
• Use the DUPLICATE DATABASE ... FOR FARSYNC – Use this command to create an Oracle Data Guard far sync instance using duplication.
• DUPLICATE PLUGGABLE DATABASE – Use this command to duplicate one or more PDBs while connected to the root.

You can also use SET NEWNAME command to specify alternate names for duplicate database files,

Note that Using duplication to create a standby database to Oracle Cloud is not supported

Regards

Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

Relocated PDBs in Oracle Database 18c

Oracle 18c allows RMAN backups created before the non-CDB or PDB was migrated into a different target CDB can be used for recovery operations. The COMPATIBLE parameter of the source and Oracle Cloud must be set to 18.0.0 or higher

The RMAN commands used to backup and recovery CDBs and PDBs are the same as those used for non-CDBs, with few variations in the syntax.

The backup and recovery operations performed on non-CDBs can also be performed on CDBs and PDBs. This includes the following:
– Full and incremental backups
– Complete and point-in-time recovery (PITR)
– Reporting operations (such as listing and cross-checking backups)
– Flashback Database

We need take metadata for the existing backups and available to the destination CDB. To export metadata user needs to run DBMS_PDB.EXPORTRMANBACKUP procedure on the source database.

EXECUTE DBMS_PDB.exportrmanbackup();
Or
EXECUTE DBMS_PDB.exportrmanbackup('EMP_PDB');

In case if you are unplug you no need to run this command as unplug already includes the metadata.

Convert NON-CDB to PDB : As we have metadata, now we can covert the NON-CDB instance to PDB.

Step1: Open the non-CDB instance in read-only mode and describe and shutdown

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP OPEN READ ONLY;
SQL> BEGIN
DBMS_PDB.DESCRIBE( pdb_descr_file => '/oracle/empdb.xml');
END;
SQL> SHUTDOWN IMMEDIATE;

Step2: Create the new pluggable database using the non-CDB description file that we have taken in above step

SQL> CREATE PLUGGABLE DATABASE empdb_pdb USING '/oracle/empdb.xml' COPY;
SQL> ALTER SESSION SET CONTAINER= empdb_pdb;
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
SQL> ALTER PLUGGABLE DATABASE OPEN;
SQL> ALTER PLUGGABLE DATABASE SAVE STATE;

Step3:- Restore and recovery using pre-plugin backup

SQL> ALTER PLUGGABLE DATABASE empdb_pdb CLOSE IMMEDIATE;

RMAN> SET PREPLUGIN CONTAINER=db18cpdb;

RMAN> RESTORE PLUGGABLE DATABASE empdb_pdb FROM PREPLUGIN;
RMAN> RECOVER PLUGGABLE DATABASE empdb_pdb FROM PREPLUGIN;

Sometimes users may come across RMAN-06054 error in that case you need to CATALOG the missing archive log and start the recovery again.

RMAN-06054: media recovery requesting unknown archived log for thread 3 sequence 94983
RMAN>SET PREPLUGIN CONTAINER= empdb_pdb;
RMAN>CATALOG PREPLUGIN ARCHIVELOG 'oracle/archivelog/arc_empdb_pdb_3_94983.arc';
RMAN>RECOVER PLUGGABLE DATABASE empdb_pdb FROM PREPLUGIN;

Perform normal recovery and open the database

RMAN>RECOVER PLUGGABLE DATABASE empdb_pdb;
RMAN>ALTER PLUGGABLE DATABASE empdb_pdb OPEN;


Reference:
Check preplugin backups available to the CDB instance
RMAN> LIST PREPLUGIN BACKUP OF PLUGGABLE DATABASE empdb_pdb;

Preplugin backups are usable only on the destination CDB into which you plug in the source non-CDB or PDB

Regards

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

How to Register or Catalog Backups, Archive logs, data file copy, FRA...etc

When you backup using RMAN all backups  will be registered with catalog. If you have taken backup without catalog and you can also register manually.

Backup pieces copied manually to different location to catalog backup piece run below command.
RMAN> CATALOG BACKUPPIECE /oracle/backup/DB1_1978087_6446005710.bkp' ;

In case if you have taken backup using o/s command and to catalog a datafile copy use below command.
RMAN> CATALOG DATAFILECOPY '/oracle/backup/employee01.bak' LEVEL 0;

In case, archive log location full and you have moved backups to different location using o/s command then you can register the archive logs using below command

RMAN> CATALOG ARCHIVELOG ‘/oracle/backup2/db1_arch_99089_0876589.arc'
If you are using Flash recovery area and want to catalog all files in the FRA use below command.
RMAN> CATALOG recovery area;

This command will prompt to confirm that you want the found files to be cataloged. If you do not want to prompt use “noprompt” clause.
RMAN> CATALOG recovery area noprompt;

If you want to catalog only files Start with specific name or belongs to specific SID (db1) use below command
RMAN> CATALOG START WITH '/oracle/backup/db1'

If you run CROSSCHECK, it will go through the files that match criteria and will be marked as available, and files not found will be marked as EXPIRED.

RMAN> CROSSCHECK BACKUP ;
If you want to DELETE EXPIRED backups you can use below command, note that EXPIRED copy will no longer appear in RMAN. You have to be extra cautious to delete backups.

RMAN> DELETE EXPIRED BACKUP ;

If you find extra copies and want to delete those extra copies use DELETE BACKUPPIECE command:
RMAN> DELETE BACKUPPIECE 4555;
Hope this helps managing your backups taken outside of RMAN recovery catalog .

Thanks & Regards 

Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

ORA-19815: WARNING: db_recovery_file_dest_size of 128849010 bytes is 99.03% used, and has 14136528 remaining bytes available.

You will receive ORA-19815 is reaching maximum size of flash backup recovery area (FRA) and note that this limitation will be managed by db_recovery_file_dest_size parameter

If you got this error as one time then you might be running adhoc load or some batch job. Also you will get below message in alert.log file. Please follow one of the choice to fix the issue.

ORA-19815: WARNING: db_recovery_file_dest_size of 128849010 bytes is 99.03% used, and has 14136528 remaining bytes available.
2020-02-21T11:31:19.892420-07:00

************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.

In case if you archive logs are going to FRA then you should run RMAN backups and delete the unnecessary files to avoid Database hung or CRASH situation.

RMAN> backup archivelog all delete input;

If the Flash recovery area is 100% then you might receive below error when starting up the database

Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 9789
Session ID: 321 Serial number: 3

Pls make sure you follow below steps to add more space to flash recovery area (FRA)

SQL> startup mount;
SQL> alter system set db_recovery_file_dest_size=<size>G scope=both;
Replace <size> with a number larger than the current and ensure is sufficient space in the destination
SQL> alter database open;

To see what FRA has run below command

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID

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

CONTROL FILE                          0                         0               0          0

REDO LOG                              0                         0               0          0

ARCHIVED LOG                          0                         0               0          0

BACKUP PIECE                          0                         0               0          0

IMAGE COPY                            0                         0               0          0

FLASHBACK LOG                     99.76                         0             192          0

FOREIGN ARCHIVED LOG                  0                         0               0          0

AUXILIARY DATAFILE C                  0                         0               0          0

In my case I am using for FLASHBACK LOG, these logs are different from redo logs or undo. They are a separate, optional recording of database activity.

These logs are automatically deleted when FRA is our of space and logs is not needed for recvoery within the window specified in DB_FLASHBACK_RETENTION_TARGET parameter

To determine the disk quota and current disk usage in the flash recovery area, query the view
V$RECOVERY_FILE_DEST.

Thanks & Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

When using Data Guard broker Error: ORA-16627: operation disallowed since no member would remain to support protection mode

You will encounter this error when PROTECTION mode is not consistent betweeen PRIMARY and STANDBY. Make sure you are using DGMGRL to update the configurationn.

DGMGRL> show configuration;
Configuration - oradb_dg_fsfconf
Protection Mode: MaxPerformance
Members:
oradb - Primary database
oradb_stdby - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 13 seconds ago)

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Error: ORA-16627: operation disallowed since no member would remain to support protection mode Failed.

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

Make sure you verify the entire configuration using verbose.

DGMGRL> show configuration verbose;
Configuration - oradb_dg_fsfconf
Protection Mode: MaxPerformance
Members:
oradb - Primary database
oradb_stdby - Physical standby database

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
bserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
ConfigurationWideServiceName = 'ORADB_CFG'

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS

DGMGRL> show database verbose ORADB;
Database - oradb
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORADB

Properties:
DGConnectIdentifier = 'oradb'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'scllqa0078'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=seroradb-db)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORADB_DGMGRL)(INSTANCE_NAME=ORADB)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'

Log file locations:
Alert log : /home/oracle/diag/rdbms/oradb/ORADB/trace/alert_ORADB.log
Data Guard Broker log : /home/oracle/diag/rdbms/oradb/ORADB/trace/drcORADB.log

Database Status:
SUCCESS

DGMGRL> show database verbose ORADB_STDBY;

Database - oradb_stdby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 26.00 KByte/s
Active Apply Rate: 938.00 KByte/s
Maximum Apply Rate: 940.00 KByte/s
Real Time Query: OFF
Instance(s):
ORADB

Properties:
DGConnectIdentifier = 'oradb_stdby'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'scllqa0120'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraoradb-dg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORADB_STDBY_DGMGRL)(INSTANCE_NAME=ORADB)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'

Log file locations:
Alert log : /home/oracle/diag/rdbms/oradb_stdby/ORADB/trace/alert_ORADB.log
Data Guard Broker log : /home/oracle/diag/rdbms/oradb_stdby/ORADB/trace/drcORADB.log

Database Status:
SUCCESS
Make sure you update the LogXptMode and set the protection mode.

DGMGRL> EDIT DATABASE ORADB SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL> EDIT DATABASE ORADB_STDBY SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated

DGMGRL> show configuration;
Configuration - oradb_dg_fsfconf

Protection Mode: MaxPerformance
Members:
oradb - Primary database
oradb_stdby - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 32 seconds ago)

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.

DGMGRL> show configuration;

Configuration - oradb_dg_fsfconf
Protection Mode: MaxAvailability
Members:
oradb - Primary database
oradb_stdby - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 57 seconds ago) Regards,

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

ORA-00230: operation disallowed: snapshot control file enqueue unavailable

User might encounter below error, in case if RMAN backup is already running and user might have triggered another backup. The second backup must wait control file enqueuer to create a snapshot of the controlfile.

RMAN-03002: failure of backup command at 04/10/2020 16:40:33
ORA-00230: operation disallowed: snapshot control file enqueue unavailable
You can run below command to find out the backup that is running and causing the wait

SELECT S.SID, USERNAME , PROGRAM, MODULE,
ACTION, LOGON_TIME FROM V$SESSION s, V$ENQUEUE_LOCK l
WHERE l.SID = s.SID AND l.TYPE = 'CF' AND l.ID1 = 0 AND l.ID2 = 2;

To avoid this error make sure that backups are not overlapping each other.

Regards,
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

ORA-01665: control file is not a standby control file

Users may see this error when trying to mount standby database after restore or during the switch over

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE MOUNT STANDBY DATABASE
*
ERROR at line 1:
ORA-01665: control file is not a standby control file

SQL> SELECT database_role FROM v$database;
DATABASE_ROLE
----------------------------------------------------------------
PRIMARY

In current scenario, we see the database role as primary and trying to convert as Physical Standby. You can convert when database not mounted, otherwise you will see below error.

SQL> Alter database convert to physical standby;
Alter database convert to physical standby
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> shutdown immediate
SQL> startup nomount;

SQL> Alter database convert to physical standby;
Database altered.
SQL> SELECT database_role FROM v$database;
DATABASE_ROLE
----------------------------------------------------------------
PHYSICAL STANDBY
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;
Database altered.

Make sure you start the recovery using below command

sql> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;

To avoid any user mistakes I would advise to use Data Guard broker

Refer below presentation related to Data Guard.

1. “Why Oracle DataGuard? New Features in Oracle 18c,19c”
https://www.oracleracexpert.com/2019/12/why-oracle-dataguard-new-features-in.html

2. Data Guard Physical Standby Setup in Oracle?
https://www.oracleracexpert.com/2019/02/webinar-data-guard-physical-standby.html

Regards
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

All redo log files were not defined properly and Operation RENAME is not allowed

All redo log files were not defined properly and Operation RENAME is not allowed

We have created the Standby database using RMAN in Oracle 19c and received following error after standby DB restore.

RMAN-05535: warning: All redo log files were not defined properly.
Oracle error from auxiliary database: ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

I have seen this error in the newer Oracle versions only and this warning can be ignored as parameter STANDBY_FILE_MANAGEMENT is set to AUTO

During recovery I also come across below error, this is because I have not allocated any auxiliary channel.

run{
allocate channel PR1 type disk;
allocate channel PR2 type disk;
duplicate target database for standby from active database dorecover nofilenamecheck
spfile
set db_unique_name='ORCLT_STDBY'
set standby_file_management='AUTO'
}

During the upgrade
RMAN-05501: aborting duplication of target database
RMAN-05503: at least one auxiliary channel must be allocated to execute this command

I have added below command in the script and restore worked fine.

allocate auxiliary channel STDBY type disk;

Check presentation on Data Guard Physical Standby Setup in Oracle?

Regards
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

I have come across issue when adding Standby Database during Data Guard broker config

DGMGRL> create configuration 'ORCLT' as primary database is 'ORCLT' connect identifier is ORCLT;
Configuration "ORCLT" created with primary database "ORCLT"
DGMGRL> add database 'ORCLT_STDBY' as connect identifier is ORCLT_STDBY maintained as physical;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

I have not seen this issue on older versions, after research found that there are few changes from Oracle 12c. We have to unset LOG_ARCHIVE_DEST_2 parameter as DGMGRL will take care of updating this parameter.

I have followed below steps

1. Disable and Remove Data Guard configuration

DGMGRL> disable configuration;
DGMGRL> remove configuration;

2. Disable/Enable Data Guard broker on both Primary/standby

SQL> alter system set dg_broker_start=false scope=both;
SQL> alter system set dg_broker_start=true scope=both;

3. Disable log_archive_dest_2 on both Primary/standby
SQL> alter system set log_archive_dest_2'' scope=both;

4. Configure Data Guard Broker

DGMGRL> create configuration ‘orclt_dg’ as primary database is 'ORCLT' connect identifier is ORCLT;
Configuration "ORCLT" created with primary database "ORCLT"
DGMGRL> add database 'ORCLT_STDBY' as connect identifier is ORCLT_STDBY maintained as physical;
DGMGRL> Database 'ORCLT_STDBY' added
DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;
Configuration – orclt_dg
Protection Mode: MaxPerformance
Members:
orclt - Primary database
orclt_stdby - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS (status updated 46 seconds ago)

Regards,

Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

Webinar: What’s New in Oracle Database 19c?

Oracle Database 19c has many exciting new features and in order to take advantage of these features you need to upgrade the databases from older versions to Oracle 19c

Join the Webinar to learn New Features of Oracle Database 19c

Date and time: Mar 31st 2018 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com.

Note that registrations are limited, first come and first serve basis only. You will receive an email confirmation with meeting session link.

For Presentation, link "Click here"

Thanks,
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com