How to enable Examine (Diagnostics menu) in Oracle eBS R12

To be able to use the examine option in Oracle eBS you need to set the following Profile settings:

Profile option name: Hide Diagnostics menu entry

Value: No

Profile option name: Utilities:Diagnostics

Value: No

After that switch responsibility or logout and login and you will have a new menu item under the “Help” menu.

To also have the “About this page” link in the OAF Pages also set the following profiles options:

FND: Diagnostics = “Yes”

Personalize Self-Service Defn = “Yes”

FND: Personalization Region Link Enabled = “Yes”

Disable Self-Service Personal = “No” (Only at Site)

Source: Internet / Internal R&D

Scott_Schema.sql

Copy and paste on blank notepad file and save as a scott_schema.sql
------------------------------------------------------------------------------------
-- creates some test-tables and data
-- DROP TABLE EMPLOYEE;
-- DROP TABLE DEPARTMENT;
-- DROP TABLE SALARYGRADE;
-- DROP TABLE BONUS;
-- DROP TABLE PROJECT;
-- DROP TABLE PROJECT_PARTICIPATION;
-- DROP TABLE ROLE;

CREATE TABLE EMPLOYEE(
   empno      INTEGER NOT NULL,
   name       VARCHAR(10),
   job        VARCHAR(9),
   boss       INTEGER,
   hiredate   VARCHAR(12),
   salary     DECIMAL(7, 2),
   comm       DECIMAL(7, 2),
   deptno     INTEGER
);

CREATE TABLE DEPARTMENT(
   deptno     INTEGER NOT NULL,
   name       VARCHAR(14),
   location   VARCHAR(13)
);

CREATE TABLE SALARYGRADE(
   grade      INTEGER NOT NULL,
   losal      INTEGER NOT NULL,
   hisal      INTEGER NOT NULL
);

CREATE TABLE BONUS (
   ename      VARCHAR(10) NOT NULL,
   job        VARCHAR(9) NOT NULL,
   sal        DECIMAL(7, 2),
   comm       DECIMAL(7, 2)
);

CREATE TABLE PROJECT(
   projectno    INTEGER NOT NULL,
   description  VARCHAR(100),
   start_date   VARCHAR(12),
   end_date     VARCHAR(12)
);

CREATE TABLE PROJECT_PARTICIPATION(
   projectno    INTEGER NOT NULL,
   empno        INTEGER NOT NULL,
   start_date   VARCHAR(12) NOT NULL,
   end_date     VARCHAR(12),
   role_id      INTEGER
);

CREATE TABLE ROLE(
   role_id      INTEGER NOT NULL,
   description  VARCHAR(100)
);

-- Primary Keys
ALTER TABLE EMPLOYEE
   ADD CONSTRAINT emp_pk
   PRIMARY KEY (empno);

ALTER TABLE DEPARTMENT
   ADD CONSTRAINT dept_pk
   PRIMARY KEY (deptno);

ALTER TABLE SALARYGRADE
   ADD CONSTRAINT salgrade_pk
   PRIMARY KEY (grade);

ALTER TABLE BONUS
   ADD CONSTRAINT bonus_pk
   PRIMARY KEY (ename, job);

ALTER TABLE PROJECT
   ADD CONSTRAINT project_pk
   PRIMARY KEY (projectno);

ALTER TABLE PROJECT_PARTICIPATION
   ADD CONSTRAINT participation_pk
   PRIMARY KEY (projectno, empno, start_date);

ALTER TABLE ROLE
   ADD CONSTRAINT role_pk
   PRIMARY KEY (role_id);

-- EMPLOYEE to DEPARTMENT
ALTER TABLE EMPLOYEE
   ADD CONSTRAINT department
   FOREIGN KEY (deptno)
   REFERENCES DEPARTMENT (deptno);

-- EMPLOYEE to EMPLOYEE
ALTER TABLE EMPLOYEE
   ADD CONSTRAINT boss
   FOREIGN KEY (boss)
   REFERENCES EMPLOYEE (empno);

-- EMPLOYEE to PROJECT_PARTICIPATION
ALTER TABLE PROJECT_PARTICIPATION
   ADD CONSTRAINT employee
   FOREIGN KEY (empno)
   REFERENCES EMPLOYEE (empno);

-- PROJECT to PROJECT_PARTICIPATION
ALTER TABLE PROJECT_PARTICIPATION
   ADD CONSTRAINT project
   FOREIGN KEY (projectno)
   REFERENCES PROJECT (projectno);

-- ROLE to PROJECT_PARTICIPATION
ALTER TABLE PROJECT_PARTICIPATION
   ADD CONSTRAINT role
   FOREIGN KEY (role_id)
   REFERENCES ROLE (role_id);

-- data
INSERT INTO DEPARTMENT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPARTMENT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPARTMENT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPARTMENT VALUES (40, 'OPERATIONS', 'BOSTON');

INSERT INTO EMPLOYEE VALUES (7839, 'KING',   'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
    INSERT INTO EMPLOYEE VALUES (7566, 'JONES',  'MANAGER',   7839, '1981-04-02',  2975, NULL, 20);
       INSERT INTO EMPLOYEE VALUES(7788, 'SCOTT',  'ANALYST',   7566, '1982-12-09', 3000, NULL, 20);
          INSERT INTO EMPLOYEE VALUES(7876, 'ADAMS',  'CLERK',     7788, '1983-01-12', 1100, NULL, 20);
       INSERT INTO EMPLOYEE VALUES(7902, 'FORD',   'ANALYST',   7566, '1981-12-03',  3000, NULL, 20);
          INSERT INTO EMPLOYEE VALUES(7369, 'SMITH',  'CLERK',     7902, '1980-12-17',  800, NULL, 20);
    INSERT INTO EMPLOYEE VALUES (7698, 'BLAKE',  'MANAGER',   7839, '1981-05-01',  2850, NULL, 30);
       INSERT INTO EMPLOYEE VALUES(7499, 'ALLEN',  'SALESMAN',  7698, '1981-02-20', 1600,  300, 30);
       INSERT INTO EMPLOYEE VALUES(7521, 'WARD',   'SALESMAN',  7698, '1981-02-22', 1250,  500, 30);
       INSERT INTO EMPLOYEE VALUES(7654, 'MARTIN', 'SALESMAN',  7698, '1981-09-28', 1250, 1400, 30);
       INSERT INTO EMPLOYEE VALUES(7844, 'TURNER', 'SALESMAN',  7698, '1981-09-08',  1500,    0, 30);
       INSERT INTO EMPLOYEE VALUES(7900, 'JAMES',  'CLERK',     7698, '1981-12-03',   950, NULL, 30);
    INSERT INTO EMPLOYEE VALUES(7782, 'CLARK',  'MANAGER',   7839, '1981-06-09',  2450, NULL, 10);
       INSERT INTO EMPLOYEE VALUES(7934, 'MILLER', 'CLERK',     7782, '1982-01-23', 1300, NULL, 10);

INSERT INTO SALARYGRADE VALUES (1,  700, 1200);
INSERT INTO SALARYGRADE VALUES (2, 1201, 1400);
INSERT INTO SALARYGRADE VALUES (3, 1401, 2000);
INSERT INTO SALARYGRADE VALUES (4, 2001, 3000);
INSERT INTO SALARYGRADE VALUES (5, 3001, 9999);

INSERT INTO ROLE VALUES (100, 'Developer');
INSERT INTO ROLE VALUES (101, 'Researcher');
INSERT INTO ROLE VALUES (102, 'Project manager');

INSERT INTO PROJECT VALUES (1001, 'Development of Novel Magnetic Suspension System', '2006-01-01', '2007-08-13');
INSERT INTO PROJECT VALUES (1002, 'Research on thermofluid dynamics in Microdroplets', '2006-08-22', '2007-03-20');
INSERT INTO PROJECT VALUES (1003, 'Foundation of Quantum Technology', '2007-02-24', '2008-07-31');
INSERT INTO PROJECT VALUES (1004, 'High capacity optical network', '2008-01-01', null);

INSERT INTO PROJECT_PARTICIPATION VALUES (1001, 7902, '2006-01-01', '2006-12-30', 102);
INSERT INTO PROJECT_PARTICIPATION VALUES (1001, 7369, '2006-01-01', '2007-08-13', 100);
INSERT INTO PROJECT_PARTICIPATION VALUES (1001, 7788, '2006-05-15', '2006-11-01', 100);

INSERT INTO PROJECT_PARTICIPATION VALUES (1002, 7876, '2006-08-22', '2007-03-20', 102);
INSERT INTO PROJECT_PARTICIPATION VALUES (1002, 7782, '2006-08-22', '2007-03-20', 101);
INSERT INTO PROJECT_PARTICIPATION VALUES (1002, 7934, '2007-01-01', '2007-03-20', 101);

INSERT INTO PROJECT_PARTICIPATION VALUES (1003, 7566, '2007-02-24', '2008-07-31', 102);
INSERT INTO PROJECT_PARTICIPATION VALUES (1003, 7900, '2007-02-24', '2007-01-31', 101);

INSERT INTO PROJECT_PARTICIPATION VALUES (1004, 7499, '2008-01-01', null, 102);
INSERT INTO PROJECT_PARTICIPATION VALUES (1004, 7521, '2008-05-01', null, 101);
INSERT INTO PROJECT_PARTICIPATION VALUES (1004, 7654, '2008-04-15', null, 101);
INSERT INTO PROJECT_PARTICIPATION VALUES (1004, 7844, '2008-02-01', null, 101);
INSERT INTO PROJECT_PARTICIPATION VALUES (1004, 7900, '2008-03-01', '2008-04-01', 101);
INSERT INTO PROJECT_PARTICIPATION VALUES (1004, 7900, '2008-05-20', null, 101);

ASM Grid Services

useradd -u 1100 -g oinstall -G asmadmin,asmdba,asmoper,dba -d  /home/oracle/ -s /bin/bash -c "oracle grid" oracle

useradd -u 1101 -g oinstall -G dba,asmdba,oper -d /home/oracle -s /bin/bash -c "OR Owner" oracle

 alter system set control_files='+EBSDATA/prod/controlfile/cntrl01.dbf', '+EBSDATA/prod/controlfile/cntrl02.dbf','+EBSDATA/prod/controlfile/cntrl03.dbf' scope=spfile;

login as: root

root@192.168.10.123's password:

Last login: Sun Dec 25 02:46:33 2016

[root@furqan ~]# su - oracle

[oracle@furqan ~]$ echo $ORACLE_HOME

/u01/oracle/PROD/db/tech_st/11.2.0

[oracle@furqan ~]$

[oracle@furqan ~]$ source grid.env

[oracle@furqan ~]$ srvctl add database -d PROD -o /u01/oracle/PROD/db/tech_st/11.2.0

[oracle@furqan ~]$ srvctl remove listener

PRCR-1025 : Resource ora.LISTENER.lsnr is still running

[oracle@furqan ~]$ srvctl stop listener

[oracle@furqan ~]$ srvctl remove listener

[oracle@furqan ~]$ echo $TNS_ADMIN

/u01/oracle/PROD/db/tech_st/11.2.0/network/admin/PROD_furqan

[oracle@furqan ~]$ srvctl add listener -l LISTENER_EBS -o  /u01/oracle/PROD/db/tech_st/11.2.0/network/admin/PROD_furqan

PRCN-2061 : Failed to add listener LISTENER_EBS

PRCT-1406 : Oracle Home location: /u01/oracle/PROD/db/tech_st/11.2.0/network/admin/PROD_furqan does not contain bin/srvctl

[oracle@furqan ~]$ srvctl add listener -l LISTENER_EBS -o  /u01/oracle/PROD/db/tech_st/11.2.0/network/admin/PROD_furqan

PRCN-2061 : Failed to add listener LISTENER_EBS

PRCT-1406 : Oracle Home location: /u01/oracle/PROD/db/tech_st/11.2.0/network/admin/PROD_furqan does not contain bin/srvctl

[oracle@furqan ~]$ exit

logout

[root@furqan ~]# source /home/oracle/grid.env

[root@furqan ~]# srvctl add listener -l LISTENER_EBS -o  /u01/oracle/PROD/db/tech_st/11.2.0/network/admin/PROD_furqan

PRCN-2061 : Failed to add listener LISTENER_EBS

PRCT-1406 : Oracle Home location: /u01/oracle/PROD/db/tech_st/11.2.0/network/admin/PROD_furqan does not contain bin/srvctl

[root@furqan ~]# crsctl status res -t

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

NAME           TARGET  STATE        SERVER                   STATE_DETAILS     

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

Local Resources

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

ora.EBSDATA.dg

               ONLINE  ONLINE       furqan                                     

ora.asm

               ONLINE  ONLINE       furqan                   Started           

ora.ons

               OFFLINE OFFLINE      furqan                                     

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

Cluster Resources

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

ora.cssd

      1        ONLINE  ONLINE       furqan                                     

ora.diskmon

      1        OFFLINE OFFLINE                                                  

ora.evmd

      1        ONLINE  ONLINE       furqan                                     

ora.prod.db

      1        OFFLINE OFFLINE                                                 

[root@furqan ~]# crsctl config has

CRS-4622: Oracle High Availability Services autostart is enabled.

[root@furqan ~]#

[oracle@furqan ~]$ srvctl start service -s production -d PROD

PRCD-1084 : Failed to start service production

PRCR-1079 : Failed to start resource ora.prod.production.svc

CRS-5017: The resource action "ora.prod.db start" encountered the following error:

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name 'PROD_LOCAL'

. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0/grid/log/furqan/agent/ohasd/oraagent_oracle/oraagent_oracle.log".

CRS-2674: Start of 'ora.prod.db' on 'furqan' failed

[oracle@furqan ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 25 14:17:48 2016

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

Connected to:

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

With the Automatic Storage Management option

SQL> select status from v$instance;

STATUS

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

STARTED

SQL> show parameter local_liste

NAME                                 TYPE  VALUE

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

local_listener                       string

 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.123)(PORT=1521))))

SQL>

SQL> alter system set local_listener=' ';

alter system set local_listener=' '

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name ' '

SQL> set lines 133 pages 133           

SQL> show parameter local_liste

NAME                 TYPE                              VALUE

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

local_listener                       string              (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.123)(PORT=1521))))

SQL>

[oracle@furqan ~]$ lsnrctl status PROD

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-DEC-2016 03:04:22

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=furqan.oracle.com)(PORT=1521)))

TNS-12541: TNS:no listener

 TNS-12560: TNS:protocol adapter error

  TNS-00511: No listener

   Linux Error: 111: Connection refused

[oracle@furqan ~]$ lsnrctl start PROD

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-DEC-2016 03:04:30

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

Starting /u01/oracle/PROD/db/tech_st/11.2.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production

System parameter file is /u01/oracle/PROD/db/tech_st/11.2.0/network/admin/PROD_furqan/listener.ora

Log messages written to /u01/oracle/PROD/db/tech_st/11.2.0/admin/PROD_furqan/diag/tnslsnr/furqan/prod/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=furqan.oracle.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=furqan.oracle.com)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     PROD

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                25-DEC-2016 03:04:31

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/oracle/PROD/db/tech_st/11.2.0/network/admin/PROD_furqan/listener.ora

Listener Log File         /u01/oracle/PROD/db/tech_st/11.2.0/admin/PROD_furqan/diag/tnslsnr/furqan/prod/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=furqan.oracle.com)(PORT=1521)))

