DUPLICATE "duplicates" several datafiles.

Hiroshi Komatsu

Hi, I'm trying to duplicate a database as part of a R12.1.3 EBS cloning process, it's still on 11.1.0.7 (Linux x86-64), and the plan is to upgrade within a year to R12.2.x, and 19c.

The problem I'm facing is that the duplicate runs, and seems to be restoring the files:

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 16-JUN-2020 18:39:27

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=2380 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00045 to /db02/oradata/TEST/a_txn_data14.dbf

channel ORA_AUX_DISK_1: restoring datafile 00055 to /db01/oradata/TEST/sysaux05.dbf

channel ORA_AUX_DISK_1: restoring datafile 00392 to /db01/oradata/TEST/a_txn_data01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00406 to /db00/oradata/TEST/a_txn_ind05.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u06/hotbkp/PROD/past/DB_g7v23huv_1_1.rman

However, if I list the database directories, I see this:

$ ls -lh /db0?/oradata/TEST

/db00/oradata/TEST:

total 30G

-rw-r----- 1 oracle dba 32G Jun 16 19:30 a_txn_data14.dbf

-rw-r----- 1 oracle dba 32G Jun 16 19:30 a_txn_ind05.dbf

/db01/oradata/TEST:

total 30G

drwxr-xr-x 2 oracle dba 4.0K Jun 11 21:56 archive

-rw-r----- 1 oracle dba  32G Jun 16 19:30 a_txn_data01.dbf

-rw-r----- 1 oracle dba  32G Jun 16 19:30 sysaux05.dbf

/db02/oradata/TEST:

total 30G

-rw-r----- 1 oracle dba 32G Jun 16 19:30 a_txn_data14.dbf

-rw-r----- 1 oracle dba 32G Jun 16 19:30 a_txn_ind05.dbf

As you can see, some files are being "duplicated" on other directories; the source database does not have files with the same name in different directories.  This is the second time this is happening, in the first DUPLICATE attempt I specified DB_FILE_NAME_CONVERT in the command.  This time, explicitly assigned paths to the datafiles using SET NEWNAME for datafiles and tempfiles, in the same run block as the DUPLICATE command.

That first duplicate completed succesfully, and I was able to startup/shutdown the database, when I listed the datafiles, there were no duplicates:

SQL> select file#, name, status, bytes/1048576 "Mb"

  2    from v$datafile

  3   where name like '%ind09%';

     FILE# NAME                                               STATUS          Mb

---------- -------------------------------------------------- ------- ----------

        35 /db00/oradata/TEST/a_txn_ind09.dbf                 ONLINE  32767.9844

But, in the OS:

# ls -lh /db0?/oradata/TEST/a_txn_ind09.dbf

-rw-r----- 1 oracle dba 32G Jun 13 16:42 /db00/oradata/TEST/a_txn_ind09.dbf

-rw-r----- 1 oracle dba 32G Jun 13 16:42 /db02/oradata/TEST/a_txn_ind09.dbf

I shutdown the db, and renamed the file in /db02:

# cd /db02/oradata/TEST

# mv a_txn_ind09.dbf a_txn_ind09.not

# ls -lh a_txn_ind09*

-rw-r----- 1 oracle dba 32G Jun 13 16:56 a_txn_ind09.not

When I restarted the db:

SQL> startup

ORACLE instance started.

Total System Global Area 1.6034E+10 bytes

Fixed Size                  2165888 bytes

Variable Size            2415920000 bytes

Database Buffers         1.3556E+10 bytes

Redo Buffers               60137472 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 35 - see DBWR trace file

ORA-01110: data file 35: '/db00/oradata/TEST/a_txn_ind09.dbf'

The same occurred when If I renamed the file in /db00.

I'd appreciate if anybody could shed some light on this.  I doesn´t happen with every file, only with some.

Thanks!

Hiroshi Komatsu 

Hiroshi Komatsu

Hi.  Turned out that /db00 and /db02 were mounted on the same physical partition, and so, we get the same files in both filesystems.  I'll wait for the restore to finish, to correct this.

