Migrating a big old 10g database to a new home



I recently blogged about migrating a database into ASM, this week I've just had to migrate a normal filesystem based database from one disk array to another leaving it as normal filesystems.

The system is 10g and will be removed within a year (although that line has been used every year for about 5 years now).

However  the old array is being removed and being replaced by a nice shiny new one - so how best to migrate?

Well there is more than one way to attack this but i wanted to use the opportunity to simplify what we had. We currently have 20 filesystems and files are all over the place in most other database we support we've embraced OMF (something i laughed at when it first came out....) and follow the 2 filesystems approach - one for db files and one for recovery based files (with critical files existing in both places - control/redo etc). This doesn't give the 100% best performance you could maybe get but it's good enough for us and the time we save by keeping things simple outweighs any small loss in performance.

OK enough waffle - here is what i did

The current setup takes up ~1TB of space - as can be seen from this pair of queries

SQL> select sum(bytes)/(1024*1024*1024) from v$datafile;

SUM(BYTES)/(1024*1024*1024)
---------------------------
                 841.420288


SQL>  select sum(bytes)/(1024*1024*1024) from v$tempfile;

SUM(BYTES)/(1024*1024*1024)
---------------------------
                         88

The new array i have mounted as just 2 filesystems and i set one of these to db_create_file_dest and the other to db_recovery_file_dest.

These are

/oracle/OLD10GDB/oradata
/oracle/OLD10GDB/recovery_area


To transfer the database to those 2 new locations i use the following rman/shell combo (I only do this so i can easily nohup it and avoid a dodgy network connection killing the process) - you only need rman to accomplish this

These are the scripts

1) is the rman command file
# cat 3par.rcv

run {
allocate channel disk1 type disk;
allocate channel disk2 type disk;
allocate channel disk3 type disk;
allocate channel disk4 type disk;
backup as copy database to destination '/oracle/OLD10GDB/oradata';
}

2) is the shell script that calls that
#cat 3parmigrate.ksh

#!/usr/bin/ksh
export ORACLE_HOME=/app/oracle/product/10.2.0.4.DB
export ORACLE_SID=OLD10GDB
export LD_LIBRARY_PATH=/app/oracle/product/10.2.0.4.DB/lib
export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"
Timestamp=$(date +%Y%m%d_%H%M)
RMANCMD=""
while read LINE; do RMANCMD="$RMANCMD\n$LINE"; done < /home/oracle/3par.rcv
print $RMANCMD|$ORACLE_HOME/bin/rman target / log=/tsm/OLD10GDB/log/migrate_$Timestamp.log


I then start the process like this

 nohup ./3parmigrate.ksh &

A quick look at the log reveals it is away and running

 tail -100f migrate_20150427_1418.log

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Apr 27 14:18:32 2015

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

connected to target database: OLD10GDB (DBID=4008918326)

RMAN>
RMAN> 2> 3> 4> 5> 6> 7>
using target database control file instead of recovery catalog
allocated channel: disk1
channel disk1: sid=518 devtype=DISK

allocated channel: disk2
channel disk2: sid=527 devtype=DISK

allocated channel: disk3
channel disk3: sid=478 devtype=DISK

allocated channel: disk4
channel disk4: sid=490 devtype=DISK

Starting backup at 27-APR-2015 14:18:38
channel disk1: starting datafile copy
input datafile fno=00145 name=/data/oradata/OLD10GDB_11/data001/transform_data_small_03.dbf
channel disk2: starting datafile copy
input datafile fno=00146 name=/data/oradata/OLD10GDB_12/data001/transform_data_small_04.dbf
channel disk3: starting datafile copy
input datafile fno=00191 name=/data/oradata/OLD10GDB_10/users_05.dbf
channel disk4: starting datafile copy
input datafile fno=00196 name=/data/oradata/OLD10GDB_10/owbrep_data_04.dbf
output filename=/oracle/OLD10GDB/oradata/OLD10GDB/datafile/o1_mf_users_bmwfsmg2_.dbf tag=TAG20150427T141841 recid=107 stamp=878135
119

Checking back later and the script has finished fine - see tail of the log here

Starting Control File and SPFILE Autobackup at 27-APR-2015 15:50:48
piece handle=/app/oracle/admin/OLD10GDB/control/c-4008918326-20150427-09 comment=NONE
Finished Control File and SPFILE Autobackup at 27-APR-2015 15:50:53
released channel: disk1
released channel: disk2
released channel: disk3
released channel: disk4

RMAN>

So what have i done here? I've create image copies of all the database files and told rman to put them in the the destination /oracle/OLD10GDB/oradata - this is the same place i have specified for db_create_file_dest. Note the process creates all the subdirectories automatically and makes everything in omf format for me.

It copied the 850GB of data in about 90 minutes using the 4 disk channels i got rman to create - quite good i thought for quite an ageing server....

Now all i have at the moment is a second copy of all the files - which are not even consistent with each other they were all copied at various points during that 90 minutes so how do i now swap over to them?

Well before i do that a quick caveat - make sure your database is in force logging mode or you know that no nologging activity can have happened since you started the image copy. Otherwise you can end up with inconsistent files (and not just this kind of migration any recovery will have issues) - this may be OK if you designed it in and you want to truncate some tables etc but generally i would always say use force logging unless you know what you are doing.

In my case i know nologging is used in this db but not during the window i am working - to double check i can run

RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------

RMAN>

This shows no datafiles are changed since the last backup.

OK - enough of the caveat - now to do the switch we follow these steps

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 3305111552 bytes
Fixed Size                  2087648 bytes
Variable Size            2365588768 bytes
Database Buffers          922746880 bytes
Redo Buffers               14688256 bytes
Database mounted.
SQL>

