Database duplication

There is a decent share of blogs and articles about database duplication but I thought its worth to give my share too. Recently I did quite simple database copy for testing purposes and I ran into a few bumps so I thought it might be interesting.

Goal was, as usual, to create a duplicate database with different SID. In my case the problem was little more complex since source database was RAC and we wanted the new one to be also. As of now only way to do that is to copy you database as single and then convert it to RAC. But we will not go into that today.

So, let’s start:

Create PFILE (initnewSID.ora) for your auxiliary database

Important parameter are:

db_name – Axiliary database SID
db_block_size – Database block size
db_create_file_dest – File destination for OMF
db_file_name_convert – Conversion of location for files
log_file_name_convert – Conversion of location for redlogs

Mine looked like this:

db_name=newSID
db_block_size=8192
db_create_file_dest=+DATA3
db_file_name_convert=(+DATA,+DATA3)
log_file_name_convert=(+DATA,+DATA3)
compatible='11.2.0.3'

You can get RMAN-06136: ORACLE error from auxiliary database: ORA-00201: control file version 11.2.0.3.0 incompatible with ORACLE version 11.2.0.0.0 error, if you are using compatible parameter in your target database. Then you’ll have to add it into your auxiliary PFILE

Create password file

$ orapwd file=orapwnewSID password=MyPassworrd entries=20

Start auxiliary database in nomount

$ sqlplus / as sysdba
SQL> startup nomount

Start target database in mount

$ sqlplus / as sysdba
SQL> startup mount;

Now, before you move forward, I do suggest you edit tnsnames.ora and listener.ora to register your auxiliary database

Example:

cd /opt/oracle/product/11.2.0/dbhome_4/network/admin
vi tnsnames.ora

newSID =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
  (CONNECT_DATA =
  (SERVER = DEDICATED)
   (SERVICE_NAME = newSID.mydomain.com)
  )
 )

vi listener.ora

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = newSID.mydomain.com)
   (SID_NAME = SCVON)
   (ORACLE_HOME = /opt/oracle/product/11.2.0/dbhome_4)
  )
 )

$ lsnrctl reload

Run rman and connect to both target and auxiliary database

$ rman nocatalog
RMAN> connect target sys@oldSID
RMAN> connect auxiliary sys@newSID

Start duplication

duplicate target database to newSID from active database;

If you get error like ORA-01103: database name 'oldSID' in control file is not 'newSID', when starting new database, just check your PFILE, if it did not get messed up by rman. If so, just correct the values in PFILE and try to restart your new database.

ORA-22804: remote operations not permitted on object tables or user-defined type columns

Just a quick post today, recently I was extending advanced replications with table containing varray. Creation of remote materialized view failed with

ORA-22804: remote operations not permitted on object tables or user-defined type columns

I found Domagoj’s blog post very helpful and it did the trick. So if you have same problem, I would suggest to you to go and check it out: https://blog.dsl-platform.com/query-user-defined-types-over-database-link/

I'm speaker at DOAG 2015

Facepalm is comming to DOAG 2015! :)

I'm happy to announce that I'll be presenting "Think simple and spare yourself a facepalm" session at developer track at DOAG 2015. Looking forward to meet you there!

Parallel facepalm

