Oracle 11g R2 rac通过rman 恢复到单实例数据库

时间:2022-08-04 07:45:05

生产环境是2个节点的rac + dataguard(物理备库也是两个节点的rac),通过rman每天进行备份,现在需要定期对生产库进行恢复操作

恢复步骤如下:

1.      把生产库的备份拷贝到目标端

建立存放备份的目录修改目录属主属组

mkdir /oracle/backup

chown oracle:oinstall /oracle/backup

2.      拷贝备份到目标端

scp -P 22 incremental_level*  oracle@ip:/oracle/backup

scp -P 22 ctrl_ORCL_1381360903 *  oracle@ip:/oracle/backup

scp -P 22 arch_ORCL_ *  oracle@ip:/oracle/backup

3.      构造参数文件

在rac环境上生产pfile文件,然后修改pfile文件如下:

把rac相关的参数注释掉,修改相应的参数为单实例数据库上的路径

*._ash_size=104857600

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

*.audit_trail='NONE'

#*.cluster_database=true

*.compatible='11.2.0.4.0'

*.control_files='/oracle/oracle/oradata/orcl/control1.ctl','/oracle/oracle/oradata/control2.ctl'

*.db_block_size=8192

#*.db_create_file_dest='+DATADG'

*.db_domain=''

#*.db_file_name_convert='+DATADG','+DATADG'

*.db_name='orcl'

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

*.db_recovery_file_dest_size=107374182400

*.db_unique_name='orcl'

*.diagnostic_dest='/oracle/oracle'

#*.fal_client='primaryorcl'

#*.fal_server='standbyorcl'

#orcl1.instance_number=1

#orcl2.instance_number=2

#orcl1.large_pool_size=0

#orcl2.large_pool_size=0

#*.log_archive_config='dg_config=(orcl,orcldg)'

*.log_archive_dest_1='LOCATION=/oracle/oracle/oradata/archive'

#*.log_archive_dest_2='service=STANDBYORCLvalid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=orcldg'

#*.log_archive_dest_state_2='ENABLE'

#*.log_file_name_convert='+SYSTEMDG','+SYSTEMDG'

*.memory_max_target=16548578304

*.memory_target=16548578304

*.open_cursors=1000

*.pga_aggregate_target=5242880000

*.processes=4000

#*.remote_listener='172.19.0.107:1521'

*.remote_login_passwordfile='exclusive'

*.session_cached_cursors=800

*.sessions=3305

*.sga_target=10777216000

#orcl1.shared_pool_size=1073741824

#shared_pool_size=1073741824

#orcl2.shared_pool_size=1073741824

#*.standby_file_management='auto'

#orcl2.thread=2

#orcl1.thread=1

*.undo_retention=10800

#orcl2.undo_tablespace='UNDOTBS2'

#orcl1.undo_tablespace='UNDOTBS1'

undo_tablespace='UNDOTBS1'

文件修改好后放到$ORACLE_HOME/dbs下并命名为initorcl.ora

4.      创建相关的目录

mkdir –p /oracle/oracle/oradata/archive    --归档目录

mkdir –p /oracle/oracle/admin/orcl/adump  --跟踪审计目录

mkdir –p /oracle/oracle/oradata/orcl       --数据文件存放目录

chown oracle:oinstall /oracle/oracle/oradata/archive

chown oracle:oinstall /oracle/oracle/admin/orcl/adump

chown oracle:oinstall /oracle/oracle/oradata/orcl

5.      设置oracle_sid环境变量用编辑的好参数启动数据库到nomount状态

[oracle@database orcl]$ sqlplus "/assysdba"

SQL*Plus: Release 11.2.0.4.0Production on Fri Jan 23 12:14:27 2015

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

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1.6502E+10 bytes

Fixed Size                  2269632 bytes

Variable Size            7516196416 bytes

Database Buffers         8959033344 bytes

Redo Buffers               24379392 bytes

SQL>

6.      从备份集还原控制文件

restore controlfile from 'name';

RMAN>restore controlfile from '/oracle/backup/ctrl_ORCL_1381360903_20150121_485_1';

Startingrestore at 23-JAN-15

usingchannel ORA_DISK_1

channelORA_DISK_1: restoring control file

channelORA_DISK_1: restore complete, elapsed time: 00:00:01

outputfile name=/oracle/oracle/oradata/orcl/control1.ctl

outputfile name=/oracle/oracle/oradata/control2.ctl

Finishedrestore at 23-JAN-15

RMAN>mount database;  --打开数据库到mount状态

databasemounted

releasedchannel: ORA_DISK_1

