ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

KUP-11024: This external table can only be accessed from within a Data Pump job.

Alert Log details: 

Trace file details:

Cause:

The primary cause of this issue is that an external table existed at some point in time but does not now. However, the database still believes the table exists since the dictionary information about the object has not been modified to reflect the change. When DBMS_STATS is run against the table in question, it makes a call out to the external table which fails because the object is not there.

There are many reasons that an external table may not exist including:

• - Temporary Datapump external tables have not been cleaned up properly. The dictionary information should have been dropped when the DataPump jobs completed.
• - An OS file for an External table has been removed without clearing up the corresponding data dictionary information. 
• Solution:
• Essentially the solution to this issue is to clean up the orphaned dictionary entries.
• SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1,2;
• or 
• select OWNER,OBJECT_NAME,OBJECT_TYPE, status,
  to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created
  ,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
  from dba_objects
  where object_name like 'ET$%'
  /
  
  select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
  from dba_external_tables
  order by 1,2
  /
• 
  Drop external temporary table
• 
  Ref. Doc: ID 1274653.1
• 
  

Calculate UNDO_RETENTION for given UNDO Tabespace

UNDO_RETENTION is a parameter in the init.ora initialization parameters file that specifies the time period in seconds for which a system retains undo data for committed transactions. The flashback query can go upto the point of time specified as a value in the UNDO_RETENTION parameter.

Optimal Undo Retention =
           Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC)

Actual Undo Size

SELECT SUM(a.bytes) "UNDO_SIZE"
FROM v$datafile a,
       v$tablespace b,
       dba_tablespaces c
WHERE c.contents = 'UNDO'
   AND c.status = 'ONLINE'
   AND b.name = c.tablespace_name
   AND a.ts# = b.ts#;

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM v$undostat;

DB Block Size

SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';

Optimal Undo Retention Calculation

Formula:
Optimal Undo Retention = 
           Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC)

Using Inline Views, you can do all calculation in one query

SQL Code:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
    SUBSTR(e.value,1,25)    "UNDO RETENTION [Sec]",
    ROUND((d.undo_size / (to_number(f.value) *
    g.undo_block_per_sec)))"OPTIMAL UNDO RETENTION [Sec]"
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) d,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))undo_block_per_sec
       FROM v$undostat
       ) g
WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'

Calculate Needed UNDO Size for given Database Activity

If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:

Formula:
Undo Size = Optimal Undo Retention × DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC
Using Inline Views, you can do all calculation in one query

SQL Code:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
      v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'

Top 99 Responsibilities of a DBA

Database Architecture Duties

1. Planning for the database's future storage requirements
2. Defining database availability and fault management architecture
3. Defining and creating environments for development and new release installation
4. Creating physical database storage structures after developers have designed an application
5. Constructing the database
6. Determining and setting the size and physical locations of datafiles
7. Evaluating new hardware and software purchase
8. Researching, testing, and recommending tools for Oracle development, modeling, database administration, and backup and recovery implementation, as well as planning for the future
9. Providing database design and implementation
10. Understanding and employing the optimal flexible architecture to ease administration, allow flexibility in managing I/O, and to increase the capability to scale the system
11. Working with application developers to determine and define proper partitioning

Backup and Recovery

12. Determining and implementing the backup/recovery plan for each database while in development and as the application moves through test and onto production
13. Establishing and maintaining sound backup and recovery policies and procedures
14. Having knowledge and practice of Oracle backup and recovery scenarios
15. Performing Oracle cold backups when the database is shut down to ensure consistency of the data
16. Performing Oracle hot backups while the database is operational
17. Performing Oracle import/export as a method of recovering data or individual objects
18. Providing retention of data to satisfy legal responsibilities of the company
19. Restoring database services for disaster recovery
20. Recovering the database in the event of a hardware or software failure
21. Using partitioning and transportable tablespaces to reduce downtime, when appropriate

Maintenance and Daily Tasks

22. Providing adjustment and configuration management of INIT.ORA
23. Adjusting extent size of rapidly growing tables and indexes
24. Administering database-management software and related utilities
25. Automating database startup and shutdown
26. Automating repetitive operations
27. Determining and setting critical thresholds for disk, tablespaces, extents, and fragmentation
28. Enrolling new users while maintaining system security
29. Filtering database alarm and alert information
30. Installing, configuring, and upgrading Oracle server software and related products installation
31. Logging Technical Action Reports (TARs); applying patches
32. Maintaining the "Database Administrator's Handbook"
33. Maintaining an ongoing configuration for database links to other databases
34. Maintaining archived Oracle data
35. Managing contractual agreements with providers of database-management software
36. Managing service level agreements with Oracle consultants or vendors
37. Monitoring and advising management on licensing issues while ensuring compliance with Oracle license agreements
38. Monitoring and coordinating the update of the database recovery plan with the site's disaster recovery plan
39. Monitoring and optimizing the performance of the database
40. Monitoring rollback segment and temporary tablespace use
41. Monitoring the status of database instances
42. Performing housekeeping tasks as required; purging old files from the Oracle database
43. Performing database troubleshooting
44. Performing modifications of the database structure from information provided by application developers
45. Performing monthly and annual performance reports for trend analysis and capacity planning
46. Installing new and maintaining existing client configurations
47. Performing ongoing configuration management
48. Performing ongoing Oracle security management
49. Performing routine audits of user and developer accounts
50. Performing translation of developer modeled designs for managing data into physical implementation
51. Performing correlation of database errors, alerts, and events
52. Planning and coordinating the testing of the new database, software, and application releases
53. Providing a focal point on calls to Oracle for technical support
54. Working as part of a team and providing 24x7 support when required

Methodology and Business Process

55. Coordinating and executing database upgrades
56. Coordinating upgrades of system software products to resolve any Oracle/operating system issues/conflicts
57. Creating error and alert processes and procedures
58. Creating standard entry formats for SQLNet files
59. Creating processes and procedures for functional and stress testing of database applications
60. Creating processes and procedures of application transport from DEV, to TEST, to PROD
61. Defining and maintaining database standards for the organization to ensure consistency in database creation
62. Defining database standards and procedures to cover the instance parameters, object sizing, storage, and naming. The procedures define the process for install/upgrade, corporate database requirements, security, backup/recovery, applications environment, source code control, change control, naming conventions, and table/index creation.
63. Defining the database service levels necessary for application availability
64. Defining methodology tasks for database software integration
65. Defining a methodology for developing and improving business applications
66. Creating a process to determine whether a new release is "stable" enough to be placed on the development system
67. Developing data-conversion processes for customization, testing, and production
68. Developing database test plans
69. Developing database administration procedures and responsibilities for production systems
70. Developing production migration procedures
71. Establishing and providing schema definitions, as well as tablespace, table, constraint, trigger, package, procedure, and index naming conventions
72. Facilitating design sessions for requirements gathering and defining system requirements
73. Providing database problem reporting, management, and resolution
74. Providing final approval for all technical architecture components that manage and exchange data, including database management software, serve hardware, data distribution management software, server hardware, data distribution management software, transaction processing monitors, and connecting client applications software
75. Providing processes for the setup of new database environments
76. Providing risk and impact analysis of maintenance or new releases of code
77. Providing standards and methods for database software purchasing
78. Providing standards and naming conventions
79. Handling multiple projects and deadlines

Education and Training

80. Attending training classes and user group conferences
81. Evaluating Oracle features and Oracle-related products
82. Understanding the Oracle database, related utilities, and tools
83. Understanding the underlying operating system as well as the design of the physical database
84. Understanding Oracle data integrity
85. Knowing the organization's applications and how they map to the business requirements
86. Knowing how Oracle acquires and manages resources
87. Knowing enough about the Oracle tool's normal functional behavior to be able to determine whether a problem lies with the tool or the database
88. Processing sound knowledge in database and system performance tuning
89. Providing in-house technical consulting and training
90. Staying abreast of the most current release of Oracle software and compatibility issues
91. Subscribing to database trade journals and web sources

Communication

92. Interfacing with vendors
93. Disseminating Oracle information to the developers, users, and staff
94. Training application developers to understand and use Oracle concepts, techniques, and tools that model and access managed data
95. Assisting developers with database design issues and problem resolutions, including how to run and understand the output from both TKProf and the Explain Plan utilities
96. Training interim DBAs and junior-level DBAs

Documentation

97. Creating and maintaining a database operations handbook for frequently performed tasks
98. Defining standards for database documentation
99. Creating documentation of the database environment

Source - http://appsdbaportal.blogspot.com/2009/04/top-99-responsibilities-of-dba.html

Meaning of Oracle “I” “G” and “C”

Meaning of “I” and “G” in Oracle and the Release format number of version

Meaning of I in Oracle:

The Oracle version starting of I. The starting in 1999 with version 6i, 8i and 9i, I signify “Internet” means stands for “Internet” and Oracle added the “I” to the version name to reflect support for the Internet with its built-in Java Virtual Machine (JVM). Oracle 9i added more support for XML in 2001.

Meaning of G in Oracle:

The starting in 2003 with version 10g and 11g, G signifies “Grid Computing” with the release of Oracle10g in 2003. Oracle 10g was introduced with emphasis on the “g” for grid computing, which enables clusters of low-cost, industry standard servers to be treated as a single unit. Upgrade Enterprise Manager 10g Grid Control Release 4 (10.2.0.4.0) or higher to Enterprise Manager 11g Grid Control Release 1 (11.1.0.1.0).

Meaning of C in Oracle:

In the Oracle Database 12c the "c" stands for "cloud". In addition to many new features, this new version of the Oracle Database implements a multitenant architecture, which enables the creation of pluggable databases (PDBs) in a multitenant container database (CDB). The Oracle Database 12c is a high-performance, enterprise-class database. Oracle released Oracle Database 12c into general availability July 1, 2013. According to Oracle, this is "the first database designed for the cloud." Oracle Database 12c also introduces 500 new features to the database, most notably pluggable databases and multitenant architecture. The Oracle Database 12c release 12.0.1.2 also features the Oracle Database 12c In-Memory, an optional add-on that provides in-memory capabilities. The in-memory option makes Oracle Database 12c the first Oracle database to offer real-time analytics.

How to find failed login attempts??

Enable auditing for failed login attempts
-- # 1 - Modifiy initialization parameters
alter system set audit_trail=db scope=spfile;
-- # 2 - Restart the database 
shutdown immediate
startup
-- # 3 - Enable auditing for failed login attempts
audit create session whenever not successful;
-- # 4 - Query the dba_audit_trail view to see failed login attempts 
select OS_USERNAME,USERNAME,USERHOST,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'), returncode
from dba_audit_trail 
where returncode > 0

Oracle 11g architecture Quick Reference

How to check if your Oracle client is 32 bit or 64 bit?

SOLUTION 1:
If logged into SQL Plus, the banner will tell you 64-bit if the 64-bit version is installed. If it does not specify 64-bit then it is 32-bit (even though it does not explicitly say so).

SOLUTION 2:
You can specifically query the info by running the following:
SELECT * FROM V$VERSION;

The banner will tell you 64-bit if the 64-bit version is installed. If it does not specify 64-bit then it is 32-bit.
SOLUTION 3:
Run the query to check the following:
select distinct address from v$sql where rownum<2;
If the address returned is 16 characters long, it is 64 bit.
If it is 32 bit you will get an 8 character address.

SOLUTION 4:
If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib are existing then it is 64 bit. If there is only an ORACLE_HOME/lib directory then it is 32 bit client.
*Headsup: In newer versions of the client, the library is not included and this directory may not exist.

How to Prevent a User to Drop Own Objects???

CREATE OR REPLACE TRIGGER trigger_prevent_drop BEFORE DROP ON DATABASE
BEGIN
IF ora_dict_obj_type = 'VIEW' 
AND ora_dict_obj_owner = 'QO112_1616' 
AND ora_login_user = 'QO112_1616' 
AND ora_dict_obj_name='SAMPLE_NEW'
THEN
raise_application_error (-20000, 'YOU CAN NOT DROP SAMPLE_NEW VIEW!');
END IF;
END;

We will get an erorr message when trying to delete object in my case VIEW

How to find Blocking Sessions??

What is Blocking Session??
Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn't release it before another sessions wants to update the same data. This will block the second until the first one has done its work. It mainly happens when a session issues an insert, update or delete command that changes a row. When the change occurs, the row is locked until the session either commits the change or rolls the change back.
How to Find Blocked/Blocking other sessions??

select 

t.process, 

t.sid, 

t.SERIAL#, 

t.blocking_session, 

t.USER#, 

t.USERNAME

from 

v$session t 

where blocking_session is not null;

Identify blocked objects

If you encounter a TM lock is means that two sessions are trying to modify some data but blocking each other.
What is TM lock - TM (DML enqueue lock)- This is a general table lock. Every time a session wants to lock a table (for an UPDATE, INSERT, or DELETE), a TM enqueue is requested. 
SELECT sid, id1 FROM v$lock WHERE TYPE='TM'

The ID you get from this query refers to the actual database object which can help you to identify the problem

Oracle DataPump “INCLUDE” parameter and limit of 4000 characters

Today I am trying to export certain tables (202 in count) using expdp, but I got “UDE-00014: invalid value for parameter, ‘include'”:
expdp SYSTEM/PASSWORD SCHEMAS=TEST_2016_S DIRECTORY=FULLBACKUPS
DUMPFILE=TEST_2016_S.DMP LOGFILE=TEST_2016_S_.LOG
INCLUDE=TABLE:"IN('AAA','BBB','CCC','DDD',
......
'ZZZ','AAAA','BBBB','CCCC')" COMPRESSION=ALL PARALLEL=8
“include” parameter accepts only 4000 characters. A simple workaround is to use a table to hold the names of the tables/objects and use this table with include parameter:
CREATE TABLE tables_list(table_name VARCHAR2(30) );
INSERT INTO table_list ( 'AAA' );
INSERT INTO table_list ( 'BBB' );
INSERT INTO table_list ( 'CCC' );
INSERT INTO table_list ( 'CCC' );
...
...
INSERT INTO table_list ( 'ZZZ' );
INSERT INTO table_list ( 'AAAA' );
INSERT INTO table_list ( 'BBBB' );
INSERT INTO table_list ( 'CCCC' );
COMMIT;

expdp SYSTEM/PASSWORD SCHEMAS=TEST_2016_S DIRECTORY=FULLBACKUPS
DUMPFILE=TEST_2016_S.DMP LOGFILE=TEST_2016_S_.LOG include=TABLE:"IN (SELECT table_name FROM table_list)" 

How to check ORA error or Viewing Errors with the oerr Utility

Many Oracle products on UNIX have a utility called "oerr" that can be used to aid error investigation. 
The "oerr" utility is a shell script that is located in the ORACLE_HOME/bin directory. "oerr" requires the ORACLE_HOME to be set and two parameters when called: the facility which is the three to four letter code to the left of dash and the error number.

How to enable/disable Archivelog mode in Oracle?

There are two types of logging modes in Oracle database :

ARCHIVELOG : In this type of logging whatever oracle writes in a redo log file related to transactions in database, saved to another location after a log file has been filled . This location is called Archive location. if database is in Archive log mode then in case of any disaster, we can recover our database upto the last commit and user don't have to reenter their data. Until a redo log file is not written to the Archive location it cannot be reused by oracle to write redo related data.

NOARCHIVELOG : In this type of logging whatever oracle writes in a redo log file related to transactions in database must be overwritten when all the log files have been filled. In this type of logging we can recover our database upto the last consistent backup we have with us, after that users have to reenter their data.

How to check log mode

SQL> archive log list

How to enable ARCHIVELOG mode

SQL> shut immediate;

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

How to disable ARCHIVELOG mode

SQL> shut immediate

SQL> startup mount

SQL> alter database noarchivelog;

SQL> alter database open;

SQL> archive log list

How to recreate a control file in Oracle Database?

Oracle provides the "alter database backup controlfile to trace" command to copy the create database syntax into a trace file in your user dump directory.

Use this command to re-create a control file only if:

• All copies of your existing control files have been lost through media failure.
• You want to change the name of the database.
• You want to change the maximum number of redo log file groups, redo log file members, archived redo log files, datafiles, or instances that can concurrently have the database mounted and open.

