迁移Oracle数据库

查看当前数据库的数据文件，临时文件，日志文件，控制文件，参数文件等信息。

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/sysdata/jingyu/system01.dbf
/oradata/sysdata/jingyu/sysaux01.dbf
/oradata/sysdata/jingyu/undotbs01.dbf
/oradata/sysdata/jingyu/users01.dbf

SQL> select name from v$tempfile; 

NAME
--------------------------------------------------------------------------------
/oradata/sysdata/jingyu/temp01.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oradata/sysdata/jingyu/redo03.log
/oradata/sysdata/jingyu/redo02.log
/oradata/sysdata/jingyu/redo01.log

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oradata/sysdata/jingyu/control01.ctl
/opt/app/oracle/fast_recovery_area/jingyu/control02.ctl

SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /opt/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfilejingyu.ora

建立一个文件夹来存放需要拷贝出来的数据文件，建完之后需要修改文件夹属主及读写权限，如下：

[root@chd106 ~]# cd /orcl
[root@chd106 ~]# mkdir oracle
[root@chd106 ~]# mkdir oradata
[root@chd106 ~]# mkdir orcl
[root@chd106 ~]# chown -R oracle:oinstall /orcl/oracle/oradata/orcl/
[root@chd106 ~]# chmod -R 755 /orcl/oracle/oradata/orcl/

关闭数据库监听

[oracle@chd106 ~]$ lsnrctl stop

关掉数据库，并启动数据库到mount状态；

SQL> shutdown immediate;
SQL>startup mount;

拷贝数据库文件

重定向数据库的所有数据文件、日志文件路径，然后正常打开数据库。

SQL> alter database rename file '/oradata/sysdata/jingyu/system01.dbf' to '/usr2/oradata/sysdata/jingyu/system01.dbf';

Database altered.

SQL> alter database rename file '/oradata/sysdata/jingyu/sysaux01.dbf' to '/usr2/oradata/sysdata/jingyu/sysaux01.dbf';

Database altered.

SQL> alter database rename file '/oradata/sysdata/jingyu/undotbs01.dbf' to '/usr2/oradata/sysdata/jingyu/undotbs01.dbf';   

Database altered.

SQL> alter database rename file '/oradata/sysdata/jingyu/temp01.dbf' to '/usr2/oradata/sysdata/jingyu/temp01.dbf';

Database altered.

SQL> alter database rename file '/oradata/sysdata/jingyu/users01.dbf' to '/usr2/oradata/sysdata/jingyu/users01.dbf';

Database altered.

SQL> alter database rename file '/oradata/sysdata/jingyu/redo01.log' to '/usr2/oradata/sysdata/jingyu/redo01.log';

Database altered.

SQL> alter database rename file '/oradata/sysdata/jingyu/redo02.log' to '/usr2/oradata/sysdata/jingyu/redo02.log';

Database altered.

SQL> alter database rename file '/oradata/sysdata/jingyu/redo03.log' to '/usr2/oradata/sysdata/jingyu/redo03.log';

Database altered.

SQL> alter database open;

Database altered.

核查各文件路径没有问题，重启数据库实例

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1.3429E+10 bytes
Fixed Size                  2241064 bytes
Variable Size            6744444376 bytes
Database Buffers         6677331968 bytes
Redo Buffers                4636672 bytes
Database mounted.
Database opened.

参考：
https://www.cnblogs.com/jyzhao/p/3968504.html
https://blog.csdn.net/sishuhai/article/details/78115572

最后编辑于：2019.04.09 11:18:54