Validate Oracle XDB

XDB.PATH_VIEW is INVALID

In few cases Oracle XDB becomes Invalid and if it needs to be validated again without reinstalling the below work around might work.

Error:

XDB.PATH_VIEW and XDB.XDB_PV_TRIG are in INVALID state 

Fix:

To validate XDB, upgrade table XDB.XDB$H_LINK, then recompile the invalid objects.

Action Plan:

connect / as sysdba

alter table XDB.XDB$H_LINK upgrade;

alter view XDB.PATH_VIEW compile;

alter trigger XDB.XDB_PV_TRIG compile;

Check the XDB Component:

select comp_name,version,Version_Full,status from dba_registry where comp_id = 'XDB';

The XDB should be validated now. If this does not work then you need to reinstall the XDB and befor doing that please consult Oracle, as it might corrupt some dependent XML attributes in other tables. 

You can follow XDB Reinstall to reinstall the XDB.

OR,

Alternative Method for Re Installing:

1. Export any custom tables that has XML type, using Data Pump:

2. Deinstall and reinstall XDB

spool xdb_reinstall.log

set echo on;

connect / as sysdba

shutdown immediate

startup

@?/rdbms/admin/prvtnoqm.plb

@?/rdbms/admin/catproc.sql

@?/rdbms/admin/utlrp.sql

set pagesize 1000

set linesize 150

col comp_name format a36

col version format a12

col status format a8

col owner format a12

col object_name format a35

col object_type format a20

col name format a35

select comp_name, version, status from dba_registry order by status, comp_name;

select owner, object_name, object_type, status 

from dba_objects

where status = 'INVALID'

and owner in ('SYS', 'XDB')

order by owner, object_name;

select owner, name, type, text

from dba_errors

where owner in ('SYS', 'XDB')

order by owner, name;

spool off;

3. Drop the tables in step 1 and import them from the dump file.

Best Wishes!!

 

ORA-03113 end-of-file on communication channel Error in Oracle

How to Fix ORA-03113: end-of-file on communication channel Error in Oracle Database

This is a generic error and there can be multiple reasons to it. to find the actual reason, you need to consider the alert log and additional infrastructure components as well.

In my case, while doing some maintenance, during DB shutdown, the server was crashed and it kept the database in inconsistency. The background processes got cleared but, the running transactions were still in progress. which was preventing the DB to be up. So after drilling down it was some unarchived logfiles causing this error. I did the following to fix the issue. 

Note: Please do not perform this in PROD without consulting Oracle support, as this might cause some data loss for the uncommitted transactions. 

Steps I followed:

Connect as sysdba:

sqlplus / as sysdba

Startup the database in Mount state:

startup nomount

alter database mount;

Clear the unarchived redo logs:

alter database clear unarchived logfile group 1;

alter database clear unarchived logfile group 2;

alter database clear unarchived logfile group 3;

Keep adding the logfile groups if you have more groups

Now, restart the database:

shutdown immediate

startup

Check the mode on database:

select name, open_mode from v$database;

Hope this helps.

Best Wishes!!

Increase parallel worker in expdp/impdp runtime

How to increase the value if the job is running for long time?

select OWNER_NAME,JOB_NAME,STATE from  DBA_DATAPUMP_JOBS;

make a note of the job name, which is in executing state and then run the below command

