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
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....
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
Post a Comment