Akshay Narang

Below query to check the wait events in the database col EVENT for a50 set head on feed on set lines 134 pages 2000 select event,count(*) from  gv$session where event not like 'Streams%' and wait_class <> 'Idle' group by event order by 2;

How to delete Oracle database using command line You can follow below steps to drop a Oracle database using command line: 1. Connect to SQLPLUS using sysdba.     sqlplus / as sysdba 2. Shutdown the database if it is running.     SQL> shutdown immediate 3. Mount the database in restrict mode.     SQL> startup mount exclusive restrict; 4. Execute the "Drop Database" command.           SQL> drop database;     SQL> quit 5. Delete the database service using "oradim", if running on Windows.      c:\>  oradim -delete -sid <sid_name>

Add Disks to Existing ASM Disk Group In this blog I will show how to add new disks to an existing ASM Disk group. This also contains the steps to perform the migration from existing to the new storage system. In order to add the disk to the ASM disk group, you will first need to configure these disk using the operating system commands. I have provided the steps to configure the disks on AIX system in my blog " Configure Oracle ASM Disks on AIX" Adding New Disks to DATA Disk Group (Storage Migration for DATA Disk Group) Login to your ASM instance $ sqlplus / as sysasm If the name of the new disk is in different format from the existing disk, the modify the asm_diskstring parameter to identify the new disks. In my below example /dev/ora_data* is the format of the existing disks and /dev/new_disk* is the naming format of the newly configured disks. You should not modify this parameter unless the naming format changes. SQL> alter system set asm_diskstring = '/dev/ora_data*...

Oracle Vote & OCR Disk Migration to New Storage Vote Disk Migration Prepare the new disks to be used as vote disk by Operating System command/tools. Procedure to configure new disks on AIX system is listed on my blog " Configure Oracle ASM Disks on AIX " Shutdown the Oracle Clusterware (crsctl stop crs as root) on all nodes before making any modification to the voting disk.  Determine the current voting disk location using.  Issue the commands as root user. # crsctl query css votedisk You would need to shutdown the Clusterware to replace voting disk # crsctl stop crs Add the New voting Disk # crsctl add css votedisk '/dev/ora_vote3' -force Delete the voting disk on old storage. # crsctl delete css votedisk '/dev/ora_vote1' –force Start the Clusterware # crsctl start crs Verify that new voting disks are added: # crsctl query css votedisk OCR Migration Issue the following commands as root user. Identify the current ocr devices.  # ocrcheck To...

Configure Oracle ASM Disks on AIX You can use below steps to configure the new disks for ASM after the raw disks are added to your AIX server by your System/Infrastructure team experts: # /usr/sbin/lsdev -Cc disk The output from this command is similar to the following: hdisk9 Available 02-T1-01 PURE MPIO Drive (Fibre) hdisk10 Available 02-T1-01 PURE MPIO Drive (Fibre) If the new disks are not listed as available, then use the below command to configure the new disks. # /usr/sbin/cfgmgr Enter the following command to identify the device names for the physical disks that you want to use: # /usr/sbin/lspv | grep -i none This command displays information similar to the following for each disk that is not configured in a volume group: hdisk9     0000014652369872   None In the above example hdisk9 is the device name and  0000014652369872  is the physical volume ID (PVID). The disks that you want to use may have a PVID, but they must not belong to a volu...

The purpose of Product_user_profile table of SYSTEM is to control users from executing SQL commands. For example, if you don't want one of your users to execute DELETE command, this can be implemented in this table. Below example shows the usage of Product_User_Profile Connect as user system SQL> insert into sqlplus_product_profile(product, userid, attribute, char_value) values('SQL*Plus','AKS','DELETE','DISABLED'); 1 row created. SQL> commit; Commit complete. [oracle@linux38 ~]$ sqlplus aks SQL*Plus: Release 11.2.0.2.0 Production on Fri May 6 10:28:17 2016 Copyright (c) 1982, 2010, Oracle.  All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> delete from test_table; SP2-0544: Command "delete" disabled in Product User Profile

Below are the manual Steps to create an Oracle Database: Create an Initialization parameter file. You can copy it from another database or create it using samples provided inside $ORACLE_HOME/dbs/ with the name init.ora. Below is an example of text based parameter file. *.__db_cache_size=322961408 *.__java_pool_size=4194304 *.__large_pool_size=4194304 *.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment *.__pga_aggregate_target=339738624 *.__sga_target=503316480 *.__shared_io_pool_size=0 *.__shared_pool_size=163577856 *.__streams_pool_size=0 *.audit_file_dest='/opt/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/opt/oracle/oradata/orcl/control01.ctl','/opt/oracle/flash_recovery_area/orcl/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.db_recovery_file_dest='/opt/oracle/flash_recovery_area' *.db_recove...

Before you install oracle you must complete several task as the root user Check memory and swap space Memory minimum recommended: 2GB of RAM grep MemTotal /proc/meminfo Check swap space grep SwapTotal /proc/meminfo It is recommended to keep swap space as double of the memory, maximum 16 GB. /tmp should have atleast 1GB of free space as the installer creates temporary files inside /tmp. Check that below rpm's are installed. You can use "rpm -qa" command to verify the rpm's. Create the required OS groups and user. groupadd oinstall groupadd dba useradd -g oinstall -G dba oracle passwd oracle Execute the sysctl -a command to see the that the required kernel parameters are set Installing Oracle 11g with software only option. Set the DISPLAY and start the runInstaller program and follow the instructions.

