Here, we will have a look on how to configure spfile, listener.ora,tnsnames.ora and password file.
1. Pfile/Spfile:
First, you have to create pfile. It should be located in the $ORACLE_HOME/dbs directory. The name is pfileSID.ora, where SID is the name of the database(instance).
Here is the example of minimal pfile:
db_name=ocm11g
db_block_size=8152
undo_tablespace=UNDOTBS1
undo_management=AUTO
diagnostic_dest='/oracle/diag'
control_files=('/path_to_control1','/path_to_control2','/path_to_control3')
db_cache_size=100M
shared_pool_size=50M
java_pool_size=50M
large_pool_size20M
after this, you can create the spfile from the pfile:
SQL> create spfile from pfile;
File created.
Now restart the database and you will use the spfile.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/product/11.2.0/dbhome_1/dbs/spfileocm11g.ora
Adding parameters to spfile:
SQL> alter system set large_pool_size=10M scope=both;
System altered.
scope:
spfile - it will add the parameter only to spfile (you need to restart the database)
memory - it will set the parameter in you current session
both - insert into spfile and memory
2 lietener.ora
Here, example of the listener.ora:
LISTENER=
(DESCRIPTION=
(ADDRESS = (HOST=localhost)(PROTOCOL=tcp)(PORT=1521))
(ADDRESS = (PROTOCOL=ipc)(KEY=EXTPROC))
)
SID_LIST_LISTENER
(SID_LIST=
(SID_DESC=
(SID_NAME=ocm11g)
(ORACLE_HOME=/oracle/product/11.2.0/dbhome_1)
)
(SID_DESC=
(SID_NAME=PLSExtProc)
(ORACLE_HOME=/oracle/product/11.2.0/dbhome_1)
(PROGRAM=extproc)
)
)
Starting the listener:
[oracle@ocm11g admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-AUG-2011 12:35:56
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Starting /oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/diag/tnslsnr/ocm11g/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm11g.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Connecting to (DESCRIPTION=(ADDRESS=(HOST=localhost)(PROTOCOL=tcp)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 15-AUG-2011 12:35:56
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /oracle/diag/tnslsnr/ocm11g/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm11g.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ocm11g" has 1 instance(s).
Instance "ocm11g", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
3. tnsnames.ora
ocm11g=
(DESCRIPTION=
( ADDRESS= (HOST=localhost)(PROTOCOL=tcp)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=ocm11g)
)
)
[oracle@ocm11g admin]$ tnsping ocm11g_tcp
TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 15-AUG-2011 12:45:22
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(HOST=localhost)(PORT=1521)(PROTOCOL=tcp)) (CONNECT_DATA= (SERVICE_NAME=ocm11g)))
OK (0 msec)
[oracle@ocm11g admin]$ sqlplus system@ocm11g_tcp
SQL*Plus: Release 11.2.0.2.0 Production on Mon Aug 15 12:46:04 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
4. password file
SQL> select * from v$pwfile_users;
no rows selected
[oracle@ocm11g admin]$ sqlplus sys/testOra1@ocm11g_tcp as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Aug 15 12:52:01 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
[oracle@ocm11g admin]$ orapwd file=/oracle/product/11.2.0/dbhome_1/dbs/orapwocm11g password=test entries=5
[oracle@ocm11g admin]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.2.0 Production on Mon Aug 15 13:05:00 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> grant sysdba to sys;
Grant succeeded.
[oracle@ocm11g admin]$ sqlplus sys/test@ocm11g_tcp as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Aug 15 13:05:58 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
First, you have to create pfile. It should be located in the $ORACLE_HOME/dbs directory. The name is pfileSID.ora, where SID is the name of the database(instance).
Here is the example of minimal pfile:
db_name=ocm11g
db_block_size=8152
undo_tablespace=UNDOTBS1
undo_management=AUTO
diagnostic_dest='/oracle/diag'
control_files=('/path_to_control1','/path_to_control2','/path_to_control3')
db_cache_size=100M
shared_pool_size=50M
java_pool_size=50M
large_pool_size20M
after this, you can create the spfile from the pfile:
SQL> create spfile from pfile;
File created.
Now restart the database and you will use the spfile.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/product/11.2.0/dbhome_1/dbs/spfileocm11g.ora
Adding parameters to spfile:
SQL> alter system set large_pool_size=10M scope=both;
System altered.
scope:
spfile - it will add the parameter only to spfile (you need to restart the database)
memory - it will set the parameter in you current session
both - insert into spfile and memory
2 lietener.ora
Here, example of the listener.ora:
LISTENER=
(DESCRIPTION=
(ADDRESS = (HOST=localhost)(PROTOCOL=tcp)(PORT=1521))
(ADDRESS = (PROTOCOL=ipc)(KEY=EXTPROC))
)
SID_LIST_LISTENER
(SID_LIST=
(SID_DESC=
(SID_NAME=ocm11g)
(ORACLE_HOME=/oracle/product/11.2.0/dbhome_1)
)
(SID_DESC=
(SID_NAME=PLSExtProc)
(ORACLE_HOME=/oracle/product/11.2.0/dbhome_1)
(PROGRAM=extproc)
)
)
Starting the listener:
[oracle@ocm11g admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-AUG-2011 12:35:56
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Starting /oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/diag/tnslsnr/ocm11g/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm11g.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Connecting to (DESCRIPTION=(ADDRESS=(HOST=localhost)(PROTOCOL=tcp)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 15-AUG-2011 12:35:56
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /oracle/diag/tnslsnr/ocm11g/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm11g.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ocm11g" has 1 instance(s).
Instance "ocm11g", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
3. tnsnames.ora
ocm11g=
(DESCRIPTION=
( ADDRESS= (HOST=localhost)(PROTOCOL=tcp)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=ocm11g)
)
)
[oracle@ocm11g admin]$ tnsping ocm11g_tcp
TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 15-AUG-2011 12:45:22
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(HOST=localhost)(PORT=1521)(PROTOCOL=tcp)) (CONNECT_DATA= (SERVICE_NAME=ocm11g)))
OK (0 msec)
[oracle@ocm11g admin]$ sqlplus system@ocm11g_tcp
SQL*Plus: Release 11.2.0.2.0 Production on Mon Aug 15 12:46:04 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
4. password file
SQL> select * from v$pwfile_users;
no rows selected
[oracle@ocm11g admin]$ sqlplus sys/testOra1@ocm11g_tcp as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Aug 15 12:52:01 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
[oracle@ocm11g admin]$ orapwd file=/oracle/product/11.2.0/dbhome_1/dbs/orapwocm11g password=test entries=5
[oracle@ocm11g admin]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.2.0 Production on Mon Aug 15 13:05:00 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> grant sysdba to sys;
Grant succeeded.
[oracle@ocm11g admin]$ sqlplus sys/test@ocm11g_tcp as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Aug 15 13:05:58 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE