Configure Listener Using NETCA

Networking for Oracle Database

Oracle Net is a software layer that maintains connection between the client and the database over the network. Client application identifies the database it wants to connect to, through the service name which is the logical representation of the database. Multiple services can be configured on the single database.

What is Oracle Listener?

Oracle listener is a process to listen request from client connection to database server. Default listener configuration is stored in the listener.ora file located at $ORACLE_HOME/network/admin directory. 

  When listener starts, database and its services gets dynamically registered with the listener. PMON is responsible for database service registration to the listener. When services gets registered there is no need of any modifications to the listener.ora file. 

Listener Commands

1) Starting the listener
[oracle@cobra01 ~]$ lsnrctl start

2) Stopping the listener

[oracle@cobra01 ~]$ lsnrctl stop

3) Check the listener status

[oracle@cobra01 ~]$ lsnrctl status

4) Check if listener process is running

[oracle@cobra01 ~]$ ps -ef|grep tns

What is NETCA?

NETCA is the utility used to configure listener and also used to add an entry to tnsnames.ora file. Connection descriptors gets stored on the tnsnames.ora file that identifies their net service names

Configure Listener using NETCA utility

1) Run netca command to invoke the network configuration assistant 

[oracle@cobra01 ~]$ netca

 

2) Select Add option and click on Next

3) Provide Oracle service name or database name then click on Next

4) We will select TCP protocol option in the list box and click on Next

5) Provide the Hostname and port number. Default port is 1521, in our case will will keep port number as it is. Then click on Next

6) Select Option Yes, perform a test. This is to make sure that a connection can access the database

7) Here in the screen shot provided, tests have been cleared. In case the test fails check if you have entered the correct login credentials. Login credentials can be updated by pressing change login button. Everything seems to be fine, so click on Next to proceed forward

8) Click on No Option and click Next

9) We have successfully completed listener configuration, finally click on Next

10) Click on the Finish button, this will close the netca window

11) Now check the listener status, it must have started already 

[oracle@cobra01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 30-AUG-2016 19:14:05

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cobra01.localdomain)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date                30-AUG-2016 19:13:01

Uptime                    0 days 0 hr. 1 min. 4 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/cobra01/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cobra01.localdomain)(PORT=1521)))

Services Summary...

Service "KMDB" has 1 instance(s).

  Instance "KMDB", status READY, has 1 handler(s) for this service...

Service "KMDBXDB" has 1 instance(s).

  Instance "KMDB", status READY, has 1 handler(s) for this service...

The command completed successfully

Oracle Database Architecture

Oracle database server is combination of instance(Memory component) and database(Storage component). Initial set of database files created during database creation. When instance starts SGA gets allocated and background process starts. Database comprises of physical and logical structure.

Shared Global Area - SGA

SGA is a dynamic pool which consists of various memory components. It contains control and data information for oracle database instance. SGA is shared by server process as well as background processes. SGA memory allocation and deallocation is done in terms of granules(contiguous memory) except for redolog buffer. Components of SGA are as follows

• Database Buffer Cache
• Redo Log Buffer
• Shared Pool
• Large Pool
• Java Pool
• Streams Pool
• Fixed SGA

I have posted another article with detailed information on process architecture

Click Here to view the Oracle Database Memory Architecture Post

Database Buffer cache is the memory component that stores copies of data blocks that are read from data files. A buffer is a memory that temporarily caches a current or recently used data blocks. This component takes care of the following 

1. Optimize the physical I/O
2. Keep frequently used blocks in buffer cache
3. Write least frequently used blocks to disk

Buffers in the buffer cache has different states

1. Unused :- The buffer is available for use
2. Clean :- Buffers that were previously used but now it has read-consistent block
3. Dirty : These are modified buffers that are not yet written to the disk

Allocation and de-allocation of buffers work on LRU(Least recently used) basis. Frequently used buffers are called hot buffer and cold buffer are those that are not used recently. Lets say when a query executes if server process can find the required data in buffer cache its called cache hit. If the data is not available in buffer cache its cache miss. Physical read happens when data block is copied from data file to memory. 

Buffer cache has different pools

1. Default cache :- Defaults location for caching the blocks
2. Keep cache :- Frequently used blocks as usually kept here, to avoid aging out of blocks from cache
3. Recycle cache :- Infrequently used blocks are kept here, or to phase it out of cache from consuming memory
4. Cache to keep non standard blocks (2k, 4k, 16k, 32k blocks) :- This pool keeps blocks that are not standard block size

Redo log buffer is a circular buffer having changed vectors to the database. These changes are made through DML or DDL operations. It is the smallest memory component in SGA. Log writer process writes changed vectors from redolog buffer to online redolog files.

Shared Pool caches various control information such as parsed SQL, PL/SQL code, system parameters, and data dictionary information. Shared pool has different components

1. Library cache :- It stores executable SQL and PL/SQL code, along with control structures such as locks and library cache handles. 
2. Data dictionary cache :- It holds metadata of tables and reference information about the database.
3. Result Cache :- This component contains the SQL query result cache and PL/SQL function result cache. In simple words it stores the result of processed SQL statement
4. Reserved pool :- is a shared pool component that Oracle Database can use to allocate large contiguous chunks of memory. This pool can be used to Allocate memory from the shared pool which is performed in chunks. It will allow large object(over 5 KB) to be loaded into the cache without needing a single contiguous area.

Large pool is an optional memory area intended for memory allocations that are larger than is appropriate for the shared pool. 

Java Pool is used along with JVM component. 

Streams Pool is used if Oracle streams is configured or used. This pool is exclusively for the use of Oracle streams. 

Fixed SGA is used for internal housekeeping tasks like keeping information about locks etc.

Background Process

Process Monitor - PMON (Mandatory/Instance Critical) :- PMON is a background process that monitors all other background processes Performs process recovery for sessions terminated abnormally. It registers instance information to listener

System Monitor - SMON (Mandatory/Instance Critical) :- SMON is a background process responsible for instance recovery and clean up duties. It recovers transactions that were skipped during instance recovery due to file-read or tablespace offline errors. SMON cleans unused temporary segment from temp (temp tablespace)

Log Writer - LGWR (Mandatory/Instance Critical) :- LGWR is a background process writes changed vectors from buffer cache(redolog buffer) to online redolog files. Write happens from redolog buffer to redolog group in the following conditions

1. when transaction is comitted
2. when log switch occurs
3. every 3 seconds
4. 1/3rd of redolog buffer is full
5. redolog buffer contains 1MB of data

Database Writer - DBWn 0-9 (Mandatory/Instance Critical) :- DBWR process writes dirty buffers in buffer cache to disk or datafiles. Writing happens in the following conditions

1. Clean reusable buffers is not available in cache after checking threshold number of blocks
2. To advance the check point, this is the position in redolog file where recovery of the instance starts

Check Point - CKPT (Mandatory/Instance Critical) :- CKPT is a background process which is responsible for 

1. Updating latest SCN to control file and data file headers
2. Writing dirty buffers from buffer cache to disk

Memory Monitor - MMON (New in Oracle 10g) :- MMON collects the statistics required for AWR reports

Memory Monitor Light - MMNL (New in Oracle 10g) :- MMNL writes the statistics collected to the disk and statistics are used for ASH reports as well

Memory Manager - MMAN (New in Oracle 10g) :- MMAN background process manages the dynamic resizing of SGA memory components depending on load for database

Diagnostic Capture Process - DIAG (New in Oracle 11g) :- DIAG process executes oradebug commands and performs diagnostic dumps requested by other processes or termination of instance. Alertlog file and all trace files will be written to a single location

Recoverer - RECO :- Its a background process run on distributed database environment

General Task Execution process - GENo (New in Oracle 11g) :- This process performs required task for DMLs and SQL. GENo process handles the processes that are blocking other processes

Diagnosability Process 0 - DIA0 (New in Oracle 11g) :- DIA0 is responsible for hang detection and deadlock resolution

Virtual Keeper of Time Process - VKTM (New in Oracle 11g) :- VKTM acts as a time publisher for an Oracle instance. VKTM publishes two sets of time: a wall clock time using a seconds interval and a higher resolution time (which is not wall clock time) for interval measurements

