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

Monday, August 30, 2021

Database startup failed with ORA-600 [dbgripmg_2: infinite init action] [ADR_CONTROL_AUX]

Oracle database startup failed with ORA-00600 as following
SQL> startup
ORACLE instance started.

Total System Global Area 8589878792 bytes
Fixed Size                 12854792 bytes
Variable Size            5402263552 bytes
Database Buffers         3154116608 bytes
Redo Buffers               20643840 bytes
ORA-00600: internal error code, arguments: [dbgripmg_2: infinite init action],
[11], [ADR_CONTROL_AUX], [], [], [], [], [], [], [], [], []

Errors ORA-00700 [dbgrmblcp_corrupt_page]/ORA-00600 [dbgrmblgp_get_page_1] can be found from alert log as following,
Dumping diagnostic data in directory=[cdmp_20210830103319], requested by (instance=1, osid=85656173 (M000)), summary=[incident=186167].
2021-08-30 10:33:19.773000 -04:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_m000_11797188.trc  (incident=186169) (PDBNAME=CDB$ROOT):
ORA-00700: soft internal error, arguments: [dbgrmblcp_corrupt_page], [/u01/app/oracle/diag/rdbms/db01/DB01/metadata/INCIDENT.ams], [11], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/db01/DB01/incident/incdir_186169/DB01_m000_11797188_i186169.trc
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_59376292.trc  (incident=186150) (PDBNAME=CDB$ROOT):
ORA-00700: soft internal error, arguments: [dbgrmblcp_corrupt_page], [/u01/app/oracle/diag/rdbms/db01/DB01/metadata/ADR_CONTROL.ams], [11], [], [], [], [], [], [], [], [], []
ORA-00700: soft internal error, arguments: [dbgrmblcp_corrupt_page], [/u01/app/oracle/diag/rdbms/db01/DB01/metadata/ADR_CONTROL.ams], [11], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/db01/DB01/incident/incdir_186150/DB01_ora_59376292_i186150.trc
Dumping diagnostic data in directory=[cdmp_20210830103320], requested by (instance=1, osid=85656173 (M000)), summary=[incident=186168].
2021-08-30 10:33:21.211000 -04:00
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_m000_11797188.trc  (incident=186170) (PDBNAME=CDB$ROOT):
ORA-00700: soft internal error, arguments: [dbgrmblcp_corrupt_page], [/u01/app/oracle/diag/rdbms/db01/DB01/metadata/INCIDENT.ams], [11], [], [], [], [], [], [], [], [], []
ORA-00700: soft internal error, arguments: [dbgrmblcp_corrupt_page], [/u01/app/oracle/diag/rdbms/db01/DB01/metadata/INCIDENT.ams], [11], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_m000_11797188.trc  (incident=186171) (PDBNAME=CDB$ROOT):
ORA-00600: internal error code, arguments: [dbgrmblgp_get_page_1], [11], [0], [0], [], [], [], [], [], [], [], []
ORA-00700: soft internal error, arguments: [dbgrmblcp_corrupt_page], [/u01/app/oracle/diag/rdbms/db01/DB01/metadata/INCIDENT.ams], [11], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/db01/DB01/incident/incdir_186171/DB01_m000_11797188_i186171.trc
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_59376292.trc  (incident=186151) (PDBNAME=CDB$ROOT):
ORA-00600: internal error code, arguments: [dbgrmblgp_get_page_1], [11], [0], [0], [], [], [], [], [], [], [], []
ORA-00700: soft internal error, arguments: [dbgrmblcp_corrupt_page], [/u01/app/oracle/diag/rdbms/db01/DB01/metadata/ADR_CONTROL.ams], [11], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/db01/DB01/incident/incdir_186151/DB01_ora_59376292_i186151.trc

ADRCI is accessing ams files which are corrupted. In the example alert, corrupted files are INCIDENT.ams and ADR_CONTROL.ams under $ADR_HOME. The corrupted ams file may be different in different scenarios.

Solution is that deleting the corrupted files and restart database.

Wednesday, July 7, 2021

AQ Queue Monitor Does Not Change Message MSG_STATE from WAIT to READY on RAC

In RAC database, the Advanced Queue (AQ) message MSG_STATE (column of view AQ$<QUEUE_TABLE>) stays with value 'WAIT' and will not be changed to 'READY' any more. It usually happens after database was shut down with immediate/abort option followed by database patching/upgrading. 

Wednesday, June 23, 2021

EM 12c/13c Change Lifecycle Status with SQL

The Lifecycle Status property of EM targets is often used to prioritize the notification of incident rule. For example, DBA will be paged while database which Lifecycle Status is 'Production' crashed, and only emailed for 'Test' database. This post is going to show how to use sql scripts to find out targets with different Lifecycle Status and how to change the Lifecycle Status.

Before you can run these scripts, you have to connect to EM repository database as sysman.

List all databases which Lifecycle Status are not set yet with following sql,
select t.target_name, t.target_type, t.host_name
  from mgmt_targets t, mgmt_target_properties p
 where t.target_guid=p.target_guid(+)
       and t.target_type='oracle_database' -- comment out this line if going to widen the query
       and p.property_name(+)='orcl_gtp_lifecycle_status' and p.property_value is null;

If you want to widen the query result, you can remove 'target_type' predicate from where clause, or you assign different value for the predicate to find out other type of targets (e.g. listener, asm instance, etc.), the populate values of 'target_type' include

    oracle_database      Single instance database or RAC instance
    oracle_pdb              Pluggable database
    rac_database           RAC database
    osm_instance          ASM instance
    oracle_listener        Oracle database listener

Sample query
sysman@OEMR> select t.target_name, t.target_type, t.host_name
  2    from mgmt_targets t, mgmt_target_properties p
  3   where t.target_guid=p.target_guid(+)
  4         and t.target_type in ('oracle_database','oracle_pdb','rac_database','osm_instance','oracle_listener')
  5         and p.property_name(+)='orcl_gtp_lifecycle_status' and p.property_value is null;

ENTITY_NAME                    ENTITY_TYPE                                        HOST_NAME                     
------------------------------ -------------------------------------------------- ------------------------------
+ASM_host01.dbaplus.ca         osm_instance                                       host01.dbaplus.ca
ORCL                           oracle_database                                    host01.dbaplus.ca
DB01                           oracle_database                                    host01.dbaplus.ca
DB01_CDBROOT                   oracle_pdb                                         host01.dbaplus.ca
DB01_PDB1                      oracle_pdb                                         host01.dbaplus.ca
DB01_PDB2                      oracle_pdb                                         host01.dbaplus.ca
DB02                           rac_database                                       rac01.dbaplus.ca
DB02_CDBROOT                   oracle_pdb                                         rac01.dbaplus.ca
DB02_DB02_1                    oracle_database                                    rac02.dbaplus.ca
DB02_DB02_2                    oracle_database                                    rac01.dbaplus.ca
DB02_PDB1                      oracle_pdb                                         rac01.dbaplus.ca
LISTENER_host01.dbaplus.ca     oracle_listener                                    host01.dbaplus.ca

The Lifecycle Status can be changed with following sql (pl/sql),
exec mgmt_target.set_target_property('<target_name>','<target_type>','orcl_gtp_lifecycle_status','INSTANCE','<Lifecycle Status>');

Valid 'Lifecycle Status' can be one of following values,

   Development, Test, Stage, Production

Following command changes Lifcycle Status of database DB01 to 'Test',
# Before changing
sysman@OEMR> select t.target_name, t.target_type, t.host_name, p.property_value lifecycle_status
  2    from mgmt_targets t, mgmt_target_properties p
  3   where t.target_guid=p.target_guid(+)
  4         and t.target_name='DB01'
  5         and t.target_type='oracle_database'
  6         and p.property_name(+)='orcl_gtp_lifecycle_status';

TARGET_NAME   TARGET_TYPE        HOST_NAME          LIFECYCLE_STATUS
------------- ------------------ ------------------ ----------------
DB01          oracle_database    host01.dbaplus.ca

# Change
sysman@OEMR> exec mgmt_target.set_target_property('DB01','oracle_database','orcl_gtp_lifecycle_status','INSTANCE','Test');

PL/SQL procedure successfully completed.

sysman@OEMR> commit;

Commit complete.

# After changing
sysman@OEMR> select t.target_name, t.target_type, t.host_name, p.property_value lifecycle_status
  2    from mgmt_targets t, mgmt_target_properties p
  3   where t.target_guid=p.target_guid(+)
  4         and t.target_name='CHPTEST1'
  5         and t.target_type='oracle_database'
  6         and p.property_name(+)='orcl_gtp_lifecycle_status';

TARGET_NAME   TARGET_TYPE        HOST_NAME            LIFECYCLE_STATUS
------------- ------------------ -------------------- ----------------
DB01          oracle_database    host01.dbaplus.ca    Test

Change multiple targets with sql statements like following,
declare
  cursor c1 is
   select t.target_name, t.target_type, t.host_name
     from mgmt_targets t, mgmt_target_properties p
    where t.target_guid=p.target_guid(+)
          and t.target_type in ('oracle_database','oracle_pdb','rac_database','osm_instance','oracle_listener')
          and p.property_name(+)='orcl_gtp_lifecycle_status' and p.property_value is null;
begin
  for cc in c1 loop
 mgmt_target.set_target_property(cc.target_name,cc.target_type,'orcl_gtp_lifecycle_status','INSTANCE','Test')
  end loop;
end;


For example, change all new databases (Lifecycle Status not set) to 'Test',
sysman@OEMR> declare
  2    cursor c1 is
  3     select t.target_name, t.target_type, t.host_name
  4       from mgmt_targets t, mgmt_target_properties p
  5      where t.target_guid=p.target_guid(+)
  6            and t.target_type='oracle_database'
  7            and p.property_name(+)='orcl_gtp_lifecycle_status' and p.property_value is null;
  8  begin
  9    for cc in c1 loop
 10        mgmt_target.SET_TARGET_PROPERTY(cc.target_name,cc.target_type,'orcl_gtp_lifecycle_status','INSTANCE','Test');
 11    end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

sysman@OEMR> commit;

Commit complete.
[/div]

Tuesday, June 22, 2021

EM 12c/13c How to find out new discovered targets with SQL

 When agent is deployed to new host, or new targets is installed or created on existing hosts, Oracle Enterprise Manger (EM) Cloud Control can discover the targets automatically. However, EM does not promote the new discovered targets automatically.

Although, the new discovered targets can be found from EM console -> Setup -> Add Target -> Auto Discovery Results, many DBAs like command line to do that. It is more efficient and flexible.

Log into EM repository database as sysman, run following sql,
col entity_name for a20
col entity_type for a20
col host_name for a50
select entity_name,entity_type,host_name,
       decode (manage_status, 0, 'Ignored', 
                              1, 'Not managed yet', 
                              2, 'Managed', 
                              3, 'Managed target component',
                                 'Unknown') "Manage Status", 
       decode (promote_status, 0, 'Cannot promote (existence only entity)', 
                              1, 'Eligible for promotion', 
                              2, 'Promotion in progress', 
                              3, 'Promoted',
                                 'Unknown') "Promote Status"
from mgmt$manageable_entities
where promote_status=1;

Example output,
sysman@OEMR> col entity_name for a20
sysman@OEMR> col entity_type for a20
sysman@OEMR> col host_name for a50
sysman@OEMR>
sysman@OEMR> select entity_name,entity_type,host_name,
  2         decode (manage_status, 0, 'Ignored',
  3                                1, 'Not managed yet',
  4                                2, 'Managed',
  5                                3, 'Managed target component',
  6                                   'Unknown') "Manage Status",
  7         decode (promote_status, 0, 'Cannot promote (existence only entity)',
  8                                1, 'Eligible for promotion',
  9                                2, 'Promotion in progress',
 10                                3, 'Promoted',
 11                                   'Unknown') "Promote Status"
 12  from mgmt$manageable_entities
 13  where promote_status=1;

ENTITY_NAME   ENTITY_TYPE          HOST_NAME                Manage Status     Promote Status
------------- -------------------- ------------------------ ----------------- -------------
dbtest        oracle_database      host01.lab.dbaplus.ca    Ignored           Eligible for promotion
db02          oracle_database      host01.lab.dbaplus.ca    Not managed yet   Eligible for promotion

Two databases are discovered, dbtest is ignored because it was created for temporary testing purpose and does not need to be promoted, and db01 is candidate for promotion.

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
===========================================================