Rename Datafile In Oracle Standby on New Location

Recently, I encountered a situation while working for a customer where there was a space constraint on the DR server. Unfortunately, we were unable to allocate additional space. However, some directories had approximately 90GB of available free space. To resolve this issue, I came up with a solution to rename the data file and move it to a new mount that had sufficient space. The process of renaming a data file on a standby database is relatively straightforward.

Step 1: Terminate the managed recovery process and transition the physical standby database to the mount state.

SQL> Alter database recover managed standby database cancel;

SQL> shutdown immediate

SQL> startup mount

Step 2: Now, Modify the value of the standby_file_management parameter to MANUAL. By default, this parameter is set to AUTO, which results in the automatic addition of standby datafiles whenever a datafile is added in the primary database.

SQL> alter system set standby_file_management=MANUAL;

Step 3: Relocate the datafile to a new location using the appropriate OS command. Since I am operating in the 19c environment, I have the capability to perform the move online, ensuring uninterrupted accessibility.

ALTER DATABASE MOVE DATAFILE '/u01/oracle/db/UATDB/APEX002.dbf' TO '/u20/oracle/db/UATDB/APEX002.dbf';

Step 4: Restore the standby_file_management parameter back to its default setting of AUTO.

SQL> alter system set standby_file_management=AUTO;

Step 5: Resume the managed recovery process that was halted in Step 1.

SQL> Alter database recover managed standby database using current logfile disconnect;

December 23, 2022 Redo Transport Services fails with ORA-16198

Errors/Workarounds19c

Redo Transport Services fails with ORA-16198

At the alert log, we received this error message on the production database for the 19c Data Guard.

The primary alert log file showed:

LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (16198)
LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Fri Jan 4 12:00:36 2023
Errors in file //bdump/.trc:
ORA-16198: Timeout incurred on internal channel during remote archival
LGWR: Network asynch I/O wait error 16198 log 2 service ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=)(INSTANCE_NAME=)(SERVER=dedicated)))’

After investigating the issue, we noticed that our network is too slow. We encountered this error when the primary database attempted to send a large logfile to the standby database.

The NET_TIMEOUT attribute in the LOG_ARCHIVE_DEST_2 at the primary database is either not set or set to a low value (such as 5-10 seconds), causing the LNS (Log Network Services) to be unable to complete the sending of redo blocks within the specified time.

To resolve this issue, you should increase the NET_TIMEOUT value in the LOG_ARCHIVE_DEST_2 at the primary database to at least 15 to 20 seconds, depending on the speed of your network.

In our specific case, we have already set the NET_TIMEOUT value in the LOG_ARCHIVE_DEST_2 at the primary database to 60 seconds.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 SERVICE= LGWR SYNC DB_UNIQUE_NAME= NET_TIMEOUT=60 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

Reference:
Redo Transport Services fails with ORA-16198 when using SYNC (synchronous) mode (Doc ID 808469.1)