Thinking Out Loud

August 2, 2014

RMAN Pet Peeves

Filed under: 11g,oracle,RMAN — mdinh @ 6:38 pm

Do you validate your backup and what command do you use?

Lately, I have been using restore database validate preview summary to kill 2 birds with 1 stone.

The issue is RMAN will skip validation of archived log backupset when archived log exists.

Does this seem wrong to you?

Please take a look at a test case here

What do you think?

July 15, 2014

Create Physical Standby Database using RMAN Restore

Filed under: 11g,Dataguard,oracle,RMAN — mdinh @ 3:19 am

Normally, when I create physical standby database, the configuration has the same directory structures and name values as production with the exception of db_unique_name.

But this time was not the case as shown below.

ANGEL:(SYS@xmenstby):PHYSICAL STANDBY> show parameter name

NAME                      TYPE        VALUE
------------------------- ----------- ----------------------------------------
cell_offloadgroup_name    string
db_file_name_convert      string      /oradata/xmenprod, /oradata/xmenstby
db_name                   string      xmenprod
db_unique_name            string      angel_xmenstby
global_names              boolean     FALSE
instance_name             string      xmenstby
lock_name_space           string
log_file_name_convert     string      /oradata/xmenprod, /oradata/xmenstby
processor_group_name      string
service_names             string      xmenstby
ANGEL:(SYS@xmenstby):PHYSICAL STANDBY>

I have not been accustomed to adding suffixes such as prod, stby, qa, dev, uat, etc… to database name.

Hopefully, when a connection is made to QA server, it’s for a QA database and not PROD.

Enough of the rant, the requirement is to create physical standby with different directory structures and ORACLE_SID at standby site is xmenstby.

The format I have been using is to prefix db_name with closest airport code to the data center to create db_unique_name.

Alternatively is to prefix with hostname.

Active database duplication is not an option because concern it may take a long time.

It was suggested to perform RMAN backup on primary, transfer backup to standby server using multiple scp, and restore database.

Here I go and if you are interested in how this turned out, then please read more about it here

UPDATE: July 18, 2014

If the intention is to know the primary is now a standby and vice versa  after a switchover, then naming the db with the environment will achieve this.

DGMGRL> show configuration

Configuration - dg_xmen

  Protection Mode: MaxPerformance
  Databases:
    xmenprod       - Primary database
    angel_xmenstby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> switchover to angel_xmenstby
Performing switchover NOW, please wait...
Operation requires a connection to instance "xmenstby" on database "angel_xmenstby"
Connecting to instance "xmenstby"...
Connected.
New primary database "angel_xmenstby" is opening...
Operation requires startup of instance "xmenprod" on database "xmenprod"
Starting instance "xmenprod"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "angel_xmenstby"
DGMGRL> show configuration verbose

Configuration - dg_xmen

  Protection Mode: MaxPerformance
  Databases:
    angel_xmenstby - Primary database
    xmenprod       - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

June 27, 2014

Employees Who Stay In Companies Longer Than Two Years Get Paid 50% Less

Filed under: Uncategorized — mdinh @ 3:39 am

I came across an interesting article Employees Who Stay In Companies Longer Than Two Years Get Paid 50% Less and thought to share with you.

Let’s use the rule of 72 as an example.

Basically, the rule of 72 is a simple method of determining the number of years an investment will double by dividing 72 by the rate of return.

With a 4% annual salary increase, salary will double in 18 years (72/4).

With an average 6% annual salary increase, salary will double in 12 years (72/6).

What’s the likelihood of getting an average 6% annual increase for 12 years while remaining at the same company? Next to none!

It’s definitely possible to double salary in 12 years depending on the starting point.

For salary starting with $40k is easier to achieve versus starting salary of $100k, per se.

 

 

 

June 17, 2014

Priceless

Filed under: Uncategorized — mdinh @ 12:51 pm

Another wager lost to my daughter, this time an iPhone 5s.
Thank God she does not want the 6.

Learning the alphabets in school, A is for apple.
Earning straight A’s all the way through 8th grade.
Having dad pay for all the Apple toys – Priceless!
priceless

June 7, 2014

Learning RMAN is like learning to ride a bike

Filed under: 11g,oracle,RMAN — mdinh @ 12:13 am

For almost five years, I have used RMAN very minimally.

Luckily, learning RMAN is like learning to ride a bike. One may not be able to perform wheelie, but one does not forget.

Please allow me to share the details on how to restore database to a new server, new database name, and new location using RMAN.

Summary:

At source, database is using OMF and backup is located at /oradata/backup.
At target, database is not using OMF and backup is copied from source to /oradata/bkup.

Database was renamed from SAN to DB02.

Detail’s here

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’);

April 23, 2014

Missing Password for Database Link Bug

Filed under: 11g,oracle — mdinh @ 3:50 am

So there I was, working on another database duplication project, the requirement is to save the existing database links.

Sounds pretty easy, right?

SELECT OWNER, DB_LINK, DBMS_METADATA.GET_DDL('DB_LINK',DB_LINK,OWNER) as DDL FROM DBA_DB_LINKS;

Wrong and I know why I am getting bald. Pulling my hair out.

After searching for hours, I found DBMS_METADATA.GET_DDL database link password missing

Another 11.2.0.4 Bug.

I believe the bug was introduced when I modified user’s password as shown below since  everything was working fine just hours ago.

alter user SCOTT identified by values 'S:EDCCC6A91707D978B7D49476BCA228BC7D702C135557F41154ACBF744645;F894844C34402B67';

Got desperate and restored the database which did not help.

Now what and how is one suppose to save database links info?

Find out more here

March 5, 2014

Automating DataPump Export

Filed under: 11g,DataPump,oracle — mdinh @ 12:21 am

Please click link here to read my blog at Pythian.

Next Page »

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 302 other followers