The growth of blue-chasing DBAs (20): Why, build a library escort (two releases-practice using markdown editing)

Original works, from the "Blue Blog" blog, Welcome to reprint, please be sure to indicate the source, otherwise hold the copyright legal responsibility.

The blog:http://blog.csdn.net/huangyanlong/article/details/48776573 of Blue

Objective

    自接触oracle至今，愈是深入了解oracle愈是察觉到个人的渺小，时常感受到技术知识可以助推思维方式，一路走来，在汗水中收获着成长的充实，不仅局限于oracle技术，借由此系列文章，分享个人在追逐DBA道路上收获的些许感悟与成长的点滴记录。在浩瀚星空里，鉴证自己人生中那一道弧线。

Messy to find the rhythm of life, in the days of overtime, no unnecessary distractions.

                                                       ——深蓝很久没有更新文章了，在这段日子里，兼顾着原本的工作的基础上，开始逐步学习项目建设、项目管理的知识，学习着如何推进项目，如何建设团队，零零总总的知识，一时间感觉到24小时还真是如此短暂。在紧张的工作之余，意料之外的找到了一些生活的气息。还是比较心怀感恩的在一步一步前行着。曾几何时，有老大哥跟我说生活不只是工作而已，需要有梦想，同样需要有情感的支持，生活是个永恒的旋律，不能单方向前行，要不然索然无味，生活乏味会让人身心疲惫。想毕，当时是不以为然，而现如今的转变，很多事，已经可以放下，这才发现，原来不经意的一次尝试，可能真有什么命里注定，当不再驻足过往的风景时，人生中的岔路口上，会自然而然的铺开一条路，而顺路前行，心灵如同是敞开了一扇开阔而且光亮的窗。开始学着经营起一份感情，不刻意，不强求，随遇而安，岁月安好。

Chapter One: Return to life
When the opportunity to let two people meet, will no longer be remorse of the past, something, an unexpected one day, the future has a better vision.

                                                                                      ——深蓝钟摆嘀嗒嘀嗒，1:00，2:00，3:00,今夜有些莫名的孤单，不太想说话，注视着夜色的光亮顺着窗帘映射进酒店的房间内，眼望天色在天边处已渐渐探出一丝光亮。一种说不出的情绪，在静谧的夜空下却感觉出下雨的情绪。不经意间，已经转眼过了22天，仿佛只是过了两天的光景，意识中有些分不出是哪个周末发生的事，在某一个清晨开始了这段旅程，却在心里贮藏了一个有些朦胧的面庞。                                                                                    ——于郑州，9月12日，早。感情，是需要时间去了解彼此内心的，一个圆，左半部分和右半部分，有一点偏离了，那就不再是圆了。                                                      ——深蓝 于郑州2015年9月20日星期日 2:00 早安。当遇见，有些慌张来的不适时宜，又却恰当好处，不刻意，不掩饰，对于未来既然已坚定，对当前就应义无反顾。且行且珍惜的态度。夜幕下，突然醒了过来，睁着眼睛发呆，看着天花板，眼前开始闪现出不同的画面，一幕幕幻灯片的情节，仿佛就像是昨天发生的一样，一刹那感觉时间这东西，原来过得好快。回望在一个人的日子里，自由、无拘、无束、没什么不用管、没什么牵绊、享受着孤独、想走就走的日子里，让人反而觉得自在。这样的日子是不是需要改变一下了呢？静谧下，脑子里突然乱乱的。想好了嘛？自己在质疑。自己靠谱嘛？优容寡断的那种感觉？还要太认真嘛？一时间突然又仿佛陷入到曾经的过往里面。朦朦胧胧的有些飘离。                                                          ——深蓝 于郑州2015年9月21日星期一 05:00。

The future is fine
Some little childish, but the heart is very warm.

记不清上次收到类似礼物这样子的东西是什么时候了，有些意料之内，却被一些小幼稚触动着心底，狠狠的暖了一把。                                                          ——深蓝 于郑州2015年9月21日星期一 00:00。

Mid-Autumn Festival, calm in the spent

