Showing posts with label DBA.Admin. Show all posts
Showing posts with label DBA.Admin. Show all posts

Thursday, June 17, 2021

Java Stored Procedure failed with java.lang.OutOfMemoryError

Oracle database Jave stored procedure failed with "java.lang.OutOfMemoryError", the error stack looks like
ORA-29532: Java call terminated by uncaught Java exception: java.lang.OutOfMemoryError
ORA-06512: at "USER01.MYJAVAPROC", line 28
ORA-06512: at line 7

This error is thrown when there is insufficient space to allocate an object in the Java heap. In this case, The Java garbage collector cannot make space available to accommodate a new object, and the heap cannot be expanded further.  

As we know, Java stored procedure is impleted in Java, so that the excution of the procedure also follows the same rule as normal Java application doing. The only difference is that the procedure is running on JVM built inside database, not normal Java Runtime Engine (java.exe).

When normal Java application got out of memory error, parameter -Xmx can be used while starting the application to configure the heap memory with larger value. However, as the Oracle Databae JVM is running in the process space of the Oracle executable, there is no way to use the -Xmx parameter.  But it is also configurable using a method in the "Java Runtime" class "oracle.aurora.vm.OracleRuntime". The class has following methods,

    getMaxMemorySize   - Get current setting of heap size
    setMaxMemorySize   - Set new heap size

In order to call the methods inside database, we have to create Java stored procedure to expose the method to Oracle database, the example code as following
create or replace function get_java_heap_size return number is
    language java name 'oracle.aurora.vm.OracleRuntime.getMaxMemorySize() returns long';

create or replace function set_java_heap_size(mem_size number) return number is
    language java name 'oracle.aurora.vm.OracleRuntime.setMaxMemorySize(long) returns long';

The out of memory error can be fixed by running created Jave store procedure set_java_heap_size before running the application Java stored procedure which triggers the error. For example
declare
  heap_size number;
begin
  -- Set heap size to 1GB
  heap_size := set_java_heap_size(1024*1024*1024);
  -- Run application Java stored procedure
  user01.myjavaproc;
end;
/

Sunday, May 16, 2021

Grid Infrastructure 12.2 restore OCR, Voting File and mgmtdb when disk group corrupted

For some reason, Oracle desupported the placement of OCR and voting files directly on a shared file system from Grid Infrastructure 12.2 until rescinding the desupport for Standalone Clusters from 19.3. Therefore, when GI 12.2 is installed, OCR, Voting file, and OCR backup location are, by default, configured in the ASM disk group, and mostly likely in the same disk group. When the disk group cannot be mounted because of any issues, the cluster will not be brought up anymore.

Technically, GI has to be re-configured as like a new installation, that could be a big job. However, it may not be that bad. Although 12.2 does not allow you have OCR backup location out of ASM disk group, DBA still can copy the OCR backup file from ASM disk group to file system with command 'asmcmd cp'. I am going to demonstrate how we can maximize the opportunity of bringing the cluster backup without rebuild/reinstall it.

Sunday, May 2, 2021

Oracle 19.11 deinstall failed with "ERROR: oracle/rat/tfa/util/ManageTfa"

After Oracle database 19c home is applied Release Update 19.11.0.0.210420, the home cannot be de-installed anymore.

The deinstall utility will fail with errors,
######################## DEINSTALL CLEAN OPERATION START ########################
## [START] Preparing for Deinstall ##
Setting LOCAL_NODE to thanos
Setting CRS_HOME to false
Setting oracle.installer.invPtrLoc to /tmp/deinstall2021-05-02_07-34-15PM/oraInst.loc
Setting oracle.installer.local to false

ERROR: oracle/rat/tfa/util/ManageTfa
Exited from program.


############# ORACLE DEINSTALL TOOL END #############

In deinstall error log,
java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at oracle.install.db.deinstall.wrapper.Deinstall.callCleanImpl(Deinstall.java:1876)
        at oracle.install.db.deinstall.wrapper.Deinstall.main(Deinstall.java:907)
Caused by: java.lang.NoClassDefFoundError: oracle/rat/tfa/util/ManageTfa
        at oracle.install.db.deinstall.core.PrepForOUIDeinstall.cleanConfig(PrepForOUIDeinstall.java:187)
        ... 6 more
Caused by: java.lang.ClassNotFoundException: oracle.rat.tfa.util.ManageTfa
        at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:418)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:355)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:351)
        ... 7 more

Looks like something related to TFA is missing after RU 19.11 is applied.

Workaround,

Roll back RU 19.11, then re-run deinstall utility.

Wednesday, March 10, 2021

DBCA does not list ASM diskgroup for storage option

Oracle 19c DBCA does not list ASM disk groups in "Select Database Storage Option" window.
Messages in dbca trace file $ORACLE_BASE/cfgtoollogs/dbca/trace.log_<TIMESTAMP>
[DBStorageOption.flowWorker] [ 2021-03-08 13:24:45.969 EST ] [KfodUtil.kfodOutput:375]  /u01/app/19.0.0/grid/bin/kfod
[DBStorageOption.flowWorker] [ 2021-03-08 13:24:45.969 EST ] [KfodUtil.kfodOutput:375]  nohdr=TRUE
[DBStorageOption.flowWorker] [ 2021-03-08 13:24:45.969 EST ] [KfodUtil.kfodOutput:375]  OP=GROUPS
[DBStorageOption.flowWorker] [ 2021-03-08 13:24:45.969 EST ] [KfodUtil.kfodOutput:375]  status=true
[DBStorageOption.flowWorker] [ 2021-03-08 13:24:45.969 EST ] [KfodUtil.kfodOutput:375]  asmcompatibility=true dbcompatibility=true
[DBStorageOption.flowWorker] [ 2021-03-08 13:24:46.045 EST ] [KfodUtil.kfodOutput:386]  Kfod result
Error 49802 initializing ADR
ERROR!!! could not initialize the diag context

[DBStorageOption.flowWorker] [ 2021-03-08 13:24:46.046 EST ] [ASMUtils.loadDiskGroups:1221]  Loading  the diskgroups. exception using kfodError retrieving diskgroup using kfod utility, null
INFO: Mar 08, 2021 1:24:46 PM oracle.assistants.common.lib.FileSystemInfo getSharedStoragePaths
INFO: Getting default shared storage path.

DBCA failed with command "/u01/app/19.0.0/grid/bin/kfod", manually run the command
[oracle@host01]$ /u01/app/19.0.0/grid/bin/kfod nohdr=TRUE OP=GROUPS status=true asmcompatibility=true dbcompatibility=true
Error 49802 initializing ADR
ERROR!!! could not initialize the diag context

Debug the command with truss or strace as following
  On Linux:  strace -o /tmp/kfod.out /u01/app/19.9.0/grid/bin/kfod nohdr=TRUE OP=GROUPS status=true asmcompatibility=true dbcompatibility=true
  On Solaris/AIX: truss -o /tmp/kfod.out /u01/app/19.0.0/grid/bin/kfod nohdr=TRUE OP=GROUPS status=true asmcompatibility=true dbcompatibility=true
Example on Solaris
[oracle@host01]$ truss -o /tmp/kfod.out /u01/app/19.0.0/grid/bin/kfod nohdr=TRUE OP=GROUPS status=true asmcompatibility=true dbcompatibility=true
Error 49802 initializing ADR
ERROR!!! could not initialize the diag context
[oracle@host01]$
[oracle@host01]$ tail /tmp/kfod.out
stat("/u01/app/19.0.0/grid/log/diag/kfod/host01/kfod/log", 0xFFFFFFFF7FFFBF80) Err#2 ENOENT
stat("/u01/app/19.0.0/grid/log/diag/kfod/host01/kfod", 0xFFFFFFFF7FFFBF80) Err#2 ENOENT
stat("/u01/app/19.0.0/grid/log/diag/kfod/host01", 0xFFFFFFFF7FFFBF80) Err#2 ENOENT
stat("/u01/app/19.0.0/grid/log/diag/kfod", 0xFFFFFFFF7FFFBF80) Err#2 ENOENT
stat("/u01/app/19.0.0/grid/log/diag", 0xFFFFFFFF7FFFBF80) = 0
getuid()     = 104 [104]
getgid()     = 112 [112]
getuid()     = 104 [104]
getuid()     = 104 [104]
mmap(0x00010000, 65536, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_ANON|MAP_ALIGN, -1, 0) = 0xFFFFFFFF73900000
getuid()     = 104 [104]
getuid()     = 104 [104]
open("/system/volatile/name_service_door", O_RDONLY) = 6
fcntl(6, F_SETFD, 0x00000001)   = 0
door_info(6, 0xFFFFFFFF75D8B5B0)  = 0
door_call(6, 0xFFFFFFFF7FFFBBB8)  = 0
ioctl(1, TCGETA, 0xFFFFFFFF7FFF8F7C)  = 0
fstat(1, 0xFFFFFFFF7FFF8F10)   = 0
write(1, " E r r o r   4 9 8 0 2  ".., 29) = 29
write(1, " E R R O R ! ! !   c o u".., 47) = 47
close(5)     = 0
_exit(1)

From the trace file /tmp/kfod.out, kfod failed with accessing following directories
    /u01/app/19.0.0/grid/log/diag/kfod/host01/kfod/log
    /u01/app/19.0.0/grid/log/diag/kfod/host01/kfod
    /u01/app/19.0.0/grid/log/diag/kfod/host01
    /u01/app/19.0.0/grid/log/diag/kfod
    /u01/app/19.0.0/grid/log/diag

Check the permission of the directory
[oracle@host01]$ ls -ld /u01/app/19.0.0/grid/log/diag
drwxr-x---   4 grid     oinstall       5 Dec 12 23:17 /u01/app/19.0.0/grid/log/diag
[oracle@host01]$ id
uid=104(oracle) gid=112(oinstall)

oracle, who is running kfod, is member of group oinstall, but group oinstall does not have write permission on directory. Trying to grant write permission as root or grid (owner of GI home)
[grid@host01]$ chmod g+w /u01/app/19.0.0/grid/log/diag
[grid@host01]$ ls -ld /u01/app/19.0.0/grid/log/diag
drwxrwx---   4 grid     oinstall       5 Dec 13 02:45 /u01/app/19.0.0/grid/log/diag

Re-try kfod as oracle
[oracle@host01]$ /u01/app/19.0.0/grid/bin/kfod nohdr=TRUE OP=GROUPS status=true asmcompatibility=true dbcompatibility=true
    204528     188110 EXTERN REDO 10.1.0.0.0 12.1.0.0.0
    613584     388124 EXTERN FRA  10.1.0.0.0 12.1.0.0.0
   4091624     470712 EXTERN DATA 10.1.0.0.0 12.1.0.0.0

Now, dbca can list ASM disk groups.

Tuesday, February 23, 2021

Use UTL_FILE to Write BLOBs Stored Inside the Database Out to Files

Create a precedure as following
create or replace procedure  save_blob_to_file (a_directory varchar2, a_filename varchar2, a_data blob)
is
  p_blob_length  integer;
  p_file         utl_file.file_type;
  p_offset       integer := 1;
  p_chunksize    pls_integer := 32767;
  p_buffer       raw(32767);
begin
  /* Determine size of BLOB */
  p_blob_length := dbms_lob.getlength(a_data);
  /* open a file handle */
  p_file := utl_file.fopen(a_directory, a_filename, 'wb', p_chunksize);
  begin
    /* write BLOB to file in chunks */
    while p_offset <= p_blob_length loop
      if p_offset + p_chunksize - 1 > p_blob_length then
         p_chunksize := p_blob_length - p_offset + 1;
      end if;
      dbms_lob.read(a_data, p_chunksize, p_offset, p_buffer);
      utl_file.put_raw(p_file, p_buffer, true);
      p_offset := p_offset + p_chunksize;
    end loop;
  exception
    when others then
      /* Close the file handle in case of any exceptions*/
      utl_file.fclose(p_file);
      raise;
  end;
  /* Close the file handle after all data is written out*/
  utl_file.fclose(p_file);
end;
/

Sample code to save BLOB to file with procedure 'save_blob_to_file'
declare
  p_blob       blob;
  p_directory  varchar2(30) := 'BLOB_DIR';
  p_filename   varchar2(255):= 'first_blob_file.tst';
begin
  select blob_data into p_blob from tb_blob_file where rownum=1;
  save_blob_to_file(p_directory,p_filename,p_blob);
end;
/

Here, directory 'BLOB_DIR' has to be created before calling the procedure. Example for creating directory,
system@db01> create directory BLOB_DIR as '/u01/blob_files';
Directory created.

Make sure OS directory '/u01/blob_files' exists and Oracle home owner (normally oracle) has read/write under this directory
[oracle@host01]$ ls -ld /u01/blob_files
drwxr-xr-x    2 oracle   oinstall       8192 Feb 20 20:33 /u01/blob_files

Friday, November 27, 2020

Rename / Change the Multipath Device Names on Linux

 Each multipath device has a World Wide Identifier (WWID), which is guaranteed to be globally unique and unchanging. By default, the name of a multipath device is set to its WWID. Alternately, you can set the user_friendly_names option in the multipath configuration file, which sets the alias to a node-unique name of the form mpathn.

Thursday, November 5, 2020

Oracle AHF installation failed with "AHF-00074: Perl Modules not found: Data::Dumper"

Oracle combined Trace File Analyzer (TFA) & ORAchk/EXAchk into single installer called Oracle Autonomous Health Framework (AHF).

When installing AHF on Linux platform (Red Hat Enterprise Linux / Oracle Linux), got error "AHF-00074: Perl Modules not found :  Data::Dumper"
[root@host01]# ./ahf_setup
AHF Installer for Platform Linux Architecture x86_64
AHF Installation Log : /tmp/ahf_install_202300_4904_2020_11_05-18_04_25.log
PERL : /u01/app/19.9.0/grid/perl/bin/perl
[ERROR] : AHF-00074: Perl Modules not found :  Data::Dumper

Saturday, October 31, 2020

Friday, October 30, 2020

Pluggable database opened with RESTRICTED SESSION due to Sync PDB failed with ORA-65177 during 'alter user'

 When open pluggable database, got following error
SQL> alter pluggable database ORCL open;

Warning: PDB altered with errors.

SQL> 
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           READ WRITE YES

The pluggable databse ORCL is opened with RESTRICTED SESSION. Check the view pdb_plug_in_violations for error details,
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> col name for a10
SQL> col time for a18
SQL> col cause for a15
SQL> col message for a75
SQL> set linesize 255
SQL> alter session set nls_timestamp_format='dd-mon-yy hh24:mi:ss';

Session altered.

SQL> select time,name,type,cause,status,message from pdb_plug_in_violations where status <>'RESOLVED';

TIME               NAME       TYPE      CAUSE           STATUS    MESSAGE
------------------ ---------- --------- --------------- --------- ---------------------------------------------------------------------------
30-oct-20 12:00:52 ORCL       ERROR     Sync Failure    PENDING   Sync PDB failed with ORA-65177 during 'alter user ABCDE_APP account unlock'

When the pdb ORCL is being opened, it tries to synchronize the sql "alter user ABCDE_APP account unlock" between pdb and cdb$root, because the sql was run in cdb$root but not in pdb. Check the status of user ABCDE_APP
SQL> col username for a10
SQL> select con_id,username,account_status,created,common from cdb_users where username='ABCDE_APP';

    CON_ID USERNAME   ACCOUNT_STATUS     CREATED   COM
---------- ---------- ------------------ --------- ---
         1 ABCDE_APP  OPEN               11-JUN-19 YES

SQL> alter session set container=ORCL;

Session altered.

SQL>
SQL> select con_id,username,account_status,created,common from cdb_users where username='ABCDE_APP';

    CON_ID USERNAME   ACCOUNT_STATUS    CREATED   COM
---------- ---------- ----------------- --------- ---
         3 ABCDE_APP  OPEN              04-AUG-20 NO


The user ABCDE_APP was created as common user in root container, but in pdb user ABCDE_APP was created as local user. Both user are using same user name, but are totally different user account in the database. When DBA run sql 'alter user ABCDE_APP account unlock' in root container, the database tries to sync the sql with all pdbs, but the common user cannot be synced in pdb ORCL because the user ABCDE_APP is not the same user.

AS a workaround, the issue can be temporarily solved by deleting the sync-pending statement (alter user ABCDE_APP account unlock) from PDB_SYNC$ in both CDB and PDB 

In CDB root container,
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> 
SQL> col name for a10
SQL> col sqlstmt for a40
SQL> 
SQL> alter session set nls_date_format='dd-mon-yy hh24:mi:ss';

Session altered.

SQL> 
SQL> select ctime,sqlstmt,name,flags,opcode,replay# from pdb_sync$ where sqlstmt like 'alter user ABCDE_APP account unlock%';

CTIME              SQLSTMT                                  NAME            FLAGS      OPCODE     REPLAY#
------------------ ---------------------------------------- ---------- ---------- ---------- ----------
30-oct-20 10:24:42 alter user ABCDE_APP account unlock      ABCDE_APP           0          5          21

SQL> delete from PDB_SYNC$ where sqlstmt like ('alter user ABCDE_APP account unlock%');

1 row deleted.

SQL> commit;

Commit complete.

In pdb ORCL
SQL> alter session set container=ORCL;

Session altered.

SQL> select ctime,sqlstmt,name,flags,opcode,replay# from pdb_sync$ where sqlstmt like 'alter user ABCDE_APP account unlock%';

CTIME              SQLSTMT                                  NAME         FLAGS      OPCOD      EREPLAY#
------------------ ---------------------------------------- ---------- ------- ---------- ----------
30-oct-20 10:24:42 alter user ABCDE_APP account unlock      ABCDE_APP        0          5          21

SQL> delete from PDB_SYNC$ where sqlstmt like 'alter user ABCDE_APP account unlock%';

1 row deleted.

SQL> commit;

Commit complete.

Re-open the pdb
SQL> alter pluggable database ORCL close;

Pluggable database altered.

SQL> alter pluggable database ORCL open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           READ WRITE NO

The pdb is open without RESTRICT SESSION. However, the issue may happen again when you run 'alter user ABCDE_APP' statement in root container. The permanent solution is that drop local user from pdb and grant common user access to the pdb.

Tuesday, October 27, 2020

Reduce number of privileged access audit files for ASM with rsyslog on Linux

This post shows how to configure rsyslog on Linux (Red Hat Enterprise Linux 7/8) to redirect ASM audit message to OS log files.

If you are looking for the information for Solaris or AIX, please check out following posts

Saturday, July 18, 2020

Oracle database 19c "srvctl status service" fails with Java.lang.NullPointerException

The following errors occur while running "srvctl status service -db <db_name>" command from 19.7 database home on AIX and Linux x86_64,
$ srvctl status service -db orcl
Exception in thread "main" java.lang.NullPointerException
        at oracle.ops.opsctl.StatusAction.internalExecuteService(StatusAction.java:2077)
        at oracle.ops.opsctl.StatusAction.executeSIHAService(StatusAction.java:1692)
        at oracle.ops.opsctl.Action.executeSIHA(Action.java:445)
        at oracle.ops.opsctl.OPSCTLDriver.execute(OPSCTLDriver.java:537)
        at oracle.ops.opsctl.OPSCTLDriver.main(OPSCTLDriver.java:252)

Monday, June 10, 2019

Reduce number of privileged access audit files for ASM with syslog on AIX

ASM instance audits all privileged (log in as sysasm, sysdba or sysoper) access by creating audit file for each connection under the audit file destination directories (defined by initialization parameter audit_file_dest). The directory can grow to contain a very large number of files if they are not regularly maintained, especially when initialization parameter audit_sys_operations is true which is default from 12c.

Thursday, January 31, 2019

DBCA Failed with CRS-2566: User 'oracle' does not have sufficient permissions to operate on resource 'ora.driver.afd'

DBCA 12.2.0.1 failed with following errors 

   PRCR-1006: Failed to add resource ora.corcl.db for corcl
   PRCR-1071: Failed to register or update resource ora.corcl.db
   CRS-2566 : User 'oracle' does not have sufficient permissions to operate on resource 'ora.driver.afd', which is part of the dependency specification.

The clusterware is Grid Infrastructure 18c in Standalone mode (Oracle Restart), the GI home owner is grid. Log into system as grid and check the permissions of CRS resourc 'ora.driver.afd'
[grid@host01]$ crsctl stat res ora.driver.afd -p -attr ACL
NAME=ora.driver.afd
ACL=owner:grid:rwx,pgrp:asmadmin:r-x,other::r--,user:grid:r-x

ACL shows that grid is the owner of the resource and have full control (rwx) on the resource, OS group asmadmin has read & execute(r-x), and database owner oracle is not explicitly granted permissions on this resource.  

Check if oracle is member of group asmadmin,
[grid@host01]$ id oracle
uid=104(oracle) gid=112(oinstall) groups=112(oinstall),114(asmdba),116(dba),117(oper),118(racdba),119(backupdba),120(dgdba),121(kmdba)

User oracle is not in the group asmadmin. Therefore, DBCA is telling the truth that oracle does not have permissions on the resource. It is technically easy to fix this issue, but honestly it is not expected there and should be considered as GI 18c bug.

To fix it, just add the group of which oracle is a member (I pick oinstall) to the ACL as following,
[grid@host01]$ crsctl modify resource ora.driver.afd -attr "ACL='owner:grid:rwx,pgrp:asmadmin:r-x,other::r--,group:oinstall:r-x,user:grid:r-x'" -init

Confirm that the ACL is changed,
[grid@host01]$ crsctl stat res ora.driver.afd -p -attr ACL
NAME=ora.driver.afd
ACL=owner:grid:rwx,pgrp:asmadmin:r-x,other::r--,group:oinstall:r-x,user:grid:r-x'

Now, DBCA can create database successfully.

Friday, August 31, 2018

Oracle Database 18c Password Authentication with Microsoft Active Directory

Oracle database 18c introduces a new feature 'Integration of Active Directory Services with Oracle Database'. It gives us a way to authenticate and authorize users directly with Microsoft Active Directory. Oracle database users and roles can map directly to Active Directory users and groups without using Oracle Enterprise User Security (EUS) or another intermediate directory service. Here, I am going to demonstrate how to make it work step by step.
 
Environment:

  Database Server - Oracle Database 18c Enterprise Edition on Oracle Linux Server 7.5
  Microsoft Active Directory Server - Windows Server 2012 R2

Thursday, March 1, 2018

How to delete archive log files using PL/SQL


Although RMAN is always the first choice for most DBAs to delete archive log files, PL/SQL could be easier for this scenario,

User wants to switch an existing database from NOARCHIVELOG mode to ARCHIVELOG mode in order to increase the database availability because the database backup job has to shut down database when running in NOARCHIVELOG mode.

Saturday, February 10, 2018

12.2 database Alert log shows ORA-20001: Latest xml inventory is not loaded into table

While 12.2 database is being started by srvctl, the alert log shows following messages,
Unable to obtain current patch information due to error: 20001, ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at "SYS.DBMS_QOPATCH", line 777
ORA-06512: at "SYS.DBMS_QOPATCH", line 864
ORA-06512: at "SYS.DBMS_QOPATCH", line 2222
ORA-06512: at "SYS.DBMS_QOPATCH", line 740
ORA-06512: at "SYS.DBMS_QOPATCH", line 2247
===========================================================
Dumping current patch information
===========================================================
Unable to obtain current patch information due to error: 20001
===========================================================

Tuesday, February 21, 2017

Oracle Rules Manager 11.2.0.4.0 INVALID with invalid JAVA Classes

Oracle database 11.2.0.4 was created with INVALID component 'Oracle Rules Manager',  
system@DB01> select schema,comp_name,version,status from dba_registry;
SCHEMA                         COMP_NAME                                          VERSION                STATUS
------------------------------ -------------------------------------------------- ------------------------------
APEX_050000                    Oracle Application Express                         5.0.3.00.03            VALID
EXFSYS                         Oracle Expression Filter                           11.2.0.4.0             VALID
EXFSYS                         Oracle Rules Manager                               11.2.0.4.0             INVALID
XDB                            Oracle XML Database                                11.2.0.4.0             VALID
WMSYS                          Oracle Workspace Manager                           11.2.0.4.0             VALID
SYS                            Oracle Database Catalog Views                      11.2.0.4.0             VALID
SYS                            Oracle Database Packages and Types                 11.2.0.4.0             VALID
SYS                            JServer JAVA Virtual Machine                       11.2.0.4.0             VALID

Find out invalid objects owned by schema 'EXFSYS' which could INVALID the component Oracle Rules Manager,
system@DB01> select object_name,object_type,status
  2  from dba_objects
  3  where owner='EXFSYS' and status='INVALID';
OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
/53ad31cb_TagsLocator          JAVA CLASS          INVALID
/357abd97_XPathFilter          JAVA CLASS          INVALID

Check errors of found invalid JAVA CLASSes,
system@DB01> select name,text
  2  from dba_errors
  3  where owner='EXFSYS' and name in ('/53ad31cb_TagsLocator','/357abd97_XPathFilter');
NAME                      TEXT
------------------------- ------------------------------------------------------------
/357abd97_XPathFilter     ORA-29534: referenced object EXFSYS.oracle/expfil/xpath/TagsLocator could not be resolved
/53ad31cb_TagsLocator     ORA-29521: referenced name oracle/xml/parser/v2/DOMParser could not be found
/53ad31cb_TagsLocator     ORA-29521: referenced name oracle/xml/parser/v2/XMLParseException could not be found
/53ad31cb_TagsLocator     ORA-29521: referenced name oracle/xml/parser/v2/XMLDocument could not be found

Oracle Rules Manager is in INVALID status because the referenced object/names created by installing XDK cannot be resolved/found. EXFSYS (owner of Oracle Rules Manager) JAVA Classes are dependent on XDK. Therefore installing XDK will validate Oracle Rules Manager,
conn / as sysdba
@?/xdk/admin/initxml.sql
@?/rdbms/admin/utlrp.sql

Check component status,
system@DB01> select schema,comp_name,version,status from dba_registry;
SCHEMA                         COMP_NAME                                          VERSION                STATUS
------------------------------ -------------------------------------------------- ------------------------------
APEX_050000                    Oracle Application Express                         5.0.3.00.03            VALID
EXFSYS                         Oracle Expression Filter                           11.2.0.4.0             VALID
EXFSYS                         Oracle Rules Manager                               11.2.0.4.0             VALID
XDB                            Oracle XML Database                                11.2.0.4.0             VALID
WMSYS                          Oracle Workspace Manager                           11.2.0.4.0             VALID
SYS                            Oracle Database Catalog Views                      11.2.0.4.0             VALID
SYS                            Oracle Database Packages and Types                 11.2.0.4.0             VALID
SYS                            JServer JAVA Virtual Machine                       11.2.0.4.0             VALID


Monday, August 8, 2016

Automated SQL Tuning Task Failed with ORA-00600: [kpdbSwitchPreRestore: Txn]

Database: Oracle 12.1.0.2 RAC + Proactive Bundle Patch 12.1.0.2.160419
Operation System:  AIX 7.1


Automated SQL Tuning Task (SYS_AUTO_SQL_TUNING_TASK) failed and alert log shows errors:
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
2016-08-07 22:00:21.020000 -05:00
 <<Message truncated>>
2016-08-07 22:10:53.145000 -05:00
Errors in file /u01/app/oracle/diag/rdbms/cdb01/CDB01_1/trace/CDB01_1_j004_31719682.trc  (incident=109991) (PDBNAME=PDB01):
ORA-00600: internal error code, arguments: [kpdbSwitchPreRestore: txn], [0x7000101ECFA4F00], [0], [2685403136], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/cdb01/CDB01_1/incident/incdir_109991/CDB01_1_j004_31719682_i109991.trc
2016-08-07 22:11:11.467000 -05:00
Dumping diagnostic data in directory=[cdmp_201608071221111], requested by (instance=1, osid=85983625 (J004)), summary=[incident=109991].


The issue was not seen in non-cdb databases. According to Oracle Support explanation, the task is executed from a pluggable database but also accessing the container database through dblink to tune a remote query which triggers a bug. The bug can be fixed by patch 21283337 if it is available for your platform.

Before the patch is available or the Automated SQL Tuning Task is not really needed, as a workaround, the task (client_name is 'sql tuning advisor' in view dba_autotask_client) can be disabled,
system@PDB01> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED
sql tuning advisor                                               ENABLED

system@PDB01> select client_name,operation_name,status from dba_autotask_operation;

CLIENT_NAME                              OPERATION_NAME                           STATUS
---------------------------------------- ---------------------------------------- --------
auto optimizer stats collection          auto optimizer stats job                 ENABLED
auto space advisor                       auto space advisor job                   ENABLED
sql tuning advisor                       automatic sql tuning task                ENABLED

system@PDB01> begin
  2  dbms_auto_task_admin.disable(client_name=>'SQL TUNING ADVISOR',
  3                               operation=>null,
  4                               window_name=>null);
  5  end;
  6  /

PL/SQL procedure successfully completed.

system@PDB01> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
sql tuning advisor                                               DISABLED
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED

system@PDB01> select client_name,operation_name,status from dba_autotask_operation;

CLIENT_NAME                              OPERATION_NAME                           STATUS
---------------------------------------- ---------------------------------------- --------
auto optimizer stats collection          auto optimizer stats job                 ENABLED
auto space advisor                       auto space advisor job                   ENABLED
sql tuning advisor                       automatic sql tuning task                DISABLED


Usage notes for dbms_auto_task_admin.disable,

 * If operation and window_name are both NULL, the client is disabled.
 * If operation is not NULL, window_name is ignored and the operation is disabled
 * If operation is NULL and window_name is not NULL, the client is disabled in the specified window.

Note: If disabling the task as workaround, we have to disable it in all containers (PDB & root),

SYSTEM@PDB01> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS  
---------------------------------------------------------------- --------
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED
sql tuning advisor                                               DISABLED

SYSTEM@PDB01> alter session set container=cdb$root;

Session altered.

SYSTEM@CDB$ROOT> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS  
---------------------------------------------------------------- --------
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED
sql tuning advisor                                               ENABLED

SYSTEM@CDB$ROOT> select con_id,client_name,status from cdb_autotask_client;

    CON_ID CLIENT_NAME                                                      STATUS 
---------- ---------------------------------------------------------------- --------

         1 auto optimizer stats collection                                  ENABLED
         1 auto space advisor                                               ENABLED
         1 sql tuning advisor                                               ENABLED
         3 auto optimizer stats collection                                  ENABLED
         3 auto space advisor                                               ENABLED
         3 sql tuning advisor                                               DISABLED

6 rows selected.

SYSTEM@CDB$ROOT> exec dbms_auto_task_admin.disable('SQL TUNING ADVISOR',null,null);

PL/SQL procedure successfully completed.

SYSTEM@CDB$ROOT> select con_id,client_name,status from cdb_autotask_client;

    CON_ID CLIENT_NAME                                                      STATUS  
