Oracle 11g使用rman从单实例迁移到RAC

2022-07-19 09:10:44

一、准备环境

1、单实例配置

数据库版本：Oracle 11.2.0.4
操作系统：CentOS 6.5

2、RAC配置（数据库实例未装载）

服务器主机名	rac1	rac2
公共IP地址(eth0)	192.168.91.140	192.168.91.142
虚拟IP地址(eth0)	192.168.91.152	192.168.91.153
私有IP地址(eth1)	192.168.1.130	192.168.1.131

名称	说明
SCAN IP	192.168.91.154
集群实例名称	orcl
操作系统	CentOS 6.5
ORACLE	11.2.0.4
存储	ASM

[root@rac1 Desktop]# crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.FRA.dg
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.LISTENER.lsnr
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.NEW_BURTON_LISTENER.lsnr
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.OCRVOTE.dg
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.asm
               ONLINE  ONLINE       rac1                     Started
               ONLINE  ONLINE       rac2                     Started
ora.gsd
               OFFLINE OFFLINE      rac1
               OFFLINE OFFLINE      rac2
ora.net1.network
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.ons
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac1
ora.cvu
      1        ONLINE  ONLINE       rac1
ora.oc4j
      1        ONLINE  ONLINE       rac1
ora.rac1.vip
      1        ONLINE  ONLINE       rac1
ora.rac2.vip
      1        ONLINE  ONLINE       rac2
ora.scan1.vip
      1        ONLINE  ONLINE       rac1

二、源数据库备份和参数文件复制到目标端

1、对源端数据库做全备

[oracle@orcl ~]$ mkdir -p /u01/app/oracle/backup/
[oracle@orcl archivelog]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Apr 21 13:09:57 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1110855286)
RMAN> run{
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;
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';
 }

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1145 device type=DISK
validation succeeded for archived log
archived log file name=/u01/app/oracle/archivelog/arch_1_68_929977336.arc RECID=64 STAMP=941688053
Crosschecked 1 objects
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1145 device type=DISK
specification does not match any archived log in the repository
……

2、创建参数文件pfile

