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

Problem
=========
$
$ sqlplus nt24db/xxxx@racdb

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 18 16:33:15 2017

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

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


Enter user-name:
$
$

Solution

=========

SQL> alter system set local_listener = '(address_list=(address=(protocol=tcp)(host=drp4db-vip.nblbd.com)(port=1521)))';

As soon as set the value of local_listener, PMON will know the port number on which listener is running and it will dynamically register the instance with listener.

Check Connection:

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

$ sqlplus nt24db@racdb

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 18 17:01:42 2017

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

Enter password:

Connected to:

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

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

Data Mining and Real Application Testing options

SQL>

SQL>

SQL>

Dataguard SYNC Check

Dataguard SYNC Check 

Primary: 
SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

PhyStdby:
SQL> select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

PhyStdby:
SQL>select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in ('YES','IN-MEMORY')
group by thread# order by 1;


PhyStdby:
SQL> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" , almax-lhmax "Sequence Difference"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) lh
where al.thrd = lh.thrd;

RMAN Incremental Level 0 and Level 1 backup with Archivelog.

1. RMAN Incremental Level 0 Disk Backup with archivelog
oracle@d**db1 [/u02/app/oracle]$ cat 01.daily_rman_level0_disk_backup.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
export ORACLE_SID=d**db1
export PATH=$ORACLE_HOME/bin:$PATH
export TZ=GMT6

dt=`date '+%d%m%Y%H%M'`

LOG_FILE=/db_rmanbackup/rman_logs/${ORACLE_SID}_DAILY_LEVEL0_BACKUP_${dt}.log
echo "DCPDB DAILY LEVEL 0 BACKUP STARTED on `date` " >$LOG_FILE
rman  <<! >>$LOG_FILE
connect target r**nbkp/r**nbkp
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
sql 'alter system archive log current';
RUN
{
allocate channel c1  type disk;
allocate channel c2  type disk;
allocate channel c3  type disk;
allocate channel c4  type disk;
allocate channel c5  type disk;
allocate channel c6  type disk;
allocate channel c7  type disk;
sql "alter system switch logfile";
backup incremental level 0 AS COMPRESSED BACKUPSET database tag 'LEV0_T24_DB_$dt' format '/db_rmanbackup/t24_db_l0_%p_%s_$dt.bkp';
backup AS COMPRESSED BACKUPSET archivelog all tag 'daily_arch_bkp_disk' format '/db_rmanbackup/t24_arch_%p_%s_$dt.bkp';
backup current controlfile tag T24_CTL_$dt format '/db_rmanbackup/t24_ctl_lv0_%p_%s_$dt.bkp';
backup spfile tag T24_SPF_$dt format '/db_rmanbackup/t24_spf_lv0_%p_%s_$dt.bkp';
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
delete noprompt archivelog until time 'sysdate -3';
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
}
!
echo "DCPDB DAILY LEVEL 0 BACKUP FINISHIED on  `date` " >>$LOG_FILE

exit 0
oracle@d**db1 [/u02/app/oracle]$


2. RMAN Incremental Level 1 Disk Backup with Archivelog
oracle@d**db1 [/u02/app/oracle]$ cat 02.daily_rman_level1_disk_backup.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
export ORACLE_SID=d**db1
export PATH=$ORACLE_HOME/bin:$PATH
export TZ=GMT6

dt=`date '+%d%m%Y%H%M'`

LOG_FILE=/db_rmanbackup/rman_logs/${ORACLE_SID}_DAILY_LEVEL1_BACKUP_${dt}.log
echo "DCPDB DAILY LEVEL 1 BACKUP STARTED on `date` " >$LOG_FILE
rman  <<! >>$LOG_FILE
connect target r**nbkp/r**nbkp
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
sql 'alter system archive log current';
RUN
{
allocate channel c1  type disk;
allocate channel c2  type disk;
allocate channel c3  type disk;
allocate channel c4  type disk;
allocate channel c5  type disk;
allocate channel c6  type disk;
allocate channel c7  type disk;
sql "alter system switch logfile";
backup incremental level 1 cumulative AS COMPRESSED BACKUPSET database tag 'LEV1_T24_DB_$dt' format '/db_rmanbackup/t24_db_l1_%p_%s_$dt.bkp';
backup AS COMPRESSED BACKUPSET archivelog all tag 'daily_arch_bkp_disk' format '/db_rmanbackup/t24_arch_%p_%s_$dt.bkp';
backup current controlfile tag T24_CTL_$dt format '/db_rmanbackup/t24_ctl_lv0_%p_%s_$dt.bkp';
backup spfile tag T24_SPF_$dt format '/db_rmanbackup/t24_spf_lv0_%p_%s_$dt.bkp';
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
delete noprompt archivelog until time 'sysdate -3';
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
}
!
echo "DCPDB LEVEL 1 BACKUP FINISHIED on  `date` " >>$LOG_FILE

exit 0
oracle@d**db1 [/u02/app/oracle]$


3. RMAN level 0 disk backup (Monthly)
oracle@d**db1 [/u02/app/oracle]$ cat 03.monthly_rman_level0_disk_backup.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
export ORACLE_SID=d**db1
export PATH=$ORACLE_HOME/bin:$PATH
export TZ=GMT6

dt=`date '+%d%m%Y%H%M'`

LOG_FILE=/db_rmanbackup/rman_logs/${ORACLE_SID}_Monthly_LEVEL0_BACKUP_${dt}.log
echo "DCPDB MONTHLY LEVEL 0 BACKUP STARTED on `date` " >$LOG_FILE
rman  <<! >>$LOG_FILE
connect target r**nbkp/r**nbkp
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
sql 'alter system archive log current';
RUN
{
allocate channel c1  type disk;
allocate channel c2  type disk;
allocate channel c3  type disk;
allocate channel c4  type disk;
allocate channel c5  type disk;
allocate channel c6  type disk;
allocate channel c7  type disk;
sql "alter system switch logfile";
backup incremental level 0 AS COMPRESSED BACKUPSET database tag 'MONTHLY_L0_T24_DB_$dt' format '/db_rmanbackup/t24_db_month_%p_%s_$dt.bkp' keep until time 'sysdate+1095';
backup AS COMPRESSED BACKUPSET archivelog all tag 'monthly_arch_bkp_disk' format '/db_rmanbackup/t24_arch_%p_%s_$dt.bkp' keep until time 'sysdate+1095';
backup current controlfile tag T24_CTL_$dt format '/db_rmanbackup/t24_ctl_lv0_%p_%s_$dt.bkp';
backup spfile tag T24_SPF_$dt format '/db_rmanbackup/t24_spf_lv0_%p_%s_$dt.bkp';
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
delete noprompt archivelog until time 'sysdate -3';
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
}
!
echo "DCPDB MONTHLY LEVEL 0 BACKUP FINISHIED on  `date` " >>$LOG_FILE

exit 0
oracle@d**db1 [/u02/app/oracle]$

3. RMAN level 0 disk backup (Yearly)
oracle@dcpdb1 [/u02/app/oracle]$ cat 04.yearly_rman_level0_disk_backup.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
export ORACLE_SID=d**db1
export PATH=$ORACLE_HOME/bin:$PATH
export TZ=GMT6

dt=`date '+%d%m%Y%H%M'`

LOG_FILE=/db_rmanbackup/rman_logs/${ORACLE_SID}_Yearly_LEVEL0_BACKUP_${dt}.log
echo "DCPDB YEARLY LEVEL 0 BACKUP STARTED on `date` " >$LOG_FILE
rman  <<! >>$LOG_FILE
connect target r**nbkp/r**nbkp
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
sql 'alter system archive log current';
RUN
{
allocate channel c1  type disk;
allocate channel c2  type disk;
allocate channel c3  type disk;
allocate channel c4  type disk;
allocate channel c5  type disk;
allocate channel c6  type disk;
allocate channel c7  type disk;
sql "alter system switch logfile";
backup incremental level 0 AS COMPRESSED BACKUPSET database tag 'YEARLY_L0_T24_DB_$dt' format '/db_rmanbackup/t24_db_year_l0_%p_%s_$dt.bkp' keep until time 'sysdate+3650';
backup AS COMPRESSED BACKUPSET archivelog all tag 'yearly_arch_bkp_disk' format '/db_rmanbackup/t24_arch_%p_%s_$dt.bkp' keep until time 'sysdate+3650';
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
delete noprompt archivelog until time 'sysdate -3';
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
}
!
echo "DCPDB YEARLY LEVEL 0 BACKUP FINISHIED on  `date` " >>$LOG_FILE

exit 0
oracle@d**db1 [/u02/app/oracle]$

ORA-03137: TTC protocol internal error : [3147] [] [] [] [] [] [] []

ORA-03137: TTC protocol internal error : [3147] [] [] [] [] [] [] []

This error is only telling us that the client and server process communication was terminated due to some "event".

Client / server message exchanges use an Oracle specific protocol (TTC). ORA-3137 is reported if something unexpected is seen that does not conform to the expected protocol. The error can be raised by the client or server side of a connection.

Solution-1:

Disable bind peeking by setting:

SQL> alter system set "_optim_peek_user_binds"=false;

Restart the database and listener services.

This hidden parameter was introduced since 9i in order to help optimizer makes better decisions for producing better executions plans. This hidden parameter lets Optimizer peeks the values of binds at hard parsing phase using them for looking for the best exec plans based on current stats and values of binds.

When bind variables are used in a statement, it is assumed that cursor sharing is intended and that different invocations are supposed to use the same execution plan.

The parameter ( "_optim_peek_user_binds") controls this feature's behavior, default value is TRUE.

Solution-2:

If error raise continuously, then you can install the following patch:

Patch Number# 18841764

Note: Bug 18841764 Network related error like ORA-12592 or ORA-3137 or ORA-3106 may be signaled

How to collect ADDM and AWR Report

Below are the procedure to collect ADDM & AWR report:

1. Collect ADDM report

[oracle@qpaydb ~]$ cd $ORACLE_HOME/rdbms/admin

[oracle@qpaydb admin]$ pwd

/u01/app/oracle/product/11.2.0.4.0/dbhome_1/rdbms/admin

[oracle@qpaydb admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 2 16:03:56 2017

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, OLAP, Data Mining and Real Application Testing options

SQL> @addmrpt.sql

2. Collect AWR report

[oracle@qpaydb admin]$

[oracle@qpaydb admin]$ pwd

/u01/app/oracle/product/11.2.0.4.0/dbhome_1/rdbms/admin

[oracle@qpaydb admin]$

[oracle@qpaydb admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 2 16:08:19 2017

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, OLAP, Data Mining and Real Application Testing options

SQL> @awrrpt.sql

Cleaning Disk header or making ASM disk from member to candidate

We have to below operation for disk cleaning OR making ASM member disk to candidate disk:

# dd if=/dev/zero of=/dev/oracle/asm_ocr01 bs=1024k count=1000

1000+0 records in

1000+0 records out

# dd if=/dev/zero of=/dev/oracle/asm_ocr02 bs=1024k count=1000

1000+0 records in

1000+0 records out

# dd if=/dev/zero of=/dev/oracle/asm_ocr03 bs=1024k count=1000

1000+0 records in

1000+0 records out

#dd if=/dev/zero of=/dev/oracle/asm_vot01 bs=1024k count=1000

1000+0 records in

1000+0 records out

# dd if=/dev/zero of=/dev/oracle/asm_vot02 bs=1024k count=1000

1000+0 records in

1000+0 records out

# dd if=/dev/zero of=/dev/oracle/asm_vot03 bs=1024k count=1000

1000+0 records in

1000+0 records out

#

# dd if=/dev/zero of=/dev/oracle/asm_data01 bs=1024k count=1000

1000+0 records in

1000+0 records out

# dd if=/dev/zero of=/dev/oracle/asm_data02 bs=1024k count=1000

1000+0 records in

1000+0 records out

# dd if=/dev/zero of=/dev/oracle/asm_data03 bs=1024k count=1000

1000+0 records in

1000+0 records out

# dd if=/dev/zero of=/dev/oracle/asm_fra01 bs=1024k count=1000

1000+0 records in

1000+0 records out

# dd if=/dev/zero of=/dev/oracle/asm_control1 bs=1024k count=1000

1000+0 records in

1000+0 records out

# dd if=/dev/zero of=/dev/oracle/asm_control2 bs=1024k count=1000

1000+0 records in

1000+0 records out

# dd if=/dev/zero of=/dev/oracle/asm_redo1 bs=1024k count=1000

1000+0 records in

1000+0 records out

#

# dd if=/dev/zero of=/dev/oracle/asm_redo2 bs=1024k count=1000

1000+0 records in

1000+0 records out