Earlier this month I posted a, er, post on migrating a Standby database from UFS to ASM. This week I had to migrate a 'normal' database to ASM from UFS. This was even easier than the standby, and here's how to do it.
Note, if the database is large, you should set block change tracking on. You can also perform a full backup the day before which will minimise the database down time.
Make a Level 0 backup of the database
rman
connect target /
run
{
allocate channeL dev1 device type disk;
allocate channel dev2 device type disk;
allocate channel dev3 device type disk;
allocate channel dev4 device type disk;
allocate channel dev5 device type disk;
allocate channel dev6 device type disk;
backup as copy incremental level 0
database format '+UATDB_DATA01' tag ‘ORA_ASM_MIGRATION_UATDB';
}
This will copy the database datafiles to the ASM disks.
If the database is large, nearer the migration time backup the database with a level 1:
connect target /
###
### If a new datafile is added after the last backup, you have run it again or the ASM switch will fail.
###
### select name, to_char(CREATION_TIME,'dd/mm hh24:mi' ) as CT from v$datafile where CREATION_TIME > sysdate - 1;
###
run
{
allocate channeL dev1 device type disk;
allocate channel dev2 device type disk;
allocate channel dev3 device type disk;
allocate channel dev4 device type disk;
backup incremental level 1 for recover of copy datafilecopy FORMAT '+UATDB_DATA01'
with tag 'ORA_ASM_MIGRATION_UATDB' database FORMAT '/apps/oracle/admin/UATDB/RMAN_BACK_DISK/%d.data.%U';
recover copy of database with tag 'ORA_ASM_MIGRATION_UATDB';
}
This will update any changed files onto the ASM disks.
Stop the database.
Copy some files as a backup just in case
cp -p /oracle/product/10.2.0/db_2/dbs/spfileUATDB.ora /apps/oracle/admin/UATDB/SAVED_FILES/
cp -p /oracle/eu_data_1/data/UATDB/UATDB_ctrl_1.ctl /apps/oracle/admin/UATDB/SAVED_FILES/
cp -p /oracle/ora_redo/data/UATDB/redo_*.log /apps/oracle/admin/UATDB/SAVED_FILES/
Restart the database again in nomount mode
Set the create destinations
NOLOG> ALTER SYSTEM SET DB_CREATE_FILE_DEST='+UATDB_DATA01' SID='*';
System altered.
NOLOG> ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1 = '+UATDB_DATA01' SID='*';
System altered.
Restart the database in nomount mode
NOLOG> startup force nomount
Set the controlfile destination
NOLOG> ALTER SYSTEM SET CONTROL_FILES='+UATDB_DATA01' SCOPE=SPFILE SID='*';
System altered.
NOLOG> exit;
Now restore the controlfile to ASM:
RMAN> connect target /
connected to target database: UATDB (not mounted)
RMAN> RESTORE CONTROLFILE FROM '/oracle/eu_data_1/data/UATDB/UATDB_ctrl_1.ctl';
Starting restore at 19-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=254 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=/oracle/eu_data_1/data/UATDB/UATDB_ctrl_1.ctl
Finished restore at 19-DEC-13
This will put the controlfile onto the ASM disk
Mount the database and run the command to switch the datafiles to use the ASM copies
RMAN> ALTER DATABASE MOUNT;
database mounted
released channel: ORA_DISK_1
RMAN> SWITCH DATABASE TO COPY;
datafile 41 switched to datafile copy "+UATDB_DATA01/UATDB_base/datafile/cis_idx03.341.834494307"
datafile 42 switched to datafile copy "+UATDB_DATA01/UATDB_base/datafile/cis_idx02.292.834493627"
datafile 43 switched to datafile copy "+UATDB_DATA01/UATDB_base/datafile/cis_tab03.419.834495445"
datafile 44 switched to datafile copy "+UATDB_DATA01/UATDB_base/datafile/cis_idx03.342.834494311"
datafile 45 switched to datafile copy "+UATDB_DATA01/UATDB_base/datafile/cis_idx02.293.834493627"
datafile 46 switched to datafile copy "+UATDB_DATA01/UATDB_base/datafile/temp.415.834495405"
datafile 47 switched to datafile copy "+UATDB_DATA01/UATDB_base/datafile/temp.416.834495429"
datafile 48 switched to datafile copy "+UATDB_DATA01/UATDB_base/datafile/cis_tab03.420.834495445"
datafile 49 switched to datafile copy "+UATDB_DATA01/UATDB_base/datafile/cis_idx02.267.834492661"
datafile 50 switched to datafile copy "+UATDB_DATA01/UATDB_base/datafile/rpt_tab01.278.834493253"
datafile 51 switched to datafile copy "+UATDB_DATA01/UATDB_base/datafile/cis_tab02.268.834492687"
datafile 52 switched to datafile copy "+UATDB_DATA01/UATDB_base/datafile/cis_aud_tab01.528.834496019"
datafile 53 switched to datafile copy "+UATDB_DATA01/UATDB_base/datafile/cis_tab03.421.834495461"
datafile 54 switched to datafile copy "+UATDB_DATA01/UATDB_base/datafile/cis_tab02.307.834493799"
Once all these have been done, recover the database
RMAN> RUN
2> {
3> ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
4> ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
5> ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
6> ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
7> RECOVER DATABASE;
8> }
Finished recover at 19-DEC-13
released channel: dev1
released channel: dev2
released channel: dev3
released channel: dev4
RMAN> exit
Open the database
SQL> alter database open;
You also need to change the tempfiles
select NAME from v$tempfile;
ALTER DATABASE TEMPFILE '/oracle/129/data/UATDB/temp_01.dbf' DROP;
ALTER DATABASE TEMPFILE '/oracle/129/data/UATDB/temp_02.dbf' DROP;
ALTER DATABASE TEMPFILE '/oracle/129/data/UATDB/temp_03.dbf' DROP;
ALTER DATABASE TEMPFILE '/oracle/129/data/UATDB/temp_04.dbf' DROP;
ALTER TABLESPACE TEMP ADD TEMPFILE ;
ALTER TABLESPACE TEMP ADD TEMPFILE ;
ALTER TABLESPACE TEMP ADD TEMPFILE ;
Switch the logfiles a few times, then move the online redo log files with the following code
alter system switch logfile; - do it 3 or 4 times
Run this
SET SERVEROUTPUT ON;
DECLARE
CURSOR rlc IS
SELECT GROUP# GRP, THREAD# THR, BYTES, 'NO' SRL
FROM V$LOG
UNION
SELECT GROUP# GRP, THREAD# THR, BYTES, 'YES' SRL
FROM V$STANDBY_LOG
ORDER BY 1;
stmt VARCHAR2(2048);
BEGIN
FOR rlcRec IN rlc LOOP
IF (rlcRec.srl = 'YES') THEN
stmt := 'ALTER DATABASE ADD STANDBY LOGFILE THREAD ' ||
rlcRec.thr || ' SIZE ' || rlcRec.bytes;
EXECUTE IMMEDIATE stmt;
stmt := 'ALTER DATABASE DROP STANDBY LOGFILE GROUP ' || rlcRec.grp;
EXECUTE IMMEDIATE stmt;
ELSE
stmt := 'ALTER DATABASE ADD LOGFILE THREAD ' ||
rlcRec.thr || ' SIZE ' || rlcRec.bytes;
EXECUTE IMMEDIATE stmt;
BEGIN
stmt := 'ALTER DATABASE DROP LOGFILE GROUP ' || rlcRec.grp;
DBMS_OUTPUT.PUT_LINE(stmt);
EXECUTE IMMEDIATE stmt;
EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';
EXECUTE IMMEDIATE 'ALTER SYSTEM CHECKPOINT GLOBAL';
EXECUTE IMMEDIATE stmt;
END;
END IF;
END LOOP;
END;
/
This should move the log files
sys.UATDB> select member from v$logfile;
MEMBER
----------------------------------------------------------------------------------------------------
+UATDB_DATA01/UATDB_base/onlinelog/group_3.544.834579079
+UATDB_DATA01/UATDB_base/onlinelog/group_2.543.834579073
+UATDB_DATA01/UATDB_base/onlinelog/group_1.542.834579067
+UATDB_DATA01/UATDB_base/onlinelog/group_5.541.834579063
Stop and restart the database, and check all the files are now on ASM disk.
+ERCISU_DATA01/ercisu_base/datafile/rpt_idx01.538.834496023
+ERCISU_DATA01/ercisu_base/datafile/cis_idx01.525.834496015
+ERCISU_DATA01/ercisu_base/datafile/cis_tab01.509.834495999
+ERCISU_DATA01/ercisu_base/datafile/cis_tab03.256.834491477
+ERCISU_DATA01/ercisu_base/datafile/cis_idx03.261.834491477
+UATDB_DATA01/UATDB_base/onlinelog/group_3.544.834579079
+UATDB_DATA01/UATDB_base/onlinelog/group_2.543.834579073
+UATDB_DATA01/UATDB_base/onlinelog/group_1.542.834579067
+UATDB_DATA01/UATDB_base/onlinelog/group_5.541.834579063
+UATDB_DATA01/UATDB_base/controlfile/current.545.834579799
Maybe wait a day or two (and check all the last used dates) before deleting the old UFS files.
Taaa Daaaa.
No comments:
Post a Comment