ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > rac10.2.0.4恢复到单机

rac10.2.0.4恢复到单机

Oracle 作者：xfhuangfu 时间：2015-07-04 09:09:25 0 删除 编辑
源端：192.168.10.141:143
目标端：192.168.1.236 ，机器名rac1

在目的端 192.168.1.236 上首先安装oracle 10.2.0.1的软件，然后升级软件到10.2.0.4

oracle 用户的 .bash_profile文件
#set environment
export ORACLE_BASE=/home/oracle
export ORACLE_HOME=$ORACLE_BASE/10.2.0/db_1
export ORACLE_SID=racdb1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin/
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
ulimit -u 16384 -n 65536
umask 02

[oracle@rac1 dbs]$ pwd
/home/oracle/10.2.0/db_1/dbs
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ ls -l
total 32
-rw-rw----  1 oracle dba  1544 Feb 20 22:17 hc_racdb1.dat
-rw-r--r--  1 oracle dba 12920 May  3  2001 initdw.ora
-rw-r-----  1 oracle dba  8385 Sep 11  1998 init.ora

然后将源端rman备份的文件传到目的库192.168.1.236 上

[oracle@rac1 bk]$ ls -l
total 1179904
-rw-r-----  1 oracle dba   32177664 Feb 21 19:53 03p16ucq_1_1
-rw-r-----  1 oracle dba 1159397376 Feb 21 19:52 04p16udf_1_1
-rw-r-----  1 oracle dba   15400960 Feb 21 19:35 05p16uk8_1_1
-rw-r-----  1 oracle dba      49152 Feb 21 19:35 06p16ul9_1_1
[oracle@rac1 bk]$ pwd
/home/oracle/bk

然后登陆目的端的 rman 环境

[oracle@rac1 ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Feb 21 19:46:18 2014
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database (not started)

RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/10.2.0/db_1/dbs/initracdb1.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area     159383552 bytes
Fixed Size                     1266344 bytes
Variable Size                 54529368 bytes
Database Buffers             100663296 bytes
Redo Buffers                   2924544 bytes

根据之前的备份找到spfile文件所在的备份文件，然后恢复spfile

RMAN> restore spfile to pfile '/home/oracle/10.2.0/db_1/dbs/pfile.ora' from '/home/oracle/bk/05p16uk8_1_1';
Starting restore at 21-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /home/oracle/bk/05p16uk8_1_1
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 21-FEB-14

RMAN>

接下来要做的是个体力活，修改pfile.ora中的初始化参数，主要有两方面的修改：
修改含文件路径的参数，达到符合当前服务器环境的实际情况
修改多实例相关的参数

原 pfile 文件内容如下：
[oracle@rac1 dbs]$ more pfile.ora
racdb1.__db_cache_size=427819008
racdb2.__db_cache_size=436207616
racdb1.__java_pool_size=12582912
racdb2.__java_pool_size=4194304
racdb1.__large_pool_size=4194304
racdb2.__large_pool_size=4194304
racdb1.__shared_pool_size=159383552
racdb2.__shared_pool_size=159383552
racdb1.__streams_pool_size=0
racdb2.__streams_pool_size=0
*.audit_file_dest='/home/oracle/admin/racdb/adump'
*.background_dump_dest='/home/oracle/admin/racdb/bdump'
*.cluster_database_instances=2
*.cluster_database=TRUE
*.compatible='10.2.0.1.0'
*.control_files='+DATA/racdb/controlfile/current.260.797085891','+ARCHIVE/racdb/controlfile/current.256.797085893'
*.core_dump_dest='/home/oracle/admin/racdb/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='racdb'
*.db_recovery_file_dest='+ARCHIVE'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
racdb1.instance_number=1
racdb2.instance_number=2
*.job_queue_processes=10
racdb1.local_listener='LISTENER_RACDB1'
racdb2.local_listener='LISTENER_RACDB2'
racdb2.log_archive_dest_1='location=/home/oracle/rac2_arch'
racdb1.log_archive_dest_1='location=/home/oracle/rac1_arch'
*.open_cursors=300
*.processes=150
*.remote_listener='LISTENERS_RACDB'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=608174080
racdb1.thread=1
racdb2.thread=2
*.undo_management='AUTO'
racdb1.undo_tablespace='UNDOTBS1'
racdb2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/home/oracle/admin/racdb/udump'

最终修改完之后 如下：
*.audit_file_dest='/home/oracle/admin/racdb/adump'
*.background_dump_dest='/home/oracle/admin/racdb/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/oracle/oradata/control01.ctl','/home/oracle/oradata/control02.ctl','/home/oracle/oradata/control03.ctl'
*.core_dump_dest='/home/oracle/admin/racdb/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='racdb'
*.job_queue_processes=10
*.log_archive_dest_1='location=/home/oracle/arch'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=608174080
*.pga_aggregate_target=121634816
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/oracle/admin/racdb/udump'

后进入sqlplus命令行环境，通过编辑好的pfile创建spfile，并重启oracle到nomount状态：
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Feb 21 20:18:33 2014
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create spfile from pfile='/home/oracle/10.2.0/db_1/dbs/pfile.ora';
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area  608174080 bytes
Fixed Size                  1268920 bytes
Variable Size             167773000 bytes
Database Buffers          436207616 bytes
Redo Buffers                2924544 bytes
SQL>

接下来要恢复控制文件了，再次进入RMAN命令行，注意操作前别忘了设置dbid（如何获取dbid就不说了吧），执行操作如下：
[oracle@rac1 ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Feb 21 20:25:19 2014
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: racdb (not mounted)
RMAN> set dbid=794702659
executing command: SET DBID
RMAN> restore controlfile from '/home/oracle/bk/05p16uk8_1_1';
Starting restore at 21-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:09
output filename=/home/oracle/oradata/control01.ctl
output filename=/home/oracle/oradata/control02.ctl
output filename=/home/oracle/oradata/control03.ctl
Finished restore at 21-FEB-14
RMAN>

控制文件成功恢复到指定路径下。
下面就可以进入到mount状态了：
RMAN>  alter database mount;
database mounted
released channel: ORA_DISK_1

注册表库备份后生成的归档文件备份集：
RMAN> catalog backuppiece  '/home/oracle/bk/03p16ucq_1_1';
cataloged backuppiece
backup piece handle=/home/oracle/bk/03p16ucq_1_1 recid=3 stamp=840141457
RMAN> catalog backuppiece  '/home/oracle/bk/04p16udf_1_1';
cataloged backuppiece
backup piece handle=/home/oracle/bk/04p16udf_1_1 recid=4 stamp=840141478
RMAN> catalog backuppiece  '/home/oracle/bk/05p16uk8_1_1';
cataloged backuppiece
backup piece handle=/home/oracle/bk/05p16uk8_1_1 recid=5 stamp=840141486

RMAN> catalog backuppiece  '/home/oracle/bk/06p16ul9_1_1';
cataloged backuppiece
backup piece handle=/home/oracle/bk/06p16ul9_1_1 recid=6 stamp=840141499
RMAN>

由于之前在RMAN中配置了默认通道，这里也要将这些配置清除，操作如下：
RMAN> configure channel 1 device type disk clear;
old RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT '*';
old RMAN configuration parameters are successfully deleted

RMAN> configure channel 2 device type disk clear;
old RMAN configuration parameters:
CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT '*';
old RMAN configuration parameters are successfully deleted

RMAN> configure device type disk clear;
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
RMAN configuration parameters are successfully reset to default value
RMAN>

接下来先别忙着做restore，有下列文件是需要我们预先进行处理的：
SQL> col name format a55;
SQL> select file#,name from v$datafile;
     FILE# NAME
---------- -------------------------------------------------------
         1 +DATA/racdb/datafile/system.256.797085793
         2 +DATA/racdb/datafile/undotbs1.258.797085795
         3 +DATA/racdb/datafile/sysaux.257.797085795
         4 +DATA/racdb/datafile/users.259.797085795
         5 +DATA/racdb/datafile/example.264.797085917
         6 +DATA/racdb/datafile/undotbs2.265.797086133

6 rows selected.

SQL> select member from v$logfile;
MEMBER
---------------------------------------------------------------------------
+DATA/racdb/onlinelog/group_2.262.797085897
+ARCHIVE/racdb/onlinelog/group_2.258.797085897
+DATA/racdb/onlinelog/group_1.261.797085895
+ARCHIVE/racdb/onlinelog/group_1.257.797085897
+DATA/racdb/onlinelog/group_3.266.797086191
+ARCHIVE/racdb/onlinelog/group_3.259.797086193
+DATA/racdb/onlinelog/group_4.267.797086193
+ARCHIVE/racdb/onlinelog/group_4.260.797086193

8 rows selected.

SQL> select file#,name from v$tempfile;
     FILE# NAME
---------- -------------------------------------------------------
         1 +DATA/racdb/tempfile/temp.263.797085905
SQL>

RMAN 中的SET命令可以用来为数据文件和临时文件重命名，这里就使用set命令
对数据文件和临时文件的路径进行重定义，然后再执行恢复操作，如下：

run {
SET NEWNAME FOR DATAFILE 1 to '/home/oracle/oradata/system01.dbf';
SET NEWNAME FOR DATAFILE 2 to '/home/oracle/oradata/undo01.dbf';
SET NEWNAME FOR DATAFILE 3 to '/home/oracle/oradata/sysaux.dbf';
SET NEWNAME FOR DATAFILE 4 to '/home/oracle/oradata/users.dbf';
SET NEWNAME FOR DATAFILE 5 to '/home/oracle/oradata/example.dbf';
SET NEWNAME FOR DATAFILE 6 to '/home/oracle/oradata/undotbs.dbf';
SET NEWNAME FOR TEMPFILE 1 to '/home/oracle/oradata/temp01.dbf';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
}

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 21-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/oracle/oradata/system01.dbf
restoring datafile 00002 to /home/oracle/oradata/undo01.dbf
restoring datafile 00003 to /home/oracle/oradata/sysaux.dbf
restoring datafile 00004 to /home/oracle/oradata/users.dbf
restoring datafile 00005 to /home/oracle/oradata/example.dbf
restoring datafile 00006 to /home/oracle/oradata/undotbs.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/04p16udf_1_1
channel ORA_DISK_1: restored backup piece 1
failover to piece handle=/home/oracle/bk/04p16udf_1_1 tag=TAG20140221T192534
channel ORA_DISK_1: restore complete, elapsed time: 00:02:17
Finished restore at 21-FEB-14

datafile 1 switched to datafile copy
input datafile copy recid=8 stamp=840141754 filename=/home/oracle/oradata/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=9 stamp=840141755 filename=/home/oracle/oradata/undo01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=10 stamp=840141755 filename=/home/oracle/oradata/sysaux.dbf
datafile 4 switched to datafile copy
input datafile copy recid=11 stamp=840141756 filename=/home/oracle/oradata/users.dbf
datafile 5 switched to datafile copy
input datafile copy recid=12 stamp=840141756 filename=/home/oracle/oradata/example.dbf
datafile 6 switched to datafile copy
input datafile copy recid=13 stamp=840141756 filename=/home/oracle/oradata/undotbs.dbf
renamed temporary file 1 to /home/oracle/oradata/temp01.dbf in control file
RMAN>

对数据库执行recover，由于我们只复制了备份集，而没有复制源库中新归档以及online redo文件，
这里只能进行不完全的恢复(创建的备份并非一致性冷备份)，也就是说，
报错是必然的，不过没关系，只要能修复到一致性状态就好：

RMAN> recover database;
Starting recover at 21-FEB-14
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=34
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=73
channel ORA_DISK_1: reading from backup piece /home/oracle/bk/06p16ul9_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/bk/06p16ul9_1_1 tag=TAG20140221T192944
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/home/oracle/arch/2_34_797085894.dbf thread=2 sequence=34
archive log filename=/home/oracle/arch/1_73_797085894.dbf thread=1 sequence=73
unable to find archive log
archive log thread=2 sequence=35
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/21/2014 20:44:38
RMAN-06054: media recovery requesting unknown log: thread 2 seq 35 lowscn 2987304
RMAN>

接下来，我们不得不重建控制文件，以修复重做日志文件的路径：
sql[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Feb 21 20:45:21 2014
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database backup controlfile to trace;
Database altered.
SQL>

你也许在想，可以通过"alter database rename file"方式修改重做日志文件路径的啊，
事实上"alter database rename file"方式极有可能触发
" ORA-00600: internal error code, arguments: [kgeade_is_0], [], [], [], [], [], [], [] " 错误，
经查这是ORACLE的一个BUG，对应BUG为7207932，通常是RAC环境从asm向文件系统迁移时被触发，
在10204版本中依然存在，Doc ID: 742289.1对此有详细说明，号称11g版本中对该问题进行了修复。

获取控制文件创建脚本之后，稍加修改(主要是改redolog的路径)，然后在sqlplus命令行环境下执行：
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Feb 21 20:56:40 2014
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
SQL> startup nomount;
ORACLE instance started.
Total System Global Area  608174080 bytes
Fixed Size                  1268920 bytes
Variable Size             167773000 bytes
Database Buffers          436207616 bytes
Redo Buffers                2924544 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "RACDB" RESETLOGS  ARCHIVELOG
        MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/home/oracle/oradata/redo01.log' SIZE 50M,
  GROUP 2 '/home/oracle/oradata/redo02.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/home/oracle/oradata/system01.dbf',
  '/home/oracle/oradata/undo01.dbf',
  '/home/oracle/oradata/sysaux.dbf',
  '/home/oracle/oradata/users.dbf',
  '/home/oracle/oradata/example.dbf',
  '/home/oracle/oradata/undotbs.dbf'
CHARACTER SET ZHS16GBK
;
Control file created.

重建控制文件的过程中，注意只添加 GROUP 1、GROUP 2 两组日志即可
如果不执行如下添加线程2的日志组命令，将会导致 ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

SQL> ALTER DATABASE ADD LOGFILE THREAD 2  GROUP 3 '/home/oracle/oradata/redo03.log' SIZE 50M,
     GROUP 4 '/home/oracle/oradata/redo04.log' SIZE 50M;
Database altered.
 
SQL> alter database open resetlogs;
Database altered.

接下来别忘了重建临时表空间的数据文件：
SQL>  ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/oradata/tmp01.dbf' size 50m;
Tablespace altered.


清除未使用线程的redo日志组，操作如下
SQL> select thread#,status,enabled from v$thread;
   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED PRIVATE

SQL> select group#,thread#,archived,status from v$log;
    GROUP#    THREAD# ARC STATUS
---------- ---------- --- ----------------
         1          1 NO  CURRENT
         2          1 YES UNUSED
         3          2 NO  CURRENT
         4          2 YES UNUSED

SQL> alter database disable thread 2;
Database altered.

SQL>  alter database drop logfile group 3;
 alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance UNNAMED_INSTANCE_2 (thread 2) needs to be archived
ORA-00312: online log 3 thread 2: '/home/oracle/oradata/redo03.log'

SQL> alter database drop logfile group 4;
Database altered.

SQL> alter database clear unarchived logfile group 3;
Database altered.

SQL> alter database drop logfile group 3;
Database altered.

清除多余的undo文件

SQL> select name from v$tablespace where name like 'UNDO%';
NAME
------------------------------
UNDOTBS1
UNDOTBS2

SQL>  show parameter undo_tablespace;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1

SQL> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped

至此，rac恢复到单实例完毕

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28373936/viewspace-1722276/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： oracle10g 物理standby dataguard 创建过程
下一篇： ORACLE 10gR2 RAC升级至10.2.0.4
请登录后发表评论 登录
全部评论
xfhuangfu
专家姓名：皇甫晓飞
Xiaofei Huangfu Oracle DBA，Oracle ACE Oracle 19c OCM12c OCM,11g OCM MySQL 5.6,5.7,8.0 OCP 18年以上IT从业经验，有丰富的数据库和开发领域经验，多年Oracle运维经验和java开发经验。

注册时间：2013-09-05

  • 博文量
    151
  • 访问量
    231238

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593