Moving the control, data and redo log file of an Oracle 11g R2 database to a new location
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.
$ 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>
Recent Comments