The Listener Supports no services #ora-12541 #ora-12505 #listener

 The Listener Supports No Services #ORA-12541#ORA-12505

lsnrctl status

the output of the last two statements would be like 

The listener supports no services

The command completed successfully

Solution 

lsnrctl services < name of the listener >

connect SQLPLUS 

SQL> show parameter local_listener

NAME                                 TYPE        VALUE

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

local_listener                       string

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= IP-ADDRESS OR HOSTNAME)(PORT=1521)))' scope=both;

SQL> alter system register;

System altered.

SQL> exit

[oracle@OEL_VM_11 ~]$ lsnrctl services < name of the listener >

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.15)(PORT=1521 )))

Services Summary...

Service "ggf" has 1 instance(s).

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

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         LOCAL SERVER

Service "ggfXDB" has 1 instance(s).

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

    Handler(s):

      "D000" established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER <machine: OEL_VM_11, pid: 12790>

         (ADDRESS=(PROTOCOL=tcp)(HOST=OEL_VM_11)(PORT=15699))

The command completed successfully

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

UNDO Tablespace total and used size in MB or GB

To check the size of each undo tablespace along with total and used sizes.

--Undo TBS size in MB

select

a. tablespace_name,

sum(a.bytes)/(1024*10240) "Allocation in MB",

sum(b.bytes)/(1024*10240) "Used in MB"

from

dba_data_files a,

dba_segments b

where

a.tablespace_name = b.tablespace_name

and

a.tablespace_name in (select tablespace_name from dba_undo_extents) 

group by

a.tablespace_name;

--Undo TBS size in GB

set linesize 200

set pagesize 200  

select 

   tablespace_name, 

   --round(TABLESPACE_SIZE/1024,2) size_MB,

   round(TABLESPACE_SIZE/1024/1024,2) size_GB,

   round(used_space/1024/1024,2) Used_MB,

   --round(used_space/1024/1024,2) Used_GB,

   round(used_percent,2) Percentage_used

from

   dba_tablespace_usage_metrics

 where tablespace_name in (select tablespace_name from dba_undo_extents) 

order by 2;

Resolving Tempfile is failing verification test or the error message ORA-01187: cannot read from file because it failed verification tests + ORA-01110 on tempfiles

Following are the commands and steps to resolve  ORA-01187: cannot read from file because it failed verification tests + ORA-01110 on tempfiles

-----Tempfile drop and recreate----

 

--Enable restricted session 

ALTER SYSTEM ENABLE RESTRICTED SESSION;

--Create another Temporary Tablespace TEMP1

CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE  'D:\app\oracle\oradata\......\TEMP02.DBF' SIZE 2G;

 

--Move Default Database temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

--shutdown database

shutdown immediate;

--startup database

startup;

--Drop the original temp tablespace.

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

--Create TEMP tablespace

 

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'D:\app\oracle\oradata\.......\TEMP01.DBF' SIZE 2G;

--Make TEMP as default tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

--shutdown database

shutdown immediate;

--startup database

startup;

--Drop temporary for tablespace temp1

 

DROP TABLESPACE TEMP1 INCLUDING CONTENTS AND DATAFILES;

ALTER SYSTEM DISABLE RESTRICTED SESSION;

 

Query to find Tablespace Space Utilization

Query to find Tablespace Space Utilization in Bytes

