如何把Oracle?數(shù)據(jù)庫(kù)從?RAC?集群遷移到單機(jī)環(huán)境
更新時(shí)間：2022年08月01日 10:28:59 作者：睿思達(dá)DBA_WGX
這篇文章主要介紹了把?Oracle?數(shù)據(jù)庫(kù)從?RAC?集群遷移到單機(jī)環(huán)境,內(nèi)容包括系統(tǒng)環(huán)境搭建，源數(shù)據(jù)庫(kù)的操作及目標(biāo)數(shù)據(jù)庫(kù)的操作，本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下
把 Oracle 數(shù)據(jù)庫(kù)從 RAC 集群遷移到單機(jī)環(huán)境
一、系統(tǒng)環(huán)境
1、源數(shù)據(jù)庫(kù)
db_name：hisdb SID：hisdb1、hisdb2 IP: 192.168.1.101、192.168.1.102 os：CentOS Linux release 7.3.1611 (Core)
2、目標(biāo)數(shù)據(jù)庫(kù)
IP: 192.168.1.15 os：CentOS Linux release 7.3.1611 (Core) 安裝 Oracle 軟件， 不創(chuàng)建實(shí)例
二、源數(shù)據(jù)庫(kù)的操作
1、創(chuàng)建 pfile 文件
SQL> create pfile='/home/oracle/pfile0728.ora' from spfile; File created.
2、查看生成的 pfile 文件
[oracle@rac1 ~]$ pwd /home/oracle [oracle@rac1 ~]$ ll total 2487204 drwxr-xr-x 2 oracle oinstall 111 Jun 24 21:30 data-bak drwxr-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 文件傳到目標(biāo)數(shù)據(jù)庫(kù)的 $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)? yes Warning: 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 ~]$ # 切換到目標(biāo)主機(jī) [oracle@mysql bin]$ cd ~ [oracle@mysql ~]$ ls db_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、備份源數(shù)據(jù)庫(kù)
（1）創(chuàng)建備份目錄
[root@rac1 ~]# mkdir /arch/bk0729 -p [root@rac1 ~]# chown -R oracle:oinstall /arch/bk0729 [root@rac1 ~]# ll /arch/ 總用量 0 drwxr-xr-x 2 oracle oinstall 6 7月 30 18:58 bk0729
（2）用RMAN 全備數(shù)據(jù)庫(kù)：
#=設(shè)置備份參數(shù)：備份到磁盤，6 個(gè)通道 ====================================== configure device type disk parallelism 6 backup type to backupset; #=設(shè)置備份參數(shù)：設(shè)置備份文件的位置及文件名格式 ================================== configure channel device type disk format '/arch/bk0729/%d_%I_%s_%p_%T.bkp'; # 備份控制文件 ============================================ backup current controlfile format ='/arch/bk0729/control_bak_%s.bak'; # 備份數(shù)據(jù)庫(kù) ============================================ backup as compressed backupset database; # 下面的備份命令可以同時(shí)備份數(shù)據(jù)庫(kù)和控制文件 backup incremental level 0 format '/rmanbackup/orcl_full_%U' database include current controlfile; #= 設(shè)置備份文件格式：=========================================== configure channel device type disk format '/arch/bk0729/ctl_%d_%I_%s_%p_%T.bkp'; #= 備份歸檔日志：=========================================== backup as compressed backupset archivelog all; #=設(shè)置備份參數(shù)：備份到磁盤，6 個(gè)通道 ====================================== 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 #=設(shè)置備份參數(shù)：設(shè)置備份文件的位置及文件名格式 ================================== 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-22 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=31 instance=hisdb1 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=125 instance=hisdb1 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=158 instance=hisdb1 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=159 instance=hisdb1 device type=DISK allocated channel: ORA_DISK_5 channel ORA_DISK_5: SID=162 instance=hisdb1 device type=DISK allocated channel: ORA_DISK_6 channel ORA_DISK_6: SID=36 instance=hisdb1 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 30-JUL-22 channel ORA_DISK_1: finished piece 1 at 30-JUL-22 piece handle=/arch/bk0729/control_bak_32.bak tag=TAG20220730T193424 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 30-JUL-22 # 備份數(shù)據(jù)庫(kù) ============================================ RMAN> backup as compressed backupset database; Starting backup at 30-JUL-22 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 using channel ORA_DISK_5 using channel ORA_DISK_6 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=+DATA/hisdb/datafile/system.278.1107994145 channel ORA_DISK_1: starting piece 1 at 30-JUL-22 channel ORA_DISK_2: starting compressed full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00002 name=+DATA/hisdb/datafile/sysaux.279.1107994147 input datafile file number=00004 name=+DATA/hisdb/datafile/users.270.1107994131 channel ORA_DISK_2: starting piece 1 at 30-JUL-22 channel ORA_DISK_3: starting compressed full datafile backup set channel ORA_DISK_3: specifying datafile(s) in backup set input datafile file number=00003 name=+DATA/hisdb/datafile/undotbs1.271.1107994123 input datafile file number=00006 name=+DATA/hisdb/datafile/ts001.277.1107994139 channel ORA_DISK_3: starting piece 1 at 30-JUL-22 channel ORA_DISK_4: starting compressed full datafile backup set channel ORA_DISK_4: specifying datafile(s) in backup set input datafile file number=00007 name=+DATA/hisdb/datafile/undotbs2.284.1108022905 input datafile file number=00005 name=+DATA/hisdb/datafile/ts001.276.1107994131 channel ORA_DISK_4: starting piece 1 at 30-JUL-22 channel ORA_DISK_5: starting compressed full datafile backup set channel ORA_DISK_5: specifying datafile(s) in backup set channel ORA_DISK_6: starting compressed full datafile backup set channel ORA_DISK_6: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_6: starting piece 1 at 30-JUL-22 channel ORA_DISK_3: finished piece 1 at 30-JUL-22 piece handle=/arch/bk0729/HISDB_2002805648_35_1_20220730.bkp tag=TAG20220730T193500 comment=NONE channel ORA_DISK_3: backup set complete, elapsed time: 00:00:54 channel ORA_DISK_6: finished piece 1 at 30-JUL-22 piece handle=/arch/bk0729/HISDB_2002805648_38_1_20220730.bkp tag=TAG20220730T193500 comment=NONE channel ORA_DISK_6: backup set complete, elapsed time: 00:00:27 channel ORA_DISK_1: finished piece 1 at 30-JUL-22 piece handle=/arch/bk0729/HISDB_2002805648_33_1_20220730.bkp tag=TAG20220730T193500 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26 channel ORA_DISK_2: finished piece 1 at 30-JUL-22 piece handle=/arch/bk0729/HISDB_2002805648_34_1_20220730.bkp tag=TAG20220730T193500 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:01:26 channel ORA_DISK_4: finished piece 1 at 30-JUL-22 piece handle=/arch/bk0729/HISDB_2002805648_36_1_20220730.bkp tag=TAG20220730T193500 comment=NONE channel ORA_DISK_4: backup set complete, elapsed time: 00:00:45 including current control file in backup set channel ORA_DISK_5: starting piece 1 at 30-JUL-22 channel ORA_DISK_5: finished piece 1 at 30-JUL-22 piece handle=/arch/bk0729/HISDB_2002805648_37_1_20220730.bkp tag=TAG20220730T193500 comment=NONE channel ORA_DISK_5: backup set complete, elapsed time: 00:00:01 Finished 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 #= 設(shè)置備份文件格式：=========================================== 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 stored released channel: ORA_DISK_1 released channel: ORA_DISK_2 released channel: ORA_DISK_3 released channel: ORA_DISK_4 released channel: ORA_DISK_5 released channel: ORA_DISK_6 #= 備份歸檔日志：=========================================== RMAN> backup as compressed backupset archivelog all; Starting backup at 30-JUL-22 current log archived using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 using channel ORA_DISK_5 using channel ORA_DISK_6 channel ORA_DISK_1: starting compressed archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=98 RECID=13 STAMP=1111432401 channel ORA_DISK_1: starting piece 1 at 30-JUL-22 channel ORA_DISK_2: starting compressed archived log backup set channel ORA_DISK_2: specifying archived log(s) in backup set input archived log thread=1 sequence=99 RECID=14 STAMP=1111432403 channel ORA_DISK_2: starting piece 1 at 30-JUL-22 channel ORA_DISK_3: starting compressed archived log backup set channel ORA_DISK_3: specifying archived log(s) in backup set input archived log thread=1 sequence=100 RECID=15 STAMP=1111432904 channel ORA_DISK_3: starting piece 1 at 30-JUL-22 channel ORA_DISK_4: starting compressed archived log backup set channel ORA_DISK_4: specifying archived log(s) in backup set input archived log thread=1 sequence=101 RECID=16 STAMP=1111432905 channel ORA_DISK_4: starting piece 1 at 30-JUL-22 channel ORA_DISK_5: starting compressed archived log backup set channel ORA_DISK_5: specifying archived log(s) in backup set input archived log thread=1 sequence=102 RECID=17 STAMP=1111433394 channel ORA_DISK_5: starting piece 1 at 30-JUL-22 channel ORA_DISK_6: starting compressed archived log backup set channel ORA_DISK_6: specifying archived log(s) in backup set input archived log thread=1 sequence=103 RECID=18 STAMP=1111433805 channel ORA_DISK_6: starting piece 1 at 30-JUL-22 channel ORA_DISK_1: finished piece 1 at 30-JUL-22 piece handle=/arch/bk0729/HISDB_2002805648_39_1_20220730.bkp tag=TAG20220730T193645 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:00 channel ORA_DISK_2: finished piece 1 at 30-JUL-22 piece handle=/arch/bk0729/HISDB_2002805648_40_1_20220730.bkp tag=TAG20220730T193645 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:00:00 channel ORA_DISK_3: finished piece 1 at 30-JUL-22 piece handle=/arch/bk0729/HISDB_2002805648_41_1_20220730.bkp tag=TAG20220730T193645 comment=NONE channel ORA_DISK_3: backup set complete, elapsed time: 00:00:00 channel ORA_DISK_4: finished piece 1 at 30-JUL-22 piece handle=/arch/bk0729/HISDB_2002805648_42_1_20220730.bkp tag=TAG20220730T193645 comment=NONE channel ORA_DISK_4: backup set complete, elapsed time: 00:00:00 channel ORA_DISK_5: finished piece 1 at 30-JUL-22 piece handle=/arch/bk0729/HISDB_2002805648_43_1_20220730.bkp tag=TAG20220730T193645 comment=NONE channel ORA_DISK_5: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_6: finished piece 1 at 30-JUL-22 piece handle=/arch/bk0729/HISDB_2002805648_44_1_20220730.bkp tag=TAG20220730T193645 comment=NONE channel ORA_DISK_6: backup set complete, elapsed time: 00:00:01 Finished 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
三、目標(biāo)數(shù)據(jù)庫(kù)的操作
1、修改參數(shù)文件
（1）源數(shù)據(jù)庫(kù)的參數(shù)文件內(nèi)容如下：
[oracle@rac1 ~]$ vi pfile0728.ora hisdb2.__db_cache_size=192937984 hisdb1.__db_cache_size=201326592 hisdb2.__java_pool_size=4194304 hisdb1.__java_pool_size=4194304 hisdb2.__large_pool_size=8388608 hisdb1.__large_pool_size=8388608 hisdb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment hisdb2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment hisdb2.__pga_aggregate_target=222298112 hisdb1.__pga_aggregate_target=222298112 hisdb2.__sga_target=419430400 hisdb1.__sga_target=419430400 hisdb2.__shared_io_pool_size=0 hisdb2.__db_cache_size=192937984 hisdb1.__db_cache_size=201326592 hisdb2.__java_pool_size=4194304 hisdb1.__java_pool_size=4194304 hisdb2.__large_pool_size=8388608 hisdb1.__large_pool_size=8388608 hisdb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment hisdb2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment hisdb2.__pga_aggregate_target=222298112 hisdb1.__pga_aggregate_target=222298112 hisdb2.__sga_target=419430400 hisdb1.__sga_target=419430400 hisdb2.__shared_io_pool_size=0 hisdb1.__shared_io_pool_size=0 hisdb2.__shared_pool_size=201326592 hisdb1.__shared_pool_size=192937984 hisdb2.__streams_pool_size=0 hisdb1.__streams_pool_size=0 # 以上內(nèi)容全部刪除 # 創(chuàng)建如下目錄 mkdir -p /usr/local/oracle/admin/hisdb/adump mkdir -p /usr/local/oracle/controlfile/ mkdir -p /data/oracle/controlfile/ mkdir -p /data/oracle/flash_recovery_area mkdir -p /data/oracle/arch mkdir -p /data/oracle/oradata # *.audit_file_dest='/u01/app/oracle/admin/hisdb/adump' --修改此行內(nèi)容如下 *.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' --修改此行內(nèi)容如下 *.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' --修改此行內(nèi)容如下 *.db_recovery_file_dest='/data/oracle/flash_recovery_area' *.db_recovery_file_dest_size=4102029312 # 此行不變 #*.diagnostic_dest='/u01/app/oracle' --修改此行內(nèi)容如下 *.diagnostic_dest='/usr/local/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=hisdbXDB)' # 此行不變 # hisdb1.instance_number=1 # 刪除此行 # hisdb2.instance_number=2 # 刪除此行 # *.log_archive_dest_1='location=+BAK' --修改此行內(nèi)容如下 *.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）修改后的參數(shù)文件內(nèi)容如下：
*.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、使用修改后的參數(shù)文件啟動(dòng)數(shù)據(jù)庫(kù)到 nomount
SQL> startup nomount pfile='/home/oracle/pfile0729.ora'; ORACLE instance started. Total System Global Area 233861120 bytes Fixed Size 2251976 bytes Variable Size 176161592 bytes Database Buffers 50331648 bytes Redo Buffers 5115904 bytes
3、生成 spfile 文件，關(guān)閉數(shù)據(jù)庫(kù)，然后重新啟動(dòng)到 nomount
SQL> create spfile from pfile='/home/oracle/pfile0729.ora'; File created. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup m SP2-0714: invalid combination of STARTUP options SQL> startup nomount; ORACLE instance started. Total System Global Area 233861120 bytes Fixed Size 2251976 bytes Variable Size 176161592 bytes Database Buffers 50331648 bytes Redo Buffers 5115904 bytes SQL>
4、啟動(dòng) rman，恢復(fù)控制文件
[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 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: HISDB (not mounted) -- 恢復(fù)控制文件 RMAN> restore controlfile from '/data/backup/control_bak_331659.bak'; Starting restore at 31-JUL-22 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=189 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 output file name=/usr/local/oracle/controlfile/control01.ctl output file name=/data/oracle/controlfile/control02.ctl Finished restore at 31-JUL-22
5、啟動(dòng)數(shù)據(jù)庫(kù)到 mount
SQL> alter database mount; Database altered.
6、查看控制文件中的數(shù)據(jù)文件與臨時(shí)文件信息
RMAN> report schema;
四、開始恢復(fù)數(shù)據(jù)庫(kù)
1、核對(duì)備份文件
RMAN> crosscheck backup; using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 using channel ORA_DISK_5 using channel ORA_DISK_6 crosschecked 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=1111356921 Crosschecked 45 objects
2、刪除失效的備份文件
RMAN> delete expired backup; using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 using channel ORA_DISK_5 using channel ORA_DISK_6 List of Backup Pieces BP 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 piece backup 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=1111356921 Deleted 45 EXPIRED objects
3、更新備份文件
RMAN> catalog start with '/data/backup/';
4、查看備份片信息
RMAN> list backup;
5、恢復(fù)數(shù)據(jù)庫(kù)
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_3501 18 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';
五、啟動(dòng)數(shù)據(jù)庫(kù)
1、打開數(shù)據(jù)庫(kù)
RMAN> alter database open resetlogs; database opened
2、查看 redo log 信息，刪除無(wú)效日志組（節(jié)點(diǎn)2日志）
SQL> select THREAD#, STATUS, ENABLED from v$thread;
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED PUBLIC
SQL> 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 PUBLIC
SQL> 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_101
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 5 104857600 512 1 NO
CURRENT 3.4711E+10 31-JUL-22 2.8147E+14
2 1 2 104857600 512 1 YES
INACTIVE 3.4711E+10 31-JUL-22 3.4711E+10 31-JUL-22
5 1 3 104857600 512 1 YES
INACTIVE 3.4711E+10 31-JUL-22 3.4711E+10 31-JUL-22
6 1 4 104857600 512 1 YES
INACTIVE 3.4711E+10 31-JUL-22 3.4711E+10 31-JUL-22
3、查看 undo 表空間，并刪除節(jié)點(diǎn)2的 undo 表空間
SQL> sho parameter undo; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> SQL> SQL> SQL> select tablespace_name from dba_tablespaces where contents='UNDO'; TABLESPACE_NAME ------------------------------ UNDOTBS1 UNDOTBS2 SQL> drop tablespace UNDOTBS2 including contents and datafiles; Tablespace dropped.
4、創(chuàng)建臨時(shí)表空間
SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY'; TABLESPACE_NAME ------------------------------ TEMP SQL> 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、重啟數(shù)據(jù)庫(kù)，OK??！
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 233861120 bytes Fixed Size 2251976 bytes Variable Size 176161592 bytes Database Buffers 50331648 bytes Redo Buffers 5115904 bytes Database mounted. Database opened.
到此這篇關(guān)于把 Oracle 數(shù)據(jù)庫(kù)從 RAC 集群遷移到單機(jī)環(huán)境的文章就介紹到這了,更多相關(guān)Oracle RAC 集群遷移到單機(jī)環(huán)境內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:
- oracle與gbase8s遷移數(shù)據(jù)類型對(duì)照以及舉例說(shuō)明
- Oracle數(shù)據(jù)遷移MySQL的三種簡(jiǎn)單方法
- Oracle通過(guò)LogMiner實(shí)現(xiàn)數(shù)據(jù)同步遷移
- Oracle 10g DG 數(shù)據(jù)文件遷移的實(shí)現(xiàn)
- 如何將Oracle的一個(gè)大數(shù)據(jù)表快速遷移到 Sqlserver2008數(shù)據(jù)庫(kù)(圖文教程)
- oracle數(shù)據(jù)遷移到db2數(shù)據(jù)庫(kù)的實(shí)現(xiàn)方法(分享)
- oracle數(shù)據(jù)庫(kù)遷移到MySQL的方法總結(jié)
- 直接拷貝數(shù)據(jù)文件實(shí)現(xiàn)Oracle數(shù)據(jù)遷移
- Oracle數(shù)據(jù)庫(kù)遷移方案
- Oracle數(shù)據(jù)庫(kù)遷移所有文件到新掛載磁盤路徑
相關(guān)文章
to_date()是Oracle數(shù)據(jù)庫(kù)函數(shù)的代表函數(shù)之一，下文對(duì)Oracle to_date()函數(shù)的幾種用法作了詳細(xì)的介紹說(shuō)明,需要的朋友可以參考下2014-08-08
oracle行轉(zhuǎn)列與列轉(zhuǎn)行的幾種方式匯總
最近項(xiàng)目需要進(jìn)行行轉(zhuǎn)列,經(jīng)過(guò)上網(wǎng)查找到了一些解決方法,分享給大家,這篇文章主要給大家介紹了關(guān)于oracle行轉(zhuǎn)列與列轉(zhuǎn)行的幾種方式,文中通過(guò)圖文以及實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-05-05
oracle誤刪數(shù)據(jù)恢復(fù)方法小結(jié)
最近幫客戶維護(hù)一個(gè)數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)中的數(shù)據(jù)被他誤刪了，下面就為大家分享下我是通過(guò)什么方法來(lái)實(shí)現(xiàn)的2015-08-08
Oracle Database 10g數(shù)據(jù)庫(kù)安裝及配置教程
這篇文章主要介紹了Oracle Database 10g Release 2數(shù)據(jù)庫(kù)安裝及配置教程，感興趣的小伙伴們可以參考一下2016-08-08
oracle—SQL技巧之(二)WMSYS.WM_CONCAT函數(shù)實(shí)現(xiàn)多行記錄用逗號(hào)拼接在一起
由于業(yè)務(wù)系統(tǒng)的交易記錄有很多，常常有些主管需要看到所有的記錄情況；又不想滾動(dòng)；接下來(lái)介紹使用Oracle自帶的函數(shù) WMSYS.WM_CONCAT，進(jìn)行拼接，感興趣的朋友可以了解下2013-01-01
快速查出Oracle數(shù)據(jù)庫(kù)中鎖等待的方法
這篇文章主要介紹了快速查出Oracle數(shù)據(jù)庫(kù)中鎖等待的方法,在實(shí)際開發(fā)中很有應(yīng)用價(jià)值,需要的朋友可以參考下2014-08-08
oracle幫助文檔下載方便學(xué)習(xí)oracle的朋友門，大量的問(wèn)答式解決方法2008-05-05
自動(dòng)備份Oracle數(shù)據(jù)庫(kù)
自動(dòng)備份Oracle數(shù)據(jù)庫(kù)...2007-03-03
安裝了Oracle數(shù)據(jù)庫(kù)，以前都是安裝好就可以連接了，可是今天出現(xiàn)了一點(diǎn)小意外,遇到ORA-12514錯(cuò)誤問(wèn)題，今天就來(lái)介紹一下解決方法，感興趣的可以了解一下2023-05-05
Oracle數(shù)據(jù)庫(kù)常用語(yǔ)句總結(jié)大全
SQL是結(jié)構(gòu)化查詢語(yǔ)言,用于管理關(guān)系型數(shù)據(jù)庫(kù)系統(tǒng),本指南涵蓋了創(chuàng)建和刪除數(shù)據(jù)庫(kù)、表以及其他高級(jí)表操作如增加、修改、刪除字段,還介紹了主鍵、索引、視圖操作和數(shù)據(jù)DML操作,需要的朋友可以參考下2024-09-09