devbox
小舞很执着
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Oracle10.2.0.1冷备迁移之_数据文件拷贝方式_oracle数据库迁移到新服务器

作者：小舞很执着 | 2024-08-02 18:40:27

赞

踩

oracle数据库迁移到新服务器

由于阿里云机房要下架旧服务器，单位未购买整机迁移服务，且业务较老不兼容Oracle11g，所以新购买一台新服务器进行安装Oracle10.2.0.1 ，后续再将数据迁移到新服务器上。

id

数据库版本

操作系统版本

实例名

源库

115.28.242.253

Oracle10.2.0.1

Centos6.9 x86_64

cq

目标库

120.46.146.123

Oracle10.2.0.1

Centos6.9 x86_64

一.迁移前准备

1.1.查询源库信息

1.1.1.查看服务器配置

确保新服务器配置等于或高于旧服务器。

1.1.2.1.查看操作系统版本
  1. [root@cq ~]# cat /etc/redhat-release
  2. CentOS release 6.9 (Final)
1.1.2.2.查看磁盘空间
  1. [root@cq ~]# df -Th
  2. Filesystem     Type   Size  Used Avail Use% Mounted on
  3. /dev/vda1      ext4    40G  4.4G   33G  12% /
  4. tmpfs          tmpfs  3.9G     0  3.9G   0% /dev/shm
  5. /dev/vdb       ext4   493G  446G   22G  96% /opt
1.1.2.3.cpu配置
  1. [root@cq ~]# lscpu
  2. Architecture:          x86_64
  3. CPU op-mode(s):        32-bit, 64-bit
  4. Byte Order:            Little Endian
  5. CPU(s):                4
  6. On-line CPU(s) list:   0-3
  7. Thread(s) per core:    2
  8. Core(s) per socket:    2
  9. Socket(s):             1
  10. NUMA node(s):          1
  11. Vendor ID:             GenuineIntel
  12. CPU family:            6
  13. Model:                 79
  14. Model name:            Intel(R) Xeon(R) CPU E5-2682 v4 @ 2.50GHz
  15. Stepping:              1
  16. CPU MHz:               2499.996
  17. BogoMIPS:              4999.99
  18. Hypervisor vendor:     KVM
  19. Virtualization type:   full
  20. L1d cache:             32K
  21. L1i cache:             32K
  22. L2 cache:              256K
  23. L3 cache:              40960K
  24. NUMA node0 CPU(s):     0-3
1.1.2.4.内存配置
  1. [root@cq ~]# awk '/MemTotal/{print $2}' /proc/meminfo  K
  2. 8193112   --单位
  3.  
  4. 或 
  5.  
  6. [root@cq ~]# free -g
  7.              total       used       free     shared    buffers     cached
  8. Mem:             7          6          1          3          0          4
  9. -/+ buffers/cache:          1          6
  10. Swap:           15          0         15
1.1.2.5.磁盘IO速度监控
  1. --磁盘IO速度监控 每间隔3秒监控3次
  2. [root@cq ~]# iostat 3 3
  3. Linux 2.6.32-696.16.1.el6.x86_64 (cq) 	05/10/2024 	_x86_64_	(4 CPU)
  4.  
  5. avg-cpu:  %user   %nice %system %iowait  %steal   %idle
  6.            7.42    0.00    0.46    3.94    0.00   88.18
  7.  
  8. Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
  9. vda               0.45         0.83         5.53   17854722  118882528
  10. vdb             299.55     13416.03      2750.53 288563271330 59160700608
  11.  
  12. avg-cpu:  %user   %nice %system %iowait  %steal   %idle
  13.            5.35    0.00    1.00   18.90    0.00   74.75
  14.  
  15. Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
  16. vda               0.00         0.00         0.00          0          0
  17. vdb             711.00     67051.00       131.33     201153        394
  18.  
  19. avg-cpu:  %user   %nice %system %iowait  %steal   %idle
  20.            9.71    0.00    0.84    7.28    0.00   82.18
  21.  
  22. Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
  23. vda               1.00         0.00        10.67          0         32
  24. vdb             265.00     19712.00        68.67      59136        206

1.1.2.查看数据库信息

1.1.2.1.统计数据量

由于该库数据量较小，该步骤省略。该步骤只适用于数据量大的场景。

  1. --查看数据文件总大小
  2. select sum(bytes)/1024/1024/1024 as "size(G)" from dba_data_files;
  3.    size(G)
  4. ----------
  5. 156.899414
  6.  
  7. --查看总大小
  8. select round(sum(bytes) / 1024 / 1024 / 1024, 2) || 'G'
  9.   from dba_segments
  10.  where owner not in ('MDSYS', 'OUTLN', 'CTXSYS', 'OLAPSYS', 'HR', 'SYSTEM',
  11.         'EXFSYS', 'SCOTT', 'DBSNMP', 'ORDSYS', 'SYSMAN', 'OE', 'PM', 'SH',
  12.         'XDB', 'ORDDATA', 'IX', 'SYS', 'WMSYS') ;
  13. 或
  14. select round(sum(bytes) / 1024 / 1024 / 1024, 2) || 'G'
  15.   from dba_segments;
  16.  
  17.  ROUND(SUM(BYTES)/1024/1024/1024,2)||'G'
  18. -----------------------------------------
  19. 155.52G
1.1.2.2.清理业务数据

需和开发 直属领导同意。对于占用空间大的表采用方案如下：

1、删除表数据。

2、备份时加参数排除，即不备份对业务来说不重要的表。

  1. --查看表大小超过10G的大表
  2. col owner for a40
  3. col TABLE_NAME for a60
  4. set linesize 999 pagesize 999
  5. select * from (SELECT OWNER,TABLE_NAME,SUM(tabsize) table_size from (
  6.  SELECT owner,SEGMENT_NAME as table_name,SUM(BYTES)/1024/1024/1024 as tabsize FROM DBA_SEGMENTS WHERE (OWNER,SEGMENT_NAME) IN (
  7.  SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER NOT IN  ('SYS','SYSTEM','DBSNMP'))
  8.  GROUP BY SEGMENT_NAME,owner
  9.  union ALL
  10.  select lob.owner,lob.table_name,sum(seg.bytes)/1024/1024/1024 as tabsize from dba_lobs lob,dba_segments seg 
  11.  where lob.segment_name=seg.segment_name and lob.OWNER NOT IN  ('SYS','SYSTEM','DBSNMP') group by lob.owner,lob.table_name
  12.  ) 
  13.  GROUP BY OWNER,TABLE_NAME ORDER  BY 3 DESC)
  14.  where table_size>10 ;
  15.  
  16. --查询库中记录数大于2千万的所有表，当前用户拥有select any table权限
  17. select table_name, num_rows from dba_tables t where t.owner = upper('hr') and num_rows > 20000000;
  18. 或
  19. select table_name, num_rows from all_tables t where t.owner = upper('hr') and num_rows > 20000000;
1.1.2.3.停止监听
  1. --停止监听
  2. su - oracle
  3. [oracle@top130 ~]$ lsnrctl status
  4. [oracle@top130 ~]$ lsnrctl stop
  5. [oracle@top130 ~]$ lsnrctl stop
1.1.2.4.开启防火墙
  1. --开启防火墙
  2. systemctl start firewalld
  3. systemctl status firewalld
  4. firewall-cmd --list-ports
1.1.2.5.停止会话

查询会话，如果有业务会话给干掉

  1. --查看不同用户的连接数
  2. select username,count(username) from v$session where username is not null group by username;
  3.  
  4. USERNAME		       COUNT(USERNAME)
  5. ------------------------------ ---------------
  6. CQUSER					5
  7. DEV					    78
  8. SYS					     1
  9.  
  10.  
  11. --查询用户会话
  12. select username,serial#, sid from v$session where username is not null;
  13.  
  14. set linesize 999
  15. select username,status,schemaname,osuser,process,machine,program,serial#, sid from v$session where username is not null;
  16.  
  17. --删除相关用户会话
  18. alter system kill session 'sid，serial#';
  19.  
  20. 补充：
  21. --源库杀用户连接 不建议 该步骤慎重 该文档未进行该操作  该方法迫不得已不用
  22. ps -ef|grep LOCAL=NO|awk '{print $2}'|xargs kill -9

1.1.3.查看源库配置

保证新库和源库所有配置相同。

1.1.3.1.查看数据库参数
  1. --数据库的创建日期和归档方式    
  2. SELECT to_char(created,'yyyy-mm-dd'), log_mode, log_mode FROM V$database; 
  3. TO_CHAR(CR LOG_MODE	LOG_MODE
  4. ---------- ------------ ------------
  5. 2022-08-02 ARCHIVELOG	ARCHIVELOG
  6.  
  7.  
  8. --查数据库版本
  9. SELECT version FROM product_component_version WHERE substr(product, 1, 6) = 'Oracle'; 
  10. VERSION
  11. --------------------------------------------------------------------------------------------------------------------------------
  12. 10.2.0.1.0
  13.  
  14.  
  15. --查看实例名
  16. SQL> select instance_name from v$instance;
  17.  
  18. INSTANCE_NAME
  19. ----------------
  20. cq
  21.  
  22. SQL> show parameter name
  23.  
  24. NAME				     TYPE	 VALUE
  25. ------------------------------------ ----------- ------------------------------
  26. db_file_name_convert		     string
  27. db_name 			     string	 cq
  28. db_unique_name			     string	 cq
  29. global_names			     boolean	 FALSE
  30. instance_name			     string	 cq
  31. lock_name_space 		     string
  32. log_file_name_convert		     string
  33. service_names			     string	 cq
  34.  
  35.  
  36. -- 查看数据库字符集
  37. set linesize 999
  38. col value for a60
  39. select * from nls_database_parameters where PARAMETER in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
  40. PARAMETER		       VALUE
  41. ------------------------------ ------------------------------------------------------------
  42. NLS_CHARACTERSET	       ZHS16GBK
  43. NLS_NCHAR_CHARACTERSET	 AL16UTF16
  44.  
  45.  
  46. select userenv('language') from dual;
  47.  
  48. USERENV('LANGUAGE')
  49. ----------------------------------------------------
  50. SIMPLIFIED CHINESE_CHINA.ZHS16GBK
  51.  
  52. -- 查看数据库配置参数
  53. set lin 1000 pagesize 999
  54. col NAME for a30
  55. col value for a40
  56. col DISPLAY_VALUE for a40
  57. SELECT name,value,display_value FROM v$parameter
  58. WHERE name IN(
  59. 'processes',
  60. 'sessions',
  61. 'memory_target',
  62. 'memory_max_target',
  63. 'sga_max_size',
  64. 'sga_target',
  65. 'pga_aggregate_target'
  66. );
  67. NAME			                   VALUE					DISPLAY_VALUE
  68. -----------------------      ------- ---------------------------------------- ----------------------------------------
  69. processes		                 1000					    1000
  70. sessions		                 1450					    1450
  71. sga_max_size		             4294967296				4G
  72. sga_target		               4294967296				4G
  73. pga_aggregate_target	       1073741824				1G
1.1.3.2.查看用户
  1. --查看用户
  2. USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
  3. ------------------------------ ------------------------------ ------------------------------
  4. BACKUPKEVIN		       USERS			        TEMP
  5. TESTSSS 		         USERS			        TEMP
  6. YUNTEST 		         USERS			        TEMP
  7. CQTEST			         CQSCHEMA 		      TEMP
  8. CQUSER			         CQSCHEMA 		      TEMP
  9. DEV			             CQSCHEMA 		      TEMP
  10. TEST			           CQSCHEMA 		      TEMP
  11. DMSYS			           SYSAUX			        TEMP
  12. TSMSYS			         USERS			        TEMP
  13.  
  14. 9 rows selected.
  15.  
  16.  
  17. --查看用户使用了哪些表空间
  18. select distinct tablespace_name from dba_segments where owner in ('TOPICIS','XFYQ','DSJ','WLSJ');

1.1.3.3.查Role(角色)

由于没有自定义角色，所以不用生成创建角色的sql。

  1. SELECT * FROM  DBA_ROLE_PRIVS WHERE GRANTEE IN ('BACKUPKEVIN' ,'TESTSSS' ,'YUNTEST' ,'CQTEST' ,'CQUSER' ,'DEV' ,'TEST' ,'DMSYS' ,'TSMSYS') order by grantee;
  2.  
  3. GRANTEE 		       GRANTED_ROLE		      ADM DEF
  4. ------------------------------ ------------------------------ --- ---
  5. BACKUPKEVIN		       DBA			          NO  YES
  6. CQTEST			       CONNECT			        YES YES
  7. CQTEST			       DBA			            YES YES
  8. CQTEST			       RESOURCE 		        YES YES
  9. CQUSER			       CONNECT			        NO  YES
  10. CQUSER			       DBA			            NO  YES
  11. DEV			           CONNECT			        NO  YES
  12. DEV			           DBA			            NO  YES
  13. TEST			         CONNECT			        YES YES
  14. TEST			         DBA			            YES YES
  15. TESTSSS 		       CONNECT			        YES YES
  16. TESTSSS 		       DBA			            YES YES
  17. TSMSYS			       RESOURCE 		        NO  YES
  18. YUNTEST 		       CONNECT			        YES YES
  19. YUNTEST 		       DBA			            YES YES
  20.  
  21. 15 rows selected.

如果有有自定义角色，生成创建角色的sql，不含系统角色

  1. --查询用户拥有的非系统角色  
  2. SELECT * FROM dba_role_privs where grantee in ('ENTSERVICE') and granted_role not in ('ADM_PARALLEL_EXECUTE_TASK' ,'APEX_ADMINISTRATOR_ROLE' ,'AQ_ADMINISTRATOR_ROLE' ,'AQ_USER_ROLE' ,'AUTHENTICATEDUSER' ,'CONNECT' ,'CSW_USR_ROLE' ,'CTXAPP' ,'CWM_USER' ,'DATAPUMP_EXP_FULL_DATABASE' ,'DATAPUMP_IMP_FULL_DATABASE' ,'DBA' ,'DBFS_ROLE' ,'DELETE_CATALOG_ROLE' ,'EJBCLIENT' ,'EXECUTE_CATALOG_ROLE' ,'EXP_FULL_DATABASE' ,'GATHER_SYSTEM_STATISTICS' ,'GLOBAL_AQ_USER_ROLE' ,'HS_ADMIN_EXECUTE_ROLE' ,'HS_ADMIN_ROLE' ,'HS_ADMIN_SELECT_ROLE' ,'IMP_FULL_DATABASE' ,'JAVADEBUGPRIV' ,'JAVAIDPRIV' ,'JAVASYSPRIV' ,'JAVAUSERPRIV' ,'JAVA_ADMIN' ,'JAVA_DEPLOY' ,'JMXSERVER' ,'LOGSTDBY_ADMINISTRATOR' ,'MGMT_USER' ,'OEM_ADVISOR' ,'OEM_MONITOR' ,'OLAP_DBA' ,'OLAP_USER' ,'OLAP_XS_ADMIN' ,'ORDADMIN' ,'OWBCLIENT' ,'OWB_DESIGNCENTER_VIEW' ,'OWB_USER' ,'RECOVERY_CATALOG_OWNER' ,'RESOURCE' ,'SCHEDULER_ADMIN' ,'SELECT_CATALOG_ROLE' ,'SPATIAL_CSW_ADMIN' ,'SPATIAL_WFS_ADMIN' ,'WFS_USR_ROLE' ,'WM_ADMIN_ROLE' ,'XDBADMIN' ,'XDB_SET_INVOKER' ,'XDB_WEBSERVICES' ,'XDB_WEBSERVICES_OVER_HTTP' ,'XDB_WEBSERVICES_WITH_PUBLIC');
  3.  
  4. --批量创建非系统角色
  5. select 'create role '|| granted_role||';' from dba_role_privs where grantee in ('ENTSERVICE') and granted_role not in ('ADM_PARALLEL_EXECUTE_TASK' ,'APEX_ADMINISTRATOR_ROLE' ,'AQ_ADMINISTRATOR_ROLE' ,'AQ_USER_ROLE' ,'AUTHENTICATEDUSER' ,'CONNECT' ,'CSW_USR_ROLE' ,'CTXAPP' ,'CWM_USER' ,'DATAPUMP_EXP_FULL_DATABASE' ,'DATAPUMP_IMP_FULL_DATABASE' ,'DBA' ,'DBFS_ROLE' ,'DELETE_CATALOG_ROLE' ,'EJBCLIENT' ,'EXECUTE_CATALOG_ROLE' ,'EXP_FULL_DATABASE' ,'GATHER_SYSTEM_STATISTICS' ,'GLOBAL_AQ_USER_ROLE' ,'HS_ADMIN_EXECUTE_ROLE' ,'HS_ADMIN_ROLE' ,'HS_ADMIN_SELECT_ROLE' ,'IMP_FULL_DATABASE' ,'JAVADEBUGPRIV' ,'JAVAIDPRIV' ,'JAVASYSPRIV' ,'JAVAUSERPRIV' ,'JAVA_ADMIN' ,'JAVA_DEPLOY' ,'JMXSERVER' ,'LOGSTDBY_ADMINISTRATOR' ,'MGMT_USER' ,'OEM_ADVISOR' ,'OEM_MONITOR' ,'OLAP_DBA' ,'OLAP_USER' ,'OLAP_XS_ADMIN' ,'ORDADMIN' ,'OWBCLIENT' ,'OWB_DESIGNCENTER_VIEW' ,'OWB_USER' ,'RECOVERY_CATALOG_OWNER' ,'RESOURCE' ,'SCHEDULER_ADMIN' ,'SELECT_CATALOG_ROLE' ,'SPATIAL_CSW_ADMIN' ,'SPATIAL_WFS_ADMIN' ,'WFS_USR_ROLE' ,'WM_ADMIN_ROLE' ,'XDBADMIN' ,'XDB_SET_INVOKER' ,'XDB_WEBSERVICES' ,'XDB_WEBSERVICES_OVER_HTTP' ,'XDB_WEBSERVICES_WITH_PUBLIC');
  6.  
  7. --查询非系统角色拥有的角色
  8. select * from dba_role_privs where grantee in  (SELECT granted_role FROM dba_role_privs where grantee in ('ENTSERVICE') and granted_role not in ('ADM_PARALLEL_EXECUTE_TASK' ,'APEX_ADMINISTRATOR_ROLE' ,'AQ_ADMINISTRATOR_ROLE' ,'AQ_USER_ROLE' ,'AUTHENTICATEDUSER' ,'CONNECT' ,'CSW_USR_ROLE' ,'CTXAPP' ,'CWM_USER' ,'DATAPUMP_EXP_FULL_DATABASE' ,'DATAPUMP_IMP_FULL_DATABASE' ,'DBA' ,'DBFS_ROLE' ,'DELETE_CATALOG_ROLE' ,'EJBCLIENT' ,'EXECUTE_CATALOG_ROLE' ,'EXP_FULL_DATABASE' ,'GATHER_SYSTEM_STATISTICS' ,'GLOBAL_AQ_USER_ROLE' ,'HS_ADMIN_EXECUTE_ROLE' ,'HS_ADMIN_ROLE' ,'HS_ADMIN_SELECT_ROLE' ,'IMP_FULL_DATABASE' ,'JAVADEBUGPRIV' ,'JAVAIDPRIV' ,'JAVASYSPRIV' ,'JAVAUSERPRIV' ,'JAVA_ADMIN' ,'JAVA_DEPLOY' ,'JMXSERVER' ,'LOGSTDBY_ADMINISTRATOR' ,'MGMT_USER' ,'OEM_ADVISOR' ,'OEM_MONITOR' ,'OLAP_DBA' ,'OLAP_USER' ,'OLAP_XS_ADMIN' ,'ORDADMIN' ,'OWBCLIENT' ,'OWB_DESIGNCENTER_VIEW' ,'OWB_USER' ,'RECOVERY_CATALOG_OWNER' ,'RESOURCE' ,'SCHEDULER_ADMIN' ,'SELECT_CATALOG_ROLE' ,'SPATIAL_CSW_ADMIN' ,'SPATIAL_WFS_ADMIN' ,'WFS_USR_ROLE' ,'WM_ADMIN_ROLE' ,'XDBADMIN' ,'XDB_SET_INVOKER' ,'XDB_WEBSERVICES' ,'XDB_WEBSERVICES_OVER_HTTP' ,'XDB_WEBSERVICES_WITH_PUBLIC'));
  9.  
  10. --批量生成非系统角色拥有的角色
  11. select 'grant '||granted_role||' to '||grantee||';' from dba_role_privs where grantee in  (SELECT granted_role FROM dba_role_privs where grantee in ('ENTSERVICE') and granted_role not in ('ADM_PARALLEL_EXECUTE_TASK' ,'APEX_ADMINISTRATOR_ROLE' ,'AQ_ADMINISTRATOR_ROLE' ,'AQ_USER_ROLE' ,'AUTHENTICATEDUSER' ,'CONNECT' ,'CSW_USR_ROLE' ,'CTXAPP' ,'CWM_USER' ,'DATAPUMP_EXP_FULL_DATABASE' ,'DATAPUMP_IMP_FULL_DATABASE' ,'DBA' ,'DBFS_ROLE' ,'DELETE_CATALOG_ROLE' ,'EJBCLIENT' ,'EXECUTE_CATALOG_ROLE' ,'EXP_FULL_DATABASE' ,'GATHER_SYSTEM_STATISTICS' ,'GLOBAL_AQ_USER_ROLE' ,'HS_ADMIN_EXECUTE_ROLE' ,'HS_ADMIN_ROLE' ,'HS_ADMIN_SELECT_ROLE' ,'IMP_FULL_DATABASE' ,'JAVADEBUGPRIV' ,'JAVAIDPRIV' ,'JAVASYSPRIV' ,'JAVAUSERPRIV' ,'JAVA_ADMIN' ,'JAVA_DEPLOY' ,'JMXSERVER' ,'LOGSTDBY_ADMINISTRATOR' ,'MGMT_USER' ,'OEM_ADVISOR' ,'OEM_MONITOR' ,'OLAP_DBA' ,'OLAP_USER' ,'OLAP_XS_ADMIN' ,'ORDADMIN' ,'OWBCLIENT' ,'OWB_DESIGNCENTER_VIEW' ,'OWB_USER' ,'RECOVERY_CATALOG_OWNER' ,'RESOURCE' ,'SCHEDULER_ADMIN' ,'SELECT_CATALOG_ROLE' ,'SPATIAL_CSW_ADMIN' ,'SPATIAL_WFS_ADMIN' ,'WFS_USR_ROLE' ,'WM_ADMIN_ROLE' ,'XDBADMIN' ,'XDB_SET_INVOKER' ,'XDB_WEBSERVICES' ,'XDB_WEBSERVICES_OVER_HTTP' ,'XDB_WEBSERVICES_WITH_PUBLIC'));
  12.  
  13. --查看非系统角色拥有的对象权限
  14.  select * from dba_sys_privs where grantee in  (SELECT granted_role FROM dba_role_privs where grantee in ('ENTSERVICE') and granted_role not in ('ADM_PARALLEL_EXECUTE_TASK' ,'APEX_ADMINISTRATOR_ROLE' ,'AQ_ADMINISTRATOR_ROLE' ,'AQ_USER_ROLE' ,'AUTHENTICATEDUSER' ,'CONNECT' ,'CSW_USR_ROLE' ,'CTXAPP' ,'CWM_USER' ,'DATAPUMP_EXP_FULL_DATABASE' ,'DATAPUMP_IMP_FULL_DATABASE' ,'DBA' ,'DBFS_ROLE' ,'DELETE_CATALOG_ROLE' ,'EJBCLIENT' ,'EXECUTE_CATALOG_ROLE' ,'EXP_FULL_DATABASE' ,'GATHER_SYSTEM_STATISTICS' ,'GLOBAL_AQ_USER_ROLE' ,'HS_ADMIN_EXECUTE_ROLE' ,'HS_ADMIN_ROLE' ,'HS_ADMIN_SELECT_ROLE' ,'IMP_FULL_DATABASE' ,'JAVADEBUGPRIV' ,'JAVAIDPRIV' ,'JAVASYSPRIV' ,'JAVAUSERPRIV' ,'JAVA_ADMIN' ,'JAVA_DEPLOY' ,'JMXSERVER' ,'LOGSTDBY_ADMINISTRATOR' ,'MGMT_USER' ,'OEM_ADVISOR' ,'OEM_MONITOR' ,'OLAP_DBA' ,'OLAP_USER' ,'OLAP_XS_ADMIN' ,'ORDADMIN' ,'OWBCLIENT' ,'OWB_DESIGNCENTER_VIEW' ,'OWB_USER' ,'RECOVERY_CATALOG_OWNER' ,'RESOURCE' ,'SCHEDULER_ADMIN' ,'SELECT_CATALOG_ROLE' ,'SPATIAL_CSW_ADMIN' ,'SPATIAL_WFS_ADMIN' ,'WFS_USR_ROLE' ,'WM_ADMIN_ROLE' ,'XDBADMIN' ,'XDB_SET_INVOKER' ,'XDB_WEBSERVICES' ,'XDB_WEBSERVICES_OVER_HTTP' ,'XDB_WEBSERVICES_WITH_PUBLIC'));
  15.  
  16.  --批量生成非系统角色拥有的对象权限
  17.  select 'grant '||PRIVILEGE ||' to '||grantee||';' from dba_sys_privs where grantee in  (SELECT granted_role FROM dba_role_privs where grantee in ('ENTSERVICE') and granted_role not in ('ADM_PARALLEL_EXECUTE_TASK' ,'APEX_ADMINISTRATOR_ROLE' ,'AQ_ADMINISTRATOR_ROLE' ,'AQ_USER_ROLE' ,'AUTHENTICATEDUSER' ,'CONNECT' ,'CSW_USR_ROLE' ,'CTXAPP' ,'CWM_USER' ,'DATAPUMP_EXP_FULL_DATABASE' ,'DATAPUMP_IMP_FULL_DATABASE' ,'DBA' ,'DBFS_ROLE' ,'DELETE_CATALOG_ROLE' ,'EJBCLIENT' ,'EXECUTE_CATALOG_ROLE' ,'EXP_FULL_DATABASE' ,'GATHER_SYSTEM_STATISTICS' ,'GLOBAL_AQ_USER_ROLE' ,'HS_ADMIN_EXECUTE_ROLE' ,'HS_ADMIN_ROLE' ,'HS_ADMIN_SELECT_ROLE' ,'IMP_FULL_DATABASE' ,'JAVADEBUGPRIV' ,'JAVAIDPRIV' ,'JAVASYSPRIV' ,'JAVAUSERPRIV' ,'JAVA_ADMIN' ,'JAVA_DEPLOY' ,'JMXSERVER' ,'LOGSTDBY_ADMINISTRATOR' ,'MGMT_USER' ,'OEM_ADVISOR' ,'OEM_MONITOR' ,'OLAP_DBA' ,'OLAP_USER' ,'OLAP_XS_ADMIN' ,'ORDADMIN' ,'OWBCLIENT' ,'OWB_DESIGNCENTER_VIEW' ,'OWB_USER' ,'RECOVERY_CATALOG_OWNER' ,'RESOURCE' ,'SCHEDULER_ADMIN' ,'SELECT_CATALOG_ROLE' ,'SPATIAL_CSW_ADMIN' ,'SPATIAL_WFS_ADMIN' ,'WFS_USR_ROLE' ,'WM_ADMIN_ROLE' ,'XDBADMIN' ,'XDB_SET_INVOKER' ,'XDB_WEBSERVICES' ,'XDB_WEBSERVICES_OVER_HTTP' ,'XDB_WEBSERVICES_WITH_PUBLIC'));
1.1.3.4.查Profile（配置文件）
  1. select username,profile from dba_users;
  2.  
  3. USERNAME		       PROFILE
  4. ------------------------------ ------------------------------
  5. SYS			       DEFAULT
  6. TESTSSS 		       DEFAULT
  7. BACKUPKEVIN		       DEFAULT
  8. YUNTEST 		       DEFAULT
  9. DEV			       DEFAULT
  10. CQTEST			       DEFAULT
  11. CQUSER			       DEFAULT
  12. TEST			       DEFAULT
  13. SYSTEM			       DEFAULT
  14. OUTLN			       DEFAULT
  15. MGMT_VIEW		       DEFAULT
  16. MDSYS			       DEFAULT
  17. ORDSYS			       DEFAULT
  18. EXFSYS			       DEFAULT
  19. DMSYS			       DEFAULT
  20. DBSNMP			       MONITORING_PROFILE
  21. WMSYS			          DEFAULT
  22. CTXSYS			       DEFAULT
  23. ANONYMOUS		       DEFAULT
  24. SYSMAN			       DEFAULT
  25. XDB			           DEFAULT
  26. ORDPLUGINS		       DEFAULT
  27. SI_INFORMTN_SCHEMA	       DEFAULT
  28. OLAPSYS 		       DEFAULT
  29. SCOTT			         DEFAULT
  30. TSMSYS			       DEFAULT
  31. MDDATA			       DEFAULT
  32. DIP			           DEFAULT
  33.  
  34. 28 rows selected.
1.1.3.5.查dblink
select * from dba_db_links where username in ('BACKUPKEVIN' ,'TESTSSS' ,'YUNTEST' ,'CQTEST' ,'CQUSER' ,'DEV' ,'TEST' ,'DMSYS' ,'TSMSYS');

无返回

1.1.4.锁定业务用户

  1. --查看用户  和现场负责人确定哪些是业务用户，哪些是需要迁的用户
  2. set lin1000 pagesize 999
  3. 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');
  4.  
  5. USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
  6. ------------------------------ ------------------------------ ------------------------------
  7. BACKUPKEVIN		       USERS			      TEMP
  8. TESTSSS 		         USERS			      TEMP
  9. YUNTEST 		         USERS			      TEMP
  10. CQTEST			         CQSCHEMA 		    TEMP
  11. CQUSER			         CQSCHEMA 		    TEMP
  12. DEV			             CQSCHEMA 		    TEMP
  13. TEST			           CQSCHEMA 		    TEMP
  14. DMSYS			           SYSAUX			      TEMP
  15. TSMSYS			         USERS			      TEMP
  16.  
  17. 9 rows selected.
  18.  
  19. --锁定业务用户
  20. select username,account_status,lock_date from dba_users where username in ('BACKUPKEVIN' ,'TESTSSS' ,'YUNTEST' ,'CQTEST' ,'CQUSER' ,'DEV' ,'TEST' ,'DMSYS' ,'TSMSYS');
  21. USERNAME		       ACCOUNT_STATUS			LOCK_DATE
  22. ------------------------------ -------------------------------- --------------
  23. CQTEST			       OPEN
  24. CQUSER			       OPEN
  25. BACKUPKEVIN		     OPEN
  26. TESTSSS 		       OPEN
  27. DEV			           OPEN
  28. TEST			         OPEN
  29. YUNTEST 		       OPEN
  30. TSMSYS			       EXPIRED & LOCKED 		02-8Ղ -22
  31. DMSYS			         EXPIRED & LOCKED 		02-8Ղ -22
  32.  
  33. 9 rows selected.
  34.  
  35.  
  36. select 'alter user '||username||' account lock;' from dba_users where username in ('BACKUPKEVIN' ,'TESTSSS' ,'YUNTEST' ,'CQTEST' ,'CQUSER' ,'DEV' ,'TEST' ,'DMSYS' ,'TSMSYS') and ACCOUNT_STATU='OPEN';
  37. 执行以上输出结果

1.1.5.查看表空间及数据文件

  1. -- 查看表空间及数据文件位置及大小
  2. set lin 1000 pagesize 999
  3. col file_name for a55
  4. select tablespace_name,file_name,bytes/1024/1024/1024 gb,AUTOEXTENSIBLE from dba_data_files order by tablespace_name;
  5. TABLESPACE_NAME 	       FILE_NAME						       GB AUT
  6. ------------------------------ ------------------------------------------------------- ---------- ---
  7. CQSCHEMA		       /opt/oracle/oradata/cq/CQSCHEMA1.dbf		       30.4882813 YES
  8. CQSCHEMA		       /opt/oracle/oradata/cq/CQSCHEMA4.dbf			31.171875 YES
  9. CQSCHEMA		       /opt/oracle/oradata/cq/CQSCHEMA3.dbf		       30.8789063 YES
  10. CQSCHEMA		       /opt/oracle/oradata/cq/CQSCHEMA2.dbf		       31.1230469 YES
  11. CQSCHEMA		       /opt/oracle/oradata/cq/CQSCHEMA5.dbf		       30.6347656 YES
  12. SYSAUX			       /opt/oracle/oradata/cq/sysaux01.dbf			 .5078125 YES
  13. SYSTEM			       /opt/oracle/oradata/cq/system01.dbf		       .537109375 YES
  14. UNDOTBS1		       /opt/oracle/oradata/cq/undotbs01.dbf		       1.12304688 YES
  15. USERS			         /opt/oracle/oradata/cq/users01.dbf		       .434570313 YES
  16.  
  17. 9 rows selected.
  18.  
  19. --查看临时表空间及临时表空间数据文件位置及大小
  20. set lin 1000 
  21. col FILE_NAME for a60
  22. select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024/1024 total_gb,USER_BYTES/1021/1024/1024 gb from dba_temp_files;
  23.  
  24. TABLESPACE_NAME 	       FILE_NAME						      TOTAL_GB	       GB
  25. ------------------------------ ------------------------------------------------------------ ---------- ----------
  26. TEMP			       /opt/oracle/oradata/cq/temp01.dbf			    2.67285156 2.67972576
  27.  
  28. --查看控制文件位置
  29. SQL> show parameter control
  30.  
  31. NAME				     TYPE	 VALUE
  32. ------------------------------------ ----------- ------------------------------
  33. control_file_record_keep_time	     integer	 7
  34. control_files			     string	 /opt/oracle/oradata/cq/control
  35. 						                   01.ctl, /opt/oracle/oradata/cq
  36. 						                   /control02.ctl, /opt/oracle/or
  37. 						                   adata/cq/control03.ctl
  38.  
  39.  
  40. --查看redo log大小和位置
  41. set linesize 999
  42. col member for a60
  43. 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#;	
  44.  
  45.    THREAD#     GROUP# MEMBER							   BYTES/1024/1024    MEMBERS STATUS
  46. ---------- ---------- ------------------------------------------------------------ --------------- ---------- ----------------
  47. 	 1	    3 /opt/oracle/oradata/cq/redo03.log 					50	    1 INACTIVE
  48. 	 1	    2 /opt/oracle/oradata/cq/redo02.log 					50	    1 CURRENT
  49. 	 1	    1 /opt/oracle/oradata/cq/redo01.log 					50	    1 INACTIVE

1.1.6..查业务数据

  1. --每个业务用户下的总对象数量校验
  2. SELECT D.OWNER,COUNT(1)
  3.   FROM dba_objects d
  4.  WHERE d.OWNER   in ('BACKUPKEVIN' ,'TESTSSS' ,'YUNTEST' ,'CQTEST' ,'CQUSER' ,'DEV' ,'TEST' ,'DMSYS' ,'TSMSYS')
  5.  AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner) 
  6.  GROUP BY D.OWNER
  7.  ORDER BY D.OWNER ;
  8. OWNER				 COUNT(1)
  9. ------------------------------ ----------
  10. CQTEST				      407
  11. CQUSER				      109
  12. DEV				          458
  13. DMSYS				        189
  14. TEST				        326
  15. TESTSSS 			       36
  16. TSMSYS					     3
  17. YUNTEST 			      452
  18.  
  19. 8 rows selected.
  20.  
  21.  
  22. --每个业务用户下的各个对象类别的数量校验
  23. set pagesize 999
  24. select OWNER,OBJECT_TYPE,status,count(OBJECT_NAME) from dba_objects  where owner in ('BACKUPKEVIN' ,'TESTSSS' ,'YUNTEST' ,'CQTEST' ,'CQUSER' ,'DEV' ,'TEST' ,'DMSYS' ,'TSMSYS')  group by OBJECT_TYPE,owner,status order by 1,3,2;
  25.  
  26. OWNER			       OBJECT_TYPE	   STATUS  COUNT(OBJECT_NAME)
  27. ------------------------------ ------------------- ------- ------------------
  28. CQTEST			       INDEX		   VALID		  161
  29. CQTEST			       LOB		     VALID		  156
  30. CQTEST			       TABLE		   VALID		   90
  31. CQUSER			       INDEX		   VALID		   51
  32. CQUSER			       LOB		     VALID		   11
  33. CQUSER			       TABLE		   VALID		   47
  34. DEV			           INDEX		   VALID		  179
  35. DEV			           LOB		     VALID		  162
  36. DEV			           PROCEDURE	 VALID		    4
  37. DEV			           TABLE		   VALID		  103
  38. DEV			           VIEW		     VALID		   10
  39. DMSYS			         FUNCTION 	   VALID		   12
  40. DMSYS			         INDEX		   VALID		    2
  41. DMSYS			         LIBRARY		   VALID		    6
  42. DMSYS			         PACKAGE		   VALID		   27
  43. DMSYS			         PACKAGE BODY	   VALID		   23
  44. DMSYS			         PROGRAM		   VALID		    9
  45. DMSYS			         SEQUENCE 	   VALID		    1
  46. DMSYS			         TABLE		   VALID		    2
  47. DMSYS			         TYPE		   VALID		   94
  48. DMSYS			         TYPE BODY	   VALID		   12
  49. DMSYS			         VIEW		   VALID		    1
  50. TEST			         INDEX		   VALID		  137
  51. TEST			         LOB		   VALID		  110
  52. TEST			         TABLE		   VALID		   79
  53. TESTSSS 		       INDEX		   VALID		   20
  54. TESTSSS 		       LOB		   VALID		    5
  55. TESTSSS 		       TABLE		   VALID		   11
  56. TSMSYS			       INDEX		   VALID		    1
  57. TSMSYS			       LOB		   VALID		    1
  58. TSMSYS			       TABLE		   VALID		    1
  59. YUNTEST 		       INDEX		   VALID		  172
  60. YUNTEST 		       LOB		   VALID		  175
  61. YUNTEST 		       PROCEDURE	   VALID		    4
  62. YUNTEST 		       TABLE		   VALID		   97
  63. YUNTEST 		       VIEW		   VALID		    4
  64.  
  65. 36 rows selected.
  66.  
  67.  
  68. --查询回收站lob对象数量(可选),lob数量不一致排查
  69. select owner,type,count(*) from dba_recyclebin group by owner,type order by owner;
  70. 无返回
  71.  
  72. --数据量统计
  73. ----新增count_rows 函数
  74. CREATE OR REPLACE FUNCTION count_rows (
  75. 	table_name IN varchar2, 
  76. 	owner IN varchar2 := NULL
  77. )
  78. RETURN number AUTHID current_user
  79. AS
  80. num_rows number;
  81. 	stmt varchar2(2000);
  82. BEGIN
  83. 	IF owner IS NULL THEN
  84. 		stmt := 'select count(*) from "' || table_name || '"';
  85. 	ELSE
  86. 		stmt := 'select count(*) from "' || owner || '"."' || table_name || '"';
  87. 	END IF;
  88. 	EXECUTE IMMEDIATE stmt INTO num_rows;
  89. 	RETURN num_rows;
  90. END;
  91. /
  92. ---调用count_rows 函数，统计各种表的记录数
  93. select table_name, count_rows(table_name) nrows from user_tables  where tablespace_name='ENTSERVICE'
  94. order by nrows desc;

1.2.查看新库信息

1.2.1.查看服务器配置

确保新服务器配置等于或高于旧服务器。

1.2.2.1.查看操作系统版本
  1. [root@ecs-677a ~]# cat /etc/redhat-release
  2. CentOS release 6.10 (Final)
1.2.2.2.查看磁盘空间
  1. [root@ecs-677a ~]#  df -Th
  2. Filesystem           Type   Size  Used Avail Use% Mounted on
  3. /dev/vda1            ext4    40G  1.6G   36G   5% /
  4. tmpfs                tmpfs  3.9G     0  3.9G   0% /dev/shm
  5. /dev/mapper/data-data01
  6.                      ext4   689G   69M  654G   1% /data
1.2.2.3.cpu配置
  1. [root@ecs-677a ~]# lscpu
  2. Architecture:          x86_64
  3. CPU op-mode(s):        32-bit, 64-bit
  4. Byte Order:            Little Endian
  5. CPU(s):                4
  6. On-line CPU(s) list:   0-3
  7. Thread(s) per core:    2
  8. Core(s) per socket:    2
  9. Socket(s):             1
  10. NUMA node(s):          1
  11. Vendor ID:             GenuineIntel
  12. CPU family:            6
  13. Model:                 85
  14. Model name:            Intel(R) Xeon(R) Gold 6161 CPU @ 2.20GHz
  15. Stepping:              4
  16. CPU MHz:               2200.000
  17. BogoMIPS:              4400.00
  18. Hypervisor vendor:     KVM
  19. Virtualization type:   full
  20. L1d cache:             32K
  21. L1i cache:             32K
  22. L2 cache:              1024K
  23. L3 cache:              30976K
  24. NUMA node0 CPU(s):     0-3
1.2.2.4.内存配置
  1. [root@cq ~]# awk '/MemTotal/{print $2}' /proc/meminfo  K
  2. 8060540   --单位
  3.  
  4. 或 
  5.  
  6. [root@cq ~]# free -g
  7. [root@ecs-677a ~]# free -g
  8.              total       used       free     shared    buffers     cached
  9. Mem:             7          0          7          0          0          0
  10. -/+ buffers/cache:          0          7
  11. Swap:            0          0          0
1.2.2.5.磁盘IO测速
1.2.2.5.1.创建存放测速文件的目录
  1. --创建存放测速文件的目录
  2. [root@ecs-677a ~]# ls -ld /ddtest  --确定不存在该目录
  3. [root@ecs-677a ~]# mkdir -p /ddtest
  4. [root@ecs-677a ~]# cd /ddtest
  5. [root@ecs-677a ddtest]# ls -l
  6. total 0
1.2.2.5.2.测试纯写入性能
  1. --测试纯写入性能  连续执行3次取平均值
  2. [root@ecs-677a ddtest]# dd if=/dev/zero of=test bs=4G count=1 oflag=direct
  3. 0+1 records in
  4. 0+1 records out
  5. 2147479552 bytes (2.1 GB) copied, 18.0524 s, 119 MB/s
  6. [root@ecs-677a ddtest]# dd if=/dev/zero of=test01 bs=4G count=1 oflag=direct
  7. 0+1 records in
  8. 0+1 records out
  9. 2147479552 bytes (2.1 GB) copied, 17.8508 s, 120 MB/s
  10. [root@ecs-677a ddtest]# dd if=/dev/zero of=test02 bs=4G count=1 oflag=direct
  11. 0+1 records in
  12. 0+1 records out
  13. 2147479552 bytes (2.1 GB) copied, 17.7979 s, 121 MB/s
1.2.2.5.3.测试纯读取性能
  1.  
  2. --测试纯读取性能 连续执行3次取平均值
  3. [root@ecs-677a ddtest]# dd if=test of=/dev/null bs=4G count=1 iflag=direct
  4. 0+1 records in
  5. 0+1 records out
  6. 2147479552 bytes (2.1 GB) copied, 16.1827 s, 133 MB/s
  7. [root@ecs-677a ddtest]# dd if=test01 of=/dev/null bs=4G count=1 iflag=direct
  8. 0+1 records in
  9. 0+1 records out
  10. 2147479552 bytes (2.1 GB) copied, 16.5974 s, 129 MB/s
  11. [root@ecs-677a ddtest]# dd if=test02 of=/dev/null bs=4G count=1 iflag=direct
  12. 0+1 records in
  13. 0+1 records out
  14. 2147479552 bytes (2.1 GB) copied, 16.7011 s, 129 MB/s

1.3.新服务器配置

1.3.1.配置主机名

  1. --查看主机名
  2. [root@ecs-677a ddtest]# cat /etc/sysconfig/network
  3. NETWORKING=yes
  4. HOSTNAME=ecs-677a
  5. RES_OPTIONS='timeout:1 single-request-reopen'
  6.  
  7. --配置主机名
  8. /bin/hostname cqnew
  9. sysctl kernel.hostname="cqnew"
  10. echo "cqnew" >/proc/sys/kernel/hostname
  11. sed -i "s/^HOSTNAME=.*/HOSTNAME=cqnew/" /etc/sysconfig/network
  12.  
  13. --查看主机名
  14. hostname

1.3.2.配置/etc/hosts

如果是弹性云服务器不能配置为对外访问的ip 120.46.146.123 需配置成实际网卡的ip，不然后面监听启动报错。

  1. --查看ip地址
  2. [oracle@cqnew admin]$ ip addr
  3. 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
  4.     link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
  5.     inet 127.0.0.1/8 scope host lo
  6.     inet6 ::1/128 scope host
  7.        valid_lft forever preferred_lft forever
  8. 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
  9.     link/ether fa:16:3e:5d:b5:d8 brd ff:ff:ff:ff:ff:ff
  10.     inet 192.168.0.35/24 brd 192.168.0.255 scope global eth0
  11.     inet6 fe80::f816:3eff:fe5d:b5d8/64 scope link
  12.        valid_lft forever preferred_lft forever
  13.        
  14. --配置/etc/hosts
  15. [root@ecs-677a ddtest]# vi /etc/hosts
  16. ::1	localhost	localhost.localdomain	localhost6	localhost6.localdomain6
  17. 127.0.0.1	localhost	localhost.localdomain	localhost4	localhost4.localdomain4
  18. #127.0.0.1	ecs-677a	ecs-677a
  19. 192.168.0.35 cqnew

1.3.3.设置系统服务 

禁用一些不必要的系统服务

  1. --Linux/Centos 6.10环境下
  2. chkconfig iptables off
  3. chkconfig ip6tables off
  4. chkconfig postfix off

Linux/Centos 6.5 采用以下

  1. chkconfig iptables off
  2. chkconfig ip6tables off
  3. chkconfig cups off
  4. chkconfig firstboot off
  5. chkconfig wpa_supplicant off
  6. chkconfig postfix off

1.3.4.禁用透明大页

  1. --临时禁用
  2. echo never > /sys/kernel/mm/transparent_hugepage/enabled
  3. echo never > /sys/kernel/mm/transparent_hugepage/defrag
  4.  
  5. --加入开机启动
  6. echo '
  7. echo never > /sys/kernel/mm/transparent_hugepage/enabled
  8. echo never > /sys/kernel/mm/transparent_hugepage/defrag' >>/etc/rc.local
  9. chmod +x /etc/rc.local
  10.  
  11. --检查是否关闭透明大页的命令
  12. grep Huge /proc/meminfo

1.3.5.关闭numa

注意Centos6和Centos7的配置不一样。

  1. --检查检查OS是否开启NUMA
  2. # numactl --hardware     检查 numa 节点信息
  3.  
  4. available: 1 nodes (0)       #如果是2或多个nodes就说明numa没关掉
  5.  
  6. --关闭 numa
  7. numastat  #查看
  8.  
  9. vi /etc/grub.conf  设置 numa=off
  10.  
  11. 查看每个节点的分配情况
  12.  
  13. 【1】numastat　　【2】numactl --show
  14.  
  15. 然后重启生效，reboot

注意：numa=off 要写在内核后

1.3.5.用在线安装方式常用软件及必需软件 

安装kernel-devel和kernel-headers，并且在更新系统时，禁止更新kernel

yum -y install kernel-devel kernel-headers && echo exclude=kernel*

更新yum

yum -y update

安装常用软件

yum -y install autoconf automake binutils-devel bison cpp dos2unix ftp gcc gcc-c++ lrzsz python-devel

安装Oracle必需软件

yum -y install compat-db compat-gcc-34 compat-gcc-34-c++ compat-libstdc++-33 glibc-* glibc-*.i686 libXpm-*.i686 libXp.so.6 libXt.so.6 libXtst.so.6 libgcc_s.so.1 ksh libXp libaio-devel numactl numactl-devel unixODBC unixODBC-devel

注：安装完成以上软件后，重启系统。

1.3.6.检查或配置交换空间

若swap>=2G，跳过该步骤，

若swap=0，则执行以下操作

  1. --检查内存配置
  2. [root@cqnew install]# free -g
  3.              total       used       free     shared    buffers     cached
  4. Mem:             7          7          0          0          0          6
  5. -/+ buffers/cache:          0          7
  6. Swap:            0          0          0
  7.  
  8. --创建指定大小的空文件 /swapfile，并将其格式化为交换分区
  9. dd if=/dev/zero of=/data/swapfile bs=2G count=8
  10.  
  11. --设置文件权限为 0600
  12. chmod 600 /data/swapfile
  13.  
  14. --格式化文件为 Swap 分区
  15. mkswap /data/swapfile
  16.  
  17. --启用 Swap 分区
  18. swapon /data/swapfile
  19.  
  20. --将 Swap 分区信息添加到 /etc/fstab 文件中，以便系统重启后自动加载
  21. cp /etc/fstab /etc/fstab_bak_20240511
  22. echo "/data/swapfile none swap sw 0 0" >> /etc/fstab
  24. --手动挂载所有磁盘
  25. mount -a
  26.  
  27. --查看内存  已经有swap了
  28. [root@cqnew install]# free -g
  29.              total       used       free     shared    buffers     cached
  30. Mem:             7          5          2          0          0          5
  31. -/+ buffers/cache:          0          7
  32. Swap:           15          0         15

1.3.7.创建Oracle相关用户和目录

  1. --创建oracle组及用户
  2. groupadd oinstall
  3. groupadd dba
  4. useradd -g oinstall -G dba oracle
  5. passwd oracle
  6.  
  7. --创建安装目录
  8. mkdir -p /data/app/oracle
  9. mkdir -p /data/app/oracle/oraInventory
  10. mkdir -p /data/app/oracle/product/10.2.0.1/dbhome_1
  11. chown -R oracle.oinstall /data/app/oracle/
  12. chmod -R 755 /data/app/oracle/
  13.  
  14. --创建数据目录
  15. mkdir -p /data/oracle/oradata/cq
  16. chown -R oracle.oinstall /data/oracle/oradata/cq
  17. chmod -R 755 /data/oracle/oradata/cq

1.3.8.修改内核参数

  1. -物理内存（KB）
  2. os_memory_total=$(awk '/MemTotal/{print $2}' /proc/meminfo)
  3.  
  4. --获取系统页面大小，用于计算内存总量
  5. pagesize=$(getconf PAGE_SIZE)
  6.  
  7. min_free_kbytes = $os_memory_total / 250
  8.  
  9. shmall = ($os_memory_total - 1) * 1024 / $pagesize
  10.  
  11. shmmax = $os_memory_total * 1024 - 1
  12.  
  13.   # 如果 shmall 小于 2097152，则将其设为 2097152
  14.   (($shmall < 2097152)) && shmall=2097152
  15.   # 如果 shmmax 小于 4294967295，则将其设为 4294967295
  16.   (($shmmax < 4294967295)) && shmmax=4294967295
  17.  
  18.   
  19. $shmall
  20. $shmmax
  21. $min_free_kbytes
  22. $local_ifname
  1. [root@cqnew ~]# cp /etc/sysctl.conf /etc/sysctl.conf_bak_20240511
  2. [root@cqnew ~]# cat /etc/sysctl.conf
  3. # Controls IP packet forwarding
  4. net.ipv4.ip_forward = 0
  5. # Controls source route verification
  6. net.ipv4.conf.all.rp_filter=0
  7. net.ipv4.conf.default.rp_filter = 0
  8. # Do not accept source routing
  9. net.ipv4.conf.default.accept_source_route = 0
  10. net.ipv4.conf.default.arp_announce = 2
  11. net.ipv4.conf.lo.arp_announce=2
  12. net.ipv4.conf.all.arp_announce=2
  13. # Controls the System Request debugging functionality of the kernel
  14. kernel.sysrq = 0
  15. # Controls whether core dumps will append the PID to the core filename.
  16. # Useful for debugging multi-threaded applications.
  17. kernel.core_uses_pid = 1
  18. # Controls the use of TCP syncookies
  19. net.ipv4.tcp_syncookies = 1
  20. net.ipv4.tcp_synack_retries = 2
  21. # Controls the default maxmimum size of a mesage queue
  22. kernel.msgmnb = 65536
  23. # Controls the maximum size of a message, in bytes
  24. kernel.msgmax = 65536
  25. # Controls the maximum shared segment size, in bytes
  26. kernel.shmmax = 68719476736
  27. # Controls the maximum number of shared memory segments, in pages
  28. kernel.shmall = 2015134
  29. kernel.shmmni = 4096
  30. kernel.sem = 250 32000 100 128
  31. fs.file-max = 65536
  32. net.ipv4.ip_local_port_range = 1024 65000
  33. net.core.rmem_default=4194304
  34. net.core.rmem_max=4194304
  35. net.core.wmem_default=262144
  36. net.core.wmem_max=262144
  37. fs.aio-max-nr = 1048576
  38. vm.swappiness=0
  39. net.core.somaxconn=1024
  40. net.ipv4.tcp_max_tw_buckets=5000
  41. net.ipv4.tcp_max_syn_backlog=1024
  42. net.ipv4.neigh.default.gc_stale_time=120
  43.  
  44. --使设置生效
  45. sysctl -p

1.3.9.配置图形化界面

如果不配置，后面安装过程中调用图形化界面会报错：

  1. yum groupinstall -y "X Window System"
  2. yum groupinstall -y "Desktop"
  3. yum groupinstall -y "Chinese Support"

如果是Centos7

  1. yum groupinstall "GNOMEDesktop" -y
  2. yum groupinstall -y "X Window System"

1.3.10.Oracle用户设置Shell限制 

编辑/etc/security/limits.conf
  1. cp /etc/security/limits.conf /etc/security/limits.conf_bak_20240511
  2. vim /etc/security/limits.conf 
  3. //最后添加如下参数
  4. #add by oracle
  5. oracle soft nproc 16384
  6. oracle hard nproc 16384
  7. oracle soft nofile 65536
  8. oracle hard nofile 65536
  9. oracle soft memlock 6542450944
  10. oracle hard memlock 6542450944
编辑/etc/pam.d/login
  1. cp /etc/pam.d/login /etc/pam.d/login_bak_20240511
  2.  
  3. vim /etc/pam.d/login 
  4. 最后添加如下参数
  5. session    required     /lib/security/pam_limits.so
  6. session    required     pam_limits.so
编辑/etc/profile
  1. cp /etc/profile /etc/profile_bak_20240511
  2. vim /etc/profile
  3. 最后添加如下内容：
  4. #add by oracle
  5. if [ root = "oracle" ]; then
  6.    if [ /bin/bash = "/bin/ksh" ]; then
  7.       ulimit -p 16384
  8.       ulimit -n 65536
  9.       else
  10.       ulimit -u 16384 -n 65536
  11.    fi
  12. fi
  13.  
  14. source /etc/profile
编辑/etc/csh.login
  1. cp /etc/csh.login /etc/csh.login_bak_20240511
  2. vim /etc/csh.login
  3. 最后添加如下内容：
  4. #add by oracle
  5. if ( $USER == "oracle") then
  6.     limit maxproc 16384
  7.     limit descriptors 65536
  8. endif
编辑/home/oracle/.bash_profile
  1. cp /home/oracle/.bash_profile /home/oracle/.bash_profile_bak_20240511
  2. vim /home/oracle/.bash_profile
  3. 最后添加如下内容：
  4. #add by oracle
  5. umask 022
  6. export LANG=en_US.UTF-8
  7. export ORACLE_BASE=/data/app/oracle
  8. export ORACLE_HOME=/data/app/oracle/product/10.2.0.1/dbhome_1
  9. export ORACLE_HOSTNAME=Centos3
  10. export ORACLE_SID=cqnew
  11. export NLS_LANG="SIMPLIFIED CHINESE_CHINA".ZHS16GBK
  12. export PATH=$PATH:$ORACLE_HOME/bin

1.3.10.上传Oracle安装包上传至服务器并解压

将Oracle服务端10201_database_linux_x86_64.cpio.gz安装包上传至服务器，保存在/data目录下面。

在/data目录下面会生成一个名为database的目录。

  1. cd /data
  2. [root@cqnew data]# ls -l
  3. total 740684
  4. -rw-r--r-- 1 oracle oinstall 758433170 May 11 10:21 10201_database_linux_x86_64.cpio.gz
  5.  
  6. --解压安装包
  7. cd /data
  8. [root@cqnew data]# zcat 10201_database_linux_x86_64.cpio.gz | cpio -idmv > /dev/null 2>&1
  9. [root@cqnew data]# ls -l
  10. total 740684
  11. -rw-r--r-- 1 oracle oinstall 758433170 May 11 10:21 10201_database_linux_x86_64.cpio.gz
  12. drwxr-xr-x 6  94110    42424      4096 Oct 23  2005 database
  13.  
  14. --赋予权限
  15. [root@cqnew data]# chown -R oracle:oinstall database/
  16.  
  17. [root@cqnew data]# ls -l
  18. total 740684
  19. -rw-r--r-- 1 oracle oinstall 758433170 May 11 10:21 10201_database_linux_x86_64.cpio.gz
  20. drwxr-xr-x 6 oracle oinstall      4096 Oct 23  2005 database

以上步骤完成后，重启服务器验证下重启正常。

二.安装数据库软件

2.1.更改参数文件

  1. --解压安装包
  2. su - oracle
  3. cd /data
  4. zcat 10201_database_linux_x86_64.cpio.gz | cpio -idmv > /dev/null 2>&1

在/data目录下面会生成一个名为database的目录，进入database目录。 

编辑install目录下面的oraparam.ini文件，在“Certified Versions”的项目中添加“redhat-6”。

  1. su - oracle
  2. cd /data/database/install/
  3. cp oraparam.ini oraparam.ini_bak_20240511
  4. vim oraparam.ini
  5. [Certified Versions]
  6. Linux=redhat-6,redhat-3,SuSE-9,redhat-4,UnitedLinux-1.0,asianux-1,asianux-2

2.2.开始安装

2.2.1.安装数据库软件

  1. su - oracle
  2. export DISPLAY=192.168.16.122:0.0
  3. cd /data/database/
  4. export LANG=en_US.UTF-8
  5. ./runInstaller
选择高级安装

选择高级安装“Advanced Installation”，然后Next：

指定产品清单目录和身份证明

指定产品清单目录和身份证明，默认即可：

选择安装类型

选择安装类型，这里选择企业版“Enterprise Edition”，点击“Product Language”按钮可以进行产品语言的选择：

指定安装主目录

指定安装主目录的详细信息，这里默认即可，点击Next:

产品特定的先决条件检查

产品特定的先决条件检查（注：一般会出现warning），默认即可，点击Next：

出现如下的Warning窗口，点击“Yes”继续：

只安装数据库软件

安装过程中会有报错画面，直接“Continue”即可

执行脚本

执行以下脚本后点ok，继续

  1. /data/app/oracle/oraInventory/orainstRoot.sh
  2. /data/app/oracle/product/10.2.0.1/dbhome_1/root.sh

脚本执行详细过程：

  1. [root@cqnew bin]# /data/app/oracle/oraInventory/orainstRoot.sh
  2. Changing permissions of /data/app/oracle/oraInventory to 770.
  3. Changing groupname of /data/app/oracle/oraInventory to oinstall.
  4. The execution of the script is complete
  5.  
  6.  
  7. [root@cqnew bin]# /data/app/oracle/product/10.2.0.1/dbhome_1/root.sh
  8. Running Oracle10 root.sh script...
  9.  
  10. The following environment variables are set as:
  11.     ORACLE_OWNER= oracle
  12.     ORACLE_HOME=  /data/app/oracle/product/10.2.0.1/dbhome_1
  13.  
  14. Enter the full pathname of the local bin directory: [/usr/local/bin]:
  15.    Copying dbhome to /usr/local/bin ...
  16.    Copying oraenv to /usr/local/bin ...
  17.    Copying coraenv to /usr/local/bin ...
  18.  
  19.  
  20. Creating /etc/oratab file...
  21. Entries will be added to the /etc/oratab file as needed by
  22. Database Configuration Assistant when a database is created
  23. Finished running generic part of root.sh script.
  24. Now product-specific root actions will be performed.

安装完成，退出安装。

问题处理
调用图形化失败

如果调用图形化失败，执行下面进行配置图形化界面操作

如果不配置，后面安装过程中调用图形化界面会报错：

  1. yum groupinstall -y "X Window System"
  2. yum groupinstall -y "Desktop"
  3. yum groupinstall -y "Chinese Support"

如果是Centos7

  1. yum groupinstall "GNOMEDesktop" -y
  2. yum groupinstall -y "X Window System"
图形化界面乱码
  1. echo $LANG
  2. export LANG=en_US.UTF-8

三.迁移过程

3.1.传输物理文件

主要传输文件参考：

1.数据文件

2.控制文件

3.重做日志文件

4.参数文件

5.监听文件tnsnames.ora listener.ora

6.密码文件PWDfile

直接拷贝oracle目录下的admin、oradata(datafile， controlfile，redo)、flash_recovery_area三个文件夹，db_1目录下database(PWDfile、pfile)、dbs(spfile)、NETWORK/ADMIN(listener.ora、tnsnames.ora)，到其他存储实现备份。

3.1.1.传输参数文件

3.1.1.1.源库传输参数文件至目标库

将源库/home/oracle/initcq_bak_20240607拷贝到目标库的/home/oracle目录。

  1. --源库上操作  传输参数文件
  2. su - oracle
  3. [oracle@cq ~]$ scp  initcq_bak_20240607 oracle@120.46.146.123:/home/oracle/

由于源库和目标库内存cpu磁盘配置一样，因此参数文件不用更改内存参数，只用改路径就可以了。

3.1.1.2.目标库更改参数文件

参数文件中

*.audit_file_dest='/data/app/oracle/admin/cq/adump' 为$ORACLE_BASE目录/admin/cq/adump

.control_files中control01.ctl、control02.ctl、control03.ctl路径为数据目录/cq/control01.ctl

.db_recovery_file_dest为$ORACLE_BASE目录/fast_recovery_area

.diagnostic_dest为$ORACLE_BASE目录

  1. --目标库操作  备份已传输的参数文件，避免改的有问题重新从源库服务器传输到目标库服务器
  2. [oracle@cqnew:/home/oracle]$ cp initcq_bak_20240607 initcq_bak_20240607_1
  3.  
  4. --更改参数文件中的oracle_base目录为目标库的oracle_base目录
  5. su - oracle
  6. vi initcq_bak_20240607
  7. :%s#旧目录#新目录#g

更改后记得确认，避免更改错误。

注意事项：如果源服务器和目标服务器的内存和cpu配置不一样，需修改内存和cpu方面的参数，内存公式如下：

内存参数修改公式：

--sga和pga配置的最佳实践

对于OLTP系统来说，

--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

对于DSS系统来说，oracle建议是sga=系统内存*0.8*0.5 单位字节

pga=系统内存*0.8*0.5

更改前
  1.  
  2. 更改initcq_bak_20240607参数文件
  3. su - oracle
  4. vi initcq_bak_20240607
  5. --更改前
  6. cq.__db_cache_size=3858759680
  7. cq.__java_pool_size=16777216
  8. cq.__large_pool_size=16777216
  9. cq.__shared_pool_size=385875968
  10. cq.__streams_pool_size=0
  11. *.aq_tm_processes=0
  12. *.audit_file_dest='/opt/oracle/admin/cq/adump'
  13. *.background_dump_dest='/opt/oracle/admin/cq/bdump'
  14. *.compatible='10.2.0.1.0'
  15. *.control_files='/opt/oracle/oradata/cq/control01.ctl','/opt/oracle/oradata/cq/control02.ctl','/opt/oracle/oradata/cq/control03.ctl'
  16. *.core_dump_dest='/opt/oracle/admin/cq/cdump'
  17. *.db_block_size=8192
  18. *.db_domain=''
  19. *.db_file_multiblock_read_count=16
  20. *.db_files=500
  21. *.db_name='cq'
  22. *.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
  23. *.db_recovery_file_dest_size=21474836480
  24. *.dispatchers='(PROTOCOL=TCP) (SERVICE=cqXDB)'
  25. *.filesystemio_options='setall'
  26. *.job_queue_processes=1000
  27. *.open_cursors=1000
  28. *.pga_aggregate_target=1073741824
  29. *.processes=1000
  30. *.remote_login_passwordfile='EXCLUSIVE'
  31. *.sessions=1450
  32. *.sga_target=4294967296
  33. *.undo_management='AUTO'
  34. *.undo_tablespace='UNDOTBS1'
  35. *.user_dump_dest='/opt/oracle/admin/cq/udump'
更改后
  1. *.aq_tm_processes=0
  2. *.audit_file_dest='/data/app/oracle/admin/cq/adump'
  3. *.background_dump_dest='/data/app/oracle/admin/cq/bdump'
  4. *.compatible='10.2.0.1.0'
  5. *.control_files='/data/oracle/oradata/cq/control01.ctl','/data/oracle/oradata/cq/control02.ctl','/data/oracle/oradata/cq/control03.ctl'
  6. *.core_dump_dest='/data/app/oracle/admin/cq/cdump'
  7. *.db_block_size=8192
  8. *.db_domain=''
  9. *.db_file_multiblock_read_count=16
  10. *.db_files=500
  11. *.db_name='cq'
  12. *.db_recovery_file_dest='/data/app/oracle/flash_recovery_area'
  13. *.db_recovery_file_dest_size=21474836480
  14. *.dispatchers='(PROTOCOL=TCP) (SERVICE=cqXDB)'
  15. *.filesystemio_options='setall'
  16. *.job_queue_processes=1000
  17. *.open_cursors=1000
  18. *.pga_aggregate_target=1073741824
  19. *.processes=1000
  20. *.remote_login_passwordfile='EXCLUSIVE'
  21. *.sessions=1450
  22. *.sga_target=4294967296
  23. *.undo_management='AUTO'
  24. *.undo_tablespace='UNDOTBS1'
  25. *.user_dump_dest='/data/app/oracle/admin/cq/udump'
3.1.1.3.目标库创建参数文件中不存在的目录

目标库所在服务器上创建参数文件中涉及的不存在的目录，从上到下先查目录是否存在若不存在务必创建避免后面步骤报错。

  1. --$ORACLE_BASE目录
  2. [oracle@cqnew ~]$ ls -ld /data/app/oracle
  3. drwxr-xr-x 7 oracle oinstall 4096 May 14 11:10 /data/app/oracle
  4.  
  5. --审计目录
  6. [oracle@cqnew:/home/oracle]$ ls -ld /data/app/oracle/admin/cq/adump
  7. ls: cannot access /data/app/oracle/admin/cq/adump: No such file or directory
  8.  
  9. [oracle@cqnew:/home/oracle]$ mkdir -p /data/app/oracle/admin/cq/adump
  10.  
  11. [oracle@cqnew ~]$ ls -ld /data/app/oracle/admin/cq/adump
  12. drwxr-xr-x 2 oracle oinstall 4096 Jun  8 11:01 /data/app/oracle/admin/cq/adump
  13.  
  14.  
  15. --数据目录 使用新的数据目录，老的数据目录不创建，新的数据目录上面已创建
  16. [oracle@cqnew:/home/oracle]$ ls -ld /opt/oracle/oradata/cq  老的数据目录
  17. ls: cannot access /opt/oracle/oradata/cq: No such file or directory
  18.  
  19. [oracle@cqnew:/home/oracle]$ mkdir -p /data/oracle/oradata/cq/
  20. [oracle@cqnew:/home/oracle]$ chown -R oracle:oinstall /data/oracle/oradata/cq/
  21.  
  22. [oracle@cqnew:/home/oracle]$ ls -ld /data/oracle/oradata/cq  新的数据目录
  23. drwxr-xr-x 2 oracle oinstall 4096 Jun  8 07:17 /data/oracle/oradata/cq
  24.  
  25. --诊断目录
  26. [oracle@cqnew:/home/oracle]$ ls -ld /data/app/oracle/admin/cq/bdump
  27. ls: cannot access /data/app/oracle/admin/cq/bdump: No such file or directory
  28.  
  29.  
  30. [oracle@cqnew:/home/oracle]$ ls -ld /data/app/oracle/admin/cq/cdump
  31. ls: cannot access /data/app/oracle/admin/cq/cdump: No such file or directory
  32.  
  33. [oracle@cqnew:/home/oracle]$ ls -ld /data/app/oracle/admin/cq/udump 
  34. ls: cannot access /data/app/oracle/admin/cq/udump: No such file or directory
  35.  
  36. [oracle@cqnew:/home/oracle]$ mkdir -p /data/app/oracle/admin/cq/{bdump,cdump,udump}
3.1.1.4.目标库start nomount启库
  1. su - oracle
  2. [oracle@cqnew:/home/oracle]$ export ORACLE_SID=cq
  3. [oracle@cqnew:/home/oracle]$ sqlplus / as sysdba
  4. SQL*Plus: Release 10.2.0.1.0 - Production on чǚ¹ 6Ղ 8 11:58:17 2024
  6. Copyright (c) 1982, 2013, Oracle.  All rights reserved.
  7.  
  8. Connected to an idle instance.
  9.  
  10. SQL> startup nomount pfile='/home/oracle/initcq_bak_20240607';
  11. ORACLE instance started.
  12.  
  13. Total System Global Area 8.5516E+10 bytes
  14. Fixed Size		    2262656 bytes
  15. Variable Size		 1.1543E+10 bytes
  16. Database Buffers	 7.3820E+10 bytes
  17. Redo Buffers		  151117824 bytes
  18. SQL>
3.1.1.5.目标库生成spfile参数文件(勿忘记)

该步骤不可省略，因为启库默认查找$ORACLE_HOME/dbs下的spfilesid.ora文件，如果不存在

  1. SQL> create spfile from pfile='/home/oracle/initcq_bak_20240607';
  2. SQL> shutdown immediate;
  3. SQL> startup nomount;  --启动到nomount状态

3.2.传输控制文件

源库传输控制文件至目标库

源库控制文件位置：

/opt/oracle/oradata/cq/control01.ctl

/opt/oracle/oradata/cq/control02.ctl

/opt/oracle/oradata/cq/control03.ctl

目标库控制文件位置：

/data/oracle/oradata/cq/control01.ctl

/data/oracle/oradata/cq/control02.ctl

/data/oracle/oradata/cq/control03.ctl

  1. --源库操作 control01.ctl   control02.ctl   control03.ctl传输
  2. su - oracle
  3. cd /opt/oracle/oradata/cq/
  4. [oracle@cq ~]$ scp *.ctl oracle@120.46.146.123:/data/oracle/oradata/cq/

目标库启库至mount状态

  1. --目标库操作 启库到mount状态 
  2. SQL> alter database mount;  --启动到mount状态

3.3.传输数据文件

源库数据文件位置：

/opt/oracle/oradata/cq/

目标库数据文件位置：/data/oracle/oradata/cq/

本文档步骤采用目标库和源库数据文件位置不一样，新的数据目录需提前创建。

  1. mkdir -p /data/oracle/oradata/cq/
  2. chown -R oracle:oinstall /data/oracle/oradata/cq/

目标库目录检查

目标库操作 检查数据文件所有目录是否存在，若不存在，创建不存在的数据目录。

  1. --目标库操作 创建不存在的新的数据目录  上面步骤已创建
  2. [oracle@cqnew:/home/oracle]$ ls -ld /data/oracle/oradata/cq/
  3. drwxr-xr-x 2 oracle oinstall 4096 Jun  8 07:17 /data/oracle/oradata/cq/
  4.  
  5. [root@cqnew:/root]$ mkdir -p /data/oracle/oradata/cq/
  6. [root@cqnew:/root]$ chown -R oracle:oinstall /data/oracle/oradata/cq/

源库传输数据文件至目标库

scp方式(文件小)
  1. --源库操作  传输数据文件
  2. cd /opt/oracle/oradata/cq/
  3. [oracle@cq cq]$ ls -lhtr
  4. total 163G
  5. -rw-r----- 1 oracle oinstall  51M Jun  7 18:32 redo01.log
  6. -rw-r----- 1 oracle oinstall  51M Jun  7 22:00 redo02.log
  7. -rw-r----- 1 oracle oinstall 2.7G Jun  7 22:00 temp01.dbf
  8. -rw-r----- 1 oracle oinstall  51M Jun  7 22:15 redo03.log
  9. -rw-r----- 1 oracle oinstall 551M Jun  7 22:15 system01.dbf
  10. -rw-r----- 1 oracle oinstall 1.2G Jun  7 22:15 undotbs01.dbf
  11. -rw-r----- 1 oracle oinstall 521M Jun  7 22:15 sysaux01.dbf
  12. -rw-r----- 1 oracle oinstall 446M Jun  7 22:15 users01.dbf
  13. -rw-r----- 1 oracle oinstall  31G Jun  7 22:15 CQSCHEMA1.dbf
  14. -rw-r----- 1 oracle oinstall  32G Jun  7 22:15 CQSCHEMA2.dbf
  15. -rw-r----- 1 oracle oinstall  32G Jun  7 22:15 CQSCHEMA3.dbf
  16. -rw-r----- 1 oracle oinstall  32G Jun  7 22:15 CQSCHEMA4.dbf
  17. -rw-r----- 1 oracle oinstall  32G Jun  7 22:15 CQSCHEMA5.dbf
  18. -rw-r----- 1 oracle oinstall 6.9M Jun  7 22:15 control03.ctl
  19. -rw-r----- 1 oracle oinstall 6.9M Jun  7 22:15 control02.ctl
  20. -rw-r----- 1 oracle oinstall 6.9M Jun  7 22:15 control01.ctl
  21.  
  22. [oracle@cq ~]$ scp *.dbf oracle@120.46.146.123:/data/oracle/oradata/cq/
rsync方式(文件大)
配置免密
  1. #1.本地生成密钥文件(172.17.130.7上操作)
  2. ssh-keygen   --全程敲回车
  3.  
  4. #2.将本地公钥传输到远程主机(172.17.130.7上操作)
  5. ssh-copy-id -i ~/.ssh/id_rsa.pub root@172.17.76.18
  6.  
  7. #3.测试本地免密登录到远程主机(172.17.130.7上操作,ssh 目标服务器ip)
  8. ssh 172.17.76.18
  9. 不提示输入密码即免密配置成功
rsync传输
  1. nohup /usr/bin/rsync -av --progress /opt/oracle/oradata/cq/redo* root@120.46.146.123:/data/oracle/oradata/cq/ > /tmp/load.log 2>&1 &
  2.  
  3. 参数说明：
  4. -z: --compress 使用压缩机制  时间长带宽速度慢，相反时间段带宽速度快
  5. -v: --verbose 打印详细信息
  6. -r: --recursive 以递归模式同步子目录
  7. --progress 实时的显示拷贝进度, 以及传输速率等信息. 尤其是拷贝大文件时, 程序不输出信息, 用户往往无法区分程序是在响应中, 还是已经挂起
  8. -a --archive ：归档模式，表示递归传输并保持文件属性。可以同步元信息（比如修改时间、权限等），比-r更有用
  9. -r --recursive：表示递归，即包含子目录。注意，-r是必须的，否则 rsync 运行不会成功

3.4.传输日志文件

源库日志文件位置：/opt/oracle/oradata/cq/

目标库日志文件位置：/data/oracle/oradata/cq/

源库传输日志文件至目标库

  1. --源库操作  传输数据文件
  2. cd /opt/oracle/oradata/cq/
  3. [oracle@cq cq]$ ls -lhtr
  4. total 163G
  5. -rw-r----- 1 oracle oinstall  51M Jun  7 18:32 redo01.log
  6. -rw-r----- 1 oracle oinstall  51M Jun  7 22:00 redo02.log
  7. -rw-r----- 1 oracle oinstall 2.7G Jun  7 22:00 temp01.dbf
  8. -rw-r----- 1 oracle oinstall  51M Jun  7 22:15 redo03.log
  9. -rw-r----- 1 oracle oinstall 551M Jun  7 22:15 system01.dbf
  10. -rw-r----- 1 oracle oinstall 1.2G Jun  7 22:15 undotbs01.dbf
  11. -rw-r----- 1 oracle oinstall 521M Jun  7 22:15 sysaux01.dbf
  12. -rw-r----- 1 oracle oinstall 446M Jun  7 22:15 users01.dbf
  13. -rw-r----- 1 oracle oinstall  31G Jun  7 22:15 CQSCHEMA1.dbf
  14. -rw-r----- 1 oracle oinstall  32G Jun  7 22:15 CQSCHEMA2.dbf
  15. -rw-r----- 1 oracle oinstall  32G Jun  7 22:15 CQSCHEMA3.dbf
  16. -rw-r----- 1 oracle oinstall  32G Jun  7 22:15 CQSCHEMA4.dbf
  17. -rw-r----- 1 oracle oinstall  32G Jun  7 22:15 CQSCHEMA5.dbf
  18. -rw-r----- 1 oracle oinstall 6.9M Jun  7 22:15 control03.ctl
  19. -rw-r----- 1 oracle oinstall 6.9M Jun  7 22:15 control02.ctl
  20. -rw-r----- 1 oracle oinstall 6.9M Jun  7 22:15 control01.ctl
  21.  
  22. [oracle@cq ~]$ scp *.log oracle@120.46.146.123:/data/oracle/oradata/cq/

3.5.更改控制文件中数据文件和日志文件位置

若源库和目标库的数据目录、日志目录相同，需忽略该步骤。

  1. --生成数据目录转换脚本
  2. select 'alter database rename ' || a.FILE# || ' to "' || a.NAME || '";'
  3. from v$datafile a
  4. union all
  5. select 'alter database rename ' || a.FILE# || ' to "' || a.NAME || '";'
  6. from v$tempfile a
  7. union all
  8. SELECT 'alter database rename ''''' || a.MEMBER || ''''' to ''''' ||
  9. a.MEMBER || ''''' ";'
  10. FROM v$logfile a
  11.  
  12. --在库中执行 以上sql输出的语句
  13. alter database rename 9 to "/data/oracle/oradata/cq/CQSCHEMA5.dbf";
  14. alter database rename 8 to "/data/oracle/oradata/cq/CQSCHEMA4.dbf";
  15. alter database rename 7 to "/data/oracle/oradata/cq/CQSCHEMA3.dbf";
  16. alter database rename 6 to "/data/oracle/oradata/cq/CQSCHEMA2.dbf";
  17. alter database rename 5 to "/data/oracle/oradata/cq/CQSCHEMA1.dbf";
  18. alter database rename 4 to "/data/oracle/oradata/cq/users01.dbf";
  19. alter database rename 3 to "/data/oracle/oradata/cq/sysaux01.dbf";
  20. alter database rename 2 to "/data/oracle/oradata/cq/undotbs01.dbf";
  21. alter database rename 1 to "/data/oracle/oradata/cq/temp01.dbf";
  22. alter database rename 1 to "/data/oracle/oradata/cq/system01.dbf";
  23. alter database rename ''/opt/oracle/oradata/cq/redo03.log'' to ''/data/oracle/oradata/cq/redo03.log'' ";
  24. alter database rename ''/opt/oracle/oradata/cq/redo02.log'' to ''/data/oracle/oradata/cq/redo02.log'' ";
  25. alter database rename ''/opt/oracle/oradata/cq/redo01.log'' to ''/data/oracle/oradata/cq/redo01.log'' ";