Services Summary...

Service "PROD" has 1 instance(s).

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

The command completed successfully

[oracle@furqan ~]$ lsnrctl stop PROD

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-DEC-2016 13:35:32

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=furqan.oracle.com)(PORT=1521)))

The command completed successfully

[oracle@furqan ~]$

root@192.168.10.123's password:

Last login: Sun Dec 25 03:00:50 2016 from 192.168.10.12

[root@furqan ~]# crsctl config has

-bash: crsctl: command not found

[root@furqan ~]#

[root@furqan ~]# source /home/oracle/grid.env

[root@furqan ~]# crsctl config has

CRS-4622: Oracle High Availability Services autostart is enabled.

[root@furqan ~]# su - oracle

[oracle@furqan ~]$ source grid.env

[oracle@furqan ~]$ srvctl config listener

PRCN-2044 : No listener exists

[oracle@furqan ~]$ srvctl add listener -l listener -o /u01/oracle/PROD/db/tech_st/11.2.0

PRCN-2061 : Failed to add listener ora.LISTENER.lsnr

PRCN-2065 : Port(s) 1521 are not available on the nodes given

PRCN-2067 : Port 1521 is not available across node(s) "furqan.oracle.com"

[oracle@furqan ~]$ srvctl add listener -l listener -o /u01/oracle/PROD/db/tech_st/11.2.0

