ORA-19913: unable to decrypt backup - solution

While restoring database, I received below error.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/11/2019 04:53:47
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-19870: error while restoring backup piece /u02/work/038_08-Nov-2019_Gouranga_arisg51d_refresh/rman/arisg51p_03-Nov-2019_13-39-15_full_cf.bkp
ORA-19913: unable to decrypt backup


From verification found, this backup was taken with encryption enabled database and there is wallet configure also.

So while you restore, then start the database in 'nomount' state and see the below status. If it is closed, then you can't restore. So follow the below steps to start restore database.

-- start in nmount 
[oracle@example01 ~]$ sqlplus / as sysdba


SQL> select status from v$encryption_wallet;

STATUS
------------------------------------------------------------------------
CLOSED

SQL> quit


status must be 'open'


-- fix/ Solution

copy the wallets files and configure in sqlnet.ora also, then verify the status

e.g.,

coped below wallet files to your location something like this:

/u01/app/oracle/admin/<your_db_name>/wallet

Then, point your wallet in 'sqlnet.ora' file.

$ cd $ORACLE_HOME/network/admin
$ cat sqlnet.ora
ENCRYPTION_WALLET_LOCATION =
  (SOURCE = (METHOD = FILE)
     (METHOD_DATA = (DIRECTORY = /u01/app/oracle/admin/$ORACLE_UNQNAME/wallet)
     )
  )


SQL> select status from v$encryption_wallet;

STATUS
-------------------------------------------------
OPEN


But ensure, to enable encryption while restore the database like what is enabled during backup.

e.g.,

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

-- Encryption enabled with key AES256
RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;

new RMAN configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES256';

new RMAN configuration parameters:
CONFIGURE ENCRYPTION ALGORITHM 'AES256';
new RMAN configuration parameters are successfully stored

Note that you can enable the encryption only after restoring the controlfile.

Fix: ORA-65086: cannot open/close the pluggable database

During cloning activity I unplugged a pdb and then try to open it but I faced "ORA-65086: cannot open/close the pluggable database  " error. I view Oracle document and found below concept to do the following steps:

-- Unplug a pluggable database

SQL> ALTER PLUGGABLE DATABASE hrpdb close immediate;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE hrpdb UNPLUG INTO '/u10/EXAM/bkp_pdb/hrpdb.xml';

Pluggable database altered.

Now you can see the status of pdbs.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 SALESPDB                       READ WRITE NO

         5 PDB2                           READ WRITE NO

         6 HRPDB                          MOUNTED

SQL>

Here, I am trying to open the same database. But it is not opening due to unplugged.

SQL> alter pluggable database hrpdb open;

alter pluggable database hrpdb open

*

ERROR at line 1:

ORA-65086: cannot open/close the pluggable database

SQL>

It is not possible to open again the pluggable database… The official documentation claims:

After a PDB is unplugged, it remains in the CDB with an open mode of MOUNTED and a status of UNPLUGGED. The only operation you can perform on an unplugged PDB is DROP PLUGGABLE DATABASE, which will remove it from the CDB. You must drop the PDB before you can plug it into the same CDB or another CDB.

So, following official documentation recommendations:

SQL> drop pluggable database hrpdb;

Pluggable database dropped.

SQL> CREATE pluggable DATABASE hrpdb USING '/u10/EXAM/bkp_pdb/hrpdb.xml' NOCOPY;

Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 SALESPDB                       READ WRITE NO

         5 PDB2                           READ WRITE NO

         6 HRPDB                          MOUNTED

SQL> alter pluggable database hrpdb open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 SALESPDB                       READ WRITE NO

         5 PDB2                           READ WRITE NO

         6 HRPDB                          READ WRITE NO

SQL> 

Hope, It may helped you. 

ORA-00600: error in Queue Table SYS.SYS$SERVICE_METRICS_TAB

I found below ORA-600 in my RAC production database of version 11.2.0.4+PSU7. From the Oracle documents, I did the following and floating of ORA-600 is stopped completly.

Verify the following:

- alert_PROD1.log
- PROD1_q002_7930002.trc


"alert_PROD1.log" shows an ORA-00600 [kwqitnmphe:ltbagi] here:
---
Fri Mar 25 13:43:49 2016
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD1/trace/PROD1_q000_10682516.trc (incident=40305):
ORA-00600: internal error code, arguments: [kwqitnmphe:ltbagi], [1], [0], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/prod/PROD1/incident/incdir_40305/PROD1_q000_10682516_i40305.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
..............................
..............................


Verify the "PROD1_q000_10682516.trc" and "PROD1_q000_10682516_i40305.trc" files.


Trace file /u01/app/oracle/diag/rdbms/prod/PROD1/trace/PROD1_q000_10682516.trc
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
ORACLE_HOME = /u01/app/oracle/11.2.0/db_home
System name: AIX
Node name: rac01
Release: 1
Version: 7
Machine: 00F9D98B4C00
Instance name: PROD1
Redo thread mounted by this instance: 1
Oracle process number: 38
Unix process pid: 10682516, image: oracle@rac01 (Q000)


*** 2016-03-25 13:43:49.018
*** SESSION ID:(579.19) 2016-03-25 13:43:49.018
*** CLIENT ID:() 2016-03-25 13:43:49.018
*** SERVICE NAME:(SYS$BACKGROUND) 2016-03-25 13:43:49.018
*** MODULE NAME:(Streams) 2016-03-25 13:43:49.018
*** ACTION NAME:(QMON Slave) 2016-03-25 13:43:49.018

Incident 40305 created, dump file: /u01/app/oracle/diag/rdbms/prod/PROD1/incident/incdir_40305/PROD1_q000_10682516_i40305.trc
ORA-00600: internal error code, arguments: [kwqitnmphe:ltbagi], [1], [0], [], [], [], [], [], [], [], [], []

kwqitmmit during kwqitmmit:time mgr IOT proc: Error 600 in Queue Table SYS.SYS$SERVICE_METRICS_TAB
kwqicaclcur: Error 600
Cursor Session Number : 580
Cursor Session Serial : 3
Cursor Pin Number : 24585
kwqitmmit during kwqitmmit:end proc: Error 600 in Queue Table SYS.SYS$SERVICE_METRICS_TAB
kwqitptm: error 600 encountered during serving 13593

*** 2016-03-25 13:43:51.148
ORA-00600: internal error code, arguments: [kwqitnmphe:ltbagi], [1], [0], [], [], [], [], [], [], [], [], []
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-00600: internal error code, arguments: [kwqitnmphe:ltbagi], [1], [0], [], [], [], [], [], [], [], [], []


Action plan:

1) If you are upgrading :

Drop the queue table -

exec dbms_aqadm.drop_queue_table( queue_table=>'SYS.SYS$SERVICE_METRICS_TAB', force => TRUE)

select object_name, object_type from dba_objects where object_name like '%SYS$SERVICE_%'; ----> no rows should be returned.

Follow the below steps to recreate them.

SQL>STARTUP UPGRADE

SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

select object_name, object_type,status from dba_objects where object_name like '%SYS$SERVICE_%';

If the script fails to create the queue, please let us know so that we may recreate it manually.

2) If it is comming in production, then take downtime and do the following:

 Drop the queue table -

exec dbms_aqadm.drop_queue_table( queue_table=>'SYS.SYS$SERVICE_METRICS_TAB', force => TRUE)

select object_name, object_type from dba_objects where object_name like '%SYS$SERVICE_%'; ----> no rows should be returned.

Follow the below steps to recreate them.

SQL>STARTUP

SQL>alter system enable restricted session;

SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL>alter system disable restricted session;

Thanks .

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file :Fix

Fix: ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '+DATA'

Issue:

If you are using XML in your database and you are trying to update any XML data, then above error may come.

Fix:

1) Add below line in your parameter file and bounce the database if not there.


*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)'

2) Unlock the XDB user if locked.

SQL> alter user XDB account unlock;


3) If you have created a duplicate database or open standby database r/w mode, then source TEMP tablespaces will no more valid. drop your TEMP tablespace and create new TEMP tablespace and make it default.

e.g.,
SQL> DROP TABLESPACE TEMP  INCLUDING CONTENTS AND DATAFILES;

SQL> create temporary tablespace TEMP02 tempfile '/u02/PROD/ORADATA/temp_02.dbf' size 1024M autoextend on;

SQL> alter database default temporary tablespace TEMP02;

Bounce the database. Now sure the above error will not come.






Thanks

ORA-12162: TNS:net service name is incorrectly specified

ORA-12162: TNS:net service name is incorrectly specified

I have received a call from junior DBA that while I am connecting to newly installed database software and connecting as "/ as sysdba", above error is comming.

The Error:

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 29 14:30:36 2015

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

ERROR:
ORA-12162: TNS:net service name is incorrectly specified


Enter user-name:

...
Solution:
This is a very small and simple issue.The error message ORA-12162 "TNS:net service name is incorrectly specified", is very misleading.

It suggests that there is a problem with the tnsnames.ora file contents, but in reality the message ORA-12162 "TNS:net service name is incorrectly specified" results from improperly setting your ORACLE_SID value.

To fix this error in Windows, set your $ORACLE_HOME:

c:> set ORACLE_SID=PROD

In Linux, these commands sets ORACLE_HOME and $ORACLE_SID as follows:

export ORACLE_HOME=/u01/app/product/11.2.0/dbhome_1;

export ORACLE_SID=PROD;
export PATH=$ORACLE_HOME/bin:$PATH

I am sure now no issue will come.

ORA-07445 issues for SYS_AUTO_SQL_TUNING_TASK job

ORA-07445 for SYS_AUTO_SQL_TUNING_TASK

Automatic SQL Tuning in Oracle Database 11g

• As part of Automatic SQL Tuning, Oracle 11g automatically runs the SQL Tuning Advisor against high impact SQL statements during maintenance windows. This process involves the following steps:
• AWR statistics are used to compile an ordered list of the SQL statements with the greatest performance impact on the system, where the impact is the sum of the CPU and I/O times for the statement during the past week. The list excludes statements that are inherently less tunable, such as recently (within a month) tuned recursive statements, parallel queries, DML, DDL and SQL statements whose performance problems are caused by concurrency issues.
• The SQL tuning advisor is run against each statement in turn. The outcome may include both SQL profiles and other recommendations.
• Suggested SQL profiles are performance tested, and those that result in at least a threefold improvement are accepted if the ACCEPT_SQL_PROFILES parameter is set to TRUE, or reported if it is set to FALSE.The accepted SQL profiles are optionally implemented . Several factors many prevent SQL profiles from being implemented automatically, including stale optimizer statistics of dependent objects. The TYPE column of the DBA_SQL_PROFILES view indicates if SQL profiles are created manually (MANUAL) or automatically (AUTO-TUNE).

Oracle Database 11g Release 2 (11.2.0.2 onward) has some minor changes to the Automatic SQL Tuning feature introduced on Oracle 11g Release 1. In the previous release, reports and amendments to the automatic tuning task parameters was performed using the DBMS_SQLTUNE package. From 11.2.0.2 onward, this should be done using the DBMS_AUTO_SQLTUNE package, which requires the DBA role.

Note. The top-level enabling and disabling of the admin task is still done using the DBMS_AUTO_TASK_ADMIN package,


Issue:

If you are facing issues with "auto tuning task" issue, then it will lead more core dump generation issues.


Below sample errors may be found from alert log.
-----------------------------------------------

< Wed Mar 11 22:00:02 2015
< Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
< Wed Mar 11 22:00:35 2015
< Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x20] [PC:0x47EDC6F, __intel_new_memcpy()+3343] [flags: 0x0, count: 1]
< Errors in file /u02/app/oracle/diag/rdbms/PROD/PROD/trace/PROD_j000_53026.trc  (incident=36860):
< ORA-07445: exception encountered: core dump [__intel_new_memcpy()+3343] [SIGSEGV] [ADDR:0x20] [PC:0x47EDC6F] [Address not mapped to object] []
< Incident details in: /u02/app/oracle/diag/rdbms/PROD/PROD/incident/incdir_36860/PROD_j000_53026_i36860.trc
< Use ADRCI or Support Workbench to package the incident.
< See Note 411.1 at My Oracle Support for error and packaging details.
< Wed Mar 11 22:00:43 2015
< Dumping diagnostic data in directory=[cdmp_20150311220043], requested by (instance=1, osid=53026 (J000)), summary=[incident=36860].
< Wed Mar 11 22:00:44 2015
< Sweep [inc][36860]: completed
< Sweep [inc2][36860]: completed
< Wed Mar 11 22:01:11 2015
< Thread 1 cannot allocate new log, sequence 2199
< Private strand flush not complete
<   Current log# 3 seq# 2198 mem# 0: /u02/PROD/oradata/redo03.log
< Thread 1 advanced to log sequence 2199 (LGWR switch)
<   Current log# 4 seq# 2199 mem# 0: /u02/PROD/oradata/redo04.log
< Wed Mar 11 22:01:16 2015
< Archived Log entry 3026 added for thread 1 sequence 2198 ID 0x81a6dea4 dest 1:


Action:

Disable auto tuning task advisor.

To disable:

BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/


If required to enable:

BEGIN
  DBMS_AUTO_TASK_ADMIN.enable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

To verify:

SELECT parameter_name, parameter_value
FROM   dba_advisor_parameters
WHERE  task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND    parameter_name = 'ACCEPT_SQL_PROFILES';

output:

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------       --------------------------------
ACCEPT_SQL_PROFILES            FALSE

Resolving ORA-02374, 12899, 02372 issues during data-migration with different characterset

Resolving ORA-02374, ORA-12899, ORA-02372 issues during data-migration with different database character set

-- Error in import log

ORA-02374: conversion error loading table "crm"."transaction"
ORA-12899: value too large for column DETAILS_NOTE (actual: 4024, maximum: 4000)
ORA-02372: data for row: DETAILS_NOTE: 0X'434552564943414C205350494E450D0A0D0A4D756C7469706C' 

Issue Description:

I have transaction data in table "crm.transaction" where my database Characterset is "WE8MSWIN1252". In this table "DETAILS_NOTE" column datatype is varchar2 where some of the column values fully occupied with 4000 characters. When I am trying to import using IMPDP with another characterset database "AL32UTF8" then above error is coming.


Root cause:

RCA: When data are coming from source character-set to target new character set, special charters taking 3 char space, so it is showing as "ORA-12899: value too large for column RESULT (actual: 4024, maximum: 4000)".

Solution :

Target Character-set : AL32UTF8

Method-1:

• Migrate your schema or database.
• Create dblink with souuce to target database.
• Disable constraints.
• alter the table as example shown:
          SQL> alter table crm.transaction modify DETAILS_NOTE  VARCHAR2(4000 CHAR);
• Now insert the whole data .

Method-2:

• Migrate your schema or database.
• Disable constraints.
• alter the table as example shown:
          SQL> alter table crm.transaction modify DETAILS_NOTE  VARCHAR2(4000 CHAR);
• import the table from logical dump with below format.

impdp full=N directory=data_pump dumpfile=PROD_full_%U.dmp logfile=PROD_trx_imp.log TABLE_EXISTS_ACTION=TRUNCATE schemas=CRM TABLES=CRM.transaction REMAP_TABLE=HR.transaction:transaction

See the DDL of the Table after alter:

create table crm.transaction
(
  trnxid      NUMBER not null,
  rsltrpthdrid    NUMBER not null,
  paramdetailid    NUMBER not null,
  details_note     VARCHAR2(4000 CHAR),
  unitid      NUMBER,
) tablespace CRM_TBLS;

Thanx

Configuring mail alerts via PLSQL - Troubleshot ORA-29278

Troubleshot ORA-29278: SMTP transient error: 421 Service not available.

Using utl_smtp:

The obselete utl_smtppackage was first introduced in Oracle 8i to give access to the SMTP protocol from PL/SQL.  The package is dependent on the JServer option  which can be loaded using the Database Configuration Assistant (DBCA) or by running the following scripts as the sys user if it is not already present.

Using the package to send an email requires some knowledge of the SMTP protocol, but for the purpose of this text, a simple send_mail procedure has been written that should be suitable for most error reporting.

Assume: 
>> My SMTP server IP is : 170.1.1.1
>> We will use ALERT user to send test mails.

-- To create alert user:
SQL> create user ALERT identified by xwdkjdlc13ns default tablespace USERS temporary tablespace TEMP profile DEFAULT;
SQL> grant CONNECT to ALERT;
SQL> grant execute on UTL_MAIL to ALERT;
SQL> grant MGMT_USER to ALERT;
SQL> grant SELECT_CATALOG_ROLE to ALERT;

-- Necessary configuration:
-- in spfile set

SQL> alter system set smtp_out_server = '170.1.1.1' scope=spfile;

Now, bounce the database;

-- before set

SQL> show parameter smtp;
NAME                TYPE        VALUE
------------------- ----------- ------------------------------
smtp_out_server     string
SQL>

-- after set
SQL> show parameter smtp;
NAME                TYPE        VALUE
------------------- ----------- ------------------------------
smtp_out_server     string      170.1.1.1
SQL>

-- give grants

SQL> grant execute on UTL_MAIL to public;
OR
SQL> grant execute on UTL_MAIL to ALERT;
SQL> ALTER SESSION SET smtp_out_server = '170.1.1.1';
SQL> exec UTL_MAIL.send(sender => 'oracle.com', recipients => 'gourang_m@gmail.com', subject => 'Test Mail', message => 'Hello World', mime_type => 'text; charset=us-ascii');

If you are facing "ORA-29278: SMTP transient error: 421 Service not available" error, the read the below steps.

The Problem:

You are trying to use the UTL_MAIL package to send email from your database. When attempting to send a mail, you recieve the following error:

SQL> exec utl_mail.send('oracle.com','gourang_m@gmail.com','test mail','Hello World',mime_type => 'text; charset=us-ascii');

ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at line 2

Troubleshoot methods:

The Cause:

If this fails it could be a result of many things, so please check all of the items below:

1) SMTP_OUT_SERVER
Check the value for the initialisation parameter SMTP_OUT_SERVER. This should be set to the SMTP server IP. If not, run:

ALTER SYSTEM SET SMTP_OUT_SERVER=”<IP>” scope=both;

2) Recent Upgrade to Oracle 11g
Are you running Oracle database 11g? If so, you will need to have XMLDB & Java installed in order to configure fine grained auditing and enable it there for each user explicitly. You can run through these checks to confirm you have everything in place:

col COMP_NAME format a40;
col VERSION format a12;
col STATUS format a12;
SELECT COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;

COMP_NAME                                VERSION         STATUS
---------------------------------------- --------------- ---------
Oracle Enterprise Manager                11.2.0.3.0      VALID
Oracle XML Database                      11.2.0.3.0      VALID
Oracle Expression Filter                 11.2.0.3.0      VALID
Oracle Rules Manager                     11.2.0.3.0      VALID
Oracle Workspace Manager                 11.2.0.3.0      VALID
Oracle Database Catalog Views            11.2.0.3.0      VALID
Oracle Database Packages and Types       11.2.0.3.0      VALID
JServer JAVA Virtual Machine             11.2.0.3.0      VALID
Oracle XDK                               11.2.0.3.0      VALID
Oracle Database Java Packages            11.2.0.3.0      VALID

10 rows selected.


If the “JServer JAVA Virtual Machine” and “Oracle XML Database” components are not there then you need to install them to get this working.

The following scripts are what you would use to install Java and XML DB.

Note: These instructions are taken directly from Oracle Metalink, but I would recommend double checking the notes on there just to make sure there have been no updates to them since I wrote this.

3. UTL_MAIL Package & Grants:

You will need to ensure that the UTL_MAIL package exists and that the required users have permission to execute it. You can install it and grant execute privileges on it to a user with the following commands:

SQL/> connect as sysdba
SQL> @?/rdbms/admin/utlmail.sql
SQL> @?/rdbms/admin/prvtmail.plb
SQL> GRANT EXECUTE ON SYS.UTL_MAIL TO USER;

e.g.,
SQL> GRANT EXECUTE ON SYS.UTL_MAIL TO alert;

When executed:

SQL> @?/rdbms/admin/utlmail.sql
Package created.
Synonym created.

SQL> @?/rdbms/admin/prvtmail.plb
Package created.
Package body created.
Grant succeeded.
Package body created.

No errors.
SQL> GRANT EXECUTE ON SYS.UTL_MAIL TO alert;
Grant succeeded.

4. Access Control List (ACL) Configuration:

Have you configured your Access Control List (ACL)? If not, you can do it with the code below. This is required for any Oracle 11g database where you want to send email using Access Control Lists (ACLs) and is a very common error to encounter after upgrading your database from 10g to 11g.

SQL>
BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl => 'UTL_SMTP.xml',
    description => 'Granting privs to required users for UTL_SMTP.xml',
    principal => 'ALERT',
    is_grant => TRUE,
    privilege => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl => 'UTL_SMTP.xml',
    principal => 'ALERT',
    is_grant => TRUE,
    privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl => 'UTL_SMTP.xml',
    host => '170.1.1.1');
END;
/

COMMIT;

SQL>
PL/SQL procedure successfully completed

Commit complete
SQL>

Now I am testing the above:

SQL> exec utl_mail.send('oracle.com','gourang_m@gmail.com','test mail','Hello World',mime_type => 'text; charset=us-ascii');

PL/SQL procedure successfully completed
SQL>
Now it is succeeded.

Hopefully that has worked for you, but it may not…You might now be be getting a different error reporting ORA-24247: network access denied by access control list (ACL) if you have not configured your ACLs for the user running the package. if that’s the case, check out that article. It also covers the error whereby you have refreshed your environment from your production environment and receive the ORA-24247: network access denied by access control list (ACL).