RMAN>

7.      将备份信息注册到控制文件中

catalog start with '/oracle/backup/'

RMAN> catalog start with'/oracle/backup/';

Starting implicit crosscheck backup at23-JAN-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1887 devicetype=DISK

Crosschecked 19 objects

Finished implicit crosscheck backup at23-JAN-15

Starting implicit crosscheck copy at23-JAN-15

using channel ORA_DISK_1

Finished implicit crosscheck copy at23-JAN-15

searching for all files in the recoveryarea

cataloging files...

no files cataloged

searching for all files that match thepattern /oracle/backup/

List of Files Unknown to the Database

=====================================

File Name:/oracle/backup/ctrl_ORCL_1381360903_20150121_485_1

File Name:/oracle/backup/incremental_level1_eppt2p9m_20150119

File Name:/oracle/backup/incremental_level1_f3pt81qe_20150121

File Name:/oracle/backup/ctrl_ORCL_1381360903_20150118_469_1

File Name:/oracle/backup/incremental_level1_eopt2p54_20150119

File Name:/oracle/backup/ctrl_ORCL_1381360903_20150119_475_1

File Name:/oracle/backup/ctrl_ORCL_1381360903_20150120_480_1

File Name:/oracle/backup/incremental_level2_etpt5dce_20150120

File Name:/oracle/backup/incremental_level2_eupt5dha_20150120

File Name:/oracle/backup/incremental_level1_f2pt81ka_20150121

File Name:/oracle/backup/incremental_level0_ORCL_20150118_466_1

File Name:/oracle/backup/incremental_level0_ORCL_20150118_467_1

Do you really want to catalog the abovefiles (enter YES or NO)? YES

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name:/oracle/backup/ctrl_ORCL_1381360903_20150121_485_1

File Name:/oracle/backup/incremental_level1_eppt2p9m_20150119

File Name:/oracle/backup/incremental_level1_f3pt81qe_20150121

File Name:/oracle/backup/ctrl_ORCL_1381360903_20150118_469_1

File Name:/oracle/backup/incremental_level1_eopt2p54_20150119

File Name:/oracle/backup/ctrl_ORCL_1381360903_20150119_475_1

File Name:/oracle/backup/ctrl_ORCL_1381360903_20150120_480_1

File Name:/oracle/backup/incremental_level2_etpt5dce_20150120

File Name:/oracle/backup/incremental_level2_eupt5dha_20150120

File Name:/oracle/backup/incremental_level1_f2pt81ka_20150121

File Name:/oracle/backup/incremental_level0_ORCL_20150118_466_1

File Name:/oracle/backup/incremental_level0_ORCL_20150118_467_1

8.      查询确认数据文件、联机日志文件、临时文件路径

SQL> set pagesize 9999

SQL> col NAME for a65

SQL> select file#,name from v$datafile;

    FILE# NAME

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

        1 +DATADG/orcl/datafile/system.259.853580173

        2 +DATADG/orcl/datafile/sysaux.260.853580187

        3 +DATADG/orcl/datafile/undotbs1.261.853580197

        4 +DATADG/orcl/datafile/undotbs2.263.853580205

        5 +DATADG/orcl/datafile/users.264.853580205

        6 +DATADG/orcl/datafile/prod01.dbf

        7 +DATADG/orcl/datafile/prod02.dbf

        8 +DATADG/orcl/datafile/prod03.dbf

        9 +DATADG/orcl/datafile/prod04.dbf

       10 +DATADG/orcl/datafile/prod05.dbf

       11 +DATADG/orcl/datafile/prod06.dbf

       12 +DATADG/orcl/datafile/undotbs1_2.dbf

       13 +DATADG/orcl/datafile/undotbs2_2.dbf

       14 +DATADG/orcl/datafile/prod07.dbf

       15 +DATADG/orcl/datafile/prod08.dbf

       16 +DATADG/orcl/datafile/ttsinx01.dbf

       17 +DATADG/orcl/datafile/ttsinx02.dbf

        18 +DATADG/orcl/datafile/ttsinx03.dbf

       19 +DATADG/orcl/datafile/ttsinx04.dbf

       20 +DATADG/orcl/datafile/ttsinx05.dbf

       21 +DATADG/orcl/datafile/ttsinx06.dbf

       22 +DATADG/orcl/datafile/ttsinx07.dbf

       23 +DATADG/orcl/datafile/ttsinx08.dbf

       24 +DATADG/orcl/datafile/test.dbf

       25 +DATADG/orcl/datafile/undotbs2.2.dbf

       26 +DATADG/orcl/datafile/undotbs1.2.dbf

