Oracle 数据库迁移操作手册

Oracle 数据库迁移操作手册（oracle-migrate-bash）

场景

目前遇到的场景是: 1.1T数据量进行异机迁移,并且是由rac模式(分布式模式)迁移至单体模式

迁移方式

总体迁移方式分为以下3种:

使用rman官方工具进行迁移
使用plsql或navicat等第三方工具进行迁移, 如plsql可以导出plsql的特有二进制格式进行横向迁移
使用sql进行导出操作,类似于mysql的sqldump工具

选择迁移方式

由于当前数据量过大, 只能从第一和第二种方式选择, 但是我们的场景是纯内网环境, 且原有rac模式已经使用nas进行数据备份, 可以做到同网络下千兆宽带无感知移动文件, 所以选择第一种方式进行迁移.

数据迁移前期准备工作

1. 操作系统参数检查

1.1 系统内核参数

注意当前参数为核心参数, 会影响到同服务器下的其他应用, 建议安装oracle时进行独立安装, 不要与其他应用放置在同一服务器
检查文件：/etc/sysctl.conf

# 文件最大句柄数量
fs.file-max = 6815744
# 文件异步通道数量
fs.aio-max-nr = 1048576
# 内核核心参数(尽量不要修改)
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 4294967296
kernel.shmmax = 4398046511104
# 网络核心参数(尽量不要修改)
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.ip_local_port_range = 9000 65500

执行生效：

sysctl -p

1.2 硬件限制

检查文件：/etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240

1.3 Linux 安全组件

检查文件：/etc/selinux/config

SELINUX=disabled

重启服务器使其生效。

2. 切换到oracle用户执行如下操作

su - oracle
sqlplus / as sysdba

3. 查看内存配置

SELECT
    name,
    display_value,
    isdefault
FROM v$parameter
WHERE name IN (
    'memory_target',
    'memory_max_target',
    'sga_target',
    'sga_max_size',
    'pga_aggregate_target',
    'shared_pool_size',
    'db_cache_size',
    'large_pool_size',
    'java_pool_size'
)
ORDER BY name;

当前为 ASMM 模式：

SGA：9.2G

PGA：3G

2.1 怎么区分内存模式?

oracle根据历史原因分为三种内存模式, 直接内存, amm, asmm

直接内存: 可以手动针对所有的内存点进行配置, 如针对sid进行配置
amm模式: 直接分配整体内存, 由oracle自主分配
asmm模式: 可以区分系统内存与业务内存, 分别配置后可以由oracle自主分配

4. 游标数量设置

SHOW PARAMETER open_cursors;
ALTER SYSTEM SET open_cursors = 1500 SCOPE=BOTH;

5. 快速恢复区大小

SHOW PARAMETER db_recovery_file_dest_size;
ALTER SYSTEM SET db_recovery_file_dest_size = 300G SCOPE=BOTH;

6. 会话连接数

SHOW PARAMETER sessions;

当前值：1952

7. 最大物理进程数

SHOW PARAMETER processes;
ALTER SYSTEM SET processes = 1280 SCOPE=SPFILE;

⚠️ 需要重启数据库生效

8. 关闭密码过期策略

SELECT username, profile FROM dba_users;
SELECT * FROM dba_profiles WHERE resource_name = 'PASSWORD_LIFE_TIME';

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ALTER PROFILE ORA_STIG_PROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED;

9. 数据库字符集确认

SELECT * FROM nls_database_parameters;

确认与源库保持一致

10. 兼容旧版本客户端连接

编辑文件(如果没有需要创建)：$ORACLE_HOME/network/admin/sqlnet.ora

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

11. 关闭审计功能

SELECT value FROM v$option WHERE parameter = 'Unified Auditing';
SHOW PARAMETER audit_trail;

ALTER SYSTEM SET audit_trail = NONE SCOPE=SPFILE;

⚠️ 需要重启数据库
SHUTDOWN ABORT;
STARTUP MOUNT;
ALTER DATABASE OPEN;

数据迁移开始

具体脚本请查看我的github仓库: https://github.com/HeyAlaia/oracle-migrate-bash

12. 清除快速恢复区

