Thursday, August 22, 2019

How to change location for the redo log files in physical standby database


Short background:
After a cold restore of the database files from standbyserver1 to standbyserver2, the redo logfiles where incorrectly registered in the database's control file.
This resulted in an error about the missing file every time the database opened or mounted:
Errors in file /u01/oracle/diag/rdbms/prodbb01_stby2/proddb01/trace/proddb01_m000_38117.trc:
ORA-00312: online log 3 in thread 1: /fra/PRODDB01_STBY1/onlinelog/o1_mf_3__dm491kvf_.log
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Since this is a standby database it is not stopping the database from functioning as intended, but it was annoying nevertheless.
Here is how I fixed the problem:

Shutdown the database, cancel managed recovery and open it in mounted mode:
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate
startup mount

Allow file renaming on the stadby by temporarily setting STANDBY_FILE_MANAGEMENT to MANUAL.
alter system set STANDBY_FILE_MANAGEMENT=manual scope=memory;
If this is not done, Oracle will throw error
ORA-01275: Operation RENAME is not allowed if standby file management is automatic.
Rename the file(s):
alter database rename file '/fra/PRODDB01_STBY1/onlinelog/o1_mf_3__dm491kvf_.log' to '/fra/PRODDB01_STBY2/onlinelog/o1_mf_3__df26ddnv_.log';

Depending on your configuration, start managed reply in mounted state, or open the database in real-time query mode. I am doing the latter:
alter database open;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

No comments:

Post a Comment