Lokeshaggarwal's DBA

Live the life you love. Love the life you live

Non ASM to ASM Migration


First we create appropirate partition:

[root@localhost arch]# fdisk /dev/sda

The number of cylinders for this disk is set to 19457.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)

Command (m for help): m
Command action
a   toggle a bootable flag
b   edit bsd disklabel
c   toggle the dos compatibility flag
d   delete a partition
l   list known partition types
m   print this menu
n   add a new partition
o   create a new empty DOS partition table
p   print the partition table
q   quit without saving changes
s   create a new empty Sun disklabel
t   change a partition’s system id
u   change display/entry units
v   verify the partition table
w   write table to disk and exit
x   extra functionality (experts only)

Command (m for help): p

Disk /dev/sda: 160.0 GB, 160040803840 bytes
255 heads, 63 sectors/track, 19457 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          13      104391   83  Linux
/dev/sda2              14       10212    81923467+  83  Linux
/dev/sda3           10213       10849     5116702+  8e  Linux LVM
/dev/sda4           10850       19457    69143760    5  Extended
/dev/sda5           10850       11486     5116671   82  Linux swap / Solaris
/dev/sda6           11487       11996     4096543+  83  Linux
/dev/sda7           11997       12060      514048+  83  Linux
/dev/sda8           12061       12650     4739143+  83  Linux
/dev/sda9           12651       12900     2008093+  83  Linux

Command (m for help): m
Command action
a   toggle a bootable flag
b   edit bsd disklabel
c   toggle the dos compatibility flag
d   delete a partition
l   list known partition types
m   print this menu
n   add a new partition
o   create a new empty DOS partition table
p   print the partition table
q   quit without saving changes
s   create a new empty Sun disklabel
t   change a partition’s system id
u   change display/entry units
v   verify the partition table
w   write table to disk and exit
x   extra functionality (experts only)

Command (m for help): n
First cylinder (12901-19457, default 12901):
Using default value 12901
Last cylinder or +size or +sizeM or +sizeK (12901-19457, default 19457): 13600

Command (m for help): p

Disk /dev/sda: 160.0 GB, 160040803840 bytes
255 heads, 63 sectors/track, 19457 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          13      104391   83  Linux
/dev/sda2              14       10212    81923467+  83  Linux
/dev/sda3           10213       10849     5116702+  8e  Linux LVM
/dev/sda4           10850       19457    69143760    5  Extended
/dev/sda5           10850       11486     5116671   82  Linux swap / Solaris
/dev/sda6           11487       11996     4096543+  83  Linux
/dev/sda7           11997       12060      514048+  83  Linux
/dev/sda8           12061       12650     4739143+  83  Linux
/dev/sda9           12651       12900     2008093+  83  Linux
/dev/sda10          12901       13600     5622718+  83  Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.

WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table.
The new table will be used at the next reboot.
Syncing disks.
[root@localhost arch]# partprobe
Warning: Unable to open /dev/fd0 read-write (Read-only file system).  /dev/fd0 has been opened read-only.

Now lets try to create asm disk:

