首頁 > 軟體

如何把Oracle 資料庫從 RAC 叢集遷移到單機環境

2022-08-01 14:03:51

把 Oracle 資料庫從 RAC 叢集遷移到單機環境

一、系統環境

1、源資料庫

db_name：hisdb  
SID：hisdb1、hisdb2
IP: 192.168.1.101、192.168.1.102
os：CentOS Linux release 7.3.1611 (Core)

2、目標資料庫

IP: 192.168.1.15
os：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 ~]$ 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 檔案傳到目標資料庫的 $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 ~]$ 

# 切換到目標主機
[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、備份源資料庫

（1）建立備份目錄

[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 全備資料庫：

#=設定備份引數：備份到磁碟，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-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
# 備份資料庫 ============================================
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
#= 設定備份檔案格式：===========================================
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

三、目標資料庫的操作

1、修改引數檔案

（1）源資料庫的引數檔案內容如下：

[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
# 以上內容全部刪除
# 建立如下目錄
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'  --修改此行內容如下
*.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 bytes
Fixed Size		    2251976 bytes
Variable Size		  176161592 bytes
Database Buffers	   50331648 bytes
Redo Buffers		    5115904 bytes

3、生成 spfile 檔案，關閉資料庫，然後重新啟動到 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、啟動 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 2022

Copyright (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-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、啟動資料庫到 mount

SQL> alter database mount;
Database altered.

6、檢視控制檔案中的資料檔案與臨時檔案資訊

RMAN> report schema;

四、開始恢復資料庫

1、核對備份檔案

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、恢復資料庫

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';

五、啟動資料庫

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 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 表空間，並刪除節點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、建立臨時表空間

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、重啟資料庫，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.

到此這篇關於把 Oracle 資料庫從 RAC 叢集遷移到單機環境的文章就介紹到這了,更多相關Oracle  RAC 叢集遷移到單機環境內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！


相關文章

  • 德漢姆中學就讀條件 德漢姆中學就讀條件

    <em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011

    2021-06-01 09:32:01

  • 黑科技的輕簡出行，告別「電量焦慮症」：Anker氮化鎵超能充系列 黑科技的輕簡出行，告別「電量焦慮症」：Anker氮化鎵超能充系列

    综合看Anker超能充系列的性价比很高，并且与不仅和iPhone12/苹果<em>Mac</em>Book很配，而且适合多设备充电需求的日常使用或差旅场景，不管是安卓还是Switch同样也能用得上它，希望这次分享能给准备购入充电器的小伙伴们有所

    2021-06-01 09:31:42

  • 吳亦凡廠牌首秀，L4WUDU居然忘詞了，Rapper和明星比還是有差距！ 吳亦凡廠牌首秀，L4WUDU居然忘詞了，Rapper和明星比還是有差距！

    除了L4WUDU与吴亦凡已经多次共事，成为了明面上的厂牌成员，吴亦凡还曾带领20XXCLUB全队参加2020年的一场音乐节，这也是20XXCLUB首次全员合照，王嗣尧Turbo、陈彦希Regi、<em>Mac</em> Ova Seas、林渝植等人全部出场。然而让

    2021-06-01 09:31:34

  • IPFS、Chia、Bzz和ICP挖礦該怎麼選？哪一個更有優勢？ IPFS、Chia、Bzz和ICP挖礦該怎麼選？哪一個更有優勢？

    目前应用IPFS的机构：1 谷歌<em>浏览器</em>支持IPFS分布式协议 2 万维网 (历史档案博物馆)数据库 3 火狐<em>浏览器</em>支持 IPFS分布式协议 4 EOS 等数字货币数据存储 5 美国国会图书馆，历史资料永久保存在 IPFS 6 加

    2021-06-01 09:31:24

  • 有哪些事是買了雪佛蘭後才知道的？美事偷著樂，開拓者車主隨聊 有哪些事是買了雪佛蘭後才知道的？美事偷著樂，開拓者車主隨聊

    开拓者的车机是兼容苹果和<em>安卓</em>，虽然我不怎么用，但确实兼顾了我家人的很多需求：副驾的门板还配有解锁开关，有的时候老婆开车，下车的时候偶尔会忘记解锁，我在副驾驶可以自己开门：第二排设计很好，不仅配置了一个很大的

    2021-06-01 09:30:48

  • iPhone12在618最新定價，跌價幅度超過1400元，還等iPhone13嗎？ iPhone12在618最新定價，跌價幅度超過1400元，還等iPhone13嗎？

    不仅是<em>安卓</em>手机，苹果手机的降价力度也是前所未有了，iPhone12也“跳水价”了，发布价是6799元，如今已经跌至5308元，降价幅度超过1400元，最新定价确认了。iPhone12是苹果首款5G手机，同时也是全球首款5nm芯片的智能机，它

    2021-06-01 09:30:45

IT145.com E-mail:sddin#qq.com