Resolve Unnamed Datafile Issue in Oracle Data Guard

In an Oracle Data Guard environment, one of the common issues DBAs encounter after adding a new datafile on the primary database is the UNNAMED datafile error on the standby database. This issue usually appears when managed recovery is running and the standby database does not know the exact location of the newly added datafile. It commonly occurs when standby file management is set to MANUAL, when sufficient free space is not available on the standby mount point, or when the ASM disk group does not have enough space. In some cases, this issue can also occur on the primary database if the datafile location (mount point) or ASM disk group is not specified correctly or does not have sufficient free space.

Error Symptoms

You may observe the following errors in the standby alert log or while starting managed recovery:

ORA-01111: name for data file is unknown - rename to correct file
ORA-01110: data file : '/oracle/dbs/UNNAMED000xx'
ORA-01157: cannot identify/lock data file 
        

Oracle creates a placeholder file with the name UNNAMEDxxxxx when the standby database is unable to create the actual datafile path.

Why Does This Issue Occur?

• A new datafile is added to the primary database
• The standby database does not have the same directory structure
• DB_FILE_NAME_CONVERT is not configured correctly
• Standby is unable to auto-create the datafile

Identify the Unnamed Datafile

Connect to the standby database and identify the unnamed datafile:

SQL> SELECT file#, name FROM v$datafile WHERE name LIKE '%UNNAMED%';
        

Steps to Resolve Unnamed Datafile Issue

Step 1: Stop Managed Recovery

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
        

Step 2: Rename the Unnamed Datafile

Create the correct directory on the standby server (if not exists) and rename the file:

SQL> ALTER DATABASE RENAME FILE '/oracle/dbs/UNNAMED00012' TO '/u01/oradata/STDB/users01.dbf';
        

Step 3: Start Managed Recovery

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
        

Verify the Status

SQL> SELECT file#, name, status FROM v$datafile;
        

Ensure that the datafile status is ONLINE and redo apply is progressing normally.

Preventing This Issue

• Configure DB_FILE_NAME_CONVERT properly
• Ensure directory structure is identical on primary and standby
• Use OMF (Oracle Managed Files) when possible
• Monitor standby alert logs regularly

If you are frequently adding datafiles, enabling Oracle Managed Files (OMF) can eliminate this issue entirely by allowing Oracle to automatically manage file names and locations.

Conclusion

The UNNAMED datafile issue in Data Guard is not critical but must be addressed immediately to resume redo apply. With proper configuration and monitoring, this issue can be avoided altogether.

Please feel free to ask. Thank you 🙂

Toufique Khan

Purge the Shared Pool in Oracle | DBMS_SHARED_POOL vs ALTER SYSTEM FLUSH SHARED_POOL

The Shared Pool is a key component of the Oracle SGA (System Global Area) that caches parsed SQL, PL/SQL code, and data dictionary information. Over time, it may accumulate obsolete or invalid objects, leading to performance degradation or “ORA-04031” errors.

In this post, we will discuss two methods to clear or refresh the Shared Pool — either entirely or selectively — using ALTER SYSTEM FLUSH SHARED_POOL and DBMS_SHARED_POOL.PURGE.

1. What Is the Shared Pool?

The Shared Pool contains:

• Library Cache — parsed SQL and PL/SQL statements
• Dictionary Cache — metadata from the data dictionary
• Session and control structures

Heavy DML operations, frequent code recompilation, or dynamic SQL can cause fragmentation or invalid objects in the Shared Pool, impacting parsing efficiency and causing memory allocation errors.

2. Flushing the Entire Shared Pool

To clear all cached SQL and PL/SQL objects from memory, use the following command:

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

Note: This operation clears the entire Shared Pool and forces Oracle to reparse all SQL statements. It may cause temporary performance degradation, so use it only during maintenance or troubleshooting windows.

When to Use:

• After major schema changes or deployments
• To resolve ORA-04031 errors related to memory fragmentation
• When performance issues are traced to a corrupted Shared Pool

3. Purging Specific Objects Using DBMS_SHARED_POOL

Instead of flushing everything, you can selectively purge specific PL/SQL packages, types, triggers, or cursors using the DBMS_SHARED_POOL package.

Grant the required privilege:

SQL> GRANT EXECUTE ON DBMS_SHARED_POOL TO your_user;

Example 1: Purge a PL/SQL Package

SQL> EXEC DBMS_SHARED_POOL.PURGE('HR.EMP_BONUS_PKG','P');

Example 2: Purge a Cursor or SQL Statement

First, find the SQL in the library cache:

SQL> SELECT ADDRESS, HASH_VALUE, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%EMPLOYEES%';

Then purge using the address and hash value:

SQL> EXEC DBMS_SHARED_POOL.PURGE('00000007FA2E3D790,1670346623','C');

4. Object Type Flags

• P — Package or Package Body
• T — Type or Type Body
• R — Trigger
• C — Cursor / SQL Statement
• Q — Sequence

5. Comparing the Two Methods

Command	Scope	Impact	Recommended Use
ALTER SYSTEM FLUSH SHARED_POOL	Entire Shared Pool	High	For major resets or memory corruption issues
DBMS_SHARED_POOL.PURGE	Specific objects	Low	Routine maintenance, selective cleanup

6. Example Scenario

Suppose the package APPS.SALARY_PKG was recompiled, but users still see old logic executing. Instead of flushing the entire pool, you can simply purge that package:

SQL> EXEC DBMS_SHARED_POOL.PURGE('APPS.SALARY_PKG','P');

This forces Oracle to reload the package from the data dictionary on the next call, ensuring the latest version is executed.

7. Best Practices

• Prefer selective purging using DBMS_SHARED_POOL
• Use ALTER SYSTEM FLUSH SHARED_POOL only during controlled maintenance
• Monitor V$SGASTAT and V$LIBRARYCACHE before and after purging
• Avoid frequent Shared Pool flushes in production

Conclusion

Clearing the Shared Pool is sometimes necessary but should be done carefully. The DBMS_SHARED_POOL package provides fine-grained control, while ALTER SYSTEM FLUSH SHARED_POOL performs a full reset. Always choose the least disruptive method based on your situation.

Oracle documentation reference: Oracle Database Reference

Please feel free to ask if you have any questions. Thank you 🙂

Toufique Khan

How to Fix Oracle RAC 19c Installation Error: [INS-20802] Oracle Cluster Verification Utility Failed

During an Oracle Grid Infrastructure installation, encountering the [INS-20802] Oracle Cluster Verification Utility failed error can be a major roadblock. This error usually appears when the installer’s integrated CVU pre-check fails due to a missing prerequisite or a temporary environmental issue.

Fortunately, there’s a simple and effective workaround that allows you to verify prerequisites manually and proceed with your Oracle RAC installation without delays.

The Workaround: Manual Verification with cluvfy

The key to resolving this issue is to bypass the installer’s internal CVU check and perform the verification manually. If the manual check succeeds, it confirms that all prerequisites are indeed met and you can safely ignore the installer’s error.

Steps to Perform Manual Verification

• Open a terminal on your Grid Infrastructure server.
• Navigate to the bin directory of your Oracle Grid home.
• Run the cluvfy command with the appropriate stage and verbose options.

Example command to check the post-installation status for cluster services:

[grid@testdb03 bin]$ ./cluvfy stage -post crsinst -n testdb03,testdb04 -verbose

Expected Output on Success

Post-check for cluster services setup was successful.

CVU operation performed: stage -post crsinst
Date: Feb 25, 2025 11:34:17 PM
CVU home: /u01/app/19.0.0/grid_home/
User: grid

Tip: If your manual cluvfy command runs without any errors and shows a successful result, you can continue with your Oracle Grid Infrastructure installation. The installer’s previous error was likely a temporary glitch.

Why This Works

The installer uses its internal CVU to validate prerequisites, but sometimes environmental timing issues, DNS hiccups, or temporary resource bottlenecks trigger a false negative. By running cluvfy yourself, you directly test the same prerequisites without the installer’s overhead.

