做名片制作网站有什么,网站策划过程,大朗镇网站仿做,网站建设公司扬州由于阿里云机房要下架旧服务器#xff0c;单位未购买整机迁移服务#xff0c;且业务较老不兼容Oracle11g#xff0c;所以新购买一台新服务器进行安装Oracle10.2.0.1 #xff0c;后续再将数据迁移到新服务器上。 id 数据库版本 操作系统版本 实例名 源库 115.28.242.25…由于阿里云机房要下架旧服务器单位未购买整机迁移服务且业务较老不兼容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.查看操作系统版本
[rootcq ~]# cat /etc/redhat-release
CentOS release 6.9 (Final)
1.1.2.2.查看磁盘空间
[rootcq ~]# df -Th
Filesystem Type Size Used Avail Use% Mounted on
/dev/vda1 ext4 40G 4.4G 33G 12% /
tmpfs tmpfs 3.9G 0 3.9G 0% /dev/shm
/dev/vdb ext4 493G 446G 22G 96% /opt
1.1.2.3.cpu配置
[rootcq ~]# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 4
On-line CPU(s) list: 0-3
Thread(s) per core: 2
Core(s) per socket: 2
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 79
Model name: Intel(R) Xeon(R) CPU E5-2682 v4 2.50GHz
Stepping: 1
CPU MHz: 2499.996
BogoMIPS: 4999.99
Hypervisor vendor: KVM
Virtualization type: full
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 40960K
NUMA node0 CPU(s): 0-3
1.1.2.4.内存配置
[rootcq ~]# awk /MemTotal/{print $2} /proc/meminfo K
8193112 --单位或 [rootcq ~]# free -gtotal used free shared buffers cached
Mem: 7 6 1 3 0 4
-/ buffers/cache: 1 6
Swap: 15 0 15
1.1.2.5.磁盘IO速度监控
--磁盘IO速度监控 每间隔3秒监控3次
[rootcq ~]# iostat 3 3
Linux 2.6.32-696.16.1.el6.x86_64 (cq) 05/10/2024 _x86_64_ (4 CPU)avg-cpu: %user %nice %system %iowait %steal %idle7.42 0.00 0.46 3.94 0.00 88.18Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
vda 0.45 0.83 5.53 17854722 118882528
vdb 299.55 13416.03 2750.53 288563271330 59160700608avg-cpu: %user %nice %system %iowait %steal %idle5.35 0.00 1.00 18.90 0.00 74.75Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
vda 0.00 0.00 0.00 0 0
vdb 711.00 67051.00 131.33 201153 394avg-cpu: %user %nice %system %iowait %steal %idle9.71 0.00 0.84 7.28 0.00 82.18Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
vda 1.00 0.00 10.67 0 32
vdb 265.00 19712.00 68.67 59136 2061.1.2.查看数据库信息
1.1.2.1.统计数据量
由于该库数据量较小该步骤省略。该步骤只适用于数据量大的场景。
--查看数据文件总大小
select sum(bytes)/1024/1024/1024 as size(G) from dba_data_files;size(G)
----------
156.899414--查看总大小
select round(sum(bytes) / 1024 / 1024 / 1024, 2) || Gfrom dba_segmentswhere owner not in (MDSYS, OUTLN, CTXSYS, OLAPSYS, HR, SYSTEM,EXFSYS, SCOTT, DBSNMP, ORDSYS, SYSMAN, OE, PM, SH,XDB, ORDDATA, IX, SYS, WMSYS) ;
或
select round(sum(bytes) / 1024 / 1024 / 1024, 2) || Gfrom dba_segments;ROUND(SUM(BYTES)/1024/1024/1024,2)||G
-----------------------------------------
155.52G
1.1.2.2.清理业务数据
需和开发 直属领导同意。对于占用空间大的表采用方案如下
1、删除表数据。
2、备份时加参数排除即不备份对业务来说不重要的表。
--查看表大小超过10G的大表
col owner for a40
col TABLE_NAME for a60
set linesize 999 pagesize 999
select * from (SELECT OWNER,TABLE_NAME,SUM(tabsize) table_size from (SELECT owner,SEGMENT_NAME as table_name,SUM(BYTES)/1024/1024/1024 as tabsize FROM DBA_SEGMENTS WHERE (OWNER,SEGMENT_NAME) IN (SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER NOT IN (SYS,SYSTEM,DBSNMP))GROUP BY SEGMENT_NAME,ownerunion ALLselect lob.owner,lob.table_name,sum(seg.bytes)/1024/1024/1024 as tabsize from dba_lobs lob,dba_segments seg where lob.segment_nameseg.segment_name and lob.OWNER NOT IN (SYS,SYSTEM,DBSNMP) group by lob.owner,lob.table_name) GROUP BY OWNER,TABLE_NAME ORDER BY 3 DESC)where table_size10 ;--查询库中记录数大于2千万的所有表当前用户拥有select any table权限
select table_name, num_rows from dba_tables t where t.owner upper(hr) and num_rows 20000000;
或
select table_name, num_rows from all_tables t where t.owner upper(hr) and num_rows 20000000;
1.1.2.3.停止监听
--停止监听
su - oracle
[oracletop130 ~]$ lsnrctl status
[oracletop130 ~]$ lsnrctl stop
[oracletop130 ~]$ lsnrctl stop
1.1.2.4.开启防火墙
--开启防火墙
systemctl start firewalld
systemctl status firewalld
firewall-cmd --list-ports
1.1.2.5.停止会话
查询会话如果有业务会话给干掉
--查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username;USERNAME COUNT(USERNAME)
------------------------------ ---------------
CQUSER 5
DEV 78
SYS 1--查询用户会话
select username,serial#, sid from v$session where username is not null;set linesize 999
select username,status,schemaname,osuser,process,machine,program,serial#, sid from v$session where username is not null;--删除相关用户会话
alter system kill session sidserial#;补充
--源库杀用户连接 不建议 该步骤慎重 该文档未进行该操作 该方法迫不得已不用
ps -ef|grep LOCALNO|awk {print $2}|xargs kill -9 1.1.3.查看源库配置
保证新库和源库所有配置相同。
1.1.3.1.查看数据库参数
--数据库的创建日期和归档方式
SELECT to_char(created,yyyy-mm-dd), log_mode, log_mode FROM V$database;
TO_CHAR(CR LOG_MODE LOG_MODE
---------- ------------ ------------
2022-08-02 ARCHIVELOG ARCHIVELOG--查数据库版本
SELECT version FROM product_component_version WHERE substr(product, 1, 6) Oracle;
VERSION
--------------------------------------------------------------------------------------------------------------------------------
10.2.0.1.0--查看实例名
SQL select instance_name from v$instance;INSTANCE_NAME
----------------
cqSQL show parameter nameNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string cq
db_unique_name string cq
global_names boolean FALSE
instance_name string cq
lock_name_space string
log_file_name_convert string
service_names string cq-- 查看数据库字符集
set linesize 999
col value for a60
select * from nls_database_parameters where PARAMETER in (NLS_CHARACTERSET,NLS_NCHAR_CHARACTERSET);
PARAMETER VALUE
------------------------------ ------------------------------------------------------------
NLS_CHARACTERSET ZHS16GBK
NLS_NCHAR_CHARACTERSET AL16UTF16select userenv(language) from dual;USERENV(LANGUAGE)
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK-- 查看数据库配置参数
set lin 1000 pagesize 999
col NAME for a30
col value for a40
col DISPLAY_VALUE for a40
SELECT name,value,display_value FROM v$parameter
WHERE name IN(
processes,
sessions,
memory_target,
memory_max_target,
sga_max_size,
sga_target,
pga_aggregate_target
);
NAME VALUE DISPLAY_VALUE
----------------------- ------- ---------------------------------------- ----------------------------------------
processes 1000 1000
sessions 1450 1450
sga_max_size 4294967296 4G
sga_target 4294967296 4G
pga_aggregate_target 1073741824 1G1.1.3.2.查看用户
--查看用户
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
BACKUPKEVIN USERS TEMP
TESTSSS USERS TEMP
YUNTEST USERS TEMP
CQTEST CQSCHEMA TEMP
CQUSER CQSCHEMA TEMP
DEV CQSCHEMA TEMP
TEST CQSCHEMA TEMP
DMSYS SYSAUX TEMP
TSMSYS USERS TEMP9 rows selected.--查看用户使用了哪些表空间
select distinct tablespace_name from dba_segments where owner in (TOPICIS,XFYQ,DSJ,WLSJ);1.1.3.3.查Role(角色)
由于没有自定义角色所以不用生成创建角色的sql。
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE IN (BACKUPKEVIN ,TESTSSS ,YUNTEST ,CQTEST ,CQUSER ,DEV ,TEST ,DMSYS ,TSMSYS) order by grantee;GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
BACKUPKEVIN DBA NO YES
CQTEST CONNECT YES YES
CQTEST DBA YES YES
CQTEST RESOURCE YES YES
CQUSER CONNECT NO YES
CQUSER DBA NO YES
DEV CONNECT NO YES
DEV DBA NO YES
TEST CONNECT YES YES
TEST DBA YES YES
TESTSSS CONNECT YES YES
TESTSSS DBA YES YES
TSMSYS RESOURCE NO YES
YUNTEST CONNECT YES YES
YUNTEST DBA YES YES15 rows selected.
如果有有自定义角色生成创建角色的sql不含系统角色
--查询用户拥有的非系统角色
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);--批量创建非系统角色
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);--查询非系统角色拥有的角色
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));--批量生成非系统角色拥有的角色
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));--查看非系统角色拥有的对象权限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));--批量生成非系统角色拥有的对象权限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配置文件
select username,profile from dba_users;USERNAME PROFILE
------------------------------ ------------------------------
SYS DEFAULT
TESTSSS DEFAULT
BACKUPKEVIN DEFAULT
YUNTEST DEFAULT
DEV DEFAULT
CQTEST DEFAULT
CQUSER DEFAULT
TEST DEFAULT
SYSTEM DEFAULT
OUTLN DEFAULT
MGMT_VIEW DEFAULT
MDSYS DEFAULT
ORDSYS DEFAULT
EXFSYS DEFAULT
DMSYS DEFAULT
DBSNMP MONITORING_PROFILE
WMSYS DEFAULT
CTXSYS DEFAULT
ANONYMOUS DEFAULT
SYSMAN DEFAULT
XDB DEFAULT
ORDPLUGINS DEFAULT
SI_INFORMTN_SCHEMA DEFAULT
OLAPSYS DEFAULT
SCOTT DEFAULT
TSMSYS DEFAULT
MDDATA DEFAULT
DIP DEFAULT28 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.锁定业务用户
--查看用户 和现场负责人确定哪些是业务用户哪些是需要迁的用户
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
------------------------------ ------------------------------ ------------------------------
BACKUPKEVIN USERS TEMP
TESTSSS USERS TEMP
YUNTEST USERS TEMP
CQTEST CQSCHEMA TEMP
CQUSER CQSCHEMA TEMP
DEV CQSCHEMA TEMP
TEST CQSCHEMA TEMP
DMSYS SYSAUX TEMP
TSMSYS USERS TEMP9 rows selected.--锁定业务用户
select username,account_status,lock_date from dba_users where username in (BACKUPKEVIN ,TESTSSS ,YUNTEST ,CQTEST ,CQUSER ,DEV ,TEST ,DMSYS ,TSMSYS);
USERNAME ACCOUNT_STATUS LOCK_DATE
------------------------------ -------------------------------- --------------
CQTEST OPEN
CQUSER OPEN
BACKUPKEVIN OPEN
TESTSSS OPEN
DEV OPEN
TEST OPEN
YUNTEST OPEN
TSMSYS EXPIRED LOCKED 02-8Ղ -22
DMSYS EXPIRED LOCKED 02-8Ղ -229 rows selected.select alter user ||username|| account lock; from dba_users where username in (BACKUPKEVIN ,TESTSSS ,YUNTEST ,CQTEST ,CQUSER ,DEV ,TEST ,DMSYS ,TSMSYS) and ACCOUNT_STATUOPEN;
执行以上输出结果
1.1.5.查看表空间及数据文件
-- 查看表空间及数据文件位置及大小
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
------------------------------ ------------------------------------------------------- ---------- ---
CQSCHEMA /opt/oracle/oradata/cq/CQSCHEMA1.dbf 30.4882813 YES
CQSCHEMA /opt/oracle/oradata/cq/CQSCHEMA4.dbf 31.171875 YES
CQSCHEMA /opt/oracle/oradata/cq/CQSCHEMA3.dbf 30.8789063 YES
CQSCHEMA /opt/oracle/oradata/cq/CQSCHEMA2.dbf 31.1230469 YES
CQSCHEMA /opt/oracle/oradata/cq/CQSCHEMA5.dbf 30.6347656 YES
SYSAUX /opt/oracle/oradata/cq/sysaux01.dbf .5078125 YES
SYSTEM /opt/oracle/oradata/cq/system01.dbf .537109375 YES
UNDOTBS1 /opt/oracle/oradata/cq/undotbs01.dbf 1.12304688 YES
USERS /opt/oracle/oradata/cq/users01.dbf .434570313 YES9 rows selected.--查看临时表空间及临时表空间数据文件位置及大小
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 /opt/oracle/oradata/cq/temp01.dbf 2.67285156 2.67972576--查看控制文件位置
SQL show parameter controlNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /opt/oracle/oradata/cq/control01.ctl, /opt/oracle/oradata/cq/control02.ctl, /opt/oracle/oradata/cq/control03.ctl--查看redo log大小和位置
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#; THREAD# GROUP# MEMBER BYTES/1024/1024 MEMBERS STATUS
---------- ---------- ------------------------------------------------------------ --------------- ---------- ----------------1 3 /opt/oracle/oradata/cq/redo03.log 50 1 INACTIVE1 2 /opt/oracle/oradata/cq/redo02.log 50 1 CURRENT1 1 /opt/oracle/oradata/cq/redo01.log 50 1 INACTIVE1.1.6..查业务数据
--每个业务用户下的总对象数量校验
SELECT D.OWNER,COUNT(1)FROM dba_objects dWHERE d.OWNER in (BACKUPKEVIN ,TESTSSS ,YUNTEST ,CQTEST ,CQUSER ,DEV ,TEST ,DMSYS ,TSMSYS)AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_nameD.OBJECT_NAME AND D.OWNERB.owner) GROUP BY D.OWNERORDER BY D.OWNER ;
OWNER COUNT(1)
------------------------------ ----------
CQTEST 407
CQUSER 109
DEV 458
DMSYS 189
TEST 326
TESTSSS 36
TSMSYS 3
YUNTEST 4528 rows selected.--每个业务用户下的各个对象类别的数量校验
set pagesize 999
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;OWNER OBJECT_TYPE STATUS COUNT(OBJECT_NAME)
------------------------------ ------------------- ------- ------------------
CQTEST INDEX VALID 161
CQTEST LOB VALID 156
CQTEST TABLE VALID 90
CQUSER INDEX VALID 51
CQUSER LOB VALID 11
CQUSER TABLE VALID 47
DEV INDEX VALID 179
DEV LOB VALID 162
DEV PROCEDURE VALID 4
DEV TABLE VALID 103
DEV VIEW VALID 10
DMSYS FUNCTION VALID 12
DMSYS INDEX VALID 2
DMSYS LIBRARY VALID 6
DMSYS PACKAGE VALID 27
DMSYS PACKAGE BODY VALID 23
DMSYS PROGRAM VALID 9
DMSYS SEQUENCE VALID 1
DMSYS TABLE VALID 2
DMSYS TYPE VALID 94
DMSYS TYPE BODY VALID 12
DMSYS VIEW VALID 1
TEST INDEX VALID 137
TEST LOB VALID 110
TEST TABLE VALID 79
TESTSSS INDEX VALID 20
TESTSSS LOB VALID 5
TESTSSS TABLE VALID 11
TSMSYS INDEX VALID 1
TSMSYS LOB VALID 1
TSMSYS TABLE VALID 1
YUNTEST INDEX VALID 172
YUNTEST LOB VALID 175
YUNTEST PROCEDURE VALID 4
YUNTEST TABLE VALID 97
YUNTEST VIEW VALID 436 rows selected.--查询回收站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);
BEGINIF owner IS NULL THENstmt : select count(*) from || table_name || ;ELSEstmt : 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_nameENTSERVICE
order by nrows desc;
1.2.查看新库信息
1.2.1.查看服务器配置
确保新服务器配置等于或高于旧服务器。
1.2.2.1.查看操作系统版本
[rootecs-677a ~]# cat /etc/redhat-release
CentOS release 6.10 (Final)
1.2.2.2.查看磁盘空间
[rootecs-677a ~]# df -Th
Filesystem Type Size Used Avail Use% Mounted on
/dev/vda1 ext4 40G 1.6G 36G 5% /
tmpfs tmpfs 3.9G 0 3.9G 0% /dev/shm
/dev/mapper/data-data01ext4 689G 69M 654G 1% /data
1.2.2.3.cpu配置
[rootecs-677a ~]# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 4
On-line CPU(s) list: 0-3
Thread(s) per core: 2
Core(s) per socket: 2
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 85
Model name: Intel(R) Xeon(R) Gold 6161 CPU 2.20GHz
Stepping: 4
CPU MHz: 2200.000
BogoMIPS: 4400.00
Hypervisor vendor: KVM
Virtualization type: full
L1d cache: 32K
L1i cache: 32K
L2 cache: 1024K
L3 cache: 30976K
NUMA node0 CPU(s): 0-31.2.2.4.内存配置
[rootcq ~]# awk /MemTotal/{print $2} /proc/meminfo K
8060540 --单位或 [rootcq ~]# free -g
[rootecs-677a ~]# free -gtotal used free shared buffers cached
Mem: 7 0 7 0 0 0
-/ buffers/cache: 0 7
Swap: 0 0 0
1.2.2.5.磁盘IO测速
1.2.2.5.1.创建存放测速文件的目录
--创建存放测速文件的目录
[rootecs-677a ~]# ls -ld /ddtest --确定不存在该目录
[rootecs-677a ~]# mkdir -p /ddtest
[rootecs-677a ~]# cd /ddtest
[rootecs-677a ddtest]# ls -l
total 0
1.2.2.5.2.测试纯写入性能
--测试纯写入性能 连续执行3次取平均值
[rootecs-677a ddtest]# dd if/dev/zero oftest bs4G count1 oflagdirect
01 records in
01 records out
2147479552 bytes (2.1 GB) copied, 18.0524 s, 119 MB/s
[rootecs-677a ddtest]# dd if/dev/zero oftest01 bs4G count1 oflagdirect
01 records in
01 records out
2147479552 bytes (2.1 GB) copied, 17.8508 s, 120 MB/s
[rootecs-677a ddtest]# dd if/dev/zero oftest02 bs4G count1 oflagdirect
01 records in
01 records out
2147479552 bytes (2.1 GB) copied, 17.7979 s, 121 MB/s
1.2.2.5.3.测试纯读取性能 --测试纯读取性能 连续执行3次取平均值
[rootecs-677a ddtest]# dd iftest of/dev/null bs4G count1 iflagdirect
01 records in
01 records out
2147479552 bytes (2.1 GB) copied, 16.1827 s, 133 MB/s
[rootecs-677a ddtest]# dd iftest01 of/dev/null bs4G count1 iflagdirect
01 records in
01 records out
2147479552 bytes (2.1 GB) copied, 16.5974 s, 129 MB/s
[rootecs-677a ddtest]# dd iftest02 of/dev/null bs4G count1 iflagdirect
01 records in
01 records out
2147479552 bytes (2.1 GB) copied, 16.7011 s, 129 MB/s1.3.新服务器配置
1.3.1.配置主机名
--查看主机名
[rootecs-677a ddtest]# cat /etc/sysconfig/network
NETWORKINGyes
HOSTNAMEecs-677a
RES_OPTIONStimeout:1 single-request-reopen--配置主机名
/bin/hostname cqnew
sysctl kernel.hostnamecqnew
echo cqnew /proc/sys/kernel/hostname
sed -i s/^HOSTNAME.*/HOSTNAMEcqnew/ /etc/sysconfig/network--查看主机名
hostname
1.3.2.配置/etc/hosts
如果是弹性云服务器不能配置为对外访问的ip 120.46.146.123 需配置成实际网卡的ip不然后面监听启动报错。
--查看ip地址
[oraclecqnew admin]$ ip addr
1: lo: LOOPBACK,UP,LOWER_UP mtu 65536 qdisc noqueue state UNKNOWNlink/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host loinet6 ::1/128 scope hostvalid_lft forever preferred_lft forever
2: eth0: BROADCAST,MULTICAST,UP,LOWER_UP mtu 1500 qdisc mq state UP qlen 1000link/ether fa:16:3e:5d:b5:d8 brd ff:ff:ff:ff:ff:ffinet 192.168.0.35/24 brd 192.168.0.255 scope global eth0inet6 fe80::f816:3eff:fe5d:b5d8/64 scope linkvalid_lft forever preferred_lft forever--配置/etc/hosts
[rootecs-677a ddtest]# vi /etc/hosts
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
#127.0.0.1 ecs-677a ecs-677a
192.168.0.35 cqnew
1.3.3.设置系统服务
禁用一些不必要的系统服务
--Linux/Centos 6.10环境下
chkconfig iptables off
chkconfig ip6tables off
chkconfig postfix off
Linux/Centos 6.5 采用以下
chkconfig iptables off
chkconfig ip6tables off
chkconfig cups off
chkconfig firstboot off
chkconfig wpa_supplicant off
chkconfig postfix off
1.3.4.禁用透明大页
--临时禁用
echo never /sys/kernel/mm/transparent_hugepage/enabled
echo never /sys/kernel/mm/transparent_hugepage/defrag--加入开机启动
echo
echo never /sys/kernel/mm/transparent_hugepage/enabled
echo never /sys/kernel/mm/transparent_hugepage/defrag /etc/rc.local
chmod x /etc/rc.local--检查是否关闭透明大页的命令
grep Huge /proc/meminfo
1.3.5.关闭numa
注意Centos6和Centos7的配置不一样。
--检查检查OS是否开启NUMA
# numactl --hardware 检查 numa 节点信息available: 1 nodes (0) #如果是2或多个nodes就说明numa没关掉--关闭 numa
numastat #查看vi /etc/grub.conf 设置 numaoff查看每个节点的分配情况【1】numastat 【2】numactl --show然后重启生效reboot
注意numaoff 要写在内核后
1.3.5.用在线安装方式常用软件及必需软件
安装kernel-devel和kernel-headers并且在更新系统时禁止更新kernel
yum -y install kernel-devel kernel-headers echo excludekernel*
更新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.检查或配置交换空间
若swap2G跳过该步骤
若swap0则执行以下操作
--检查内存配置
[rootcqnew install]# free -gtotal used free shared buffers cached
Mem: 7 7 0 0 0 6
-/ buffers/cache: 0 7
Swap: 0 0 0--创建指定大小的空文件 /swapfile并将其格式化为交换分区
dd if/dev/zero of/data/swapfile bs2G count8--设置文件权限为 0600
chmod 600 /data/swapfile--格式化文件为 Swap 分区
mkswap /data/swapfile--启用 Swap 分区
swapon /data/swapfile--将 Swap 分区信息添加到 /etc/fstab 文件中以便系统重启后自动加载
cp /etc/fstab /etc/fstab_bak_20240511
echo /data/swapfile none swap sw 0 0 /etc/fstab--手动挂载所有磁盘
mount -a--查看内存 已经有swap了
[rootcqnew install]# free -gtotal used free shared buffers cached
Mem: 7 5 2 0 0 5
-/ buffers/cache: 0 7
Swap: 15 0 15
1.3.7.创建Oracle相关用户和目录
--创建oracle组及用户
groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle--创建安装目录
mkdir -p /data/app/oracle
mkdir -p /data/app/oracle/oraInventory
mkdir -p /data/app/oracle/product/10.2.0.1/dbhome_1
chown -R oracle.oinstall /data/app/oracle/
chmod -R 755 /data/app/oracle/--创建数据目录
mkdir -p /data/oracle/oradata/cq
chown -R oracle.oinstall /data/oracle/oradata/cq
chmod -R 755 /data/oracle/oradata/cq
1.3.8.修改内核参数
-物理内存KB
os_memory_total$(awk /MemTotal/{print $2} /proc/meminfo)--获取系统页面大小用于计算内存总量
pagesize$(getconf PAGE_SIZE)min_free_kbytes $os_memory_total / 250shmall ($os_memory_total - 1) * 1024 / $pagesizeshmmax $os_memory_total * 1024 - 1# 如果 shmall 小于 2097152则将其设为 2097152(($shmall 2097152)) shmall2097152# 如果 shmmax 小于 4294967295则将其设为 4294967295(($shmmax 4294967295)) shmmax4294967295$shmall
$shmmax
$min_free_kbytes
$local_ifname
[rootcqnew ~]# cp /etc/sysctl.conf /etc/sysctl.conf_bak_20240511
[rootcqnew ~]# cat /etc/sysctl.conf
# Controls IP packet forwarding
net.ipv4.ip_forward 0
# Controls source route verification
net.ipv4.conf.all.rp_filter0
net.ipv4.conf.default.rp_filter 0
# Do not accept source routing
net.ipv4.conf.default.accept_source_route 0
net.ipv4.conf.default.arp_announce 2
net.ipv4.conf.lo.arp_announce2
net.ipv4.conf.all.arp_announce2
# Controls the System Request debugging functionality of the kernel
kernel.sysrq 0
# Controls whether core dumps will append the PID to the core filename.
# Useful for debugging multi-threaded applications.
kernel.core_uses_pid 1
# Controls the use of TCP syncookies
net.ipv4.tcp_syncookies 1
net.ipv4.tcp_synack_retries 2
# Controls the default maxmimum size of a mesage queue
kernel.msgmnb 65536
# Controls the maximum size of a message, in bytes
kernel.msgmax 65536
# Controls the maximum shared segment size, in bytes
kernel.shmmax 68719476736
# Controls the maximum number of shared memory segments, in pages
kernel.shmall 2015134
kernel.shmmni 4096
kernel.sem 250 32000 100 128
fs.file-max 65536
net.ipv4.ip_local_port_range 1024 65000
net.core.rmem_default4194304
net.core.rmem_max4194304
net.core.wmem_default262144
net.core.wmem_max262144
fs.aio-max-nr 1048576
vm.swappiness0
net.core.somaxconn1024
net.ipv4.tcp_max_tw_buckets5000
net.ipv4.tcp_max_syn_backlog1024
net.ipv4.neigh.default.gc_stale_time120--使设置生效
sysctl -p
1.3.9.配置图形化界面
如果不配置后面安装过程中调用图形化界面会报错
yum groupinstall -y X Window System
yum groupinstall -y Desktop
yum groupinstall -y Chinese Support如果是Centos7
yum groupinstall GNOMEDesktop -y
yum groupinstall -y X Window System
1.3.10.Oracle用户设置Shell限制
编辑/etc/security/limits.conf
cp /etc/security/limits.conf /etc/security/limits.conf_bak_20240511
vim /etc/security/limits.conf
//最后添加如下参数
#add by oracle
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft nofile 65536
oracle hard nofile 65536
oracle soft memlock 6542450944
oracle hard memlock 6542450944
编辑/etc/pam.d/login
cp /etc/pam.d/login /etc/pam.d/login_bak_20240511vim /etc/pam.d/login
最后添加如下参数
session required /lib/security/pam_limits.so
session required pam_limits.so
编辑/etc/profile
cp /etc/profile /etc/profile_bak_20240511
vim /etc/profile
最后添加如下内容
#add by oracle
if [ root oracle ]; thenif [ /bin/bash /bin/ksh ]; thenulimit -p 16384ulimit -n 65536elseulimit -u 16384 -n 65536fi
fisource /etc/profile
编辑/etc/csh.login
cp /etc/csh.login /etc/csh.login_bak_20240511
vim /etc/csh.login
最后添加如下内容
#add by oracle
if ( $USER oracle) thenlimit maxproc 16384limit descriptors 65536
endif
编辑/home/oracle/.bash_profile
cp /home/oracle/.bash_profile /home/oracle/.bash_profile_bak_20240511
vim /home/oracle/.bash_profile
最后添加如下内容
#add by oracle
umask 022
export LANGen_US.UTF-8
export ORACLE_BASE/data/app/oracle
export ORACLE_HOME/data/app/oracle/product/10.2.0.1/dbhome_1
export ORACLE_HOSTNAMECentos3
export ORACLE_SIDcqnew
export NLS_LANGSIMPLIFIED CHINESE_CHINA.ZHS16GBK
export PATH$PATH:$ORACLE_HOME/bin
1.3.10.上传Oracle安装包上传至服务器并解压
将Oracle服务端10201_database_linux_x86_64.cpio.gz安装包上传至服务器保存在/data目录下面。
在/data目录下面会生成一个名为database的目录。
cd /data
[rootcqnew data]# ls -l
total 740684
-rw-r--r-- 1 oracle oinstall 758433170 May 11 10:21 10201_database_linux_x86_64.cpio.gz--解压安装包
cd /data
[rootcqnew data]# zcat 10201_database_linux_x86_64.cpio.gz | cpio -idmv /dev/null 21
[rootcqnew data]# ls -l
total 740684
-rw-r--r-- 1 oracle oinstall 758433170 May 11 10:21 10201_database_linux_x86_64.cpio.gz
drwxr-xr-x 6 94110 42424 4096 Oct 23 2005 database--赋予权限
[rootcqnew data]# chown -R oracle:oinstall database/[rootcqnew data]# ls -l
total 740684
-rw-r--r-- 1 oracle oinstall 758433170 May 11 10:21 10201_database_linux_x86_64.cpio.gz
drwxr-xr-x 6 oracle oinstall 4096 Oct 23 2005 database以上步骤完成后重启服务器验证下重启正常。
二.安装数据库软件
2.1.更改参数文件
--解压安装包
su - oracle
cd /data
zcat 10201_database_linux_x86_64.cpio.gz | cpio -idmv /dev/null 21
在/data目录下面会生成一个名为database的目录进入database目录。
编辑install目录下面的oraparam.ini文件在“Certified Versions”的项目中添加“redhat-6”。
su - oracle
cd /data/database/install/
cp oraparam.ini oraparam.ini_bak_20240511
vim oraparam.ini
[Certified Versions]
Linuxredhat-6,redhat-3,SuSE-9,redhat-4,UnitedLinux-1.0,asianux-1,asianux-2
2.2.开始安装
2.2.1.安装数据库软件
su - oracle
export DISPLAY192.168.16.122:0.0
cd /data/database/
export LANGen_US.UTF-8
./runInstaller
选择高级安装
选择高级安装“Advanced Installation”然后Next 指定产品清单目录和身份证明
指定产品清单目录和身份证明默认即可 选择安装类型
选择安装类型这里选择企业版“Enterprise Edition”点击“Product Language”按钮可以进行产品语言的选择 指定安装主目录
指定安装主目录的详细信息这里默认即可点击Next: 产品特定的先决条件检查
产品特定的先决条件检查注一般会出现warning默认即可点击Next 出现如下的Warning窗口点击“Yes”继续 只安装数据库软件 安装过程中会有报错画面直接“Continue”即可 执行脚本
执行以下脚本后点ok继续
/data/app/oracle/oraInventory/orainstRoot.sh
/data/app/oracle/product/10.2.0.1/dbhome_1/root.sh
脚本执行详细过程
[rootcqnew bin]# /data/app/oracle/oraInventory/orainstRoot.sh
Changing permissions of /data/app/oracle/oraInventory to 770.
Changing groupname of /data/app/oracle/oraInventory to oinstall.
The execution of the script is complete[rootcqnew bin]# /data/app/oracle/product/10.2.0.1/dbhome_1/root.sh
Running Oracle10 root.sh script...The following environment variables are set as:ORACLE_OWNER oracleORACLE_HOME /data/app/oracle/product/10.2.0.1/dbhome_1Enter the full pathname of the local bin directory: [/usr/local/bin]:Copying dbhome to /usr/local/bin ...Copying oraenv to /usr/local/bin ...Copying coraenv to /usr/local/bin ...Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed. 安装完成退出安装。 问题处理
调用图形化失败
如果调用图形化失败执行下面进行配置图形化界面操作
如果不配置后面安装过程中调用图形化界面会报错
yum groupinstall -y X Window System
yum groupinstall -y Desktop
yum groupinstall -y Chinese Support如果是Centos7
yum groupinstall GNOMEDesktop -y
yum groupinstall -y X Window System
图形化界面乱码
echo $LANG
export LANGen_US.UTF-8
三.迁移过程
3.1.传输物理文件
主要传输文件参考
1.数据文件
2.控制文件
3.重做日志文件
4.参数文件
5.监听文件tnsnames.ora listener.ora
6.密码文件PWDfile
直接拷贝oracle目录下的admin、oradata(datafile controlfileredo)、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目录。
--源库上操作 传输参数文件
su - oracle
[oraclecq ~]$ scp initcq_bak_20240607 oracle120.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目录
--目标库操作 备份已传输的参数文件避免改的有问题重新从源库服务器传输到目标库服务器
[oraclecqnew:/home/oracle]$ cp initcq_bak_20240607 initcq_bak_20240607_1--更改参数文件中的oracle_base目录为目标库的oracle_base目录
su - oracle
vi initcq_bak_20240607
:%s#旧目录#新目录#g
更改后记得确认避免更改错误。
注意事项如果源服务器和目标服务器的内存和cpu配置不一样需修改内存和cpu方面的参数内存公式如下
内存参数修改公式
--sga和pga配置的最佳实践
对于OLTP系统来说
--os_memory_total$(awk /MemTotal/{print $2} /proc/meminfo)
--$sga_targetos_memory_total * 8 * 8 / 100 / 1024
--pga_targetos_memory_total * 8 * 2 / 100 / 1024
对于DSS系统来说oracle建议是sga系统内存*0.8*0.5 单位字节
pga系统内存*0.8*0.5
更改前 更改initcq_bak_20240607参数文件
su - oracle
vi initcq_bak_20240607
--更改前
cq.__db_cache_size3858759680
cq.__java_pool_size16777216
cq.__large_pool_size16777216
cq.__shared_pool_size385875968
cq.__streams_pool_size0
*.aq_tm_processes0
*.audit_file_dest/opt/oracle/admin/cq/adump
*.background_dump_dest/opt/oracle/admin/cq/bdump
*.compatible10.2.0.1.0
*.control_files/opt/oracle/oradata/cq/control01.ctl,/opt/oracle/oradata/cq/control02.ctl,/opt/oracle/oradata/cq/control03.ctl
*.core_dump_dest/opt/oracle/admin/cq/cdump
*.db_block_size8192
*.db_domain
*.db_file_multiblock_read_count16
*.db_files500
*.db_namecq
*.db_recovery_file_dest/opt/oracle/flash_recovery_area
*.db_recovery_file_dest_size21474836480
*.dispatchers(PROTOCOLTCP) (SERVICEcqXDB)
*.filesystemio_optionssetall
*.job_queue_processes1000
*.open_cursors1000
*.pga_aggregate_target1073741824
*.processes1000
*.remote_login_passwordfileEXCLUSIVE
*.sessions1450
*.sga_target4294967296
*.undo_managementAUTO
*.undo_tablespaceUNDOTBS1
*.user_dump_dest/opt/oracle/admin/cq/udump更改后
*.aq_tm_processes0
*.audit_file_dest/data/app/oracle/admin/cq/adump
*.background_dump_dest/data/app/oracle/admin/cq/bdump
*.compatible10.2.0.1.0
*.control_files/data/oracle/oradata/cq/control01.ctl,/data/oracle/oradata/cq/control02.ctl,/data/oracle/oradata/cq/control03.ctl
*.core_dump_dest/data/app/oracle/admin/cq/cdump
*.db_block_size8192
*.db_domain
*.db_file_multiblock_read_count16
*.db_files500
*.db_namecq
*.db_recovery_file_dest/data/app/oracle/flash_recovery_area
*.db_recovery_file_dest_size21474836480
*.dispatchers(PROTOCOLTCP) (SERVICEcqXDB)
*.filesystemio_optionssetall
*.job_queue_processes1000
*.open_cursors1000
*.pga_aggregate_target1073741824
*.processes1000
*.remote_login_passwordfileEXCLUSIVE
*.sessions1450
*.sga_target4294967296
*.undo_managementAUTO
*.undo_tablespaceUNDOTBS1
*.user_dump_dest/data/app/oracle/admin/cq/udump3.1.1.3.目标库创建参数文件中不存在的目录
目标库所在服务器上创建参数文件中涉及的不存在的目录从上到下先查目录是否存在若不存在务必创建避免后面步骤报错。
--$ORACLE_BASE目录
[oraclecqnew ~]$ ls -ld /data/app/oracle
drwxr-xr-x 7 oracle oinstall 4096 May 14 11:10 /data/app/oracle--审计目录
[oraclecqnew:/home/oracle]$ ls -ld /data/app/oracle/admin/cq/adump
ls: cannot access /data/app/oracle/admin/cq/adump: No such file or directory[oraclecqnew:/home/oracle]$ mkdir -p /data/app/oracle/admin/cq/adump[oraclecqnew ~]$ ls -ld /data/app/oracle/admin/cq/adump
drwxr-xr-x 2 oracle oinstall 4096 Jun 8 11:01 /data/app/oracle/admin/cq/adump--数据目录 使用新的数据目录老的数据目录不创建新的数据目录上面已创建
[oraclecqnew:/home/oracle]$ ls -ld /opt/oracle/oradata/cq 老的数据目录
ls: cannot access /opt/oracle/oradata/cq: No such file or directory[oraclecqnew:/home/oracle]$ mkdir -p /data/oracle/oradata/cq/
[oraclecqnew:/home/oracle]$ chown -R oracle:oinstall /data/oracle/oradata/cq/[oraclecqnew:/home/oracle]$ ls -ld /data/oracle/oradata/cq 新的数据目录
drwxr-xr-x 2 oracle oinstall 4096 Jun 8 07:17 /data/oracle/oradata/cq--诊断目录
[oraclecqnew:/home/oracle]$ ls -ld /data/app/oracle/admin/cq/bdump
ls: cannot access /data/app/oracle/admin/cq/bdump: No such file or directory[oraclecqnew:/home/oracle]$ ls -ld /data/app/oracle/admin/cq/cdump
ls: cannot access /data/app/oracle/admin/cq/cdump: No such file or directory[oraclecqnew:/home/oracle]$ ls -ld /data/app/oracle/admin/cq/udump
ls: cannot access /data/app/oracle/admin/cq/udump: No such file or directory[oraclecqnew:/home/oracle]$ mkdir -p /data/app/oracle/admin/cq/{bdump,cdump,udump}
3.1.1.4.目标库start nomount启库
su - oracle
[oraclecqnew:/home/oracle]$ export ORACLE_SIDcq
[oraclecqnew:/home/oracle]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on чǚ¹ 6Ղ 8 11:58:17 2024Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to an idle instance.SQL startup nomount pfile/home/oracle/initcq_bak_20240607;
ORACLE instance started.Total System Global Area 8.5516E10 bytes
Fixed Size 2262656 bytes
Variable Size 1.1543E10 bytes
Database Buffers 7.3820E10 bytes
Redo Buffers 151117824 bytes
SQL
3.1.1.5.目标库生成spfile参数文件(勿忘记)
该步骤不可省略因为启库默认查找$ORACLE_HOME/dbs下的spfilesid.ora文件如果不存在
SQL create spfile from pfile/home/oracle/initcq_bak_20240607;
SQL shutdown immediate;
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
--源库操作 control01.ctl control02.ctl control03.ctl传输
su - oracle
cd /opt/oracle/oradata/cq/
[oraclecq ~]$ scp *.ctl oracle120.46.146.123:/data/oracle/oradata/cq/
目标库启库至mount状态
--目标库操作 启库到mount状态
SQL alter database mount; --启动到mount状态
3.3.传输数据文件
源库数据文件位置
/opt/oracle/oradata/cq/
目标库数据文件位置/data/oracle/oradata/cq/
本文档步骤采用目标库和源库数据文件位置不一样新的数据目录需提前创建。
mkdir -p /data/oracle/oradata/cq/
chown -R oracle:oinstall /data/oracle/oradata/cq/
目标库目录检查
目标库操作 检查数据文件所有目录是否存在若不存在创建不存在的数据目录。
--目标库操作 创建不存在的新的数据目录 上面步骤已创建
[oraclecqnew:/home/oracle]$ ls -ld /data/oracle/oradata/cq/
drwxr-xr-x 2 oracle oinstall 4096 Jun 8 07:17 /data/oracle/oradata/cq/[rootcqnew:/root]$ mkdir -p /data/oracle/oradata/cq/
[rootcqnew:/root]$ chown -R oracle:oinstall /data/oracle/oradata/cq/
源库传输数据文件至目标库
scp方式(文件小)
--源库操作 传输数据文件
cd /opt/oracle/oradata/cq/
[oraclecq cq]$ ls -lhtr
total 163G
-rw-r----- 1 oracle oinstall 51M Jun 7 18:32 redo01.log
-rw-r----- 1 oracle oinstall 51M Jun 7 22:00 redo02.log
-rw-r----- 1 oracle oinstall 2.7G Jun 7 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 51M Jun 7 22:15 redo03.log
-rw-r----- 1 oracle oinstall 551M Jun 7 22:15 system01.dbf
-rw-r----- 1 oracle oinstall 1.2G Jun 7 22:15 undotbs01.dbf
-rw-r----- 1 oracle oinstall 521M Jun 7 22:15 sysaux01.dbf
-rw-r----- 1 oracle oinstall 446M Jun 7 22:15 users01.dbf
-rw-r----- 1 oracle oinstall 31G Jun 7 22:15 CQSCHEMA1.dbf
-rw-r----- 1 oracle oinstall 32G Jun 7 22:15 CQSCHEMA2.dbf
-rw-r----- 1 oracle oinstall 32G Jun 7 22:15 CQSCHEMA3.dbf
-rw-r----- 1 oracle oinstall 32G Jun 7 22:15 CQSCHEMA4.dbf
-rw-r----- 1 oracle oinstall 32G Jun 7 22:15 CQSCHEMA5.dbf
-rw-r----- 1 oracle oinstall 6.9M Jun 7 22:15 control03.ctl
-rw-r----- 1 oracle oinstall 6.9M Jun 7 22:15 control02.ctl
-rw-r----- 1 oracle oinstall 6.9M Jun 7 22:15 control01.ctl[oraclecq ~]$ scp *.dbf oracle120.46.146.123:/data/oracle/oradata/cq/
rsync方式(文件大)
配置免密
#1.本地生成密钥文件(172.17.130.7上操作)
ssh-keygen --全程敲回车#2.将本地公钥传输到远程主机(172.17.130.7上操作)
ssh-copy-id -i ~/.ssh/id_rsa.pub root172.17.76.18#3.测试本地免密登录到远程主机(172.17.130.7上操作,ssh 目标服务器ip)
ssh 172.17.76.18
不提示输入密码即免密配置成功
rsync传输
nohup /usr/bin/rsync -av --progress /opt/oracle/oradata/cq/redo* root120.46.146.123:/data/oracle/oradata/cq/ /tmp/load.log 21 参数说明
-z: --compress 使用压缩机制 时间长带宽速度慢相反时间段带宽速度快
-v: --verbose 打印详细信息
-r: --recursive 以递归模式同步子目录
--progress 实时的显示拷贝进度, 以及传输速率等信息. 尤其是拷贝大文件时, 程序不输出信息, 用户往往无法区分程序是在响应中, 还是已经挂起
-a --archive 归档模式表示递归传输并保持文件属性。可以同步元信息比如修改时间、权限等比-r更有用
-r --recursive表示递归即包含子目录。注意-r是必须的否则 rsync 运行不会成功
3.4.传输日志文件
源库日志文件位置/opt/oracle/oradata/cq/
目标库日志文件位置/data/oracle/oradata/cq/
源库传输日志文件至目标库
--源库操作 传输数据文件
cd /opt/oracle/oradata/cq/
[oraclecq cq]$ ls -lhtr
total 163G
-rw-r----- 1 oracle oinstall 51M Jun 7 18:32 redo01.log
-rw-r----- 1 oracle oinstall 51M Jun 7 22:00 redo02.log
-rw-r----- 1 oracle oinstall 2.7G Jun 7 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 51M Jun 7 22:15 redo03.log
-rw-r----- 1 oracle oinstall 551M Jun 7 22:15 system01.dbf
-rw-r----- 1 oracle oinstall 1.2G Jun 7 22:15 undotbs01.dbf
-rw-r----- 1 oracle oinstall 521M Jun 7 22:15 sysaux01.dbf
-rw-r----- 1 oracle oinstall 446M Jun 7 22:15 users01.dbf
-rw-r----- 1 oracle oinstall 31G Jun 7 22:15 CQSCHEMA1.dbf
-rw-r----- 1 oracle oinstall 32G Jun 7 22:15 CQSCHEMA2.dbf
-rw-r----- 1 oracle oinstall 32G Jun 7 22:15 CQSCHEMA3.dbf
-rw-r----- 1 oracle oinstall 32G Jun 7 22:15 CQSCHEMA4.dbf
-rw-r----- 1 oracle oinstall 32G Jun 7 22:15 CQSCHEMA5.dbf
-rw-r----- 1 oracle oinstall 6.9M Jun 7 22:15 control03.ctl
-rw-r----- 1 oracle oinstall 6.9M Jun 7 22:15 control02.ctl
-rw-r----- 1 oracle oinstall 6.9M Jun 7 22:15 control01.ctl[oraclecq ~]$ scp *.log oracle120.46.146.123:/data/oracle/oradata/cq/
3.5.更改控制文件中数据文件和日志文件位置
若源库和目标库的数据目录、日志目录相同需忽略该步骤。
--生成数据目录转换脚本
select alter database rename || a.FILE# || to || a.NAME || ;
from v$datafile a
union all
select alter database rename || a.FILE# || to || a.NAME || ;
from v$tempfile a
union all
SELECT alter database rename || a.MEMBER || to ||
a.MEMBER || ;
FROM v$logfile a--在库中执行 以上sql输出的语句
alter database rename 9 to /data/oracle/oradata/cq/CQSCHEMA5.dbf;
alter database rename 8 to /data/oracle/oradata/cq/CQSCHEMA4.dbf;
alter database rename 7 to /data/oracle/oradata/cq/CQSCHEMA3.dbf;
alter database rename 6 to /data/oracle/oradata/cq/CQSCHEMA2.dbf;
alter database rename 5 to /data/oracle/oradata/cq/CQSCHEMA1.dbf;
alter database rename 4 to /data/oracle/oradata/cq/users01.dbf;
alter database rename 3 to /data/oracle/oradata/cq/sysaux01.dbf;
alter database rename 2 to /data/oracle/oradata/cq/undotbs01.dbf;
alter database rename 1 to /data/oracle/oradata/cq/temp01.dbf;
alter database rename 1 to /data/oracle/oradata/cq/system01.dbf;
alter database rename /opt/oracle/oradata/cq/redo03.log to /data/oracle/oradata/cq/redo03.log ;
alter database rename /opt/oracle/oradata/cq/redo02.log to /data/oracle/oradata/cq/redo02.log ;
alter database rename /opt/oracle/oradata/cq/redo01.log to /data/oracle/oradata/cq/redo01.log ;
3.6.目标库启库至open
--目标库操作 启库到open状态
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
--源库操作 传输密码文件
[oraclecq dbs]$ ls -l
total 7.0M
-rw-rw---- 1 oracle oinstall 1.6K Jun 8 11:00 hc_cq.dat
-rw-r----- 1 oracle oinstall 13K May 3 2001 initdw.ora
-rw-r----- 1 oracle oinstall 8.2K Sep 11 1998 init.ora
-rw-rw---- 1 oracle oinstall 24 Jun 8 08:33 lkCQ
-rw-r----- 1 oracle oinstall 1.5K Jun 8 09:36 orapwcq
-rw-r----- 1 oracle oinstall 6.9M Jun 8 08:43 snapcf_cq.f
-rw-r----- 1 oracle oinstall 3.5K Jun 8 11:00 spfilecq.ora[oraclecq dbs]$ pwd
/opt/oracle/product/10.2/db_1[oraclecq ~]$ scp orapwcq oracle120.46.146.123:/data/app/oracle/product/10.2.0.1/dbhome_1/dbs/
3.8.传输监听文件(可选)
如果监听文件没有特殊设置可省略该步骤。
本文档案例监听文件没有特殊设置省略该步骤
建议将源系统的network下的文件拷过来根据实际情况修改listener.ora和tnsnames.ora
目标库停止监听
--目标库操作 停止监听服务
su - oracle
lsnrctl stop
lsnrctl status--目标库操作 监听文件备份
cd /opt/oracle/product/10.2/db_1/network/admin
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
--源库操作 传输监听文件
cd /opt/oracle/product/10.2/db_1/network/admin
[oraclecq admin]$ ls -l
total 20K
-rw-rw-r-- 1 oracle oinstall 486 Jul 22 2022 listener.ora
drwxr-x--- 2 oracle oinstall 4.0K Jul 22 2022 samples
-rw-r----- 1 oracle oinstall 172 Dec 26 2003 shrept.lst
-rw-r--r-- 1 oracle oinstall 563 Jul 31 2022 sqlnet.log
-rw-rw-r-- 1 oracle oinstall 509 Aug 2 2022 tnsnames.ora[oraclecq admin]$ pwd
/opt/oracle/product/10.2/db_1/network/admin[oraclecq ~]$ scp listener.ora oracle120.46.146.123:/data/app/oracle/product/10.2.0.1/dbhome_1/dbs/network/admin/
[oraclecq ~]$ scp tnsnames.ora oracle120.46.146.123:/data/app/oracle/product/10.2.0.1/dbhome_1/dbs/network/admin/
目标库启动监听
--目标库操作 启动监听服务
su - oracle
lsnrctl start
lsnrctl status
3.9.目标库数据量统计
目标库数据量统计
--查看数据量
select sum(bytes)/1024/1024/1024 as size(G) from dba_data_files;size(G)
----------
159.34082select tablespace_name,sum(bytes)/1024/1024/1024 GB from dba_data_files group by tablespace_name;
TABLESPACE_NAME GB
------------------------------ ----------
SYSAUX .5078125
UNDOTBS1 1.12304688
CQSCHEMA 156.738281
USERS .434570313
SYSTEM .537109375--查看总大小
select round(sum(bytes) / 1024 / 1024 / 1024, 2) || Gfrom dba_segmentswhere owner not in (MDSYS, OUTLN, CTXSYS, OLAPSYS, HR, SYSTEM,EXFSYS, SCOTT, DBSNMP, ORDSYS, SYSMAN, OE, PM, SH,XDB, ORDDATA, IX, SYS, WMSYS) ;
或
select round(sum(bytes) / 1024 / 1024 / 1024, 2) || Gfrom dba_segments;ROUND(SUM(BYTES)/1024/1024/1024,2)||G
-----------------------------------------
157.91G--查看用户
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
------------------------------ ------------------------------ ------------------------------
BACKUPKEVIN USERS TEMP
TESTSSS USERS TEMP
YUNTEST USERS TEMP
CQTEST CQSCHEMA TEMP
CQUSER CQSCHEMA TEMP
DEV CQSCHEMA TEMP
TEST CQSCHEMA TEMP
DMSYS SYSAUX TEMP
TSMSYS USERS TEMP9 rows selected.--每个业务用户下的总对象数量校验
SELECT D.OWNER,COUNT(1)FROM dba_objects dWHERE d.OWNER in (BACKUPKEVIN,TESTSSS,YUNTEST,CQTEST,CQUSER,DEV,TEST,DMSYS,TSMSYS)AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_nameD.OBJECT_NAME AND D.OWNERB.owner) GROUP BY D.OWNERORDER BY D.OWNER ;OWNER COUNT(1)
------------------------------ ----------
CQTEST 407
CQUSER 109
DEV 458
DMSYS 189
TEST 326
TESTSSS 36
TSMSYS 3
YUNTEST 4528 rows selected.--每个业务用户下的各个对象类别的数量校验
SQL set pagesize 999
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;OWNER OBJECT_TYPE STATUS COUNT(OBJECT_NAME)
------------------------------ ------------------- ------- ------------------
CQTEST INDEX VALID 161
CQTEST LOB VALID 156
CQTEST TABLE VALID 90
CQUSER INDEX VALID 51
CQUSER LOB VALID 11
CQUSER TABLE VALID 47
DEV PROCEDURE INVALID 1
DEV VIEW INVALID 5
DEV INDEX VALID 179
DEV LOB VALID 162
DEV PROCEDURE VALID 3
DEV TABLE VALID 103
DEV VIEW VALID 5
DMSYS FUNCTION VALID 12
DMSYS INDEX VALID 2
DMSYS LIBRARY VALID 6
DMSYS PACKAGE VALID 27
DMSYS PACKAGE BODY VALID 23
DMSYS PROGRAM VALID 9
DMSYS SEQUENCE VALID 1
DMSYS TABLE VALID 2
DMSYS TYPE VALID 94
DMSYS TYPE BODY VALID 12
DMSYS VIEW VALID 1
TEST INDEX VALID 137
TEST LOB VALID 110
TEST TABLE VALID 79
TESTSSS INDEX VALID 20
TESTSSS LOB VALID 5
TESTSSS TABLE VALID 11
TSMSYS INDEX VALID 1
TSMSYS LOB VALID 1
TSMSYS TABLE VALID 1
YUNTEST INDEX VALID 172
YUNTEST LOB VALID 175
YUNTEST PROCEDURE VALID 4
YUNTEST TABLE VALID 97
YUNTEST VIEW VALID 438 rows selected.--查询回收站lob对象数量(可选),lob数量不一致排查
select owner,type,count(*) from dba_recyclebin group by owner,type order by owner;
no rows selected--数据量统计
----新增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);
BEGINIF owner IS NULL THENstmt : select count(*) from || table_name || ;ELSEstmt : 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_nameENTSERVICE
order by nrows desc;
最后Oracle10单点异机冷备迁移数据目录不同的场景下的操作步骤和Oracle11g一样。
吐槽一点上云容易下云难阿里云上的文件传送到华为云服务器速度竟低至3.4M/s。
