ORA-01940 : Cannot Drop a User that is Currently Connected

SQL>drop user myuser cascade;
drop user username cascade

*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected


SQL>select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = 'myuser' and p.addr (+) = s.paddr;

SID           SERIAL# STATUS    SPID
----------   ----------    --------       ------------
159               29          INACTIVE 12349

SQL>alter system kill session '159,29';

System altered.

or

SQL>!kill -9 12349            - (kill the process in OS level)


SQL>drop user username cascade;


User dropped.

Tablespace Administration

Tablespace is part of the Oracle database architecture logic [at first glance, the structure of the Oracle database logic is tablespace, segment, extent, and block]. Tablespace used as a (storage) for the segment. Segment is a database object that has the data. Which includes segment is table, index, cluster, rollback (undo), lobsegment, lobindex, table partition, index partition, partition lob, temporary segments, etc.. Use the following query to see the segment types are available in our database

SQL> select distinct SEGMENT_TYPE from dba_segments;

Physically, tablespace consists of one or more datafiles. Information about the tablespace is in view v $ tablespace, dba_tablespaces, dba_data_files, dba_temp_files, etc..

Use the following command to see the types of tablespace

SQL> select distinct CONTENTS from dba_tablespaces;

Based on the results of the query, the following three types of tablespaces:

• UNDO. To save the rollback (undo) segment
• TEMPORARY. To save the temporary segment 
• PERMANENT. To save the segment in addition to the two above (eg, tables, indexes)

UNDO TABLESPACE 

Examples made ​​with the Undo Tablespace undotbs2 name, datafile / oradata/oracle/ts_bak/undotbs201.dbf, file size 10M. Do not forget to add an undo option after create.

SQL> create undo tablespace undotbs2 datafile '/oradata/oracle/ts_bak /undotbs201.dbf' size 10m;

To add (increase size / space) can be done by increasing the size of the datafile or add datafile

SQL> alter database datafile '/oradata/oracle/ts_bak/undotbs201.dbf' resize 20m;
SQL> alter tablespace undotbs2 add datafile '/oradata/oracle/ts_bak /undotbs202.dbf' size 10m; 

To see the size of the datafile and tablespace UNDOTBS2

SQL> select file_name,bytes from dba_data_files where tablespace_name='UNDOTBS2';

To see the free space of each datafile of the tablespace UNDOTBS2

SQL> select a.name, sum(b.bytes) from v$datafile a, dba_free_space b where a.file#=b.file_id and b.TABLESPACE_NAME='UNDOTBS2' group by a.name;

Untuk melihat undo tablespace yang aktif saat ini gunakan

SQL> show parameter undo_tablespace

Untuk mengubah undo_tablespace ke tablespace yang baru saja kita buat

SQL> alter system set undo_tablespace=UNDOTBS2;

TEMPORARY TABLESPACE

Example making temporay tablespace with name temp2, tempfile / oradata/oracle/ts/temp21.dbf, file size 10M. Do not forget the post-create temporary option, and instead use a tempfile datafile.

SQL> create temporary tablespace temp2 tempfile '/oradata/oracle/ts/temp21.dbf' size 10m;

To add (increase size / space) to do with the size of the tempfile elevates or add tempfile

SQL> alter database tempfile '/oradata/oracle/ts/temp21.dbf' resize 20m;
SQL> alter tablespace temp2 add tempfile '/oradata/oracle/ts/temp22.dbf' size 10m;

 To see the temp files (files belonging TEMPORARY tablespace) and size. For example, suppose name is TEMP TEMPORARY tablespace:

 SQL> select file_name,bytes from dba_temp_files where tablespace_name='TEMP';

To view free spacenya

 SQL> select a.name, sum(b.BYTES_FREE) from v$tempfile a, V$TEMP_SPACE_HEADER b where a.file#=b.file_id and b.TABLESPACE_NAME='TEMP' group by a.name;

To view the temporary tablespace is used as the database is DEFAULT

SQL> select PROPERTY_VALUE from database_properties where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