[oracle@furqan ~]$

[oracle@furqan ~]$ srvctl config listener

Name: LISTENER

Home: /u01/oracle/PROD/db/tech_st/11.2.0

End points: TCP:1521

[oracle@furqan ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-DEC-2016 13:40:41

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

TNS-12541: TNS:no listener

 TNS-12560: TNS:protocol adapter error

  TNS-00511: No listener

   Linux Error: 2: No such file or directory

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=furqan.oracle.com)(PORT=1521)))

TNS-12541: TNS:no listener

 TNS-12560: TNS:protocol adapter error

  TNS-00511: No listener

   Linux Error: 111: Connection refused

[oracle@furqan ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-DEC-2016 13:40:45

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

Starting /u01/app/11.2.0/grid/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production

System parameter file is /u01/oracle/PROD/db/tech_st/11.2.0/network/admin/PROD_furqan/listener.ora

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

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

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=furqan.oracle.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                25-DEC-2016 13:40:46

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/oracle/PROD/db/tech_st/11.2.0/network/admin/PROD_furqan/listener.ora

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

Listening Endpoints Summary...

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

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=furqan.oracle.com)(PORT=1521)))

The listener supports no services

The command completed successfully

[oracle@furqan ~]$ lsnrctl start listsner

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-DEC-2016 13:41:04

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

Starting /u01/app/11.2.0/grid/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production

System parameter file is /u01/oracle/PROD/db/tech_st/11.2.0/network/admin/PROD_furqan/listener.ora

Log messages written to /u01/app/diag/tnslsnr/furqan/listsner/alert/log.xml

TNS-01151: Missing listener name, listsner, in LISTENER.ORA

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

[oracle@furqan ~]$ lsnrctl start PROD

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-DEC-2016 13:41:12

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

TNS-01106: Listener using listener name LISTENER has already been started

[oracle@furqan ~]$

[oracle@furqan ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 25 13:41:37 2016

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

Connected to:

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

With the Automatic Storage Management option

SQL> alter system register ;

System altered.

SQL>

SQL>

SQL>

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Automatic Storage Management option

[oracle@furqan ~]$

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.123)(PORT=1521))))

SQL> alter system set local_listener ='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.123)(PORT=1521))))' scope=spfile;

System altered.

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@furqan ~]$ source grid.env

[oracle@furqan ~]$ srvctl start database -d PROD

[oracle@furqan ~]$ . /u01/oracle/PROD/db/tech_st/11.2.0/

-bash: .: /u01/oracle/PROD/db/tech_st/11.2.0/: is a directory

[oracle@furqan ~]$ . /u01/oracle/PROD/db/tech_st/11.2.0/PROD_furqan.env

