使用Shell脚本对Oracle元数据进行动态版本控制

The general process of code movement to Higher environments

向更高环境迁移代码的一般过程

During any development process for any organization, the critical part is to move the objects from the development environment to higher environments using manually extracting the metadata and pushing it to version controlling system from which the other automated tools used to capture the source and place or commit on higher environments.

在任何组织的任何开发过程中，关键部分是使用手动提取的元数据并将其推送到版本控制系统中，从而将对象从开发环境移至更高的环境，其他自动化工具可从该版本控制系统捕获源，放置或提交在更高的环境中。

For any organization, it is very important that the developers should commit all the codes if changed into a version control system so that there will be no point where we lose the codes.

对于任何组织来说，如果将开发人员更改为版本控制系统，开发人员都应提交所有代码，这一点非常重要，这样我们就不会丢失任何代码。

The coding movement from lower to higher environments is performed by using tools like GIT/BitBucket or other version controlling tools.  

通过使用诸如GIT / BitBucket之类的工具或其他版本控制工具，可以实现从较低环境到较高环境的编码运动。

As of today, there is no automated process available to perform end to end solution without any manual intervention for the Oracle database. 

到目前为止，没有任何自动过程即可执行端到端解决方案，而无需对Oracle数据库进行任何手动干预。

What are the risks or Issues and why we see rework

有哪些风险或问题，为什么我们会看到返工

There is always a high risk if the developer forgets to check-in the code into a version control system. If we take an example of one developer developed an Oracle function and did not checked into GIT/BitBucket (Using this term as my organization is using GIT/Bitbucket for version controlling).

如果开发人员忘记将代码签入版本控制系统，则始终存在很高的风险。 如果我们以一个开发人员开发Oracle功能并且未检入GIT / BitBucket（使用该术语，因为我的组织正在使用GIT / Bitbucket进行版本控制）为例。

Now another developer comes and makes changes to the same objects and moved the code to GIT. So the code developed by the first developer is lost and there is no way to retrieve and we don’t have any track what changes were done by the first developer as the code is now overwritten.

现在，另一个开发人员来了并对相同的对象进行更改，并将代码移至GIT。 因此，第一个开发人员开发的代码丢失了，无法检索，并且由于第一个开发人员现在的代码已被覆盖，我们没有任何追踪。

If the developer missed to commit and now we are in the release cycle where we need to move the codes to the production instance.  In such a scenario, the objects which were supposed to be moved to production are missed or even if pick the latest one from version control system still we will have the old code because the new changes are not been checked-in into version controlling system.

如果开发人员错过了提交，而现在我们处于发布周期，则需要将代码移至生产实例。 在这种情况下，应该移动到生产环境的对象会丢失，或者即使从版本控制系统中选择了最新的对象，由于新更改尚未签入版本控制系统，因此我们仍将拥有旧代码。

The developers develop the objects and manually commits them on the GIT and prepare for the release, in any case, if the objects are missed then build preparation and release process gets impacted. Multiple teams across the globe need to make sure they commit everything they developed and pass the list of the object that needs to be a part of the release process. There is a potential risk of the object getting missed during the release process because the object might not be moved to version control. 

开发人员开发对象并在GIT上手动提交它们，并为发布做准备，无论如何，如果丢失了对象，则会影响构建准备和发布过程。 全球各地的多个团队需要确保他们完成开发的所有工作，并传递需要作为发布过程一部分的对象列表。 在发布过程中可能会丢失对象，因为该对象可能不会移至版本控制。

The Relationship of Governance, GIT/ BitBucket, and Oracle Database Metadata

治理，GIT / BitBucket和Oracle数据库元数据的关系

The above picture shows an overview of how we can do governance around code commit using a version controlling system. 

上图显示了如何使用版本控制系统围绕代码提交进行治理的概述。

Example of how the current code check-in process is done in most of the organizations

大多数组织如何完成当前代码检入过程的示例

