Sunday, July 3, 2011

Control file location altering: Case Study

Control file location altering: Case Study




1.Locate the existing control file:

SQL> SELECT NAME FROM V$CONTROLFILE;

NAME
--------------------------------------------------------------------------------
/ora_data_db1/ORCL1/ORCL1/control01.ctl
/oracle/app/oracle/flash_recovery_area/ORCL1/control02.ctl

2.Make oracle as owner for the newly created mount point

bash-3.2# chown -R oracle:dba /ora_data_archv
bash-3.2# chmod 777 /ora_data_archv
bash-3.2#


3. Shutdown the database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


4. Do OS level copy of the control file to the new location:

cp /ora_data_db1/ORCL1/ORCL1/control01.ctl /ora_data_archv

oracle@XX1/oracle>cp /ora_data_db1/ORCL1/ORCL1/control01.ctl /ora_data_archv
oracle@XX1/oracle>


5. Create pfile:
oracle@XX1/oracle/app/oracle/product/11.2.0/dbhome_1/dbs>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 3 21:27:38 2011

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> create pfile='pfileORCL1' from spfile;

File created.

SQL> exit
Disconnected

6.Edit the pfile:
oracle@XX1/oracle/app/oracle/product/11.2.0/dbhome_1/dbs>ls -ltr
total 15128
-rw-r--r--    1 oracle   dba            2851 May 15 2009  init.ora
-rw-rw----    1 oracle   dba            1544 Jul 01 20:45 hc_DBUA0.dat
-rw-r-----    1 oracle   dba              24 Jul 01 20:53 lkORCL1
-rw-r-----    1 oracle   dba            2560 Jul 01 21:46 spfileORCL1.ora
-rw-r-----    1 oracle   dba            1536 Jul 03 13:21 orapwORCL1
-rw-r-----    1 oracle   dba         7716864 Jul 03 17:54 snapcf_ORCL1.f
-rw-rw----    1 oracle   dba            1544 Jul 03 21:27 hc_ORCL1.dat
-rw-r--r--    1 oracle   dba             971 Jul 03 21:28 pfileORCL1


oracle@XX1/oracle/app/oracle/product/11.2.0/dbhome_1/dbs>cp pfileORCL1 pfileORCL1.orig


*.control_files='/ora_data_db1/ORCL1/ORCL1/control01.ctl','/oracle/app/oracle/flash_recovery_area/ORCL1/contr
ol02.ctl'


/ora_data_archv

*.control_files='/ora_data_archv/control01.ctl','/oracle/app/oracle/flash_recovery_area/ORCL1/contr
ol02.ctl'




oracle@XX1/oracle/app/oracle/product/11.2.0/dbhome_1/dbs>mv spfileORCL1.ora spfileORCL1.ora.orig
oracle@XX1/oracle/app/oracle/product/11.2.0/dbhome_1/dbs>


7. Recreate the spfile from the edited spfile
oracle@XX1/oracle/app/oracle/product/11.2.0/dbhome_1/dbs>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 3 21:32:39 2011

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

SQL> conn / as sysdba
Connected to an idle instance.



SQL> create spfile from pfile='pfileORCL1';

File created.

8. Start the database

SQL> startup;
ORACLE instance started.

Total System Global Area 6847938560 bytes
Fixed Size                  2219808 bytes
Variable Size            3539992800 bytes
Database Buffers         3288334336 bytes
Redo Buffers               17391616 bytes
Database mounted.
Database opened.

9. Check new location


SQL> SELECT NAME FROM V$CONTROLFILE;

NAME
--------------------------------------------------------------------------------
/ora_data_archv/control01.ctl
/oracle/app/oracle/flash_recovery_area/ORCL1/control02.ctl


No comments:

Post a Comment