Converting database at target

There was a valid response for yesterday's post by The Human Fly where he suggested that this method was not feasible for large databases because of the need for a staging area to hold the converted files.
Well according to Oracle disk space is cheap :-) and I know a lot of bugs that have been closed on this premise (where the workaround has meant additional disk space)

But seriously you can get around this

• You can take a backup of the source database to tape and then overwrite the datafile location (assuming you don’t need the source database anymore)

CONVERT DATABASE NEW DATABASE 'USYD'
transport script 'C:\temp\newtransportscript'
to platform 'Linux 64-bit for AMD'
db_file_name_convert 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\' 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\'
;

It is also possible to convert the database at the target to free up CPU on the source. You need to run the following on the source database

CONVERT DATABASE ON TARGET PLATFORM
CONVERT SCRIPT 'C:\temp\convertscript.rman'
TRANSPORT SCRIPT 'C:\temp\transport_at_target.sql'
new database 'USYD'
FORMAT 'C:\temp\target\%U';

This will create 2 scripts

• convertscript.rman which will have convert commands for each datafile of the database to be converted.


• transport_at_target.sql contains the syntax to create the controlfile and start the database at the target

LINUX64>>startup nomount pfile='/u01/oradata/USYD/initUSYD.ora'
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 2069712 bytes
Variable Size 113249072 bytes
Database Buffers 50331648 bytes
Redo Buffers 6316032 bytes

LINUX64>>CREATE CONTROLFILE REUSE SET DATABASE "USYD" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1
9 '/u01/oradata/USYD/ARCH_D-USYD_ID-768471617_S-212_T-1_A-600609988_00I2I4PR' SIZE 50M,
GROUP 2
10 11 '/u01/oradata/USYD/ARCH_D-USYD_ID-768471617_S-213_T-1_A-600609988_00I2I4PR' SIZE 50M,
GROUP 3
12 13 '/u01/oradata/USYD/ARCH_D-USYD_ID-768471617_S-211_T-1_A-600609988_00I2I4PR' SIZE 50M
14 DATAFILE
15 '/u01/oradata/USYD/SYSTEM01.DBF',
16 '/u01/oradata/USYD/UNDOTBS01.DBF',
17 '/u01/oradata/USYD/SYSAUX01.DBF',
18 '/u01/oradata/USYD/USERS01.DBF'
19 CHARACTER SET WE8MSWIN1252;

Control file created.

Now convert the files using the script convertscript.rman
eiger-> rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Wed Nov 22 14:32:33 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: USYD (DBID=768471617, not open)

RUN {

CONVERT DATAFILE '/u01/oradata/USYD/SYSTEM01.DBF'
FROM PLATFORM 'Microsoft Windows IA (32-bit)'
FORMAT '/u01/oradata/USYD/NEW/SYSTEM01.DBF';


CONVERT DATAFILE '/u01/oradata/USYD/SYSAUX01.DBF'
FROM PLATFORM 'Microsoft Windows IA (32-bit)'
FORMAT '/u01/oradata/USYD/NEW/SYSAUX01.DBF';


CONVERT DATAFILE '/u01/oradata/USYD/UNDOTBS01.DBF'
FROM PLATFORM 'Microsoft Windows IA (32-bit)'
FORMAT '/u01/oradata/USYD/NEW/UNDOTBS01.DBF';


CONVERT DATAFILE '/u01/oradata/USYD/USERS01.DBF'
FROM PLATFORM 'Microsoft Windows IA (32-bit)'
FORMAT '/u01/oradata/USYD/NEW/USERS01.DBF';

}


Starting backup at 22-NOV-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/oradata/USYD/SYSTEM01.DBF
converted datafile=/u01/oradata/USYD/NEW/SYSTEM01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:46
Finished backup at 22-NOV-06


Starting backup at 22-NOV-06
channel ORA_DISK_1: sid=39 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/oradata/USYD/SYSAUX01.DBF
converted datafile=/u01/oradata/USYD/NEW/SYSAUX01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:02:17
Finished backup at 22-NOV-06

Starting backup at 22-NOV-06
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/oradata/USYD/UNDOTBS01.DBF
converted datafile=/u01/oradata/USYD/NEW/UNDOTBS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:02:48
Finished backup at 22-NOV-06


Starting backup at 22-NOV-06
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/oradata/USYD/USERS01.DBF
converted datafile=/u01/oradata/USYD/NEW/USERS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Finished backup at 22-NOV-06

Unfortunately it appears that the file cannot be overwritten and hence
a staging area is required because when I tried to do that

RMAN> CONVERT DATAFILE '/u01/oradata/USYD/SYSTEM01.DBF'
FROM PLATFORM 'Microsoft Windows IA (32-bit)'
FORMAT '/u01/oradata/USYD/SYSTEM01.DBF';

Starting backup at 22-NOV-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/oradata/USYD/SYSTEM01.DBF
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/22/2006 14:35:00
ORA-19635: input and output filenames are identical: /u01/oradata/USYD/SYSTEM01.DBF

You then need to move the files from the staging area and resetlogs to start
the database

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

eiger-> cd USYD/N*
eiger-> ls
SYSAUX01.DBF SYSTEM01.DBF UNDOTBS01.DBF USERS01.DBF
eiger-> pwd
/u01/oradata/USYD/NEW
eiger-> mv * ../

SQL> startup mount pfile='/u01/oradata/USYD/initUSYD.ora'
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 2069712 bytes
Variable Size 113249072 bytes
Database Buffers 50331648 bytes
Redo Buffers 6316032 bytes
Database mounted.

SQL> recover database using BACKUP CONTROLFILE until cancel;
ORA-00279: change 6936865 generated at 11/22/2006 10:13:25 needed for thread 1
ORA-00289: suggestion :
/u00/app/oracle/product/10.2.0/dbs/arch1_219_600609988.dbf
ORA-00280: change 6936865 for thread 1 is in sequence #219


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.