Archive for the ‘Oracle Queries’ Category

PLS-00201: identifier UTL MAIL.SEND must be declared

January 9th, 2022, posted in Oracle Queries
Share

PLS-00201: identifier ‘UTL_MAIL.SEND’ must be declared

During execution of the package use UTL Mail package for send the mail may give the error PLS-00201. It may be caused due to in configured of UTL Mail package in the database. You may configure the UTL Mail package with following script:

Error:

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'UTL_MAIL.SEND' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Solution:

1. Set the Oracle Home Environment Variable

Set ORACLE_HOME=D:\Oracle\12.1.0\dbhome_1
set PATH=%ORACLE_HOME%\bin;%PATH%

2. Connect with sqlplus / as sysdba

@?\rdbms\admin\utlmail.sql;
@?\rdbms\admin\prvtmail.plb;

3. Set up the Parameter SMTP server.

ALTER SYSTEM SET smtp_out_server='address:25' SCOPE=BOTH;

4. Grant the user with required permission.

GRANT execute ON utl_mail TO NEWUSER;

Share

ORA-38760: This database instance failed to turn on flashback database

October 24th, 2021, posted in Oracle Queries
Share
Error :

Starting recover at 24-APR-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1072 device type=DISK

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/24/2018 14:54:07
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database

Cause :

Database was in flashback mode.

Solution :

trun off flashback mode and rerun recovery.

SQL> alter database flashback off;
Database altered.

SQL>

RMAN> run
{
set until sequence 576;
recover database;
}2> 3> 4> 5>

executing command: SET until clause

Starting recover at 24-APR-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1072 device type=DISK

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=573
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=574
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=575
channel ORA_DISK_1: reading from backup piece /d02/dbbackup/RMAN_backup1/RMAN_BKPMon_23Apr18/ar_974238549_2165_1
channel ORA_DISK_1: errors found reading piece handle=/d02/dbbackup/RMAN_backup1/RMAN_BKPMon_23Apr18/ar_974238549_2165_1
channel ORA_DISK_1: failover to piece handle=/d01/oracln/backup/RMAN_BKPMon_23Apr18/ar_974238549_2165_1 tag=TAG20180423T211704
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
archived log file name=/d01/oracln/data/archive1_573_970862557.dbf thread=1 sequence=573
archived log file name=/d01/oracln/data/archive1_574_970862557.dbf thread=1 sequence=574
archived log file name=/d01/oracln/data/archive1_575_970862557.dbf thread=1 sequence=575
media recovery complete, elapsed time: 00:00:27
Finished recover at 24-APR-18

RMAN>
Recovery Manager complete.
Share

Unable to archive thread 1 sequence

June 30th, 2021, posted in Oracle Queries
Share

From alert log it was noticed that archive process is leaving some messages as below.

Sun Nov 11 09:41:34 2018
ARC5: Evaluating archive log 20 thread 1 sequence 59168
ARC5: Unable to archive thread 1 sequence 59168
Log actively being archived by another process
ARC5: Evaluating archive log 22 thread 1 sequence 59175
ARC5: Unable to archive thread 1 sequence 59175
Log actively being archived by another process

Cause :

It is due to having more than one archiver.

Solution :

If automatic archiving is turned ‘ON’ and LOG_ARCHIVE_MAX_PROCESSES is set to more than one, then all of the archiver processes will try to archive this logfile, however they will not be able to aquire the lock ‘kcrrlt’, to protect multiple arch processes from archiving the same logfile.

The failing process process will write to the trace/alert message that it was unable to archive the logfile.

It doesn’t mean that the log mentioned is not archived; it is successfully archived by some other process. It only means that the log was not archived the first time it was tried.

If you go a bit down you will see that the log had been archived.

Share

ORA-39083: Object type PROCACT_SYSTEM failed to create with error

June 20th, 2021, posted in Oracle, Oracle EBS Application, Oracle Queries
Share

Error

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
>>> Cannot set an SCN larger than the current SCN. If a Streams Capture configuration was imported then the Apply that processes the captured messages needs to be dropp
ed and recreated. See My Oracle Support article number 1380295.1.
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:

ORA-20000: Incompatible version of Workspace Manager Installed

Cause

Version of the Oracle Workspace Manager (OWM) in the source and target are not same.

Solution

Ignore the following errors, if IMPDP is importing all other components and data successfully:

To move the workspaces from the 11.2.0.3 or 11.2.0.4 SOURCE db, the FULL db export must be generated with the parameter “VERSION=12”.

If the SOURCE database is older than 11.2.0.3, e.g. 11.2.0.2 or 11.1.0.7, the database will first need to be upgraded to 11.2.0.3 or 11.2.0.4

Share