Just like all other code, DAC metadata are also regularly backed up to prevent any loss due to unexpected corruption. But the backup activity is as usual a manual overhead for the administrator to login to DAC client and export the metadata as needed.

In continuation to my other posts, the DAC metadata export can also be automated with the help of a shell script for Unix based systems and a batch file for the Windows. Thus the administrator can be relieved of some boring, repetitive work. All he or she must do is to make sure this schedule always works.

Conceptually this is similar to my earlier posts on Informatica and OBIEE backups: i.e.; it is composed of two parts.

i) A script that takes or exports the metadata from DAC tables

ii) Scheduling the script

It’s just one command, the command that invokes DAC server to export the metadata to a specified directory, that changes. And, you need not have a DAC server installed on your machine where you extract this metadata backup in order to get it automated. A DAC client would suffice the need. Here is how.

The Crux

The crux in this automation is that there is already a shell and a batch script that comes along with a DAC server/client installation, called automationUtils.sh and automationUtils.bat under “<DAC_HOME>/dac”. It is just that we need some more parameters to invoke this script file and that is where we create our custom script. i.e.; a custom script that calls the built-in automationUtils.sh or automationUtils.bat with some additional parameters.

These executable files, automationUtils.sh automationUtils.bat are capable of handling various activities of DAC, however that is not something I focus in this post but the “export” and “exportCategory” options. I shall come back to these two options in a short while. Before that, there is one parameter file, called automationUtils.properties under the same directory as of these script files. This file acts as a parameter to the script automationUtils.sh or .bat.

So, it is important to understand what this file is then, isn’t it? Well, it’s not a big file though. It simply contains a few connectivity information that you would generally use when you connect with a DAC Client like DAC Database details, the JDBC URL, the authentication file, etc. The below screenshot shows a sample automationUtils.properties file.

As can be seen, the first two parameters are JDBCDriver and JDBCUrl which you may already have handy. The JDBCUrl has to be of this format:

JDBCUrl=jdbc:oracle:thin:@<host>:<port>/<instance name>

The next is the DatabaseCredentialsFile which is most of the time your DAC authentication file; i.e.; the cwallet.sso file. You just have to give the path along with the file name.

When your Database Credentials authentication file is different from your DAC user login wallet file, you will have to set the path for DB credentials authentication file only, for this parameter. However, most of the time, DAC is set up to use a common cwallet.sso file.

The next parameter is UserCredentialsCWalletPath which is the user credentials cwallet.sso file.

Then, the AuthenticationType has to be set to ‘DAC’. [The other value is FMW which you may find in the file already]

The rest, you may leave them as such, undisturbed.

Now that we are familiar with the automationUtils and automation.properties, we proceed to write our own custom script to leverage these two files.

Now I come back to the two options, export and exportCategory which I mentioned a little earlier. These two are the commands that we use to export the metadata from the DAC metadata tables. The following is the syntax.

export

./automationUtils.sh automationutils.properties export <folder_name> <container_1> <container_2> ...

automationUtils.bat automationutils.properties export <folder_name> <container_1> <container_2> ...

While,

folder name - Full path to the location where you want the metadata to be exported

container_1, container 2 - Name of the source system container for which you want to export DAC metadata.

“container” is an optional parameter. So If no container is named, all containers that are found in the file structure will be exported.

exportCategory

./automationUtils.sh automationutils.properties exportCategory <folder_name> <category>

automationUtils.bat automationutils.properties exportCategory <folder_name> <category>

while,

folder name - Full path to the location where you want the metadata to be exported

category – It takes one or all of the three values: logical, runtime, system

logical - Exports all data categorized as logical (information contained in the DAC Design view).
runtime - Exports all data categorized as run time (information contained in the DAC Execute view).
system - Exports all data categorized as run time (information contained in the DAC Setup view).

The Unix Shell Script

You can write a shell script that invokes one of the above two options: export and exportCategory. But before that, you set a directory where you would like the backup files to be stored, the path where your DAC client or the server is installed.

A sample script is given below which serves as the first half in achieving this automation.

#!/bin/bash

#*******************************************************

#Set your required back up directory below#

#*******************************************************

backupDirectory="/oracle/Weekly_BKP/ETL_DAC_BKP/";

#*******************************************************

#--Logic to create dynamic name for backup by sysdate--#

#--This is to make the backup name unique for every day--#

#*******************************************************

backupDate=$(date +%F)

backupName=$backupDirectory"DEV_DAC_MetaData_"$backupDate;

#*******************************************************

#-----------------set your DAC directory---------------#

#*******************************************************

dacDir="/DAC11g/dac/";

#*******************************************************

#-----change directory to where the DAC utility is-----#

#*******************************************************

cd $dacDir;

#echo CD Done:$(pwd);

#*******************************************************

#Below command extracts DAC metadata for all containers#

#-------It extracts only logical and system data-------#

#You can add runtime by appending " runtime" at the end#

#-Or you may also remove logical or system as required-#

#*******************************************************

./automationUtils.sh automationutils.properties exportCategory $backupName logical system

#*******************************************************

#-------------------Backup Completed-------------------#

#*******************************************************

Batch file for Windows

A small batch file can be created to do exactly same as what was mentioned above. i.e.; setting the backup directory, setting the DAC client or server installed directory and finally invoking the automationUtils.bat file to export the metadata.

Here is a sample script to accomplish this.

@ECHO OFF
rem #*******************************************************
rem #------Set your required back up directory below-------#
rem #*******************************************************
SET backupDirectory=D:\\DAC_BKP\\DEV
rem #*******************************************************
rem #--Logic to create dynamic name for backup by sysdate--#
rem #*******************************************************
SET backupDate=%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%
SET backupName=%backupDirectory%\\DEV_DAC_MetaData_%backupDate%
rem mkdir %backupName%
rem #*******************************************************
rem #-----------Define your DAC installation path----------#
rem #*******************************************************
SET dacDir=D:\\DAC_Client\\dac
rem #*******************************************************
rem #----change directory to where the DAC utility is------#
rem #*******************************************************
D:
cd %dacDir%
rem #*******************************************************
rem #----------------Backup command execution--------------#
rem #*******************************************************
automationUtils.bat automationutils.properties exportCategory %backupName% logical system
rem #*******************************************************

Auto-Delete Old Backups

As the script will keep creating backups periodically, we would end up having too many backups eating the disk space extensively though we require only one or two latest archives. Again manual intervention is needed to clean them up retaining only the latest ones. To avoid this, you may simply add another line to your script for deleting the old backups based on the desired retention duration.

If you opted for Unix scripting, you may add the following line at the end of your shell file.

find <Backup Directory> -mtime +<days> -exec rm {} \;

This statement simply finds the files inside the <Backup Directory> whose last modified date is greater than the number of days specified by <days>. For example, if you want your backup to be retained only for 15 days, your script would look like:

find $backupDirectory -mtime +15 -exec rm {} \;

In case you take windows approach, you may add this statement.

forfiles -p "<backupDirectory>" -d -15 -c "cmd /c IF @isdir == TRUE rd /S /Q del @path"

Example:

forfiles -p "D:\DAC_BKP" -d -21 -c "cmd /c IF @isdir == TRUE rd /S /Q del @path"

For more information on ‘forfiles’, you may check here.

Scheduling the Backup Activity

Now that we are ready with the script for creating backups, the next step is to schedule them to trigger at the desired time and frequency. In Unix machines, you can make use of “crontab” to schedule your utility. On a Windows, just a basic task in the “task scheduler” should serve the purpose. The following sections show you how, in case it sounds new to you.

On UNIX based machines

After creating the script, it has to be scheduled in the Unix server where the backup you intend to automate.
Login to the Unix server as the user with which the backups are to be taken and open the terminal shell.
Type the command: crontab –e
In the editor that opens, set the desired time, frequency of backup and the backup script as shown below.
If you would like to create a backup every Friday at 09:30 AM and the script you created is under /DAC_Weekly_BKP, the crontab entry should be something like this-

30 09 * * 5 /DAC_Weekly_BKP/DAC-Metadata-Backup.sh

6. Save and close the crontab editor. [To save and quit, use Esc :wq!]

For further reading or understanding on crontab, you may check here.

On Windows

Go to Task Scheduler
Create a task or basic task
Set the trigger with the desired frequency of backup. For example, every Tuesday and Thursdays at 12:00 AM.
Set the action to start the created batch file [Dac-Metadata-Backup.bat]

That's it! Your DAC Metadata backup activity is now automated.

Below is a sample screen showing the same.