First time this happens to me.

Thanks!

Hiroshi Komatsu

Dude!

There's nothing wrong looking at your file directories or RMAN output so far. There could be several reasons that may explain why you have identical files in different directories. However, looking at a filename and concluding what went wrong is beyond my skills. You need to show your RMAN duplicate command and also analyze the RMAN output to verify what was restored and renamed exactly.

Did you ever use "backup as copy" instead of "duplicate"? Just an idea, what do you see if you enter the following into RMAN:

RMAN> report schema;

RMAN> list datafilecopy all;

Hiroshi Komatsu

Hi, thanks for the reply!

This is the script I'm using for the second attempt (it's still running):

run {

set newname for datafile  1 to '/db01/oradata/TEST/system01.dbf';

set newname for datafile  2 to '/db01/oradata/TEST/system02.dbf';

set newname for datafile  3 to '/db01/oradata/TEST/system03.dbf';

set newname for datafile  4 to '/db01/oradata/TEST/system04.dbf';

set newname for datafile  5 to '/db01/oradata/TEST/system05.dbf';

set newname for datafile  6 to '/db01/oradata/TEST/ctxd01.dbf';

set newname for datafile  7 to '/db01/oradata/TEST/owad01.dbf';

set newname for datafile  8 to '/db02/oradata/TEST/a_queue02.dbf';

set newname for datafile  9 to '/db01/oradata/TEST/odm.dbf';

set newname for datafile  10 to '/db01/oradata/TEST/olap.dbf';

set newname for datafile  11 to '/db01/oradata/TEST/sysaux01.dbf';

set newname for datafile  12 to '/db02/oradata/TEST/apps_ts_tools01.dbf';

set newname for datafile  13 to '/db01/oradata/TEST/system12.dbf';

set newname for datafile  14 to '/db01/oradata/TEST/a_txn_data04.dbf';

set newname for datafile  15 to '/db00/oradata/TEST/a_txn_ind06.dbf';

set newname for datafile  16 to '/db02/oradata/TEST/a_ref03.dbf';

set newname for datafile  17 to '/db02/oradata/TEST/a_int02.dbf';

set newname for datafile  18 to '/db01/oradata/TEST/sysaux02.dbf';

set newname for datafile  19 to '/db01/oradata/TEST/xxcustd01.dbf';

set newname for datafile  20 to '/db01/oradata/TEST/xxcustx01.dbf';

set newname for datafile  21 to '/db01/oradata/TEST/a_media02.dbf';

set newname for datafile  22 to '/db01/oradata/TEST/a_txn_data05.dbf';

set newname for datafile  23 to '/db01/oradata/TEST/a_txn_data06.dbf';

set newname for datafile  24 to '/db00/oradata/TEST/a_txn_ind07.dbf';

set newname for datafile  25 to '/db02/oradata/TEST/a_queue03.dbf';

set newname for datafile  26 to '/db01/oradata/TEST/a_txn_data07.dbf';

set newname for datafile  27 to '/db01/oradata/TEST/a_txn_data08.dbf';

set newname for datafile  28 to '/db01/oradata/TEST/undo02.dbf';

set newname for datafile  29 to '/db02/oradata/TEST/a_txn_data09.dbf';

set newname for datafile  30 to '/db02/oradata/TEST/a_txn_data10.dbf';

set newname for datafile  31 to '/db02/oradata/TEST/a_queue04.dbf';

set newname for datafile  32 to '/db00/oradata/TEST/a_txn_ind08.dbf';

set newname for datafile  33 to '/db01/oradata/TEST/undo03.dbf';

set newname for datafile  34 to '/db02/oradata/TEST/a_txn_data11.dbf';

set newname for datafile  35 to '/db00/oradata/TEST/a_txn_ind09.dbf';

set newname for datafile  36 to '/db02/oradata/TEST/a_txn_data12.dbf';

set newname for datafile  37 to '/db00/oradata/TEST/a_txn_ind10.dbf';

set newname for datafile  38 to '/db02/oradata/TEST/a_queue05.dbf';

set newname for datafile  39 to '/db01/oradata/TEST/a_nolog02.dbf';

set newname for datafile  40 to '/db01/oradata/TEST/a_media03.dbf';

set newname for datafile  41 to '/db02/oradata/TEST/a_int03.dbf';

set newname for datafile  42 to '/db02/oradata/TEST/a_archive02.dbf';

set newname for datafile  43 to '/db01/oradata/TEST/sysaux03.dbf';

set newname for datafile  44 to '/db02/oradata/TEST/a_txn_data13.dbf';

set newname for datafile  45 to '/db02/oradata/TEST/a_txn_data14.dbf';

set newname for datafile  46 to '/db02/oradata/TEST/a_txn_data15.dbf';

set newname for datafile  47 to '/db00/oradata/TEST/a_txn_ind11.dbf';

set newname for datafile  48 to '/db01/oradata/TEST/xxcustx02.dbf';

set newname for datafile  49 to '/db01/oradata/TEST/perfstat01.dbf';

set newname for datafile  50 to '/db02/oradata/TEST/a_queue06.dbf';

set newname for datafile  51 to '/db02/oradata/TEST/a_queue07.dbf';

set newname for datafile  52 to '/db01/oradata/TEST/sysaux04.dbf';

set newname for datafile  53 to '/db00/oradata/TEST/a_txn_ind12.dbf';

set newname for datafile  54 to '/db02/oradata/TEST/a_queue08.dbf';

set newname for datafile  55 to '/db01/oradata/TEST/sysaux05.dbf';

set newname for datafile  56 to '/db00/oradata/TEST/a_txn_ind13.dbf';

set newname for datafile  57 to '/db02/oradata/TEST/a_txn_data16.dbf';

set newname for datafile  58 to '/db02/oradata/TEST/a_txn_data17.dbf';

set newname for datafile  59 to '/db02/oradata/TEST/a_queue09.dbf';

set newname for datafile  60 to '/db01/oradata/TEST/sysaux06.dbf';

set newname for datafile  61 to '/db00/oradata/TEST/a_txn_ind14.dbf';

set newname for datafile  62 to '/db01/oradata/TEST/a_txn_data18.dbf';

set newname for datafile  63 to '/db02/oradata/TEST/a_txn_ind15.dbf';

set newname for datafile  64 to '/db02/oradata/TEST/a_txn_ind16.dbf';

set newname for datafile  65 to '/db02/oradata/TEST/a_queue10.dbf';

set newname for datafile  66 to '/db01/oradata/TEST/a_txn_data19.dbf';

set newname for datafile  67 to '/db01/oradata/TEST/a_txn_data20.dbf';

set newname for datafile  68 to '/db01/oradata/TEST/sysaux07.dbf';

set newname for datafile  69 to '/db01/oradata/TEST/a_txn_data21.dbf';

set newname for datafile  70 to '/db02/oradata/TEST/a_txn_data22.dbf';

set newname for datafile  71 to '/db02/oradata/TEST/a_txn_data23.dbf';

set newname for datafile  72 to '/db02/oradata/TEST/a_txn_data24.dbf';

set newname for datafile 288 to '/db01/oradata/TEST/system10.dbf';

set newname for datafile 295 to '/db01/oradata/TEST/system06.dbf';

set newname for datafile 314 to '/db01/oradata/TEST/portal01.dbf';

set newname for datafile 351 to '/db01/oradata/TEST/system07.dbf';

set newname for datafile 352 to '/db01/oradata/TEST/system09.dbf';

set newname for datafile 353 to '/db01/oradata/TEST/system08.dbf';

set newname for datafile 354 to '/db01/oradata/TEST/system11.dbf';

set newname for datafile 379 to '/db01/oradata/TEST/undo01.dbf';

set newname for datafile 392 to '/db01/oradata/TEST/a_txn_data01.dbf';

set newname for datafile 393 to '/db00/oradata/TEST/a_txn_ind01.dbf';

set newname for datafile 394 to '/db02/oradata/TEST/a_ref01.dbf';

set newname for datafile 395 to '/db02/oradata/TEST/a_int01.dbf';

set newname for datafile 396 to '/db02/oradata/TEST/a_summ01.dbf';

set newname for datafile 397 to '/db01/oradata/TEST/a_nolog01.dbf';

set newname for datafile 398 to '/db02/oradata/TEST/a_archive01.dbf';

set newname for datafile 399 to '/db02/oradata/TEST/a_queue01.dbf';

set newname for datafile 400 to '/db01/oradata/TEST/a_media01.dbf';

set newname for datafile 401 to '/db01/oradata/TEST/a_txn_data02.dbf';

set newname for datafile 402 to '/db01/oradata/TEST/a_txn_data03.dbf';

set newname for datafile 403 to '/db00/oradata/TEST/a_txn_ind02.dbf';

set newname for datafile 404 to '/db00/oradata/TEST/a_txn_ind03.dbf';

set newname for datafile 405 to '/db00/oradata/TEST/a_txn_ind04.dbf';

set newname for datafile 406 to '/db00/oradata/TEST/a_txn_ind05.dbf';

set newname for datafile 407 to '/db02/oradata/TEST/a_ref02.dbf';

set newname for tempfile 1 to '/db01/oradata/TEST/tempa01.dbf';

set newname for tempfile 2 to '/db01/oradata/TEST/tempb01.dbf';

set newname for tempfile 3 to '/db01/oradata/TEST/tempa02.dbf';

set newname for tempfile 4 to '/db01/oradata/TEST/tempb02.dbf';

set newname for tempfile 5 to '/db01/oradata/TEST/tempb03.dbf';

set newname for tempfile 7 to '/db01/oradata/TEST/tempa03.dbf';

DUPLICATE TARGET DATABASE TO 'TEST'

  SPFILE

      PARAMETER_VALUE_CONVERT ('PROD', 'TEST')

      SET LOG_FILE_NAME_CONVERT '/r01/oraredo/PROD','/r01/oraredo/TEST'

  SET DIAGNOSTICS_DEST '/u01/app/oracle/TEST/db/tech_st/11.1.0/admin/TEST_dbora01-t'

      SET LOCAL_LISTENER 'TEST_LOCAL'

  SET UTL_FILE_DIR '/usr/tmp','/u01/app/oracle/TEST/db/tech_st/11.1.0/appsutil/outbound/TEST_dbora01-t'

  UNTIL TIME '06-JUN-2020 22:40:00';

}

