Connect Via SCAN Intermittently Fails With ORA-12516: TNS:listener Could Not Find Available Handler With Matching Protocol Stack

SYMPTOMS:

• multinode (3+) RAC  cluster
• application using SCAN and a dedicated service (here serv1) to connect to the database
• service is configured to run on instances 1 & 2 (preferred)
• intermittently connect attempts fail with error ORA-12516: TNS:listener could not find available handler with matching protocol Stack

CAUSE:

The status of the service used to connect to the database shows online on instances 1 & 2:

ora.racdb1.serv1.svc
  1 ONLINE ONLINE racdb11
  2 ONLINE ONLINE racdb12

however the node listener on node 2 is having some problem, 'crsctl stat res -t' shows:

ora.LISTENER.lsnr
  ONLINE ONLINE racnode1
  ONLINE INTERMEDIATE racnode2 CHECK TIMED OUT
  ONLINE ONLINE racnode3

Running 'lsnrctl status LISTENER' shows that the listener has no TCP endpoints open:

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
The listener supports no services
The command completed successfully

It is unclear how the listener got into this state., it prevents the instance(s) on node racnode2 to register their services with the node listener, since the instance connects to the listener via the TCP port 1525.

The instances can however register their services with the SCAN listeners, so the ORA-12516 error seems to be reported whenever a SCAN listener tries to hand off a connect request to the listener on node racnode2.

SOLUTION:

1. Find the PID of the node listener - e.g.:

$ ps -ef | grep tns
oracle 18135 1 0 Oct 3 ? 348:26 /u01/app/grid/11203/bin/tnslsnr LISTENER_SCAN1 -inherit
oracle 21652 1 0 Feb 18 ? 96:44 /u01/app/grid/11203/bin/tnslsnr LISTENER -inherit

2. Kill the listener using:

kill -9 21652

3. Restart the listener using:

srvctl start listener -n racnode2

4. Make sure the listener is now listening on TCP endpoints

lsnrctl status LISTENER

5. One minute after the listener has been started check if it shows services:

lsnrctl services LISTENER

Reference: Connect Via SCAN Intermittently Fails With ORA-12516: TNS:listener Could Not Find Available Handler With Matching Protocol Stack (Doc ID 1638175.1)

ORA-12516: TNS:listener could not find available handler with matching protocol stack

CAUSE

 This is an issue with the number of database process/sessions. There are some bugs that reduce the processes/sessions required, and the fixes are included in Patch:19197270 AD:R12.AD.C.DELTA.6 PATCH and later.

SOLUTION


As a workaround, bouncing the database, and raising the number of processes/sessions should let you complete the patch (you will need to restart the patch after you do the workaround). (NOTE: Simply bouncing the database might allow you to continue.)

Reference: adop fails with "ORA-12516: TNS:listener could not find available handler with matching protocol stack" (Doc ID 2105049.1)

Row Level Locking with Hybrid Columnar Compression (HCC)

Database Storage Optimization

Row Level Locking with Hybrid Columnar Compression (HCC)

Hybrid Columnar Compression (HCC) is a very unique feature in that it gives a very big gain in compression ratios (10x on average), makes table scans run a lot faster (up to 10x), and all this not for a subset of DW scenarios, but for every DW use case. HCC is used by most if not all Exadata DW customers and POCs.

The only real limitation of HCC in first release was that it was not suited for data that is actively being modified (thought it can be still used in such environments as part of an ILM strategy). As of Oracle Database 11g Release 2, Hybrid Columnar Compression didn’t support row level locking like with other table formats in Oracle, such as non-compressed or Advanced Row compressed tables.

Oracle’s Hybrid Columnar Compression technology is a different and new method for organizing data within a database block. As the name implies, this technology utilizes a combination of both row and columnar methods for storing data. This hybrid approach achieves the compression benefits of columnar storage, while avoiding the performance shortfalls of a pure columnar format. A logical construct called the compression unit (CU) is used to store a set of hybrid columnar compressed rows. When data is loaded, column values for a set of rows are grouped together, compressed and stored in a compression unit. With Oracle Database 11g Release 2, HCC supported compression unit level locking; that is, locking the entire compression unit on an active transaction modifying a single row in the unit.

Row level locking is a requirement for mixed OLTP and DW environments and is a definite requirement for OLTP applications. For mixed workloads, ILM approach likely works fine, but needs to be used much more cautiously. Lack of row level locking was a major restriction for HCC, and as a result HCC was mainly targeted for Data Warehouse environments. With Oracle Database 12c, HCC tables now support row level locking. In order to do so, it needs to be able to store the locking information on per row basis (including whether or not a row is locked, and if locked the locking transaction information). The approach is similar to locking in non-compressed or Advanced Row compressed tables as described next.

For non-compressed or Advanced Row compressed tables Oracle stores a lock byte (index into the list of active transactions in the data block) in the row header to indicate whether or not the row is locked, and if locked then information about the locking transaction. This approach works well when there can be high concurrency and many active transactions in a block at a given time. But in the most typical cases allocating a byte per row is an over kill.

For HCC tables, the approach is similar but a more efficient. Rather than allocating the lock byte upfront, Oracle allocates lock bit(s) on demand and based on number of active and anticipated transactions on a compression unit. It could pre-allocate enough bits to support anticipated concurrency on the block (dependent on INITRANS setting for the table), OR with a CU level lock for the entire compression unit.  Since most of the times there won’t be any transactions modifying data in the CUs, there is no need to allocate any bits per row for locking. If there are active transactions and a need for row level locking, Oracle allocates more bit(s) per row to be able to support row level locking. 1 bit per row will be able to support 1 active transaction, 2 bits supports up to 3 concurrent transactions, 3 bits supports up to 7 concurrent transactions and so on... The maximum need is to support 255 active transactions per compression unit, as the number of ITLs (interested transaction list) in the data blocks is limited to 255. That is, we will need maximum of 1 byte per row.

As you see, this approach doesn’t pre-allocate 1 byte per row; but allocates it on demand. It is possible to support 255 concurrent transactions (like uncompressed or Advanced Row compressed blocks); have CU level locking; or anywhere in between. In the worst case, Oracle needs 1 byte per row to support this; but in most practical cases, we will be much better than that.

The next question that comes to one’s mind is where does the space for allocating locking bit(s) come from? Just like non-compressed tables use PCTFREE to extend the ITLs as needed, Oracle uses PCTFREE in the blocks to extend ITLs and allocate any additional lock bits for HCC tables. This is the most natural place to allocate the space from.

And where are lock bits stored? The lock information is stored uncompressed in the CU header. Today Oracle already stores other information for rows in the CU header. Lock bit(s) are stored in the CU header along with other per row information.

The ability to have row level locking has further widened the applicability of HCC’s columnar and compression technology to OLTP or mixed workload environments. It has allowed for making the use of HCC wide spread and operationally complete. Row Level Locking for Hybrid Columnar Compression is part of Advanced Compression Option which enables the highest levels of data compression and provides enterprises with tremendous cost-savings and performance improvements due to reduced I/O.

Source: https://blogs.oracle.com/DBStorage/entry/row_level_locking_with_hybrid

CREATING DBLINK BETWEEN ORACLE AND SQLSERVER USING ODBC GATEWAY METHOD

Creating DBLINK between ORACLE AND SQLSERVER using ODBC gateway method

Configuration Steps:

1.       Install ODBC Gateway 11gR2

1.1   Download the Oracle Database Gateways 11g Release 2 (11.2.0.1.0) for Microsoft Windows (32-bit) from the  

        following link :

                 http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html

1.2   unzip the file and install the gateway

   

          

    

    

   

   

    

   

2.       Create DSN in windows

Go to Control Panel àAdministrative Tools àData sources (ODBC)àSystem DSN

2.1   click add and follow the below steps

2.2   Select SQL SERVER from driver list. 

2.3   Next Screen give the following details

             Name:  <DSN NAME >     // This name will use in initdg4odbc.ora file as a DSN NAME

            Description: any message 

                           

                 

             Server : <Sql server name >

                                 

                                 

 

                          

                             

2.4   Select Sql Authentication and provide the sql server username and password.

2.5   Connection will established when all the given details (username, password, server) correct otherwise it will show error. 

                  

    

2.6   Once the connection established, it will show the database list and other things related to sql server. 

                    

