一.迁移背景
源端是套跑了十年的RAC，目标端是个新的单机（都是同一架构平台），数据量约3T左右，需要将源端数据迁移至目标端单点。
目前+DATA目录存储和归档放在一起，整个磁盘组只剩下了20G空间，每间隔1小时左右就要清一理下归档，要不库就爆了。
所以这个迁移要选个周末停机窗口，停业务来进行迁移（因为开业务归档就爆，所以还是停机，就不考虑为啥归档这么多的问题先）。
从RAC迁移到单机，常规的方法有几个：
1、duplicate在线搭建ADG，然后再切换（需要看网络带宽，这种方法一般是首选，停机时间短），过程中会产生新归档。版本需相同
2、RAC端rman备份到NAS，单机端挂载，再恢复（需要额外的存储空间，这个过程也受到带宽限制，整个时间需要备份+恢复2个时间相加），过程中会产生新归档。
3、OGG或者DSG（需要了解业务结构，需要测试，避免恢复后有逻辑错误），过程中会产生新归档。
4、RAC端EXPDP到NAS，单机端挂载NAS，再导入（需要额外的存储空间，需要的时间为导出+导入时间之和，还需要了解业务结构，需要测试，避免恢复后有逻辑错误），过程中会产生新归档。数据库版本可不同
5、冷迁移：cp数据文件，过程中不产生新归档，需数据库版本相同
二.迁移环境
ip | 归档 | 操作系统和服务器登录信息 | 数据库版本号 | 数据库信息 | |
源库 | ## RAC1 Public IP 192.168.40.200 orcl01 ## RAC1 Virtual IP 192.168.40.202 orcl01-vip ## RAC1 Private IP 192.168.183.200 orcl01-priv ## RAC2 IP's: orcl02 ## RAC2 Public IP 192.168.40.201 orcl02 ## RAC2 Virtual IP 192.168.40.203 orcl02-vip ## RAC2 Private IP 192.168.183.201 orcl02-priv ## SCAN IP 192.168.40.205 orcl-scan | 已开 | centos7.9 dhh | 11.2.0.4 | 实例名：orcl 家目录： /u01/app/oracle/product/11.2.0/db |
目标库 | 192.168.40.52 | centos7.9 dhh | 11.2.0.4 | 实例名：orcl 家目录： /u01/app/oracle 数据目录： /u01/app/oracle/product/11.2.0.4/dbhome_1 | |
源库：RAC 11.2.0.4 centos7.9
目标库：单点11.2.0.4 centos7.9
可以停业务，停业务时间：72小时
三.迁移前准备
3.1.源库
3.1.1.开启防火墙
--2个节点都开启防火墙
systemctl start firewalld
systemctl enable firewalld3.1.2.停止监听
规避有新的应用连接进入数据库，进而生成新的数据。
操作之前确认源端关闭监听，断开所有连接，确保操作期间没有脏数据产生
停止监听已经连接的不会断，只会让新连接的会话进不来
--查看集群中监听部分状态
......
[root@orcl01:/root]$ crsctl stat res -t
ora.LISTENER.lsnr
ONLINE ONLINE orcl01
ONLINE ONLINE orcl02
......
--停止监听
srvctl stop listener --2个节点的监听都会停止
--查看集群中监听部分状态
[root@orcl01:/root]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
......
ora.LISTENER.lsnr
OFFLINE OFFLINE orcl01
OFFLINE OFFLINE orcl02
...... 3.1.3.业务会话确定终止
尽量根据计算机名和应用系统保障方确认，进行服务停止，能应用停止就不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.1.1.查看 rac 环境及创建测试表
如果是生产或开发环境，创建测试表步骤忽略。本文档是虚拟机模拟的实验环境。
确认源端字符集，rman异机恢复要求数据库名要一致，实例名和数据库名保持一致。
3.1.2./etc/hosts文件
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
## OracleBegin
## RAC1 IP's: orcl01
## RAC1 Public IP
192.168.40.200 orcl01
## RAC1 Virtual IP
192.168.40.202 orcl01-vip
## RAC1 Private IP
192.168.183.200 orcl01-priv
## RAC2 IP's: orcl02
## RAC2 Public IP
192.168.40.201 orcl02
## RAC2 Virtual IP
192.168.40.203 orcl02-vip
## RAC2 Private IP
192.168.183.201 orcl02-priv
## SCAN IP
192.168.40.205 orcl-scan
3.1.3.查看网卡信息
[oracle@orcl01:/home/oracle]$ ifconfig
ens32: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.40.200 netmask 255.255.255.0 broadcast 192.168.40.255
inet6 fe80::5e77:2d6d:d660:338d prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:0f:47:8a txqueuelen 1000 (Ethernet)
RX packets 634 bytes 55512 (54.2 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 297 bytes 35553 (34.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.205 netmask 255.255.255.0 broadcast 192.168.40.255
ether 00:0c:29:0f:47:8a txqueuelen 1000 (Ethernet)
ens32:2: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.40.203 netmask 255.255.255.0 broadcast 192.168.40.255
ether 00:0c:29:0f:47:8a txqueuelen 1000 (Ethernet)
ens32:3: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.40.202 netmask 255.255.255.0 broadcast 192.168.40.255
ether 00:0c:29:0f:47:8a txqueuelen 1000 (Ethernet)
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.183.200 netmask 255.255.255.0 broadcast 192.168.183.255
inet6 fe80::fae9:3ecc:8092:5009 prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:0f:47:94 txqueuelen 1000 (Ethernet)
RX packets 101 bytes 15945 (15.5 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 274 bytes 24299 (23.7 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 169.254.242.113 netmask 255.255.0.0 broadcast 169.254.255.255
ether 00:0c:29:0f:47:94 txqueuelen 1000 (Ethernet)
lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10<host>
loop txqueuelen 1000 (Local Loopback)
RX packets 9063 bytes 4892897 (4.6 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 9063 bytes 4892897 (4.6 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
3.1.4.服务器配置查询
目标服务器配置至少不低于源服务器配置。
--查内存
[oracle@11g-db ~]$ free -g
total used free shared buff/cache available
Mem: 3 0 0 0 2 1
Swap: 5 0 5
--查cpu
[oracle@11g-db ~]$ lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 1
On-line CPU(s) list: 0
Thread(s) per core: 1
Core(s) per socket: 1
Socket(s): 1
--查磁盘空间
[oracle@11g-db ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/rhel-root 63G 16G 48G 25% /
devtmpfs 2.0G 0 2.0G 0% /dev
tmpfs 2.0G 912M 1.1G 47% /dev/shm
tmpfs 2.0G 21M 1.9G 2% /run
tmpfs 2.0G 0 2.0G 0% /sys/fs/cgroup
/dev/sda1 1014M 165M 850M 17% /boot
tmpfs 394M 40K 394M 1% /run/user/1001
tmpfs 394M 4.0K 394M 1% /run/user/42
tmpfs 394M 0 394M 0% /run/user/0
3.1.5.查看RAC集群运行状态
[root@orcl01:/root]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ARCH.dg
ONLINE ONLINE orcl01
ONLINE ONLINE orcl02
ora.DATA.dg
ONLINE ONLINE orcl01
ONLINE ONLINE orcl02
ora.LISTENER.lsnr
ONLINE ONLINE orcl01
ONLINE ONLINE orcl02
ora.OCR.dg
ONLINE ONLINE orcl01
ONLINE ONLINE orcl02
ora.asm
ONLINE ONLINE orcl01 Started
ONLINE ONLINE orcl02 Started
ora.gsd
OFFLINE OFFLINE orcl01
OFFLINE OFFLINE orcl02
ora.net1.network
ONLINE ONLINE orcl01
ONLINE ONLINE orcl02
ora.ons
ONLINE ONLINE orcl01
ONLINE ONLINE orcl02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE orcl01
ora.cvu
1 ONLINE ONLINE orcl01
ora.oc4j
1 ONLINE ONLINE orcl01
ora.orcl.db
1 ONLINE ONLINE orcl01 Open
2 ONLINE ONLINE orcl02 Open
ora.orcl01.vip
1 ONLINE ONLINE orcl01
ora.orcl02.vip
1 ONLINE ONLINE orcl02
ora.scan1.vip
1 ONLINE ONLINE orcl013.1.6.查数据库版本
[oracle@orcl01]$ sqlplus -V
SQL*Plus: Release 11.2.0.4.0 Production3.1.7.查看集群参数
注意db_name db_unique_name instance_name service_names的区别和不同之处。
rman异机恢复要求数据库名要一致，实例名和数据库名保持一致
--查看数据库是否为Real Application Clusters (RAC)
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE #true：数据库是RAC
cluster_database_instances integer 2 #集群有2个实例
cluster_interconnects string
--查看节点1信息
set line 9999 pagesize 999
show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl1 #实例名字
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string orcl
--查看字符集
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 UTF8
select userenv('language') from dual;
USERENV('LANGUAGE')
--------------------------------------------------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
3.1.8.查看归档模式是否开启
--查看归档模式是否开启
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled #已开启
Archive destination +ARCH #归档文件存放位置+ARCH
Oldest online log sequence 35
Next log sequence to archive 36
Current log sequence 36
3.1.9.创建测试数据(可选)
-- 查看表空间及数据文件位置及大小
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
------------------------------ ------------------------------------------------------- ---------- ---
GG_DATA +DATA/orcl/datafile/gg_data.269.1157015867 .009765625 YES
SYSAUX +DATA/orcl/datafile/sysaux.257.1145176977 .556640625 YES
SYSTEM +DATA/orcl/datafile/system.256.1145176977 .7421875 YES
TEST_TBS +DATA/orcl/datafile/test_tbs.271.1157115519 .000976563 YES
UNDOTBS1 +DATA/orcl/datafile/undotbs1.258.1145176979 .073242188 YES
UNDOTBS2 +DATA/orcl/datafile/undotbs2.264.1145177133 .048828125 YES
USERS +DATA/orcl/datafile/users.259.1145176979 .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/orcl/tempfile/temp.263.1145177065 .032226563 .031341822
GG_TEMP +DATA/orcl/tempfile/gg_temp.270.1157015869 .004882813 .003917728
--创建临时表空间
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
3.1.10.查询所有数据库文件
--查询所有数据库文件
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/orcl/datafile/system.256.1145176977 SYSTEM READ WRITE
datafile 2 +DATA/orcl/datafile/sysaux.257.1145176977 ONLINE READ WRITE
datafile 3 +DATA/orcl/datafile/undotbs1.258.1145176979 ONLINE READ WRITE
datafile 4 +DATA/orcl/datafile/users.259.1145176979 ONLINE READ WRITE
datafile 5 +DATA/orcl/datafile/undotbs2.264.1145177133 ONLINE READ WRITE
datafile 6 +DATA/orcl/datafile/gg_data.269.1157015867 ONLINE READ WRITE
datafile 7 +DATA/orcl/datafile/test_tbs.271.1157115519 ONLINE READ WRITE
datafile 8 +DATA/orcl/datafile/entservice.273.1165062049 ONLINE READ WRITE
tempfile 1 +DATA/orcl/tempfile/temp.263.1145177065 ONLINE READ WRITE
tempfile 2 +DATA/orcl/tempfile/gg_temp.270.1157015869 ONLINE READ WRITE
tempfile 3 +DATA/orcl/tempfile/entservicetemp.274.1165062123 ONLINE READ WRITE
logfile 2 +DATA/orcl/onlinelog/group_2.262.1145177063
logfile 1 +DATA/orcl/onlinelog/group_1.261.1145177063
logfile 3 +DATA/orcl/onlinelog/group_3.265.1145177259
logfile 4 +DATA/orcl/onlinelog/group_4.266.1145177259
controlfile +DATA/orcl/controlfile/current.260.1145177061
controlfile +DATA/orcl/controlfile/control02.ctl
17 rows selected.
3.1.11.查业务数据
--查看数据量
select sum(bytes)/1024/1024/1024 as "size(G)" from dba_data_files;
size(G)
----------
3877.35168
select round(sum(bytes) / 1024 / 1024 / 1024, 2) || 'G'
from dba_segments
where owner not in ('MDSYS', 'OUTLN', 'CTXSYS', 'OLAPSYS', 'HR', 'SYSTEM',
'EXFSYS', 'SCOTT', 'DBSNMP', 'ORDSYS', 'SYSMAN', 'OE', 'PM', 'SH',
'XDB', 'ORDDATA', 'IX', 'SYS', 'WMSYS') ;
ROUND(SUM(BYTES)/1024/1024/1024,2)||'G'
----------------------------------------------------------------------------------
2831.58G
----查看用户
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
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
DZZWB USERS TEMP
ICOP USERS TEMP
SHIYONGBO USERS TEMP
SHIENGUANG USERS TEMP
DOUJIE USERS TEMP
QCCX USERS TEMP
FENGRUI USERS TEMP
WANGLILI USERS TEMP
CHENZIYUE USERS TEMP
ZHANGYANG USERS TEMP
TVFS USERS TEMP
XUKUNPENG USERS TEMP
MC USERS TEMP
SUNHAOZHE USERS TEMP
WANGQIN USERS TEMP
KAIFA USERS TEMP
SELUSER USERS TEMP
LIUHAIYANG USERS TEMP
XTSHENGJI USERS TEMP
orclA orcl TEMP
--每个业务用户下的总对象数量校验
SELECT D.OWNER,COUNT(1)
FROM dba_objects d
WHERE d.OWNER in ('DZZWB' ,'ICOP' ,'SHIYONGBO' ,'SHIENGUANG' ,'DOUJIE' ,'QCCX' ,'FENGRUI' ,'WANGLILI' ,'CHENZIYUE' ,'ZHANGYANG' ,'TVFS' ,'XUKUNPENG' ,'MC' ,'SUNHAOZHE' ,'WANGQIN' ,'KAIFA' ,'SELUSER' ,'LIUHAIYANG' ,'XTSHENGJI' ,'orclA')
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)
------------------------------------------------------------ ----------
orclA 3428
LIUHAIYANG 1
--每个业务用户下的各个对象类别的数量校验
set pagesize 999
select OWNER,OBJECT_TYPE,status,count(OBJECT_NAME) from dba_objects where owner in ('DZZWB' ,'ICOP' ,'SHIYONGBO' ,'SHIENGUANG' ,'DOUJIE' ,'QCCX' ,'FENGRUI' ,'WANGLILI' ,'CHENZIYUE' ,'ZHANGYANG' ,'TVFS' ,'XUKUNPENG' ,'MC' ,'SUNHAOZHE' ,'WANGQIN' ,'KAIFA' ,'SELUSER' ,'LIUHAIYANG' ,'XTSHENGJI' ,'orclA') group by OBJECT_TYPE,owner,status order by 1,3,2;
OWNER OBJECT_TYPE STATUS COUNT(OBJECT_NAME)
------------------------------------------------------------ -------------------------------------- -------------- ------------------
orclA PROCEDURE INVALID 1
orclA VIEW INVALID 1
orclA DATABASE LINK VALID 1
orclA INDEX VALID 1826
orclA LOB VALID 421
orclA SEQUENCE VALID 9
orclA TABLE VALID 1267
orclA VIEW VALID 5
LIUHAIYANG TABLE VALID 1
9 rows selected.
--查询回收站lob对象数量(可选),lob数量不一致排查
select owner,type,count(*) from dba_recyclebin group by owner,type order by owner;
OWNER TYPE COUNT(*)
------------------------------------------------------------ -------------------------------------------------- ----------
orclA INDEX 6
orclA LOB 103
orclA LOB INDEX 103
orclA TABLE 103
--数据量统计
----新增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.1.12.创建nfs挂载目录并赋予权限
在磁盘剩余空间较大的目录下创建存放数据文件的目录，避免磁盘空间耗尽备份未完成。
su - root
df -h
--创建nfs共享目录并赋予权限，用于存放数据文件
mkdir -p /mnt/orcl
chown -R grid:asmadmin /mnt/
chmod -R 755 /mnt/orcl/3.1.13.生成脚本
3.1.13.1.生成拷贝文件脚本
--生成拷贝文件脚本
set line 500 pagesize 500
select 'cp '||''||member||''||' /mnt/orcl/ ' from v$logfile where member like '+DATA/orcl/onlinelog/%'
union
select 'cp '||''||name||''||' /mnt/orcl/ ' from v$datafile where name like '+DATA/orcl/datafile/%'
union
select 'cp '||''||name||''||' /mnt/orcl/ ' from v$controlfile where name like '+DATA/orcl/controlfile/%'
union
select 'cp '||''||name||''||' /mnt/orcl/ ' from v$tempfile where name like '+DATA/orcl/tempfile/%';
--输出结果如下：
'CP'||''||MEMBER||''||'/BACKUP/FILEBAK/'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
cp +DATA/orcl/controlfile/control02.ctl /mnt/orcl/
cp +DATA/orcl/controlfile/current.260.1145177061 /mnt/orcl/
cp +DATA/orcl/datafile/entservice.273.1165062049 /mnt/orcl/
cp +DATA/orcl/datafile/gg_data.269.1157015867 /mnt/orcl/
cp +DATA/orcl/datafile/sysaux.257.1145176977 /mnt/orcl/
cp +DATA/orcl/datafile/system.256.1145176977 /mnt/orcl/
cp +DATA/orcl/datafile/test_tbs.271.1157115519 /mnt/orcl/
cp +DATA/orcl/datafile/undotbs1.258.1145176979 /mnt/orcl/
cp +DATA/orcl/datafile/undotbs2.264.1145177133 /mnt/orcl/
cp +DATA/orcl/datafile/users.259.1145176979 /mnt/orcl/
cp +DATA/orcl/onlinelog/group_1.261.1145177063 /mnt/orcl/
cp +DATA/orcl/onlinelog/group_2.262.1145177063 /mnt/orcl/
cp +DATA/orcl/onlinelog/group_3.265.1145177259 /mnt/orcl/
cp +DATA/orcl/onlinelog/group_4.266.1145177259 /mnt/orcl/
cp +DATA/orcl/tempfile/entservicetemp.274.1165062123 /mnt/orcl/
cp +DATA/orcl/tempfile/gg_temp.270.1157015869 /mnt/orcl/
cp +DATA/orcl/tempfile/temp.263.1145177065 /mnt/orcl/
17 rows selected.
3.1.13.2.生成重命名脚本
--生成重命名脚本
set line 500 pagesize 500
select 'alter database rename file '||''''||name||''''||' to '||chr(39)||replace(name,'+DATA/orcl/tempfile/','/u01/app/oracle/oradata/orcl/')||''';' from v$tempfile where name like '+DATA/orcl/tempfile/%'
union
select 'alter database rename file '||''''||name||''''||' to '||chr(39)||replace(name,'+DATA/orcl/datafile/','/u01/app/oracle/oradata/orcl/')||''';' from v$datafile where name like '+DATA/orcl/datafile/%'
union
select 'alter database rename file '||''''||member||''''||' to '||chr(39)||replace(member,'+DATA/orcl/onlinelog/','/u01/app/oracle/oradata/orcl/')||''';' from v$logfile where member like '+DATA/orcl/onlinelog/%';
--输出结果如下：
'ALTERDATABASERENAMEFILE'||''''||NAME||''''||'TO'||CHR(39)||REPLACE(NAME,'+DATA/ORCL/TEMPFILE/','/U01/APP/ORACLE/ORADATA/ORCL/')||''';'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter database rename file '+DATA/orcl/datafile/entservice.273.1165062049' to '/u01/app/oracle/oradata/orcl/entservice.273.1165062049';
alter database rename file '+DATA/orcl/datafile/gg_data.269.1157015867' to '/u01/app/oracle/oradata/orcl/gg_data.269.1157015867';
alter database rename file '+DATA/orcl/datafile/sysaux.257.1145176977' to '/u01/app/oracle/oradata/orcl/sysaux.257.1145176977';
alter database rename file '+DATA/orcl/datafile/system.256.1145176977' to '/u01/app/oracle/oradata/orcl/system.256.1145176977';
alter database rename file '+DATA/orcl/datafile/test_tbs.271.1157115519' to '/u01/app/oracle/oradata/orcl/test_tbs.271.1157115519';
alter database rename file '+DATA/orcl/datafile/undotbs1.258.1145176979' to '/u01/app/oracle/oradata/orcl/undotbs1.258.1145176979';
alter database rename file '+DATA/orcl/datafile/undotbs2.264.1145177133' to '/u01/app/oracle/oradata/orcl/undotbs2.264.1145177133';
alter database rename file '+DATA/orcl/datafile/users.259.1145176979' to '/u01/app/oracle/oradata/orcl/users.259.1145176979';
alter database rename file '+DATA/orcl/onlinelog/group_1.261.1145177063' to '/u01/app/oracle/oradata/orcl/group_1.261.1145177063';
alter database rename file '+DATA/orcl/onlinelog/group_2.262.1145177063' to '/u01/app/oracle/oradata/orcl/group_2.262.1145177063';
alter database rename file '+DATA/orcl/onlinelog/group_3.265.1145177259' to '/u01/app/oracle/oradata/orcl/group_3.265.1145177259';
alter database rename file '+DATA/orcl/onlinelog/group_4.266.1145177259' to '/u01/app/oracle/oradata/orcl/group_4.266.1145177259';
alter database rename file '+DATA/orcl/tempfile/entservicetemp.274.1165062123' to '/u01/app/oracle/oradata/orcl/entservicetemp.274.1165062123';
alter database rename file '+DATA/orcl/tempfile/gg_temp.270.1157015869' to '/u01/app/oracle/oradata/orcl/gg_temp.270.1157015869';
alter database rename file '+DATA/orcl/tempfile/temp.263.1145177065' to '/u01/app/oracle/oradata/orcl/temp.263.1145177065';3.1.14.生成pfile文件
--生成pfile文件
su - oracle
sqlplus / as sysdba
create pfile='/home/oracle/initorcl20240401.ora' from spfile;
--将pfile文件传输至目标端
scp /home/oracle/initorcl20240401.ora oracle@192.168.40.52:/home/oracle/3.1.15.传输密码文件至目标服务器
--查看密码文件
[oracle@orcl01:/home/oracle]$ cd $ORACLE_HOME/dbs
[oracle@orcl01:/u01/app/oracle/product/11.2.0/db/dbs]$ ls -l
total 20
-rw-r-----. 1 oracle asmadmin 1024 Aug 18 2023 arch2_1_1145177063.dbf
-rw-rw----. 1 oracle asmadmin 1544 Mar 31 11:45 hc_orcl1.dat
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 35 Aug 18 2023 initorcl1.ora
-rw-r-----. 1 oracle oinstall 1536 Aug 18 2023 orapworcl1
--将密码文件传输至目录端
scp orapworcl1 oracle@192.168.40.52:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/3.1.16.关闭数据库
--使用集群命令正常关闭数据库 2个节点都会停止
su - oracle
srvctl stop database -d orcl
--查看数据库状态
srvctl status database -d orcl
Instance orcl1 is not running on node orcl01
Instance orcl2 is not running on node orcl02如果执行了上述命令，库还卡着正常关闭不了，可以尝试以下方式
方法1.先杀掉LOCAL=NO的会话，进程结束后，数据库会自动进行关闭
不建议
ps -ef |grep LOCAL=NO|grep -v grep|cut -c 9-15|xargs kill -9方法2.可以快速关闭数据库，如果不放心，可以再open，最后正常关闭（其实冷备份文件全abort也没有事，会自动恢复）
不建议
快速关闭的命令为
srvctl stop database -d orcl -o abort3.1.17.配置 NFS共享
源端rac节点1的ip：192.168.40.200 nfs客户端ip
目标端ip：192.168.40.52 nfs服务端ip
语法：nfs服务器ip：nfs服务器上共享目录 nfs客户端挂载目录
--RAC节点安装nfs
yum install -y nfs-utils
--RAC客户段挂载NFS（192.168.40.200上操作） nfs服务器端192.168.40.52:/u01/app/oracle/oradata nfs客户端挂载目录/mnt
mount -t nfs -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,vers=3,timeo=600,nolock 192.168.40.52:/u01/app/oracle/oradata /mnt
--使用root创建目录，给NFS目录赋予权限 若上面已创建省略
mkdir -p /mnt/orcl
chown -R grid:asmadmin /mnt/
chmod -R 755 /mnt/orcl/
补充：
在NFS服务端，可以通过cat /var/lib/nfs/etab查看NFS服务器端配置参数的细节。
在NFS客户端，可以通过cat /proc/mounts 查看mount的挂载参数细节。官方文档对于不同版本操作系统挂载NFS是有要求的，具体参照官方文档
Mount Options for Oracle files for RAC databases and Clusterware when used with NFS on NAS devices (Doc ID 359515.1)
这里注意与官方文档不同的地方是加了nolock，如果不添加，拷贝的时候会有如下报错,具体原因没查到为啥。
ASMCMD> cp * /mnt/orcl
copying +data/orcl/datafile/SYSTEM.256.1086172033 -> /mnt/orcl/SYSTEM.256.1086172033
ASMCMD-8016: copy source '+data/orcl/datafile/SYSTEM.256.1086172033' and target '/mnt/orcl/SYSTEM.256.1086172033' failed
ORA-19505: failed to identify file "/mnt/orcl/SYSTEM.256.1086172033"
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 37: No locks available
Additional information: 10
ORA-15120: ASM file name '/mnt/orcl/SYSTEM.256.1086172033' does not begin with the ASM prefix character
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 415
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)3.3.目标库
3.3.1.安装数据库软件
默认目标库已经安装好了同源库一样的数据库版本。
若已创建实例，需按以下步骤进行闭库，删除：
--关闭数据库
shutdown immediate
--静默删除数据库实例
dbca -silent -deleteDatabase -sourcedb orcl -sid orcl3.3.2.查数据库版本
[oracle@11g-db oradata]$ sqlplus -V
SQL*Plus: Release 11.2.0.4.0 Production3.3.3.配置nfs服务器
192.169.11.1 nfs客户端ip
--安装nfs
yum install -y nfs-utils rpcbind
--编辑NFS配置文件 nfs服务端192.168.40.52:/u01/app/oracle/oradata nfs客户端ip:192.168.40.200
[root@topywk ~]# cat /etc/exports
/u01/app/oracle/oradata 192.168.40.52(rw,sync,no_root_squash,no_all_squash,no_subtree_check)
语法：nfs服务器端共享目录 nfs客户端ip
--启动nfs服务器
centos7.6:
[root@topywk ~]# systemctl restart nfs-server
[root@topywk ~]# systemctl status nfs-server
redhat 6.9:
/etc/init.d/nfs start
--显示挂载详细信息
[root@topywk ~]# exportfs -v
/u01/app/oracle/oradata
192.168.40.52(sync,wdelay,hide,no_subtree_check,sec=sys,rw,secure,no_root_squash,no_all_squash)
语法：nfs服务器端共享目录 nfs客户端ip 补充：
在NFS服务端，可以通过cat /var/lib/nfs/etab查看NFS服务器端配置参数的细节。
在NFS客户端，可以通过cat /proc/mounts 查看mount的挂载参数细节。3.3.4.服务器配置查询
目标服务器配置至少不低于源服务器配置。
--查内存
[oracle@11g-db ~]$ free -g
total used free shared buff/cache available
Mem: 3 0 0 0 2 1
Swap: 5 0 5
--查cpu
[oracle@11g-db ~]$ lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 1
On-line CPU(s) list: 0
Thread(s) per core: 1
Core(s) per socket: 1
Socket(s): 1
--查磁盘空间
[oracle@11g-db ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/rhel-root 63G 16G 48G 25% /
devtmpfs 2.0G 0 2.0G 0% /dev
tmpfs 2.0G 912M 1.1G 47% /dev/shm
tmpfs 2.0G 21M 1.9G 2% /run
tmpfs 2.0G 0 2.0G 0% /sys/fs/cgroup
/dev/sda1 1014M 165M 850M 17% /boot
tmpfs 394M 40K 394M 1% /run/user/1001
tmpfs 394M 4.0K 394M 1% /run/user/42
tmpfs 394M 0 394M 0% /run/user/0
四.迁移过程
4.1.源库
4.1.1.源库冷拷贝数据文件
使用grid用户登录到asmcmd命令行
粘贴生成的拷贝脚本，等到拷贝完成
--使用grid用户登录到asmcmd命令行
su - grid
asmcmd
ASMCMD>
--执行拷贝文件脚本
cp +DATA/orcl/controlfile/control02.ctl /mnt/orcl/
cp +DATA/orcl/controlfile/current.260.1145177061 /mnt/orcl/
cp +DATA/orcl/datafile/entservice.273.1165062049 /mnt/orcl/
cp +DATA/orcl/datafile/gg_data.269.1157015867 /mnt/orcl/
cp +DATA/orcl/datafile/sysaux.257.1145176977 /mnt/orcl/
cp +DATA/orcl/datafile/system.256.1145176977 /mnt/orcl/
cp +DATA/orcl/datafile/test_tbs.271.1157115519 /mnt/orcl/
cp +DATA/orcl/datafile/undotbs1.258.1145176979 /mnt/orcl/
cp +DATA/orcl/datafile/undotbs2.264.1145177133 /mnt/orcl/
cp +DATA/orcl/datafile/users.259.1145176979 /mnt/orcl/
cp +DATA/orcl/onlinelog/group_1.261.1145177063 /mnt/orcl/
cp +DATA/orcl/onlinelog/group_2.262.1145177063 /mnt/orcl/
cp +DATA/orcl/onlinelog/group_3.265.1145177259 /mnt/orcl/
cp +DATA/orcl/onlinelog/group_4.266.1145177259 /mnt/orcl/
cp +DATA/orcl/tempfile/entservicetemp.274.1165062123 /mnt/orcl/
cp +DATA/orcl/tempfile/gg_temp.270.1157015869 /mnt/orcl/
cp +DATA/orcl/tempfile/temp.263.1145177065 /mnt/orcl/
--拷贝完成后，卸载nfs
cd ~
su - root
umount /mnt问题处理
--卸载nfs失败
[root@orcl01:/mnt]$ umount /mnt
umount.nfs: /mnt: device is busy
--查看占用的进程
[root@localhost /]# fuser -m -v /data/
用户 进程号 权限 命令
USER PID ACCESS COMMAND
/mnt: root kernel mount /mnt
root 4112 ..c.. bash
参数说明：
-v 表示 verbose 模式。进程以 ps 的方式显示，包括 PID、USER、COMMAND、ACCESS 字段
-m 表示指定文件所在的文件系统或者块设备（处于 mount 状态）。所有访问该文件系统的进程都被列出。
如上所示，有1个进程占用了，将其kill掉，再重新取消挂载。
[root@orcl01:/mnt]$ kill -9 4112
[root@orcl01:/root]$ umount /mnt4.2.目标库
4.2.1.目标库恢复密码文件
--查看密码文件
su - oracle
cd /u01/app/oracle/product/11.2.0/db/dbs
ls -l
[oracle@11g-db dbs]$ ls -l
total 4
-rw-r----- 1 oracle oinstall 1536 Apr 1 12:49 orapworcl1
--重命名密码文件
mv orapworcl1 orapworcl
或者重建密码文件
orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle entries=54.2.2.生成spfile文件
4.2.2.1.查看源库pfile文件内容
[oracle@11g-db ~]$ cat initorcl20240401.ora
orcl1.__db_cache_size=549453824
orcl2.__db_cache_size=549453824
orcl1.__java_pool_size=4194304
orcl2.__java_pool_size=4194304
orcl1.__large_pool_size=8388608
orcl2.__large_pool_size=8388608
orcl1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl1.__pga_aggregate_target=209715200
orcl2.__pga_aggregate_target=209715200
orcl1.__sga_target=838860800
orcl2.__sga_target=838860800
orcl1.__shared_io_pool_size=0
orcl2.__shared_io_pool_size=0
orcl1.__shared_pool_size=268435456
orcl2.__shared_pool_size=268435456
orcl1.__streams_pool_size=0
orcl2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/orcl/controlfile/current.260.1145177061','+DATA/orcl/controlfile/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='orcl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_goldengate_replication=TRUE
orcl2.instance_number=2
orcl1.instance_number=1
*.log_archive_dest_1='location=+ARCH'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=208666624
*.processes=150
*.remote_listener='orcl-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=837812224
orcl2.thread=2
orcl1.thread=1
*.undo_retention=86400
orcl2.undo_tablespace='UNDOTBS2'
orcl1.undo_tablespace='UNDOTBS1'4.2.2.2.更改后的pfile文件内容
[oracle@11g-db ~]$ cp initorcl20240401.ora initorcl20240401.ora_bak_20240401
[oracle@11g-db ~]$ cat initorcl20240401.ora
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/current.260.1145177061','/u01/app/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata/orcl'
*.db_domain=''
*.db_name='orcl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_1='location=/u01/archive'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=208666624
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=837812224
*.undo_retention=86400
:%s#+DATA/orcl/controlfile#/u01/app/oracle/oradata/orcl#g
:%s#+ARCH#/u01/archive#g
:%s#+DATA#/u01/app/oracle/oradata/orcl#g
4.2.2.4.创建参数文件中不存在的目录
mkdir -p /u01/app/oracle/admin/orcl/adump
mkdir -p /u01/archive4.2.3.nomount启库
su - oracle
sqlplus / as sysdba
create spfile from pfile='/home/oracle/initorcl20240401.ora';
startup nomount;4.2.4.启库至mount状态
4.2.4.1.更改数据目录权限
[root@11g-db ~]# chown -R oracle:oinstall /u01/app/oracle/oradata/4.2.4.2.mount库
SQL> alter database mount;
Database altered.4.2.5.更改控制文件中数据文件位置
--更改控制文件中数据文件位置
alter database rename file '+DATA/orcl/datafile/entservice.273.1165062049' to '/u01/app/oracle/oradata/orcl/entservice.273.1165062049';
alter database rename file '+DATA/orcl/datafile/gg_data.269.1157015867' to '/u01/app/oracle/oradata/orcl/gg_data.269.1157015867';
alter database rename file '+DATA/orcl/datafile/sysaux.257.1145176977' to '/u01/app/oracle/oradata/orcl/sysaux.257.1145176977';
alter database rename file '+DATA/orcl/datafile/system.256.1145176977' to '/u01/app/oracle/oradata/orcl/system.256.1145176977';
alter database rename file '+DATA/orcl/datafile/test_tbs.271.1157115519' to '/u01/app/oracle/oradata/orcl/test_tbs.271.1157115519';
alter database rename file '+DATA/orcl/datafile/undotbs1.258.1145176979' to '/u01/app/oracle/oradata/orcl/undotbs1.258.1145176979';
alter database rename file '+DATA/orcl/datafile/undotbs2.264.1145177133' to '/u01/app/oracle/oradata/orcl/undotbs2.264.1145177133';
alter database rename file '+DATA/orcl/datafile/users.259.1145176979' to '/u01/app/oracle/oradata/orcl/users.259.1145176979';
alter database rename file '+DATA/orcl/onlinelog/group_1.261.1145177063' to '/u01/app/oracle/oradata/orcl/group_1.261.1145177063';
alter database rename file '+DATA/orcl/onlinelog/group_2.262.1145177063' to '/u01/app/oracle/oradata/orcl/group_2.262.1145177063';
alter database rename file '+DATA/orcl/onlinelog/group_3.265.1145177259' to '/u01/app/oracle/oradata/orcl/group_3.265.1145177259';
alter database rename file '+DATA/orcl/onlinelog/group_4.266.1145177259' to '/u01/app/oracle/oradata/orcl/group_4.266.1145177259';
alter database rename file '+DATA/orcl/tempfile/entservicetemp.274.1165062123' to '/u01/app/oracle/oradata/orcl/entservicetemp.274.1165062123';
alter database rename file '+DATA/orcl/tempfile/gg_temp.270.1157015869' to '/u01/app/oracle/oradata/orcl/gg_temp.270.1157015869';
alter database rename file '+DATA/orcl/tempfile/temp.263.1145177065' to '/u01/app/oracle/oradata/orcl/temp.263.1145177065';
--查看更改后的数据文件
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 /u01/app/oracle/oradata/orcl/system.256.1145176977 SYSTEM READ WRITE
datafile 2 /u01/app/oracle/oradata/orcl/sysaux.257.1145176977 ONLINE READ WRITE
datafile 3 /u01/app/oracle/oradata/orcl/undotbs1.258.1145176979 ONLINE READ WRITE
datafile 4 /u01/app/oracle/oradata/orcl/users.259.1145176979 ONLINE READ WRITE
datafile 5 /u01/app/oracle/oradata/orcl/undotbs2.264.1145177133 ONLINE READ WRITE
datafile 6 /u01/app/oracle/oradata/orcl/gg_data.269.1157015867 ONLINE READ WRITE
datafile 7 /u01/app/oracle/oradata/orcl/test_tbs.271.1157115519 ONLINE READ WRITE
datafile 8 /u01/app/oracle/oradata/orcl/entservice.273.1165062049 ONLINE READ WRITE
tempfile 1 /u01/app/oracle/oradata/orcl/temp.263.1145177065 ONLINE READ WRITE
tempfile 2 /u01/app/oracle/oradata/orcl/gg_temp.270.1157015869 ONLINE READ WRITE
tempfile 3 /u01/app/oracle/oradata/orcl/entservicetemp.274.1165062123 ONLINE READ WRITE
logfile 2 /u01/app/oracle/oradata/orcl/group_2.262.1145177063
logfile 1 /u01/app/oracle/oradata/orcl/group_1.261.1145177063
logfile 3 /u01/app/oracle/oradata/orcl/group_3.265.1145177259
logfile 4 /u01/app/oracle/oradata/orcl/group_4.266.1145177259
controlfile /u01/app/oracle/oradata/orcl/current.260.1145177061
controlfile /u01/app/oracle/oradata/orcl/control02.ctl
17 rows selected.4.2.6.open库
SQL> alter database open;
Database altered.4.3.验证数据
4.3.1.查看实例参数
set linesize 999
set pagesize 999
show parameter name
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string +DATA/orcl/datafile, /u01/app/oracle/oradata/orcl/datafile,
+DATA/orcl/tempfile, /u01/app/oracle/oradata/orcl/tempfile
db_name string ORCL
db_unique_name string ORCL
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string +DATA/orcl/onlinelog, /u01/app/oracle/oradata/orcl/onlinelog
processor_group_name string
service_names string ORCL
4.3.2.查看归档
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archive
Oldest online log sequence 47
Next log sequence to archive 48
Current log sequence 48
4.4.3.查看所有数据文件
--查看所有数据文件
set line 9999 pagesize 9999
col FILE_NAME format a90
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 /u01/app/oracle/oradata/orcl/datafile/system.256.1145176977 SYSTEM READ WRITE
datafile 2 /u01/app/oracle/oradata/orcl/datafile/sysaux.257.1145176977 ONLINE READ WRITE
datafile 3 /u01/app/oracle/oradata/orcl/datafile/undotbs1.258.1145176979 ONLINE READ WRITE
datafile 4 /u01/app/oracle/oradata/orcl/datafile/users.259.1145176979 ONLINE READ WRITE
datafile 5 /u01/app/oracle/oradata/orcl/datafile/undotbs2.264.1145177133 ONLINE READ WRITE
datafile 6 /u01/app/oracle/oradata/orcl/datafile/gg_data.269.1157015867 ONLINE READ WRITE
datafile 7 /u01/app/oracle/oradata/orcl/datafile/test_tbs.271.1157115519 ONLINE READ WRITE
datafile 8 /u01/app/oracle/oradata/orcl/datafile/entservice.273.1165062049 ONLINE READ WRITE
tempfile 1 /u01/app/oracle/oradata/orcl/tempfile/temp.263.1145177065 ONLINE READ WRITE
tempfile 2 /u01/app/oracle/oradata/orcl/tempfile/gg_temp.270.1157015869 ONLINE READ WRITE
tempfile 3 /u01/app/oracle/oradata/orcl/tempfile/entservicetemp.274.1165062123 ONLINE READ WRITE
logfile 2 /u01/app/oracle/oradata/orcl/onlinelog/group_2.262.1145177063
logfile 1 /u01/app/oracle/oradata/orcl/onlinelog/group_1.261.1145177063
logfile 3 /u01/app/oracle/oradata/orcl/onlinelog/group_3.265.1145177259
logfile 4 /u01/app/oracle/oradata/orcl/onlinelog/group_4.266.1145177259
controlfile /u01/app/oracle/oradata/orcl/controlfile/control01.ctl
controlfile /u01/app/oracle/oradata/orcl/controlfile/control02.ctl
17 rows selected.
4.4.4.业务数据验证
--查看数据量
select sum(bytes)/1024/1024/1024 as "size(G)" from dba_data_files;
select round(sum(bytes) / 1024 / 1024 / 1024, 2) || 'G'
from dba_segments
where owner not in ('MDSYS', 'OUTLN', 'CTXSYS', 'OLAPSYS', 'HR', 'SYSTEM',
'EXFSYS', 'SCOTT', 'DBSNMP', 'ORDSYS', 'SYSMAN', 'OE', 'PM', 'SH',
'XDB', 'ORDDATA', 'IX', 'SYS', 'WMSYS') ;
--查看用户
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');
OGG GG_DATA GG_TEMP
ENTSERVICE ENTSERVICE ENTSERVICETEMP
TEST TEST_TBS TEMP
--每个业务用户下的总对象数量校验
SELECT D.OWNER,COUNT(1)
FROM dba_objects d
WHERE d.OWNER in ('TEST','OGG','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 ;
ENTSERVICE 1
OGG 4
TEST 5
--每个业务用户下的各个对象类别的数量校验
set pagesize 999
select OWNER,OBJECT_TYPE,status,count(OBJECT_NAME) from dba_objects where owner in ('TEST','OGG','ENTSERVICE') group by OBJECT_TYPE,owner,status order by 1,3,2;
OWNER OBJECT_TYPE STATUS COUNT(OBJECT_NAME)
------------------------------------------------------------ -------------------------------------- -------------- ------------------
ENTSERVICE TABLE VALID 1
OGG INDEX VALID 2
OGG TABLE VALID 2
TEST INDEX VALID 2
TEST SEQUENCE VALID 1
TEST TABLE VALID 2
--查询回收站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.4.收尾操作
4.4.1. redo 日志组操作
4.4.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 YES INACTIVE
2 1 NO CURRENT
3 2 YES INACTIVE
4 2 YES INACTIVE
--查redo大小和位置 状态
set linesize 999
col member for a80
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#;
--禁用线程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@11g-db ~]$ cd /u01/app/oracle/oradata/orcl/
[oracle@11g-db onlinelog]$ ls -l
total 204816
-rw-r----- 1 oracle oinstall 52429312 Apr 1 00:45 group_1.261.1145177063
-rw-r----- 1 oracle oinstall 52429312 Apr 1 00:35 group_2.262.1145177063
-rw-r----- 1 oracle oinstall 52429312 Apr 1 00:45 group_3.265.1145177259
-rw-r----- 1 oracle oinstall 52429312 Apr 1 00:35 group_4.266.1145177259
[oracle@11g-db onlinelog]$ rm -f group_3.265.1145177259
[oracle@11g-db onlinelog]$ rm -f group_4.266.1145177259
4.5.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 '/u01/app/oracle/oradata/orcl/redo03.log' size 50m;
alter database add logfile group 4 '/u01/app/oracle/oradata/orcl/redo04.log' size 50m;4.5.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.5.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五.注意事项
如果指定SCN进行恢复，会提示报错，按SCN恢复需要连target库或是catalog库
rman auxiliary /
RMAN> DUPLICATE DATABASE TO orcl BACKUP LOCATION '/u01/rman/' NOFILENAMECHECK until scn 2534722;
Starting Duplicate Db at 07-DEC-23
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/07/2023 20:43:24
RMAN-05501: aborting duplication of target database
RMAN-05542: Only UNTIL TIME can be used with DUPLICATE without TARGET and CATALOG connections如果指定时间进行恢复，而备份里没这些归档，那么会失败。
rman auxiliary /
DUPLICATE DATABASE TO orcl BACKUP LOCATION '/u01/rman/' NOFILENAMECHECK UNTIL TIME "TO_DATE('07-DEC-2023 20:27:42','DD-MON-YYYYHH24:MI:SS')"报错如下：
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01507: database not mounted
ORA-06512: at "SYS.X$DBMS_RCVMAN", line 13658
ORA-06512: at line 1
RMAN-06617: UNTIL TIME (07-DEC-23) is ahead of last NEXT TIME in archived logs (07-DEC-23)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/07/2023 20:49:26
RMAN-05501: aborting duplication of target database六.总结
1、duplicate在线搭建ADG，然后再切换（需要看网络带宽，这种方法一般是首选，停机时间短），过程中会产生新归档。
2、RAC端rman备份到NAS，单机端挂载，再恢复（需要额外的存储空间，这个过程也受到带宽限制，整个时间需要备份+恢复2个时间相加），过程中会产生新归档。
3、OGG或者DSG（需要了解业务结构，需要测试，避免恢复后有逻辑错误），过程中会产生新归档。
4、RAC端EXPDP到NAS，单机端挂载NAS，再导入（需要额外的存储空间，需要的时间为导出+导入时间之和，还需要了解业务结构，需要测试，避免恢复后有逻辑错误），过程中会产生新归档。
5、冷迁移：cp数据文件，过程中不产生新归档
参考链接：https://www.modb.pro/doc/45317
https://blog.csdn.net/sudahai102448567/article/details/104893099
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
