Showing posts with label ORA Errors. Show all posts

Sunday, February 5, 2023

PLS-00302:component

PLS-00302: component 'UNLOCK_TABLE_STATS' must be declared

ORA-06550: line 1, column 7:

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

SQL> exec dbms_stats.unlock_table_stats( ownname => 'HR' , tabname => 'EMPLOYEES');
BEGIN dbms_stats.unlock_table_stats( ownname => 'HR' , tabname => 'EMPLOYEES'); END;

*
ERROR at line 1:
ORA-06550: line 1, column 18:
PLS-00302: component 'UNLOCK_TABLE_STATS' must be declared
ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

Solution:

exec sys.DBMS_STATS.UNLOCK_TABLE_STATS( ownname => 'HR' , tabname => 'EMPLOYEES');

Your comments are most valuable and it would help me to come up with better posts.

Thursday, December 2, 2021

ORA-19815: WARNING: db_recovery_file_dest_size

ORA-19815

Errors in file /oracle12.2/diag/rdbms/KARDB/KARDB/trace/KARDB_m000_25197.trc:

ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472000 bytes is 92.44% used, and has 243602825216 remaining bytes available.

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

col NAME format a30

select substr(name, 1, 30) name, round(space_limit/1024/1024/1024, 1) quota_gb, round(space_used/1024/1024/1024, 1) used_gb, round(space_reclaimable/1024/1024/1024, 1) reclaimable_gb, number_of_files files from v$recovery_file_dest;

NAME QUOTA_GB USED_GB RECLAIMABLE_GB FILES
------------------------------ ---------- ---------- -------------- ----------

/fast_recovery_area 3000 2068 0 1338

SELECT FILE_TYPE "Type", PERCENT_SPACE_USED "% Used", PERCENT_SPACE_RECLAIMABLE "% Reclaim", NUMBER_OF_FILES "# Files" FROM V$FLASH_RECOVERY_AREA_USAGE;

Solution;

rman target /
RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;

or via script:

more delete_archivelog_KARDB.sh

#!/bin/bash
date '+%Y-%m-%d %H:%M:%S'

. setdb_home KARDB
export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'
rman target / <<EOF
crosscheck archivelog all;
delete force noprompt archivelog all completed before 'sysdate - 5';
#delete force noprompt expired archivelog all;
EOF
date '+%Y-%m-%d %H:%M:%S'
cd /fast_recovery_area/KARDB/archivelog

