Oracle Apps DBA

My photo
More than 5+ Years of IT Experience in administering Enterprise wide Multi Vendor UNIX Servers, Oracle Databases (8i to 11gR2), Middle-tiers, Applications and Clusters. I am a Sun Certified System Administrator (SCSA) for Solaris 10, oracle 10g database certified associate(OCA), Oracle 10g Database certified professional (OCP) and Oracle E-Business suite R12 certified professionl(OCP)

Oracle Database and Applications

Wednesday, 27 October 2010

Move control file from Non-ASM to ASM in 11.2.0.2

This article will be useful for Moving control file from Normal UNIX file system or any other file system to Oracle ASM.

Moving control file from UFS to ASM:

1) Check the location of control file:

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/11g_Home/oradata/db1/control01.ctl


2)check ASM Disk group:

SQL> select state,name from v$asm_diskgroup;

STATE NAME
----------- ------------------------------
MOUNTED ASMDATA1


3) Shutdown the database and startup in nomount

SQL> shutdown normal
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


----

startup database in nomount state:

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1720844288 bytes
Fixed Size 2159024 bytes
Variable Size 1241517648 bytes
Database Buffers 469762048 bytes
Redo Buffers 7405568 bytes
SQL>



4) connect rman target with nocatalog option


bash-3.00$ rman nocatalog

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Oct 27 11:16:31 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target

connected to target database: DB1 (not mounted)
using target database control file instead of recovery catalog

5) Restore control file

RMAN> restore controlfile to '+ASMDATA1' from '/u01/11g_Home/oradata/db1/control01.ctl';

Starting restore at 27-OCT-10
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
Finished restore at 27-OCT-10

6) Check the restored controlfile on ASM Disk group to get the new name of file

ASMCMD> find -t CONTROLFILE +ASMDATA1 *

+ASMDATA1/DB1/CONTROLFILE/current.256.733490439

7) Update pfile or spfile with new location of controlfile

update the parameter:

*.control_files='+ASMDATA1/DB1/CONTROLFILE/current.256.733490439'

I am using pfile, so updated the parameter in pfile. If using spfile we need to update it with "Alter system command"

8) Shutdown and startup the database

bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Oct 27 11:31:46 2010

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate
ORA-01507: database not mounted


ORACLE instance shut down.

--------


bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Oct 27 11:32:44 2010

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1720844288 bytes
Fixed Size 2159024 bytes
Variable Size 1241517648 bytes
Database Buffers 469762048 bytes
Redo Buffers 7405568 bytes
Database mounted.
Database opened.

SQL>

9) Verify the new location of controlfile

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+ASMDATA1/db1/controlfile/current.256.733490439

No comments: