ORA-12514: TNS:listener does not currently know of service requested in connect descriptor  !!!!!!!!!!!!! 

Oracle 12c pluggable database connection problem 

Oracle 12c Not able to connect pluggable database ... 

SQL> startup ;
ORACLE instance started.

Total System Global Area  622149632 bytes
Fixed Size                  2291424 bytes
Variable Size             473958688 bytes
Database Buffers          142606336 bytes
Redo Buffers                3293184 bytes
Database mounted.
Database opened.
SQL> alter pluggable database anujv open;

Pluggable database altered.

SQL> !tnsping anujv

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 31-MAY-2014 12:33:10

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.18)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = anujv)))
OK (0 msec)

SQL> !lsnrctl start

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 31-MAY-2014 12:33:19

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

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

SQL>  !lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 31-MAY-2014 12:33:28

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.18)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                31-MAY-2014 12:30:27
Uptime                    0 days 0 hr. 3 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/unknown/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.18)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "plsextproc" has 1 instance(s).
  Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
Service "vihaan" has 1 instance(s).
  Instance "vihaan", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

SQL> alter system register ;

System altered.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 31-MAY-2014 12:33:53

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.18)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                31-MAY-2014 12:30:27
Uptime                    0 days 0 hr. 3 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/unknown/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.18)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "plsextproc" has 1 instance(s).
  Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
Service "vihaan" has 1 instance(s).
  Instance "vihaan", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

SQL> !tnsping anujv

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 31-MAY-2014 12:34:22

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.18)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = anujv)))
OK (0 msec)



set linesize 200 pagesize 200
select SERVICE_ID,NAME,CON_NAME,CON_ID from v$active_services

SERVICE_ID NAME                                                             CON_NAME                           CON_ID
---------- ---------------------------------------------------------------- ------------------------------ ----------
         7 anujv                                                            ANUJV                                   4
         0 vihaanpdb                                                        VIHAANPDB                               3
         5 vihaanXDB                                                        CDB$ROOT                                1
         6 vihaan                                                           CDB$ROOT                                1
         1 SYS$BACKGROUND                                                   CDB$ROOT                                1
         2 SYS$USERS                                                        CDB$ROOT                                1

6 rows selected.



[oracle@unknown ~]$ sqlplus anuj/vihaa1@192.168.0.18:1521/anujv

SQL*Plus: Release 12.1.0.1.0 Production on Sat May 31 12:38:23 2014

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor



SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string                                             ****************---- value is blank
 


SQL> def
DEFINE _DATE           = "31-MAY-14" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "vihaan" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1201000100" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1201000100" (CHAR)


set local listener -----------------

SQL> ALTER SYSTEM SET local_listener="(address=(protocol=tcp)(host=192.168.0.18)(port=1521))" scope=both sid='vihaan' ;

System altered.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 31-MAY-2014 12:42:27

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.18)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                31-MAY-2014 12:30:27
Uptime                    0 days 0 hr. 11 min. 59 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/unknown/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.18)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "anujv" has 1 instance(s).
  Instance "vihaan", status READY, has 1 handler(s) for this service...
Service "plsextproc" has 1 instance(s).
  Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
Service "vihaan" has 2 instance(s).
  Instance "vihaan", status UNKNOWN, has 1 handler(s) for this service...
  Instance "vihaan", status READY, has 1 handler(s) for this service...
Service "vihaanXDB" has 1 instance(s).
  Instance "vihaan", status READY, has 1 handler(s) for this service...
Service "vihaanpdb" has 1 instance(s).
  Instance "vihaan", status READY, has 1 handler(s) for this service...
The command completed successfully




[oracle@unknown ~]$ sqlplus anuj/vihaa1@192.168.0.18:1521/anujv

SQL*Plus: Release 12.1.0.1.0 Production on Sat May 31 12:44:15 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

====

VIHAANPDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.18)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = vihaanpdb)
    )
  )


anujv =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.18)(PORT = 1521)))
 (CONNECT_DATA =
 (SERVICE_NAME = anujv)
 )
  )

[oracle@unknown admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/n                                                                                    etwork/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.18)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = vihaan)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = vihaan)
    )
    (SID_DESC =
      (SID_NAME = plsextproc)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.0.18)(PORT = 1521))
      (ADDRESS = (PROTOCOL = ipc)(KEY = extproc))
    )
  )