find /fast_recovery_area/KARDB/archivelog/* -type d -empty -print -exec rm -r {} \;

As cronJob:

35 * * * * /script/delete_archivelog_KARDB.ksh > /script/log/delete_archivelog_KARDB.log.$(date +\%Y-\%m-\%d--\%H-\%M) 2>&1

Monday, October 4, 2021

ORA-65500: could not modify DB_UNIQUE_NAME

ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

ERROR at line 1:

ORA-32017: failure in updating SPFILE

ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

SQL> alter system set db_unique_name='TSTDB01' scope=spfile sid='*';
alter system set db_unique_name='TSTDB01' scope=spfile sid='*'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE

ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

The followings workaround works.

srvctl config database -d TSTDB

Database unique name: TSTDB
Database name: TSTDB
Oracle home: /oracle12.2/product/12.2.0.1/db
Oracle user: oracle
Spfile: /oracle12.2/product/12.2.0.1/db/dbs/spfileTSTDB01.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA01
Services:
OSDBA group: dba
OSOPER group: dba

Database instance: TSTDB01

Stop DB and remove db from CRS.

srvctl stop database -d TSTDB

srvctl remove database -d TSTDB

Remove the database TSTDB? (y/[n]) y

sqlplus / as sysdba

startup

alter system set db_unique_name=TSTDB01 scope=spfile sid='*';

System altered.

It is required to restart DB restart.

shutdown immediate;

startup

Now looks good.

SQL> show parameter db_name;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string TSTDB
SQL> show parameter db_unique_name;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------

db_unique_name string TSTDB01

Re register the instace again into CRS:

srvctl add database -d TSTDB01 -n TSTDB -o $ORACLE_HOME -p $ORACLE_HOME/dbs/spfileTSTDB01.ora

srvctl config database -db TSTDB01

Database unique name: TSTDB01
Database name: TSTDB
Oracle home: /oracle12.2/product/12.2.0.1/db
Oracle user: oracle
Spfile: /oracle12.2/product/12.2.0.1/db/dbs/spfileTSTDB01.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups:
Services:
OSDBA group: dba
OSOPER group: dba

Database instance: TSTDB01

Wednesday, April 7, 2021

Error 12514 received logging

Error 12514 received logging on to the standby

Fatal NI connect error 12514, connecting to

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

Fatal NI connect error 12514, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=kardb-sbt.th)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=KARDB01)(CID=(PROGRAM=oracle)(HOST=kardb-prd.th)(USER=oracle))))

VERSION INFORMATION:

TNS for Linux: Version 12.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 12.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.2.0.1.0 - Production
Time: 04-MAR-2021 08:48:42
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
2021-03-04T08:48:42.998683+01:00

Error 12514 received logging on to the standby

Solution:

check the connections as follow:

==> Primary: sqlplus sys@kardb01 as sysdba

tnsping kardb01

NOTE: in my case everything works fine but somehow got the above error - Strange behavior.

Add the entry in the sqlnet.ora as follow.

NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT, HOSTNAME)

Everything works fine at this point.

Friday, February 12, 2021

CRS-2613: Could not find resource ora.asm

Error Message:

CRS-2613 Solution

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

srvctl start asm

PRCR-1001 : Resource ora.asm does not exist

crsctl status resource ora.asm

CRS-2613: Could not find resource 'ora.asm'.

Solution:

It is missing detail in the server Control (srvctl) Utility. We need to add as follow.

srvctl add asm -p '/oracle/product/12.2.0.1/grid/dbs/init+ASM.ora'

srvctl start asm

PRCR-1001 : Resource does not exist

Error Message:

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

srvctl start database -db KARDB

PRCD-1120 : The resource for database KARDB could not be found.

PRCR-1001 : Resource ora.kardb.db does not exist

Solution:

It is missing detail in the server Control (srvctl) Utility. We need to add as follow.

srvctl add database -db KARDB -o $ORACLE_HOME

srvctl start database -db KARDB

srvctl status database -db KARDB

If you get the same error due to start asm:

srvctl start asm

PRCR-1001 : Resource ora.asm does not exist

srvctl add asm -p '$ORACLE_HOME/dbs/spfile+ASM.ora'

srvctl start asm

srvctl status asm

ASM is running on ....

In generally init+ASM.ora seems to as follow:

+ASM.__oracle_base='/oracle'
*.asm_diskstring='/dev/sd*'
*.asm_power_limit=1
*.large_pool_size=12M

*.remote_login_passwordfile='EXCLUSIVE'

ORA-01078: failure in processing system parameters

Error Message:

ORA-01078: failure in processing

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

srvctl start database -db KARDB

PRCR-1079 : Failed to start resource ora.kardb.db

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

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/oracle/product/12.2.0.1/db/dbs/initKARDB.ora'

. For details refer to "(:CLSN00107:)" in "/oracle/diag/crs/vm-kardb-tst/crs/trace/ohasd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.kardb.db' on vm-kardb-tst 'vm-kardb-tst' failed

Solution:

It is missing detail in the server Control (srvctl) Utility. We need to add as follow.

create spfile from pfile='/oracle/product/12.2.0.1/db/dbs/initKARDB.ora';

srvctl start database -db KARDB

srvctl status database -db KARDB

Database is running.

Saturday, March 28, 2020

ORA-16053: DB_UNIQUE_NAME is not in the Data Guard Configuration

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=EDUDB01';

alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=EDUDB01'

ERROR at line 1:

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

ORA-16053: DB_UNIQUE_NAME EDUDB01 is not in the Data Guard Configuration

SOLUTION:

srvctl remove database -d edudb

srvctl add database -d EDUDB01 -oraclehome /oracle19c/product/19.6.0.0/db

srvctl modify database -d EDUDB01 -oraclehome /oracle19c/product/19.6.0.0/db -p +DATA/EDUDB/PARAMETERFILE/spfile.270.1034096743

srvctl config database -d EDUDB01

alter system set db_unique_name=xxx scope=spfile;

Done:

ORA-16055: FAL request rejected

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

On standby DB:

rman target /

show all;

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

RMAN> exit

On Primary:

If you have you already enabled log_archive_dest_state_2 enabled or defer do again as follow.

SQL> ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;

SQL> alter system switch logfile;

SQL> ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

Or Check following parameter

show parameter log_archive_dest_2;

The SERVICE name must be correct and same as it is defined in your tnsnames.ora file.

Check the alert Log file. The error should be resolved.

Friday, January 24, 2020

ORA-15100: invalid or missing diskgroup name

ORA-15100:

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

SQL> alter diskgroup DATE mount;

alter diskgroup DATE mount

ERROR at line 1:

ORA-15100: invalid or missing diskgroup name

SOLUTION:

Login into ASM and check the

select name,state from v$asm_diskgroup;

NAME STATE

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

DATE DISMOUNTED

Reason:

Because the diskgroup name DATE interpreted as date. This is the reason that the diskgroup with name DATE cannot start.

You need to start it manually as follow by using apostrophe.

alter diskgroup "DATE" mount;

NOTE: Always avoid to you such type of disk group Name.

ORA-32004: obsolete or deprecated parameter

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORA-32004:

You can fix the above error as follow.

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

Try to find which parameter is deprecated.

col NAME format a30;

col VALUE format a10;

select p.name,p.value from v$parameter p, v$spparameter s where s.name=p.name and p.isdeprecated='TRUE' and s.isspecified='TRUE';

NAME VALUE

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

sec_case_sensitive_logon FALSE

NOTE: This parameter is deprecated in the 12c Version so no need anymore.

Now reset the parameter:

alter system reset sec_case_sensitive_logon scope=spfile;

The Error should be gone:

Tuesday, December 3, 2019

RMAN-06025: no backup of archived log for thread 1

If you see such type of errors:

RMAN-06025: no backup of archived log for thread 1 with sequence 148635 and starting SCN of 57325629377 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 148634 and starting SCN of 57325618994 found to restore

--
PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

SOLUTION:

Create a backup of the missing Sequence.

RMAN> run

{

ALLOCATE CHANNEL disk07 DEVICE TYPE DISK MAXPIECESIZE 500M FORMAT '/oracledb/backup/ARC_%d.%p.%s.%T.%t';

BACKUP ARCHIVELOG FROM SEQUENCE 148506 UNTIL SEQUENCE 148635;

}

Restore it as follow:

RMAN> run

{

RESTORE ARCHIVELOG FROM SEQUENCE 148506 UNTIL SEQUENCE 148635;

}

Friday, November 29, 2019

CRS-2415: Resource 'ora.asm' cannot be registered

PRCR-1071 : Failed to register or update resource ora.asm

ERROR: CRS-2415, PRCR-1071, PRCA-1003

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

During the Upgrade of GRID_HOME to the new version in my case from 11.2.0.3 to 12.2.0.1 getting following error.

PRCA-1003 : Failed to create ASM asm resource ora.asm

PRCR-1071 : Failed to register or update resource ora.asm

CRS-2415: Resource 'ora.asm' cannot be registered because its owner 'root' is not the same as the Oracle Restart user 'oracle'.

Let us fix the Issue.

This is due to Bug 25183818

The Patch needs to be applied to 12.2 Home.

(Doc ID 2246888.1)

We need to install following Patch on new 12c Grid_HOME.

p25183818_12201190716OCWJUL2019RU_Linux-x86-64.zip

As root user.

export PATH=$PATH:/apps/oracle/product/12.2.0.1/grid_12c/OPatch

/apps/oracle/product/12.2.0.1/grid_12c/OPatch/opatchauto apply /download/Bug_25183818/25183818 -oh /apps/oracle/product/12.2.0.1/grid_12c

You see the failed error at the end. You can ignore it. However, this is fine because "Binary patch applied successfully"

Start applying binary patch on home /apps/oracle/product/12.2.0.1/grid_12c

Binary patch applied successfully on home /apps/oracle/product/12.2.0.1/grid_12c

Starting CRS service on home /apps/oracle/product/12.2.0.1/grid_12c

Failed to start CRS service on home /apps/oracle/product/12.2.0.1/grid_12c

...

OPatchAuto failed.

opatchauto failed with error code 42

Tags

Sunday, February 5, 2023

PLS-00302: component 'UNLOCK_TABLE_STATS' must be declared