Hopefully one of the suggestions above should help you to resolve the issue.

Note:
Limitations on sending e-mail in Oracle with utl_mail. There are several limitations in utl_mail for sending e-mail messages from inside Oracle. The utl_mail package can only handle a RAW datatype, and hence a maximum value of 32k for a 32k mail message.

Compressed piece HARD block error/ Validating backups / Troubleshoot ORA-19870

Verification of Compressed piece HARD block error in RMAN
Validating backups in RMAN
Troubleshoot ORA-19870

Problem Description :
When I tried in one of my standby database to restore database, I received the below error.

Error from RMAN log:

$ rman target /

RMAN> RUN
{
allocate channel ch1 type disk;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
}2> 3> 4> 5> 6> 

using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=273 device type=DISK

Starting restore at 23-JAN-15

channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00003 to /u01/ORADATA/PROD/undotbs01.dbf
channel ch1: restoring datafile 00004 to /u01/ORADATA/PROD/users01.dbf
...
...
channel ch1: reading from backup piece 

/u02/bkp/prodbkp/backupset/backupset/2015_01_22/o1_mf_nnndf_TAG20150122T050216_bd0fr199_.bkp
channel ch1: ORA-19870: error while restoring backup piece 

/u02/bkp/prodbkp/backupset/backupset/2015_01_22/o1_mf_nnndf_TAG20150122T050216_bd0fr199_.bkp
ORA-19599: block number 167207 is corrupt in backup piece 

/u02/bkp/prodbkp/backupset/backupset/2015_01_22/o1_mf_nnndf_TAG20150122T050216_bd0fr199_.bkp

channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to /u01/ORADATA/PROD/system01.dbf
channel ch1: restoring datafile 00002 to /u01/ORADATA/PROD/sysaux01.dbf
...
...
channel ch1: reading from backup piece 

/u02/bkp/prodbkp/backupset/backupset/2015_01_22/o1_mf_nnndf_TAG20150122T050216_bd0gpk9j_.bkp
channel ch1: ORA-19870: error while restoring backup piece 

/u02/bkp/prodbkp/backupset/backupset/2015_01_22/o1_mf_nnndf_TAG20150122T050216_bd0gpk9j_.bkp
ORA-19599: block number 204403 is corrupt in backup piece 

/u02/bkp/prodbkp/backupset/backupset/2015_01_22/o1_mf_nnndf_TAG20150122T050216_bd0gpk9j_.bkp

failover to previous backup

released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/23/2015 14:30:27
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 62 found to restore
RMAN-06023: no backup or copy of datafile 58 found to restore
RMAN-06023: no backup or copy of datafile 33 found to restore
RMAN-06023: no backup or copy of datafile 14 found to restore
RMAN-06023: no backup or copy of datafile 13 found to restore

RMAN> 

Error From Alert log:


Fri Jan 23 14:22:55 2015 
Full restore complete of datafile 35 /u01/ORADATA/PROD/HRM_INDEX01.dbf. Elapsed time: 0:04:44 
checkpoint is 101068670522 
last deallocation scn is 98368972366 
Fri Jan 23 14:23:11 2015 
Corrupt block 167207 found during reading backup piece, 

file=/u02/bkp/prodbkp/backupset/backupset/2015_01_22/o1_mf_nnndf_TAG20150122T050216_bd0fr199_.bkp, 

corr_type=3 
Reread of blocknum=167207, 
..... 
..... 
file=/u02/bkp/prodbkp/backupset/backupset/2015_01_22/o1_mf_nnndf_TAG20150122T050216_bd0fr199_.bkp, 

found same corrupt data 
Reread of blocknum=167207, 

file=/u02/bkp/prodbkp/backupset/backupset/2015_01_22/o1_mf_nnndf_TAG20150122T050216_bd0fr199_.bkp, 

found same corrupt data 
Compressed piece HARD block error, bit 8 off in block number 00000001 for file 0 
Fri Jan 23 14:25:12 2015 
.... 
.... 


Investigation:
A) Primary database:
Step: 1 - Is there any block corruption in the database?

Action:
SQL> select * from v$database_block_corruption; 

no rows selected 

Step:2 - Is there any failure backups?

RMAN> list failure; 

using target database control file instead of recovery catalog 
no failures found that match specification 
RMAN>

Step:3 - Was it created with the compression option set?

If No, no issues, If yes, then we will see the backup transfer process.

Step:4 - Are all required backup pices validated?

RMAN> list backuppiece 

'/u02/bkp/prodbkp/backupset/backupset/2015_01_22/o1_mf_nnndf_TAG20150122T050216_bd0fr199_.bkp';

using target database control file instead of recovery catalog

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1227    1227    1   1   AVAILABLE   DISK        

/u02/bkp/prodbkp/backupset/backupset/2015_01_22/o1_mf_nnndf_TAG20150122T050216_bd0fr199_.bkp
RMAN>

-- Take the BP Key and run the below command.

RMAN> validate backupset 1308;   -- Just verified with taking a BP key

Starting validate at 23-JAN-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=807 device type=DISK
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece 

/u01/app/oracle/fast_recovery_area/PROD/backupset/2015_01_27/o1_mf_annnn_TAG20150127T053329_bdfog

l4f_.bkp
channel ORA_DISK_1: piece 

handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2015_01_27/o1_mf_annnn_TAG20150127T05332

9_bdfogl4f_.bkp tag=TAG20150127T053329
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished validate at 23-JAN-15

RMAN>

-- It is successfully validated. i.e., there is no issue with backup piece with production.

Note :

You can use the VALIDATE option of the BACKUP command to verify that database files exist and are in the correct locations, and have no physical or logical corruptions that would prevent RMAN from creating backups of them. When performing a BACKUP... VALIDATE, RMAN reads the files to be backed up in their entirety, as it would during a real backup. It does not, however, actually produce any backup sets or image copies.

If the backup validation discovers corrupt blocks, then RMAN updates the $DATABASE_BLOCK_CORRUPTION view with rows describing the corruptions. You can repair corruptions using block media recovery, documented in Oracle Database Backup and Recovery Advanced User's Guide. After a corrupt block is repaired, the row identifying this block is deleted from the view.

For example, you can validate that all database files and archived logs can be backed up by running a command as follows:

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

The RMAN client displays the same output that it would if it were really backing up the files. If RMAN cannot validate the backup of one or more of the files, then it issues an error message. For example, RMAN may show output similar to the following:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 08/29/2002 14:33:47
ORA-19625: error identifying file /oracle/oradata/trgt/arch/archive1_6.dbf
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3


Now verification in standby database:

1) How the backup pieces are transferred?
If SCP, then may be a block corruption in backup piece. Here the rman log and alert log is showing this type of error.  So to irradicate, tranfer the backups and archivelogs using ftp binary mode.

2) Have you validated backups in standby after catalog all backup pieces?
If no. Do it. If yes, then see the status. If it is showing like below, then there is corrupt block in backup piece.

RMAN> list backuppiece '/u02/bkp/prodbkp/backupset/backupset/2015_01_22/o1_mf_nnndf_TAG20150122T050216_bd0fr199_.bkp'; 

using target database control file instead of recovery catalog 

List of Backup Pieces 
BP Key BS Key Pc# Cp# Status Device Type Piece Name 
------- ------- --- --- ----------- ----------- ---------- 
1227 1227 1 1 AVAILABLE DISK /u02/bkp/prodbkp/backupset/backupset/2015_01_22/o1_mf_nnndf_TAG20150122T050216_bd0fr199_.bkp 

RMAN> validate backupset 1227; 

Starting validate at 23-JAN-15 
allocated channel: ORA_DISK_1 
channel ORA_DISK_1: SID=273 device type=DISK 
channel ORA_DISK_1: starting validation of datafile backup set 
channel ORA_DISK_1: reading from backup piece /u02/bkp/prodbkp/backupset/backupset/2015_01_22/o1_mf_nnndf_TAG20150122T050216_bd0fr199_.bkp 
RMAN-00571: =========================================================== 
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== 
RMAN-00571: =========================================================== 
RMAN-03009: failure of validate command on ORA_DISK_1 channel at 01/23/2015 20:42:34 
ORA-19870: error while restoring backup piece /u02/bkp/prodbkp/backupset/backupset/2015_01_22/o1_mf_nnndf_TAG20150122T050216_bd0fr199_.bkp 
ORA-19599: block number 167207 is corrupt in backup piece /u02/bkp/prodbkp/backupset/backupset/2015_01_22/o1_mf_nnndf_TAG20150122T050216_bd0fr199_.bkp 

RMAN>

-- It seems block corruption in backup piece.

Others:
Verification of FTP services:
-- Verify service status , All services

# service --status-all

-- Specific services

# service vsftpd restart
# service vsftpd status
# service vsftpd stop

Example:
# service vsftpd status
vsftpd is stopped

# service vsftpd start
Starting vsftpd for vsftpd:                                [  OK  ]

# service vsftpd status
vsftpd (pid 32674) is running...

Troubleshoot : ORA-02020: too many database links in use

Resolve "ORA-02020: too many database links in use" error 

One of my database is used as remote database for local / distributed databases. For one requirement we have created 'n' number of db links. So many queries fired to use the dblink and at that time we found below error:

"ORA-02020: too many database links in use "

Cause:  The current session has exceeded the INIT.ORA open_links maximum.

To find:
---------
SQL> show parameter open_links;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_links                           integer         4
open_links_per_instance      integer         4


Action: 
-------
Increase the open_links limit, or free up some open links by committing or rolling back the transaction and canceling open cursors that reference remote databases.

To Change:
----------
SQL> alter system set open_links_per_instance=10 scope=spfile;
SQL> alter system set open_links=10 scope=spfile;

and then, bounce the database.

Note:
> The default value is set to 4.
> If it is set to 0, distributed transactions are not allowed.
> If you are expecting your transactions to have a maximum of 3 database links open concurrently, set this parameter to 3 or higher.
> Do not set it too high, it is better for the application to close database links when no longer in use than to change the parameter to a high number.

If you are not sure how many database links are opened up concurrently by your session's database application, you can query v$dblink.

SQL> select in_transaction, count(*) from v$dblink
     group by in_transaction;

IN_     COUNT(*)
---       ----------
YES          1

Extra Coverage:

Close a db link
To explicitly close the database link , use the command below:

SQL> alter session close database link remotedb;  -- remotedb --> a dblink name
Session altered.

OR

SQL> exec DBMS_SESSION.CLOSE_DATABASE_LINK (dblink_name);

Know your open links

Once you have created and made use of a database link in Oracle,
you might want to keep an eye on the number of concurrent open database links
in your database so you can tune the open_links initialization parameter.

Read more here on limiting concurrent open links.

You will need to query v$dblink to see how many links are open in your session:

SQL> select in_transaction, count(*) from v$dblink
     group by in_transaction;

IN_   COUNT(*)
--- ----------
YES          1

Here are some interesting columns and descriptions of v$dblink: 

db_link               Db link name
owner_id            Owner name
logged_on           Is the database link currently logged on?
protocol              Dblink's communications protocol
open_cursors      Are there any cursors open for the db link ?
in_transaction      Is the db link part of a transaction which has not been commited or rolled back yet ?
update_sent        Was there an update on the db link ?

dba_db_links

To gather information on all database links in your database, query dba_db_links.
You will need dba privileges to see this view, the alternatives are user_db_links and all_db_links.

A user who does not have dba privileges can query all_db_links to see which db links are available to him.

SQL> select * from dba_db_links;

OWNER            DB_LINK       HOST       CREATED
----------           ----------         ----------   ----------
GOURANG      REMOTEDB    remotedb   12-May-2014 18:10:01

Some interesting columns: 

owner           User who owns the db link, will state 'PUBLIC' if it is a public database link.
db_link         Db link name.
username      Username that was specified if it was hardcoded during the create statement, null if not        
                     specified during the create statement.
host              The tnsnames alias specified during the create statement.
created         Date and time of link creation.

Thanks
Please feel free to post comments...

Trouble shoot -- enq: TM - contention

Resolve  "enq: TM - contention " issues in Oracle

Recently, during  monitoring production system, I found "enq: TM - contention" oracle event. The blocked sessions were executing simple INSERT & UPDATE statements similar to:

INSERT INTO customer VALUES (:1, :2, :3);

Query to find blocking session details:

select sid,serial#,event, blocking_session, username,status,terminal,program,sql_id
from v$session
where BLOCKING_SESSION  IS NOT NULL;

About "enq: TM - contention" :

These kind of Waits i.e., enq: TM - contention indicate there are un-indexed foreign key constraints. Reviewing the CUSTOMER table, we found a foreign key constraint referencing the PRODUCT table that did not have an associated index. This was also confirmed with development team and verified with DDL. We added the index on the column referencing the PRODUCT table and the problem was solved.

Finding the root cause of the enq: TM - contention wait event

Using the above query to find the blocking sessions, we found the real culprit. Periodically, as the company reviewed its vendor list, they "cleaned up" the CUSTOMER  table several times a week. As a result, rows from the CUSTOMER table were deleted. Those delete statements were then cascading to the PRODUCT table and taking out TM locks on it.

Reproducing a typical problem that leads to this wait

This problem has a simple fix, but I wanted to understand more about why this happens. So I reproduced the same issue to see what happens under the covers. I first created a subset of the tables from this CUSTOMER and loaded them with sample data.

CREATE TABLE customer
( customer_id number(10) not null,
customer_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT customer_pk PRIMARY KEY (customer_id)
);
INSERT INTO customer VALUES (1, 'customer 1', 'Contact 1');
INSERT INTO customer VALUES (2, 'customer 2', 'Contact 2');
COMMIT;

CREATE TABLE product
( product_id number(10) not null,
product_name varchar2(50) not null,
customer_id number(10) not null,
CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customer(customer_id)
ON DELETE CASCADE );
INSERT INTO product VALUES (1, 'Product 1', 1);
INSERT INTO product VALUES (2, 'Product 2', 1);
INSERT INTO product VALUES (3, 'Product 3', 2);
COMMIT;

I then executed statements similar to what we found at this customer:

User 1: DELETE customer WHERE customer_id = 1;
User 2: DELETE customer WHERE customer_id = 2;
User 3: INSERT INTO customer VALUES (5, 'customer 5', 'Contact 5');

Similar to the customer's experience, User 1 and User 2 hung waiting on "enq: TM - contention". Reviewing information from V$SESSION I found the following:

-- Find details of blocking sessions
sql>
SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, l.request, o.object_name, o.object_type
FROM v$lock l, dba_objects o, v$session s
WHERE UPPER(s.username) = UPPER('&User')
AND l.id1 = o.object_id (+)
AND l.sid = s.sid
ORDER BY sid, type;

-- Solution

Following along with the solution we used for our customer, we added an index for the foreign key constraint on the CUSTOMER table back to the PRODUCT table:

sql> CREATE INDEX idx_fk_customer ON product (customer_id);

When we ran the test case again everything worked fine. There were no exclusive locks acquired and hence no hanging. Oracle takes out exclusive locks on the child table, the PRODUCT table in our example, when a foreign key constraint is not indexed.

Sample query to find unindexed foreign key constraints

Now that we know unindexed foreign key constraints can cause severe problems, here is a script that I use to find them for a specific user (this can easily be tailored to search all schemas):

SELECT * FROM (
SELECT c.table_name, cc.column_name, cc.position column_position
FROM   user_constraints c, user_cons_columns cc
WHERE  c.constraint_name = cc.constraint_name
AND    c.constraint_type = 'R'
MINUS
SELECT i.table_name, ic.column_name, ic.column_position
FROM   user_indexes i, user_ind_columns ic
WHERE  i.index_name = ic.index_name
)
ORDER BY table_name, column_position;

Thanks
Please feel free to post comments...

dbms_scheduler jobs are not running automatically- a case study

Issue Description:

Suddenly I received a call few database jobs are not running automatically which are stopped automatically. I verified all those jobs. From the DDL I did not find any issues. All used procedures are also working fine which are not changed since six months. I started my investigation.

Investigation :

Step-1:
Disabled and enabled both the jobs, dropped and re-created both jobs with existing DDL. But issue not resolved.

