Friday, January 9, 2015

how to check listener status in oracle

Oracle LSNRCTL – Listener Shutdown and Startup Procedures

 

1) Oracle listener is not running  .

  

connect user prabhakarh
pass:

1) sudo bash
2) connect oracle user (su - oracle)

C:\>lsnrctl status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   32-bit Windows Error: 61: Unknown error

 

 

2) Oracle listener is running

C:\>lsnrctl status
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 10-JAN-2015 12:17
:41
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date                10-JAN-2015 12:17:34
Uptime                    0 days 0 hr. 0 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   E:\app\honnikery\product\11.2.0\dbhome_1\network\admin
\listener.ora
Listener Log File         e:\app\honnikery\diag\tnslsnr\honnikery-PC\listener\al
ert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

3. Stop Oracle Listener

$ lsnrctl stop



Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
The command completed successfully
 
 

4. Restart Oracle Listener

lsnrctl reload

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.2)(PORT=1521)))
The command completed successfully
 
 
listener full help 
 
 
 
C:\>lsnrctl

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 10-JAN-2015 12:28
:24

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:

start               stop                status
services            version             reload
save_config         trace               change_password
quit                exit                set*
show*

LSNRCTL>


Oracle Listener Help

1. View Available Listener Commands

lsnrctl help command will display all available listener commands. In Oracle 11g following are the available listener commands.

start – Start the Oracle listener
stop – Stop the Oracle listener
status – Display the current status of the Oracle listener
services – Retrieve the listener services information
version – Display the oracle listener version information
reload – This will reload the oracle listener SID and parameter files. This is equivalent to lsnrctl stop and lsnrctl start.
save_config – This will save the current settings to the listener.ora file and also take a backup of the listener.ora file before overwriting it. If there are no changes, it will display the message “No changes to save for LISTENER”
trace – Enable the tracing at the listener level. The available options are ‘trace OFF’, ‘trace USER’, ‘trace ADMIN’ or ‘trace SUPPORT’
spawn – Spawns a new with the program with the spawn_alias mentioned in the listener.ora file
change_password – Set the new password to the oracle listener (or) change the existing listener password.
show – Display log files and other relevant listener information.









 
 

 














Wednesday, January 7, 2015

How to get Sql query for order by in respecive of Case Insensitive in Oracle

create table test (case varchar2(10));
 
Table created. 
 
 insert into test  values('a');
 insert into test  values('B');
 insert into test  values('c');
 insert into test  values('D');
 insert into test  values('E');
 insert into test  values('D'); 
 insert into test  values('D');
select * from test  
 
CASE
 
D
f
a
B
c
D
E
D
f   
 
 select * from test order by case 
 
CASE 
 
B
D
D
D
E
a
c
f
f 


select * from test order by order by nlssort(case,'NLS_SORT=BINARY_CI');
 
CASE       
----------
a          
B          
c          
D          
D          
D          
E          
f          
f          
 9 rows selected  

Thursday, January 1, 2015

how to check free space in mysql database



SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;

 


Friday, December 26, 2014

DBD::mysql::st execute failed: Table './bugs/bugs_fulltext' is marked








mysql> REPAIR TABLE bugs_fulltext;

| Table  | Op     | Msg_type | Msg_text | 
 bugs.bugs_fulltext | repair | status   | OK       |

Wednesday, December 24, 2014

how to check last analyzed table in oracle

SELECT table_name,num_rows,last_analyzed FROM user_TABLES




TABLE_NAME                       NUM_ROWS               LAST_ANALYZED
------------------------------ ---------- -------------
MTHPASSKBS                          611                         15-SEP-14    
SH_NO                                       6319                       19-SEP-14    
D_R_D_SEP                              280                         11-OCT-14      
REQ_TRAINEE_T                    3152                       20-DEC-14    
REQUISITION_T                      1251                      20-DEC-14    
EVALUATION_T                       1349                     20-DEC-14    
EVALUATION_WEEK_T          296                      20-DEC-14    
REGISTRATION_T                    1634                   20-DEC-14    
OFFICEORDER_T                      2069                  20-DEC-14    
MASTER                                     710                    20-DEC-14    



 10 rows selected


ANALYZE TABLE command

ANALYZE TABLE <TABLE_NAME> COMPUTE STATISTICS;

Tuesday, December 23, 2014

How to check whether Oracle instance is started using pfile or spfile?

SQL> show parameter spfile;
 
NAME       TYPE    VALUE                                                                                                
-------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 
spfile    string   F:\APP\PRABHAKAR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEORCL.ORA                                     
 
 
It gives path of spfile file which is getting used by database for starting up. 
It means my database is using spfile 

No value is displayed because database is not using spfile it is using pfile. 
It means database is using pfile for startup.

Monday, May 12, 2014

MySQL: SHOW FUNCTION STATUS WHERE Db = ‘name’: Cannot load from mysql.proc. The table is probably corrupted

mysqldump: Couldn't execute 
'SHOW FUNCTION STATUS WHERE Db ='something'':
 Cannot load from mysql.proc. 
The table is probably corrupted (1548)

 
 
The reason why is because MySQL 5.5 provided an update to the schemas, which are not automatically applied to already existing databases. To provide that schema update, it's important to run the MySQL upgrade command, as it also helps with missing tables or prefixed tables.
The command is run as follows.
$ mysql_upgrade

If you're getting an access denied error, pass along the username + password.
$ mysql_upgrade mysqlcheck:
 Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to
 connect FATAL ERROR: Upgrade failed

$ mysql_upgrade -u root -p


That should solve your problem. If it does not, have a look at your MySQL logfiles for more indications.