Record oracle full-database migration in windows

According to the customer's requirements, we need to move the oracle Database Control Files, data files, and online log files from the local disk to the storage to prepare for the subsequent cluster work. Therefore, we have prepared two solutions, the simulation record is as follows:

Solution 1:

1shutdownimmediate Database
2 copydatafiles, logfiles, controlfiles, and tempfiles to the new directory
3startupnomount Database
4. Run altersystemsetcontrol_files to specify the new location of the control file.
5startupforcemount Database
6. Specify the new location of datafiles and tempfiles
7. Specify the new location of the redo log file
8alterdatabaseopen Database

Solution 2 steps are as follows:

1. Generate the statement for recreating the control file
2shutdownimmediate Database
3 copydatafiles, logfiles, tempfiles, to a new location
4. Run altersystemsetcontrol_files to change the point of the control file in the spfile.
5. Recreate the control file
6. Add a data file for the temp tablespace

The implementation process of solution 1 is as follows:

1. query the current data file and log file as follows:

SQL> selectnamefromv $ datafile;

NAME
--------------------------------------------------
G: \ DATA \ ERP \ SYSTEM01.DBF
G: \ DATA \ ERP \ SYSAUX01.DBF
G: \ DATA \ ERP \ UNDOTBS01.DBF
G: \ DATA \ ERP \ USERS01.DBF

SQL> selectgroup #, memberfromv $ logfile;

GROUP # MEMBER
------------------------------------------------------------
2G: \ DATA \ ERP \ REDO02.LOG
1G: \ DATA \ ERP \ REDO01.LOG
3G: \ DATA \ ERP \ REDO03.LOG

2. Close the database consistently:

SQL> shutdownimmediate;
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled.
SQL>

3. Copy data files, log files, temporary data files, and control files to the storage directory:

C: \ Users \ Administrator. ZX> copyG: \ DATA \ ERP \ *. * E: \ DATA \ ERP \
G: \ DATA \ ERP \ CONTROL01.CTL
G: \ DATA \ ERP \ CONTROL02.CTL
G: \ DATA \ ERP \ REDO01.LOG
G: \ DATA \ ERP \ REDO02.LOG
G: \ DATA \ ERP \ REDO03.LOG
G: \ DATA \ ERP \ SYSAUX01.DBF
G: \ DATA \ ERP \ SYSTEM01.DBF
G: \ DATA \ ERP \ TEMP01.DBF
G: \ DATA \ ERP \ UNDOTBS01.DBF
G: \ DATA \ ERP \ USERS01.DBF
10 files have been copied.

4. Run altersystemsetcontrol_files to specify the new location of the control file.

C: \ Users \ Administrator. ZX> sqlplus/assysdba

SQL * Plus: Release11.2.0.1.0Productionon Friday 17: 29: 492013

Copyright (c) 1982,2010, Oracle. Allrightsreserved.

Already connected to the idle routine.

SQL> startupnomount;
The ORACLE routine has been started.

TotalSystemGlobalArea430075904bytes
FixedSize2176448bytes
VariableSize301992512bytes
DatabaseBuffers117440512bytes
RedoBuffers8466432bytes

SQL> showparametercontrol_files;

NAMETYPEVALUE
Bytes ----------------------------------------------------------------------------------------
Control_filesstringG: \ DATA \ ERP \ CONTROL01.CTL, G :\
DATA \ ERP \ CONTROL02.CTL

SQL> altersystemsetcontrol_files = 'e: \ DATA \ ERP \ CONTROL01.CTL ', 'e: \ DATA \ ERP \ CONTROL02.CTL' scope = spfile;

The system has been changed.

SQL> startupforcemount;
The ORACLE routine has been started.

TotalSystemGlobalArea430075904bytes
FixedSize2176448bytes
VariableSize301992512bytes
DatabaseBuffers117440512bytes
RedoBuffers8466432bytes
The database has been loaded.
SQL> showparametercontrol_files;

NAMETYPEVALUE
-----------------------------------------------------------------------------
Control_filesstringE: \ DATA \ ERP \ CONTROL01.CTL, E :\
DATA \ ERP \ CONTROL02.CTL
SQL>

5. Specify the new location of the data file and temporary data file

SQL> alterdatabaserenamefile 'G: \ DATA \ ERP \ sysaux01.dbf ',
2 'G: \ DATA \ ERP \ system01.dbf ',
3 'G: \ DATA \ ERP \ undotbs01.dbf ',
4 'G: \ DATA \ ERP \ users01.dbf ',
5'G: \ DATA \ ERP \ temp01.dbf'
6to 'e: \ DATA \ ERP \ sysaux01.dbf ',
7 'e: \ DATA \ ERP \ system01.dbf ',
8 'e: \ DATA \ ERP \ undotbs01.dbf ',
9 'e: \ DATA \ ERP \ users01.dbf ',
10'E: \ DATA \ ERP \ temp01.dbf ';