[oracle@orcl ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 21 13:12:16 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create pfile='/tmp/pfile.ora' from spfile;
File created.

[oracle@orcl ~]$ cat /tmp/pfile.ora
orcl.__db_cache_size=2751463424
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=1677721600
orcl.__sga_target=5033164800
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=2197815296
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/u01/app/oracle'
*.disk_asynch_io=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.filesystemio_options='SETALL'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog'
*.log_archive_format='arch_%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=1672478720
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=5017436160
*.undo_tablespace='UNDOTBS1'

3、把备份集传到目标数据库rac1上

[oracle@orcl~]$ scp -r /u01/app/oracle/backup/* oracle@192.168.91.140:/u01/app/oracle/backup/
目标端rac1上确认
[oracle@rac1 ~]$ ll /u01/app/oracle/backup
total 2386364
-rw-r--r-- 1 oracle oinstall  222914560 Apr 21 13:10 arc_ORCL_20170421_16_0gs28a5g_1_1
-rw-r--r-- 1 oracle oinstall  202394624 Apr 21 13:10 arc_ORCL_20170421_17_0hs28a5g_1_1
-rw-r--r-- 1 oracle oinstall      12288 Apr 21 13:10 arc_ORCL_20170421_22_0ms28a64_1_1
-rw-r--r-- 1 oracle oinstall  671866880 Apr 21 13:10 full_ORCL_20170421_18_0is28a5j_1_1
-rw-r--r-- 1 oracle oinstall 1336508416 Apr 21 13:10 full_ORCL_20170421_19_0js28a5k_1_1
-rw-r--r-- 1 oracle oinstall    9830400 Apr 21 13:10 full_ORCL_20170421_20_0ks28a5r_1_1
-rw-r--r-- 1 oracle oinstall      98304 Apr 21 13:10 full_ORCL_20170421_21_0ls28a5t_1_1

三、目标端数据恢复

1、配置oracle用户的环境变量（所有的节点执行,SID稍有差异）

[oracle@rac1 ~]$ vi ~/.bash_profile
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=orcl1
export PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
umask 022
[oracle@rac1 ~]$ source ~/.bash_profile

2、在共享磁盘上创建必要的目录

2.1 查看磁盘挂载情况

[root@rac1 ~]# su - grid
[grid@rac1 ~]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 13 16:32:13 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
SQL> col name for a10
SQL> col state for a10
SQL> select group_number, name, state, type, total_mb, free_mb  from v$asm_diskgroup ;
GROUP_NUMBER NAME STATE    TYPE   TOTAL_MB    FREE_MB
------------ ---------- ---------- ------------------ ---------- ----------
    1 DATA MOUNTED    EXTERN      9992       9896
    2 FRA MOUNTED    EXTERN      4996       4902
    3 OCRVOTE MOUNTED    NORMAL      2997       2071

2.2 共享磁盘上创建必要的目录

[grid@rac1 ~]$ asmcmd
ASMCMD> ls
DATA/
FRA/
OCRVOTE/
ASMCMD> cd data
ASMCMD> mkdir ORCL
ASMCMD> cd orcl
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir PARAMETERFILE
ASMCMD> mkdir DATAFILE
ASMCMD> mkdir TEMPFILE
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir archivelog
ASMCMD> cd ../../fra
ASMCMD> mkdir ORCL
ASMCMD> cd orcl
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir CONTROLFILE

3、在rac1本地磁盘上创建必要的目录（保险起见所有节点执行）

[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/admin/orcl/adump
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/archivelog
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/oradata/orcl
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/backup

4、在rac1上编辑pfile文件（根据实际情况调整内存大小）

[oracle@rac1 backup]$ vi /tmp/pfile.ora

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA/orcl/controlfile/current.289.939812385','+FRA/orcl/controlfile/current.286.939812387'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4421074432
*.diagnostic_dest='/u01/app/oracle'
*.disk_asynch_io=TRUE
*.filesystemio_options='SETALL'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_goldengate_replication=TRUE
*.log_archive_dest_1='LOCATION=+data/orcl/archivelog'
*.log_archive_format='arch_%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=302478720
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.cluster_database=false
*.sga_target=917436160
*.undo_management='AUTO'
orcl1.instance_name='orcl1'
orcl2.instance_name='orcl2'
orcl1.instance_number=1
orcl2.instance_number=2
orcl1.thread=1
orcl2.thread=2
orcl1.undo_tablespace='UNDOTBS1'
orcl2.undo_tablespace='UNDOTBS2'
注：等数据库恢复完毕后，再cluster_database设置为true。还有如果用rman duplicate必须设置如下两个参数：
    *.db_file_name_convert='/u01/app/oracle/oradata/orcl','+DATA/orcl/datafile'
    *.log_file_name_convert='/u01/app/oracle/oradata/orcl','+DATA/orcl/onlinelog','/u01/app/oracle/oradata/orcl','+FRA/orcl/onlinelog'

5、恢复数据库(一)

5.1 用pfile文件创建spfile

[oracle@rac1 ~]$ su - oracle
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 13 16:36:20 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/tmp/pfile.ora';
ORACLE instance started.
Total System Global Area  914440192 bytes
Fixed Size      2258600 bytes
Variable Size    297797976 bytes
Database Buffers   608174080 bytes
Redo Buffers      6209536 bytes
SQL> create spfile='+DATA/orcl/spfileorcl.ora' from pfile='/tmp/pfile.ora';
File created.
SQL> shutdown abort
ORACLE instance shut down.

5.2 将所有的节点上pfile文件，指向共享文件上的spfile文件

rac1节点:
[oracle@rac1 ~]$ echo "SPFILE='+DATA/orcl/spfileorcl.ora' " > /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl1.ora
rac2节点:
[oracle@rac2 ~]$ echo "SPFILE='+DATA/orcl/spfileorcl.ora' " > /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl2.ora

5.3 创建口令文件

rac1节点:
[oracle@rac1 ~]$ orapwd file=@ORACLE_HOME/dbs/orapworcl1 password=oracle4U
[oracle@rac1 ~]$ ll /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl1
-rw-r----- 1 oracle oinstall 1536 Apr 21 13:52 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl1
rac2节点:
[oracle@rac2 ~]$ orapwd file=@ORACLE_HOME/dbs/orapworcl2 password=oracle4U
[oracle@rac2 dbs]$ ll /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl2
-rw-r----- 1 oracle oinstall 1536 Apr 21 13:52 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl2

5.4 还原控制文件

[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 13 17:28:27 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area  914440192 bytes
Fixed Size      2258600 bytes
Variable Size    297797976 bytes
Database Buffers   608174080 bytes
Redo Buffers      6209536 bytes
SQL> exit

[oracle@rac1 dbs]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Apr 13 17:29:35 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/backup/full_ORCL_20170421_20_0ks28a5r_1_1';
Starting restore at 2017-04-21 13:55:09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/orcl/controlfile/current.263.941896511
output file name=+FRA/orcl/controlfile/current.256.941896511
Finished restore at 2017-04-21 13:55:13

注：具体哪个文件还原控制文件，可参数源端运行 list backup of controlfile 可知。

RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> catalog start with '/u01/app/oracle/backup';
Starting implicit crosscheck backup at 2017-04-21 13:56:01
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 2017-04-21 13:56:03
·······

6、恢复数据库(二)

6.1 查看源端数据文件

sqlplus / as sysdba
SQL> set line 80
SQL> set pagesize 9999
SQL> col file_name for a60
SQL> select 'set newname for datafile '||file_id||' to '''||'+DATA'||''';' cmd from dba_data_files order by file_id;
CMD
-----------------------------------------------------------------------------
set newname for datafile 1 to '+DATA';
set newname for datafile 2 to '+DATA';
set newname for datafile 3 to '+DATA';
set newname for datafile 4 to '+DATA';
set newname for datafile 5 to '+DATA';
SQL> select 'set newname for tempfile '||file_id||' to '''||'+DATA'||''';' cmd from dba_temp_files;
CMD
-----------------------------------------------------------------------------
set newname for tempfile 1 to '+DATA';

6.2 目标端rac1执行：

[oracle@rac1 dbs]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Apr 13 17:45:29 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1110855286, not open)
RMAN> run {
set newname for datafile 1 to '+DATA';
set newname for datafile 2 to '+DATA';
set newname for datafile 3 to '+DATA';
set newname for datafile 4 to '+DATA';
set newname for datafile 5 to '+DATA';
set newname for tempfile 1 to '+DATA';
restore database;
switch datafile all;
switch tempfile all;
}
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2017-04-21 14:23:43
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA
channel ORA_DISK_1: restoring datafile 00005 to +DATA
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/full_ORCL_20170421_18_0is28a5j_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/full_ORCL_20170421_18_0is28a5j_1_1 tag=TAG20170421T131011
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to +DATA
channel ORA_DISK_1: restoring datafile 00003 to +DATA
channel ORA_DISK_1: restoring datafile 00004 to +DATA
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/full_ORCL_20170421_19_0js28a5k_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/full_ORCL_20170421_19_0js28a5k_1_1 tag=TAG20170421T131011
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:56
Finished restore at 2017-04-21 14:27:45
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=941898467 file name=+DATA/orcl/datafile/system.280.941898225
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=941898467 file name=+DATA/orcl/datafile/sysaux.265.941898351
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=941898467 file name=+DATA/orcl/datafile/undotbs1.276.941898351
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=941898467 file name=+DATA/orcl/datafile/users.261.941898351
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=941898468 file name=+DATA/orcl/datafile/orcl.275.941898225
renamed tempfile 1 to +DATA in control file

RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
16      193.02M    DISK        00:00:00     2017-04-21 13:10:08
        BP Key: 16   Status: AVAILABLE  Compressed: NO  Tag: TAG20170421T131007
        Piece Name: /u01/app/oracle/backup/arc_ORCL_20170421_17_0hs28a5g_1_1
  List of Archived Logs in backup set 16
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    69      6759027    2017-04-19 04:00:52 6860693    2017-04-21 13:10:05
  1    70      6860693    2017-04-21 13:10:05 6860702    2017-04-21 13:10:07
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
17      212.59M    DISK        00:00:02     2017-04-21 13:10:10
        BP Key: 17   Status: AVAILABLE  Compressed: NO  Tag: TAG20170421T131007
        Piece Name: /u01/app/oracle/backup/arc_ORCL_20170421_16_0gs28a5g_1_1
  List of Archived Logs in backup set 17
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    68      6658903    2017-04-16 20:10:20 6759027    2017-04-19 04:00:52
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
22      11.50K     DISK        00:00:00     2017-04-21 13:10:28
        BP Key: 22   Status: AVAILABLE  Compressed: NO  Tag: TAG20170421T131028
        Piece Name: /u01/app/oracle/backup/arc_ORCL_20170421_22_0ms28a64_1_1
  List of Archived Logs in backup set 22
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    71      6860702    2017-04-21 13:10:07 6860727    2017-04-21 13:10:28

注：根据备份集可以先恢复到SCN为 6860727 ，如果直接 recover database 会因丢失部分日志报错，但可以忽略。

RMAN> recover database until scn 6860727;
Starting recover at 2017-04-21 14:37:29
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=71
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/arc_ORCL_20170421_22_0ms28a64_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/arc_ORCL_20170421_22_0ms28a64_1_1 tag=TAG20170421T131028
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+DATA/orcl/archivelog/arch_1_71_929977336.arc thread=1 sequence=71
media recovery complete, elapsed time: 00:00:00
Finished recover at 2017-04-21 14:37:34

7、源端备份后的变化数据

7.1 准备源数据全备后变化的数据

[oracle@orcl ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 14 10:17:15 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system switch logfile;
System altered.
SQL> create table t4 (id number,name varchar2(20));
Table created.
SQL> insert into t4 values (1,'burton');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

7.2 在线重做日志和新归档日志拷贝到目标库rac1

scp -r ./* oracle@192.168.91.140:/u01/app/oracle/archivelog
scp -r ./redo* oracle@192.168.91.140:/u01/app/oracle/oradata/orcl

8、恢复新增数据

8.1 查看复制到目标端在线重做日志和新归档日志

rac1:
[oracle@rac1 ~]$ ll /u01/app/oracle/archivelog/
total 1636
-rw-r--r-- 1 oracle oinstall 1654272 Apr 21 14:32 arch_1_72_929977336.arc
-rw-r--r-- 1 oracle oinstall   16896 Apr 21 14:33 arch_1_73_929977336.arc
[oracle@rac1 ~]$ ll /u01/app/oracle/oradata/orcl/
total 786448
-rw-r--r-- 1 oracle oinstall 268435968 Apr 21 14:32 redo01.log
-rw-r--r-- 1 oracle oinstall 268435968 Apr 21 14:33 redo02.log
-rw-r--r-- 1 oracle oinstall 268435968 Apr 21 14:33 redo03.log

8.2 把归档日志注册到rman

RMAN> catalog archivelog '/u01/app/oracle/archivelog/arch_1_72_929977336.arc','/u01/app/oracle/archivelog/arch_1_73_929977336.arc';
cataloged archived log
archived log file name=/u01/app/oracle/archivelog/arch_1_72_929977336.arc RECID=70 STAMP=941899951
cataloged archived log
archived log file name=/u01/app/oracle/archivelog/arch_1_73_929977336.arc RECID=71 STAMP=941899951

RMAN> recover database;
Starting recover at 2017-04-21 14:55:25
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 72 is already on disk as file /u01/app/oracle/oradata/orcl/redo01.log
archived log for thread 1 with sequence 73 is already on disk as file /u01/app/oracle/oradata/orcl/redo02.log
archived log for thread 1 with sequence 74 is already on disk as file /u01/app/oracle/oradata/orcl/redo03.log
archived log file name=/u01/app/oracle/oradata/orcl/redo01.log thread=1 sequence=72
archived log file name=/u01/app/oracle/oradata/orcl/redo02.log thread=1 sequence=73
archived log file name=/u01/app/oracle/oradata/orcl/redo03.log thread=1 sequence=74
media recovery complete, elapsed time: 00:00:09
Finished recover at 2017-04-21 14:55:37

9、修改日志文件路径

[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> set line 150
SQL> col member for a50
SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER           IS_
---------- ------- ------- -------------------------------------------------- ---
  3    ONLINE  /u01/app/oracle/oradata/orcl/redo03.log       NO
  2    ONLINE  /u01/app/oracle/oradata/orcl/redo02.log       NO
  1    ONLINE  /u01/app/oracle/oradata/orcl/redo01.log       NO
SQL> alter database rename file '/u01/app/oracle/oradata/orcl/redo01.log' to '+DATA';
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/orcl/redo02.log' to '+DATA';
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/orcl/redo03.log' to '+DATA';
Database altered.

SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER           IS_
---------- ------- ------- -------------------------------------------------- ---
  3    ONLINE  +DATA           NO
  2    ONLINE  +DATA           NO
  1    ONLINE  +DATA           NO

SQL> alter database open resetlogs;
Database altered.

SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER           IS_
---------- ------- ------- -------------------------------------------------- ---
  3    ONLINE  +DATA/orcl/onlinelog/group_3.256.941900533       NO
  2    ONLINE  +DATA/orcl/onlinelog/group_2.278.941900517       NO
  1    ONLINE  +DATA/orcl/onlinelog/group_1.273.941900501       NO
  1    ONLINE  +FRA/orcl/onlinelog/group_1.259.941900513       YES
  2    ONLINE  +FRA/orcl/onlinelog/group_2.258.941900527       YES
  3    ONLINE  +FRA/orcl/onlinelog/group_3.257.941900545       YES
6 rows selected.

10、打开数据库

SQL> alter database open resetlogs;
Database altered.

四、配置数据库参数

1、查看数据情况

修改参数：
SQL> col value for a10
SQL> select * from v$option where parameter='Real Application Clusters';
PARAMETER        VALUE
----------------------------------------------- ----------
Real Application Clusters      TRUE
SQL> show parameter cluster
NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
cluster_database       boolean  FALSE
cluster_database_instances      integer  1
cluster_interconnects       string
SQL> show parameter thread
NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
parallel_threads_per_cpu      integer  2
thread         integer  1
SQL> show parameter instance_number
NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
instance_number        integer  1
SQL> alter system set cluster_database=true scope=spfile sid='*';
System altered.
SQL> alter system set cluster_database_instances=2 scope=spfile sid='*';
System altered.
SQL> alter system set instance_number=1 scope=spfile sid='orcl1';
System altered.
SQL> alter system set instance_number=2 scope=spfile sid='orcl2';
System altered.
SQL> alter system set thread=1 scope=spfile sid='orcl1';
System altered.
SQL> alter system set thread=2 scope=spfile sid='orcl2';
System altered.
SQL> show parameter undo_tablespace
NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace        string  UNDOTBS1
SQL> create undo tablespace UNDOTBS2 datafile '+DATA/orcl/datafile/undotbs02.dbf' size 100M;
Tablespace created.
SQL> alter system set undo_tablespace='UNDOTBS2' scope=spfile sid='orcl2';
System altered.
SQL> alter database add logfile thread 2 group 4 ('+DATA','+FRA') size 50M;
Database altered.
SQL> alter database add logfile thread 2 group 5 ('+DATA','+FRA') size 50M;
Database altered.
SQL> alter database add logfile thread 2 group 6 ('+DATA','+FRA') size 50M;
Database altered.
注：根据实际情况分配日志文件大小，可以增加日志组文件
    alter database add logfile member '+FRA' to group 4;
SQL> alter database enable thread 2;
Database altered
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

2、将其他的信息注册到CRS里

[oracle@rac1 ~]$ srvctl add database -d orcl -o $ORACLE_HOME -p +DATA/orcl/spfileorcl.ora
[oracle@rac1 ~]$ srvctl add instance -d orcl -i orcl1 -n rac1
[oracle@rac1 ~]$ srvctl add instance -d orcl -i orcl2 -n rac2

3、启动rac1和rac2实例

[oracle@rac1 ~]$ srvctl start database -d orcl -o open

4、确认实例情况

[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 21 15:13:57 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> col instance_name for a20
SQL> col host_name for a10
SQL> select instance_number,instance_name ,host_name from gv$instance;
INSTANCE_NUMBER INSTANCE_NAME      HOST_NAME
--------------- -------------------- ----------
       1 orcl1       rac1
       2 orcl2       rac2

5、执行catclust.sql脚本创建相关视图

SQL> @$ORACLE_HOME/rdbms/admin/catclust.sql
Package created.
Package body created.
PL/SQL procedure successfully completed.
View created.
Synonym created.
Grant succeeded.
View created.
Grant succeeded.
Synonym created.
View created.
Grant succeeded.
Synonym created.
View created.
Grant succeeded.
Synonym created.
View created.
Grant succeeded.
Synonym created.
View created.
Grant succeeded.
Synonym created.
View created.
Grant succeeded.
Synonym created.
View created.
Grant succeeded.
Synonym created.
View created.
Grant succeeded.
View created.
Grant succeeded.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
View created.
Grant succeeded.
Synonym created.
View created.
Grant succeeded.
Synonym created.
PL/SQL procedure successfully completed.

6、重建 temp表空间

6.1 手工重建temp表空间

因为在 recover 的时候不会对temp 表空间进行recover。所以recover 后，我们要手工重建temp表空间。

[grid@rac1 ~]$ asmcmd lsof |grep temp
orcl    orcl1         +data/orcl/tempfile/temp.266.941900557
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> alter tablespace temp add tempfile '+DATA' size 100M;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/orcl/tempfile/temp.266.941900557
+DATA/orcl/tempfile/temp.267.941901771

SQL> alter database tempfile '+DATA/orcl/tempfile/temp.266.941900557' offline ;
Database altered.
SQL> alter database tempfile '+DATA/orcl/tempfile/temp.266.941900557' drop including datafiles;
alter database tempfile '+DATA/orcl/tempfile/temp.266.941900557' drop including datafiles
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time
6.2 重启数据库再删除原temp表空间
[oracle@rac1 oracle]$ srvctl stop database -d orcl -o immediate
[oracle@rac1 oracle]$ srvctl start database -d orcl -o open
[oracle@rac1 oracle]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 14 14:11:13 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> alter database tempfile '+DATA/orcl/tempfile/temp.266.941900557' drop including datafiles;
Database altered.

7、配置集群监听

7.1 配置tnsnames.ora (所有节点执行)

[oracle@rac1 ~]$ su - oracle
[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@rac1 admin]$ vi tnsnames.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.154)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

7.2 启动监听

[oracle@rac1 admin]$ srvctl stop listener
[oracle@rac1 admin]$ srvctl start listener
[oracle@rac1 admin]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): rac2,rac1

7.3 用PC机测试

编辑tnsname： G:\app\oracle\product\11.2.0\client_1\network\admin\tnsnames.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.152)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.153)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

运行 cmd ：
C:\Users\Burton>sqlplus system/oracle4U@orcl
SQL*Plus: Release 11.2.0.1.0 Production on 星期五 4月 21 16:35:32 2017
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data Mining and Real Application Testing options
SQL>

8、查看在线日志情况

SQL> set pagesize 9999
SQL> col member for a50
SQL> select group#,member from v$logfile order by group#;
    GROUP# MEMBER
---------- --------------------------------------------------
  3 +DATA/orcl/onlinelog/group_3.256.941900533
  2 +DATA/orcl/onlinelog/group_2.278.941900517
  1 +DATA/orcl/onlinelog/group_1.273.941900501
  1 +FRA/orcl/onlinelog/group_1.259.941900513
  2 +FRA/orcl/onlinelog/group_2.258.941900527
  3 +FRA/orcl/onlinelog/group_3.257.941900545
  4 +DATA/orcl/onlinelog/group_4.281.941900935
  4 +FRA/orcl/onlinelog/group_4.260.941900937
  5 +DATA/orcl/onlinelog/group_5.262.941900943
  5 +FRA/orcl/onlinelog/group_5.261.941900945
  6 +DATA/orcl/onlinelog/group_6.271.941900951
  6 +FRA/orcl/onlinelog/group_6.262.941900953
12 rows selected.

五、验证数据库及集群情况

1、验证数据完整性

[oracle@rac1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 21 16:03:44 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select * from t4;
 ID NAME
---------- --------------------
  1 burton

2、查看数据库的状态

[oracle@rac1 admin]$ srvctl status database -d orcl
Instance orcl1 is running on node rac1
Instance orcl2 is running on node rac2

3、查看数据的配置情况

[root@rac2 Desktop]# srvctl config database -d orcl
Database unique name: orcl
Database name: 
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/orcl/spfileorcl.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: orcl
Database instances: orcl1,orcl2
Disk Groups: DATA,FRA
Mount point paths: 
Services: 
Type: RAC
Database is administrator managed

4、查看集群配置

[root@rac2 Desktop]# crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.FRA.dg
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.LISTENER.lsnr
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.OCRVOTE.dg
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.asm
               ONLINE  ONLINE       rac1                     Started
               ONLINE  ONLINE       rac2                     Started
ora.gsd
               OFFLINE OFFLINE      rac1
               OFFLINE OFFLINE      rac2
ora.net1.network
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.ons
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac1
ora.cvu
      1        ONLINE  ONLINE       rac1
ora.oc4j
      1        ONLINE  ONLINE       rac1
ora.rac1.vip
      1        ONLINE  ONLINE       rac1
ora.rac2.vip
      1        ONLINE  ONLINE       rac2
ora.scan1.vip
      1        ONLINE  ONLINE       rac1
ora.orcl.db
      1        ONLINE  ONLINE       rac1                     Open
      2        ONLINE  ONLINE       rac2                     Open
[root@rac2 Desktop]# olsnodes -s
rac1 Active
rac2 Active

码农公寓

一、准备环境

1、单实例配置

2、RAC配置（数据库实例未装载）

二、源数据库备份和参数文件复制到目标端

1、对源端数据库做全备

2、创建参数文件pfile

3、把备份集传到目标数据库rac1上

三、目标端数据恢复

1、配置oracle用户的环境变量（所有的节点执行,SID稍有差异）

2、在共享磁盘上创建必要的目录

2.1 查看磁盘挂载情况

2.2 共享磁盘上创建必要的目录

3、在rac1本地磁盘上创建必要的目录（保险起见所有节点执行）

4、在rac1上编辑pfile文件（根据实际情况调整内存大小）

5、恢复数据库(一)

5.1 用pfile文件创建spfile

5.2 将所有的节点上pfile文件，指向共享文件上的spfile文件

5.3 创建口令文件

5.4 还原控制文件

6、恢复数据库(二)

6.1 查看源端数据文件

6.2 目标端rac1执行：

7、源端备份后的变化数据

7.1 准备源数据全备后变化的数据

7.2 在线重做日志和新归档日志拷贝到目标库rac1

8、恢复新增数据

8.1 查看复制到目标端在线重做日志和新归档日志

8.2 把归档日志注册到rman

9、修改日志文件路径

10、打开数据库

四、配置数据库参数

1、查看数据情况

2、将其他的信息注册到CRS里

3、启动rac1和rac2实例

4、 确认实例情况

5、执行catclust.sql脚本创建相关视图

6、重建 temp表空间

6.1 手工重建temp表空间

7、配置集群监听

7.1 配置tnsnames.ora (所有节点执行)

7.2 启动监听

7.3 用PC机测试

8、查看在线日志情况

五、验证数据库及集群情况

1、验证数据完整性

2、查看数据库的状态

3、查看数据的配置情况

4、查看集群配置

相关文章

4、确认实例情况