Does Log writer writes both uncommitted and committed data to redo log files? Answer is yes Log writer writes the information from log buffer to redo log files when log buffer is 1/3rd full or 1 MB of redo is generated or every 3 seconds or at every commit. Let's take an example of a user "A" who is executing a huge transaction which generated a lot of redo records and log buffer went 1/3rd full or 1 MB full. Even if the user has not performed a commit, log writer has to write all the contents of log buffer to log files. Let's take another example where one user performed a commit on his transaction and as the rule says LGWR has to write at every commit. At this point with the committed data of the particular session there can also be uncommitted data of other sessions or transactions which will also be written to redo log files. Does DBWR writes both uncommitted and committed data to data files? Answer is yes Redo for dirty blocks can’t be overwritten. ...

SQL statement is parsed in a sequence of steps.  Syntax Check : is the statement a valid one? Does it make sense given the SQL grammar documented in the SQL Reference Manual. Does it follow all of the rules for SQL. Semantic Check : Going beyond the syntax ? is the statement valid in light of the objects in the database (do the tables and columns referenced exist). Do you have access to the objects ? are the proper privileges in place? Are there ambiguities in the statement ? The next step in the parse operation is to see if the statement we are currently parsing has already in fact been processed by some other session. If it has ? we may be in luck here, we can skip the next two steps in the process. If we can skip these next two steps in the process, we have done what is known as a Soft Parse . If we cannot, if we must do all of the steps, we are performing what is known as a Hard Parse . Below are only done durin...

I recently faced an issue when one of my production database which was running very slow. A simple select statement on dictionary tables were taking too much time. Size of one of my tablespace was reaching 100% and the "Alter Tablespace add datafile" command juts to add 1 GB of datafile with autoextend on also went into hang mode. My initial investigation started with checking CPU and memory of the server. CPU was 80% free and also there was enough memory available on the server. Next step was to check the active wait events on the database using below query: select event,count(*) from  gv$session where event not like 'Streams%' and wait_class <> 'Idle' group by event order by 2 / Results of the above query displayed that almost all of my database sessions were waiting on wait event " log file switch (checkpoint incomplete) ". Even my session from where I executed the command to add the datafile was waiting on this event. Along wit...

Oracle is a relational database management system. It is a mechanism to store and retrieve user data. Data is stored logically in oracle objects such as tables and physically within operating system files which are refered as datafiles in Oracle. In order to retrieve or view the data we can use various programing languages. SQL is the widely used language to query or modify or add more data within an oracle database. Oracle Server consists of an  Instance and a database . An instance is associated to one database. In a RAC environment there can be more than one instances associated to a single database. An instance mainly consists of two parts.  Memory allocated to an oracle server, ie., SGA and Oracle background processes . System global area or SGA consists of below memory structures: DB Buffer: Temporarily stores users queried blocks. These blocks are managed using a LRU queue. A block which is most frequently used is kept on the most recent used (MRU) side...

When Oracle database is started, it goes through two different stages before it is finally opened for users/applications for queries or updates. Below is the description of each stage and the commands that can be used to bring the database in these stages: NOMOUNT Command: STARTUP NOMOUNT This is first step of opening the database. Oracle will search for the parameter file with the name sp<sid>.ora (binary file) or init<sid>.ora (text based parameter file). Oracle reads the spfile or pfile and an instance is created with the name specified in ORACLE_SID variable. Instance consists of two things: System Global Area or SGA, which is the memory allocated to Oracle Server as per the parameters defined in the initialization parameter file. Background processes (SMON, PMON, DBWn, LGWn, CKPT etc.) which are mandatory for running a Oracle database. MOUNT Command: STARTUP MOUNT or ALTER DATABASE MOUNT (If database is in NOMOUNT stage). ...

Below are the steps to connect to an Oracle database. These can be helpful to the people who are new to the Oracle world. 1. Login to unix user which has the permissions to connect to database. Most of the times it is with the name Oracle but it may vary from one installation to other. This unix user is part of the dba group for administrator actions and oinstall group to manage the software (install, software patching etc.). 2. Ensure your environment variables are set properly. Below are the environment variables and their description that should be set: ORACLE_HOME - Location to the Oracle database software. ORACLE_SID - Name of the Oracle Instance to connect. Database name can be different that of the instance. This is the case in ORACLE RAC environment. PATH - PATH environment should be modified to also include the location of Oracle binaries like sqlplus, lsnrctl etc. For example if your database name is TEST and location of the Oracle software is /home/oracle/product/...

In case of incomplete recovery of the database it is always required to open the database using RESETLOGS option. But what if I fired the "Recover Database Until Cancel using Backup Controlfile" command on a database which just got aborted. Below test explains that resetlogs is always required after firing recover database ....... using backup controlfile. A running database is brought down using ABORT option. SQL> shutdown abort ORACLE instance shut down. [oracle]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Thu Jul 30 15:51:28 2014 Copyright (c) 1982, 2010, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 1620115456 bytes Fixed Size                  2226952 bytes Variable Size            1241515256 bytes Database Buffers          369098752 bytes Redo ...

Tuning has always been a hard nut to crack for Oracle DBA's. Every DBA who had spent time dealing with databases would have faced situations when he got complaints about slowness in the database or most of the time a query which was running fine in the past but has been degraded in performance. It is not always advisable to extract the AWR or statspack report and go through each section for digging into such issues. Oracle has provided some very useful database views which can help the administrators in such situations. Below are some steps and queries making use of these views which can help administrators to find the cause of the slowness in the database. Rather than checking the full AWR report always identify the bottle necks and try fixing those. Check for Active Wait Events in the Database. select event,count(*) from  gv$session where event not like 'Streams%' and wait_class <> 'Idle' group by event order by 2; Find the most executed sql’s w...