Oracle Cold Backup migrate database to other machine steps

The steps are simple: Guan-> copy to target machine-> boot to mount State rename->open database

Login to the source database host

Create map directory 192.168.1.2oradata map to Z: Disk

Check to see if the file is allowed to be written after mapping

Need to be aware of shutting down the firewall of the target database location server

Collecting Source Library Information

Instance name, version, block size, table space, data file

SELECT * from V$instance;

SELECT * from V$database;

SELECT * from V$tablespace;

SELECT * from V$datafile;

Organize the files you want to copy on the source library

The implementation document CP.SQL, which reads 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 name= ' spfile ';

Spool off

Generate Rename Script

The implementation document REN.SQL, which reads 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 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 Library

Host Lsnrctl Stop

Shutdown immediate

Set the Windows service to start manually (critical!!! Prevent replication of consistent files in the event of replication problems)

Copy control files, log files, data files,

Host Cpfile.bat

Startup

Host Lsnrctl Start

Copy pfile, Tnsname.ora, password file, Renfile.sql

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

Configure startup parameter file Pfile, modify control file location and path information

Boot to Mount State

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 to see if temporary files have

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 Log Group Location

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 1M;

Add members

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 log Group

ALTER DATABASE DROP LOGFILE GROUP 3;

Delete a member

ALTER DATABASE DROP LOGFILE member ' $HOME/oradata/u04/log3c.rdo ';

Clear Log

ALTER DATABASE Clear LOGFILE ' $HOME/oradata/u01/log2a.rdo ';

Additional Temporary files

ALTER tablespace temp ADD tempfile '/oradata/temp03.dbf ' SIZE 100M;

To delete a temporary file:

ALTER DATABASE tempfile '/oradata/temp02.dbf ' DROP including datafiles;

250 database 245G Copy time takes 9 hours