Process Spawner - PSPO :- Process Spawner spawns oracle background process after initial startup of the instance. It was introduced in Oracle 10g. This process creates and manages other oracle background processes

Job Queue Coordinator Process - CJQO :- This process selects jobs that need to be run from the data dictionary and spawns job queue slave processes (Jnnn) to run the jobs. JQ0 is automatically started and stopped as needed by Oracle Scheduler. CJQ0 starts only as many job queue processes as required by the number of jobs to run and available resources

Space Management Coordinator - SMCO :-This background process is responsible for coordinating the execution of various space management tasks, including proactive space allocation and space reclamation. SMCO dynamically spawns slave processes (Wnnn) to implement these tasks.

Archiver - ARCH (Optional) :- Archiver is the background process that copies exact copy of the online redolog file to the disk (destination provided in the parameter file log_archive_dest_1). This background process run only when the database is in archivelog mode Click Here - How to enable or Disable archivelog mode

Recovery Writer - RVWR (Optional) (New in Oracle 10g) :- RVWR background process runs if database is in flashback mode. It writes flashback data to the flashback logs in the fast recovery area(FRA). FRA destination parameter db_recovery_file_dest is set in database pfile Click Here - How to enable or disable flashback mode

Change Tracking Writer - CTWR (Optional)(New in Oracle 10g) :- CTWR process writes changed block information from buffer to the change tracking file. Block change tracking is enabled to improve the performance of RMAN incremental backup

Database resource manager - DBRM (Optional) :- Database resource manager takes care of setting plans to users and all other database resource management activities. It controls the use of computing resources

Virtual Scheduler for Resource Manager - VKRM (Optional) :- VKRM process Serves as centralized scheduler for Resource Manager activity.CPU scheduling for all managed Oracle processes is handled by VKRM. The process schedules managed processes in according to an active resource plan.

Logical Structure

Data Blocks :- Its the smallest unit of space allocation in the data files and also minimum unit of I/O for database. Default block size for the database is set in initialization parameter DB_BLOCK_SIZE. The standard block size of the database is 4KB or 8KB, most of the databases use 8K as its default block size. We can create the tablespace with non standard block size like 16KB, 32KB etc

Space allocation in data blocks 

Top most section of the block holds block header information, data fills up from bottom up

1. PCTUSED : Its the parameter specified at the block level. PCTUSED is the minimum percentage of the block that can be used when new data gets added during insert operation
2. PCTFREE : Its the parameter specified to reserve the space in the block for future update

Extents :- It comprises of group of data blocks which is the default unit of space allocation. When a datafile is added and even though no data is inserted, database allocates initial extent for data segment during segment creation. When the initial extent is full, database allocates incremental extent/next extent for this segment.

De-allocation of extents

Extents belonging to the user segment returned only if object is dropped via DROP command. Example :- Lets say, if 1000 rows of table sample_emp are deleted that doesn't claim data blocks to be used by other objects in the tablespace. Drop or truncating table will help reclaim space, another way to do so is defragmenting table

Segments :- It is a group of extents containing all the data for logical storage(table, index etc) within a tablespace. There are different kinds of segments

• User segments
• Temporary segments
• Undo segments

User segment stores the data for one user object in the form of table, index etc. When user inserts first row to a table, oracle database creates segments to that table.

Temporary segment gets allocated during query processing(like sorting operation). Temporary tables and their indexes consumes temporary segments which is session specific. 

Undo segment: Oracle maintains record of previous image of data when it gets changed. Record of the previous data stored is undo data. Undo segment gets allocated to consume undo data

Tablespace :-  Its the logical space where all segment is contained. Database contains SYSTEM, SYSAUX, UNDO, TEMP and user created tablespaces.

Permanent tablespace or user created tablespace contains user data stored in the form of tables or indexes, usually its application data

System tablespace contains data dictionary information and metadata information for user created objects

Undo tablespace is locally managed tablespace that stores undo data

Temporary tablespace contains session specific temporary data used for sorting operations. It also contains session specific data stored in temporary tables and indexes

Physical Structure

Datafiles :- Data gets stored in the datafiles under the tablespaces. Tablespace has one or more datafiles. User data spans across multiple datafiles

Control file :- Its the most important file which is required for the database to run, which is in binary format. Control file information such as 

• Database name
• Database creation time stamp
• Data files, online redo log files, and archived redo log files location
• Latest SCN information
• Tablespace information
• RMAN backups information etc

Redolog File :- This is the file where LGWR writes the database changes from redolog buffer to redolog files

Archivelog File :- Its the exact copy of the redolog file which is used in case of instance recovery

Parameter file :- It contains parameters required for database initialization

Enable or Disable Flashback Mode in Oracle11g Database

Enable Flashback Mode 

Step 1:- Log on to the database and check archivelog mode for database. Its mandatory that database must have enabled archivelog mode 

[oracle@pract1 fra]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 28 18:03:43 2016

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

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

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
KMDB             OPEN

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     1
Current log sequence           2

Step 2:- Looks like archivelog destination parameter is set to default location. We will change the archivelog destination to new location 

SQL> alter system set log_archive_dest_1='LOCATION=/u01/oradata/KMDB/arch' scope = both;

System altered.

Step 3:- Verify the archivelog destination which is pointing to new location

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/oradata/KMDB/arch
Oldest online log sequence     1
Current log sequence           2

Step 4:- Shutdown the database and start it in mount state

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2217992 bytes
Variable Size             494929912 bytes
Database Buffers          339738624 bytes
Redo Buffers                2396160 bytes
Database mounted.

Step 5:- We need to set archivelog mode and open the database

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oradata/KMDB/arch
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2

Step 6:- Check the flashback mode and flash recovery area parameter for the database

SQL> select name,  flashback_on from v$database;

NAME      FLASHBACK_ON
--------- ------------------
KMDB      NO

SQL> sho parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
recovery_parallelism                 integer     0

Step 7:- Now we will set the flash recovery area parameter

SQL> alter system set db_recovery_file_dest_size=2g scope = both;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/oradata/KMDB/fra' scope = both;

System altered.

Step 8:- Enable flashback mode and verify if its enabled

SQL> alter database flashback on;

Database altered.

SQL> select name,  flashback_on from v$database;

NAME      FLASHBACK_ON
--------- ------------------
KMDB      YES

Disable Flashback Mode

Step 1:- Disable the flashback mode using below mentioned command and verify the status

SQL> alter database flashback off;

Database altered.

SQL> select name,  flashback_on from v$database;

NAME      FLASHBACK_ON

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

KMDB      NO

Possible Errors during Activity 

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38707: Media recovery is not enabled.

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE

ORA-01126: database must be mounted in this instance and not open in any

instance

Case 1:- Archivelog not enabled

SQL> alter database flashback on;

alter database flashback on

*

ERROR at line 1:

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38707: Media recovery is not enabled.

Cause:- 

This error occurs when we try to enable the flashback mode without enabling archivelog mode 

Solution:- 

After the enabling the archivelog mode, issue should be resolved. Click Here on How to enable archivelog mode

Case 2:- DB_RECOVERY_FILE_DEST parameter no set in database parameter file

SQL> alter system set db_recovery_file_dest='/u01/oradata/KMDB/fra' scope = both;

alter system set db_recovery_file_dest='/u01/oradata/KMDB/fra' scope = both

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE

Cause:- 
This error occurs when we try to set DB_RECOVERY_FILE_DEST without setting the database recovery destination size parameter DB_RECOVERY_FILE_DEST_SIZE 

Solution:-
Set the database recovery destination size parameter DB_RECOVERY_FILE_DEST_SIZE 
SQL> alter system set db_recovery_file_dest_size=2g scope = both;

Case 3:- Database is open while enabling archivelog mode  

SQL> alter database archivelog; 

*
ERROR at line 1:

ORA-01126: database must be mounted in this instance and not open in any

instance

Cause:-

This error happens when you try to enable archivelog mode when the database is in open state 

Solution:- 

Shutdown the database and start the database in mount state. Then try to enable archivelog mode, it should work