中午好像空调开的过猛，嗓子变得沙哑，不想说话，脑袋有些迷糊，爬在床上不想动堪。但，迷糊的，还是有些莫名的高兴。当看到蹦蹦哒的消息时心情就会好好的。哈，有些幼稚吧。出差在外的日子里，不联络与距离感有时候可能是一道屏障，但有时候似乎也是一种体验，也许在经历过了这样的日子里，才懂得身边的人，需要珍惜。一路花香，回望风景，人在其中，却未闻花香，而远了，才感受到，花香其实早在不经意间环绕在身旁。愿，安好。健康。豁达。                                                                ——深蓝于郑州2015年9月28日星期一 02:32

Chapter Two: Data migration
There is no beginning, the fresh Gram has the end. Do the responsibility to do, for the construction of the library escort, will not be discarded Oracle.

                                                                 ——深蓝本次完成一次数据迁移的任务，数据库字符集的调整：ZHS16GBK—>AL32UTF8

The character set used by the source library to build the library is ZHS16GBK, and the database character set of the target library is Al32utf8.

The difference between this migration, the new library and the old library is simply divided into the following situations:
The first part: The new library and the old library, the table-level structure does not change, the data volume is small (5G);
The second part: The new library and the old library, the table level structure does not change, the data quantity is large (1T);
The third part: The new library and the old library, the table level structure change, the data quantity is small (15G);

A table with a small amount of data is planned in the form of DMP. But one problem is dealing with Chinese characters. Under ZHS16GBK, a Chinese requires two bytes, while in Al32utf8, a Chinese requires three bytes. This also means that under the same table structure, the data under the Source library may appear as long as the word descriptor is inserted into the new library.
For this section, create the same tablespace, the user, and the appropriate permissions in the new library first.
Due to the small amount of data, before the migration, do a full library level import test, keep the Imp log, the log will be filtered out due to the length of the problem caused by Chinese;
The tables in the business that are not affected by the field length are then imported into the target library in the form of DMP to view the DMP logs;
(1), check the log, if "EXP-00003: Not found segment (0,0) storage Definition" error, need to manually supplement the table;
(2), check the log, if "ORA-01461: can only bind Long value to insert long" error, because the character length of more than 4000, you need to change the data type from VARCHAR2 to Clob;
(3), through the DMP log, see the need to expand the field length of the table information, such as:

According to the problem identified above, from the source side of the database to export the corresponding statement, modify the script for different issues, re-organize the supplementary table statements, and the need to expand the length of the table manually adjust the structure, manually created. Next, the target-side supplemental Build Table statement is executed. Then, manually extract the field length change table's business data. Finally, the source side, the target end of the table-level number of statistics, the same amount of data after the migration is confirmed to complete.
The second most of the migration, the table-level structure is unchanged, but the large data volume of the table, on the part of the partition to create data, in the form of partition DMP export data. Then import to the new library, after the final export is completed, statistics on the source side, the target side of the amount of data, confirm the export is complete. Since the large data scale data are all incrementally derived from different business libraries, the address of the data increment is adjusted after the subsequent migration is complete.
However, for the third part of the migration, it is not possible to use DMP in the way of structural changes, but instead of using the method of writing fields, the data migration task is accomplished by using insert.

Chapter Three: Dictionary conversion open the dusty Pandora, once firm Dream, the original road, still continue to move forward.
--Dark blue
In this construction work, not only need data migration, but also to complete the task of dictionary conversion. The reason is that the code for the Dictionary class is re-tuned and unified by the business unit, so the dictionary needs to be converted to the new standard.

This time the dictionary conversion involves two aspects, as follows:
The first aspect: the conversion of the landing data dictionary;
The second aspect: for the increment data, according to the new Standard dictionary transforms;

首先需要整理出一套新标准代码与老代码的对应关系，类似格式如下：

Table for dictionary translation:

Example: Table name: CODE_YEWU Business Platform Dictionary table

Simply enumerate a processing way, the idea is to use sub-query way, as follows:

Conversion example of landing data: Convert the YSDM field of the test table, where YSDM represents the color code and YSMC represents the color name.
Reference statements such as:
Update table test a set (A.YSDM,A.YSMC) = (

Select B.code,b.name

From Code_yewu b

where b.root_key= ' YSDM '

and B.OLD_CODE=A.YSDM

);

