Migrate tablespace and data files from the file system to ASM

Migrate tablespace and data files from the file system to ASM 1. the only two factors that need to be taken into account are the downtime and storage capacity for database migration, for whatever reason and need. These two factors often determine how you can migrate the database: if there is not enough downtime, you can choose hot migration, but requires enough storage space; if the storage capacity is insufficient, you can choose cold migration, but requires sufficient downtime. These two factors are mutually constrained. The example below www.2cto.com will conduct multiple migration experiments, from OS File System to ASM, or vice versa; from the table space, data File and database layer. 1.0. the database environment is Linux 5.7x64 + Oracle 10.2.0.5 x64 + 4 1 GB disks [oracle @ gtser1 gt10g] $ uname-aLinux gtser1 2.6.32-200.13.1.el5uek #1 SMP WedJul 27 21:02:33 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux [oracle @ gtser1 gt10g] $ cat/etc/issueOracle Linux Server release 5.7 Kernel \ r on an \ m SQL> select * from v $ version; BANNER ---------------------------------------------------------------- Oracle Database 10g I Se Edition Release10.2.0.5.0-64 biPL/SQL Release 10.2.0.5.0-ProductionCORE 10.2.0.5.0 ProductionTNS for Linux: Version 10.2.0.5.0-ProductionNLSRTL Version 10.2.0.5.0-Production 1.2.0. tablespace and data file migration-from File System to ASM [oracle @ gtser1 ~] $ Sqlplus "/as sysdba" SQL * Plus: Release 10.2.0.5.0-Production on MonFeb 25 15:09:59 2013 Copyright (c) 1982,201 0, Oracle. all Rights Reserved. connected to: Oracle Database 10g Enterprise Edition Release10.2.0.5.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and RealApplication Testing options SQL> archive log list; Database log mode Archive ModeAutomatic archival EnabledArchive des Tination/u01/oracle/10g/archOldest online log sequence 7 Next log sequence to archive 9 Current log sequence 9SQL> set linesize 150SQL> column ts # format 9SQL> column name format a50 -- use the tablespace GTLIONS as the Migration object, contains two data files: SQL> Select ts #, Name From v $ tablespace WhereName = 'gtlions' 2 Union All 3 Select file #, Name From v $ datafile Wherets # = 7; TS # NAME ------------------------------------------------------- 7 GTLI ONS 5/u01/oracle/10g/oradata/gt10g/gtlions01.dbf 6/u01/oracle/10g/oradata/gt10g/gtlions02.dbf -- use RMAN for migration [oracle @ gtser1 ~] $ Rman target/Recovery Manager: Release 10.2.0.5.0-Productionon Mon Feb 25 15:11:03 2013 Copyright (c) 1982,200 7, Oracle. all rights reserved. connected to target database: GT10G (DBID = 2268277830) -- list the current user information RMAN> report schema; using target database control file instead ofrecovery catalogReport of database schema List of Permanent Datafiles ================ = File Size (MB) tablespace RB segs Datafile Name ---- -------- ------------------ lifecycle 1 440 SYSTEM ***/u01/oracle/10g/oradata/gt10g/system01.dbf2 25 UNDOTBS1 ***/u01/oracle/10g /oradata/gt10g/undotbs1.dbf3 250 SYSAUX ***/u01/oracle/10g/oradata/gt10g/sysaux01.dbf4 5 USERS ***/u01/oracle/10g/oradata/gt10g /users01.dbf5 0 GTLIONS ***/u01/oracle/10g/oradata/gt10g/gtlions01.dbf6 0 GTLIONS ***/u01/oracle/10g/oradata/gt10g/upload List temporary Files =============================== File Size (MB) tablespace Maxsize (MB) tempfile Name ---- -------- temperature ----------- ------------------ 1 100 TEMP 100/u01/oracle/10g/oradata/gt10g/temp01.dbf4 100 GTLIONSTEMP 100/u01/oracle/10g/oradata/gt10g/large -- Take the tablespace offline RMAN> SQL 'alter tablespace gtlions offline '; SQL statement: alter tablespace gtlions offline -- start to back up two data files: RMAN> backup as copy datafile 5 format' + data01 '; Starting backup at 25-FEB-13allocated channel: ORA_DISK_1channel ORA_DISK_1: sid = 143 devtype = DISKchannel ORA_DISK_1: starting datafile copyinput datafile fno = 00005 name =/u01/oracle/10g/oradata/gt10g/fingerprint = + DATA01/gt10g/datafile/gtlions.258.808326729 tag = 117 stamp = 808326729 channel ORA_DISK_1: datafile copy complete, elapsed time: 00: 00: 07 Finished backup at 25-FEB-13 RMAN> backup as copy datafile 6 format '+ data01'; Starting backup at 25-FEB-13using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile fno = 00006 name =/u01/oracle/10g/oradata/gt10g/platform = + DATA01/gt10g/datafile/gtlions.265.808326737 tag = 118 stamp = 808326738 channel ORA_DISK_1: datafile copy complete, elapsed time: 00: 00: 03 Finished backup at 25-FEB-13 -- switch to primary copy RMAN> switch datafile 5 to copy; datafile 5 switched to datafile copy "+ DATA01/gt10g/datafile/gtlions.258.808326729" RMAN> switch datafile 6 to copy; datafile 6 switched to datafile copy "+ DATA01/gt10g/datafile/gtlions.265.808326737" -- Re-connect the GTLIONS tablespace RMAN> SQL 'alter tablespace gtlions online'; SQL statement: alter tablespace gtlions online -- check the migration result RMAN> report schema in RMAN; report of database schema List of Permanent Datafiles ============================ File Size (MB) tablespace RB segs Datafile Name ---- -------- ------------------ lifecycle 1 440 SYSTEM ***/u01/oracle/10g/oradata/gt10g/system01.dbf2 25 UNDOTBS1 ***/u01/oracle/10g /oradata/gt10g/undotbs1.dbf3 250 SYSAUX ***/u01/oracle/10g/oradata/gt10g/sysaux01.dbf4 5 USERS ***/u01/oracle/10g/oradata/gt10g /users01.dbf5 10 GTLIONS *** + DATA01/gt10g/datafile/quota 10 GTLIONS *** + DATA01/gt10g/datafile/gtlions.265.808326737 List of Temporary Files ======== ================= File Size (MB) tablespace Maxsize (MB) tempfile Name ---- -------- ------------------ bytes 1 100 TEMP 100/u01/oracle/10g/oradata/gt10g/temp01.dbf4 100 GTLIONSTEMP 100/u01/oracle/10g/oradata/gt10g/bytes -- check the migration result in SQL * plus SQL> Select ts #, name From v $ tablespace WhereName = 'gtlions' 2 Union All 3 Select file #, Name From v $ datafile Wherets # = 7; TS # NAME extensions 7 GTLIONS 5 + DATA01/gt10g/datafile/gtlions.258.808326729 6 + DATA01/gt10g/datafile/gtlions.265.808326737-The End-