Stuck Archiver Processes and FAL Gap Resolution Not Working

On a RAC to RAC data guard configuration (11.1.0.7) network wait class and waits on LGWR LNS seem to appear out of the blue. System has been running for a quite a number of years and there has not been any changes to network or any other hardware (NIC, cables and etc). Output from the emconsole
Drilling down to "other" wait class could see following LGWR LNS wait
The wait histogram showed a constant value of 16ms
Following was observed in the network wait class drill down
Metalink note Data Guard Wait Events (233491.1) describes these wait event as "ARCH wait on ATTACH - This wait event monitors the amount of time spent by all archive processes to spawn an RFS connection. The LGWR-LNS wait on channel wait event is for standby destinations configured with either the LGWR ASYNC or LGWR SYNC=PARALLEL attributes.
LGWR-LNS wait on channel - This wait event monitors the amount of time spent by the log writer (LGWR) process or the network server processes waiting to receive messages on KSR channels."

During this time there was a considerable archive gap between primary and standby and FAL gap resolution seems unable to resolve it. (FAL use to work fine).

The thought of making changes to transport related values in Oracle net (send and receive buffer) was suppressed since there has not been any hardware changes.
Metalink didn't give any more than definition for these waits.
Googling yield a forum post with a mention of metalink note Bug 5576816 - FAL gap resolution does not work with max_connection set in some scenario. This was applicable for 10.2.0.3 not 11.1.0.7 but the recommendation on the posting (found through googling) was to kill all the archive processes in primary(these will get restated as soon as they get killed). Reason given was that these archive processes were "stuck" and need restart. Looking at the emconsole it was also visible waits were happening on the archive processes.

Tried to kill the archive processors "proper way" by changing the log_archive_max_processes to 1 but this didn't kill any of the processes. Even after setting it to 1 all the archive processes were running. Then did a rolling shutdown and start up of the primary which resolved the issue.

Unfortunately the issue was back after few days on one of the nodes. This time killing the Oracle database session of the archive processes waiting for these wait events resolved it. It seem the archive processes being "stuck" is the symptom and cause could be something else.
Blog post will be updated ...

Remap Column Data on the Fly using EXPDP

Remap data is a new feature introduced in 11g releases which allows data on a column to be changed on the fly when exporting (expdp).

Few things to note. The help option gives the usage of remap_data as

REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

It shows only the table name and column name is required but this result in following error

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39146: schema "" does not exist

Schema name is also required.

Also the function used to remap the data must accept the column type as argument otherwise following error will be thrown

ORA-31693: Table data object "ASANGA"."X" failed to load/unload and is being skipped due to error:
ORA-06553: PLS-306: wrong number or types of arguments in call to function_name

Also the function used must be a function within a package. Schema level function will not be used and following error will be shown

ORA-39001: invalid argument value
ORA-39232: invalid remap function: DUMP_OBFUSCATE

Below is an example package function that could be used

create or replace package obfuscate as

 function dump_obfuscate(ssn in number) return number;
 
end;
/

create or replace package body  obfuscate as
  
 function dump_obfuscate(ssn in number) return number
 is
  i number;
 begin
  i := dbms_random.value(0,100);
  return  round(i);
 end;

end;
/

Use the expdp syntax as

expdp asanga/asa directory=dumpdir dumpfile=asa.dmp logfile=asa.log SCHEMAS=asanga
REMAP_DATA=asanga.x.a:obfuscate.dump_obfuscate

It is also possible to remap data in multiple tables using multiple remap functions.

expdp asanga/*** directory=dumpdir dumpfile=asa.dmp logfile=asa.log SCHEMAS=asanga
REMAP_DATA=asanga.x.a:obfuscate.dump_obfuscate,asanga.y.a:obfuscate1.dump_obfuscate1

Creating and Removing ACFS

Oracle Automatic Storage Management Cluster File System (Oracle ACFS) is a multi-platform, scalable file system, and storage management technology that extends Oracle Automatic Storage Management (Oracle ASM) functionality to support customer files maintained outside of Oracle Database.

Creating ACFS

1. Set the diskgroup attribute parameters. Compatible.asm and compatible.advm should be >= 11.2

$ sqlplus  / as sysasm
SQL> alter diskgroup flash set attribute 'compatible.asm'='11.2';
SQL> alter diskgroup flash set attribute 'compatible.advm'='11.2';

2. Create a volume in the appropriate diskgroup

SQL> alter diskgroup flash add volume volume1 size 1g;

This could also be done using asmcmd. Volume device could be identified with

select volume_name,volume_device from v$asm_volume;
VOLUME_NAME    VOLUME_DEVICE
-------------- ---------------------
VOLUMNE1       /dev/asm/volumne1-398

This could also confirmed with ls /dev/asm/*

3. Create a file system on the created volume

# mkfs -t acfs /dev/asm/volumne1-398
mkfs.acfs: version                   = 11.2.0.2.0
mkfs.acfs: on-disk version           = 39.0
mkfs.acfs: volume                    = /dev/asm/volumne1-398
mkfs.acfs: volume size               = 1073741824
mkfs.acfs: Format complete.

(Update 2013/10/30)
mkfs could also be run as grid user provided that user has the permission on the device

[grid@rhel6m1 ~]$ ls -l /dev/asm/volume1-149
brwxrwx---. 1 root asmadmin 252, 76289 Oct 30 15:31 /dev/asm/volume1-149
[grid@rhel6m1 ~]$  mkfs -t acfs /dev/asm/volume1-149
mkfs.acfs: version                   = 11.2.0.3.0
mkfs.acfs: on-disk version           = 39.0
mkfs.acfs: volume                    = /dev/asm/volume1-149
mkfs.acfs: volume size               = 1073741824
mkfs.acfs: Format complete.

4. Register the file system with

acfsutil registry -a /dev/asm/volumne1-398 /opt/acfsvol

/opt/acfsvol will be the mountpoint for this file system.

5. Mount the file system

# mount -t acfs /dev/asm/volumne1-398 /opt/acfsvol

ASMCMD> volinfo -G flash VOLUMNE1
Diskgroup Name: FLASH

         Volume Name: VOLUMNE1
         Volume Device: /dev/asm/volumne1-398
         State: ENABLED
         Size (MB): 1024
         Resize Unit (MB): 256
         Redundancy: UNPROT
         Stripe Columns: 4
         Stripe Width (K): 128
         Usage: ACFS
         Mountpath: /opt/acfsvol

6. Change ownership to Oracle user to allow oracle user processes to use the file system

# chown oracle:oinstall /opt/acfsvol

6. As oracle user create a file in the mount point

cd /opt/acfsvol
touch x

In a RAC system the volume will be mounted on all nodes after server reboots and requires selinux to be permissive.




Removing ACFS

1. Unmount the file system

# umount /opt/acfsvol

2. De-register the file system

# acfsutil registry -d /opt/acfsvol
acfsutil registry: successfully removed ACFS mount point /opt/acfsvol from Oracle Registry

3. Drop the volumne from the diskgroup

SQL> alter diskgroup flash drop volume volume1;

Diskgroup altered.

SQL> select volume_name,volume_device from v$asm_volume;

no rows selected