If you are used to parallel execution in Oracle, you’ve definitely read through their documentation on the matter ones or twice (http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel003.htm#i1006712) … I did. And in that in mind I was bashing my head against this simple query:

UPDATE /*+ parallel(d) */ my_table d SET d.flag = 'A';

Table is partitioned by range with 38 partitions. So there is nice opportunity to run this DML in parallel from read to write. So I’ve set up the environment …

ALTER SESSION ENABLE PARALLEL DML;

Checked execution plan and …

----------------------------------------------- | Id  | Operation             | Name          | ----------------------------------------------- |   0 | UPDATE STATEMENT      |               | |   1 |  UPDATE               | PBVADAVKY_VZP | |   2 |   PX COORDINATOR      |               | |   3 |    PX SEND QC (RANDOM)| :TQ10000      | |   4 |     PX BLOCK ITERATOR |               | |   5 |      TABLE ACCESS FULL| MY_TABLE     | -----------------------------------------------

Ok, how about forcing it.

ALTER SESSION FORCE PARALLEL DML PARALLEL 4;

----------------------------------------------- | Id  | Operation             | Name          | ----------------------------------------------- |   0 | UPDATE STATEMENT      |               | |   1 |  UPDATE               | PBVADAVKY_VZP | |   2 |   PX COORDINATOR      |               | |   3 |    PX SEND QC (RANDOM)| :TQ10000      | |   4 |     PX BLOCK ITERATOR |               | |   5 |      TABLE ACCESS FULL| MY_TABLE     | -----------------------------------------------

Ok …. We’ll …. After 1-2 hours …. Long story short:

SELECT trigger_name, trigger_type from DBA_TRIGGERS WHERE table_name='MY_TABLE';

TRIGGER_NAME                   TRIGGER_TYPE ------------------------------ ---------------- MY_TABLE_UPD              BEFORE EACH ROW MY_TABLE_DEL              BEFORE EACH ROW MY_TABLE_INS              BEFORE EACH ROW

So remember folks, you should remember your data model ... and If you don’t …. have a proper look first (http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel003.htm#BEICCEJJ).

ORA-00600: internal error code [kkmupsViewDestFro_4]

After an upgrade to Oracle 11.2.0.4 (from 11.2.0.3), one of our batch jobs in test environment starting crushing on following error:

SQL Error: ORA-00600: internal error code [kkmupsViewDestFro_4], [0], [8024169], [], [], [], [], [], [], [], [], []
00600. 00000 -  "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
*Cause:    This is the generic internal error number for Oracle program
               exceptions. This indicates that a process has encountered an
               exceptional condition.
*Action:   Report as a bug - the first argument is the internal error number

SQL had nothing special in it:

MERGE INTO TABLE1 a USING
   (SELECT d.*
   FROM TABLE2 d,
            TABLE3 t
    WHERE d.cola   = t.cola
    AND d.colb   = t.colb
   ) b ON (a.col1 = b.col1 AND a.col2 = b.col2)
WHEN MATCHED THEN
  UPDATE
  SET ….
  WHERE a.SCN <= b.SCN
WHEN NOT MATCHED THEN
  INSERT
    (
      …
    )
    VALUES
    (
      …
    )

I searched through Oracle My Support and found article ORA-600 [kkmupsViewDestFro_4] During Merge Statement (Doc ID 1181833.1): 

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=534519724153934&id=1181833.1&_adf.ctrl-state=b2we448s9_695

One of the solution was to upgrade to Oracle 11.2.0.2 … Hmmmm … well, that didn’t work out :)

So I tried next one:

alter session set "_optimizer_join_elimination_enabled"=false;

Nope … and next one:

alter session set "_fix_control"="7679164:OFF";

Nope … and next one … recode the MERGE statement, for example add ROWNUM pseudo column:

MERGE INTO TABLE1 a USING
   (SELECT d.*, ROWNUM
   FROM TABLE2 d,
            TABLE3 t
    WHERE d.cola   = t.cola
    AND d.colb   = t.colb
   ) b ON (a.col1 = b.col1 AND a.col2 = b.col2)
WHEN MATCHED THEN
  UPDATE
  SET ….
  WHERE a.SCN <= b.SCN
WHEN NOT MATCHED THEN
  INSERT
    (
      …
    )
    VALUES
    (
      …
    )

And voilà … it’s working!

I still don’t understand why we got the error in 1st place … should have been fixed in 11.2.0.2

How to estimate space needed for archive logs

We are back in LinkedIn and this time in Oracle senior DBA group discussion. The question is:

How to calculate necessary space on File System for put in archive log mode an database oracle 11g, currently is not in archive log mode, thanks

Space needed for your archive log location(s) can be estimated by following script, which gets average redo log file size (which should be same for all files BTW) and basically multiplies that by number of log file switches per day:

SELECT log_hist.*,
ROUND(log_hist.num_of_log_swithes * log_file.avg_log_size / 1024 / 1024) avg_mb
FROM
(SELECT TO_CHAR(first_time,'DD.MM.YYYY') DAY,
COUNT(1) num_of_log_swithes
FROM v$log_history
GROUP BY TO_CHAR(first_time,'DD.MM.YYYY')
ORDER BY DAY DESC
) log_hist,
(SELECT AVG(bytes) avg_log_size FROM v$log) log_file;

Archive log files usually go into same location where your “hot” backups go, like FRA. Space needed there depends on your backup strategy and retention policy. If you are deleting archive log files after each successful full or incremental backup, space needed should be count of days between backups plus some good reserve. If you are backing archive log files from archive log destination to some other device, than you should estimate needed space based on that.

ORA-12008: error in materialized view refresh path

This was just one of those days, when strange things happen. Consider this:

You have a materialized view with left outer join:

CREATE MATERIALIZED VIEW table_mw
      REFRESH FAST ON COMMIT
      WITH ROWID
      AS
SELECT
      v.ROWID v_rid,
      t.ROWID t_rid,
      s.ROWID s_rid,
      v.*,
      s.dfr,
      s.dfr
FROM
      table_v v,
      table_t t,
      table_s s
WHERE v.ref_id = t.id (+)
      AND v.ref_id = s.id (+)
/

CREATE MATERIALIZED VIEW LOG ON table_v WITH ROWID
/

CREATE MATERIALIZED VIEW LOG ON table_t WITH ROWID
/

CREATE MATERIALIZED VIEW LOG ON table_s WITH ROWID
/

Now, let’s keep aside that not only you have to comply with documented rules for view to be fast refreshable (http://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm#i1006674), there is also one undocumented restriction: ANSI-joins are not supported.

With that solved, we did a simple UPDATE of one row in table TABLE_V. Everything looked fine, until we wanted to commit. Commit returned following error stack:

ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist

Cause:  Table SNAP$_<mview_name> reads rows from the view MVIEW$_<mview_name>, which is a view on the master table (the master may be at a remote site). Any error in this path will cause this error at refresh time. For fast refreshes, the table <master_owner>.MLOG$_<master> is also referenced.

Action: Examine the other messages on the stack to find the problem. See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>, <mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink> still exist.

I checked alert log and trace files to find nothing of use there. After some experimentation I found out that for some reason, Oracle cannot cope with timestamp-based materialized view logs. Implementation of commit SCN-based materialized view logs solved the problem.

So in the end materialized view logs looked like this:

CREATE MATERIALIZED VIEW LOG ON table_v WITH ROWID, COMMIT SCN
/

CREATE MATERIALIZED VIEW LOG ON table_t WITH ROWID, COMMIT SCN
/

CREATE MATERIALIZED VIEW LOG ON table_s WITH ROWID, COMMIT SCN
/

Seems like one of those problems you solve, but don’t know why they came in first place.

PS: There is also one important think you should not forget or you'll get same generic error on COMMIT of one of the source tables. The thing is that if you materialized view is in different schema, don't forget to also GRANT SELECT on materialized view logs (MLOG$, RUPD$).