rman target=/

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Apr 27 20:12:24 2015

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

connected to target database: OLD10GDB (DBID=4008918326, not open)

RMAN> switch database to copy;

using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "/oracle/OLD10GDB/oradata/OLD10GDB/datafile/o1_mf_system_bmwltpd9_.dbf"
datafile 2 switched to datafile copy "/oracle/OLD10GDB/oradata/OLD10GDB/datafile/o1_mf_undotbs1_bmwkx90w_.dbf"
datafile 3 switched to datafile copy "/oracle/OLD10GDB/oradata/OLD10GDB/datafile/o1_mf_sysaux_bmwlswfp_.dbf"
--delete some output for brevity
datafile 199 switched to datafile copy "/oracle/OLD10GDB/oradata/OLD10GDB/datafile/o1_mf_zainet_c_bmwm217s_.dbf"
datafile 200 switched to datafile copy "/oracle/OLD10GDB/oradata/OLD10GDB/datafile/o1_mf_streams_bmwgwd7c_.dbf"


So that switched OK - now we need to get all the files in sync and up to date

RMAN> recover database;

Starting recover at 27-APR-15
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=538 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.3.3.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=536 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:01:19

Finished recover at 27-APR-15

All good now we open the db

RMAN> alter database open;

database opened

RMAN>

So far so good, datafiles now - now on to the other file types.

Tempfiles are the easiest so I'll gloss over them - just run

 alter tablespace temp add tempfile;
alter tablespace temp drop tempfile 'old filesystem name';

as easy as that - no messing around and no outage.

Logfiles are a little more fiddly - we add new ones with the same size as the old ones and then drop the originals - but we have to make sure they are not in use or needed any more - see example below

SQL> alter database add logfile group 4 size 1G;

Database altered.

SQL> alter database add logfile group 5 size 1G;

Database altered.

SQL> c/5/6
  1* alter database add logfile group 6 size 1G
SQL> /

Database altered.


SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/data/oradata/OLD10GDB_03/data001/redo_1_1.log
/data/oradata/OLD10GDB_06/data001/redo_1_2.log
/data/oradata/OLD10GDB_04/data001/redo_2_1.log
/data/oradata/OLD10GDB_07/data001/redo_2_2.log
/data/oradata/OLD10GDB_05/data001/redo_3_1.log
/data/oradata/OLD10GDB_08/data001/redo_3_2.log
/oracle/OLD10GDB/oradata/OLD10GDB/onlinelog/o1_mf_4_bmx39vl5_.log
/oracle/OLD10GDB/recovery_area/OLD10GDB/onlinelog/o1_mf_4_bmx3b040_.log
/oracle/OLD10GDB/oradata/OLD10GDB/onlinelog/o1_mf_5_bmx3dbk0_.log
/oracle/OLD10GDB/recovery_area/OLD10GDB/onlinelog/o1_mf_5_bmx3dgx8_.log
/oracle/OLD10GDB/oradata/OLD10GDB/onlinelog/o1_mf_6_bmx3ds47_.log

LMEMBER
--------------------------------------------------------------------------------
/oracle/OLD10GDB/recovery_area/OLD10GDB/onlinelog/o1_mf_6_bmx3dxjx_.log

12 rows selected.

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance OLD10GDB (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/data/oradata/OLD10GDB_03/data001/redo_1_1.log'
ORA-00312: online log 1 thread 1: '/data/oradata/OLD10GDB_06/data001/redo_1_2.log'


SQL>  alter database drop logfile group 2;

Database altered.

SQL>  alter database drop logfile group 3;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter database drop logfile group 1;

Database altered.

So now all tidied up

Now for the controlfiles which needs another outage - we do a quick comment out of the control files from the init file and then make oracle recreate the new ones in OMF locations based on a restore of the current file in the old path

SQL> create pfile from spfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

comment out control_files at this point in the pfile

SQL> startup nomount  pfile=initOLD10GDB.ora
ORACLE instance started.

Total System Global Area 3305111552 bytes
Fixed Size                  2087648 bytes
Variable Size            2365588768 bytes
Database Buffers          922746880 bytes
Redo Buffers               14688256 bytes

SQL> create spfile from pfile; -- important to create new version of spfile with blank controlfile parameter

File created.

SQL> shutdown;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount




RMAN>  restore controlfile from '/data/oradata/OLD10GDB_01/data001/control01.ctl';

Starting restore at 27-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=538 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=/oracle/OLD10GDB/oradata/OLD10GDB/controlfile/o1_mf_bmx3ztoo_.ctl
output filename=/oracle/OLD10GDB/recovery_area/OLD10GDB/controlfile/o1_mf_bmx3zv44_.ctl
Finished restore at 27-APR-15

Now we switch the archive area to the new location


SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

System altered.

And that should be everything done.

A quick check with lsof looking for the old VG names in the output


oracle:server:OLD10GDB> lsof |grep lv_DH
oracle    1462476  oracle   17u  VREG               26,2           74514944      4 /data/oradata/OLD10GDB_02 (/dev/lv_DH_d02)

And we missed something - what is that

oracle:server:OLD10GDB> ps -ef |grep 1462476
  oracle  979026 2388076   0 20:40:08  pts/1  0:00 grep 1462476
  oracle 1462476       1   0 20:38:31      -  0:00 ora_ctwr_OLD10GDB

Ah - block change tracking - lets quickly fix that

SQL> alter database disable block change tracking;

Database altered.

SQL> alter database enable block change tracking;

Database altered.

Thats it switched ok to OMF location - a quick double check with lsof and everything is migrated.

The actual outages were very quick and the process was pretty simple. In 12c i think everything but the controlfiles would now be online - maybe controlfiles can be done online in the next release......

Comments