The database has been changed.

6. Specify the new location of the log file:

SQL> alterdatabaserenamefile 'G: \ DATA \ ERP \ redo01.log ',
2 'G: \ DATA \ ERP \ REDO02.LOG ',
3 'G: \ DATA \ ERP \ redo03.log'
4to 'e: \ DATA \ ERP \ redo01.log ',
5 'e: \ DATA \ ERP \ REDO02.LOG ',
6 'e: \ DATA \ ERP \ redo03.log ';

The database has been changed.

SQL> selectfile #, name, statusfromv $ datafile;

FILE # NAMESTATUS
-----------------------------------------------------------------------------
1E: \ DATA \ ERP \ SYSTEM01.DBFSYSTEM
2E: \ DATA \ ERP \ SYSAUX01.DBFONLINE
3E: \ DATA \ ERP \ UNDOTBS01.DBFONLINE
4E: \ DATA \ ERP \ USERS01.DBFONLINE

SQL> selectgroup #, memberfromv $ logfile;

GROUP # MEMBER
------------------------------------------------------------
2E: \ DATA \ ERP \ REDO02.LOG
1E: \ DATA \ ERP \ REDO01.LOG
3E: \ DATA \ ERP \ REDO03.LOG

SQL> selectname, statusfromv $ tempfile;

NAMESTATUS
-------------------------------------------------------------------
E: \ DATA \ ERP \ TEMP01.DBFONLINE

The migration process of the second solution is as follows:

1. Generate the statement for recreating the control file:

SQL> selectopen_modefromv $ database;

OPEN_MODE
--------------------
READWRITE

SQL> alterdatabasebackupcontrolfiletotrace;

The database has been changed.

SQL> oradebugsetmypid
Processed statements
SQL> oradebugtracefile_name
C: \ app \ diag \ rdbms \ erp \ trace \ erp_ora_2724.trc

SQL> exit
From OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-64 bitProduction
WiththePartitioning, OLAP, DataMiningandRealApplicationTestingoptions disconnected

C: \ Users \ Administrator. ZX> startc: \ app \ diag \ rdbms \ erp \ trace \ erp_ora_2724.trc
Open the trace file, find the following control file reconstruction statement, and save it to the control. SQL file.

C: \ Users \ Administrator. ZX> e:

E: \> typecontrol. SQL
STARTUPNOMOUNT
CREATECONTROLFILEREUSEDATABASE "ERP" NORESETLOGSARCHIVELOG
MAXLOGFILES16
MAXLOGMEMBERS3
Maxdatafile100
MAXINSTANCES8
MAXLOGHISTORY292
LOGFILE
GROUP1 'e: \ DATA \ ERP \ redo01.log' SIZE200MBLOCKSIZE512,
GROUP2 'e: \ DATA \ ERP \ REDO02.LOG 'SIZE200MBLOCKSIZE512,
GROUP3 'e: \ DATA \ ERP \ redo03.log' SIZE200MBLOCKSIZE512
-- STANDBYLOGFILE
DATAFILE
'E: \ DATA \ ERP \ system01.dbf ',
'E: \ DATA \ ERP \ sysaux01.dbf ',
'E: \ DATA \ ERP \ undotbs01.dbf ',
'E: \ DATA \ ERP \ users01.dbf'
CHARACTERSETZHS16GBK
;

2. Close the database consistently:

E: \> sqlplus/assysdba

SQL * Plus: Release11.2.0.1.0Productionon Friday September 20

Copyright (c) 1982,2010, Oracle. Allrightsreserved.

Connect:
OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-64 bitProduction
WiththePartitioning, OLAP, DataMiningandRealApplicationTestingoptions

SQL> shutdownimmediate;
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled.

3 copydatafiles, logfiles, and tempfiles to the new location

SQL> exit
From OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-64 bitProduction
WiththePartitioning, OLAP, DataMiningandRealApplicationTestingoptions disconnected

E: \> copyE: \ data \ erp \ *. * G: \ data \ erp \
E: \ data \ erp \ CONTROL01.CTL
E: \ data \ erp \ CONTROL02.CTL
E: \ data \ erp \ REDO01.LOG
E: \ data \ erp \ REDO02.LOG
E: \ data \ erp \ REDO03.LOG
E: \ data \ erp \ SYSAUX01.DBF
E: \ data \ erp \ SYSTEM01.DBF
E: \ data \ erp \ TEMP01.DBF
E: \ data \ erp \ UNDOTBS01.DBF
E: \ data \ erp \ USERS01.DBF
10 files have been copied.

4. Run altersystemsetcontrol_files to change the point of the control file in the spfile.

E: \> sqlplus/assysdba

SQL * Plus: Release11.2.0.1.0Productionon Friday September 20