SQL> col MEMBER for a65

SQL> select member from v$logfile;

MEMBER

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

+SYSTEMDG/orcl/onlinelog/redo1_1.log

+SYSTEMDG/orcl/onlinelog/redo1_2.log

+SYSTEMDG/orcl/onlinelog/standbyredo1_9.log

+SYSTEMDG/orcl/onlinelog/standbyredo1_10.log

+SYSTEMDG/orcl/onlinelog/redo1_3.log

+SYSTEMDG/orcl/onlinelog/redo1_4.log

+SYSTEMDG/orcl/onlinelog/standbyredo1_11.log

+SYSTEMDG/orcl/onlinelog/standbyredo1_12.log

+SYSTEMDG/orcl/onlinelog/redo2_5.log

+SYSTEMDG/orcl/onlinelog/redo2_6.log

+SYSTEMDG/orcl/onlinelog/redo2_7.log

+SYSTEMDG/orcl/onlinelog/redo2_8.log

+SYSTEMDG/orcl/onlinelog/standbyredo1_13.log

+SYSTEMDG/orcl/onlinelog/standbyredo2_14.log

+SYSTEMDG/orcl/onlinelog/standbyredo2_15.log

+SYSTEMDG/orcl/onlinelog/standbyredo2_16.log

+SYSTEMDG/orcl/onlinelog/standbyredo2_17.log

+SYSTEMDG/orcl/onlinelog/standbyredo2_18.log

SQL> select name from v$tempfile;

NAME

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

+DATADG/orcl/tempfile/temp.262.853580199

SQL>

9.      通过rman重名数据库文件临时文件,进行restore

RUN {

SET NEWNAME FOR DATAFILE 1 to'/oracle/oracle/oradata/orcl/system01.dbf';

SET NEWNAME FOR DATAFILE 2 to'/oracle/oracle/oradata/orcl/sysaux01.dbf';

SET NEWNAME FOR DATAFILE 3 to'/oracle/oracle/oradata/orcl/undotbs1.dbf';

SET NEWNAME FOR DATAFILE 4 to'/oracle/oracle/oradata/orcl/undotbs2.dbf';

SET NEWNAME FOR DATAFILE 5 to'/oracle/oracle/oradata/orcl/users.dbf';

SET NEWNAME FOR DATAFILE 6 to'/oracle/oracle/oradata/orcl/prod01.dbf';

SET NEWNAME FOR DATAFILE 7 to'/oracle/oracle/oradata/orcl/prod02.dbf';

SET NEWNAME FOR DATAFILE 8 to'/oracle/oracle/oradata/orcl/prod03.dbf';

SET NEWNAME FOR DATAFILE 9 to'/oracle/oracle/oradata/orcl/prod04.dbf';

SET NEWNAME FOR DATAFILE 10 to'/oracle/oracle/oradata/orcl/prod05.dbf';

SET NEWNAME FOR DATAFILE 11 to'/oracle/oracle/oradata/orcl/prod06.dbf';

SET NEWNAME FOR DATAFILE 12 to'/oracle/oracle/oradata/orcl/undotbs1_2.dbf';

SET NEWNAME FOR DATAFILE 13 to'/oracle/oracle/oradata/orcl/undotbs2_2.dbf';

SET NEWNAME FOR DATAFILE 14 to'/oracle/oracle/oradata/orcl/prod07.dbf';

SET NEWNAME FOR DATAFILE 15 to '/oracle/oracle/oradata/orcl/prod08.dbf';

SET NEWNAME FOR DATAFILE 16 to'/oracle/oracle/oradata/orcl/ttsinx01.dbf';

SET NEWNAME FOR DATAFILE 17 to'/oracle/oracle/oradata/orcl/ttsinx02.dbf';

SET NEWNAME FOR DATAFILE 18 to'/oracle/oracle/oradata/orcl/ttsinx03.dbf';

SET NEWNAME FOR DATAFILE 19 to'/oracle/oracle/oradata/orcl/ttsinx04.dbf';

SET NEWNAME FOR DATAFILE 20 to'/oracle/oracle/oradata/orcl/ttsinx05.dbf';

SET NEWNAME FOR DATAFILE 21 to'/oracle/oracle/oradata/orcl/ttsinx06.dbf';

SET NEWNAME FOR DATAFILE 22 to'/oracle/oracle/oradata/orcl/ttsinx07.dbf';

SET NEWNAME FOR DATAFILE 23 to'/oracle/oracle/oradata/orcl/ttsinx08.dbf';

SET NEWNAME FOR DATAFILE 24 to'/oracle/oracle/oradata/orcl/test.dbf';

SET NEWNAME FOR DATAFILE 25 to'/oracle/oracle/oradata/orcl/undotbs2.2.dbf';

SET NEWNAME FOR DATAFILE 26 to'/oracle/oracle/oradata/orcl/undotbs1.2.dbf';

SET NEWNAME FOR TEMPFILE 1 to'/oracle/oracle/oradata/orcl/temp01.dbf';

RESTORE DATABASE;

SWITCH DATAFILE ALL;

SWITCH TEMPFILE ALL;

}

如下restore过程:

RMAN>

RMAN> RUN {

2> SET NEWNAME FOR DATAFILE 1 to'/oracle/oracle/oradata/orcl/system01.dbf';

3> SET NEWNAME FOR DATAFILE 2 to'/oracle/oracle/oradata/orcl/sysaux01.dbf';

4> SET NEWNAME FOR DATAFILE 3 to'/oracle/oracle/oradata/orcl/undotbs1.dbf';

5> SET NEWNAME FOR DATAFILE 4 to'/oracle/oracle/oradata/orcl/undotbs2.dbf';

6> SET NEWNAME FOR DATAFILE 5 to'/oracle/oracle/oradata/orcl/users.dbf';

7> SET NEWNAME FOR DATAFILE 6 to'/oracle/oracle/oradata/orcl/prod01.dbf';

8> SET NEWNAME FOR DATAFILE 7 to'/oracle/oracle/oradata/orcl/prod02.dbf';

9> SET NEWNAME FOR DATAFILE 8 to'/oracle/oracle/oradata/orcl/prod03.dbf';

10> SET NEWNAME FOR DATAFILE 9 to'/oracle/oracle/oradata/orcl/prod04.dbf';

11> SET NEWNAME FOR DATAFILE 10 to'/oracle/oracle/oradata/orcl/prod05.dbf';

12> SET NEWNAME FOR DATAFILE 11 to'/oracle/oracle/oradata/orcl/prod06.dbf';

13> SET NEWNAME FOR DATAFILE 12 to'/oracle/oracle/oradata/orcl/undotbs1_2.dbf';

14> SET NEWNAME FOR DATAFILE 13 to'/oracle/oracle/oradata/orcl/undotbs2_2.dbf';

15> SET NEWNAME FOR DATAFILE 14 to'/oracle/oracle/oradata/orcl/prod07.dbf';

16> SET NEWNAME FOR DATAFILE 15 to'/oracle/oracle/oradata/orcl/prod08.dbf';

17> SET NEWNAME FOR DATAFILE 16 to'/oracle/oracle/oradata/orcl/ttsinx01.dbf';

18> SET NEWNAME FOR DATAFILE 17 to'/oracle/oracle/oradata/orcl/ttsinx02.dbf';

19> SET NEWNAME FOR DATAFILE 18 to'/oracle/oracle/oradata/orcl/ttsinx03.dbf';

20> SET NEWNAME FOR DATAFILE 19 to'/oracle/oracle/oradata/orcl/ttsinx04.dbf';

21> SET NEWNAME FOR DATAFILE 20 to'/oracle/oracle/oradata/orcl/ttsinx05.dbf';

22> SET NEWNAME FOR DATAFILE 21 to'/oracle/oracle/oradata/orcl/ttsinx06.dbf';

23> SET NEWNAME FOR DATAFILE 22 to'/oracle/oracle/oradata/orcl/ttsinx07.dbf';

24> SET NEWNAME FOR DATAFILE 23 to '/oracle/oracle/oradata/orcl/ttsinx08.dbf';

25> SET NEWNAME FOR DATAFILE 24 to'/oracle/oracle/oradata/orcl/test.dbf';

26> SET NEWNAME FOR DATAFILE 25 to'/oracle/oracle/oradata/orcl/undotbs2.2.dbf';

27> SET NEWNAME FOR DATAFILE 26 to'/oracle/oracle/oradata/orcl/undotbs1.2.dbf';

28> SET NEWNAME FOR TEMPFILE 1 to'/oracle/oracle/oradata/orcl/temp01.dbf';

29> RESTORE DATABASE;

30> SWITCH DATAFILE ALL;

31> SWITCH TEMPFILE ALL;

32> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 23-JAN-15

using channel ORA_DISK_1

datafile 2 is already restored to file/oracle/oracle/oradata/orcl/sysaux01.dbf

datafile 3 is already restored to file/oracle/oracle/oradata/orcl/undotbs1.dbf

datafile 4 is already restored to file/oracle/oracle/oradata/orcl/undotbs2.dbf

datafile 6 is already restored to file/oracle/oracle/oradata/orcl/prod01.dbf

datafile 7 is already restored to file/oracle/oracle/oradata/orcl/prod02.dbf

datafile 8 is already restored to file/oracle/oracle/oradata/orcl/prod03.dbf

datafile 9 is already restored to file/oracle/oracle/oradata/orcl/prod04.dbf

datafile 10 is already restored to file/oracle/oracle/oradata/orcl/prod05.dbf

datafile 11 is already restored to file/oracle/oracle/oradata/orcl/prod06.dbf

datafile 14 is already restored to file/oracle/oracle/oradata/orcl/prod07.dbf

datafile 15 is already restored to file/oracle/oracle/oradata/orcl/prod08.dbf

datafile 16 is already restored to file/oracle/oracle/oradata/orcl/ttsinx01.dbf

datafile 17 is already restored to file/oracle/oracle/oradata/orcl/ttsinx02.dbf

datafile 19 is already restored to file/oracle/oracle/oradata/orcl/ttsinx04.dbf

channel ORA_DISK_1: starting datafilebackup set restore

channel ORA_DISK_1: specifying datafile(s)to restore from backup set

channel ORA_DISK_1: restoring datafile00001 to /oracle/oracle/oradata/orcl/system01.dbf

channel ORA_DISK_1: restoring datafile00005 to /oracle/oracle/oradata/orcl/users.dbf

channel ORA_DISK_1: restoring datafile 00012to /oracle/oracle/oradata/orcl/undotbs1_2.dbf

channel ORA_DISK_1: restoring datafile00013 to /oracle/oracle/oradata/orcl/undotbs2_2.dbf

channel ORA_DISK_1: restoring datafile00018 to /oracle/oracle/oradata/orcl/ttsinx03.dbf

channel ORA_DISK_1: restoring datafile00020 to /oracle/oracle/oradata/orcl/ttsinx05.dbf

channel ORA_DISK_1: restoring datafile00021 to /oracle/oracle/oradata/orcl/ttsinx06.dbf

channel ORA_DISK_1: restoring datafile00022 to /oracle/oracle/oradata/orcl/ttsinx07.dbf

channel ORA_DISK_1: restoring datafile00023 to /oracle/oracle/oradata/orcl/ttsinx08.dbf

channel ORA_DISK_1: restoring datafile00024 to /oracle/oracle/oradata/orcl/test.dbf

channel ORA_DISK_1: restoring datafile00025 to /oracle/oracle/oradata/orcl/undotbs2.2.dbf

channel ORA_DISK_1: restoring datafile00026 to /oracle/oracle/oradata/orcl/undotbs1.2.dbf

channel ORA_DISK_1: reading from backuppiece /oracle/backup/incremental_level0_ORCL_20150118_466_1

channel ORA_DISK_1: piecehandle=/oracle/backup/incremental_level0_ORCL_20150118_466_1tag=TAG20150118T010640

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete,elapsed time: 00:10:05

Finished restore at 23-JAN-15

datafile 1 switched to datafile copy

input datafile copy RECID=27STAMP=869749128 file name=/oracle/oracle/oradata/orcl/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=28STAMP=869749128 file name=/oracle/oracle/oradata/orcl/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=29 STAMP=869749129file name=/oracle/oracle/oradata/orcl/undotbs1.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=30STAMP=869749129 file name=/oracle/oracle/oradata/orcl/undotbs2.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=31STAMP=869749129 file name=/oracle/oracle/oradata/orcl/users.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=32STAMP=869749129 file name=/oracle/oracle/oradata/orcl/prod01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=33STAMP=869749129 file name=/oracle/oracle/oradata/orcl/prod02.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=34STAMP=869749129 file name=/oracle/oracle/oradata/orcl/prod03.dbf

datafile 9 switched to datafile copy

input datafile copy RECID=35STAMP=869749129 file name=/oracle/oracle/oradata/orcl/prod04.dbf

datafile 10 switched to datafile copy

input datafile copy RECID=36STAMP=869749129 file name=/oracle/oracle/oradata/orcl/prod05.dbf

datafile 11 switched to datafile copy

input datafile copy RECID=37STAMP=869749130 file name=/oracle/oracle/oradata/orcl/prod06.dbf

datafile 12 switched to datafile copy

input datafile copy RECID=38STAMP=869749130 file name=/oracle/oracle/oradata/orcl/undotbs1_2.dbf

datafile 13 switched to datafile copy

input datafile copy RECID=39STAMP=869749130 file name=/oracle/oracle/oradata/orcl/undotbs2_2.dbf

datafile 14 switched to datafile copy

input datafile copy RECID=40STAMP=869749130 file name=/oracle/oracle/oradata/orcl/prod07.dbf

datafile 15 switched to datafile copy

input datafile copy RECID=41STAMP=869749130 file name=/oracle/oracle/oradata/orcl/prod08.dbf

datafile 16 switched to datafile copy

input datafile copy RECID=42STAMP=869749131 file name=/oracle/oracle/oradata/orcl/ttsinx01.dbf

datafile 17 switched to datafile copy

input datafile copy RECID=43STAMP=869749131 file name=/oracle/oracle/oradata/orcl/ttsinx02.dbf

datafile 18 switched to datafile copy

input datafile copy RECID=44STAMP=869749131 file name=/oracle/oracle/oradata/orcl/ttsinx03.dbf

datafile 19 switched to datafile copy

input datafile copy RECID=45STAMP=869749131 file name=/oracle/oracle/oradata/orcl/ttsinx04.dbf

datafile 20 switched to datafile copy

input datafile copy RECID=46STAMP=869749131 file name=/oracle/oracle/oradata/orcl/ttsinx05.dbf

datafile 21 switched to datafile copy

input datafile copy RECID=47STAMP=869749132 file name=/oracle/oracle/oradata/orcl/ttsinx06.dbf

datafile 22 switched to datafile copy

input datafile copy RECID=48STAMP=869749132 file name=/oracle/oracle/oradata/orcl/ttsinx07.dbf

datafile 23 switched to datafile copy

input datafile copy RECID=49STAMP=869749132 file name=/oracle/oracle/oradata/orcl/ttsinx08.dbf

datafile 24 switched to datafile copy

input datafile copy RECID=50STAMP=869749132 file name=/oracle/oracle/oradata/orcl/test.dbf

datafile 25 switched to datafile copy

input datafile copy RECID=51STAMP=869749132 file name=/oracle/oracle/oradata/orcl/undotbs2.2.dbf

datafile 26 switched to datafile copy

input datafile copy RECID=52STAMP=869749132 file name=/oracle/oracle/oradata/orcl/undotbs1.2.dbf

renamed tempfile 1 to/oracle/oracle/oradata/orcl/temp01.dbf in control file

RMAN>

RMAN>

10.  修改联机日志文件的路径和删除standby redo log

alter database rename file'+SYSTEMDG/orcl/onlinelog/redo1_1.log' to '/oracle/oracle/oradata/orcl/redo1_1.log';

alter database rename file'+SYSTEMDG/orcl/onlinelog/redo1_2.log' to '/oracle/oracle/oradata/orcl/redo1_2.log';

alter database rename file'+SYSTEMDG/orcl/onlinelog/redo1_3.log' to '/oracle/oracle/oradata/orcl/redo1_3.log';

alter database rename file'+SYSTEMDG/orcl/onlinelog/redo1_4.log' to '/oracle/oracle/oradata/orcl/redo1_4.log';

alter database rename file '+SYSTEMDG/orcl/onlinelog/redo2_5.log'to '/oracle /oracle/oradata/orcl/redo2_5.log';

alter database rename file'+SYSTEMDG/orcl/onlinelog/redo2_6.log' to '/oracle/oracle/oradata/orcl/redo2_6.log';

alter database rename file'+SYSTEMDG/orcl/onlinelog/redo2_7.log' to '/oracle//oracle/oradata/orcl/redo2_7.log';

alter database rename file'+SYSTEMDG/orcl/onlinelog/redo2_8.log' to '/oracle//oracle/oradata/orcl/redo2_8.log';

alter database drop logfile group 9;

alter database drop logfile group 10;

alter database drop logfile group 11;

alter database drop logfile group 12;

alter database drop logfile group 13;

alter database drop logfile group 14;

alter database drop logfile group 15;

alter database drop logfile group 16;

alter database drop logfile group 17;

alter database drop logfile group 18;

修改后查询:

SQL> select member from v$logfile;

MEMBER

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

/oracle/oracle/oradata/orcl/redo1_1.log

/oracle/oracle/oradata/orcl/redo1_2.log

/oracle/oracle/oradata/orcl/redo1_3.log

/oracle/oracle/oradata/orcl/redo1_4.log

/oracle/oracle/oradata/orcl/redo2_5.log

/oracle/oracle/oradata/orcl/redo2_6.log

/oracle/oracle/oradata/orcl/redo2_7.log

/oracle/oracle/oradata/orcl/redo2_8.log

11.  恢复数据库

RMAN> recover database;

Starting recover at 23-JAN-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting incrementaldatafile backup set restore

channel ORA_DISK_1: specifying datafile(s)to restore from backup set

destination for restore of datafile 00001:/oracle/oracle/oradata/orcl/system01.dbf

destination for restore of datafile 00002:/oracle/oracle/oradata/orcl/sysaux01.dbf

destination for restore of datafile 00003:/oracle/oracle/oradata/orcl/undotbs1.dbf

destination for restore of datafile 00004:/oracle/oracle/oradata/orcl/undotbs2.dbf

destination for restore of datafile 00005:/oracle/oracle/oradata/orcl/users.dbf

destination for restore of datafile 00006:/oracle/oracle/oradata/orcl/prod01.dbf

destination for restore of datafile 00007:/oracle/oracle/oradata/orcl/prod02.dbf

destination for restore of datafile 00008:/oracle/oracle/oradata/orcl/prod03.dbf

destination for restore of datafile 00009:/oracle/oracle/oradata/orcl/prod04.dbf

destination for restore of datafile 00010:/oracle/oracle/oradata/orcl/prod05.dbf

destination for restore of datafile 00011:/oracle/oracle/oradata/orcl/prod06.dbf

destination for restore of datafile 00012:/oracle/oracle/oradata/orcl/undotbs1_2.dbf

destination for restore of datafile 00013:/oracle/oracle/oradata/orcl/undotbs2_2.dbf

destination for restore of datafile 00014:/oracle/oracle/oradata/orcl/prod07.dbf

destination for restore of datafile 00015:/oracle/oracle/oradata/orcl/prod08.dbf

destination for restore of datafile 00016:/oracle/oracle/oradata/orcl/ttsinx01.dbf

destination for restore of datafile 00017:/oracle/oracle/oradata/orcl/ttsinx02.dbf

destination for restore of datafile 00018:/oracle/oracle/oradata/orcl/ttsinx03.dbf

destination for restore of datafile 00019:/oracle/oracle/oradata/orcl/ttsinx04.dbf

destination for restore of datafile 00020:/oracle/oracle/oradata/orcl/ttsinx05.dbf

destination for restore of datafile 00021:/oracle/oracle/oradata/orcl/ttsinx06.dbf

destination for restore of datafile 00022:/oracle/oracle/oradata/orcl/ttsinx07.dbf

destination for restore of datafile 00023:/oracle/oracle/oradata/orcl/ttsinx08.dbf

destination for restore of datafile 00024:/oracle/oracle/oradata/orcl/test.dbf

destination for restore of datafile 00025:/oracle/oracle/oradata/orcl/undotbs2.2.dbf

destination for restore of datafile 00026:/oracle/oracle/oradata/orcl/undotbs1.2.dbf

channel ORA_DISK_1: reading from backuppiece /oracle/backup/incremental_level1_eopt2p54_20150119

channel ORA_DISK_1: piecehandle=/oracle/backup/incremental_level1_eopt2p54_20150119tag=TAG20150119T010643

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete,elapsed time: 00:11:45

channel ORA_DISK_1: starting incrementaldatafile backup set restore

channel ORA_DISK_1: specifying datafile(s)to restore from backup set

destination for restore of datafile 00001:/oracle/oracle/oradata/orcl/system01.dbf

destination for restore of datafile 00002:/oracle/oracle/oradata/orcl/sysaux01.dbf

destination for restore of datafile 00003:/oracle/oracle/oradata/orcl/undotbs1.dbf

destination for restore of datafile 00004:/oracle/oracle/oradata/orcl/undotbs2.dbf

destination for restore of datafile 00005:/oracle/oracle/oradata/orcl/users.dbf

destination for restore of datafile 00006:/oracle/oracle/oradata/orcl/prod01.dbf

destination for restore of datafile 00007:/oracle/oracle/oradata/orcl/prod02.dbf

destination for restore of datafile 00008:/oracle/oracle/oradata/orcl/prod03.dbf

destination for restore of datafile 00009:/oracle/oracle/oradata/orcl/prod04.dbf

destination for restore of datafile 00010:/oracle/oracle/oradata/orcl/prod05.dbf

destination for restore of datafile 00011:/oracle/oracle/oradata/orcl/prod06.dbf

destination for restore of datafile 00012:/oracle/oracle/oradata/orcl/undotbs1_2.dbf

destination for restore of datafile 00013:/oracle/oracle/oradata/orcl/undotbs2_2.dbf

destination for restore of datafile 00014:/oracle/oracle/oradata/orcl/prod07.dbf

destination for restore of datafile 00015:/oracle/oracle/oradata/orcl/prod08.dbf

destination for restore of datafile 00016:/oracle/oracle/oradata/orcl/ttsinx01.dbf

destination for restore of datafile 00017:/oracle/oracle/oradata/orcl/ttsinx02.dbf

destination for restore of datafile 00018:/oracle/oracle/oradata/orcl/ttsinx03.dbf

destination for restore of datafile 00019:/oracle/oracle/oradata/orcl/ttsinx04.dbf

destination for restore of datafile 00020:/oracle/oracle/oradata/orcl/ttsinx05.dbf

destination for restore of datafile 00021:/oracle/oracle/oradata/orcl/ttsinx06.dbf

destination for restore of datafile 00022:/oracle/oracle/oradata/orcl/ttsinx07.dbf

destination for restore of datafile 00023:/oracle/oracle/oradata/orcl/ttsinx08.dbf

destination for restore of datafile 00024:/oracle/oracle/oradata/orcl/test.dbf

destination for restore of datafile 00025:/oracle/oracle/oradata/orcl/undotbs2.2.dbf

destination for restore of datafile 00026:/oracle/oracle/oradata/orcl/undotbs1.2.dbf

channel ORA_DISK_1: reading from backuppiece /oracle/backup/incremental_level1_f2pt81ka_20150121

channel ORA_DISK_1: piecehandle=/oracle/backup/incremental_level1_f2pt81ka_20150121tag=TAG20150121T010201

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete,elapsed time: 00:17:36

starting media recovery

RMAN-00571:===========================================================

RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002: failure of recover command at01/23/2015 13:36:37

RMAN-06053: unable to perform media recoverybecause of missing log

RMAN-06025: no backup of archived log forthread 2 with sequence 1458 and starting SCN of 8494903381 found to restore

RMAN-06025: no backup of archived log forthread 1 with sequence 2276 and starting SCN of 8494903259 found to restore

12.  打开数据库

SQL> alter database open;

alter database open *

ERROR at line 1:

ORA-00392: log 2 of thread 1 is beingcleared, operation not allowed

ORA-00312: online log 2 thread 1:'/oracle/oracle/oradata/orcl/redo1_2.log'

打开数据库报错,执行情况日志

SQL> alter database clear unarchivedlogfile group  1;

Database altered.

SQL> alter database clear unarchivedlogfile group 2;

Database altered.

SQL> alter database clear unarchivedlogfile group 3;   

Database altered.

SQL> alter database clear unarchivedlogfile group 4;   

Database altered.

SQL> alter database clear unarchivedlogfile group 5; 

Database altered.

SQL> alter database clear unarchivedlogfile group 6;

Database altered.

SQL> alter database clear unarchivedlogfile group 7; 

Database altered.

SQL> alter database clear unarchivedlogfile group 8; 

Database altered.

SQL>

在打开数据库

SQL> alter database open;

alter database open*

ERROR at line 1:

ORA-19751: could not create the changetracking file

ORA-19750: change tracking file:

'+SYSTEMDG/orcl/changetracking/ctf.517.868528725'

ORA-17502: ksfdcre:1 Failed to create file

+SYSTEMDG/orcl/changetracking/ctf.517.868528725

ORA-17501: logical block size 4294967295 isinvali

ORA-17503: ksfdopn:2 Failed to open file

+SYSTEMDG/orcl/changetracking/ctf.517.868528725

ORA-15001: diskgroup "SYSTEMDG"does not exist or is not mounted

ORA-15077: could not locate ASM instanceserving a required diskgroup

ORA-29701: unable to connect to ClusterSynchronization Service

原因是生产环境开启块变化跟踪功能了

关闭块跟踪:

SQL> alter database disable block changetracking;

Database altered.

Open数据库正常,完成恢复

SQL> alter database open;

Database altered.

SQL>

查看:

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /oracle/oracle/oradata/archive

Oldest online log sequence     1

Next log sequence to archive   2

Current log sequence           2

SQL> selectgroup#,thread#,archived,status from v$log;

   GROUP#    THREAD# ARC STATUS

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

        1          1 YES INACTIVE

        2          1 NO  CURRENT

        3          1 YES UNUSED

        4          1 YES UNUSED

        5          2 YES INACTIVE

        6          2 YES UNUSED

         7          2 YES UNUSED

        8          2 YES UNUSED

8 rows selected.

SQL> select thread#,status,enabled from v$thread;

  THREAD# STATUS ENABLED

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

        1 OPEN   PUBLIC

        2 CLOSED PUBLIC