Unix: List Files Which Do Not Have A Particular Pattern In Filename

Many a times we want to list files which do not have a particular pattern in its file name.
Here are a few things you can do (Remember not to use ls -l here):

ls |grep -v bz2 -- file name has bz2 anywhere in the file name
ls |grep -v bz2$ -- file name ends with bz2
ls |grep -v ^bz2 -- file name starts with bz2
ls |grep -v .bz2. -- file name has bz2 in the middle
ls |grep ..bz2 -- file name has atleast 2 chars before bz2
ls |grep '\.bz2$' -- file name ends with .bz2 (\ to escape)
ls |grep "\.bz2$" -- file name ends with .bz2 ("or ' might matter with the shell being used)
ls |grep b.2 -- file name can have bz2 or bx2
ls |grep '\.b.2' -- file name can have .bz2 or .bx2
ls |grep "\.b.2" -- file name can have .bz2 or .bx2

Debug Methods Oracle (Coarse Grained Auditing)

Scenario

There are many times, where there was an issue in production and you did not have any idea how it happened. The following article will give you some direction. Please note there are many other ways too. Currently, I will just focus on a method I had used during a similar situation.

This error information is from the latest logs from Production said:

ORA-00001: unique constraint (TABLE_ABCD.SYS_C006189415) violated

      Now the constraints to troubleshoot the above scenario are:

• You have limited access/privilege in production environment.
• The above was due to a duplicate record that came in and your process tried to insert that duplicate data.

But what was that data that caused disruption? The answer could have been found out by using FlashBack Query feature from Oracle. This however is version specific. Let’s look into some other aspects of debugging.

Observation

• There is no clue to the data that was being inserted at the time this exception was thrown.
• Not much information either from any of the relevant log files about the metadata.
• The respective process tables handle massive number of records which makes it further more   cumbersome to troubleshoot or pin point the culprit.
• No bulletin or logs from front end either.

Other Debug Options

 In this scenario we have a few options to explore and figure out the corrupt data:

• Putting ON our Application Debug option – Heavy On Performance & Resource (Not always helpful either)
• Get the trace log files inspected by the DBA (if at all the relevant data is set to be captured) – Complex, Heavy On Performance & Resource
• There are other DBMS packages like DBMS_ERRLOG, DBMS_MONITOR, DBMS_RLS  etc. – Little More Coding and better with live capture instead of historical
• Triggers to capture & hold the data. – Needs another table to be created to hold the bad data
• There are some other ways as well not mentioned for now

A Simpler Option (for database issues)

Auditing, at Statement Level.

There are other variations to this as well like Object level, Privilege level, Fine Grained Auditing to increase the granularity of data captured.

PREREQUISITES: The system parameters, AUDIT_SYS_OPERATIONS are set to TRUE & AUDIT_TRAIL is set to DB_EXTENDED (This setting required to capture the SQL_BIND data).  Check V$PARAMETER view. Since by default there will be a certain level of auditing enabled for any database, luckily these settings might pre-exist. In case it doesn’t, enhanced privileges will be required to modify them.

SWITCH ON AUDIT (FOR THE TABLE ONLY):   

AUDIT INSERT, UPDATE ON <OWNER>.<TABLE_NAME>                                                                                                                                          

BY ACCESS – Can be BY SESSION as well                                                                                                                                               

WHENEVER NOT SUCCESSFUL;

CHECK RESULTS: Query the USER_AUDIT_TRAIL table and check the fields, SQL_BIND (the data of interest), SQL_TEXT (the query which caused the failure), ACTION and RETURN CODE (0 for Success)

e.g. select * from USER_AUDIT_TRAIL where owner = 'OWNER_NAME' and OBJ_NAME= 'TABLE_NAME';

The bound data from SQL_BIND column will provide the solution:

               

SWITCH OFF AUDIT:  NOAUDIT INSERT, UPDATE

                                                                                 ON <OWNER>.<TABLE_NAME>

                                                                                  WHENEVER NOT SUCCESSFUL;

NOTE: The above is also an instance where we made use of a minimal elevated privilege.

Refer to the following links for more details:

http://www.oracle-base.com/articles/8i/auditing.php

http://www.oracle-base.com/articles/10g/auditing-10gr2.php

http://docs.oracle.com/cd/B10501_01/server.920/a96524/c25audit.htm

Oracle Version

Requirement is simple; you want to retrieve/recover the Version Information of your currently installed Oracle Database. Let us discuss most of the ways of doing so in Oracle & SQL Server.

Here are some of the ways to get it:

1) DBA_REGISTRY (Table) 

     SELECT * FROM DBA_REGISTRY;

SELECT comp_id, comp_name, version, status, namespace, control FROM DBA_REGISTRY WHERE comp_name LIKE '%Oracle%' ORDER BY comp_name;

This will give you the db version along with all the other oracle components installed.

Here is the result:

COMP_ID	COMP_NAME	VERSION	STATUS	NAMESPACE	CONTROL
CATALOG	Oracle Database Catalog Views	9.1.0.3.0	VALID	SERVER	SYS
CATPROC	Oracle Database Packages and Types	9.1.0.3.0	INVALID	SERVER	SYS

2)      V$VERSION (View)

SELECT * FROM V$VERSION

This is the result:

BANNER

Oracle Database 9G Enterprise Edition Release 9.1.0.3.0 - 64bi

PL/SQL Release 9.1.0.3.0 - Production

NLSRTL Version 9.1.0.3.0 - Production

3)      DBMS_DB_VERSION.VERSION (Procedure)

In case you are using SQL Assistant from Oracle you need the Server output to be set.

SET SERVEROUTPUT ON

EXEC DBMS_OUTPUT.PUT_LINE (DBMS_DB_VERSION.VERSION);

EXEC DBMS_OUTPUT.PUT_LINE (DBMS_DB_VERSION.RELEASE);

In case you are using 3rd Party Vendors for SQL Assistant as Benthic Golden Software you can have an output like this:

Press F10 or select View à DBMS OUTPUT Window

Then run the above code by F7. You will have the DBMS_OUTPUT.PUT_LINE generated output in the following window:

The result of the above query will be as:

4)      DBMS_UTILITY.DB_VERSION (Function)

DECLARE

mydb_version       varchar2 (200);

mydb_compatibility varchar2 (200);

BEGIN

dbms_utility.db_version( mydb_version, mydb_compatibility );

dbms_output.put_line( mydb_version );

dbms_output.put_line( mydb_compatibility );

END;

Here is the result using Benthic Golden Software.

 5)      DATABASE_PROPERTIES (Table)

SELECT * FROM database_properties WHERE property_name LIKE '%RDBMS%';

Here is the result:

PROPERTY_NAME	PROPERTY_VALUE	DESCRIPTION
NLS_RDBMS_VERSION	9.2.0.3.0	RDBMS version for NLS parameters

SQL Server Version Information

1)     

            1) SERVERPROPERTY()

SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

GO

The result will be something like this:

10.0.1600.1

CTP

Developer Edition

SERVERPROPERTY can be used to extract other data as well including COLLATION, BUILD etc.

Refer to the following link for more details:

http://msdn.microsoft.com/en-us/library/ms174396.aspx 

 2) @@VERSION

SELECT @@VERSION

The result will be something like this:

Microsoft SQL Server 2008 (SP1) - 10.0.2411.0 (X64)   Feb 21 2009

1:08:36   Copyright (c) 1988-2008 Microsoft Corporation Express

Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600)

Refer to the following link for more details:

http://support.microsoft.com/kb/321185/

Automate SAP R/3 Login with QTP

Test automation for SAP R/3 applications using QTP has become quite an interesting topic and in absolute demand by most companies implementing SAP these days worldwide. As a result, it has become necessary to have, in hand, a ready library of functions which assist to implement QTP scripting for SAP R/3 application quickly and easily.

In this section, we will discuss how to login to SAP R/3 application using QTP and Vbscript when the user credentials are provided through an external spreadsheet. The steps towards building such script would be;

1)       Close any existing excel process running on the system.

2)       Open the spreadsheet where user credentials have been provided and read the 

           credentials.

3)       Read the details like Server name, Client details and Language from the spreadsheet

4)       Login to SAP.

5)       Quit Excel process and close all open objects.

Following sections helps to understand the code used for each steps.

1)       Close any existing excel process running on the system.

The  SystemUtil.CloseProcessByName(“Excel.exe”) method can be used to do this. One needs to note that SystemUtil is a QTP reserved object and can be used only with QTP.

However if one needs to achieve this using vbscript, then the following code may be used. This block of code may also be used to close multiple processes.

myComputer = "."

‘Enter the list of processes in the array. These processes will be closed by the block of code.

myArrayOfTargetProcess = Array("EXCEL.EXE")

Set objWMIService = GetObject("winmgmts:" _

 & "{impersonationLevel=impersonate}!\\" &myComputer & "\root\cimv2")

Set CollectionOfProcesses = objWMIService.ExecQuery("SELECT * FROM Win32_Process")

For Each IndividualProcess in CollectionOfProcesses

 For Each TargetProcess In myArrayOfTargetProcess

                                If LCase(IndividualProcess.Name) = LCase(TargetProcess) Then

                                intReturn = IndividualProcess.Terminate

 End If

                  Next

Next

2)       Open the spreadsheet where user credentials have been provided and read the   

          credentials.  For this, use the following code block.

    PathForLoginSpreadSheet = <enter the path for login spread sheet>

       'Create an excel application                   

                       Set  myExcelForLogin = CreateObject( "Excel.Application")

                      ‘Open the file in the specified location                     

                      myExcelForLogin.WorkBooks.open(PathForLoginSpreadSheet)

                      Set ExcelWorkBook = myExcelForLogin.ActiveWorkbook

                      Set ExcelLoginSheet = ExcelWorkBook.Worksheets("Login")

3)       Read the details like Server name, Client details and Language from the spreadsheet

Let’s assume that the data is entered in the second row of the excel sheet. Data for “Server”, “Client”, “User”, “Password” and “Language” are in the 2^nd, 3^rd, 4^th, 5^th and 6^th column of the spreadsheet respectively.

This code is in continuation with the previous blocks.

With ExcelLoginSheet

               

                ‘Read the values for “Server”, “Client”, “User”, “Password” and “Language”

                SAPR3ServerName = .Cells(intRow,2)

                SAPR3Client = .Cells(intRow,3)

                SAPUserName=.Cells(intRow,4)

                SAPPassword=.Cells(intRow,5)

                UserLanguage = .Cells(intRow,6)

                               

End With                    

               

4)      Login to SAP.

Use the statement below:

SAPGuiUtil.AutoLogon SAPR3ServerName, SAPR3Client, SAPUserName, SAPPassword, UserLanguage

Please note that SAPGuiUtil.AutoLogon is a QTP reserved keyword.

5)      Quit all created objects.

    myExcelForLogin.Quit

                Set ExcelWorkBook =Nothing

    Set ExcelLoginSheet=Nothing

                Set myExcelForLogin =Nothing

Automatic Replenishment ~ Warehouse Management (WM) in SAP

This article is intended for SAP functional consultants to configure automatic replenishment of a particular storage type for transfer orders. Whenever the stock in a bin falls below the MIN value (predefined minimum quantity), it will trigger replenishment or transfer request (TR) which will in turn get converted into a transfer order, so that the workers in the warehouse can carry out the actual physical transfer of stock.

We have a provision of assigning a definite storage type from where the stock will be picked up for replenishment.  

Step 1:

Material Master has to be maintained with Warehouse Views.

Under Warehouse Mgmt 2 Storage bin, Maximum bin Quantity, Minimum bin Quantity and Replenishment Quantity has to be maintained.

Configuration Changes:

Step 2:

Movement type 320 is copied from existing movement type 319 under Logistics Execution ->Warehouse management->Activities->Transfers->Define Movement Types.

Movement type 320 is configured as the following and saved. We can define SRC for the Source storage bin so that no interim storage bin is taken into consideration.

Step 3:

Replenishment Control is defined for the storage type under Logistics Execution ->Warehouse management->Activities->Transfers-> Define Replenishment Control for Storage Type and saved.

Dedicated printers can be set up under Logistics Execution ->Warehouse management->Activities->Define Print Control for a particular storage type and saved

Step 4:

One job needs to be scheduled for each report. The report jobnames are randomly defined:

Report                 
~~~~~~~     

RLAUTA10          

RLLNACH1          
 .
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~