Tuesday, October 4, 2011

How to convert non asm (DB) to asm oracle database 10g

Migration from a non ASM instance to ASM instance is very easy with RMAN
i am doing it here ... just follow the steps.

Assume that you have created a ASM instance in a DISK group.
if you don't have then see here .....
How to create ASM environment and ASM instance

Now we are trying to convert a Non ASM instance(db) "HALIMDB" to a ASM instance .

Here i am using a Disk group "DISKGP1" AS A storage of ASM instance. now we have to convert all existing database file to ASM disk group.

here are live example :- just follow below steps ....( in a one session)

(this is in window environment)

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>set oracle_sid=HALIMDB

C:\Documents and Settings\Administrator>
C:\Documents and Settings\Administrator>
C:\Documents and Settings\Administrator>SQLPLUS /NOLOG

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 4 15:50:23 2011

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

SQL>
SQL>
SQL> CONN / AS SYSDBA
Connected.
SQL>
SQL> SHOW parameter inst

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
active_instance_count integer
cluster_database_instances integer 1
instance_groups string
instance_name string halimdb
instance_number integer 0
instance_type string RDBMS
open_links_per_instance integer 4
parallel_instance_group string
parallel_server_instances integer 1
SQL>
SQL> alter system set control_files='+DISKGP1' scope=spfile;

System altered.

SQL> alter system set db_create_file_dest='+DISKGP1' scope=spfile;

System altered.

SQL> alter system set db_recovery_file_dest='+DISKGP1' scope=spfile;

System altered.

SQL>
SQL>
SQL>
SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL>
SQL> startup nomount
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 205523844 bytes
Database Buffers 398458880 bytes
Redo Buffers 7135232 bytes
SQL>
SQL>
SQL> host rman target=sys

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Oct 4 15:52:44 2011

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

target database Password:
connected to target database: HALIMDB (not mounted)

RMAN>

RMAN>

RMAN> restore controlfile from 'G:\oracle\product\10.2.0\oradata\HALIMDB\CONTROL
01.CTL' ;

Starting restore at 04-OCT-11
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=+DISKGP1/halimdb/controlfile/backup.256.763660435
Finished restore at 04-OCT-11

RMAN>

RMAN> alter database mount ;

database mounted
released channel: ORA_DISK_1

RMAN>

RMAN>

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

Starting backup at 04-OCT-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\SYSTEM01.
DBF
output filename=+DISKGP1/halimdb/datafile/system.257.763661277 tag=TAG20111004T1
60749 recid=2 stamp=763661331
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\SYSAUX01.
DBF
output filename=+DISKGP1/halimdb/datafile/sysaux.258.763661343 tag=TAG20111004T1
60749 recid=3 stamp=763661360
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\EXAMPLE01
.DBF
output filename=+DISKGP1/halimdb/datafile/example.259.763661367 tag=TAG20111004T
160749 recid=4 stamp=763661375
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\UNDOTBS01
.DBF
output filename=+DISKGP1/halimdb/datafile/undotbs1.260.763661383 tag=TAG20111004
T160749 recid=5 stamp=763661386
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\USERS01.D
BF
output filename=+DISKGP1/halimdb/datafile/users.261.763661391 tag=TAG20111004T16
0749 recid=6 stamp=763661392
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DISKGP1/halimdb/controlfile/backup.262.763661393 tag=TAG2011100
4T160749 recid=7 stamp=763661395
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 04-OCT-11
channel ORA_DISK_1: finished piece 1 at 04-OCT-11
piece handle=+DISKGP1/halimdb/backupset/2011_10_04/nnsnf0_tag20111004t160749_0.2
63.763661397 tag=TAG20111004T160749 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 04-OCT-11

RMAN>

RMAN>

RMAN>

RMAN> switch database to copy ;

datafile 1 switched to datafile copy "+DISKGP1/halimdb/datafile/system.257.76366
1277"
datafile 2 switched to datafile copy "+DISKGP1/halimdb/datafile/undotbs1.260.763
661383"
datafile 3 switched to datafile copy "+DISKGP1/halimdb/datafile/sysaux.258.76366
1343"
datafile 4 switched to datafile copy "+DISKGP1/halimdb/datafile/users.261.763661
391"
datafile 5 switched to datafile copy "+DISKGP1/halimdb/datafile/example.259.7636
61367"

RMAN>

RMAN>

RMAN> exit


Recovery Manager complete.

SQL> conn / as sysdba
Connected.
SQL>
SQL> recover database using backup controlfile until cancel ;
ORA-00279: change 664362 generated at 10/03/2011 17:49:50 needed for thread 1
ORA-00289: suggestion : +DISKGP1
ORA-00280: change 664362 for thread 1 is in sequence #3


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL>
SQL>
SQL> alter database open resetlogs ;

Database altered.

SQL>
SQL>
SQL> alter database tempfile 'G:\oracle\product\10.2.0\oradata\HALIMDB\TEMP01.DB
F' drop including datafiles ;

Database altered.

SQL>
SQL>
SQL> alter tablespace temp add tempfile size 512m autoextend off ;

Tablespace altered.

SQL>column member format a30
SQL>
SQL> select tablespace_name, file_name, bytes from dba_temp_files;
SQL>
GROUP# MEMBER BYTES
---------- ------------------------------ ----------
3 G:\ORACLE\PRODUCT\10.2.0\ORADA 52428800
TA\HALIMDB\REDO03.LOG