Example of conversion of new data:
When the new data is converted, it is inserted into the database by invoking the Dictionary conversion table (CODE_YEWU) when inserting the data to complete the conversion of the dictionary.
The reference statements are as follows:
INSERT into Test (ID,NAME,YSDM,YSMC)

Select

Sys_guid,

T.name

(Select A.code,

A.name

From Code_yewu A

where a.root_key= ' YSDM '

and A.OLD_CODE=T.YSDM)

From [email protected] t;

Chapter Four: Comparison of table structure differences between different business systems, sometimes there is a comparison of table structure differences, here is a way to use Oracle's outer joins to achieve the enumeration of two-party structural differences.

For example, a method:
Organize the table structure in the Source library in Excel;
Organize the table structure of the target library in Excel;

Create a table structure (in order to put the source library structure data)
CREATE TABLE A

(

T_name_a VARCHAR2 (500),

T_col VARCHAR2 (500),

T_type VARCHAR2 (500),

T_comment VARCHAR2 (500)

);

– Table name, field name, type, Chinese comment in Source Library

Create the structure of table B (in order to put the target library structure data)
CREATE TABLE B

(

T_name_a VARCHAR2 (500),

T_name_b VARCHAR2 (500),

T_col VARCHAR2 (500),

T_type VARCHAR2 (500),

T_comment VARCHAR2 (500)

);

– Table name in Source Library, table name in Target library, field name, type, Chinese comment
– For comparison purposes, the Source Library table name of table A is also built in table B, so there is a more intuitive comparison to prepare for the follow-up

Then import the Excel data into the appropriate table using a tool such as SQL loader or PL/developer.

Below are the fields that need to be spliced and compared from table A and B to generate intermediate tables A1, B1

CREATE TABLE A1 as

Select T_name_a,t_col,t_type,t_comment,t_name_a| | T_col LM

From A;

CREATE TABLE B1 as

Select T_name_a,t_name_b,t_col,t_type,t_comment,t_name_a| | T_col LM

From B;

Then, according to the content of A1 and B1, the difference statistic data is formed by using join.
CREATE TABLE BD

As

Select

A.T_NAME_A,A.T_COL,A.T_TYPE,A.T_COMMENT,A.LM,B.LM b_lm,b.t_name_a B_t_name_a,b.t_name_b,b.t_col B_t_col,b.t_type b _t_type,b.t_comment b_t_comment

From

(select T_name_a,t_col,t_type,t_comment,lm from A1) A

Full Join

(select T_name_a,t_name_f,t_col,t_type,t_comment,lm from B1) b

On A.LM=B.LM;

Supplement, you can use the All_tab_columns view to see the structure of the field under the specific table, but with this view stitching, the resulting number type length defaults to number (22), the resulting date type defaults to date (7), and requires two manual corrections.

Chapter Five: Eye project reading million volumes, thousands of miles, the face of boundless, Shen Calm air, the welcome to scale.
--Dark blue
In the days when you try to change your character, blue, feel the pressure you never had, and realize that the knowledge and skills you need to learn are not overnight. It takes a while to make yourself strong.

Follow-up plan, simple in groping:
(Plan 1)
Familiar with Times's "guiding task book": Want to be the helm of the team, we must first enrich their own knowledge;
Many times, repetition is the best teacher, the simple method of quantification, it will appear not simple.

(Plan 2)
Learning without thinking is blind, thinking and not learning is dangerous: project construction, technical thinking by the wind, management thinking waves;
Project managers are neither the implementers of technology nor the "Superman" who has to do everything themselves, and the whole project building is what should be done, instead of placing yourself in a handyman's position.

Do not let ease ruin themselves, do not let the timid vision of chaos, do the perseverance of practitioners, for the project into cavity blood.
--Dark blue

********************************* Blue's growth kee series _20150820*************************************

Original works, from the "Blue Blog" blog, Welcome to reprint, please be sure to indicate the source (Http://blog.csdn.net/huangyanlong).

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

The growth of blue-chasing DBAs (20): Why, build a library escort (two releases-practice using markdown editing)