Recently I had to create a Data Guard physical standby database, and I used RMAN active database duplication ( "duplicate target database for standby from active database" ) to create the database on the remote server.
As the remote server had the same file system layout as the primary, I used the directive
NOFILENAMECHECK
in the duplicate command.
Consequently, I expected all datafiles, including the redo and standby redo log files, to be created in exactly the same location as the target.
For some reason this did not happen; in fact, they were named with OMF file names, and placed in the db_recovery_file_dest which point to the path /recovery_data, while on the primary, the same files reside in /u02/oradata
I found the solution based on a post called "
JoeLi's TechLife" and added my own experience to find a workaround.
The author points out
after the duplication, all other files seem to be fine regarding their names and locations except for redo log files and standby redo log files. They are created under the fast recovery area with OMF file names!
How come? Well here is the reason — duplicate will always re-create redo log files and standby redo log files. And because DB_RECOVERY_FILE_DEST is defined on the Primary server, redo and standby redo log files are created as OMF despite NOFILENAMECHECK is used. And they are not multiplexed as you have on the Primary database!
I was not aware of this.
The author continues to point out that he hasn't found a solution, yet.
The proposed solution according to Joe is
... to recreate redo log and standby redo log files on the newly created standby server — basically dropping the OMF ones and creating them under the correct locations with correct file names.
I did however, find a solution, which worked for me and was really quite simple, although not very intuitive: simply set the directive log_file_name_convert in your clone script, like examplified below:
connect target sys/password@primary
connect auxiliary target sys/password@stb
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
SET DB_UNIQUE_NAME='stb'
SET FAL_CLIENT='stb'
SET FAL_SERVER='primary'
SET LOG_ARCHIVE_DEST_1='location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stb'
SET LOG_ARCHIVE_DEST_2='' comment 'Must be set to empty string during duplication'
SET STANDBY_FILE_MANAGEMENT='AUTO'
SET DG_BROKER_CONFIG_FILE1='/u01/oracle/product/12c/dbs/dr1stb.dat'
SET DG_BROKER_CONFIG_FILE2='/u01/oracle/product/12c/dbs/dr2stb.dat'
SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(stb,primary)'
SET DG_BROKER_START='TRUE'
SET LOG_ARCHIVE_TRACE='0' comment 'Must be set to same value as primary during duplicaton'
SET dispatchers='(PROTOCOL=TCP) (SERVICE=stbXDB)' comment 'Must be set to unique name of stb db'
SET LOCAL_LISTENER='stb.skead.no' comment 'Must be set to unique name of stb db'
SET LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf' comment 'Must be set to same value as primary during duplicaton'
SET LOG_FILE_NAME_CONVERT='/redodata','/redodata' comment 'Must be set during cloning to avoid OMF naming of redo log files'
NOFILENAMECHECK
USING COMPRESSED BACKUPSET;
}
Aftwards, the query
COL MEMBER FORMAT A50
SET LINES 200
SELECT A.GROUP#,A.MEMBER, B.BYTES/1024/1024 "MB", B.ARCHIVED,B.STATUS,B.SEQUENCE#
FROM V$LOGFILE A INNER JOIN V$LOG B ON A.GROUP# = B.GROUP#
ORDER BY GROUP# ASC;
set lines 200
col member format a50
select s.group#,s.thread#,s.sequence#,s.archived,s.status,f.member, f.type
from v$standby_log s, v$logfile f
where f.type = 'STANDBY'
and s.group# = f.group#;
confirms that the standby database was created with non-OMF datafiles:
SQL> @chk_redo.sql
GROUP# MEMBER MB ARC STATUS SEQUENCE#
---------- -------------------------------------------------- ---------- --- ---------------- ----------
1 /redodata/redo01.log 2048 NO CURRENT 156
2 /redodata/redo02.log 2048 YES UNUSED 0
3 /redodata/redo03.log 2048 YES UNUSED 0
GROUP# THREAD# SEQUENCE# ARC STATUS MEMBER TYPE
---------- ---------- ---------- --- ---------- -------------------------------------------------- -------
4 1 0 YES UNASSIGNED /redodata/stb_redo01.log STANDBY
5 1 0 YES UNASSIGNED /redodata/stb_redo02.log STANDBY
6 1 0 YES UNASSIGNED /redodata/stb_redo03.log STANDBY
7 1 0 YES UNASSIGNED /redodata/stb_redo04.log STANDBY
