如何把Oracle 数据库从 RAC 集群迁移到单机环境
作者:睿思达DBA_WGX 发布时间:2024-01-28 06:29:05
标签:Oracle,RAC,集群,单机
把 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 5121 NO
CURRENT 3.4711E+10 31-JUL-22 2.8147E+14
2 1 2 104857600 5121 YES
INACTIVE 3.4711E+10 31-JUL-22 3.4711E+10 31-JUL-22
5 1 3 104857600 5121 YES
INACTIVE 3.4711E+10 31-JUL-22 3.4711E+10 31-JUL-22
6 1 4 104857600 5121 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.
来源:https://blog.csdn.net/weixin_44377973/article/details/126087392
0
投稿
猜你喜欢
- PHP如何获取当前页完整URL及其参数 <? echo 'http://'.$_SERVER[&
- 依据null-values,MySQL的值为null的意思只是代表没有数据,null值和某种类型的零值是两码事,比如int类型的零值为0,字
- 前提是已设置ANDROID_HOME环境变量,使用aapt工具获取apk的信息,保存至脚本所在目录下的PackageInfo.txt文件中:
- 这篇文章主要介绍了Python Django 简单分页的实现代码解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习
- 本文为大家分享了Python文本特征抽取与向量化的具体代码,供大家参考,具体内容如下假设我们刚看完诺兰的大片《星际穿越》,设想如何让机器来自
- 本文主要介绍了Jupyter notebook快速入门教程,分享给大家,具体如下:本篇将给大家介绍一款超级好用的工具:Jupyter not
- 在执行程序前,先在MySQL中创建一个数据库"pachong"。import pymysqlimport request
- python遍历字符串中的每一个字符有4种方式:1. for in ;2.iter内置函数;3.内置函数range()或xrange();4
- 首先,创建一个存储过程 get_clob: t_name:要查询的表名;f_name:要查询的字段名;u_id:表的主键,查询条件;l_po
- 1. 前言分形几何是几何数学中的一个分支,也称大自然几何学,由著名数学家本华曼德勃罗( 法语:BenoitB.Mandelbrot)在 19
- rem ---表单提示函数 Being-----------------------------CODE Copy ... Fu
- 相信很多学编程的人都对Vlookup函数不陌生,一些在excel中不方便处理的大量数据,用Python就可以轻松解决。下面介绍openpyx
- 1. 引言当我们设计软件时,我们通常会花费大量精力来编写高质量的代码。但这往往还不够,一个好的软件还应该考虑其整个系统,如测试、部署、网络等
- Java一直标榜一句老话叫“编写一次,到处运行(Write Once,Run Anywhere)”,CSS也差一点点做到了。但就是为了差的一
- #! /usr/bin/python''' File&n
- 1.预备知识python语言,scrapy爬虫基础,json模块,flask后端2.抓取CSDN数据接口使用谷歌抓包工具抓取CSDN搜索引擎
- #写在前面,这个程序我已经弄出来了,但是因为黄牛泛滥以及懒人太多,整个程序的代码就不贴出来了,这里纯粹就是技术交流。只做技术交流、、、、、
- 本文章是建立在已经安装MySQL数据库的前提,默认安装在C:\Program Files (x86)\MySQL,建议在安装时选中Conne
- 远程登录控制台Scrapy附带一个内置的telnet控制台,用于检查和控制Scrapy运行过程。telnet控制台只是一个运行在scrapp
- 为什么要实现分页?在大部分网站中分页的功能都是必要的,尤其是在后台管理中分页更是不可或缺分页能带给用户更好的体验,也能减轻服务器的压力对于分