$expdp system/********** attach=SYS_EXPORT_FULL_01

OR

$impdp system/********** attach=SYS_IMPORT_FULL_01

This will open the interactive session for the datapump utility and you can execute the below commands:

> status

> parallel=4 (whichever value you have determined to provide)

And now run status again and it will show that 4 workers have been assigned to the job.

Hope this helps. Best Wishes!!

Reinstall XDB Component in Oracle

How to install/reinstall/deinstall XDB in oracle:

In this article I will cover one of the important issue faced after sustainment patching. It is sometime observed after patching the XDB Component gets Invalid and recompiling does not fix it.

Known Issues:

Few known errors like few XDB objects are Invalid under DBA_OBJECTS and in DBA_REGISTRY XDB is showing as Invalid. 

Query to check:

DBA Registry:

set pages 300

set lines 400

col COMP_ID for a20

col Comp_Name for a40

col Version for a10

col Version_Full for a20

col Status for a10

select COMP_ID,

  COMP_NAME,

  VERSION,

  VERSION_FULL,

  STATUS

from dba_registry;

DBA Objects:

col owner for a20

select owner,

  count(*)

from dba_objects

where status = 'INVALID'

group by owner;

As per Oracle, deinstall and reinstall it, but that will impact if there is any data that is using the XML. So, It is aways a good idea to reinstall it. 

Reinstallation:

(this is applicable for Databases version greater than 10g)

Command:

catqm.sql xdb_password xdb_ts_name temp_ts_name secure_file_for_repos

Where:

xdb_password is the password

xdb_ts_name is the tablespace to use for Oracle XML DB

temp_ts_name is the temporary tablespace

secure_file_for_repo is YES or NO (uppercase), YES meaning to use SecureFile LOB storage for Oracle XML DB Repository

For example:

catqm.sql <XDB user password> SYSAUX TEMP YES

Alternate Method:

In few cases I have seen issues with catqm.sql and not fixing few invalid objects, so if you are ok, you can always run catproc.sql. Please donot run this in prod without proper backup. 

spool reinstall_xdb.log

set echo on;

connect / as sysdba

shutdown immediate;

startup

@?/rdbms/admin/prvtnoqm.plb

drop trigger SYS.XDB_PI_TRIG;

alter profile default limit PASSWORD_VERIFY_FUNCTION null;

@?/rdbms/admin/catproc.sql

@?/rdbms/admin/utlrp.sql

spool off;

This will fix the XDB component and the status should be Valid now. 

If reinstalling doesnot fix it, you can completely deinstall and install the component but doing so might have some impact. so it is recommended to reach out to Oracle SR for confirmation. I will be outlining the steps:

Deinstall/Uninstall XDB:

--- The catnoqm.sql script deinstall/Uninstall the XDB Component

spool deinstall_xdb.log

set echo on;

connect / as sysdba

shutdown immediate;

startup

@?/rdbms/admin/catnoqm.sql

spool off;

shutdown immediate;

startup;

@?/rdbms/admin/utlrp.sql

Install XDB:

Pease follow the same procedure as earlier installation methods.

Verify Installation:

spool verify_xdb.log

set echo on;

connect / as sysdba

set pagesize 1000

col comp_name format a36

col version format a12

col status format a8

col owner format a12

col object_name format a35

col name format a25

-- Check status of XDB

select comp_name,

  version,

  Version_Full,

  status

from dba_registry

where comp_id = 'XDB';

-- Check for invalid objects in SYS and XDB schema

-- This is a common issue after installing XDB

select owner,

  object_name,

  object_type,

  status

from dba_objects

where status = 'INVALID'

  and owner in ('SYS', 'XDB');

spool off;

Hope this helps. 

Reference: 

How to Reinstall XDB on 12c and above? (Doc ID 2403285.1)

Primary Note for Oracle XML Database (XDB) Install / Deinstall (Doc ID 1292089.1)

 

Oracle DB NLS Characterset Conversion

How to Convert NLS Character set in Oracle:

In this article, I will be converting character set of a 19c oracle database from WE8MSWIN1252 to AL32UTF8

Steps to be followed:

Prerequisites:

Run the below query and keep record of the existing information.

select value from NLS_DATABASE_PARAMETERS where Parameter='NLS_CHARACTERSET';

SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';

select * from v$nls_parameters where parameter like '%CHARACTERSET';

select userenv('language') from dual;

For my example this is the output:

SQL> select value from NLS_DATABASE_PARAMETERS where Parameter='NLS_CHARACTERSET';

VALUE

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

WE8MSWIN1252

SQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';

VALUE

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

AL16UTF16

SQL> select * from v$nls_parameters where parameter like '%CHARACTERSET';

PARAMETER

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

VALUE                                                                CON_ID

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

NLS_CHARACTERSET

WE8MSWIN1252                                                              0

NLS_NCHAR_CHARACTERSET

AL16UTF16                                                                 0

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')

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

AMERICAN_AMERICA.WE8MSWIN1252

Now run the below query to see the datatype it is using:

select distinct(nls_charset_name(charsetid)) CHARACTERSET,

decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),

decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),

decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),

decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN

from sys.col$

where charsetform in (1,2)

and type# in (1, 9, 96, 112)

order by CHARACTERSET;

Example output:

SQL> select distinct(nls_charset_name(charsetid)) CHARACTERSET,

  2  decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),

  3  9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),

  4  96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),

112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN

from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112) order by CHARACTERSET;  5    6

CHARACTERSET                             TYPES_USED_IN

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

AL16UTF16                                NCHAR

AL16UTF16                                NCLOB

AL16UTF16                                NVARCHAR2

WE8MSWIN1252                             CHAR

WE8MSWIN1252                             CLOB

WE8MSWIN1252                             VARCHAR2

6 rows selected.

Now check the Database Size:

col "Database Size" format a20

col "Free space" format a20

col "Used space" format a20

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"

, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -

round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"

, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"

from (select bytes

from v$datafile

union all

select bytes

from v$tempfile

union all

select bytes

from v$log) used

, (select sum(bytes) as p

from dba_free_space) free

group by free.p

/

Backup:

Take a full backup of the database. Either through RMAN or Expdp, depending on the db size and your database setup.

For RMAN: follow this link

For EXPDP : follow this link

Once the backup is done, check there are ample archive space available. 

If you would like, you can create a restore point for easy revert back of the change.

create restore point pre_charset guarantee flashback database;

Check the restore point is created or not.

set lines 400

set pages 300

col name for a20

col time for a40

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,

GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE

FROM V$RESTORE_POINT

where GUARANTEE_FLASHBACK_DATABASE='YES';

check Invalid count:

select count(*) from dba_objects where status='INVALID';

Invalid object count schema wise:

set pages 300

set lines 300

col owner for a30

select owner, count(*) from dba_objects where status='INVALID' group by owner;

If there are numerous invalid count, run utlrp.sql to recompile the invalid objects:

SQL>@?/rdbms/admin/utlrp.sql

Change the character set:

Now follow the below steps to convert the characterset:

--Proceed to alter the database

sqlplus / as sysdba

shutdown immediate

startup Restrict

SQL> sho parameter  job_queue_processes

NAME                                 TYPE        VALUE

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

job_queue_processes                  integer     160

SQL> show parameter aq_tm_processes;

NAME                                 TYPE        VALUE

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

aq_tm_processes                      integer     1

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

System altered.

ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

ALTER SYSTEM DISABLE RESTRICTED SESSION;

-- After changing the character set bounce the database

shutdown immediate

startup

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=160;

ALTER SYSTEM SET AQ_TM_PROCESSES=1;

Now check for the character set again to validate the change is successful:

Query:

select distinct(nls_charset_name(charsetid)) CHARACTERSET,

decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),

decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),

decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),

decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN

from sys.col$

where charsetform in (1,2)

and type# in (1, 9, 96, 112)

order by CHARACTERSET;

Example:

SQL> select distinct(nls_charset_name(charsetid)) CHARACTERSET,

  2  decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),

  3  9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),

  4  96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),

112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN

  5    6  from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112) order by CHARACTERSET;

CHARACTERSET                             TYPES_USED_IN

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

AL16UTF16                                NCHAR

AL16UTF16                                NCLOB

AL16UTF16                                NVARCHAR2

AL32UTF8                                 CHAR

AL32UTF8                                 CLOB

AL32UTF8                                 VARCHAR2

6 rows selected.

check invalid Count:

select count(*) from dba_objects where status='INVALID';

Invalid Count schema wise:

set pages 300

set lines 300

col owner for a30

select owner, count(*) from dba_objects where status='INVALID' group by owner;

And it completes the characterset changes. for few cases it might not work, in that case you can use Oracle Data Migration Assistant for Unicode (DMU), to learn more about how to use DMU follow the below link.

Rollback:

In case you want to revert back the change, you can revert to the restore point you just created before making the changes.

-- Flashback the database to the restore point

shutdown immediate;

startup mount;

flashback database to restore point pre_charset;

alter database open resetlogs;

Note: This is done in Development Instance, before running it in production, please make sure you validated all the data. 

Best Wishes!!

Oracle Database timezone update

How to update Oracle Database Time Zone:

First make a note of the existing timezone with the below query.

select SYSTIMESTAMP, current_TIMESTAMP from dual;

select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;

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

To change timezone:(OS Level)

$tzselect

$timedatectl set-timezone <new_time_zone>

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

Steps to be followed to change DBTIMEZONE:

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

SQL> SELECT SESSIONTIMEZONE, DBTIMEZONE FROM DUAL;

SESSIONTIMEZONE         DBTIME

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

-06:00                  -07:00

SQL> ALTER DATABASE SET TIME_ZONE='Canada/Mountain';

Database altered.

SQL> ALTER DATABASE SET TIME_ZONE='-06:00';

Database altered.

SQL> shutdown immediate;

SQL> Startup;

SQL> SELECT SESSIONTIMEZONE, DBTIMEZONE FROM DUAL;

SESSIONTIMEZONE         DBTIME

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

-06:00                  -06:00

Oracle Data guard check configuration

 

How To check any Oracle Database is configured with data guard or not?

There are few ways to validate it within the database and at the OS level. The easiest way to check would be running a SQL query which is oracle built-in function and validate the status.

set serveroutput on

declare

  feature_boolean number;

  aux_count number;

  feature_info clob;

begin

  dbms_feature_data_guard(feature_boolean, aux_count, feature_info);

  dbms_output.put_line(feature_boolean);

  dbms_output.put_line(feature_info);

end;

/

Output will be like below:

If DataGuard is not configured:

0

Data Guard usage not detected

If DataGuard is configured:

1

Number of standbys: 1, Number of Cascading databases: 0, Number of Terminal

databases: 1, Redo Apply used: TRUE, SQL Apply used: FALSE, Far Sync Instance

used: FALSE, Snapshot Standby used: FALSE, Broker used: TRUE, Protection mode:

MAXIMUM PERFORMANCE, Log transports used: LGWR ASYNC, Fast Sync used: FALSE,

Fast-Start Failover used: FALSE, Real-Time Apply used: TRUE, Compression used:

FALSE, Flashback used: FALSE, Recovery Appliance used: FALSE

Alternate method:

create a session dgmgrl, connect to the database, in this example db1. 

DGMGRL> show configuration

Configuration - db1

  Protection Mode: MaxPerformance

  Members:

  db1 - Primary database

    db1_stby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:

SUCCESS   (status updated 37 seconds ago)

 

Hope this helps, Thanks!!

OPatch Debug log

How to enable additional logging on OPATCH 

Sometime while patching we need to look at some of the additional details which is not on the opatch logs. Specially if any patch is failing/ any patch is stuck for very long period of time. In such scenarios, enabling a few additional logging would be a great help to troubleshoot the issue. 

Commands:

Before starting the session, export this two variable on the unix session and then check the opatch logs.

If you are running ASM and you can check some additional debugging on the crs logs as well. 

export OPATCH_DEBUG=true

export JAVA_VM_OPTION="$JAVA_VM_OPTION -Doracle.installer.logLevel=FINEST"

After setting this variables, now run the patching session and it will keep putting more steps on the log file. 

Happy patching!!

ASM Oracle permission issue

 

ORA-12547: TNS:lost contact

This error is very generic and below are the symptoms to it. If you find any of these errors in the log it is probably the permission issue on oracle binary on Oracle Home under grid user. 

RMAN Crosschecks error:

RMAN-03002: failure of backup command at 03/24/2025 13:01:37

RMAN-06059: expected archived log not found, loss of archived log compromises recoverability

ORA-19625: error identifying file <datafile>

ORA-17503: ksfdopn:2 Failed to open file <filename>

ORA-12547: TNS:lost contact

ORA-12547: TNS:lost contact

ASM alert log showing error:

*** 2025-03-24T13:28:11.414105-06:00

*** MODULE NAME:(emagent_SQL_osm_instance) 2025-03-24T13:28:11.414215-06:00

*** ACTION NAME:(FileGroup_Usage) 2025-03-24T13:28:11.414234-06:00

ERROR: submiting READ on /dev/rdsk/<diskname> failed due to kgfknm error

crsctl status:

crsctl status res -t

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4000: Command Status failed, or completed with errors.

Oracle Alert log:

Fatal NI connect error 12547, connecting to:

 <JDBC>

  VERSION INFORMATION:

        TNS for <OS>: Version 19.0.0.0.0 - Production

        Oracle Bequeath NT Protocol Adapter for Solaris: Version 19.0.0.0.0 - Production

  Version 19.25.0.0.0

  Time: 24-MAR-2025 14:46:06

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12547

TNS-12547: TNS:lost contact

    ns secondary err code: 12560

    nt main err code: 517

Solution:

For all these error, below permission change will fix the issue.

login as grid user, and go to GRID_HOME/bin location

chmod 6751 oracle

ls -lrt oracle

-rwsr-s--x   1 grid oinstall 563794648 Mar 26 20:13 oracle

Oracle Bug reference:

But this was is not applicable for our version. 

Bug details: Bug 36461761 - ASM numerous trace files generated with 'error: submiting read on /dev/rdsk/nnn 'failed due to kgfknm error' messages (Doc ID 36461761.8)

Best Wishes!!