一、准备条件
1、single instance 配置
数据库版本：11.2.0.4 操作系统：CentOS 6.5
2、rac配置(数据库实例未装)
服务器主机名 rac1 rac2
公共IP地址(eth0) 192.168.91.140 192.168.91.142
虚拟IP地址(eth0) 192.168.91.152 192.168.91.153
私有IP地址(eth1) 192.168.214.130 192.168.214.131
集群实例名称 wxqyh
SCAN IP 192.168.91.154
操作系统 CentOS 6.5
存储 ASM
ORACLE 11.2.0.4
[root@rac1 Desktop]# crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.FRA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.LISTENER.lsnr
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.NEW_BURTON_LISTENER.lsnr
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.OCRVOTE.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.asm
ONLINE ONLINE rac1 Started
ONLINE ONLINE rac2 Started
ora.gsd
OFFLINE OFFLINE rac1
OFFLINE OFFLINE rac2
ora.net1.network
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.ons
ONLINE ONLINE rac1
ONLINE ONLINE rac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE rac1
ora.cvu
1 ONLINE ONLINE rac1
ora.oc4j
1 ONLINE ONLINE rac1
ora.rac1.vip
1 ONLINE ONLINE rac1
ora.rac2.vip
1 ONLINE ONLINE rac2
ora.scan1.vip
1 ONLINE ONLINE rac1
二、源数据库备份和参数文件复制到目标端
1、对源端数据库做全备
[oracle@wxqyh ~]$ mkdir -p /u01/app/oracle/backup/
[oracle@wxqyh archivelog]$ rman target/
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Apr 21 13:09:57 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: WXQYH (DBID=1110855286)
RMAN> run{
2> crosscheck archivelog all;
3> delete noprompt expired archivelog all;
4> crosscheck backup;
5> delete noprompt expired backup;
6> report obsolete;
7> delete noprompt obsolete;
8> delete noprompt archivelog all completed before 'sysdate-7';
9> allocate channel c1 type disk;
10> allocate channel c2 type disk;
11> sql 'alter system archive log current';
12> backup database format '/u01/app/oracle/backup/full_%d_%T_%s_%U' include current controlfile
13> plus archivelog format '/u01/app/oracle/backup/arc_%d_%T_%s_%U' delete all input ;
14> release channel c1;
15> release channel c2;
16> }
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1145 device type=DISK
validation succeeded for archived log
archived log file name=/u01/app/oracle/archivelog/arch_1_68_929977336.arc RECID=64 STAMP=941688053
Crosschecked 1 objects
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1145 device type=DISK
specification does not match any archived log in the repository
using channel ORA_DISK_1
specification does not match any backup in the repository
using channel ORA_DISK_1
specification does not match any backup in the repository
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
no obsolete backups found
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
no obsolete backups found
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1145 device type=DISK
released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=1145 device type=DISK
allocated channel: c2
channel c2: SID=1717 device type=DISK
sql statement: alter system archive log current
Starting backup at 2017-04-21 13:10:07
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=68 RECID=64 STAMP=941688053
channel c1: starting piece 1 at 2017-04-21 13:10:08
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=69 RECID=65 STAMP=941893807
input archived log thread=1 sequence=70 RECID=66 STAMP=941893807
channel c2: starting piece 1 at 2017-04-21 13:10:08
channel c2: finished piece 1 at 2017-04-21 13:10:09
piece handle=/u01/app/oracle/backup/arc_WXQYH_20170421_17_0hs28a5g_1_1 tag=TAG20170421T131007 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
channel c2: deleting archived log(s)
archived log file name=/u01/app/oracle/archivelog/arch_1_69_929977336.arc RECID=65 STAMP=941893807
archived log file name=/u01/app/oracle/archivelog/arch_1_70_929977336.arc RECID=66 STAMP=941893807
channel c1: finished piece 1 at 2017-04-21 13:10:11
piece handle=/u01/app/oracle/backup/arc_WXQYH_20170421_16_0gs28a5g_1_1 tag=TAG20170421T131007 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
channel c1: deleting archived log(s)
archived log file name=/u01/app/oracle/archivelog/arch_1_68_929977336.arc RECID=64 STAMP=941688053
Finished backup at 2017-04-21 13:10:11
Starting backup at 2017-04-21 13:10:11
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/wxqyh/wxqyh001.dat
input datafile file number=00001 name=/u01/app/oracle/oradata/wxqyh/system01.dbf
channel c1: starting piece 1 at 2017-04-21 13:10:12
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/wxqyh/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/wxqyh/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/wxqyh/users01.dbf
channel c2: starting piece 1 at 2017-04-21 13:10:12
channel c1: finished piece 1 at 2017-04-21 13:10:19
piece handle=/u01/app/oracle/backup/full_WXQYH_20170421_18_0is28a5j_1_1 tag=TAG20170421T131011 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:07
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 2017-04-21 13:10:20
channel c1: finished piece 1 at 2017-04-21 13:10:21
piece handle=/u01/app/oracle/backup/full_WXQYH_20170421_20_0ks28a5r_1_1 tag=TAG20170421T131011 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 2017-04-21 13:10:21
channel c1: finished piece 1 at 2017-04-21 13:10:22
piece handle=/u01/app/oracle/backup/full_WXQYH_20170421_21_0ls28a5t_1_1 tag=TAG20170421T131011 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c2: finished piece 1 at 2017-04-21 13:10:28
piece handle=/u01/app/oracle/backup/full_WXQYH_20170421_19_0js28a5k_1_1 tag=TAG20170421T131011 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:16
Finished backup at 2017-04-21 13:10:28
Starting backup at 2017-04-21 13:10:28
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=71 RECID=67 STAMP=941893828
channel c1: starting piece 1 at 2017-04-21 13:10:28
channel c1: finished piece 1 at 2017-04-21 13:10:29
piece handle=/u01/app/oracle/backup/arc_WXQYH_20170421_22_0ms28a64_1_1 tag=TAG20170421T131028 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: deleting archived log(s)
archived log file name=/u01/app/oracle/archivelog/arch_1_71_929977336.arc RECID=67 STAMP=941893828
Finished backup at 2017-04-21 13:10:29
released channel: c1
released channel: c2
2、创建参数文件pfile
[oracle@wxqyh ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 21 13:12:16 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create pfile='/tmp/pfile.ora' from spfile;
File created.
[oracle@wxqyh ~]$ cat /tmp/pfile.ora
wxqyh.__db_cache_size=2751463424
wxqyh.__java_pool_size=16777216
wxqyh.__large_pool_size=33554432
wxqyh.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
wxqyh.__pga_aggregate_target=1677721600
wxqyh.__sga_target=5033164800
wxqyh.__shared_io_pool_size=0
wxqyh.__shared_pool_size=2197815296
wxqyh.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/wxqyh/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/wxqyh/control01.ctl','/u01/app/oracle/fast_recovery_area/wxqyh/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='wxqyh'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/u01/app/oracle'
*.disk_asynch_io=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=wxqyhXDB)'
*.filesystemio_options='SETALL'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog'
*.log_archive_format='arch_%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=1672478720
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=5017436160
*.undo_tablespace='UNDOTBS1'
3、把备份集传到目标数据库rac1上
[oracle@wxqyh ~]$ scp -r /u01/app/oracle/backup/*oracle@192.168.91.140:/u01/app/oracle/backup/
目标端rac1上确认
[oracle@rac1 ~]$ ll /u01/app/oracle/backup
total 2386364
-rw-r--r-- 1 oracle oinstall 222914560 Apr 21 13:10 arc_WXQYH_20170421_16_0gs28a5g_1_1
-rw-r--r-- 1 oracle oinstall 202394624 Apr 21 13:10 arc_WXQYH_20170421_17_0hs28a5g_1_1
-rw-r--r-- 1 oracle oinstall 12288 Apr 21 13:10 arc_WXQYH_20170421_22_0ms28a64_1_1
-rw-r--r-- 1 oracle oinstall 671866880 Apr 21 13:10 full_WXQYH_20170421_18_0is28a5j_1_1
-rw-r--r-- 1 oracle oinstall 1336508416 Apr 21 13:10 full_WXQYH_20170421_19_0js28a5k_1_1
-rw-r--r-- 1 oracle oinstall 9830400 Apr 21 13:10 full_WXQYH_20170421_20_0ks28a5r_1_1
-rw-r--r-- 1 oracle oinstall 98304 Apr 21 13:10 full_WXQYH_20170421_21_0ls28a5t_1_1
三、目标端数据恢复
1、配置oracle用户的环境变量(所有的节点执行,SID稍有差异)
[oracle@rac1 ~]$ vi ~/.bash_profile
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=wxqyh1
export PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
umask 022
[oracle@rac1 ~]$ source ~/.bash_profile
2、在共享磁盘上创建必要的目录
2.1 查看磁盘挂载情况
[root@rac1 ~]# su - grid
[grid@rac1 ~]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 13 16:32:13 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
SQL> col name for a10
SQL> col state for a10
SQL> select group_number , name , state, type, total_mb, free_mb from v$asm_diskgroup ;
GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB
------------ ---------- ---------- ------------------ ---------- ----------
1 DATA MOUNTED EXTERN 9992 9896
2 FRA MOUNTED EXTERN 4996 4902
3 OCRVOTE MOUNTED NORMAL 2997 2071
[grid@rac1 ~]$ asmcmd
ASMCMD> ls
DATA/
FRA/
OCRVOTE/
ASMCMD> cd data
ASMCMD> mkdir WXQYH
ASMCMD> cd wxqyh
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir PARAMETERFILE
ASMCMD> mkdir DATAFILE
ASMCMD> mkdir TEMPFILE
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir archivelog
ASMCMD> cd ../../fra
ASMCMD> mkdir WXQYH
ASMCMD> cd wxqyh
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir CONTROLFILE
3、在rac1本地磁盘上创建必要的目录(保险起劲所有节点执行)
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/admin/wxqyh/adump
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/archivelog
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/oradata/wxqyh
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/backup
4、在rac1上编辑pfile文件(根据实际情况调整内存大小)
[oracle@rac1 backup]$ vi /tmp/pfile.ora
*.audit_file_dest='/u01/app/oracle/admin/wxqyh/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA/wxqyh/controlfile/current.289.939812385','+FRA/wxqyh/controlfile/current.286.939812387'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='wxqyh'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4421074432
*.diagnostic_dest='/u01/app/oracle'
*.disk_asynch_io=TRUE
*.filesystemio_options='SETALL'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=wxqyhXDB)'
*.enable_goldengate_replication=TRUE
*.log_archive_dest_1='LOCATION=+data/wxqyh/archivelog'
*.log_archive_format='arch_%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=302478720
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.cluster_database=false
*.sga_target=917436160
*.undo_management='AUTO'
wxqyh1.instance_name='wxqyh1'
wxqyh2.instance_name='wxqyh2'
wxqyh1.instance_number=1
wxqyh2.instance_number=2
wxqyh1.thread=1
wxqyh2.thread=2
wxqyh1.undo_tablespace='UNDOTBS1'
wxqyh2.undo_tablespace='UNDOTBS2'
注：等数据库恢复完毕后，再cluster_database设置为true。还有如果用rman duplicate必须设置如下两个参数：
*.db_file_name_convert='/u01/app/oracle/oradata/wxqyh','+DATA/wxqyh/datafile'
*.log_file_name_convert='/u01/app/oracle/oradata/wxqyh','+DATA/wxqyh/onlinelog','/u01/app/oracle/oradata/wxqyh','+FRA/wxqyh/onlinelog'
5、恢复数据库
5.1 用pfile文件创建spfile
[oracle@rac1 ~]$ su - oracle
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 13 16:36:20 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/tmp/pfile.ora';
ORACLE instance started.
Total System Global Area 914440192 bytes
Fixed Size 2258600 bytes
Variable Size 297797976 bytes
Database Buffers 608174080 bytes
Redo Buffers 6209536 bytes
SQL> create spfile='+DATA/wxqyh/spfilewxqyh.ora' from pfile='/tmp/pfile.ora';
File created.
SQL> shutdown abort
ORACLE instance shut down.
5.2 将所有的节点上pfile文件，指向共享文件上的spfile文件
rac1节点:
[oracle@rac1 ~]$ echo "SPFILE='+DATA/wxqyh/spfilewxqyh.ora' " > /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initwxqyh1.ora
rac2节点:
[oracle@rac2 ~]$ echo "SPFILE='+DATA/wxqyh/spfilewxqyh.ora' " > /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initwxqyh2.ora
5.3 创建口令文件
rac1节点:
[oracle@rac1 ~]$ orapwd file=?/dbs/orapwwxqyh1 password=oracle4U
[oracle@rac1 ~]$ ll /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwwxqyh1
-rw-r----- 1 oracle oinstall 1536 Apr 21 13:52 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwwxqyh1
rac2节点:
[oracle@rac2 ~]$ orapwd file=?/dbs/orapwwxqyh2 password=oracle4U
[oracle@rac2 dbs]$ ll /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwwxqyh2
-rw-r----- 1 oracle oinstall 1536 Apr 21 13:52 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwwxqyh2
5.4 还原控制文件
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 13 17:28:27 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 914440192 bytes
Fixed Size 2258600 bytes
Variable Size 297797976 bytes
Database Buffers 608174080 bytes
Redo Buffers 6209536 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@rac1 dbs]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Apr 13 17:29:35 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: WXQYH (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/backup/full_WXQYH_20170421_20_0ks28a5r_1_1';
Starting restore at 2017-04-21 13:55:09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/wxqyh/controlfile/current.263.941896511
output file name=+FRA/wxqyh/controlfile/current.256.941896511
Finished restore at 2017-04-21 13:55:13
注：具体哪个文件还原控制文件，可参数源端运行 list backup of controlfile 可知。
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> catalog start with '/u01/app/oracle/backup';
Starting implicit crosscheck backup at 2017-04-21 13:56:01
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 2017-04-21 13:56:03
Starting implicit crosscheck copy at 2017-04-21 13:56:03
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2017-04-21 13:56:04
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /u01/app/oracle/backup
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/backup/full_WXQYH_20170421_21_0ls28a5t_1_1
File Name: /u01/app/oracle/backup/full_WXQYH_20170421_19_0js28a5k_1_1
File Name: /u01/app/oracle/backup/full_WXQYH_20170421_20_0ks28a5r_1_1
File Name: /u01/app/oracle/backup/arc_WXQYH_20170421_22_0ms28a64_1_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/backup/full_WXQYH_20170421_21_0ls28a5t_1_1
File Name: /u01/app/oracle/backup/full_WXQYH_20170421_19_0js28a5k_1_1
File Name: /u01/app/oracle/backup/full_WXQYH_20170421_20_0ks28a5r_1_1
File Name: /u01/app/oracle/backup/arc_WXQYH_20170421_22_0ms28a64_1_1
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
16 B A A DISK 2017-04-21 13:10:08 1 1 NO TAG20170421T131007
17 B A A DISK 2017-04-21 13:10:10 1 1 NO TAG20170421T131007
18 B F A DISK 2017-04-21 13:10:18 1 1 NO TAG20170421T131011
19 B F A DISK 2017-04-21 13:10:21 1 1 NO TAG20170421T131011
20 B F A DISK 2017-04-21 13:10:12 1 1 NO TAG20170421T131011
21 B F A DISK 2017-04-21 13:10:19 1 1 NO TAG20170421T131011
22 B A A DISK 2017-04-21 13:10:28 1 1 NO TAG20170421T131028
6、恢复数据库
6.1 查看源端数据文件
sqlplus / as sysdba
SQL> set line 80
SQL> set pagesize 9999
SQL> col file_name for a60
SQL> select 'set newname for datafile '||file_id||' to '''||'+DATA'||''';' cmd from dba_data_files order by file_id;
CMD
-----------------------------------------------------------------------------
set newname for datafile 1 to '+DATA';
set newname for datafile 2 to '+DATA';
set newname for datafile 3 to '+DATA';
set newname for datafile 4 to '+DATA';
set newname for datafile 5 to '+DATA';
SQL> select 'set newname for tempfile '||file_id||' to '''||'+DATA'||''';' cmd from dba_temp_files;
CMD
-----------------------------------------------------------------------------
set newname for tempfile 1 to '+DATA';
6.2 目标端rac1执行：
[oracle@rac1 dbs]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Apr 13 17:45:29 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: WXQYH (DBID=1110855286, not open)
RMAN> run {
set newname for datafile 1 to '+DATA';
set newname for datafile 2 to '+DATA';
set newname for datafile 3 to '+DATA';
set newname for datafile 4 to '+DATA';
set newname for datafile 5 to '+DATA';
set newname for tempfile 1 to '+DATA';
restore database;
switch datafile all;
switch tempfile all;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11>
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 2017-04-21 14:23:43
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA
channel ORA_DISK_1: restoring datafile 00005 to +DATA
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/full_WXQYH_20170421_18_0is28a5j_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/full_WXQYH_20170421_18_0is28a5j_1_1 tag=TAG20170421T131011
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to +DATA
channel ORA_DISK_1: restoring datafile 00003 to +DATA
channel ORA_DISK_1: restoring datafile 00004 to +DATA
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/full_WXQYH_20170421_19_0js28a5k_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/full_WXQYH_20170421_19_0js28a5k_1_1 tag=TAG20170421T131011
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:56
Finished restore at 2017-04-21 14:27:45
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=941898467 file name=+DATA/wxqyh/datafile/system.280.941898225
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=941898467 file name=+DATA/wxqyh/datafile/sysaux.265.941898351
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=941898467 file name=+DATA/wxqyh/datafile/undotbs1.276.941898351
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=941898467 file name=+DATA/wxqyh/datafile/users.261.941898351
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=941898468 file name=+DATA/wxqyh/datafile/wxqyh.275.941898225
renamed tempfile 1 to +DATA in control file
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16 193.02M DISK 00:00:00 2017-04-21 13:10:08
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20170421T131007
Piece Name: /u01/app/oracle/backup/arc_WXQYH_20170421_17_0hs28a5g_1_1
List of Archived Logs in backup set 16
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 69 6759027 2017-04-19 04:00:52 6860693 2017-04-21 13:10:05
1 70 6860693 2017-04-21 13:10:05 6860702 2017-04-21 13:10:07
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
17 212.59M DISK 00:00:02 2017-04-21 13:10:10
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20170421T131007
Piece Name: /u01/app/oracle/backup/arc_WXQYH_20170421_16_0gs28a5g_1_1
List of Archived Logs in backup set 17
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 68 6658903 2017-04-16 20:10:20 6759027 2017-04-19 04:00:52
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
22 11.50K DISK 00:00:00 2017-04-21 13:10:28
BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20170421T131028
Piece Name: /u01/app/oracle/backup/arc_WXQYH_20170421_22_0ms28a64_1_1
List of Archived Logs in backup set 22
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 71 6860702 2017-04-21 13:10:07 6860727 2017-04-21 13:10:28
注：根据备份集可以先恢复到SCN为 6860727 ，如果直接 recover database 会因丢失部分日志报错，但可以忽略。
RMAN> recover database until scn 6860727;
Starting recover at 2017-04-21 14:37:29
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=71
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/arc_WXQYH_20170421_22_0ms28a64_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/arc_WXQYH_20170421_22_0ms28a64_1_1 tag=TAG20170421T131028
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+DATA/wxqyh/archivelog/arch_1_71_929977336.arc thread=1 sequence=71
media recovery complete, elapsed time: 00:00:00
Finished recover at 2017-04-21 14:37:34
7、源端备份后的变化数据
7.1 准备源数据全备后变化的数据
[oracle@wxqyh ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 14 10:17:15 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system switch logfile;
System altered.
SQL> create table t4 (id number,name varchar2(20));
Table created.
SQL> insert into t4 values (1,'burton');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
8、恢复新增数据
8.1 查看复制到目标端在线重做日志和新归档日志
rac1:
[oracle@rac1 ~]$ ll /u01/app/oracle/archivelog/
total 1636
-rw-r--r-- 1 oracle oinstall 1654272 Apr 21 14:32 arch_1_72_929977336.arc
-rw-r--r-- 1 oracle oinstall 16896 Apr 21 14:33 arch_1_73_929977336.arc
[oracle@rac1 ~]$ ll /u01/app/oracle/oradata/wxqyh/
total 786448
-rw-r--r-- 1 oracle oinstall 268435968 Apr 21 14:32 redo01.log
-rw-r--r-- 1 oracle oinstall 268435968 Apr 21 14:33 redo02.log
-rw-r--r-- 1 oracle oinstall 268435968 Apr 21 14:33 redo03.log
8.2 把归档日志注册到rman
RMAN> catalog archivelog '/u01/app/oracle/archivelog/arch_1_72_929977336.arc','/u01/app/oracle/archivelog/arch_1_73_929977336.arc';
cataloged archived log
archived log file name=/u01/app/oracle/archivelog/arch_1_72_929977336.arc RECID=70 STAMP=941899951
cataloged archived log
archived log file name=/u01/app/oracle/archivelog/arch_1_73_929977336.arc RECID=71 STAMP=941899951
RMAN> recover database;
Starting recover at 2017-04-21 14:55:25
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 72 is already on disk as file /u01/app/oracle/oradata/wxqyh/redo01.log
archived log for thread 1 with sequence 73 is already on disk as file /u01/app/oracle/oradata/wxqyh/redo02.log
archived log for thread 1 with sequence 74 is already on disk as file /u01/app/oracle/oradata/wxqyh/redo03.log
archived log file name=/u01/app/oracle/oradata/wxqyh/redo01.log thread=1 sequence=72
archived log file name=/u01/app/oracle/oradata/wxqyh/redo02.log thread=1 sequence=73
archived log file name=/u01/app/oracle/oradata/wxqyh/redo03.log thread=1 sequence=74
media recovery complete, elapsed time: 00:00:09
Finished recover at 2017-04-21 14:55:37
9、修改日志文件路径
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> set line 150
SQL> col member for a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/wxqyh/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/wxqyh/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/wxqyh/redo01.log NO
SQL> alter database rename file '/u01/app/oracle/oradata/wxqyh/redo01.log' to '+DATA';
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/wxqyh/redo02.log' to '+DATA';
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/wxqyh/redo03.log' to '+DATA';
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE +DATA NO
2 ONLINE +DATA NO
1 ONLINE +DATA NO
SQL> alter database open resetlogs;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE +DATA/wxqyh/onlinelog/group_3.256.941900533 NO
2 ONLINE +DATA/wxqyh/onlinelog/group_2.278.941900517 NO
1 ONLINE +DATA/wxqyh/onlinelog/group_1.273.941900501 NO
1 ONLINE +FRA/wxqyh/onlinelog/group_1.259.941900513 YES
2 ONLINE +FRA/wxqyh/onlinelog/group_2.258.941900527 YES
3 ONLINE +FRA/wxqyh/onlinelog/group_3.257.941900545 YES
6 rows selected.
10、打开数据库
SQL> alter database open resetlogs;
Database altered.
四、配置数据库参数
1、查看数据情况
修改参数：
SQL> col value for a10
SQL> select * from v$option where parameter='Real Application Clusters';
PARAMETER VALUE
----------------------------------------------- ----------
Real Application Clusters TRUE
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects string
SQL> show parameter thread
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_threads_per_cpu integer 2
thread integer 1
SQL> show parameter instance_number
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_number integer 1
SQL> alter system set cluster_database=true scope=spfile sid='*';
System altered.
SQL> alter system set cluster_database_instances=2 scope=spfile sid='*';
System altered.
SQL> alter system set instance_number=1 scope=spfile sid='wxqyh1';
System altered.
SQL> alter system set instance_number=2 scope=spfile sid='wxqyh2';
System altered.
SQL> alter system set thread=1 scope=spfile sid='wxqyh1';
System altered.
SQL> alter system set thread=2 scope=spfile sid='wxqyh2';
System altered.
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> create undo tablespace UNDOTBS2 datafile '+DATA/wxqyh/datafile/undotbs02.dbf' size 100M;
Tablespace created.
SQL> alter system set undo_tablespace='UNDOTBS2' scope=spfile sid='wxqyh2';
System altered.
SQL> alter database add logfile thread 2 group 4 ('+DATA','+FRA') size 50M;
Database altered.
SQL> alter database add logfile thread 2 group 5 ('+DATA','+FRA') size 50M;
Database altered.
SQL> alter database add logfile thread 2 group 6 ('+DATA','+FRA') size 50M;
Database altered.
注：根据实际情况分配日志文件大小，可以增加日志组文件
alter database add logfile member '+FRA' to group 4;
SQL> alter database enable thread 2;
Database altered
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2、将其他的信息注册到CRS里
[oracle@rac1 ~]$ srvctl add database -d wxqyh -o $ORACLE_HOME -p +DATA/wxqyh/spfilewxqyh.ora
[oracle@rac1 ~]$ srvctl add instance -d wxqyh -i wxqyh1 -n rac1
[oracle@rac1 ~]$ srvctl add instance -d wxqyh -i wxqyh2 -n rac2
3、启动rac1和rac2实例
[oracle@rac1 ~]$ srvctl start database -d wxqyh -o open
4、 确认实例情况
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 21 15:13:57 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> col instance_name for a20
SQL> col host_name for a10
SQL> select instance_number,instance_name ,host_name from gv$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
--------------- -------------------- ----------
1 wxqyh1 rac1
2 wxqyh2 rac2
5、执行catclust.sql脚本创建相关视图
SQL> @$ORACLE_HOME/rdbms/admin/catclust.sql
Package created.
Package body created.
PL/SQL procedure successfully completed.
View created.
Synonym created.
Grant succeeded.
View created.
Grant succeeded.
Synonym created.
View created.
Grant succeeded.
Synonym created.
View created.
Grant succeeded.
Synonym created.
View created.
Grant succeeded.
Synonym created.
View created.
Grant succeeded.
Synonym created.
View created.
Grant succeeded.
Synonym created.
View created.
Grant succeeded.
Synonym created.
View created.
Grant succeeded.
View created.
Grant succeeded.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
View created.
Grant succeeded.
Synonym created.
View created.
Grant succeeded.
Synonym created.
PL/SQL procedure successfully completed.
6、重建 temp表空间
6.1 因为在 recover 的时候不会对temp 表空间进行recover。所以recover 后，我们要手工重建temp表空间。
[grid@rac1 ~]$ asmcmd lsof |grep temp
wxqyh wxqyh1 +data/wxqyh/tempfile/temp.266.941900557
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> alter tablespace temp add tempfile '+DATA' size 100M;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/wxqyh/tempfile/temp.266.941900557
+DATA/wxqyh/tempfile/temp.267.941901771
SQL> alter database tempfile '+DATA/wxqyh/tempfile/temp.266.941900557' offline ;
Database altered.
SQL> alter database tempfile '+DATA/wxqyh/tempfile/temp.266.941900557' drop including datafiles;
alter database tempfile '+DATA/wxqyh/tempfile/temp.266.941900557' drop including datafiles
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time
6.2 重启数据库再删除原temp表空间
[oracle@rac1 oracle]$ srvctl stop database -d wxqyh -o immediate
[oracle@rac1 oracle]$ srvctl start database -d wxqyh -o open
[oracle@rac1 oracle]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 14 14:11:13 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> alter database tempfile '+DATA/wxqyh/tempfile/temp.266.941900557' drop including datafiles;
Database altered.
7、配置集群监听
7.1 配置tnsnames.ora (所有节点执行)
[oracle@rac1 ~]$ su - oracle
[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@rac1 admin]$ vi tnsnames.ora
WXQYH =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.154)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = wxqyh)
)
)
7.2 启动监听
[oracle@rac1 admin]$ srvctl stop listener
[oracle@rac1 admin]$ srvctl start listener
[oracle@rac1 admin]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): rac2,rac1
7.3 用PC机测试
编辑 G:\app\oracle\product\11.2.0\client_1\network\admin\tnsnames.ora
WXQYH =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.152)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.153)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = wxqyh)
)
)
运行 cmd
C:\Users\Burton>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on 星期五 4月 21 16:35:32 2017
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data Mining and Real Application Testing options
SQL>
8、查看在线日志情况
SQL> set pagesize 9999
SQL> col member for a50
SQL> select group#,member from v$logfile order by group#;
GROUP# MEMBER
---------- --------------------------------------------------
3 +DATA/wxqyh/onlinelog/group_3.256.941900533
2 +DATA/wxqyh/onlinelog/group_2.278.941900517
1 +DATA/wxqyh/onlinelog/group_1.273.941900501
1 +FRA/wxqyh/onlinelog/group_1.259.941900513
2 +FRA/wxqyh/onlinelog/group_2.258.941900527
3 +FRA/wxqyh/onlinelog/group_3.257.941900545
4 +DATA/wxqyh/onlinelog/group_4.281.941900935
4 +FRA/wxqyh/onlinelog/group_4.260.941900937
5 +DATA/wxqyh/onlinelog/group_5.262.941900943
5 +FRA/wxqyh/onlinelog/group_5.261.941900945
6 +DATA/wxqyh/onlinelog/group_6.271.941900951
6 +FRA/wxqyh/onlinelog/group_6.262.941900953
12 rows selected.
五、验证数据库及集群情况
1、验证数据完整性
[oracle@rac1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 21 16:03:44 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select * from t4;
ID NAME
---------- --------------------
1 burton
2、查看数据库的状态
[oracle@rac1 admin]$ srvctl status database -d wxqyh
Instance wxqyh1 is running on node rac1
Instance wxqyh2 is running on node rac2
3、查看数据的配置情况
[root@rac2 Desktop]# srvctl config database -d wxqyh
Database unique name: wxqyh
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/wxqyh/spfilewxqyh.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: wxqyh
Database instances: wxqyh1,wxqyh2
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Database is administrator managed
4、查看集群配置
[root@rac2 Desktop]# crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.FRA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.LISTENER.lsnr
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.OCRVOTE.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.asm
ONLINE ONLINE rac1 Started
ONLINE ONLINE rac2 Started
ora.gsd
OFFLINE OFFLINE rac1
OFFLINE OFFLINE rac2
ora.net1.network
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.ons
ONLINE ONLINE rac1
ONLINE ONLINE rac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE rac1
ora.cvu
1 ONLINE ONLINE rac1
ora.oc4j
1 ONLINE ONLINE rac1
ora.rac1.vip
1 ONLINE ONLINE rac1
ora.rac2.vip
1 ONLINE ONLINE rac2
ora.scan1.vip
1 ONLINE ONLINE rac1
ora.wxqyh.db
1 ONLINE ONLINE rac1 Open
2 ONLINE ONLINE rac2 Open
[root@rac2 Desktop]# olsnodes -s
rac1 Active
rac2 Active
800
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
