导读：本文记录 GaussDB 1.0.1升级到1.0.2的全过程，也介绍GaussDB 1.0.2相关新功能说明。
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]$
[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.
[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.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
[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
安全方面增强
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.
SQL> SELECT CURRENT_LOCAL_SCN() FROM SYS_DUMMY;
CURRENT_LOCAL_SCN()
--------------------
6755116323168257
1 rows fetched.
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.
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.
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.
SQL> select RANK(2) WITHIN GROUP (ORDER BY a) as "rank" FROM roger.test;
rank
------------
2
1 rows fetched.
SQL> select to_bigint(12341) from sys_dummy;
TO_BIGINT(12341)
--------------------
12341
1 rows fetched.
SQL> select to_int(99999) from sys_dummy;
TO_INT(99999)
-------------
99999
1 rows fetched.
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
联系客服
