TNS-12541, TNS-12560, TNS-00511

In my test machine after finished with the database installation, when I was trying to start the listener, it failed with below error.

-bash-3.2$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1

-bash-3.2$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-AUG-2014 07:57:37

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

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNS-12537: TNS:connection closed
 TNS-12560: TNS:protocol adapter error
  TNS-00507: Connection closed
   Linux Error: 29: Illegal seek

Excerpts from  the listener.log file.

System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/test/listener/alert/log.xml

Trace information written to /u01/app/oracle/diag/tnslsnr/test/listener/trace/ora_2910_3086120640.trc

Trace level is currently 0

Started with pid=2910

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.0)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

Listener completed notification to CRS on start

Fri Aug 01 07:59:32 2014

System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/test/listener/alert/log.xml

Trace information written to /u01/app/oracle/diag/tnslsnr/test/listener/trace/ora_2978_3086464704.trc

Trace level is currently 0

Started with pid=2978

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.0)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

Listener completed notification to CRS on start

Fri Aug 01 08:08:55 2014

System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/test/listener/alert/log.xml

Trace information written to /u01/app/oracle/diag/tnslsnr/test/listener/trace/ora_3001_3085989568.trc

Trace level is currently 0

Started with pid=3001

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.0)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE

Fri Aug 01 08:13:27 2014

System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/test/listener/alert/log.xml

Trace information written to /u01/app/oracle/diag/tnslsnr/test/listener/trace/ora_3319_3086780096.trc

Trace level is currently 0

Started with pid=3319

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.0)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

Listener completed notification to CRS on start

Debugging.

I checked the listener file and tnsnames file and they are having with proper setting.

In Listener.ora 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = db1)

      (SID_NAME = catalog)

      (SID_NAME = db2)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

In Tnsnames.ora

DB1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = db1)

    )

  )

CATALOG =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = catalog)

    )

  )

DB2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = db2)

    )

  )

Solution.

When I checked into the /etc/hosts file at the OS level, I found that an entry was somehow missing.

127.0.0.1       localhost.localdomain   localhost

After adding the above, I restarted the network services as root and then start the listener which get successful.

-bash-3.2$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-AUG-2014 08:13:26

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

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production

System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/test/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.0)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

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

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date                01-AUG-2014 08:13:27

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

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

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

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.0)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

Services Summary...

Service "db1" has 1 instance(s).

  Instance "db1", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

-bash-3.2$ 

Fix for Oracle Errors ORA-51106, ORA-51108, ORA-48101, ORA-48102, ORA-12012, ORA-48223

To fix the Oracle errors as ORA-51106, ORA-51108, ORA-48101, ORA-48102, ORA-12012 , ORA-48223, need to drop and create HMA schema using the procedure.

Below is how the Oracle Error looks like.

Message=Alert Log Message: Errors in file /u01/app/oracle/diag/rdbms/prim/prim/trace/trace/prim_j001_2001.trc: ORA-12012: error on auto execute of job "SYS"."DRA_REEVALUATE_OPEN_FAILURES" ORA-51108: unable to access diagnostic repository - retry command ORA-48223: Interrupt Requested - Fetch Aborted - Return Code [51014] [HM_FINDING] ORA-48223: Interrupt Requested - Fetch Aborted - Return Code [51014] [HM_FINDING] ORA-06512: at "SYS.DBMS_IR", line 522 raised 1 times 
Metric=User-Defined Numeric Metric 

Solution:

prim-->(prim) /u01/app/oracle/diag/rdbms/prim/prim/metadata> ls -alhtr HM_*.ams

-rw-r----- 1 oracle dba  64K Aug 27  2013 HM_FDG_SET.ams

-rw-r----- 1 oracle dba  64K Sep 12 22:50 HM_RECOMMENDATION.ams

-rw-r----- 1 oracle dba  73M Sep 13 02:13 HM_MESSAGE.ams

-rw-r----- 1 oracle dba 139M Sep 13 02:13 HM_INFO.ams

-rw-r----- 1 oracle dba 124M Sep 13 02:13 HM_FINDING.ams

-rw-r----- 1 oracle dba 2.1M Sep 13 02:13 HM_RUN.ams

prim-->(prim) /u01/app/oracle/diag/rdbms/prim/prim/metadata> sqlplus –S / as sysdba

SQL> exec dbms_hm.drop_schema;

PL/SQL procedure successfully completed.

SQL> !ls -alhtr HM_*.ams

-rw-r----- 1 oracle dba  64K Sep 13 02:13 HM_RUN.ams

-rw-r----- 1 oracle dba  64K Sep 13 02:13 HM_MESSAGE.ams

-rw-r----- 1 oracle dba  64K Sep 13 02:13 HM_INFO.ams

-rw-r----- 1 oracle dba 464K Sep 13 02:13 HM_FINDING.ams

SQL> exec dbms_hm.create_schema;

PL/SQL procedure successfully completed.

SQL> !ls -alhtr HM_*.ams

-rw-r----- 1 oracle dba  64K Sep 13 02:14 HM_RUN.ams

-rw-r----- 1 oracle dba 464K Sep 13 02:14 HM_MESSAGE.ams

-rw-r----- 1 oracle dba 464K Sep 13 02:14 HM_INFO.ams

-rw-r----- 1 oracle dba 464K Sep 13 02:14 HM_FINDING.ams

-rw-r----- 1 oracle dba  64K Sep 13 02:16 HM_RECOMMENDATION.ams

-rw-r----- 1 oracle dba  64K Sep 13 02:16 HM_FDG_SET.ams

Script to get the Tablespace size

[oracle@source ~]$ cat > /u01/tab_size.sql
select a.tablespace_name name,
       total_size,
       total_size-nvl(bytes_free,0) used,
       nvl(bytes_free,0) free,
       ((total_size-nvl(bytes_free,0))/total_size)*100 pct_used
       from ( select sum(bytes/(1024*1024*1024)) bytes_free,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes/(1024*1024*1024)) total_size,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name )b
where a.tablespace_name = b.tablespace_name
and a.tablespace_name like '&i';

[oracle@source ~]$ sqlplus "/as sysdba"

SQL> @/u01/tab_size.sql
Enter value for i: USERS
old  16: and a.tablespace_name like '&i'
new  16: and a.tablespace_name like 'USERS'

NAME                           TOTAL_SIZE       USED       FREE   PCT_USED
------------------------------ ---------- ---------- ---------- ----------
USERS                          .574951172 .001708984 .573242188 .297239915

SQL> l
  1  select a.tablespace_name name,
  2         total_size,
  3         total_size-nvl(bytes_free,0) used,
  4         nvl(bytes_free,0) free,
  5         ((total_size-nvl(bytes_free,0))/total_size)*100 pct_used
  6         from ( select sum(bytes/(1024*1024*1024)) bytes_free,
  7                tablespace_name
  8         from  sys.dba_free_space
  9         group by tablespace_name ) a,
 10       ( select sum(bytes/(1024*1024*1024)) total_size,
 11                tablespace_name
 12         from sys.dba_data_files
 13         group by tablespace_name )b
 14  where a.tablespace_name = b.tablespace_name
 15* and a.tablespace_name like '&i'

ORA-01017: invalid username/password; logon denied

I got a user call where he was not able to login into the Production database. I query the database for that user and found that his account was neither locked nor expired. Then I realize might be the user's credential is in case sensitive which he is not able to recall.
I change the database case sensitive parameter which helps that user to log in successfully into the database.

I simulated that scenario into my test machine for you.
I'm using user 'scott' whose password is 'tiger'(in lower case).
Now I'll try to login into the database in upper,middle,mixed to check the database behaviour.

SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE from dba_users;

USERNAME ACCOUNT_STATUS  LOCK_DATE  EXPIRY_DA

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

SYSTEM   OPEN

SYS      OPEN

DBSNMP   OPEN

SYSMAN   OPEN

SCOTT    OPEN

...

....

...

[oracle@oracle GG]$ sqlplus scott/TIGER
ERROR:
ORA-01017: invalid username/password; logon denied
[oracle@oracle GG]$ sqlplus scott/tiGER
ERROR:
ORA-01017: invalid username/password; logon denied
[oracle@oracle GG]$ sqlplus scott/Tiger
ERROR:
ORA-01017: invalid username/password; logon denied
[oracle@oracle GG]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 3 19:27:31 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn /as sysdba
Connected.

SQL> show parameter case_sensitive

NAME                                 TYPE        VALUE

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

sec_case_sensitive_logon             boolean     TRUE

SQL> alter system set sec_case_sensitive_logon=FALSE scope=both;

System altered.

Now try to login the database by user 'scott'  credential.

SQL> conn scott/TIger

Connected.

SQL> conn scott/TigER

Connected.

SQL> conn scott/TIGER

Connected.

Now user scott can login into the database without facing issue with case sensitivity in his password.  

We can also create a oracle password file without having in case sensitive attribute.
[oracle@oracle ~]$ orapwd file=orapwdb1 password=Oracle ignorecase=Y

Thanks for your time. :)

Temporary Tablespace Creating/Deleting/Shrinking

There may be some cases like if you recreate a control file of your database manually in that case you will need to recreate a temporary tablespace.
To delete the old temp tablespace you need to create the new one and then make the newly one is the default temp tablespace.


SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

SQL> CREATE TEMPORARY TABLESPACE TEMP1

  2  TEMPFILE '/u01/app/oracle/oradata/db1/temp02.dbf' SIZE 200M;

Tablespace created.

Elapsed: 00:00:00.57

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

Database altered.

Elapsed: 00:00:00.13

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Elapsed: 00:00:01.33

SQL> SELECT NAME,BYTES,STATUS FROM V$TEMPFILE;

NAME

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

     BYTES STATUS

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

/u01/app/oracle/oradata/db1/temp02.dbf

 209715200 ONLINE

Elapsed: 00:00:00.00

Shrinking the Temp Tablespace.

SQL> SELECT NAME,BYTES,STATUS FROM V$TEMPFILE;

NAME

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

     BYTES STATUS

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

/u01/app/oracle/oradata/db1/temp02.dbf

 209715200 ONLINE

Elapsed: 00:00:00.00

SQL> SELECT * FROM DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

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

TEMP1                                209715200         2097152  207618048

Elapsed: 00:00:00.01

In the below case we tell the oracle explicitly to shrink the Temp tablespace to keep atleast some defined value.

SQL> ALTER TABLESPACE TEMP1 SHRINK SPACE KEEP 100M;

Tablespace altered.

Elapsed: 00:00:00.13

SQL> COL NAME FOR A40

SQL> SET LINE 100

SQL> SELECT NAME,BYTES,STATUS FROM V$TEMPFILE;

NAME                                          BYTES STATUS

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

/u01/app/oracle/oradata/db1/temp02.dbf    105906176 ONLINE

Elapsed: 00:00:00.00

SQL> SELECT * FROM DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

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

TEMP1                                105906176         2097152  103809024

Elapsed: 00:00:00.01

SQL> ALTER TABLESPACE TEMP1 SHRINK SPACE;

Tablespace altered.

Elapsed: 00:00:00.03

SQL> SELECT NAME,BYTES,STATUS FROM V$TEMPFILE;

NAME                                          BYTES STATUS

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

/u01/app/oracle/oradata/db1/temp02.dbf      2088960 ONLINE

Elapsed: 00:00:00.00

In the below case Oracle will shrink the Temp Tablespace to the min possible value.

SQL> SELECT * FROM DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

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

TEMP1                                  2088960         2088960          0

Elapsed: 00:00:00.00

Changing the Time Zone of the Database

Once I was trying to change the database time zone, I got the below alert:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns.

