When migrating databases using Data Pump Transportable Tablespaces one challenge is how to keep the Data Guard standby in sync when paths and/or datafile names are different between primary and standby (e.g. OMF/ASM).
Following, I describe an approach that lets you synchronize the physical standby during TTS migration in a few minutes, irrespective of database size.
I’m not going into the details of Transportable Tablespace migration itself. I assume you are familiar with the procedure and are using the scripts from KB144840 “M5 Cross Endian Platform Migration using Full Transportable Data Pump Export/Import and RMAN Incremental Backups”. The procedure is not bound to M5 scripts and you can apply it to your custom TTS procedure, it just makes the task of illustration a bit easier for me.
On the source side, there is nothing special. Just take the L0/L1/L1F backups as you usually would:
./dbmig_driver_m5.sh L0
On the target side, restore the L0/L1/L1F on both, primary and standby. For simplicity, I assume the backups and M5 command files are on a shared filesystem (NFS) that is available on the primary and standby server.
Make sure that primary and standby side do not overwrite each others logs as we need the logfiles from both sides later. During all RMAN restores, the standby CDB can be open read-only with redo apply running (synchronized).
Restore L0 target primary DB:
rman target / cmdfile=/share/restore_L0_mydb_260702105920.cmd
Extract from primary M5/RMAN logfile:
channel DISK1: restoring foreign file 408 to +DATAC3/MYDB_PRIM/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.3110.1237886187 channel DISK2: restoring foreign file 409 to +DATAC3/MYDB_PRIM/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.5914.1237927363
Restore the exact same L0 on target standby DB:
rman target / cmdfile=/share/restore_L0_mydb_260702105920.cmd
Extract from standby M5/RMAN logfile:
channel DISK1: restoring foreign file 408 to +DATAC5/MYDB_STBY/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.5731.1237885553 channel DISK2: restoring foreign file 409 to +DATAC5/MYDB_STBY/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.6551.1237925841
The resulting datafile names are totally different on primary and standby. In this case they even go to different ASM disk group names. What’s important to note is that we see the foreign datafile number (original file number from source database) – we will make use of this later to map the restored datafile names.
For the sake of brevity we skip any L1 backup/restore and jump directly to the L1F & metadata export.
./dbmig_driver_m5.sh L1F
Restore L1F target primary DB:
rman target / cmdfile=/share/restore_L1F_mydb_260704081504.cmd
Restore the exact same L1F on target standby DB:
rman target / cmdfile=/share/restore_L1F_mydb_260704081504.cmd
Before we do the Data Pump metadata import let’s prepare for keeping the physical standby synchronized.
As the metadata import only happens on the primary with the datafile names from the primary, the standby will not know these files and redo apply will fail if we don’t do anything about it.
That means we have to make sure, the datafile names on the standby match the OMF file names that were generated during restore on the standby. So, what we will want to do later is:
alter database rename file '+DATAC3/MYDB_PRIM/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.3110.1237886187' to '+DATAC5/MYDB_STBY/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.5731.1237885553'; alter database rename file '+DATAC3/MYDB_PRIM/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.5914.1237927363' to '+DATAC5/MYDB_STBY/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.6551.1237925841';
With the following commands we can build a script to rename all datafiles based on the M5 restore logfiles.
Step 1)
Generate a file# <> filename mapping from the primary
grep 'restoring foreign file .* to' /share/primary/log/restore_L*.log | awk -F' ' '{printf "%s:%s\n", $6, $8}' | sort --field-separator=: --numeric-sort --unique > /share/filemap_primary.txt
cat /share/filemap_primary.txt
408:+DATAC3/MYDB_PRIM/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.3110.1237886187
409:+DATAC3/MYDB_PRIM/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.5914.1237927363
Step 2)
Generate a file# <> filename mapping from the standby
grep 'restoring foreign file .* to' /share/standby/log/restore_L*.log | awk -F' ' '{printf "%s:%s\n", $6, $8}' | sort --field-separator=: --numeric-sort --unique > /share/filemap_standby.txt
cat /share/filemap_standby.txt
408:+DATAC5/MYDB_STBY/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.5731.1237885553
409:+DATAC5/MYDB_STBY/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.6551.1237925841
Step 3)
Generate “ALTER DATABASE RENAME FILE” by combining the primary and standby mapping files
paste --delimiters=: /share/filemap_primary.txt /share/filemap_standby.txt | awk -F':' '{printf "/* file id %s:%s */ alter database rename file '\''%s'\'' to '\''%s'\'';\n", $1, $3, $2, $4}' > /share/filemap-rename.sql
cat /share/filemap-rename.sql
/* file id 408:408 */ alter database rename file '+DATAC3/MYDB_PRIM/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.3110.1237886187' to '+DATAC5/MYDB_STBY/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.5731.1237885553';
/* file id 409:409 */ alter database rename file '+DATAC3/MYDB_PRIM/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.5914.1237927363' to '+DATAC5/MYDB_STBY/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.6551.1237925841';
Now we are ready to hanlde the standby when we import the metadata. Let’s do that on the primary.
./impdp.sh exp_mydb_260704081504.dmp log/restore_L0_mydb_260702105920.log log/restore_L1F_mydb_260704081504.log run Y
This is plugging-in all the tablespaces and make the datafiles part of the database. The metadata for tablespaces and datafiles is being replicated to the standby, just with the datafile names from the the primary.
As soon as the PLUGTS part of the Data Pump import is done, the datafiles are also known to the standby, just with the “wrong” name:
select t.ts#, t.name, f.file#, f.name from v$tablespace t join v$datafile f on (t.con_id = f.con_id and t.ts# = f.ts#) where t.name = 'TOOLS'; TS# NAME FILE# NAME --- ------ ----- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 12 TOOLS 408 +DATAC3/MYDB_PRIM/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.3110.1237886187 12 TOOLS 409 +DATAC3/MYDB_PRIM/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.5914.1237927363
Once all plugged-in tablespaces and datafiles are “visible” on the standby, we disable redo apply and rename the files:
edit database mydb_stby set state='APPLY-OFF'"
alter session set container = cdb$root; alter system set standby_file_management = MANUAL scope=both sid='*'; alter session set container = mypdb; @/share/filemap-rename.sql alter session set container = cdb$root; alter system set standby_file_management = AUTO scope=both sid='*';
Let’s check the file names on the standby again:
select t.ts#, t.name, f.file#, f.name from v$tablespace t join v$datafile f on (t.con_id = f.con_id and t.ts# = f.ts#) where t.name = 'TOOLS'; TS# NAME FILE# NAME --- ------ ----- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 12 TOOLS 408 +DATAC5/MYDB_STBY/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.5731.1237885553 12 TOOLS 409 +DATAC5/MYDB_STBY/3F7076425EC606DCE053AB192904E1A1/DATAFILE/tools.6551.1237925841
The datafiles on the standby now match the actual file in ASM. This means we can re-enable redo apply:
edit database mydb_stby set state='APPLY-ON'
Voilà, after a few minutes, your standby database will be synchronized and happily applying redo.
Notes:
- Relying on “grepping” logs to generate the rename SQLs seems a bit flaky. The problem is, up to 19.28, you don’t see the original file number anywhere on the target database. As far as I’m aware there is no other reliable way to generate a mapping for primary <> standby datafile names. And anyhow, M5 scripts also rely on logfiles to extract information, so I’m not the only weird one
- Starting with 19.29 Oracle changed the behaviour and now lists the restored datafiles as “foreign datafiles” with original file number. That could also serve as a source for mapping datafiles. But what if you have multiple TTS migrations in the same CDB going? Not sure if we can uniquely identify the right files
- I’ve also wanted to test if we can use RMAN “switch datafile to copy” to “rename” the datafiles on the standby…maybe I’ll give it a try in the next project.
- I’m interested in other approaches, please let me know in the comments how you handle this