一、环境配置情况
1、第一套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
ORACLE RAC SID burton1 burton2
集群实例名称 burton
SCAN IP 192.168.91.154
操作系统 CentOS 6.5
存储 ASM
ORACLE 11.2.0.4
2、第二套rac配置(数据库实例未装)
服务器主机名 dbrac1 dbrac2
公共IP地址(eth0) 192.168.10.165 192.168.10.170
虚拟IP地址(eth0) 192.168.10.197 192.168.10.198
私有IP地址(eth1) 10.0.0.1 10.0.0.2
集群实例名称 burton
SCAN IP 192.168.10.199
操作系统 CentOS 6.5
存储 ASM
ORACLE 11.2.0.4
二、数据库迁移
1、在源端备份传到目标端数据库
1.1 备份数据库(节点1上操作)
[oracle@rac1 ~]$ su - oracle
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/backup
[oracle@rac1 ~]$ rman target
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 18 15:55:03 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: BURTON (DBID=3896087231)
RMAN> run{
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
report obsolete;
delete noprompt obsolete;
delete noprompt archivelog all completed before 'sysdate-7';
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter system archive log current';
backup database format '/u01/app/oracle/backup/full_%d_%T_%s_%U' include current controlfile
plus archivelog format '/u01/app/oracle/backup/arc_%d_%T_%s_%U' delete all input ;
release channel c1;
release channel c2;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16>
using target database control file instead of recovery catalog
configuration for DISK channel 2 is ignored
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 instance=burton1 device type=DISK
validation succeeded for archived log
archived log file name=+DATA/burton/archivelog/arch_1_21_941298372.arc RECID=31 STAMP=941535734
validation succeeded for archived log
archived log file name=+DATA/burton/archivelog/arch_1_22_941298372.arc RECID=33 STAMP=941541785
validation succeeded for archived log
archived log file name=+DATA/burton/archivelog/arch_1_23_941298372.arc RECID=34 STAMP=941541809
validation succeeded for archived log
archived log file name=+DATA/burton/archivelog/arch_2_16_941298372.arc RECID=30 STAMP=941535734
validation succeeded for archived log
archived log file name=+DATA/burton/archivelog/arch_2_17_941298372.arc RECID=32 STAMP=941535737
validation succeeded for archived log
archived log file name=+DATA/burton/archivelog/arch_2_18_941298372.arc RECID=35 STAMP=941541846
Crosschecked 6 objects
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 instance=burton1 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=41 instance=burton1 device type=DISK
released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=41 instance=burton1 device type=DISK
allocated channel: c2
channel c2: SID=62 instance=burton1 device type=DISK
sql statement: alter system archive log current
Starting backup at 2017-04-18 15:55:53
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=21 RECID=31 STAMP=941535734
input archived log thread=2 sequence=16 RECID=30 STAMP=941535734
input archived log thread=1 sequence=22 RECID=33 STAMP=941541785
input archived log thread=2 sequence=17 RECID=32 STAMP=941535737
input archived log thread=2 sequence=18 RECID=35 STAMP=941541846
channel c1: starting piece 1 at 2017-04-18 15:55:59
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=23 RECID=34 STAMP=941541809
input archived log thread=1 sequence=24 RECID=37 STAMP=941644552
input archived log thread=2 sequence=19 RECID=36 STAMP=941644552
channel c2: starting piece 1 at 2017-04-18 15:56:00
channel c1: finished piece 1 at 2017-04-18 15:56:03
piece handle=/u01/app/oracle/backup/arc_BURTON_20170418_36_14s20mof_1_1 tag=TAG20170418T155558 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:04
channel c1: deleting archived log(s)
archived log file name=+DATA/burton/archivelog/arch_1_21_941298372.arc RECID=31 STAMP=941535734
archived log file name=+DATA/burton/archivelog/arch_2_16_941298372.arc RECID=30 STAMP=941535734
archived log file name=+DATA/burton/archivelog/arch_1_22_941298372.arc RECID=33 STAMP=941541785
archived log file name=+DATA/burton/archivelog/arch_2_17_941298372.arc RECID=32 STAMP=941535737
archived log file name=+DATA/burton/archivelog/arch_2_18_941298372.arc RECID=35 STAMP=941541846
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=25 RECID=39 STAMP=941644558
input archived log thread=2 sequence=20 RECID=38 STAMP=941644556
channel c1: starting piece 1 at 2017-04-18 15:56:04
channel c2: finished piece 1 at 2017-04-18 15:56:04
piece handle=/u01/app/oracle/backup/arc_BURTON_20170418_37_15s20mof_1_1 tag=TAG20170418T155558 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:04
channel c2: deleting archived log(s)
archived log file name=+DATA/burton/archivelog/arch_1_23_941298372.arc RECID=34 STAMP=941541809
archived log file name=+DATA/burton/archivelog/arch_1_24_941298372.arc RECID=37 STAMP=941644552
archived log file name=+DATA/burton/archivelog/arch_2_19_941298372.arc RECID=36 STAMP=941644552
channel c1: finished piece 1 at 2017-04-18 15:56:05
piece handle=/u01/app/oracle/backup/arc_BURTON_20170418_38_16s20mok_1_1 tag=TAG20170418T155558 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: deleting archived log(s)
archived log file name=+DATA/burton/archivelog/arch_1_25_941298372.arc RECID=39 STAMP=941644558
archived log file name=+DATA/burton/archivelog/arch_2_20_941298372.arc RECID=38 STAMP=941644556
Finished backup at 2017-04-18 15:56:05
Starting backup at 2017-04-18 15:56:05
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/burton/datafile/system.264.941298255
input datafile file number=00004 name=+DATA/burton/datafile/users.274.941298255
input datafile file number=00005 name=+DATA/burton/datafile/undotbs2.279.941298541
channel c1: starting piece 1 at 2017-04-18 15:56:06
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/burton/datafile/sysaux.270.941298255
input datafile file number=00006 name=+DATA/burton/datafile/test.258.941302355
input datafile file number=00003 name=+DATA/burton/datafile/undotbs1.268.941298255
channel c2: starting piece 1 at 2017-04-18 15:56:06
channel c1: finished piece 1 at 2017-04-18 15:57:41
piece handle=/u01/app/oracle/backup/full_BURTON_20170418_39_17s20mom_1_1 tag=TAG20170418T155606 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:35
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
channel c2: finished piece 1 at 2017-04-18 15:57:42
piece handle=/u01/app/oracle/backup/full_BURTON_20170418_40_18s20mom_1_1 tag=TAG20170418T155606 comment=NONE
channel c2: backup set complete, elapsed time: 00:01:36
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 2017-04-18 15:57:42
including current control file in backup set
channel c1: starting piece 1 at 2017-04-18 15:57:45
channel c2: finished piece 1 at 2017-04-18 15:57:45
piece handle=/u01/app/oracle/backup/full_BURTON_20170418_42_1as20mrm_1_1 tag=TAG20170418T155606 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:03
channel c1: finished piece 1 at 2017-04-18 15:57:46
piece handle=/u01/app/oracle/backup/full_BURTON_20170418_41_19s20mrm_1_1 tag=TAG20170418T155606 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-04-18 15:57:46
Starting backup at 2017-04-18 15:57:47
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=2 sequence=21 RECID=41 STAMP=941644670
channel c1: starting piece 1 at 2017-04-18 15:57:53
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=26 RECID=40 STAMP=941644667
channel c2: starting piece 1 at 2017-04-18 15:57:54
channel c1: finished piece 1 at 2017-04-18 15:57:55
piece handle=/u01/app/oracle/backup/arc_BURTON_20170418_43_1bs20ms1_1_1 tag=TAG20170418T155753 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
channel c1: deleting archived log(s)
archived log file name=+DATA/burton/archivelog/arch_2_21_941298372.arc RECID=41 STAMP=941644670
channel c2: finished piece 1 at 2017-04-18 15:57:55
piece handle=/u01/app/oracle/backup/arc_BURTON_20170418_44_1cs20ms1_1_1 tag=TAG20170418T155753 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
channel c2: deleting archived log(s)
archived log file name=+DATA/burton/archivelog/arch_1_26_941298372.arc RECID=40 STAMP=941644667
Finished backup at 2017-04-18 15:57:55
released channel: c1
released channel: c2
1.2 把备份文件复制到目标端(确保对端目录存在)
[oracle@rac1 bin]$ scp -r /u01/app/oracle/backup/*oracle@192.168.10.165:/u01/app/oracle/backup/
oracle@192.168.10.165'spassword:
arc_BURTON_20170418_36_14s20mof_1_1 100% 11MB 10.7MB/s 00:01
arc_BURTON_20170418_37_15s20mof_1_1 100% 13MB 13.3MB/s 00:01
arc_BURTON_20170418_38_16s20mok_1_1 100% 4096 4.0KB/s 00:00
arc_BURTON_20170418_43_1bs20ms1_1_1 100% 2560 2.5KB/s 00:00
arc_BURTON_20170418_44_1cs20ms1_1_1 100% 3072 3.0KB/s 00:00
full_BURTON_20170418_39_17s20mom_1_1 100% 640MB 11.0MB/s 00:58
full_BURTON_20170418_40_18s20mom_1_1 100% 420MB 11.3MB/s 00:37
full_BURTON_20170418_41_19s20mrm_1_1 100% 18MB 18.0MB/s 00:01
full_BURTON_20170418_42_1as20mrm_1_1 100% 96KB 96.0KB/s 00:00
2、用spfile创建pfile文件
2.1 创建pfile文件
[oracle@rac1 bin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 18 17:02:37 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> create pfile='/tmp/pfile.ora' from spfile='+DATA/burton/spfileburton.ora';
File created.
2.2 查看pfile文件
[oracle@rac1 bin]$ cat /tmp/pfile.ora
burton2.__db_cache_size=402653184
burton1.__db_cache_size=385875968
burton1.__java_pool_size=16777216
burton2.__java_pool_size=16777216
burton2.__large_pool_size=33554432
burton1.__large_pool_size=33554432
burton1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
burton1.__pga_aggregate_target=486539264
burton2.__pga_aggregate_target=486539264
burton1.__sga_target=704643072
burton2.__sga_target=704643072
burton1.__shared_io_pool_size=0
burton2.__shared_io_pool_size=0
burton2.__shared_pool_size=234881024
burton1.__shared_pool_size=251658240
burton1.__streams_pool_size=0
burton2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/burton/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/burton/controlfile/current.281.941298367','+FRA/burton/controlfile/current.256.941298369'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='burton'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=burtonXDB)'
burton2.instance_number=2
burton1.instance_number=1
*.log_archive_dest_1='location=+data/burton/archivelog'
*.log_archive_format='arch_%t_%s_%r.arc'
*.memory_target=1189085184
*.open_cursors=300
*.processes=150
*.remote_listener='scan-ip.burton.com:1521'
*.remote_login_passwordfile='exclusive'
burton2.thread=2
burton1.thread=1
burton1.undo_tablespace='UNDOTBS1'
burton2.undo_tablespace='UNDOTBS2'
3、在目标端oracle用户下配置环境变量(节点2修改SID:burton2)
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=burton1
export PATH=/usr/sbin:$PATH
export PATH=$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
4、创建必要的目录
4.1 在 oracle 用户下创建(所有节点执行)
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/backup
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/archivelog
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/admin/burton/adump
4.2 在 grid 用户下在共享设备创建必要的目录
[grid@rac1 ~]$ asmcmd
[grid@dbrac1 ~]$ asmcmd
ASMCMD> ls
DATA/
FRA/
OCRVOTE/
ASMCMD> cd data
ASMCMD> mkdir BURTON
ASMCMD> cd burton
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir TEMPFILE
ASMCMD> mkdir DATAFILE
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir PARAMETERFILE
ASMCMD> mkdir archivelog
ASMCMD> mkdir archivelog
ASMCMD> cd ../../fra
ASMCMD> mkdir BURTON
ASMCMD> cd burton
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir ONLINELOG
5、用源端 pfile 创建目标端数据库 spfile
5.1 在目标端编辑 /tmp/pfile.ora (根据实际情况修改内存配置)
[oracle@dbrac1 ~]$ vi /tmp/pfile.ora
*.audit_file_dest='/u01/app/oracle/admin/burton/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/burton/controlfile/current.281.941298367','+FRA/burton/controlfile/current.256.941298369'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='burton'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=burtonXDB)'
burton2.instance_number=2
burton1.instance_number=1
*.log_archive_dest_1='location=+data/burton/archivelog'
*.log_archive_format='arch_%t_%s_%r.arc'
*.memory_target=6089085184
*.open_cursors=300
*.processes=150
*.remote_listener='dbscan-ip.burton.com:1521'
*.remote_login_passwordfile='exclusive'
burton2.thread=2
burton1.thread=1
burton1.undo_tablespace='UNDOTBS1'
burton2.undo_tablespace='UNDOTBS2'
5.2 在目标端用 pfile.ora 在共享设备上创建spfile
[oracle@dbrac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 18 17:37:53 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile='+DATA/burton/spfileburton.ora' from pfile='/tmp/pfile.ora';
File created.
5.3 在所有节点上创建 pfile 内容指向共享设备上的spfile文件
节点1：
[oracle@dbrac1 dbs]$ echo "SPFILE='+DATA/burton/spfileburton.ora' " > /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initburton1.ora
[oracle@dbrac1 dbs]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initburton1.ora
SPFILE='+DATA/burton/spfileburton.ora'
节点2：
[oracle@dbrac2 ~]$ echo "SPFILE='+DATA/burton/spfileburton.ora' " > /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initburton2.ora
[oracle@dbrac2 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initburton2.ora
SPFILE='+DATA/burton/spfileburton.ora'
6、创建口令文件
节点1：
[oracle@dbrac1 dbs]$ orapwd file=?/dbs/orapwburton1 password=oracle4U
节点2
[oracle@dbrac2 dbs]$ orapwd file=?/dbs/orapwburton2 password=oracle4U
7、还原控制文件(在一个节点上执行)
7.1 数据库启动到 nomount
[oracle@dbrac1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 18 17:54:41 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 6062931968 bytes
Fixed Size 2264376 bytes
Variable Size 3305112264 bytes
Database Buffers 2734686208 bytes
Redo Buffers 20869120 bytes
注：报错 ORA-01565，参考文章末尾。
SQL> exit
[oracle@dbrac1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 18-APR-2017 18:16:16
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 18-APR-2017 11:09:57
Uptime 0 days 7 hr. 6 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/dbrac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.165)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.197)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "burton" has 1 instance(s).
Instance "burton1", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
[oracle@dbrac1 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 18 18:18:26 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: BURTON (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/backup/full_BURTON_20170418_41_19s20mrm_1_1';
Starting restore at 2017-04-18 18:19:06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 instance=burton1 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/burton/controlfile/current.265.941653147
output file name=+FRA/burton/controlfile/current.256.941653147
Finished restore at 2017-04-18 18:19:07
注：不知道哪个文件还原控制文件，可以在源数据库中查看 list backup of controlfile;
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> catalog start with '/u01/app/oracle/backup/' ;
注：报错 ORA-12154,解决方案见文章末尾
[oracle@dbrac1 ~]$ rman target
RMAN> catalog start with '/u01/app/oracle/backup/' ;
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/arc_BURTON_20170418_44_1cs20ms1_1_1
File Name: /u01/app/oracle/backup/arc_BURTON_20170418_43_1bs20ms1_1_1
File Name: /u01/app/oracle/backup/full_BURTON_20170418_42_1as20mrm_1_1
File Name: /u01/app/oracle/backup/full_BURTON_20170418_41_19s20mrm_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/arc_BURTON_20170418_44_1cs20ms1_1_1
File Name: /u01/app/oracle/backup/arc_BURTON_20170418_43_1bs20ms1_1_1
File Name: /u01/app/oracle/backup/full_BURTON_20170418_42_1as20mrm_1_1
File Name: /u01/app/oracle/backup/full_BURTON_20170418_41_19s20mrm_1_1
8、恢复数据库
8.1 查看源端数据文件
SQL> set line 80
SQL> set pagesize 9999
SQL> col file_name for a60
SQL> select 'set newname for datafile '||file_id||' to '''||'+DATA/burton/datafile'||substr(file_name,instr(file_name,'/',-1,1),length(file_name))||''';' from dba_data_files order by file_id;
'SETNEWNAMEFORDATAFILE'||FILE_ID||'TO'''||'+DATA/BURTON/DATAFILE'||SUBSTR(FILE_N
--------------------------------------------------------------------------------
set newname for datafile 1 to '+DATA/burton/datafile/system.264.941298255';
set newname for datafile 2 to '+DATA/burton/datafile/sysaux.270.941298255';
set newname for datafile 3 to '+DATA/burton/datafile/undotbs1.268.941298255';
set newname for datafile 4 to '+DATA/burton/datafile/users.274.941298255';
set newname for datafile 5 to '+DATA/burton/datafile/undotbs2.279.941298541';
set newname for datafile 6 to '+DATA/burton/datafile/test.258.941302355';
6 rows selected.
SQL> select 'set newname for tempfile '||file_id||' to '''||'+DATA/burton/tempfile'||substr(file_name,instr(file_name,'/',-1,1),length(file_name))||''';' from dba_temp_files;
'SETNEWNAMEFORTEMPFILE'||FILE_ID||'TO'''||'+DATA/BURTON/TEMPFILE'||SUBSTR(FILE_N
--------------------------------------------------------------------------------
set newname for tempfile 1 to '+DATA/burton/tempfile/temp.262.941298399';
8.2 目标端rac1还原数据库(为简单起见，路径名称完全一样，不需要 set newname)
RMAN> restore database;
Starting restore at 2017-04-19 10:10:46
using channel ORA_DISK_1
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/burton/datafile/system.264.941298255
channel ORA_DISK_1: restoring datafile 00004 to +DATA/burton/datafile/users.274.941298255
channel ORA_DISK_1: restoring datafile 00005 to +DATA/burton/datafile/undotbs2.279.941298541
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/full_BURTON_20170418_39_17s20mom_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/full_BURTON_20170418_39_17s20mom_1_1 tag=TAG20170418T155606
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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/burton/datafile/sysaux.270.941298255
channel ORA_DISK_1: restoring datafile 00003 to +DATA/burton/datafile/undotbs1.268.941298255
channel ORA_DISK_1: restoring datafile 00006 to +DATA/burton/datafile/test.258.941302355
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/full_BURTON_20170418_40_18s20mom_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/full_BURTON_20170418_40_18s20mom_1_1 tag=TAG20170418T155606
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2017-04-19 10:11:00
9、对数据库进行恢复
9.1 查看能最大能恢复到哪个SCN
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
33 10.68M DISK 00:00:04 2017-04-18 15:56:03
BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20170418T155558
Piece Name: /u01/app/oracle/backup/arc_BURTON_20170418_36_14s20mof_1_1
List of Archived Logs in backup set 33
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 21 1080023 2017-04-15 19:58:51 1083858 2017-04-15 20:33:02
1 22 1083869 2017-04-17 09:42:12 1094772 2017-04-17 11:23:03
2 16 1080027 2017-04-15 19:58:51 1083871 2017-04-17 09:42:12
2 17 1083871 2017-04-17 09:42:12 1083873 2017-04-17 09:42:14
2 18 1083873 2017-04-17 09:42:14 1094884 2017-04-17 11:24:03
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
34 13.28M DISK 00:00:04 2017-04-18 15:56:03
BP Key: 34 Status: AVAILABLE Compressed: NO Tag: TAG20170418T155558
Piece Name: /u01/app/oracle/backup/arc_BURTON_20170418_37_15s20mof_1_1
List of Archived Logs in backup set 34
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 23 1094772 2017-04-17 11:23:03 1094800 2017-04-17 11:23:29
1 24 1094800 2017-04-17 11:23:29 1111931 2017-04-18 15:55:49
2 19 1094907 2017-04-18 12:11:37 1111934 2017-04-18 15:55:49
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
35 3.50K DISK 00:00:00 2017-04-18 15:56:04
BP Key: 35 Status: AVAILABLE Compressed: NO Tag: TAG20170418T155558
Piece Name: /u01/app/oracle/backup/arc_BURTON_20170418_38_16s20mok_1_1
List of Archived Logs in backup set 35
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 25 1111931 2017-04-18 15:55:49 1111947 2017-04-18 15:55:58
2 20 1111934 2017-04-18 15:55:49 1111944 2017-04-18 15:55:55
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
38 2.50K DISK 00:00:00 2017-04-18 15:57:53
BP Key: 38 Status: AVAILABLE Compressed: NO Tag: TAG20170418T155753
Piece Name: /u01/app/oracle/backup/arc_BURTON_20170418_44_1cs20ms1_1_1
List of Archived Logs in backup set 38
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 26 1111947 2017-04-18 15:55:58 1112026 2017-04-18 15:57:47
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
39 2.00K DISK 00:00:00 2017-04-18 15:57:53
BP Key: 39 Status: AVAILABLE Compressed: NO Tag: TAG20170418T155753
Piece Name: /u01/app/oracle/backup/arc_BURTON_20170418_43_1bs20ms1_1_1
List of Archived Logs in backup set 39
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
2 21 1111944 2017-04-18 15:55:55 1112030 2017-04-18 15:57:49
注：Thrd1 最大能恢复到 1112026 , Thrd2 最大能恢复到 1112030，故选取 1112026 为恢复CSN，如不加SCN会因缺少一部分日志而报错。
9.2 恢复数据库
RMAN> recover database until scn 1112026;
Starting recover at 2017-04-19 10:37:18
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=2 sequence=21
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/arc_BURTON_20170418_43_1bs20ms1_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/arc_BURTON_20170418_43_1bs20ms1_1_1 tag=TAG20170418T155753
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=26
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/arc_BURTON_20170418_44_1cs20ms1_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/arc_BURTON_20170418_44_1cs20ms1_1_1 tag=TAG20170418T155753
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+DATA/burton/archivelog/arch_1_26_941298372.arc thread=1 sequence=26
archived log file name=+DATA/burton/archivelog/arch_2_21_941298372.arc thread=2 sequence=21
media recovery complete, elapsed time: 00:00:00
Finished recover at 2017-04-19 10:37:21
10、将源库新增的归档日志和在线日志拷贝到目标端
10.1 源库创建测试数据，模拟数据库备份后的变化
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 19 10:48:38 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 system switch logfile;
System altered.
SQL> create table t5 (id number,name varchar2(10));
Table created.
SQL> insert into t5 values (1,'burton');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
10.2 关闭源库
[oracle@rac1 ~]$ srvctl stop database -d burton -o immediate
[oracle@rac1 ~]$ srvctl status database -d burton
Instance burton1 is not running on node rac1
Instance burton2 is not running on node rac2
10.3 将新增的归档日志和在线日志复制到目标端数据库
10.3.1 将源端数据库归档日志复制到目标端数据库
a. 将源端归档日志从共享设备复制到本地
[root@rac2 Desktop]# mkdir -p /u01/app/tmparch
[root@rac2 Desktop]# chown grid:oinstall /u01/app/tmparch
[root@rac2 Desktop]# su - grid
[grid@rac2 ~]$ asmcmd
ASMCMD> cd +DATA/burton/archivelog/2017_04_18
ASMCMD> pwd
+DATA/burton/archivelog/2017_04_18
ASMCMD> ls
thread_1_seq_27.266.941652809
thread_1_seq_28.256.941652811
thread_2_seq_22.278.941654057
ASMCMD> cp thread_1_seq_27.266.941652809 /u01/app/tmparch
copying +DATA/burton/archivelog/2017_04_18/thread_1_seq_27.266.941652809 -> /u01/app/tmparch/thread_1_seq_27.266.941652809
ASMCMD> cp thread_1_seq_28.256.941652811 /u01/app/tmparch
copying +DATA/burton/archivelog/2017_04_18/thread_1_seq_28.256.941652811 -> /u01/app/tmparch/thread_1_seq_28.256.941652811
ASMCMD> cp thread_2_seq_22.278.941654057 /u01/app/tmparch
copying +DATA/burton/archivelog/2017_04_18/thread_2_seq_22.278.941654057 -> /u01/app/tmparch/thread_2_seq_22.278.941654057
ASMCMD> cd ../2017_04_19
ASMCMD> pwd
+DATA/burton/archivelog/2017_04_19
ASMCMD> cp thread_1_seq_29.273.941712529 /u01/app/tmparch
copying +DATA/burton/archivelog/2017_04_19/thread_1_seq_29.273.941712529 -> /u01/app/tmparch/thread_1_seq_29.273.941712529
ASMCMD> cp thread_1_seq_30.277.941712551 /u01/app/tmparch
copying +DATA/burton/archivelog/2017_04_19/thread_1_seq_30.277.941712551 -> /u01/app/tmparch/thread_1_seq_30.277.941712551
ASMCMD> cp thread_2_seq_23.261.941712639 /u01/app/tmparch
copying +DATA/burton/archivelog/2017_04_19/thread_2_seq_23.261.941712639 -> /u01/app/tmparch/thread_2_seq_23.261.941712639
[grid@rac2 ~]$ ll /u01/app/tmparch
total 13972
-rw-r----- 1 grid oinstall 5414400 Apr 19 11:16 thread_1_seq_27.266.941652809
-rw-r----- 1 grid oinstall 1024 Apr 19 11:16 thread_1_seq_28.256.941652811
-rw-r----- 1 grid oinstall 2224640 Apr 19 11:17 thread_1_seq_29.273.941712529
-rw-r----- 1 grid oinstall 13824 Apr 19 11:17 thread_1_seq_30.277.941712551
-rw-r----- 1 grid oinstall 5069312 Apr 19 11:16 thread_2_seq_22.278.941654057
-rw-r----- 1 grid oinstall 1569792 Apr 19 11:17 thread_2_seq_23.261.941712639
b. 将源端本地归档日志拷贝到目标端数据库本地
[grid@rac2 tmparch]$ scp -r /u01/app/tmparch/*oracle@192.168.10.165:/u01/app/oracle/archivelog
oracle@192.168.10.165'spassword:
thread_1_seq_27.266.941652809 100% 5288KB 5.2MB/s 00:01
thread_1_seq_28.256.941652811 100% 1024 1.0KB/s 00:00
thread_1_seq_29.273.941712529 100% 2173KB 2.1MB/s 00:00
thread_1_seq_30.277.941712551 100% 14KB 13.5KB/s 00:00
thread_2_seq_22.278.941654057 100% 4951KB 4.8MB/s 00:00
thread_2_seq_23.261.941712639 100% 1533KB 1.5MB/s 00:00
10.3.2 将在线日志复制到目标端数据库
a. 将源端在线重做日志从共享设备复制到本地
[root@rac2 Desktop]# mkdir -p /u01/app/tmpredo
[root@rac2 Desktop]# chown grid:oinstall /u01/app/tmpredo
[root@rac2 Desktop]# su - grid
[grid@rac2 ~]$ asmcmd
ASMCMD> cd +data/burton/onlinelog
ASMCMD> ls
group_1.257.941298373
group_2.267.941298375
group_3.269.941298671
group_4.271.941298675
ASMCMD> cp group_1.257.941298373 /u01/app/tmpredo/group_1
copying +data/burton/onlinelog/group_1.257.941298373 -> /u01/app/tmpredo/group_1
ASMCMD> cp group_2.267.941298375 /u01/app/tmpredo/group_2
copying +data/burton/onlinelog/group_2.267.941298375 -> /u01/app/tmpredo/group_2
ASMCMD> cp group_3.269.941298671 /u01/app/tmpredo/group_3
copying +data/burton/onlinelog/group_3.269.941298671 -> /u01/app/tmpredo/group_3
ASMCMD> cp group_4.271.941298675 /u01/app/tmpredo/group_4
copying +data/burton/onlinelog/group_4.271.941298675 -> /u01/app/tmpredo/group_4
注：如不重命名，后拷贝到目标端数据库共享设备上会报错 ORA-15056，见章末。
[grid@rac2 ~]# ll /u01/app/tmpredo
total 204816
-rw-r----- 1 grid oinstall 52429312 Apr 19 12:30 group_1
-rw-r----- 1 grid oinstall 52429312 Apr 19 12:30 group_2
-rw-r----- 1 grid oinstall 52429312 Apr 19 12:31 group_3
-rw-r----- 1 grid oinstall 52429312 Apr 19 12:31 group_4
b. 将源端本地在线重做日志拷贝到目标端数据库本地
[grid@rac2 ~]$ scp -r /u01/app/tmpredo/*grid@192.168.10.165:/u01/app/tmpredo/
grid@192.168.10.165'spassword:
group_1 100% 50MB 10.0MB/s 00:05
group_2 100% 50MB 12.5MB/s 00:04
group_3 100% 50MB 10.0MB/s 00:05
group_4 100% 50MB 12.5MB/s 00:04
c. 将目标端本地的在线重做日志拷贝到共享文件上(另起窗口grid用户操作)
[grid@dbrac1 tmpredo]$ asmcmd cp /u01/app/tmpredo/group_1 +DATA/burton/onlinelog/group_1
copying /u01/app/tmpredo/group_1 -> +DATA/burton/onlinelog/group_1
[grid@dbrac1 tmpredo]$ asmcmd cp /u01/app/tmpredo/group_2 +DATA/burton/onlinelog/group_2
copying /u01/app/tmpredo/group_2 -> +DATA/burton/onlinelog/group_2
[grid@dbrac1 tmpredo]$ asmcmd cp /u01/app/tmpredo/group_3 +DATA/burton/onlinelog/group_3
copying /u01/app/tmpredo/group_3 -> +DATA/burton/onlinelog/group_3
[grid@dbrac1 tmpredo]$ asmcmd cp /u01/app/tmpredo/group_4 +DATA/burton/onlinelog/group_4
copying /u01/app/tmpredo/group_4 -> +DATA/burton/onlinelog/group_4
11、对目标数据库再次进行恢复
11.1 重新注册归档文件
RMAN> catalog archivelog '/u01/app/oracle/archivelog/thread_1_seq_27.266.941652809';
cataloged archived log
archived log file name=/u01/app/oracle/archivelog/thread_1_seq_27.266.941652809 RECID=42 STAMP=941722884
RMAN> catalog archivelog '/u01/app/oracle/archivelog/thread_1_seq_28.256.941652811';
cataloged archived log
archived log file name=/u01/app/oracle/archivelog/thread_1_seq_28.256.941652811 RECID=43 STAMP=941722890
RMAN> catalog archivelog '/u01/app/oracle/archivelog/thread_1_seq_29.273.941712529';
cataloged archived log
archived log file name=/u01/app/oracle/archivelog/thread_1_seq_29.273.941712529 RECID=44 STAMP=941722897
RMAN> catalog archivelog '/u01/app/oracle/archivelog/thread_1_seq_30.277.941712551';
cataloged archived log
archived log file name=/u01/app/oracle/archivelog/thread_1_seq_30.277.941712551 RECID=45 STAMP=941722902
RMAN> catalog archivelog '/u01/app/oracle/archivelog/thread_2_seq_22.278.941654057';
cataloged archived log
archived log file name=/u01/app/oracle/archivelog/thread_2_seq_22.278.941654057 RECID=46 STAMP=941722908
RMAN> catalog archivelog '/u01/app/oracle/archivelog/thread_2_seq_23.261.941712639';
cataloged archived log
archived log file name=/u01/app/oracle/archivelog/thread_2_seq_23.261.941712639 RECID=47 STAMP=941722913
11.2 rename在线日志
[oracle@dbrac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 19 13:43:21 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> set line 100
SQL> col member for a50
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
2 +DATA/burton/onlinelog/group_2.267.941298375
2 +FRA/burton/onlinelog/group_2.257.941298379
1 +DATA/burton/onlinelog/group_1.257.941298373
1 +FRA/burton/onlinelog/group_1.260.941298375
3 +DATA/burton/onlinelog/group_3.269.941298671
3 +FRA/burton/onlinelog/group_3.258.941298673
4 +DATA/burton/onlinelog/group_4.271.941298675
4 +FRA/burton/onlinelog/group_4.259.941298677
8 rows selected.
SQL> alter database rename file '+DATA/burton/onlinelog/group_1.257.941298373' to '+DATA/burton/onlinelog/group_1';
Database altered.
SQL> alter database rename file '+DATA/burton/onlinelog/group_2.267.941298375' to '+DATA/burton/onlinelog/group_2';
Database altered.
SQL> alter database rename file '+DATA/burton/onlinelog/group_3.269.941298671' to '+DATA/burton/onlinelog/group_3';
Database altered.
SQL> alter database rename file '+DATA/burton/onlinelog/group_4.271.941298675' to '+DATA/burton/onlinelog/group_4';
Database altered.
11.3 应用新的日志，恢复数据库
RMAN> recover database;
Starting recover at 2017-04-19 13:53:20
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/archivelog/thread_1_seq_27.266.941652809
archived log for thread 1 with sequence 28 is already on disk as file /u01/app/oracle/archivelog/thread_1_seq_28.256.941652811
archived log for thread 1 with sequence 29 is already on disk as file /u01/app/oracle/archivelog/thread_1_seq_29.273.941712529
archived log for thread 1 with sequence 30 is already on disk as file +DATA/burton/onlinelog/group_2
archived log for thread 1 with sequence 31 is already on disk as file +DATA/burton/onlinelog/group_1
archived log for thread 2 with sequence 21 is already on disk as file +DATA/burton/archivelog/arch_2_21_941298372.arc
archived log for thread 2 with sequence 22 is already on disk as file +DATA/burton/onlinelog/group_4
archived log for thread 2 with sequence 23 is already on disk as file +DATA/burton/onlinelog/group_3
archived log file name=/u01/app/oracle/archivelog/thread_1_seq_27.266.941652809 thread=1 sequence=27
archived log file name=+DATA/burton/archivelog/arch_2_21_941298372.arc thread=2 sequence=21
archived log file name=+DATA/burton/onlinelog/group_4 thread=2 sequence=22
archived log file name=/u01/app/oracle/archivelog/thread_1_seq_28.256.941652811 thread=1 sequence=28
archived log file name=/u01/app/oracle/archivelog/thread_1_seq_29.273.941712529 thread=1 sequence=29
archived log file name=+DATA/burton/onlinelog/group_3 thread=2 sequence=23
archived log file name=+DATA/burton/onlinelog/group_2 thread=1 sequence=30
archived log file name=+DATA/burton/onlinelog/group_1 thread=1 sequence=31
Finished recover at 2017-04-19 13:53:26
12、打开数据库
RMAN> alter database open resetlogs;
database opened
三、数据库参数调整
1、重建 temp表空间
1.1 因为在recover 的时候不会对temp 表空间进行recover。所以等recover 后，我们要手工重建temp表空间。
[grid@dbrac1 tmpredo]$ asmcmd lsof |grep temp
burton burton1 +data/burton/tempfile/temp.277.941723763
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> alter tablespace temp add tempfile '+DATA' size 50M;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
-------------------------------------------------------------------------------
+DATA/burton/tempfile/temp.277.941723763
+DATA/burton/tempfile/temp.270.941724325
SQL> alter database tempfile '+DATA/burton/tempfile/temp.277.941723763' offline ;
Database altered.
SQL> alter database tempfile '+DATA/burton/tempfile/temp.277.941723763' drop including datafiles;
alter database tempfile '+DATA/burton/tempfile/temp01.dbf' drop including datafiles
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time
1.2 重启数据库再删除原temp表空间
SQL> shutdown immediate
SQL> startup
SQL> alter database tempfile '+DATA/burton/tempfile/temp.277.941723763' drop including datafiles;
Database altered.
2、整理在线日志文件
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
2 +DATA/burton/onlinelog/group_2
2 +DATA/burton/onlinelog/group_2.278.941723755
1 +DATA/burton/onlinelog/group_1
1 +DATA/burton/onlinelog/group_1.281.941723753
3 +DATA/burton/onlinelog/group_3
3 +DATA/burton/onlinelog/group_3.272.941723757
4 +DATA/burton/onlinelog/group_4
4 +DATA/burton/onlinelog/group_4.271.941723759
1 +FRA/burton/onlinelog/group_1.258.941723753
2 +FRA/burton/onlinelog/group_2.259.941723755
3 +FRA/burton/onlinelog/group_3.260.941723757
4 +FRA/burton/onlinelog/group_4.263.941723759
12 rows selected.
SQL> select GROUP#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 UNUSED
3 UNUSED
4 UNUSED
SQL> alter database drop logfile member '+DATA/burton/onlinelog/group_2';
Database altered.
SQL> alter database drop logfile member '+DATA/burton/onlinelog/group_3';
Database altered.
SQL> alter database drop logfile member '+DATA/burton/onlinelog/group_4';
Database altered.
SQL> alter database drop logfile member '+DATA/burton/onlinelog/group_1';
alter database drop logfile member '+DATA/burton/onlinelog/group_1'
*
ERROR at line 1:
ORA-01609: log 1 is the current log for thread 1 - cannot drop members
ORA-00312: online log 1 thread 1: '+DATA/burton/onlinelog/group_1'
ORA-00312: online log 1 thread 1: '+DATA/burton/onlinelog/group_1.281.941723753'
ORA-00312: online log 1 thread 1: '+FRA/burton/onlinelog/group_1.258.941723753'
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 UNUSED
4 UNUSED
SQL> alter system checkpoint;
System altered.
SQL> select GROUP#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 UNUSED
4 UNUSED
SQL> alter database drop logfile member '+DATA/burton/onlinelog/group_1';
Database altered.
SQL> shutdown immediate
3、将其他的信息注册到CRS里
[oracle@dbrac1 ~]$ srvctl add database -d burton -o $ORACLE_HOME -p +DATA/burton/spfileburton.ora
[oracle@dbrac1 ~]$ srvctl add instance -d burton -i burton1 -n dbrac1
[oracle@dbrac1 ~]$ srvctl add instance -d burton -i burton2 -n dbrac2
[oracle@dbrac1 ~]$ srvctl start database -d burton -o open
4、配置集群监听
4.1 配置tnsnames.ora (所有节点执行)
[oracle@dbrac1 ~]$ su - oracle
[oracle@dbrac1 ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@dbrac1 admin]$ vi tnsnames.ora
BURTON =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbscan-ip.burton.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = burton)
)
)
BURTON1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.165)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.197)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = burton1)
)
)
4.2 重启监听
[oracle@dbrac1 admin]$ srvctl stop listener
[oracle@dbrac1 admin]$ srvctl start listener
[oracle@dbrac1 admin]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): dbrac1,dbrac2
4.3 测试远程访问，用源端连接目标端
配置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
BURTONS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.199 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = burton)
)
)
[oracle@rac1 admin]$ sqlplusas sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 19 14:39:22 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>
四、验证数据库及集群情况
1、查看数据库启动情况
[oracle@dbrac1 admin]$ srvctl status database -d burton
Instance burton1 is running on node dbrac1
Instance burton2 is running on node dbrac2
2、查看数据是否全部同步
[oracle@dbrac1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 19 14:45:29 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 t5;
ID NAME
---------- ----------
1 burton
3、查看集群情况
3.1 数据库配置信息
[oracle@dbrac1 ~]$ srvctl config database -d burton
Database unique name: burton
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/burton/spfileburton.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: burton
Database instances: burton1,burton2
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Database is administrator managed
3.2 集群配置信息
[root@dbrac1 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE dbrac1
ONLINE ONLINE dbrac2
ora.FRA.dg
ONLINE ONLINE dbrac1
ONLINE ONLINE dbrac2
ora.LISTENER.lsnr
ONLINE ONLINE dbrac1
ONLINE ONLINE dbrac2
ora.OCRVOTE.dg
ONLINE ONLINE dbrac1
ONLINE ONLINE dbrac2
ora.asm
ONLINE ONLINE dbrac1 Started
ONLINE ONLINE dbrac2 Started
ora.gsd
OFFLINE OFFLINE dbrac1
OFFLINE OFFLINE dbrac2
ora.net1.network
ONLINE ONLINE dbrac1
ONLINE ONLINE dbrac2
ora.ons
ONLINE ONLINE dbrac1
ONLINE ONLINE dbrac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE dbrac1
ora.burton.db
1 ONLINE ONLINE dbrac1 Open
2 ONLINE ONLINE dbrac2 Open
ora.cvu
1 ONLINE ONLINE dbrac1
ora.dbrac1.vip
1 ONLINE ONLINE dbrac1
ora.dbrac2.vip
1 ONLINE ONLINE dbrac2
ora.oc4j
1 ONLINE ONLINE dbrac1
ora.scan1.vip
1 ONLINE ONLINE dbrac1
问题一：
SQL> startup nomount
ORA-01565: error in identifying file '+DATA/burton/spfileburton.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/burton/spfileburton.ora
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15040: diskgroup is incomplete
ORA-15040: diskgroup is incomplete
exit
解决方案：
[oracle@dbrac1 dbs]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/bin/
[oracle@dbrac1 bin]$ ll oracle
-rwsr-s--x 1 oracle oinstall 239626641 Apr 18 11:35 oracle
修改权限(用root 用户在所有节点执行)
[root@rac1 bin]$ chown oracle:asmadmin oracle
[root@rac1 bin]$ chmod 6751 oracle
[root@rac1 bin]$ ll oracle
-rwsr-x--x 1 oracle asmadmin 239626641 Apr 12 13:32 oracle
问题二：
RMAN> catalog start with '/u01/app/backup/' ;
Starting implicit crosscheck backup at 2017-04-18 18:19:51
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 04/18/2017 18:20:31
RMAN-12001: could not open channel ORA_DISK_1
RMAN-10008: could not create channel context
RMAN-10003: unable to connect to target database
ORA-12154: TNS:could not resolve the connect identifier specified
解决方案：
所有节点上配置TNS
[oracle@dbrac2 ~]$vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
BURTON1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.165)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.197)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = burton1)
)
)
问题三
[grid@dbrac1 tmpredo]$ asmcmd cp /u01/app/tmpredo/group_1.257.941298373 +DATA/burton/onlinelog/group_1.257.941298373
copying /u01/app/tmpredo/group_1.257.941298373 -> +DATA/burton/onlinelog/group_1.257.941298373
ASMCMD-8016: copy source '/u01/app/tmpredo/group_1.257.941298373' and target '+DATA/burton/onlinelog/group_1.257.941298373' failed
ORA-15056: additional error message
ORA-15046: ASM file name '+DATA/burton/onlinelog/group_1.257.941298373' is not in single-file creation form
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 415
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)
187
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