2 G:\ORACLE\PRODUCT\10.2.0\ORADA 52428800
TA\HALIMDB\REDO02.LOG

1 G:\ORACLE\PRODUCT\10.2.0\ORADA 52428800
TA\HALIMDB\REDO01.LOG

4 G:\ORACLE\PRODUCT\10.2.0\ORADA 52428800
TA\HALIMDB\REDO04.LOG

GROUP# MEMBER BYTES
---------- ------------------------------ ----------


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

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

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

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

GROUP# STATUS
---------- ----------------
1 ACTIVE
2 ACTIVE
3 CURRENT
4 UNUSED

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance halimdb (thread 1)
ORA-00312: online log 1 thread 1:
'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO01.LOG'


SQL> alter system checkpoint global;

System altered.

SQL> alter system checkpoint global;

System altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL>
SQL>
SQL> alter database add logfile group 1 size 50m ;

Database altered.

SQL> alter system checkpoint global;

System altered.

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

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

SQL> alter database drop logfile group 2;

Database altered.

SQL>
SQL> alter database add logfile group 2 size 50m ;

Database altered.

SQL>
SQL>
SQL> alter system checkpoint global;

System altered.

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

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

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

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

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

SQL> alter database drop logfile group 3;

Database altered.

SQL>
SQL> alter database add logfile group 3 size 50m ;

Database altered.

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

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

SQL> alter database drop logfile group 4;

Database altered.

SQL>
SQL> alter database add logfile group 4 size 50m ;

Database altered.

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

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

SQL>
SQL> select a.group#, a.member, b.bytes from v$logfile a, v$log b where a.group#
= b.group#;

GROUP# MEMBER BYTES
---------- ------------------------------ ----------
3 +DISKGP1/halimdb/onlinelog/gro 52428800
up_3.269.763664937

2 +DISKGP1/halimdb/onlinelog/gro 52428800
up_2.267.763664865

1 +DISKGP1/halimdb/onlinelog/gro 52428800
up_1.265.763664821

4 +DISKGP1/halimdb/onlinelog/gro 52428800
up_4.271.763664975

GROUP# MEMBER BYTES
---------- ------------------------------ ----------

1 +DISKGP1/halimdb/onlinelog/gro 52428800
up_1.266.763664825

2 +DISKGP1/halimdb/onlinelog/gro 52428800
up_2.268.763664869

3 +DISKGP1/halimdb/onlinelog/gro 52428800
up_3.270.763664941

4 +DISKGP1/halimdb/onlinelog/gro 52428800

GROUP# MEMBER BYTES
---------- ------------------------------ ----------
up_4.272.763664977


8 rows selected.

SQL>
SQL>
SQL>
SQL> create pfile ='G:\oracle\asmhalim\initnow.ora' from spfile ;

File created.

SQL>
SQL> create spfile='+DISKGP1/SPFILEASM.ORA' FROM pfile='G:\oracle\asmhalim\init
now.ora' ;

File created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> host rman target=sys

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Oct 4 17:13:05 2011

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

target database Password:
connected to target database: HALIMDB (DBID=3159673037)

RMAN>

RMAN>

RMAN> delete noprompt force copy ;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
specification does not match any archive log in the recovery catalog

List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
8 1 A 04-OCT-11 664362 03-OCT-11 G:\ORACLE\PRODUCT\10.2
.0\ORADATA\HALIMDB\SYSTEM01.DBF
9 2 A 04-OCT-11 664362 03-OCT-11 G:\ORACLE\PRODUCT\10.2
.0\ORADATA\HALIMDB\UNDOTBS01.DBF
10 3 A 04-OCT-11 664362 03-OCT-11 G:\ORACLE\PRODUCT\10.2
.0\ORADATA\HALIMDB\SYSAUX01.DBF
11 4 A 04-OCT-11 664362 03-OCT-11 G:\ORACLE\PRODUCT\10.2
.0\ORADATA\HALIMDB\USERS01.DBF
12 5 A 04-OCT-11 664362 03-OCT-11 G:\ORACLE\PRODUCT\10.2
.0\ORADATA\HALIMDB\EXAMPLE01.DBF

List of Control File Copies
Key S Completion Time Ckp SCN Ckp Time Name
------- - --------------- ---------- --------------- ----
7 A 04-OCT-11 664362 03-OCT-11 +DISKGP1/halimdb/controlfil
e/backup.262.763661393
deleted datafile copy
datafile copy filename=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\SYSTEM01.DBF rec
id=8 stamp=763661844
deleted datafile copy
datafile copy filename=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\UNDOTBS01.DBF re
cid=9 stamp=763661844
deleted datafile copy
datafile copy filename=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\SYSAUX01.DBF rec
id=10 stamp=763661844
deleted datafile copy
datafile copy filename=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\USERS01.DBF reci
d=11 stamp=763661844
deleted datafile copy
datafile copy filename=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\EXAMPLE01.DBF re
cid=12 stamp=763661844
deleted control file copy
control file copy filename=+DISKGP1/halimdb/controlfile/backup.262.763661393 rec
id=7 stamp=763661395
Deleted 6 objects


RMAN>

RMAN>

RMAN>

RMAN>

RMAN> end end end end end end




No comments: