oracle 控制文件迁移,ORACLE 12C 冷备份方式的数据迁移（重建控制文件）

实验对象：两台linux单机的oracle 12C 数据库(大版本和小版本都一致)，数据迁移采取冷备份的方式。目标机器的路径跟源库不一致，需要重建controlfile。

迁移步骤

首先关闭监听，和kill掉连接的应用

ps -ef | grep LOCAL=NO | awk '{print ($2)}' | xargs kill -9

创建pfile

create pfile='/tmp/inittest.ora1012' from spfile;

查看数据文件，日志文件的路径，日志文件路径

SQL> set line 200

SQL> col FILE_NAME for a80

SQL>  select TABLESPACE_NAME,file_name from  dba_data_files;

TABLESPACE_NAME                FILE_NAME

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

SYSTEM                         /u01/app/oracle/oradata/test/system01.dbf

SYSAUX                         /u01/app/oracle/oradata/test/sysaux01.dbf

UNDOTBS1                       /u01/app/oracle/oradata/test/undotbs01.dbf

USERS                          /u01/app/oracle/oradata/test/users01.dbf

QWERTY                         /u01/app/oracle/oradata/test/qwerty.dbf

TESTBIG                        /u01/app/oracle/oradata/test/testbig.dbf

DATA                           /u01/app/oracle/oradata/test/data_01.dbf

SQL> select TABLESPACE_NAME,file_name from  dba_temp_files;

TABLESPACE_NAME                FILE_NAME

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

TEMP                           /u01/app/oracle/oradata/test/temp01.dbf

TEMP_ASYNC                     /u01/app/oracle/oradata/test/temp_async_01.dbf

SQL> set line 200

SQL>  col MEMBER for a80

SQL> select GROUP#,MEMBER from v$logfile;

GROUP# MEMBER

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

1 /u01/app/oracle/oradata/test/redo01.log

2 /u01/app/oracle/oradata/test/redo02.log

3 /u01/app/oracle/oradata/test/redo03.log

SQL> show parameter control_files

NAME                                 TYPE        VALUE

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

control_files                        string      /u01/app/oracle/oradata/test/c

ontrol01.ctl, /u01/app/oracle/

fast_recovery_area/test/contro

l02.ctl

关闭数据库后，拷贝参数文件，数据文件，日志文件，控制文件。

shutdown immediate

将所有需要的文件拷到本地，以便更好的传输。

cp ......

拷贝文件到目标服务器上。

scp ...

在目标服务器上修改pfile的信息。

源库

[oracle@test dbs]$ vi inittest.ora

test.__data_transfer_cache_size=0

test.__db_cache_size=339738624

test.__java_pool_size=4194304

test.__large_pool_size=8388608

test.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

test.__pga_aggregate_target=293601280

test.__sga_target=545259520

test.__shared_io_pool_size=16777216

test.__shared_pool_size=167772160

test.__streams_pool_size=0

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

*.audit_trail='db'

*.compatible='12.1.0.2.0'

*.control_files='/u01/app/oracle/oradata/test/control01.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='test'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4815m

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

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

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=800m

*.open_cursors=300

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

以下操作在目标库上执行:

修改了控制文件的路径

*.control_files='/u01/app/oracle/oradata/test/controlfile/control01.ctl'

重建控制文件

startup mount

alter database backup controlfile to trace as '/tmp/crontol_trace';

cat /tmp/crontol_trace | grep -v ^- | grep -v ^$ > /tmp/ctl.sql

修改控制文件里的路径,因为我们有onlinelog所以选择noresetlog模式

vi /tmp/ctl.sql

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS  ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/test/onlinelog/redo01.log'  SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/test/onlinelog/redo02.log'  SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/test/onlinelog/redo03.log'  SIZE 50M BLOCKSIZE 512

DATAFILE

'/u01/app/oracle/oradata/test/datafile/system01.dbf',

'/u01/app/oracle/oradata/test/datafile/sysaux01.dbf',

'/u01/app/oracle/oradata/test/datafile/undotbs01.dbf',

'/u01/app/oracle/oradata/test/datafile/users01.dbf',

'/u01/app/oracle/oradata/test/datafile/qwerty.dbf',

'/u01/app/oracle/oradata/test/datafile/testbig.dbf',

'/u01/app/oracle/oradata/test/datafile/data_01.dbf'

CHARACTER SET AL32UTF8

;

RECOVER DATABASE

ALTER SYSTEM ARCHIVE LOG ALL;

ALTER DATABASE OPEN;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/test/datafile/temp01.dbf' REUSE;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/test/datafile/temp02.dbf' REUSE;

ALTER TABLESPACE TEMP_ASYNC ADD TEMPFILE '/u01/app/oracle/oradata/test/datafile/temp_async_01.dbf' REUSE;

修改完毕后，将数据库启动到nomount状态

shutdown immediate

startup nomout;

运行重建控制文件的sql语句

@/tmp/ctl.sql

SQL> select status from v$instance;

STATUS

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

OPEN

至此，已经完成数据库的迁移。