Move datafile to different location

From time to time a DBA might need move an Oracle database datafile from one location to another. Reasons for this might include: I/O balancing for performance reasons, or to rectify incorrect naming standards.
Choose one of the following procedures based on the log mode of your database (select log_mode from sys.v_$database):

Database is in ARCHIVELOG mode

• Take the datafile offline with the "ALTER DATABASE DATAFILE '/old/location' OFFLINE;" command.
• Copy or move the datafile to its new location. On Unix this can be done with the "dd" command.

Example:

dd if=/old/location of=/new/location bs=4096

SQL> ALTER DATABASE RENAME FILE '/old/location' TO '/new/location';
SQL> RECOVER DATAFILE '/new/location';
SQL> ALTER DATABASE DATAFILE '/new/location' ONLINE;

Database is in NOARCHIVELOG mode

• Shutdown the database
• Copy or move the datafile to its new location. On Unix this can be done with the "dd" command. Example:

dd if=/old/location of=/new/location bs=4096

• Start SQL*Plus, do a "STARTUP MOUNT" and rename the file:

SQL> ALTER DATABASE RENAME FILE '/old/location' TO '/new/location';
SQL> ALTER DATABASE OPEN;

Note: '/old/location' and '/new/location' means the file name of the old and new location. Generic selection with an asterisk is unfortunately not possible.
Note: If you use new 3GB HDD and the physical block size is 4K - use fsutil to check - it is NOT possible to use REDO logs on this device. Rename fails with error ORA-01512 and a message, that the header couldn't read with ReadFile(). If this occurs, use another device which have a correct block size.

Check current location of datafiles

If the database is offline you can still check the current location of the datafiles and archive log mode.

SQL> select log_mode from v$database;
SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> select name from v$tempfile;
SQL> select name from v$controlfile;

Note that the location of data files (and other fixed tables/views) is stored in the control files, and the location of the control files is stored in the init file (pfile or spfile).

Some Network Related Commands for Linux

TO resolve network related issue/connection issue in linux server, some commands are so much helpful.

• finding host/domain name and IP address - hostname
• test network connection – ping
• getting network configuration – ifconfig
• Network connections, routing tables, interface statistics – netstat
• query DNS lookup name – nslookup
• communicate with other hostname – telnet
• outing steps that packets take to get to network host – traceroute
• view user information – finger
• checking status of destination host - telnet




hostname
hostname with no options displays the machines host name
hostname –i displays the IP address for the current machine
hostname –d displays the domain name the machine belongs to
hostname –f displays the fully qualified host and domain name


ping
It sends packets of information to the user-defined source. If the packets are received, the destination device sends packets back. Ping can be used for two purposes

1. To ensure that a network connection can be established.
2. Timing information as to the speed of the connection.

If you do ping www.google.com it will display its IP address. Use ctrl+C to stop the test. 

ifconfig
View network configuration, it displays the current network adapter configuration. It is handy to determine if you are getting transmit (TX) or receive (RX) errors.

netstat
Most useful and very versatile for finding connection to and from the host. You can find out all the multicast groups (network) subscribed by this host by issuing "netstat -g"
netstat -nap | grep port will display process id of application which is using that port
netstat -a  or netstat –all will display all connections including TCP  and UDP  
netstat --tcp  or netstat –t will display only TCP  connection
netstat --udp or netstat –u will display only UDP  connection
netstat -g will display all multicast network subscribed by this host.

nslookup
If you know the IP address it will display hostname. To find all the IP addresses for a given domain name, the command nslookup is used. 
You can also use nslookup to convert hostname to IP Address and from IP Address from hostname.

traceroute
A handy utility to view the number of hops and response time to get to a remote system or web site is traceroute. Again you need an internet connection to make use of this tool.

finger
View user information, displays a user’s login name, real name, terminal name and write status. this is pretty old unix command and rarely used now days.
In ubuntu finger package is not installed by default. To install that, we need to install that using command:

      sudo apt-get install finger

telnet
Connects destination host via telnet protocol, if telnet connection establish on any port means connectivity between two hosts is working fine.
telnet hostname port   will telnet hostname with the port specified. Normally it is used to see whether host is alive and network connection is fine or not.

Calling webservice from PLSQL

Sometimes for software architectural issue we are in need to call webservice API. Suppose, we are using database for all type of backend processing, in that scenario its very impractical to call Webservice using another application or language. We can easily overcome this scenario by calling webservice from plsql code. For this purpose, we can use the below code:


Its a package containing the Stored Procedure to call a soap service form PL Sql:

Package Spec:

create or replace PACKAGE PKG_ABS_ALERT_WS AS 

  /* TODO enter package declarations (types, exceptions, methods etc) here */ 

 PROCEDURE SP_CALL_SMS_SERVICE(

  P_MOBILE_NUM IN VARCHAR2,

  P_MESSAGE IN VARCHAR2

  );

END PKG_ABS_ALERT_WS;

Package Body:

create or replace PACKAGE BODY PKG_ABS_ALERT_WS AS
  PROCEDURE SP_CALL_SMS_SERVICE(
  P_MOBILE_NUM IN VARCHAR2,
  P_MESSAGE IN VARCHAR2
  ) AS

  vg_funciton_fnc VARCHAR2(256);
  vg_ws_address   VARCHAR2(255);
  l_namespace VARCHAR2(255);
  l_return    VARCHAR2(32767);
  ol_req  soap_api.t_request;
  ol_resp soap_api.t_response;

  BEGIN  
    DBMS_OUTPUT.PUT_LINE('P_MOBILE_NUM: '||P_MOBILE_NUM||'  P_MESSAGE:'||P_MESSAGE );   
     vg_funciton_fnc:= 'ns1:sendSMS';
     vg_ws_address:= 'http://:8080/NotificationService/SMSService?wsdl';
     l_namespace  := 'xmlns:ns1="http://sms.notification.dbbl.com/"';

       --we initilize a new request 
           ol_req := soap_api.new_request(vg_funciton_fnc,l_namespace);
        
       -- we started to add parameters
          
              
          soap_api.add_parameter(ol_req,
                                 'channelName','ABS'
                                 );
                             
          soap_api.add_parameter(ol_req,
                                 'mobileNo',P_MOBILE_NUM
                                  );                               
          soap_api.add_parameter(ol_req,
                                 'messageText',P_MESSAGE
                                 );
          soap_api.add_parameter(ol_req,
                                 'refId',
                                 alrt_seq.nextval);
                                 
        
                       
          -- we call the web service
          ol_resp := soap_api.invoke(ol_req, vg_ws_address, vg_funciton_fnc);
          
          -- we get back the results
          l_return := soap_api.get_return_value(ol_resp,
                                           'return', -- result tag name
                                           'xmlns:m="' || --can be change as "xmlns:n1"
                                           vg_ws_address || '"');
                                           
         DBMS_OUTPUT.PUT_LINE('Output: '||l_return);                          
     
  END SP_CALL_SMS_SERVICE;

END PKG_ABS_ALERT_WS;

Installing Gradle in Ubuntu

Install Gradle on Ubuntu Linux

Step-by-step instructions

Gradle is a Java build system. It uses a clean, simple configuration syntax and is used by Android as the default build system. For API development, Gradle is supported by Spring.

Installation is quick and simple. We are going to install a private copy so that root permissions are not required during development.

Download Gradle

mkdir -p ~/opt/packages/gradle && cd $_

wget https://services.gradle.org/distributions/gradle-2.3-bin.zip

unzip gradle-2.3-bin.zip

Install and setup Gradle

Next, we will create a symlink that provides a shorter path to the specific Gradle version. The symlink will allow us to upgrade Gradle later without changing any other configuration.

ln -s ~/opt/packages/gradle/gradle-2.3/ ~/opt/gradle

Open your .profile file in vi, emacs, or gedit.

gedit ~/.profile

Paste the following at the bottom of your .profile file.

# Gradle
if [ -d "$HOME/opt/gradle" ]; then
    export GRADLE_HOME="$HOME/opt/gradle"
    PATH="$PATH:$GRADLE_HOME/bin"
fi

Finally, source your .profile and test gradle.

source
~/.profile

which gradle

gradle -version

Congratulations, you now have a working Gradle installation.

To create and add Swap file in Ubuntu Linux after installation

What is a swap file on Ubuntu server or desktop system?

As a sysadmin it is necessary to add more swap space after installation on the server. Swap file allows Ubuntu Linux to use hard disk to increase virtual memory.

Virtual Memory = RAM + Swap space/file
Virtual Memory (1GB) = Actual RAM (512MB) + Swap space/file (512MB)

When the Ubuntu server runs low on memory, it swaps a section of RAM (say an idle program like foo) onto the hard disk (swap space) to free up memory for other programs. Then when you need that program (say foo again), kernel swapped out foo program, it changes places with another program in RAM.

Procedure to add a swap file on a Ubuntu Linux

Open the Terminal app or use the ssh client to get into the remote server. Login as a root user using sudo command:

 
sudo -s
 

Create a swap file command

Type the following command to create a 2GB swap file on Ubuntu:# dd if=/dev/zero of=/swapfile bs=1G count=2
Sample outputs:

2+0 records in
2+0 records out
2147483648 bytes (2.1 GB) copied, 20.2256 s, 106 MB/s

Verify that file has been created on the server:# ls -lh /swapfile
Sample outputs:

-rw-r--r-- 1 root root 2.0G Oct 29 14:07 /swapfile

Creating swap space using fallocate command instead of dd command

Instead of the dd command, you can use the the faster fallocate command to create swap file as follows:# fallocate -l 1G /swapfile-1
# ls -lh /swapfile-1
Sample outputs:

-rw-r--r-- 1 root root 1.0G Oct 29 14:11 /swapfile-1

Secure the swap file

Type the following chmod command and chown command to secure and set correct file permission for security reasons:# chown root:root /swapfile
# chmod 0600 /swapfile
# ls -lh /swapfile
Sample outputs:

-rw------- 1 root root 2.0G Oct 29 14:07 /swapfile

A world-readable swap file is a huge local vulnerability. The above commands make sure only root user can read and write to the file.

Turn on the swap file

First, use the mkswap command as follows to enable the swap space on Ubuntu:# mkswap /swapfile
Sample outputs:

Setting up swapspace version 1, size = 2097148 KiB
no label, UUID=10231c61-6e55-4dd3-8324-9e2a892e7137

Finally, activate the swap file, enter:# swapon /swapfile

Verify new swap file and settings on Ubuntu

Type the following command# swapon -s
Sample outputs:

Filename    Type  Size Used Priority
/dev/sda5                               partition 3998716 704 -1
/swapfile                               file  2097148 0 -2

You can also run the following commands to verify swap file and its usage:# grep -i --color swap /proc/meminfo
# top
# htop
# atop

How can I disable swapfile on Ubuntu?

You need to use the swapoff command as follows:# swapoff /swapfile
# swapon -s

Update /etc/fstab file

You need to make sure the swap file enabled when server comes on line after the reboot. Edit /etc/fstab file, enter:# vi /etc/fstab
Append the following line:

/swapfile none            swap    sw              0       0

Save and close the file.

Tuning the swap file i.e. tuning virtual memory

You can tune the following two settings:

1. swappiness
2. min_free_kbytes
3. vfs_cache_pressure

How do I set swappiness on a Ubuntu server?

The syntax is:# sysctl vm.swappiness=VALUE
# sysctl vm.swappiness=20
OR# echo VALUE > /proc/sys/vm/swappiness
# echo 30 > /proc/sys/vm/swappiness
The value in /proc/sys/vm/swappiness file controls how aggressively the kernel will swap memory pages. Higher values increase agressiveness, lower values descrease aggressiveness. The default value is 60. To make changes permanent add the following line to/etc/sysctl.conf:

 
echo 'vm.swappiness=30' >> /etc/sysctl.conf
 

For database server such as Oracle or MySQL I suggest you set a swappiness value of 10. 

Source