Key Takeaways

• Always verify prerequisites manually if the installer shows [INS-20802].
• Ensure SSH equivalency, correct DNS entries, and shared storage availability before running cluvfy.
• If cluvfy passes, it’s safe to proceed with the installation.

Reference: See Oracle’s official documentation on Oracle Grid Infrastructure Installation and Upgrade Guide for more details.

Please feel free to ask. Thank you 🙂

Your Name / Blog Name

RMAN Command Cheat Sheet for DBAs - Part 1

Oracle's Recovery Manager (RMAN) is the go-to tool for database backup, recovery, and cloning. Whether you're a beginner or an experienced DBA, having a quick reference handy saves time and prevents errors during critical operations.

This cheat sheet lists essential RMAN commands for day-to-day DBA work.

Start command

To start RMAN and connect to your database, use the following commands:

$ rman
$ rman NOCATALOG
$ rman TARGET SYS/pwd@target
$ rman TARGET SYS/pwd@target NOCATALOG
$ rman CATALOG rman/pwd@catdb
$ rman TARGET=SYS/pwd@target CATALOG=rman/pwd@cat
$ rman TARGET / CATALOG rman/rman@cat
$ rman | tee rman.log
$ rman help=yes

CONNECT command

Establish a connection between RMAN and a target, auxiliary, or recovery catalog database.

RMAN> CONNECT TARGET;
RMAN> CONNECT TARGET /
RMAN> CONNECT TARGET sys@tgt;
RMAN> CONNECT TARGET sys/pwd@tgt;
RMAN> CONNECT CATALOG rman@catdb;
RMAN> CONNECT CATALOG rman/pwd@catdb;
RMAN> CONNECT AUXILIARY /
RMAN> CONNECT AUXILIARY rman@auxdb;
RMAN> CONNECT AUXILIARY rman/pwd@auxdb;

SHOW command

Display the current CONFIGURE settings.

RMAN> SHOW ALL;
RMAN> SHOW RETENTION POLICY;
RMAN> SHOW RETENTION POLICY FOR DB_UNIQUE_NAME ALL;
RMAN> SHOW DEVICE TYPE;
RMAN> SHOW DEVICE TYPE FOR DB_UNIQUE_NAME 
RMAN> SHOW DEFAULT DEVICE TYPE;
RMAN> SHOW CHANNEL;
RMAN> SHOW MAXSETSIZE;
RMAN> SHOW BACKUP OPTIMIZATION;
RMAN> SHOW SNAPSHOT CONTROLFILE NAME;
RMAN> SHOW CONTROLFILE AUTOBACKUP;
RMAN> SHOW COMPRESSION ALGORITHM;
RMAN> SHOW ENCRYPTION ALGORITHM;
RMAN> SHOW ALL FOR DB_UNIQUE_NAME ALL;
RMAN> SHOW ALL FOR DB_UNIQUE_NAME 'STANDBY';

LIST command

Produce a detailed listing of backup sets or copies.