1. The developers extract the metadata from the Oracle database and save them into.SQL files on their system. 
   开发人员从Oracle数据库中提取元数据并将其保存到系统上的.SQL文件中。
2. Taking the files and placing them into the local GIT folder where they already mapped the source location. 
   提取文件并将它们放入本地GIT文件夹中，在该文件夹中它们已经映射了源位置。
3. Once the codes are checked-in into version control,  the authorized person picks them and move them to high environments as a part of the release process.   
   将代码签入版本控制后，授权人员会选择它们并将它们移到较高的环境中，作为发布过程的一部分。
4. This process required all the developers to install the GIT tool locally so that they can extract the data into.SQL file and place it into the same location. This also required to map the right location of the version control so that the reflections would be properly displayed on the web for further processing.
   此过程要求所有开发人员在本地安装GIT工具，以便他们可以将数据提取到.SQL文件中并将其放置在同一位置。 还需要映射版本控制的正确位置，以便将反射正确显示在Web上以进行进一步处理。

An automated way to commit codes on SVN

在SVN上提交代码的自动化方法

1. The design is to make use of existing technologies and making sure to avoid any miss or slippage of metadata of objects missed during the release process.
   该设计将利用现有技术，并确保避免在发布过程中遗漏任何丢失或遗漏的对象元数据。
2. The utility will be placed on a different server or same or as per you define where it connects to the database and extract all the metadata like tables, procedures, functions, packages, synonyms and write it into <<object name.sql>> and place it on GIT under each object folders. Now if any object folders are not present and recently created the job dynamically create the folders and place the files to the folder on the ETL server, and finally all the changes will be committed on GIT which will be reflected on BITBUCKET.
   该实用程序将被放置在不同的服务器上，或者位于相同的服务器上，或者按照您定义的位置连接到数据库，并提取所有元数据，例如表，过程，函数，包，同义词，并将其写入<< object name.sql >>和将其放在每个对象文件夹下的GIT上。 现在，如果不存在任何对象文件夹并且最近创建了作业，则该作业将动态创建文件夹并将文件放置到ETL服务器上的文件夹中，最后所有更改都将提交到GIT上，并将反映在BITBUCKET上。
3. There is a configuration to limit the extraction process, where the options are to only define the list of the schema that need to be extracted and not all the system objects which are very specific to the Product/ project / specific to business use-case. This will be maintained under objects.sql file under the SQL folder.
   有一种限制提取过程的配置，其中的选项是仅定义需要提取的架构列表，而不是非常特定于产品/项目/特定于业务用例的所有系统对象。 这将在SQL文件夹下的objects.sql文件下维护。
4. This utility can be scheduled using any automated job control system like Autosys or even using Cron jobs or any scheduling tools available within the organization.
   
   
   可以使用任何自动化的作业控制系统（例如Autosys）来调度此实用程序，甚至可以使用Cron作业或组织内可用的任何调度工具来调度。
   
   

Once you find the Server where you want to place all the.SQL file and shell script in one folder or you can change the paths on the shell script. 

找到要在其中将所有.SQL文件和Shell脚本放置在一个文件夹中的服务器后，即可更改Shell脚本上的路径。

The configuration is to pass the schema name where you can add or remove for which you need to under the object.sql file under the SQL folder shared here.

配置是在您在此处共享SQL文件夹下的object.sql文件下传递需要添加或删除的架构名称。

  object_name
FROM
    all_objects
