Wednesday, September 18, 2019

Using TAR Utility

tar.gz file is a combination of a .tar file and a .gz file. It is an archive file with several other files inside it.

Create TAR.gz


Simple TAR file of a Specific location

    
    [oracle@server]$                tar -cvf file_name.tar.gz /home/d01/oracle

Create simple TAR file of a Specific Location & save on /home/oracle/backup/ folder


    [oracle@server]$                tar -cvf /home/oracle/backup/file_name.tar.gz /home/d01/oracle

  • c – creates a new archive
  • v – verbose, meaning it lists the files it includes
  • f – specifies the name of the file

Create compressed TAR file

    Add the -z option to create a compressed GNUZIP file

    [oracle@server]$    tar -cvzf file_name.tar.gz /home/d01/oracle
    [oracle@server]$    tar -cvzf /home/oracle/backup/file_name.tar.gz /home/d01/oracle


Extract TAR file

    You can unzip these files the same way you would unzip a regular zipped file:

Extract TAR file


    [oracle@server]$     tar -xvzf filename.tar

  • x – instructs tar to extract the files from the zipped file
  • v – to list out the files it’s extracting
  • z – instructs tar to decompress the files
  • f – tells tar the filename you want it to work on

Extract TAR file in specific /home/oracle/clone/db folder

    
    [oracle@server]$    tar –xvzf documents.tar.gz –C /home/oracle/clone/db/

Saturday, September 14, 2019

Standard Purge Programs in Oracle Applications

 Purge Concurrent Request and/or Manager Data Purges Concurrent

requests and/or Concurrent Manager data and log/output files

Purge Signon Audit data Purges all Signon Audit
information ( FND_LOGINS, FND_LOGIN_RESPONSIBILITIES,
FND_LOGIN_RESP_FORMS ) created before a given date
GL Archive and Purge Archive and Purge Program

Purge Consolidation Audit Data Purge Consolidation Audit

Mass Additions Purge Report Mass Additions Purge
Report

Purge accounting tables Purge accounting tables

AP/PO Purge Abort Routine AP/PO Purge Abort Routine

AP/PO Purge Deletion Routine AP/PO Purge Deletion
Routine

AP/PO Purge Confirmation Routine AP/PO Purge Confirmation
Routine

AP/PO Purge Initiation (Selection) Routine AP/PO Purge Initiation
(Selection) Routine

AP/PO Purge Summarization Routine AP/PO Purge Summarization
Routine

Purge Report Listings Purge Report Listings

Autoinvoice Purge Program Purge processed
transactions in the AutoInvoice Interface tables

Purge Purge

Archive and Purge Archive and Purge

Purge Module Purge Module

Purge ABC information Purge ABC information

Purge transaction history Purge transaction history

Purge cycle count information Purge cycle count
information

Purge replenishment counts Purge replenishment
counts

Purge physical inventory information Purge physical inventory
information

Transaction Purge Transaction Purge

Purge Standard Cost History Purge Standard Cost
History

Purge Cost Information Purge Cost Information

Purge Configurations Purge Configuration Items

Purge Standard Cost History from SRS Purge Standard Cost
History from SRS

Purge Standard Cost Update History Purge Standard Cost
Update History

Engineering Change Order Purge program Engineering Change Order
Purge Program

Purge Designator Purge Designator

CRP Purge Bill of Resources Purge Capacity Bill of
Resources

Purge Simulation Sets Purge Simulation Sets

MIX Batch Process Validate,Transfer or
Purge MIX batches

MIX Batch Process (Purge) Purges all records
related to a batch

Purge Sales Orders Purge Sales Orders

Purge Completed Deals Purge Completed Deals

Archive/Purge Bank Statements Program – Archive/Purge

Order Purge Selection Order Purge Selection

Order Purge Order Purge

Purge Margin Analysis Run Purge Margin Analysis Run

WIP Purge WIP Purge

Payables Open Interface Purge Payables Open Interface
Purge

Purge Demand Interface Data Purge Demand Interface
Data

GL Archive and Purge Child Program Archive and Purge Child
Program

Purge Purchasing Open Interface Processed Data Purge Purchasing Open
Interface Processed Data Program

New Archive and Purge New Archive and Purge

Call New Archive and Purge Call New Archive and
Purge

Purge Credit Usages Purge Credit Usages

Not able to connect to Apps user in Database due to library cache locks

If there are many applications integrated with EBS then this issue arises, when we change apps password but the integrated applications keep trying to connect with old password causes library cache locks in the Database.

This prevent apps user from connecting to database.This scenario also arises when EBS instance is refreshed.
Numerous failed logins attempts can cause row cache lock waits and/or library cache lock waits.

We can observe  'Library cache lock' or 'row cache lock' when concurrent users login with wrong password to the database.
'row cache lock' is seen in 10.2 and 11.1
'library cache lock' is seen in 11.2.

Solution

1. Check for bad or incorrect password or login attack by running following sql:

select username,
os_username,
userhost,
client_id,
trunc(timestamp),
count(*) failed_logins
from dba_audit_trail
where returncode=1017 and --1017 is invalid username/password
timestamp < sysdate -7
group by username,os_username,userhost, client_id,trunc(timestamp);

 
2. Set the below event in the spfile or init.ora file and restart the database:

alter system set event ="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1" scope=spfile;

or

EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1"

REF:Library Cache Locks Due to Invalid Login Attempts (Doc ID 1309738.1)

Script for Changing Oracle Application Users Password

 We can use the below API for changing the EBS(fnd_user) user password.

 
 apps.fnd_user_pkg.changepassword
 
Sample Script:
 
DECLARE
   v_user_name      VARCHAR2 (100) := 'HSINGH';

   v_new_password   VARCHAR2 (100) := :NEWPASSWORD;
   v_status         BOOLEAN := NULL;
BEGIN
   v_status := fnd_user_pkg.changepassword (v_user_name, v_new_password);

 
   COMMIT;
   DBMS_OUTPUT.put_line (
      'Password is changed successfully for the user=> ' || v_user_name);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
            'Error encountered while restting password for user and the Error Detail is '
         || SQLERRM);
END;

 
The script can also be modified to reset bulk ebs user passworda. The below is a sample script, You need to modify as per you need.
 
Refer below syntax
 
DECLARE
   v_status   BOOLEAN;
   CURSOR user_name_list
   IS
      SELECT user_name
        FROM fnd_user
       WHERE     NVL (end_date, SYSDATE + 1) > SYSDATE
             AND user_name NOT IN ('SYSADMIN',
                                   'GUEST',
                                   'XML_USER',
                                   'APPSMGR',
                                   'PORTAL30',
                                   'PORTAL30_SSO');
BEGIN
   FOR user_name_list IN c_user
   LOOP
      BEGIN
         v_status :=
            fnd_user_pkg.ChangePassword (
               username      => user_name_list.user_name,
               newpassword   => 'welcome123');
      --  dbms_output.put_line('password successfully changed for' || user_name_list.user_name);

      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
                  'Error encountered while resetting password for users and the Error Detail is '
               || SQLERRM);
      END;
   END LOOP;
END;

Some Useful UNIX Commands

 1.Deleting files older than N number of days ? 

find . -name '*.trc' -mtime +[N in days]  -exec rm {} \;  
Command will delete files older then N days in that directory

2.Copying file keeping timestamp preserve

cp -ip file.txt file_txt_bkp

3.Listing files modified in last N days

find . -mtime -<ndays> -exec ls -lt {} \;

4.Sorting files based on Size of file ?

ls -l | sort -nrk 5  | more
du -sm *|sort -nr|head -10


5.Finding CPU & Memory detail 

->cat /proc/cpuinfo  (CPU)
->cat /proc/meminfo (Memory)
->topas


6.Finding if any service is listening on particular port or not ?

netstat -an | grep {port no} 

7.Finding Process ID (PID) associated with any port ?

lsof | grep {port no.} 
lsof should be installed and in path.Many times it will be installed eith root user.Make sure you have that permission.

9.Finding a pattern in some file in a directory ?

grep pattern  file_name  ( find pattern in particular file )
grep -i pattern  file_name (find pattern ignoring the case)

10.Create symbolic link to a file ?

ln -s  pointing_to_original_file  symbolic_link_name

example
 ln -s /home/text.txt test.txt

11.History of command executed in the unix box

fc -l
fc -e - ls ( Would execute the last ls command.) 

History command will also do the same
history

12. Compressing a bigfile

zip -r new.zip new
or
compress file_name

13.Creating a tar file

tar -cvwf file.tar myfile.txt

14.Extracting the files from a tar file

tar -xvwf myfile.tar(System would unarchive (untar) the myfile.tar file into the current directory.)

tar -xvwzf myfile.tar.gz(System would unarchive (untar) the myfile.tar.gz file in the current directory.)

14.Extracting a gz file?

Use guzip command as follows:
gunzip file.gz
OR
gzip -d file.gz

15.Extract a tar.gz or .tgz file?

Files with extension tar.gz or .tgz are tar files compressed with gzip. On Unix system extract them with following command:
gunzip < file.tar.gz | tar xvf -
gunzip < file.tgz | tar xvf -

If you have GNU tar (Linux system) you can use the z option directly:
tar xvzf file.tar.gz
tar xvzf file.tgz

 
16.Using mailx command

echo "This is going to be body of the mail" |mailx -s "Subject:Testing" "false@gmail.com"


17.Checking the folders size & used space

df -h

Saturday, October 14, 2017

Few scripts for Your Oracle Database …..

 SQL> select sum(bytes)/1024/1024/1024 “SIZE_GB_TOTAL” from dba_data_files;

SIZE_GB_TOTAL

2671.33588

SQL> select sum(bytes)/1024/1024/1024 “SIZE_GB_ACTUAL” from dba_segments;

2084.94

SQL> select sum(bytes)/1024/1024/1024 “SIZE_GB_FREE” from dba_free_space;

SIZE_GB_FREE

585.972672


Note :-

dba_free_space:- describes the free extents in all tablespaces in the database.

dba_segments :- describes the storage allocated for all segments in the database.

dba_data_files :- describes d

Thursday, September 14, 2017

Install and Configure Hyper-V on Windows 10

 Hyper-V is a built-in part of Windows Server 2008 and later. However, the Hyper-V role is not active by default, therefore you need to enable it manually. The process of installing the Hyper-V role is extremely easy and intuitive. There are three ways through which you can enable Hyper-V in your Windows system: Windows System Settings, PowerShell Command Line Interface (CLI), or Deployment Image Servicing and Management (DISM). The process of installing Hyper-V using Windows Control Panel, which is the most popular approach, will be demonstrated below.

  1. Type Settings in the search box situated on the taskbar below and press ENTER. The Settings app will open.
  2. Click the Apps icon.
  3. Select Programs and Features on the right under the Related Settings section.
  4. Select Turn Windows Features on or off on the left.
  5. In the Windows Features dialog box, select Hyper-V and click OK.

‘Windows Features’ Dialog Box

After the process of installing Hyper-V is complete, click Restart Now to implement all the required changes or click Don’t Restart to postpone the restart of your computer.

Creating a Virtual Switch for Hyper-V Virtual Machines

After installing Hyper-V on your computer, you may want to create new VMs in order to start building virtual environment from scratch. However, prior to that, you should first consider creating a virtual switch, which can be used to ensure communication between VMs. Moreover, a virtual switch enables the connection of VMs to both physical and virtual networks. Also, virtual switches can be used for migrating VMs from one physical host to another. For this purpose, ensure that the source host and the target host have virtual switches with the matching names.

You can create a virtual switch by using Hyper-V Manager, which can be done in the following way:

1. Open Hyper-V Manager, select the Hyper-V host computer name.

2. Select the Action option above. In the drop-down menu, find Virtual Switch Manager and click on it.

Hyper-V Manager

3. Virtual Switch Manager will open, where you can choose the type of virtual switch that you need. There are three types of virtual switches available:

  • External switch helps to create a virtual switch which provides VMs with the access to a physical network by binding to the physical network adapter.
  • Internal switch helps to create a virtual switch which is only available to the VMs that run on this physical computer, but it does not provide access to a physical network connection.
  • Private switch helps to create a virtual switch which can be used only by the VMs that run on the same physical computer. A private network allows to create an isolated networking environment which cannot be accessed externally.

4. Select Create Virtual Switch.

Creating Virtual Switch

5. Set up Virtual Switch Properties. Here, you can insert the name of the new virtual switch and type some additional information in the Notes section.

6. Configure the connection type by choosing the network type you want the virtual switch to connect to (external, internal, private).

If External, choose the type of the network adapter that you want to use. After that, check the box below if you want to allow management operating system to share this network adapter.

Moreover, if you want to isolate the management Hyper-V host operating system (OS) or other VMs that share the same virtual switch from the network traffic and identify the local area network (LAN) that will be used for network communications, select Enable virtual LAN identification for management operating system. This feature is available for external and internal networks. You can manually set up VLAN ID, which will then be associated with a certain VLAN and used for future network communications.

Configuring Virtual Switch Properties

7. Click OK. The following dialogue window will appear.

‘Apply Networking Changes’ Dialog Box

8. Click Yes if you want to apply the networking changes.

After that, the virtual switch will be created, meaning that your virtual environment can now use its own virtual network to facilitate VM networking.

How to Create Hyper-V Virtual Machine

There are three common ways through which you can create Hyper-V virtual machine: Hyper-V Manager, PowerShell, and Hyper-V Quick Create. All of them will be discussed in detail below.

How to Create Hyper-V Virtual Machine Using Hyper-V Manager

1. Type Hyper-V Manager in the search box situated on the taskbar below and press ENTER. Hyper-V Manager will open.

2. On the left, select the Actions section, find New, and click Virtual Machine.

Creating New VM with Hyper-V Manager

3. New Virtual Machine Wizard will open, presenting the set of VM options that you need to configure. They include: Before You Begin, Specify Name and Location, Specify Generation, Assign Memory, Configure Networking, Connect Virtual Hard Disk, and Summary.

4. The Before You Begin section provides a short overview of what this wizard can do and how to use it. Read it and check the box Do not show this page again below if you want to skip this information in the future.

New Virtual Machine Wizard

5. In the next section, you can configure the VM name and location. Ensure that the VM name is unique and allows you to easily identify the required VM. As for the VM location, you can either leave the default one, or you can create a folder and assign a new location of your choice. For this purpose, check the box below and click Browse.

Specifying VM Name and Location

6. In the Generation section, you can choose the generation of the VM. The choice between Generation 1 and Generation 2 is mainly dictated by the guest OS that you want to install. Generation 1 VMs support 32-bit and 64-bit guest OSes and BIOS-based architecture. Also, they provide functionality of the earlier versions of Hyper-V. Generation 2 VMs, on the other hand, support 64-bit Windows OSes and the latest versions of Linux and FreeBSD OSes and provide advanced virtualization features, such as Secure Boot. Take all aspects into account when choosing between the two generation types because you can’t change the VM generation after the VM has been created.

Specifying VM Generation

7. In the following section, you must specify the amount of memory (from 32 MB up to 12,582,912 MB) which will be assigned to the VM. The future performance of the VM will largely depend on the amount of allocated memory. Moreover, you can choose to use Dynamic Memory for this VM by checking the box below. This feature allows you to take a part of the memory available on a physical host and assign resources to the VM which needs it most.

Assigning Startup Memory

8. The next step allows you to choose a virtual network that will be used for connecting the VM to the network. For this purpose, select a virtual switch that you have created earlier. If you don’t have a virtual switch, only the Not Connected option will be provided, meaning that your VM will be left without network access.

Configuring the Network Adapter

9. Next, configure the virtual hard disk requirements. In this section, you can create a new virtual hard disk, which requires specifying its name, location, and size. Or, you can use an existing virtual hard disk (of the VHD or VHDX format). Another option is to skip this step and attach a virtual hard disk later.

Configuring Virtual Hard Disk

If you have decided to select Create a Virtual Hard Disk, the Installation Options section will appear where you can specify configurations right away or postpone it for later. Here, you can install a guest OS by choosing the ISO file. In this case, you can select one of the following variants:

  • Install an operating system later
  • Install an operating system from a bootable CD/DVD-ROM
  • Install an operating system from a bootable floppy disk
  • Install an operating system from a network-based installation server

Select one of the options and click Next.

10. The last section is Summary, which provides a short description of this VM. Look through it once again and check to make sure everything is correct. If so, click Finish to create the VM and close the wizard.

Summary of the New VM