RMAN> LIST INCARNATION;
RMAN> LIST INCARNATION OF DATABASE;
RMAN> LIST INCARNATION OF DATABASE ;
RMAN> LIST DB_UNIQUE_NAME ALL;
RMAN> LIST DB_UNIQUE_NAME OF DATABASE;
RMAN> LIST BACKUP;
RMAN> LIST BACKUP SUMMARY;
RMAN> LIST BACKUP BY FILE;
RMAN> LIST BACKUP OF DATABASE;
RMAN> LIST BACKUP OF DATABASE BY BACKUP;
RMAN> LIST BACKUP OF TABLESPACE  SUMMARY;
RMAN> LIST BACKUP OF DATAFILE ;
RMAN> LIST BACKUP OF DATAFILE  SUMMARY;
RMAN> LIST BACKUP OF CONTROLFILE;
RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE ;
RMAN> LIST BACKUP OF ARCHIVELOG FROM TIME 'sysdate-1';
RMAN> LIST BACKUP OF ARCHIVELOG ALL COMPLETED BEFORE 'sysdate-2';
RMAN> LIST BACKUP RECOVERABLE;
RMAN> LIST EXPIRED BACKUP;
RMAN> LIST EXPIRED BACKUP OF ARCHIVELOG ALL SUMMARY;
RMAN> LIST COPY;
RMAN> LIST COPY OF DATABASE ARCHIVELOG ALL;
RMAN> LIST COPY OF TABLESPACE ;
RMAN> LIST COPY OF DATAFILE ;
RMAN> LIST COPY OF CONTROLFILE;
RMAN> LIST EXPIRED COPY;
RMAN> LIST BACKUPSET SUMMARY;
RMAN> LIST BACKUPSET 109;
RMAN> LIST BACKUPSET OF DATAFILE 1;
RMAN> LIST ARCHIVELOG;
RMAN> LIST ARCHIVELOG ALL LIKE '%5515%';
RMAN> LIST CONTROLFILECOPY "/tmp/cntrlfile.copy";
RMAN> LIST SCRIPT NAMES;
RMAN> LIST ALL SCRIPT NAMES;
RMAN> LIST GLOBAL SCRIPT NAMES;
RMAN> LIST FAILURE;
RMAN> LIST FAILURE 420 DETAIL;
RMAN> LIST FAILURE ALL;
RMAN> LIST RESTORE POINT ALL;

CONFIGURE command

The CONFIGURE command sets persistent RMAN settings.

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf%F';
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+BACKUP';
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE RETENTION POLICY CLEAR;
RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY CLEAR;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO sbt;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 3 TIMES TO disk;
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt_tape;
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
RMAN> CONFIGURE DEVICE TYPE sbt PARALLELISM 3;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt;
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt PARMS='ENV=mml_env_settings';
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt PARMS 'ENV=(NSR_SERVER=bksrv1)';
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt PARMS 'BLKSIZE=1048576';
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt FORMAT 'bkup_%U';
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt CLEAR;
RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE sbt CONNECT 'SYS/pwd@node2' PARMS 'ENV=(NSR_SERVER=bksrv2)';
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/tmp/%U';
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'C:\backup\df%t_s%s_s%p';
RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/backup/db_%s%d_%p';
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT CLEAR;
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK DEBUG 5;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE BACKUP OPTIMIZATION OFF;
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/backup/snapcf_%d.f';
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA/snap/snapcf_%d.f';
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ocfs/oradata/snapcf';
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/dev/sda';
RMAN> CONFIGURE MAXSETSIZE TO 100M;
RMAN> CONFIGURE MAXSETSIZE TO UNLIMITED;
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt MAXPIECESIZE 1G;
RMAN> CONFIGURE EXCLUDE FOR TABLESPACE example;
RMAN> CONFIGURE EXCLUDE CLEAR;
RMAN> CONFIGURE AUXNAME FOR DATAFILE 4 TO '/oracle/auxfiles/aux_4.f';
RMAN> CONFIGURE AUXNAME FOR DATAFILE 2 CLEAR;
RMAN> CONFIGURE COMPRESSION ALGORITHM 'BZIP2';
RMAN> CONFIGURE COMPRESSION ALGORITHM 'ZLIB';
RMAN> CONFIGURE COMPRESSION ALGORITHM 'LOW';
RMAN> CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';
RMAN> CONFIGURE COMPRESSION ALGORITHM 'HIGH';
RMAN> CONFIGURE COMPRESSION ALGORITHM 'BASIC';
RMAN> CONFIGURE DB_UNIQUE_NAME 'standby' CONNECT IDENTIFIER 'standby_cs';
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK FOR DB_UNIQUE_NAME 'standby';
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK FOR DB_UNIQUE_NAME ALL;
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO SBT FOR DB_UNIQUE_NAME po;

SET command

Set the value of various attributes that affect RMAN behavior for the duration of a RUN block or a session.

