Steps for migrating a database to another machine through oracle cold backup

The procedure is simple: Shut down the source database-copy to the target machine-start to mount state rename-open Database login source Database Host build ing directory 192.168.1.2oradata ing to z: after the disk ing is completed, check whether the file can be written. Pay attention to disable the firewall of the target database location to collect the source database information. Instance name, version, and block size.

The procedure is simple: Shut down the source database-copy to the target machine-start to mount state rename-open Database login source Database Host build ing directory 192.168.1.2oradata ing to z: after the disk ing is completed, check whether the file can be written. Pay attention to disable the firewall of the target database location to collect the source database information. Instance name, version, and block size.

The procedure is simple: Shut down the source database-> copy to the target machine-> start to mount state rename-> open Database

Log on to the host where the source database is located

Create a 192.168.ing directory 192.168.1.2oradata to map to z: Disk

Check whether file writing is allowed after the ing.

Disable the firewall of the target database location server.

Collect source database information

Instance name, version, block size, tablespace, Data File

Select * from v $ instance;

Select * from v $ database;

Select * from v $ tablespace;

Select * from v $ datafile;

Sort the files to be copied in the source database

Execute the cp. SQL file. Its content is as follows:

Set linesize 130 pagesize 2000

Set trimspool on

Set echo off

Set verify off

Set timing off

Set feedback off

Set head off

Set echo off

Spool cpfile. bat

Select 'copy' | name | 'z: orcl '| substr (name, instr (name, '',-1) + 1) cmd from v $ controlfile

Union all

Select 'copy' | member | 'z: orcl '| substr (member, instr (member, '',-1) + 1) cmd from v $ logfile

Union all

Select 'copy' | name | 'z: orcl '| substr (name, instr (name, '',-1) + 1) cmd from v $ datafile

Union all

Select 'copy' | name | 'z: orcl '| substr (name, instr (name, '',-1) + 1) cmd from v $ tempfile

Union all

Select 'copy' | value | 'z: orcl '| substr (value, instr (value, '',-1) + 1) cmd from v $ parameter where;

Spool off

Generate renaming script

Execute the file ren. SQL with the following content:

Set linesize 130 pagesize 2000

Set trimspool on

Set echo off

Set verify off

Set timing off

Set feedback off

Set head off

Set echo off

Spool renfile. SQL

Select 'alter database rename file' | member | ''' to ''d: oradataorcl '| substr (member, instr (member, '',-1) + 1) | '''; 'cmd from v $ logfile

Union all

Select 'alter database rename file ''' | name | ''' to ''d: oradataorcl '| substr (name, instr (name, '',-1) + 1) | '''; 'cmd from v $ datafile

Union all

Select 'alter database rename file ''' | name | ''' to ''d: oradataorcl '| substr (name, instr (name, '',-1) + 1) | '''; 'cmd from v $ tempfile;

Spool off

Create pfile from spfile;

Close source database

Host lsnrctl stop

Shutdown immediate

Set windows service to manual start (key !!! In case of a copy problem, you can copy the same file again)

Copy control files, log files, and data files,

Host cpfile. bat

Startup

Host lsnrctl start

Copy pfile, tnsname. ora, password file, and renfile. SQL

Modify the control file content in the parameter file of the target database

Configure the startup parameter file pfile and modify the control file location and path information.

Start to mount status

Sqlplus "/as sysdba"

Startup mount pfile =? /Database/initorcl. ora

Modify file location

SQL> @ renfile. SQL

Open Database

Create spfile from pfile;

Host lsnrctl start

Check whether temporary files exist

Connection test

Reference command:

Oradim-NEW-SID test-STARTMODE manual-PFILE "D: Oracleadmintestpfileinittest. ora"

Alter database rename file 'C: oracleproductoradataexample. dbf'

TO 'C: oracleproductoradatademos. dbf'

Modify the location of a log Group

Alter datbase [database}

Rename file 'filename' [, 'filename']…

TO 'filename']…

Create a log Group

Alter database add logfile group 3

('$ HOME/ORADATA/u01/log3a. rdo ',

'$ HOME/ORADATA/u02/log3b. rdo ')

SIZE 1 M;

Add Member

ALTER DATABASE ADD LOGFILE MEMBER

'$ HOME/ORADATA/u04/log1c. rdo' to group 1,

'$ HOME/ORADATA/u04/log2c. rdo' to group 2,

'$ HOME/ORADATA/u04/log3c. rdo' to group 3;

Delete a log Group

Alter database drop logfile group 3;

Delete a member

Alter database drop logfile member '$ HOME/ORADATA/u04/log3c. rdo ';

Clear logs

Alter database clear logfile '$ HOME/ORADATA/u01/log2a. rdo ';

Add temporary files

Alter tablespace temp add tempfile '/oradata/temp03.dbf' SIZE 100 M;

Delete temporary files:

Alter database tempfile '/oradata/temp02.dbf' drop including datafiles;

250 it takes 9 hours to copy 245 GB of Database