It is a common practice to take regular backup of Informatica repository contents, especially of Dev and Production instances so that any loss or corruption can be confronted later. But this backup activity has always been a manual headache for the administrator. If you are looking on how to get rid of the manual efforts and trying to automate the entire process, here is it. [Update 1: By process, it’s almost similar to my post on OBIEE web catalog backup which can be found here]

Strategy

The idea here is to create a shell/bash script (for Unix based) or a batch file (for Windows machines) in which you specify the path to store the backup repositories and also the username and password to connect to your Informatica domain. Eventually the script you create has to be scheduled to run as per your need. The following section would help you understand how.

The Linux/Unix Way

Write a shell script to connect to the Informatica repository service by specifying your repository name, username and password along with the domain. Let me show a sample.

#!/bin/bash

backupDirectory="/oracle/ETL_Weekly_BKP/";

backupDate=$(date +%F)

backupName=$backupDirectory"InfaRep-Dev-Bkp-"$backupDate".rep";

cd $backupDirectory;

pmrep connect -r DEV_RS -n Administrator -x Admin123 -d domain_infadev

pmrep backup -o $backupName –f

In the above snippet, you may notice the pmrep connect and pmrep backup commands which actually do the job for you.

Explanation to the attributes follows.

-r : Specifies that the repository name follows

-n : To specify the username

-x : To specify the password

-d : To specify your domain

-o : Output file name

The Windows way

If you are looking to automate this activity in windows, write a batch file instead of a shell as against the previous case with the same functionality. Here is a sample one.

@ECHO OFF

SET backupDirectory=D:\\ETL_Weekly_Backup

SET backupDate=%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%

SET backupName=Infa_Dev_Backup_%backupDate%.rep

cd %backupDirectory%

C:\Informatica\9.5.1\clients\PowerCenterClient\client\bin\pmrep connect -r DEV_RS -n Administrator -x Admin123 -d domain_infadev

C:\Informatica\9.5.1\clients\PowerCenterClient\client\bin\pmrep backup -o $backupName –f

[For description on the pmrep arguments, please refer to the Linux/Unix section]

Auto-Delete Old Backups

If you opted for Unix scripting, you may add the following line at the end of your shell file.

find <Backup Directory> -mtime +<days> -exec rm {} \;

find $backupDirectory -mtime +15 -exec rm {} \;

In case you take windows approach, you may add this statement.

forfiles -p <backup directory> -m *.rep /D -<days> /C "cmd /c del @file"

Example:

forfiles -p %backupDirectory% -m *.rep /D -15 /C "cmd /c del @file"

For more information on ‘forfiles’, you may check here.

Scheduling the Backup Process

With the script being ready for creating backups, the next step is to schedule and set trigger at the desired time and frequency. In Linux/Unix based machines, you can make use of “crontab” to schedule your utility. On a Windows, just a basic task in the “task scheduler” should serve the purpose. The following sections show you how, in case it sounds new to you.

On UNIX based machines

After creating the script, it has to be scheduled in the Unix server where the backup you intend to automate.
Login to the Unix server as the user with which the backups are to be taken and open the terminal shell.
Type the command: crontab –e
In the editor that opens, set the desired time, frequency of backup and the backup script as shown below.
If you would like to create a backup every Friday at 09:30 AM and the script you created is under /ETL_Weekly_BKP, the crontab entry should be something like this-

30 09 * * 5 /ETL_Weekly_BKP/InfaRepositoryBkp.sh

6. Save and close the crontab editor. [To save and quit, use Esc :wq!]

For further reading or understanding on crontab, you may check here.

On Windows

Go to Task Scheduler
Create a task or basic task
Set the trigger with the desired frequency of backup. for example, 30 July, 2013.
Set the action to start the created batch file [InfaRepositoryBkp.bat]

There it is! Your Informatica repository will backed up automatically.

Sample Screens

Windows Task Scheduler showing the scheduled trigger

Windows Task Scheduler with the batch file as the action

[M]adventures with Business Intelligence

Sunday 16 November 2014

How to Automate DAC Metadata Back up in Unix and Windows

The Crux

export

exportCategory

The Unix Shell Script

Batch file for Windows

Auto-Delete Old Backups

Scheduling the Backup Activity

On UNIX based machines

On Windows

Friday 25 July 2014

Automatic Informatica Repository Backup

Strategy

The Linux/Unix Way

The Windows way

Auto-Delete Old Backups

Scheduling the Backup Process

On UNIX based machines

On Windows

Sample Screens