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: {
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:
Post a Comment