合 Oracle单实例备份集恢复到RAC集群

 

0    421    1

Tags：

👉 本文共约31920个字，系统预计阅读时间或需120分钟。

前言部分

导读

各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：

① 单实例环境的备份集如何恢复到rac环境(重点)

② rman恢复数据库的一般步骤

③ rac环境的简单操作

实验环境介绍

源库：11.2.0.1 RHEL6.5 单实例

目标库：11.2.0.1 RHEL6.5 rac环境

本文简介

本文基于如何将单实例的数据库备份恢复到rac环境下，至于rac环境的备份集如何恢复到单实例及rac环境的备份集恢复到rac环境的实验请参考相关文章链接部分。

实验的一些数据库环境参考如下表格：

项目source dbtarget db
db 类型单实例rac环境
db version11.2.0.111.2.0.1
ORACLE_SIDorastracorastrac1 和 orastrac2
db_nameorastracorastrac
主机IP地址：192.168.59.129192.168.1.31 192.168.1.32

先描述下大致步骤：

  • 源端创建备份集；
  • 目标端安装数据库软件和集群件，并配置好共享存储（安装rac的时候一般已经配置好了）；
  • 复制源端备份集到目标端；
  • 目录端任意节点执行正常恢复，恢复时注意要将 spfile,controlfile,datafile,redofile 等路径改到共享存储上，恢复完后这会儿仍然是个单实例数据库；
  • 修改初始化参数、增加 UNDO 表空间、增加 REDOLOG 线程组，重建密钥文件，目标端任意节点执行；
  • 目标端各个节点配置监听及网络服务名；
  • 将新建的数据库配置到 crs，目标端任意节点执行即可。

实验部分

实验目标

将单实例的备份集成功的恢复到rac环境下，并添加数据库到crs环境。

源库操作

source库上需要做的操作主要是备份和创建测试用户。

静默创建一个单实例的测试库

首先修改归档模式，这样创建的数据库默认为归档模式，然后我们在源库上静默创建一个单实例的库，sid为orastrac，为oracle single instance to rac，关于静默安装数据库参考：

静默安装系列
【推荐】 【DBCA -SILENT】静默方式安装11gR2 oracle数据库软件http://blog.itpub.net/26736162/viewspace-1589769/
【推荐】 【DBCA -SILENT】静默安装之rac数据库安装http://blog.itpub.net/26736162/viewspace-1586352/
【推荐】 【DBCA -SILENT】静默安装如何启用归档模式http://blog.itpub.net/26736162/viewspace-1585925/
【推荐】 DBCA静默方式建库http://blog.itpub.net/26736162/viewspace-1448220/

[oracle@orcltest ~]$ echo $ORACLE_HOME

/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@orcltest ~]$ sqlplus -v

SQL*Plus: Release 11.2.0.1.0 Production

[oracle@orcltest ~]$ strings $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc | grep -i arch

\<archiveLogMode>false\</archiveLogMode>

[oracle@orcltest ~]$ vi $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc

\<?xml version = '1.0'?>

\<DatabaseTemplate name="General_Purpose" description="" version="11.1.0.0.0">

\<CommonAttributes>

\<option name="OMS" value="false"/>

\<option name="JSERVER" value="true"/>

\<option name="SPATIAL" value="true"/>

\<option name="IMEDIA" value="true"/>

\<option name="XDB_PROTOCOLS" value="true">

\<tablespace id="SYSAUX"/>

\</option>

\<option name="ORACLE_TEXT" value="true">

\<tablespace id="SYSAUX"/>

\</option>

\<option name="SAMPLE_SCHEMA" value="false"/>

\<option name="CWMLITE" value="true">

\<tablespace id="SYSAUX"/>

\</option>

\<option name="EM_REPOSITORY" value="true">

\<tablespace id="SYSAUX"/>

\</option>

\<option name="APEX" value="true"/>

\<option name="OWB" value="true"/>

\<option name="DV" value="false"/>

\</CommonAttributes>

\<Variables/>

\<CustomScripts Execute="false"/>

\<InitParamAttributes>

\<InitParams>

\<initParam name="db_name" value=""/>

\<initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE={SID}XDB)"/>

\<initParam name="audit_file_dest" value="{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump"/>

\<initParam name="compatible" value="11.2.0.0.0"/>

\<initParam name="remote_login_passwordfile" value="EXCLUSIVE"/>

\<initParam name="processes" value="150"/>

\<initParam name="undo_tablespace" value="UNDOTBS1"/>

\<initParam name="control_files" value="("{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl", \"{ORACLE_BASE}/flash_recovery_area/{DB_UNIQUE_NAME}/control02.ctl")"/>

\<initParam name="diagnostic_dest" value="{ORACLE_BASE}"/>

\<initParam name="db_recovery_file_dest" value="{ORACLE_BASE}/flash_recovery_area"/>

\<initParam name="audit_trail" value="db"/>

\<initParam name="memory_target" value="250" unit="MB"/>

\<initParam name="db_block_size" value="8" unit="KB"/>

\<initParam name="open_cursors" value="300"/>

\<initParam name="db_recovery_file_dest_size" value="" unit="MB"/>

\</InitParams>

\<MiscParams>

\<databaseType>MULTIPURPOSE\</databaseType>

\<maxUserConn>20\</maxUserConn>

\<percentageMemTOSGA>40\</percentageMemTOSGA>

\<customSGA>false\</customSGA>

\<archiveLogMode>true\</archiveLogMode>

\<initParamFileName>{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/pfile/init.ora\</initParamFileName>

\</MiscParams>

\<SPfile useSPFile="true">{ORACLE_HOME}/dbs/spfile{SID}.ora\</SPfile>

\</InitParamAttributes>

\<StorageAttributes>

\<DataFiles>

"/u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/General_Purpose.dbc" 95L, 4985C written

[oracle@orcltest ~]$ strings $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc | grep -i arch

\<archiveLogMode>true\</archiveLogMode>

[oracle@orcltest ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname orastrac -sid orastrac -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination /u01/app/oracle/oradata -redoLogFileSize 50 -recoveryAreaDestination /u01/app/oracle/flash_recovery_area -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -totalMemory 200 -databaseType OLTP -emConfiguration NONE -automaticMemoryManagement true

Copying database files

1% complete

3% complete

10% complete

17% complete

24% complete

35% complete

Creating and starting Oracle instance

37% complete

42% complete

47% complete

52% complete

53% complete

56% complete

58% complete

Registering database with Oracle Restart

64% complete

Completing Database Creation

68% complete

71% complete

75% complete

85% complete

86% complete

96% complete

100% complete

Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/orastrac/orastrac.log" for further details.

[oracle@orcltest ~]$

[oracle@orcltest ~]$ ORACLE_SID=orastrac

[oracle@orcltest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 1 11:09:02 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter name

NAME TYPE VALUE

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

db_file_name_convert string

db_name string orastrac

db_unique_name string orastrac

global_names boolean FALSE

instance_name string orastrac

lock_name_space string

log_file_name_convert string

service_names string orastrac

SQL> create user lhr identified by lhr;

User created.

SQL> grant dba to lhr;

Grant succeeded.

SQL> create table lhr.test_rac as select * from dba_objects;

Table created.

SQL> select count(1) FROM LHR.TEST_RAC;

COUNT(1)

----------

72468

SQL>

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 3

Next log sequence to archive 5

Current log sequence 5

SQL>

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/spfileorastrac.o

ra

SQL>

source库执行备份操作

备份脚本如下，注意控制文件需要最后备份：

run

{

allocate channel c1 type disk;

allocate channel c2 type disk;

backup database format '/home/oracle/rmanback/full%n%T%t%s%p.bak';

sql 'alter system archive log current';

backup archivelog all format '/home/oracle/rmanback/arch%d%T%s_%p.bak' delete input;

backup current controlfile format '/home/oracle/rmanback/ctl%d%T%s_%p.bak';

release channel c1;

release channel c2;

}

执行过程如下：

[oracle@orcltest ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jun 1 11:12:15 2015

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

connected to target database: ORASTRAC (DBID=1317814272)

RMAN> run

2> {

3> allocate channel c1 type disk;

4> allocate channel c2 type disk;

5> backup database format '/home/oracle/rmanback/full%n%T%t%s%p.bak';

6> sql 'alter system archive log current';

7> backup archivelog all format '/home/oracle/rmanback/arch%d%T%s_%p.bak' delete input;

8> backup current controlfile format '/home/oracle/rmanback/ctl%d%T%s_%p.bak';

9> release channel c1;

10> release channel c2;

11> }

using target database control file instead of recovery catalog

allocated channel: c1

channel c1: SID=142 device type=DISK

allocated channel: c2

channel c2: SID=20 device type=DISK

Starting backup at 2015-06-01 11:12:28

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/orastrac/system01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/orastrac/users01.dbf

channel c1: starting piece 1 at 2015-06-01 11:12:29

channel c2: starting full datafile backup set

channel c2: specifying datafile(s) in backup set

input datafile file number=00002 name=/u01/app/oracle/oradata/orastrac/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/orastrac/example01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/orastrac/undotbs01.dbf

channel c2: starting piece 1 at 2015-06-01 11:12:29

channel c2: finished piece 1 at 2015-06-01 11:15:26

piece handle=/home/oracle/rman_back/full_ORASTRAC_20150601_881233949_2_1.bak tag=TAG20150601T111228 comment=NONE

channel c2: backup set complete, elapsed time: 00:02:59

channel c2: starting full datafile backup set

channel c2: specifying datafile(s) in backup set

including current control file in backup set

channel c2: starting piece 1 at 2015-06-01 11:15:48

channel c2: finished piece 1 at 2015-06-01 11:15:49

piece handle=/home/oracle/rman_back/full_ORASTRAC_20150601_881234138_3_1.bak tag=TAG20150601T111228 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:01

channel c2: starting full datafile backup set

channel c2: specifying datafile(s) in backup set

including current SPFILE in backup set

channel c2: starting piece 1 at 2015-06-01 11:15:50

channel c2: finished piece 1 at 2015-06-01 11:15:51

piece handle=/home/oracle/rman_back/full_ORASTRAC_20150601_881234150_4_1.bak tag=TAG20150601T111228 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:01

channel c1: finished piece 1 at 2015-06-01 11:15:53

piece handle=/home/oracle/rman_back/full_ORASTRAC_20150601_881233948_1_1.bak tag=TAG20150601T111228 comment=NONE

channel c1: backup set complete, elapsed time: 00:03:24

Finished backup at 2015-06-01 11:15:53

sql statement: alter system archive log current

Starting backup at 2015-06-01 11:16:05

current log archived

channel c1: starting archived log backup set

channel c1: specifying archived log(s) in backup set

input archived log thread=1 sequence=5 RECID=1 STAMP=881234164

channel c1: starting piece 1 at 2015-06-01 11:16:05

channel c2: starting archived log backup set

channel c2: specifying archived log(s) in backup set

input archived log thread=1 sequence=6 RECID=2 STAMP=881234165

channel c2: starting piece 1 at 2015-06-01 11:16:06

channel c1: finished piece 1 at 2015-06-01 11:16:09

piece handle=/home/oracle/rman_back/arch_ORASTRAC_20150601_5_1.bak tag=TAG20150601T111605 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:04

channel c1: deleting archived log(s)

archived log file name=/u01/app/oracle/flash_recovery_area/ORASTRAC/archivelog/2015_06_01/o1_mf_1_5bpqmmhbp.arc RECID=1 STAMP=881234164

channel c2: finished piece 1 at 2015-06-01 11:16:10

piece handle=/home/oracle/rman_back/arch_ORASTRAC_20150601_6_1.bak tag=TAG20150601T111605 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:04

channel c2: deleting archived log(s)

archived log file name=/u01/app/oracle/flash_recovery_area/ORASTRAC/archivelog/2015_06_01/o1_mf_1_6bpqmmo4t.arc RECID=2 STAMP=881234165

Finished backup at 2015-06-01 11:16:10

Starting backup at 2015-06-01 11:16:10

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

including current control file in backup set

channel c1: starting piece 1 at 2015-06-01 11:16:11

channel c1: finished piece 1 at 2015-06-01 11:16:12

piece handle=/home/oracle/rman_back/ctl_ORASTRAC_20150601_7_1.bak tag=TAG20150601T111610 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

Finished backup at 2015-06-01 11:16:12

released channel: c1

released channel: c2

RMAN> list backupset;

List of Backup Sets

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

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

1 Full 501.84M DISK 00:02:42 2015-06-01 11:15:11

BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20150601T111228

Piece Name: /home/oracle/rman_back/full_ORASTRAC_20150601_881233949_2_1.bak

List of Datafiles in backup set 1

File LV Type Ckp SCN Ckp Time Name

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

2 Full 1027268 2015-06-01 11:12:29 /u01/app/oracle/oradata/orastrac/sysaux01.dbf

3 Full 1027268 2015-06-01 11:12:29 /u01/app/oracle/oradata/orastrac/undotbs01.dbf

5 Full 1027268 2015-06-01 11:12:29 /u01/app/oracle/oradata/orastrac/example01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

2 Full 9.33M DISK 00:00:10 2015-06-01 11:15:48

BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20150601T111228

Piece Name: /home/oracle/rman_back/full_ORASTRAC_20150601_881234138_3_1.bak

Control File Included: Ckp SCN: 1027334 Ckp time: 2015-06-01 11:15:38

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

3 Full 80.00K DISK 00:00:00 2015-06-01 11:15:50

BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20150601T111228

Piece Name: /home/oracle/rman_back/full_ORASTRAC_20150601_881234150_4_1.bak

SPFILE Included: Modification time: 2015-06-01 11:12:28

SPFILE db_unique_name: ORASTRAC

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

4 Full 599.97M DISK 00:03:24 2015-06-01 11:15:52

BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20150601T111228

Piece Name: /home/oracle/rman_back/full_ORASTRAC_20150601_881233948_1_1.bak

List of Datafiles in backup set 4

File LV Type Ckp SCN Ckp Time Name

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

1 Full 1027267 2015-06-01 11:12:29 /u01/app/oracle/oradata/orastrac/system01.dbf

4 Full 1027267 2015-06-01 11:12:29 /u01/app/oracle/oradata/orastrac/users01.dbf

BS Key Size Device Type Elapsed Time Completion Time

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

5 41.16M DISK 00:00:02 2015-06-01 11:16:07

BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20150601T111605

Piece Name: /home/oracle/rman_back/arch_ORASTRAC_20150601_5_1.bak

List of Archived Logs in backup set 5

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 5 1012317 2015-06-01 11:04:50 1027356 2015-06-01 11:15:59

BS Key Size Device Type Elapsed Time Completion Time

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

6 2.00K DISK 00:00:02 2015-06-01 11:16:07

BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20150601T111605

Piece Name: /home/oracle/rman_back/arch_ORASTRAC_20150601_6_1.bak

List of Archived Logs in backup set 6

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 6 1027356 2015-06-01 11:15:59 1027365 2015-06-01 11:16:05

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

7 Full 9.33M DISK 00:00:02 2015-06-01 11:16:12

BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20150601T111610

Piece Name: /home/oracle/rman_back/ctl_ORASTRAC_20150601_7_1.bak

Control File Included: Ckp SCN: 1027386 Ckp time: 2015-06-01 11:16:10

RMAN> list backupset summary;

List of Backups

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

Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

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

1 B F A DISK 2015-06-01 11:15:11 1 1 NO TAG20150601T111228

2 B F A DISK 2015-06-01 11:15:48 1 1 NO TAG20150601T111228

3 B F A DISK 2015-06-01 11:15:50 1 1 NO TAG20150601T111228

4 B F A DISK 2015-06-01 11:15:52 1 1 NO TAG20150601T111228

5 B A A DISK 2015-06-01 11:16:07 1 1 NO TAG20150601T111605

6 B A A DISK 2015-06-01 11:16:07 1 1 NO TAG20150601T111605

7 B F A DISK 2015-06-01 11:16:12 1 1 NO TAG20150601T111610

RMAN>

将备份传递到target库

这个方法就多了，可以采用ftp上传下载，也可以采用NFS网络文件系统，或者scp命令都可以，这里我们采用scp直接传递到rac环境的第一个节点。

源库scp操作：

[oracle@orcltest ~]$ cd rman_back/

[oracle@orcltest rman_back]$ ll

total 1189660

-rw-r----- 1 oracle asmadmin 43154944 Jun 1 11:16 arch_ORASTRAC_20150601_5_1.bak

-rw-r----- 1 oracle asmadmin 2560 Jun 1 11:16 arch_ORASTRAC_20150601_6_1.bak

-rw-r----- 1 oracle asmadmin 9797632 Jun 1 11:16 ctl_ORASTRAC_20150601_7_1.bak

-rw-r----- 1 oracle asmadmin 629121024 Jun 1 11:15 full_ORASTRAC_20150601_881233948_1_1.bak

-rw-r----- 1 oracle asmadmin 526229504 Jun 1 11:14 full_ORASTRAC_20150601_881233949_2_1.bak

-rw-r----- 1 oracle asmadmin 9797632 Jun 1 11:15 full_ORASTRAC_20150601_881234138_3_1.bak

-rw-r----- 1 oracle asmadmin 98304 Jun 1 11:15 full_ORASTRAC_20150601_881234150_4_1.bak

[oracle@orcltest rman_back]$ ll -h

total 1.2G

-rw-r----- 1 oracle asmadmin 42M Jun 1 11:16 arch_ORASTRAC_20150601_5_1.bak

-rw-r----- 1 oracle asmadmin 2.5K Jun 1 11:16 arch_ORASTRAC_20150601_6_1.bak

-rw-r----- 1 oracle asmadmin 9.4M Jun 1 11:16 ctl_ORASTRAC_20150601_7_1.bak

-rw-r----- 1 oracle asmadmin 600M Jun 1 11:15 full_ORASTRAC_20150601_881233948_1_1.bak

-rw-r----- 1 oracle asmadmin 502M Jun 1 11:14 full_ORASTRAC_20150601_881233949_2_1.bak

-rw-r----- 1 oracle asmadmin 9.4M Jun 1 11:15 full_ORASTRAC_20150601_881234138_3_1.bak

-rw-r----- 1 oracle asmadmin 96K Jun 1 11:15 full_ORASTRAC_20150601_881234150_4_1.bak

[oracle@orcltest rman_back]$

[oracle@orcltest rman_back]$ scp -r /home/oracle/rman_back oracle@192.168.1.31:/home/oracle

oracle@192.168.1.31's password:

ctl_ORASTRAC_20150601_7_1.bak 100% 9.4MB 17.7MB/s 00:01

arch_ORASTRAC_20150601_5_1.bak 100% 42MB 27.0MB/s 00:03

arch_ORASTRAC_20150601_6_1.bak 100% 2.5KB 16.5MB/s 00:02

full_ORASTRAC_20150601_881233948_1_1.bak 100% 18MB 17.7MB/s 00:01

full_ORASTRAC_20150601_881233949_2_1.bak 100% 618MB 12.4MB/s 00:50

full_ORASTRAC_20150601_881234138_3_1.bak 100% 500MB 15.2MB/s 00:33

full_ORASTRAC_20150601_881234150_4_1.bak 100% 96KB 96.0KB/s 00:00

[oracle@node2 rman_back]$

target库查看备份文件：

[oracle@node1 rman_back]$ ll -h

total 1.2G

-rw-r----- 1 oracle oinstall 42M Jun 1 11:19 arch_ORASTRAC_20150601_5_1.bak

-rw-r----- 1 oracle oinstall 2.5K Jun 1 11:23 arch_ORASTRAC_20150601_6_1.bak

-rw-r----- 1 oracle oinstall 9.4M Jun 1 11:21 ctl_ORASTRAC_20150601_7_1.bak

-rw-r----- 1 oracle oinstall 600M Jun 1 11:23 full_ORASTRAC_20150601_881233948_1_1.bak

-rw-r----- 1 oracle oinstall 502M Jun 1 11:21 full_ORASTRAC_20150601_881233949_2_1.bak

-rw-r----- 1 oracle oinstall 9.4M Jun 1 11:21 full_ORASTRAC_20150601_881234138_3_1.bak

-rw-r----- 1 oracle oinstall 96K Jun 1 11:23 full_ORASTRAC_20150601_881234150_4_1.bak

[oracle@node1 rman_back]$

target库操作

以下操作若无特殊说明均在节点一操作。

查看rac环境

target库已经有3个库，都处于close状态。

[root@node2 ~]# crsstat

Name Type Target State Host

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

ora.ARCH.dg ora.diskgroup.type ONLINE ONLINE node1

ora.DATA.dg ora.diskgroup.type ONLINE ONLINE node1

ora.LISTENER.lsnr ora.listener.type ONLINE ONLINE node1

ora.LISTENER_SCAN1.lsnr ora.scan_listener.type ONLINE ONLINE node1

ora.OVDISK.dg ora.diskgroup.type ONLINE ONLINE node1

ora.TEST.dg ora.diskgroup.type ONLINE ONLINE node1

ora.asm ora.asm.type ONLINE ONLINE node1

ora.db.db ora.database.type OFFLINE OFFLINE

ora.eons ora.eons.type ONLINE ONLINE node1

ora.gsd ora.gsd.type OFFLINE OFFLINE

ora.jmrac.db ora.database.type ONLINE ONLINE node1

ora.jmrac.haha.svc ora.service.type ONLINE ONLINE node1

ora.net1.network ora.network.type ONLINE ONLINE node1

ora.node1.ASM1.asm application ONLINE ONLINE node1

ora.node1.LISTENER_NODE1.lsnr application ONLINE ONLINE node1

ora.node1.gsd application OFFLINE OFFLINE

ora.node1.ons application ONLINE ONLINE node1

ora.node1.vip ora.cluster_vip_net1.type ONLINE ONLINE node1

ora.node2.ASM2.asm application ONLINE ONLINE node2

ora.node2.LISTENER_NODE2.lsnr application ONLINE ONLINE node2

ora.node2.gsd application OFFLINE OFFLINE

ora.node2.ons application ONLINE ONLINE node2

ora.node2.vip ora.cluster_vip_net1.type ONLINE ONLINE node2

ora.oc4j ora.oc4j.type OFFLINE OFFLINE

ora.ons ora.ons.type ONLINE ONLINE node1

ora.ora11g.db ora.database.type OFFLINE OFFLINE

ora.registry.acfs ora.registry.acfs.type ONLINE ONLINE node1

ora.scan1.vip ora.scan_vip.type ONLINE ONLINE node1

[root@node2 ~]# crsstat | grep ora.database.type

ora.db.db ora.database.type OFFLINE OFFLINE

ora.jmrac.db ora.database.type OFFLINE OFFLINE

ora.ora11g.db ora.database.type OFFLINE OFFLINE

[root@node2 ~]#

查看磁盘组的情况，确保有足够的空间来还原数据库：

[root@node2 ~]# su - grid

[grid@node2 ~]$ asmcmd

ASMCMD>

ASMCMD> lsdg

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 4096 1048576 9999 8905 0 8905 0 N ARCH/

MOUNTED EXTERN N 512 4096 1048576 19999 11604 0 11604 0 N DATA/

MOUNTED EXTERN N 512 4096 1048576 3099 2703 0 2703 0 N OVDISK/

MOUNTED EXTERN N 512 4096 1048576 1024 929 0 929 0 N TEST/

ASMCMD>

[root@node2 ~]# cat /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1 localhost.localdomain localhost

::1 localhost6.localdomain6 localhost6

#public

192.168.1.31 node1

192.168.1.32 node2

#vip

192.168.1.131 node1-vip

192.168.1.132 node2-vip

#priv

9.9.9.31 node1-priv

9.9.9.32 node2-priv

#scan

192.168.1.35 cluster-scan

[root@node2 ~]# ifconfig

eth0 Link encap:Ethernet HWaddr 00:0C:29:79:BA:86

inet addr:192.168.1.32 Bcast:192.168.1.255 Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

RX packets:150190 errors:0 dropped:0 overruns:0 frame:0

TX packets:109804 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:205303912 (195.7 MiB) TX bytes:20182601 (19.2 MiB)

eth0:1 Link encap:Ethernet HWaddr 00:0C:29:79:BA:86

inet addr:192.168.1.132 Bcast:192.168.1.255 Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

eth1 Link encap:Ethernet HWaddr 00:0C:29:79:BA:90

inet addr:9.9.9.32 Bcast:9.9.9.255 Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

RX packets:49075 errors:0 dropped:0 overruns:0 frame:0

TX packets:49811 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:23642469 (22.5 MiB) TX bytes:31528595 (30.0 MiB)

lo Link encap:Local Loopback

inet addr:127.0.0.1 Mask:255.0.0.0

UP LOOPBACK RUNNING MTU:16436 Metric:1

RX packets:16496 errors:0 dropped:0 overruns:0 frame:0

TX packets:16496 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:0

RX bytes:15118447 (14.4 MiB) TX bytes:15118447 (14.4 MiB)

[root@node2 ~]#

恢复spfile到pfile，修改pfile，创建相关路径：

首先利用rman恢复spfile：

[oracle@node1 rman_back]$ ORACLE_SID=orastrac

[oracle@node1 rman_back]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jun 1 13:25:33 2015

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

connected to target database (not started)

RMAN> set dbid 1317814272

executing command: SET DBID

RMAN> startup nomount;

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

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorastrac.ora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area 158662656 bytes

Fixed Size 2211448 bytes

Variable Size 96469384 bytes

Database Buffers 54525952 bytes

Redo Buffers 5455872 bytes

RMAN> restore spfile to pfile '?/dbs/initorastrac.ora' from '/home/oracle/rman_back/full_ORASTRAC_20150601_881234150_4_1.bak';

Starting restore at 01-JUN-2015 13:25:59

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=25 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/rman_back/full_ORASTRAC_20150601_881234150_4_1.bak

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 01-JUN-2015 13:26:00

RMAN>

RMAN> exit

Recovery Manager complete.

[oracle@node1 rman_back]$ ll /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorastrac.ora

-rw-r--r-- 1 oracle asmadmin 997 Jun 1 13:25 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorastrac.ora

[oracle@node1 rman_back]$

[oracle@node1 rman_back]$ more /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorastrac.ora

orastrac.__db_cache_size=121634816

orastrac.__java_pool_size=4194304

orastrac.__large_pool_size=4194304

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

orastrac.__pga_aggregate_target=167772160

orastrac.__sga_target=243269632

orastrac.__shared_io_pool_size=0

orastrac.__shared_pool_size=100663296

orastrac.__streams_pool_size=0

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

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/orastrac/control01.ctl','/u01/app/oracle/flash_recovery_area/orastrac/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orastrac'

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

*.db_recovery_file_dest_size=4070572032

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

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

*.log_archiveformat='%t%s_%r.dbf'

*.memory_target=408944640

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

[oracle@node1 rman_back]$

修改与路径相关的参数，修改后如下：

[oracle@node1 dbs]$ more initorastrac.ora

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

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='+DATA/orastrac/controlfile/control01.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orastrac'

*.db_recovery_file_dest='+ARCH'

*.db_recovery_file_dest_size=4070572032

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

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

*.log_archiveformat='%t%s_%r.dbf'

*.memory_target=408944640

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

插曲：注意，这里如果想让控制文件的格式为ASM格式，可以这样设置控制文件的路径：

pfile文件中的control_files这样写： *.control_files='+DATA','+ARCH'，然后还原的时候就可以直接还原为asm格式的文件了，我这里由于是后边补上的，所以就不采用如下的形式了，如下：

RMAN> restore controlfile from '/home/oracle/rman_back/ctl_ORASTRAC_20150601_7_1.bak';

Starting restore at 01-JUN-2015 17:59:39

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=+DATA/orastrac/controlfile/current.357.881258379

output file name=+ARCH/orastrac/controlfile/current.369.881258379

Finished restore at 01-JUN-2015 17:59:40

RMAN>

创建os文件路径，注意在节点一和节点二都执行：

[oracle@node1 ~]$ mkdir -p /u01/app/oracle/admin/orastrac/adump

[oracle@node1 ~]$

注意哟，创建的 spfile 可是要放到共享存储上去的：

[oracle@node1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 1 15:49:22 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> create spfile='+DATA' from pfile;

File created.

SQL>

File created.

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string

SQL>

查看新创建的spfile的文件名：

ASMCMD> pwd

+DATA/orastrac/PARAMETERFILE

ASMCMD> ls

spfile.335.881250575

ASMCMD>

[oracle@node1 dbs]$ cp initorastrac.ora initorastrac.ora_bk2

[oracle@node1 dbs]$ echo "SPFILE='+DATA/ORASTRAC/PARAMETERFILE/spfile.335.881250575'" > $ORACLE_HOME/dbs/initorastrac.ora

[oracle@node1 dbs]$

[oracle@node1 dbs]$ more initorastrac.ora

SPFILE='+DATA/ORASTRAC/PARAMETERFILE/spfile.335.881250575'

[oracle@node1 dbs]$

恢复控制文件

[oracle@node1 dbs]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jun 1 15:46:55 2015

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

connected to target database (not started)

RMAN> startup nomount force;

Oracle instance started

Total System Global Area 409194496 bytes

Fixed Size 2213856 bytes

Variable Size 272631840 bytes

Database Buffers 130023424 bytes

Redo Buffers 4325376 bytes

RMAN> restore controlfile from '/home/oracle/rman_back/ctl_ORASTRAC_20150601_7_1.bak';

Starting restore at 01-JUN-2015 15:47:14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=29 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

output file name=+DATA/orastrac/controlfile/control01.ctl

Finished restore at 01-JUN-2015 15:47:21

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN>

还原及恢复数据文件

首先得到运行的脚本：

[oracle@node1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 1 15:58:47 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> set line 9999 pagesize 9999

SQL> col FILE_NAME format a60

SQL> select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile

2 union all

3 select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile

4 union all

5 select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile

6 union all

7 select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile

8 ;

FILE_TYPE FILE# FILE_NAME STATUS ENABLED

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

datafile 1 /u01/app/oracle/oradata/orastrac/system01.dbf SYSTEM READ WRITE

datafile 2 /u01/app/oracle/oradata/orastrac/sysaux01.dbf ONLINE READ WRITE

datafile 3 /u01/app/oracle/oradata/orastrac/undotbs01.dbf ONLINE READ WRITE

datafile 4 /u01/app/oracle/oradata/orastrac/users01.dbf ONLINE READ WRITE

datafile 5 /u01/app/oracle/oradata/orastrac/example01.dbf ONLINE READ WRITE

tempfile 1 /u01/app/oracle/oradata/orastrac/temp01.dbf ONLINE READ WRITE

logfile 3 /u01/app/oracle/oradata/orastrac/redo03.log

logfile 2 /u01/app/oracle/oradata/orastrac/redo02.log

logfile 1 /u01/app/oracle/oradata/orastrac/redo01.log

controlfile +DATA/orastrac/controlfile/control01.ctl

10 rows selected.

SQL> set pagesize 200 linesize 200

SQL> select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";'

2 from v$datafile a

3 union all

4 select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";'

5 from v$tempfile a

6 union all

7 SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''' ||

8 a.MEMBER || ''''' ";'

9 FROM v$logfile a;

'SETNEWNAMEFORDATAFILE'||A.FILE#||'TO"'||A.NAME||'";'

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

set newname for datafile 1 to "/u01/app/oracle/oradata/orastrac/system01.dbf";

set newname for datafile 2 to "/u01/app/oracle/oradata/orastrac/sysaux01.dbf";

set newname for datafile 3 to "/u01/app/oracle/oradata/orastrac/undotbs01.dbf";

set newname for datafile 4 to "/u01/app/oracle/oradata/orastrac/users01.dbf";

set newname for datafile 5 to "/u01/app/oracle/oradata/orastrac/example01.dbf";

set newname for tempfile 1 to "/u01/app/oracle/oradata/orastrac/temp01.dbf";

SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orastrac/redo03.log'' to ''/u01/app/oracle/oradata/orastrac/redo03.log'' ";

SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orastrac/redo02.log'' to ''/u01/app/oracle/oradata/orastrac/redo02.log'' ";

SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orastrac/redo01.log'' to ''/u01/app/oracle/oradata/orastrac/redo01.log'' ";

9 rows selected.

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
AiDBA后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！
验证码：
获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签：

Avatar photo

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复