At the OS, I see:

[[email protected] solusoft]$ ls /db0?/oradata/TEST

/db00/oradata/TEST:

a_queue02.dbf  a_queue04.dbf     a_txn_data12.dbf  a_txn_data17.dbf  a_txn_ind05.dbf  a_txn_ind08.dbf

a_queue03.dbf  a_txn_data10.dbf  a_txn_data14.dbf  a_txn_ind02.dbf   a_txn_ind07.dbf  a_txn_ind11.dbf

/db01/oradata/TEST:

a_media03.dbf  archive  a_txn_data01.dbf  a_txn_data05.dbf  a_txn_data07.dbf  sysaux01.dbf  sysaux03.dbf  sysaux05.dbf  undo02.dbf

/db02/oradata/TEST:

a_queue02.dbf  a_queue04.dbf     a_txn_data12.dbf  a_txn_data17.dbf  a_txn_ind05.dbf  a_txn_ind08.dbf

a_queue03.dbf  a_txn_data10.dbf  a_txn_data14.dbf  a_txn_ind02.dbf   a_txn_ind07.dbf  a_txn_ind11.dbf

Hmmm, it's duplicating files in /db00 and /db02, but not those in /db01 (so far).

Thanks!

Hiroshi Komatsu

Hiroshi Komatsu

Hi.  Turned out that /db00 and /db02 were mounted on the same physical partition, and so, we get the same files in both filesystems.  I'll wait for the restore to finish, to correct this.

First time this happens to me.

Thanks!

Hiroshi Komatsu