DBA Hub

📋Steps in this guide1/5

How to convert a non ASM instance to ASM instance - DBACLASS DBACLASS

Here in this article, We will provide detailed steps for converting a non-asm instance to an asm-instance.  Prerequisites: 1. Oracle cluster  need to be installed on that server( for ASM)  2. Create required asm disk groups. EXAMPLE: Currrently the datafiles are in /u02 […]

oracle clusteringintermediate
by OracleDba
13 views
1

Overview

Here in this article, We will provide detailed steps for converting a non-asm instance to an asm-instance. Prerequisites: 1. Oracle cluster  need to be installed on that server( for ASM) 2. Create required asm disk groups. EXAMPLE:
2

Section 2

Currrently the datafiles are in /u02 mount point. We will move them to ASM DISK GROUP +TEST_DG’. Database Name: DEVDBA Asm disk group: +TEST_DG’ First move the controlfile to ASM disk
3

Section 3

MOVE DATAFILE TO ASM: Once files are copied to ASM , switch database

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP NOMOUNT
ORACLE instance started. 
Total System Global Area 1.7103E+10 bytes
Fixed Size                  2245480 bytes
Variable Size            2214595736 bytes
Database Buffers         1.4865E+10 bytes
Redo Buffers               21708800 bytes
[oracle@TEST admin]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Oct 3 09:20:31 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DEVDB (not mounted)

 

RMAN>  restore controlfile to '+TEST_DG' from '/u02/oradata/11.2.0.3/DEVDB/control01.ctl';
 

Starting restore at 03-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=193 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 03-OCT-13

 

RMAN> exit

Recovery Manager complete.
 

[oracle@TEST admin]$ s
 
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 3 09:25:18 2013 
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, OLAP, Data Mining and Real Application Testing options


SQL> create spfile from pfile;

 File created.

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

ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 1.7103E+10 bytes
Fixed Size                  2245480 bytes
Variable Size            2214595736 bytes
Database Buffers         1.4865E+10 bytes
Redo Buffers               21708800 bytes

SQL> alter system set control_files='+TEST_DG/DEVDB/CONTROLFILE/current.270.827832049' scope=spfile;

System altered.

SQL> shutdown immediate;

ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.7103E+10 bytes
Fixed Size 2245480 bytes
Variable Size 2214595736 bytes
Database Buffers 1.4865E+10 bytes
Redo Buffers 21708800 bytes
Database mounted.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+TEST_DG/devdb/controlfile/current.270.827832049

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

[oracle@TEST admin]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Oct 3 09:28:11 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DEVDB (DBID=732555631, not open)

 

RMAN> configure device type disk parallelism 4;
using target database control file instead of recovery catalog
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 '+TEST_DG';

 

Starting backup at 03-OCT-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=233 evice type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=241 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=249 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u02/oradata/DEVDB/system.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00002 name=/u02/oradata/DEVDB/sysaux.dbf
channel ORA_DISK_3: starting datafile copy
input datafile file number=00003 name=/u02/oradata/DEVDB/undotbs1.dbf
channel ORA_DISK_4: starting datafile copy
copying current control file
output file name=+TEST_DG/devdb/controlfile/backup.274.827832515 tag=TAG20131003T092833 RECID=1 STAMP=827832514
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_4: starting full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_4: starting piece 1 at 03-OCT-13
channel ORA_DISK_4: finished piece 1 at 03-OCT-13
piece handle=+TEST_DG/devdb/backupset/2013_10_03/nnsnf0_tag20131003t092833_0.275.827832515 tag=TAG20131003T092833 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:01
output file name=+TEST_DG/devdb/datafile/sysaux.272.827832515 tag=TAG20131003T092833 RECID=2 STAMP=827832517
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:04
output file name=+TEST_DG/devdb/datafile/undotbs1.273.827832515 tag=TAG20131003T092833 RECID=3 STAMP=827832518
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:04
output file name=+TEST_DG/devdb/datafile/system.271.827832513 tag=TAG20131003T092833 RECID=4 STAMP=827832530
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:27
Finished backup at 03-OCT-13
4

Section 4

