Oracle11g RAC迁移到RAC
一、环境
1.RAC环境(源库)
| HostName | Public IP | Private IP | VIP | Scan IP | DB Name |
|---|---|---|---|---|---|
| Rac01 | 192.168.1.21 | 10.0.0.21 | 192.168.1.23 | 192.168.1.25 | mydb |
| Rac02 | 192.168.1.22 | 10.0.0.22 | 192.168.1.24 | mydb |
2.RAC环境(目标库)
目标库可以是一套尚未安装数据库的RAC，也可以是一套已经存在数据库的RAC
| HostName | Public IP | Private IP | VIP | Scan IP | DB Name |
|---|---|---|---|---|---|
| racnode01 | 192.168.1.81 | 10.0.0.81 | 192.168.1.83 | 192.168.1.85 | mydb |
| racnode02 | 192.168.1.82 | 10.0.0.82 | 192.168.1.84 | mydb |
二、RMAN备份源库
备份源库，并将备份文件传到目标库，最好位置相同
[oracle@server01 ~]$ mkdir /tmp/rmanbak
[oracle@server01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Dec 27 19:17:02 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB (DBID=2999538401)
RMAN> run{
allocate channel d1 type disk;
allocate channel d2 type disk;
backup database include current controlfile format '/tmp/rmanbak/full_%d_%s_%T_%U';
sql 'alter system archive log current';
backup archivelog all format '/tmp/rmanbak/log_%d_%s_%T_%U' delete all input;
crosscheck archivelog all;
crosscheck backup;
crosscheck copy;
delete noprompt obsolete;
delete noprompt expired backup;
delete noprompt expired archivelog all;
delete noprompt copy;
release channel d2;
release channel d1;
}
三、Copy备份至目标库
[oracle@server01 ~]$ scp -r /tmp/rmanbak 192.168.1.81:/tmp四、目标库恢复
原则上目标库只需要安装Oracle Home，然后创建pfile中所需目录即可，无需创建数据库，目标库如果已存在，则需要手工删除数据文件，参数文件、密码文件等
1.目标数据库已经存在
1.关闭目标数据库
[grid@racnode01 ~]$ srvctl stop database -d mydb
2.DBCA卸载数据库或手工删除数据
如果是DBCA卸载数据库，则需要创建spfilemydb.ora参数文件及在dbs目录下创建initmydb.ora参数文件还有orapwmydb密码文件，这里使用手工删除数据文件
[grid@racnode01 ~]$ asmcmd
ASMCMD> ls
DATA/
FRA/
OCRVOTING/
ASMCMD> cd data/mydb
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfilemydb.ora
ASMCMD> rm -rf CONTROLFILE/*
ASMCMD> rm -rf DATAFILE/*
ASMCMD> rm -rf ONLINELOG/*
ASMCMD> rm -rf TEMPFILE/*
ASMCMD> cd fra/mydb
ASMCMD> ls
CONTROLFILE/
ONLINELOG/
ASMCMD> rm -rf CONTROLFILE/*
ASMCMD> rm -rf ONLINELOG/*
温馨提示: 如果ASM目录被删除了，则需手工创建以下目录:
[grid@racnode01 ~]$ asmcmd
ASMCMD> cd DATA
ASMCMD> mkdir MYDB
ASMCMD> cd mydb
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir DATAFILE
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir PARAMETERFILE
ASMCMD> mkdir TEMPFILE
[grid@racnode01 ~]$ asmcmd
ASMCMD> ls
DATA/
FRA/
OCRVOTING/
ASMCMD> cd fra
ASMCMD> mkdir MYDB
ASMCMD> cd mydb
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir ONLINELOG
3.将目标库启动到nomount
[oracle@racnode01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 6 17:30:37 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 567869440 bytes
Fixed Size 2255272 bytes
Variable Size 285214296 bytes
Database Buffers 276824064 bytes
Redo Buffers 3575808 bytes
SQL>
4.恢复控制文件
[oracle@racnode01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 6 17:34:26 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB (not mounted)
RMAN> restore controlfile from '/tmp/rmanbak/full_MYDB_24_20220105_0o0ihev6_1_1';
Starting restore at 06-JAN-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 instance=mydb1 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/mydb/controlfile/current.263.1093282511
output file name=+FRA/mydb/controlfile/current.260.1093282511
Finished restore at 06-JAN-22
RMAN> alter database mount;
RMAN> catalog start with '/tmp/rmanbak/';
Starting implicit crosscheck backup at 06-JAN-22
allocated channel: ORA_DISK_1
Crosschecked 8 objects
Finished implicit crosscheck backup at 06-JAN-22
Starting implicit crosscheck copy at 06-JAN-22
using channel ORA_DISK_1
Finished implicit crosscheck copy at 06-JAN-22
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /tmp/rmanbak/
List of Files Unknown to the Database
=====================================
File Name: /tmp/rmanbak/full_MYDB_24_20220105_0o0ihev6_1_1
File Name: /tmp/rmanbak/full_MYDB_25_20220105_0p0ihev6_1_1
File Name: /tmp/rmanbak/log_MYDB_27_20220105_0r0ihevk_1_1
File Name: /tmp/rmanbak/log_MYDB_28_20220105_0s0ihevq_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: /tmp/rmanbak/full_MYDB_24_20220105_0o0ihev6_1_1
File Name: /tmp/rmanbak/full_MYDB_25_20220105_0p0ihev6_1_1
File Name: /tmp/rmanbak/log_MYDB_27_20220105_0r0ihevk_1_1
File Name: /tmp/rmanbak/log_MYDB_28_20220105_0s0ihevq_1_1
5.恢复数据库
查看源库数据文件位置:
SQL> set line 9999 pagesize 9999
SQL> col FILE_NAME format a60
SQL> select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile
union all
select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile
union all
select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile
union all
select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile;
FILE_TYPE FILE# FILE_NAME STATUS ENABLED
----------- ---------- ------------------------------------------------------------ ------- ----------
datafile 1 +DATA/mydb/datafile/system.259.1084542583 SYSTEM READ WRITE
datafile 2 +DATA/mydb/datafile/sysaux.260.1084542629 ONLINE READ WRITE
datafile 3 +DATA/mydb/datafile/undotbs1.261.1084542659 ONLINE READ WRITE
datafile 4 +DATA/mydb/datafile/undotbs2.263.1084542685 ONLINE READ WRITE
datafile 5 +DATA/mydb/datafile/users.264.1084542699 ONLINE READ WRITE
datafile 6 +DATA/mydb/datafile/test.268.1092418937 ONLINE READ WRITE
datafile 7 +DATA/mydb/datafile/admin.269.1093188281 ONLINE READ WRITE
tempfile 1 +DATA/mydb/tempfile/temp.262.1084542667 ONLINE READ WRITE
logfile 1 +DATA/mydb/onlinelog/group_1.257.1084542567
logfile 1 +FRA/mydb/onlinelog/group_1.257.1084542569
logfile 2 +DATA/mydb/onlinelog/group_2.258.1084542573
logfile 2 +FRA/mydb/onlinelog/group_2.258.1084542579
logfile 3 +DATA/mydb/onlinelog/group_3.265.1084548477
logfile 3 +FRA/mydb/onlinelog/group_3.259.1084548481
logfile 4 +DATA/mydb/onlinelog/group_4.266.1084548487
logfile 4 +FRA/mydb/onlinelog/group_4.260.1084548489
controlfile +DATA/mydb/controlfile/current.256.1084542565
controlfile +FRA/mydb/controlfile/current.256.1084542567
18 rows selected.
批量修改源库数据文件位置:
SQL> set line 80
SQL> set pagesize 9999
SQL> col file_name for a60
SQL> select 'set newname for datafile '||file_id||' to '''||'+DATA/mydb/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/mydb/datafile/system.259.1084542583';
set newname for datafile 2 to '+DATA/mydb/datafile/sysaux.260.1084542629';
set newname for datafile 3 to '+DATA/mydb/datafile/undotbs1.261.1084542659';
set newname for datafile 4 to '+DATA/mydb/datafile/undotbs2.263.1084542685';
set newname for datafile 5 to '+DATA/mydb/datafile/users.264.1084542699';
set newname for datafile 6 to '+DATA/mydb/datafile/test.268.1092418937';
set newname for datafile 7 to '+DATA/mydb/datafile/admin.269.1093188281';
7 rows selected.
批量修改临时文件位置:
SQL> select 'set newname for tempfile '||file_id||' to '''||'+DATA/mydb/tempfile'||substr(file_name,instr(file_name,'/',-1,1),length(file_name))||''';' from dba_temp_files;
'SETNEWNAMEFORTEMPFILE'||FILE_ID||'TO'''||'+DATA/MYDB/TEMPFILE'||SUBSTR(FILE_NAM
--------------------------------------------------------------------------------
set newname for tempfile 1 to '+DATA/mydb/tempfile/temp.262.1084542667';
恢复数据库:
RMAN> run{
allocate channel t1 device type disk;
allocate channel t2 device type disk;
set newname for datafile 1 to '+DATA/mydb/datafile/system.259.1084542583';
set newname for datafile 2 to '+DATA/mydb/datafile/sysaux.260.1084542629';
set newname for datafile 3 to '+DATA/mydb/datafile/undotbs1.261.1084542659';
set newname for datafile 4 to '+DATA/mydb/datafile/undotbs2.263.1084542685';
set newname for datafile 5 to '+DATA/mydb/datafile/users.264.1084542699';
set newname for datafile 6 to '+DATA/mydb/datafile/test.268.1092418937';
set newname for datafile 7 to '+DATA/mydb/datafile/admin.269.1093188281';
set newname for tempfile 1 to '+DATA/mydb/tempfile/temp.262.1084542667';
restore database;
switch datafile all;
recover database;
release channel t1;
release channel t2;
}
RMAN> alter database open resetlogs;
database opened
增量恢复:
如果源库有新增数据需要恢复至目标库，则需要在open resetlogs前使用以下步骤恢复，复制归档日志至目标库:
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> col name for a70
SQL> select sequence#,thread#,name,first_time,next_time,archived,applied,status from v$archived_log where name is not null;
SEQUENCE# THREAD# NAME FIRST_TIME NEXT_TIME ARC APPLIED S
---------- ---------- ---------------------------------------------------------------------- ------------------- ------------------- --- --------- -
103 1 +FRA/mydb/archivelog/2022_01_05/thread_1_seq_103.291.1093198059 2022-01-05 15:29:54 2022-01-05 18:07:39 YES NO A
37 2 +FRA/mydb/archivelog/2022_01_05/thread_2_seq_37.292.1093198063 2022-01-05 15:29:51 2022-01-05 18:07:42 YES NO A
104 1 +FRA/mydb/archivelog/2022_01_05/thread_1_seq_104.289.1093198125 2022-01-05 18:07:39 2022-01-05 18:08:44 YES NO A
38 2 +FRA/mydb/archivelog/2022_01_05/thread_2_seq_38.290.1093198125 2022-01-05 18:07:42 2022-01-05 18:08:45 YES NO A
SQL> set linesize 200 pagesize 200
SQL> col name for a80
SQL> select 'copy archivelog '''||name||''' to '''||'/tmp/rmanbak'||substr(name,instr(name,'/',-1,1),length(name))||''';' from v$archived_log where name is not null;
'COPYARCHIVELOG'''||NAME||'''TO'''||'/TMP/RMANBAK'||SUBSTR(NAME,INSTR(NAME,'/',-1,1),LENGTH(NAME))||''';'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
copy archivelog '+FRA/mydb/archivelog/2022_01_05/thread_1_seq_103.291.1093198059' to '/tmp/rmanbak/thread_1_seq_103.291.1093198059';
copy archivelog '+FRA/mydb/archivelog/2022_01_05/thread_2_seq_37.292.1093198063' to '/tmp/rmanbak/thread_2_seq_37.292.1093198063';
copy archivelog '+FRA/mydb/archivelog/2022_01_05/thread_1_seq_104.289.1093198125' to '/tmp/rmanbak/thread_1_seq_104.289.1093198125';
copy archivelog '+FRA/mydb/archivelog/2022_01_05/thread_2_seq_38.290.1093198125' to '/tmp/rmanbak/thread_2_seq_38.290.1093198125';
[oracle@server01 ~]$ cd /tmp/rmanbak
[oracle@server01 rmanbak]$ ll
total 1287080
-rw-r----- 1 oracle asmadmin 499089408 Jan 5 15:29 full_MYDB_22_20220105_0m0iheu3_1_1
-rw-r----- 1 oracle asmadmin 690634752 Jan 5 15:29 full_MYDB_23_20220105_0n0iheu3_1_1
-rw-r----- 1 oracle asmadmin 18644992 Jan 5 15:29 full_MYDB_24_20220105_0o0ihev6_1_1
-rw-r----- 1 oracle asmadmin 98304 Jan 5 15:29 full_MYDB_25_20220105_0p0ihev6_1_1
-rw-r----- 1 oracle asmadmin 79067648 Jan 5 15:30 log_MYDB_27_20220105_0r0ihevk_1_1
-rw-r----- 1 oracle asmadmin 1973760 Jan 5 15:30 log_MYDB_28_20220105_0s0ihevq_1_1
-rw-r----- 1 oracle asmadmin 5776384 Jan 5 18:49 thread_1_seq_103.291.1093198059
-rw-r----- 1 oracle asmadmin 5120 Jan 5 18:49 thread_1_seq_104.289.1093198125
-rw-r----- 1 oracle asmadmin 22665216 Jan 5 18:49 thread_2_seq_37.292.1093198063
-rw-r----- 1 oracle asmadmin 1536 Jan 5 18:49 thread_2_seq_38.290.1093198125
[oracle@server01 rmanbak]$ scp thread* 192.168.1.81:/tmp/rmanbak/
oracle@192.168.1.81's password:
thread_1_seq_103.291.1093198059 100% 5641KB 34.2MB/s 00:00
thread_1_seq_104.289.1093198125 100% 5120 5.2MB/s 00:00
thread_2_seq_37.292.1093198063 100% 22MB 23.0MB/s 00:00
thread_2_seq_38.290.1093198125 100% 1536 1.2MB/s 00:00
[oracle@server01 rmanbak]$
目标库恢复:
[oracle@racnode01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 6 18:54:49 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB (DBID=2999538401, not open)
RMAN> catalog start with '/tmp/rmanbak/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/rmanbak/
List of Files Unknown to the Database
=====================================
File Name: /tmp/rmanbak/thread_1_seq_103.291.1093198059
File Name: /tmp/rmanbak/thread_1_seq_104.289.1093198125
File Name: /tmp/rmanbak/thread_2_seq_37.292.1093198063
File Name: /tmp/rmanbak/thread_2_seq_38.290.1093198125
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/rmanbak/thread_1_seq_103.291.1093198059
File Name: /tmp/rmanbak/thread_1_seq_104.289.1093198125
File Name: /tmp/rmanbak/thread_2_seq_37.292.1093198063
File Name: /tmp/rmanbak/thread_2_seq_38.290.1093198125
----注意until sequence是不包含这个sequence，如果需要恢复到这个sequence数据，需要+1，另外RAC需要指定两个线程
RMAN> run {
set until sequence 105 thread 1;
set until sequence 39 thread 2;
recover database;
}
executing command: SET until clause
executing command: SET until clause
Starting recover at 07-JAN-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 instance=mydb1 device type=DISK
starting media recovery
archived log for thread 1 with sequence 101 is already on disk as file +FRA/mydb/archivelog/2022_01_06/thread_1_seq_101.256.1093297125
archived log for thread 1 with sequence 102 is already on disk as file +FRA/mydb/archivelog/2022_01_06/thread_1_seq_102.257.1093297125
archived log for thread 1 with sequence 103 is already on disk as file /tmp/rmanbak/thread_1_seq_103.291.1093198059
archived log for thread 1 with sequence 104 is already on disk as file /tmp/rmanbak/thread_1_seq_104.289.1093198125
archived log for thread 2 with sequence 35 is already on disk as file +FRA/mydb/archivelog/2022_01_06/thread_2_seq_35.265.1093297125
archived log for thread 2 with sequence 36 is already on disk as file +FRA/mydb/archivelog/2022_01_06/thread_2_seq_36.258.1093297125
archived log for thread 2 with sequence 37 is already on disk as file /tmp/rmanbak/thread_2_seq_37.292.1093198063
archived log for thread 2 with sequence 38 is already on disk as file /tmp/rmanbak/thread_2_seq_38.290.1093198125
archived log for thread 1 with sequence 1 is already on disk as file +FRA/mydb/archivelog/2022_01_07/thread_1_seq_1.273.1093355897
archived log for thread 2 with sequence 1 is already on disk as file +FRA/mydb/archivelog/2022_01_07/thread_2_seq_1.272.1093354219
archived log file name=+FRA/mydb/archivelog/2022_01_06/thread_1_seq_101.256.1093297125 thread=1 sequence=101
archived log file name=+FRA/mydb/archivelog/2022_01_06/thread_2_seq_35.265.1093297125 thread=2 sequence=35
archived log file name=+FRA/mydb/archivelog/2022_01_06/thread_1_seq_102.257.1093297125 thread=1 sequence=102
archived log file name=+FRA/mydb/archivelog/2022_01_06/thread_2_seq_36.258.1093297125 thread=2 sequence=36
archived log file name=/tmp/rmanbak/thread_2_seq_37.292.1093198063 thread=2 sequence=37
archived log file name=/tmp/rmanbak/thread_1_seq_103.291.1093198059 thread=1 sequence=103
archived log file name=/tmp/rmanbak/thread_1_seq_104.289.1093198125 thread=1 sequence=104
archived log file name=/tmp/rmanbak/thread_2_seq_38.290.1093198125 thread=2 sequence=38
unable to find archived log
archived log thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/07/2022 14:36:32
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 1713064
RMAN> alter database open resetlogs;
using target database control file instead of recovery catalog
database opened2.目标数据库没有安装
假定目标库只安装了Oracle Home，没有DBCA创建数据库
1.查看当前RAC状态
[grid@racnode01 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE racnode01
ONLINE ONLINE racnode02
ora.OCRVOTING.dg
ONLINE ONLINE racnode01
ONLINE ONLINE racnode02
ora.asm
ONLINE ONLINE racnode01 Started
ONLINE ONLINE racnode02 Started
ora.gsd
OFFLINE OFFLINE racnode01
OFFLINE OFFLINE racnode02
ora.net1.network
ONLINE ONLINE racnode01
ONLINE ONLINE racnode02
ora.ons
ONLINE ONLINE racnode01
ONLINE ONLINE racnode02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE racnode01
ora.cvu
1 ONLINE ONLINE racnode01
ora.oc4j
1 ONLINE ONLINE racnode01
ora.racnode01.vip
1 ONLINE ONLINE racnode01
ora.racnode02.vip
1 ONLINE ONLINE racnode02
ora.scan1.vip
1 ONLINE ONLINE racnode01
2.修改权限
因为没有使用DBCA建库，所以需要修改oracle或grid用户$ORACLE_HOME/bin目录下的oracle权限，否则创建asm磁盘或RMAN恢复会权限不足:
[root@racnode01 ~]# cd /u01/app/oracle/product/11.2.0/db_1/bin/
[root@racnode01 bin]# ll -d oracle
-rwsr-s--x 1 oracle oinstall 239501488 Jan 7 18:48 oracle
[root@racnode01 bin]# chown oracle.asmadmin oracle
[root@racnode01 bin]# chmod 6751 oracle
[root@racnode01 bin]# ll -d oracle
-rwsr-s--x 1 oracle asmadmin 239501488 Jan 7 18:52 oracle
[root@racnode02 ~]# cd /u01/app/oracle/product/11.2.0/db_1/bin/
[root@racnode02 bin]# ll -d oracle
-rwsr-s--x 1 oracle oinstall 239501488 Jan 7 18:52 oracle
[root@racnode02 bin]# chown oracle.asmadmin oracle
[root@racnode02 bin]# chmod 6751 oracle
[root@racnode02 bin]# ll -d oracle
-rwsr-s--x 1 oracle asmadmin 239501488 Jan 7 18:52 oracle
[root@server01 ~]# cd /opt/app/11.2.0/grid/bin/
[root@server01 bin]# ll -d oracle
-rwsr-s--x. 1 grid oinstall 209836184 Sep 29 11:38 oracle
[root@racnode02 ~]# cd /u01/app/11.2.0/grid/bin
[root@racnode02 bin]# ll -d oracle
-rwsr-s--x 1 grid oinstall 209840344 Jan 10 11:44 oracle
温馨提示:如果不修改权限，oracle用户就无法读取ASM磁盘，RMAN恢复的时候会提示如下报错
3.创建ASM磁盘组
netca创建磁盘组或手工创建磁盘组:
[grid@racnode01 ~]$ sqlplus / as sysasm
SQL> set linesize 200 pagesize 200
SQL> select group_number gno,name,state,type,total_mb,free_mb,required_mirror_free_mb rmfmb,usable_file_mb ufmb from v$asm_diskgroup;
GNO NAME STATE TYPE TOTAL_MB FREE_MB RMFMB UFMB
---------- ------------------------------ ----------- ------ ---------- ---------- ---------- ----------
1 OCRVOTING MOUNTED NORMAL 3072 2032 1024 504
SQL> set linesize 200 pagesize 200
SQL> col path for a20
SQL> col name for a15
SQL> col failgroup for a15
SQL> select group_number,disk_number,path,failgroup,name,state,mount_status,total_mb,free_mb,os_mb from v$asm_disk;
GROUP_NUMBER DISK_NUMBER PATH FAILGROUP NAME STATE MOUNT_S TOTAL_MB FREE_MB OS_MB
------------ ----------- -------------------- --------------- --------------- -------- ------- ---------- ---------- ----------
0 0 /dev/asm-diskg NORMAL CLOSED 0 0 10240
0 1 /dev/asm-diskh NORMAL CLOSED 0 0 10240
0 2 /dev/asm-diskf NORMAL CLOSED 0 0 4096
0 3 /dev/asm-diske NORMAL CLOSED 0 0 4096
1 2 /dev/asm-diskd OCRVOTING_0002 OCRVOTING_0002 NORMAL CACHED 1024 680 1024
1 1 /dev/asm-diskc OCRVOTING_0001 OCRVOTING_0001 NORMAL CACHED 1024 676 1024
1 0 /dev/asm-diskb OCRVOTING_0000 OCRVOTING_0000 NORMAL CACHED 1024 676 1024
7 rows selected.
SQL> show parameter asm_diskstring
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
asm_diskstring string /dev/asm*
SQL> col name for a40
SQL> col value for a15
SQL> select group_number,name,value from v$asm_attribute where name in ('au_size','compatible.asm','compatible.rdbms');
GROUP_NUMBER NAME VALUE
------------ ---------------------------------------- ---------------
1 au_size 4194304
1 compatible.asm 11.2.0.0.0
1 compatible.rdbms 10.1.0.0.0
SQL> create diskgroup FRA external redundancy
disk
'/dev/asm-diske' name FRA_0000,
'/dev/asm-diskf' name FRA_0001
attribute 'au_size'='4M',
'compatible.asm' = '11.2',
'compatible.rdbms' = '10.1';
Diskgroup created.
SQL> create diskgroup DATA external redundancy
disk
'/dev/asm-diskg' name DATA_0000,
'/dev/asm-diskh' name DATA_0001
attribute 'au_size'='4M',
'compatible.asm' = '11.2',
'compatible.rdbms' = '10.1';
Diskgroup created.
4.创建RAC所需目录
[oracle@racnode01 ~]$ mkdir -p /u01/app/oracle/admin/mydb/adump
[oracle@racnode02 ~]$ mkdir -p /u01/app/oracle/admin/mydb/adump
[grid@racnode01 ~]$ asmcmd
ASMCMD> ls
DATA/
FRA/
OCRVOTING/
ASMCMD> mkdir +DATA/MYDB
ASMCMD> cd +DATA/MYDB
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir DATAFILE
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir TEMPFILE
ASMCMD> mkdir PARAMETERFILE
ASMCMD> mkdir +FRA/MYDB
ASMCMD> cd +FRA/MYDB
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir ONLINELOG
5.创建口令文件
两个节点分别创建密码文件:
[oracle@racnode01 ~]$ cd $ORACLE_HOME/dbs
[oracle@racnode01 dbs]$ orapwd file=orapwmydb password=oracle entries=5
[oracle@racnode02 ~]$ cd $ORACLE_HOME/dbs
[oracle@racnode02 dbs]$ orapwd file=orapwmydb password=oracle entries=5
6.源库创建pfile
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/mydb/spfilemydb.ora
SQL> create pfile='/tmp/pfile.ora' from spfile;
File created.
7.传输pfile至目标库
[oracle@server01 tmp]$ scp pfile.ora 192.168.1.81:/tmp
8.目标库创建pfile
按目标库目录及配置修改/tmp/pfile.ora，并创建spfilemydb.ora
[root@racnode01 tmp]# more pfile.ora
mydb1.__db_cache_size=339738624
mydb2.__db_cache_size=339738624
mydb1.__java_pool_size=4194304
mydb2.__java_pool_size=4194304
mydb1.__large_pool_size=8388608
mydb2.__large_pool_size=8388608
mydb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
mydb1.__pga_aggregate_target=192937984
mydb2.__pga_aggregate_target=192937984
mydb1.__sga_target=570425344
mydb2.__sga_target=570425344
mydb1.__shared_io_pool_size=0
mydb2.__shared_io_pool_size=0
mydb1.__shared_pool_size=209715200
mydb2.__shared_pool_size=209715200
mydb1.__streams_pool_size=0
mydb2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/mydb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/mydb/controlfile/current.256.1084542565','+FRA/mydb/controlfile/current.256.1084542567'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='mydb'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=6005194752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mydbXDB)'
mydb1.instance_number=1
mydb2.instance_number=2
*.log_archive_dest_1='location=+FRA/'
*.open_cursors=300
*.pga_aggregate_target=189792256
*.processes=150
*.remote_listener='rac11g-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=570425344
mydb2.thread=2
mydb1.thread=1
mydb2.undo_tablespace='UNDOTBS2'
mydb1.undo_tablespace='UNDOTBS1'
[oracle@racnode01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 10 13:45:20 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile='+DATA/MYDB/spfilemydb.ora' from pfile='/tmp/pfile.ora';
File created.
9.创建initmydb.ora文件
节点1：
[oracle@racnode01 dbs]$ echo "SPFILE='+DATA/mydb/spfilemydb.ora' " > /u01/app/oracle/product/11.2.0/db_1/dbs/initmydb1.ora
[oracle@racnode01 dbs]$ cat //u01/app/oracle/product/11.2.0/db_1/dbs/initmydb1.ora
SPFILE='+DATA/mydb/spfilemydb.ora'
节点2：
[oracle@racnode02 dbs]$ echo "SPFILE='+DATA/mydb/spfilemydb.ora' " > /u01/app/oracle/product/11.2.0/db_1/dbs/initmydb2.ora
[oracle@racnode02 dbs]$ cat //u01/app/oracle/product/11.2.0/db_1/dbs/initmydb2.ora
SPFILE='+DATA/mydb/spfilemydb.ora'
10.启动nomount
SQL> startup nomount
ORACLE instance started.
Total System Global Area 567869440 bytes
Fixed Size 2255272 bytes
Variable Size 285214296 bytes
Database Buffers 276824064 bytes
Redo Buffers 3575808 bytes
11.目标库恢复控制文件
[oracle@racnode01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 7 20:56:31 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB (not mounted)
RMAN> restore controlfile from '/tmp/rmanbak/full_MYDB_44_20220109_1c0irrom_1_1';
Starting restore at 10-JAN-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 instance=mydb1 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/mydb/controlfile/current.257.1093616031
output file name=+FRA/mydb/controlfile/current.256.1093616033
Finished restore at 10-JAN-22
12.启动mount
RMAN> alter database mount;
13.恢复数据库
查看源库数据文件位置:
SQL> set line 9999 pagesize 9999
SQL> col FILE_NAME format a60
SQL> select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile
union all
select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile
union all
select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile
union all
select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile;
FILE_TYPE FILE# FILE_NAME STATUS ENABLED
----------- ---------- ------------------------------------------------------------ ------- ----------
datafile 1 +DATA/mydb/datafile/system.259.1084542583 SYSTEM READ WRITE
datafile 2 +DATA/mydb/datafile/sysaux.260.1084542629 ONLINE READ WRITE
datafile 3 +DATA/mydb/datafile/undotbs1.261.1084542659 ONLINE READ WRITE
datafile 4 +DATA/mydb/datafile/undotbs2.263.1084542685 ONLINE READ WRITE
datafile 5 +DATA/mydb/datafile/users.264.1084542699 ONLINE READ WRITE
datafile 6 +DATA/mydb/datafile/test.268.1092418937 ONLINE READ WRITE
datafile 7 +DATA/mydb/datafile/admin.269.1093188281 ONLINE READ WRITE
tempfile 1 +DATA/mydb/tempfile/temp.262.1084542667 ONLINE READ WRITE
logfile 1 +DATA/mydb/onlinelog/group_1.257.1084542567
logfile 1 +FRA/mydb/onlinelog/group_1.257.1084542569
logfile 2 +DATA/mydb/onlinelog/group_2.258.1084542573
logfile 2 +FRA/mydb/onlinelog/group_2.258.1084542579
logfile 3 +DATA/mydb/onlinelog/group_3.265.1084548477
logfile 3 +FRA/mydb/onlinelog/group_3.259.1084548481
logfile 4 +DATA/mydb/onlinelog/group_4.266.1084548487
logfile 4 +FRA/mydb/onlinelog/group_4.260.1084548489
controlfile +DATA/mydb/controlfile/current.256.1084542565
controlfile +FRA/mydb/controlfile/current.256.1084542567
18 rows selected.
批量修改源库数据文件位置:
SQL> set line 80
SQL> set pagesize 9999
SQL> col file_name for a60
SQL> select 'set newname for datafile '||file_id||' to '''||'+DATA/mydb/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/mydb/datafile/system.259.1084542583';
set newname for datafile 2 to '+DATA/mydb/datafile/sysaux.260.1084542629';
set newname for datafile 3 to '+DATA/mydb/datafile/undotbs1.261.1084542659';
set newname for datafile 4 to '+DATA/mydb/datafile/undotbs2.263.1084542685';
set newname for datafile 5 to '+DATA/mydb/datafile/users.264.1084542699';
set newname for datafile 6 to '+DATA/mydb/datafile/test.268.1092418937';
set newname for datafile 7 to '+DATA/mydb/datafile/admin.269.1093188281';
7 rows selected.
临时文件位置:
SQL> select 'set newname for tempfile '||file_id||' to '''||'+DATA/mydb/tempfile'||substr(file_name,instr(file_name,'/',-1,1),length(file_name))||''';' from dba_temp_files;
'SETNEWNAMEFORTEMPFILE'||FILE_ID||'TO'''||'+DATA/MYDB/TEMPFILE'||SUBSTR(FILE_NAM
--------------------------------------------------------------------------------
set newname for tempfile 1 to '+DATA/mydb/tempfile/temp.262.1084542667';
开始恢复:
RMAN> catalog start with '/tmp/rmanbak/';
RMAN> run{
allocate channel t1 device type disk;
allocate channel t2 device type disk;
set newname for datafile 1 to '+DATA/mydb/datafile/system.259.1084542583';
set newname for datafile 2 to '+DATA/mydb/datafile/sysaux.260.1084542629';
set newname for datafile 3 to '+DATA/mydb/datafile/undotbs1.261.1084542659';
set newname for datafile 4 to '+DATA/mydb/datafile/undotbs2.263.1084542685';
set newname for datafile 5 to '+DATA/mydb/datafile/users.264.1084542699';
set newname for datafile 6 to '+DATA/mydb/datafile/test.268.1092418937';
set newname for datafile 7 to '+DATA/mydb/datafile/admin.269.1093188281';
set newname for tempfile 1 to '+DATA/mydb/tempfile/temp.262.1084542667';
restore database;
switch datafile all;
recover database;
release channel t1;
release channel t2;
}
RMAN> alter database open resetlogs;
database opened
增量恢复:
如果源库有新增数据需要恢复至目标库，则需要在open resetlogs前使用以下步骤恢复，复制归档日志至目标库:
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> col name for a70
SQL> select sequence#,thread#,name,first_time,next_time,archived,applied,status from v$archived_log where name is not null;
SEQUENCE# THREAD# NAME FIRST_TIME NEXT_TIME ARC APPLIED S
---------- ---------- ---------------------------------------------------------------------- ------------------- ------------------- --- --------- -
103 1 +FRA/mydb/archivelog/2022_01_05/thread_1_seq_103.291.1093198059 2022-01-05 15:29:54 2022-01-05 18:07:39 YES NO A
37 2 +FRA/mydb/archivelog/2022_01_05/thread_2_seq_37.292.1093198063 2022-01-05 15:29:51 2022-01-05 18:07:42 YES NO A
104 1 +FRA/mydb/archivelog/2022_01_05/thread_1_seq_104.289.1093198125 2022-01-05 18:07:39 2022-01-05 18:08:44 YES NO A
38 2 +FRA/mydb/archivelog/2022_01_05/thread_2_seq_38.290.1093198125 2022-01-05 18:07:42 2022-01-05 18:08:45 YES NO A
SQL> set linesize 200 pagesize 200
SQL> col name for a80
SQL> select 'copy archivelog '''||name||''' to '''||'/tmp/rmanbak'||substr(name,instr(name,'/',-1,1),length(name))||''';' from v$archived_log where name is not null;
'COPYARCHIVELOG'''||NAME||'''TO'''||'/TMP/RMANBAK'||SUBSTR(NAME,INSTR(NAME,'/',-1,1),LENGTH(NAME))||''';'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
copy archivelog '+FRA/mydb/archivelog/2022_01_05/thread_1_seq_103.291.1093198059' to '/tmp/rmanbak/thread_1_seq_103.291.1093198059';
copy archivelog '+FRA/mydb/archivelog/2022_01_05/thread_2_seq_37.292.1093198063' to '/tmp/rmanbak/thread_2_seq_37.292.1093198063';
copy archivelog '+FRA/mydb/archivelog/2022_01_05/thread_1_seq_104.289.1093198125' to '/tmp/rmanbak/thread_1_seq_104.289.1093198125';
copy archivelog '+FRA/mydb/archivelog/2022_01_05/thread_2_seq_38.290.1093198125' to '/tmp/rmanbak/thread_2_seq_38.290.1093198125';
[oracle@server01 ~]$ cd /tmp/rmanbak
[oracle@server01 rmanbak]$ ll
total 1287080
-rw-r----- 1 oracle asmadmin 499089408 Jan 5 15:29 full_MYDB_22_20220105_0m0iheu3_1_1
-rw-r----- 1 oracle asmadmin 690634752 Jan 5 15:29 full_MYDB_23_20220105_0n0iheu3_1_1
-rw-r----- 1 oracle asmadmin 18644992 Jan 5 15:29 full_MYDB_24_20220105_0o0ihev6_1_1
-rw-r----- 1 oracle asmadmin 98304 Jan 5 15:29 full_MYDB_25_20220105_0p0ihev6_1_1
-rw-r----- 1 oracle asmadmin 79067648 Jan 5 15:30 log_MYDB_27_20220105_0r0ihevk_1_1
-rw-r----- 1 oracle asmadmin 1973760 Jan 5 15:30 log_MYDB_28_20220105_0s0ihevq_1_1
-rw-r----- 1 oracle asmadmin 5776384 Jan 5 18:49 thread_1_seq_103.291.1093198059
-rw-r----- 1 oracle asmadmin 5120 Jan 5 18:49 thread_1_seq_104.289.1093198125
-rw-r----- 1 oracle asmadmin 22665216 Jan 5 18:49 thread_2_seq_37.292.1093198063
-rw-r----- 1 oracle asmadmin 1536 Jan 5 18:49 thread_2_seq_38.290.1093198125
[oracle@server01 rmanbak]$ scp thread* 192.168.1.81:/tmp/rmanbak/
oracle@192.168.1.81's password:
thread_1_seq_103.291.1093198059 100% 5641KB 34.2MB/s 00:00
thread_1_seq_104.289.1093198125 100% 5120 5.2MB/s 00:00
thread_2_seq_37.292.1093198063 100% 22MB 23.0MB/s 00:00
thread_2_seq_38.290.1093198125 100% 1536 1.2MB/s 00:00
[oracle@server01 rmanbak]$
目标库恢复:
[oracle@racnode01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 6 18:54:49 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB (DBID=2999538401, not open)
RMAN> catalog start with '/tmp/rmanbak/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/rmanbak/
List of Files Unknown to the Database
=====================================
File Name: /tmp/rmanbak/thread_1_seq_103.291.1093198059
File Name: /tmp/rmanbak/thread_1_seq_104.289.1093198125
File Name: /tmp/rmanbak/thread_2_seq_37.292.1093198063
File Name: /tmp/rmanbak/thread_2_seq_38.290.1093198125
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/rmanbak/thread_1_seq_103.291.1093198059
File Name: /tmp/rmanbak/thread_1_seq_104.289.1093198125
File Name: /tmp/rmanbak/thread_2_seq_37.292.1093198063
File Name: /tmp/rmanbak/thread_2_seq_38.290.1093198125
[oracle@racnode01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 6 21:38:36 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB (DBID=2999538401, not open)
RMAN> recover database;
RMAN> alter database open resetlogs;
温馨提示: 如果需要指定恢复到某个日志，可以使用以下命令恢复:
----注意until sequence是不包含这个sequence，如果需要恢复到这个sequence数据，需要+1，另外RAC需要指定两个线程
RMAN> run {
set until sequence 105 thread 1;
set until sequence 39 thread 2;
recover database;
}
RMAN> alter database open resetlogs;
14.注册DB
恢复后，DB信息还没有注册进集群，需要使用以下命令注册:
[grid@racnode01 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE racnode01
OFFLINE OFFLINE racnode02
ora.FRA.dg
ONLINE ONLINE racnode01
OFFLINE OFFLINE racnode02
ora.LISTENER.lsnr
ONLINE ONLINE racnode01
ONLINE ONLINE racnode02
ora.OCRVOTING.dg
ONLINE ONLINE racnode01
ONLINE ONLINE racnode02
ora.asm
ONLINE ONLINE racnode01 Started
ONLINE ONLINE racnode02 Started
ora.gsd
OFFLINE OFFLINE racnode01
OFFLINE OFFLINE racnode02
ora.net1.network
ONLINE ONLINE racnode01
ONLINE ONLINE racnode02
ora.ons
ONLINE ONLINE racnode01
ONLINE ONLINE racnode02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE racnode01
ora.cvu
1 ONLINE ONLINE racnode01
ora.oc4j
1 ONLINE ONLINE racnode01
ora.racnode01.vip
1 ONLINE ONLINE racnode01
ora.racnode02.vip
1 ONLINE ONLINE racnode02
ora.scan1.vip
1 ONLINE ONLINE racnode01
注册:
[oracle@racnode01 ~]$ srvctl add database -d mydb -o $ORACLE_HOME
[oracle@racnode01 ~]$ srvctl add instance -d mydb -i mydb1 -n racnode01
[oracle@racnode01 ~]$ srvctl add instance -d mydb -i mydb2 -n racnode02
[grid@racnode01 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE racnode01
OFFLINE OFFLINE racnode02
ora.FRA.dg
ONLINE ONLINE racnode01
OFFLINE OFFLINE racnode02
ora.LISTENER.lsnr
ONLINE ONLINE racnode01
ONLINE ONLINE racnode02
ora.OCRVOTING.dg
ONLINE ONLINE racnode01
ONLINE ONLINE racnode02
ora.asm
ONLINE ONLINE racnode01 Started
ONLINE ONLINE racnode02 Started
ora.gsd
OFFLINE OFFLINE racnode01
OFFLINE OFFLINE racnode02
ora.net1.network
ONLINE ONLINE racnode01
ONLINE ONLINE racnode02
ora.ons
ONLINE ONLINE racnode01
ONLINE ONLINE racnode02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE racnode01
ora.cvu
1 ONLINE ONLINE racnode01
ora.mydb.db
1 OFFLINE OFFLINE
2 OFFLINE OFFLINE
ora.oc4j
1 ONLINE ONLINE racnode01
ora.racnode01.vip
1 ONLINE ONLINE racnode01
ora.racnode02.vip
1 ONLINE ONLINE racnode02
ora.scan1.vip
1 ONLINE ONLINE racnode01
[grid@racnode01 ~]$
15.设置DB开机自启动:
[root@racnode01 ~]# /u01/app/11.2.0/grid/bin/crsctl modify resource "ora.mydb.db" -attr "AUTO_START=always"
五、错误处理
1.ORA-00349: failure obtaining block size for '+dat
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 567869440 bytes
Fixed Size 2255272 bytes
Variable Size 222299736 bytes
Database Buffers 335544320 bytes
Redo Buffers 7770112 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORA11G" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+data', '/opt/Ora11gData/ora11g/group_1.257.1084542569' ) SIZE 50 M REUSE,
GROUP 2 ( '+data', '/opt/Ora11gData/ora11g/group_2.258.1084542579' ) SIZE 50 M REUSE
DATAFILE
'/opt/Ora11gData/ora11g/system.259.1084542583'
CHARACTER SET AL32UTF8
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/27/2021 20:57:30
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-00349: failure obtaining block size for '+dat
解决办法:
SQL> set linesize 200 pagesize 200
SQL> col member format a60
SQL> select v1.thread#, v1.group#, member, sequence#, first_change#, archived, v1.status from v$log v1, v$logfile v2 where v1.group#=v2.group#;
THREAD# GROUP# MEMBER SEQUENCE# FIRST_CHANGE# ARC STATUS
---------- ---------- ------------------------------------------------------------ ---------- ------------- --- ----------------
1 1 +DATA/mydb/onlinelog/group_1.257.1084542567 93 1464214 NO CURRENT
1 1 +FRA/mydb/onlinelog/group_1.257.1084542569 93 1464214 NO CURRENT
1 2 +DATA/mydb/onlinelog/group_2.258.1084542573 92 1464120 YES INACTIVE
1 2 +FRA/mydb/onlinelog/group_2.258.1084542579 92 1464120 YES INACTIVE
2 3 +DATA/mydb/onlinelog/group_3.265.1084548477 25 1464125 YES INACTIVE
2 3 +FRA/mydb/onlinelog/group_3.259.1084548481 25 1464125 YES INACTIVE
2 4 +DATA/mydb/onlinelog/group_4.266.1084548487 26 1464476 NO CURRENT
2 4 +FRA/mydb/onlinelog/group_4.260.1084548489 26 1464476 NO CURRENT
8 rows selected.
#*.log_file_name_convert=('+DATA/mydb/onlinelog','/opt/Ora11gData/ora11g')
#*.db_file_name_convert=('+DATA/mydb/datafile','/opt/Ora11gData/ora11g')
#*.db_file_name_convert=('+DATA/mydb/tempfile','/opt/Ora11gData/ora11g')
#*.log_file_name_convert=('+FRA/mydb/onlinelog','/opt/Ora11gData/ora11g')
log_file_name_convert或db_file_name_convert有多条记录不能分开写，因此上面这种写法是错误的，需要写在同一行上，如下所示:
*.log_file_name_convert=('+DATA/mydb/onlinelog','/opt/Ora11gData/ora11g','+FRA/mydb/onlinelog','/opt/Ora11gData/ora11g')
*.db_file_name_convert=('+DATA/mydb/datafile','/opt/Ora11gData/ora11g','+DATA/mydb/tempfile','/opt/Ora11gData/ora11g')
2.RMAN-06025: no backup of archived log for thread 2
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 4 was not restored from a sufficiently old backup
ORA-01110: data file 4: '/opt/Ora11gData/ora11g/undotbs2.263.1084542685'
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/28/2021 11:43:05
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 2 with sequence 20 and starting SCN of 1371415 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 89 and starting SCN of 1396907 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 88 and starting SCN of 1371411 found to restore
解决办法:
[oracle@server01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 28 12:11:32 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB (DBID=2999538401)
RMAN> copy archivelog '+FRA/mydb/archivelog/2021_12_28/thread_1_seq_88.265.1092482977' to '/tmp/rman/thread_1_seq_88.265.1092482977';
Starting backup at 28-DEC-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=55 instance=mydb1 device type=DISK
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=88 RECID=8 STAMP=1092482977
output file name=/tmp/rman/thread_1_seq_88.265.1092482977 RECID=10 STAMP=1092485497
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 28-DEC-21
RMAN> copy archivelog '+FRA/mydb/archivelog/2021_12_28/thread_1_seq_89.263.1092482977' to '/tmp/rman/thread_1_seq_89.263.1092482977';
Starting backup at 28-DEC-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 instance=mydb1 device type=DISK
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=89 RECID=9 STAMP=1092482977
output file name=/tmp/rman/thread_1_seq_89.263.1092482977 RECID=11 STAMP=1092485686
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 28-DEC-21
RMAN> copy archivelog '+FRA/mydb/archivelog/2021_12_28/thread_2_seq_20.266.1092482975' to '/tmp/rman/thread_2_seq_20.266.1092482975';
Starting backup at 28-DEC-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=20 RECID=7 STAMP=1092482976
output file name=/tmp/rman/thread_2_seq_20.266.1092482975 RECID=12 STAMP=1092485715
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 28-DEC-21
[oracle@server01 ~]$ scp -r /tmp/rman/thread* 192.168.1.112:/tmp/rman
oracle@192.168.1.112's password:
thread_1_seq_88.265.1092482977 100% 3390KB 28.4MB/s 00:00
thread_1_seq_89.263.1092482977 100% 1024 483.0KB/s 00:00
thread_2_seq_20.266.1092482975 100% 1885KB 23.4MB/s 00:00
[oracle@server ora11g]$ rman target sys/oracle@rac_mydb auxiliary /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 28 12:20:45 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB (DBID=2999538401)
connected to auxiliary database: ORA11G (not mounted)
RMAN> duplicate target database to ora11g;
3.ORA-01180: can not create datafile 1
RMAN> run{
allocate channel t1 device type disk;
allocate channel t2 device type disk;
set newname for datafile 1 to '/opt/app/oracle/oradata/mydb/system.259.1084542583';
set newname for datafile 2 to '/opt/app/oracle/oradata/mydb/sysaux.260.1084542629';
set newname for datafile 3 to '/opt/app/oracle/oradata/mydb/undotbs1.261.1084542659';
set newname for datafile 4 to '/opt/app/oracle/oradata/mydb/undotbs2.263.1084542685';
set newname for datafile 5 to '/opt/app/oracle/oradata/mydb/users.264.1084542699';
set newname for datafile 6 to '/opt/app/oracle/oradata/mydb/test.268.1092418937';
restore database;
switch datafile all;
recover database;
release channel t1;
release channel t2;
}
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=18 device type=DISK
allocated channel: t2
channel t2: SID=20 device type=DISK
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 31-DEC-21
creating datafile file number=1 name=/opt/app/oracle/oradata/mydb/system.259.1084542583
released channel: t1
released channel: t2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/31/2021 17:30:10
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '+DATA/mydb/datafile/system.259.1084542583'
解决办法:
[oracle@server01 dbs]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Dec 31 18:45:44 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB (DBID=2999538401, not open)
RMAN> catalog start with '/tmp/rman/';