Oracle 11g database migration

Due to problems such as server replacement or hard disk damage, in some scenarios, we need to migrate the database.

Oracle database can be backed up in a variety of ways. If downtime is possible, it is recommended to use cold backup to migrate data files directly, which will be more efficient.

Because both hosts are Windows server, in order to facilitate the direct use of replicated files, due to special reasons, the pre-migrated data directory and the migrated data directory are inconsistent. After the data is migrated directly, startup will report the following error:

The SQL > startupORACLE routine has been started. Total System Global Area 1071333376 bytesFixed Size 1334380 bytesVariable Size 301990804 bytesDatabase Buffers 754974720 bytesRedo Buffers 13033472 bytes database loaded. ORA-01157: unable to identify / lock data file 1-see DBWR trace file ORA-01110: data file 1: d:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ SYSTEM01.DBF'

View the file path:

SQL > select name from vaulting controlFileShield NAME Murray Murray F:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ CONTROL01.CTLF:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ CONTROL02.CTLF:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ CONTROL03.CTLSQL > select name from v$datafile NAME---D:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ SYSTEM01.DBFD:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ SYSAUX01.DBFD:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ UNDOTBS01.DBFD:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ USERS01.DBFD:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ AGRISK.DBFSQL > select name from v$tempfile NAME---D:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ TEMP01.DBFD:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ AGRISK_TEMP.DBFSQL > select member from v$logfile MEMBER---D:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ REDO03.LOGD:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ REDO02.LOGD:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ REDO01.LOG

Stop the database:

SQL > shutdown immediate

Copy all the files of the instance under the oradata directory on the original host to the corresponding directory of the migrated host. Then start the database instance in mount on the new host:

SQL > startup mount

Except that the path of the control file is normal, all other file paths need to be modified. Change the original error path D disk to F disk:

SQL > alter database rename file'D:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ SYSTEM01.DBF' to'F:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ SYSTEM01.DBF'; database has been changed. SQL > alter database rename file'D:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ SYSAUX01.DBF' to'F:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ SYSAUX01.DBF'; database has been changed. SQL > alter database rename file'D:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ UNDOTBS01.DBF' to'F:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ UNDOTBS01.DBF'; database has been changed. SQL > alter database rename file'D:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ USERS01.DBF' to'F:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ USERS01.DBF'; database has been changed. SQL > alter database rename file'D:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ AGRISK.DBF' to'F:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ AGRISK.DBF'; database has been changed. SQL > alter database rename file'D:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ TEMP01.DBF' to'F:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ TEMP01.DBF'; database has been changed. SQL > alter database rename file'D:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ AGRISK_TEMP.DBF' to'F:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ AGRISK_TEMP.DBF'; database has been changed. SQL > alter database rename file'D:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ REDO01.LOG' to'F:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ REDO01.LOG'; database has been changed. SQL > alter database rename file'D:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ REDO02.LOG' to'F:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ REDO02.LOG'; database has been changed. SQL > alter database rename file'D:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ REDO03.LOG' to'F:\ APP\ ADMINISTRATOR\ ORADATA\ AGRISK\ REDO03.LOG'; database has been changed.

After confirming that the modification is successful, start the database:

The SQL > alter database open; database has changed.

Restart the database:

The SQL > shutdown immediate; database has been closed. The database has been uninstalled. The ORACLE routine has been closed. The SQL > startupORACLE routine has been started. Total System Global Area 1071333376 bytesFixed Size 1334380 bytesVariable Size 301990804 bytesDatabase Buffers 754974720 bytesRedo Buffers 13033472 bytes database loaded. The database is already open.

View the listening port. If the netca network listening port is not configured, you need to use the Net Cofiguration Assistant that comes with the system.