Oracle11g使用rman從rac遷移到rac
一、環境配置情況
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 sys/oracle4U@burton1
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's password:
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 sys/oracle4U@burton1
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's password:
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's password:
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]$ sqlplus sys/oracle4U@burtons as 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)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30590361/viewspace-2137601/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle11g使用rman從單例項遷移到racOracle單例
- RAC 例項 遷移到 單例項 -- 使用RMAN Duplicate單例
- 使用RMAN遷移單庫到RAC
- RAC 10g下從裸裝置遷移到ASMASM
- 規劃單節點遷移到rac
- Oracle DataBase單例項遷移到Oracle RACOracleDatabase單例
- 單例項資料遷移到RAC補充單例
- RMAN遷移資料庫(rac or single)資料庫
- oracle單例項通過dataguard遷移到RAC 轉Oracle單例
- 單例項資料庫expdp遷移到RAC庫單例資料庫
- 【RMAN】oracle11g單機資料通過RMAN恢復至RACOracle
- 【RMAN】oracle11g單機資料透過RMAN恢復至RACOracle
- 【RAC】Oracle11g RAC 你不懂crs_*Oracle
- [RAC] HP Unix Install Oracle11g(RAC)Oracle
- oracle 10g rac hacmp 遷移到asm實驗步驟Oracle 10gACMASM
- Oracle 10g 安裝及單例項遷移到RACOracle 10g單例
- Oracle 12c 使用RMAN搭建物理備庫(RAC to RAC)Oracle
- aix 下 oracle 11.2 rac 把資料遷移到新儲存AIOracle
- 使用rman將資料庫遷移到ASM例項資料庫ASM
- [RAC] Oracle11g RAC禁止DB自動啟動Oracle
- 從Perforce遷移到GitGit
- 使用DynamoShake從dynamodb遷移到mongodbMongoDB
- 使用rman copy將資料庫遷移到ASM例項資料庫ASM
- 利用RMAN將資料庫從檔案系統遷移到ASM資料庫ASM
- 【RAC】Oracle11g RAC CRS磁碟丟失後恢復Oracle
- Oracle 11.2.0.4 從單例項，使用RMAN 異機恢復到RACOracle單例
- AS4 10g 單節點遷移到ASM RAC步驟總結ASM
- 從 golang flag 遷移到 cmdrGolang
- 從 Nginx 遷移到 Envoy ProxyNginx
- RMAN Duplicate Database From RAC ASM To RAC ASM [ID 461479.1]DatabaseASM
- Oracle 資料遷移（從Oracle11G高版本遷移到Oracle10G低版本）Oracle
- 【RAC】Oracle11g RAC刪除節點相關事項Oracle
- 【RAC】Oracle11g RAC新增新節點相關事項Oracle
- oracle11g RAC新增節點Oracle
- oracle11g 搭建 rac+dgOracle
- oracle11g 解除安裝racOracle
- Oracle RAC中使用RMAN管理歸檔日誌Oracle
- DB遷移RAC環境