Step-2:
Tried to call the job manually using below method. But Job is called and runned sucessfully. Then I thoght any J00* and CJQ process may be creating issue. Tried to trace both jobs.

-- Manually calling the job

SQL> exec DBMS_SCHEDULER.run_job('owner.jobname');

-- Started tracing
Note : Give this grant to your user/job owner  --> grant alter session to user
and conect to user/ job owner

SQL> alter session set tracefile_identifier='10046';
SQL> alter session set timed_statistics = true;
SQL> alter session set statistics_level=all;
SQL> alter session set max_dump_file_size = unlimited;
SQL> alter session set events '10046 trace name context forever,level 12';
SQL> exec DBMS_SCHEDULER.run_job('jobname');
SQL> select * from dual;
SQL> exit;

But when verified the tkprof output no symptoms identified.

Step-3:
I doubted the number of job queue slave process. job_queue_processes may be too low (this is the most common problem). The value of job_queue_processes limits the total number of dbms_scheduler and dbms_job jobs that can be running at a given time. To check whether this is the case check the current value of job_queue_processes with:

SQL> select value from v$parameter where name='job_queue_processes';
Then check the number of running jobs
SQL> select count(*) from dba_scheduler_running_jobs;
SQL> select count(*) from dba_jobs_running;

If this is the problem you can increase the parameter job_queue_processes:

SQL> alter system set job_queue_processes=1000;

But, I found counts are very less and job_queue_process value as 1000. Then I doubt sessions/ process may be low. But I found the set value is sufficient enough for this environment.

Click here to increase session/ process parameter value...

Step-4:
I checked any error in alert log. But not found anything related to this job. If any issue it must write to alert log and write to j00* named .trc file.

Step: 5:
If either of the above indicates that the SYSAUX tablespace (where the scheduler stores its logging tables) is full, you can use the dbms_scheduler.purge_log procedure to clear out old log entries.

-- To purge dbms_scheduler logging tables manually
sql> exec dbms_scheduler.purge_log ();

-- To purge dbms_scheduler logging tables automatically ( keep 90 days log)
sql> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','90');

-- Verify the count
sql> select count(*) from user_scheduler_job_run_details;

Step-6:
Database is up and running. Other jobs are running. This job is not associated to any other window job. No session issue. Job is calling manually. Then I doubt on job priority which may cause not to run. Some times this issue may come.

-- How to see job priority

SQL> select owner,job_name,job_type,job_action,
repeat_interval,enabled,job_priority
from dba_scheduler_jobs where owner='owner';

I found the job priority is 3. i.e., very less priority. I thouht the job schould be disabled and enabled with followed by setting priority 1. I followed these steps:
-- if your doing in sys user:
SQL> exec dbms_scheduler.disable(name => 'owner.jobname');
SQL> exec dbms_scheduler.enable(name => 'owner.jobname');
SQL> exec DBMS_SCHEDULER.SET_ATTRIBUTE (name => 'owner.jobname',attribute => 'job_priority',value => 1 );

Then I verified with following query, I found the stopped jobs id running fine.

SQL> select * from dba_scheduler_job_run_details
where owner = 'owner' and trunc(log_date) = trunc(log_date) 
order by log_date desc;

Ohh. Sometimes issue may be resolved with tricky way but we may take it very serious to resolve this. Any way with this case study lots of things are covered.


Another workaround:

Issue may be for Oracle default window issues / job_queue process value.
-- Current job_queue_processes parameter value:
SQL> show parameter job_queue_processes;

NAME                      TYPE        VALUE
------------------------- ----------- ------------
job_queue_processes       integer     1000

-- alter to '0'
SQL> alter system set job_queue_processes=0;
System altered.

-- Againt set to original value/ required value:

SQL> alter system set job_queue_processes=100;


Note: Keeping a larger value may create issue also. Based on env and jobs you can decide.

Some Oracle default values I made False in Env. You choose yours:

SQL> select window_name,active from DBA_SCHEDULER_WINDOWS;

WINDOW_NAME          ACTIVE
------------------------------   -----
MONDAY_WINDOW        FALSE
TUESDAY_WINDOW       FALSE
WEDNESDAY_WINDOW     FALSE
THURSDAY_WINDOW      FALSE
FRIDAY_WINDOW        FALSE
SATURDAY_WINDOW      FALSE
SUNDAY_WINDOW        FALSE
WEEKNIGHT_WINDOW     FALSE
WEEKEND_WINDOW       TRUE

9 rows selected.

Note- I found WEEKNIGHT_WINDOW job was still running(active). I stopped like below, then all jobs started running automatically. Run below query to find some info about your jobs:

select * from DBA_SCHEDULER_RUNNING_JOBS;
select * from dba_scheduler_global_attribute;
select * from dba_scheduler_windows;
select * from dba_scheduler_jobs;

SQL> EXECUTE DBMS_SCHEDULER.CLOSE_WINDOW ('WEEKNIGHT_WINDOW');

Note:
Some times a miss-configuration exist in your scheduler windows settings that may lead the WEEKNIGHT_WINDOW kept ACTIVE and make the scheduler hang, the WEEKNIGHT_WINDOW must not be active as well as WEEKEND_WINDOW in 11g because they must not be used in 11g as there is a window for each week day, so please run the below to disable these windows:

exec DBMS_SCHEDULER.DISABLE ('WEEKNIGHT_WINDOW, WEEKEND_WINDOW'); 

Thanks you. Please feel free to ask questions via this blog.
Have a nice day...