使用rman备份将rac环境恢复到单实例
rac环境
[oracle@rac02 ~]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.11.*.61 rac01
1.1.1.13 rac01-priv
10.11.*.200 rac01-vip
10.11.*.62 rac02
1.1.1.14 rac02-priv
10.11.*.201 rac02-vip
10.11.*.202 scanvip-ip
rac集群信息查看
# crsstat
ora.bol.db ora....se.type 0/2 0/1 ONLINE ONLINE rac01
[oracle@rac02 ~]$ sqlplus / as sysdba
SQL> show parameter clusterNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
SQL> show parameter nameNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string bol
db_unique_name string bol
global_names boolean FALSE
instance_name string bol2
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string bol
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA/bol/arch
Oldest online log sequence 343
Next log sequence to archive 344
Current log sequence 344
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/bol/datafile/system.259.1015241807 SYSTEM READ WRITE
datafile 2 +DATA/bol/datafile/sysaux.260.1015241811 ONLINE READ WRITE
datafile 3 +DATA/bol/datafile/undotbs1.261.1015241811 ONLINE READ WRITE
datafile 4 +DATA/bol/datafile/undotbs2.263.1015241815 ONLINE READ WRITE
datafile 5 +DATA/bol/datafile/users.264.1015241815 ONLINE READ WRITE
datafile 6 +DATA/bol/datafile/cad01.dbf ONLINE READ WRITE
datafile 7 +DATA/bol/datafile/scm01.dbf ONLINE READ WRITE
datafile 8 +DATA/bol/datafile/zabbix01.dbf ONLINE READ WRITE
tempfile 1 +DATA/bol/tempfile/temp.262.1015241813 ONLINE READ WRITE
logfile 1 +DATA/bol/onlinelog/group_1.257.1015241807
logfile 1 +FRA/bol/onlinelog/group_1.257.1015241807
logfile 2 +DATA/bol/onlinelog/group_2.258.1015241807
logfile 2 +FRA/bol/onlinelog/group_2.258.1015241807
logfile 3 +DATA/bol/onlinelog/group_3.265.1015242985
logfile 3 +FRA/bol/onlinelog/group_3.259.1015242985
logfile 4 +DATA/bol/onlinelog/group_4.266.1015242985
logfile 4 +FRA/bol/onlinelog/group_4.260.1015242985
controlfile +DATA/bol/controlfile/current.256.1015241807
controlfile +FRA/bol/controlfile/current.256.1015241807
SQL> show parameter instance_nNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string bol2
instance_number integer 2
SQL> show parameter spfileNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/bol/spfilebol.ora
查看rac02节点的rman备份
#ll -art /home/oracle/backup
-rw-r----- 1 oracle asmadmin 85729280 Dec 3 22:35 bol_fullbakepuihggn_1_1_20191203
-rw-r----- 1 oracle asmadmin 2966593536 Dec 3 22:35 bol_fullbakequihggo_1_1_20191203
-rw-r----- 1 oracle asmadmin 144319488 Dec 3 22:35 arch_esuihghs_1_1_20191203
-rw-r----- 1 oracle asmadmin 72366080 Dec 3 22:35 arch_etuihght_1_1_20191203
-rw-r----- 1 oracle asmadmin 18644992 Dec 3 22:35 20191203_BOL_478_1_4237955019.ctl
-rw-r----- 1 oracle asmadmin 98304 Dec 3 22:35 bol_spfile_evuihgi1_1_1_20191203
将rman备份文件cp到单实例环境（这里的单实例环境-只安装了数据库软件）
[oracle@rac02 backup]$ scp bol_fullbakepuihggn_1_1_20191203 bol_fullbakequihggo_1_1_20191203 arch_esuihghs_1_1_20191203 arch_etuihght_1_1_20191203 20191203_BOL_478_1_4237955019.ctl bol_spfile_evuihgi1_1_1_20191203 oracle@10.11.*.80:/home/oracle/backup/.
[oracle@oracle backup]$ cp bol_spfile_evuihgi1_1_1_20191203 initbol.ora
—修改参数文件
[oracle@oracle backup]$ more initbol.ora
*.audit_file_dest='/u01/app/oracle/admin/bol/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/bol/control01.ctl','/u01/app/oracle/fast_recovery_area/bol/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='bol'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=6005194752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=bolXDB)'
*.log_archive_dest_1='location=/u01/app/oracle/archive'
*.log_archive_format='%t_%s_%r.arch'
*.memory_target=2147483648
*.open_cursors=300
*.processes=350
*.remote_login_passwordfile='exclusive'
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
—select 6744440832/1024/1024/1024,3*1024*1024*1024;
单实例库创建目录
mkdir -p /u01/app/oracle/fast_recovery_area/bol
mkdir -p /u01/app/oracle/oradata/bol/data
mkdir -p /u01/app/oracle/oradata/bol/tempfile
mkdir -p /u01/app/oracle/admin/bol/adump
mkdir -p /u01/app/oracle/admin/bol/data
mkdir -p /u01/app/oracle/admin/bol/redo
mkdir -p /u01/app/oracle/admin/bol/tempfile
mkdir -p /u01/app/oracle/archive
单实例创建spfile文件
[oracle@oracle ~]$ vim .bash_profile
[oracle@oracle ~]$ source .bash_profile
[oracle@oracle ~]$ sqlplus -vSQL*Plus: Release 11.2.0.4.0 Production
[oracle@oracle dbs]$ ORACLE_SID=bol
[oracle@oracle dbs]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 4 11:30:49 2019Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to an idle instance.SQL> create spfile from pfile='/home/oracle/backup/initbol.ora';File created.
启动单实例到nomount状态
[oracle@oracle ~]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Wed Dec 4 11:35:07 2019Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database (not started)RMAN> startup nomount;Oracle instance startedTotal System Global Area 2137886720 bytesFixed Size 2254952 bytes
Variable Size 1342179224 bytes
Database Buffers 788529152 bytes
Redo Buffers 4923392 bytes
从备份中恢复控制文件
==restore controlfile from '/home/oracle/backup/20191203_BOL_478_1_4237955019.ctl';
RMAN> restore controlfile from '/home/oracle/backup/20191203_BOL_478_1_4237955019.ctl';Starting restore at 04-DEC-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=284 device type=DISKchannel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/bol/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/bol/control02.ctl
Finished restore at 04-DEC-19
==控制文件已经还原，注意此处控制文件的还原路径是spfile中指定的路径，接下来还原数据文件及恢复数据库
==启动到mount状态并还原和恢复整个数据库
RMAN> alter database mount;database mounted
released channel: ORA_DISK_1RMAN> list backupset summary;
RMAN> list backupset of archivelog all;
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
477 69.01M DISK 00:00:01 03-DEC-19
BP Key: 477 Status: AVAILABLE Compressed: NO Tag: TAG20191203T223540
Piece Name: /home/oracle/backup/arch_etuihght_1_1_20191203 List of Archived Logs in backup set 477
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 433 10580072 03-DEC-19 10598676 04-DEC-19
1 434 10598676 04-DEC-19 10598684 04-DEC-19
2 341 10594034 04-DEC-19 10597753 04-DEC-19
2 342 10597753 04-DEC-19 10598672 04-DEC-19
2 343 10598672 04-DEC-19 10598689 04-DEC-19
==数据文件的转换
SQL> set pagesize 200 linesize 200
SQL> select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";'
from v$datafile a
union all
select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";'
from v$tempfile a
union all
SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''' ||
a.MEMBER || ''''' ";'
FROM v$logfile a;
'SETNEWNAMEFORDATAFILE'||A.FILE#||'TO"'||A.NAME||'";'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
set newname for datafile 1 to "+DATA/bol/datafile/system.259.1015241807";
set newname for datafile 2 to "+DATA/bol/datafile/sysaux.260.1015241811";
set newname for datafile 3 to "+DATA/bol/datafile/undotbs1.261.1015241811";
set newname for datafile 4 to "+DATA/bol/datafile/undotbs2.263.1015241815";
set newname for datafile 5 to "+DATA/bol/datafile/users.264.1015241815";
set newname for datafile 6 to "+DATA/bol/datafile/cad01.dbf";
set newname for datafile 7 to "+DATA/bol/datafile/scm01.dbf";
set newname for datafile 8 to "+DATA/bol/datafile/zabbix01.dbf";
set newname for tempfile 1 to "+DATA/bol/tempfile/temp.262.1015241813";
SQL "ALTER DATABASE RENAME FILE ''+DATA/bol/onlinelog/group_1.257.1015241807'' to ''+DATA/bol/onlinelog/group_1.257.1015241807'' ";
SQL "ALTER DATABASE RENAME FILE ''+FRA/bol/onlinelog/group_1.257.1015241807'' to ''+FRA/bol/onlinelog/group_1.257.1015241807'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/bol/onlinelog/group_2.258.1015241807'' to ''+DATA/bol/onlinelog/group_2.258.1015241807'' ";
SQL "ALTER DATABASE RENAME FILE ''+FRA/bol/onlinelog/group_2.258.1015241807'' to ''+FRA/bol/onlinelog/group_2.258.1015241807'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/bol/onlinelog/group_3.265.1015242985'' to ''+DATA/bol/onlinelog/group_3.265.1015242985'' ";
SQL "ALTER DATABASE RENAME FILE ''+FRA/bol/onlinelog/group_3.259.1015242985'' to ''+FRA/bol/onlinelog/group_3.259.1015242985'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/bol/onlinelog/group_4.266.1015242985'' to ''+DATA/bol/onlinelog/group_4.266.1015242985'' ";
SQL "ALTER DATABASE RENAME FILE ''+FRA/bol/onlinelog/group_4.260.1015242985'' to ''+FRA/bol/onlinelog/group_4.260.1015242985'' ";17 rows selected.RMAN> RUN{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
set newname for datafile 1 to "/u01/app/oracle/oradata/bol/data/system01.dbf";
set newname for datafile 2 to "/u01/app/oracle/oradata/bol/data/sysaux02.dbf";
set newname for datafile 3 to "/u01/app/oracle/oradata/bol/data/undotbs101.dbf";
set newname for datafile 4 to "/u01/app/oracle/oradata/bol/data/undotbs202.dbf";
set newname for datafile 5 to "/u01/app/oracle/oradata/bol/data/users01.dbf";
set newname for datafile 6 to "/u01/app/oracle/oradata/bol/data/cad01.dbf";
set newname for datafile 7 to "/u01/app/oracle/oradata/bol/data/scm01.dbf";
set newname for datafile 8 to "/u01/app/oracle/oradata/bol/data/zabbix01.dbf";
set newname for tempfile 1 to "/u01/app/oracle/oradata/bol/tempfile/temp01.dbf";
SQL "ALTER DATABASE RENAME FILE ''+DATA/bol/onlinelog/group_1.257.1015241807'' to ''/u01/app/oracle/admin/bol/redo/redo01_1.log'' ";
SQL "ALTER DATABASE RENAME FILE ''+FRA/bol/onlinelog/group_1.257.1015241807'' to ''/u01/app/oracle/admin/bol/redo/redo01_2.log'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/bol/onlinelog/group_2.258.1015241807'' to ''/u01/app/oracle/admin/bol/redo/redo02_1.log'' ";
SQL "ALTER DATABASE RENAME FILE ''+FRA/bol/onlinelog/group_2.258.1015241807'' to ''/u01/app/oracle/admin/bol/redo/redo02_2.log'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/bol/onlinelog/group_3.265.1015242985'' to ''/u01/app/oracle/admin/bol/redo/redo03_1.log'' ";
SQL "ALTER DATABASE RENAME FILE ''+FRA/bol/onlinelog/group_3.259.1015242985'' to ''/u01/app/oracle/admin/bol/redo/redo03_2.log'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/bol/onlinelog/group_4.266.1015242985'' to ''/u01/app/oracle/admin/bol/redo/redo04_1.log'' ";
SQL "ALTER DATABASE RENAME FILE ''+FRA/bol/onlinelog/group_4.260.1015242985'' to ''/u01/app/oracle/admin/bol/redo/redo04_2.log'' ";
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
}
==告警日志
==查看数据文件是否已经还原
[oracle@oracle ~]$ ll -h /u01/app/oracle/oradata/bol/data/
total 6.9G
-rw-r-----. 1 oracle oinstall 1.1G Dec 4 11:58 cad01.dbf
-rw-r-----. 1 oracle oinstall 1.1G Dec 4 11:58 scm01.dbf
-rw-r-----. 1 oracle oinstall 2.4G Dec 4 11:59 sysaux02.dbf
-rw-r-----. 1 oracle oinstall 761M Dec 4 11:59 system01.dbf
-rw-r-----. 1 oracle oinstall 1.2G Dec 4 11:58 undotbs101.dbf
-rw-r-----. 1 oracle oinstall 201M Dec 4 11:58 undotbs202.dbf
-rw-r-----. 1 oracle oinstall 5.1M Dec 4 11:58 users01.dbf
-rw-r-----. 1 oracle oinstall 501M Dec 4 11:58 zabbix01.dbf
==由前边的备份集中可以看出，备份集中的thread 1的最大日志号为434，thread 2的最大日志号为343，所以不完全恢复
RMAN> run{
2> set until sequence 434 thread 1;
3> set until sequence 343 thread 2;
4> recover database;
5> }executing command: SET until clauseexecuting command: SET until clauseStarting recover at 04-DEC-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=284 device type=DISKstarting media recoverychannel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=433
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=342
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/arch_etuihght_1_1_20191203
channel ORA_DISK_1: piece handle=/home/oracle/backup/arch_etuihght_1_1_20191203 tag=TAG20191203T223540
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/archive/2_342_1015241803.arch thread=2 sequence=342
archived log file name=/u01/app/oracle/archive/1_433_1015241803.arch thread=1 sequence=433
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-DEC-19
使用resetlogs打开数据库
RMAN> alter database open resetlogs;database opened
查看redo日志
[oracle@oracle backup]$ ll -h /u01/app/oracle/admin/bol/redo/
total 401M
-rw-r-----. 1 oracle oinstall 51M Dec 4 12:18 redo01_1.log
-rw-r-----. 1 oracle oinstall 51M Dec 4 12:18 redo01_2.log
-rw-r-----. 1 oracle oinstall 51M Dec 4 12:18 redo02_1.log
-rw-r-----. 1 oracle oinstall 51M Dec 4 12:18 redo02_2.log
-rw-r-----. 1 oracle oinstall 51M Dec 4 12:18 redo03_1.log
-rw-r-----. 1 oracle oinstall 51M Dec 4 12:18 redo03_2.log
-rw-r-----. 1 oracle oinstall 51M Dec 4 12:18 redo04_1.log
-rw-r-----. 1 oracle oinstall 51M Dec 4 12:18 redo04_2.log
查看相关文件
[oracle@oracle backup]$ sqlplus / as sysdba
SQL> show parameter name
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archive
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
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
;
==清除未使用的redo
SQL> col instance format a8
SQL> select thread#,instance,status,enabled from v$thread; THREAD# INSTANCE STATUS ENABLED
---------- -------- ------ --------
1 bol OPEN PUBLIC
2 bol2 CLOSED PUBLICSQL> select group#,thread#,archived,status from v$log; GROUP# THREAD# ARC STATUS
---------- ---------- --- ----------------
1 1 NO CURRENT
2 1 YES UNUSED
3 2 YES INACTIVE
4 2 YES UNUSEDSQL> alter database disable thread 2;Database altered.SQL> select thread#,instance,status,enabled from v$thread; THREAD# INSTANCE STATUS ENABLED
---------- -------- ------ --------
1 bol OPEN PUBLIC
2 bol2 CLOSED DISABLED
==清除多余的 undo 文件
SQL> select name from v$tablespace where name like 'UNDO%';NAME
------------------------------
UNDOTBS1
UNDOTBS2SQL> show parameter undo_tablespace;NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> drop tablespace undotbs2 including contents and datafiles;Tablespace dropped.
到此，恢复以及完成。迁移还可以使用在线 RMAN Duplicate
参考：
https://www.cnblogs.com/lhrbest/p/4546661.html
