121231异机恢复oracle数据库

异机恢复oracle数据库；

 

找新的一台虚机，安装oracle数据库，并创建默认数据库。需要注意以下几点：

1.        恢复目标机target db的instance name，sid以及数据库名称需与source db 恢复源db的名称保持一致，在目标恢复机恢复时copy备份数据到fra flashrecoverarea且设置源的dbid；

2.        恢复目标机target db的数据库字符集编码需要与source db恢复源db的字符编码保持一致；

 

SQL>select * from nls_database_parameters;

 

PARAMETER                      VALUE

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

NLS_CSMIG_SCHEMA_VERSION       5

NLS_LANGUAGE                   AMERICAN

NLS_TERRITORY                  AMERICA

NLS_CURRENCY                   $

NLS_ISO_CURRENCY               AMERICA

NLS_NUMERIC_CHARACTERS         .,

NLS_CHARACTERSET              ZHS16GBK

NLS_CALENDAR                   GREGORIAN

NLS_DATE_FORMAT                DD-MON-RR

NLS_DATE_LANGUAGE              AMERICAN

NLS_SORT                       BINARY

 

PARAMETER                      VALUE

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

NLS_TIME_FORMAT                HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY              $

NLS_COMP                       BINARY

NLS_LENGTH_SEMANTICS           BYTE

NLS_NCHAR_CONV_EXCP            FALSE

NLS_NCHAR_CHARACTERSET         UTF8

NLS_RDBMS_VERSION              11.2.0.1.0

 

21 rows selected.

 

查出NLS_CHARACTERSET               ZHS16GBK

 

Copy test.rar压缩包中的源source db的flash_recovery_area目录，到target db的flash_recovery_area目录；

最终第一次尝试恢复失败,失败原因是已经在targetdb目标数据库中创建了数据库,不是创建了就无法恢复,而是如果源和目标sid不一样,如何修改尚不得而知;

 

使用deinstall反安装数据库;

 

创建fraflash_recovery_area目录，oradata目录等rman恢复需要的目录；

 

对数据库软件重新进行安装,在安装时不创建数据库,自然也不需要创建数据库；只保留数据库软件即可；

 

        C:\Windows\system32>oradim-new-sid test

        Instance created.

    

重建数据库instance，sid名称需要与源数据库source db的名称一致；

 

C:\Windows\system32>orapwdfile=c:\app\Azar\product\11.2.0\test\database\PWDTEST.ora password=Test123

 

重建密码文件；

 

Microsoft Windows [Version 6.1.7601]

Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

 

C:\Users\Administrator>set oracle_sid=test

 

C:\Users\Administrator>rman target /

 

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 3110:22:51 2012

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database (not started)

 

RMAN> set dbid=2098104796;

 

executing command: SET DBID

 

 

Set dbid 数据库唯一标识id，该id需要从源数据库source DB中查询：

 

SQL>select dbid from v$database;

 

      DBID

----------

2098104796

 

RMAN> startup nomount;

tartup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file'D:\ORACLE\PRODUCT\11.2.0\TEST\DATABASE

\INITTEST.ORA'

 

starting Oracle instance without parameter file for retrieval ofspfile

Oracle instance started

 

Total System Global Area    158662656 bytes

 

Fixed Size                    2173840 bytes

Variable Size                88081520 bytes

Database Buffers             62914560 bytes

Redo Buffers                   5492736 bytes

 

从数据库文件中恢复spfile，需要知道spfile存在于哪个rman备份中；

 

查询源sourceDB数据库：

RMAN> list backup of spfile;

 

 

Listof Backup Sets

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

 

 

BSKey  Type LV Size       Device Type Elapsed Time Completion Time

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

33      Full   9.45M      DISK        00:00:00     30-DEC-12

        BP Key: 33   Status: AVAILABLE  Compressed: NO  Tag: TAG20121230T181522

        Piece Name:D:\ORACLE\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2012_12_30\O1_MF_S_803412922_8G051TMY_.BKP

  SPFILE Included: Modification time: 30-DEC-12

  SPFILE db_unique_name: TEST

 

备份集BS 33中文件就是spfile和controlfile的备份文件；

 

RMAN> restore spfile to pfile 'd:\pfile18.txt' from'D:\oracle\flash_recovery_ar

ea\test\AUTOBACKUP\2012_12_30\O1_MF_S_803412922_8G051TMY_.BKP'

2> ;

 

Starting restore at 31-DEC-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=96 device type=DISK

 

channel ORA_DISK_1: restoring spfile from AUTOBACKUPD:\oracle\flash_recovery_ar

ea\test\AUTOBACKUP\2012_12_30\O1_MF_S_803412922_8G051TMY_.BKP

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 31-DEC-12

 

将该文件恢复到d盘pfile18.txt；

 

此处容易出错，恢复完pfile后，需要使用该pfile startup nomount，如果不使用该pfile进行挂载，则后面恢复controlfile是只能恢复到默认位置，并不是sourceDB源数据库应该恢复的位置及数量；

 

RMAN>restore controlfile from 'D:\oracle\flash_recovery_area\test\AUTOBACKUP\20

12_12_30\O1_MF_S_803412922_8G051TMY_.BKP';

 

Startingrestore at 31-DEC-12

usingchannel ORA_DISK_1

 

channelORA_DISK_1: restoring control file

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

outputfile name=D:\ORACLE\PRODUCT\11.2.0\TEST\DATABASE\CTL1TEST.ORA

Finishedrestore at 31-DEC-12

 

这个位置和数量是错的

 

        先挂载恢复出来的pfile

        Startup nomount pfile=‘d:\pfile18.txt’；

        然后通过该pfile生成spfile；

        Select spfile from pfile;

        然后再恢复controlfile；

 

RMAN>restore controlfile from 'D:\oracle\flash_recovery_area\test\AUTOBACKUP\20

12_12_30\O1_MF_S_803412922_8G051TMY_.BKP';

 

Startingrestore at 31-DEC-12

allocatedchannel: ORA_DISK_1

channelORA_DISK_1: SID=134 device type=DISK

 

channelORA_DISK_1: restoring control file

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

outputfile name=D:\ORACLE\ORADATA\TEST\CONTROL01.CTL

outputfile name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL

Finishedrestore at 31-DEC-12

 

Controlfile恢复成功；

 

RMAN>restore database;

 

Startingrestore at 31-DEC-12

Startingimplicit crosscheck backup at 31-DEC-12

allocatedchannel: ORA_DISK_1

channelORA_DISK_1: SID=134 device type=DISK

Crosschecked3 objects

Finishedimplicit crosscheck backup at 31-DEC-12

 

Startingimplicit crosscheck copy at 31-DEC-12

usingchannel ORA_DISK_1

Finishedimplicit crosscheck copy at 31-DEC-12

 

searchingfor all files in the recovery area

catalogingfiles...

catalogingdone

 

List ofCataloged Files

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

FileName: D:\ORACLE\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2012_12_30\O1_MF_S_8034

12922_8G051TMY_.BKP

 

usingchannel ORA_DISK_1

 

channelORA_DISK_1: starting datafile backup set restore

channelORA_DISK_1: specifying datafile(s) to restore from backup set

channelORA_DISK_1: restoring datafile 00001 to D:\ORACLE\ORADATA\TEST\SYSTEM01.

DBF

channelORA_DISK_1: restoring datafile 00002 to D:\ORACLE\ORADATA\TEST\SYSAUX01.

DBF

channelORA_DISK_1: restoring datafile 00003 to D:\ORACLE\ORADATA\TEST\UNDOTBS01

.DBF

channelORA_DISK_1: restoring datafile 00004 to D:\ORACLE\ORADATA\TEST\USERS01.D

BF

channelORA_DISK_1: restoring datafile 00005 to D:\ORACLE\ORADATA\TEST\EXAMPLE01

.DBF

channelORA_DISK_1: restoring datafile 00006 to D:\ORACLE\ORADATA\TEST\NONCRIT.D

BF

channelORA_DISK_1: restoring datafile 00007 to D:\ORACLE\PRODUCT\11.2.0\TEST\DA

TABASE\UNDO_NG01.DBF

channelORA_DISK_1: restoring datafile 00008 to D:\ORACLE\PRODUCT\11.2.0\TEST\DA

TABASE\UNDO_G01.DBF

channelORA_DISK_1: restoring datafile 00009 to D:\ORACLE\ORADATA\TEST\TEST12121

7.DBF

channelORA_DISK_1: reading from backup piece D:\ORACLE\FLASH_RECOVERY_AREA\TEST

\BACKUPSET\2012_12_30\O1_MF_NNNDF_TAG20121230T170800_8G013JGD_.BKP

channelORA_DISK_1: piece handle=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\BACKUPSET\20

12_12_30\O1_MF_NNNDF_TAG20121230T170800_8G013JGD_.BKPtag=TAG20121230T170800

channelORA_DISK_1: restored backup piece 1

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

Finishedrestore at 31-DEC-12

 

 

 

RMAN>recover database;

 

Startingrecover at 31-DEC-12

usingchannel ORA_DISK_1

 

startingmedia recovery

 

archivedlog for thread 1 with sequence 103 is already on disk as file D:\ORACLE

\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2012_12_30\O1_MF_1_103_8G051RW2_.ARC

archivedlog file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2012_12_30\

O1_MF_1_103_8G051RW2_.ARCthread=1 sequence=103

unableto find archived log

archivedlog thread=1 sequence=104

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

RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============

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

RMAN-03002:failure of recover command at 12/31/2012 14:43:00

RMAN-06054:media recovery requesting unknown archived log for thread 1 with seq

uence104 and starting SCN of 2798010

 

这里出错是因为archivelog只有104个记录号；所以只恢复104号记录；

 

 

RMAN>run{

2>set until sequence 104;

3>recover database;}

 

executingcommand: SET until clause

 

Startingrecover at 31-DEC-12

usingchannel ORA_DISK_1

 

startingmedia recovery

mediarecovery complete, elapsed time: 00:00:00

 

Finishedrecover at 31-DEC-12

 

恢复成功；

 

但是打开数据库后，修改temp临时段表空间时系统报错；

RMAN>alter tablespace temp add tempfile 'D:\oracle\oradata\test\temp01.dbf' siz

e 100Mreuse autoextend on next 10M maxsize 1000M;

 

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

RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============

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

RMAN-00558:error encountered while parsing input commands

RMAN-01009:syntax error: found "tablespace": expecting one of:"database"

RMAN-01007:at line 2 column 7 file: standard input

 

RMAN>alter tablespace temp add tempfile 'D:\oracle\oradata\test\temp01.dbf' siz

e 100Mreuse autoextend on next 10M maxsize 1000M;

 

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

RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============

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

RMAN-00558:error encountered while parsing input commands

RMAN-01009:syntax error: found "tablespace": expecting one of:"database"

RMAN-01007:at line 1 column 7 file: standard input

 

RMAN>alter database open resetlogs;

 

Resetlog，系统会根据controlfile中的定义，重新生成redolog组，因为备份并不需要备份redolog，只备份redolog相关的archivelog；

 

此处之前理解的有点问题，Resetlogs会重新生成redolog，如果之前的数据库是正常关闭的，则所有的redolog中的数据都会写入归档联机日志，恢复的时候在做recover的时候会应用到数据库或者相应的表空间当中；

如果之前的数据库使用abort进行的关闭，则redolog联机日志中还有数据没有写入到归档日志当中，所以恢复的时候必须有联机日志才可以进行完全恢复；否则，只能进行不完全恢复；

 

databaseopened

RMAN-06900:WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row

RMAN-06901:WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT row

s

ORACLEerror from target database:

ORA-00604:error occurred at recursive SQL level 1

ORA-06553:PLS-801: internal error [56327]

ORA-06553:PLS-801: internal error [56327]

 

 

RMAN>alter tablespace temp add tempfile 'D:\oracle\oradata\test\temp01.dbf' siz

e 100Mreuse autoextend on next 10M maxsize 1000M;

 

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

RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============

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

RMAN-00558:error encountered while parsing input commands

RMAN-01009:syntax error: found "tablespace": expecting one of:"database"

RMAN-01007:at line 1 column 7 file: standard input

 

RMAN>shutdown immediate;

 

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

RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============

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

RMAN-03002:failure of shutdown command at 12/31/2012 14:46:26

ORA-00604:error occurred at recursive SQL level 1

ORA-06553:PLS-801: internal error [56327]

ORA-06553:PLS-801: internal error [56327]

 

RMAN>alter temporary tablespace temp add tempfile 'D:\oracle\oradata\test\temp0

1.dbf'size 100M reuse autoextend on next 10M maxsize 1000M;

 

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

RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============

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

RMAN-00558:error encountered while parsing input commands

RMAN-01009:syntax error: found "identifier": expecting one of:"database"

RMAN-01008:the bad identifier was: temporary

RMAN-01007:at line 1 column 7 file: standard input

 

RMAN>create temporary tablespace temp1 tempfile 'D:\oracle\oradata\test\temp02.

dbf'size 20M;

 

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

RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============

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

RMAN-00558:error encountered while parsing input commands

RMAN-01009:syntax error: found "identifier": expecting one of: "catalog,global

,restore, script, virtual"

RMAN-01008:the bad identifier was: temporary

RMAN-01007:at line 1 column 8 file: standard input

 

RMAN>exit

 

需要加载到upgrade模式后，重新编译数据库对象；

Shutdownimmediate

 

C:\Users\Administrator>sqlplus/ as sysdba;

 

SQL*Plus:Release 11.2.0.1.0 Production on Mon Dec 31 14:49:22 2012

 

Copyright(c) 1982, 2010, Oracle.  All rightsreserved.

 

 

Connectedto:

OracleDatabase 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

 

 

STARTUPUPGRADE

@@?/rdbms/admin/utlirp.sql

 

编译后shutdown immediate

在startup open;

后

再重新执行@@?/rdbms/admin/utlirp.sql编译oracle 对象；

 

打开数据库修改表空间，成功；