1.GaussDB数据库索引的管理包含以下内容  --索引管理 (1)创建唯一索引,必须指定表的分布键,否则无法成功。 (2)创建复合索引 (3)创建部分索引,只在表的一部分数据上创建索引 (4)创建函数索引 (5)创建分区表的本地索引 (6)创建分区表的全局索引,并指定索引所在的表空间。 (7)索引重命名 (8)修改索引的表空间 (9)让索引不可用 (10)重建索引 (11)重建索引的分区 (12)让索引的某个分区不可用 (13)修改索引分区的名字 (14)移动索引的某个分区到另外的表空间 (15)删除索引 (16)重建表上单个索引 (17)重建表上所有索引  2.GaussDB索引管理具体操作如下  --创建测试数据： sjzt=> create table t1 as select oid,* from pg_class limit 10; NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'relallvisible' as the distribution column by default. HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. INSERT 0 10 -- 创建唯一索引 --创建索引的时候必须指定分布键,否则无法创建成功。 sjzt=> create unique index t1_fn_idx on t1(relfilenode,relallvisible);  CREATE INDEX  -- 创建复合索引 sjzt=> create index t1_owner_tbs_idx on t1(relowner,reltablespace);  CREATE INDEX -- 创建部分索引 sjzt=> create index t1_lttbs_idx on t1(reltablespace) where reltablespace<20; CREATE INDEX -- 创建函数索引 sjzt=> create index t1_upname_idx on t1(upper(relname));  CREATE INDEX -- 创建分区表的本地索引 sjzt=> create index pt1_id_idx on pt1(id) local;  CREATE INDEX -- 创建分区表的全局索引,并指定索引所在的表空间。 sjzt=> create index pt1_score_idx on pt1(score) global tablespace tbs2; CREATE INDEX  --索引重命名 sjzt=> alter index t1_fn_idx rename to t1_fn_idx2; ALTER INDEX ---修改索引的表空间 sjzt=> alter index t1_fn_idx2 set tablespace tbs3; ALTER INDEX --让索引不可用 sjzt=> alter index t1_lttbs_idx unusable; ALTER INDEX --重建索引。 sjzt=> alter index t1_lttbs_idx rebuild; REINDEX --重建索引的分区 sjzt=> alter index pt1_id_idx rebuild partition p1_id_idx; REINDEX --让索引的某个分区不可用 sjzt=> alter index pt1_id_idx modify partition p1_id_idx unusable; ALTER INDEX --修改索引分区的名字 sjzt=> alter index pt1_id_idx rename partition p1_id_idx to p1_id_idx2; ALTER INDEX --移动索引的某个分区到另外的表空间 sjzt=>  alter index pt1_id_idx move partition p1_id_idx2 tablespace tbs3; ALTER INDEX --删除索引： sjzt=> drop index t1_lttbs_idx; DROP INDEX -- 重建表上单个索引 sjzt=> reindex index t1_lttbs_idx; REINDEX -- 重建表上所有索引 sjzt=> reindex table t1; REINDEX  3.总结  GaussDB的索引管理，不仅支持我们常见的索引。还支持Reindex关键字，一键重建所有的索引。  索引的管理非常方便，灵活。索引的每个分区都可以独立管理。包括删除，重建，重命名，修改存储表空间等。非常灵活多变 

 1.视图的管理内容如下  --视图管理 (1)创建视图 (2)创建物化视图 (3)查询视图数据 (4)查询物化视图 (5)查询视图定义pg_get_viewdef (6)重命名视图 (7)修改视图属主 (8)修改视图schema (9)刷新物化视图 (10)删除视图 (11)删除物化视图 2.视图的基本操作步骤   -- 创建视图 sjzt=> create view v1 as SELECT * FROM pg_tablespace WHERE spcname = 'pg_default'; CREATE VIEW    -- 创建物化视图 sjzt=> create materialized view mv1 tablespace tbs2 as SELECT * FROM pg_tablespace WHERE spcname = 'pg_default'; NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'spcmaxsize' as the distribution column by default. HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. INSERT 0 1  -- 查询视图数据 sjzt=> select * from v1;   spcname   | spcowner | spcacl | spcoptions | spcmaxsize | relative  ------------+----------+--------+------------+------------+----------  pg_default |       10 |        |            |            | f (1 row)  -- 查询物化视图 sjzt=> select * from mv1;    spcname   | spcowner | spcacl | spcoptions | spcmaxsize | relative  ------------+----------+--------+------------+------------+----------  pg_default |       10 |        |            |            | f (1 row) -- 查询视图定义pg_get_viewdef sjzt=> select pg_get_viewdef('v1');                                   pg_get_viewdef                                   ----------------------------------------------------------------------------------  SELECT  * FROM pg_tablespace WHERE (pg_tablespace.spcname = 'pg_default'::name); (1 row) -- 重命名视图 sjzt=> alter view v1 rename to v2;  ALTER VIEW   -- 修改视图属主 sjzt=> alter view v2 owner to jack; ERROR:  must be member of role "jack" --这里修改报错,原因是sjzt用户必须是jack用户的成员。 sjzt=> \c - sjzt Password for user sjzt:  Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "sjzt" as user "sjzt". sjzt=> grant sjzt to jack;  GRANT ROLE sjzt=> \c - jack Password for user jack:  Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "sjzt" as user "jack". sjzt=> alter view sjzt.v2 owner to jack; ALTER VIEW   --注意:     这里将视图修改属主,有两个步骤:     1.登陆自己,将自己的角色(sjzt)授予别人(jack):登陆sjzt用户:grant sjzt to jack;     2.登陆别人,将自己的视图属主权限授予别人:登陆jack用户:alter view sjzt.v2 owner to jack;      -- 修改视图schema --当前用户是JACK用户/数据库是SJZT: sjzt=> \c - jack Password for user jack:  Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "sjzt" as user "jack" sjzt=> create schema jack authorization jack; CREATE SCHEMA sjzt=> alter view sjzt.v2 set schema jack; ALTER VIEW sjzt=> \dv            List of relations  Schema | Name | Type | Owner | Storage  --------+------+------+-------+---------  jack   | v2   | view | jack  |  (1 row)   -- 刷新物化视图 sjzt=> \c - sjzt Password for user sjzt:  Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "sjzt" as user "sjzt". sjzt=>  sjzt=> refresh materialized view mv1; INSERT 0 1 -- 删除视图,视图现在属于jack,所以需要登陆jack操作。 sjzt=> \c - jack Password for user jack:  Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "sjzt" as user "jack". sjzt=> \dv            List of relations  Schema | Name | Type | Owner | Storage  --------+------+------+-------+---------  jack   | v2   | view | jack  |  (1 row)   sjzt=> drop view jack.v2; DROP VIEW   -- 删除物化视图,雾化视图MV1属于sjzt用户,所以需要切换用户。 sjzt=> \c - sjzt Password for user sjzt:  Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "sjzt" as user "sjzt". sjzt=> \d                                          List of relations  Schema |     Name     |       Type        | Owner |                    Storage                      --------+--------------+-------------------+-------+------------------------------------------------  sjzt   | dept         | table             | sjzt  | {orientation=row,compression=no}  sjzt   | emp          | table             | sjzt  | {orientation=row,compression=no}  sjzt   | emp2         | table             | sjzt  | {orientation=row,compression=no}  sjzt   | mv1          | materialized view | sjzt  | {orientation=row,compression=no}  sjzt   | part_tbl1    | table             | sjzt  | {orientation=row,compression=no}  sjzt   | part_tbl2    | table             | sjzt  | {orientation=row,compression=no}  sjzt   | pt1          | table             | sjzt  | {orientation=row,compression=no}  sjzt   | s_seq01      | sequence          | sjzt  |   sjzt   | t1           | table             | sjzt  | {orientation=row,compression=no}  sjzt   | v_1          | view              | sjzt  |   sjzt   | warehouse_t1 | table             | sjzt  | {orientation=row,compression=no}  sjzt   | warehouse_t2 | table             | sjzt  | {orientation=row,fillfactor=70,compression=no}  sjzt   | warehouse_t3 | table             | sjzt  | {orientation=column,compression=high} (13 rows)   sjzt=> drop materialized view mv1; DROP MATERIALIZED VIEW  3.总结  这里有两个点需要注意:  （1）修改视图的属主，需要首选将自己的角色授予对象，然后登陆对方，授予自己的视图权限到对象。  （2）GaussDB实现了物化视图的功能，且比oracle的物理视图更加简单。Oracle的物化视图是需要创建日志表的，但是GaussDB直接省略了创建日志的步骤。直接一句命令就可以创建物化视图。物化视图需要使用refresh进行刷新才可以看到最新的数据。 作者：薛双奇

 1.序列的管理主要包含以下内容  创建管理 (1)创建序列 (2)创建序列并设置最大值，最小值，步长 (3)递增序列并返回新值 (4)设置序列的当前数值 (5)修改序列的属主,同修改视图的属主。 (6)删除序列 (7)使用元命令查看序列 (8)使用序列名称查询序列 (9)修改某个字段的默认值为序列 2.序列的使用演示  --创建序列 sjzt=> create sequence seq01; CREATE SEQUENCE --创建序列并设置最大值，最小值，步长 sjzt=> create sequence seq02 increment by 1 minvalue 1 maxvalue 99999 cache 1 nocycle; CREATE SEQUENCE -- 递增序列并返回新值 sjzt=> select nextval('seq01');  nextval  ---------        1 (1 row) sjzt=>  select seq01.nextval;  nextval  ---------        2 (1 row) -- 最近一次nextval返回的值,currval 函数暂时不支持。 sjzt=> select currval('seq01'); ERROR:  currval function is not supported CONTEXT:  referenced column: currval sjzt=> select seq01.currval; ERROR:  currval function is not supported CONTEXT:  referenced column: currval -- 最近一次nextval返回的值 sjzt=> select lastval(); ERROR:  lastval function is not supported CONTEXT:  referenced column: lastval   -- 设置序列的当前数值 sjzt=> select setval('seq01',1);  setval  --------       1 (1 row)   sjzt=> alter sequence seq01 maxvalue 99999; ALTER SEQUENCE   --修改序列的属主,同修改视图的属主。 --都是需要当前用户是目标属主的成员。然后在目标属主中修改当前序列的属主。 --sjzt是jack的成员; gaussdb=# \du      List of roles  Role name |Attributes|Member of                        -----------+-+--------  jack      | | {sjzt} sjzt=\c - jack Password for user jack:  Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "sjzt" as user "jack". sjzt=> alter sequence sjzt.seq01 owner to jack; ALTER SEQUENCE sjzt=>  --删除序列 sjzt=> drop sequence seq01; DROP SEQUENCE sjzt=> drop sequence seq02 cascade; DROP SEQUENCE   -- 使用元命令查看序列 sjzt=> create sequence seq01; CREATE SEQUENCE sjzt=>  \d seq01               Sequence "sjzt.seq01"     Column     |  Type   |        Value         ---------------+---------+---------------------  sequence_name | name    | seq01  last_value    | bigint  | -1  start_value   | bigint  | 1  increment_by  | bigint  | 1  max_value     | bigint  | 9223372036854775807  min_value     | bigint  | 1  cache_value   | bigint  | 1  log_cnt       | bigint  | 0  is_cycled     | boolean | f  is_called     | boolean | f  uuid          | bigint  | 11000002   -- 查询序列 sjzt=>  select * from seq01;  sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called |   uuid    ---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------+----------  seq01         |         -1 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | f         | 11000002 (1 row)    --序列应用： sjzt=> create table t2(id serial,name varchar(20),tag int);  NOTICE:  CREATE TABLE will create implicit sequence "t2_id_seq" for serial column "t2.id" NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default. HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE sjzt=> insert into t2 values(default,'Jerry'); INSERT 0 1 sjzt=> insert into t2 values(default,'Tom'); INSERT 0 1 sjzt=> select * from t2;  id | name  | tag  ----+-------+-----   1 | Jerry |       2 | Tom   |     (2 rows)   --修改某个字段的默认值为序列 sjzt=> alter table t2 alter tag set default nextval('seq01'); ALTER TABLE sjzt=> insert into t2 values(default,'Jack'); INSERT 0 1 sjzt=> insert into t2 values(default,'Jhon'); INSERT 0 1 sjzt=> select * from t2;  id | name  | tag  ----+-------+-----   3 | Jack  |   1   1 | Jerry |       2 | Tom   |       4 | Jhon  |   2 (4 rows) --使用序列名称查询序列 sjzt=> select * from seq01;  sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called |   uuid    ---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------+----------  seq01         |          2 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | t         | 11000002 (1 row)  3.总结  这里需要注意，GaussDB的序列，不支持如下三种：  select currval('seq01');  select seq01.currval;  select lastval();  取而代之的是使用：select * from seq01;   直接使用序列的名称查看序列的最小值，最大值，当前值等详细信息。这个与以往其他数据库的使用略有差异。 

 1.同义词概述 同义词概述 同义词（SYNONYM）是数据库对象的别名，用于记录与其他数据库对象名间的映射关系，用户可以使用同 义词访问关联的数据库对象，支持的数据库对象包括：表、视图、函数和存储过程。 若指定模式名称，则同义词在指定模式中创建。否则，在当前模式创建 使用同义词时，用户需要具有对关联对象的相应权限，包括：    SELECT、INSERT、UPDATE、DELETE、EXPLAIN、CALL 2.同义词管理包含如下内容  (1)创建表的同义词 (2)创建视图的同义词 (3)创建函数的存储过程并调用 (4)创建存储过程的同义词并调用 (5)删除同义词 3.同义词演示如下  -- 创建表的同义词 sjzt=> create synonym syn_t1 for t1; CREATE SYNONYM  -- 创建视图的同义词 sjzt=> create synonym syn_v1 for v_1; CREATE SYNONYM sjzt=> select * from syn_v1;  id | ename | sal  | empno | deptno  ----+-------+------+-------+--------   1 | xsq1  | 2000 |  6379 |     10 (1 row)      -- 创建函数同义词 sjzt=> \df                                                   List of functions  Schema |         Name          | Result data type | Argument data types |  Type  | fencedmode | propackage | prokind  --------+-----------------------+------------------+---------------------+--------+------------+------------+---------  sjzt   | package_func_overload | integer          | col integer         | normal | f          | t          | f (1 row)   --创建函数的存储过程并调用 sjzt=> create synonym syn_f_pfo for package_func_overload; CREATE SYNONYM sjzt=> select package_func_overload(1); one int parameters 122  package_func_overload  -----------------------                      0 (1 row)   --创建存储过程的同义词并调用 sjzt=> create synonym syn_proc_emp for my_proc; CREATE SYNONYM sjzt=> call syn_proc_emp(); inner emp_id =7799 outer_var =6688 outer emp_id =7788  syn_proc_emp  --------------   (1 row)   --删除同义词 sjzt=> drop synonym syn_proc_emp; DROP SYNONYM  4.总结  同义词是非常有用的，使用访问同义词的用户需要有关联对象的 SELECT、INSERT、UPDATE、DELETE、EXPLAIN、CALL 这些权限。同义词能够屏蔽具体的对象，而开放一个不真实的名称进行对外访问，提高了安全性。 作者：薛双奇

1、安装依赖包yum install gawk gcc make which perl crontabs unzip tar numactl rsync cut iplogrotate sed sha256sum wc xargsyum install -y gcc patch libffi-devel python-devel yum install -y zlib-devel yum install -y bzip2-devel yum install -y openssl-devel yum install -y ncurses-devel yum install -y sqlite-devel yum install -y readline-devel yum install -y tk-devel yum install -y gdbm-devel yum install -y db4-devel yum install -y libpcap-devel yum install -y xz-devel useradd ugo passwd ugo 密码:ugo 2、上传包UGO24.1.T0229.zip unzip UGO24.1.T0229.zip cd UGO24.1.T0229tar xvf ThirdPartySoftware.tar.gzcd ThirdPartySoftwarebash install.sh --第三方工具安装输出内容[+] All the required softwares will be installed under root user and required environment variables will be configured for user where UGO will be installed.[?] Enter the username where UGO is going to be installed (User will be created if not exists) : ugoTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT[+] Pre-Check Started[+] Pre-Check system Architecture[+] Current System Architecture : x86_64[+] Pre-Check OS[!] Current OS Type is not compatible.Do you want to continue(y/n)?y[+] Pre-Check gcc[+] current gcc version: gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44)[+] Pre-Check Make command[+] current make version: GNU Make 3.82[+] Pre-Check which[+] Pre-check Perl[+] Current perl Version : 5.16.3[+] Pre-Check cron[+] Pre-Check unzip[+] current unzip version: 6.00[+] Pre-Check numactl libs[+] Pre-Check rsync[+] current rsync version: rsync version 3.1.2 protocol version 31[+] Pre-Check other system softwaresTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT[+] Pre-Check username[+] Pre-Check Memory[+] Current available memory : 14GB[+] Pre-Check Disk[+] Available disk space : 43GB[+] Pre-Check CPU Cores[+] Current CPU Core Count : 4TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT[+] Installation Started.[+] Start check haveged installed or notwarning: haveged-1.9.1-1.h1.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 255f3799: NOKEYPreparing... ################################# [100%]Updating / installing... 1:haveged-1.9.1-1.h1 ################################# [100%]Created symlink from /etc/systemd/system/multi-user.target.wants/haveged.service to /usr/lib/systemd/system/haveged.service.[+] Extracting libaio package.[+] Installing libaio.[+] Extracting zlib package.[+] Configuring zlib.[+] Compiling zlib.[+] Installing zlib.[+] Extracting libffi package.[+] Configuring libffi.[+] Compiling libffi.[+] Installing libffi.[+] Extracting openssl package.[+] Configuring openssl.[+] Compiling openssl.[+] Installing openssl.[+] Extracting Python tar.xz package to tar.[+] Extracting Python tar package.[+] Compiling python.[+] Installing python.[+] Installing python modules...[+] Installing six.[+] Installing protobuf.[+] Installing pymysql.[+] Installing pycparser.[+] Installing cffi.[+] Installing cryptography.[+] Installing defusedxml.[+] Installing xlsxwriter.[+] Installing charset_normalizer.[+] Installing certifi.[+] Installing idna.[+] Installing urllib3.[+] Installing requests.[+] Extracting jre package.[+] Installation Completed.TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT[+] Configuring .bashrc[+] Configuring Crontab[+] User has access to crontab.[+] Configuration Completed.TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT[+] Verification Started.[+] Verifying Python installation[+] Version : Python 3.10.12[+] Verifying Python module ssl.[+] Verifying Python module defusedxml.[+] Verifying Python module zlib.[+] Verifying Python module gzip.[+] Verifying Python module pymysql.[+] Verifying Python module pycparser.[+] Verifying Python module cffi.[+] Verifying Python module cryptography.[+] Verifying Python module charset_normalizer.[+] Verifying Python module certifi.[+] Verifying Python module idna.[+] Verifying Python module urllib3.[+] Verifying Python module requests.[+] Verification Ended.2、Python3检查[root@oracle11gdg ThirdPartySoftware]# python3 --versionPython 3.10.123、手工创建web签名mkdir /root/ugo keytool -genkey -alias UGOWebKeystoreCRT -validity 3650 -keyalg RSA -keystore "/root/ugo/UGOWebKeystore" -keysize 2048 -storetype PKCS12 [root@oracle11gdg ~]# keytool -genkey -alias UGOWebKeystoreCRT -validity 3650 -keyalg RSA -keystore "/root/ugo/UGOWebKeystore" -keysize 2048 -storetype PKCS12Enter keystore password: Re-enter new password: What is your first and last name? [Unknown]: xueshuangqiWhat is the name of your organizational unit? [Unknown]: attWhat is the name of your organization? [Unknown]: attWhat is the name of your City or Locality? [Unknown]: chengduWhat is the name of your State or Province? [Unknown]: sichuanWhat is the two-letter country code for this unit? [Unknown]: 001Is CN=xueshuangqi, OU=att, O=att, L=chengdu, ST=sichuan, C=001 correct? [no]: yes4、解压安装包cd /root/UGO24.1.T0229/tar xvf UGO.tar.gz cp /root/LICUGO_2_20240327RWY55L.xml /root/UGO24.1.T0229/UGOcd /root/UGO24.1.T0229/UGO LICUGO_2_20240327RWY55L.xml 是向华为申请的LICENSE文件，和MAC地址绑定。 --UGO的数据库安装时设置一个负载的密码:设置一个UGO数据库用户密码: Ugo#1234 python3 install.py install --install-repo-db --license /root/UGO24.1.T0229/UGO/LICUGO_2_20240327RWY55L.xml --web-cert /root/ugo/UGOWebKeystore --os-user ugoPreCheck => Java | OK | Java - ( Version: 1.8.0_392, Distribution: openjdk ) Triggering install for given user ... PreCheck => OS User | OK | Current user verified successfully. PreCheck => Java | OK | Java - ( Version: 1.8.0_392, Distribution: openjdk ) PreCheck => openssl | OK | openssl verified successfully. PreCheck => keytool | OK | keytool verified successfully. PreCheck => unzip | OK | unzip verified successfully. PreCheck => gzip | OK | gzip verified successfully. PreCheck => rsync | OK | rsync verified successfully. PreCheck => cut | OK | cut verified successfully. PreCheck => ip | OK | ip verified successfully. PreCheck => sed | OK | sed verified successfully. PreCheck => sha256sum | OK | sha256sum verified successfully. PreCheck => wc | OK | wc verified successfully. PreCheck => xargs | OK | xargs verified successfully. PreCheck => OS | WARN | Current OS may not be compatible. Current OS : Red Hat Enterprise Linux Server 7.9 (Maipo)PreCheck => Cron job | OK | Cron job verified successfully. PreCheck => Locale | OK | Verified Locale Successfully. PreCheck => CPU Cores | OK | No. of CPU Cores : 4 PreCheck => License | OK | License is valid till 28-May-2024 23:59:59 CST. Enter the password for Certificate : rootroot --输入认证密码，上面创建签名文件的签名密码。PreCheck => WebCert | WARN | Web Certificate is valid for 3649 day(s). Given certificate is self signed.PreCheck => Disk | OK | Available disk space : 41.44GB PreCheck => Ports | OK | Input ports verified successfully. PreCheck => Memory | OK | Available Memory : 14GB Enter DB Name: ugo --输入管理ugo的数据库Enter DB UserName: ugo --输入管理ugo的用户。Enter the password for DB User -> ugo: Ugo#1234 --输入自定义密码,需要稍微复杂的密码。Retype password: Ugo#1234 --输入自定义密码,需要稍微复杂的密码。Enter the password for IAM User -> admin: Ugo#1234 --输入自定义复杂密码,WEB控制台用户密码。Retype password: Ugo#1234 --输入自定义密码,需要稍微复杂的密码。WEB控制台用户密码Installation started. Cron => Python access check. | OK | Cron access check for Python started... Cron => Java access check. | OK | Cron access check for Java started... Create config DB => logrotate | OK | Config file created: /home/ugo/ugoserver/bin/script/mysql.confConfigure => DB | OK | Properties configured. Create config catalina => logrotate | OK | Config file created: /home/ugo/ugoserver/bin/script/catalina.confConfigure => WebUI | OK | Properties configured. Configure => UGO_AppMigration | OK | Properties configured. Configure => UGO_CharsetAssess | OK | Properties configured. Configure => UGO_DbObjectCollection | OK | Properties configured. Configure => UGO_Migration | OK | Properties configured. Configure => UGO_PreMigration | OK | Properties configured. Configure => UGO_Verification | OK | Properties configured. Configure => UGO_IAMService | OK | Properties configured. Start => DB | OK | Service started successfully. Test => DB connection | OK | DB connection tested successfully. Start => Create DataBase | OK | Database created successfully. Start => WebUI | OK | Service started successfully. Configure => IAM User Details | OK | IAM User details Configured. Start => UGO_IAMService | OK | Service started successfully. Cron => Verify Python access Check | OK | Python access Check for Cron Verified. Cron => Cleanup Python access check. | OK | Cleanup done for Python access check Cron. Cron => Verify Java access Check | OK | Java access Check for Cron Verified. Cron => Cleanup Java access check. | OK | Cleanup done for Java access check Cron. URL to access the Web: https://192.168.75.58:8090/ugo/ Note : Started WebUI Services in Backend. Please wait for sometime before accessing WebURL.Configured Application Migration Port: 9040Configured Charset Assess Port: 9040Configured DB Object Collection Port: 9040Configured Migration Port: 9040Configured Pre Migration Port: 9040Configured Verification Port: 9040Configured IAM Port: 9001Please refer installer log for more Information. Installed in below mentioned Directory:-----------------------------------------------------------------path: /home/ugo/ugoserver Total Execution time 0:04:07.577295Installation completed.5、解压UGO进程[ugo@oracle11gdg ~]$ ps -ef |grep ugougo 4815 1 0 10:34 ? 00:00:00 /bin/sh /home/ugo/ugoserver/db/server/bin/mysqld_safe --defaults-file=/home/ugo/ugoserver/db/server/my.cnfugo 5595 4815 4 10:34 ? 00:00:13 /home/ugo/ugoserver/db/server/bin/mysqld --defaults-file=/home/ugo/ugoserver/db/server/my.cnf --basedir=/home/ugo/ugoserver/db/server --datadir=/home/ugo/ugoserver/db/data --plugin-dir=/home/ugo/ugoserver/db/server/lib/plugin --log-error=/home/ugo/ugoserver/logs/db/repodb.err --pid-file=/home/ugo/ugoserver/db/server/mysqld.pid --socket=/home/ugo/ugoserver/db/server/mysql.sock --port=3306ugo 6068 1 99 10:35 ? 00:10:24 /home/ugo/jre1.8.0_392/bin/java -Dnop -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Dlog4j2.formatMsgNoLookups=true -Djava.security.properties=/home/ugo/ugoserver/web/conf/provider.security -Xms3072m -Xmx5120m -XX:+PrintGCDetails -XX:+PrintGCApplicationStoppedTime -XX:+PrintGCApplicationConcurrentTime -XX:+PrintGCDateStamps -Xloggc:/home/ugo/ugoserver/logs/services/gclog.log -XX:+UseGCLogFileRotation -XX:NumberOfGCLogFiles=10 -XX:GCLogFileSize=5m -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/home/ugo/ugoserver/logs/services/ -Djdk.tls.ephemeralDHKeySize=2048 -Djava.protocol.handler.pkgs=org.apache.catalina.webresources -Dorg.apache.catalina.security.SecurityListener.UMASK=0077 -Djava.net.preferIPv4Stack=false -Djava.net.preferIPv4Addresses -Dorg.apache.catalina.connector.RECYCLE_FACADES=true -Dorg.apache.catalina.STRICT_SERVLET_COMPLIANCE=true -Dorg.apache.tomcat.util.http.ServerCookie.FWD_SLASH_IS_SEPARATOR=false -Dorg.apache.tomcat.util.http.ServerCookie.STRICT_NAMING=false -Dignore.endorsed.dirs= -classpath /home/ugo/ugoserver/web/bin/bootstrap.jar:/home/ugo/ugoserver/web/bin/tomcat-juli.jar -Djava.security.manager -Djava.security.policy==/home/ugo/ugoserver/web/conf/catalina.policy -Dcatalina.base=/home/ugo/ugoserver/web -Dcatalina.home=/home/ugo/ugoserver/web -Djava.io.tmpdir=/home/ugo/ugoserver/web/temp org.apache.catalina.startup.Bootstrap startugo 6102 1 21 10:35 ? 00:00:45 java -Dlog4j2.formatMsgNoLookups=true -XX:+PrintGCDetails -Djava.net.preferIPv4Addresses -Djava.net.preferIPv4Stack=false -XX:+PrintGCApplicationStoppedTime -XX:+PrintGCApplicationConcurrentTime -XX:+PrintGCDateStamps -Xloggc:/home/ugo/ugoserver/logs/services/UGO_IAMService/gclog.log -XX:+UseGCLogFileRotation -XX:NumberOfGCLogFiles=10 -XX:GCLogFileSize=5m -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/home/ugo/ugoserver/logs/services/UGO_IAMService/ -Xms512m -Xmx1024m -DIAM_HOME=/home/ugo/ugoserver/services/UGO_IAMService -DLOGFILE_PATH=/home/ugo/ugoserver/logs/services/UGO_IAMService -cp /home/ugo/ugoserver/services/UGO_IAMService/../../web/shared/3rd_party_lib/*:/home/ugo/ugoserver/services/UGO_IAMService/../../web/shared/UGO_lib/*:/home/ugo/ugoserver/services/UGO_IAMService/lib/* com.huawei.gauss.dsc.iamservice.IAMServiceApplication*/--可以看到，UGO的后台进程，有一个MySQL的后台数据库。 --mysql 的目录假如环境变量。[ugo@oracle11gdg bin]$ cat ~/.bash_profile # .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then . ~/.bashrcfi# User specific environment and startup programsPATH=/home/ugo/ugoserver/db/server/bin:$PATH:$HOME/.local/bin:$HOME/binexport PATH source ~/.bash_profile 6、等了GUO元数据库 mysql -uugo -pUgo#1234 -S /home/ugo/ugoserver/db/server/mysql.sock -h127.0.0.1--查看UGO相关的数据库元数据库。 mysql -uugo -pUgo#1234 -S /home/ugo/ugoserver/db/server/mysql.sock -h127.0.0.1mysql> use ugo Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A showDatabase changedmysql> show tables; +-------------------------------------+| Tables_in_ugo |+-------------------------------------+| APPMIG_SOURCEDB_SUPPORT_INFO || APP_CODE_FILE || APP_COLLECTION_TYPE_SUPPORT_INFO || APP_DATA_PROCESSING_PROGRESS || APP_FILE_PARSING_PROGRESS || APP_FILE_PARSING_TASK_PROGRESS_INFO || APP_SOURCESQL_HISTORY || APP_SQL_INFO || APP_TYPE_SUPPORT_INFO || APP_UPLOAD_FILE_INFO || AUDIT_COLLECTION_DATABASE_OBJECTS || AUDIT_DATABASES || AUDIT_ERROR_FILES || AUDIT_PROJECT || AUDIT_RULES || AUDIT_SINGLE_SQL_HISTORY || AUDIT_SQL || AUDIT_SQL_DEPENDENCE || AUDIT_SQL_META_INFO || AUDIT_SQL_NO_MATCH_RULES || AUDIT_SQL_VIOLATION || AUDIT_TASK || AUDIT_TASK_CAPTURE || AUDIT_TASK_SQL || AUDIT_TEMPLATES || AUDIT_TEMPLATE_RULES || CHARSET_ASSESS_TABLE || CHARSET_CONFIG || CHARSET_CONVERT_RECORD || CHARSET_MAL_RECORD || CHARSET_PROJECT || CHARSET_TABLE_COLUMN_STAT || DBAPP_UNZIP_FILE_PROGRESS || DBTYPE_OBJTYPE_MAP || DBTYPE_SQLTYPE_MAP || DB_ALARM || DB_APPMIG_PROJECTS || DB_APPMIG_PROJECT_FILE_INFO || DB_APPMIG_PROJECT_TRAN_SQL_MAP || DB_APP_CPU_INFO || DB_APP_INCR_SQL_INFO || DB_APP_INCR_SQL_SNAPSHOTS_INFO || DB_APP_MEM_INFO || DB_APP_SQLOBJECT_TYPE || DB_APP_SQL_COMPLEX_INFO || DB_APP_SQL_INFO || DB_APP_SQL_INFO_STATS || DB_APP_SYS_INFO || DB_COLLECTION_CHECKPOINT_STATE || DB_COLLECTION_DIFF_DATA || DB_COLLECTION_SCHEMA || DB_COLLECTION_STATUS || DB_COLLECTION_VERIFICATION || DB_COLL_SRCDB_ROUTE_COUNTER || DB_COMMON_TASK || DB_CONNECTIONS || DB_DELTA_COLLECTION_INFO || DB_DELTA_COLLECTION_PROGRESS || DB_DEP_ORDER_PROGRESS || DB_DISTRIBUTED_COLUMN || DB_EVALUATION_PROJECTS || DB_EVAL_PROJECT_STATISTIC || DB_EVAL_PROJECT_STATISTIC_DETAILS || DB_EVA_DB2_TOKEN || DB_IAM_HISTORY || DB_IAM_OPERATION || DB_IAM_PERMISSION || DB_IAM_ROLE || DB_IAM_USER || DB_IAM_USER_ACCESS_CONTROL || DB_IAM_USER_LOGIN_ATTEMPTS || DB_IAM_USER_LOGIN_INFO || DB_IAM_USER_SESSION || DB_MAPPING_TB_MIGRATION || DB_MIGRATION || DB_MIGRATION_CONFIG || DB_MIGRATION_CONFIG_CATEGORY || DB_MIGRATION_DEFAULT_CONFIG || DB_MIGRATION_HISTORY || DB_MIGRATION_OBJECT_BULK_HISTORY || DB_MIGRATION_OBJECT_HISTORY || DB_MIGRATION_PROJECTS || DB_MIGRATION_SPLIT_STATEMENT || DB_MIGRATION_SQL_CONVERT_CONFIG || DB_MIGRATION_STATUS || DB_MIGRATION_TEMPLATE_CONFIG || DB_MIGRATION_TEMPLATE_CONFIG_MAP || DB_MIG_OBJ_UPLOAD_DETAIL || DB_MIG_OBJ_UPLOAD_TASK || DB_MIG_PROJECT_STATISTIC || DB_OBJCOMP_PROJECTS || DB_OFFLINE_PARSE_FILE_INFO || DB_OFFLINE_PROJECT_FILE_INFO || DB_PREMIG_OBJECTSTAT || DB_PREMIG_OBJECT_MATCH || DB_PREMIG_OBJTYPE_SUMMARY || DB_PREMIG_PROGRESS || DB_PREMIG_RISK_ANALYSIS || DB_PREMIG_RISK_CATEGORY || DB_PREMIG_SUBEVAL_MAPPING || DB_PREMIG_SUMMARY || DB_PREMIG_SYSTEMOBJ || DB_PREMIG_SYSTEMOBJ_COLUMNS || DB_PREMIG_TABLE_COMPLEXITY || DB_PRE_MIGRATION || DB_PRE_MIGRATION_DATABASE_ANALYSIS || DB_SCHEMA_COUNT_VERIFICATION || DB_SCHEMA_DELTA_COUNT_PROGRESS || DB_SQL_CONVERSION_STATISTIC || DB_SQL_SIZE || DB_SRC_CATALOG || DB_SRC_DEPENDENCY || DB_SRC_INDEX_DEPENDENCY || DB_SRC_MIGRATION_OBJ || DB_SRC_TABLE_STATS || DB_SRC_TRACE_INFO || DB_SUPPORT_INFO || DB_TYPES || DB_VERIFICATION_CLEANUP || DB_VERIFICATION_ERR_FREQUENCY || DB_VERIFICATION_HISTORY || DB_VERIFICATION_OBJECT_BULK_REQDATA || DB_VERIFICATION_ROLE_GRANT || DB_VERIFICATION_SNAPSHOT || DB_VERIFICATION_STATUS || DOWNLOAD_LOG_TASK || EVAL_PROJECT_INFO_MSG || EVAL_SYSTEM_ROLE_LIST || EVA_DELTACOL_OBJINFO || EVA_INFO_STATS || EVA_METRIC_DATA_CONFIGS || EVA_METRIC_HIS || EVA_METRIC_MIN_MAX || EVA_PACKAGE_OBJECT_TYPES || EVA_PRE_CHECK_ITEMS || EVA_SCHEMA_INFO || EVA_SKIPPED_OBJ || EVA_SQL_DATA || EXPORT_TASK || EXTRACT_SQL_TASK || MIGRATION_EVALUATION_PROJECT_MAP || MIGRATION_SKIP_OBJECTS || MIG_CACHE_STORE || MIG_OBJCOMP_COMPARE_RESULT || MIG_OBJCOMP_OBJLIST || MIG_OBJCOMP_OBJLIST_COUNT || MIG_OBJCOMP_OBJTYPELIST || MIG_OBJCOMP_TRACE_INFO || MIG_ONLINE_FILE_UPLOAD || MIG_ONLINE_FILE_UPLOAD_INFO || MIG_PRE_CHECK_PROGRESS || MIG_PRE_CHECK_RESULT || MIG_USERS_PASSWORD || OBJCOMP_OP_COUNT || OBJCOMP_PROJECT_MAP || OBJCOMP_SCHEMA_TYPE_LIST || OBJECT_NAME_MAPPING || OFFLINE_SOURCEDB_SUPPORT_INFO || REPO_DB_VERSION_INFO || SOURCE_DB_OBJECT_FILTER || SRC_SCHEMA_FILTER_LIST || SRC_SYSTEM_ROLE_LIST_V11 || SRC_TO_TGT_VERSION_MAP || TABLESPACE_DETAIL || TAG_INFO_STATS || UGO_ALARMRECORDS || UGO_ALARMS || UGO_AUDITRECORD || UGO_Configuration || UGO_PROJECT_CONFIG || UGO_SLI_SUMMARY || UGO_WEAK_PWD_DICT || USER_ROLES_COLLECTION || seqgen |+-------------------------------------+174 rows in set (0.01 sec)7、UGO web界面登录网络可联通的浏览器访问UGO WEB界面。https://192.168.75.58:8090/ugo/#/console/dashboardadmin/Ugo#1234 --安装时设置的用户密码。--可以登录控制台查看控制台的内容。目前只有单机UGO,无GaussDB,无法看到其他内容。8、UGO服务重启su - ugo cd /home/ugo/ugoserver/bin/python3 ugoserver.py restart----------------------------------------------------------------- Service Name | Operation Details----------------------------------------------------------------- DB | Service stopped successfully. WebUI | Service stopped successfully. UGO_IAMService | Service stopped successfully. DB | Service started successfully. WebUI | Service started successfully. UGO_IAMService | Service started successfully.-----------------------------------------------------------------Note : Started WebUI Services in Backend. Please wait for sometime before accessing WebURL.9、查看UGO服务状态su - ugo cd /home/ugo/ugoserver/bin/python3 ugoserver.py status----------------------------------------------------------------- Service Name | Status ----------------------------------------------------------------- DB | UP DB | Service listening on port : 3306 WebUI | UP License | License is valid till 28-May-2024 23:59:59 CST. PREMIGRATION - Validity | 2024-05-28 OBJMIGRATION - Validity | 2024-05-28 APPMIGRATION - Validity | 2024-05-28 OBJCOMPARISON - Validity | 2024-05-28 SQLCONV - Validity | 2024-05-28 WebUI | Service listening on port : 8090 UGO_AppMigration | Service listening on port : 9040 UGO_CharsetAssess | Service listening on port : 9040 UGO_DbObjectCollection | Service listening on port : 9040 UGO_Migration | Service listening on port : 9040 UGO_PreMigration | Service listening on port : 9040 UGO_Verification | Service listening on port : 9040 UGO_IAMService | UP UGO_IAMService | Service listening on port : 9001-----------------------------------------------------------------10、检查license状态python3 ugoserver.py status -s webui ----------------------------------------------------------------- Service Name | Status ----------------------------------------------------------------- WebUI | UP License | License is valid till 28-May-2024 23:59:59 CST. PREMIGRATION - Validity | 2024-05-28 OBJMIGRATION - Validity | 2024-05-28 APPMIGRATION - Validity | 2024-05-28 OBJCOMPARISON - Validity | 2024-05-28 SQLCONV - Validity | 2024-05-28 WebUI | Service listening on port : 8090 UGO_AppMigration | Service listening on port : 9040 UGO_CharsetAssess | Service listening on port : 9040 UGO_DbObjectCollection | Service listening on port : 9040 UGO_Migration | Service listening on port : 9040 UGO_PreMigration | Service listening on port : 9040 UGO_Verification | Service listening on port : 9040----------------------------------------------------------------- --执行以下命令，查看UGO服务的License证书状态:python3 ugoserver.py license-status ----------------------------------------------------------------- License | Status ----------------------------------------------------------------- Status | Valid Message | License is valid Validity | 28-May-2024 23:59:59 CST PREMIGRATION - Validity | 2024-05-28 OBJMIGRATION - Validity | 2024-05-28 APPMIGRATION - Validity | 2024-05-28 OBJCOMPARISON - Validity | 2024-05-28 SQLCONV - Validity | 2024-05-28----------------------------------------------------------------- 11、查看特定License证书文件中的License证书的状态：  su - root  cd /root/UGO24.1.T0229/UGO/ python3 install.py license-check --ip 192.168.75.58 --license  /root/UGO24.1.T0229/UGO/LICUGO_2_20240327RWY55L.xml   -----------------------------------------------------------------  License                        | Status     -----------------------------------------------------------------  Status                         | Valid       Message                        | License is valid  Validity                       | 28-May-2024 23:59:59 CST  PREMIGRATION - Validity        | 2024-05-28  OBJMIGRATION - Validity        | 2024-05-28  APPMIGRATION - Validity        | 2024-05-28  OBJCOMPARISON - Validity       | 2024-05-28  SQLCONV - Validity             | 2024-05-28 ----------------------------------------------------------------- 12.Ugo数据库root密码未知，修改数据库root用户的密码。--修改root用户密码。vi /home/ugo/ugoserver/db/server/my.cnfskip-grant-tables = FALSE --修改为:skip-grant-tables = TRUE --重启服务。su - ugo cd /home/ugo/ugoserver/binpython3 ugoserver.py restart --密码登录后修改密码。mysql -uroot -p -S /home/ugo/ugoserver/db/server/mysql.sockmysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> select version();+-----------+| version() |+-----------+| 8.0.35 |+-----------+1 row in set (0.00 sec)mysql> select user,host from mysql.user; +------------------+-----------+| user | host |+------------------+-----------+| ugo | 127.0.0.1 || ugo-root | 127.0.0.1 || ugo-root | ::1 || mysql.infoschema | localhost || mysql.session | localhost || mysql.sys | localhost || ugo-root | localhost |+------------------+-----------+ ALTER USER 'ugo-root'@'localhost' IDENTIFIED BY 'Ugo#1234';ALTER USER 'ugo-root'@'127.0.0.1' IDENTIFIED BY 'Ugo#1234';ALTER USER 'ugo-root'@'::1' IDENTIFIED BY 'Ugo#1234'; --创建一个UGO能够远程登陆的ROOT用户。create user 'ugo-root'@'%' identified by 'Ugo#1234';grant all privileges on *.* to 'ugo-root'@'%'; --修改参数文件设置skip-grant-tables = FALSE ，重启UGO;vi /home/ugo/ugoserver/db/server/my.cnfskip-grant-tables = FALSE su - ugo cd /home/ugo/ugoserver/binpython3 ugoserver.py restart --创建SOCKET软链接。ln -sv /home/ugo/ugoserver/db/server/mysql.sock /tmp/mysql.sock --登录UGO的root管理员账号即可查看和管理UGO;mysql -u'ugo-root' -pUgo#1234 mysql> show databases; +--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys || ugo |+--------------------+5 rows in set (0.00 sec) --登录UGO;--UGO的用户里面有127.0.0.1的名称。所以登录时需要带h参数。mysql -uugo -pUgo#1234 -h127.0.0.1mysql> show databases; +--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys || ugo |+--------------------+5 rows in set (0.00 sec) 13.如何修改 UGO 运行内存参数？cd /home/ugo/ugoserver/web/binvim setenv.sh-XmsXXXXm -XmxYYYYmpython3 ugoserver.py restart --当前默认值如下。cd /home/ugo/ugoserver/web/bincat setenv.sh#!/bin/bashumask 0077export LOGGING_HOME=/home/ugo/ugoserver/logs/servicesexport UGO_HOME=/home/ugo/ugoserverexport UGO_DB_DRIVER_LIB=/home/ugo/ugoserver/web/shared/UGO_DBDriver_Libexport JAVA_OPTS="$JAVA_OPTS -Dlog4j2.formatMsgNoLookups=true -Djava.security.properties=$UGO_HOME/web/conf/provider.security -Xms3072m -Xmx5120m -XX:+PrintGCDetails -XX:+PrintGCApplicationStoppedTime -XX:+PrintGCApplicationConcurrentTime -XX:+PrintGCDateStamps -Xloggc:$LOGGING_HOME/gclog.log -XX:+UseGCLogFileRotation -XX:NumberOfGCLogFiles=10 -XX:GCLogFileSize=5m -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=$LOGGING_HOME/"export CATALINA_OUT=/home/ugo/ugoserver/logs/web/catalina.outexport CATALINA_PID=/home/ugo/ugoserver/web/HwEoLrLlOd.tcatpid --修改这个值即可。-Xms3072m -Xmx5120m--更新 JVM 内存的规则：● 对于62 GB或更多RAM计算机：maxJVMMemory（或）Xmx● 对于32 GB RAM计算机：maxJVMMemory（或）Xmx● 对于16 GB RAM计算机：maxJVMMemory（或）Xmx● 对于大于16 GB的RAM计算机：maxJVMMemory（或）Xmx作者：薛双奇

1、检查节点状态[omm@gauss001 ~]$ gs_om -t status --detail[ CMServer State ] node node_ip instance state-----------------------------------------------------------------------------1 192.168.0.142 192.168.0.142 1 /data/cluster/data/cm/cm_server Primary [ Cluster State ] cluster_state : Normalredistributing : Nobalanced : Yescurrent_az : AZ_ALL [ Datanode State ] node node_ip instance state---------------------------------------------------------------------------------------1 192.168.0.142 192.168.0.142 6001 /data/cluster/data/dn/dn_6001 P Primary Normal2、节点状态查看 [omm@gauss001 dn_6001]$ gs_om -t status -h 192.168.0.142----------------------------------------------------------------------- cluster_state : Normalredistributing : Nobalanced : Yes-----------------------------------------------------------------------node : 1node_name : 192.168.0.142node : 1instance_id : 1node_ip : 192.168.0.142data_path : /data/cluster/data/cm/cm_servertype : CMServerinstance_state : Primarynode : 1instance_id : 6001node_ip : 192.168.0.142data_path : /data/cluster/data/dn/dn_6001type : Datanodeinstance_state : Primarystatic_connections : 0HA_state : Normalreason : Normalstandby_node : standby_data_path : standby_node : standby_data_path : standby_state : Unknownsender_sent_location : 0/0sender_write_location : 0/0sender_flush_location : 0/0sender_replay_location : 0/0receiver_received_location: 0/0receiver_write_location : 0/0receiver_flush_location : 0/0receiver_replay_location : 0/0sync_state : Unknownsecondary_state : Unknownsender_sent_location : 0/0sender_write_location : 0/0sender_flush_location : 0/0sender_replay_location : 0/0receiver_received_location: 0/0receiver_write_location : 0/0receiver_flush_location : 0/0receiver_replay_location : 0/0sync_state : Unknownnode : 1node_ip : 192.168.0.142type : Fenced UDFstate : Normal-----------------------------------------------------------------------node_state : Normal3、数据库状态cluster_state 数据库状态。•Normal：表示数据库可用，且数据有冗余备份。所有进程都在运行，主备关系正常。•Unavailable：表示数据库不可用。•Degraded：表示数据库可用，但存在故障的数据库节点、数据库主节点实例。4、实例角色•Normal：表示单主机实例。•Primary：表示实例为主实例。•Standby：表示实例为备实例。•Pending：表示该实例在仲裁阶段。•Unknown：表示实例状态未知。•Down：表示实例处于宕机状态。•Abnormal：表示节点处于异常状态。•Manually stopped：表示节点已经被手动停止。5、节点状态说明状态:字段含义Normal:表示节点启动正常。Need repair:当前节点需要修复。Starting:节点正在启动中。Wait promoting:节点正等待升级中。例如备机向主机发送升级请求后，正在等待主机回应时的状态。Promoting:备节点正在升级为主节点的状态Demoting:节点正在降级中，如主机正在降为备机中。Building:备机启动失败，需要重建。Catchup:备节点正在追赶主节点。Coredump:节点程序崩溃。Unknown:节点状态未知。

1、实例主备切换--将备节点正常切换为主节点。gs_ctl switchover -D /home/omm/cluster/dn1/ --主机故障时，可以在备机执行如下命令。强制将其切换为主节点。gs_ctl failover -D /home/omm/cluster/dn1/ 2、脑裂双主如何处理 --出现双主状态后，请按如下步骤恢复成正常的主备状态。否则可能会造成数据丢失。 1).执行以下命令查询数据库当前的实例状态。gs_om -t status --detail 若查询结果显示两个实例的状态都为Primary，这种状态为异常状态。 2).确定降为备机的节点，在节点上执行如下命令关闭服务。gs_ctl stop -D /home/omm/cluster/dn1/ 3).执行以下命令，以standby模式启动备节点。gs_ctl start -D /home/omm/cluster/dn1/ -M standby 4).查看数据库状态，确认实例状态恢复。gs_om -t status --detail

1、检查实例状态 [omm@gauss001 ~]$ cm_ctl query -Cv[ CMServer State ] node instance state---------------------------------1 192.168.0.142 1 Primary [ Cluster State ] cluster_state : Normalredistributing : Nobalanced : Yescurrent_az : AZ_ALL [ Datanode State ] node instance state---------------------------------------------1 192.168.0.142 6001 P Primary Normal2、查询数据库中锁信息  --2.查询数据库中的锁信息SELECT * FROM pg_locks;--查询等待锁的线程状态信息，获取持锁线程block_sessionid。SELECT * FROM pg_thread_wait_status WHERE wait_status = 'acquire lock'; node_name | db_name | thread_name | query_id | tid | sessionid | lwtid | psessionid | tlevel | smpid | wait_status | wait_event | locktag | lockmode | block_sessionid | global_sessionid -----------+---------+-------------+----------+-----+-----------+-------+------------+--------+-------+-------------+------------+---------+----------+-----------------+------------------(0 rows)--查找正在持锁的会话，然后使用pg_terminate_session命令结束此会话。gaussdb=# SELECT pid,sessionid,query FROM pg_stat_activity where sessionid=block_sessionid; gaussdb=# SELECT pg_terminate_session(pid,sessionid);3、活跃会话检查 --SQL语句长时间运行会占用大量系统资源，用户可以通过查看事件发生的时间，占用内存大小来了解现在数据库运行状态。--查询事件的时间--查询事件的线程启动时间、事务启动时间、SQL启动时间以及状态变更时间gaussdb=# SELECT backend_start,xact_start,query_start,state_change FROM pg_stat_activity;--查询当前服务器的会话计数信息SELECT count(*) FROM pg_stat_activity;--查询当前使用内存最多的会话信息。gaussdb=# SELECT sessid, sum(totalsize)/1024/1024 as "totalsize MB", count(1) as count FROM gs_session_memory_context GROUP BY sessid ORDER BY 2 DESC LIMIT 10;3、对象检查 --表、索引、分区、约束等是数据库的核心存储对象，其核心信息和对象维护是DBA重要的日常工作。--查看表的详细信息\d+ table_name --查询表统计信息SELECT * FROM pg_statistic;--查看索引的详细信息\d+ index_name--查询分区表信息SELECT * FROM pg_partition;5、收集统计信息 --使用ANALYZE语句收集数据库相关的统计信息。gaussdb=# ANALYZE;gaussdb=# ANALYZE table_name; 6.使用VACUUM语句可以回收空间并更新统计信息。  gaussdb=# VACUUM; gaussdb=# VACUUM table_name; --查询约束信息 gaussdb=# SELECT * FROM pg_constraint; 7.基本信息检查  --基本信息包括版本、组件、补丁集等信息，定期检查数据库信息并登记在案是数据库生命周期管理的重要内容之一。 --版本信息 gaussdb=# SELECT version(); 8.容量检查：表大小，数据库大小。 gaussdb=# SELECT pg_table_size('table_name');gaussdb=# SELECT pg_database_size('database_name');

1、执行如下命令查看操作系统的日志文件vim /var/log/messages关注其中近一个月出现的kernel、error、fatal等字样，根据系统报警信息进行处理。2.检查数据库运行日志•收集日志的主机网络通畅且未宕机，数据库安装用户互信正常。•日志收集工具依赖操作系统工具如gstack，如果未安装该工具，则提示错误后，跳过该收集项。3.收集某个时间段内的日志。gs_collector --begin-time="20240407 18:00" --end-time="20240408 08:00" -h 192.168.0.142gs_collector --begin-time="20240407 18:00" --end-time="20240408 08:00" [omm@gauss001 ~]$ gs_collector --begin-time="20240407 18:00" --end-time="20240408 08:00"Successfully parsed the configuration file.create Dir.Successfully create dir.do system check interval 0 : count 1Collecting OS information.Failed to collect OS information.do database check interval 0 : count 1Collecting catalog statistics.Failed collected catalog statistics.do log check interval 0 : count 1Collecting Log files.Successfully collected Log files.do Config check 0:1Collecting Config files.Successfully collected Config files.Collecting files.Successfully to collect files.All results are stored in /data/cluster/logs/gaussdb/omm/collector_20240408_093215.tar.gz.4、解压并查看日志[omm@gauss001 ~]$ tar xvf collector_20240408_093215.tar.gz collector_20240408_093215/collector_20240408_093215/Detail.logcollector_20240408_093215/Summary.logcollector_20240408_093215/192.168.0.142.tar.gz (1)Detail.log [omm@gauss001 collector_20240408_093215]$ more Detail.log -----------------------------------------------------------------------| || 192.168.0.142 - Collecting OS information - Failed || |-----------------------------------------------------------------------| SuccessfulTask | hostname ; ps ux ; free -m |-----------------------------------------------------------------------| FailedTask | iostat -xm 2 3 : | --没有安装sysstat软件包。-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| || 192.168.0.142 - Collecting catalog information - Failed || |--------------------------------------------------------------------------------------------------------------------| FailedTask | Collecting catalog information: [GAUSS-53503] : There is no coordinator inst in this host |------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| || 192.168.0.142 - Collecting pg_log information - Success || |----------------------------------------------------------------------------| SuccessfulTask | find log files; copy log files |--------------------------------------------------------------------------------------------------------------------------------------------------------| || 192.168.0.142 - Collecting Config information - Success || |----------------------------------------------------------------------------| SuccessfulTask | collect Config information |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| || 192.168.0.142 - Copy Collect Info Files - Success || |-------------------------------------------------------------------------------------------------------------------| SuccessfulTask | Check Dir; Copy Collect Info Files; Compress Collect Info Files; Send Collect Info Files |------------------------------------------------------------------------------------------------------------------- (2)Summary.log [omm@gauss001 collector_20240408_093215]$ more Summary.log --------------------------------------------------------------------------------| | | || TASK NAME | SUCCESS HOSTNAME | FAILED HOSTNAME || | | |--------------------------------------------------------------------------------|Collecting OS information-1 | | 192.168.0.142 |--------------------------------------------------------------------------------|Collecting catalog information-1 | | 192.168.0.142 |--------------------------------------------------------------------------------|Collecting pg_log information-1 | 192.168.0.142 | |--------------------------------------------------------------------------------|Collecting Config information-1 | 192.168.0.142 | |--------------------------------------------------------------------------------|Copy Collect Info Files-1 | 192.168.0.142 | |-------------------------------------------------------------------------------- [omm@gauss001 collector_20240408_093215]$ tar xvf 192.168.0.142.tar.gz 192.168.0.142/192.168.0.142/logfiles/192.168.0.142/logfiles/log_20240408_093216907859.tar.gz192.168.0.142/catalogfiles/192.168.0.142/systemfiles/192.168.0.142/systemfiles/OS_information_20240408_093215613953.txt192.168.0.142/systemfiles/database_system_info_20240408_093215643918.txt192.168.0.142/configfiles/192.168.0.142/configfiles/config_20240408_093217458939/192.168.0.142/configfiles/config_20240408_093217458939/dn_6001/192.168.0.142/configfiles/config_20240408_093217458939/dn_6001/pg_hba.conf192.168.0.142/configfiles/config_20240408_093217458939/dn_6001/pg_control192.168.0.142/configfiles/config_20240408_093217458939/dn_6001/gaussdb.state192.168.0.142/configfiles/config_20240408_093217458939/dn_6001/postgresql.conf192.168.0.142/configfiles/config_20240408_093217458939/dn_6001/pg_replslot/192.168.0.142/configfiles/config_20240408_093217458939/dn_6001/pg_ident.conf[omm@gauss001 collector_20240408_093215]$ cd 192.168.0.142[omm@gauss001 192.168.0.142]$ lltotal 16drwx------ 2 omm omm 4096 Apr 8 09:32 catalogfilesdrwx------ 3 omm omm 4096 Apr 8 09:32 configfilesdrwx------ 2 omm omm 4096 Apr 8 09:32 logfilesdrwx------ 2 omm omm 4096 Apr 8 09:32 systemfiles

1、加入主机名称 vim /tmp/mpphosts2、检查时间for ihost in `cat /tmp/mpphosts`; do ssh -n -q $ihost "hostname;date"; done > /tmp/sys_ctl-os1.log 注意:前提是多个节点之间要做免密，否则需要输入密码。时间肯定不一致。 --检查主备是否握手成功。ntpq -p

1、SQL语句查看连接数 gaussdb=> SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s; count ------- 40(1 row) 2、查看现有最大连接数 gaussdb=> SHOW max_connections; max_connections ----------------- 1024(1 row)3、异常处理 如果显示的连接数接近数据库的最大连接数max_connections，则需要考虑清理现有连接数或者增加新的连接数。1)执行如下SQL语句，查看state字段等于idle，且state_change字段长时间没有更新过的连接信息。 SELECT * FROM pg_stat_activity where state='idle' order by state_change; 2)释放空闲的连接数。 --查看每个连接，并与此连接的使用者确认是否可以断开连接，或执行如下SQL语句释放连接。--其中，pid为上一步查询中空闲连接所对应的pid字段值。gaussdb=# SELECT pg_terminate_backend(140390132872976); 3)增加最大连接数。gs_guc set [-Z datanode] -D /gaussdb/data/dbnode -c "max_connections= 800" 4)重启数据库服务使新的设置生效gs_om -t stop && gs_om -t start

为了保证数据库有效运行，数据库必须在插入/删除操作后，基于客户场景，定期做VACUUM FULL和ANALYZE，更新统计信息，以便获得更优的性能。1.相关概念使用VACUUM、VACUUM FULL和ANALYZE命令定期对每个表进行维护，主要有以下原因：•VACUUM FULL可回收已更新或已删除的数据所占据的磁盘空间，同时将小数据文件合并。•VACUUM对每个表维护了一个可视化映射来跟踪包含对别的活动事务可见的数组的页。一个普通的索引扫描首先通过可视化映射来获取对应的数组，来检查是否对当前事务可见。若无法获取，再通过堆数组抓取的方式来检查。因此更新表的可视化映射，可加速唯一索引扫描。•VACUUM可避免执行的事务数超过数据库阈值时，事务ID重叠造成的原有数据丢失。•ANALYZE可收集与数据库中表内容相关的统计信息。统计结果存储在系统表PG_STATISTIC中。查询优化器会使用这些统计数据，生成最有效的执行计划。2、使用VACUUM或VACUUM FULL命令，进行磁盘空间回收。VACUUM customer; 可以与数据库操作命令并行运行（执行期间，可正常使用的语句：SELECT、INSERT、UPDATE和DELETE。不可正常使用的语句：ALTER TABLE）。 --对表分区执行VACUUM操作。VACUUM customer_par PARTITION ( P1 ); VACUUM FULL customer;--执行VACUUM FULL customer时会阻塞涉及customer表的所有操作。3.使用ANALYZE语句更新统计信息。 ANALYZE customer; --使用ANALYZE VERBOSE语句更新统计信息，并输出表的相关信息。ANALYZE VERBOSE customer; --也可以同时执行VACUUM ANALYZE命令进行查询优化 VACUUM ANALYZE customer; 注意:VACUUM 和 ANALYZE 会导致I/O流量的大幅增加，这可能会影响其他活动会话的性能。因此，建议通过 vacuum_cost_delay 参数设置.4、删除表 DROP TABLE customer;5、维护建议 •定期对部分大表做VACUUM FULL，在性能下降后为全库做VACUUM FULL，目前暂定每月做一次VACUUM FULL。•定期对系统表做VACUUM FULL，主要是 PG_ATTRIBUTE。•启用系统自动清理线程（AUTOVACUUM）自动执行VACUUM和ANALYZE，回收被标识为删除状态的记录空间，并更新表的统计数据。

1、背景信息 数据库经过多次删除操作后，索引页面上的索引键将被删除，造成索引膨胀。例行重建索引，可有效地提高查询效率。 数据库支持的索引类型为B-tree索引，例行重建索引可有效地提高查询效率。•如果数据发生大量删除后，索引页面上的索引键将被删除，导致索引页面数量的减少，造成索引膨胀。重建索引可回收浪费的空间。•新建的索引中逻辑结构相邻的页面，通常在物理结构中也是相邻的，所以一个新建的索引比更新了多次的索引访问速度要快。2、重建索引 重建索引有以下两种方式：•先运行DROP INDEX语句删除索引，再运行CREATE INDEX语句创建索引。在删除索引过程中，会在父表上增加一个临时排他锁，阻止相关读写操作。在创建索引过程中，会锁住写操作但是不会锁住读操作，此时读操作只能使用顺序扫描。 •使用REINDEX语句重建索引。 ◾使用REINDEX TABLE语句重建索引，会在重建过程中增加排他锁，阻止相关读写操作。 ◾使用REINDEX INTERNAL TABLE语句重建desc表（包括列存表的cudesc表）的索引，会在重建过程中增加排他锁，阻止相关读写操作。3、操作步骤 1).删除索引。gaussdb=# DROP INDEX areaS_idx;2).创建索引。gaussdb=# CREATE INDEX areaS_idx ON areaS (area_id);3)使用REINDEX重建索引 ◾使用REINDEX TABLE语句重建索引。 gaussdb=# REINDEX TABLE areaS; ◾使用REINDEX INTERNAL TABLE重建desc表（包括列存表的cudesc表）的索引。 gaussdb=# REINDEX INTERNAL TABLE areaS;

使用jdbc连接时报错，有没有大哥能帮忙？

GaussDB数据库迁移方案介绍云数据库GaussDB提供了多种数据迁移方案，可满足从MySQL数据库、Oracle数据库、GaussDB数据库、PostgreSQL数据库、DB2 for LUW、RDS for SQL Server、Microsoft SQL Server数据库到云数据库GaussDB的迁移。数据迁移工具有DRS、DAS和gs_loader。推荐使用DRS，DRS可以快速解决多场景下，数据库之间的数据流通问题，操作便捷、简单，仅需分钟级就能搭建完成迁移任务。通过服务化迁移，免去了传统的DBA人力成本和硬件成本，帮助降低数据传输的成本。数据迁移命令有gs_dump和gs_dumpall、gs_restore和copy。使用DRS工具将数据导入GaussDB通过DRS实时同步功能，将数据从一个数据源拷贝到GaussDB，实现关键业务的数据实时流动。主要聚焦于表和数据的同步导入。使用DAS工具的导出、导入功能迁移数据sql/CSV文件格式 通过DAS的数据导出功能，获取完整的数据信息，再将数据从本地或者从OBS桶导入目标数据表。使用copy to/from命令导出导入数据CSV文件格式gsql工具提供了元命令\copy进行数据导入导出。\copy只适合小批量、格式良好的数据导入，不会对非法字符做预处理，也无容错能力，无法适用于含有异常数据的场景。JDBC中使用COPY命令导出导入数据其他文件或数据库使用Java语言开发应用程序时，通过调用JDBC驱动的CopyManager接口，从文件或其他数据库向GaussDB写入数据。使用gs_dump和gs_dumpall命令导出数据纯文本格式 自定义归档格式 目录归档格式 tar归档格式 gs_dump支持导出单个数据库或其内的对象，而gs_dumpall支持导出集群中所有数据库或各库的公共全局对象。通过导入工具将导出的元数据信息导入至需要的数据库，可以完成数据库信息的迁移。使用gs_restore命令导入数据sql/tmp/tar文件格式在数据库迁移场景下，支持使用gs_restore工具将事先使用gs_dump工具导出的文件格式，重新导入GaussDB，实现表定义、数据库对象定义等元数据的导入。导入数据主要包括以下内容：所有数据库对象定义。 单个数据库对象定义。 单个Schema定义。 单张表定义。使用gs_loader工具导入数据CSV文件格式对于copy to导出的文件，可以使用gs_loader工具进行数据导入。gs_loader将控制文件支持的语法转换为\COPY语法，然后利用已有的\COPY功能，做主要数据导入工作，同时gs_loader将\COPY结果记录到日志中。