唐修
唐修_个人页
唐修
文章
4
问答
5
视频
0
个人介绍
ORACLE数据库专家，ADAM产品专家。多年政府、金融领域ORACLE、MySQL、PostgreSQL数据库运维、优化、迁移经验，现于阿里云ADAM产品团队专注企业数据库和应用高效、稳定上云产品研发设计，助力企业IT技术以最小的代价实现业务系统上云。
擅长的技术
-
发表了文章 2019-02-05
大年初一mark一下
新的一年将会下线个人ORACLE外文blog，精力有限，会全力在阿里云云栖社区分享，主要内容还是数据库相关，包括但不限于以下内容： ORACLE数据库性能分析 PostgreSQL数据库全栈支持 PPAS数据库全栈支持 专注ORACLE数据库和应用迁移至阿里云PPAS、PostgreSQL等数据库 C/C++、Python分享 产品经理转型心路历程，专注云数据库、传统数据库迁移产品解决方案设计 定一个小目标，19年完成100篇数据库相关blog分享，希望在19年和志同道合的伙伴们共同进步
-
发表了文章 2018-12-10
PostgreSQL 远程链接 Sever端配置
PostgreSQL Sever需要配置才可被用户远程链接 配置文件：pg_hba.conf 控制访问安全，管理客户端访问PostgreSQL server的访问权限。postgresql.conf 数据库参数文件，配置数据库相关参数。pg_ident.conf 客户端访问server通过ident模式，会使用pg_ident.conf文件，模拟操作系统用户访问。 PG查看配置文件的位置 postgres=# select name, setting from pg_settings where category = 'File Locations'; name | setting -------------------+--------------------------------- config_file | /opt/pgsql/data/postgresql.conf data_directory | /opt/pgsql/data external_pid_file | hba_file | /opt/pgsql/data/pg_hba.conf ident_file | /opt/pgsql/data/pg_ident.conf (5 rows) 配置server可远程访问 1.配置pg_hba.conf # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust host all all 0.0.0.0/0 md5 # IPv6 local connections: host all all ::1/128 trust md5使用密码登陆 2.配置监听postgresql.conf #listen_addresses = 'localhost' # what IP address(es) to listen on; listen_addresses = '*' # what IP address(es) to listen on; 3.为初始用户设定密码 alter user postgres with password 'ikdhfel'; 4.配置文件都配置好后重启pg server测试远程链接 [postgres@postgresql data]$ pg_ctl restart -D /opt/pgsql/data waiting for server to shut down.... done server stopped waiting for server to start....2018-12-10 16:40:29.828 CST [636] LOG: listening on IPv4 address "0.0.0.0", port 5432 2018-12-10 16:40:29.828 CST [636] LOG: listening on IPv6 address "::", port 5432 2018-12-10 16:40:29.833 CST [636] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2018-12-10 16:40:29.857 CST [637] LOG: database system was shut down at 2018-12-10 16:40:29 CST 2018-12-10 16:40:29.861 CST [636] LOG: database system is ready to accept connections done server started
-
发表了文章 2018-12-07
PostgreSQL 11源码安装
PostgreSQL 11源码安装 测试环境：RedHat 7.0 Linux 64bit PostgreSQL 11源码安装包下载地址link 系统环境准备 RedHat 7.0默认会安装相关系统包，还需要增加PostgreSQL编译时必须包，如下： gcc, bison, gcc-c++, readline, readline-devel, zlib, zlib-devel, perl, perl-devel 创建postgres用户，并创建安装目录 useradd Postgres mkdir -p /opt/pgsql/data chown -R postgres:postgres /opt/pgsql 安装PostgreSQL 解压、编译、安装 tar -zxvf postgresql-11.1.tar.gz cd postgresql-11.1 ./configure --prefix=/opt/pgsql make & make install 初始化数据库 su - postgres [postgres@postgresql bin]$cd /opt/pgsql/bin [postgres@postgresql bin]$ ./initdb -D /opt/pgsql/data/ The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /opt/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting dynamic shared memory implementation ... posix creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: ./pg_ctl -D /opt/pgsql/data/ -l logfile start [postgres@postgresql bin]$./pg_ctl -D /opt/pgsql/data -l logfile start waiting for server to start.... done server started #配置用户环境变量文件.bash.profile增加如下内容 export PATH=$PATH:/opt/pgsql/bin [postgres@postgresql ~]$ psql psql (11.1) Type "help" for help. postgres=# postgres=# postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} postgres=#
-
发表了文章 2017-10-23
EDB Postgres MTK 使用小记
EDB Postgres Sever Migration Toolkit 是PPAS自带的一款数据迁移工具。使用MTK可以方便的把目标数据库（ORACLE/MySQL/Sybase/MS SQL Sever）的数据对象和数据内容迁移到 PPAS or PostgreSQL 。 安装 MTK 软件 安装方式： 图像化 or command-line测试案例为 text mode [root@Opython mtk]# ./edb-migrationtoolkit-51.0.0-1-linux-x64.run Language Selection Please select the installation language [1] English - English [2] Japanese - 日本語 [3] Simplified Chinese - 简体中文 [4] Traditional Chinese - 繁体中文 [5] Korean - 한국어 Please choose an option [1] : ---------------------------------------------------------------------------- Welcome to the EDB Postgres Migration Toolkit Setup Wizard. ---------------------------------------------------------------------------- Please read the following License Agreement. You must accept the terms of this agreement before continuing with the installation. Press [Enter] to continue: Limited Use Software License Agreement Version 2.9 IMPORTANT - READ CAREFULLY ............................................... Please specify the directory where Migration Toolkit will be installed. Installation Directory [/opt/edb/mtk]: ---------------------------------------------------------------------------- Setup is now ready to begin installing Migration Toolkit on your computer. Do you want to continue? [Y/n]: Y ---------------------------------------------------------------------------- Please wait while Setup installs Migration Toolkit on your computer. Installing EDB Postgres Migration Toolkit 0% ______________ 50% ______________ 100% ######################################### ---------------------------------------------------------------------------- EnterpriseDB is the leading provider of value-added products and services for the Postgres community. Please visit our website at www.enterprisedb.com 安装 ORACLE client oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm 下载地址：http://www.oracle.com/technetwork/cn/database/features/instant-client/index-097480.html [root@Opython mtk]# rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm Preparing... ########################################### [100%] 1:oracle-instantclient11.########################################### [100%] [root@Opython mtk]# cp /usr/lib/oracle/11.2/client64/lib/ojdbc6.jar /usr/lib/jvm/jre-1.7.0/lib/ext/ 配置 toolkit.properties 配置文件（位置在 MTK 主目录 /etc 下） SRC_DB_URL=jdbc:oracle:thin:@1.2.3.4:1528:wprod11g #Ensure that the oracle instance client is avalible SRC_DB_USER=system SRC_DB_PASSWORD=oracle TARGET_DB_URL=jdbc:edb://5.6.7.8:5444/cww TARGET_DB_USER=pgmg TARGET_DB_PASSWORD=oracle 迁移测试 /opt/edb/mtk/bin/runMTK.sh -dropSchema true SCOTT Running EnterpriseDB Migration Toolkit (Build 51.0.0) ... Source database connectivity info... conn =jdbc:oracle:thin:@ user =system password=****** Target database connectivity info... conn =jdbc:edb:// user =pgmg password=****** Connecting with source Oracle database server... Connected to Oracle, version 'Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options' Connecting with target EDB Postgres database server... Connected to EnterpriseDB, version '9.3.1.3' Importing redwood schema SCOTT... Dropping Schema: scott Creating Schema...scott Creating Tables... Creating Table: BONUS Creating Table: DEPT Creating Table: EMP Creating Table: SALGRADE Created 4 tables. Loading Table Data in 8 MB batches... Loading Table: BONUS ... [BONUS] Table Data Load Summary: Total Time(s): 0.089 Total Rows: 0 Loading Table: DEPT ... [DEPT] Migrated 4 rows. [DEPT] Table Data Load Summary: Total Time(s): 0.051 Total Rows: 4 Loading Table: EMP ... [EMP] Migrated 14 rows. [EMP] Table Data Load Summary: Total Time(s): 0.066 Total Rows: 14 Loading Table: SALGRADE ... [SALGRADE] Migrated 5 rows. [SALGRADE] Table Data Load Summary: Total Time(s): 0.049 Total Rows: 5 Data Load Summary: Total Time (sec): 0.563 Total Rows: 23 Total Size(MB): 0.0 Creating Constraint: PK_DEPT Creating Constraint: PK_EMP Creating Constraint: FK_DEPTNO Schema SCOTT imported successfully. MTK-14003: One or more users couldn't be found in the source Oracle database. With -users mode, the user name should be in uppercase unless it is case-sensitive. One or more schema objects could not be imported during the migration process. Please review the migration output for more details. Migration logs have been saved to /root/.enterprisedb/migration-toolkit/logs ******************** Migration Summary ******************** Tables: 4 out of 4 Constraints: 3 out of 3 Total objects: 7 Successful count: 7 Failed count: 0 Invalid count: 0 ************************************************************* 数据检测 cww=# \d scott.* Table "scott.bonus" Column | Type | Modifiers --------+-----------------------+----------- ename | character varying(10) | job | character varying(9) | sal | numeric | comm | numeric | Table "scott.dept" Column | Type | Modifiers --------+-----------------------+----------- deptno | numeric(2,0) | not null dname | character varying(14) | loc | character varying(13) | Indexes: "pk_dept" PRIMARY KEY, btree (deptno) Referenced by: TABLE "scott.emp" CONSTRAINT "fk_deptno" FOREIGN KEY (deptno) REFERENCES scott.dept(deptno) Table "scott.emp" Column | Type | Modifiers ----------+-----------------------------+----------- empno | numeric(4,0) | not null ename | character varying(10) | job | character varying(9) | mgr | numeric(4,0) | hiredate | timestamp without time zone | sal | numeric(7,2) | comm | numeric(7,2) | deptno | numeric(2,0) | Indexes: "pk_emp" PRIMARY KEY, btree (empno) Foreign-key constraints: "fk_deptno" FOREIGN KEY (deptno) REFERENCES scott.dept(deptno) Index "scott.pk_dept" Column | Type | Definition --------+--------------+------------ deptno | numeric(2,0) | deptno primary key, btree, for table "scott.dept" Index "scott.pk_emp" Column | Type | Definition --------+--------------+------------ empno | numeric(4,0) | empno primary key, btree, for table "scott.emp" Table "scott.salgrade" Column | Type | Modifiers --------+---------+----------- grade | numeric | losal | numeric | hisal | numeric | EDB Migration Toolkit 支持数据对象类型 MTK tools常用参数： MTK 工具可根据需求使用参数灵活使用 -verbose [on|off] Display application log messages on standard output (default: on). -schemaOnly Import the schema object definitions only. -dataOnly Import the table data only. -sourcedbtype db_type The -sourcedbtype option specifies the source database type. -targetdbtype db_type The -targetdbtype option specifies the target database type. -allTables Import all tables. -tables LIST Import comma-separated list of tables. -constraints Import the table constraints. -indexes Import the table indexes. -triggers Import the table triggers. -allViews Import all Views. -views LIST Import comma-separated list of Views. -allProcs Import all stored procedures. -procs LIST Import comma-separated list of stored procedures. -allFuncs Import all functions. -funcs LIST Import comma-separated list of functions. -allPackages Import all packages. -packages LIST Import comma-separated list of packages. -allSequences Import all sequences. -sequences LIST Import comma-separated list of sequences. -targetSchema NAME Name of the target schema (default: target schema is named after source schema). -allDBLinks Import all Database Links. -allSynonyms It enables the migration of all public and private synonyms from an Oracle database to an Advanced Server database. If a synonym with the same name already exists in the target database, the existing synonym will be replaced with the migrated version. -allPublicSynonyms It enables the migration of all public synonyms from an Oracle database to an Advanced Server database. If a synonym with the same name already exists in the target database, the existing synonym will be replaced with the migrated version. -allPrivateSynonyms It enables the migration of all private synonyms from an Oracle database to an Advanced Server database. If a synonym with the same name already exists in the target database, the existing synonym will be replaced with the migrated version. -allQueues Import all queues from the source database. -queues LIST Import the selected queues from the source database. LIST is a comma-separated list of queue names. -skipUserSchemaCreation This option prevents implicit schema creation for a migrated Oracle user. -dropSchema [true|false] Drop the schema if it already exists in the target database (default: false). -truncLoad It disables any constraints on target table and truncates the data from the table before importing new data. This option can only be used with -dataOnly. -safeMode Transfer data in safe mode using plain SQL statements. -copyDelimiter Specify a single character to be used as delimiter in copy command when loading table data. Default is \t -batchSize Specify the Batch Size to be used by the bulk inserts. Valid values are 1-50000, default batch size is 1000, reduce if you run into Out of Memory exception -cpBatchSize Specify the Batch Size in MB, to be used in the Copy Command. Valid value is from 1 to 1024, default batch size is 8 MB. -fetchSize Specify fetch size in terms of number of rows should be fetched in result set at a time. This option can be used when tables contain millions of rows and you want to avoid out of memory errors. -filterProp The properties file that contains table where clause. -skipFKConst Skip migration of FK constraints. -skipCKConst Skip migration of Check constraints. -ignoreCheckConstFilter By default MTK does not migrate Check constraints and Default clauses from Sybase, use this option to turn off this filter. -fastCopy Bypass WAL logging to perform the COPY operation in an optimized way, default disabled. -customColTypeMapping LIST Use custom type mapping represented by a semi-colon separated list, where each entry is specified using COL_NAME_REG_EXPR=TYPE pair. e.g. .*ID=INTEGER -customColTypeMappingFile PROP_FILE The custom type mapping represented by a properties file, where each entry is specified using COL_NAME_REG_EXPR=TYPE pair. e.g. .*ID=INTEGER -offlineMigration [PATH] This performs offline migration and saves the DDL/DML scripts in files for a later execution. By default the script files will be saved under user home folder, if required follow -offlineMigration option with a custom path. -logDir LOG_PATH Specify a custom path to save the log file. By default, on Linux the logs will be saved under folder $HOME/.enterprisedb/migration-toolkit/logs. In case of Windows logs will be saved under folder %HOMEDRIVE%%HOMEPATH%\.enterprisedb\migration-toolkit\logs. -copyViaDBLinkOra This option can be used to copy data using dblink_ora COPY command. This option can only be used in Oracle to EnterpriseDB migration mode. -singleDataFile Use single SQL file for offline data storage for all tables. This option cannot be used in COPY format. -allUsers Import all users and roles from the source database. -users LIST Import the selected users/roles from the source database. LIST is a comma-separated list of user/role names e.g. -users MTK,SAMPLE -allProfiles Import all profiles from the source database. -profiles LIST Import the selected profiles from the source database. LIST is a comma-separated list of profile names e.g. -profiles USER_PROFILE,ADMIN_PROFILE -allRules Import all rules from the source database. -rules LIST Import the selected rules from the source database. LIST is a comma-separated list of rule names e.g. -rules high_sal_emp,low_sal_emp -allGroups Import all groups from the source database. -groups LIST Import the selected groups from the source database. LIST is a comma-separated list of group names e.g. -groups acct_emp,mkt_emp -allDomains Import all domain, enumeration and composite types from the source database. -domains LIST Import the selected domain, enumeration and composite types from the source database. LIST is a comma-separated list of domain names e.g. -domains d_email,d_dob, mood -objecttypes Import the user-defined object types. -replaceNullChar <CHAR> If null character is part of a column value, the data migration fails over JDBC protocol. This option can be used to replace null character with a user-specified character. -importPartitionAsTable [LIST] Use this option to import Oracle Partitioned table as a normal table in EnterpriseDB. To apply the rule on a selected set of tables, follow the option by a comma-separated list of table names. -enableConstBeforeDataLoad Use this option to re-enable constraints (and triggers) before data load. This is useful in the scenario when the migrated table is mapped to a partition table in EnterpriseDB. -checkFunctionBodies [true|false] When set to false, it disables validation of the function body during function creation, this is to avoid errors if function contains forward references. Applicable when target database is Postgres/EnterpriseDB, default is true. -retryCount VALUE Specify the number of re-attempts performed by MTK to migrate objects that failed due to cross-schema dependencies. The VALUE parameter should be greater than 0, default is 2. -analyze It invokes ANALYZE operation against a target Postgres or Postgres Plus Advanced Server database. The ANALYZE collects statistics for the migrated tables that are utilized for efficient query plans. -vacuumAnalyze It invokes VACUUM and ANALYZE operations against a target Postgres or Postgres Plus Advanced Server database. The VACUUM reclaims dead tuple storage whereas ANALYZE collects statistics for the migrated tables that are utilized for efficient query plans. -loaderCount VALUE Specify the number of jobs (threads) to perform data load in parallel. The VALUE parameter should be greater than 0, default is 1. -logFileSize VALUE It represents the maximum file size limit (in MB) before rotating to a new log file, defaults to 50MB. -logFileCount VALUE It represents the number of files to maintain in log file rotation history, defaults to 20. Specify a value of zero to disable log file rotation. -useOraCase It preserves the identifier case while migrating from Oracle, except for functions, procedures and packages unless identifier names are given in quotes. -logBadSQL It saves the DDL scripts for the objects that fail to migrate, in a .sql file in log folder. -targetDBVersion It represents the major.minor version of the target database. This option is applicable for offline migration mode and is used to validate certain migration options as per target db version [default is 9.6 for EnterpriseDB database] MTK 可用 ./runMTK.sh -help 查看更详细功能介绍
暂无更多信息
-
发表了文章
2019-02-05
大年初一mark一下
-
发表了文章
2018-12-10
PostgreSQL 远程链接 Sever端配置
-
发表了文章
2018-12-07
PostgreSQL 11源码安装
-
发表了文章
2017-10-23
EDB Postgres MTK 使用小记
滑动查看更多
-
回答了问题
2018-10-23
RDS性能监控之内存
ReRDS性能监控之内存内存稳定是因为数据库自己的内存缓存，将热数据存放在内存中，是数据库访问更快赞0 踩0 评论0 -
回答了问题
2018-02-13
Xshell连接不上服务器
ReXshell连接不上服务器楼主，这个IP 22 端口是通的，您可以打开xshell的日志， 看看具体原因赞0 踩0 评论0 -
回答了问题
2018-02-13
ecs迁移
Reecs迁移这个迁移具体是什么东西，有没有数据盘，如果没有数据盘，如果只是应用及的迁移直接SCP过去就可以赞0 踩0 评论0 -
回答了问题
2018-02-10
Ubuntu下安裝oracle
ReUbuntu下安裝oracle问题没有描述清楚，比如Ubuntu系统版本，没有安装成功报错或现象，使用的responsefile情况等，秒速清楚才方便排错赞0 踩0 评论0
滑动查看更多
暂无更多信息