Copyright (c) 1982,2010, Oracle. Allrightsreserved.

Already connected to the idle routine.

SQL> startupnomount;
The ORACLE routine has been started.

TotalSystemGlobalArea430075904bytes
FixedSize2176448bytes
VariableSize289409600bytes
DatabaseBuffers130023424bytes
RedoBuffers8466432bytes
SQL> showparametercontrol_files;

NAMETYPEVALUE
Bytes ----------------------------------------------------------------------------------------
Control_filesstringE: \ DATA \ ERP \ CONTROL01.CTL, E :\
DATA \ ERP \ CONTROL02.CTL

SQL> altersystemsetcontrol_files = 'G: \ DATA \ ERP \ CONTROL01.CTL ', 'G: \ DATA \ ERP \ CONTROL02.CTL' scope = spfile;

The system has been changed.

5. Recreate the control file

SQL> shutdownabort;
The ORACLE routine has been disabled.
SQL> hosttypee: \ control. SQL
STARTUPNOMOUNT
CREATECONTROLFILEREUSEDATABASE "ERP" NORESETLOGSARCHIVELOG
MAXLOGFILES16
MAXLOGMEMBERS3
Maxdatafile100
MAXINSTANCES8
MAXLOGHISTORY292
LOGFILE
GROUP1 'e: \ DATA \ ERP \ redo01.log' SIZE200MBLOCKSIZE512,
GROUP2 'e: \ DATA \ ERP \ REDO02.LOG 'SIZE200MBLOCKSIZE512,
GROUP3 'e: \ DATA \ ERP \ redo03.log' SIZE200MBLOCKSIZE512
-- STANDBYLOGFILE
DATAFILE
'E: \ DATA \ ERP \ system01.dbf ',
'E: \ DATA \ ERP \ sysaux01.dbf ',
'E: \ DATA \ ERP \ undotbs01.dbf ',
'E: \ DATA \ ERP \ users01.dbf'
CHARACTERSETZHS16GBK
;
SQL> @ e: \ control. SQL
The ORACLE routine has been started.

TotalSystemGlobalArea430075904bytes
FixedSize2176448bytes
VariableSize289409600bytes
DatabaseBuffers130023424bytes
RedoBuffers8466432bytes

The control file has been created.

SQL> selectopen_modefromv $ database;

OPEN_MODE
--------------------
MOUNTED

SQL> colnamefora50
SQL> selectnamefromv $ datafile;

NAME
--------------------------------------------------
E: \ DATA \ ERP \ SYSTEM01.DBF
E: \ DATA \ ERP \ SYSAUX01.DBF
E: \ DATA \ ERP \ UNDOTBS01.DBF
E: \ DATA \ ERP \ USERS01.DBF

SQL> colmemberfora50
SQL> selectmemberfromv $ logfile;

MEMBER
--------------------------------------------------
E: \ DATA \ ERP \ REDO01.LOG
E: \ DATA \ ERP \ REDO03.LOG
E: \ DATA \ ERP \ REDO02.LOG

SQL> selectnamefromv $ tempfile;

Unselected row

SQL> selectnamefromv $ controlfile;

NAME
--------------------------------------------------
G: \ DATA \ ERP \ CONTROL01.CTL
G: \ DATA \ ERP \ CONTROL02.CTL

6. Add the data file temp01 to the temp tablespace.

SQL> alterdatabaseopen;

The database has been changed.

SQL> hostdirG: \ DATA \ ERP
The volume in the drive G is erp
The serial number of the volume is the F048-8EE6

G: \ DATA \ ERP directory

2013/09/<DIR>.
2013/09/<DIR> ..
2013/09/, 076,160 CONTROL01. CTL
2013/09/, 076,160 CONTROL02. CTL
2013/09/8:5252209, 715,712 REDO01. LOG
2013/09/8:5252209, 715,712 REDO02. LOG
2013/09/8:5252209, 715,712 REDO03. LOG
2013/09/8:5252503, 324,672 SYSAUX01. DBF
2013/09/2018: 52713,039,872 SYSTEM01. DBF
2013/09/2017: 1830,416,896 TEMP01. DBF
2013/09/2018: 5273,408,512 UNDOTBS01. DBF
2013/09/2018:525, 251,072 USERS01. DBF
10 files in 1,974,740,480 bytes
2 directories, 7,482,286,080 available bytes

SQL> altertablespacetempaddtempfile 'G: \ DATA \ ERP \ temp01.dbf ';

The tablespace has been changed.

SQL> selectnamefromv $ tempfile;

NAME
--------------------------------------------------
G: \ DATA \ ERP \ TEMP01.DBF

Note: Check whether the tablespace, data file, and Log File status is normal before you close data migration.

This article is from the "myblog" blog, please be sure to keep this source http://jiujian.blog.51cto.com/444665/1301425