SET lines 132 pages 66 feedback off COLUMN tablespace_name        format a15             heading 'Tablespace|(TBS)|Name' COLUMN autoextensible         format a6              heading 'Can|Auto|Extend' COLUMN files_in_tablespace    format 999             heading 'Files|In|TBS' COLUMN total_tablespace_space format 99,999,999,999 heading 'Total|Current|TBS|Space' COLUMN total_used_space       format 99,999,999,999 heading 'Total|Current|Used|Space' COLUMN total_tablespace_free_space format 99,999,999,999 heading 'Total|Current|Free|Space' COLUMN total_used_pct              format 999.99      heading 'Total|Current|Used|PCT' COLUMN total_free_pct              format 999.99      heading 'Total|Current|Free|PCT' COLUMN max_size_of_tablespace      format 99,999,999,999 heading 'TBS|Max|Size' COLUMN total_auto_used_pct         format 999.99      heading 'Total|Max|Used|PCT' COLUMN total_auto_free_pct         format 999.99      heading 'Total|Max|Free|PCT' TTITLE left _date center Tablespace Space Utilization Status Report skip 2 WITH tbs_auto AS      (SELECT DISTINCT tablespace_name, autoextensible                  FROM dba_data_files                 WHERE autoextensible = 'YES'),      files AS      (SELECT   tablespace_name, COUNT (*) tbs_files,                SUM (BYTES) total_tbs_bytes           FROM dba_data_files       GROUP BY tablespace_name),      fragments AS      (SELECT   tablespace_name, COUNT (*) tbs_fragments,                SUM (BYTES) total_tbs_free_bytes,                MAX (BYTES) max_free_chunk_bytes           FROM dba_free_space       GROUP BY tablespace_name),      AUTOEXTEND AS      (SELECT   tablespace_name, SUM (size_to_grow) total_growth_tbs           FROM (SELECT   tablespace_name, SUM (maxbytes) size_to_grow                     FROM dba_data_files                    WHERE autoextensible = 'YES'                 GROUP BY tablespace_name                 UNION                 SELECT   tablespace_name, SUM (BYTES) size_to_grow                     FROM dba_data_files                    WHERE autoextensible = 'NO'                 GROUP BY tablespace_name)       GROUP BY tablespace_name) SELECT a.tablespace_name,        CASE tbs_auto.autoextensible           WHEN 'YES'              THEN 'YES'           ELSE 'NO'        END AS autoextensible,        files.tbs_files files_in_tablespace,        files.total_tbs_bytes total_tablespace_space,        (files.total_tbs_bytes - fragments.total_tbs_free_bytes        ) total_used_space,        fragments.total_tbs_free_bytes total_tablespace_free_space,        (  (  (files.total_tbs_bytes - fragments.total_tbs_free_bytes)            / files.total_tbs_bytes           )         * 100        ) total_used_pct,        ((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100        ) total_free_pct,        AUTOEXTEND.total_growth_tbs max_size_of_tablespace,        (  (  (  AUTOEXTEND.total_growth_tbs               - (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes                 )              )            / AUTOEXTEND.total_growth_tbs           )         * 100        ) total_auto_used_pct,        (  (  (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes)            / AUTOEXTEND.total_growth_tbs           )         * 100        ) total_auto_free_pct   FROM dba_tablespaces a, files, fragments, AUTOEXTEND, tbs_auto  WHERE a.tablespace_name = files.tablespace_name    AND a.tablespace_name = fragments.tablespace_name    AND a.tablespace_name = AUTOEXTEND.tablespace_name    AND a.tablespace_name = tbs_auto.tablespace_name(+);

Query to find Tablespace Space Utilization in MBs

clear breaks clear computes col database_name noprint new_value db_name col TODAY noprint NEW_VALUE _DATE set termout off select to_char(SYSDATE,'fmMonth DD, YYYY HH24:MI:SS') TODAY from DUAL; set termout on TTITLE OFF select name database_name from v$database; TTITLE left _DATE CENTER "Tablespace used by " db_name " database" Skip 1 - CENTER "-----------------------------------" skip 2 col tablespace_name format a15 heading 'Tbsc|Name' col initial_extent_size format 99,99,999 heading 'Initial|Extent|in (KB)' col next_extent_size format 99,99,999 heading 'Next|Extent|in (KB)' col min_extents format 99 heading 'Min|Extent' col max_extents heading 'Max|Extent' col status format a8 heading 'Status' col contents format a9 heading 'Type' col avail format 99,99,990.90 heading 'Total Size|(in Mb)' col free format 99,99,990.90 heading 'Free |(in Mb)' col used format 99,99,990.90 heading 'Used |(in Mb)' col extent_management format a10 heading 'TYPE |(Extent |Mgmnt )' SELECT         dts.tablespace_name,         initial_extent/1024 initial_extent_size,         next_extent/1024 next_extent_size,         NVL(ddf.bytes / 1024 / 1024, 0) avail,         NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,         NVL(dfs.bytes / 1024 / 1024, 0) free,         TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00') "Used %",         dts.contents,         dts.extent_management,         dts.status FROM         sys.dba_tablespaces dts,         (select tablespace_name, sum(bytes) bytes                 from dba_data_files group by tablespace_name) ddf,                         (select tablespace_name, sum(bytes) bytes                                 from dba_free_space group by tablespace_name) dfs                                 WHERE                                         dts.tablespace_name = ddf.tablespace_name(+)                                 AND dts.tablespace_name = dfs.tablespace_name(+)                                 AND NOT (dts.extent_management like 'LOCAL'                                 AND dts.contents like 'TEMPORARY')                                 UNION ALL                                 SELECT dts.tablespace_name,                                         initial_extent/1024 initial_extent_size,                                         next_extent/1024 next_extent_size,                                         NVL(dtf.bytes / 1024 / 1024, 0) avail,                                         NVL(t.bytes, 0)/1024/1024 used,                                         NVL(dtf.bytes - NVL(t.bytes, 0), 0)/1024/1024 free,                                         TO_CHAR(NVL(t.bytes / dtf.bytes * 100, 0), '990.00') "Used %",                                         dts.contents,                                         dts.extent_management,                                         dts.status                                 FROM                                         sys.dba_tablespaces dts,                                         (select tablespace_name, sum(bytes) bytes                                         from dba_temp_files group by tablespace_name) dtf,                                                 (select tablespace_name, sum(bytes_used) bytes                                                 from v$temp_space_header group by tablespace_name) t         WHERE         dts.tablespace_name = dtf.tablespace_name(+)         AND dts.tablespace_name = t.tablespace_name(+)         AND dts.extent_management like 'LOCAL'         AND dts.contents like 'TEMPORARY'; set feedback on set verify on clear breaks clear computes clear columns set pagesize 100 set echo on set echo off set linesize 999 set termout off

Query to find Tablespace Space Utilization in GBs

set echo off set feedback off verify off pagesize 100 linesize 120 clear breaks clear computes col database_name noprint new_value db_name col TODAY noprint NEW_VALUE _DATE set termout off select to_char(SYSDATE,'fmMonth DD, YYYY HH24:MI:SS') TODAY from DUAL; set termout on TTITLE OFF select name database_name from v$database; TTITLE left _DATE CENTER "Tablespace used by " db_name " database" Skip 1 - CENTER "-----------------------------------" skip 2 col tablespace_name format a15 heading 'Tbsc|Name' col initial_extent_size format 99,99,999 heading 'Initial|Extent|in (KB)' col next_extent_size format 99,99,999 heading 'Next|Extent|in (KB)' col min_extents format 99 heading 'Min|Extent' col max_extents heading 'Max|Extent' col status format a8 heading 'Status' col contents format a9 heading 'Type' col avail format 99,99,990.90 heading 'Total Size|(in Gb)' col free format 99,99,990.90 heading 'Free |(in Gb)' col used format 99,99,990.90 heading 'Used |(in Gb)' col extent_management format a10 heading 'TYPE |(Extent |Mgmnt )' SELECT         dts.tablespace_name,         initial_extent/1024 initial_extent_size,         next_extent/1024 next_extent_size,         NVL(ddf.bytes / 1024 / 1024 /1024, 0) avail,         NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024/1024 used,         NVL(dfs.bytes / 1024 / 1024/1024, 0) free,         TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00') "Used %",         dts.contents,         dts.extent_management,         dts.status FROM         sys.dba_tablespaces dts,         (select tablespace_name, sum(bytes) bytes                 from dba_data_files group by tablespace_name) ddf,                         (select tablespace_name, sum(bytes) bytes                                 from dba_free_space group by tablespace_name) dfs                                 WHERE                                         dts.tablespace_name = ddf.tablespace_name(+)                                 AND dts.tablespace_name = dfs.tablespace_name(+)                                 AND NOT (dts.extent_management like 'LOCAL'                                 AND dts.contents like 'TEMPORARY')                                 UNION ALL                                 SELECT dts.tablespace_name,                                         initial_extent/1024 initial_extent_size,                                         next_extent/1024 next_extent_size,                                         NVL(dtf.bytes / 1024 / 1024 /1024, 0) avail,                                         NVL(t.bytes, 0)/1024/1024 /1024 used,                                         NVL(dtf.bytes - NVL(t.bytes, 0), 0)/1024/1024/1024 free,                                         TO_CHAR(NVL(t.bytes / dtf.bytes * 100, 0), '990.00') "Used %",                                         dts.contents,                                         dts.extent_management,                                         dts.status                                 FROM                                         sys.dba_tablespaces dts,                                         (select tablespace_name, sum(bytes) bytes                                         from dba_temp_files group by tablespace_name) dtf,                                                 (select tablespace_name, sum(bytes_used) bytes                                                 from v$temp_space_header group by tablespace_name) t         WHERE         dts.tablespace_name = dtf.tablespace_name(+)         AND dts.tablespace_name = t.tablespace_name(+)         AND dts.extent_management like 'LOCAL'         AND dts.contents like 'TEMPORARY'; set feedback on set verify on clear breaks clear computes clear columns set pagesize 100 set echo on set echo off set linesize 999 set termout off

How to check how many switches occur per day in last 100 days?

set pagesize 200 Select    to_char(trunc(first_time), 'Month') Month,    to_char(trunc(first_time), 'Mon-DD-YYYY  :DY') Date_n_Day,    count(*) Total_logswitches from    v$log_history where    trunc(first_time) > last_day(sysdate-100) +1 group by    trunc(first_time) order by 2 desc;

How to check tablespace History

select  thedate,  gbsize,  prev_gbsize,  gbsize-prev_gbsize diff from        (  select  thedate,  gbsize,  lag(gbsize,1) over (order by r) prev_gbsize  from    (  select  rownum r,  thedate,  gbsize  from    (  select  trunc(thedate) thedate,  max(gbsize) gbsize  from    (  select  to_date(to_char(snapshot.begin_interval_time,'YYYY-MON-DD HH24:MI:SS'),'YYYY-MON-DD HH24:MI:SS') thedate,  round((usage.tablespace_usedsize*block_size.value)/1024/1024/1024,2) gbsize  from    dba_hist_tbspc_space_usage usage,  v$tablespace               tablespace,  dba_hist_snapshot          snapshot,  v$parameter                block_size  where   usage.snap_id       = snapshot.snap_id  and     usage.tablespace_id = tablespace.ts#  and     tablespace.name     = '&tablespace'  and     block_size.name     = 'db_block_size'  )  group by  trunc(thedate)  order by  trunc(thedate)  )  )  );

Query to find Active users and killing their sessions

Query to find of "Active users" :


SELECT  SID, Serial#, UserName, Status, SchemaName, Logon_Time
FROM  V$Session
WHERE
Status=‘ACTIVE’ AND
UserName IS  NOT  NULL ;
SELECT * FROM V$SESSION;
--------------------------------------------------------------------------------


The basic syntax for killing a session is shown below :


SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';


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