RMAN> SET ECHO ON;
RMAN> SET ECHO OFF;
RMAN> SET DATABASE ;
RMAN> SET DBID=;
RMAN> SET DBID ;
RMAN> SET COMMAND ID TO 'rman';
RMAN> SET MAXCORRUPT FOR DATABASE TO 2;
RMAN> SET MAXCORRUPT FOR DATAFILE 15 TO 200;
RMAN> SET BACKUP COPIES = 2;
RMAN> SET NEWNAME FOR DATABASE TO '/oradata1/%b';
RMAN> SET NEWNAME FOR TABLESPACE users TO '/oradata2/%U';
RMAN> SET NEWNAME FOR DATAFILE 1 to '/oradata/system01.dbf';
RMAN> SET NEWNAME FOR DATAFILE '/disk7/tbs11.f' TO '/disk9/tbs11.f';
RMAN> SET NEWNAME FOR TEMPFILE 1 TO '/newdisk/dbs/temp1.f';
RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE sbt TO 'cf_%F';
RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf_%F.bak';
RMAN> SET UNTIL TIME '04-23-2010:23:50:04';
RMAN> SET ARCHIVELOG DESTINATION TO '/oracle/temp_restore';
RMAN> SET COMPRESSION ALGORITHM 'LOW';
RMAN> SET COMPRESSION ALGORITHM 'LOW' OPTIMIZE FOR LOAD FALSE;
RMAN> SET COMPRESSION ALGORITHM 'MEDIUM';
RMAN> SET COMPRESSION ALGORITHM 'HIGH';

Incremental backups

Commands for performing incremental backups to save time and storage space.

RMAN> BACKUP INCREMENTAL LEVEL=0 DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL=1 DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL=0 DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE SKIP INACCESSIBLE DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr' DATABASE;
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'oltp' DATABASE;
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 351986 DATABASE FORMAT '/tmp/incr_standby_%U';
RMAN> BACKUP INCREMENTAL FROM SCN 629184 DATAFILE 5 FORMAT '/tmp/ForStandby_%U' TAG 'FORSTANDBY';
RMAN> BACKUP BLOCKS ALL CHECK LOGICAL VALIDATE DATAFILE 1398;

REPORT command

Report backup status: database, files, and backups. Perform detailed analyses of the content of the recovery catalog.

RMAN> REPORT OBSOLETE;
RMAN> REPORT NEED BACKUP;
RMAN> REPORT NEED BACKUP DAYS=5;
RMAN> REPORT NEED BACKUP REDUNDANCY=3;
RMAN> REPORT NEED BACKUP RECOVERY WINDOW OF 7 DAYS;
RMAN> REPORT NEED BACKUP DATABASE;
RMAN> REPORT NEED BACKUP INCREMENTAL 1;
RMAN> REPORT UNRECOVERABLE;
RMAN> REPORT SCHEMA;
RMAN> REPORT SCHEMA AT TIME 'sysdate-20/1440';

CATALOG command

Add information about file copies and user-managed backups to the catalog repository.

RMAN> CATALOG DATAFILECOPY '';
RMAN> CATALOG DATAFILECOPY '' LEVEL 0;
RMAN> CATALOG CONTROLFILECOPY '/disk3/backup/cf_copy.bkp';
RMAN> CATALOG ARCHIVELOG '';
RMAN> CATALOG BACKUPPIECE '';
RMAN> CATALOG LIKE '/backup';
RMAN> CATALOG START WITH '/fs2/arch';
RMAN> CATALOG START WITH '/disk2/archlog' NOPROMPT;
RMAN> CATALOG START WITH '+FLASH';
RMAN> CATALOG RECOVERY AREA;

SWITCH command

Specify that a datafile copy is now the current datafile, i.e., the datafile pointed to by the control file. This command is equivalent to the SQL statement ALTER DATABASE RENAME FILE as it applies to datafiles.

RMAN> SWITCH DATABASE TO COPY;
RMAN> SWITCH TABLESPACE users TO COPY;
RMAN> SWITCH DATAFILE ALL;
RMAN> SWITCH DATAFILE '/disk1/tols.dbf' TO DATAFILECOPY '/disk2/tols.copy';
RMAN> SWITCH DATAFILE "+DG_OLD/db/datafile/sysaux.260.699468081" TO COPY;
RMAN> SWITCH TEMPFILE 1;
RMAN> SWITCH TEMPFILE 1 TO '/newdisk/dbs/temp1.f';
RMAN> SWITCH TEMPFILE ALL;
RMAN> SWITCH CLONE DATAFILE ALL;

