Oracle Database transfer from server A to server B

Oracle Database transfer from server A to server B

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 based on this spfile:
Create pfile from spfile;

3. Shut down the database on server A and shutdown immediate. If the database cannot be closed,
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 disabled. We will not discuss it here.

4. Back up all data files on server A, including the following:
A) All datafiles (temporary files can not be copied, such as temp01.dbf)
B) all online redo logs
C) All archive redo logs
D) All control files
E) $ 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) all directories under $ ORACLE_BASE/admin //, generally udump, cdump,
Bdump (this step can be omitted. You can also create a directory manually 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 ensures that the logical structure of the directory is the same, in fact, the physical structure has changed (on A, $ ORACLE_BASE is/oracle, and on 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. 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 and create spfile from pfile based on the modified pfile;

9. Start the database to the mount state and start up the 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 are also some follow-up work, including updating the listening configuration file on Database B (in fact, you don't need to worry about it, because 9i instances can already be automatically registered to the listening program, if you start listening and then start the database, it will be registered immediately. If you start the database first and then start listening, it will be slightly delayed before registration), modify the/etc/oratab file, to be able to use the dbstart and dbshut scripts. In this way, the database is completely transferred.