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: 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)源数据库的参数文件内容如下:
[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')
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.