1- PFILE
Related Topic: Click here to viewRelated Topic: Click here to view
2- SPFILE
Related Topic: Click here to view The default initialization files are located as follows:
- on Unix ---> $ORACLE_HOME/dbs
- on WindowsNT/2000 ---> %ORACLE_HOME%\database
How to use spfile for startup
SQL> startup spfile=d:\ora\database\spfile.ora
SP2-0714: invalid combination of STARTUP options
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 11gR1
RESEARCH
MEMORY
alter session set events '10046 trace name context level 12';
-- Session altered.
create pfile from memory;
-- File created.
alter session set events '10046 trace name context off';
<< locate and examine the trace file >>
The source of the data is shown as
select num,name,type,display_value,update_comment from v$system_parameter4 order by lower(name),ordinal
SPFILE|PFILE
alter session set events '10046 trace name context level 12';
-- Session altered.
create pfile from spfile;
-- File created.
alter session set events '10046 trace name context off';
<< locate and examine the trace file >>
The trace shows no dictionary queries ... as such ... the command is a direct read of the PFILE|SPFILE
3- LISTENER.ORA
Related Topic: Click here to viewCreating 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.
Example (to check for availability):
% grep 1522 /etc/services
3) Append the new name (for example LSNR) to "lsnrctl" commands
Example: % lsnrctl start LSNR
Example: % lsnrctl status LSNR
4) Use different KEY values for any IPC protocol addresses you may have.
When starting listeners for different versions of SQL*Net be sure to have the $ORACLE_HOME and PATH environment variables set so that you will start the appropriate version.
The versions and SID's in the examples below are as follows (please note that these can be ANY version as this is an example only):
1) V8.1.7 RDBMS
SID=V817 and SID=ORCL
$ORACLE_HOME = /u01/oracle/8.1.7
2) V9.2.0.8 RDBMS
SID=V9208
$ORACLE_HOME = /u01/oracle/9.2.0.8
Two Cases (examples):
=======================
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)
----------------------------
#########
# Entry for 8.1.7 listener
#########
LSNR817 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = V817))
(ADDRESS = (PROTOCOL = IPC)(KEY = ORCL))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
(ADDRESS = (PROTOCOL = TCP)(HOST = UNIX1)(PORT = 1521))
)
STARTUP_WAIT_TIME_LSNR817 = 0
CONNECT_TIMEOUT_LSNR817 = 10
SID_LIST_LSNR817=
(SID_LIST =
(SID_DESC =
(SID_NAME = V817)
(ORACLE_HOME=/u01/oracle/8.1.7)
)
(SID_DESC =
(SID_NAME = ORCL)
(ORACLE_HOME=/u01/oracle/8.1.7)
)
(SID_DESC =
(SID_NAME = extproc)
(ORACLE_HOME=/u01/oracle/8.1.7)
(PROGRAM = extproc)
)
)
#########
# Entry for 9.2.0.8 listener
#########
LSNR9208 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = V9208))
(ADDRESS = (PROTOCOL = IPC)(KEY = extproc1))
(ADDRESS = (PROTOCOL = TCP)(HOST = UNIX1)(PORT = 1522))
)
SID_LIST_LSNR9208 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME=/u01/oracle/9.2.0.8)
(PROGRAM = extproc)
)
)
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)
-----------------------
###################
# Listener.ora file
# /u01/oracle/8.1.7
###################
LSNR817 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = V817))
(ADDRESS = (PROTOCOL = IPC)(KEY = ORCL))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
(ADDRESS = (PROTOCOL = TCP)(HOST = UNIX1)(PORT = 1521))
)
STARTUP_WAIT_TIME_LSNR817 = 0
CONNECT_TIMEOUT_LSNR817 = 10
SID_LIST_LSNR817=
(SID_LIST =
(SID_DESC =
(SID_NAME = V817)
(ORACLE_HOME=/u01/oracle/8.1.7)
)
(SID_DESC =
(SID_NAME = ORCL)
(ORACLE_HOME=/u01/oracle/8.1.7)
)
(SID_DESC =
(SID_NAME = extproc)
(ORACLE_HOME=/u01/oracle/8.1.7)
(PROGRAM = extproc)
)
) LISTENER.ORA for V9.2.0.8 (located in the /u01/oracle/9.2.0.8/network/admin directory)
-----------------------
###################
# Listener.ora file
# /u01/oracle/9.2.0.8
###################
LSNR9208 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = V9208))
(ADDRESS = (PROTOCOL = IPC)(KEY = extproc1))
(ADDRESS = (PROTOCOL = TCP)(HOST = UNIX1)(PORT = 1522))
)
SID_LIST_LSNR9208 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME=/u01/oracle/9.2.0.8)
(PROGRAM = extproc)
)
) Additional Notes:
===================
Please note the following as well:
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=
The
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.
4- SQLNET.ORA
Related Topic: Click here to viewRelated Topic: Click here to view
Related Topic: Click here to view
Example: Sample SQLNET.ORA file (Unix)
# -----------------------------------------------------------------------
# Sample sqlnet.ora file - Clients and Server use this file
# -----------------------------------------------------------------------
#
# TRACE_LEVEL_CLIENT=16 # Uncomment these for Client trace
# TRACE_DIRECTORY_CLIENT=/tmp # Where to put trace files
# TRACE_FILE_CLIENT=cli # Filename prefix
# TRACE_UNIQUE_CLIENT=TRUE # Append Process ID to filename
#
AUTOMATIC_IPC=OFF # Dont try IPC first unless asked to
#
# TRACE_LEVEL_SERVER=16 # Uncomment these for Server trace
# TRACE_DIRECTORY_SERVER=/tmp # Where to put trace files
# TRACE_FILE_SERVER=srv # Filename prefix
#
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
5- TNSNAMES.ORA
TNS entry for single instanceTEST1 =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 192.168.16.55)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST1)
(INSTANCE_NAME = TEST1)
)
)
TNS entry for RAC
TESTRAC =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 192.168.16.55)
(PORT = 1521)
)
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 192.168.16.56)
(PORT = 1521)
)
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST)
)
)
TNS entry for Heterogeneous
TG4MSQL =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = tcp)
(HOST = 192.168.16.55)
(PORT = 1522)
)
(CONNECT_DATA =
(SID = tg4msql)
)
(HS = OK)
)
6- PASSWORD FILE
Related Topic: Click here to viewThe 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.
Other Help Topic
CMAN Configuration 
No comments:
Post a Comment