Home » RDBMS Server » Enterprise Manager » Database creation: Warning ORA-00119, ORA-00132
Database creation: Warning ORA-00119, ORA-00132 [message #661339] Wed, 15 March 2017 09:57 Go to next message
1stone
Messages: 5
Registered: March 2017
Junior Member
Hello,

I am using SUSE Linux Enterprise Server 11 SP4 and I used the following guide to install and configure Oracle Grid (12.2.1) and Oracle Database 12c Release 1 (12.1.0.2):
http://www.idevelopment.info/data/Oracle/DBA_tips/Linux/LINUX_22.shtml

Installation of Oracle Grid as well as the installation of the Database software finished without issues. Unfortunately the creation of a new database is producing some warnings during the database creation process:
/forum/fa/13479/0/

I am providing you the contents of the following files, so you can see how the setup looks like:
listener.ora:
# listener.ora Network Configuration File: /opt/u01/oracle/product/12.2.1/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
LOCAL_LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

tnsnames.ora:
# tnsnames.ora Network Configuration File: /opt/u01/oracle/product/12.2.1/grid/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.dbtest)
    )
  )

and the .bash_profile:
# ---------------------------------------------------
# .bash_profile
# ---------------------------------------------------
# OS User:      oracle
# Application:  Oracle Database Software Owner
#               Oracle Grid Infrastructure for a
#               Standalone Server
# Version:      Oracle 12c Release 1 (12.1.0.2)
# ---------------------------------------------------

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
      . ~/.bashrc
      fi
      
      alias ls="ls -FA"
      
      # ---------------------------------------------------
      # ORACLE_SID
      # ---------------------------------------------------
      # Specifies the Oracle system identifier (SID) for
      # the Oracle instance running on this node. When
      # using RAC, each node must have a unique ORACLE_SID.
      # (i.e. racdb1, racdb2,...)
      # ---------------------------------------------------
      ORACLE_SID=orcl.dbtest; export ORACLE_SID
      
      # ---------------------------------------------------
      # ORACLE_UNQNAME and ORACLE_HOSTNAME
      # ---------------------------------------------------
      # In previous releases of Oracle Database, you were 
      # required to set environment variables for
      # ORACLE_HOME and ORACLE_SID to start, stop, and
      # check the status of Enterprise Manager. With
      # Oracle Database 11g Release 2 (11.2) and later, you
      # need to set the environment variables ORACLE_HOME, 
      # ORACLE_UNQNAME, and ORACLE_HOSTNAME to use
      # Enterprise Manager. Set ORACLE_UNQNAME equal to
      # the database unique name and ORACLE_HOSTNAME to
      # the hostname of the machine.
      # ---------------------------------------------------
      ORACLE_UNQNAME=orcl.dbtest; export ORACLE_UNQNAME
      ORACLE_HOSTNAME=localhost; export ORACLE_HOSTNAME
      
      # ---------------------------------------------------
      # JAVA_HOME
      # ---------------------------------------------------
      # Specifies the directory of the Java SDK and Runtime
      # Environment.
      # ---------------------------------------------------
      JAVA_HOME=/usr/local/java; export JAVA_HOME
      
      # ---------------------------------------------------
      # ORACLE_BASE
      # ---------------------------------------------------
      # Specifies the base of the Oracle directory structure
      # for Optimal Flexible Architecture (OFA) compliant
      # database software installations.
      # ---------------------------------------------------
      ORACLE_BASE=/opt/u01/oracle; export ORACLE_BASE
      
      # ---------------------------------------------------
      # ORACLE_HOME
      # ---------------------------------------------------
      # Specifies the directory containing the Oracle
      # Database software.
      # ---------------------------------------------------
      ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/dbhome_1; export ORACLE_HOME
      
      # ---------------------------------------------------
      # GRID_HOME
      # ---------------------------------------------------
      # Specifies the directory containing the
      # Oracle Grid Infrastructure for a Standalone Server.
      # ---------------------------------------------------
      GRID_HOME=$ORACLE_BASE/product/12.2.1/grid; export GRID_HOME
      
      # ---------------------------------------------------
      # ORACLE_PATH
      # ---------------------------------------------------
      # Specifies the search path for files used by Oracle
      # applications such as SQL*Plus. If the full path to
      # the file is not specified, or if the file is not
      # in the current directory, the Oracle application
      # uses ORACLE_PATH to locate the file.
      # This variable is used by SQL*Plus, Forms and Menu.
      # ---------------------------------------------------
      ORACLE_PATH=/opt/u01/oracle/dba_scripts/sql:$ORACLE_HOME/rdbms/admin; export ORACLE_PATH
      
      # ---------------------------------------------------
      # SQLPATH
      # ---------------------------------------------------
      # Specifies the directory or list of directories that
      # SQL*Plus searches for a login.sql file.
      # ---------------------------------------------------
      # SQLPATH=/u01/app/oracle/dba_scripts/sql; export SQLPATH
      
      # ---------------------------------------------------
      # ORACLE_TERM
      # ---------------------------------------------------
      # Defines a terminal definition. If not set, it
      # defaults to the value of your TERM environment
      # variable. Used by all character mode products. 
      # ---------------------------------------------------
      ORACLE_TERM=xterm; export ORACLE_TERM
      
      # ---------------------------------------------------
      # NLS_DATE_FORMAT
      # ---------------------------------------------------
      # Specifies the default date format to use with the
      # TO_CHAR and TO_DATE functions. The default value of
      # this parameter is determined by NLS_TERRITORY. The
      # value of this parameter can be any valid date
      # format mask, and the value must be surrounded by 
      # double quotation marks. For example:
      #
      #         NLS_DATE_FORMAT = "MM/DD/YYYY"
      #
      # ---------------------------------------------------
      NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"; export NLS_DATE_FORMAT
      
      # ---------------------------------------------------
      # TNS_ADMIN
      # ---------------------------------------------------
      # Specifies the directory containing the Oracle Net
      # Services configuration files like listener.ora, 
      # tnsnames.ora, and sqlnet.ora. When using Oracle
      # ASM, the TNS listener will be run out of
      # GRID_HOME; otherwise the listener will be run out
      # ORACLE_HOME.
      # ---------------------------------------------------
      # TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
      TNS_ADMIN=$GRID_HOME/network/admin; export TNS_ADMIN
      
      # ---------------------------------------------------
      # ORA_NLS11
      # ---------------------------------------------------
      # Specifies the directory where the language,
      # territory, character set, and linguistic definition
      # files are stored.
      # ---------------------------------------------------
      ORA_NLS11=$ORACLE_HOME/nls/data; export ORA_NLS11
      
      # ---------------------------------------------------
      # PATH
      # ---------------------------------------------------
      # Used by the shell to locate executable programs;
      # must include the $ORACLE_HOME/bin directory.
      # ---------------------------------------------------
      PATH=.:${JAVA_HOME}/bin:$JAVA_HOME/db/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
      PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
      PATH=${PATH}:/opt/u01/oracle/dba_scripts/bin
      export PATH
      
      # ---------------------------------------------------
      # LD_LIBRARY_PATH
      # ---------------------------------------------------
      # Specifies the list of directories that the shared
      # library loader searches to locate shared object
      # libraries at runtime.
      # ---------------------------------------------------
      LD_LIBRARY_PATH=$ORACLE_HOME/lib
      LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
      LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
      export LD_LIBRARY_PATH
      
      # ---------------------------------------------------
      # CLASSPATH
      # ---------------------------------------------------
      # The class path is the path that the Java runtime
      # environment searches for classes and other resource
      # files. The class search path (more commonly known
      # by the shorter name, "class path") can be set using
      # either the -classpath option when calling a JDK
      # tool (the preferred method) or by setting the
      # CLASSPATH environment variable. The -classpath
      # option is preferred because you can set it
      # individually for each application without affecting
      # other applications and without other applications
      # modifying its value.
      # ---------------------------------------------------
      CLASSPATH=.:$ORACLE_HOME/jdbc/lib/ojdbc6.jar
      CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
      CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
      export CLASSPATH
      
      # ---------------------------------------------------
      # THREADS_FLAG
      # ---------------------------------------------------
      # All the tools in the JDK use green threads as a
      # default. To specify that native threads should be
      # used, set the THREADS_FLAG environment variable to
      # "native". You can revert to the use of green
      # threads by setting THREADS_FLAG to the value
      # "green".
      # ---------------------------------------------------
      THREADS_FLAG=native; export THREADS_FLAG
      
      # ---------------------------------------------------
      # TEMP, TMP, and TMPDIR
      # ---------------------------------------------------
      # Specify the default directories for temporary
      # files; if set, tools that create temporary files
      # create them in one of these directories.
      # ---------------------------------------------------
      export TEMP=/tmp
      export TMPDIR=/tmp
      
      # ---------------------------------------------------
      # UMASK
      # ---------------------------------------------------
      # Set the default file mode creation mask
      # (umask) to 022 to ensure that the user performing
      # the Oracle software installation creates files
      # with 644 permissions.
      # ---------------------------------------------------
      umask 022



According to this article (http://dba-oracle.com/bk_ora_00119.htm) I decided to modify the SPFILE/PFILE. First I tried to get an overview which files are located in ASM by using the command asmcmd. After entering asmcmd no command (for example: ls, cd, ..) was working. After some research I changed the following parameters:
export ORACLE_HOME=/opt/u01/oracle/product/12.2.1/grid
export PATH=$ORACLE_HOME/bin
export ORACLE_SID=+ASM
After changing the parameters the commands are working now. In /DATA/ there are the following files which I can find:
/DATA/ASM/ASMPARAMETERFILE/REGISTRY.xxx.xxxxxxxxx
/DATA/ORCL/PARAMETERFILE/spfile.xxx.xxxxxxxxx

According to this article https://juniororacledba.wordpress.com/tag/create-pfile-from-spfile/ I checked the location of the spfile:
SQL> show parameter spfile

NAME TYPE VALUE
---------------------------------------------------------------------------------------------
spfile string +DATA/ASM/ASMPARAMETERFILE/registry.253.937097749

I was surprised of the result, because I was expecting to find the SPFILE in the folder ORCL instead. But I tried to continue and create a PFILE from the SPFILE with the following command:

SQL> CREATE PFILE='/home/oracle/my_init.ora' FROM SPFILE'+DATA/ASM/ASMPARAMETERFILE/REGISTRY.253.937097749'
After hitting enter a new line shows up with the number "2" and waiting for some input from my end I think. Hitting again enter it finishes, but no PFILE was created.


Anyway is the started approach to fix this warning the correct one? If you think yes then what should I do to edit the SPFILE successfully


Best regards
1stone
Re: Database creation: Warning ORA-00119, ORA-00132 [message #661340 is a reply to message #661339] Wed, 15 March 2017 10:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You need to decide to accept the typical defaults or if you are knowledgeable enough to successfully implement a non-standard configuration.
It appears that you think you know how to install a customized database, but Oracle disagrees with you.
You don't have to convince us what you have is correct. You have to produce customizations that are acceptable to Oracle.

There is ZERO advantage to use non-standard listener on no-standard port#.
If you insist that is something that must occur, then You're On Your Own (YOYO)!
Re: Database creation: Warning ORA-00119, ORA-00132 [message #661341 is a reply to message #661339] Wed, 15 March 2017 10:17 Go to previous messageGo to next message
1stone
Messages: 5
Registered: March 2017
Junior Member
Hello BlackSwan,

In the past I used already Oracle Database software, but Oracle Grid is completely new to me as well as ASM. Therefore I was looking for an installation guide which can assist to install and configure everything. So far it was working except the last part (creating a database). Therefore I started to change settings (for example listener) so I could fix this issue, but after three days I still do not know what to do. Can you tell me what would be the standard installation process?

Best regards
1stone

[Updated on: Wed, 15 March 2017 10:17]

Report message to a moderator

Re: Database creation: Warning ORA-00119, ORA-00132 [message #661344 is a reply to message #661341] Wed, 15 March 2017 11:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Oracle listener will start & run just fine when no listener.ora exists.

BTW - when listener uses HOST=localhost, then no remote client can ever access the listener or database.
Re: Database creation: Warning ORA-00119, ORA-00132 [message #661346 is a reply to message #661339] Wed, 15 March 2017 11:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In listener.ora, "LOCAL_LISTENER" should be "LISTENER_ORCL" and "localhost" should be replaced by the actual IP or host name.
In tnsnames.ora "LISTENER_ORCL" entry should not exist.
In init.ora or spfile file, "LOCAL_LISTENER" parameter should contain "(ADDRESS = (PROTOCOL = TCP)(HOST = <myserver>)(PORT = 1522))".

Note: there may be other errors, start to fix these ones.

[Updated on: Wed, 15 March 2017 11:23]

Report message to a moderator

Re: Database creation: Warning ORA-00119, ORA-00132 [message #661352 is a reply to message #661346] Wed, 15 March 2017 11:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://community.oracle.com/thread/4026704
Re: Database creation: Warning ORA-00119, ORA-00132 [message #661372 is a reply to message #661344] Thu, 16 March 2017 05:17 Go to previous messageGo to next message
1stone
Messages: 5
Registered: March 2017
Junior Member
BlackSwan wrote on Wed, 15 March 2017 11:07
Oracle listener will start & run just fine when no listener.ora exists.

BTW - when listener uses HOST=localhost, then no remote client can ever access the listener or database.
I removed the listener.ora from the grid home directory, but during the creating process of the database the same error occurs.

Michel Cadot wrote on Wed, 15 March 2017 11:22
In listener.ora, "LOCAL_LISTENER" should be "LISTENER_ORCL" and "localhost" should be replaced by the actual IP or host name.
In tnsnames.ora "LISTENER_ORCL" entry should not exist.
In init.ora or spfile file, "LOCAL_LISTENER" parameter should contain "(ADDRESS = (PROTOCOL = TCP)(HOST = <myserver>)(PORT = 1522))".

Note: there may be other errors, start to fix these ones.

I also tried to modify the listener.ora (changed "LOCAL_LISTENER" to "LISTENER_ORCL" and put the IP instead of localhost) and the tnsnames.ora (removed LISTENER_ORCL), but in that case I am receiving the following error at the very beginning of the installation process:
/forum/fa/13481/0/

Here are the modified files:
listener.ora:
# listener.ora Network Configuration File: /opt/u01/oracle/product/12.2.1/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 15.119.202.187)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

tnsnames.ora:
# tnsnames.ora Network Configuration File: /opt/u01/oracle/product/12.2.1/grid/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.dbtest)
    )
  )

[Updated on: Thu, 16 March 2017 05:21]

Report message to a moderator

Re: Database creation: Warning ORA-00119, ORA-00132 [message #661373 is a reply to message #661372] Thu, 16 March 2017 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
In init.ora or spfile file, "LOCAL_LISTENER" parameter should contain "(ADDRESS = (PROTOCOL = TCP)(HOST = 15.119.202.187)(PORT = 1522))".
Re: Database creation: Warning ORA-00119, ORA-00132 [message #661375 is a reply to message #661373] Thu, 16 March 2017 06:23 Go to previous messageGo to next message
1stone
Messages: 5
Registered: March 2017
Junior Member
Michel Cadot wrote on Thu, 16 March 2017 05:45

Quote:
In init.ora or spfile file, "LOCAL_LISTENER" parameter should contain "(ADDRESS = (PROTOCOL = TCP)(HOST = 15.119.202.187)(PORT = 1522))".
I am not able to find this text line. I found a init.ora in the following locations:
/opt/u01/oracle/product/12.1.0.2/dbhome_1/dbs/init.ora
/opt/u01/oracle/product/12.2.1/grid/dbs/init.ora
and I also found a initorcl.ora in:
/opt/u01/oracle/product/12.1.0.2/dbhome_1/dbs/initorcl.ora

The context of both init.ora's is the following one:
# 
# $Header: rdbms/admin/init.ora /main/24 2012/02/03 08:24:01 ysarig Exp $ 
# 
# Copyright (c) 1991, 1997, 1998 by Oracle Corporation
# NAME
#   init.ora
# FUNCTION
# NOTES
# MODIFIED
#     ysarig     02/01/12  - Renaming flash_recovery_area to
#                            fast_recovery_area
#     ysarig     05/14/09  - Updating compatible to 11.2
#     ysarig     08/13/07  - Fixing the sample for 11g
#     atsukerm   08/06/98 -  fix for 8.1.
#     hpiao      06/05/97 -  fix for 803
#     glavash    05/12/97 -  add oracle_trace_enable comment
#     hpiao      04/22/97 -  remove ifile=, events=, etc.
#     alingelb   09/19/94 -  remove vms-specific stuff
#     dpawson    07/07/93 -  add more comments regarded archive start
#     maporter   10/29/92 -  Add vms_sga_use_gblpagfile=TRUE 
#     jloaiza    03/07/92 -  change ALPHA to BETA 
#     danderso   02/26/92 -  change db_block_cache_protect to _db_block_cache_p
#     ghallmar   02/03/92 -  db_directory -> db_domain 
#     maporter   01/12/92 -  merge changes from branch 1.8.308.1 
#     maporter   12/21/91 -  bug 76493: Add control_files parameter 
#     wbridge    12/03/91 -  use of %c in archive format is discouraged 
#     ghallmar   12/02/91 -  add global_names=true, db_directory=us.acme.com 
#     thayes     11/27/91 -  Change default for cache_clone 
#     jloaiza    08/13/91 -         merge changes from branch 1.7.100.1 
#     jloaiza    07/31/91 -         add debug stuff 
#     rlim       04/29/91 -         removal of char_is_varchar2 
#   Bridge     03/12/91 - log_allocation no longer exists
#   Wijaya     02/05/91 - remove obsolete parameters
#
##############################################################################
# Example INIT.ORA file
#
# This file is provided by Oracle Corporation as a starting point for
# customizing the Oracle Database installation for your site. 	 
#
# NOTE: The values that are used in this file are example values only.
# You may want to adjust those values for your specific requirements. 
# You might also consider using the Database Configuration Assistant	 
# tool (DBCA) to create a server-side initialization parameter file
# and to size your initial set of tablespaces. See the
# Oracle Database 2 Day DBA guide for more information.
###############################################################################

# Change '<ORACLE_BASE>' to point to the oracle base (the one you specify at
# install time)

db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='<ORACLE_BASE>/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='<ORACLE_BASE>'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300 
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'

and of the file initorcl.ora
SPFILE='+FRA/orcl/spfileorcl.ora'
Re: Database creation: Warning ORA-00119, ORA-00132 [message #661379 is a reply to message #661375] Thu, 16 March 2017 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This later ones (initoracl.ora and spfile) are the ones used to start the instance.
Use SQL*Plus (before set ORACLE_HOME=/opt/u01/oracle/product/12.1.0.2/dbhome_1 and ORACLE_SID=orcl) and execute:
shutdown abort
startup nomount
create pfile=/tmp/init.ora from spfile;
shutdown
and post /tmp/init.ora.

[Updated on: Thu, 16 March 2017 07:31]

Report message to a moderator

Re: Database creation: Warning ORA-00119, ORA-00132 [message #661384 is a reply to message #661379] Thu, 16 March 2017 08:28 Go to previous messageGo to next message
1stone
Messages: 5
Registered: March 2017
Junior Member
Hi,

I made changes to ORACLE_HOME and ORACLE_SID and logged in with "sqlplus / as sysdba".

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+FRA/orcl/spfileorcl.ora'
ORA-17503: ksfdopn:2 Failed to open file +FRA/orcl/spfileorcl.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:2 Failed to open file +FRA/orcl/spfileorcl.ora
ORA-15173: entry 'spfileorcl.ora' does not exist in directory 'orcl'
ORA-06512: at line 4
SQL> create pfile=/tmp/init.ora from spfile;
create pfile=/tmp/init.ora from spfile
             *
ERROR at line 1:
ORA-02236: invalid file name

It seems to me that the path inside the initorcl.ora is wrong or the spfileorcl.ora is missing in this location. I am trying to find out why this error appears.

Many thanks so far for your help!


Best regards
1stone
Re: Database creation: Warning ORA-00119, ORA-00132 [message #661385 is a reply to message #661384] Thu, 16 March 2017 08:42 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For the latest error (once you'll find your spfile), add quotes around the file name.

You don't need any spfile to start the instance, just copy the standard one you posted into initorcl.ora (changing the values between <> to your system ones) and adding the local_listener value I posted.

Previous Topic: 404 Not Found Resource /em not found on this server (2 merged)
Next Topic: OEM 12cR3:error while scheduling backup for group
Goto Forum:
  


Current Time: Thu Mar 28 06:56:06 CDT 2024