目录一、系统环境二、源数据库的操作三、目标数据库的操作四、开始恢复数据库五、启动数据库
把 Oracle 数据库从 RAC 集群迁移到单机环境
一、系统环境
1、源数据库
db_name：hisdb SID：hisdb1、hisdb2IP: 192.168.1.101、192.168.1.102os：CentOS Linux release 7.3.1611 (Core)
2、目标数据库
IP: 192.168.1.15os：CentOS Linux release 7.3.1611 (Core)安装 Oracle 软件， 不创建实例
二、源数据库的操作
1、创建 pfile 文件
SQL> create pfile=\’/home/oracle/pfile0728.ora\’ from spfile;File created.
2、查看生成的 pfile 文件
[oracle@rac1 ~]$ pwd/home/oracle[oracle@rac1 ~]$ lltotal 2487204drwxr-xr-x 2 oracle oinstall 111 Jun 24 21:30 data-bakdrwxr-xr-x 7 oracle oinstall 136 Aug 27 2013 database-rw-r–r–. 1 oracle oinstall 1395582860 Jan 7 2020 p13390677_112040_Linux-x86-64_1of7.zip-rw-r–r–. 1 oracle oinstall 1151304589 Jan 7 2020 p13390677_112040_Linux-x86-64_2of7.zip-rw-r–r– 1 oracle asmadmin 1547 Jul 28 08:27 pfile0728.ora
3、将 pfile 文件传到目标数据库的 $ORACLE_HOME/dbs/ 目录下
[oracle@rac1 ~]$ scp pfile0728.ora oracle@192.168.1.15:/home/oracle/The authenticity of host \’192.168.1.15 (192.168.1.15)\’ can\’t be established.ECDSA key fingerprint is 5c:31:ec:3c:ee:9c:6d:22:f3:60:dc:15:72:fd:67:91.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added \’192.168.1.15\’ (ECDSA) to the list of known hosts.oracle@192.168.1.15\’s password: pfile0728.ora 100% 1547 1.5KB/s 00:00 [oracle@rac1 ~]$ # 切换到目标主机[oracle@mysql bin]$ cd ~[oracle@mysql ~]$ lsdb_install.rsp pfile0728.ora[oracle@mysql ~]$ cp pfile0728.ora $ORACLE_HOME/dbs/[oracle@mysql ~]$ ls $ORACLE_HOME/dbs/p*/usr/local/oracle/product/11.2.0/db_1/dbs/pfile0728.ora
4、备份源数据库
（1）创建备份目录
[root@rac1 ~]# mkdir /arch/bk0729 -p[root@rac1 ~]# chown -R oracle:oinstall /arch/bk0729[root@rac1 ~]# ll /arch/总用量 0drwxr-xr-x 2 oracle oinstall 6 7月 30 18:58 bk0729
（2）用RMAN 全备数据库：
#=设置备份参数：备份到磁盘，6 个通道 ======================================configure device type disk parallelism 6 backup type to backupset;#=设置备份参数：设置备份文件的位置及文件名格式 ==================================configure channel device type disk format \’/arch/bk0729/%d_%I_%s_%p_%T.bkp\’;# 备份控制文件 ============================================backup current controlfile format =\’/arch/bk0729/control_bak_%s.bak\’;# 备份数据库 ============================================backup as compressed backupset database;# 下面的备份命令可以同时备份数据库和控制文件backup incremental level 0 format \’/rmanbackup/orcl_full_%U\’ database include current controlfile;#= 设置备份文件格式：===========================================configure channel device type disk format \’/arch/bk0729/ctl_%d_%I_%s_%p_%T.bkp\’;#= 备份归档日志：===========================================backup as compressed backupset archivelog all;#=设置备份参数：备份到磁盘，6 个通道 ======================================RMAN> configure device type disk parallelism 6 backup type to backupset;old RMAN configuration parameters:CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 6;new RMAN configuration parameters:CONFIGURE DEVICE TYPE DISK PARALLELISM 6 BACKUP TYPE TO BACKUPSET;new RMAN configuration parameters are successfully stored#=设置备份参数：设置备份文件的位置及文件名格式 ==================================RMAN> configure channel device type disk format \’/arch/bk0729/%d_%I_%s_%p_%T.bkp\’;old RMAN configuration parameters:CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT \’/arch/bk0729/%d_%I_%s_%p_%T.bkp\’;new RMAN configuration parameters:CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT \’/arch/bk0729/%d_%I_%s_%p_%T.bkp\’;new RMAN configuration parameters are successfully stored# 备份控制文件 ============================================RMAN> backup current controlfile format =\’/arch/bk0729/control_bak_%s.bak\’;Starting backup at 30-JUL-22allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=31 instance=hisdb1 device type=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: SID=125 instance=hisdb1 device type=DISKallocated channel: ORA_DISK_3channel ORA_DISK_3: SID=158 instance=hisdb1 device type=DISKallocated channel: ORA_DISK_4channel ORA_DISK_4: SID=159 instance=hisdb1 device type=DISKallocated channel: ORA_DISK_5channel ORA_DISK_5: SID=162 instance=hisdb1 device type=DISKallocated channel: ORA_DISK_6channel ORA_DISK_6: SID=36 instance=hisdb1 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setchannel ORA_DISK_1: starting piece 1 at 30-JUL-22channel ORA_DISK_1: finished piece 1 at 30-JUL-22piece handle=/arch/bk0729/control_bak_32.bak tag=TAG20220730T193424 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 30-JUL-22# 备份数据库 ============================================RMAN> backup as compressed backupset database;Starting backup at 30-JUL-22using channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3using channel ORA_DISK_4using channel ORA_DISK_5using channel ORA_DISK_6channel ORA_DISK_1: starting compressed full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=+DATA/hisdb/datafile/system.278.1107994145channel ORA_DISK_1: starting piece 1 at 30-JUL-22channel ORA_DISK_2: starting compressed full datafile backup setchannel ORA_DISK_2: specifying datafile(s) in backup setinput datafile file number=00002 name=+DATA/hisdb/datafile/sysaux.279.1107994147input datafile file number=00004 name=+DATA/hisdb/datafile/users.270.1107994131channel ORA_DISK_2: starting piece 1 at 30-JUL-22channel ORA_DISK_3: starting compressed full datafile backup setchannel ORA_DISK_3: specifying datafile(s) in backup setinput datafile file number=00003 name=+DATA/hisdb/datafile/undotbs1.271.1107994123input datafile file number=00006 name=+DATA/hisdb/datafile/ts001.277.1107994139channel ORA_DISK_3: starting piece 1 at 30-JUL-22channel ORA_DISK_4: starting compressed full datafile backup setchannel ORA_DISK_4: specifying datafile(s) in backup setinput datafile file number=00007 name=+DATA/hisdb/datafile/undotbs2.284.1108022905input datafile file number=00005 name=+DATA/hisdb/datafile/ts001.276.1107994131channel ORA_DISK_4: starting piece 1 at 30-JUL-22channel ORA_DISK_5: starting compressed full datafile backup setchannel ORA_DISK_5: specifying datafile(s) in backup setchannel ORA_DISK_6: starting compressed full datafile backup setchannel ORA_DISK_6: specifying datafile(s) in backup setincluding current SPFILE in backup setchannel ORA_DISK_6: starting piece 1 at 30-JUL-22channel ORA_DISK_3: finished piece 1 at 30-JUL-22piece handle=/arch/bk0729/HISDB_2002805648_35_1_20220730.bkp tag=TAG20220730T193500 comment=NONEchannel ORA_DISK_3: backup set complete, elapsed time: 00:00:54channel ORA_DISK_6: finished piece 1 at 30-JUL-22piece handle=/arch/bk0729/HISDB_2002805648_38_1_20220730.bkp tag=TAG20220730T193500 comment=NONEchannel ORA_DISK_6: backup set complete, elapsed time: 00:00:27channel ORA_DISK_1: finished piece 1 at 30-JUL-22piece handle=/arch/bk0729/HISDB_2002805648_33_1_20220730.bkp tag=TAG20220730T193500 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:26channel ORA_DISK_2: finished piece 1 at 30-JUL-22piece handle=/arch/bk0729/HISDB_2002805648_34_1_20220730.bkp tag=TAG20220730T193500 comment=NONEchannel ORA_DISK_2: backup set complete, elapsed time: 00:01:26channel ORA_DISK_4: finished piece 1 at 30-JUL-22piece handle=/arch/bk0729/HISDB_2002805648_36_1_20220730.bkp tag=TAG20220730T193500 comment=NONEchannel ORA_DISK_4: backup set complete, elapsed time: 00:00:45including current control file in backup setchannel ORA_DISK_5: starting piece 1 at 30-JUL-22channel ORA_DISK_5: finished piece 1 at 30-JUL-22piece handle=/arch/bk0729/HISDB_2002805648_37_1_20220730.bkp tag=TAG20220730T193500 comment=NONEchannel ORA_DISK_5: backup set complete, elapsed time: 00:00:01Finished backup at 30-JUL-22# 查看备份的文件[root@rac1 bk0729]# pwd/arch/bk0729[root@rac1 bk0729]# ll -h总用量 325M-rw-r—– 1 oracle asmadmin 9.4M 7月 30 19:34 control_bak_32.bak-rw-r—– 1 oracle asmadmin 213M 7月 30 20:24 HISDB_2002805648_45_1_20220730.bkp-rw-r—– 1 oracle asmadmin 99M 7月 30 20:24 HISDB_2002805648_46_1_20220730.bkp-rw-r—– 1 oracle asmadmin 1.6M 7月 30 20:23 HISDB_2002805648_47_1_20220730.bkp-rw-r—– 1 oracle asmadmin 1.1M 7月 30 20:23 HISDB_2002805648_48_1_20220730.bkp-rw-r—– 1 oracle asmadmin 1.1M 7月 30 20:24 HISDB_2002805648_49_1_20220730.bkp-rw-r—– 1 oracle asmadmin 96K 7月 30 20:23 HISDB_2002805648_50_1_20220730.bkp#= 设置备份文件格式：===========================================RMAN> configure channel device type disk format \’/arch/bk0729/ctl_%d_%I_%s_%p_%T.bkp\’;old RMAN configuration parameters:CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT \’/arch/bk0729/%d_%I_%s_%p_%T.bkp\’;new RMAN configuration parameters:CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT \’/arch/bk0729/ctl_%d_%I_%s_%p_%T.bkp\’;new RMAN configuration parameters are successfully storedreleased channel: ORA_DISK_1released channel: ORA_DISK_2released channel: ORA_DISK_3released channel: ORA_DISK_4released channel: ORA_DISK_5released channel: ORA_DISK_6#= 备份归档日志：===========================================RMAN> backup as compressed backupset archivelog all;Starting backup at 30-JUL-22current log archivedusing channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3using channel ORA_DISK_4using channel ORA_DISK_5using channel ORA_DISK_6channel ORA_DISK_1: starting compressed archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=98 RECID=13 STAMP=1111432401channel ORA_DISK_1: starting piece 1 at 30-JUL-22channel ORA_DISK_2: starting compressed archived log backup setchannel ORA_DISK_2: specifying archived log(s) in backup setinput archived log thread=1 sequence=99 RECID=14 STAMP=1111432403channel ORA_DISK_2: starting piece 1 at 30-JUL-22channel ORA_DISK_3: starting compressed archived log backup setchannel ORA_DISK_3: specifying archived log(s) in backup setinput archived log thread=1 sequence=100 RECID=15 STAMP=1111432904channel ORA_DISK_3: starting piece 1 at 30-JUL-22channel ORA_DISK_4: starting compressed archived log backup setchannel ORA_DISK_4: specifying archived log(s) in backup setinput archived log thread=1 sequence=101 RECID=16 STAMP=1111432905channel ORA_DISK_4: starting piece 1 at 30-JUL-22channel ORA_DISK_5: starting compressed archived log backup setchannel ORA_DISK_5: specifying archived log(s) in backup setinput archived log thread=1 sequence=102 RECID=17 STAMP=1111433394channel ORA_DISK_5: starting piece 1 at 30-JUL-22channel ORA_DISK_6: starting compressed archived log backup setchannel ORA_DISK_6: specifying archived log(s) in backup setinput archived log thread=1 sequence=103 RECID=18 STAMP=1111433805channel ORA_DISK_6: starting piece 1 at 30-JUL-22channel ORA_DISK_1: finished piece 1 at 30-JUL-22piece handle=/arch/bk0729/HISDB_2002805648_39_1_20220730.bkp tag=TAG20220730T193645 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:00channel ORA_DISK_2: finished piece 1 at 30-JUL-22piece handle=/arch/bk0729/HISDB_2002805648_40_1_20220730.bkp tag=TAG20220730T193645 comment=NONEchannel ORA_DISK_2: backup set complete, elapsed time: 00:00:00channel ORA_DISK_3: finished piece 1 at 30-JUL-22piece handle=/arch/bk0729/HISDB_2002805648_41_1_20220730.bkp tag=TAG20220730T193645 comment=NONEchannel ORA_DISK_3: backup set complete, elapsed time: 00:00:00channel ORA_DISK_4: finished piece 1 at 30-JUL-22piece handle=/arch/bk0729/HISDB_2002805648_42_1_20220730.bkp tag=TAG20220730T193645 comment=NONEchannel ORA_DISK_4: backup set complete, elapsed time: 00:00:00channel ORA_DISK_5: finished piece 1 at 30-JUL-22piece handle=/arch/bk0729/HISDB_2002805648_43_1_20220730.bkp tag=TAG20220730T193645 comment=NONEchannel ORA_DISK_5: backup set complete, elapsed time: 00:00:01channel ORA_DISK_6: finished piece 1 at 30-JUL-22piece handle=/arch/bk0729/HISDB_2002805648_44_1_20220730.bkp tag=TAG20220730T193645 comment=NONEchannel ORA_DISK_6: backup set complete, elapsed time: 00:00:01Finished backup at 30-JUL-22# 查看备份的文件[root@rac1 bk0729]# ll -h总用量 328M-rw-r—– 1 oracle asmadmin 9.4M 7月 30 19:34 control_bak_32.bak-rw-r—– 1 oracle asmadmin 1.5M 7月 30 20:26 ctl_HISDB_2002805648_51_1_20220730.bkp-rw-r—– 1 oracle asmadmin 169K 7月 30 20:26 ctl_HISDB_2002805648_52_1_20220730.bkp-rw-r—– 1 oracle asmadmin 218K 7月 30 20:26 ctl_HISDB_2002805648_53_1_20220730.bkp-rw-r—– 1 oracle asmadmin 1.7M 7月 30 20:26 ctl_HISDB_2002805648_54_1_20220730.bkp-rw-r—– 1 oracle asmadmin 213M 7月 30 20:24 HISDB_2002805648_45_1_20220730.bkp-rw-r—– 1 oracle asmadmin 99M 7月 30 20:24 HISDB_2002805648_46_1_20220730.bkp-rw-r—– 1 oracle asmadmin 1.6M 7月 30 20:23 HISDB_2002805648_47_1_20220730.bkp-rw-r—– 1 oracle asmadmin 1.1M 7月 30 20:23 HISDB_2002805648_48_1_20220730.bkp-rw-r—– 1 oracle asmadmin 1.1M 7月 30 20:24 HISDB_2002805648_49_1_20220730.bkp-rw-r—– 1 oracle asmadmin 96K 7月 30 20:23 HISDB_2002805648_50_1_20220730.bkp
三、目标数据库的操作
1、修改参数文件
（1）源数据库的参数文件内容如下：
[oracle@rac1 ~]$ vi pfile0728.ora hisdb2.__db_cache_size=192937984hisdb1.__db_cache_size=201326592hisdb2.__java_pool_size=4194304hisdb1.__java_pool_size=4194304hisdb2.__large_pool_size=8388608hisdb1.__large_pool_size=8388608hisdb1.__oracle_base=\’/u01/app/oracle\’#ORACLE_BASE set from environmenthisdb2.__oracle_base=\’/u01/app/oracle\’#ORACLE_BASE set from environmenthisdb2.__pga_aggregate_target=222298112hisdb1.__pga_aggregate_target=222298112hisdb2.__sga_target=419430400hisdb1.__sga_target=419430400hisdb2.__shared_io_pool_size=0hisdb2.__db_cache_size=192937984hisdb1.__db_cache_size=201326592hisdb2.__java_pool_size=4194304hisdb1.__java_pool_size=4194304hisdb2.__large_pool_size=8388608hisdb1.__large_pool_size=8388608hisdb1.__oracle_base=\’/u01/app/oracle\’#ORACLE_BASE set from environmenthisdb2.__oracle_base=\’/u01/app/oracle\’#ORACLE_BASE set from environmenthisdb2.__pga_aggregate_target=222298112hisdb1.__pga_aggregate_target=222298112hisdb2.__sga_target=419430400hisdb1.__sga_target=419430400hisdb2.__shared_io_pool_size=0hisdb1.__shared_io_pool_size=0hisdb2.__shared_pool_size=201326592hisdb1.__shared_pool_size=192937984hisdb2.__streams_pool_size=0hisdb1.__streams_pool_size=0# 以上内容全部删除# 创建如下目录mkdir -p /usr/local/oracle/admin/hisdb/adumpmkdir -p /usr/local/oracle/controlfile/mkdir -p /data/oracle/controlfile/mkdir -p /data/oracle/flash_recovery_areamkdir -p /data/oracle/archmkdir -p /data/oracle/oradata# *.audit_file_dest=\’/u01/app/oracle/admin/hisdb/adump\’ –修改此行内容如下*.audit_file_dest=\’/usr/local/oracle/admin/hisdb/adump\’# *.cluster_database=TRUE # 删除此行# *.cluster_database_instances=2 # 删除此行*.compatible=\’11.2.0.4.0\’ # 此行不变#*.control_files=\’+DATA/hisdb/controlfile/control01.ctl\’,\’+BAK/hisdb/controlfile/control02.ctl\’ –修改此行内容如下*.control_files=\’/usr/local/oracle/controlfile/control01.ctl\’,\’/data/oracle/controlfile/control02.ctl\’*.db_block_size=8192 # 此行不变# *.db_create_file_dest=\’+DATA\’ # 删除此行# *.db_domain=\’\’ # 删除此行*.db_name=\’hisdb\’ # 此行不变# *.db_recovery_file_dest=\’/u01/app/oracle/flash_recovery_area\’ –修改此行内容如下*.db_recovery_file_dest=\’/data/oracle/flash_recovery_area\’*.db_recovery_file_dest_size=4102029312 # 此行不变#*.diagnostic_dest=\’/u01/app/oracle\’ –修改此行内容如下*.diagnostic_dest=\’/usr/local/oracle\’*.dispatchers=\'(PROTOCOL=TCP) (SERVICE=hisdbXDB)\’ # 此行不变# hisdb1.instance_number=1 # 删除此行# hisdb2.instance_number=2 # 删除此行# *.log_archive_dest_1=\’location=+BAK\’ –修改此行内容如下*.log_archive_dest_1=\’location=/data/oracle/arch\’*.log_archive_format=\’%t_%s_%r.dbf# *.memory_target=638588928 # 删除此行*.open_cursors=300 # 此行不变*.processes=150 # 此行不变#*.remote_listener=\’my-racscan:1521\’ # 删除此行*.remote_login_passwordfile=\’EXCLUSIVE\’# hisdb1.thread=1 # 删除此行# hisdb2.thread=2 # 删除此行*.undo_tablespace=\’UNDOTBS1\’ # 此行不变# hisdb1.undo_tablespace=\’UNDOTBS1\’ # 删除此行# hisdb2.undo_tablespace=\’UNDOTBS2\’ # 删除此行
（2）修改后的参数文件内容如下：
*.audit_file_dest=\’/usr/local/oracle/admin/hisdb/adump\’*.compatible=\’11.2.0.4.0\’ *.control_files=\’/usr/local/oracle/controlfile/control01.ctl\’,\’/data/oracle/controlfile/control02.ctl\’*.db_block_size=8192 *.db_name=\’hisdb\’*.db_recovery_file_dest=\’/data/oracle/flash_recovery_area\’*.db_recovery_file_dest_size=2147483648*.diagnostic_dest=\’/usr/local/oracle\’*.dispatchers=\'(PROTOCOL=TCP) (SERVICE=hisdbXDB)\’*.log_archive_dest_1=\’location=/data/oracle/arch\’*.log_archive_format=\’%t_%s_%r.dbf\’*.open_cursors=300*.processes=150*.remote_login_passwordfile=\’exclusive\’*.undo_tablespace=\’UNDOTBS1\’*.log_file_name_convert=(\’+DATA/hisdb/onlinelog\’,\’/data/oracle/oradata\’)*.db_file_name_convert=(\’+DATA/hisdb/datafile\’,\’/data/oracle/oradata\’)*.db_file_name_convert=(\’+DATA/hisdb/tempfile\’,\’/data/oracle/oradata\’)
2、使用修改后的参数文件启动数据库到 nomount
SQL> startup nomount pfile=\’/home/oracle/pfile0729.ora\’;ORACLE instance started.Total System Global Area 233861120 bytesFixed Size 2251976 bytesVariable Size 176161592 bytesDatabase Buffers 50331648 bytesRedo Buffers 5115904 bytes
3、生成 spfile 文件，关闭数据库，然后重新启动到 nomount
SQL> create spfile from pfile=\’/home/oracle/pfile0729.ora\’;File created.SQL> shutdown immediate;ORA-01507: database not mountedORACLE instance shut down.SQL> startup mSP2-0714: invalid combination of STARTUP optionsSQL> startup nomount;ORACLE instance started.Total System Global Area 233861120 bytesFixed Size 2251976 bytesVariable Size 176161592 bytesDatabase Buffers 50331648 bytesRedo Buffers 5115904 bytesSQL>
4、启动 rman，恢复控制文件
[oracle@host-192-168-20-5 oracle]$ rman target /Recovery Manager: Release 11.2.0.4.0 – Production on Sun Jul 31 00:20:01 2022Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: HISDB (not mounted)– 恢复控制文件RMAN> restore controlfile from \’/data/backup/control_bak_331659.bak\’;Starting restore at 31-JUL-22using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=189 device type=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:15output file name=/usr/local/oracle/controlfile/control01.ctloutput file name=/data/oracle/controlfile/control02.ctlFinished restore at 31-JUL-22
5、启动数据库到 mount
SQL> alter database mount;Database altered.
6、查看控制文件中的数据文件与临时文件信息
RMAN> report schema;
四、开始恢复数据库
1、核对备份文件
RMAN> crosscheck backup;using channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3using channel ORA_DISK_4using channel ORA_DISK_5using channel ORA_DISK_6crosschecked backup piece: found to be \’EXPIRED\’backup piece handle=/oracle/app/oracle/product/11.2.0/db_1/dbs/c-1947403592-20220722-06 RECID=323878 STAMP=1110743343….crosschecked backup piece: found to be \’EXPIRED\’backup piece handle=/oracle/app/oracle/product/11.2.0/db_1/dbs/c-1947403592-20220729-09 RECID=327567 STAMP=1111356921Crosschecked 45 objects
2、删除失效的备份文件
RMAN> delete expired backup;using channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3using channel ORA_DISK_4using channel ORA_DISK_5using channel ORA_DISK_6List of Backup PiecesBP Key BS Key Pc# Cp# Status Device Type Piece Name——- ——- — — ———– ———– ———-……./oracle/app/oracle/product/11.2.0/db_1/dbs/c-1947403592-20220729-09## 选择yes 删除 #########Do you really want to delete the above objects (enter YES or NO)? yes####################################deleted backup piecebackup piece handle=/oracle/app/oracle/product/11.2.0/db_1/dbs/c-1947403592-20220722-06 …….backup piece handle=/oracle/app/oracle/product/11.2.0/db_1/dbs/c-1947403592-20220729-09 RECID=327567 STAMP=1111356921Deleted 45 EXPIRED objects
3、更新备份文件
RMAN> catalog start with \’/data/backup/\’;
4、查看备份片信息
RMAN> list backup;
5、恢复数据库
run{set newname for datafile 1 to \’/data/oracle/oradata/system01\’;set newname for datafile 2 to \’/data/oracle/oradata/sysaux01\’;set newname for datafile 3 to \’/data/oracle/oradata/undotbs01\’;set newname for datafile 4 to \’/data/oracle/oradata/users01\’;set newname for datafile 5 to \’/data/oracle/oradata/undotbs02\’;set newname for datafile 6 to \’/data/oracle/oradata/audit_tbs01\’;set newname for datafile 7 to \’/data/oracle/oradata/data_ais01\’;set newname for datafile 8 to \’/data/oracle/oradata/data_applyout01\’;set newname for datafile 9 to \’/data/oracle/oradata/data_aqu01\’;set newname for datafile 10 to \’/data/oracle/oradata/data_cas01\’;set newname for datafile 11 to \’/data/oracle/oradata/data_com01\’;set newname for datafile 12 to \’/data/oracle/oradata/data_emr01\’;set newname for datafile 13 to \’/data/oracle/oradata/data_execdrug01\’;set newname for datafile 14 to \’/data/oracle/oradata/data_execundrug02\’;set newname for datafile 15 to \’/data/oracle/oradata/data_feedetail01\’;set newname for datafile 16 to \’/data/oracle/oradata/data_feeinfo01\’;set newname for datafile 17 to \’/data/oracle/oradata/data_fin.31401\’;set newname for datafile 18 to \’/data/oracle/oradata/data_goa.31301\’;set newname for datafile 19 to \’/data/oracle/oradata/data_itemlist01\’;set newname for datafile 20 to \’/data/oracle/oradata/data_lis311\’;set newname for datafile 21 to \’/data/oracle/oradata/data_log3101034788143\’;set newname for datafile 22 to \’/data/oracle/oradata/data_medicinelist3091034788143\’;set newname for datafile 23 to \’/data/oracle/oradata/data_met3081034788157\’;set newname for datafile 24 to \’/data/oracle/oradata/data_order3071034788169\’;set newname for datafile 25 to \’/data/oracle/oradata/data_order3061034788197\’;set newname for datafile 26 to \’/data/oracle/oradata/data_order3051034788225\’;set newname for datafile 27 to \’/data/oracle/oradata/data_order3041034788243\’;set newname for datafile 28 to \’/data/oracle/oradata/data_other3031034788255\’;set newname for datafile 29 to \’/data/oracle/oradata/data_output3021034788255\’;set newname for datafile 30 to \’/data/oracle/oradata/data_pha3011034788271\’;set newname for datafile 31 to \’/data/oracle/oradata/data_recipedetail3001034788275\’;set newname for datafile 32 to \’/data/oracle/oradata/data_record2991034788281\’;set newname for datafile 33 to \’/data/oracle/oradata/data_sem2981034788293\’;set newname for datafile 34 to \’/data/oracle/oradata/data_user2971034788293\’;set newname for datafile 35 to \’/data/oracle/oradata/index_ais2961034788297\’;set newname for datafile 36 to \’/data/oracle/oradata/index_applyout2951034788297\’;set newname for datafile 37 to \’/data/oracle/oradata/index_aqu2941034788309\’;set newname for datafile 38 to \’/data/oracle/oradata/index_cas2931034788309\’;set newname for datafile 39 to \’/data/oracle/oradata/index_com2921034788309\’;set newname for datafile 40 to \’/data/oracle/oradata/index_emr2911034788311\’;set newname for datafile 41 to \’/data/oracle/oradata/index_execdrug2901034788311\’;set newname for datafile 42 to \’/data/oracle/oradata/index_execundrug2891034788317\’;set newname for datafile 43 to \’/data/oracle/oradata/index_feedetail2881034788321\’;set newname for datafile 44 to \’/data/oracle/oradata/index_feeinfo2871034788329\’;set newname for datafile 45 to \’/data/oracle/oradata/index_fin2861034788337\’;set newname for datafile 46 to \’/data/oracle/oradata/index_goa2851034788343\’;set newname for datafile 47 to \’/data/oracle/oradata/index_itemlist2841034788343\’;set newname for datafile 48 to \’/data/oracle/oradata/index_lis.2831034788355\’;set newname for datafile 49 to \’/data/oracle/oradata/index_log.2821034788355\’;set newname for datafile 50 to \’/data/oracle/oradata/index_medicinelist2811034788355\’;set newname for datafile 51 to \’/data/oracle/oradata/index_met2801034788361\’;set newname for datafile 52 to \’/data/oracle/oradata/index_order2791034788369\’;set newname for datafile 53 to \’/data/oracle/oradata/index_other2781034788375\’;set newname for datafile 54 to \’/data/oracle/oradata/index_output2771034788375\’;set newname for datafile 55 to \’/data/oracle/oradata/index_pha2761034788381\’;set newname for datafile 56 to \’/data/oracle/oradata/index_recipedetail2581034788387\’;set newname for datafile 57 to \’/data/oracle/oradata/index_record3251034788389\’;set newname for datafile 58 to \’/data/oracle/oradata/index_sem2681034788391\’;set newname for datafile 59 to \’/data/oracle/oradata/index_user2711034788391\’;set newname for datafile 60 to \’/data/oracle/oradata/data_order2.dbf\’;set newname for datafile 61 to \’/data/oracle/oradata/data_order3.dbf\’;set newname for datafile 62 to \’/data/oracle/oradata/nfemr.dbf\’;set newname for datafile 63 to \’/data/oracle/oradata/emr5.dbf\’;set newname for datafile 64 to \’/data/oracle/oradata/emr52012.dbf\’;set newname for datafile 65 to \’/data/oracle/oradata/emr52013.dbf\’;set newname for datafile 66 to \’/data/oracle/oradata/emr52014.dbf\’;set newname for datafile 67 to \’/data/oracle/oradata/emr52015.dbf\’;set newname for datafile 68 to \’/data/oracle/oradata/emr52016.dbf\’;set newname for datafile 69 to \’/data/oracle/oradata/emr52017.dbf\’;set newname for datafile 70 to \’/data/oracle/oradata/emr52018.dbf\’;set newname for datafile 71 to \’/data/oracle/oradata/emr52019.dbf\’;set newname for datafile 72 to \’/data/oracle/oradata/emr52020.dbf\’;set newname for datafile 73 to \’/data/oracle/oradata/emr5202001.dbf\’;set newname for datafile 74 to \’/data/oracle/oradata/emr5202002.dbf\’;set newname for datafile 75 to \’/data/oracle/oradata/emr501.dbf\’;set newname for datafile 76 to \’/data/oracle/oradata/neuicu_data1\’;set newname for datafile 77 to \’/data/oracle/oradata/neucbus_data1\’;set newname for datafile 78 to \’/data/oracle/oradata/ntsdata01.dbf\’;set newname for datafile 79 to \’/data/oracle/oradata/emr5202003.dbf\’;set newname for datafile 80 to \’/data/oracle/oradata/emr5202101.dbf\’;set newname for datafile 81 to \’/data/oracle/oradata/emr5202102.dbf\’;set newname for datafile 82 to \’/data/oracle/oradata/emr5202103.dbf\’;set newname for datafile 83 to \’/data/oracle/oradata/ndqsdata01.dbf\’;set newname for datafile 84 to \’/data/oracle/oradata/emr520210401.dbf\’;set newname for datafile 85 to \’/data/oracle/oradata/emr5202104.dbf\’;set newname for datafile 86 to \’/data/oracle/oradata/emr5202105.dbf\’;set newname for datafile 87 to \’/data/oracle/oradata/emr5202106.dbf\’;set newname for datafile 88 to \’/data/oracle/oradata/emr502.dbf\’;set newname for datafile 89 to \’/data/oracle/oradata/emr503.dbf\’;set newname for datafile 90 to \’/data/oracle/oradata/sysaux001\’;set newname for datafile 91 to \’/data/oracle/oradata/emr5202201.dbf\’;set newname for datafile 92 to \’/data/oracle/oradata/neuicu_data11\’;set newname for datafile 93 to \’/data/oracle/oradata/emr_bak.dbf\’;set newname for datafile 94 to \’/data/oracle/oradata/sysaux002\’;set newname for datafile 95 to \’/data/oracle/oradata/system_bak\’;set newname for datafile 96 to \’/data/oracle/oradata/system_bak02\’;set newname for datafile 97 to \’/data/oracle/oradata/system_bak03\’;set newname for datafile 98 to \’/data/oracle/oradata/system_bak04\’;set newname for datafile 99 to \’/data/oracle/oradata/undotbs1_bak01\’;set newname for datafile 100 to \’/data/oracle/oradata/undotbs1_bak02\’;set newname for datafile 101 to \’/data/oracle/oradata/undotbs1_bak03\’;set newname for datafile 102 to \’/data/oracle/oradata/undotbs2_bak01\’;set newname for datafile 103 to \’/data/oracle/oradata/undotbs2_bak02\’;set newname for datafile 104 to \’/data/oracle/oradata/undotbs2_bak03\’;set newname for datafile 105 to \’/data/oracle/oradata/users02\’;set newname for datafile 106 to \’/data/oracle/oradata/users03\’;set newname for datafile 107 to \’/data/oracle/oradata/users04\’;set newname for datafile 108 to \’/data/oracle/oradata/emr5202202.dbf\’;set newname for datafile 109 to \’/data/oracle/oradata/emr5202203.dbf\’;set newname for datafile 110 to \’/data/oracle/oradata/emr5202204.dbf\’;set newname for datafile 111 to \’/data/oracle/oradata/emr5202205.dbf\’;set newname for datafile 112 to \’/data/oracle/oradata/neucbus_data2\’;set newname for tempfile 1 to \’/data/oracle/oradata/temp01\’;set newname for tempfile 2 to \’/data/oracle/oradata/temp02\’;restore database;switch datafile all;switch tempfile all;recover database;}
6、修改日志文件
（1）查看日志文件
SQL> select member from v$logfile;MEMBER——————————————————————————–/data/oracle/data/group_601/data/oracle/data/group_501/data/oracle/data/group_201/data/oracle/data/group_101/data/oracle/data/group_301/data/oracle/data/group_401/data/oracle/data/group_701/data/oracle/data/group_801/data/oracle/data/group_2101/data/oracle/data/group_2201/data/oracle/data/group_2301/data/oracle/data/group_2401/data/oracle/data/group_2501/data/oracle/data/group_3101/data/oracle/data/group_3201/data/oracle/data/group_3301/data/oracle/data/group_3401/data/oracle/data/group_350118 rows selected.
（2）修改日志文件
alter database rename file \’+DATA/hisdb/onlinelog/group_6.267.1034787531\’ to \’/data/oracle/data/group_601\’;alter database rename file \’+DATA/hisdb/onlinelog/group_5.327.1034787531\’ to \’/data/oracle/data/group_501\’;alter database rename file \’+DATA/hisdb/onlinelog/group_2.262.1034787531\’ to \’/data/oracle/data/group_201\’;alter database rename file \’+DATA/hisdb/onlinelog/group_1.270.1034787531\’ to \’/data/oracle/data/group_101\’;alter database rename file \’+DATA/hisdb/onlinelog/group_3.269.1034787679\’ to \’/data/oracle/data/group_301\’;alter database rename file \’+DATA/hisdb/onlinelog/group_4.257.1034787679\’ to \’/data/oracle/data/group_401\’;alter database rename file \’+DATA/hisdb/onlinelog/group_7.272.1034787679\’ to \’/data/oracle/data/group_701\’;alter database rename file \’+DATA/hisdb/onlinelog/group_8.261.1034787679\’ to \’/data/oracle/data/group_801\’;alter database rename file \’+DATA/hisdb/onlinelog/group_21.344.1042904185\’ to \’/data/oracle/data/group_2101\’;alter database rename file \’+DATA/hisdb/onlinelog/group_22.345.1042904185\’ to \’/data/oracle/data/group_2201\’;alter database rename file \’+DATA/hisdb/onlinelog/group_23.346.1042904185\’ to \’/data/oracle/data/group_2301\’;alter database rename file \’+DATA/hisdb/onlinelog/group_24.347.1042904187\’ to \’/data/oracle/data/group_2401\’;alter database rename file \’+DATA/hisdb/onlinelog/group_25.348.1042904187\’ to \’/data/oracle/data/group_2501\’;alter database rename file \’+DATA/hisdb/onlinelog/group_31.349.1042904199\’ to \’/data/oracle/data/group_3101\’;alter database rename file \’+DATA/hisdb/onlinelog/group_32.350.1042904199\’ to \’/data/oracle/data/group_3201\’;alter database rename file \’+DATA/hisdb/onlinelog/group_33.351.1042904199\’ to \’/data/oracle/data/group_3301\’;alter database rename file \’+DATA/hisdb/onlinelog/group_34.352.1042904199\’ to \’/data/oracle/data/group_3401\’;alter database rename file \’+DATA/hisdb/onlinelog/group_35.353.1042904201\’ to \’/data/oracle/data/group_3501\’;
五、启动数据库
1、打开数据库
RMAN> alter database open resetlogs;database opened
2、查看 redo log 信息，删除无效日志组（节点2日志）
SQL> select THREAD#, STATUS, ENABLED from v$thread; THREAD# STATUS ENABLED———- —— ——– 1 OPEN PUBLIC 2 CLOSED PUBLICSQL> select group# from v$log where THREAD#=2; GROUP#———- 3 4 7 8/*alter database drop logfile group 3;alter database drop logfile group 4;alter database drop logfile group 7;alter database drop logfile group 8;*/SQL> alter database disable thread 2;Database altered.SQL> alter database drop logfile group 3； 2 SQL> alter database drop logfile group 3;Database altered.SQL> alter database drop logfile group 4;Database altered.SQL> alter database drop logfile group 7;Database altered.SQL> alter database drop logfile group 8;Database altered.SQL> select THREAD#, STATUS, ENABLED from v$thread; THREAD# STATUS ENABLED———- —— ——– 1 OPEN PUBLICSQL> select group#,member from v$logfile; GROUP# MEMBER——————————————————————————– 6 /data/oracle/data/group_601 5 /data/oracle/data/group_501 2 /data/oracle/data/group_201 1 /data/oracle/data/group_101SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC———- ———- ———- ———- ———- ———- —STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME—————- ————- ——— ———— ——— 1 1 5 104857600 5121 NOCURRENT 3.4711E+10 31-JUL-22 2.8147E+14 2 1 2 104857600 5121 YESINACTIVE 3.4711E+10 31-JUL-22 3.4711E+10 31-JUL-22 5 1 3 104857600 5121 YESINACTIVE 3.4711E+10 31-JUL-22 3.4711E+10 31-JUL-22 6 1 4 104857600 5121 YESINACTIVE 3.4711E+10 31-JUL-22 3.4711E+10 31-JUL-22
3、查看 undo 表空间，并删除节点2的 undo 表空间
SQL> sho parameter undo;NAME TYPE VALUE———————————— ———– ——————————undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS1SQL> SQL> SQL> SQL> select tablespace_name from dba_tablespaces where contents=\’UNDO\’;TABLESPACE_NAME——————————UNDOTBS1UNDOTBS2SQL> drop tablespace UNDOTBS2 including contents and datafiles;Tablespace dropped.
4、创建临时表空间
SQL> select tablespace_name from dba_tablespaces where contents=\’TEMPORARY\’;TABLESPACE_NAME——————————TEMPSQL> create temporary tablespace TEMP1 tempfile \’/data/oracle/oradata/temp01.dbf\’ size 50M;Tablespace created.SQL> alter database default temporary tablespace TEMP1;Database altered.SQL> drop tablespace TEMP including contents and datafiles;Tablespace dropped.
5、重启数据库，OK！！
SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 233861120 bytesFixed Size 2251976 bytesVariable Size 176161592 bytesDatabase Buffers 50331648 bytesRedo Buffers 5115904 bytesDatabase mounted.Database opened.
到此这篇关于把Oracle数据库从RAC集群迁移到单机环境的文章就介绍到这了,更多相关OracleRAC集群迁移到单机环境内容请搜索悠久资源以前的文章或继续浏览下面的相关文章希望大家以后多多支持悠久资源！
