Yearly Archives: 2013


Goldengate, RAC One node and PROCESSTHREADS parameter

I use Goldengate on classic capture mode to replicate several RAC One Node databases to an external system.
Since the first time there was a relocation of the RAC One Node database, there are two redo log threads (because the DB was temporarily a 2-node RAC).

Now the current thread# has changed and GoldenGate complaints that he cannot access anymore to the redologs.

What was necessary to do was:

1. Check which is the current thread:

SQL> select thread# from v$log where status=’CURRENT’; –> current=1

2. Add to the extract parameter file the parameter:
THREADOPTIONS PROCESSTHREADS EXCEPT 1

3. Recreate the extract with:

delete extract e_xx
add extract e_xx tranlog threads 2 begin now
add exttrail /oraacfs/goldengate/gg/dirdat/e_xx/xx extract e_xx

4. Check with
info e_xx
that the RBA for one of the threads increases with the time.

All this should not be needed when running in integrated capture mode.


GoldenGate – SCN translation

On GoldenGate when you do “SEND <extract> status” you get a SCN field which does not correspond to the database SCN:
GGSCI> send e_test status
Sending STATUS request to EXTRACT E_TEST …
EXTRACT E_TEST (PID 9372244)
Current status: Recovery complete: Processing data
Current read position:
Redo thread #: 1
Sequence #: 198315
RBA: 335376
Timestamp: 2013-11-11 16:11:57.000000
SCN: 78.924145222
Current write position:
Sequence #: 1548
RBA: 401683
Timestamp: 2013-11-11 16:12:31.811533
Extract Trail: /acfs/goldengate/dirdat/test/tt


SQL> select current_scn from v$database;
CURRENT_SCN
———————-
335931612762


To translate that SCN into the database SCN there is the calculation to make:


78 * (2^32) + 924145222 = 335931594310or with a hexadecimal translation:
78 to hexa        ==> 4E
924145222 to hexa ==> 37155646

Concatenate both and then
4E37155646 to decimal ==> 335931594310

Well, with the most recent versions of GoldenGate you can simply to “INFO <extract>” and you get the database SCN between parentheses.

GGSCI> info e_test
EXTRACT  E_TEST  Last Started 2013-11-13 08:08   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  Oracle Redo Logs
                  2013-11-13 09:57:40  Seqno 200447, RBA 16256592
                  SCN 78.1163096680 (336170545768)

Goldengate – Refresh single table

With the recent versions of GoldenGate >11.2 you can see the current SCN on with the extract process is reading, even when it is stopped:

GGSCI> INFO E_TEST
EXTRACT  E_TEST  Last Started 2013-11-13 03:50   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint  Oracle Redo Logs
                   2013-11-13 09:32:34  Seqno 508717, RBA 4366336
                   SCN 78.1156360444 (336163809532)

This makes it easiear to refresh a single table on a GoldenGate replication, if you can allow a small delay. There can be this need if you discover that it does not match anymore, or if you need to add a new table to the configuration. For that you do, in case of a Oracle to Oracle replication:
1 – Stop extract process and do INFO <extract> (see above). Note down the SCN between parentheses.
2 – Add the table to the parameter file (or remove an existing TABLEEXCLUDE on extract process or MAPEXCLUDE on replicat process) 
3 – Export the table from the source with FLASHBACK_SCN parameter pointing to the SCN taken from parameter 1.
4 – Make sure the replicat process has no delay with the command LAG <replicat>. You should get “At EOF, no more records to process.”
GGSCI> LAG R_TEST
Sending GETLAG request to REPLICAT R_TEST …
Last record lag: 4 seconds.
At EOF, no more records to process.
5 – Import the table at the destination (with TABLE_EXISTS=REPLACE, if needed)
6 – Re-start the extract process.
You can make the operation simpler if you have a database link between destination and source. In that case you skip the step 3 and use directly the FLASHBACK_SCN on step 5:
impdp NETWORK_LINK=source_db TABLES=test.table1 EXCLUDE=TRIGGER 

Oracle DBA role granted privileges per version

Below you can find the Oracle defaults
DBA role privileges per database version since Oracle 10.2. It is based on Enterprise Edition with Java and XML installed (on 12.1 you cannot anymore choose what to install).
On Oracle
12.1 there are 3 new roles : CAPTURE_ADMIN, EM_EXPRESS_ALL and
OPTIMIZER_PROCESSING_RATE. Comments about these new roles/privileges can be found below.

admin_option
comment
CAPTURE_ADMIN
yes
>12.1
Provides
the privileges necessary to create and manage privilege analysis policies.
DATAPUMP_EXP_FULL_DATABASE
>11.1
DATAPUMP_IMP_FULL_DATABASE
>11.1
DELETE_CATALOG_ROLE
yes
EM_EXPRESS_ALL
>12.1
Enables
users to connect to Oracle Enterprise Manager (EM) Express and use all the
functionality provided by EM Express (read and write access to all EM Express
features). The EM_EXPRESS_ALL role includes the EM_EXPRESS_BASIC role
EXECUTE_CATALOG_ROLE
yes
EXP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
IMP_FULL_DATABASE
JAVA_ADMIN
JAVA_DEPLOY
OPTIMIZER_PROCESSING_RATE
>12.1
Provides privileges to execute the
GATHER_PROCESSING_RATE, SET_PROCESSING_RATE, and DELETE_PROCESSING_RATE
procedures in the DBMS_STATS package. These procedures manage the
processing rate of a system for automatic degree of parallelism (Auto DOP).
Auto DOP uses these processing rates to determine the optimal degree of
parallelism for a SQL statement.
SCHEDULER_ADMIN
yes
SELECT_CATALOG_ROLE
yes
WM_ADMIN_ROLE
XDBADMIN
XDB_SET_INVOKER
>11.1
XDBWEBSERVICES
<10.2

The query used is:
set pages 20 lines 200
col grantee for a10
col granted_role for a40
select * from dba_role_privs where grantee=’DBA’ order by 1,2;

Duplicate problem with non standard cache sizes

Today I had a problem creating a standby database. Just after RMAN shutdowns and restarts the standby for copying the files, I was getting:

RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-04014: startup failed: ORA-04031: unable to allocate 4128 bytes of sha
red memory (“shared pool”,”unknown object”,”sga heap(1,0)”,”object queue ha
sh buckets”)

After investigating the problem was because I was decreasing the size of SGA on the target database (and putting it on Automatic Memory Management) but not decreasing the non-standard cache settings.
The solution was to add to the duplicate command the decrease of the non-standard cache (on the source it is 800M):
  SET memory_target ‘1G’
  SET db_32k_cache_size ’50M’

Find Facebook rss feed without Facebook account

I belong to the group of people who does not have a Facebook account. However, unfortunately, several companies now do only publish information on their Facebook wall.

I’m already happy that most of them have their wall public, so I can check it without account. But in some cases I would like to follow the company wall with Google Reader.

So, to find the good RSS feed to subscribe, you need first to find the Facebook id of the account you want to follow. This information you can find from the like button of that company. The like button sends you to an address like:
https://www.facebook.com/ajax/timeline/sign_up_dialog/?page_id=376807926448&next=XXXXX

So the RSS feed to subscribe is:
http://www.facebook.com/feeds/page.php?format=rss20&id=376807926448

Hope that helps someone that, like me, doesn’t have a Facebook account.


Find orphan ASM files 4

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%'
)
);

GoldenGate and Oracle 11.2

Some weeks ago a customer upgraded its databases from 10.2 to 11.2.0.3.

Some time after this change there were GoldenGate errors:

2013-02-03 10:11:55  ERROR   OGG-00519  Oracle GoldenGate Delivery for Oracle, r_xxxx.prm:  Fatal error executing DDL replication: error [Error code [922], ORA-00922: missing or invalid option, SQL create table “XXXXXX”.”DBMS_TABCOMP_TEMP_CMP” organization heap  tablespace “TBS_MAGIC” compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from “XXXXXX”.DBMS_TABCOMP_TEMP_U], no error handler present.
2013-02-03 10:14:55  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_xxxx.prm:  DDL found, operation [create table “XXXXXX”.DBMS_TABCOMP_TEMP_CMP organization heap  tablespace “TBS_MAGIC” compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from “XXXXXX”.DBMS_TABCOMP_TEMP_UNCMP mytab  (size 209)].
2013-02-03 10:14:55  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_xxxx.prm:  DDL is of mapped scope, after mapping new operation [create table “XXXXXX”.”DBMS_TABCOMP_TEMP_CMP” organization heap  tablespace “TBS_MAGIC” compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from “XXXXXX”.DBMS_TABCOMP_TEMP_UNCMP mytab  (size 211)].


After some investigation we found out this is due to the new “feature” Compression Advisor, from Oracle 11.2 (How Does Compression Advisor Work? [1284972.1]).


As it does “create table as select” (CTAS) which GoldenGate does not yet support well, the replicat abended.


There is already one Metalink note on this issue: DDL / Remap Schema : ORA-942 Connected With DBMS_TABCOMP_TEMP_CMP & DBMS_TABCOMP_TEMP_UNCMP [ID 1505178.1].


So we added to the extract parameter files:
TABLEEXCLUDE xxxxx.DBMS_TABCOMP_TEMP*