rm -rf /u01/app/oracle/fast_recovery_area/alaia/*
touch /u01/app/oracle/fast_recovery_area/alaia/control02.ctl
ll /u01/app/oracle/fast_recovery_area/alaia/

13. RMAN 全量恢复

-- 挂载nas
mount -t nfs 127.0.0.1:/mnt/oracle_backup/wljk_backup /backup_2024

-- 获取dbid
SELECT dbid FROM v$database;
-- 获取newname名称
SELECT 'set newname for datafile ' || t.RFILE# || ' to ''' || t.NAME || ''';'
FROM v$datafile t;

old_dbid = 3080101081
new_dbid = 3435667710

执行脚本：all_recovery_data.sh

14. RMAN 增量恢复

执行脚本：append_recovery_data.sh

SELECT dbid FROM v$database;

15. 查看节点恢复位置

SELECT thread#, group#, sequence#, first_change#, next_change#, status
FROM v$log
ORDER BY 1,4;

RECOVER DATABASE UNTIL SCN 26202550276;

16. Redo Log 日志路径处理

SET PAGESIZE 100
SELECT member FROM v$logfile;

16.1. Redo Log 日志路径处理脚本

alter database rename file '+FRA/alaia/ONLINELOG/group_2.2239.1040153341' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_2.2239.1040153341';
alter database rename file '+FRA/alaia/ONLINELOG/group_3.2240.1040153343' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_3.2240.1040153343';
alter database rename file '+FRA/alaia/ONLINELOG/group_1.2238.1040154185' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_1.2238.1040154185';
alter database rename file '+FRA/alaia/ONLINELOG/group_4.2241.1040153343' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_4.2241.1040153343';
alter database rename file '+FRA/alaia/ONLINELOG/group_5.2242.1040153363' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_5.2242.1040153363';
alter database rename file '+FRA/alaia/ONLINELOG/group_6.2243.1040154189' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_6.2243.1040154189';
alter database rename file '+FRA/alaia/ONLINELOG/group_7.2234.1040153441' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_7.2234.1040153441';
alter database rename file '+FRA/alaia/ONLINELOG/group_8.2235.1040153441' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_8.2235.1040153441';
alter database rename file '+FRA/alaia/ONLINELOG/group_9.2237.1040153465' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_9.2237.1040153465';
alter database rename file '+FRA/alaia/ONLINELOG/group_10.2236.1040153471' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_10.2236.1040153471';
alter database rename file '+FRA/alaia/ONLINELOG/group_11.2232.1039560133' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_11.2232.1039560133';
alter database rename file '+FRA/alaia/ONLINELOG/group_12.2231.1039560133' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_12.2231.1039560133';
alter database rename file '+FRA/alaia/ONLINELOG/group_13.2244.1039560133' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_13.2244.1039560133';
alter database rename file '+FRA/alaia/ONLINELOG/group_14.2245.1039560133' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_14.2245.1039560133';
alter database rename file '+FRA/alaia/ONLINELOG/group_15.2246.1039560133' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_15.2246.1039560133';
alter database rename file '+FRA/alaia/ONLINELOG/group_16.2247.1039560135' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_16.2247.1039560135';
alter database rename file '+FRA/alaia/ONLINELOG/group_17.2250.1039560543' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_17.2250.1039560543';
alter database rename file '+FRA/alaia/ONLINELOG/group_18.2251.1039560545' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_18.2251.1039560545';
alter database rename file '+FRA/alaia/ONLINELOG/group_19.2252.1039560545' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_19.2252.1039560545';
alter database rename file '+FRA/alaia/ONLINELOG/group_20.2253.1039560545' to '/u01/app/oracle/fast_recovery_area/alaia/onlinelog/group_20.2253.1039560545';

批量执行 ALTER DATABASE RENAME FILE，将 ASM 路径迁移至文件系统（略，见原始清单）

17. TEMP 表空间处理

SELECT file#, name, status FROM v$tempfile;

17.1. TEMP 表创建

SHUTDOWN ABORT;
STARTUP MOUNT;
ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN UPGRADE;

ALTER TABLESPACE temp DROP TEMPFILE '+DATA/alaia/TEMPFILE/temp.dbf';

ALTER TABLESPACE temp
ADD TEMPFILE '/u01/app/oracle/oradata/alaia/temp01.dbf'
SIZE 10G
AUTOEXTEND ON
NEXT 1G
MAXSIZE 100G;

18. 启动数据库（异常）

SHUTDOWN ABORT;
STARTUP MOUNT;
ALTER DATABASE OPEN;

错误信息：

ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated
ORA-00704: bootstrap process failure
ORA-00604: recursive SQL error
ORA-00904: "ACDRROWTSINTCOL#": invalid identifier

19. 处理联合错误（升级脚本）

执行脚本：start_upgrade.sh

sqlplus / as sysdba
shutdown abort;
startup mount;
alter database open upgrade;
select instance_name, status, database_status from v$instance;
exit

cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
cat catupgrd0.log | grep ORA-

20. 再次启动数据库

SHUTDOWN ABORT;
STARTUP MOUNT;
ALTER DATABASE OPEN;

数据迁移校验

21. 配置系统自启动（systemd）

cat > /etc/systemd/system/oracle.service << 'EOF'
[Unit]
Description=Oracle 12c
After=network-online.target
Wants=network-online.target

[Service]
Type=forking
User=oracle
Environment="ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1"
ExecStart=/u01/app/oracle/product/12.1.0/db_1/bin/dbstart /u01/app/oracle/product/12.1.0/db_1
ExecStop=/u01/app/oracle/product/12.1.0/db_1/bin/dbshut /u01/app/oracle/product/12.1.0/db_1
RemainAfterExit=yes

[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload
systemctl enable oracle

22. 验证数据

SELECT date
FROM (
    SELECT t.*
    FROM test
)
WHERE ROWNUM = 1;

✅ 迁移完成

posted on 2025-12-16 10:57 老赖的哲学阅读(16) 评论(1) 收藏举报

刷新页面返回顶部

Alaia