SQL>  show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /mnt/ora01/app/oracle/diag/rdbms/QA/QA/trace
[TEST:QA] pwd
/mnt/ora01/app/oracle/diag/rdbms/QA/QA/trace
[TEST:QA] ls -lrt QA_ora_1858.trc
-rw-r-----. 1 oracle oinstall 10645 Jan 23 11:21 QA_ora_1858.trc

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "QA" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/mnt/ora02/oradata/QA/redo01a.rdo',
    '/mnt/ora03/oradata/QA/redo01b.rdo'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 2 (
    '/mnt/ora02/oradata/QA/redo02a.rdo',
    '/mnt/ora03/oradata/QA/redo02b.rdo'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 3 (
    '/mnt/ora02/oradata/QA/redo03a.rdo',
    '/mnt/ora03/oradata/QA/redo03b.rdo'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 4 (
    '/mnt/ora02/oradata/QA/redo04a.rdo',
    '/mnt/ora03/oradata/QA/redo04b.rdo'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 5 (
    '/mnt/ora02/oradata/QA/redo05a.rdo',
    '/mnt/ora03/oradata/QA/redo05b.rdo'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 6 (
    '/mnt/ora02/oradata/QA/redo06a.rdo',
    '/mnt/ora03/oradata/QA/redo06b.rdo'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 7 (
    '/mnt/ora02/oradata/QA/redo07a.rdo',
    '/mnt/ora03/oradata/QA/redo07b.rdo'
  ) SIZE 500M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/mnt/ora03/oradata/QA/system01.dbf',
  '/mnt/ora03/oradata/QA/sysaux01.dbf',
  '/mnt/ora06/oradata/QA/undotbs01.dbf',
  '/mnt/ora01/oradata/QA/users01.dbf',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_01.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_02.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_03.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_04.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_05.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_06.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_07.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_08.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_09.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_10.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_11.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_12.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_13.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_14.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_15.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_01.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_02.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_03.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_04.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_05.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_06.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_07.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_08.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_09.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_10.DBF'
CHARACTER SET WE8MSWIN1252
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/mnt/ora01/app/oracle/fast_recovery_area/QA/archivelog/2016_01_23/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/mnt/ora04/oradata/QA/temp01.dbf'
     SIZE 61865984  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER TABLESPACE TEMP001 ADD TEMPFILE '/mnt/ora05/oradata/QA/TEMP001.DBF'
     SIZE 10000M REUSE AUTOEXTEND ON NEXT 1048576  MAXSIZE 50000M;
-- End of tempfile additions.
--

The listener supports no services

When i am trying to connect newly created database I am getting below error
ERROR
ORA-12514: TNS: Listener does not currently know of service requested in connect descriptor

When I checked my listener status it shown below

$ /mnt/ora01/app/oracle/product/11.2.0/db_1/bin/lsnrctl status LISTENER_QA

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 13-JAN-2016 13:47:33

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.240.230)(PORT=1530)))

STATUS of the LISTENER

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

Alias                     LISTENER_QA

Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date                13-JAN-2016 13:44:43

Uptime                    0 days 0 hr. 2 min. 50 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /mnt/ora01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File         /mnt/ora01/app/oracle/diag/tnslsnr//listener_qa/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.240.230)(PORT=1530)))

The listener supports no services

The command completed successfully

I tried multiple option but no luck. 

Reason behind the issue:
================
- Listener is a process that runs on database server and is responsible for receiving client connection and passing connection to database.
- If you are using default port (1521) for listener, then database will automatically register the instance with listener.
- But what if you are not using default listener port (1521). Instead, you are using some other port – Say 1530. Oracle doesnt do dyamic registration on non-default listener port automatically.
- If you are using non default port (other than 1521). In local_listener parameter we can specify non-default ports on which listener is running and Oracle will register services on those ports.
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.240.230)(PORT=1530)))' sid='QA' scope=spfile;
- As soon as I set the value of local_listener, PMON will know the port number on which listener is running and it will dynamically register the instance with listener.

As sys/system user

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.240.230)(PORT=1530)))' sid='QA' scope=spfile;

System altered.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 Total System Global Area 4.1823E+10 bytes

Fixed Size                  2237488 bytes

Variable Size            4429188048 bytes

Database Buffers         3.7313E+10 bytes

Redo Buffers               78639104 bytes

Database mounted.

sDatabase opened.

SQL>show parameter local

 NAME                                 TYPE        VALUE

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

local_listener                       string      (DESCRIPTION=(ADDRESS=(PROTOCO

                                                 L=tcp)(HOST=172.16.240.230)(PO

                                                 RT=1530)))

log_archive_local_first              boolean     TRUE

parallel_force_local                 boolean     FALSE

SQL> alter system register;

System altered.

Above steps fixed my issue.

Check the listener status now

$/mnt/ora01/app/oracle/product/11.2.0/db_1/bin/lsnrctl status LISTENER_QA

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 13-JAN-2016 14:25:41

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.240.230)(PORT=1530)))

STATUS of the LISTENER

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

Alias                     LISTENER_QA

Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date                13-JAN-2016 14:22:39

Uptime                    0 days 0 hr. 3 min. 2 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /mnt/ora01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File         /mnt/ora01/app/oracle/diag/tnslsnr//listener_qa/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.240.230)(PORT=1530)))

Services Summary...

Service "QA.tiermeddc.inc" has 1 instance(s).

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

Service "QAXDB.tiermeddc.inc" has 1 instance(s).

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

The command completed successfully

My TNSNAMES.ora as below

QA =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.240.230)(PORT = 1530))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = QA.domain.inc)

    )

  )  

Also add host name to /etc/hosts