I'm looking into moving the location the control file of my Oracle database into a different location. The new location will be a file system that has been mounted on an IBM Storwize V7000 storage system.
Found some great information at http://psoug.org/reference/control_file.html
Here is what I did on my system.
Next, update the init<instance_name>.ora file under $ORACLE_HOME/dbs with the new location
Now, create a new spfile with the updated information.
There we go, we have the location of the control files updated.
==================================================================
Next we will move the data files to the new location.
Lastly we will change the location of the redo logs of the database
Found lots of good information on it at http://www.ordba.net/Tutorials/Redolog.htm
Here is how I moved my redlo logs to the new location.
Found some great information at http://psoug.org/reference/control_file.html
Here is what I did on my system.
$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 12:01:47 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show parameter control; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /home/test/test1/control01.ctl , /home/test/test1/control02.c tl control_management_pack_access string DIAGNOSTIC+TUNING SQL> SQL> shutdown abort; ORACLE instance shut down. SQL> create pfile from spfile; File created. SQL> On the host system, copy the files from their current location(/home/test/test1) to the new location(/oraarch/test1) $ ls control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf control02.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf $ pwd /home/test/test1 $
Next, update the init<instance_name>.ora file under $ORACLE_HOME/dbs with the new location
$ cat inittest1.ora | grep control *.control_files='/oraarch/test1/control01.ctl','/oraarch/test1/control02.ctl'
Now, create a new spfile with the updated information.
SQL> create spfile from pfile; File created. SQL> show parameter control; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /oraarch/test1/control01.ctl, /oraarch/test1/control02.ctl control_management_pack_access string DIAGNOSTIC+TUNING SQL>
There we go, we have the location of the control files updated.
==================================================================
Next we will move the data files to the new location.
isvp17> su - oracle $ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 12:59:00 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /home/test/test1/system01.dbf /home/test/test1/sysaux01.dbf /home/test/test1/undotbs01.dbf /home/test/test1/users01.dbf SQL> shutdown abort; ORACLE instance shut down. SQL> Copy the data files to the new location. $ cp /home/test/test1/system01.dbf /oradata/test1 $ cp /home/test/test1/sysaux01.dbf /oradata/test1 $ cp /home/test/test1/undotbs01.dbf /oradata/test1 $ cp /home/test/test1/users01.dbf /oradata/test1 Start the database in startup mount mode $ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 13:04:41 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 1.0289E+10 bytes Fixed Size 2215712 bytes Variable Size 5301600480 bytes Database Buffers 4966055936 bytes Redo Buffers 18743296 bytes Database mounted. SQL> SQL> alter database rename file '/home/test/test1/system01.dbf' to '/oradata/test1/system01.dbf'; Database altered. SQL> alter database rename file '/home/test/test1/sysaux01.dbf' to '/oradata/test1/sysaux01.dbf'; Database altered. SQL> alter database rename file '/home/test/test1/undotbs01.dbf' to '/oradata/test1/undotbs01.dbf'; Database altered. SQL> alter database rename file '/home/test/test1/users01.dbf' to '/oradata/test1/users01.dbf'; Database altered. SQL> alter database open; Database altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /oradata/test1/system01.dbf /oradata/test1/sysaux01.dbf /oradata/test1/undotbs01.dbf /oradata/test1/users01.dbf SQL>==================================================================================
Lastly we will change the location of the redo logs of the database
Found lots of good information on it at http://www.ordba.net/Tutorials/Redolog.htm
Here is how I moved my redlo logs to the new location.
SQL> shutdown abort; ORACLE instance shut down. SQL> $ cp redo0*.log /oralog/test1 $ ls /oralog/test1 redo01.log redo02.log redo03.log $ SQL> startup mount; ORACLE instance started. Total System Global Area 1.0289E+10 bytes Fixed Size 2215712 bytes Variable Size 5301600480 bytes Database Buffers 4966055936 bytes Redo Buffers 18743296 bytes Database mounted. SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /home/test/test1/redo03.log /home/test/test1/redo02.log /home/test/test1/redo01.log SQL> SQL> alter database rename file '/home/test/test1/redo01.log' to '/oralog/test1/redo01.log'; Database altered. SQL> alter database rename file '/home/test/test1/redo02.log' to '/oralog/test1/redo02.log'; Database altered. SQL> alter database rename file '/home/test/test1/redo03.log' to '/oralog/test1/redo03.log'; Database altered. SQL> SQL> alter database open; Database altered. SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /oralog/test1/redo03.log /oralog/test1/redo02.log /oralog/test1/redo01.log SQL>
5 comments:
Wow, that was great! All the steps in one place - saved me a lot of time researching.
I'm glad it helped. Thank you for the kind words.
Excellent step by step instructions. Thank you my friend. Zane
Zane, thank you for leaving a comment.
Great!! Just the instruction I was looking for. Running Oracle on Windows, but I knew how to copy the files anyway ;)
Post a Comment