Oracle数据库文件迁移步骤

jiawang

确定需要迁移的数据文件

SQL>select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/system01.dbf

/u01/app/oracle/oradata/orcl/sysaux01.dbf

/u01/app/oracle/oradata/orcl/undotbs01.dbf

/u01/app/oracle/oradata/orcl/users01.dbf

SQL>  select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/control01.ctl

/u01/app/oracle/oradata/orcl/control02.ctl

SQL>select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/redo03.log

/u01/app/oracle/oradata/orcl/redo02.log

/u01/app/oracle/oradata/orcl/redo01.log

SQL>select name from v$tempfile;

NAME

--------------------------------------

/u01/app/oracle/oradata/orcl/TEMP01.DBF

创建pfile

SQL>create pfile='/home/oracle/orcl20210128.ora' from spfile;

Filecreated.

迁移数据库文件

将/u01/app/oracle/oradata/orcl/的文件复制到/home/oracle/orcl/下

修改控制文件位置

[oracle@sztech~]$ more orcl20210128.ora

orcl.__db_cache_size=331350016

orcl.__java_pool_size=4194304

orcl.__large_pool_size=8388608

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASEset from environment

orcl.__pga_aggregate_target=335544320

orcl.__sga_target=494927872

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=142606336

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)'

*.memory_target=830472192

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

[oracle@sztech~]$ vim orcl20210128.ora

orcl.__db_cache_size=331350016

orcl.__java_pool_size=4194304

orcl.__large_pool_size=8388608

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASEset from environment

orcl.__pga_aggregate_target=335544320

orcl.__sga_target=494927872

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=142606336

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/home/oracle/orcl/control01.ctl','/home/oracle/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)'

*.memory_target=830472192

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

~

关闭数据库

SQL>shutdown immediate;

Databaseclosed.

Databasedismounted.

ORACLEinstance shut down.

迁移数据库文件

[oracle@sztechorcl]$ cp *.* /home/oracle/orcl

[oracle@sztechorcl]$ pwd

/u01/app/oracle/oradata/orcl

验证

[oracle@sztechorcl]$ ll

total1480268

-rw-r-----1 oracle dba   9748480 Jan 28 17:33control01.ctl

-rw-r-----1 oracle dba   9748480 Jan 28 17:33control02.ctl

-rw-r-----1 oracle dba  52429312 Jan 28 17:33redo01.log

-rw-r-----1 oracle dba  52429312 Jan 28 17:33redo02.log

-rw-r-----1 oracle dba  52429312 Jan 28 17:33redo03.log

-rw-r-----1 oracle dba 524296192 Jan 28 17:34 sysaux01.dbf

-rw-r-----1 oracle dba 775954432 Jan 28 17:34 system01.dbf

-rw-r-----1 oracle dba  20979712 Jan 28 17:34temp01.dbf

-rw-r-----1 oracle dba  31465472 Jan 28 17:34undotbs01.dbf

-rw-r-----1 oracle dba   5251072 Jan 28 17:34users01.dbf

[oracle@sztechorcl]$

修改sysaux文件名字

[oracle@sztechorcl]$ cp sysaux01.dbf sysaux01.dbf1

[oracle@sztechorcl]$ ll

total1992276

-rw-r-----1 oracle dba   9748480 Jan 28 17:40control01.ctl

-rw-r-----1 oracle dba   9748480 Jan 28 17:40control02.ctl

-rw-r-----1 oracle dba  52429312 Jan 28 17:33redo01.log

-rw-r-----1 oracle dba  52429312 Jan 28 17:33redo02.log

-rw-r-----1 oracle dba  52429312 Jan 28 17:33redo03.log

-rw-r-----1 oracle dba 524296192 Jan 28 17:34 sysaux01.dbf

-rw-r----- 1 oracle dba 524296192 Jan 28 17:40 sysaux01.dbf1

-rw-r-----1 oracle dba 775954432 Jan 28 17:34 system01.dbf

-rw-r-----1 oracle dba  20979712 Jan 28 17:34temp01.dbf

-rw-r-----1 oracle dba  31465472 Jan 28 17:34undotbs01.dbf

-rw-r-----1 oracle dba   5251072 Jan 28 17:34users01.dbf

使用pfile启动数据库到mount

[oracle@sztechorcl]$ sqlplus / as sysdba

SQL*Plus:Release 11.2.0.4.0 Production on Thu Jan 28 17:38:09 2021

Copyright(c) 1982, 2013, Oracle.  All rightsreserved.

Connectedto an idle instance.

SQL>startup pfile='/home/oracle/orcl20210128.ora' mount;

ORACLEinstance started.

TotalSystem Global Area  826753024 bytes

FixedSize                  2257760 bytes

VariableSize             536874144 bytes

DatabaseBuffers          285212672 bytes

RedoBuffers                2408448 bytes

Databasemounted.

SQL>

rename文件

SQL> alter database rename file '/u01/app/oracle/oradata/orcl/sysaux01.dbf'to '/home/oracle/orcl/sysaux01.dbf1';

Databasealtered.

SQL>alter database rename file '/u01/app/oracle/oradata/orcl/system01.dbf' to '/home/oracle/orcl/system01.dbf';

Databasealtered.

SQL>alter database rename file '/u01/app/oracle/oradata/orcl/undotbs01.dbf' to '/home/oracle/orcl/undotbs01.dbf';

Databasealtered.

SQL>alter database rename file '/u01/app/oracle/oradata/orcl/users01.dbf' to '/home/oracle/orcl/users01.dbf';

Databasealtered.

SQL>alter database rename file '/u01/app/oracle/oradata/orcl/redo03.log' to '/home/oracle/orcl/redo03.log';

Databasealtered.

SQL>alter database rename file '/u01/app/oracle/oradata/orcl/redo02.log' to '/home/oracle/orcl/redo02.log';

Databasealtered.

SQL>alter database rename file '/u01/app/oracle/oradata/orcl/redo01.log' to '/home/oracle/orcl/redo01.log';

Databasealtered.

SQL>alter database rename file '/u01/app/oracle/oradata/orcl/temp01.dbf' to'/home/oracle/orcl/temp01.dbf';

Databasealtered.

启动数据库到open

SQL>alter database open;

Databasealtered.

重建spfile

SQL>create spfile from pfile='/home/oracle/orcl20210128.ora';

Filecreated.

验证数据文件

SQL>select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/home/oracle/orcl/system01.dbf

/home/oracle/orcl/sysaux01.dbf1

/home/oracle/orcl/undotbs01.dbf

/home/oracle/orcl/users01.dbf

SQL>select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

/home/oracle/orcl/control01.ctl

/home/oracle/orcl/control02.ctl

SQL>select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/home/oracle/orcl/redo03.log

/home/oracle/orcl/redo02.log

/home/oracle/orcl/redo01.log

SQL>select name from v$tempfile;

NAME

--------------------------------------------------------------------------------

/home/oracle/orcl/temp01.dbf

SQL>