Friday, April 12, 2013

how to convert a cooked file system oracle database to ASM



First create a ASM instance by following the link
steps-to-create-asm-instance-on-windows

once done follow the following steps


sqlplus>select name, state, type, total_mb, free_mb from v$asm_diskgroup;
sqlplus>select path,name from v$asm_disk;


set oracle_sid=test

take backup to be secure


create a table just for verification

sqlplus>create table friend (name varchar(20));
sqlplus>insert into friend values ('mani');
sqlplus>insert into friend values ('kirnai');
sqlplus>insert into friend values ('lovi');
sqlplus>commit

sqlplus>create pfile from spfile;

edit the pfile as follows
in the value of contrtolfile edit it with '+dgroup1'

sqlplus>shu immediate;

sqlplus / as sysdba


SQL> create spfile from pfile;

File created.



RMAN> startup nomount;

Oracle instance started

Total System Global Area 293601280 bytes

Fixed Size 1248624 bytes
Variable Size 113246864 bytes
Database Buffers 176160768 bytes
Redo Buffers 2945024 bytes

RMAN> restore controlfile from 'C:\oracle\product\10.2.0\oradata\orcl\CONTROL01.CTL';

Starting restore at 12-APR-13
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=+DGROUP1/orcl/controlfile/backup.256.812557745
Finished restore at 12-APR-13

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> backup as copy database format '+dgroup1';

Starting backup at 12-APR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
output filename=+DGROUP1/orcl/datafile/system.257.812558199 tag=TAG20130412T143637 recid=7 stamp=812558268
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
output filename=+DGROUP1/orcl/datafile/sysaux.258.812558273 tag=TAG20130412T143637 recid=8 stamp=812558310
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
output filename=+DGROUP1/orcl/datafile/example.259.812558319 tag=TAG20130412T143637 recid=9 stamp=812558335
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
output filename=+DGROUP1/orcl/datafile/undotbs1.260.812558345 tag=TAG20130412T143637 recid=10 stamp=812558358
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
output filename=+DGROUP1/orcl/datafile/users.261.812558361 tag=TAG20130412T143637 recid=11 stamp=812558361
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=+DGROUP1/orcl/controlfile/backup.262.812558363 tag=TAG20130412T143637 recid=12 stamp=812558366
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 12-APR-13
channel ORA_DISK_1: finished piece 1 at 12-APR-13
piece handle=+DGROUP1/orcl/backupset/2013_04_12/nnsnf0_tag20130412t143637_0.263.812558367 tag=TAG20130412T143637 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 12-APR-13

RMAN>



alter diskgroup group1 add disk ' ';
# if you are show error of insufficient space

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DGROUP1/orcl/datafile/system.257.812558199"
datafile 2 switched to datafile copy "+DGROUP1/orcl/datafile/undotbs1.260.812558345"
datafile 3 switched to datafile copy "+DGROUP1/orcl/datafile/sysaux.258.812558273"
datafile 4 switched to datafile copy "+DGROUP1/orcl/datafile/users.261.812558361"
datafile 5 switched to datafile copy "+DGROUP1/orcl/datafile/example.259.812558319"


RMAN> alter database open;

database opened


SQL> select file_name from dba_data_files;

FILE_NAME
----------------------------------------------
+DGROUP1/orcl/datafile/users.261.812558361
+DGROUP1/orcl/datafile/sysaux.258.812558273
+DGROUP1/orcl/datafile/undotbs1.260.812558345
+DGROUP1/orcl/datafile/system.257.812558199
+DGROUP1/orcl/datafile/example.259.812558319


SQL> select name from v$controlfile;

NAME
------------------------------------------------------
+DGROUP1/orcl/controlfile/backup.256.812557745


SQL> select member from v$logfile;

MEMBER
-----------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG



SQL> alter database drop logfile group 2;

Database altered.


SQL> alter database add logfile group 2 ('+DGROUP1/orcl/REDO02.LOG') size 10m;

Database altered.
SQL> alter database drop logfile group 3;

Database altered.


SQL> alter database add logfile group 3 ('+DGROUP1/orcl/REDO03.LOG') size 10m;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 1;

Database altered.


SQL> alter database add logfile group 1 ('+DGROUP1/orcl/REDO01.LOG') size 10m;

Database altered.

1

SQL> select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 UNUSED
2 INACTIVE
3 CURRENT


SQL> select member from v$logfile;

MEMBER
-------------------------------------
+DGROUP1/orcl/redo03.log
+DGROUP1/orcl/redo02.log
+DGROUP1/orcl/redo01.log


SQL> select name from v$tempfile;

NAME
----------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF


SQL> alter tablespace temp add tempfile '+DGROUP1' SIZE 10M;

Tablespace altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF
+DGROUP1/orcl/tempfile/temp.267.812559725


SQL> alter tablespace temp drop tempfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF';

Tablespace altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DGROUP1/orcl/tempfile/temp.267.812559725


SQL> select * from friend
2 ;

NAME
--------------------
mani
kirnai
lovi


thats it :)

No comments: