DBMentors is a solution oriented group, started by a team of qualified and committed professionals with vast experience in IT industry. The team has in-depth technical and design expertise with highest standards of programming quality.

In this case, you must startup with an init.ora file in which you only
specify the spfile parameter full name:
spfile=d:\ora\database\spfiletest.ora
SQL> startup pfile=d:\ora901\database\inittest.ora

CREATE PFILE Command Results in ORA-02236 Error

You need to create a traditional parameter file from an existing spfile. When
you do so, you receive an ORA-02236 error.
For example:
SQL> create pfile=initRel12bis.ora from spfile;
create pfile=initRel12bis.ora from spfile
*
ERROR at line 1:
ORA-02236: invalid file name

You must add single quotes around it, such as:
SQL> create pfile='initRel12bis.ora' from spfile;
File created.

How to specify multiple utl_file_dir entries when using a server side parameter file (spfile).

The syntax is :
ALTER SYSTEM SET UTL_FILE_DIR='directory1','directory2' scope=spfile;

Why does create PFILE|SPFILE from MEMORY output differ from that of SPFILE|PFILE?The source from which a PFILE|SPFILE is written from MEMORY differs from that of the PFILE|SPFILE. MEMORY

The source is v$system_parameter4

V$SYSTEM_PARAMETER4 returns rows for all parameters which have have been modified internally (e.g. self-tuning), including parameters which are double-underscored. (paraphrased from Oracle source code)

PFILE|SPFILE The source is the PFILE|SPFILE from which the SPFILE|PFILE is being created.
The output from this source will only include those parameters found in the source** NOTE ** CREATE PFILE|SPFILE FROM MEMORY is a new feature beginning in 11gR1RESEARCH

Creating Multiple Listeners on UNIX - Including Migration or Upgrades
- This is written based on a single PUBLIC NIC (Network Interface Card) machine. If a machine has multiple public network cards, interfaces, or TCP addresses, then secondary listeners can be setup to use these other NICs, if available, while still adhering to the separation of listener name and IPC keys.
The general information is relative to any system using the SAME HOST values.
In fact, having multiple listeners for one or more databases can distribute client connection requests across several ports and thus serve as a useful load balancing mechanism.Some common questions:
========================
Q1. How many listener.ora files do I need?
A1. This can be done with one listener, and the TNS_ADMIN environment variable set to the one common directory (as would be the case with multiple listeners of the same version), or it can be done with separate listener.ora files - one for each version.
Please use discretion and local setup when choosing which method to use. Factors which might influence this decision would be how safe the one file would be, or how complex multiple ones are to manage.
Q2. Should I manually configure the listener.ora file or use Netasst?
A2. You may use the Net Configuration Assistant or Net Manager to edit the listener.ora files. How to do it:
===============

Here are the steps to manually setup two (or more) Listeners on the same UNIX server:

1) Change the LISTENER to another name (for example LSNR) in every occurrence in the version specific LISTENER.ORA.

Example "vi" command:
%s/LISTENER/LSNR/g

2) Use a different PORT number.
In SQL*Net V2, you can use any PORT number (Oracle defaults to 1521), as the entry in the /etc/services file is for the system administrator's reference only (that is, SQL*Net does not look for a /etc/services entry like it did in V1). Select an additional PORT number to use for the second listener, such as 1522, and check /etc/services to be sure it is available.

CASE A:
------
One Listener.ora file is used for both 8.1.7 and 9.2.0.8 listeners (this example assumes that the the environment variable TNS_ADMIN for EACH $ORACLE_HOME is set). These two listeners can reside in the same listener.ora file. In fact, you can run MULTIPLE listeners in the same file.

One common LISTENER.ORA file: (located in either Home and with TNS_ADMIN set)
----------------------------

The SID_LIST section is OPTIONAL for 8i and above but has been included for greater coverage.

CASE B:
------
Two Listener.ora files are being used for each listener version (one in each respective $ORACLE_HOME/network/admin and/or the TNS_ADMIN set to them). These can be expanded to the number of homes existing if required.

Two listener.ora files:
-----------------------

LISTENER.ORA for V8.1.7 (located in the /u01/oracle/8.1.7/network/admin directory)
-----------------------

1. The SID_LIST for any 8i, 9i, 10g, and 11g instances does not have to be placed in the listener.ora entry (omission is actually recommended), as it can sometimes clash with the automatic service process. Automatic Service Registration will take place if the "Compatible" and "local_listener" parameters are set in the init.ora file for the 8i/9i/10g/11g database. This will be true for tnsnames.ora file entries using either SERVICE_NAME or SID (i.e.: Net8i Client and newer or 8.0.x and older, respectively).
However, some of the older OEM tools require that there is a SID_LIST to determine up/down status of the database so check requirements on a per-component basis.

2. When using DYNAMIC or automatic service registration from the database (8i and up), make sure that the initialization file for 8i/9i/10g/11g db has the following parameter:
local_listener="(address=(protocol=tcp)(host=)(port=))"
The must be the hostname or IP address for the database server. The should be the same number as that specified in the respective listener.ora file that is servicing the Database in question.

3. When making changes to the default port of 1521, there is a possibility that some products, tools, or third party applications may experience connectivity problems or fail to find the listener on the non-default port. Therefore, make sure that any such components are set to find the listener on the correct port. This is specific to each product, tool, and application.

Lsnrctl Services Displays "status UNKNOWN"

An UNKNOWN status means that the instance is registered statically
(with a SID_LIST) in the listener.ora file rather than dynamically with
service registration. Therefore, the database status is "not known" to the
listener. There is no reason a connection cannot be made, however, using the
SID value.
For normal communication, the SID_LIST in the listener.ora file is not required.
One of the reasons why Oracle recommends not using one, is because it
may cause un-necessary overheads on the listener process.
The SID_LIST section only needs to be used for instances such as PLSExtProc or if
older OEM tools are in use.
It is not normally a problem having the instance duplicated in the SID_LIST,
but it is preferable not to have it.

When do SQLNET.ORA changes take effect ?
For client installations, changes to the SQLNET.ORA file take immediate effect for new outgoing connections, you do not need to restart any service. For server side installations, the SQLNET.ORA file is being read only upon a server process is being started, or by the listener service when is being (re)started or reloaded. That means that, as a premise, only new incoming connections may see these changes.
For the listener service, if you made any changes also affecting the listener (e.g. tcp.validnode_checking configuration), then you must restart or reload the listener (e.g. with "lsnrctl reload") for the changes to take effect.

For the DB instance, since there are two DB server models (dedicated mode and shared server / MTS mode), which employ different methods to start server processes, we have two cases:

if the DB works in dedicated mode (or clients use only dedicated connections), then new connections will see the SQLNET.ORA changes without requiring any services to be restarted

if the DB works in shared server / MTS mode (or clients use only shared mode connections), then new connections will see the SQLNET.ORA changes only after the instance is being restarted

The passwordfile provides a method to authenticate privileged users from
a remote (over sqlnet / listener) location. By default the user SYS gets
an entry in the passwordfile when you create it so you can connect to a
remote database (for example) XPDB as follows:
SQL> connect sys/manager@xpdb as sysdba

When the "init.ora file" parameter remote_login_passwordfile is set to EXCLUSIVE,

Oracle can have a password file to maintain.

- the password of user SYS
- the list of users and their passwords that have been granted the
SYSDBA and/or SYSOPER privilege

The name of the password file has the format: "pwd.ora" and is situated by

default in the %ORACLE_HOME%\Database directory.

When the parameter remote_login_passwordfile is set to SHARED, Oracle can
only have the password file to maintain the password of SYS, since it it
shared, it can be used by multiple databases that share the same %ORACLE_HOME%.

The name of the shared password file is usually %ORACLE_HOME%\database\pwd.ora ,
however, due to bug 3277257 (not published) you will have to use REGEDIT and
create a new string value in HKEY_LOCAL_MACHINE\Software\oracle\
with the name ORA_PWFILE and specify your passwordfile with the complete pathname
included, for example :
D:\ORACLE\ORA92\DATABASE\PWD.ORA

ORAPWD is a specific command-line utility for password file creation.

Updating Windows Registry When Password File Moved or Renamed
----------------------------------------------------------------
You either renamed or moved your password file.
The Registry has an entry to denote the password file.
When the file is moved or renamed the Registry must be updated.
Editing the REGISTRY could cause severe damage to Windows.
If you are not familiar with the Registry, please consult with your System
Administrator.
A damaged registry could require a complete reload of the Windows Operating System.
- Run REGEDT32.EXE to open the Registry.
- Click on the HKEY_LOCAL_MACHINE on Local Machine hive.
- Click on the SOFTWARE folder.
- Click on the ORACLE folder.
If the database is created in the default home, skip step 2.5.5
- If the database is not created in the default home, but in HomeXX
(XX is a number from 1 to the number of homes installed on your Server)
Click on the HomeXX folder.
- Update or Create ORA__PWFILE (where is the database SID).
String with the correct name and path of the password file.
After changing the registry entry, the OracleService service needs to
be stopped and restarted for the change to take affect.
The HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORA__PWFILE or
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HomeXX\ORA__PWFILE
registry entry is used to store the name and location of the password file.

ORA-01031 while CONNECT AS SYSDBA
---------
Check if the password file exists in the correct location and is correctly
named.
Check which password file is used by checking the value of ORA__PWFILE
in the correct registry key. If the parameter is set in the correct registry
key, the place where the Oracle software is looking for the password file or
its name might be another one than the one of the password file you are
trying to use.