To change the default temporary tablespace to a tablespace we just created

SQL> alter database default temporary tablespace temp2;

PERMANENT TABLESPACE

Example of making permanent tablespace with the name DATA datafile / oradata/oracle/ts_bak/data01.dbf, file size 10M.

SQL> create tablespace DATA datafile '/oradata/oracle/ts_bak/data01.dbf' size 10m;

To add (increase size / space) to do with the size of the datafile elevates or add datafiles. How exactly the same as the UNDO tablespace

SQL> alter database datafile '/oradata/oracle/ts_bak/data01.dbf' resize 20m;
SQL> alter tablespace DATA add datafile '/oradata/oracle/ts_bak/data02.dbf' size 10m;

To view the datafile, size, and free size of PERMANENT tablespace; way as to UNDO tablespace, which use view dba_data_files, v $ datafile, and dba_free_space.
To view the permanent tablespace used as DEFAULT in the database is

SQL> select PROPERTY_VALUE from database_properties where PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE';

To change the default permanent tablespace to a tablespace we just created

SQL> alter database default tablespace data;

REDUCING THE SIZE OF TABLESPACE

Done by reducing the size of datafilenya. The command to reduce the size is the same as the command to add size, the point is to change the size (RESIZE). Do not forget, to use the temporary tablespace tempfile; for PERMANENT UNDO tablespace and the same, use the datafile.

SQL> alter database tempfile '/oradata/oracle/ts/temp21.dbf' resize 20m;
SQL> alter database datafile '/oradata/oracle/ts/undotbs1.dbf' resize 20m;

important notes
Reduced size (resize) can not be done on the block below the high water mark. High water mark is the highest ever position of block used for extents. Sometime later I talk about this high water mark. Execution error when resizing will be done under the High water mark:

ORA-03297: file contains used data beyond requested RESIZE value

Its best practice, if for example datafile size 4G, and we want to lower the size of her, do it gradually (derived 100M - 100M) to find the size (high water mark) as appropriate.
 
Done by deleting temp files
For security reasons, the datafile can not be deleted. Remember, referred datafile is' files and UNDO tablespace PERMANENT.

SQL> alter database
datafile '/oradata/oracle/ts/test02.dbf' drop;
ERROR at line 1:
ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected

While the temp files get deleted (file belongs to tablespace TEMPORARY) because this file does not contain data. With the record, at least keep 1 tempfile.

SQL> alter database tempfile '/oradata/oracle/ts/temp02.dbf2' drop;


Reference : http://rohmad.net/2008/06/04/administrasi-tablespace/

Setting the database into archivelog mode

In the Oracle database, all transactions on-record (stored) in the log file. In one instance, there are at least two groups logfile. The work is circular. If the logfile is full, the transaction log is stored in the next. After all the logs filled, the oldest log is overwritten (rewrite), of course, by removing the content (content) before. Of course, we will lose track of the transaction that is in the logfile.

In a database with archivelog mode, before logfile rewritten, its content is copied (backup) before the archived log. Therefore we do not lose track of transactions recorded in the log are rewritten.

Archived logs are used for database recovery. If we are to restore from the backup offline, the data can be retrieved when the data was performed off-line backup. So, if a full backup made ​​a month ago, then the data can be saved (taken) of data a month ago is it.

 
Unlike when we restore from the online backup. After the backup file be restored, then archived logs that formed after the online backup (which contains the transaction record) in the re-apply (term recovery). So that we can get the data until the last archived log, or just prior to the disaster (damage to the database).  

To see if the database is in archivelog mode or not

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oradata/oracle/ts/arc
Oldest online log sequence 56
Next log sequence to archive 58
Current log sequence 58
 

In the example above, the model database archivelog yet. To enable archivelog mode, run the following command:

SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;

See, now is archivelog mode database

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/oracle/ts/arc
Oldest online log sequence 56
Next log sequence to archive 58
Current log sequence 58

Note:
The command "alter database archivelog" is to create a database into ARCHIVELOG mode. In order to archive log file is done in two ways:

• manual 
• automatic

Manual selection is rare, except for specific purposes, such as learning. All production databases always choose the automatic.    

Startup dan Shutdown Instance in Oracle

Startup dan Shutdown Instance in Oracle

Administration (activity) that we can do in the instance is startup, shutdown, and alter. In general, the startup process is as follows:

1. Database off (shutdown)
Background process has not gone up. Memory has not been allocated
2.  Nomount
Backgroung process is increased. Memory allocated
3. Mount
Instance reading the control file. Control file contains the configuration database. Instance has not read the data file.
4 Open
Instance already read the data file (header). Database readily accessible

 Command Startup

startup
startup open
startup nomount
startup mount
startup force


Command "startup" without argument, by default is "startup open"
Command "startup force" is just the same with "shutdown abort" and then "startup"

 Command Shutdown

shutdown normal
shutdown transactional
shutdown immediate
shutdown abort

Here is a comparison of the shutdown process normal (N), transactional (T), immediate (I), and abort (A):

Proses	Shutdown
	A	I	T	N
Allowed new connection	No	No	No	No
wait until all sessions disconnect	No	No	No	Yes
wait until all transactions are complete	No	No	Yes	Yes
perform checkpoint and close file	No	Yes	Yes	Yes

reference : http://rohmad.net/2008/05/14/startup-dan-shutdown-instance/

Instalasion Oracle 11g on RHEL 5

Oracle Database DBMS is one of today's popular and widely used by large companies to run their business. This time I try to share how to install Oracle DBMS on RHEL 5 operating system (Redhat Enterprise Linux 5). Well actually the principle of oracle installation on linux and in windows is almost no difference. It's just to install oracle on linux we need to configure some parts manually.

Minimum hardware requirements:

32-bit (x86)

• x86 CPU
• 1 GB RAM
• 10 GB ruang disk (3.5 GB for the Oracle software + 1.5 GB for the database + 3 GB for OEL5 + 2 GB swap)

64-bit (x86_64)

• x86_64 CPU
• 1 GB RAM
• 10.5 GB ruang disk (4 GB for the Oracle software + 1.5 GB for the database + 3 GB for OEL5 + 2 GB swap)

Note: Linux and Oracle must be installed on the same architecture. This means that 32-bit Oracle will only be able to run on Linux 32-bit as well as 64-bit Oracle will only run on 64-bit Linux. Well there is a trick to install Oracle, the calculation of the size of the swap space is usually adjusted to the size of the RAM in order to avoid bottlenecks. Here is a comparison of the size of the swap to the size of RAM.

• Between 1024 MB and 2048 MB, swap = 2x RAM
• Between 2049 MB and 8192 MB, swap = 1.5x RAM
• Above 8192 MB, swap = 0.75x RAM.

 To see the size of the swap, can use the following command:

[root@localhost ~]# grep SwapTotal /proc/meminfo
 SwapTotal:     2096472 kB

The assumption this time is on the computer we have installed RHEL 5 32-bit. So straight to step Oracle installation :)
Well, to check if we are using a Linux 32-bit or 64-bit version of the kernel you can check by using the command: 

#Output dari Linux 32-bit

[root@localhost ~]# uname -r

2.6.18-128.el5

# Output dari Linux 64-bit

[root@localhost ~]# uname -r

2.6.18-8.el5 x86_64

1. Installing the required packages
To Install Oracle on linux, in requiring the package is not installed by default on linux. There are some differences in the packages that need between 32-bit and 64-bit.

32-bit
The following are thre required packages before installing Oracle on Linux-32bit. Version the package can be more or equal to what written in the following list.

• compat-libstdc++-33-3.2.3-61
• elfutils-libelf-0.125-3.el5
• elfutils-libelf-devel-0.125-3.el5
• glibc-2.5-12
• glibc-devel-2.5-12
• glibc-common-2.5-12
• gcc-4.1.1-52.el5
• gcc-c++-4.1.1-52.el5
• kernel-headers
• libgcc-4.1.1-52.el5
• libaio-0.3.106-3.2
• libaio-devel-0.3.106-3.2
• libstdc++-4.1.1-52.el5
• libstdc++-devel-4.1.1-52.el5
• unixODBC-2.2.11-7.1
• unixODBC-devel-2.2.11-7.1
• sysstat-7.0.0-3.el5
• binutils-2.17.50.0.6-2.el5
• make-3.81-1.1

To install the package, insert the DVD from RHEL5 installer then navigate our working directory on the DVD in the Server folder.

[root@localhost ~]# cd /media/RHEL_5.3\ i386\ DVD/

[root@localhost RHEL_5.3 i386 DVD]# cd Server/

[root@localhost Server]# rpm -ivh elfutils-libelf-devel* glibc-devel-2* glibc-headers-2* gcc-4* libgomp-4* gcc-c++-4* libstdc++-devel-4* compat-libstdc++-33* libaio-devel* sysstat* unixODBC*

Or you can also use the command yum. Installing packages using yum will be easier, because all the package dependencies automatically be directly installed.

[root@localhost ~]# yum install elfutils-libelf-devel* glibc-devel-2* glibc-headers-2* gcc-4* libgomp-4* gcc-c++-4* libstdc++-devel-4* compat-libstdc++-33* libaio-devel* sysstat* unixODBC*  

64-bit
The following are the required packages before installing Oracle on Linux 32-bit. In general almost the same, but there are some differences. For how to install the package can follow with an additional 32-bit package is needed. List packages that are required are as follows:  

• binutils-2.17.50.0.6
• compat-libstdc++-33-3.2.3
• compat-libstdc++-33-3.2.3 (32 bit)
• elfutils-libelf-0.125
• elfutils-libelf-devel-0.125
• gcc-4.1.1
• gcc-c++-4.1.1
• glibc-2.5-12
• glibc-2.5-12 (32 bit)
• glibc-common-2.5
• glibc-devel-2.5
• glibc-devel-2.5-12 (32 bit)
• libaio-0.3.106
• libaio-0.3.106 (32 bit)
• libaio-devel-0.3.106
• libgcc-4.1.1
• libgcc-4.1.1 (32 bit)
• libstdc++-4.1.1
• libstdc++-4.1.1 (32 bit)
• libstdc++-devel 4.1.1
• make-3.81
• sysstat-7.0.0

Configuration Linux for Oracle
Once all the required packages installed, it is now time to configure Linux to be installed oracle 

Make direktori
Create a directory in which the location of the Oracle DBMS will be placed. For example we will place the directory oracle / oracle / oracle11g

[root@localhost ~]# mkdir -p /app

[root@localhost ~]# mkdir -p /app/oracle11g

[root@localhost ~]# chown -R oracle:oinstall /app/oracle11g

[root@localhost ~]# chmod -R 775 /app




Configure the Linux kernel parameters

Oracle Database 11g requires minimum kernel parameter settings as below. But if the setting previously possessed greater value then do not need to be changed. Usually RHEL 5 is defining kernel.shmall and kernel.shmmax. No need to restart the computer after you change the kernel parameter values​​.

kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=4194304
net.core.wmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_max=262144

Use the following command to change the parameters of the Linux kernel

vi /etc/sysctl.conf <<EOF

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default=262144

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default=4194304

net.core.wmem_default=262144

net.core.rmem_max=4194304

net.core.wmem_max=262144

EOF

In order for these settings can be used, just use the command sysctl-p

Setting shell limits for user oracle 
Change the file /etc/security/limits.conf and add the following line:

oracle    soft  nproc  2047
oracle    hard  nproc  16384
oracle    soft  nofile  1024
oracle    hard  nofile  65536

Change the file /etc/pam.d/login and add the following line:

session required pam_limits.so

Change /etc/profile and add the following line:

if [ \$USER = "oracle" ]; then
 if [ \$SHELL = "/bin/ksh" ]; then
 ulimit -p 16384
 ulimit -n 65536
 else
 ulimit -u 16384 -n 65536
 fi
 umask 022
fi

Change the file /etc/csh.login and add the following line:

if ( \$USER == "oracle" ) then
 limit maxproc 16384
 limit descriptors 65536
 umask 022
endif

Create user dan group Oracle

The next stage is to create a group on Linux and the user that will be used to install and maintain Oracle database. Users will be created is a group oracle oinstall and dba

#groupadd oinstall -g 200
#groupadd dba -g 201
#useradd -u 200 -g dba -G oinstall -s /bin/bash oracle
#passwd oracle
Changing password for user oracle.
New UNIX password: *****
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password: *****
passwd: all authentication tokens updated successfully.

Configuring Oracle environment 

The last and not least no setting oracle environment, so that the oracle user can easily use the features in oracle. The trick is to add the following script into the file / home / oracle / .bash_profile 

login with TUI, 

su - oracle

vi .bash_profile

export EDITOR=vi

export ORACLE_SID=orcl

export ORACLE_BASE=/app/oracle11g

export ORACLE_HOME=/app/oracle11g/product/11.2.0/db

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PAT=$PATH:$ORACLE_HOME/bin:usr/bin:/usr/sbin:/usr/local/bin

:/usr/X1146/bin

unmask 022

set -o vi 

Download dan Ekstrak Oracle Installer 

Oracle can be downloaded for free on the Oracle Web site development and testing license. However, Oracle does not provide support for this linsensi. For more details about the license can be viewed on OTN. 

[root@localhost ~]# mkdir OraInstaller [root@localhost ~]# cd OraInstaller [root@localhost OraInstaller]# cd unzip linux_11R2_database_1of2.zip [root@localhost OraInstaller]# cd unzip linux_11R2_database_2of2.zip [root@localhost OraInstaller]# chown -R oracle database/ [root@localhost OraInstaller]# chmod 777 -R database

Instalasi Oracle Well, it's time to install the oracle. Do not forget to use the GUI in this session, as required for installation. To install Oracle, log in as oracle and go to the directory where the installer is the location and run the installer. [oracle@localhost ~]$ cd /root/OraInstaller/database [oracle@localhost database]$ ./runInstaller   Step 1 installation Oracle  Step 2 installation Oracle Step 3 installation Oracle   Step 4 installation Oracle  Step 5 installation Oracle   Step 6 installation Oracle      Step 7 installation Oracle    Step 8 installation Oracle    Step 9 installation Oracle    Step 10 installation Oracle    Step 11 installation Oracle      Don't Klik OK  At the last stage, run the script that must run as root. Then think about the result will be like below: [oracle@localhost database]$ su - root Password: [root@localhost ~]# cd /app/oraInventory [root@localhost oraInventory]# ./orainstRoot.sh Changing permissions of /home/oracle/app/oraInventorya. Adding read,write permissions for group. Removing read,write,execute permissions for world. Changing groupname of /app/oraInventory to oinstall. The execution of the script is complete. [root@localhost oraInventory]# cd /app/oracle/product/11.2.0/dbhome_1/ [root@localhost dbhome_2]# ./root.sh Running Oracle 11g root.sh script... The following environment variables are set as:              ORACLE_OWNER= oracle              ORACLE_HOME=  /app/oracle/product/11.2.0/dbhome_1 Enter the full pathname of the local bin directory: [/usr/local/bin]:    Copying dbhome to /usr/local/bin ...    Copying oraenv to /usr/local/bin ...    Copying coraenv to /usr/local/bin ... Creating /etc/oratab file... Entries will be added to the /etc/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root.sh script. Now product-specific root actions will be performed. Finished product-specific root actions.            After run the above script, then klik ok Now Oracle have been configuration and ready to use good luck guys :-) reference :  http://ifhayz.wordpress.com/2010/11/20/instalasi-oracle-11g-pada-rhel-5/ http://www.oracle.com/technetwork/articles/smiley-11gr1-install-092791.html