Installation of Oracle Database 11g Release 2 on RHEL 5

Pre-Installation Configurations

Check Hardware Requirements

-- Check Physical RAM.
# grep MemTotal /proc/meminfo
MemTotal:      2075424 kB
/*
 We need at least 1GB of physical RAM. 
 In my case I have 2GB.
*/

-- Check Swap Space.
# grep SwapTotal /proc/meminfo
SwapTotal:     3148732 kB

/*
 RAM between 1GB and 2GB then Swap = 1.5 times the size of RAM
 RAM between 2GB and 16GB then Swap = equal to the size of RAM
 RAM more than 16GB then Swap Size = 16GB
 Since my RAM is 2GB, so I have 3GB of Swap Size.
*/

# df -h /dev/shm/
Filesystem            Size  Used Avail Use% Mounted on
tmpfs                1014M     0 1014M   0% /dev/shm


/*
 What ever you are going to use your Automatic Memory Management 
 Targets later on this database, you need to have shared memory 
 file system size at lease equal to those settings. In my case I 
 have plans to use memory management targets lower then 1000M 
 so 1014 M of shared memory file system is sufficient for me. 
 But if you would like to have a bigger shared memory (/dev/shm) 
 size then do the following:
*/

# mount -t tmpfs tmpfs -o size=1500m /dev/shm
# df -h /dev/shm/
Filesystem            Size  Used Avail Use% Mounted on
tmpfs                 1.5G     0  1.5G   0% /dev/shm

-- Check space available in /tmp
# df -h /tmp
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda5             1.5G   35M  1.4G   3% /tmp

/* 
  You need to have at least 1GB of space in the /tmp directory.
  Make sure you have 1GB in the column "Avail" in the above output.
  In my case I have 1.4G space available in /tmp.
*/

-- Check space for Oracle Software and pre configured database.
-- I have created a separate partition "/u01" for Oracle Software 
-- and database files
# df -h /u01
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              15G  166M   14G   2% /u01

-- I have 14G available space on the partition /u01.
/*
  Space requirement for Oracle 11g Release 2 Software:
     Enterprise Edition      3.95G
     Standard Edition       3.88G
  
  Space requirement for Oracle Database Files:
     1.7 G 
     (
      creating a database is always optional with oracle installation. 
      Can be done later.
     )
*/

/*
 To install Oracle 11g Release 2 your system should be running
 on at least 1024x768 screen resolution.
*/
# xdpyinfo | grep 'dimensions:'
  dimensions:    1280x720 pixels (433x244 millimeters)

/*
 To install Oracle 11g Release 2 on RHEL 5 you should be running
 a kernel version 2.6.18 or later.
*/
# uname -r
2.6.18-194.el5

Meeting the software requirements

Make sure that there is an entry in /etc/hosts file for your machine like this

[IP-address]  [fully-qualified-machine-name]  [machine-name]

/* 
 Where "fully-qualified-machine-name" is your
 "machine_name"."domain_name"
*/

Next we need to adjust the Linux Kernel Parameters to support Oracle.
Open /etc/sysctl.conf and add the following lines

# Oracle settings

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
net.ipv4.tcp_wmem = 262144 262144 262144
net.ipv4.tcp_rmem = 4194304 4194304 4194304

Note: You may find the parameters mentioned above already present in the /etc/sysctl.conf file. If they have a value lower then mentioned above then make sure you change it, but if they have a larger value then perhaps its safe to leave it as is

-- Make the kernel parameters changes effective immediately:
# /sbin/sysctl -p

-- Verify the parameters are changed or not? 
# /sbin/sysctl -a | grep name_of_kernel_parameter -- e.g. shmall

Now setup User that we will use as Oracle owner and the groups that it will need for installing and managing Oracle

/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba
/usr/sbin/groupadd oper
/usr/sbin/useradd -g oinstall -G dba,oper oracle
/usr/bin/passwd oracle

Create directories where the Oracle Software and database will be installed

mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle:oinstall /u01
chmod -R 0775 /u01

/*
 The above directory structure is designed to comply with OFA 
 (Oracle Flexible Architecture) i.e. a directory structure to
 support multiple oracle software installations.

 ORACLE_BASE=/u01/app/oracle 
 -- Base directory for all oracle installations.

 ORACLE_HOME=ORACLE_BASE/product/11.2.0/db_1 -- 11.2.0 installation 1.
 ORACLE_HOME=ORACLE_BASE/product/11.2.0/db_2 -- 11.2.0 installation 2.
 ORACLE_HOME=ORACLE_BASE/product/10.2.0/db_1 -- 10.2.0 installation 1.

 and so on....
*/

Now set the shell limits for the user Oracle.
Open /etc/security/limits.conf and add these lines.

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

Open /etc/pam.d/login and add the following line if it is already not there
session    required     pam_limits.so
Disable secure linux by editing the /etc/selinux/config file, making sure the SELINUX flag is set as follows
SELINUX=disabled

If you leave SELINUX=enforcing then you may get an error later while starting sqlplus:
sqlplus: error while loading shared libraries:
/usr/lib/oracle/default/client64/lib/libclntsh.so.11.1: cannot restore segment
prot after reloc: Permission denied

Now we need to make sure we have all necessary packages for Oracle. Put your Linux Media into DVD and go to the "Server" directory

cd /dvd_mount_point/Server
if you have a problem then below link may help you.

http://openbysource.blogspot.com/2007/02/configuring-yum-in-rhel5-for-dvd-source.html

cd /me (press tab key)
cd /media/RH press tab key
cd /media/RHEL_5.1\ i386\ DVD/Server

For RHEL 5 (32-bit):

rpm -Uvih --nodeps binutils-2*
rpm -Uvih --nodeps compat-libstdc++-33*
rpm -Uvih --nodeps elfutils-libelf-0*
rpm -Uvih --nodeps elfutils-libelf-devel-0*
rpm -Uvih --nodeps elfutils-libelf-devel-static-0*
rpm -Uvih --nodeps gcc-4*
rpm -Uvih --nodeps gcc-c++-4*
rpm -Uvih --nodeps glibc-2*`uname -p`*
rpm -Uvih --nodeps glibc-common-2*
rpm -Uvih --nodeps glibc-devel-2*
rpm -Uvih --nodeps glibc-headers-2*
rpm -Uvih --nodeps kernel-headers-2*
rpm -Uvih --nodeps ksh-20*
rpm -Uvih --nodeps libaio-0*
rpm -Uvih --nodeps libaio-devel-0*
rpm -Uvih --nodeps libgcc-4*
rpm -Uvih --nodeps libgomp-4*
rpm -Uvih --nodeps libstdc++-4*
rpm -Uvih --nodeps libstdc++-devel-4*
rpm -Uvih --nodeps make-3*
rpm -Uvih --nodeps numactl-devel-0*
rpm -Uvih --nodeps sysstat-7*
rpm -Uvih --nodeps unixODBC-2*
rpm -Uvih --nodeps unixODBC-devel-2*

For RHEL 5 (64-bit):

rpm -Uvih binutils-2*`uname -p`*
rpm -Uvih compat-libstdc++-33*`uname -p`*
rpm -Uvih compat-libstdc++-33*i386*
rpm -Uvih elfutils-libelf-0*`uname -p`*
rpm -Uvih elfutils-libelf-devel-0*`uname -p`*
rpm -Uvih gcc-4*`uname -p`*
rpm -Uvih gcc-c++-4*`uname -p`*
rpm -Uvih glibc-2*`uname -p`*
rpm -Uvih glibc-2*i686*
rpm -Uvih glibc-common-2*`uname -p`*
rpm -Uvih glibc-devel-2*`uname -p`*
rpm -Uvih glibc-devel-2*i386*
rpm -Uvih glibc-headers-2*`uname -p`*
rpm -Uvih ksh-20*`uname -p`*
rpm -Uvih libaio-0*`uname -p`*
rpm -Uvih libaio-0*i386*
rpm -Uvih libaio-devel-0*`uname -p`*
rpm -Uvih libaio-devel-0*i386*
rpm -Uvih libgcc-4*`uname -p`*
rpm -Uvih libgcc-4*i386*
rpm -Uvih libstdc++-4*`uname -p`*
rpm -Uvih libstdc++-4*i386*
rpm -Uvih libstdc++-devel-4*`uname -p`*
rpm -Uvih make-3*`uname -p`*
rpm -Uvih numactl-devel-0*`uname -p`*
rpm -Uvih sysstat-7*`uname -p`*
rpm -Uvih unixODBC-2*`uname -p`*
rpm -Uvih unixODBC-2*i386*
rpm -Uvih unixODBC-devel-2*`uname -p`*
rpm -Uvih unixODBC-devel-2*i386*

NOTE: If you are using RHEL5 DVD then you should find them all in the "Server" directory in your DVD. And if you don't find one there you may download it from the Linux vendor's Web site.
If you have your Linux distribution in 3 CDs then these will be scattered on all three CDs in the Server directory on all CDs.

Allow the user oracle to use X server, which it will need to run Oracle Universal Installer
# xhost +SI:localuser:oracle
Now switch to the user oracle
# su - oracle

-- Let's see which shell is being used by the user Oracle.
$ echo $SHELL
/bin/bash

If the returned shell is bash then open ~/.bash_profile and add these lines.

# Oracle settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

# If /tmp doesn't have 1G space free then you can workaround it by
# pointing the variables TMP AND TMPDIR to a location where you have
# sufficient space.

ORACLE_HOSTNAME=ora11g.home.com; export ORACLE_HOSTNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=ora11g; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=$ORACLE_HOME/bin:/usr/sbin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export CLASSPATH

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

If using C shell then open ~/.login and add these lines:

# Oracle settings
setenv TMP=/tmp
setenv TMPDIR=$TMP

# If /tmp doesn't have 1G space free then you can workaround it
# by pointing the variables TMP AND TMPDIR to a location where you
# have sufficient space. Oracle will then use this directory for
# temporary files.

setenv ORACLE_HOSTNAME ora11g.home.com
setenv ORACLE_BASE /u01/app/oracle
setenv ORACLE_HOME $ORACLE_BASE/product/11.2.0/db_1
setenv ORACLE_SID ora11g
setenv ORACLE_TERM xterm
setenv PATH /usr/sbin:$PATH
setenv PATH $ORACLE_HOME/bin:$PATH

setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:/lib:/usr/lib
setenv CLASSPATH $ORACLE_HOME/JRE:$ORACLE_HOME/jlib
setenv CLASSPATH $CLASSPATH:$ORACLE_HOME/rdbms/jlib

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

Now run the following command to make these changes effective in the current session of user oracle.
-- for bash shell
$ source ~/.bash_profile
-- for C shell
$ source ~/.login

Install Oracle 11g Release 2 on RHEL 5
The environment is ready for oracle installation. Its time to prepare the oracle installation media now.
Download or Copy the oracle media to the oracle user home directory i.e. /home/oracle.
Once Download/Copy is done unzip the media as follows
$ cd /home/oracle
$ ls
linux_11gR2_database_1of2.zip linux_11gR2_database_2of2.zip
$
$ unzip linux_11gR2_database_1of2.zip
$ unzip linux_11gR2_database_2of2.zip

Once the unzip is finished go to the "database" directory unzipped in the previous step and start the Oracle Universal Installer.

$ cd /home/oracle/database/
$ ./runInstaller

The OUI (Oracle Universal Installer) should start and you should see following screens in the order given below:

1. Provide your email address and Oracle support password to get security updates from Oracle.

2. Installation options like creating a database, installing oracle software only or upgrading the database. Select "Create and configure a database".

3. Choose the system class here. Select "Server Class" it provides more advanced options.

4. Choose from creating a single node installation or RAC.

5. Choose your installation Typical or Advanced. We will go with typical at this moment.

6. If you choose typical install in previous screen, then you will see this page for Install Configurations. Provide Oracle software installation location, database files location and administrator password etc.

7. Specify the Oracle install inventory location and Operating system group "oinstall".

8. Now all prerequisite checks will be performed here and if every thing is ok you will be moved to the install summary page. You can hit the "Back" button and come back to see the status of all the checks performed.

9. Installation Summary page. Hit "Finish" to start the Installation.

10. Installation Progress. This will take several minutes and it will automatically invoke Database COnfiguration Assistant to create a database.

11. Database Configuration Assistant invoked by the installer.

12. Once DBCA has finished creating the database, it will show a page like this. It is a summary of the database that has just been created.

13. As a last step you will be asked to execute some configuration scripts as root.

Note:
link
change the below lines of above  ~/.bash_profile file by reopening in vi or other editor.
ORACLE_HOSTNAME = ora11g.home.com with your own host name and
ORACLE_SID=ora11g with your own SID name which you have entered at the time of oracle installation

Stopping an Oracle Database or Automatic Storage Management Instance

Connect as oracle user.

$ sqlplus

Enter user-name: / as sysdba

Caution:

Do not stop an Automatic Storage Management instance until you have stopped all Oracle Database instances that use that Automatic Storage Management instance to manage their storage.

To stop an Oracle Database or Automatic Storage Management instance:

To identify the SID and Oracle home directory for the instance that you want to shut down, run the following command:

On Solaris:
```
$ cat /var/opt/oracle/oratab
```
On other operating systems:
```
$ cat /etc/oratab
```
The oratab file contains lines similar to the following, which identify the SID and corresponding Oracle home directory for each database or Automatic Storage Management instance on the system:
```
sid:oracle_home_directory:[Y|N]
```
Note:
Oracle recommends that you use the plus sign (+) as the first character in the SID of Automatic Storage Management instances.
Depending on the default shell, run the oraenv or coraenv script to set the environment variables for the instance that you want to shut down:
- Bourne, Bash, or Korn shell:
```
$ . /usr/local/bin/oraenv
```
- C shell:
```
% source /usr/local/bin/coraenv
```
When prompted, specify the SID for the instance.
Run the following commands to shut down the instance:
```
$ sqlplus
SQL> CONNECT SYS as SYSDBA
Enter password: sys_password
SQL> SHUTDOWN NORMAL
```
After the instance shuts down, you can quit SQL*Plus.

Restarting an Oracle Database or Automatic Storage Management Instance

Caution:

If the database instance uses Automatic Storage Management for storage management, then you must start the Automatic Storage Management instance before you start the database instance.

To restart an Oracle Database or Automatic Storage Management instance:

If required, repeat Steps 1 and 2 of the preceding procedure to set the ORACLE_SID and ORACLE_HOME environment variables to identify the SID and Oracle home directory for the instance that you want to start.
Run the following commands to start the instance:
```
$ sqlplus
SQL> CONNECT SYS as SYSDBA
Enter password: sys_password
SQL> STARTUP
```
After the instance starts, you can exit from SQL*Plus.

Automating Database Startup and Shutdown on Linux

Link

The following represents the Oracle recommended method for automating database startup and shutdown of Oracle 9i instances on Linux, but it works equally well for Oracle 10g, 11G and 12c also. It can be used on any RHEL-style distribution, including Oracle Linux, up to an including RHEL6.

Once the instance is created, edit the "/etc/oratab" file setting the restart flag for each instance to 'Y'.

TSH1:/u01/app/oracle/product/9.2.0:Y

Next, create a file called "/etc/init.d/dbora" as the root user, containing the following.

This method can still be used under Oracle 10g, 11g and 12c, provided the "ORA_HOME" variable is amended to use the correct path and this is added to the end of the dbstart and dbshut lines. The lines to start and stop the listener can be removed under Oracle 10g Release 2 onward, as the dbstart command includes an automatic start of the listener.

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the 
# Oracle database in ORA_HOME.

#ORA_HOME=/u01/app/oracle/product/10.2.0/db_1
#ORA_HOME=/u01/app/oracle/product/11.1.0/db_1
#ORA_HOME=/u01/app/oracle/product/11.2.0/db_1
ORA_HOME=/u01/app/oracle/product/12.1.0/db_1
ORA_OWNER=oracle
export ORACLE_UNQNAME=db12c

if [ ! -f $ORA_HOME/bin/dbstart ]
then
    echo "Oracle startup: cannot start"
    exit
fi

case "$1" in
    'start')
        # Start the Oracle databases:
        # The following command assumes that the oracle login 
        # will not prompt the user for any values
        su $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
        touch /var/lock/subsys/dbora
        ;;
    'stop')
        # Stop the Oracle databases:
        # The following command assumes that the oracle login 
        # will not prompt the user for any values
        su $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
        rm -f /var/lock/subsys/dbora
        ;;
esac

Use the chmod command to set the privileges to 750.

chmod 750 /etc/init.d/dbora

Create symbolic links to the dbora script in the appropriate run-level script directories as follows.

# ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora

# ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

Next, we must create the "startup.sh" and "shutdown.sh" scripts in the "/home/oracle/scripts". First create the directory.


# mkdir -p /home/oracle/scripts
# chown oracle.oinstall /home/oracle/scripts

The "/home/oracle/scripts/startup.sh" script should contain the following commands.

#!/bin/bash

export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/app/oracle
#export ORACLE_HOSTNAME=ol6-112.localdomain
#export ORACLE_UNQNAME=DB11G
#export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_HOSTNAME=ol6-121.localdomain
export ORACLE_UNQNAME=db12c
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1
export PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH

export ORACLE_SID=db12c
ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES

# Start Listener
lsnrctl start

# Start Database
sqlplus / as sysdba << EOF
STARTUP;
EXIT;
EOF

The "/home/oracle/scripts/shutdown.sh" script is similar.

#!/bin/bash

export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/app/oracle
#export ORACLE_UNQNAME=DB11G
#export ORACLE_HOSTNAME=ol6-112.localdomain
#export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_HOSTNAME=ol6-121.localdomain
export ORACLE_UNQNAME=db12c
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1
export PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH

export ORACLE_SID=db12c 
ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES

# Stop Database
sqlplus / as sysdba << EOF
SHUTDOWN IMMEDIATE;
EXIT;
EOF

# Stop Listener
lsnrctl stop

Note. You could move the environment settings into the "dbora" file or into a separate file that is sourced in the startup and shutdown script. I kept it local to the script so you could see the type of things that need to be set in case you have to write a script to deal with multiple installations, instances and listeners.

Make sure the permissions and ownership of the files is correct.

# chmod u+x /home/oracle/scripts/startup.sh /home/oracle/scripts/shutdown.sh
# chown oracle.oinstall /home/oracle/scripts/startup.sh /home/oracle/scripts/shutdown.sh

Restart your system and you will see the listener and database will now start and stop automatically with the machine. You may test as follows:

login as: oracle
oracle@192.168.1.200's password:
Last login: Wed Feb 6 05:10:13 2013 from 192.168.1.141
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 6 05:24:31 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

=======================================================================

ERRORS

=======================================================================

TNS-12505: TNS:listener does not currently know of SID given in connect descriptor

Link

Solution:

alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.240)(PORT=1521))";

alter system register;

------------------------------------------------------------------------------


ORA-12203: TNS:unable to connect to destination

Link

OFF FIRE WALL ON BOTH DATABASE AND CLIENT

If needed


TNS ADMIN







TNS_ADMIN is an environment variable that points to the directory where the SQL*Net configuration files (like sqlnet.ora and tnsnames.ora) are located.

Windows:
set TNS_ADMIN=%ORACLE HOME%\network\admin


Unix/ Linux:
export TNS_ADMIN=$ORACLE HOME/network/admin

-----------------------------------------------------------------------------

In Oracle 11g a new parameter called SEC_CASE_SENSITIVE_LOGON 
which is defaults to TRUE for case sensitive passwords. My SCOTT 
password was "tiger" is small case and Forms 10g was taking it as 
"TIGER" on runtime.

Solution 1:

- Connect as sys and change the parameter to false.

      alter system set SEC_CASE_SENSITIVE_LOGON = FALSE;

Otherwise following error will occured.

ORA-01017 Invalid username/password logon denied

connecting form 6i to oracle database 10G/ express edition/11gR2 32 bit

Link

changed the database character set to UTF8, i show may step maybe will help for anybody.

connect "AS SYSDBA" in sqlplus.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8;
SHUTDOWN;
STARTUP RESTRICT;
SHUTDOWN;
STARTUP;

Oracle Reports 6i On Windows 8 Showing the Database Connection Window

Link

By switching the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE
you are able to run the reports.run the below query.

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

Remote Access on Read Hat Linux Server

Open the terminal
on oracle user enter
vino press tab button
check and unchecked the options

LISTENER keeps going down again and again

One of the DBA came complaining that the database listener is going down again and again without any errors. It was new Linux 64bit server and he had created a new database in that.
I went through all the files that I can possible look for like listener.ora, tnsnames.ora, sqlnet.ora, init<sid>.ora and listener logs. But didn't find any reasons for this issue.
Finally when I looked at the /etc/hosts file, I was surprised to see that "localhost" entry was missing in it.

127.0.0.1 AM3ES6207 AM3ES6207

I edited the line and added localhost in it as shown below.

127.0.0.1 localhost AM3ES6207

This resolved the issue and the listener started working.
In any linux machine 127.0.0.1 should always resolve to localhost and you can have any alias to it.

OCI-22303: type "SYS"."KU$_STATUS1020" not found

alter system flush shared pool;

I flushed the sahred pool, still it did not clear the alert.
Now I have decreased the java pool size and set sga_target as 0. Alert is gone and the import is running...
Not sure how good would the idea to leave sga_target to 0

`SELECT SUM(value) FROM V$SGA`	1200M
`SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY`	208M

You can take advantage of automatic shared memory management by setting Total SGA Size to 992M in Oracle Enterprise Manager, or by issuing the following statements:

ALTER SYSTEM SET SGA_TARGET = 992M;
ALTER SYSTEM SET SHARED_POOL_SIZE = 0;
ALTER SYSTEM SET LARGE_POOL_SIZE = 0;
ALTER SYSTEM SET JAVA_POOL_SIZE = 0;
ALTER SYSTEM SET DB_CACHE_SIZE = 0;
ALTER SYSTEM SET STREAMS_POOL_SIZE = 0;

where 992M = 1200M minus 208M.

SP2-1503: Unable to initialize oracle call interface

SP2-0152: ORACLE may not be functioning perperly

Link

To all who are looking for a solution to this....

I had the same issue above. I installed Oracle 10G R2 on my Windows 7 laptop. Though I had no issues during install I wasn't able to run sqlplus, either via the gui or through the command prompt. I didn't find anyone covering a solution for this besides installing Oracle 11.6, but I did find a solution.

Run sqlplus as administrator. I just right-clicked on the shortcut for the gui app and selected "Run as Administrator" and SQLPlus ran just fine. Right click on cmd icon select 'Run as Administrator'.

1. Access User Control Panel from Start Menu -> Control Panel -> User Accounts and Family Safety -> User Account

2. Click on User Account Control settings link.

3. Move the Slider to Never Notify

4. Click OK to make the change effective

5. Reboot.

Hope this helps someone.

Link

When trying to import the Oracle export dump into Oracle database, the following errors occur:

IMP-00003: ORACLE error 3113 encountered
ORA-03113: end-of-file on communication channel
IMP-00003: ORACLE error 3114 encountered
ORA-03114: not connected to ORACLE

The solution to solve the problem is as following:

$OH/rdbms/admin/dbmsread.sql

$OH/rdbms/admin/prvtread.plb

After executing the above sql scripts, retry the import. The error should disappears.

select count(*) from dba_constraints where status='ENABLED' and owner='ABC'; ---> compare in 11g (if same no need to proceed further)

IF not run the below command on 9i and spool the output

select 'alter table '||owner||'. '||table_name||' enable constraint '||constraint_name||' ;' from dba_constraints where status='ENABLED' and owner='ABC';

Run the spooled file (which has the result of the above command) on 11g.

ORA-00214: control file version inconsistent or corrupted

Link

ORA-00214: control file version inconsistent

Due to the power outage, one of my production database giving the following error.

When I try to startup the database the ORA-00214 controlfile inconsistent error occurred:

SQL> startup

ORACLE instance started.

Total System Global Area 662700032 bytes

Fixed Size 1250716 bytes

Variable Size 281021028 bytes

Database Buffers 373293056 bytes

Redo Buffers 7135232 bytes

ORA-00214: control file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\SYMP\CONTROL03.CTL'

version 1021304 inconsistent with file

'C:\ORACLE\PRODUCT\10.2.0\ORADATA\SYMP\CONTROL01.CTL' version 1021301

Error: ORA-00214

Cause: An ORA-00214 is issued whenever Oracle detects an inconsistency between two mirrored copies of the control file.

All copies of the control file must have the same internal sequence number for Oracle to start up the database or shut it down in normal or immediate mode.

Action: Use a consistent set of control files.

That is, all the files must be for the same database and from same time period.

Before going to troubleshoot the above error, Please follow the Metalink Doc ID 1014751.6

Action plan:

OS: Windows 2003 R2 32-bit

Database version: 10.2.0.1

Step1: Find the controlfile location:

SQL> show parameter control

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string C:\ORACLE\PRODUCT\10.2.0\ORADA

TA\SYMP\CONTROL01.CTL, C:\ORAC

LE\PRODUCT\10.2.0\ORADATA\SYMP

\CONTROL02.CTL, C:\ORACLE\PROD

UCT\10.2.0\ORADATA\SYMP\CONTRO

L03.CTL

Step2: Create and Edit the pfile from spfile:

SQL> create pfile='C:\oracle\product\10.2.0\db_1\dbs\inittest.ora' from spfile;

File created.

Step3: Remove the multiplexing controlfile of control_files parameter

Control_file=c:\oracle\product\10.2.0\oradata\symp\control01.ctl

Save

Now try to start the edited pfile:

SQL> startup nomount pfile='C:\oracle\product\10.2.0\db_1\dbs\inittest.ora'

ORACLE instance started.

Total System Global Area 662700032 bytes

Fixed Size 1250716 bytes

Variable Size 281021028 bytes

Database Buffers 373293056 bytes

Redo Buffers 7135232 bytes

Step4: Try to mount & open the database with pfile:

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

Step5: Now try to remove the multiplexed controlfile at os level.

C:\oracle\product\10.2.0\oradata>cd symp

C:\oracle\product\10.2.0\oradata\symp>dir

Volume in drive C has no label.

Volume Serial Number is 8048-4083

Directory of C:\oracle\product\10.2.0\oradata\symp

7,290,880 CONTROL01.CTL

7,290,880 CONTROL02.CTL

7,290,880 CONTROL03.CTL

524,296,192 IDCARD_TS.DBF

52,429,312 REDO01.LOG

52,429,312 REDO02.LOG

52,429,312 REDO03.LOG

377,495,552 SYSAUX01.DBF

513,810,432 SYSTEM01.DBF

269,492,224 TEMP01.DBF

68,165,632 UNDOTBS01.DBF

17,047,552 USERS01.DBF

12 File(s) 1,949,468,160 bytes

2 Dir(s) 245,401,767,936 bytes free

C:\oracle\product\10.2.0\oradata\symp>del CONTROL02.CTL

C:\oracle\product\10.2.0\oradata\symp>del CONTROL03.CTL

C:\oracle\product\10.2.0\oradata\symp>dir

Volume in drive C has no label.

Volume Serial Number is 8048-4083

Step6: Multiplex the controlfile with existing copy of controlfile (controlfile01.ctl) :

C:\oracle\product\10.2.0\oradata\symp>copy CONTROL01.CTL CONTROL02.CTL

1 file(s) copied.

C:\oracle\product\10.2.0\oradata\symp>copy CONTROL01.CTL CONTROL03.CTL

1 file(s) copied.

Step7: Start the database with existing default spfile:

SQL> STARTUP

Database started.

Now the database was successfully started without controlfile error.

let see the controlfile location:

SQL> Show parameter control

control_files='C:\oracle\product\10.2.0\oradata\symp\control01.ctl','C:\oracle\product\10.2.0\oradata\symp\control02.ctl','C:\oracle\product\10.2.0\oradata\symp\control03.ctl'

Please follow the Metalink Doc ID 1014751.6

OR

SQL> startup mount pfile='c:\p.txt';

SQL>rman target /

RMAN>restore controlfile from autobackup;

RMAN> quit

SQL>shut immediate

SQL>startup mount;

ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size                  2180024 bytes
Variable Size            2415922248 bytes
Database Buffers          771751936 bytes
Redo Buffers               16982016 bytes
ORA-00214: control file
'D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL' version 145625
inconsistent with file 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL' version 144010

SQL> shut immediate
ORA-01507: database not mounted


ORACLE instance shut down.

SQL>startup force mount pfile='c:\p.txt';

ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size                  2180024 bytes
Variable Size            2415922248 bytes
Database Buffers          771751936 bytes
Redo Buffers               16982016 bytes
Database mounted.
SQL>host rman target /

RMAN>recover database;

RMAN> alter database open resetlogs;

database opened.

LINUX

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 31 17:09:35 2018

Enter user-name: / as sysdba

SQL> shutdown abort

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1640484864 bytes

Fixed Size 1336876 bytes

Variable Size 1090521556 bytes

Database Buffers 536870912 bytes

Redo Buffers 11755520 bytes

SQL>

SQL> SHOW PARAMETER spfile;

SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfileshisb.ora' from spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfileshisb.ora';

File created.

SQL> control_file=/u01/app/oracle/oradata/shisb/control01.ctl

SQL> shutdown abort;

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfileshisb.ora';

ORACLE instance started.

Total System Global Area 1640484864 bytes

Fixed Size 1336876 bytes

Variable Size 1090521556 bytes

Database Buffers 536870912 bytes

Redo Buffers 11755520 bytes

SQL>

“ORA-03113: end-of-file on communication channel” on startup

Link

After hours of misdirection from official Oracle support, I dove into this on my own and fixed it. I am documenting it here

in case someone else has this problem.

To do any of this, you must be the oracle user:

$ su - oracle

Step 1: You need to look at the alert log. It isn't in /var/log as expected. You have to run an Oracle log reading program:

$ adrci

ADRCI: Release 11.2.0.1.0 - Production on Wed Sep 11 18:27:56 2013

ADR base = "/u01/app/oracle"

adrci>

Notice the ADR base. That is not the install. You need to see the homes so you can connect to the one that you use.

adrci> show homes

ADR Homes:

diag/rdbms/cci/CCI

diag/tnslsnr/cci/listener

diag/tnslsnr/cci/start

diag/tnslsnr/cci/reload

CCI is the home. Set that.

adrci> set home diag/rdbms/cci/CCI

adrci>

Now, you can look at the alert logs. It would be very nice if they were in /var/log so you could easily parse the logs.

Just stop wanting and deal with this interface. At least you can tail (and I hope you have a scrollback buffer):

adrci> show alert -tail 100

Scroll back until you see errors. You want the FIRST error. Any errors after the first error are likely being caused by the

first error. In my case, the first error was:

ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 100.00% used, and has 0 remaining bytes available.

This is caused by transactions. Oracle is not designed to be used. If you do push a lot of data into it, it saves

transaction logs. Those go into the recovery file area. Once that is full (50GB full in this case). Then, Oracle just dies.

By design, if anything is messed up, Oracle will respond by shutting down.

There are two solutions, the proper one and the quick and dirty one. The quick and dirty one is to increase

db_recovery_file_dest_size. First, exit adrci.

adrci> exit

Now, go into sqlplus without opening the database, just mounting it (you may be able to do this without mounting the

database, but I mount it anyway).

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 11 18:40:25 2013

SQL> connect / as sysdba

Connected.

SQL> startup mount

Now, you can increase your current db_recovery_file_dest_size, increased to 75G in my case:

SQL> alter system set db_recovery_file_dest_size = 75G scope=both

Now, you can shutdown and startup again and that previous error should be gone.

The proper fix is to get rid of the recovery files. You do that using RMAN, not SQLPLUS or ADRCI.

$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Sep 11 18:45:11 2013

RMAN> backup archivelog all delete input;

Wait a long time and your archivelog (that was using up all that space) will be gone. So, you can shutdown/startup your

database and be beck in business.

Link

$ rman target /

RMAN> SHOW ALL;

RMAN> LIST BACKUP SUMMARY;

RMAN> CROSSCHECK BACKUP;

RMAN> DELETE OBSOLETE;

RMAN> DELETE NOPROMPT OBSOLETE;

RMAN> DELETE OBSOLETE RECOVERY WINDOW OF 10 DAYS;

RMAN> LIST BACKUP SUMMARY;

RMAN> DELETE EXPIRED BACKUP;

log in as root and execute command "ipcs". should you see ipcs owned by the oracle user, delete them using the "ipcrm" command, and everything will work fine again

TNS-12505: TNS:listener does not currently know of SID given in connect descriptor

Link

Solution:

alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.110)(PORT=1521))";

alter system register;

Fix for ORA-03113: end-of-file on communication channel

Link

Solved

connect / as sysdba

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 200G SCOPE=BOTH SID='*';

If still not resolved then

1. rename flash_recovery_area as flash_recovery_area_bck

2. create new folder flash_recovery_area on same location.

3. connect / as sysdba

4. SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 200G SCOPE=BOTH SID='*';

5. restart/reboot the server and check database will be up.

ORA-12560: TNS:protocol adapter error occurred

Link

Possible Remedies:

* If running on the server (or workstation) that is running the database (i.e. if the database is local to the machine) then try explicitly setting 'ORACLE_SID'. e.g.

set ORACLE_SID=<sid>

and then try again. If this works then consider whether to make the change permanent by specifying it as a system variable.

sounds like the database is not up. you probably restarted your machine and the instance is not set to autostart

C:\Documents and Settings\tkyte>sqlplus scott/tiger

SQL*Plus: Release 10.1.0.4.0 - Production on Thu Sep 6 15:43:55 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options

scott%ORA10GR1> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options

C:\Documents and Settings\tkyte><b>net stop oracleserviceora10gr1
The OracleServiceORA10GR1 service is stopping.........
The OracleServiceORA10GR1 service was stopped successfully.
</b>

C:\Documents and Settings\tkyte>sqlplus scott/tiger

SQL*Plus: Release 10.1.0.4.0 - Production on Thu Sep 6 15:44:35 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
<b>
ERROR:
ORA-12560: TNS:protocol adapter error

</b>
Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
<b>
C:\Documents and Settings\tkyte>net start oracleserviceora10gr1
The OracleServiceORA10GR1 service is starting..........
The OracleServiceORA10GR1 service was started successfully.
</b>

C:\Documents and Settings\tkyte>sqlplus scott/tiger

SQL*Plus: Release 10.1.0.4.0 - Production on Thu Sep 6 15:45:12 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options

scott%ORA10GR1>

in the above ora10gr1 is my oracle sid, you can use control panel instead - control panel -> services -> look for the services starting with Oracle.

Start --> Run--> type " services.msc"

Select the OracleService name, Right Click, Start.

For more

Installation of Oracle DB11g R2 on RHEL 5

Installation of Oracle Database 11g Release 2 on RHEL 5

Automating Database Startup and Shutdown on Linux

ORA-12203: TNS:unable to connect to destination

TNS ADMIN

connecting form 6i to oracle database 10G/ express edition/11gR2 32 bit

ORA-00214: control file version inconsistent or corrupted

OR

No comments:

Post a Comment

About Me

Total Pageviews