Uncategorized


Check if Unified Audit is enabled without database running

I wanted to check if the binaries had Unified Audit enabled without using a database (because it was not yet created).

This is part of libknlopt.a library. Depending on which files are inside, it enables or disables options.

For Unified Auditing your can use:

case $(ar t ${ORACLE_HOME}/rdbms/lib/libknlopt.a | grep 'kza.ang.o') in "kzaiang.o") R="enabled" ;; "kzanang.o") R="disabled" ;; *) R="unknown" ;; esac && echo "Unified audit => $R"

For many other options (but not yet Unified Audit) you can check MOS note: How to Check and Enable/Disable Oracle Binary Options (Doc ID 948061.1)


Install latest version of Oracle Goldengate 12

With Oracle Goldengate 12 there is now a big change on the way to install: there is an installer!

Up to Oracle Goldengate 11 every new patch was a new complete version. With Oracle Goldengate 12 you need to install a base version and then the latest patch.

Here is a quick sum up of the instructions (done on AIX, but is similar on Linux):

  1. From Oracle website download the Goldengate 12.1.2.1.0 for your operating system
  2. From Oracle support note 1645495.1 download the latest patchset for Goldengate. Check the availability for your OS.
  3. Unzip both downloaded files
  4. Call the <download_dir>/ggs_AIX_ppc_shiphome/Disk1/runInstaller
  5. Follow the instructions by choosing the target DB version and install directory (I recommend $ORACLE_BASE/product/gg)
  6. Oracle GoldenGate 12.1.2.1.0 - Install Wizard - Step 1 of 5

    Oracle GoldenGate 12.1.2.1.0 - Install Wizard - Step 2 of 5

    Oracle GoldenGate 12.1.2.1.0 - Install Wizard - Step 3 of 5

    Oracle GoldenGate 12.1.2.1.0 - Install Wizard - Step 4 of 5

    Oracle GoldenGate 12.1.2.1.0 - Install Wizard - Step 5 of 5

  7. Check it works
  8. # set ORACLE_HOME to an existing Oracle database installation:
    export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4
    
    # set LD_LIBRARY_PATH to use libraries from $ORACLE_HOME:
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_BASE/product/gg
    
    # Start Goldengate:
    cd $ORACLE_BASE/product/gg
    ./ggsci
    
    Oracle GoldenGate Command Interpreter for Oracle
    Version 12.1.2.1.1 19797716 OGGCORE_12.1.2.1.1_PLATFORMS_141026.2215
    AIX 6, ppc, 64bit (optimized), Oracle 11g on Oct 27 2014 00:19:39
    Operating system character set identified as ISO-8859-1.
    
    Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
    
    GGSCI (anjovm1) 1> info all
    
    Program Status Group Lag at Chkpt Time Since Chkpt
    
    MANAGER STOPPED
    GGSCI (anjovm1) 2> exit
    
  9. Install the latest patchset with OPatch
  10. cd <download_dir>/21785294
    $ORACLE_BASE/product/gg/OPatch/opatch apply -oh $ORACLE_BASE/product/gg
    
    Invoking OPatch 11.2.0.1.7
    
    Oracle Interim Patch Installer version 11.2.0.1.7
    Copyright (c) 2011, Oracle Corporation.  All rights reserved.
    
    Oracle Home       : /oracle/app/product/gg121
    Central Inventory : /oracle/oraInventory
       from           : /etc/oraInst.loc
    OPatch version    : 11.2.0.1.7
    OUI version       : 11.2.0.3.0
    Log file location : /oracle/app/product/gg121/cfgtoollogs/opatch/opatch2015-11-11_08-32-55AM.log
    
    Applying interim patch '21785294' to OH '/oracle/app/product/gg121'
    Verifying environment and performing prerequisite checks...
    
    Do you want to proceed? [y|n] y
    User Responded with: Y
    All checks passed.
    
    Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
    (Oracle Home = '/oracle/app/product/gg121')
    
    Is the local system ready for patching? [y|n] y
    User Responded with: Y
    Backing up files...
    
    Patching component oracle.oggcore.ora11g, 12.1.2.1.0...
    Patch 21785294 successfully applied
    Log file location: /oracle/app/product/gg121/cfgtoollogs/opatch/opatch2015-11-11_08-32-55AM.log
    
    OPatch succeeded.
    
  11. Check it is updated
# Start Goldengate:
cd $ORACLE_BASE/product/gg
./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.10 21604177 OGGCORE_12.1.2.1.0OGGBP_PLATFORMS_150902.1337
AIX 6, ppc, 64bit (optimized), Oracle 11g on Sep  2 2015 16:29:21
Operating system character set identified as ISO-8859-1.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.

GGSCI (anjovm1) 1> 

We have now Oracle Goldengate 12.1.2.1.10 installed.

Interesting enough is that Oracle Goldengate 12.1 still uses the OPatch version 11.2.0.1.7, taken probably from Oracle 11.2.0.3, as we can see from the line “OUI version : 11.2.0.3.0”.


Who does MobaXterm?

Since few years that intermittently I use MobaXterm for accessing remote servers. It is very well done and great replacement for Putty, with tabbed ssh interface, integrated X11 and sftp solution.

But the main reason why I don’t fully use it is because I don’t know who develops it. The website of MobaXterm just has a contact form, no address, no name.

By doing some research one can find that Mobatek is a company based in a village East of Toulouse, France. The address can be seen here and the picture of the place can be seen on google maps. It is a very simple house, not a headquarters of a company as I would expect. Searching some more we end up at this engineer, which seems to be project manager at Thales Alenia Space.

The question remains: just a hobby? a secondary employment?

When seeing a software so well made one would expect to be part of some bigger company, at least with bigger apparence.

The only thing that conforts me is that the source code of MobaXterm is available here. But someone has checked it?


Find orphan ASM files 5

These days I’ve been doing the migration of some BI databases into a private cloud, about 7 TB of data in total. We made sure that before starting there was enough disk space.

However, in the middle of the largest “duplicate from active database”, it stops. When trying to restart, it complaints that disk group space was exhausted.

After some investigation, I found out that for a previous DB, which made 1.8 TB in datafiles, it was using above 3 TB in ASM. A closer investigation and I could see several copies of some datafiles. I believe it come from the fact that there were network problems, duplicates that were repeated with some interval between, rolling forward with RMAN incremental backup, switch to copy… a whole mess. 🙂

Then come the problem:

– how to make a easy way to detect all orphan files on ASM?

The first solution that come to mind was to spool the list of data files and, from asmcmd, output all the files for the problematic DB with the command:

asmcmd ls dgdata/O01XXXXX/datafile > /tmp/f_asm.txt

Then put both files as external tables into a database and query them. But there were several steps and was hard work.

The second solution was to use v$asm_file and v$asm_alias together with queries to v$datafile, v$logfile and v$controlfile. This from the database instance, even if the documentation states that:
“In a database instance, V$ASM_FILE displays no rows.”
(http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_1029.htm#REFRN30172)

For this solution I’ve based on a query from http://aychin.wordpress.com/2011/07/26/dfdg-script-to-display-asm-disk-usage-information-and-files/

And the solution is:

DEFINE ASMGROUP="DGDATA"
 
set linesize 200
set pagesize 50000
col reference_index noprint
col type format a15
col files format a80

WITH v_asmgroup AS (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP'),
     v_parentindex AS (SELECT parent_index 
                    FROM v$asm_alias 
              WHERE group_number = (SELECT group_number FROM v_asmgroup) 
                AND alias_index=0),
  v_asmfiles AS (SELECT file_number, type 
              FROM v$asm_file 
           WHERE group_number = (SELECT group_number FROM v_asmgroup)),
 v_dbname AS (SELECT '/'||upper(db_unique_name)||'/' dbname from v$database)
SELECT 'rm '|| files files FROM -- this line show the delete command
(
  SELECT '+&ASMGROUP'||files files, type 
  FROM (SELECT upper(sys_connect_by_path(aa.name,'/')) files, aa.reference_index, b.type
        FROM (SELECT file_number,alias_directory,name, reference_index, parent_index 
        FROM v$asm_alias) aa,
             (SELECT parent_index FROM v_parentindex) a,
             (SELECT file_number, type FROM v_asmfiles) b
  WHERE aa.file_number=b.file_number(+)
    AND aa.alias_directory='N'
   -- missing PARAMETERFILE, DATAGUARDCONFIG
   AND b.type in ('DATAFILE','ONLINELOG','CONTROLFILE','TEMPFILE')
  START WITH aa.PARENT_INDEX=a.parent_index
  CONNECT BY PRIOR aa.reference_index=aa.parent_index)
  WHERE substr(files,instr(files,'/',1,1),instr(files,'/',1,2)-instr(files,'/',1,1)+1) = (select dbname FROM v_dbname)
MINUS (
  SELECT upper(name) files, 'DATAFILE' type FROM v$datafile
    UNION ALL 
  SELECT upper(name) files, 'TEMPFILE' type FROM v$tempfile
    UNION ALL
 SELECT upper(name) files, 'CONTROLFILE' type FROM v$controlfile WHERE name like '+&ASMGROUP%'
    UNION ALL
 SELECT upper(member) files, 'ONLINELOG' type FROM v$logfile WHERE member like '+&ASMGROUP%'
)
);

FILES
--------------------------------------------------------------------------------
rm +DGDATA/O01XXXXX/CONTROLFILE/CURRENT.800.807811333

So then I just need to run from asmcmd:
ASMCMD> rm +DGDATA/O01XXXXX/CONTROLFILE/CURRENT.800.807811333

Because WITH clause does not work on MOUNTED databases (like a standby) – SQL Statement Using ‘WITH’ Clause Fails with ORA-01219 [ID 1166073.1] , here the version without the WITH clause:

DEFINE ASMGROUP="DGDATA"
 
set linesize 200
set pagesize 50000
col reference_index noprint
col type format a15
col files format a80

SELECT 'rm '|| files files FROM -- this line show the delete command
(
  SELECT '+&ASMGROUP'||files files, type 
  FROM (SELECT upper(sys_connect_by_path(aa.name,'/')) files, aa.reference_index, b.type
        FROM (SELECT file_number,alias_directory,name, reference_index, parent_index 
        FROM v$asm_alias) aa,
             (SELECT parent_index FROM (SELECT parent_index 
                    FROM v$asm_alias 
              WHERE group_number = (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP') 
                AND alias_index=0)) a,
             (SELECT file_number, type FROM (SELECT file_number, type 
                                       FROM v$asm_file 
                                    WHERE group_number = (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP'))) b
  WHERE aa.file_number=b.file_number(+)
    AND aa.alias_directory='N'
   -- missing PARAMETERFILE, DATAGUARDCONFIG
   AND b.type in ('DATAFILE','ONLINELOG','CONTROLFILE','TEMPFILE')
  START WITH aa.PARENT_INDEX=a.parent_index
  CONNECT BY PRIOR aa.reference_index=aa.parent_index)
  WHERE substr(files,instr(files,'/',1,1),instr(files,'/',1,2)-instr(files,'/',1,1)+1) = (select dbname FROM (SELECT '/'||upper(db_unique_name)||'/' dbname from v$database))
MINUS (
  SELECT upper(name) files, 'DATAFILE' type FROM v$datafile
    UNION ALL 
  SELECT upper(name) files, 'TEMPFILE' type FROM v$tempfile
    UNION ALL
 SELECT upper(name) files, 'CONTROLFILE' type FROM v$controlfile WHERE name like '+&ASMGROUP%'
    UNION ALL
 SELECT upper(member) files, 'ONLINELOG' type FROM v$logfile WHERE member like '+&ASMGROUP%'
)
);