MOVE REDOLOGS TO ASM: MODIFY THE PFILE POINTING TO SPFILE:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
RMAN> switch database to copy ;
datafile 1 switched to datafile copy "+TEST_DG/devdb/datafile/system.271.827832513"
datafile 2 switched to datafile copy "+TEST_DG/devdb/datafile/sysaux.272.827832515"
datafile 3 switched to datafile copy "+TEST_DG/devdb/datafile/undotbs1.273.827832515"

 

RMAN> ALTER DATABASE OPEN;
database opened
RMAN> exit

 Recovery Manager completed
 

[oracle@TEST admin]$ s
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 3 09:29:26 2013
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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options


SQL> select file_name from dba_data_files;

 FILE_NAME
--------------------------------------------------------------------------------
+TEST_DG/devdb/datafile/system.271.827832513
+TEST_DG/devdb/datafile/sysaux.272.827832515
+TEST_DG/devdb/datafile/undotbs1.273.827832515

SQL> select  GROUP#,MEMBERS,STATUS from  v$log; 

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------

         1          1 CURRENT
         2          1 INACTIVE
         3          1 INACTIVE
 

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

 SQL> alter database add logfile group 3 ('+TEST_DG') size 300M;
Database altered.

SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------

         1          1 CURRENT
         2          1 INACTIVE
         3          1 UNUSED

 

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

SQL> alter database add logfile group 2 ('+TEST_DG') size 300M;

Database altered.

 
SQL>  select  GROUP#,MEMBERS,STATUS from  v$log;

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

 

SQL> alter system switch logfile;
System altered.

SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

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

 SQL>  alter system switch logfile;

System altered.

SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 ACTIVE
         3          1 CURRENT

 

SQL> alter system switch logfile;

System altered.

 SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

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

SQL>  alter system switch logfile;

System altered.

SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------

         1          1 INACTIVE
         2          1 CURRENT
         3          1 INACTIVE

 

SQL> alter database drop logfile group 1;

Database altered.

 SQL> alter database add logfile group 1 ('+TEST_DG') size 300M;

Database altered.

 SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

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

 

SQL> create pfile from spfile;

 File created.

 SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
5

Section 5

MOVING THE TEMP FILE:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
[oracle@TEST dbs] vi initDEVDB.ora


Add one parameter  .db_create_file_dest=+TEST_DG


SQL> startup pfile='/u02/oradata/11.2.0.3/db_1/dbs/initDEVDB.ora';
ORACLE instance started.
Total System Global Area 1.7103E+10 bytes
Fixed Size                  2245480 bytes
Variable Size            2214595736 bytes
Database Buffers         1.4865E+10 bytes
Redo Buffers               21708800 bytes
Database mounted.
Database opened.

SQL> create spfile='+TEST_DG' from pfile='/u02/oradata/11.2.0.3/db_1/dbs/initDEVDB.ora';
File created.
SQL> exit

 

 ASMCMD> find --type parameterfile +TEST_DG *

+TEST_DG/DEVDB/PARAMETERFILE/spfile.279.827833001


##remove all entries and add the spfile parameter
$cd $ORACLE_HOME/dbs

 
[oracle@TEST dbs] vi initDEVDB.ora

 

Spfile=’ +TEST_DG/DEVDB/PARAMETERFILE/spfile.279.827833001’

 SQL> startup pfile='/u02/oradata/11.2.0.3/db_1/dbs/initDEVDB.ora';
ORACLE instance started.

Total System Global Area 1.7103E+10 bytes
Fixed Size                  2245480 bytes
Variable Size            2214595736 bytes
Database Buffers         1.4865E+10 bytes
Redo Buffers               21708800 bytes
Database mounted.
Database opened.

SQL> select name, bytes from v$tempfile;

 NAME
--------------------------------------------------------------------------------
     BYTES
----------
/u02/oradata/DEVDB/temp01.dbf

 104857600

 
SQL> 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 10120M extent management local uniform size 1M;

Tablespace created.

SQL> alter database default temporary tablespace temp;
Database altered.

 

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------

+TEST_DG/devdb/tempfile/temp.281.827833353
+TEST_DG/devdb/tempfile/temp1.280.827833319


SQL> drop tablespace temp1 including contents;

Tablespace dropped.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+TEST_DG/devdb/tempfile/temp.281.827833353

Comments (0)

Please to add comments

No comments yet. Be the first to comment!