[root@localhost arch]# /etc/init.d/oracleasm createdisk
Action “createdisk” requires two arguments
[root@localhost arch]# /etc/init.d/oracleasm createdisk VOL3
Action “createdisk” requires two arguments
[root@localhost arch]# /etc/init.d/oracleasm createdisk
Action “createdisk” requires two arguments
[root@localhost arch]# /etc/init.d/oracleasm createdisk
Action “createdisk” requires two arguments
[root@localhost arch]# /etc/init.d/oracleasm createdisk VOL3 /dev/sda10
Marking disk “VOL3” as an ASM disk:                        [  OK  ]
[root@localhost arch]# /etc/init.d/oracleasm listdisk
Usage: /etc/init.d/oracleasm {start|stop|restart|enable|disable|configure|createdisk|deletedisk|querydisk|listdisks|scandisks|status}
[root@localhost arch]# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
[root@localhost arch]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@localhost arch]# su – oracle
-bash-3.2$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Jun 7 23:41:26 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> exit
Disconnected
-bash-3.2$ export ORACLE_SID=nonasmtoasm
-bash-3.2$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Jun 7 23:41:32 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select name from v$datafile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/nonasmtoasm/system01.dbf
/u01/app/oracle/oradata/nonasmtoasm/undotbs01.dbf
/u01/app/oracle/oradata/nonasmtoasm/sysaux01.dbf
/u01/app/oracle/oradata/nonasmtoasm/users01.dbf

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host
[oracle@localhost ~]$ pwd
/home/oracle
[oracle@localhost ~]$ cd /u01/app/oracle/admin/
+ASM/        dbtest/      nonasmtoasm/ test/        testdb/
[oracle@localhost ~]$ cd /u01/app/oracle/admin/nonasmtoasm/
adump/  bdump/  cdump/  dpdump/ pfile/  udump/
[oracle@localhost ~]$ cd /u01/app/oracle/admin/nonasmtoasm/pfile/
[oracle@localhost pfile]$ vi init.ora.572010231451
[oracle@localhost pfile]$ vi init.ora.572010231451
[oracle@localhost pfile]$ ls
init.ora.572010231451  nonasm.ora
[oracle@localhost pfile]$ pwd
/u01/app/oracle/admin/nonasmtoasm/pfile

We will have to change some of the parameters like:

from:

control_files=(“/u01/app/oracle/oradata/nonasmtoasm/control01.ctl”, “/u01/app/oracle/oradata/nonasmtoasm/control02.ctl”, “/u01/app/oracle/oradata/nonasmtoasm/control03.ctl”)

to:

control_files=(“+DATA1/control01.ctl”, “+DATA1/control02.ctl”, “+DATA1/control03.ctl”)

[oracle@localhost pfile]$ vi nonasm.ora

-bash-3.2$ export ORACLE_SID=nonasmtoasm
-bash-3.2$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Jun 8 03:30:47 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

Put database in nomount state and try to recover the controlfile from rman:

SQL> startup nomount pfile=’/u01/app/oracle/admin/nonasmtoasm/pfile/nonasm.ora’;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  2019288 bytes
Variable Size              75497512 bytes
Database Buffers           83886080 bytes
Redo Buffers                6369280 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
-bash-3.2$ pwd
/u01/app/oracle/admin/nonasmtoasm/pfile
-bash-3.2$ cd ../
-bash-3.2$ ls
adump  bdump  cdump  dpdump  pfile  udump
-bash-3.2$ cd ../
-bash-3.2$ ls
+ASM  dbtest  nonasmtoasm  test  testdb
-bash-3.2$ no
nohup        notify-send
-bash-3.2$ no
nohup        notify-send
-bash-3.2$ nonasmtoasm/
adump/  bdump/  cdump/  dpdump/ pfile/  udump/
-bash-3.2$ ls
+ASM  dbtest  nonasmtoasm  test  testdb
-bash-3.2$ cd ../
-bash-3.2$ ls
admin           flash_recovery_area  oradata        par_exp02.dmp  scott.dmp
expdpscott.log  impdptest.log        oraInventory   par_exp03.dmp  soe.dmp
expdpsoe.log    noasmtoasm           par_exp01.dmp  product
-bash-3.2$ cd ora
oradata/      oraInventory/
-bash-3.2$ cd oradata/
nonasmtoasm/ test/        testdb/
-bash-3.2$ cd oradata/nonasmtoasm/
control01.ctl  redo01.log     sysaux01.dbf   undotbs01.dbf
control02.ctl  redo02.log     system01.dbf   users01.dbf
control03.ctl  redo03.log     temp01.dbf
-bash-3.2$ cd oradata/nonasmtoasm/
-bash-3.2$ pwd
/u01/app/oracle/oradata/nonasmtoasm
-bash-3.2$ export ORACLE_SID=nonasmtoasm

-bash-3.2$ pwd
/u01/app/oracle/oradata/nonasmtoasm
-bash-3.2$ ls
control01.ctl  redo01.log  sysaux01.dbf  undotbs01.dbf
control02.ctl  redo02.log  system01.dbf  users01.dbf
control03.ctl  redo03.log  temp01.dbf
-bash-3.2$ rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on Tue Jun 8 03:52:56 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: nonasmto (not mounted)

RMAN> restore controlfile from ‘/u01/app/oracle/oradata/nonasmtoasm/control01.ctl’;

Starting restore at 08-JUN-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATA1/control01.ctl
output filename=+DATA1/control02.ctl
output filename=+DATA1/control03.ctl
Finished restore at 08-JUN-10

RMAN>  alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> backup as copy database format ‘+DATA1’;

Starting backup at 08-JUN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/nonasmtoasm/system01.dbf
output filename=+DATA1/nonasmtoasm/datafile/system.259.721108803 tag=TAG20100608T040001 recid=1 stamp=721108834
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/nonasmtoasm/sysaux01.dbf
output filename=+DATA1/nonasmtoasm/datafile/sysaux.260.721108841 tag=TAG20100608T040001 recid=2 stamp=721108854
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/nonasmtoasm/undotbs01.dbf
output filename=+DATA1/nonasmtoasm/datafile/undotbs1.261.721108865 tag=TAG20100608T040001 recid=3 stamp=721108867
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/nonasmtoasm/users01.dbf
output filename=+DATA1/nonasmtoasm/datafile/users.262.721108873 tag=TAG20100608T040001 recid=4 stamp=721108872
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA1/nonasmtoasm/controlfile/backup.263.721108875 tag=TAG20100608T040001 recid=5 stamp=721108875
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 08-JUN-10

RMAN> switch database to copy;

datafile 1 switched to datafile copy “+DATA1/nonasmtoasm/datafile/system.259.721108803”
datafile 2 switched to datafile copy “+DATA1/nonasmtoasm/datafile/undotbs1.261.721108865”
datafile 3 switched to datafile copy “+DATA1/nonasmtoasm/datafile/sysaux.260.721108841”
datafile 4 switched to datafile copy “+DATA1/nonasmtoasm/datafile/users.262.721108873”

RMAN> report schema;

Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
—- ——– ——————– ——- ————————
1    470      SYSTEM               ***     +DATA1/nonasmtoasm/datafile/system.259.721108803
2    25       UNDOTBS1             ***     +DATA1/nonasmtoasm/datafile/undotbs1.261.721108865
3    230      SYSAUX               ***     +DATA1/nonasmtoasm/datafile/sysaux.260.721108841
4    5        USERS                ***     +DATA1/nonasmtoasm/datafile/users.262.721108873

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1    20       TEMP                 32767       /u01/app/oracle/oradata/nonasmtoasm/temp01.dbf

RMAN> run
2> {
3> set newname for tempfile 1 to
4> ‘+DATA1/nonasmtoasm/datafile/tempfile’;
5> switch tempfile all;
6> }

executing command: SET NEWNAME

renamed temporary file 1 to +DATA1/nonasmtoasm/datafile/tempfile in control file

RMAN> alter database open;

database opened

RMAN>

RMAN> exit

Recovery Manager complete.
-bash-3.2$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Jun 8 04:08:25 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select name from v$database;

NAME
———
NONASMTO

SQL> select * from v$logfile;

GROUP# STATUS  TYPE
———- ——- ——-
MEMBER
——————————————————————————–
IS_

3         ONLINE
/u01/app/oracle/oradata/nonasmtoasm/redo03.log
NO

2         ONLINE
/u01/app/oracle/oradata/nonasmtoasm/redo02.log
NO

GROUP# STATUS  TYPE
———- ——- ——-
MEMBER
——————————————————————————–
IS_

1         ONLINE
/u01/app/oracle/oradata/nonasmtoasm/redo01.log
NO

SQL> alter database add logfile group 4(‘+DATA’);

Database altered.

SQL> alter database add logfile group 5(‘+DATA’);

