一、环境

1.装载spfile

spifle直接装载由于系统环境不同，可能导致无法nomount，所以在这里建议大家通过生成pfile的方式修改初始化参数文件。

create pfile from spfile;

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

DDLDB.__oracle_base='/data/u01/oracle'#ORACLE_BASE set from environment

*._B_TREE_BITMAP_PLANS=FALSE

*._IN_MEMORY_UNDO=FALSE

*._INDEX_JOIN_ENABLED=FALSE

*._OPTIM_PEEK_USER_BINDS=FALSE

*._OPTIMIZER_MJC_ENABLED=FALSE

*._SORT_ELIMINATION_COST_RATIO=10

*._TABLE_LOOKUP_PREFETCH_SIZE=0

*.audit_file_dest='/data/u02/oracle/audit'

*.compatible='11.2.0'

*.control_file_record_keep_time=30

*.control_files='/data/u02/oracle/controlfile/current.260.821225699'

*.db_block_size=8192

*.db_name='DDLDB'

*.db_recovery_file_dest='/data/u02/oracle/fra'

*.db_recovery_file_dest_size=3000M

*.diagnostic_dest='/data/u02/oracle/trace'

*.FILESYSTEMIO_OPTIONS='setall'

*.log_archive_dest_1='LOCATION=/data/u02/oracle/arch'

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

*.log_checkpoints_to_alert=true

*.max_dump_file_size='20000'

*.open_cursors=2000

*.parallel_execution_message_size=16384

*.parallel_threads_per_cpu=1

*.pga_aggregate_target=5100273664

*.processes=270

*.query_rewrite_enabled='false'

*.recyclebin='off'

*.remote_login_passwordfile='exclusive'

*.remote_os_authent=true

*.replication_dependency_tracking=false

*.sessions=540

*.shared_pool_size=2319282339

*.star_transformation_enabled='true'

*.undo_tablespace='UNDOTBS1'

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

startup nomount pfile='/data/u02/oracle/initDDLDB.ora'

2.装载controlfile

1）查询最新控制文件备份集

 bplist -C CenterDB-1 -t 4 -R /

2）set dbid&装载

set dbid=xxxxxxx;

run {

allocate channel d1 type 'SBT_TAPE';

send 'NB_ORA_SERV= nbuserver,NB_ORA_CLIENT=crmrac2';

restore controlfile from 'cntrl_7780_1_927013800';

release channel d1;

}

3）alter database mount

3.装载datafile

1）登录源机器rman中执行report schema列出对应datafile并修改为目标机的真实路径

2）执行脚本

run {

allocate channel c1 type 'sbt_tape';

allocate channel c2 type 'sbt_tape';

allocate channel c3 type 'sbt_tape';

SEND 'NB_ORA_SERV=nbuserver,NB_ORA_CLIENT=crmrac2';

set newname for datafile 1 to '/data/u02/oracle/datafile/system01.dbf';

set newname for datafile 2 to '/data/u02/oracle/datafile/sysaux01.dbf';

set newname for datafile 3 to '/data/u02/oracle/datafile/undotbs01.dbf';

set newname for datafile 4 to '/data/u02/oracle/datafile/users.dbf';

set newname for datafile 5 to '/data/u02/oracle/datafile/undotbs02.dbf';

set newname for datafile 6 to '/data/u02/oracle/datafile/dctautoimp03.dbf';

set newname for datafile 7 to '/data/u02/oracle/datafile/dctautoimp02.dbf';

set newname for datafile 8 to '/data/u02/oracle/datafile/dctautoimp01.dbf';

set newname for datafile 9 to '/data/u02/oracle/datafile/dwd_data02.dbf';

set newname for datafile 10 to '/data/u02/oracle/datafile/dctautoimp04.dbf';

set newname for datafile 11 to '/data/u02/oracle/datafile/users02.dbf';

set newname for datafile 12 to '/data/u02/oracle/datafile/dctautoimp05.dbf';

set newname for datafile 13 to '/data/u02/oracle/datafile/dct_index07.dbf';

set newname for datafile 14 to '/data/u02/oracle/datafile/dwd_index02.dbf';

set newname for datafile 15 to '/data/u02/oracle/datafile/dctautoimp06.dbf';

set newname for datafile 16 to '/data/u02/oracle/datafile/undotbs03.dbf';

set newname for datafile 17 to '/data/u02/oracle/datafile/undotbs04.dbf';

set newname for datafile 18 to '/data/u02/oracle/datafile/dctautoimp07.dbf';

set newname for datafile 19 to '/data/u02/oracle/datafile/dct_index08.dbf';

set newname for datafile 20 to '/data/u02/oracle/datafile/dctautoimp08.dbf';

set newname for datafile 21 to '/data/u02/oracle/datafile/dctautoimp09.dbf';

set newname for datafile 22 to '/data/u02/oracle/datafile/dctautoimp10.dbf';

set newname for datafile 23 to '/data/u02/oracle/datafile/dctautoimp_index01.dbf';

set newname for datafile 24 to '/data/u02/oracle/datafile/dwd_data01.dbf';

set newname for datafile 25 to '/data/u02/oracle/datafile/dwd_index01.dbf';

set newname for datafile 26 to '/data/u02/oracle/datafile/dct_data01.dbf';

set newname for datafile 27 to '/data/u02/oracle/datafile/dct_index01.dbf';

set newname for datafile 28 to '/data/u02/oracle/datafile/dct_data02.dbf';

set newname for datafile 29 to '/data/u02/oracle/datafile/dct_data03.dbf';

set newname for datafile 30 to '/data/u02/oracle/datafile/dct_data04.dbf';

set newname for datafile 31 to '/data/u02/oracle/datafile/dctautoimp_index02.dbf';

set newname for datafile 32 to '/data/u02/oracle/datafile/dctautoimp_index03.dbf';

set newname for datafile 33 to '/data/u02/oracle/datafile/dct_index02.dbf';

set newname for datafile 34 to '/data/u02/oracle/datafile/dct_index03.dbf';

set newname for datafile 35 to '/data/u02/oracle/datafile/dct_index04.dbf';

set newname for datafile 36 to '/data/u02/oracle/datafile/dct_data05.dbf';

set newname for datafile 37 to '/data/u02/oracle/datafile/dct_data06.dbf';

set newname for datafile 38 to '/data/u02/oracle/datafile/dctautoimp_index04.dbf';

set newname for datafile 39 to '/data/u02/oracle/datafile/dct_index05.dbf';

set newname for datafile 40 to '/data/u02/oracle/datafile/dct_index06.dbf';

set newname for datafile 41 to '/data/u02/oracle/datafile/dct_data07.dbf';

set newname for datafile 42 to '/data/u02/oracle/datafile/dctautoimp_index05.dbf';

set newname for datafile 43 to '/data/u02/oracle/datafile/dct_index09.dbf';

set newname for datafile 44 to '/data/u02/oracle/datafile/dct_data08.dbf';

set newname for datafile 45 to '/data/u02/oracle/datafile/dct_index10.dbf';

set newname for datafile 46 to '/data/u02/oracle/datafile/dctautoimp11.dbf';

set newname for datafile 47 to '/data/u02/oracle/datafile/dctautoimp12.dbf';

set newname for datafile 48 to '/data/u02/oracle/datafile/dctautoimp_index06.dbf';

set newname for datafile 49 to '/data/u02/oracle/datafile/dct_data09.dbf';

set newname for datafile 50 to '/data/u02/oracle/datafile/users03.dbf';

set newname for datafile 51 to '/data/u02/oracle/datafile/dctautoimp13.dbf';

restore database;

switch datafile all;

release channel c1;

release channel c2;

release channel c3;

}

