自学内容网 自学内容网
自学内容网 > 正文

从 Oracle 集群到单节点环境（详细记录一次数据迁移过程）之一：生产环境与目标服务器详情

🕗 发布于 2024-09-25 00:42 oracle  服务器  数据库  

从 Oracle 集群到单节点环境（详细记录一次数据迁移过程）之一：生产环境与目标服务器详情

目录

用户有一个双节点 Oracle 集群，由于工作需要，需要对生产库中的数据进行测试。基于数据安全考虑，测试方提出把生产库中的数据导出，然后导入一个单机测试环境进行数据测试。

生产数据环境为 Oracle11g 双节点集群，服务器操作系统为 Centos7.3，目标服务器为单节点服务器，服务器操作系统为 Centos7.5，详细情况如下：

一、操作系统环境

# 1、生产服务器操作系统环境（节点1）

[root@his01 bak0921]# cat /etc/redhat-release
CentOS Linux release 7.3.1611 (Core)

# 2、目标服务器操作系统环境
[root@node1 oradata]# cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)

二、Oracle 数据库基本信息（节点1）

SQL> show parameter name;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name      string
db_file_name_convert      string   /oradata/dghisdb/, +DATA/hisdb/
db_name              string      HISDB（数据库名）
db_unique_name         string      HISDB
global_names         boolean FALSE
instance_name         string     hisdb1（节点1的实例名）
lock_name_space          string
log_file_name_convert     string     /oradata/dghisdb/onlinelog/, +DATA/hisdb/onlinelog/
processor_group_name     string
service_names         string      HISDB

三、数据文件信息

SQL> select name from v$datafile;

NAME
------------------------------------------------------------------------------------------------
+DATA/hisdb/datafile/system.413.1109379227
+DATA/hisdb/datafile/undotbs1.345.1109379195
+DATA/hisdb/datafile/sysaux.412.1109379225
+DATA/hisdb/datafile/users.423.1109379259
+DATA/hisdb/datafile/system.369.1109379199
+DATA/hisdb/datafile/sysaux.419.1109379239
+DATA/hisdb/datafile/users.347.1109379195
+DATA/hisdb/datafile/data_user.407.1109379221
+DATA/hisdb/datafile/data_com.386.1109379201
+DATA/hisdb/datafile/data_fin.318.1109379187
+DATA/hisdb/datafile/data_met.313.1109379185
+DATA/hisdb/datafile/data_log.418.1109379233
+DATA/hisdb/datafile/data_goa.422.1109379259
+DATA/hisdb/datafile/data_aqu.420.1109379239
+DATA/hisdb/datafile/data_sem.400.1109379213
+DATA/hisdb/datafile/data_emr.416.1109379229
+DATA/hisdb/datafile/data_lis.409.1109379223
+DATA/hisdb/datafile/data_cas.408.1109379221
+DATA/hisdb/datafile/data_ais.410.1109379223
+DATA/hisdb/datafile/data_other.406.1109379217
+DATA/hisdb/datafile/data_pha.292.1109379173
+DATA/hisdb/datafile/data_itemlist.281.1109379159
+DATA/hisdb/datafile/data_medicinelist.282.1109379161
+DATA/hisdb/datafile/data_feeinfo.291.1109379171
+DATA/hisdb/datafile/data_feedetail.354.1109379197
+DATA/hisdb/datafile/data_applyout.275.1109379159
+DATA/hisdb/datafile/data_output.286.1109379169
+DATA/hisdb/datafile/data_record.293.1109379173
+DATA/hisdb/datafile/data_execdrug.272.1109379159
+DATA/hisdb/datafile/data_execundrug.314.1109379185
+DATA/hisdb/datafile/data_order.274.1109379159
+DATA/hisdb/datafile/data_recipedetail.389.1109379203
+DATA/hisdb/datafile/index_user.404.1109379215
+DATA/hisdb/datafile/index_com.392.1109379203
+DATA/hisdb/datafile/index_fin.312.1109379183
+DATA/hisdb/datafile/index_pha.288.1109379171
+DATA/hisdb/datafile/index_met.311.1109379181
+DATA/hisdb/datafile/index_log.394.1109379203
+DATA/hisdb/datafile/index_goa.398.1109379211
+DATA/hisdb/datafile/index_aqu.405.1109379217
+DATA/hisdb/datafile/index_sem.414.1109379227
+DATA/hisdb/datafile/index_emr.395.1109379203
+DATA/hisdb/datafile/index_lis.399.1109379213
+DATA/hisdb/datafile/index_cas.393.1109379203
+DATA/hisdb/datafile/index_ais.421.1109379243
+DATA/hisdb/datafile/index_other.396.1109379209
+DATA/hisdb/datafile/index_itemlist.271.1109379159
+DATA/hisdb/datafile/index_medicinelist.279.1109379159
+DATA/hisdb/datafile/index_feeinfo.273.1109379159
+DATA/hisdb/datafile/index_feedetail.320.1109379187
+DATA/hisdb/datafile/index_applyout.284.1109379161
+DATA/hisdb/datafile/index_output.298.1109379173
+DATA/hisdb/datafile/index_record.391.1109379203
+DATA/hisdb/datafile/index_execdrug.343.1109379195
+DATA/hisdb/datafile/index_execundrug.351.1109379197
+DATA/hisdb/datafile/index_order.325.1109379189
+DATA/hisdb/datafile/index_recipedetail.397.1109379209
+DATA/hisdb/datafile/data_user.401.1109379215
+DATA/hisdb/datafile/data_com.350.1109379197
+DATA/hisdb/datafile/data_fin.339.1109379193
+DATA/hisdb/datafile/data_met.340.1109379193
+DATA/hisdb/datafile/data_log.349.1109379197
+DATA/hisdb/datafile/data_goa.352.1109379197
+DATA/hisdb/datafile/data_aqu.348.1109379195
+DATA/hisdb/datafile/data_sem.355.1109379197
+DATA/hisdb/datafile/data_emr.358.1109379197
+DATA/hisdb/datafile/data_lis.368.1109379199
+DATA/hisdb/datafile/data_cas.342.1109379193
+DATA/hisdb/datafile/data_ais.361.1109379199
+DATA/hisdb/datafile/data_other.356.1109379197
+DATA/hisdb/datafile/data_pha.336.1109379193
+DATA/hisdb/datafile/data_itemlist.278.1109379159
+DATA/hisdb/datafile/data_medicinelist.294.1109379173
+DATA/hisdb/datafile/data_feeinfo.321.1109379187
+DATA/hisdb/datafile/data_feedetail.364.1109379199
+DATA/hisdb/datafile/data_applyout.295.1109379173
+DATA/hisdb/datafile/data_output.299.1109379177
+DATA/hisdb/datafile/data_record.297.1109379173
+DATA/hisdb/datafile/data_execdrug.289.1109379171
+DATA/hisdb/datafile/data_execundrug.327.1109379189
+DATA/hisdb/datafile/data_order.307.1109379181
+DATA/hisdb/datafile/data_recipedetail.387.1109379203
+DATA/hisdb/datafile/index_user.417.1109379231
+DATA/hisdb/datafile/index_com.402.1109379215
+DATA/hisdb/datafile/index_fin.331.1109379191
+DATA/hisdb/datafile/index_pha.330.1109379191
+DATA/hisdb/datafile/index_met.328.1109379189
+DATA/hisdb/datafile/index_log.334.1109379193
+DATA/hisdb/datafile/index_goa.366.1109379199
+DATA/hisdb/datafile/index_aqu.363.1109379199
+DATA/hisdb/datafile/index_sem.365.1109379199
+DATA/hisdb/datafile/index_emr.360.1109379197
+DATA/hisdb/datafile/index_lis.359.1109379197
+DATA/hisdb/datafile/index_cas.367.1109379199
+DATA/hisdb/datafile/index_ais.371.1109379199
+DATA/hisdb/datafile/index_other.332.1109379193
+DATA/hisdb/datafile/index_itemlist.280.1109379159
+DATA/hisdb/datafile/index_medicinelist.310.1109379181
+DATA/hisdb/datafile/index_feeinfo.362.1109379199
+DATA/hisdb/datafile/index_feedetail.376.1109379201
+DATA/hisdb/datafile/index_applyout.357.1109379197
+DATA/hisdb/datafile/index_output.374.1109379201
+DATA/hisdb/datafile/index_record.381.1109379201
+DATA/hisdb/datafile/index_execdrug.378.1109379201
+DATA/hisdb/datafile/index_execundrug.384.1109379201
+DATA/hisdb/datafile/index_order.373.1109379199
+DATA/hisdb/datafile/index_recipedetail.380.1109379201
+DATA/hisdb/datafile/index_applyout.301.1109379179
+DATA/hisdb/datafile/index_feeinfo.296.1109379173
+DATA/hisdb/datafile/index_feedetail.306.1109379181
+DATA/hisdb/datafile/data_pha.323.1109379187
+DATA/hisdb/datafile/data_feeinfo.303.1109379179
+DATA/hisdb/datafile/data_feedetail.316.1109379187
+DATA/hisdb/datafile/index_applyout.300.1109379179
+DATA/hisdb/datafile/data_execdrug.322.1109379187
+DATA/hisdb/datafile/data_execundrug.338.1109379193
+DATA/hisdb/datafile/data_fin.324.1109379187
+DATA/hisdb/datafile/index_com.390.1109379203
+DATA/hisdb/datafile/index_order.305.1109379181
+DATA/hisdb/datafile/index_met.315.1109379185
+DATA/hisdb/datafile/data_met.302.1109379179
+DATA/hisdb/datafile/index_pha.319.1109379187
+DATA/hisdb/datafile/index_medicinelist.304.1109379179
+DATA/hisdb/datafile/data_medicinelist.290.1109379171
+DATA/hisdb/datafile/data_order.308.1109379181
+DATA/hisdb/datafile/data_applyout.287.1109379169
+DATA/hisdb/datafile/index_fin.329.1109379191
+DATA/hisdb/datafile/index_execdrug.326.1109379189
+DATA/hisdb/datafile/index_feeinfo.337.1109379193
+DATA/hisdb/datafile/index_output.309.1109379181
+DATA/hisdb/datafile/data_execdrug.317.1109379187
+DATA/hisdb/datafile/data_execundrug.335.1109379193
+DATA/hisdb/datafile/data_feeinfo.382.1109379201
+DATA/hisdb/datafile/data_feedetail.377.1109379201
+DATA/hisdb/datafile/data_itemlist.379.1109379201
+DATA/hisdb/datafile/data_medicinelist.372.1109379199
+DATA/hisdb/datafile/data_output.370.1109379199
+DATA/hisdb/datafile/data_pha.411.1109379225
+DATA/hisdb/datafile/data_recipedetail.283.1109379161
+DATA/hisdb/datafile/data_record.383.1109379201
+DATA/hisdb/datafile/index_applyout.341.1109379193
+DATA/hisdb/datafile/index_execundrug.333.1109379193
+DATA/hisdb/datafile/index_feeinfo.385.1109379201
+DATA/hisdb/datafile/index_itemlist.346.1109379195
+DATA/hisdb/datafile/index_medicinelist.344.1109379195
+DATA/hisdb/datafile/index_output.375.1109379201
+DATA/hisdb/datafile/data_fin.388.1109379203
+DATA/hisdb/datafile/data_output.353.1109379197
+DATA/hisdb/datafile/data_user.415.1109379229
+DATA/hisdb/datafile/data_order.285.1109379161
+DATA/hisdb/datafile/emr5.403.1109379215
+DATA/hisdb/datafile/data_order.277.1109379159
+DATA/hisdb/datafile/data_order.270.1109379159
+DATA/hisdb/datafile/data_user.276.1109379159
+DATA/hisdb/datafile/undotbs2.429.1109386101
+DATA/hisdb/datafile/nfemr.dbf
+DATA/hisdb/datafile/emr52012.dbf
+DATA/hisdb/datafile/emr52013.dbf
+DATA/hisdb/datafile/emr52014.dbf
+DATA/hisdb/datafile/emr52015.dbf
+DATA/hisdb/datafile/emr52016.dbf
+DATA/hisdb/datafile/emr52017.dbf
+DATA/hisdb/datafile/emr52018.dbf
+DATA/hisdb/datafile/mr52019.dbf
+DATA/hisdb/datafile/emr52020.dbf
+DATA/hisdb/datafile/emr52021.dbf
+DATA/hisdb/datafile/emr52022.dbf
+DATA/hisdb/datafile/emr5.403.1109379326.dbf
+DATA/hisdb/datafile/emr5202301.dbf
+DATA/hisdb/datafile/emr5202302.dbf
+DATA/hisdb/datafile/emr5202303.dbf
+DATA/hisdb/datafile/neuodts.dbf
+DATA/hisdb/datafile/neucbus.dbf
+DATA/hisdb/datafile/emr5202305.dbf
+DATA/hisdb/datafile/emr5202401.dbf
+DATA/hisdb/datafile/emr52022_data41801.dbf
+DATA/hisdb/datafile/emr52022_data41802.dbf
+DATA/hisdb/datafile/emr52022_data41803.dbf
+DATA/hisdb/datafile/emr52022_data41804.dbf
+DATA/hisdb/datafile/emr52023_data41801.dbf
+DATA/hisdb/datafile/emr52023_data41802.dbf
+DATA/hisdb/datafile/emr52023_data41803.dbf
+DATA/hisdb/datafile/emr52023_data41804.dbf
+DATA/hisdb/datafile/emr52024_data41801.dbf
+DATA/hisdb/datafile/emr52024_data41802.dbf
+DATA/hisdb/datafile/emr52024_data41803.dbf
+DATA/hisdb/datafile/emr52024_data41804.dbf
+DATA/hisdb/datafile/emr52024_data01.dbf
+DATA/hisdb/datafile/emr52024_data02.dbf
+DATA/hisdb/datafile/emr52024_data03.dbf
+DATA/hisdb/datafile/emr52024_data04.dbf
+DATA/hisdb/datafile/emr52024_data05.dbf
+DATA/hisdb/datafile/emr52024_data06.dbf

193 rows selected.

四、日志文件信息

SQL> select member from v$logfile;

MEMBER
-------------------------------------------------------------------------------------------------------
+DATA/hisdb/onlinelog/redo01.log
+DATA/hisdb/onlinelog/redo02.log
+DATA/hisdb/onlinelog/redo04.log
+DATA/hisdb/onlinelog/redo05.log
+DATA/hisdb/onlinelog/redo06.log
+DATA/hisdb/onlinelog/redo03.log
+DATA/hisdb/onlinelog/group_7.446.1121009477
+DATA/hisdb/onlinelog/group_8.447.1121009483
+DATA/hisdb/onlinelog/group_9.448.1121009489
+DATA/hisdb/onlinelog/group_10.449.1121009493
+DATA/hisdb/onlinelog/group_11.450.1121009499
+DATA/hisdb/onlinelog/group_12.451.1121009507
+DATA/hisdb/onlinelog/group_13.452.1121009507
+DATA/hisdb/onlinelog/group_14.453.1121009507

14 rows selected.

五、Oracle 数据库总数据量

SELECT ds.owner,
       SUM(ds.BYTES)/1024/1024/1024 AS TOTAL_SPACE_GB,
       COUNT(dt.TABLE_NAME) AS TABLE_COUNT
FROM  DBA_SEGMENTS ds JOIN  
( 
  select * from DBA_TABLES 
  WHERE owner NOT IN ('SYS', 'SYSTEM')
  AND table_name NOT LIKE 'KU$%'
)dt 
ON ds.OWNER = dt.OWNER
AND ds.SEGMENT_NAME = dt.TABLE_NAME
AND ds.SEGMENT_TYPE = 'TABLE'
 13  GROUP BY ds.OWNER,ds.TABLESPACE_NAME;