All control files are lost or corrupted :( ....  we don't have any backup !!!!!!!!!!!!!! 

ORA-00205: error in identifying control file, check alert log for more info

create a text file for control file ..

SQL> alter database backup controlfile to trace as '/tmp/control.trc';

Database altered.
or
SQL> alter database backup controlfile to trace ;
Database altered.

SQL> show parameter diag

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
diagnostic_dest string /u01/app/oracle

will be in trace dir <<<<<<<

/u01/app/oracle/diag/rdbms/vihaan/vihaan/trace

-rw-r----- 1 oracle asmadmin 6294 May 26 16:04 vihaan_ora_5960.trc <<<<<<<<<<<---- 
-rw-r----- 1 oracle asmadmin 418532 May 26 16:04 alert_vihaan.log

SQL> create table test as select sysdate sdate from dual ;

Table created.

SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

Session altered.

SQL> select * from test ;

SDATE
-------------------
26-05-2014 16:17:16

SQL>ALTER SYSTEM ARCHIVE LOG CURRENT
SQL>alter system switch logfile;

SQL> select * from test ;

SDATE
-------------------
26-05-2014 16:17:16
26-05-2014 16:19:46
26-05-2014 16:19:48
26-05-2014 16:19:49


ASMCMD [+DATA/VIHAAN/CONTROLFILE] > ls -s
Block_Size Blocks Bytes Space Name
 16384 615 10076160 16777216 current.256.848518731
 16384 615 10076160 16777216 current.284.848518731

deleted all the control file ....

ASMCMD [+DATA/VIHAAN/CONTROLFILE] > rm current.256.848518731 current.284.848518731


***************************************************************************
CREATE CONTROLFILE REUSE DATABASE "VIHAAN" RESETLOGS ARCHIVELOG
 MAXLOGFILES 16
 MAXLOGMEMBERS 3
 MAXDATAFILES 100
 MAXINSTANCES 8
 MAXLOGHISTORY 292
LOGFILE
 GROUP 1 '+DATA/vihaan/redo01.log' SIZE 50M BLOCKSIZE 512,
 GROUP 2 '+DATA/vihaan/redo02.log' SIZE 50M BLOCKSIZE 512,
 GROUP 3 '+DATA/vihaan/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
 '+DATA/vihaan/system01.dbf',
 '+DATA/vihaan/sysaux01.dbf',
 '+DATA/vihaan/undotbs01.dbf',
 '+DATA/vihaan/users01.dbf'
CHARACTER SET WE8MSWIN1252 ;
******************************************************************************

SQL> CREATE CONTROLFILE REUSE DATABASE "VIHAAN" RESETLOGS ARCHIVELOG
 2 MAXLOGFILES 16
 MAXLOGMEMBERS 3
 MAXDATAFILES 100
 MAXINSTANCES 8
 6 MAXLOGHISTORY 292
 7 LOGFILE
 8 GROUP 1 '+DATA/vihaan/redo01.log' SIZE 50M BLOCKSIZE 512,
 9 GROUP 2 '+DATA/vihaan/redo02.log' SIZE 50M BLOCKSIZE 512,
 10 GROUP 3 '+DATA/vihaan/redo03.log' SIZE 50M BLOCKSIZE 512
 11 -- STANDBY LOGFILE
 12 DATAFILE
 13 '+DATA/vihaan/system01.dbf',
 14 '+DATA/vihaan/sysaux01.dbf',
 15 '+DATA/vihaan/undotbs01.dbf',
 '+DATA/vihaan/users01.dbf'
 CHARACTER SET WE8MSWIN1252 ;

Control file created.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Disabled
Archive destination +DATA/vihaan/archivelog
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 963360 generated at 05/26/2014 16:24:15 needed for thread 1
ORA-00289: suggestion : +DATA/vihaan/archivelog/1_4_848518930.dbf
ORA-00280: change 963360 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto

SQL> alter database open RESETLOGS ;
Database altered.

SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
Session altered.

SQL> select * from test ;
SDATE
-------------------
26-05-2014 16:17:16
26-05-2014 16:19:46
26-05-2014 16:19:48
26-05-2014 16:19:49

ASM init file  .....

How to create a init+ASM.ora 

ORA-29786: SIHA attribute GET failed with error [Attribute 'SPFILE' sts[200] lsts[0]]

Create spfile for above error ...

init+ASM.ora 

###########################################
# Cache and I/O
###########################################
large_pool_size = 12m

###########################################
 # ASM Instance Parameters
###########################################
instance_type = asm
asm_power_limit = 1

###########################################
# Security and Diagnostics
###########################################
remote_login_passwordfile = exclusive
diagnostic_dest = '/u01/app/grid'

[grid@unknown dbs]$ vi init+ASM.ora
[grid@unknown dbs]$ !sql
sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 22 13:12:30 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option

SQL> create spfile from pfile ;

File created.

SQL> !cat init+ASM.ora
###########################################
# Cache and I/O
###########################################
large_pool_size = 12m
###########################################
 # ASM Instance Parameters
###########################################
instance_type = asm
asm_power_limit = 1
###########################################
# Security and Diagnostics
###########################################
remote_login_passwordfile = exclusive
diagnostic_dest = '/u01/app/grid'

ORA-15100: invalid or missing diskgroup name

ORA-15110: no diskgroups mounted 

ORA-15100: invalid or missing diskgroup name

set linesize 200
col PATH format a30
col LABEL format a15
select group_number, disk_number, name, label, path, redundancy, mount_status, header_status  from v$asm_disk;

GROUP_NUMBER DISK_NUMBER NAME                           LABEL           PATH                           REDUNDA MOUNT_S HEADER_STATU
------------ ----------- ------------------------------ --------------- ------------------------------ ------- ------- ------------
           0           0                                DATA            ORCL:DATA                      UNKNOWN CLOSED  MEMBER


SQL>  ALTER DISKGROUP data mount ;


set linesize 200
col PATH format a30
col LABEL format a15
select group_number, disk_number, name, label, path, redundancy, mount_status, header_status  from v$asm_disk;

GROUP_NUMBER DISK_NUMBER NAME                           LABEL           PATH                           REDUNDA MOUNT_S HEADER_STATU
------------ ----------- ------------------------------ --------------- ------------------------------ ------- ------- ------------
           1           0 DATA                           DATA            ORCL:DATA                      UNKNOWN CACHED  MEMBER



alter system set asm_diskstring='/dev/rdsk/*' scope=both;

SQL> alter system set asm_diskstring='ORCL:DATA' scope=both;

System altered.


SQL> alter system set asm_diskgroups='DATA' scope=both ;