WHERE
    owner IN (
        'SCOTT'    ) -- place to add or remove the schema
    AND object_type IN (
        'TABLE',
        'VIEW', 

All the SQL files that are used to extract the metadata are inside the SQL folder, unzip and validate the schema name under the object.sql file. 

用于提取元数据的所有SQL文件都位于SQL文件夹内，解压缩并验证object.sql文件下的架构名称。

sqls.zip

sqls.zip

Below is the shell script (Give any name to the below script)that will be our main script that can be scheduled using the scheduler

下面是shell脚本（给以下脚本起任何名字），它将是我们的主脚本，可以使用调度程序进行调度

#!/bin/bash
#---------------------------------------------------
# Export your Oracle details as below
#---------------------------------------------------

export ORACLE_SID=TAILND09
export ORACLE_HOME=/apps/oracle/client/<<>>/home1  # your oracle home path
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$ORACLE_HOME/bin
export PATH=$ORACLE_HOME/bin:$PATH
export PATH=$PATH:/app/git/bin # your SVN home path where GIT need to be installed

#Change the below paths and files appropriately
SCRIPT_HOME=/home/Automation        # your script home path where you placed your files 
SQL_DIR=$SCRIPT_HOME/sqls
OBJECT_SQL=$SQL_DIR/objects.sql
FILE_DIR=/app/abc/db-schemas/myinstance   # your server path where you want to export the metadata for SVN commit

# change the username from scott to a privilaged schema where you can extract the data from all schemas any DBA account will work.
createObjectDdlFile(){
	filetext=""
	SQL_FN=$SQL_DIR/$1.sql
	if [ "$object_type" = "$1" ] && [ -f $SQL_FN ] && [ -s $SQL_FN ]
        then
                FILE_OT_DIR=$FILE_OWNER_DIR/${object_type}S
                FILE_NAME=$object_name.sql
                output1=`sqlplus -S scott@${ORACLE_SID}/tiger<<EOFILE  
                        @$SQL_FN $object_name $owner
EOFILE`

                while read extract_ddl
                do
                        filetext="$filetext"$'\n'"$extract_ddl"

                done <<< "$output1"
                echo "DDLs for $object_name is extracted to "$FILE_OT_DIR"/"$FILE_NAME" file"
				mkdir -p $FILE_OT_DIR
                echo "$filetext" > "$FILE_OT_DIR"/"$FILE_NAME"
                filetext=""
		else
			echo "$SQL_FN file does not exist or is empty."
        fi
}

#The git commands that will push the files to the mapping GIT 
gitCommitAllChanges() {
	DATE=`date '+%Y-%m-%d %H:%M:%S'`
	cd $FILE_DIR
        git pull origin HEAD
	git add -A .
	git commit -m "Auto Commiting DDL Extraction changes using script at $DATE"
	git push origin HEAD
}


#The Script Execution logic starts from here
output=`sqlplus -S scott@${ORACLE_SID}/tiger<<EOF
        @$OBJECT_SQL
EOF`

rec=1
while read owner object_type object_name
do
        echo "processing line $rec - $owner $object_type $object_name"
        FILE_OWNER_DIR=$FILE_DIR/$owner
        createObjectDdlFile "$object_type"

  let rec=rec+1
done <<< "$output"

gitCommitAllChanges 

Advantages of implementing this process 实施此过程的优势

The main advantage of this process is to avoid any manual process of extracting the metadata and committing. The agent will help capture the metadata from the Oracle database, based on the frequency of the job will place the data into a version control system. Where we will have the ability to governance on what/who/which objects are been modified without any manual intervention on SVN. 

此过程的主要优点是避免了提取元数据并提交的任何手动过程。 该代理将根据作业的频率来帮助从Oracle数据库捕获元数据，并将数据放入版本控制系统中。 我们将在何处管理哪些对象/对象/对象被修改，而无需对SVN 进行任何手动干预 。

Thank you for reading this article please feel free to leave me some feedback or to suggest any future topics. I'll be looking forward to hearing from you – Swadhin Ray  (Sloba)

感谢您阅读本文，请随时给我一些反馈或建议任何将来的主题。 我期待着您的回音-Swadhin Ray（Sloba）

For more information about me, please check out my Experts Exchange Profile page.

有关我的更多信息，请查看我的Experts Exchange个人资料页面。

翻译自: https://www.experts-exchange.com/articles/33942/Dynamic-Version-Controlling-of-Oracle-Metadata-using-shell-script.html