OWNER       TOTAL_SPACE_GB TABLE_COUNT
------------------------------ -------------- -----------
CTXSYS   .002197266       34
LYHIS   11.7423706      546
LYHIS   3.45562744       38
LYHIS   .280273438       24
LYHIS   .0003051762
LYHIS   .135742188      179
APPQOSSYS   .0002441414
OUTLN   .0001831053
LYHIS   .271484375       24
LYHIS   11.1086426      148
LYHIS   4.84649658      237
LYHIS   .145263672       51
LYHIS   .005004883       62
LYHIS   .0004882817
NEUCBUS    .911254883       32
NEUODCBS   5.32043457      133
DMSYS    .000122072
LYHIS   .0908203138
LYHIS   .126953125       10
LYHIS   .0908203138
LYHIS     .2734375       24
XDB   .002075195       27
MDSYS   .018737793      106
LYHIS   .789428711      125
LYHIS       .28125       24
INF_PLAT0   .0103759777
ORDDATA    .003540039       52
WMSYS   .002380371       39
EXFSYS   .001220703       20
LYHIS   .241210938       24
LYHIS   .290039063       26
LYHIS   .268676758       26
LYHIS   47.2315063       27
LYHIS   4.95300293       97
LYHIS   1.14849854       37
HCB50   .500427246       27
NEUODBIP    .000122071
NEUHDS   .068481445       85
NEUHDSREPORT   .001220703       11
SYSMAN   .046020508      657
SCOTT   .0002441414
LYHIS    5.6416626      232
LYHIS   .295898438       22
LYHIS    .25390625       22
LYHIS   .000732422       12
DBSNMP    .00189209       22
LYHIS   .085632324      109
ORDSYS   .0003051765
OLAPSYS    .006958008      114
LYHIS   .018371582       27
LYHIS   .008728027       31
LOGMNR   .0029907235

52 rows selected.

六、数据文件总大小

SQL> select sum(bytes)/1024/1024/1024  size_GB from v$datafile;

   SIZE_GB
----------
747.445313

原文地址：https://blog.csdn.net/weixin_44377973/article/details/142502458

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024