Procedure for manual Oracle database migration

Sometimes we encounter such a situation where the existing database needs to be transferred from one machine to another. Generally, we use export and import. However, if the database contains a large amount of data and the size of the data file is large, problems may occur during the export and import process, and we cannot tolerate the long process. In this case, we can simply use the copy command of the operating system to directly transfer the database. The following example is performed in Oracle9.2.0.1 on Redhat Fedora Core 1. Other operating systems and Oracle versions are also applicable. Assume that our database is on server A, $ ORACLE_BASE is/oracle, and $ ORACLE_HOME is/oracle/product/9.2.0. Now we want to transfer this database to server B, and the new $ ORACLE_BASE is/u01/oracle, and $ ORACLE_HOME is/u01/oracle/product/9.2.0. The SID is oralinux.
The procedure is as follows:

1. Install Oracle on server B. Do not create a database during installation. The user's profile can be copied directly from server A. Note that you need to modify the original $ ORACLE_HOME location to the current location. 2. If Oracle on server A uses spfile, create pfile: create pfile from spfile based on this spfile; 3. Close the database on server A and shutdown immediate. If the database cannot be shut down, you can use online backup. In this case, the database must be in archivelog mode. The transfer principle is the same as that allowed to be closed. We will not discuss it here. 4. back up all data files on server A, including the following: a) All temporary datafile files can not be copied, such as temp01.dbf) B) all online redo logc) All archive redo logd) all control filee) $ ORACLE_HOME/dbs/init
 
  
. Ora this file was created in step 2 above) f) $ ORACLE_HOME/dbs/orapw
  
   
This is password file) g) $ ORACLE_HOME/network/listener. ora this step can be omitted and can be regenerated later) h) $ ORACLE_BASE/admin/
   
    
/, Which is generally udump, cdump, or bdump. You can also manually create a directory later)
   
  
 

5. Upload all the backup files to the corresponding directory of server B through FTP or other tools. For example, if the original $ ORACLE_BASE/oradata/<SID>/system01.dbf on server A is uploaded to server B, $ ORACLE_BASE/oradata/ /System01.dbf, ensure that the logical structure of the directory is the same. In fact, the physical structure has changed. A $ ORACLE_BASE is/oracle, and B is/u01/oracle ). Other files are processed in the same way. If there is no corresponding directory on server B, create it manually.
6. Edit the init file uploaded to server B. In the. ora file, change all the directory structures involved to the current directory structure in B. For example, in this example, change/oracle to/u01/oracle.

7. On server B, sqlplus "/as sysdba" prompts to enter the idle process. 8. create spfile based on the modified pfile, create spfile from pfile; 9. start the database to the mount status, startup mount; 10. modify the location of all data files in the control file. You can use spool to generate all SQL statements: SQL> alter database rename file '/oracle/oradata/oralinux/system01.dbf '2 to'/u01/oracle/oradata/oralinux/system01.dbf '; modify all data file locations according to the preceding syntax. 11. now the database can be used. alter database open; 12. add temporary files to the TEMP tablespace: alter tablespace TEMP add tempfile ''/u01/oracle/oradata/oralinux/temp01.dbf' size 20 M;

13. there will be some follow-up work, including updating the listening configuration file on Database B, because 9i instances can already be automatically registered to the listener. If it is to listen first and then start the database, then it will be registered immediately. If you start the database and then start listening, it will be slightly delayed before registration), modify the/etc/oratab file to use the dbstart and dbshut scripts. In this way, the database is completely transferred.