This post was originally published on this site
Hi there,
today is the 1st birthday of a service request with Oracle I opened for which I see no progress since quite a while. I raised it exactly one year ago, on Oct 2nd 2019. There were some activity in last October, November mostly logging about the issue. There was no update in the SR since January with the status “Awaiting Internal Response”.
Subject is the same as this blog entry: Definition of Online Redo Logs is “broken” after duplicate for standby.
So what is it about? It refers to the process of setting up standby databases and their file names specifically redo log members.
There is no issue if you:
- run an Oracle database version less that 19c
- use ASM
- use OMF
- use a different file structure on standby database server intentionally.
The issue occurs in case you use rman duplicate for creation, which is very probable and in case you intend to have same redo log member names on standby for online log files and/or standby redo log files.
But should we really have same file names on standby, isn’t it bad? No it is not bad, it is even a best practice to use same file names on primary as on standby unless we use ASM, OMF. Why? It simply reduces complexity. In case you need to re-setup standby, re-create standby control file or whatever, you do not want to struggle with name conversions and re-think every time about correct name of a file based on the site of the database.
So how do we set up a standby with RMAN? We have installed standby database server and created an instance with appropriate init.ora parameters. We set up log_file_name_convert which we do already since the 10g times:
- to get same names for redo log members
- to get same multiplexing of redo log files
- to avoid online logs definition in fast recovery area
- to get a correct clearing of redo log members.
See MOS articles for details, why and how we should set up log_file_name_convert here:
- ORA-19527 reported in Standby Database when starting Managed Recovery (Doc ID 352879.1)
- ORA-19527: Physical Standby Redo Log Must Be Renamed…during switchover (Doc ID 2194825.1)
- 18c & 19c Physical Standby Switchover Best Practices using SQL*Plus (Doc ID 2485237.1)
It is set to “log_file_name_convert=’/u’,’/u’” in my demo environment. I start the instance in nomount and issue:
RMAN> duplicate target database for standby from active database nofilenamecheck;
We are used to use “nofilenamecheck” clause here, as we intend to have identical file structure for everything, data files, log files, etc.. From the docs of this clause https://docs.oracle.com/en/database/oracle/oracle-database/19/rcmrf/DUPLICATE.html#GUID-E13D8A02-80F9-49A2-9C31-92DD3A795CE4
"Prevents RMAN from checking whether the data files and online redo logs files of the source database are in use when the source database files share the same names as the duplicate database files. You are responsible for determining that the duplicate operation does not overwrite useful data."
So perfect it tells us, that we are responsible for the fact the files will be overwritten at the auxiliary site, the standby server.
The output of the duplicate is quite long, so I stripped it – at least a little bit:
RMAN> duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 28.09.2020 12:27:44
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=262 device type=DISK
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/u00/app/oracle/product/19/dbs/orapwCYPECORE' ;
}
executing Memory Script
Starting backup at 28.09.2020 12:27:45
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=148 device type=DISK
Finished backup at 28.09.2020 12:27:48
contents of Memory Script:
{
restore clone from service 'cypecore_site1' standby controlfile;
}
executing Memory Script
Starting restore at 28.09.2020 12:27:48
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cypecore_site1
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/CYPECORE/ctl1CYPECORE.dbf
output file name=/u02/oradata/CYPECORE/ctl2CYPECORE.dbf
Finished restore at 28.09.2020 12:27:52
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/oradata/CYPECORE/system01CYPECORE.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/oradata/CYPECORE/pdbseed_system01CYPECORE.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/oradata/CYPECORE/sysaux01CYPECORE.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/oradata/CYPECORE/pdbseed_sysaux01CYPECORE.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/oradata/CYPECORE/undotbs01CYPECORE.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/oradata/CYPECORE/pdbseed_undotbs01CYPECORE.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/oradata/CYPECORE/users01CYPECORE.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/oradata/PARKWAYDRIVE/system01PARKWAYDRIVE.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/oradata/PARKWAYDRIVE/sysaux01PARKWAYDRIVE.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/oradata/PARKWAYDRIVE/undotbs01PARKWAYDRIVE.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/oradata/PARKWAYDRIVE/users01PARKWAYDRIVE.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (tempfile) file name /u01/oradata/CYPECORE/temp01CYPECORE.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (tempfile) file name /u01/oradata/CYPECORE/pdbseed_temp01CYPECORE.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (tempfile) file name /u01/oradata/PARKWAYDRIVE/temp01PARKWAYDRIVE.dbf conflicts with a file used by the target database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/oradata/CYPECORE/temp01CYPECORE.dbf";
... # all data and tempfiles are mapped here to the same name as before
set newname for datafile 71 to
"/u01/oradata/PARKWAYDRIVE/users01PARKWAYDRIVE.dbf";
restore
from nonsparse from service
'cypecore_site1' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
...
renamed tempfile 1 to /u01/oradata/CYPECORE/temp01CYPECORE.dbf in control file
renamed tempfile 2 to /u01/oradata/CYPECORE/pdbseed_temp01CYPECORE.dbf in control file
renamed tempfile 6 to /u01/oradata/PARKWAYDRIVE/temp01PARKWAYDRIVE.dbf in control file
executing command: SET NEWNAME
...
Starting restore at 28.09.2020 12:27:58
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cypecore_site1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/oradata/CYPECORE/system01CYPECORE.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
...
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cypecore_site1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00071 to /u01/oradata/PARKWAYDRIVE/users01PARKWAYDRIVE.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 28.09.2020 12:28:10
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=38 STAMP=1052310490 file name=/u01/oradata/CYPECORE/system01CYPECORE.dbf
...
datafile 71 switched to datafile copy
input datafile copy RECID=48 STAMP=1052310490 file name=/u01/oradata/PARKWAYDRIVE/users01PARKWAYDRIVE.dbf
RMAN-05535: warning: All redo log files were not defined properly.
...
Finished Duplicate Db at 28.09.2020 12:28:11
So we get a warning for data and temp files. It claims “conflicts with a file used by the target database”. Hmm, yes we know that the file names are the same. And we get a warning for log files “All redo log files were not defined properly”. The RMAN-05535 appears once for every redo log member no matter whether online or standby log, in my case 14 times. What does that mean? After a check on the created standby we realize that everything is fine with the data and temp files, but what the heck happened with the redo log members:
SQL> SELECT member FROM v$logfile; MEMBER --------------------------------------- /u00/app/oracle/product/19/dbs/broken0 /u00/app/oracle/product/19/dbs/broken1 /u00/app/oracle/product/19/dbs/broken2 /u00/app/oracle/product/19/dbs/broken3 /u00/app/oracle/product/19/dbs/broken4 /u00/app/oracle/product/19/dbs/broken5 /u00/app/oracle/product/19/dbs/broken6 /u00/app/oracle/product/19/dbs/broken7 /u00/app/oracle/product/19/dbs/broken8 /u00/app/oracle/product/19/dbs/broken9 /u00/app/oracle/product/19/dbs/broken10 /u00/app/oracle/product/19/dbs/broken11 /u00/app/oracle/product/19/dbs/broken12 /u00/app/oracle/product/19/dbs/broken13 14 rows selected.
They are now called “broken0” to “broken13”. At the start they are only defined like that. They are physically created when they are cleared which should happen as soon as you start recovering you standby.
In alert log of standby we find something like:
setnotrustfnames set to : 0 alter database rename file '/u01/oradata/CYPECORE/redog1m1CYPECORE.dbf' to 'broken0' Completed: alter database rename file '/u01/oradata/CYPECORE/redog1m1CYPECORE.dbf' to 'broken0'
So the issue is really with keeping the names for online logs which starting with 19c Oracle does not like any more. The renaming to “broken” happens when using rman duplicate and redo log members are the same. It does not matter whether you are using “from active” database or duplicating from backup.
Here is a statement from the SR from Oracle:
“The code did change on 19c and new versions to avoid overwriting of the online/standby redo logs.
Currently, there is no option to avoid such behavior that I am aware of.
There were many cases of RMAN duplicate that caused overwriting on ONLINE redo logs at Primary so this changes must have been implemented to avoid such scenarios.
“
Somehow I understand Oracle developments intention here, but nevertheless I do not like that, at least not when setting up a standby on a different host. Maybe it makes sense in case duplicate is not used with the “for standby” clause for cloning, but I leave that discussion open to you. And more important, it does not make sense to have log file members named “broken”. The problem here is – at least for me – that beside the fact the names are different, all the other things work like expected. So you could easily miss that warning and go on setting up your Data Guard.
Why this is a problem?
I think, if you change the ORLs to broken0, broken1 and so on, this is even worse. Some junior DBA might easily delete such files, as he/she can assume, that they should not be there, right? And, deleting online redo log files can have very bad impact, as you know.
Another problem: Overwriting now really can happen, you only need to share the Oracle home with more than one standby database instance which also might have a broken0 and so on, now imagine you need to do multiple failovers. You will have several primaries on the same server with exactly the same online redo log file definitions which of course cannot work, this will be a very hard recovery case then.
Until today I can only present workarounds for the described problem, which follow here:
- not really a good way, but we could accept different names for online and standby logs. E.g. on standby:
ALTER SYSTEM SET log_file_name_convert='CYPECORE.dbf','CYPECORE_SITE2.dbf' SCOPE=SPFILE;
Then re-setup standby with rman duplicate as usual.
- best workaround in my opinion
On standby:
ALTER SYSTEM SET standby_file_management='AUTO';
We will get even more errors and warnings in the rman output now, but the result is as desired. By setting standby_file_management to AUTO we simply disallow renaming the log file members which is attempted by the rman sessions. New messages at the rman prompt are added now:
Oracle error from auxiliary database: ORA-01275: Operation RENAME is not allowed if standby file management is automatic.
in alert log this time we see:
setnotrustfnames set to : 0 alter database rename file '/u01/oradata/CYPECORE/redog1m1CYPECORE.dbf' to 'broken0' ORA-1275 signalled during: alter database rename file '/u01/oradata/CYPECORE/redog1m1CYPECORE.dbf' to 'broken0'...
yes we would have to live with even more warnings but the result is okay.
- simply rename files back to original name.
I use the following to do this after the duplicate to check and to rename in case it is needed, the original names come from alert log accessed by x$diag_alert_ext, which is accessible even in mount mode in contrast to the documented view v$diag_alert_ext.
SET SERVEROUTPUT ON
DECLARE
v_member VARCHAR2(512);
BEGIN
FOR i IN (SELECT
CASE
WHEN d.platform_name LIKE '%Windows%'
THEN substr(l.member,instr(l.member,'',-1)+1)
ELSE substr(l.member,instr(l.member,'/',-1)+1)
END filename
FROM v$logfile l, v$database d
WHERE member LIKE '%/dbs/broken%'
OR member LIKE '%DATABASEBROKEN%') LOOP
BEGIN
SELECT substr(message_text,instr(message_text,'''',1,1)+1,instr(message_text,'''',1,2)-instr(message_text,'''',1,1)-1)
INTO v_member
FROM x$diag_alert_ext
WHERE originating_timestamp > sysdate - 10/1440
AND lower(message_text) LIKE 'completed: alter database rename file ''%'' to ''' || lower(i.filename) || '''' || chr(10)
AND rownum = 1
;
EXECUTE IMMEDIATE 'ALTER DATABASE RENAME FILE ''' || i.filename || ''' TO ''' || v_member || '''';
EXCEPTION WHEN no_data_found THEN
dbms_output.put_line('WARNING: I have no clue which is the correct name for ' || i.filename);
END;
END LOOP;
END;
/
you should run this instantly (the above code gives you 10 minutes) before you start an initial recovery or set up broker etc. so before an actual clearing happens.
- after the duplicate, get a new standby control file from primary.
a) The very traditional way:
On Primary:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/ctlCYPECORE.dbf' REUSE;
On standby:
copy over that file, stop instance, then copy to correct location:
cp /tmp/ctlCYPECORE.dbf /u01/oradata/CYPECORE/ctl1CYPECORE.dbf cp /tmp/ctlCYPECORE.dbf /u02/oradata/CYPECORE/ctl2CYPECORE.dbf
Start standby to mount mode and continue all the rest as usual.
b) the 2020 way:
use rman to connect to standby to retrieve a control file from primary via Oracle net:
RMAN> SHUTDOWN IMMEDIATE RMAN> STARTUP NOMOUNT RMAN> RESTORE STANDBY CONTROLFILE FROM SERVICE 'CYPECORE_SITE1'; RMAN> ALTER DATABASE MOUNT;
- Here we do not use rman duplicate at all. It is a very old-fashioned way.
We bring all data files to backup mode (“ALTER DATABASE BEGIN BACKUP”) and copy them over together with a standby control file which is created with “ALTER DATABASE CREATE STANDBY CONTROLFILE” like you have seen in 4. a) Then bring standby to mount and continue. Do not forget to end backup mode for data files.
- For this solution we use again rman, but not duplicate.
Interestingly the check, which we do not like, is only performed for the duplicate cases.
a) connect to standby in nomount, then:
RMAN> RESTORE STANDBY CONTROLFILE FROM SERVICE 'CYPECORE_SITE1'; RMAN> ALTER DATABASE MOUNT; RMAN> RESTORE DATABASE FROM SERVICE 'CYPECORE_SITE1';
b) Wait, there was a new cool feature, that is the all in one replacement of the same which came in 18c, right?
RMAN> RECOVER STANDBY DATABASE FROM SERVICE 'CYPECORE_SITE1';
But to be honest, to be able to run this, you need to be in mount mode, so you might also need the first two commands from 6 a). At least it works.
- use dbca?
There is another brand new feature, how to set up a standby database. Oracle encourages us to use it. So let’s try dbca. We can use dbca as follows to create a standby database.
dbca -silent -createDuplicateDB -gdbName CYPECORE -primaryDBConnectionString zam42:1521/CYPECORE_SITE1 -sysPassword manager -sid CYPECORE -createAsStandby -dbUniquename CYPECORE_SITE2
It is a little annoying that it adapts (actually scrambles) my listener.ora to set up a temporarily used listener, which is not even stopped at the end. Also the fact that init.ora parameter control_files is not set up like on the primary is something which is not that optimal.
On primary I have
control_files=’/u01/oradata/CYPECORE/ctl1CYPECORE.dbf’,’/u02/oradata/CYPECORE/ctl2CYPECORE.dbf’
On standby dbca sets up:
control_files=’/u01/oradata/CYPECORE/ctl1CYPECORE.dbf’,’/u02/fast_recovery_area/CYPECORE_SITE2/ctl2CYPECORE.dbf’
But at least a standby is created correctly. And the most important, what is with the log files? They are “broken” again. 
And this is not surprising. Under the hood an rman duplicate is used. To clean this up now you would need to combine this with one of the other workarounds.
Conclusion
I gets a little harder to set up a standby database in 19c which uses exactly same file system layout and same file names as the primary database. rman duplicate has a new security enhancement which is meant to avoid the same names. This “enhancement” needs to be circumvented here. It is a pity that Oracle support and development do not answer my service request related to that issue since a very long time.
Best workaround in my opinion is to use log_file_name_convert=’something’,’something’ as we are used to since a long time, together with standby_file_management=’AUTO’.
Hope that helps
Mathias
