ASM: Migrating Non-ASM Tablespace to ASM Tablespace

Introduction: Here we will see how to migrate a Non-ASM Tablespace to ASM Tablespace.


SQL> create tablespace test1
datafile
'/home2/oracle/ora10g/datafile/test1a.dbf'
size 20m autoextend on next 10m maxsize 100m,
'/home2/oracle/ora10g/datafile/test1b.dbf'
size 20m autoextend on next 10m maxsize 100m
extent management local;

Tablespace created.

SQL> select name from v$datafile where ts# in
2 (select ts# from v$tablespace where name='TEST1');

NAME
--------------------------------------------------------------------------------
/home2/oracle/ora10g/datafile/test1a.dbf
/home2/oracle/ora10g/datafile/test1b.dbf

SQL>

SQL> create user test1 identified by test1 default tablespace test1;

User created.

SQL> grant connect,resource to test1;

Grant succeeded.

SQL> connect test1/test1
Connected.
SQL> create table t1 as select * from all_tables;

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T1 TABLE

SQL>


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/odb/datafile/system.256.617411373
+DATA/odb/datafile/undotbs1.258.617411379
+DATA/odb/datafile/sysaux.257.617411377
+DATA/odb/datafile/users.259.617411381
+DG1/odb/datafile/t1.256.617493211
/home2/oracle/ora10g/datafile/test1a.dbf
/home2/oracle/ora10g/datafile/test1b.dbf

7 rows selected.

SQL>



-bash-2.05b$ rman nocatalog target /

RMAN> sql "alter tablespace test1 offline";
sql statement: alter tablespace test1 offline

RMAN> backup as copy tablespace test1 format '+DG1';
Starting backup at 20-MAR-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=148 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/home2/oracle/ora10g/datafile/test1a.dbf
output filename=+DG1/odb/datafile/test1.261.617749583 tag=TAG20070320T210622 recid=1 stamp=617749585
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/home2/oracle/ora10g/datafile/test1b.dbf
output filename=+DG1/odb/datafile/test1.262.617749589 tag=TAG20070320T210622 recid=2 stamp=617749592
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 20-MAR-07

RMAN> switch tablespace test1 to copy;
datafile 6 switched to datafile copy "+DG1/odb/datafile/test1.261.617749583"
datafile 7 switched to datafile copy "+DG1/odb/datafile/test1.262.617749589"

RMAN> sql "alter tablespace test1 online";
sql statement: alter tablespace test1 online

RMAN> exit
Recovery Manager complete.

-bash-2.05b$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 20 21:07:32 2007

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/odb/datafile/system.256.617411373
+DATA/odb/datafile/undotbs1.258.617411379
+DATA/odb/datafile/sysaux.257.617411377
+DATA/odb/datafile/users.259.617411381
+DG1/odb/datafile/t1.256.617493211
+DG1/odb/datafile/test1.261.617749583
+DG1/odb/datafile/test1.262.617749589

7 rows selected.

SQL> connect test1/test1
Connected.
SQL> select count(*) from t1;

COUNT(*)
----------
91

SQL>

Summary :
Non-ASM based Tablespace is taken offline.
Using RMAN BACKUP AS COPY Command an Image Copy of Tablespace TEST1 is created
Using SWITCH Command the Datafiles are Renamed.
ASM Tablespace is taken online

Note : SWITCH Command does not deletes the datafile from Operating System.
RMAN SWITCH Command is equivalent to "ALTER DATABASE RENAME FILE"

# - Posted by Senthil Rajendran - 3/20/07; 8:59:21 PM - Comment [0]

ASM : SPFILE Information and Maintenance

ASM Instance
SQL> show parameter instance_name

NAME TYPE VALUE
---------------- ----------- ---------------------------------------
instance_name string +ASM
SQL> show parameter spfile;

NAME TYPE VALUE
---------------- ----------- ----------------------------------------
spfile string /home2/oracle/ora10g/dbs/spfile+ASM.ora
SQL>

ASM Enabled Instance
SQL> show parameter instance_name;

NAME TYPE VALUE
---------------- ----------- ----------------------------------------
instance_name string ODB
SQL> show parameter spfile;

NAME TYPE VALUE
---------------- --------- ------------------------------------------
spfile string +DATA/odb/spfileodb.ora
SQL>

Making Change to an SPFILE of the ASM Enabled Instance

Step 1: Create PFILE from SPFILE;
Step 2: Make the Change to the PFILE
Step 3: Shutdown the Database
Step 4: Create SPFILE from PFILE;

Lab:
SQL> show parameter instance_name;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string ODB
SQL> create pfile='/tmp/change.ora' from spfile;

File created.

SQL>

>> PFILE '/tmp/change.ora' - Modified

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile='+DATA/odb/spfileodb.ora' from pfile='/tmp/change.ora';

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 71305460 bytes
Database Buffers 92274688 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>

Note : The SPFILE for an ASM instance cannot be stored in ASM disks groups.