如何把Oracle 数据库从 RAC 集群迁移到单机环境

db_name：hisdb 

SID：hisdb1、hisdb2

IP: 192.168.1.101、192.168.1.102

os：CentOS Linux release 7.3.1611 (Core)

IP: 192.168.1.15

os：CentOS Linux release 7.3.1611 (Core)

安装 Oracle 软件， 不创建实例

SQL> create pfile= '/home/oracle/pfile0728.ora' from spfile;

File created.

[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

[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

[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

#=设置备份参数：备份到磁盘，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 : 0

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 : 0

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 : 0

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 : 0

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

[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'  # 删除此行

*.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' )

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

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>

[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

SQL> alter database mount;

Database altered.

RMAN> report schema;

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

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

RMAN> catalog start with '/data/backup/' ;

RMAN> list backup;

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;

}

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.

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

RMAN> alter database open resetlogs;

database opened

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

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.

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.

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.