3.6.目标库启库至open

  1. --目标库操作 启库到open状态 
  2. SQL> alter database open;  --启动到mount状态

3.7.传输密码文件

源库传输密码文件至目标库

源库密码文件位置：$ORACLE_HOME/dbs 即/opt/oracle/product/10.2/db_1/dbs

目标库密码文件位置：$ORACLE_HOME/dbs即/data/app/oracle/product/10.2.0.1/dbhome_1/dbs

  1. --源库操作  传输密码文件
  2. [oracle@cq dbs]$ ls -l
  3. total 7.0M
  4. -rw-rw---- 1 oracle oinstall 1.6K Jun  8 11:00 hc_cq.dat
  5. -rw-r----- 1 oracle oinstall  13K May  3  2001 initdw.ora
  6. -rw-r----- 1 oracle oinstall 8.2K Sep 11  1998 init.ora
  7. -rw-rw---- 1 oracle oinstall   24 Jun  8 08:33 lkCQ
  8. -rw-r----- 1 oracle oinstall 1.5K Jun  8 09:36 orapwcq
  9. -rw-r----- 1 oracle oinstall 6.9M Jun  8 08:43 snapcf_cq.f
  10. -rw-r----- 1 oracle oinstall 3.5K Jun  8 11:00 spfilecq.ora
  11.  
  12. [oracle@cq dbs]$ pwd
  13. /opt/oracle/product/10.2/db_1
  14.  
  15. [oracle@cq ~]$ scp orapwcq oracle@120.46.146.123:/data/app/oracle/product/10.2.0.1/dbhome_1/dbs/

3.8.传输监听文件(可选)

如果监听文件没有特殊设置可省略该步骤。

本文档案例监听文件没有特殊设置，省略该步骤

建议将源系统的network下的文件拷过来，根据实际情况修改listener.ora和tnsnames.ora

目标库停止监听

  1. --目标库操作  停止监听服务
  2. su - oracle
  3. lsnrctl stop
  4. lsnrctl status
  5.  
  6. --目标库操作 监听文件备份
  7. cd /opt/oracle/product/10.2/db_1/network/admin
  8. cp listener.ora listener.ora_old_bak_20240608

源库传输监听文件至目标库

源库监听文件位置：$ORACLE_HOME/dbs 即/opt/oracle/product/10.2/db_1/network/admin

目标库监听文件位置：$ORACLE_HOME/dbs即/data/app/oracle/product/10.2.0.1/dbhome_1/dbs/network/admin

  1. --源库操作  传输监听文件
  2. cd /opt/oracle/product/10.2/db_1/network/admin
  3. [oracle@cq admin]$ ls -l
  4. total 20K
  5. -rw-rw-r-- 1 oracle oinstall  486 Jul 22  2022 listener.ora
  6. drwxr-x--- 2 oracle oinstall 4.0K Jul 22  2022 samples
  7. -rw-r----- 1 oracle oinstall  172 Dec 26  2003 shrept.lst
  8. -rw-r--r-- 1 oracle oinstall  563 Jul 31  2022 sqlnet.log
  9. -rw-rw-r-- 1 oracle oinstall  509 Aug  2  2022 tnsnames.ora
  10.  
  11. [oracle@cq admin]$ pwd
  12. /opt/oracle/product/10.2/db_1/network/admin
  13.  
  14. [oracle@cq ~]$ scp  listener.ora oracle@120.46.146.123:/data/app/oracle/product/10.2.0.1/dbhome_1/dbs/network/admin/
  15. [oracle@cq ~]$ scp  tnsnames.ora oracle@120.46.146.123:/data/app/oracle/product/10.2.0.1/dbhome_1/dbs/network/admin/

目标库启动监听

  1. --目标库操作  启动监听服务
  2. su - oracle
  3. lsnrctl start
  4. lsnrctl status

3.9.目标库数据量统计

  1. 目标库数据量统计
  2. --查看数据量
  3. select sum(bytes)/1024/1024/1024 as "size(G)" from dba_data_files;
  4.    size(G)
  5. ----------
  6. 159.34082
  7.  
  8. select tablespace_name,sum(bytes)/1024/1024/1024 GB from dba_data_files group by tablespace_name;
  9. TABLESPACE_NAME 		       GB
  10. ------------------------------ ----------
  11. SYSAUX				 .5078125
  12. UNDOTBS1		       1.12304688
  13. CQSCHEMA		       156.738281
  14. USERS			       .434570313
  15. SYSTEM			       .537109375
  16.  
  17. --查看总大小
  18. select round(sum(bytes) / 1024 / 1024 / 1024, 2) || 'G'
  19.   from dba_segments
  20.  where owner not in ('MDSYS', 'OUTLN', 'CTXSYS', 'OLAPSYS', 'HR', 'SYSTEM',
  21.         'EXFSYS', 'SCOTT', 'DBSNMP', 'ORDSYS', 'SYSMAN', 'OE', 'PM', 'SH',
  22.         'XDB', 'ORDDATA', 'IX', 'SYS', 'WMSYS') ;
  23. 或
  24. select round(sum(bytes) / 1024 / 1024 / 1024, 2) || 'G'
  25.   from dba_segments;
  26.  
  27.  ROUND(SUM(BYTES)/1024/1024/1024,2)||'G'
  28. -----------------------------------------
  29. 157.91G
  30.  
  31.  
  32. --查看用户
  33. set lin1000 pagesize 999
  34. 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');
  35.  
  36. USERNAME		       DEFAULT_TABLESPACE	      TEMPORARY_TABLESPACE
  37. ------------------------------ ------------------------------ ------------------------------
  38. BACKUPKEVIN		       USERS			      TEMP
  39. TESTSSS 		         USERS			      TEMP
  40. YUNTEST 		         USERS			      TEMP
  41. CQTEST			         CQSCHEMA 		    TEMP
  42. CQUSER			         CQSCHEMA 		    TEMP
  43. DEV			             CQSCHEMA 		    TEMP
  44. TEST			           CQSCHEMA 		    TEMP
  45. DMSYS			           SYSAUX			      TEMP
  46. TSMSYS			         USERS			      TEMP
  47.  
  48. 9 rows selected.
  49.  
  50.  
  51.  
  52. --每个业务用户下的总对象数量校验
  53. SELECT D.OWNER,COUNT(1)
  54.   FROM dba_objects d
  55.  WHERE d.OWNER   in ('BACKUPKEVIN','TESTSSS','YUNTEST','CQTEST','CQUSER','DEV','TEST','DMSYS','TSMSYS')
  56.  AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner) 
  57.  GROUP BY D.OWNER
  58.  ORDER BY D.OWNER ;
  59.  
  60.  OWNER				 COUNT(1)
  61. ------------------------------ ----------
  62. CQTEST				    407
  63. CQUSER				    109
  64. DEV				        458
  65. DMSYS				      189
  66. TEST				      326
  67. TESTSSS 			    36
  68. TSMSYS				  	3
  69. YUNTEST 			    452
  70.  
  71. 8 rows selected.
  72.  
  73.  
  74. --每个业务用户下的各个对象类别的数量校验
  75. SQL> set pagesize 999
  76. SQL> select OWNER,OBJECT_TYPE,status,count(OBJECT_NAME) from dba_objects  where owner in ('BACKUPKEVIN','TESTSSS','YUNTEST','CQTEST','CQUSER','DEV','TEST','DMSYS','TSMSYS')  group by OBJECT_TYPE,owner,status order by 1,3,2;
  77.  
  78. OWNER			       OBJECT_TYPE	   STATUS  COUNT(OBJECT_NAME)
  79. ------------------------------ ------------------- ------- ------------------
  80. CQTEST			       INDEX		     VALID		  161
  81. CQTEST			       LOB		       VALID		  156
  82. CQTEST			       TABLE		     VALID		   90
  83. CQUSER			       INDEX		     VALID		   51
  84. CQUSER			       LOB		       VALID		   11
  85. CQUSER			       TABLE		     VALID		   47
  86. DEV			           PROCEDURE	   INVALID		 1
  87. DEV			           VIEW		       INVALID		 5
  88. DEV			           INDEX		     VALID		   179
  89. DEV			           LOB		       VALID		   162
  90. DEV			           PROCEDURE	   VALID		 3
  91. DEV			           TABLE		     VALID		  103
  92. DEV			           VIEW		       VALID		    5
  93. DMSYS			         FUNCTION 	   VALID		   12
  94. DMSYS			         INDEX		     VALID		    2
  95. DMSYS			         LIBRARY		   VALID		    6
  96. DMSYS			         PACKAGE		   VALID		   27
  97. DMSYS			         PACKAGE BODY	 VALID		   23
  98. DMSYS			         PROGRAM		   VALID		    9
  99. DMSYS			         SEQUENCE 	   VALID		    1
  100. DMSYS			         TABLE		     VALID		    2
  101. DMSYS			         TYPE		       VALID		   94
  102. DMSYS			         TYPE BODY	   VALID		   12
  103. DMSYS			         VIEW		       VALID		    1
  104. TEST			         INDEX		     VALID		  137
  105. TEST			         LOB		       VALID		  110
  106. TEST			         TABLE		     VALID		   79
  107. TESTSSS 		       INDEX		     VALID		   20
  108. TESTSSS 		       LOB		       VALID		    5
  109. TESTSSS 		       TABLE		     VALID		   11
  110. TSMSYS			       INDEX		     VALID		    1
  111. TSMSYS			       LOB		       VALID		    1
  112. TSMSYS			       TABLE		     VALID		    1
  113. YUNTEST 		       INDEX		     VALID		  172
  114. YUNTEST 		       LOB		       VALID		  175
  115. YUNTEST 		       PROCEDURE	   VALID		    4
  116. YUNTEST 		       TABLE		     VALID		   97
  117. YUNTEST 		       VIEW		       VALID		    4
  118.  
  119. 38 rows selected.
  120.  
  121.  
  122.  
  123. --查询回收站lob对象数量(可选),lob数量不一致排查
  124. select owner,type,count(*) from dba_recyclebin group by owner,type order by owner;
  125. no rows selected
  126.  
  127.  
  128.  
  129. --数据量统计
  130. ----新增count_rows 函数
  131. CREATE OR REPLACE FUNCTION count_rows (
  132. 	table_name IN varchar2, 
  133. 	owner IN varchar2 := NULL
  134. )
  135. RETURN number AUTHID current_user
  136. AS
  137. num_rows number;
  138. 	stmt varchar2(2000);
  139. BEGIN
  140. 	IF owner IS NULL THEN
  141. 		stmt := 'select count(*) from "' || table_name || '"';
  142. 	ELSE
  143. 		stmt := 'select count(*) from "' || owner || '"."' || table_name || '"';
  144. 	END IF;
  145. 	EXECUTE IMMEDIATE stmt INTO num_rows;
  146. 	RETURN num_rows;
  147. END;
  148. /
  149. ---调用count_rows 函数，统计各种表的记录数
  150. select table_name, count_rows(table_name) nrows from user_tables  where tablespace_name='ENTSERVICE'
  151. order by nrows desc;

最后，Oracle10单点异机冷备迁移，数据目录不同的场景下的操作步骤和Oracle11g一样。

吐槽一点：上云容易下云难，阿里云上的文件传送到华为云服务器速度竟低至3.4M/s。

image

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小舞很执着/article/detail/919423
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号