The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) columns. Oracle normalizes all TIMESTAMP WITH LOCAL TIME ZONE data to the time zone of the database. You can set the database time zone when the database is created. 
It is not possible to change the database timezone when TSLTZ columns are used. As stated before TSLTZ columns are stored normalized to the database timezone, i.e. after changing the database timezone the interpretation of the data would be wrong.




Solution.

Finding TSLTZ columns
Use the following query to find all the TSLTZ columns in the database:

select u.name || '.' || o.name || '.' || c.name TSLTZcolumn
from sys.obj$ o, sys.col$ c, sys.user$ u
and o.obj# = c.obj#
where c.type# = 231
and u.user# = o.owner#;

OR
select count (*) from dba_tab_columns
where data_type like 'TIMESTAMP%WITH LOCAL TIME ZONE'; 
select owner,table_name from dba_tab_columns
where data_type like 'TIMESTAMP%WITH LOCAL TIME ZONE'; 
One should follow the below options to drop that TSLTZ cloumn from the table:
1. Backup the table that contains the TSLTZ column.
2. Drop the table or the column only.
3. Issue the alter database to change the DB time Zone.
4. Add the dropped column and restore the data OR restore the table if it's dropped. 
5. Bounce the database.

In my case, I found the table which had TSLTZ column and since it was belonging from
the EXAMPLE schema so I preferred to drop that table. 












 
 
 
 

Script to generate AWR automatically using Crotab utility

I have created a script which will take out a Performance reports like AWR/ASH automatically which means you don't need to login into the Database through SQL command prompt.
Also You can schedule this job based on your requirement.

Below are the link to download the AWR/ASH script.

AWR Script

Below are the script view.

Here in my AWR script, I have took the ORACLE_SID of one node and Begin/End time according to my necessity, you can change it according to you ease.

Now running this script through crontab scheduler.

Let me give a brief idea about Cron job.

Crontab Commands

crontab -e    Edit your crontab file, or create one if it doesn’t already exist.
crontab -l    Display your crontab file.
crontab -r    Remove your crontab file.

 
Crontab file
Crontab syntax :
A crontab file has five fields for specifying day , date and time followed by the command to be run at that interval.

*     *   *  *   *  command to be executed -     -    -   -  - |     |     |   |    | |     |     |   |    +----- day of week (0 - 6) (Sunday=0) |     |     |   +------- month (1 - 12) |     |     +--------- day of month (1 - 31) |     +----------- hour (0 - 23) +------------- min (0 - 59)

* in the value field above means all legal values as in braces for that column.
The value column can have a * or a list of elements separated by commas. An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range).

Notes
 
A.)Repeat pattern like /2 for every 2 minutes or /10 for every 10 minutes is not supported by all operating systems. If you try to use it and crontab complains it is probably not supported.
B.) The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed.

Sql to get the Database Size

Database is a collection of Physical Structure file like Data files, Redolog files,Temp files, Controlfiles, Parameter files etc.

When we talk about the Database size, We consider the total size of the Data files, Redolog files and Temp files as their size is noticeable which constitute the Database.

Below are the Sql Query for the same.

 select
 sum(used.bytes) / 1024 / 1024 / 1024  || ' GB' Database_Size,
 sum(used.bytes) / 1024 / 1024 / 1024  - free.space / 1024 / 1024 / 1024 || ' GB' Used_space,
 free.space / 1024 / 1024 / 1024 || ' GB' Free_space
 from
 (select bytes
 from v$datafile
 union   all
 select bytes
 from v$tempfile
 union   all
 select bytes
 from v$log) used,
 (select sum(bytes) space
 from dba_free_space) free
 group by free.space;

 If you want to compute the tablespace size.

col "TABLESPACE_NAME" format a15

col "USED_SPACE" format 999,999,999

col "TABLESPACE_SIZE" format 999,999,999

col "USED_PERCENT" format 999,999,999

select * from dba_tablespace_usage_metrics order by used_percent desc;