---------- ---------------------------------------------------------------- --------

         1 auto optimizer stats collection                                  ENABLED
         1 auto space advisor                                               ENABLED
         1 sql tuning advisor                                               DISABLED
         3 auto optimizer stats collection                                  ENABLED
         3 auto space advisor                                               ENABLED
         3 sql tuning advisor                                               DISABLED

6 rows selected.

Wednesday, April 29, 2015

Insufficient archive log space caused ORA-03113

Oracle 11g database was shut down for hardware maintenance. Unfortunately, the database can not be started after maintenance because of error ORA-03113.

$ srvctl sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 27 14:10:05 2015

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2226096 bytes
Variable Size 532678736 bytes
Database Buffers 276824064 bytes
Redo Buffers 23375872 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 33947688
Session ID: 749 Serial number: 3


Apparently, something happended while instance status is changed to OPEN from MOUNT. Check alert log file:

ARC3 started with pid=30, OS id=51183782
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_45220056.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '+FRA2'
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_45220056.trc:
ORA-16038: log 3 sequence# 32464 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '+REDO1/db01/onlinelog/group_3.310.857090421'
ORA-00312: online log 3 thread 1: '+REDO2/db01/onlinelog/group_3.310.857090421'
USER (ospid: 45220056): terminating the instance due to error 16038


Instance is terminated due to error 16038: log 3 sequence# 32464 cannot be archived. Redo log cannot be archived because the recovery area is 100% used.

Backup archive log and release space by running:

$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Apr 27 14:17:52 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area 835104768 bytes

Fixed Size 2226096 bytes
Variable Size 532678736 bytes
Database Buffers 276824064 bytes
Redo Buffers 23375872 bytes

RMAN> backup archivelog all delete all input;

... ...


Try to open database:

RMAN> alter database open;

database opened


Database is opened successfully.

Thursday, January 29, 2015

Database link cannot be dropped because of ORA-02024

When dropping database link got following error:
ORA-02024: database link not found.
SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST CREATED
----------------- ------------- ---------- ----------- ---------
REFRESH_DBLINK SYSTEM DBPROD 15-OCT-13

SQL> drop database link REFRESH_DBLINK;
drop database link REFRESH_DBLINK
*
ERROR at line 1:
ORA-02024: database link not found

Can not find an existing database link? What if create a new link with same name?
SQL> create database link REFRESH_DBLINK
2 connect to system identified by Ax356 using 'DBPROD';

Database link created.

SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST CREATED
------------------------ ------------- ---------- ----------- ---------
REFRESH_DBLINK SYSTEM DBPROD 15-OCT-13
REFRESH_DBLINK.WORLD SYSTEM DBPROD 29-JAN-15

The name of new database link is appended with '.WORLD'. Did more test and found that all new created database link will be added suffix '.WORLD' in the name. Try DROP DATABASE LINK to see what is going to happen:
SQL> drop database link REFRESH_DBLINK;

Database link dropped.

SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST CREATED
------------------------ ------------- ---------- ----------- ---------
REFRESH_DBLINK SYSTEM DBPROD 15-OCT-13

See, I was dropping REFRESH_DBLINK, but REFRESH_DBLINK.WORLD was dropped. I always get REFRESH_DBLINK.WORLD dropped no matter whether I include '.WORLD' in the DROP DATABASE LINK command. Why did we get the weird term '.WORLD'? It was popular long time ago as default database domain and often found in global_name & db_domain of the database:
SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string DBPROD

SQL> show parameter db_domain

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
DBPROD.WORLD

It does be a part of database global_name. Does domain part in global_name affect database link name space? Unfortunately, it does. Delete the domain part from global_name:

 
SQL> alter database rename global_name to DBPROD;

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
------------------------------------------------
DBPROD.WORLD

Interesting, the ALTER DATABASE command does not help. Let us manually change global_name bypass ALTER DATABASE command,
Note: Take a complete consistent backup of the database before you continue:
SQL> conn / as sysdba
Connected.

SQL> select name,value$ from props$ where GLOBAL_name='GLOBAL_DB_NAME';

NAME VALUE$
------------------------------ --------------------------------------------------
GLOBAL_DB_NAME DBPROD.WORLD

SQL> update props$ set value$='DBPROD' where name='GLOBAL_DB_NAME';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from global_name;

GLOBAL_NAME
---------------------
DBPROD

Oracle never suggests direct update on props$. We are not supposed to do that. Hmmm, today is special :)

Drop the database link:
SQL> connect system

SQL> drop datbaetabase link REFRESH_DBLINK;

Database link dropped.

SQL> select * from user_db_links;

no rows selected

Finally, database link is really dropped.