Database altered.

SQL> alter database add logfile group 6(‘+DATA’);
alter database add logfile group 6(‘+DATA’)
*
ERROR at line 1:
ORA-00301: error in adding log file ‘+DATA’ – file cannot be created
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup space exhausted

SQL> alter database add logfile group 6(‘+DATA1’);

Database altered.

SQL> alter database add logfile group 7(‘+DATA1’);

Database altered.

SQL> alter database add logfile group 8(‘+DATA1’);

Database altered.

SQL> select group#,member from v$logfile;

GROUP#
———-
MEMBER
——————————————————————————–
3
/u01/app/oracle/oradata/nonasmtoasm/redo03.log

2
/u01/app/oracle/oradata/nonasmtoasm/redo02.log

1
/u01/app/oracle/oradata/nonasmtoasm/redo01.log

GROUP#
———-
MEMBER
——————————————————————————–
4
+DATA/nonasmtoasm/onlinelog/group_4.293.721109357

5
+DATA/nonasmtoasm/onlinelog/group_5.294.721109377

6
+DATA1/nonasmtoasm/onlinelog/group_6.265.721109433

GROUP#
———-
MEMBER
——————————————————————————–
7
+DATA1/nonasmtoasm/onlinelog/group_7.266.721109449

8
+DATA1/nonasmtoasm/onlinelog/group_8.267.721109465

8 rows selected.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance nonasmtoasm (thread 1) – cannot
drop
ORA-00312: online log 3 thread 1:
‘/u01/app/oracle/oradata/nonasmtoasm/redo03.log’

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance nonasmtoasm (thread 1)
ORA-00312: online log 3 thread 1:
‘/u01/app/oracle/oradata/nonasmtoasm/redo03.log’

SQL> alter system checkpoint;

System altered.

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance nonasmtoasm (thread 1) needs to be archived
ORA-00312: online log 3 thread 1:
‘/u01/app/oracle/oradata/nonasmtoasm/redo03.log’

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select group#,member from v$logfile;

GROUP#
———-
MEMBER
——————————————————————————–
4
+DATA/nonasmtoasm/onlinelog/group_4.293.721109357

6
+DATA1/nonasmtoasm/onlinelog/group_6.265.721109433

7
+DATA1/nonasmtoasm/onlinelog/group_7.266.721109449

GROUP#
———-
MEMBER
——————————————————————————–
8
+DATA1/nonasmtoasm/onlinelog/group_8.267.721109465

SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01623: log 4 is current log for instance nonasmtoasm (thread 1) – cannot
drop
ORA-00312: online log 4 thread 1:
‘+DATA/nonasmtoasm/onlinelog/group_4.293.721109357’

SQL> alter database clear unarchived logfile group 4;
alter database clear unarchived logfile group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recovery of instance nonasmtoasm (thread 1)
ORA-00312: online log 4 thread 1:
‘+DATA/nonasmtoasm/onlinelog/group_4.293.721109357’

SQL> alter system checkpoint;

System altered.

SQL> alter database clear unarchived logfile group
2  ;

*
ERROR at line 2:
ORA-02177: Missing required group number

SQL> alter database clear unarchived logfile group 4;
alter database clear unarchived logfile group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recovery of instance nonasmtoasm (thread 1)
ORA-00312: online log 4 thread 1:
‘+DATA/nonasmtoasm/onlinelog/group_4.293.721109357’

SQL>

SQL>  alter system switch logfile;

System altered.

SQL> alter database clear unarchived logfile group 4;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> SQL> select group#,member from v$logfile;

GROUP#
———-
MEMBER
——————————————————————————–
6
+DATA1/nonasmtoasm/onlinelog/group_6.265.721109433

7
+DATA1/nonasmtoasm/onlinelog/group_7.266.721109449

8
+DATA1/nonasmtoasm/onlinelog/group_8.267.721109465

SQL>
SQL>

Advertisements

June 2, 2010 - Posted by | ASM

No comments yet.

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: