重庆思庄Oracle、Redhat认证学习论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

搜索
查看: 266|回复: 0

[原创] Oracle数据库文件迁移步骤

[复制链接]
发表于 2021-1-28 18:03:19 | 显示全部楼层 |阅读模式
确定需要迁移的数据文件
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>

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛 ( 渝ICP备12004239号-4 )

GMT+8, 2022-1-20 22:17 , Processed in 0.086053 second(s), 21 queries .

重庆思庄学习中心论坛-重庆思庄科技有限公司论坛

© 2001-2020

快速回复 返回顶部 返回列表