Thinking Out Loud

May 28, 2014

Headless VirtualBox on Windows 7

Filed under: VirtualBox — mdinh @ 1:04 am

Recently I came to know about Headless features for VirtualBox and was curious as to how it works.

When using Headless, there is not a need to start VirtualBox GUI which is nice and removes clutters from Desktop.

Here’s a demo on how to start VirtualBox Headless.

List available VMs.

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\dinh>cd "c:\Program Files\Oracle\VirtualBox"

c:\Program Files\Oracle\VirtualBox>VBoxManage.exe list vms
"arrow" {202d474e-7b00-4fce-a38a-844a650545b8}

Start VM Headless.

c:\Program Files\Oracle\VirtualBox>VBoxManage.exe startvm arrow --type headless
Waiting for VM "arrow" to power on...
VM "arrow" has been successfully started.

c:\Program Files\Oracle\VirtualBox>exit

Test connection to VM.
VM was shutdown in the next step while connected.

dinh@CMWPHV1 ~
$ ssh oracle@192.168.56.11
oracle@192.168.56.11's password:
Last login: Tue May 27 17:34:30 2014 from 192.168.56.1
The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:san:/home/oracle
$
Broadcast message from root@arrow.localdomain
        (unknown) at 17:38 ...

The system is going down for halt NOW!

Broadcast message from root@arrow.localdomain
        (unknown) at 17:38 ...

The system is going down for halt NOW!
Connection to 192.168.56.11 closed by remote host.
Connection to 192.168.56.11 closed.

dinh@CMWPHV1 ~
$

Shutdown VM.

c:\Program Files\Oracle\VirtualBox>VBoxManage.exe controlvm arrow acpipowerbutton

May 23, 2014

Rolling forward a standby database using RMAN Incremental Backup

Filed under: 11g,Dataguard,oracle — mdinh @ 10:24 pm

I am not going to post on how to roll forward standby database as it has been written in many blogs already.

Instead, I am going to share what I have learned how it may have been done incorrectly using the wrong SCN.

Typically, select current_scn from v$database should work until it doesn’t.

But why take the chance and why not do it right the first time.

In reviewing Steps to perform for Rolling forward a standby database using RMAN Incremental Backup. (Doc ID 836986.1)

SQL> select current_scn from v$database;

-- If no READ ONLY Tablespaces/datafiles in database use
-- Both should be the same
SQL> select min(fhscn) from x$kcvfh;
OR
SQL> select min(checkpoint_change#) from v$datafile_header;

-- If READ ONLY tablespaced/datafiles in database use
SQL> select min(f.fhscn) from x$kcvfh f, v$datafile d where f.hxfil =d.file# and d.enabled != 'READ ONLY';

In ideal situation the above 2 queries will return the almost same SCN.
However if there is huge difference its better to take backup using the SCN from second or third query (lesser SCN), as one of the datafile may be behind.

Here is a demo:

SYS@san> select current_scn from v$database;
----------------
1005703

SYS@san> select min(fhscn) from x$kcvfh;

MIN(FHSCN)
—————-
1004302

SYS@san> select min(checkpoint_change#) from v$datafile_header;

MIN(CHECKPOINT_CHANGE#)
———————–
1004302

SYS@san> select min(f.fhscn) from x$kcvfh f, v$datafile d where f.hxfil =d.file# and d.enabled != ‘READ ONLY’;

MIN(F.FHSCN)
—————-
1004302

SYS@san>

May 4, 2014

GoldenGate 12c Features Found in 11.2.1.0.21 ???

Filed under: GoldenGate — mdinh @ 4:20 pm

I was curious if there was a way to add supplemental logging for ALL columns at the schema level.

Oracle GoldenGate Release Notes 12c (12.1.2) for Windows and UNIX April 2014

ADD SCHEMATRANDATA and ADD TRANDATA have been enhanced to support integrated Replicat for Oracle.

A new ALLCOLS option enables the unconditional logging of all of the key and non-key columns of a table.

Sounds great, but I am not on 12c and my curiosity got to me if this can be done for Oracle GoldenGate 11.2

Based on Oracle GoldenGate — Oracle RDBMS Server Recommended Patches (Doc ID 1557031.1), I had the following configurations set up

Oracle Linux Server release 6.5

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Patch  18095555     : applied on Sat Apr 26 10:29:11 PDT 2014
Patch  17478514     : applied on Sat Apr 26 10:27:19 PDT 2014
Patch description:  "Database Patch Set Update : 11.2.0.4.1 (17478514)"

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.21 18343248 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140404.1029_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr  4 2014 15:18:36

Here goes nothing!

oracle@arrow:san:/u01/app/ggs01
$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.21 18343248 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140404.1029_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr  4 2014 15:18:36

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

GGSCI (arrow.localdomain) 1> dblogin userid ggs
Password:
Successfully logged into database.

GGSCI (arrow.localdomain) 2> info schematrandata soe

2014-05-04 08:27:00  INFO    OGG-01786  Schema level supplemental logging is disabled on schema SOE.
08:26:49 ARROW:(SYS@san):PRIMARY> select * from table(logmnr$always_suplog_columns('SOE','ADDRESSES')) order by intcol;

no rows selected

08:27:52 ARROW:(SYS@san):PRIMARY> 

GGSCI (arrow.localdomain) 3> add schematrandata soe

2014-05-04 08:28:12 INFO OGG-01788 SCHEMATRANDATA has been added on schema soe.

08:27:52 ARROW:(SYS@san):PRIMARY> r
  1* select * from table(logmnr$always_suplog_columns('SOE','ADDRESSES')) order by intcol

OWNER      TABLE_NAME COLUMN_NAME              INTCOL     SEGCOL    USERCOL
---------- ---------- -------------------- ---------- ---------- ----------
SOE        ADDRESSES  ADDRESS_ID                    1          1          1

08:28:15 ARROW:(SYS@san):PRIMARY> 

GGSCI (arrow.localdomain) 4> add schematrandata soe ALLCOLS

2014-05-04 08:28:26 INFO OGG-01788 SCHEMATRANDATA has been added on schema soe.

2014-05-04 08:28:26 INFO OGG-01977 SCHEMATRANDATA for all columns has been added on schema soe.

08:28:15 ARROW:(SYS@san):PRIMARY> r
  1* select * from table(logmnr$always_suplog_columns('SOE','ADDRESSES')) order by intcol

OWNER      TABLE_NAME COLUMN_NAME              INTCOL     SEGCOL    USERCOL
---------- ---------- -------------------- ---------- ---------- ----------
SOE        ADDRESSES  ADDRESS_ID                    1          1          1
SOE        ADDRESSES  CUSTOMER_ID                   2          2          2
SOE        ADDRESSES  DATE_CREATED                  3          3          3
SOE        ADDRESSES  HOUSE_NO_OR_NAME              4          4          4
SOE        ADDRESSES  STREET_NAME                   5          5          5
SOE        ADDRESSES  TOWN                          6          6          6
SOE        ADDRESSES  COUNTY                        7          7          7
SOE        ADDRESSES  COUNTRY                       8          8          8
SOE        ADDRESSES  POST_CODE                     9          9          9
SOE        ADDRESSES  ZIP_CODE                     10         10         10

10 rows selected.

08:28:32 ARROW:(SYS@san):PRIMARY>

GGSCI (arrow.localdomain) 5> exit

Verify supplemental logging for ALL COLS when new table is created.

08:28:32 ARROW:(SYS@san):PRIMARY> create table soe.t as select * from soe.addresses;

Table created.

08:28:48 ARROW:(SYS@san):PRIMARY> select * from table(logmnr$always_suplog_columns('SOE','ADDRESSES')) order by intcol;

OWNER      TABLE_NAME COLUMN_NAME              INTCOL     SEGCOL    USERCOL
---------- ---------- -------------------- ---------- ---------- ----------
SOE        ADDRESSES  ADDRESS_ID                    1          1          1
SOE        ADDRESSES  CUSTOMER_ID                   2          2          2
SOE        ADDRESSES  DATE_CREATED                  3          3          3
SOE        ADDRESSES  HOUSE_NO_OR_NAME              4          4          4
SOE        ADDRESSES  STREET_NAME                   5          5          5
SOE        ADDRESSES  TOWN                          6          6          6
SOE        ADDRESSES  COUNTY                        7          7          7
SOE        ADDRESSES  COUNTRY                       8          8          8
SOE        ADDRESSES  POST_CODE                     9          9          9
SOE        ADDRESSES  ZIP_CODE                     10         10         10

10 rows selected.

08:29:10 ARROW:(SYS@san):PRIMARY> select * from table(logmnr$always_suplog_columns('SOE','T')) order by intcol;

OWNER      TABLE_NAME COLUMN_NAME              INTCOL     SEGCOL    USERCOL
---------- ---------- -------------------- ---------- ---------- ----------
SOE        T          ADDRESS_ID                    1          1          1
SOE        T          CUSTOMER_ID                   2          2          2
SOE        T          DATE_CREATED                  3          3          3
SOE        T          HOUSE_NO_OR_NAME              4          4          4
SOE        T          STREET_NAME                   5          5          5
SOE        T          TOWN                          6          6          6
SOE        T          COUNTY                        7          7          7
SOE        T          COUNTRY                       8          8          8
SOE        T          POST_CODE                     9          9          9
SOE        T          ZIP_CODE                     10         10         10

10 rows selected.

08:29:37 ARROW:(SYS@san):PRIMARY>

Snipit from ggserr.log

oracle@arrow:san:/u01/app/ggs01
$ cat ggserr.log
2014-05-04 08:26:45  INFO    OGG-00948  Oracle GoldenGate Manager for Oracle, mgr.prm:  Lag for REPLICAT RLAS_SAN is 00:00:00 (checkpoint updated 89:00:47 ago).
2014-05-04 08:26:45  INFO    OGG-00948  Oracle GoldenGate Manager for Oracle, mgr.prm:  Lag for EXTRACT ESAN is 00:00:00 (checkpoint updated 00:00:03 ago).
2014-05-04 08:26:45  INFO    OGG-00948  Oracle GoldenGate Manager for Oracle, mgr.prm:  Lag for EXTRACT PSAN_LAS is 00:00:00 (checkpoint updated 89:00:47 ago).
2014-05-04 08:27:00  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): info schematrandata soe.
2014-05-04 08:27:00  INFO    OGG-01786  Oracle GoldenGate Command Interpreter for Oracle:  Schema level supplemental logging is disabled on schema SOE.
2014-05-04 08:28:12  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): add schematrandata soe.
2014-05-04 08:28:12  INFO    OGG-01788  Oracle GoldenGate Command Interpreter for Oracle:  SCHEMATRANDATA has been added on schema soe.
2014-05-04 08:28:26  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): add schematrandata soe ALLCOLS.
2014-05-04 08:28:26  INFO    OGG-01788  Oracle GoldenGate Command Interpreter for Oracle:  SCHEMATRANDATA has been added on schema soe.
2014-05-04 08:28:26  INFO    OGG-01977  Oracle GoldenGate Command Interpreter for Oracle:  SCHEMATRANDATA for all columns has been added on schema soe.
2014-05-04 08:28:51  INFO    OGG-01487  Oracle GoldenGate Capture for Oracle, esan.prm:  DDL found, operation [create table soe.t as select * from soe.addresses  (size 50)], start SCN [642971], commit SCN [642994] instance [san (1)], DDL seqno [2806], marker seqno [2806].
2014-05-04 08:28:51  INFO    OGG-00487  Oracle GoldenGate Capture for Oracle, esan.prm:  DDL operation included [INCLUDE ALL], optype [CREATE], objtype [TABLE], objowner [SOE], objname [T].
2014-05-04 08:28:51  INFO    OGG-00497  Oracle GoldenGate Capture for Oracle, esan.prm:  Writing DDL operation to extract trail file.
oracle@arrow:san:/u01/app/ggs01
$

For GoldenGate version 11.2.1.0.21, ADD SCHEMATRANDATA ALLCOLS works and ADD TRANDATA ALLCOLS does not work.

If ADD SCHEMATRANDATA ALLCOLS does not work for versions less than 11.2.1.0.21, then try EXECUTE DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(‘SOE’,’ALL’);

Create a free website or blog at WordPress.com.