{"id":3062,"date":"2014-05-19T17:16:49","date_gmt":"2014-05-19T09:16:49","guid":{"rendered":"http:\/\/rmohan.com\/?p=3062"},"modified":"2014-05-19T17:16:49","modified_gmt":"2014-05-19T09:16:49","slug":"moving-the-control-data-and-redo-log-file-of-an-oracle-11g-r2-database-to-a-new-location","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=3062","title":{"rendered":"Moving the control, data and redo log file of an Oracle 11g R2 database to a new location"},"content":{"rendered":"

Moving the control, data and redo log file of an Oracle 11g R2 database to a new location<\/h3>\n
<\/div>\n
I’m looking into moving the location the control file of my Oracle database into a different location.<\/b> The new location will be a file system that has been mounted on an IBM Storwize V7000 storage system.<\/p>\n

Found some great information at http:\/\/psoug.org\/reference\/control_file.html<\/p>\n

Here is what I did on my system.<\/p>\n

$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 12:01:47 2011\r\n\r\nCopyright (c) 1982, 2009, Oracle.\u00a0 All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production\r\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options\r\n\r\nSQL> show parameter control;\r\n\r\nNAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALUE\r\n------------------------------------ ----------- ------------------------------\r\ncontrol_file_record_keep_time\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 integer\u00a0\u00a0\u00a0\u00a0 7\r\ncontrol_files\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 string\u00a0\u00a0\u00a0\u00a0\u00a0 \/home\/test\/test1\/control01.ctl\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , \/home\/test\/test1\/control02.c\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 tl\r\ncontrol_management_pack_access\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 string\u00a0\u00a0\u00a0\u00a0\u00a0 DIAGNOSTIC+TUNING\r\nSQL>\r\nSQL> shutdown abort;\r\nORACLE instance shut down.\r\nSQL> create pfile from spfile;\r\n\r\nFile created.\r\n\r\nSQL>\r\n\r\nOn the host system, copy the files from their current location(\/home\/test\/test1) to the new location(\/oraarch\/test1)\r\n\r\n\r\n$ ls\r\ncontrol01.ctl\u00a0 redo01.log\u00a0\u00a0\u00a0\u00a0 redo03.log\u00a0\u00a0\u00a0\u00a0 system01.dbf\u00a0\u00a0 undotbs01.dbf\r\ncontrol02.ctl\u00a0 redo02.log\u00a0\u00a0\u00a0\u00a0 sysaux01.dbf\u00a0\u00a0 temp01.dbf\u00a0\u00a0\u00a0\u00a0 users01.dbf\r\n$ pwd\r\n\/home\/test\/test1\r\n$\r\n<\/pre>\n
Next, update the init<instance_name>.ora file under $ORACLE_HOME\/dbs with the new location<\/p>\n
$ cat inittest1.ora | grep control\r\n*.control_files='\/oraarch\/test1\/control01.ctl','\/oraarch\/test1\/control02.ctl'\r\n<\/pre>\n
Now, create a new spfile with the updated information.<\/p>\n
SQL> create spfile from pfile;\r\n\r\nFile created.\r\n\r\nSQL> show parameter control;\r\n\r\nNAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALUE\r\n------------------------------------ ----------- ------------------------------\r\ncontrol_file_record_keep_time\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 integer\u00a0\u00a0\u00a0\u00a0 7\r\ncontrol_files\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 string\u00a0\u00a0\u00a0\u00a0\u00a0 \/oraarch\/test1\/control01.ctl,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/oraarch\/test1\/control02.ctl\r\ncontrol_management_pack_access\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 string\u00a0\u00a0\u00a0\u00a0\u00a0 DIAGNOSTIC+TUNING\r\nSQL>\r\n<\/pre>\n
There we go, we have the location of the control files updated.<\/p>\n
==================================================================<\/p>\n
Next we will move the data files to the new location.<\/b><\/p>\n
isvp17> su - oracle\r\n$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 12:59:00 2011\r\n\r\nCopyright (c) 1982, 2009, Oracle.\u00a0 All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production\r\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options\r\n\r\nSQL> select name from v$datafile;\r\n\r\nNAME\r\n--------------------------------------------------------------------------------\r\n\/home\/test\/test1\/system01.dbf\r\n\/home\/test\/test1\/sysaux01.dbf\r\n\/home\/test\/test1\/undotbs01.dbf\r\n\/home\/test\/test1\/users01.dbf\r\n\r\nSQL> shutdown abort;\r\nORACLE instance shut down.\r\nSQL>\r\n\r\nCopy the data files to the new location.\r\n\r\n$ cp \/home\/test\/test1\/system01.dbf \/oradata\/test1\r\n$ cp \/home\/test\/test1\/sysaux01.dbf \/oradata\/test1\r\n$ cp \/home\/test\/test1\/undotbs01.dbf \/oradata\/test1\r\n$ cp \/home\/test\/test1\/users01.dbf \/oradata\/test1\r\n\r\n\r\nStart the database in startup mount mode\r\n$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 13:04:41 2011\r\n\r\nCopyright (c) 1982, 2009, Oracle.\u00a0 All rights reserved.\r\n\r\nConnected to an idle instance.\r\n\r\nSQL> startup mount;\r\nORACLE instance started.\r\n\r\nTotal System Global Area 1.0289E+10 bytes\r\nFixed Size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2215712 bytes\r\nVariable Size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5301600480 bytes\r\nDatabase Buffers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4966055936 bytes\r\nRedo Buffers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18743296 bytes\r\nDatabase mounted.\r\nSQL>\r\nSQL> alter database rename file '\/home\/test\/test1\/system01.dbf' to '\/oradata\/test1\/system01.dbf';\r\n\r\nDatabase altered.\r\n\r\nSQL> alter database rename file '\/home\/test\/test1\/sysaux01.dbf' to '\/oradata\/test1\/sysaux01.dbf';\r\n\r\nDatabase altered.\r\nSQL> alter database rename file '\/home\/test\/test1\/undotbs01.dbf' to '\/oradata\/test1\/undotbs01.dbf';\r\n\r\nDatabase altered.\r\n\r\nSQL> alter database rename file '\/home\/test\/test1\/users01.dbf' to '\/oradata\/test1\/users01.dbf';\r\n\r\nDatabase altered.\r\n\r\nSQL> alter database open;\r\n\r\nDatabase altered.\r\n\r\nSQL> select name from v$datafile;\r\n\r\nNAME\r\n--------------------------------------------------------------------------------\r\n\/oradata\/test1\/system01.dbf\r\n\/oradata\/test1\/sysaux01.dbf\r\n\/oradata\/test1\/undotbs01.dbf\r\n\/oradata\/test1\/users01.dbf\r\n\r\nSQL>\r\n\r\n<\/pre>\n
==================================================================================<\/p>\n
Lastly we will change the location of the redo logs of the database<\/b><\/p>\n
Found lots of good information on it at http:\/\/www.ordba.net\/Tutorials\/Redolog.htm<\/p>\n
Here is how I moved my redlo logs to the new location.<\/p>\n
SQL> shutdown abort;\r\nORACLE instance shut down.\r\nSQL>\r\n\r\n\r\n\r\n$ cp redo0*.log \/oralog\/test1\r\n$ ls \/oralog\/test1\r\nredo01.log\u00a0 redo02.log\u00a0 redo03.log\r\n$\r\n\r\n\r\nSQL> startup mount;\r\nORACLE instance started.\r\n\r\nTotal System Global Area 1.0289E+10 bytes\r\nFixed Size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2215712 bytes\r\nVariable Size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5301600480 bytes\r\nDatabase Buffers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4966055936 bytes\r\nRedo Buffers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18743296 bytes\r\nDatabase mounted.\r\nSQL> select member from v$logfile;\r\n\r\nMEMBER\r\n--------------------------------------------------------------------------------\r\n\/home\/test\/test1\/redo03.log\r\n\/home\/test\/test1\/redo02.log\r\n\/home\/test\/test1\/redo01.log\r\n\r\nSQL>\r\nSQL> alter database rename file '\/home\/test\/test1\/redo01.log' to '\/oralog\/test1\/redo01.log';\r\n\r\nDatabase altered.\r\n\r\nSQL> alter database rename file '\/home\/test\/test1\/redo02.log' to '\/oralog\/test1\/redo02.log';\r\n\r\nDatabase altered.\r\n\r\nSQL> alter database rename file '\/home\/test\/test1\/redo03.log' to '\/oralog\/test1\/redo03.log';\r\n\r\nDatabase altered.\r\n\r\nSQL>\r\nSQL> alter database open;\r\n\r\nDatabase altered.\r\n\r\nSQL> select member from v$logfile;\r\n\r\nMEMBER\r\n--------------------------------------------------------------------------------\r\n\/oralog\/test1\/redo03.log\r\n\/oralog\/test1\/redo02.log\r\n\/oralog\/test1\/redo01.log\r\n\r\nSQL>\r\n\r\n\r\n<\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
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.<\/p>\n
Found […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[34],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/3062"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3062"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/3062\/revisions"}],"predecessor-version":[{"id":3063,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/3062\/revisions\/3063"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3062"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3062"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3062"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}