4.修改redo

1)在源机中查询redo

select group#,type,member from  v$logfile;

2)在目标机修改redo路径

alter database rename file '+DDL_DATA/ddldb/onlinelog/redo05.log' to '/data/u02/oracle/redo/redo05.log';

alter database rename file '+DDL_DATA/ddldb/onlinelog/redo02.log' to '/data/u02/oracle/redo/redo02.log';

alter database rename file '+DDL_DATA/ddldb/onlinelog/redo01.log' to '/data/u02/oracle/redo/redo01.log';

alter database rename file '+DDL_DATA/ddldb/onlinelog/redo03.log' to '/data/u02/oracle/redo/redo03.log';

alter database rename file '+DDL_DATA/ddldb/onlinelog/redo04.log' to '/data/u02/oracle/redo/redo04.log';

alter database rename file '+DDL_DATA/ddldb/onlinelog/redo06.log' to '/data/u02/oracle/redo/redo06.log';

alter database rename file '+DDL_DATA/ddldb/onlinelog/redo07.log' to '/data/u02/oracle/redo/redo07.log';

alter database rename file '+DDL_DATA/ddldb/onlinelog/redo08.log' to '/data/u02/oracle/redo/redo08.log';

alter database rename file '+DDL_DATA/ddldb/onlinelog/redo09.log' to '/data/u02/oracle/redo/redo09.log';

alter database rename file '+DDL_DATA/ddldb/onlinelog/redo10.log' to '/data/u02/oracle/redo/redo10.log';

5.recover database

run {

allocate channel c1 type 'sbt_tape';

SEND 'NB_ORA_SERV=nbuserver,NB_ORA_CLIENT=crmrac2';

sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';

set until time='2016-11-04 08:00:00';

recover database;

release channel c1;

}

6.打开数据库

alter database open resetlogs

此时会提示以upgrade模式打开，同时实例会自动shutdown，需要我们从nomount到mount到open的步骤

startup nomount pfile='/data/u02/oracle/initDDLDB.ora';

alter database mount

create spfile from pfile='/data/u02/oracle/initDDLDB.ora';

alter database open upgrade;

7.恢复之后的后续处理

1）执行升级脚本

由于源机为11.2.0.3，目标机为11.2.0.4,需要执行升级脚本并重新编译无效对象

@$ORACLE_HOME\rdbms\admin\catupgrd.sql;

@$ORACLE_HOME/rdbms/admin/utlrp.sql;

2）重启数据库

shudown immediate

startup --此时无用加upgrade选项，可成功打开

3）取消限制模式

ALTER SYSTEM DISABLE RESTRICTED SESSION;

4）配置监听

修改listener.ora文件

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

DDL =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.x.x.x)(PORT = 1521))

      (SID_DESC =

       (GLOBAL_DBNAME = DDLDB)

       (ORACLE_HOME = /data/u01/oracle/db_1)

       (SID_NAME = DDLDB)

      )

  )

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

lsnrctl reload

lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 06-NOV-2016 19:48:47

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

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

Alias                     DDL

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                06-NOV-2016 11:38:26

Uptime                    0 days 8 hr. 10 min. 21 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /data/u01/oracle/db_1/network/admin/listener.ora

Listener Log File         /data/u01/oracle/diag/tnslsnr/ddlrec/ddl/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ddlrec)(PORT=1521)))

Services Summary...

Service "DDLDB" has 1 instance(s).

  Instance "DDLDB", status READY, has 1 handler(s) for this service...

The command completed successfully

用PL/SQL工具连接数据库可正常连接，至此DDL数据直联数据库恢复成功！