SPOOL command

Write RMAN output to a log file.

RMAN> SPOOL LOG TO '/tmp/spool.log';
RMAN> SPOOL LOG TO '/tmp/backup.log' APPEND;
RMAN> SPOOL LOG OFF;

Please feel free to ask if you have any questions. Thank you! 🙂

Toufique Khan

How to Rename a PDB in Oracle 19c — Step-by-Step Guide for DBAs

Renaming a Pluggable Database (PDB) in Oracle 19c is not something we do every day—but when we do, it needs to be done right. Whether you’re standardizing your naming convention, consolidating environments, or simply cleaning things up, Oracle 19c makes it relatively straightforward.

In this blog post, we’ll walk through two supported methods for renaming a PDB in Oracle 19c, along with tips, gotchas, and real-world examples.

Why Rename a PDB?

You might want to rename a PDB in Oracle 19c for reasons like:

• Fixing inconsistent naming conventions (e.g., PDBTEST1 to HRPDB)
• Standardizing naming during migrations or refreshes
• Rebranding or aligning with environment labels (e.g., STAGEPDB to PRODPDB)

Whatever the use case, renaming makes your environment more readable and manageable.

Method 1: Rename PDB from CDB Level (Using SQL*Plus or SQL Developer)

This is the most direct method and can be performed from the container (CDB) level. It's suitable for simple renames where you manage the PDB from the CDB root.

Steps:

1. Close the PDB

   ALTER PLUGGABLE DATABASE NEP1 CLOSE IMMEDIATE;

2. Rename the PDB

   ALTER PLUGGABLE DATABASE NEP1 RENAME TO NEP;

3. Open the PDB

   ALTER PLUGGABLE DATABASE NEP OPEN;

Method 2: Rename Global Name from Within the PDB

Alternatively, if you're working within the pluggable database, you can use the RENAME GLOBAL_NAME clause. This method is often preferred or necessary in specific scenarios, such as after cloning a PDB where you want to update its logical name without changing its original identity from the CDB perspective during the clone operation.

Steps:

1. Close the PDB and Open in Restricted Mode (from CDB Root)

   ALTER PLUGGABLE DATABASE PDBNEP CLOSE IMMEDIATE;
   ALTER PLUGGABLE DATABASE PDBNEP OPEN RESTRICTED;

2. Switch to the PDB Session

   ALTER SESSION SET CONTAINER=PDBNEP;

3. Rename the PDB Global Name (from within the PDB)

   ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO ECODB;

4. Restart the PDB (from CDB Root)

   First, switch back to the CDB root:

   SQL> alter pluggable database close immediate;
   
   Pluggable database altered.
   
   SQL> alter pluggable database open;
   
   Pluggable database altered.
   
   SQL>  show pdbs
   
       CON_ID CON_NAME          OPEN MODE  RESTRICTED
   ---------- ----------------- ---------- ----------
            3 ECODB             READ WRITE NO

Tip: Choosing the Right Method

The ALTER PLUGGABLE DATABASE <old_pdb_name> RENAME TO <new_pdb_name> command (Method 1) is ideal for straightforward administrative renaming at the CDB level. It performs a complete rename, updating all necessary internal structures.

The ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO command (Method 2) is particularly useful when you've cloned a PDB and want to update its logical global name to reflect its new purpose or environment, without affecting the original PDB or its physical paths on the OS (if applicable).

Conclusion

Renaming a PDB in Oracle 19c is simple but powerful. Whether you're restructuring your database environment or aligning with naming conventions, just follow the right steps and validate afterward. By understanding both supported methods, DBAs can choose the most appropriate approach for their specific scenario.

Please feel free to ask if you have any questions. Thank you! 🙂
Toufique Khan