[oracle@furqan ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 25 15:59:04 2016

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

Connected to:

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

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> select status from v$instance;

STATUS

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

OPEN

SQL>

SQL>

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

[oracle@furqan ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-DEC-2016 15:59:34

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

TNS-12541: TNS:no listener

 TNS-12560: TNS:protocol adapter error

  TNS-00511: No listener

   Linux Error: 2: No such file or directory

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=furqan.oracle.com)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                25-DEC-2016 14:11:25

Uptime                    0 days 1 hr. 48 min. 8 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/oracle/PROD/db/tech_st/11.2.0/network/admin/listener.ora

Listener Log File         /u01/oracle/PROD/db/tech_st/11.2.0/log/diag/tnslsnr/furqan/listener/alert/log.xml

Listening Endpoints Summary...

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

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

Services Summary...

Service "+ASM" has 1 instance(s).

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

Service "PROD" has 1 instance(s).

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

Service "production" has 1 instance(s).

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

The command completed successfully

[oracle@furqan ~]$ srvctl stop listener

[oracle@furqan ~]$ srvctl status listener

Listener LISTENER is enabled

Listener LISTENER is not running

[oracle@furqan ~]$ srvctl start listener

[oracle@furqan ~]$ crsctl check cluster

Parse error:

  'cluster' is an invalid argument

Brief usage:

  crsctl check has

     Check status of OHAS

  crsctl check resource {<resName> [...]|-w <filter>} [-n <server>] [-k <cid>] [-d <did>]

     Check status of resources

  crsctl check css

     Check status of Cluster Synchronization Services

  crsctl check evm

     Check status of Event Manager

For complete usage, use:

    crsctl [-h | --help]

For detailed help on each command and object and its options use:

    crsctl <command> <object> -h  For example, crsctl relocate resource -h

[oracle@furqan ~]$ crsctl check has

CRS-4638: Oracle High Availability Services is online

[oracle@furqan ~]$ crsctl check crs

Parse error:

  'crs' is an invalid argument

Brief usage:

  crsctl check has

     Check status of OHAS

  crsctl check resource {<resName> [...]|-w <filter>} [-n <server>] [-k <cid>] [-d <did>]

     Check status of resources

  crsctl check css

     Check status of Cluster Synchronization Services

  crsctl check evm

     Check status of Event Manager

For complete usage, use:

    crsctl [-h | --help]

For detailed help on each command and object and its options use:

    crsctl <command> <object> -h  For example, crsctl relocate resource -h

[oracle@furqan ~]$ crsctl stat res -t crs

Parse error:

  'crs' is an invalid argument

Brief usage:

  crsctl status <resource|type|serverpool|server> [<name>] [options]

  crsctl status <ip|testdns> <options>

  crsctl status ip -A {<IP_name>|<IP_address>}

     Check if the IP is alive

Where

     IP_name  Name which resolves to an IP. If name is not fully qualified domain name then standard name search will be used.

     IP_address     IP address

  crsctl status testdns [-address <IP_address>] [-port <port>] [-v]

     Check status of DNS server for specified domain

Where

     IP_address       DNS server address (defaults to hostname)

     port            The port on which the DNS server is listening. Default value for the port is 53.

     -v              Verbose output

For complete usage, use:

    crsctl [-h | --help]

For detailed help on each command and object and its options use:

    crsctl <command> <object> -h  For example, crsctl relocate resource -h

[oracle@furqan ~]$ crsctl stat res -t

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

NAME           TARGET  STATE        SERVER                   STATE_DETAILS      

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

Local Resources

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

ora.EBSDATA.dg

               ONLINE  ONLINE       furqan                                      

ora.LISTENER.lsnr

               ONLINE  ONLINE       furqan                                      

ora.asm

               ONLINE  ONLINE       furqan                   Started            

ora.ons

               OFFLINE OFFLINE      furqan                                      

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

Cluster Resources

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

ora.cssd

      1        ONLINE  ONLINE       furqan                                      

ora.diskmon

      1        OFFLINE OFFLINE                                                   

ora.evmd

      1        ONLINE  ONLINE       furqan                                      

ora.prod.db

      1        ONLINE  ONLINE       furqan                   Open               

ora.prod.production.svc

      1        ONLINE  ONLINE       furqan                                      

[oracle@furqan ~]$

[oracle@furqan ~]$ crs_stat

NAME=ora.EBSDATA.dg

TYPE=ora.diskgroup.type

TARGET=ONLINE

STATE=ONLINE on furqan

NAME=ora.LISTENER.lsnr

TYPE=ora.listener.type

TARGET=ONLINE

STATE=ONLINE on furqan

NAME=ora.asm

TYPE=ora.asm.type

TARGET=ONLINE

STATE=ONLINE on furqan

NAME=ora.cssd

TYPE=ora.cssd.type

TARGET=ONLINE

STATE=ONLINE on furqan

NAME=ora.diskmon

TYPE=ora.diskmon.type

TARGET=OFFLINE

STATE=OFFLINE

NAME=ora.evmd

TYPE=ora.evm.type

TARGET=ONLINE

STATE=ONLINE on furqan

NAME=ora.ons

TYPE=ora.ons.type

TARGET=OFFLINE

STATE=OFFLINE

NAME=ora.prod.db

TYPE=ora.database.type

TARGET=ONLINE

STATE=ONLINE on furqan

NAME=ora.prod.production.svc

TYPE=ora.service.type

TARGET=ONLINE

STATE=ONLINE on furqan

[oracle@furqan ~]$ crs_stat -t

Name           Type           Target    State     Host       

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

ora.EBSDATA.dg ora....up.type ONLINE    ONLINE    furqan     

ora....ER.lsnr ora....er.type ONLINE    ONLINE    furqan     

ora.asm        ora.asm.type   ONLINE    ONLINE    furqan     

ora.cssd       ora.cssd.type  ONLINE    ONLINE    furqan     

ora.diskmon    ora....on.type OFFLINE   OFFLINE              

ora.evmd       ora.evm.type   ONLINE    ONLINE    furqan     

ora.ons        ora.ons.type   OFFLINE   OFFLINE              

ora.prod.db    ora....se.type ONLINE    ONLINE    furqan     

ora....ion.svc ora....ce.type ONLINE    ONLINE    furqan     

[oracle@furqan ~]$

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

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

Method 2

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

====== get spfile ========

$srvctl config asm -a | grep -i spfile

Spfile: +DATA/asm/asmparameterfile/registry.123.123456789

OR

$grep "^Using.*spfile" alert_+ASM.log | tail -1

Using parameter settings in server-side spfile +DATA/asm/asmparameterfile/registry.123.123456789

There is another way. Thinking, as they say outside the box (Yuk! I avoid cliches like the plague!) about how tnsnames.ora allows IFILE commands, suggests that perhaps Oracle might allow me to create a pfile which specifies the existing spfile name and lets me set the correct LOCAL_LISTENER parameter to overwrite the broken setting in the spfile?

I confess, I also had a very vague recollection from way back when spfiles were first introduced, that I had seen/read/heard/tried something like this already, but as mentioned, it was a very vague recollection! Nevertheless, let’s create a plain vanilla pfile:

$vi /home/oracle/initASMtemp.ora

*.spfile="+DATA/asm/asmparameterfile/registry.123.123456789"

*.LOCAL_LISTENER='myserver.mydomain.com:1899'

The correction goes after the spfile, so that it takes effect rather than being overridden by the broken one in the spfile – assuming this trick works!

$sqlplus / as sysasm

Connected to an idle instance.

SQL> startup pfile='/home/oracle/initASMtemp.ora';

ASM instance started

Total System Global Area  283930624 bytes

Fixed Size                  2181896 bytes

Variable Size             256582904 bytes

ASM Cache                  25165824 bytes

ASM diskgroups mounted

We have a running ASM system!

Fix the broken parameter in the existing spfile:

SQL> alter system set local_listener='myserver.mydomain.com:1899' scope=spfile;

System altered.

SQL> show parameter local

NAME             TYPE        VALUE

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

local_listener   string      myserver.mydomain.com:1899

A shutdown and restart later and the spfile is once more working correctly.

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

login as: oracle

oracle@192.168.10.123's password:

Last login: Mon Dec 26 16:18:10 2016 from 192.168.10.12

[oracle@furqan ~]$ source grid.env

[oracle@furqan ~]$

[oracle@furqan ~]$

[oracle@furqan ~]$

[oracle@furqan ~]$ cd /u01/app/11.2.0/grid/dbs/

[oracle@furqan dbs]$ ls

ab_+ASM.dat  hc_+ASM.dat  hc_ASM+.dat  hc_+ASM.dat_org  init.ora  orapw+ASM

[oracle@furqan dbs]$ vi initASMtemp.ora

[oracle@furqan dbs]$ wpd

-bash: wpd: command not found

[oracle@furqan dbs]$ ls

ab_+ASM.dat  hc_+ASM.dat  hc_ASM+.dat  hc_+ASM.dat_org  initASMtemp.ora  init.ora  orapw+ASM

[oracle@furqan dbs]$ pwd

/u01/app/11.2.0/grid/dbs

[oracle@furqan dbs]$ ls /u01/app/11.2.0/grid/dbs/initASMtemp.ora

/u01/app/11.2.0/grid/dbs/initASMtemp.ora

[oracle@furqan dbs]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 26 16:55:00 2016

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

Connected to an idle instance.

SQL> startup pfile='/u01/app/11.2.0/grid/dbs/initASMtemp.ora';

ASM instance started

Total System Global Area 1135747072 bytes

Fixed Size                  2260728 bytes

Variable Size            1108320520 bytes

ASM Cache                  25165824 bytes

ORA-15032: not all alterations performed

ORA-15017: diskgroup "EBSDATA" cannot be mounted

ORA-15013: diskgroup "EBSDATA" is already mounted

SQL> show parameter local_list

NAME                 TYPE        VALUE

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

local_listener           string      furqan.oracle.com:1899

SQL> show parameter spfile

NAME                 TYPE        VALUE

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

spfile                  string      +EBSDATA/asm/asmparameterfile/

                                 registry.253.931397887

SQL> alter system set local_listener='furqan.oracle.com:1899' scope=spfile;

System altered.

SQL> shut immediate;

ORA-15097: cannot SHUTDOWN ASM instance with connected client (process 7565)

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Automatic Storage Management option

[oracle@furqan dbs]$ srvctl stop database -d PROD

[oracle@furqan dbs]$ asmcmd

ASMCMD> ls

EBSDATA/

ASMCMD> cd EBSDATA

ASMCMD> la

        commands:

        --------

        md_backup, md_restore

        lsattr, setattr

        cd, cp, du, find, help, ls, lsct, lsdg, lsof, mkalias

        mkdir, pwd, rm, rmalias

        chdg, chkdg, dropdg, iostat, lsdsk, lsod, mkdg, mount

        offline, online, rebal, remap, umount

        dsget, dsset, lsop, shutdown, spbackup, spcopy, spget

        spmove, spset, startup

        chtmpl, lstmpl, mktmpl, rmtmpl

        chgrp, chmod, chown, groups, grpmod, lsgrp, lspwusr, lsusr

        mkgrp, mkusr, orapwusr, passwd, rmgrp, rmusr

        volcreate, voldelete, voldisable, volenable, volinfo

        volresize, volset, volstat

ASMCMD> ls

ASM/

DB_UNKNOWN/

prod/

ASMCMD> cd DB_UNKNOWN/

ASMCMD> ls

PARAMETERFILE/

ASMCMD>

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

ERROR

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

Container:start oracle home /u01/app/11.2.0/grid

 [start] InstConnection::connectInt: server not attached

 [start] InstConnection:connectInt connected

 [start] InstAgent::startup

 [start] ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name ' '

Container:start oracle home /u01/app/11.2.0/grid

2016-12-26 15:42:52.847: [ora.asm][1100360000]{0:0:90} [start] InstConnection::connectInt: server not attached

2016-12-26 15:42:52.902: [ora.asm][1100360000]{0:0:90} [start] InstConnection:connectInt connected

2016-12-26 15:42:52.903: [ora.asm][1100360000]{0:0:90} [start] InstAgent::startup

2016-12-26 15:42:58.384: [ora.asm][1100360000]{0:0:90} [start] ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name ' '