TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA

Issue: while starting the listener we got the below error.

linux01(oracle:orcl1)/home/oracle: lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 15-SEP-2015 03:43:02

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

Starting /opt/oracle/product/11.2.0.4.RAC/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /opt/oracle/product/11.2.0.4.RAC/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/linux01/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
 NL-00303: syntax error in NV string

Listener failed to start. See the error message(s) above...

Solution: In the listener.ora file there were blank lines under the section SID_LIST_LISTENER. We removed the blank line entries and brought up the listener. (please note that this may be one of the scenarios)

Erroneous listener.ora file excerpts below:

SID_LIST_LISTENER =
  (SID_LIST =
  (SID_DESC =
      (GLOBAL_DBNAME = ORCL1)
      (ORACLE_HOME = /opt/oracle/product/11.2.0.4.RAC)
      (SID_NAME = ORCL1)
   )
<<<<< blank line
(SID_DESC =
      (GLOBAL_DBNAME = ORCL2)
      (ORACLE_HOME = /opt/oracle/product/11.2.0.4.RAC)
      (SID_NAME = ORCL2)
)
<<<<< blank line

)

ORA-27300: OS system dependent operation:semget failed with status: 28

linux01.test.com(oracle:RP43DB31)/opt/oracle: srvctl start database -d ORCL
PRCR-1079 : Failed to start resource ora.orcl.db
CRS-5017: The resource action "ora.orcl.db start" encountered the following error:
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0.4.GRD/log/linux01/agent/crsd/oraagent_oracle/oraagent_oracle.log".

CRS-2674: Start of 'ora.orcl.db' on 'linux01' failed
CRS-5017: The resource action "ora.orcl.db start" encountered the following error:
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0.4.GRD/log/linux02/agent/crsd/oraagent_oracle/oraagent_oracle.log".

CRS-2674: Start of 'ora.orcl.db' on 'linux02' failed
CRS-2632: There are no more servers to try to place resource 'ora.orcl.db' on that would satisfy its placement policy

Solution:
This may be caused by Kernel parameter setting at the server level. Semaphore values may be one of the reasons. Increase the values.

linux01.test.com(oracle:)/opt/oracle: cat /etc/sysctl.conf |grep kernel.sem
kernel.sem = 250 32000 100 128

As root user, edit the /etc/sysctl.conf file and edit the kernel parameters 
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 50000 100 200

and then run the below command to make the values persistent:

/sbin/sysctl -p

Discover host targets manually in OEM 12c - emctl config agent addinternaltargets

In case of silent installation of OEM 12c Agent sometimes the host target monitoring is not picked up and it is not visible in OEM console as well.

When checked in the /u01/app/oracle/agent_inst/sysman/emd/targets.xml file we couldn't find information related to the host.

To fix this we need to run "emctl config agent addInternalTargets". This forces the agent to rediscover the host.

linux01(oracle:NONE)/u01/app/oracle/agent_inst/bin: ./emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version          : 12.1.0.4.0
OMS Version            : 12.1.0.4.0
Protocol Version       : 12.1.0.1.0
Agent Home             : /u01/app/oracle/agent_inst
Agent Log Directory    : /u01/app/oracle/agent_inst/sysman/log
Agent Binaries         : /u01/app/oracle/core/12.1.0.4.0
Agent Process ID       : 3770
Parent Process ID      : 3494
Agent URL              : https://linux01:1830/emd/main/
Local Agent URL in NAT : https://linux01:1830/emd/main/
Repository URL         : https://oem12c.test.com:1159/empbs/upload
Started at             : 2015-08-18 13:46:01
Started by user        : oracle
Operating System       : Linux version 2.6.32-573.1.1.el6.x86_64 (amd64)
Last Reload            : (none)
Last successful upload                       : (none)
Last attempted upload                        : (none)
Total Megabytes of XML files uploaded so far : 0
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 94.03%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2015-08-19 00:38:02
Last successful heartbeat to OMS             : 2015-08-19 00:38:02
Next scheduled heartbeat to OMS              : 2015-08-19 00:39:02

---------------------------------------------------------------
Agent is Running and Ready

linux01(oracle:NONE)/u01/app/oracle/agent_inst/bin: ./emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
[linux01:1830, oracle_emd]

linux01(oracle:NONE)/u01/app/oracle/agent_inst/bin: ./emctl config agent addInternalTargets
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.

linux01(oracle:NONE)/u01/app/oracle/agent_inst/bin: ./emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
[linux01, host]
[linux01:1830, oracle_emd]

How to change SYSMAN Password in OEM 12c

1. Stop all OMS instances running emctl stop oms.

$OMS_Home/bin/emctl stop oms

Execute the same command on all the OMS machines including the primary OMS machine. Do not include '-all' as the Admin Server needs to be up during this operation.

2. Modify the SYSMAN password:

$OMS_Home/bin/emctl config oms -change_repos_pwd

Provide Repository User's Current Password and Repository User's New Password in command prompt

3. Stop the Admin server on the primary OMS machine and re-start all the OMS:

$OMS_Home/bin/emctl stop oms –all

4. Restart all the Management Services:

$OMS_Home/bin/emctl start oms


oem12c.us.com(oracle)/u01/oracle/product/Middleware/oms/bin: emctl stop oms
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
Oracle Management Server is Down

oem12c.us.com(oracle)/u01/oracle/product/Middleware/oms/bin: emctl status oms -details
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host        : oem12c.us.com
HTTP Console Port          : 7789
HTTPS Console Port         : 7801
HTTP Upload Port           : 4890
HTTPS Upload Port          : 4901
EM Instance Home           : /u01/oracle/product/gc_inst/em/EMGC_OMS1
OMS Log Directory Location : /u01/oracle/product/gc_inst/em/EMGC_OMS1/sysman/log
SLB or virtual hostname: oemtest.us.com
HTTPS SLB Upload Port : 4901
HTTPS SLB Console Port : 7801
Agent Upload is unlocked.
OMS Console is locked.
Active CA ID: 1
Console URL: https://oemtest.us.com:7801/em
Upload URL: https://oemtest.us.com:4901/empbs/upload

WLS Domain Information
Domain Name            : GCDomain
Admin Server Host      : oem12c.us.com
Admin Server HTTPS Port: 7102
Admin Server is RUNNING

Oracle Management Server Information
Managed Server Instance Name: EMGC_OMS1
Oracle Management Server Instance Host: oem12c.us.com
WebTier is Down
Oracle Management Server is Down

BI Publisher is not configured to run on this host.
oem12c.us.com(oracle)/u01/oracle/product/Middleware/oms/bin: emctl config oms -change_repos_pwd
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Enter Repository User's Current Password :
Enter Repository User's New Password :

Changing passwords in backend ...
Passwords changed in backend successfully.
Updating repository password in Credential Store...
Successfully updated Repository password in Credential Store.
Restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.
Successfully changed repository password.


oem12c.us.com(oracle)/u01/oracle/product/Middleware/oms/bin: emctl stop oms -all
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Already Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down


oem12c.us.com(oracle)/u01/oracle/product/Middleware/oms/bin: emctl start oms
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
Starting WebTier...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up

oem12c.us.com(oracle)/u01/oracle/product/Middleware/oms/bin: emctl status oms -details
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host        : oem12c.us.com
HTTP Console Port          : 7789
HTTPS Console Port         : 7801
HTTP Upload Port           : 4890
HTTPS Upload Port          : 4901
EM Instance Home           : /u01/oracle/product/gc_inst/em/EMGC_OMS1
OMS Log Directory Location : /u01/oracle/product/gc_inst/em/EMGC_OMS1/sysman/log
SLB or virtual hostname: oemtest.us.com
HTTPS SLB Upload Port : 4901
HTTPS SLB Console Port : 7801
Agent Upload is unlocked.
OMS Console is locked.
Active CA ID: 1
Console URL: https://oemtest.us.com:7801/em
Upload URL: https://oemtest.us.com:4901/empbs/upload

WLS Domain Information
Domain Name            : GCDomain
Admin Server Host      : oem12c.us.com
Admin Server HTTPS Port: 7102
Admin Server is RUNNING

Oracle Management Server Information
Managed Server Instance Name: EMGC_OMS1
Oracle Management Server Instance Host: oem12c.us.com
WebTier is Up
Oracle Management Server is Up

BI Publisher is not configured to run on this host.

How to reset spfile parameter value

How to reset parameter value in oracle spfile

The following command can be used reset any parameter specified in spfile. Please note that it will only delete the entry from the spfile but the original value set at instance level will remain the same.

ALTER SYSTEM RESET <parameter_name>;
ALTER SYSTEM RESET <parameter_name> SCOPE=SPFILE SID='INSTANCE_NAME'; (incase of RAC)

SQL> select * from v$spparameter where name like 'processes';

INST_ID SID   NAME       TYPE     VALUE  DISPLAY_VALUE  ISSPEC ORDINAL UPDATE_COMMENT
------- ----- ---------- -------- ------ -------------- ------ ------- --------------
      1 *     processes  integer  300    300            TRUE         1
      1 ORCL1 processes  integer  300    300            TRUE         1
      1 ORCL2 processes  integer  300    300            TRUE         1
 
SQL> alter system reset processes scope=spfile sid='ORCL2';

System altered.

SQL> select * from v$spparameter where name like 'processes';

INST_ID SID   NAME       TYPE     VALUE  DISPLAY_VALUE  ISSPEC ORDINAL UPDATE_COMMENT
------- ----- ---------- -------- ------ -------------- ------ ------- --------------
      1 *     processes  integer  300    300            TRUE         1
      1 ORCL1 processes  integer  300    300            TRUE         1
 
SQL> alter system reset processes scope=spfile sid='ORCL2';

System altered.

SQL> select * from v$spparameter where name like 'processes';

INST_ID SID   NAME       TYPE     VALUE  DISPLAY_VALUE  ISSPEC ORDINAL UPDATE_COMMENT
------- ----- ---------- -------- ------ -------------- ------ ------- --------------
      1 *     processes  integer  300    300            TRUE         1

Oracle Opatch failed with "CheckActiveFilesAndExecutables"

Environment Details:
OS: Linux Server 5.8 64bit
GRID Home Version: 11.2.0.4.0
GRID Home: /opt/oracle/product/11.2.0.4.GRD

Issue: While applying patch, the opatch failed with the below error

Following executables are active : 
/opt/oracle/product/11.2.0.4.GRD/lib/libclntsh.so.11.1 
UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.  

Solution:  The above mentioned file is still active or in use.

Check which process/tool is using the file and stop the process cleanly else kill the process.

you can use lsof(list open files) or fuser (identify processes using files or sockets) to identify the process using the file.

[oracle@linux01 ~]$ lsof /opt/oracle/product/11.2.0.4.GRD/lib/libclntsh.so.11.1
COMMAND    PID   USER  FD   TYPE DEVICE SIZE/OFF     NODE NAME
ons       2342 oracle mem    REG 253,53 53817421 21213079 /apps/oracle/product/11.2.0.4.GRD/lib/libclntsh.so.11.1
ons       2344 oracle mem    REG 253,53 53817421 21213079 /apps/oracle/product/11.2.0.4.GRD/lib/libclntsh.so.11.1


[oracle@linux01 ~]$ /sbin/fuser -cv /opt/oracle/product/11.2.0.4.GRD/lib/libclntsh.so.11.1
                     USER        PID ACCESS COMMAND
/opt/oracle/product/11.2.0.4.GRD/lib/libclntsh.so.11.1:
                     oracle     2342 F.ce. ons
                     oracle     2344 F.ce. ons

Once the processes are identified, stop/kill them:
[oracle@linux01 ~]$ kill -9 2342
[oracle@linux01 ~]$ kill -9 2344

How to move datafile from one ASM diskgroup to another

Follow the below steps to move datafile from one ASM diskgroup to another.

Case 1: Make the tablespace offline and move the datafile
1. Check the file which you need to move.
2. Make the tablespace offline
3. Connect to rman 
4. issue the copy command from the rman prompt
5. login to the sql prompt and issue rename datafile command
6. Make the tablespace online

Case 2: Make the datafile offline and move the datafile
1. Check the file which you need to move.
2. Make the datafile offline
3. Connect to rman 
4. issue the copy command from the rman prompt
5. login to the sql prompt and issue rename datafile command
6. Recover the datafile (In case you make datafile offline you need to recover the datafile)
7. Make the datafile online

Note: Be very cautious while executing the rename file command as it will remove the original file from the ASM.(tested in 11.2.0.4)

Case 1:
SQL> select tablespace_name, file_name, online_status from dba_data_files;
TABLESPACE_NAME  FILE_NAME                                            ONLINE_STATUS
---------------- ---------------------------------------------------- -------------
TEST1            +DATA_DG01/orcl/datafile/test1.1117.870325481        ONLINE

SQL> alter tablespace test1 offline;
Tablespace altered.

SQL>  select tablespace_name, file_name, online_status from dba_data_files;
TABLESPACE_NAME  FILE_NAME                                            ONLINE_STATUS
---------------- ---------------------------------------------------- -------------
TEST1            +DATA_DG01/orcl/datafile/test1.1117.870325481        OFFLINE


[oracle@linux01 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 30 05:24:02 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=408657265)

RMAN> copy datafile '+DATA_DG01/orcl/datafile/test1.1117.870325481' to '+FRA_DG01';

Starting backup at 30-JAN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA_DG01/orcl/datafile/test1.1117.870325481
output file name=+FRA_DG01/orcl/datafile/test1.263233.870326681 tag=TAG20150130T052441 RECID=7 STAMP=870326681
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-JAN-15


SQL> alter database rename file '+DATA_DG01/orcl/datafile/test1.1117.870325481' to '+FRA_DG01/orcl/datafile/test1.263233.870326681';
Database altered.

SQL>  select tablespace_name, file_name, online_status from dba_data_files;
TABLESPACE_NAME  FILE_NAME                                            ONLINE_STATUS
---------------- ---------------------------------------------------- -------------
TEST1            +FRA_DG01/orcl/datafile/test1.263233.870326681       OFFLINE

SQL> alter tablespace test1 online;
Tablespace altered.

SQL> select tablespace_name, file_name, online_status from dba_data_files;
TABLESPACE_NAME  FILE_NAME                                            ONLINE_STATUS
---------------- ---------------------------------------------------- -------------
TEST1            +FRA_DG01/orcl/datafile/test1.263233.870326681       ONLINE


====================================================================

Case 2:
SQL> select tablespace_name, file_name, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME  FILE_NAME                                        ONLINE_STATUS
---------------- ------------------------------------------------ -------------
TEST1            +FRA_DG01/orcl/datafile/test1.256846.868340771   ONLINE


SQL> alter database  datafile '+FRA_DG01/orcl/datafile/test1.256846.868340771' offline;
Database altered.

SQL>  select tablespace_name, file_name, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME  FILE_NAME                                        ONLINE_STATUS
---------------- ------------------------------------------------ -------------
TEST1            +FRA_DG01/orcl/datafile/test1.256846.868340771   RECOVER

[oracle@linux01 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 30 05:03:51 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=408657265)

RMAN> copy datafile '+FRA_DG01/orcl/datafile/test1.256846.868340771' to '+DATA_DG01';

Starting backup at 30-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=245 instance=RL3DB151 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+FRA_DG01/orcl/datafile/test1.256846.868340771
output file name=+DATA_DG01/orcl/datafile/test1.1117.870325481 tag=TAG20150130T050439 RECID=6 STAMP=870325480
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-JAN-15

SQL> alter database rename file '+FRA_DG01/orcl/datafile/test1.256846.868340771' to '+DATA_DG01/orcl/datafile/test1.1117.870325481';
Database altered.

SQL> recover datafile '+DATA_DG01/orcl/datafile/test1.1117.870325481';
Media recovery complete.

SQL>  select tablespace_name, file_name, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME  FILE_NAME                                        ONLINE_STATUS
---------------- ------------------------------------------------ -------------
TEST1            +DATA_DG01/orcl/datafile/test1.1117.870325481    OFFLINE

SQL> alter database datafile '+DATA_DG01/orcl/datafile/test1.1117.870325481' online;
Database altered.

SQL>  select tablespace_name, file_name, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME  FILE_NAME                                        ONLINE_STATUS
---------------- ------------------------------------------------ -------------
TEST1            +DATA_DG01/orcl/datafile/test1.1117.870325481    ONLINE

How to move system datafile from one ASM diskgroup to another

Follow the below steps to move system datafile from one ASM diskgroup to another.

1. Check the file which you need to move.

2. Stop the database and start it in mount mode.

3. Connect to rman 

4. issue the copy command from the rman prompt

5. login to the sql prompt and issue rename file command

6. stop and start the database

Note: Be very cautious while executing the rename file command as it will remove the original file from the ASM.(tested in 11.2.0.4)

[oracle@linux01 ~]$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 2 03:09:48 2015

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

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> select file_name from dba_data_files;

FILE_NAME

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

+FRA_DG01/orcl/datafile/system.144021.867898701

[oracle@linux01 ~]$ srvctl stop database -d ORCL

[oracle@linux01 ~]$ srvctl start database -d ORCL -o mount

[oracle@linux01 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 2 03:05:00 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=408657265, not open)

RMAN> copy datafile '+FRA_DG01/orcl/datafile/system.144021.867898701' to '+DATA_DG01';

Starting backup at 02-JAN-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=81 instance=ORCL1 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+FRA_DG01/orcl/datafile/system.144021.867898701

output file name=+DATA_DG01/orcl/datafile/system.307.867899135 tag=TAG20150102T030534 RECID=2 STAMP=867899143

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

Finished backup at 02-JAN-15

RMAN> exit

Recovery Manager complete.

[oracle@linux01 ~]$sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 2 03:06:19 2015

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

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> alter database rename file '+FRA_DG01/orcl/datafile/system.144021.867898701' to '+DATA_DG01/orcl/datafile/system.307.867899135';

Database altered.

SQL> exit

[oracle@linux01 ~]$ srvctl stop database -d ORCL

[oracle@linux01 ~]$ srvctl start database -d ORCL

[oracle@linux01 ~]$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 2 03:09:48 2015

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

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> select file_name from dba_data_files;

FILE_NAME

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

+DATA_DG01/orcl/datafile/system.307.867899135