2.7   Follow the screens and finally test the connection. Now , The DSN name have created and added to the list. 

3.       Edit the initdg4odbc.ora file

Go to initdg4odbc.ora file location and edit the following parameters

     Location:  E:\product\11.2.0\tg_1\hs\admin

     Parameters:

              HS_FDS_CONNECT_INFO = mssql     // Give the DSN name which have already created

              HS_FDS_TRACE_LEVEL = off

4.       Configure the listener

Location: E:\product\11.2.0\tg_1\NETWORK\ADMIN

                dg4odbc =

 (ADDRESS_LIST=

      (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.1.97)(PORT=1530))

 )

SID_LIST_dg4odbc=

  (SID_LIST=

      (SID_DESC=

         (SID_NAME=dg4odbc)

         (ORACLE_HOME=E:\product\11.2.0\tg_1)

         (PROGRAM=dg4odbc)

      )

  )

4.1   Run the dg4odbc listener Manually

C:\lsnrctl start dg4odbc

5.       Configure the tnsnames in oracle box(Linux OS)

Location:   $ cd /d1/oracledb/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

dg4odbc =

 (ADDRESS_LIST=

      (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.1.97)(PORT=1530))

 )

SID_LIST_dg4odbc=

  (SID_LIST=

      (SID_DESC=

         (SID_NAME=dg4odbc)

         (ORACLE_HOME=E:\product\11.2.0\tg_1)

         (PROGRAM=dg4odbc)

      )

  )

$ tnsping dg4odbc

6.       Create a dblink

 Sql> create public database link mlink

          Connect to “sa”

          Identified by “apps”

          Using ‘dg4odbc’;       // this is tnsname entry

             

7.       Run the query based on sqlserver tables. Now, you can get the result from sqlserver.

ORACLE to ORACLE  DBlink

TNS entry on both the nodes

General  Ledger Super User -> setup -> system -> Database links

Apps Read Only schema creation in R12.2

check the invalid objects before creating apps read only schema

sqlplus / as sysdba

create user appsro identified by appsro;
grant connect, resource to appsro;
grant create synonym to appsro;
commit;

sqlplus apps/oracle1234
set head off
set newpage none
set pagesize 9999
spool create_synonyms.sql
select 'create synonym ' || OBJECT_NAME || ' for ' || OWNER ||'.' ||OBJECT_NAME || ';' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM');
spool off

spool grant_select.sql
select 'grant select on '|| OWNER ||'.' ||OBJECT_NAME || ' to appsro;' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM');
spool off
exit


sqlplus / as sysdba
@grant_select.sql
exit;

sqlplus SYSTEM/manager121
alter session set current_schema=APPS;
exec AD_ZD_PREP.ENABLE_CUSTOM_USER('APPSRO');

sqlplus appsro/appsro
@create_synonyms.sql
exit;

after completion of above steps validate the invalid objects count.

Issue:
======
SQL> @create_synonyms.sql

But many synonyms creation failed while executing create_synonyms.sql. like
ORA-38818: illegal reference to editioned object APPS.OE_ENDECA_FULFILL_DELAY_V..


Solution:
========
Before running create_synonym pls do the below steps.

sqlplus SYSTEM
SQL> alter session set current_schema=APPS;
SQL> exec AD_ZD_PREP.ENABLE_CUSTOM_USER('CUSTOM_SCHEMA');

Ref: Create And Register CUSTOM Schema On EBS 12.2 (Doc ID 1929668.1)

Delete Server Pool in Oracle RAC

crsctl status serverpool -f

crsctl delete serverpool 

Day to Day Activities of Oracle DBA - Checklist

DBA Checklist (Activities of Oracle DBA):

-->Daily DBA Checklist
-->Daily Night DBA Checklist
-->Weekly DBA Checklist
-->Weekly Tuning DBA Checklist
-->Monthly DBA Checklist
-->Quarterly DBA Checklist
-->One Time Activity DBA Checklist

Daily DBA Checklist:

* Health check of the Database Instance and Listener.
* Viewing the Alert log file and/or check Alert log in regular interval to solve the ORA errors.
* Check any session blocking the other session and oracle locks. Clear locks
* Check long running UNIX process
* Ensure that there are no DBMS_JOBS with the status of failed or broken. Also last refresh times of all running jobs should be current.
* Check all cron house keeping script logs
* Daily Tablespace Utilization.
* Rebuilding of Indexes, if bulk load of data is inserted.
* Check the temporary tablespace/files.
* Check the UNDO tablespace and retaintion.
* Monitor the Unix /tmp and /var Location
* Monitor the UTL_FILE location.
* Monitor all Database file system or drive.
* Monitor Archive Log location.
* Verify success of database archiving to tape
* Monitoring Backups.
* Monitoring the log files, backups, database space usage and the use of system resources.
* Monitoring Production Database Performance
* Find high CPU/Memory/Physical IO consuming processes and trace the SQL/From running behind and Update to Application team.
* Check OEM Agent is running Or not in each node.
* Verify DBSNMP is running
* Verify success of database backup
* Daily RMAN(Incremental+Cumulative)/Data Pump export backups after business hours.
* User Management. User Profile monitoring.
* Monitor User account GRACE period.
* Check Invalid objects and recompile.
* Check and monitor Audit log or table.
* Backup your CRONTAB or Win Schedular
* Most Important - read DBA manuals for one hour
* Most Important - Check your oracle licence and do not run/execute/create beyond the oracle Licence policy.

Daily Night DBA Checklist:

* Look for objects that break rules (Check for Huge NEXT_EXTENT or MAX_EXTENT)
* Check the objects reaching to it’s Max extents
* Note, All tables should have unique primary keys, so check missing/disabled PK and
* Check for Block corruption


Weekly DBA Checklist:

* Database Growth Comparision.
* Identify bad growth projections.
* RMAN full databsae(Level 0) backup.
* Weekly cold backup during maintaince windows.
* Analyze Database and Schemas to gather statistics
* Check Index monitoring Usage to validate the Index usage which is not used yet. Drop/Mark Unuseable the unused Indexes.
* All indexes should use INDEXES tablespace and shoud not user DATA tablespace.
* All index datafile should not be in same file system where the DATA tablespace or SYSTEM/SYSAUX/UNDO/TEMP datafiles are.
* Look in SQL*Net logs for errors, issues (Both in Client side & Server side)
* Archive all Alert Logs and application log to history
* Check the number of log switch per hour
* How_much_redo_generated_per_hour
* Check free quota limited available of each user
* Truncate the listener.log file in the $ORACLE_HOME/network/log, if the listener log has increased to a size > than 500 MB. Ensure the space is released, otherwise 'reload' listener.

Weekly Tuning DBA Checklist:

* Check the Chaining & Migrated Rows
* Check the size of tables & check weather it need to partition or not
* Check the objects having the more extents
* Check the tables having FK but there is no Index
* Check the tables having no Indexes and tables having more Indexes
* Check the frequently pin objects & place them in separate tablespace & in cache
* Check the objects reload in memory many time
* Check open cursor not reaching to the max limit
* Check locks not reaching to the max lock
* Check I/O of each data file

Monthly DBA Checklist:

* Index Rebuild.
* Tablespace Reorganization.
* Bounce critical database once a month (If no cold backup configured)
* Look for Harmful Growth Rates
* Review database file activity.  Compare to past output to identify trends that could lead to possible contention.
* Investigate fragmentation (e.g. row chaining, etc.).
* Check location of data file also check auto extendable or not
* Check default tablespace & temporary tablespace of each user
* Check the Extents of each object and compare if any object extent are overriddenwhich is define at tablespace level
* Tablespace need coalescing
* Check the overall database statistics
* Trend Analysis of objects with tablespace, last analyzed, no. of Rows, Growth indays & growth in KB

Quarterly DBA Checklist:

* Patching
* Database Reorganization
* Check the quota of non-system tables in system tablespace.
* Bounce most critical database once a month (If no cold backup configured)
* Review common Oracle tuning points such as cache hit ratio, latch contention, and other points dealing with memory management

One Time Activity DBA Checklist:

* Database user creation with required privileges
* Make the portal of Oracle Predefined error with possible solution.
* Check database startup time(if not 24X7)
* Check location of control file
* Check location of log file
* Prepare the Backup strategy and test all the recovery scenario