SQL> select * from v$version;
VERSION
----------------------------------------------------------------
GaussDB_100_1.0.1.SPC2.B003 Release 3ae9d6c
ZENGINE
3ae9d6c
3 rows fetched.
[roger@mysqldb GaussDB_T_1.0.2]$ python upgrade.py --help
upgrade.py is a utility to upgrade a Zengine server.
Usage:
python upgrade.py --help
python upgrade.py -?
python upgrade.py -t upgrade-type --package=path_to_package_file
--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]
[--GSDB_DATA=path_to_data_dir] [-f cmd_config_file]
python upgrade.py -t pretest --package=path_to_package_file
--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]
[--GSDB_DATA=path_to_data_dir] [-f cmd_config_file]
python upgrade.py -t precheck --package=path_to_package_file
--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]
[--GSDB_DATA=path_to_data_dir] [-f cmd_config_file]
python upgrade.py -t prepare --package=path_to_package_file
--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]
[--GSDB_DATA=path_to_data_dir] [-f cmd_config_file]
python upgrade.py -t replace --package=path_to_package_file
--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]
[--GSDB_DATA=path_to_data_dir] [-f cmd_config_file]
python upgrade.py -t start --package=path_to_package_file
--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]
[--GSDB_DATA=path_to_data_dir] [-f cmd_config_file]
python upgrade.py -t upgrade --package=path_to_package_file
--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]
[--GSDB_DATA=path_to_data_dir] [-f cmd_config_file]
python upgrade.py -t sync --package=path_to_package_file
--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]
[--GSDB_DATA=path_to_data_dir] [-f cmd_config_file]
python upgrade.py -t restart --package=path_to_package_file
--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]
[--GSDB_DATA=path_to_data_dir] [-f cmd_config_file]
python upgrade.py -t upgrade-view --package=path_to_package_file
--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]
[--GSDB_DATA=path_to_data_dir] [-f cmd_config_file]
python upgrade.py -t checkpoint --package=path_to_package_file
--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]
[--GSDB_DATA=path_to_data_dir] [-f cmd_config_file]
python upgrade.py -t dbcheck --package=path_to_package_file
--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]
[--GSDB_DATA=path_to_data_dir] [-f cmd_config_file]
python upgrade.py -t flush --package=path_to_package_file
--backupdir=path_to_backup [--GSDB_HOME=path_to_gsdb_home]
[--GSDB_DATA=path_to_data_dir] [-f cmd_config_file]
python upgrade.py -t rollback-check --backupdir=path_to_backup
[--GSDB_HOME=path_to_gsdb_home] [--GSDB_DATA=path_to_data_dir]
[-f cmd_config_file]
python upgrade.py -t rollback --backupdir=path_to_backup
[--GSDB_HOME=path_to_gsdb_home] [--GSDB_DATA=path_to_data_dir]
[-f cmd_config_file]
python upgrade.py -t rollback-clean --backupdir=path_to_backup
[--GSDB_HOME=path_to_gsdb_home] [--GSDB_DATA=path_to_data_dir]
[-f cmd_config_file]
python upgrade.py -s pre-check --config-file=CONFIG_FILE
[--upgrade-mode=ha|single] [--packtype=run|package]
[-f cmd_config_file]
python upgrade.py -s run --config-file=CONFIG_FILE
[--auto-rollback=true|false]
[--upgrade-mode=ha|single] [--packtype=run|package]
[-f cmd_config_file]
python upgrade.py -s cleanup --config-file=CONFIG_FILE
[--upgrade-mode=ha|single]
[--packtype=run|package] [-f cmd_config_file]
python upgrade.py -s rollback-check --config-file=CONFIG_FILE
[--upgrade-mode=ha|single]
[--packtype=run|package] [-f cmd_config_file]
python upgrade.py -s rollback --config-file=CONFIG_FILE
[--upgrade-mode=ha|single]
[--packtype=run|package] [-f cmd_config_file]
Common options:
--help show this help, then exit.
-? show this help, then exit.
-P input password.
-t input the function that need to be executed.
-s input the step for upgrade.
--package input the name of package containing the path.
--backupdir input the name of backup
folder containing the path.
--GSDB_HOME input the name of app floder containing the path.
--GSDB_DATA input the name of data floder containing the path.
--config-file input the name of node configure file,
the format is:
IP=pkg,app_path,backup_path,data1,data2,..
--auto-rollback if auto-rollback is false,
will not rollback when run step failed
--upgrade-mode if upgrade-mode is ha,
will upgrade all the nodes in the configure file
--packtype input upgrade package type,
value scope is [run, package]
-f input the config file
that provide 'parameter=value'.
-P, --package, --backupdir, --GSDB_HOME,
--GSDB_DATA can be configed by a file.
in the config file, interactive=True is
equal with specify '-P' parameter.
for example, the config file content:
GSDB_HOME=path_to_gsdb_home
GSDB_HOME=path_to_gsdb_home
backupdir=path_to_package_file
interactive=TRUE
---config_file.ini
[roger@mysqldb gauss_upgrade]$ cat config_file.ini
127.0.0.1=/opt/gauss/gauss_upgrade/GaussDB_T_1.0.2-DATABASE-REDHAT-64bit.tar.gz,/opt/gauss/gauss100,/tmp/gaussdb_backup,/opt/gauss/gaussdata
[roger@mysqldb gauss_upgrade]$
—执行升级检查
[roger@mysqldb GaussDB_T_1.0.2-DATABASE-REDHAT-64bit]$ python upgrade.py -s pre-check --config-file=/opt/gauss/gauss_upgrade/config_file.ini --upgrade-mode=single
Begin to precheck for single upgrade.
Old version: 1.0.1.SPC2.B003 New version: 1.0.2.B319.
Precheck for single upgrade finished.
Upgrade [pre-check] step successfully.
[roger@mysqldb GaussDB_T_1.0.2-DATABASE-REDHAT-64bit]$
–开始升级
[roger@mysqldb GaussDB_T_1.0.2-DATABASE-REDHAT-64bit]$ python upgrade.py -s run --config-file=/opt/gauss/gauss_upgrade/config_file.ini --upgrade-mode=single
Old version: 1.0.1.SPC2.B003 New version: 1.0.2.B319.
Precheck step for single upgrade.
Prepare step for single upgrade.
Replace step for single upgrade.
Begin distrubute key to other instances.
output:
attr:MOUNT
single=============output:
connected.
SQL>
VALUE
----------------------------------------------------------------
(/opt/gauss/gaussdata/protect/kmc_a.ksf, /opt/gauss/gaussdata/protect/kmc_b.ksf)
1 rows fetched.
single===========status:0
Start step for single upgrade.
Upgrade step for single upgrade.
Sync step for single upgrade.
Dbcheck step for single upgrade.
Flush step for single upgrade.
Run for single upgrade finished.
Upgrade [run] step successfully.
—手工启动数据库
[roger@mysqldb bin]$ python zctl.py -t start
Successfully started instance.
[roger@mysqldb bin]$
—check是否升级成功
[roger@mysqldb ~]$ zsql / as sysdba -q
connected.
SQL> select * from v$version;
VERSION
----------------------------------------------------------------
GaussDB_T_1.0.2.B319 Release de68b82
ZENGINE
2 rows fetched.
SQL>
–执行完毕后可以删除相关软件信息
[roger@mysqldb GaussDB_T_1.0.2-DATABASE-REDHAT-64bit]$ python upgrade.py -s cleanup --config-file=/opt/gauss/gauss_upgrade/config_file.ini --upgrade-mode=single
clean backup and tmp files.
Upgrade [cleanup] step successfully.
同时看了一下在该版本中新增的关于备份恢复方面的功能：
- 在1.0.2版本中backup命令新增了基于表空间的备份:
SQL> backup database copy of tablespace users format '/tmp/gaussdb_backup/backup_0302';
Succeed.
[roger@mysqldb gauss]$ ls -ltr /tmp/gaussdb_backup/backup_0302
total 215080
-rw------- 1 roger roger 10485760 Mar 2 21:05 ctrl_0_0.bak
-rw------- 1 roger roger 75055104 Mar 2 21:05 data_USERS_4_1.bak
-rw------- 1 roger roger 134209536 Mar 2 21:05 data_USERS_4_0.bak
-rw------- 1 roger roger 484352 Mar 2 21:05 arch_32_0.bak
-rw------- 1 roger roger 3512 Mar 2 21:05 backupset
- 新增针对archivelog的restore
3）新增基于数据文件的恢复（之前只能恢复全库）
[roger@mysqldb ~]$ zengine mount -D /opt/gauss/gaussdata &
[1] 10005
[roger@mysqldb ~]$ starting instance(mount)
instance started
[roger@mysqldb ~]$
[roger@mysqldb ~]$
[roger@mysqldb ~]$ zsql / as sysdba -q
connected.
SQL> restore filerecover fileid 4 from '/tmp/gaussdb_backup/backup_0302';
Succeed.
SQL> alter database open;
Succeed.
SQL> select id,file_name,status,HIGH_WATER_MARK from v$datafile;
ID FILE_NAME STATUS HIGH_WATER_MARK
------------ ---------------------------------------- -------------------- ---------------
0 /opt/gauss/gaussdata/system ONLINE 2778
1 /opt/gauss/gaussdata/temp1_01 ONLINE 2
2 /opt/gauss/gaussdata/temp1_02 ONLINE 1
3 /opt/gauss/gaussdata/undo ONLINE 66490
4 /opt/gauss/gaussdata/user1 ONLINE 25546
5 /opt/gauss/gaussdata/user2 ONLINE 1
6 /opt/gauss/gaussdata/user3 ONLINE 1
7 /opt/gauss/gaussdata/user4 ONLINE 1
8 /opt/gauss/gaussdata/user5 ONLINE 1
9 /opt/gauss/gaussdata/temp2_01 ONLINE 2
10 /opt/gauss/gaussdata/temp2_02 ONLINE 1
11 /opt/gauss/gaussdata/temp2_undo ONLINE 2
12 /opt/gauss/gaussdata/sysaux ONLINE 13798
+++++ 安全方面增强
新增加了个内置高级包：
dbms_redact 用于脱敏
dbms_rls 用户安全策略控制
++++ 逻辑备份增强
exp新增对于分区的支持：
SQL> exp -h;
The syntax of logic export is:
Format: EXP KEYWORD=value or KEYWORD=value1,value2,...,valueN;
Example: EXP TABLES=EMP,DEPT,MGR;
or EXP USERS=USER_A,USER_B;
or EXP DIST_RULES=RULE_1,RULE_2;
Keyword Description (Default)
---------------------------------------------------------------------------------------------------------------------------
USERS List of schema names. Specify a percent sign (%) to export all users.
TABLES List of table names. Specify a percent sign (%) to export all tables.
DIST_RULES List of distribute rule names. Specify a percent sign (%) to export all distribution rules. Supported only for sharding.
TABLESPACE_FILTER List of tablespace names, the data or objects in these tablespaces will be exported. Case-sensitive words enclosed by '`' or '"'.
FILE Output file (EXPDAT.DMP)
FILETYPE Output file type: (TXT), BIN
LOG Log file of screen output
COMPRESS Compress output file (0), only for FILETYPE=BIN, values is 0~9, litter for faster compress speed, 0 is not compressed.
CONTENT Specifies data to unload where the valid keyword, values are: (ALL), DATA_ONLY, and METADATA_ONLY.
QUERY Predicate clause used to export a subset of a table, eg. "where rownum <= 10"
SKIP_COMMENTS Do not add comments to dump file. (N)
FORCE Continue even if an SQL error occurs during a table dump. (N)
SKIP_ADD_DROP_TABLE Do not add a DROP TABLE statement before each CREATE TABLE statement. (N)
SKIP_TRIGGERS Do not dump triggers. (N)
QUOTE_NAMES Quote identifiers. (Y)
TABLESPACE Default transport all tablespaces except for system reserved. (N)
COMMIT_BATCH Batch commit rows, commit once if set 0. (1000)
INSERT_BATCH Batch insert rows. (1)
FEEDBACK Feedback row count, feedback once if set 0 (10000)
PARALLEL Table data export parallelism settings, range 2~16, The default value is 0
CONSISTENT Cross - table consistency(N)
CREATE_USER Export user definition(N),Used in conjunction with USERS.
ROLE Export user roles expect system preset roles (N),Used in conjunction with USERS.
GRANT Grant role and pemission to USER (N),Used in conjunction with USERS and ROLE.
WITH_CR_MODE Export tables and indexes with CR_MODE options (N)
ENCRYPT Export files will be encrypted.
REMAP_TABLES Table's name will remapped to another tablename.
PARTITIONS Export tables's data within the input partition.
++++++ 新增加的函数：
1) current_local_Scn
SQL> SELECT CURRENT_LOCAL_SCN() FROM SYS_DUMMY;
CURRENT_LOCAL_SCN()
--------------------
6755116323168257
1 rows fetched.
2)DBA_FBDR_2PC(从undo表空间中查询已完成的两阶段事务信息)
SQL> select * FROM TABLE(DBA_FBDR_2PC(6755116323168257,1)) ;
GLOBAL_TRAN_ID LOCAL_TRAN_ID TLOCK_LOBS TLOCK_LOBS_EXT FORMAT_ID BRANCH_ID OWNER PREPARE_SCN COMMIT_SCN
---------------------------------------------------------------- -------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------- ---------------------------------------------------------------- -------------------- -------------------- --------------------
0 rows fetched.
3）DBA_PAGE_CORRUPTION
这个函数功能非常强大和实用。
SQL> select * from table(dba_page_corruption('DATABASE'));
FILE_ID FILE_NAME INFO_TYPE EXAMINED_NUM SUCCEED_NUM CORRUPT_NUM PAGE_ID PAGE_TYPE MARKED_CHECKSUM CALC_CHECKSUM
------------ ---------------------------------------- ------------- ------------ ------------ ------------ ------------ ------------------ --------------- -------------
0 /opt/gauss/gaussdata/system FILE SUMMARY 2778 2778 0
3 /opt/gauss/gaussdata/undo FILE SUMMARY 66490 66490 0
4 /opt/gauss/gaussdata/user1 FILE SUMMARY 25546 25546 0
5 /opt/gauss/gaussdata/user2 FILE SUMMARY 1 1 0
6 /opt/gauss/gaussdata/user3 FILE SUMMARY 1 1 0
7 /opt/gauss/gaussdata/user4 FILE SUMMARY 1 1 0
8 /opt/gauss/gaussdata/user5 FILE SUMMARY 1 1 0
9 /opt/gauss/gaussdata/temp2_01 FILE SUMMARY 2 2 0
10 /opt/gauss/gaussdata/temp2_02 FILE SUMMARY 1 1 0
11 /opt/gauss/gaussdata/temp2_undo FILE SUMMARY 2 2 0
12 /opt/gauss/gaussdata/sysaux FILE SUMMARY 13798 13798 0
11 rows fetched.
SQL> select * from table(dba_page_corruption('TABLESPACE',3));
FILE_ID FILE_NAME INFO_TYPE EXAMINED_NUM SUCCEED_NUM CORRUPT_NUM PAGE_ID PAGE_TYPE MARKED_CHECKSUM CALC_CHECKSUM
------------ ---------------------------------------- ------------- ------------ ------------ ------------ ------------ ------------------ --------------- -------------
4 /opt/gauss/gaussdata/user1 FILE SUMMARY 25546 25546 0
5 /opt/gauss/gaussdata/user2 FILE SUMMARY 1 1 0
6 /opt/gauss/gaussdata/user3 FILE SUMMARY 1 1 0
7 /opt/gauss/gaussdata/user4 FILE SUMMARY 1 1 0
8 /opt/gauss/gaussdata/user5 FILE SUMMARY 1 1 0
5 rows fetched.
SQL> select * from table(dba_page_corruption('DATAFILE',3));
FILE_ID FILE_NAME INFO_TYPE EXAMINED_NUM SUCCEED_NUM CORRUPT_NUM PAGE_ID PAGE_TYPE MARKED_CHECKSUM CALC_CHECKSUM
------------ ---------------------------------------- ------------- ------------ ------------ ------------ ------------ ------------------ --------------- -------------
3 /opt/gauss/gaussdata/undo FILE SUMMARY 66490 66490 0
1 rows fetched.
SQL> select * from table(dba_page_corruption('PAGE',4,10));
FILE_ID FILE_NAME INFO_TYPE EXAMINED_NUM SUCCEED_NUM CORRUPT_NUM PAGE_ID PAGE_TYPE MARKED_CHECKSUM CALC_CHECKSUM
------------ ---------------------------------------- ------------- ------------ ------------ ------------ ------------ ------------------ --------------- -------------
4 /opt/gauss/gaussdata/user1 PAGE 1 1 0 10 btree_segment 36019 36019
1 rows fetched.
- LSCN2GSCN(将本地SCN转换为GTS SCN)
SQL> select current_local_Scn() from sys_dummy;
CURRENT_LOCAL_SCN()
--------------------
6758768140668929
1 rows fetched.
SQL> select LSCN2GSCN(6758768140668929) from sys_dummy;
LSCN2GSCN(6758768140668929)
---------------------------
158298313993801729
1 rows fetched.
-
PENDING_TRANS_SESSION(查询正在执行的两阶段事务信息)
-
rank(聚合、分析函数)
SQL> select RANK(2) WITHIN GROUP (ORDER BY a) as "rank" FROM roger.test;
rank
------------
2
1 rows fetched.
7）TO_BIGINT(将数据转换成BIGINT类型)
SQL> select to_bigint(12341) from sys_dummy;
TO_BIGINT(12341)
--------------------
12341
1 rows fetched.
8)TO_INT(将数据转换成INT类型)
SQL> select to_int(99999) from sys_dummy;
TO_INT(99999)
-------------
99999
1 rows fetched.
9)TRY_GET_SHARED_LOCK(为一个会话尝试获取一把锁名为name_expr的共享咨询锁)
++++SQL 操作 （支持交集查询)
SQL> conn roger/Roger007@127.0.0.1:1611
connected.
SQL> create table test_2 as select * from test limit 5;
Succeed.
SQL> select a from test intersect select a from test_2;
A
----------------------------------------
26.531219482421875
605.14545440673828125
645.55263519287109375
710.174560546875
757.1773529052734375
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
