关键步骤：
1、移动硬盘格式化成Linux可以识别的文件系统，mount到备份目录，开始rman备份，备份完成后，插到目标服务器挂载，
2、恢复参数文件nomount库，恢复控制文件mount库，restore renew数据文件路径，recover 库 ,关库，升级， 清理redo， 删除多余undo。
一.迁移背景
由于源库RAC(数据库版本11.2.0.3)共享存储服役时长10年之久，磁盘空间仅剩余约50G，不能进行空间扩容，为保障业务正常运行，准备迁移至新的单点服务器(数据库版本11.2.0.4)上。
二.迁移环境
源库：RAC 11.2.0.3
目标库：单点11.2.0.4
可以停业务，停业务时间：72小时
三.迁移前准备
3.1.业务准备
1、客户方：下发公告通知停业务时间、时长。
2、系统应用方：统计业务服务器，进行业务服务停止。
3、数据库方：停止监听、锁定用户
3.2.源库
3.2.1.停止业务
先发通知，停监听，锁用户
3.2.1.1.停止监听
规避有新的应用连接进入数据库，进而生成新的数据。
操作之前确认源端关闭监听，断开所有连接，确保操作期间没有脏数据产生
停止监听已经连接的不会断，只会让新连接的会话进不来
--查看集群中监听部分状态
......
[grid@racdb01:/home/grid]$crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
.....
ora.LISTENER.lsnr
ONLINE ONLINE racdb01
ONLINE ONLINE racdb02
......
--停止监听
srvctl stop listener --2个节点的监听都会停止
--查看集群中监听部分状态
[grid@racdb01:/home/grid]$crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
......
ora.LISTENER.lsnr
OFFLINE OFFLINE racdb01
OFFLINE OFFLINE racdb02
...... 3.2.1.2.业务会话确定终止
尽量根据计算机名和应用系统保障方确认，进行服务停止，能应用停止就不kill会话。
查询正在执行的SQL
ELECT b.sid oracleID,
b.username 登录Oracle用户名,
b.serial#,
spid 操作系统ID,
paddr,
sql_text 正在执行的SQL,
b.machine 计算机名
FROM v$session b
left join v$process a on a.addr = b.paddr
left join v$sqlarea c on b.sql_hash_value = c.hash_value
WHERE b.type != 'BACKGROUND';
或
SELECT b.sid oracleID,
b.username 登录Oracle用户名,
b.serial#,
a.spid 操作系统ID,
b.paddr,
c.sql_text 正在执行的SQL,
b.machine 计算机名
FROM v$session b
left join v$process a on a.addr = b.paddr
left join v$sqlarea c on b.sql_hash_value = c.hash_value
WHERE b.type != 'BACKGROUND' ;
--杀会话
alter system kill session 'sid,serial#' immediate; （根据v$session中查出sid和serial#进行替换）3.2.2.查看 rac 环境及创建测试表
如果是生产或开发环境，创建测试表步骤忽略。本文档是虚拟机模拟的实验环境。
确认源端字符集，rman异机恢复要求数据库名要一致，实例名和数据库名保持一致。
3.2.2.1./etc/hosts文件
[grid@racdb01:/home/grid]$cat cat /etc/hosts
cat: cat: No such file or directory
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.40.135 racdb01
192.168.40.145 racdb02
192.168.183.135 racdb01_privatevip
192.168.183.145 racdb02_privatevip
192.168.40.13 racdb01_vitureip
192.168.40.14 racdb02_vitureip
192.168.40.100 racdbscan01 ##安装时注意集群名不要超过 15 个字符，也不能有大写主机名。
3.2.2.2.查看网卡信息
[grid@racdb01:/home/grid]$ifconfig
ens32: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.40.135 netmask 255.255.255.0 broadcast 192.168.40.255
inet6 fe80::fda3:e7dd:7d49:47eb prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:55:73:f9 txqueuelen 1000 (Ethernet)
RX packets 1039 bytes 166469 (162.5 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 666 bytes 87762 (85.7 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
ens32:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.40.100 netmask 255.255.255.0 broadcast 192.168.40.255
ether 00:0c:29:55:73:f9 txqueuelen 1000 (Ethernet)
ens32:2: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.40.13 netmask 255.255.255.0 broadcast 192.168.40.255
ether 00:0c:29:55:73:f9 txqueuelen 1000 (Ethernet)
ens34: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.183.135 netmask 255.255.255.0 broadcast 192.168.183.255
inet6 fe80::ed72:3adc:be54:87d6 prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:55:73:03 txqueuelen 1000 (Ethernet)
RX packets 54791 bytes 39489204 (37.6 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 76484 bytes 68924643 (65.7 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
ens34:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 169.254.169.55 netmask 255.255.0.0 broadcast 169.254.255.255
ether 00:0c:29:55:73:03 txqueuelen 1000 (Ethernet)
3.2.2.3.查看RAC集群运行状态
[grid@racdb01:/home/grid]$crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE racdb01
ONLINE ONLINE racdb02
ora.LISTENER.lsnr
OFFLINE OFFLINE racdb01
OFFLINE OFFLINE racdb02
ora.OCR.dg
ONLINE ONLINE racdb01
ONLINE ONLINE racdb02
ora.asm
ONLINE ONLINE racdb01 Started
ONLINE ONLINE racdb02 Started
ora.gsd
OFFLINE OFFLINE racdb01
OFFLINE OFFLINE racdb02
ora.net1.network
ONLINE ONLINE racdb01
ONLINE ONLINE racdb02
ora.ons
ONLINE ONLINE racdb01
ONLINE ONLINE racdb02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE racdb01
ora.cvu
1 ONLINE ONLINE racdb01
ora.oc4j
1 ONLINE ONLINE racdb01
ora.racdb.db
1 ONLINE ONLINE racdb01 Open
2 ONLINE ONLINE racdb02 Open
ora.racdb01.vip
1 ONLINE ONLINE racdb01
ora.racdb02.vip
1 ONLINE ONLINE racdb02
ora.scan1.vip
1 ONLINE ONLINE racdb01
3.2.2.4.查数据库版本
[oracle@racdb01:/home/oracle]$sqlplus -V
SQL*Plus: Release 11.2.0.3.0 Production3.2.2.5.查看集群参数
注意db_name db_unique_name instance_name service_names的区别和不同之处。
rman异机恢复要求数据库名要一致，实例名和数据库名保持一致
--查看数据库是否为Real Application Clusters (RAC)
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
_clusterwide_global_transactions boolean FALSE
cluster_database boolean TRUE #true：数据库是RAC
cluster_database_instances integer 2 #集群有2个实例
cluster_interconnects string
--查看节点1信息
SQL> show parameter name
db_file_name_convert string
db_name string racdb
db_unique_name string racdb
global_names boolean FALSE
instance_name string racdb1
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string racdb
--查看字符集
set linesize 999
col value for a60
select * from nls_database_parameters where PARAMETER in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
PARAMETER VALUE
------------------------------------------------------------ ------------------------------------------------------------
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
select userenv('language') from dual;
USERENV('LANGUAGE')
--------------------------------------------------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF83.2.2.6.查看归档模式是否开启
--查看归档模式是否开启
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled #已开启
Archive destination +DATA #归档文件存放位置+DATA
Oldest online log sequence 12
Next log sequence to archive 13
Current log sequence 133.2.2.7.创建测试数据(可选)
-- 查看表空间及数据文件位置及大小
set lin 1000 pagesize 999
col file_name for a55
select tablespace_name,file_name,bytes/1024/1024/1024 gb,AUTOEXTENSIBLE from dba_data_files order by tablespace_name;
TABLESPACE_NAME FILE_NAME GB AUT
------------------------------ ------------------------------------------------------- ---------- ---
SYSAUX +DATA/racdb/datafile/sysaux.270.1170000589 .537109375 YES
SYSTEM +DATA/racdb/datafile/system.269.1170000589 .693359375 YES
UNDOTBS1 +DATA/racdb/datafile/undotbs1.271.1170000589 .073242188 YES
UNDOTBS2 +DATA/racdb/datafile/undotbs2.280.1170000713 .024414063 YES
USERS +DATA/racdb/datafile/users.272.1170000589 .004882813 YES
--创建表空间，大小5G，开启自动扩展
create tablespace ENTSERVICE datafile '+DATA' size 1M autoextend on NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
-- 根据需要添加一定数量的数据文件(可选)
alter tablespace ENTSERVICE add datafile '+DATA' size 1M autoextend on NEXT 1M MAXSIZE UNLIMITED;
--查看临时表空间及临时表空间数据文件位置及大小
set lin 1000
col FILE_NAME for a60
select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024/1024 total_gb,USER_BYTES/1021/1024/1024 gb from dba_temp_files;
TABLESPACE_NAME FILE_NAME TOTAL_GB GB
------------------------------ ------------------------------------------------------------ ---------- ----------
TEMP +DATA/racdb/tempfile/temp.279.1170000665 .029296875 .028403526
--创建临时表空间
CREATE TEMPORARY TABLESPACE ENTSERVICETEMP TEMPFILE '+DATA'
SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;
--创建用户
create user ENTSERVICE identified by entservice1234 default tablespace ENTSERVICE TEMPORARY TABLESPACE ENTSERVICETEMP ACCOUNT UNLOCK;
--赋予权限
grant dba,connect,resource to ENTSERVICE with admin option;
注意：密码最长30位
--创建表
CREATE TABLE ENTSERVICE.CUST
(
CUST_ID NUMBER,
LAST_NAME VARCHAR2(30),
FIRST_NAME VARCHAR2(30)
) ;
--生成测试数据
DECLARE
v_cust_id NUMBER;
v_last_name VARCHAR2(30);
v_first_name VARCHAR(30);
BEGIN
FOR i IN 1..1000 LOOP
v_cust_id := i;
v_last_name := 'Last' || TO_CHAR(i, 'FM000');
v_first_name := 'First' || TO_CHAR(i, 'FM000');
INSERT into ENTSERVICE.cust (cust_id, last_name, first_name)
VALUES (v_cust_id, v_last_name, v_first_name);
END LOOP;
COMMIT;
END;
/
--查询数据
select count(*) from ENTSERVICE.CUST;
COUNT(*)
----------
1000
--执行检查点
SQL> alter system checkpoint;3.2.2.8.查询所有数据库文件
--查询所有数据库文件
set line 9999 pagesize 9999
col FILE_NAME format a60
select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile
union all
select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile
union all
select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile
union all
select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile;
FILE_TYPE FILE# FILE_NAME STATUS ENABLED
----------- ---------- ------------------------------------------------------------ ------- ----------
datafile 1 +DATA/racdb/datafile/system.269.1170000589 SYSTEM READ WRITE
datafile 2 +DATA/racdb/datafile/sysaux.270.1170000589 ONLINE READ WRITE
datafile 3 +DATA/racdb/datafile/undotbs1.271.1170000589 ONLINE READ WRITE
datafile 4 +DATA/racdb/datafile/users.272.1170000589 ONLINE READ WRITE
datafile 5 +DATA/racdb/datafile/undotbs2.280.1170000713 ONLINE READ WRITE
datafile 6 +DATA/racdb/datafile/entservice.303.1170422443 ONLINE READ WRITE
datafile 7 +DATA/racdb/datafile/entservice.304.1170422455 ONLINE READ WRITE
tempfile 1 +DATA/racdb/tempfile/temp.279.1170000665 ONLINE READ WRITE
tempfile 2 +DATA/racdb/tempfile/entservicetemp.305.1170422495 ONLINE READ WRITE
logfile 2 +DATA/racdb/onlinelog/group_2.277.1170000665
logfile 2 +DATA/racdb/onlinelog/group_2.278.1170000665
logfile 1 +DATA/racdb/onlinelog/group_1.275.1170000663
logfile 1 +DATA/racdb/onlinelog/group_1.276.1170000665
logfile 3 +DATA/racdb/onlinelog/group_3.281.1170000777
logfile 3 +DATA/racdb/onlinelog/group_3.282.1170000777
logfile 4 +DATA/racdb/onlinelog/group_4.283.1170000777
logfile 4 +DATA/racdb/onlinelog/group_4.284.1170000777
controlfile +DATA/racdb/controlfile/current.274.1170000661
controlfile +DATA/racdb/controlfile/current.273.1170000661
19 rows selected.
3.2.2.9.查业务数据
--查看数据量
select sum(bytes)/1024/1024/1024 as "size(G)" from dba_data_files;
size(G)
----------
1.33496094
select round(sum(bytes) / 1024 / 1024 / 1024, 2) || 'G'
from dba_segments;
ROUND(SUM(BYTES)/1024/1024/1024,2)||'G'
----------------------------------------------------------------------------------
1.26G
----查看用户
set lin1000 pagesize 999
select username,default_tablespace,temporary_tablespace from dba_users where username not in ('SYS','SYSTEM','HR','OUTLN','MGMT_VIEW','FLOWS_FILES','MDSYS','ORDSYS','EXFSYS','DBSNMP','WMSYS','APPQOSSYS','APEX_030200','OWBSYS_AUDIT','ORDDATA','CTXSYS','ANONYMOUS','SYSMAN','XDB','ORDPLUGINS','OWBSYS','SI_INFORMTN_SCHEMA','OLAPSYS','SCOTT','ORACLE_OCM','XS$NULL','BI','PM','MDDATA','IX','SH','DIP','OE','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR');
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
ENTSERVICE ENTSERVICE ENTSERVICETEMP
--每个业务用户下的总对象数量校验
SELECT D.OWNER,COUNT(1)
FROM dba_objects d
WHERE d.OWNER in ('ENTSERVICE')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner)
GROUP BY D.OWNER
ORDER BY D.OWNER ;
OWNER COUNT(1)
------------------------------------------------------------ ----------
ENTSERVICE 1
--每个业务用户下的各个对象类别的数量校验
set pagesize 999
select OWNER,OBJECT_TYPE,status,count(OBJECT_NAME) from dba_objects where owner in ('ENTSERVICE') group by OBJECT_TYPE,owner,status order by 1,3,2;
OWNER OBJECT_TYPE STATUS COUNT(OBJECT_NAME)
------------------------------------------------------------ -------------------------------------- -------------- ------------------
ENTSERVICE TABLE VALID 1
--查询回收站lob对象数量(可选),lob数量不一致排查
select owner,type,count(*) from dba_recyclebin group by owner,type order by owner;
--数据量统计
----新增count_rows 函数
CREATE OR REPLACE FUNCTION count_rows (
table_name IN varchar2,
owner IN varchar2 := NULL
)
RETURN number AUTHID current_user
AS
num_rows number;
stmt varchar2(2000);
BEGIN
IF owner IS NULL THEN
stmt := 'select count(*) from "' || table_name || '"';
ELSE
stmt := 'select count(*) from "' || owner || '"."' || table_name || '"';
END IF;
EXECUTE IMMEDIATE stmt INTO num_rows;
RETURN num_rows;
END;
/
---调用count_rows 函数，统计各种表的记录数
select table_name, count_rows(table_name) nrows from user_tables where tablespace_name='ENTSERVICE'
order by nrows desc; 3.2.3.创建备份目录
在磁盘剩余空间较大的目录下创建备份目录，避免磁盘空间耗尽备份未完成。
su - root
df -h
mkdir -p /backup
chown -R oracle:oinstall /backup3.2.5.生成备份文件
3.2.5.1.查看全库备份脚本内容
maxpiecesize不要大于30G，通常是20G或者30G。限制指定通道的每个备份片的最大容量，会小于上限值。
maxpiecesize和section size 不能同时用。
sql 'alter system archive log current'; 集群和单点通用
--查看全库备份脚本内容
cat /backup/rman_bak.sh
#!/bin/bash
source /home/oracle/.bash_profile
export NLS_DATE_FORMAT='YYYYMMDD hh24:mi:ss'
rq=`date +%Y%m%d`
bakdir=/backup/${rq}
autobak=$bakdir/autobackup
if [ ! -d ${bakdir} ];
then mkdir -p ${bakdir}
fi
if [ ! -d ${autobak} ];
then mkdir -p ${autobak}
fi
cd $ORACLE_HOME/bin
./rman log $bakdir/rman${rq}.log target / <<EOF
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ;
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
allocate channel c8 type disk;
sql 'alter system archive log current';
backup as compressed backupset incremental level 0 database section size 20g format '${autobak}/orcl_full_%U_%d_%T_%s';
backup as compressed backupset archivelog all section size 20g format '${autobak}/orcl_arc_%U_%d_%T_%s';
BACKUP CURRENT CONTROLFILE format '${autobak}/orcl_ctl_bk_%U_%d_%T' ;
backup spfile format '${autobak}/orcl_spfile_bk_%U_%d_%T' ;
sql 'alter system archive log current';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
}
exit;
EOF
参数说明：
或
backup as compressed backupset archivelog all format '/u01/rman/arch_%d_%U.bak';
sysdate-1/12 :备份2小时以内的归档。 可以进行更改
%U_%d_%T_%s
%U：默认是%u_%p_%c的简写形式，利用它可以为每一个备份片段（即磁盘文件）生成一个唯一名称，这是最常用的命名方式，执行不同备份操作时，生成的规则也不同，如下所示：
生成备份片段时，%U=%u_%p_%c；
%u：是一个由备份集编号和建立时间压缩后组成的8字符名称。利用%u可以为每个备份集生成一个唯一的名称。
%p：备份集中备份片段的编号，从1开始。
%c：备份片段的复制数（从1开始编号，最大不超过256）。
%d：Oracle数据库名称。
%T：当前时间的年月日格式（YYYYMMDD）。
%s：备份集号。3.2.5.2.执行全库备份脚本
执行全库备份脚本 必须后台运行脚本，避免会话端口备份终止
--源库上查看空间,将备份文件存放到剩余空间较大的目录，避免备份过程中空间不足中断
df -h
--执行全库备份脚本 必须后台运行脚本，避免会话端口备份终止
[oracle@racdb01:/backup]$ nohup sh rman_bak.sh &3.2.5.3.查看备份执行过程
[oracle@racdb01:/backup/20240531]$cat rman20240531.log
Recovery Manager: Release 11.2.0.3.0 - Production on Fri May 31 13:30:04 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=1167617429)
RMAN>
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
RMAN>
new RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
new RMAN configuration parameters are successfully stored
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24>
allocated channel: c1
channel c1: SID=51 instance=racdb1 device type=DISK
allocated channel: c2
channel c2: SID=35 instance=racdb1 device type=DISK
allocated channel: c3
channel c3: SID=63 instance=racdb1 device type=DISK
allocated channel: c4
channel c4: SID=64 instance=racdb1 device type=DISK
allocated channel: c5
channel c5: SID=65 instance=racdb1 device type=DISK
allocated channel: c6
channel c6: SID=66 instance=racdb1 device type=DISK
allocated channel: c7
channel c7: SID=67 instance=racdb1 device type=DISK
allocated channel: c8
channel c8: SID=68 instance=racdb1 device type=DISK
sql statement: alter system archive log current
Starting backup at 20240531 13:30:18
channel c1: starting compressed incremental level 0 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/racdb/datafile/system.269.1170000589
channel c1: starting piece 1 at 20240531 13:30:18
channel c2: starting compressed incremental level 0 datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/racdb/datafile/sysaux.270.1170000589
channel c2: starting piece 1 at 20240531 13:30:19
channel c3: starting compressed incremental level 0 datafile backup set
channel c3: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/racdb/datafile/undotbs1.271.1170000589
channel c3: starting piece 1 at 20240531 13:30:19
channel c4: starting compressed incremental level 0 datafile backup set
channel c4: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/racdb/datafile/undotbs2.280.1170000713
channel c4: starting piece 1 at 20240531 13:30:19
channel c5: starting compressed incremental level 0 datafile backup set
channel c5: specifying datafile(s) in backup set
channel c6: starting compressed incremental level 0 datafile backup set
channel c6: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA/racdb/datafile/users.272.1170000589
channel c6: starting piece 1 at 20240531 13:30:19
channel c7: starting compressed incremental level 0 datafile backup set
channel c7: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/racdb/datafile/entservice.303.1170422443
channel c7: starting piece 1 at 20240531 13:30:19
channel c8: starting compressed incremental level 0 datafile backup set
channel c8: specifying datafile(s) in backup set
input datafile file number=00007 name=+DATA/racdb/datafile/entservice.304.1170422455
channel c8: starting piece 1 at 20240531 13:30:20
channel c4: finished piece 1 at 20240531 13:30:21
piece handle=/backup/20240531/autobackup/orcl_full_042s6f7b_1_1_RACDB_20240531_4 tag=TAG20240531T133018 comment=NONE
channel c4: backup set complete, elapsed time: 00:00:04
channel c4: starting compressed incremental level 0 datafile backup set
channel c4: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c4: starting piece 1 at 20240531 13:30:28
channel c6: finished piece 1 at 20240531 13:30:29
piece handle=/backup/20240531/autobackup/orcl_full_062s6f7b_1_1_RACDB_20240531_6 tag=TAG20240531T133018 comment=NONE
channel c6: backup set complete, elapsed time: 00:00:10
channel c8: finished piece 1 at 20240531 13:30:29
piece handle=/backup/20240531/autobackup/orcl_full_082s6f7b_1_1_RACDB_20240531_8 tag=TAG20240531T133018 comment=NONE
channel c8: backup set complete, elapsed time: 00:00:09
channel c3: finished piece 1 at 20240531 13:30:29
piece handle=/backup/20240531/autobackup/orcl_full_032s6f7b_1_1_RACDB_20240531_3 tag=TAG20240531T133018 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:10
channel c4: finished piece 1 at 20240531 13:30:29
piece handle=/backup/20240531/autobackup/orcl_full_092s6f7j_1_1_RACDB_20240531_9 tag=TAG20240531T133018 comment=NONE
channel c4: backup set complete, elapsed time: 00:00:01
including current control file in backup set
channel c5: starting piece 1 at 20240531 13:30:29
channel c7: finished piece 1 at 20240531 13:30:29
piece handle=/backup/20240531/autobackup/orcl_full_072s6f7b_1_1_RACDB_20240531_7 tag=TAG20240531T133018 comment=NONE
channel c7: backup set complete, elapsed time: 00:00:10
channel c5: finished piece 1 at 20240531 13:30:32
piece handle=/backup/20240531/autobackup/orcl_full_052s6f7b_1_1_RACDB_20240531_5 tag=TAG20240531T133018 comment=NONE
channel c5: backup set complete, elapsed time: 00:00:03
channel c2: finished piece 1 at 20240531 13:30:44
piece handle=/backup/20240531/autobackup/orcl_full_022s6f7b_1_1_RACDB_20240531_2 tag=TAG20240531T133018 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:25
channel c1: finished piece 1 at 20240531 13:30:54
piece handle=/backup/20240531/autobackup/orcl_full_012s6f7a_1_1_RACDB_20240531_1 tag=TAG20240531T133018 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:36
Finished backup at 20240531 13:30:54
Starting backup at 20240531 13:30:56
current log archived
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=2 STAMP=1170000830
input archived log thread=2 sequence=1 RECID=1 STAMP=1170000778
input archived log thread=2 sequence=2 RECID=5 STAMP=1170001356
channel c1: starting piece 1 at 20240531 13:31:01
channel c2: starting compressed archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=3 STAMP=1170000833
input archived log thread=1 sequence=7 RECID=4 STAMP=1170001308
input archived log thread=2 sequence=3 RECID=7 STAMP=1170003583
channel c2: starting piece 1 at 20240531 13:31:01
channel c3: starting compressed archived log backup set
channel c3: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=6 STAMP=1170003582
input archived log thread=2 sequence=4 RECID=8 STAMP=1170003583
input archived log thread=1 sequence=9 RECID=9 STAMP=1170080613
channel c3: starting piece 1 at 20240531 13:31:01
channel c4: starting compressed archived log backup set
channel c4: specifying archived log(s) in backup set
input archived log thread=2 sequence=5 RECID=10 STAMP=1170080613
input archived log thread=2 sequence=6 RECID=11 STAMP=1170080614
input archived log thread=1 sequence=10 RECID=12 STAMP=1170250021
channel c4: starting piece 1 at 20240531 13:31:01
channel c5: starting compressed archived log backup set
channel c5: specifying archived log(s) in backup set
input archived log thread=2 sequence=7 RECID=13 STAMP=1170250022
input archived log thread=2 sequence=8 RECID=14 STAMP=1170250023
input archived log thread=1 sequence=11 RECID=15 STAMP=1170273073
channel c5: starting piece 1 at 20240531 13:31:01
channel c6: starting compressed archived log backup set
channel c6: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=16 STAMP=1170415962
input archived log thread=2 sequence=9 RECID=17 STAMP=1170415962
input archived log thread=2 sequence=10 RECID=18 STAMP=1170415962
channel c6: starting piece 1 at 20240531 13:31:01
channel c7: starting compressed archived log backup set
channel c7: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=20 STAMP=1170423009
input archived log thread=2 sequence=11 RECID=19 STAMP=1170423008
channel c7: starting piece 1 at 20240531 13:31:01
channel c8: starting compressed archived log backup set
channel c8: specifying archived log(s) in backup set
input archived log thread=2 sequence=12 RECID=22 STAMP=1170423056
input archived log thread=1 sequence=14 RECID=21 STAMP=1170423056
channel c8: starting piece 1 at 20240531 13:31:01
channel c1: finished piece 1 at 20240531 13:31:01
piece handle=/backup/20240531/autobackup/orcl_arc_0a2s6f8l_1_1_RACDB_20240531_10 tag=TAG20240531T133100 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:00
channel c2: finished piece 1 at 20240531 13:31:01
piece handle=/backup/20240531/autobackup/orcl_arc_0b2s6f8l_1_1_RACDB_20240531_11 tag=TAG20240531T133100 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:00
channel c3: finished piece 1 at 20240531 13:31:02
piece handle=/backup/20240531/autobackup/orcl_arc_0c2s6f8l_1_1_RACDB_20240531_12 tag=TAG20240531T133100 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:01
channel c4: finished piece 1 at 20240531 13:31:02
piece handle=/backup/20240531/autobackup/orcl_arc_0d2s6f8l_1_1_RACDB_20240531_13 tag=TAG20240531T133100 comment=NONE
channel c4: backup set complete, elapsed time: 00:00:01
channel c5: finished piece 1 at 20240531 13:31:02
piece handle=/backup/20240531/autobackup/orcl_arc_0e2s6f8l_1_1_RACDB_20240531_14 tag=TAG20240531T133100 comment=NONE
channel c5: backup set complete, elapsed time: 00:00:01
channel c6: finished piece 1 at 20240531 13:31:02
piece handle=/backup/20240531/autobackup/orcl_arc_0f2s6f8l_1_1_RACDB_20240531_15 tag=TAG20240531T133100 comment=NONE
channel c6: backup set complete, elapsed time: 00:00:01
channel c7: finished piece 1 at 20240531 13:31:02
piece handle=/backup/20240531/autobackup/orcl_arc_0g2s6f8l_1_1_RACDB_20240531_16 tag=TAG20240531T133100 comment=NONE
channel c7: backup set complete, elapsed time: 00:00:01
channel c8: finished piece 1 at 20240531 13:31:02
piece handle=/backup/20240531/autobackup/orcl_arc_0h2s6f8l_1_1_RACDB_20240531_17 tag=TAG20240531T133100 comment=NONE
channel c8: backup set complete, elapsed time: 00:00:01
Finished backup at 20240531 13:31:02
Starting backup at 20240531 13:31:03
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 20240531 13:31:04
channel c1: finished piece 1 at 20240531 13:31:05
piece handle=/backup/20240531/autobackup/orcl_ctl_bk_0i2s6f8n_1_1_RACDB_20240531 tag=TAG20240531T133103 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 20240531 13:31:05
Starting backup at 20240531 13:31:05
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 20240531 13:31:05
channel c1: finished piece 1 at 20240531 13:31:06
piece handle=/backup/20240531/autobackup/orcl_spfile_bk_0j2s6f8p_1_1_RACDB_20240531 tag=TAG20240531T133105 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 20240531 13:31:06
sql statement: alter system archive log current
released channel: c1
released channel: c2
released channel: c3
released channel: c4
released channel: c5
released channel: c6
released channel: c7
released channel: c8
RMAN>
Recovery Manager complete.
3.2.5.4.查看备份文件
[oracle@racdb01:/backup/20240531]$cd autobackup/
[oracle@racdb01:/backup/20240531/autobackup]$ls -lhtr
total 309M
-rw-r----- 1 oracle asmadmin 104K May 31 13:30 orcl_full_082s6f7b_1_1_RACDB_20240531_8
-rw-r----- 1 oracle asmadmin 1.2M May 31 13:30 orcl_full_042s6f7b_1_1_RACDB_20240531_4
-rw-r----- 1 oracle asmadmin 1.1M May 31 13:30 orcl_full_062s6f7b_1_1_RACDB_20240531_6
-rw-r----- 1 oracle asmadmin 176K May 31 13:30 orcl_full_072s6f7b_1_1_RACDB_20240531_7
-rw-r----- 1 oracle asmadmin 1.3M May 31 13:30 orcl_full_032s6f7b_1_1_RACDB_20240531_3
-rw-r----- 1 oracle asmadmin 96K May 31 13:30 orcl_full_092s6f7j_1_1_RACDB_20240531_9
-rw-r----- 1 oracle asmadmin 1.2M May 31 13:30 orcl_full_052s6f7b_1_1_RACDB_20240531_5
-rw-r----- 1 oracle asmadmin 76M May 31 13:30 orcl_full_022s6f7b_1_1_RACDB_20240531_2
-rw-r----- 1 oracle asmadmin 195M May 31 13:30 orcl_full_012s6f7a_1_1_RACDB_20240531_1
-rw-r----- 1 oracle asmadmin 785K May 31 13:31 orcl_arc_0a2s6f8l_1_1_RACDB_20240531_10
-rw-r----- 1 oracle asmadmin 593K May 31 13:31 orcl_arc_0b2s6f8l_1_1_RACDB_20240531_11
-rw-r----- 1 oracle asmadmin 5.0K May 31 13:31 orcl_arc_0h2s6f8l_1_1_RACDB_20240531_17
-rw-r----- 1 oracle asmadmin 139K May 31 13:31 orcl_arc_0f2s6f8l_1_1_RACDB_20240531_15
-rw-r----- 1 oracle asmadmin 1.4M May 31 13:31 orcl_arc_0d2s6f8l_1_1_RACDB_20240531_13
-rw-r----- 1 oracle asmadmin 1.7M May 31 13:31 orcl_arc_0c2s6f8l_1_1_RACDB_20240531_12
-rw-r----- 1 oracle asmadmin 2.8M May 31 13:31 orcl_arc_0g2s6f8l_1_1_RACDB_20240531_16
-rw-r----- 1 oracle asmadmin 8.4M May 31 13:31 orcl_arc_0e2s6f8l_1_1_RACDB_20240531_14
-rw-r----- 1 oracle asmadmin 18M May 31 13:31 orcl_ctl_bk_0i2s6f8n_1_1_RACDB_20240531
-rw-r----- 1 oracle asmadmin 96K May 31 13:31 orcl_spfile_bk_0j2s6f8p_1_1_RACDB_202405313.2.5.5.备份密码文件
[oracle@racdb01:/home/oracle]$cd $ORACLE_HOME/dbs
[oracle@racdb01:/oracle/app/oracle/product/11.2.0/db_1/dbs]$ls -l
total 18080
-rw-rw---- 1 oracle asmadmin 1544 May 31 11:33 hc_racdb1.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 37 May 26 16:13 initracdb1.ora
-rw-r----- 1 oracle oinstall 1536 May 26 16:11 orapwracdb1
-rw-r----- 1 oracle asmadmin 18497536 May 31 13:31 snapcf_racdb1.f
[oracle@racdb01:/oracle/app/oracle/product/11.2.0/db_1/dbs]$cp orapwracdb1 /backup/3.2.6.将备份传递到 target 库
可以采用 ftp 上传下载，也可以采用 NFS 网络文件系统，或者 scp 命令都可以，本文档采用 scp 直接传递 。
注意：如果源端服务器和目标服务器不在同一个网段，可以对目标库服务器再添加一块网卡，将新添加的网卡配置成同网段。或者直接找负责网络的人员实现跨网段访问。
--目标库上查看空间,将备份文件存放到剩余空间较大的目录，避免传输过程中空间不足中断
df -h
--源库上操作
su - oracle
scp -r /backup oracle@192.168.40.52:/home/oracle/ 3.3.目标库
3.3.1.安装数据库软件
默认目标库已经安装好了同源库一样的数据库版本。
若已创建实例，需按以下步骤进行闭库，删除：
--关闭数据库
shutdown immediate
--静默删除数据库实例
dbca -silent -deleteDatabase -sourcedb racdb -sid racdb3.3.2.查数据库版本
[oracle@11g-db oradata]$ sqlplus -V
SQL*Plus: Release 11.2.0.4.0 Production四.迁移过程
4.1.生成密码文件
su - oracle
--复制密码文件
cd /home/oracle/backup/
cp orapwracdb1 $ORACLE_HOME/dbs/
--重命名密码文件
cd $ORACLE_HOME/dbs
mv orapwracdb1 orapwracdb4.2.生成spfile文件
参数文件是RAC的，要改成符合单实例数据库
4.2.1.还原备份中的spfile文件
startup nomount;后$ORACLE_HOME/dbs下生成hc_orcl.dat
restore spfile后$ORACLE_HOME/dbs下生成spfileorcl.ora
su - oracle
export ORACLE_SID=racdb
rman target /
run
{
startup nomount;
restore spfile from '/home/oracle/backup/20240531/autobackup/orcl_spfile_bk_0j2s6f8p_1_1_RACDB_20240531';
}
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db/dbs/initracdb.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 281019272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5517312 bytes
Starting restore at 31-MAY-24
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/backup/20240531/autobackup/orcl_spfile_bk_0j2s6f8p_1_1_RACDB_20240531
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 31-MAY-24
4.2.2.生成pfile文件
su - oracle
export ORACLE_SID=racdb
sqlplus / as sysdba
create pfile='/home/oracle/initracdb20240601.ora' from spfile;查看pfile文件发现，本文档源库RAC未开启闪回恢复功能，故没有闪回恢复相关参数；使用的是ASMM内存管理。
4.2.3.备份并查看pfile文件内容
--备份pfile文件
[oracle@11g-db ~]$ cp initracdb20240601.ora initracdb20240601.ora_bak_20240601
--查看pfile文件内容
[oracle@dxj:/home/oracle]$ cat initracdb20240601.ora
racdb2.__db_cache_size=536870912
racdb1.__db_cache_size=520093696
racdb1.__java_pool_size=16777216
racdb2.__java_pool_size=16777216
racdb1.__large_pool_size=16777216
racdb2.__large_pool_size=16777216
racdb1.__pga_aggregate_target=419430400
racdb2.__pga_aggregate_target=419430400
racdb1.__sga_target=1241513984
racdb2.__sga_target=1241513984
racdb1.__shared_io_pool_size=0
racdb2.__shared_io_pool_size=0
racdb2.__shared_pool_size=654311424
racdb1.__shared_pool_size=671088640
racdb1.__streams_pool_size=0
racdb2.__streams_pool_size=0
*._b_tree_bitmap_plans=FALSE
*._cleanup_rollback_entries=2000
*._clusterwide_global_transactions=FALSE
*._datafile_write_errors_crash_instance=FALSE
*._gc_policy_time=0
*._gc_undo_affinity=FALSE
*._index_partition_large_extents='FALSE'
*._memory_imm_mode_without_autosga=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_null_aware_antijoin=FALSE
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='/oracle/app/oracle/admin/racdb/adump'
*.audit_trail='NONE'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_file_record_keep_time=31
*.control_files='+DATA/racdb/controlfile/current.274.1170000661','+DATA/racdb/controlfile/current.273.1170000661'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_files=5000
*.db_name='racdb'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=2147483648
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
*.enable_ddl_logging=TRUE
*.event='28401 trace name context forever,level 1','10949 trace name context forever,level 1'
racdb2.instance_number=2
racdb1.instance_number=1
*.log_archive_dest_1='LOCATION=+DATA'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=1000
*.parallel_force_local=TRUE
*.parallel_max_servers=64
*.pga_aggregate_target=411041792
*.processes=3000
*.remote_listener='racdbscan01:1521'
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.resource_manager_plan='force:'
*.sec_case_sensitive_logon=FALSE
*.session_cached_cursors=300
*.sessions=4544
*.sga_target=1235222528
racdb2.thread=2
racdb1.thread=1
*.undo_retention=10800
racdb1.undo_tablespace='UNDOTBS1'
racdb2.undo_tablespace='UNDOTBS2'4.2.4.修改后pfile文件内容
修改生成 pfile 中的文件路径和文件名
在目标主机创建参数文件，将其中cluster的参数删掉，修改完之后，比原来的参数内容精简了很多，保留或修改如下参数：
[oracle@dxj:/home/oracle]$ more initracdb20240601.ora
*._b_tree_bitmap_plans=FALSE
*._cleanup_rollback_entries=2000
*._datafile_write_errors_crash_instance=FALSE
*._gc_policy_time=0
*._gc_undo_affinity=FALSE
*._index_partition_large_extents='FALSE'
*._memory_imm_mode_without_autosga=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_null_aware_antijoin=FALSE
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='/u01/app/oracle/admin/racdb/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.0.0'
*.control_file_record_keep_time=31
*.control_files='/oradata/racdb/control01.ctl','/oradata/racdb/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oradata/racdb'
*.db_domain=''
*.db_files=5000
*.db_name='racdb'
*.db_recovery_file_dest='/oradata/racdb'
*.db_recovery_file_dest_size=2147483648
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
*.enable_ddl_logging=TRUE
*.event='28401 trace name context forever,level 1','10949 trace name context forever,level 1'
*.log_archive_dest_1='LOCATION=/oradata/racdb'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=1000
*.parallel_force_local=TRUE
*.parallel_max_servers=64
*.pga_aggregate_target=411041792
*.processes=3000
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.resource_manager_plan='force:'
*.sec_case_sensitive_logon=FALSE
*.session_cached_cursors=300
*.sessions=4544
*.sga_target=1235222528
*.undo_retention=10800
:%s#+DATA/racdb/controlfile#/oradata/racdb#g
:%s#+DATA#/oradata/racdb#g内存修改可参考如下公式：
--os_memory_total=$(awk '/MemTotal/{print $2}' /proc/meminfo)
--$sga_target=os_memory_total * 8 * 8 / 100 / 1024
--pga_target=os_memory_total * 8 * 2 / 100 / 1024
也可以后面恢复后open库修改sga和pga参数
4.2.5.创建相关路径
su - oracle
mkdir -p /u01/app/oracle/admin/racdb/adump4.2.6.生成spfile文件
--关库
su - oracle
export ORACLE_SID=racdb
sqlplus / as sysdba
shutdown immediate
--生成spfile文件
create spfile from pfile='/home/oracle/initracdb20240601.ora';
exit
--查看生成的spfile文件
[oracle@dxj:/u01/app/oracle/product/11.2.0/db/dbs]$ ls -l
total 40
-rw-rw----. 1 oracle oinstall 1544 May 26 18:57 hc_dxj.dat
-rw-rw---- 1 oracle oinstall 1544 Jun 1 14:20 hc_racdb.dat
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 24 May 26 18:54 lkDXJ
-rw-r-----. 1 oracle oinstall 1536 May 26 18:54 orapwdxj
-rw-r----- 1 oracle oinstall 1536 May 31 13:44 orapwracdb
-rw-r-----. 1 oracle oinstall 4608 Jun 1 14:06 spfiledxj.ora
-rw-r----- 1 oracle oinstall 4608 Jun 1 14:23 spfileracdb.ora
4.3.用spfile文件启动到 nomount 状态
--启动到 nomount 状态
su - oracle
export ORACLE_SID=racdb
sqlplus / as sysdba
startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 281021520 bytes
Database Buffers 549453824 bytes
Redo Buffers 2371584 bytes
--看一眼，确认数据库是用spfile启动的，而不是用pfile启动的
idle 01-JUN-24> set linesize 999
idle 01-JUN-24> show parameter pfile;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db/dbs/spfileracdb.ora
补充：如果startup nomount提示如下报错，则需要增加initracdb20240601.ora中sga_target参数值
idle 01-JUN-24> startup nomount;
ORA-00821: Specified value of sga_target 1248M is too small, needs to be at least 1760M4.4.还原控制文件并启库到mount 状态
su - oracle
export ORACLE_SID=racdb
rman target /
run{
restore controlfile from '/home/oracle/backup/20240531/autobackup/orcl_ctl_bk_0i2s6f8n_1_1_RACDB_20240531';
sql 'alter database mount';
}
Starting restore at 01-JUN-24
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/racdb/control01.ctl
output file name=/oradata/racdb/control02.ctl
Finished restore at 01-JUN-24
sql statement: alter database mount
released channel: ORA_DISK_1
控制文件已经还原，注意此处控制文件的还原路径是 spfile 中指定的路径和控制文件名。
4.5.还原数据库
4.5.1.查看备份集
在目标端查看一下是否有备份的信息
4.5.1.1.查看数据文件的备份集
RMAN> list backupset summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1 B 0 A DISK 31-MAY-24 1 1 YES TAG20240531T133018
2 B 0 A DISK 31-MAY-24 1 1 YES TAG20240531T133018
3 B 0 A DISK 31-MAY-24 1 1 YES TAG20240531T133018
4 B 0 A DISK 31-MAY-24 1 1 YES TAG20240531T133018
5 B 0 A DISK 31-MAY-24 1 1 YES TAG20240531T133018
6 B 0 A DISK 31-MAY-24 1 1 YES TAG20240531T133018
7 B 0 A DISK 31-MAY-24 1 1 YES TAG20240531T133018
8 B 0 A DISK 31-MAY-24 1 1 YES TAG20240531T133018
9 B 0 A DISK 31-MAY-24 1 1 YES TAG20240531T133018
10 B A A DISK 31-MAY-24 1 1 YES TAG20240531T133100
11 B A A DISK 31-MAY-24 1 1 YES TAG20240531T133100
12 B A A DISK 31-MAY-24 1 1 YES TAG20240531T133100
13 B A A DISK 31-MAY-24 1 1 YES TAG20240531T133100
14 B A A DISK 31-MAY-24 1 1 YES TAG20240531T133100
15 B A A DISK 31-MAY-24 1 1 YES TAG20240531T133100
16 B A A DISK 31-MAY-24 1 1 YES TAG20240531T133100
17 B A A DISK 31-MAY-24 1 1 YES TAG20240531T133100
4.5.1.2.查看归档文件的备份集
RMAN> list backupset of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10 784.50K DISK 00:00:00 31-MAY-24
BP Key: 10 Status: AVAILABLE Compressed: YES Tag: TAG20240531T133100
Piece Name: /backup/20240531/autobackup/orcl_arc_0a2s6f8l_1_1_RACDB_20240531_10
List of Archived Logs in backup set 10
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 5 1040451 26-MAY-24 1041105 26-MAY-24
2 1 1040793 26-MAY-24 1040932 26-MAY-24
2 2 1041103 26-MAY-24 1044771 26-MAY-24
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
11 592.50K DISK 00:00:00 31-MAY-24
BP Key: 11 Status: AVAILABLE Compressed: YES Tag: TAG20240531T133100
Piece Name: /backup/20240531/autobackup/orcl_arc_0b2s6f8l_1_1_RACDB_20240531_11
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 1041105 26-MAY-24 1041107 26-MAY-24
1 7 1041107 26-MAY-24 1044754 26-MAY-24
2 3 1044771 26-MAY-24 1071036 26-MAY-24
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
12 4.50K DISK 00:00:00 31-MAY-24
BP Key: 12 Status: AVAILABLE Compressed: YES Tag: TAG20240531T133100
Piece Name: /backup/20240531/autobackup/orcl_arc_0h2s6f8l_1_1_RACDB_20240531_17
List of Archived Logs in backup set 12
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 14 1187938 31-MAY-24 1190100 31-MAY-24
2 12 1187934 31-MAY-24 1190104 31-MAY-24
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
13 138.00K DISK 00:00:00 31-MAY-24
BP Key: 13 Status: AVAILABLE Compressed: YES Tag: TAG20240531T133100
Piece Name: /backup/20240531/autobackup/orcl_arc_0f2s6f8l_1_1_RACDB_20240531_15
List of Archived Logs in backup set 13
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 12 1149454 29-MAY-24 1171043 31-MAY-24
2 9 1149886 29-MAY-24 1171041 31-MAY-24
2 10 1171041 31-MAY-24 1171049 31-MAY-24
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
14 1.36M DISK 00:00:01 31-MAY-24
BP Key: 14 Status: AVAILABLE Compressed: YES Tag: TAG20240531T133100
Piece Name: /backup/20240531/autobackup/orcl_arc_0d2s6f8l_1_1_RACDB_20240531_13
List of Archived Logs in backup set 14
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 10 1100304 27-MAY-24 1121926 29-MAY-24
2 5 1071383 26-MAY-24 1100303 27-MAY-24
2 6 1100303 27-MAY-24 1100310 27-MAY-24
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
15 1.67M DISK 00:00:01 31-MAY-24
BP Key: 15 Status: AVAILABLE Compressed: YES Tag: TAG20240531T133100
Piece Name: /backup/20240531/autobackup/orcl_arc_0c2s6f8l_1_1_RACDB_20240531_12
List of Archived Logs in backup set 15
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 8 1044929 26-MAY-24 1071037 26-MAY-24
1 9 1071037 26-MAY-24 1100304 27-MAY-24
2 4 1071036 26-MAY-24 1071189 26-MAY-24
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
16 2.72M DISK 00:00:01 31-MAY-24
BP Key: 16 Status: AVAILABLE Compressed: YES Tag: TAG20240531T133100
Piece Name: /backup/20240531/autobackup/orcl_arc_0g2s6f8l_1_1_RACDB_20240531_16
List of Archived Logs in backup set 16
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 13 1171043 31-MAY-24 1187938 31-MAY-24
2 11 1171495 31-MAY-24 1187934 31-MAY-24
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
17 8.34M DISK 00:00:01 31-MAY-24
BP Key: 17 Status: AVAILABLE Compressed: YES Tag: TAG20240531T133100
Piece Name: /backup/20240531/autobackup/orcl_arc_0e2s6f8l_1_1_RACDB_20240531_14
List of Archived Logs in backup set 17
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 11 1121926 29-MAY-24 1149454 29-MAY-24
2 7 1100756 27-MAY-24 1121924 29-MAY-24
2 8 1121924 29-MAY-24 1121932 29-MAY-24
4.5.2.还原数据文件
4.5.2.1.数据文件转换脚本
由于RAC下使用的是OMF路径，所以先用脚本查询出RAC环境中的数据文件名以及路径
sqlplus 执行
数据文件的转换，由于原 rac 库是 asm 存储的，所以到新环境需要采用 set newname 来转换一下
可通过RMAN重命名数据文件、临时文件、日志文件，进行还原
--数据文件转换脚本
set pagesize 200 linesize 200
select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";'
from v$datafile a
union all
select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";'
from v$tempfile a
union all
SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''' ||
a.MEMBER || ''''' ";'
FROM v$logfile a;
--输出结果
'SETNEWNAMEFORDATAFILE'||A.FILE#||'TO"'||A.NAME||'";'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
set newname for datafile 1 to "+DATA/racdb/datafile/system.269.1170000589";
set newname for datafile 2 to "+DATA/racdb/datafile/sysaux.270.1170000589";
set newname for datafile 3 to "+DATA/racdb/datafile/undotbs1.271.1170000589";
set newname for datafile 4 to "+DATA/racdb/datafile/users.272.1170000589";
set newname for datafile 5 to "+DATA/racdb/datafile/undotbs2.280.1170000713";
set newname for datafile 6 to "+DATA/racdb/datafile/entservice.303.1170422443";
set newname for datafile 7 to "+DATA/racdb/datafile/entservice.304.1170422455";
set newname for tempfile 1 to "+DATA/racdb/tempfile/temp.279.1170000665";
set newname for tempfile 2 to "+DATA/racdb/tempfile/entservicetemp.305.1170422495";
SQL "ALTER DATABASE RENAME FILE ''+DATA/racdb/onlinelog/group_2.277.1170000665'' to ''+DATA/racdb/onlinelog/group_2.277.1170000665'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/racdb/onlinelog/group_2.278.1170000665'' to ''+DATA/racdb/onlinelog/group_2.278.1170000665'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/racdb/onlinelog/group_1.275.1170000663'' to ''+DATA/racdb/onlinelog/group_1.275.1170000663'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/racdb/onlinelog/group_1.276.1170000665'' to ''+DATA/racdb/onlinelog/group_1.276.1170000665'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/racdb/onlinelog/group_3.281.1170000777'' to ''+DATA/racdb/onlinelog/group_3.281.1170000777'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/racdb/onlinelog/group_3.282.1170000777'' to ''+DATA/racdb/onlinelog/group_3.282.1170000777'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/racdb/onlinelog/group_4.283.1170000777'' to ''+DATA/racdb/onlinelog/group_4.283.1170000777'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/racdb/onlinelog/group_4.284.1170000777'' to ''+DATA/racdb/onlinelog/group_4.284.1170000777'' ";
17 rows selected.
4.5.2.2.将备份目录注册到控制文件(必须)
由于源端备份目录和目标端存放备份目录不一样，因此需要在目标端把备份目录注册到控制文件，不然还原数据文件调用的是控制文件中的源端备份目录，进而还原时因为找不到文件而报错。
目录后面一定要加/，不然报错。
rman target /
catalog start with '/home/oracle/backup/20240531/autobackup/';
......
Do you really want to catalog the above files (enter YES or NO)? yes4.5.2.3.还原数据文件
注意，对switch的说明：
对于nocatalog 模式下，rman备份的信息是保存在控制文件里的，包括文件的路径信息。 这里的switch的作用，就是更新控制文件里的信息。
restore 的时候不会对temp 表空间进行restore。所以等restore 之后，我们需要手工创建temp表空间。
不过在这个测试里，我们还是对tempfile 进行了指定。 但是这个操作只更新控制文件，不恢复数据文件。
数据文件的转换，由于原 rac 库是 asm 存储的，所以到新环境需要采用 set newname 来转换一下
run
{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
set newname for datafile 1 to "/oradata/racdb/system.269.1170000589";
set newname for datafile 2 to "/oradata/racdb/sysaux.270.1170000589";
set newname for datafile 3 to "/oradata/racdb/undotbs1.271.1170000589";
set newname for datafile 4 to "/oradata/racdb/users.272.1170000589";
set newname for datafile 5 to "/oradata/racdb/undotbs2.280.1170000713";
set newname for datafile 6 to "/oradata/racdb/entservice.303.1170422443";
set newname for datafile 7 to "/oradata/racdb/entservice.304.1170422455";
set newname for tempfile 1 to "/oradata/racdb/temp.279.1170000665";
set newname for tempfile 2 to "/oradata/racdb/entservicetemp.305.1170422495";
SQL "ALTER DATABASE RENAME FILE ''+DATA/racdb/onlinelog/group_2.277.1170000665'' to ''/oradata/racdb/redo_2.277.1170000665'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/racdb/onlinelog/group_2.278.1170000665'' to ''/oradata/racdb/redo_2.278.1170000665'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/racdb/onlinelog/group_1.275.1170000663'' to ''/oradata/racdb/redo_1.275.1170000663'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/racdb/onlinelog/group_1.276.1170000665'' to ''/oradata/racdb/redo_1.276.1170000665'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/racdb/onlinelog/group_3.281.1170000777'' to ''/oradata/racdb/redo_3.281.1170000777'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/racdb/onlinelog/group_3.282.1170000777'' to ''/oradata/racdb/redo_3.282.1170000777'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/racdb/onlinelog/group_4.283.1170000777'' to ''/oradata/racdb/redo_4.283.1170000777'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/racdb/onlinelog/group_4.284.1170000777'' to ''/oradata/racdb/redo_4.284.1170000777'' ";
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
}
run
{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
set newname for database to '/u01/app/oracle/oradata%b';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
}或
--数据文件
select 'set newname for datafile '||chr(39)||name||chr(39)||' to '||chr(39)||replace(name,'+DG0/db/datafile/','/home/oracle/data2/')||chr(39)||';' from v$datafile;
--临时文件
select 'set newname for datafile '||chr(39)||name||chr(39)||' to '||chr(39)||replace(name,'+DG0/db/datafile/','/home/oracle/data2/')||chr(39)||';' from v$tempfile;
--日志文件
select 'alter database rename file '||chr(39)||member||chr(39)||' to '||chr(39)||replace(member,'+DG0/db/onlinelog/','/home/oracle/data2/')||chr(39)||';' from v$logfile;
4.5.2.3.验证转换后的数据文件
--查看转换后的数据文件
set pagesize 200 linesize 200
col name for a60
select a.FILE#,a.NAME from v$datafile a
union all
select b.FILE#,b.NAME from v$tempfile b
union all
SELECT 1,a.MEMBER FROM v$logfile a;
FILE# NAME
---------- ------------------------------------------------------------
1 /oradata/racdb/system.269.1170000589
2 /oradata/racdb/sysaux.270.1170000589
3 /oradata/racdb/undotbs1.271.1170000589
4 /oradata/racdb/users.272.1170000589
5 /oradata/racdb/undotbs2.280.1170000713
6 /oradata/racdb/entservice.303.1170422443
7 /oradata/racdb/entservice.304.1170422455
1 /oradata/racdb/temp.279.1170000665
2 /oradata/racdb/entservicetemp.305.1170422495
1 /oradata/racdb/redo_2.277.1170000665
1 /oradata/racdb/redo_2.278.1170000665
1 /oradata/racdb/redo_1.275.1170000663
1 /oradata/racdb/redo_1.276.1170000665
1 /oradata/racdb/redo_3.281.1170000777
1 /oradata/racdb/redo_3.282.1170000777
1 /oradata/racdb/redo_4.283.1170000777
1 /oradata/racdb/redo_4.284.1170000777
17 rows selected.
4.6.恢复数据库
由前边的备份集中可以看出，备份集中的 thread 1 的最大日志号为 14，thread 2 的最大日志号为 12，所以不完全恢复如下：
--恢复数据库
RMAN> RUN
{
recover database;
}
--恢复数据库过程
RMAN> recover database ;
Starting recover at 01-JUN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=12
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=14
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/20240531/autobackup/orcl_arc_0h2s6f8l_1_1_RACDB_20240531_17
channel ORA_DISK_1: piece handle=/home/oracle/backup/20240531/autobackup/orcl_arc_0h2s6f8l_1_1_RACDB_20240531_17 tag=TAG20240531T133100
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/oradata/racdb/1_14_1170000663.dbf thread=1 sequence=14
archived log file name=/oradata/racdb/2_12_1170000663.dbf thread=2 sequence=12
unable to find archived log
archived log thread=1 sequence=15
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/01/2024 15:09:40
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 15 and starting SCN of 1190100
4.7.升级数据库
startup upgrade会自动open库
4.7.1.关库
--关库
SQL> shutdown immediate4.7.2.执行升级
--执行升级
idle 01-JUN-24> startup upgrade;
ORACLE instance started.
Total System Global Area 2288205824 bytes
Fixed Size 2255312 bytes
Variable Size 1778386480 bytes
Database Buffers 503316480 bytes
Redo Buffers 4247552 bytes
Database mounted.
Database opened.4.7.3.执行脚本
不要多窗口并行执行脚本，会引发死锁，该过程比较耗时。
sql>@?/rdbms/admin/catalog.sql --这个脚本执行很快就结束了
sql>@?/rdbms/admin/catproc.sql --这个脚本执行大概3-5分钟
sql>@?/rdbms/admin/catupgrd.sql --这个脚本执行时间比较久，脚本执行结束会shutdown immediate;脚本说明：
主要用于创建数据字典视图。其中，脚本catalog.sql 和 catproc.sql 是创建数据库后必须要运行的两个脚本。
catalog.sql 创建系统常用的数据字典视图和同义词
catproc.sql 运行服务器端所需要的PL/SQL脚本
(1) catalog.sql
该脚本创建相对于系统基表的视图和系统动态性能视图以及他们的同义词。该脚本又同时运行创建其他对象的脚本，主要有：
基本PL/SQL环境，包括PL/SQL的声明：
- 数据类型
- 预定义例外
- 内建的过程和函数
- SQL操作等
- 审计
- 导入/导出
- SQL*Loader
- 安装选项
（2）catproc.sql
该脚本主要用于建立PL/SQL功能的使用环境。此外，还创建几个PL/SQL包用于扩展RDBMS功能。该脚本同时还为下面的一些RDBMS功能创建另外的一些包和视图：
-警告（Alerts）
-管道（Pipes）
-日志分析（LogMiner）
-大对象（Large objects）
-对象（Objects）
- 高级队列（Advanced queuing）
-复制选项（ Replication option）
-其他的一些内建包和选项（Other built-ins and options）
(3) catupgrd.sql
随着Oracle版本的升级，某些对象的属性需要改变，而这些改变操作都将体现在升级脚本catupgrd.sql中
4.8.验证数据
4.8.1.查看实例参数
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string racdb
db_unique_name string racdb
global_names boolean FALSE
instance_name string racdb
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string racdb4.8.2.查看归档
idle 01-JUN-24> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/racdb
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
4.8.3.查看所有数据文件
文件路径已转换
--查看所有数据文件
su - oracle
export ORACLE_SID=racdb
sqlplus / as sysdba
set line 9999 pagesize 9999
col FILE_NAME format a60
select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile
union all
select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile
union all
select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile
union all
select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile;
FILE_TYPE FILE# FILE_NAME STATUS ENABLED
----------- ---------- ------------------------------------------------------------ ------- ----------
datafile 1 /oradata/racdb/system.269.1170000589 SYSTEM READ WRITE
datafile 2 /oradata/racdb/sysaux.270.1170000589 ONLINE READ WRITE
datafile 3 /oradata/racdb/undotbs1.271.1170000589 ONLINE READ WRITE
datafile 4 /oradata/racdb/users.272.1170000589 ONLINE READ WRITE
datafile 5 /oradata/racdb/undotbs2.280.1170000713 ONLINE READ WRITE
datafile 6 /oradata/racdb/entservice.303.1170422443 ONLINE READ WRITE
datafile 7 /oradata/racdb/entservice.304.1170422455 ONLINE READ WRITE
tempfile 1 /oradata/racdb/temp.279.1170000665 ONLINE READ WRITE
tempfile 2 /oradata/racdb/entservicetemp.305.1170422495 ONLINE READ WRITE
logfile 2 /oradata/racdb/group_2.277.1170000665
logfile 2 /oradata/racdb/group_2.278.1170000665
logfile 1 /oradata/racdb/group_1.275.1170000663
logfile 1 /oradata/racdb/group_1.276.1170000665
logfile 3 /oradata/racdb/group_3.281.1170000777
logfile 3 /oradata/racdb/group_3.282.1170000777
logfile 4 /oradata/racdb/group_4.283.1170000777
logfile 4 /oradata/racdb/group_4.284.1170000777
controlfile /oradata/racdb/control01.ctl
controlfile /oradata/racdb/control02.ctl
19 rows selected.
4.8.4.业务数据验证
--查看数据量
select sum(bytes)/1024/1024/1024 as "size(G)" from dba_data_files;
size(G)
----------
1.33496094
select round(sum(bytes) / 1024 / 1024 / 1024, 2) || 'G'
from dba_segments;
ROUND(SUM(BYTES)/1024/1024/1024,2)||'G'
-----------------------------------------
1.26G
--查看用户
set lin1000 pagesize 999
select username,default_tablespace,temporary_tablespace from dba_users where username not in ('SYS','SYSTEM','HR','OUTLN','MGMT_VIEW','FLOWS_FILES','MDSYS','ORDSYS','EXFSYS','DBSNMP','WMSYS','APPQOSSYS','APEX_030200','OWBSYS_AUDIT','ORDDATA','CTXSYS','ANONYMOUS','SYSMAN','XDB','ORDPLUGINS','OWBSYS','SI_INFORMTN_SCHEMA','OLAPSYS','SCOTT','ORACLE_OCM','XS$NULL','BI','PM','MDDATA','IX','SH','DIP','OE','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR');
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
ENTSERVICE ENTSERVICE ENTSERVICETEMP
--每个业务用户下的总对象数量校验
SELECT D.OWNER,COUNT(1)
FROM dba_objects d
WHERE d.OWNER in ('ENTSERVICE')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner)
GROUP BY D.OWNER
ORDER BY D.OWNER ;
OWNER COUNT(1)
------------------------------ ----------
ENTSERVICE 1
--每个业务用户下的各个对象类别的数量校验
set pagesize 999
select OWNER,OBJECT_TYPE,status,count(OBJECT_NAME) from dba_objects where owner in ('ENTSERVICE') group by OBJECT_TYPE,owner,status order by 1,3,2;
OWNER OBJECT_TYPE STATUS COUNT(OBJECT_NAME)
------------------------------ ------------------- ------- ------------------
ENTSERVICE TABLE VALID 1
--查询回收站lob对象数量(可选),lob数量不一致排查
select owner,type,count(*) from dba_recyclebin group by owner,type order by owner;
--数据量统计
----新增count_rows 函数
CREATE OR REPLACE FUNCTION count_rows (
table_name IN varchar2,
owner IN varchar2 := NULL
)
RETURN number AUTHID current_user
AS
num_rows number;
stmt varchar2(2000);
BEGIN
IF owner IS NULL THEN
stmt := 'select count(*) from "' || table_name || '"';
ELSE
stmt := 'select count(*) from "' || owner || '"."' || table_name || '"';
END IF;
EXECUTE IMMEDIATE stmt INTO num_rows;
RETURN num_rows;
END;
/
---调用count_rows 函数，统计各种表的记录数
select table_name, count_rows(table_name) nrows from user_tables where tablespace_name='ENTSERVICE'
order by nrows desc;
--查看
SQL> select count(*) from ENTSERVICE.CUST;
COUNT(*)
----------
1000
数据已经恢复
4.9.收尾操作
4.9.1. redo 日志组操作
4.9.1.1.清除未使用线程的 redo 日志组
--查看redo 日志组
SQL> select thread#,status,enabled from v$thread;
THREAD# STATUS ENABLED
---------- ------------ ----------------
1 OPEN PUBLIC
2 CLOSED PUBLIC
--查看redo日志组成员
SQL> select group#,thread#,archived,status from v$log;
GROUP# THREAD# ARCHIV STATUS
---------- ---------- ------ --------------------------------
1 1 NO CURRENT
2 1 YES UNUSED
3 2 YES UNUSED
4 2 YES UNUSED
--禁用线程2的redo日志组 只有状态是UNUSED和inacitve才能删除， acitve和 current不能删除，需alter system switch logfile 切换状态变成INACTIVE才能删除
SQL> alter database disable thread 2 ;
Database altered.
--数据库里删除线程2的redo日志文件
SQL> alter database drop logfile group 3 ;
Database altered.
SQL> alter database drop logfile group 4 ;
Database altered.
--操作系统上删除线程2的redo日志文件
[oracle@dxj]$ cd /oradata/racdb
[oracle@dxj:/oradata/racdb]$ ls -l
total 1847688
-rw-r----- 1 oracle oinstall 1536 Jun 1 15:35 1_1_1170515588.dbf
-rw-r----- 1 oracle oinstall 2048 Jun 1 15:09 1_14_1170000663.dbf
-rw-r----- 1 oracle oinstall 1024 Jun 1 15:35 2_1_1170515588.dbf
-rw-r----- 1 oracle oinstall 2048 Jun 1 15:09 2_12_1170000663.dbf
-rw-r----- 1 oracle oinstall 18497536 Jun 1 15:47 control01.ctl
-rw-r----- 1 oracle oinstall 18497536 Jun 1 15:47 control02.ctl
-rw-r----- 1 oracle oinstall 1056768 Jun 1 15:35 entservice.303.1170422443
-rw-r----- 1 oracle oinstall 1056768 Jun 1 15:35 entservice.304.1170422455
-rw-r----- 1 oracle oinstall 52436992 Jun 1 15:35 entservicetemp.305.1170422495
-rw-r----- 1 oracle oinstall 52429312 Jun 1 15:35 group_1.275.1170000663
-rw-r----- 1 oracle oinstall 52429312 Jun 1 15:35 group_1.276.1170000665
-rw-r----- 1 oracle oinstall 52429312 Jun 1 15:46 group_2.277.1170000665
-rw-r----- 1 oracle oinstall 52429312 Jun 1 15:46 group_2.278.1170000665
-rw-r----- 1 oracle oinstall 52429312 Jun 1 15:46 group_3.281.1170000777
-rw-r----- 1 oracle oinstall 52429312 Jun 1 15:46 group_3.282.1170000777
-rw-r----- 1 oracle oinstall 52429312 Jun 1 15:13 group_4.283.1170000777
-rw-r----- 1 oracle oinstall 52429312 Jun 1 15:13 group_4.284.1170000777
drwxr-x--- 3 oracle oinstall 23 Jun 1 15:13 RACDB
-rw-r----- 1 oracle oinstall 576724992 Jun 1 15:35 sysaux.270.1170000589
-rw-r----- 1 oracle oinstall 744497152 Jun 1 15:40 system.269.1170000589
-rw-r----- 1 oracle oinstall 20979712 Jun 1 15:35 temp.279.1170000665
-rw-r----- 1 oracle oinstall 78651392 Jun 1 15:40 undotbs1.271.1170000589
-rw-r----- 1 oracle oinstall 26222592 Jun 1 15:35 undotbs2.280.1170000713
-rw-r----- 1 oracle oinstall 5251072 Jun 1 15:35 users.272.1170000589
[oracle@dxj:/oradata/racdb]$ rm -f group_3.281.1170000777
[oracle@dxj:/oradata/racdb]$ rm -f group_3.282.1170000777
[oracle@dxj:/oradata/racdb]$ rm -f group_4.283.1170000777
[oracle@dxj:/oradata/racdb]$ rm -f group_4.284.1170000777
4.9.1.2.增加日志组
生产环境业务库，数据量比较大，这里决定再增加一些日志组
--查redo大小和位置 状态
set linesize 999
col member for a60
select b.thread#,a.group#,a.member,bytes/1024/1024,b.members,b.status from v$logfile a,v$log b where a.group#=b.group#;
--增加联机日志文件
alter database add logfile group 3 ('/oradata/racdb/redo0301.log','/oradata/racdb/redo0302.log') size 50m;
alter database add logfile group 4 ('/oradata/racdb/redo0401.log','/oradata/racdb/redo0402.log') size 50m;4.9.2.清除多余的 undo 文件
rac 中每个节点使用的都是自己的 undo，所以有 2 个 undo 文件，这里可以清除，也可以不用清除，因为有的时候 undo 坏了可以很迅速的切换到另外的 undo 空间， 本文档采用清除多余的undo文件。
--查看undo 文件
SQL> select name from v$tablespace where name like 'UNDO%';
NAME
------------------------------------------------------------
UNDOTBS1
UNDOTBS2
--查看默认undo表空间
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
undo_tablespace string UNDOTBS1
--删除多余的 undo 文件
SQL> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped.4.9.3.修改sga和pga参数
如果目标端比源端内存配置高，则需要进行sga和pga参数优化。
内存修改可参考如下公式：
--os_memory_total=$(awk '/MemTotal/{print $2}' /proc/meminfo)
--$sga_target=os_memory_total * 8 * 8 / 100 / 1024
--pga_target=os_memory_total * 8 * 2 / 100 / 1024
--备份参数文件
create pfile='/home/oracle/pfile1124.ora' from spfile;
--更改参数
alter system set sga_max_size=4096M scope=spfile;
alter system set sga_target=4096M scope=spfile;
alter system set pga_aggregate_target=1024M scope=spfile;
--重启生效
shutdown immediate
startup open参考链接：【RAC】rac数据库恢复到单机.pdf - 墨天轮文档
Oracle RAC迁移到单实例（通过备份集手动恢复）_rac迁移单机需要重建控制文件吗-CSDN博客
参考文档：
📎【RAC】rac数据库恢复到单机-解密.pdf
