Starting and stoping Oralce Database

In this article is a quick note to start and stop an oracle database.

How To Startup Oracle Database

1. Login to the system with oracle username

Typical oracle installation will have oracle as username and dba as group. On Linux, do su to oracle as shown below.

$ su - oracle

2. Connect to oracle sysdba

Make sure ORACLE_SID and ORACLE_HOME are set properly as shown below.

$ env | grep ORA
ORACLE_SID=DEVDB
ORACLE_HOME=/u01/app/oracle/product/10.2.0


You can connect using either “/ as sysdba” or an oracle account that has DBA privilege.

$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 18 11:11:28 2009
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
SQL>

3. Start Oracle Database

The default SPFILE (server parameter file) is located under $ORACLE_HOME/dbs. Oracle will use this SPFILE during startup, if you don’t specify SPFILE.

Oracle will look for the parameter file in the following order under $ORACLE_HOME/dbs. If any one of them exist, it will use that particular parameter file.

    spfile$ORACLE_SID.ora
    spfile.ora
    init$ORACLE_SID.ora


Type “startup” at the SQL command prompt to startup the database as shown below.

SQL> startup
ORACLE instance started.

Total System Global Area  812529152 bytes
Fixed Size                  2264280 bytes
Variable Size             960781800 bytes
Database Buffers           54654432 bytes
Redo Buffers                3498640 bytes
Database mounted.
Database opened.
SQL>


If you want to startup Oracle with PFILE, pass it as a parameter as shown below.

SQL> STARTUP PFILE=/u01/app/oracle/product/10.2.0/dbs/init.ora

How To Shutdown Oracle Database

Following three methods are available to shutdown the oracle database:

    Normal Shutdown
    Shutdown Immediate
    Shutdown Abort

1. Normal Shutdown

During normal shutdown, before the oracle database is shut down, oracle will wait for all active users to disconnect their sessions. As the parameter name (normal) suggest, use this option to shutdown the database under normal conditions.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

2. Shutdown Immediate

During immediate shutdown, before the oracle database is shut down, oracle will rollback active transaction and disconnect all active users. Use this option when there is a problem with your database and you don’t have enough time to request users to log-off.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

3. Shutdown Abort

During shutdown abort, before the oracle database is shutdown, all user sessions will be terminated immediately. Uncomitted transactions will not be rolled back. Use this option only during emergency situations when the “shutdown” and “shutdown immediate” doesn’t work.

$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 18 11:11:33 2009
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to an idle instance.

SQL> shutdown abort
ORACLE instance shut down.
SQL>

How To Start, Stop and Restart Oracle Listener

For a database administrator, starting up and shutting down the oracle listener is a routine task .

Displaying oracle listener status:

Before starting, stopping or restarting make sure to execute lsnrctl status command to check the oracle listener status as shown below. Apart from letting us know whether the listener is up or down, you can also find the following valuable information from the lsnrctl status command output.

    Listner Start Date and Time.
    Uptime of listner – How long the listener has been up and running.
    Listener Parameter File – Location of the listener.ora file. Typically located under $ORACLE_HOME/network/admin
    Listener Log File – Location of the listener log file. i.e log.xml

command: $ lsnrctl status

Starting oralce listener:

If the Oracle listener is not running, start the listener as shown below. This will start all the listeners. If you want to start a specific listener, specify the listener name next to start. i.e lsnrctl start [listener-name]

command: $ lsnrctl start

Stoppping listener:

If the Oracle listener is running, stop the listener as shown below. This will stop all the listeners. If you want to stop a specific listener, specify the listener name next to stop. i.e lsnrctl stop [listener-name]

command: $ lsnrctl stop

Restarting listener:

To restart the listener use lsnrctl reload as shown below instead of lsnrctl stop and lsnrctl start. realod will read the listener.ora file for new setting without stop and start of the Oracle listener.

command: $ lsnrctl reload

Available listener commands:

lsnrctl help command will display all available listener commands. In Oracle 11g following are the available listener commands.

    start – Start the Oracle listener
    stop – Stop the Oracle listener
    status – Display the current status of the Oracle listener
    services – Retrieve the listener services information
    version – Display the oracle listener version information
    reload – This will reload the oracle listener SID and parameter files. This is equivalent to lsnrctl stop and lsnrctl start.
    save_config – This will save the current settings to the listener.ora file and also take a backup of the listener.ora file before overwriting it. If there are no changes, it will display the message “No changes to save for LISTENER”
    trace – Enable the tracing at the listener level. The available options are ‘trace OFF’, ‘trace USER’, ‘trace ADMIN’ or ‘trace SUPPORT’
    spawn – Spawns a new with the program with the spawn_alias mentioned in the listener.ora file
    change_password – Set the new password to the oracle listener (or) change the existing listener password.
    show – Display log files and other relevant listener information.

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.