Migrating Oracle Database to Automatic Storage management (ASM)


Disable Block change tracking:
bash-3.2$ sqlplus "/ as sysdba";
SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 17 09:52:13 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
Oracle Database Vault and Real Application Testing options
SQL> set linesize 1000
SQL> set pagesize 100
SQL> select * from v$block_change_tracking;
STATUS
----------
FILENAME
--------------------------------------------------------------------------------
 BYTES
----------
 DISABLED
If not disabled then, disable using this command.
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
 
Database altered.
Shutdown Database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 11.2.0.3.0 - Production
 With the Partitioning, OLAP and Data Mining options
Create pfile :
bash-3.2$ sqlplus "/ as sysdba";
SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 17 09:52:13 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create pfile from spfile;
 
 File created.
Then modify the below parameters: (Already created 2 ASM diskgroups DATA and FLASH)
*.control_files=(+DATA, +FLASH)
*.db_recovery_file_dest=+FLASH
*.db_recovery_file_dest_size=2147483648
*.db_create_file_dest=+DATA
*.db_create_online_log_dest_1=+FLASH
*.db_create_online_log_dest_2=+DATA
Create spfile :
SQl> create spfile from pfile;
File created.
Copy Database to ASM diskgroups using rman:
bash-3.2$ rman
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Sep 17 09:57:05 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target
 
connected to target database (not started)
RMAN> startup nomount
 
Oracle instance started
 
Total System Global Area 167772160 bytes
 
Fixed Size 1218316 bytes
Variable Size 83888372 bytes
Database Buffers 79691776 bytes
Redo Buffers 2973696 bytes
RMAN> restore controlfile from '/home/oracle/oradata/anar11g/control01.ctl';
Starting restore at 17-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
 
channel ORA_DISK_1: copied control file copy
output filename=+DATA/anar11g/controlfile/backup.256.596369129
output filename=+FLASH/anar11g/controlfile/backup.256.596369131
Finished restore at 17-SEP-15
RMAN> startup mount
 
database is already started
database mounted
released channel: ORA_DISK_1
RMAN> configure device type disk parallelism 4;
 
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';
 
Starting backup at 17-SEP-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=151 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=150 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=149 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/home/oracle/oradata/anar11g/system01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile fno=00003 name=/home/oracle/oradata/anar11g/sysaux01.dbf
channel ORA_DISK_3: starting datafile copy
input datafile fno=00002 name=/home/oracle/oradata/anar11g/undotbs01.dbf
channel ORA_DISK_4: starting datafile copy
input datafile fno=00004 name=/home/oracle/oradata/anar11g/users01.dbf
output filename=+DATA/anar11g/datafile/undotbs1.259.596369341 tag=TAG20060721T100858 recid=2 stamp=596369352
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:16
channel ORA_DISK_3: starting datafile copy
copying current control file
output filename=+DATA/anar11g/datafile/users.260.596369341 tag=TAG20060721T100858 recid=1 stamp=596369350
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:20
channel ORA_DISK_4: starting full datafile backupset
channel ORA_DISK_4: specifying datafile(s) in backupset
output filename=+DATA/anar11g/controlfile/backup.261.596369361 tag=TAG20060721T100858 recid=3 stamp=596369364
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:06
including current SPFILE in backupset
channel ORA_DISK_4: starting piece 1 at 17-SEP-15
channel ORA_DISK_4: finished piece 1 at 17-SEP-15
piece handle=+DATA/anar11g/backupset/2006_07_21/nnsnf0_tag20060721t100858_0.262.596369369 tag=TAG20060721T100858 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:10
output filename=+DATA/anar11g/datafile/sysaux.258.596369341 tag=TAG20060721T100858 recid=4 stamp=596369390
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:01:05
output filename=+DATA/anar11g/datafile/system.257.596369339 tag=TAG20060721T100858 recid=5 stamp=596369414
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:21
Finished backup at 17-SEP-15
 
RMAN> SWITCH DATABASE TO COPY;
 
datafile 1 switched to datafile copy "+DATA/anar11g/datafile/system.257.596369339"
datafile 2 switched to datafile copy "+DATA/anar11g/datafile/undotbs1.259.596369341"
datafile 3 switched to datafile copy "+DATA/anar11g/datafile/sysaux.258.596369341"
datafile 4 switched to datafile copy "+DATA/anar11g/datafile/users.260.596369341"
RMAN> alter database open;
 
database opened
 
RMAN> exit
Recovery Manager complete.
Migrate tempfile to ASM:
bash-3.2$ sqlplus "/ as sysdba";
SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 17 09:59:35 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
Oracle Database Vault and Real Application Testing options
SQL> select name, bytes from v$tempfile;
 
NAME
--------------------------------------------------------------------------------
 BYTES
----------
/home/oracle/oradata/anar11g/temp01.dbf
 20971520
QL> create temporary tablespace temp1 tempfile SIZE 100M extent management local uniform size 1M;
 
Tablespace created.
 
SQL> alter database default temporary tablespace temp1;
 
Database altered.
 
SQL> drop tablespace temp including contents;
 
Tablespace dropped.
 
SQL> create temporary tablespace temp tempfile SIZE 100M extent management local uniform size 1M;
 
Tablespace created.
 
SQL> alter database default temporary tablespace temp;
 
Database altered.
 
SQL> drop tablespace temp1 including contents;
 
Tablespace dropped.
 
SQL> select name from v$tempfile;
 
NAME
--------------------------------------------------------------------------------
 +DATA/anar11g/tempfile/temp.264.596370217
Migrate and drop the old Online Redo Logs to ASM:
SQL> select member from v$logfile;
 
MEMBER
--------------------------------------------------------------------------------
+FLASH/anar11g/onlinelog/group_3.259.596373619
+FLASH/anar11g/onlinelog/group_2.258.596373615
+FLASH/anar11g/onlinelog/group_1.261.596373613
+FLASH/anar11g/onlinelog/group_4.257.596373293
+FLASH/anar11g/onlinelog/group_5.260.596373609
 
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
 With the Partitioning, OLAP and Data Mining options
DELETE THE OLD DATAFILES USING RMAN.
RMAN> run {
2> DELETE COPY OF DATABASE;
3> }
 
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=134 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=151 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=153 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=138 devtype=DISK
 
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
6 1 A 17-SEP-15 461254 17-SEP-15 /home/oracle/oradata/anar11g/system01.dbf
7 2 A 17-SEP-15 461254 17-SEP-15 /home/oracle/oradata/anar11g/undotbs01.dbf
8 3 A 17-SEP-15 461254 17-SEP-15 /home/oracle/oradata/anar11g/sysaux01.dbf
9 4 A 17-SEP-15 461254 17-SEP-15 /home/oracle/oradata/anar11g/users01.dbf
 
Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy filename=/home/oracle/oradata/anar11g/system01.dbf recid=6 stamp=596369439
deleted datafile copy
datafile copy filename=/home/oracle/oradata/anar11g/undotbs01.dbf recid=7 stamp=596369439
deleted datafile copy
datafile copy filename=/home/oracle/oradata/anar11g/sysaux01.dbf recid=8 stamp=596369440
deleted datafile copy
datafile copy filename=/home/oracle/oradata/anar11g/users01.dbf recid=9 stamp=596369440
Deleted 4 objects
 
RMAN> exit
 
Recovery Manager complete.
REMOVE THE OLD ONLINE REDO LOGS FILES :
bash-3.2$ rm /home/oracle/oradata/anar11g/redo*.log
Enable the block change tracking:
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
 
Database altered.

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

%d bloggers like this: