Friday 13 May 2016

UNIX / LINUX Commands for Oracle DBA





  1) Basic Commands
  2) File and directory related commands
  3) Listing Commands
  4) Performance related commands
  5) Networking related commands
  6) Other commands

 

 Basic Commands



  $date --- To display the date and time of system.
  $cal--- To display the calender of the year.
      $cal 2013
      $cal 2014
      $cal 8 2014
      $cal 6 2011
      $cal 9
  $who--- To display all the users who curretly connected to the server
  $whoami --- To display information about you
  $su --- To switch from one user to another user.

       first login in to the server as root user, then switch to oracle user as below
  
       $su - oracle
  $clear --- To clear the window screen.
  $exit --- To exit from current user.
  $login --- To login to server using your username and passwd.
  $logout --- To logout of the current session.
  $banner --- To display the character or work followed by this command.

       $banner HYDERABAD
  $init 0 ---To shutdown the server or system
  $init 6 --- To restart the server or system
  $find--- To find a specific file in the current locaion.
  $which --- To find the location of executable you are using currently.
 
          $which sqlplus
  $uname -a ---- To display which OS is running and its version.
  $ps ---- To display current user processes information
  $ps -ef --- To display all current user processes plus system processes information
  $ps -ef|grep "process name" --- To display a specific process among many processes.
  $gzip xxx.txt --- To zip or compress a file on unix and linux side
  $gunzip xxx.txt.gz --- To unzip or uncompress a file
  $man ---- To get information/manual of a command.
 
        $man gzip

  $scp ---- To copy a file from one server to another server.

      $scp xxx.txt oracle@rr2.oracle.com:/u01/sales/

  $ping --- To check whether a ip address is reachable or not

     $ping 192.168.1.1

  $

 



FILE AND DIRECTORY Related commands


  Creating a new file :

   $cat > xxx.txt -- To create a new file using this. after typing a over..just press control + c.
   $touch zzz.txt --- To create a new empty file.
   $vi aaa.txt --- To create a new file in VI editor. if you want to insert data into this file, press on i key and type.
   $touch .abc.txt --- To creat empty hidden file.
  

 Copying a file :

   $cp /u01/xxx.txt /u02/
   $cp /u01/sales/xxx.txt   /u01/hdfc
  
 Moving a file

  $mv /u01/xxx.txt  /u02
  $mv /u02/abc/ttt.txt /u02/sales/

 Renaming a file

  $mv xxx.txt  yyy.txt(new name)

 Deleting or removing a file

  $rm xxx.txt
  $rm -f xxx.txt

 Directory related commands :



 

 creating a directory

  $mkdir /u02/movie
  $mkdir -p /u02/movie/hindi/krish (with -p option we can create all inner sub directories at a time along with main directoy)

 coping a directorty:

 $cp -r /u02/movie  /u03

 moving a directory :

 $mv -r /u02/movie  /u04

 Deleting or removing a directory :

 $rm -rf /u02/movie

 renaming a directory :

 $mv movie movie_new


 Performance related commands :


  $top  

 This command is going to display following information..

  * Server avg load
  * number of tasks are currently running on the server
  * current cpu Usage on the server
  * current memory usage on the server
  * which process is consuming high CPU and memory on the server
  * other information.

 $sar  (system activity report)

  This display's the report of system activity

   $sar 2 5 ( This display's report of the system activity for every 2 seconds until 5 times)

 $iostat ( This is to check what is the io statistics on the devices of the server. This display's the stats of
inputs (reads) per second and output (write) per second.

 $free ( this display's the free RAM MEMORY and swap memory information on the server)

 $vmstat ( This display's the virtual memory stats on the system)



 Network related commands :

  $/sbin/ifconfig -a  -- This is to find out the ip address and any packates drop over the network. this is similar to ipconfig on windows side.

  $netstat -- This is to find out the network stats on the server
 
  $nslookup 99.88.77.9  --- This is to find out the server(hostname) detatils of we know the ip address

 Listing commands :

  $ls -- To list out all the directories and files under a specific file system (/u01)
  $ls -a -- To display all the hidden files under a specific location

  $ls -i --- To display the inode number of the files and folders..whenever we create a file or folder on the unix/linux side,
os assignes a unique number to those files, it is called inode number.

  $ls -r -- displays  the files and folders in reverse order..this is opposite to ls command

  $ls -l -- displays the files and folders in the long listing order

  $ls -lt -- display the fiels and folders in time order that are crated.

  $ls -ltr --- display the files and folders in reverse,timely and long listing order.


  Other commands :

  #useradd --- to add a user account at OS level..this is generally done by OS ADMIN but not a dba.

  #userdel -- to delete a user account at os level.

  #groupadd -- to add a group at os level

  #groupdel --to delete a group at os level

  #chmod -- to change permission of a file or folder.

    #chmod 775 xxx.txt

  #chown -- to change the ownership of a file or folder

    $chown -R oracle:oinstall /u01/app.


  #df -h -- to display the disk free (all file systems) on the server

  #du -sh xxx.txt  -- to display the size of a file or folder in a specific location (/u01)

  #more xxx.txt --- to display the file content page by page. press spacebar on the keypad if you want to see the content page by page.

  #wc -l --- to get work count(wc)  in the a specific location

  #echo -- to print the text after the echo


      #echo $SHELL
 
      #echo $ORACLE_SID


 

Shutting Down a Database


To shut down a database and instance, you must first connect as SYSOPER or SYSDBA.

·         Shutting Down with the NORMAL Clause
·         Shutting Down with the IMMEDIATE Clause
·         Shutting Down with the TRANSACTIONAL Clause
·         Shutting Down with the ABORT Clause


 Shutting Down with the NORMAL Clause

To shut down a database in normal situations, use the SHUTDOWN command with the NORMAL clause:
SQL> SHUTDOWN NORMAL
The NORMAL clause is optional, because this is the default shutdown method if no clause is provided.
Normal database shutdown proceeds with the following conditions:
·         No new connections are allowed after the statement is issued.
·         Before the database is shut down, the database waits for all currently connected users to disconnect from the database.
The next startup of the database will not require any instance recovery procedures.


Shutting Down with the IMMEDIATE Clause

Immediate database shutdown proceeds with the following conditions:
·         No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
·         Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)
·         Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly rolls back active transactions and disconnects all connected users.
The next startup of the database will not require any instance recovery procedures.


Shutting Down with the TRANSACTIONAL Clause

When you want to perform a planned shutdown of an instance while allowing active transactions to complete first, use the SHUTDOWN command with the TRANSACTIONAL clause:
SQL> SHUTDOWN TRANSACTIONAL
Transactional database shutdown proceeds with the following conditions:
·         No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
·         After all transactions have completed, any client still connected to the instance is disconnected.
·         At this point, the instance shuts down just as it would when a SHUTDOWN IMMEDIATE statement is submitted.
The next startup of the database will not require any instance recovery procedures.


Shutting Down with the ABORT Clause

You can shut down a database instantaneously by aborting the database instance.
When you must do a database shutdown by aborting transactions and user connections, issue the SHUTDOWN command with the ABORT clause:
SQL> SHUTDOWN ABORT
An aborted database shutdown proceeds with the following conditions:
·         No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
·         Current client SQL statements being processed by Oracle Database are immediately terminated.
·         Uncommitted transactions are not rolled back.
·         Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly disconnects all connected users
.


Tuesday 10 May 2016

Recovery of ControlFiles to Another Location, without AutoBackup available


Today , I will show you a scenario, where I lost all my current Controlfile and I don’t have any AutoBackup of Current ControlFile. And how I rescue from that situation.

Actually my MountPoint was Gone due to some issues with System Admin. Hence I Lost the Directory too.
So, I will Restore to Another Location


Lets see, What are my ControlFile Locations are . . using Below command
 SQL> show parameter control_file  
 NAME                         TYPE       VALUE  
 ---------------------------------------      -----------     ------------------------------  
 control_file_record_keep_time     integer      7  
 control_files                    string       /u01/app/oracle/oradata/orcl/c  
                                           ontrol01.ctl, /u01/app/oracle/  
                                           flash_recovery_area/orcl/contr  
                                           ol02.ctl  
 SQL>  





OK,  so now Lets Simulate this Scenario.. as Below
I’m Removing my Current Controlfiles. (Assuming that – I lost my Both ControlFiles )
 [oracle@system1 ~]$ rm -rf /u01/app/oracle/oradata/orcl/control01.ctl  
 [oracle@system1 ~]$ cd /u01/app/oracle/oradata/orcl/  
 [oracle@system1 orcl]$ ls control  
 ls: cannot access control: No such file or directory  
 [oracle@system1 ~]$ rm -rf /u01/app/oracle/flash_recovery_area/orcl/control02.ctl  



Lets open RMAN prompt & Backup the ControlFile from Memory . . using Below Command
 SQL> ! rman target /  
 RMAN> backup current controlfile to destination '/u01';  
 using channel ORA_DISK_1  
 channel ORA_DISK_1: starting full datafile backup set  
 channel ORA_DISK_1: specifying datafile(s) in backup set  
 including current control file in backup set  
 channel ORA_DISK_1: starting piece 1 at 09-MAY-16  
 channel ORA_DISK_1: finished piece 1 at 09-MAY-16  
 piece handle=/u01/ORCL/backupset/2016_05_09/o1_mf_ncnnf_TAG20160509T162824_cm0vbkv4_.bkp tag=TAG20160509T162824 comment=NONE  
 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03  
 Finished backup at 09-MAY-16  
 RMAN> exit  
 Recovery Manager complete.  




Now , when we try to shutdown immediate / or normal, we see below error.
But don’t worry. We will use Shutdown Abort.
 SQL> shut immediate  
 Database closed.  
 ORA-00210: cannot open the specified control file  
 ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'  
 ORA-27041: unable to open file  
 Linux Error: 2: No such file or directory  
 Additional information: 3  
 SQL> shut abort  
 ORACLE instance shut down.  




Again , Open your database in Nomount stage to Restore the Control File.
Here , I’m going to Restore to Another location (Other than Existing Location)

So, we need to change the parameter ‘control_files’.
 SQL> startup nomount  
 ORACLE instance started.  
 Total System Global Area 422670336 bytes  
 Fixed Size            1336960 bytes  
 Variable Size        109054336 bytes  
 Database Buffers    306184192 bytes  
 Redo Buffers         6094848 bytes  
 SQL> !rman target /  
 Recovery Manager: Release 11.2.0.1.0 - Production on Mon May 9 16:30:05 2016  
 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.  
 connected to target database: ORCL (not mounted)  
 RMAN> restore controlfile to '/u01/app/oracle/oradata/orcl/control001.' from '/u01/ORCL/backupset/2016_05_09/o1_mf_ncnnf_TAG20160509T162824_cm0vbkv4_.bkp' ;  
 Starting restore at 09-MAY-16  
 using channel ORA_DISK_1  
 channel ORA_DISK_1: restoring control file  
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01  
 Finished restore at 09-MAY-16  
 RMAN> exit  
 SQL> alter system set control_files=/u01/app/oracle/oradata/orcl/control001. Scope=spfile;  
 SQL> startup mount;  
 ORACLE instance started.  
 Total System Global Area 422670336 bytes  
 Fixed Size            1336960 bytes  
 Variable Size        100665728 bytes  
 Database Buffers    314572800 bytes  
 Redo Buffers         6094848 bytes  
 Database mounted.  
 SQL>  




Now , when I try to Recover the Database , I Get an Error .
 SQL> recover database ;  
 ORA-00283: recovery session canceled due to errors  
 ORA-01610: recovery using the BACKUP CONTROLFILE option must be done  
 So, Now , I tried Another command, and Again Facing an Error  
 SQL> recover database using backup controlfile;  
 ORA-00279: change 792051 generated at 05/09/2016 16:23:23 needed for thread 1  
 ORA-00289: suggestion :  
 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2016_05_09/o1_mf_1_4_%u_.arc  
 ORA-00280: change 792051 for thread 1 is in sequence #4  
 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}  
 AUTO  
 ORA-00308: cannot open archived log  
 '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2016_05_09/o1_mf_1_4_%u_.ar  
 c'  
 ORA-27037: unable to obtain file status  
 Linux Error: 2: No such file or directory  
 Additional information: 3  
 ORA-00308: cannot open archived log  
 '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2016_05_09/o1_mf_1_4_%u_.ar  
 c'  
 ORA-27037: unable to obtain file status  
 Linux Error: 2: No such file or directory  
 Additional information: 3  
 SQL> alter database open resetlogs ;  
 alter database open resetlogs  
 *  
 ERROR at line 1:  
 ORA-01113: file 1 needs media recovery  
 ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'  



So Now , Simply. . . what  I did is - - - > open RMAN - - > and Recover Database,
 SQL> !rman target /  
 Recovery Manager: Release 11.2.0.1.0 - Production on Mon May 9 16:40:04 2016  
 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.  
 connected to target database: ORCL (DBID=1433520325, not open)  
 RMAN> recover database ;  
 Starting recover at 09-MAY-16  
 Starting implicit crosscheck backup at 09-MAY-16  
 using target database control file instead of recovery catalog  
 allocated channel: ORA_DISK_1  
 channel ORA_DISK_1: SID=17 device type=DISK  
 Crosschecked 1 objects  
 Finished implicit crosscheck backup at 09-MAY-16  
 Starting implicit crosscheck copy at 09-MAY-16  
 using channel ORA_DISK_1  
 Finished implicit crosscheck copy at 09-MAY-16  
 searching for all files in the recovery area  
 cataloging files...  
 no files cataloged  
 using channel ORA_DISK_1  
 starting media recovery  
 archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/oradata/orcl/redo01.log  
 archived log file name=/u01/app/oracle/oradata/orcl/redo01.log thread=1 sequence=4  
 media recovery complete, elapsed time: 00:00:00  
 Finished recover at 09-MAY-16  
 RMAN> exit  
 Recovery Manager complete.  
 SQL> alter database open resetlogs;  
 Database altered.  
 SQL>  



Tickiting Tool for DBA's

Defination of Ticketing Tool  : 
It is a software which provides solutions for the tickets



Ø    Ticket means a problem.There are many ticketing tools are available.

Ø  for example  BMC Remedy.




Ticket consists :  SLA (SERVICE LEVEL AGREEMENT)

Ticket No:

Summary :

Status      :

Start Time :

End Time   :



Reporting Person :
Email ID , Phone , Number will be there in this If Instructions are big attachments will be there



Q)   What is REMEDY Ticketing tool, how it works?



A)  REMEDY is a customer relationship tool which can be used to  log / monitor the issues or problems faced by customers by the means of incident management tickets. Each ticket is  like an incident (problem) which is created by help desk and  assigned to relevant support team.

Concerned support team member take the ownership of the  ticket and updates the work log (troubleshooting steps performed during the course of action)

Also , it can be used to monitor Service Requests / change  management ( Change Requests) and problem management.




About Wok Log  :  Maximo Work Log will be there.



----> Data Base team , Unix team , Application team , Middle ware team

-----> Different teams will be involved in project will give their working status in this work log



SEVIARITIES ABOUT TICKETS :



SERV 1   :  Need to resolve tickets in 0-4 hours

SERV 2   :  Need to resolve tickets in 0-8 hours

SERV 3   : Need to resolve tickets in 3 working days.



Production Support  : Should be on call support.

Situation Manager    : Makes every one to attend on call.




Q)  When you are On Call Support when you get Severity 1 ticket ? 
How do you take action ?



A)  ----->  Firstly open the ticket

     ------> Read the message what the problem is about

     -----> Login to targeted Servers.

    ----->  Check Logs depending on Error.

   ------>  Propogate first hand information to STAKE HOLDER (DUTY MANAGER)


   ------> Sit on Issue , fix it , Close it


Click here to Join Our Blog and Stay Updated

How to search for Specific text in Alert Log

vi and vim have powerful searching capabilities because they bring to bear the richness of regular expressions.

To search for the next occurence of the text ‘ORA’ from the current cursor position, type:

/ORA

Search backward from the current position by using  ' ? '  instead of / in the command.

Once you have searched for something, you can find the next occurrence by pressing n or the previous occurrence with N .

Searching in vi/vim is enhanced with regular expressions.
For example, to find the next occurence of the text ‘ORA’ that occurs at the beginning of a line, use:
/^ORA

Monday 9 May 2016

Unix related Oracle DBA Interview

Questions:
1. How do you see how many instances are running?
2. How do you automate starting and shutting down of databases in Unix?
3. You have written a script to take backups. How do you make it run automatically every week?
4. What is OERR utility?
5. How do you see Virtual Memory Statistics in Linux?
6. How do you see how much hard disk space is free in Linux?
7. What is SAR?
8. What is SHMMAX?
9. Swap partition must be how much the size of RAM?
10. What is DISM in Solaris?
11. How do you see how many memory segments are acquired by Oracle Instances?
12. How do you see which segment belongs to which database instances?
13. What is VMSTAT?
14. How do you set Kernel Parameters in Red Hat Linux, AIX and Solaris?
15. How do you remove Memory segments?
16. What is the difference between Soft Link and Hard Link?
17. What is stored in oratab file?
18. How do you see how many processes are running in Unix?
19. How do you kill a process in Unix?
20. Can you change priority of a Process in Unix?

Saturday 7 May 2016

Installation/Configuration Interview Questions

1. Define OFA.  Level: Low 
Expected answer: OFA stands for Optimal Flexible Architecture. It is a method of placing directories and files in an Oracle system so that you get the maximum flexibility for future tuning and file placement.

2. How do you set up your tablespace on installation?
Expected answer: The answer here should show an understanding of separation of redo and rollback, data and indexes and isolation os SYSTEM tables from other tables. An example would be to specify that at least 7 disks should be used for an Oracle installation so that you can place SYSTEM tablespace on one, redo logs on two (mirrored redo logs) the TEMPORARY tablespace on another, ROLLBACK tablespace on another and still have two for DATA and INDEXES. They should indicate how they will handle archive logs and exports as well. As long as they have a logical plan for combining or further separation more or less disks can be specified.

3. What should be done prior to installing Oracle (for the OS and the disks)?  Level: Low
Expected Answer: adjust kernel parameters or OS tuning parameters in accordance with installation guide. Be sure enough contiguous disk space is available.

4. You have installed Oracle and you are now setting up the actual instance. You have been waiting an hour for the initialization script to finish, what should you check first to determine if there is a problem?
Expected Answer: Check to make sure that the archiver isn’t stuck. If archive logging is turned on during install a large number of logs will be created. This can fill up your archive log destination causing Oracle to stop to wait for more space.

5. When configuring SQLNET on the server what files must be set up?  Level: Intermediate
Expected answer: INITIALIZATION file, TNSNAMES.ORA file, SQLNET.ORA file

6. When configuring SQLNET on the client what files need to be set up?  Level: Intermediate
Expected answer: SQLNET.ORA, TNSNAMES.ORA

7. What must be installed with ODBC on the client in order for it to work with Oracle?
Expected answer: SQLNET and PROTOCOL (for example: TCPIP adapter) layers of the transport programs.

8. You have just started a new instance with a large SGA on a busy existing server. Performance is terrible, what should you check for?   The first thing to check with a large SGA is that it isn’t being swapped out.
 
9. What OS user should be used for the first part of an Oracle installation (on UNIX)?
Expected answer: You must use root first.

10. When should the default values for Oracle initialization parameters be used as is?
Expected answer: Never

11. How many control files should you have? Where should they be located?  Level: Low
Expected answer: At least 2 on separate disk spindles. Be sure they say on separate disks, not just file systems.

12. How many redo logs should you have and how should they be configured for maximum recoverability?
Expected answer: You should have at least three groups of two redo logs with the two logs each on a separate disk spindle (mirrored by Oracle). The redo logs should not be on raw devices on UNIX if it can be avoided.


13. You have a simple application with no "hot" tables (i.e. uniform IO and access requirements). How many disks should you have assuming standard layout for SYSTEM, USER, TEMP and ROLLBACK tablespaces?
Expected answer: At least 7, see disk configuration answer above. 




Click here to Join Our Blog and Stay Updated

Sunday 1 May 2016

Performance Tuning Concepts




Below are the Basics Concepts for Performance tuning Beginners

Blocks changed per Read %:

The % Blocks changed per Read statistic indicates all blocks are retrieved for update.
Blocks Changed per Read % = (Block Changes + 100/ Logical Reads)


Recursive Call %:

Sometimes, in order to execute a SQL statement issued by a user, Oracle must issue additional statements. Such statements are called recursive calls or recursive SQL statements. For example, if you insert a row into a table that does not have enough space to hold that row, then Oracle makes recursive calls to allocate the space dynamically. Recursive calls are also generated when data dictionary information is not available in the data dictionary cache and must be retrieved from disk


1. Redo size: The amount of redo generated during the report.

2. Logical Reads:
 Calculated as
Consistent Gets XE "Consistent Gets" + DB Block Gets XE "DB Block Gets" = Logical Reads

Block changes:
The number of blocks modified during the sample interval.


Physical Reads:
 The number of requests for a block that caused a physical I/O operation.

3. Physical Writes:
 Number of physical writes performed.


User Calls:
Number of user queries generated.

4. Parses: The total of all parses; both hard and soft.

5. Hard Parses:
The parses requiring a completely new parse of the SQL statement. These consume both latches and shared pool area.

6. Soft Parses:
Soft parses are not listed but derived by subtracting the hard parses from parses. A soft parse reuses a previous hard parse; hence it consumes from far fewer resources.

7. Sorts, Logons, Executes and Transactions: All self-explanatory.