Moving an Oracle Database from filesystem to ASM

Here i’l show you how to move existing database from filesystem to ASM(Automatic Storage Management) on same host.
environment

OS : Oracle Linux 6.5
DB : Oracle database 11g(11.2.0.3.0)

First of all we need to configure Grid infrastructure, with root user make next steps:
1) install ASM packages, which you can find on official oracle site, for example:
HERE
2) add disk for ASM and partition it
3) add separate Operating system user and groups for ASM, which is recommendation from oracle, for example: grid user and groups: asmadmin,asmdba,asmoper

 groupadd  -g 2100 asmadmin
 groupadd  -g 2200 asmdba
 groupadd  -g 2300 asmoper

 mkdir -p /u01/app/grid
 mkdir -p /home/grid
 useradd -s /bin/bash -d /home/grid  -g oinstall -G asmadmin,asmdba,asmoper grid

4) give permissions to grid user

chown -R grid:oinstall /u01/app/grid
chown -R grid:oinstall /home/grid
chmod -R 775 /u01/app/grid
passwd grid

5) configure ASM library driver

/etc/init.d/oracleasm configure
grid
asmadmin
y
y

6) Mark disk for ASM

/etc/init.d/oracleasm createdisk DATA01 /dev/sdc1

7) add grid user limits in /etc/security/limits.conf

 vi /etc/security/limits.conf

grid   soft   nofile    1024
grid   hard   nofile    65536
grid   soft   nproc    16384
grid   hard   nproc    16384
grid   soft   stack    10240
grid   hard   stack    32768

8) install and configure Grid infrastructure with grid user

Now we are ready for migrate database from filesystem to ASM.
with oracle OS user:
1) connect to database with rman and make copy for database to the ASM

rman target /

RMAN> backup as copy database format '+DG01'; --our diskgroup name

2) make copy for current controlfile to ASM

backup as copy current controlfile format '+DG01';

3) Restore your spfile to a location inside ASM with the following rman script.

run {
backup as backupset spfile;
restore spfile to '+DG01/spfiletestdb.ora';
}

4) create pfile in $ORACLE_HOME/dbs with parameter SPFILE=’+DG01/spfiletestdb.ora’ and delete spfile from $ORACLE_HOME/dbs

cd $ORACLE_HOME/dbs
vi inittestdb.ora
SPFILE='+DG01/spfiletestdb.ora'

rm -rf spfiletestdb.ora

5) next steps include restore controlfile, switch database to copy, swith tempfile, add new redo logs in ASM and drop olders from filesystem:

sqlplus / as sysdba
SQL> startup nomount;
SQL> alter system set control_files='+DG01' scope=spfile;
SQL> shutdown immediate;
SQL> startup nomount;
SQL> ! rman target /
RMAN> restore controlfile from '+dg01/testdb/controlfile/Backup.263.875890551';
RMAN> alter database mount;
RMAN> switch database to copy;
RMAN> recover database;
RMAN> run {
set newname for tempfile 1 to '+DG01';
switch tempfile 1;
}
RMAN> exit
SQL> alter database add logfile '+DG01' size 50M;
SQL> alter database add logfile '+DG01' size 50M;
SQL> alter database add logfile '+DG01' size 50M;
SQL> alter database open resetlogs;
SQL> alter database drop logfile '/u01/app/oracle/oradata/TESTDB/onlinelog/redo03.log';
SQL> alter database drop logfile '/u01/app/oracle/oradata/TESTDB/onlinelog/redo02.log';
SQL> alter database drop logfile '/u01/app/oracle/oradata/TESTDB/onlinelog/redo01.log';

if you encounter an ORA-01623 while trying to delete a member just switch logs and retry. To switch a log execute

alter system switch logfile;

That’s all our database completely migrated to the ASM.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s