vicker313 tech blog

June 11, 2011

Copy or Move Oracle Database

Filed under: Oracle — Tags: , , — vicker313 @ 12:18 pm

Consider the following scenario: I got a oracle database that reside in /db/mydb. For some reason I need to copy or move the database to somewhere else, let’s say /db2/mydb2.

To do this, follow the steps:

  1. Shutdown the database either using service or sql statement: shutdown immediate.
  2. Copy or move the database to /db2 and rename the new database directory to mydb2.
  3. Edit the initialize parameter file in the mydb2 (something like initxxx.ora), change the all the paths to the new path (from /db/mydb to /db2/mydb2)
  4. Log in to sqlplus with NOLOG (run in terminal): sqlplus /nolog
  5. Connect to empty instance (run in SQLPLUS): connection / as sysdba
  6. Create SPFILE, aware that the path of the initialize parameter file might be vary (run in SQLPLUS): create spfile from pfile='/db2/mydb2/admin/pfile/initxxx.ora';
  7. Start database in mount mode (run in SQLPLUS): startup mount;
  8. Change the data file path to the new path (run in SQLPLUS): alter database rename file '/db/mydb/data.dbf', '/db/mydb/system.dbf' to '/db2/mydb2/data.dbf', '/db2/mydb2/system.dbf';
  9. Ensure you change all the data file path! Or else it will cause the orginal database cannot be startup! (Refer updates below)
  10. Open your database (run in SQLPLUS): alter database open;
  11. Now it is good to go.

If you want to run both databases in the same time, you need to rename the new database. Follow the following steps:

  1. Rename the new database to mydb2
  2. Remember to change the database name in mydb2 parameter file as well.
  3. Log into SQLPLUS. At this point, you need to aware the SID_NAME in your environment parameter, need to be point to the database that you want to log in. Now we want to log in to the original, so we set the SID_NAME to mydb (terminal): export SID_NAME=mydb
  4. Connect to empty instance (SQLPLUS): connect / as sysdba
  5. Create the SPFILE again using the original parameter file (SQLPLUS): create spfile from pfile='/db/mydb/admin/pfile/initxxx.ora';
  6. Now you should able to start your database (SQLPLUS): startup

Update 2013/2/24

You may find out the data files path using the following query (thanks to Concerned_Netizen):

  • select name from v$datafile
  • select member from v$tempfile
  • select member from v$logfile
Advertisements

2 Comments »

  1. My database since the instruction didn’t mention anything about alter the database’s log file’s path.

    Please make amend to these post. Thank you

    Comment by Concerned_Netizen — February 20, 2013 @ 4:26 pm

    • thank you for the comment

      Comment by vicker313 — February 20, 2013 @ 4:29 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: