Search

OakieTags

Who's online

There are currently 0 users and 31 guests online.

Recent comments

Affiliations

Oakies Blog Aggregator

Recovering a standby over the network in 12c

Another one of the cool but underrated features in 12c is the possibility to recover a physical standby over the network with one line in RMAN.

Why do you need to perform this activity? Assume someone really clever created a segment “nologging” and the database was not in force logging mode. This operation cannot be replicated by redo apply on the standby, and you are bound to have a problem. Or, in my case, I had the standby shut down in my lab environment (intentionally) and created a few PDBs on my primary. For some reason I lost an archived redo log. This would of course not happen in a production environment, but my lab VM is limited when it comes to space and I may have moved my backup to a USB disk that I didn’t bring along.

So a long story short: you either recreate the standby-which is a rather blunt-force approach to the problem-or recover the standby from the SCN where the problem originated. I was hoping the new 12c recover database from service command in RMAN could help. It did, but not as I expected at first.

The problem with my lab

My environment is Oracle Linux 6.4 x86-64 on Virtual Box with 4 GB RAM. Oracle is 12.1.0.1.2. CDB1 is primary, CDB2 is my standby database. Both are on the same VM (I only have 8 GB total memory). CDB2 complained about a missing archived redo log:

SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#     CON_ID
---------- ------------- -------------- ----------
         1            27             27          1

Actually this is the first time I ever saw v$archive_gap return something when there was a gap! The issue is also visible in the alert.log

2014-03-12 14:12:29.361000 +00:00
FAL[client]: Failed to request gap sequence
 GAP - thread 1 sequence 27-27
 DBID 793624397 branch 819906960
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
------------------------------------------------------------

And to cut a long story short, I couldn’t get that back as I forgot my USB drive at home. Now another useful 12c feature will warn you that you cannot switch over!

In dgmgrl you can validate the database:

DGMGRL> validate database cdb2;

  Database Role:     Physical standby database
  Primary Database:  cdb1

  Ready for Switchover:  No
  Ready for Failover:    Yes (Primary Running)

  Temporary Tablespace File Information:
    cdb1 TEMP Files:  5
    cdb2 TEMP Files:  3

  Flashback Database Status:
    cdb1:  Off
    cdb2:  On

  Standby Apply-Related Information:
    Apply State:      Running
    Apply Lag:        205 days 3 hours 44 minutes 32 seconds
    Apply Delay:      0 minutes

You can fail over, but not switch over. And indeed, 205 days are rather a long delay… The delay is calculated using sysdate-first_time in v$archived_log for the missing archived redo log.

Also notice that I have fewer temp files in CDB1 compared to CDB2, that’s because I have 2 additional PDBs in CDB1 that are not (yet) in CDB2. OK, so here is my first try to get things back into shape (note that I’m connecting to the standby database as the target):

[oracle@server1 CDB2]$ rman target sys/xxx@cdb2 auxiliary sys/xxx@cdb1
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Mar 12 14:19:24 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB1 (DBID=793624397, not open)
connected to auxiliary database: CDB1 (DBID=793624397)

RMAN> recover database from service cdb1;

...

Starting recover at 12.03.2014 14:19:57
using channel ORA_DISK_1
using channel ORA_DISK_2
skipping datafile 5; already restored to SCN 1735641
skipping datafile 7; already restored to SCN 1735641
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service cdb1
destination for restore of datafile 00001: /u01/oradata/CDB2/system01.dbf
channel ORA_DISK_2: starting incremental datafile backup set restore
channel ORA_DISK_2: using network backup set from service cdb1
destination for restore of datafile 00003: /u01/oradata/CDB2/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service cdb1
destination for restore of datafile 00004: /u01/oradata/CDB2/undotbs01.dbf
channel ORA_DISK_2: restore complete, elapsed time: 00:01:15
channel ORA_DISK_2: starting incremental datafile backup set restore
channel ORA_DISK_2: using network backup set from service cdb1
destination for restore of datafile 00006: /u01/oradata/CDB2/users01.dbf
channel ORA_DISK_2: restore complete, elapsed time: 00:00:03
channel ORA_DISK_2: starting incremental datafile backup set restore
channel ORA_DISK_2: using network backup set from service cdb1
destination for restore of datafile 00008: /u01/oradata/CDB2/MASTER/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:10
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service cdb1
destination for restore of datafile 00009: /u01/oradata/CDB2/MASTER/sysaux01.dbf
channel ORA_DISK_2: restore complete, elapsed time: 00:00:08
channel ORA_DISK_2: starting incremental datafile backup set restore
channel ORA_DISK_2: using network backup set from service cdb1
destination for restore of datafile 00010: /u01/oradata/CDB2/MASTER/MASTER_users01.dbf
channel ORA_DISK_2: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16

starting media recovery

archived log for thread 1 with sequence 28 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_28_9l0tjztf_.arc
archived log for thread 1 with sequence 29 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_29_9l0tk008_.arc
archived log for thread 1 with sequence 30 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_30_9l0tk2tq_.arc
archived log for thread 1 with sequence 31 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_31_9l0tk73b_.arc
archived log for thread 1 with sequence 32 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_32_9l0tk7mt_.arc
archived log for thread 1 with sequence 33 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_33_9l0tkrd1_.arc
archived log for thread 1 with sequence 34 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_34_9l0tkrgv_.arc
archived log for thread 1 with sequence 35 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_35_9l0tkzvo_.arc
archived log for thread 1 with sequence 36 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_36_9l0tkyrm_.arc
archived log for thread 1 with sequence 37 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_37_9l0tkwf6_.arc
archived log for thread 1 with sequence 38 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_38_9l0tl281_.arc
archived log for thread 1 with sequence 39 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_39_9l0tl24w_.arc
archived log for thread 1 with sequence 40 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_40_9l0tl3m0_.arc
archived log for thread 1 with sequence 41 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_41_9l0tl7o2_.arc
archived log for thread 1 with sequence 42 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_42_9l0tlos0_.arc
archived log for thread 1 with sequence 43 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_43_9l0tlng5_.arc
archived log for thread 1 with sequence 44 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_44_9l0trto7_.arc
archived log for thread 1 with sequence 45 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_45_9l0v5ho6_.arc
media recovery complete, elapsed time: 00:00:00
Finished recover at 12.03.2014 14:21:41

RMAN>

How very nice! It did it, or let’s say, it didn’t report any errors. Except that the operation had no effect.

Digging deeper

Here is the true problem. Connected to CDB2 I see these data files:

RMAN> report schema;

using target database control file instead of recovery catalog
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name CDB2

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    790      SYSTEM               ***     /u01/oradata/CDB2/system01.dbf
3    750      SYSAUX               ***     /u01/oradata/CDB2/sysaux01.dbf
4    90       UNDOTBS1             ***     /u01/oradata/CDB2/undotbs01.dbf
5    250      PDB$SEED:SYSTEM      ***     /u01/oradata/CDB2/pdbseed/system01.dbf
6    5        USERS                ***     /u01/oradata/CDB2/users01.dbf
7    590      PDB$SEED:SYSAUX      ***     /u01/oradata/CDB2/pdbseed/sysaux01.dbf
8    260      MASTER:SYSTEM        ***     /u01/oradata/CDB2/MASTER/system01.dbf
9    620      MASTER:SYSAUX        ***     /u01/oradata/CDB2/MASTER/sysaux01.dbf
10   26       MASTER:USERS         ***     /u01/oradata/CDB2/MASTER/MASTER_users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       /u01/oradata/CDB2/temp01.dbf
2    20       PDB$SEED:TEMP        32767       /u01/oradata/CDB2/pdbseed/pdbseed_temp01.dbf
3    20       MASTER:TEMP          32767       /u01/oradata/CDB2/MASTER/temp01.dbf

While for CDB1 I get those. Note there are data files with file# > 10

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name CDB1

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    790      SYSTEM               ***     /u01/oradata/CDB1/system01.dbf
3    750      SYSAUX               ***     /u01/oradata/CDB1/sysaux01.dbf
4    90       UNDOTBS1             ***     /u01/oradata/CDB1/undotbs01.dbf
5    250      PDB$SEED:SYSTEM      ***     /u01/oradata/CDB1/pdbseed/system01.dbf
6    5        USERS                ***     /u01/oradata/CDB1/users01.dbf
7    590      PDB$SEED:SYSAUX      ***     /u01/oradata/CDB1/pdbseed/sysaux01.dbf
8    260      MASTER:SYSTEM        ***     /u01/oradata/CDB1/MASTER/system01.dbf
9    620      MASTER:SYSAUX        ***     /u01/oradata/CDB1/MASTER/sysaux01.dbf
10   26       MASTER:USERS         ***     /u01/oradata/CDB1/MASTER/MASTER_users01.dbf
11   270      VALID:SYSTEM         ***     /u01/oradata/CDB1/valid/system01.dbf
12   610      VALID:SYSAUX         ***     /u01/oradata/CDB1/valid/sysaux01.dbf
13   260      RCAT:SYSTEM          ***     /u01/oradata/CDB1/rcat/system01.dbf
14   610      RCAT:SYSAUX          ***     /u01/oradata/CDB1/rcat/sysaux01.dbf
15   100      RCAT:RMAN_CATALOG    ***     /u01/oradata/CDB1/rcat/rman_catalog01.dbf
16   50       VALID:USERS          ***     /u01/oradata/CDB1/valid/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       /u01/oradata/CDB1/temp01.dbf
2    20       PDB$SEED:TEMP        32767       /u01/oradata/CDB1/pdbseed/pdbseed_temp01.dbf
3    20       MASTER:TEMP          32767       /u01/oradata/CDB1/MASTER/temp01.dbf
4    20       VALID:TEMP           32767       /u01/oradata/CDB1/valid/pdbseed_temp01.dbf
5    20       RCAT:TEMP            32767       /u01/oradata/CDB1/rcat/pdbseed_temp01.dbf

In hindsight it makes sense: the “recover … from service” command did indeed recover the data files it found. But it didn’t create those it didn’t see in the standby database’s controlfile! It was surprising to me at first.

Getting things back to working order

Using the old fashioned way I first have to get those additional data files to my standby database. This requires a backup of the files that didn’t exist at v$archived_log.begin_scn# for sequence# = 27.

In this scenario it is obvious which files are missing but the scientifically correct method of working that out is this command:

SQL> SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 1893106;

     FILE# NAME
---------- ------------------------------------------------------------
        11 /u01/oradata/CDB1/valid/system01.dbf
        12 /u01/oradata/CDB1/valid/sysaux01.dbf
        13 /u01/oradata/CDB1/rcat/system01.dbf
        14 /u01/oradata/CDB1/rcat/sysaux01.dbf
        15 /u01/oradata/CDB1/rcat/rman_catalog01.dbf
        16 /u01/oradata/CDB1/valid/users01.dbf

1893106 is the first change number for archived log 27. Now in the next step you have to create a backup of these files (backup datafile 11,12,13,14,15,16 format … tag …) and make this backup available on the standby host. You also need a new standby controlfile. Back the current controlfile up and ship it over too. Then shut the standby down, and restore the controlfile. Use the RMAN “catalog start with” command to register the backup of your missing data files and restore them. In my case I’m using a file system for the data files so the restore can be scripted:

SQL>  select 'set newname for datafile ' || file# || ' to ''' || replace(name,'CDB1','CDB2') || ''';'
  2   from v$datafile where file# between 11 and 16;

Resulting in this run block + restore commands, executed on the standby:

run {
set newname for datafile 11 to '/u01/oradata/CDB2/valid/system01.dbf';
set newname for datafile 12 to '/u01/oradata/CDB2/valid/sysaux01.dbf';
set newname for datafile 13 to '/u01/oradata/CDB2/rcat/system01.dbf';
set newname for datafile 14 to '/u01/oradata/CDB2/rcat/sysaux01.dbf';
set newname for datafile 15 to '/u01/oradata/CDB2/rcat/rman_catalog01.dbf';
set newname for datafile 16 to '/u01/oradata/CDB2/valid/users01.dbf';
restore datafile 11;
restore datafile 12;
restore datafile 13;
restore datafile 14;
restore datafile 15;
restore datafile 16;
}

The directories referenced here obviously have to exist. For exactly this reason it is probably not a good idea to use a directory on the file system per PDB: if it doesn’t exist on the standby your MRP0 process will fall over, complaining about the missing directory. When using the “report schema” command you should now have an equivalent number of data files on each database.

Getting the standby into sync with the primary the easy way

And NOW it’s time for the recover from service command just to be safe and have the standby on the same level as the primary. For this to work managed recovery has to be switched off. Here is the output in all its glory (the standby database is the target):

[oracle@server1 CDB2]$ rman target sys/xxx@cdb2 auxiliary sys/xxx@cdb1

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Mar 12 15:00:12 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB1 (DBID=793624397, not open)
connected to auxiliary database: CDB1 (DBID=793624397)

RMAN> recover database from service cdb1;

Starting recover at 12.03.2014 15:00:27
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=237 device type=DISK
skipping datafile 5; already restored to SCN 1735641
skipping datafile 7; already restored to SCN 1735641
skipping datafile 8; already restored to SCN 2054839
skipping datafile 9; already restored to SCN 2054839
skipping datafile 10; already restored to SCN 2054839
skipping datafile 13; already restored to SCN 2068617
skipping datafile 14; already restored to SCN 2068617
skipping datafile 15; already restored to SCN 2068617
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service cdb1
destination for restore of datafile 00001: /u01/oradata/CDB2/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service cdb1
destination for restore of datafile 00003: /u01/oradata/CDB2/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service cdb1
destination for restore of datafile 00004: /u01/oradata/CDB2/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service cdb1
destination for restore of datafile 00006: /u01/oradata/CDB2/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service cdb1
destination for restore of datafile 00011: /u01/oradata/CDB2/valid/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service cdb1
destination for restore of datafile 00012: /u01/oradata/CDB2/valid/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service cdb1
destination for restore of datafile 00016: /u01/oradata/CDB2/valid/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 47 is already on disk as file /u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_47_9l0wzq03_.arc
archived log file name=/u01/fra/CDB2/archivelog/2014_03_12/o1_mf_1_47_9l0wzq03_.arc thread=1 sequence=47
media recovery complete, elapsed time: 00:00:01
Finished recover at 12.03.2014 15:02:06

Now everything looks a lot better:

DGMGRL> edit database cdb2 set state=apply-on;

DGMGRL> validate database cdb2;

  Database Role:     Physical standby database
  Primary Database:  cdb1

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    cdb1:  Off
    cdb2:  Off

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups   Standby Redo Log Groups
              (cdb2)                   (cdb1)
    1         3                        0
    Warning: standby redo logs not configured for thread 3 on cdb1

DGMGRL>

This is confirmed from the standby as well:

SQL> select process,status,thread#,sequence#,block#,delay_mins from v$managed_standby;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK# DELAY_MINS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING               1         47      26624          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1         48       7673          0
MRP0      APPLYING_LOG          1         48       7673          0

8 rows selected.

Excellent! We are back in sync.

Supporting Your Virtual VMs, Post II, PC and VM/Image Settings

I’m in the midst of transferring over to my travel laptop to run all my VMs on and retiring my previous ASUS work laptop to my youngest son.  I was surprised to find out that not ALL laptops are set up to run virtual environments these days.

1.  Virtualization may not be enabled in the BIOS, (i.e. On-Boot UEFI in the Lenovo Yoga 11s.)

enable_vm

Once this is enabled, save the configuration and reboot, allowing you to now run a VM on your laptop.

If you are importing appliances, make sure you have set the location to import the disks to the appropriate location, especially if you are using an external disk to house your VMs.

Before you click, “Import”, look at the very bottom where the location of the virtual disk will reside and verify that is where you want them to live.  The default is commonly the data location for the PC user on Windows or the /home/user for Linux users.

imp_disk

 

Now onto the VM

2.  All VM images are not the same.  If you are using an image file or have created the image from scratch, be prepared to do some preparation to get the image ready before you can successfully install Oracle on it and/or any other product such as Enterprise Manager, etc.

The nicest part is that Oracle will let you know what is wrong in its prereq checks during the installation of Oracle.  It’s going to let you know if your OS and virtual host has what it takes to support the installation-

vm_updates

 

Most of these are pretty straight forward for anyone who’s installed Oracle for awhile, maybe not for those that are newer to the product.

1.  Most VM images have a small /tmp and/or it can be filled quickly by installation.

  • clear out files from /tmp after each installation or in this case, failed install.
  • create a soft link to point to a second temp directory that has more space and make it the default temporary directory.

2.  Increase is the swapfile space.  This can be created in a location you have space to reserve and read/write swap to.  On  our VM, we’re kind of limited at the moment, so we’re just going to create a swapfile off of /u01 and give it 2GB of space:

dd if=/dev/zero of=/u01/swapfile bs=1024 count=2097152
mkswap /u01/swapfile
swapon /u01/swapfile
swapon -a

3.  Hard limit/soft limit-  These can be fixed by the following inside your VM, most of the steps must be performed as ROOT, but verify if it’s looking for ROOT to perform or a check as the OS user-

Next, we’re alerted that the max open file descriptors is an issue, so let’s look at those for the system, the user and then increase them:

cat /proc/sys/fs/file-max
Do as the user!!
ulimit -Hn
4096
ulimit -Sn
1024

We’ve now verified that they are smaller than the requested amount, (in our installation, it requested 65536) so we’ll add this and then verify the change in the file-

sysctl -w fs.file-max=65536
vi /etc/sysctl.conf
We can also simply append to file, (as ROOT):
fs.file-max = 100000

Now save and verify with the following:

sysctl -p

Log out and log back in to set the new values.

Next post we’ll take on all those darn lib files… :)

 



Tags:  


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [Supporting Your Virtual VMs, Post II, PC and VM/Image Settings], All Right Reserved. 2014.

Supporting Your Virtualbox VMs, Post 1- External Storage

I love my Lenovo Yoga 11s special build, ultra book.  It has 16G of memory, an Intel core i7 and 256 SSD, but that’s no where near the amount of space that I’m going to require to be running numerous virtual environments on it.  To help me out, I went on a fun buying and testing spree with a number of external disk solutions to find what worked best for my needs.  

The contenders, (yes, I was all over the board on these)-

The goal was to come up with a solid combination of performance, portability and storage.

I’m not going to go over any benchmarks as you can see the claims by the manufacturer and the reviews from folks who purchased them on the links, but I can tell you what ended up working for me.

The My Book was just to large and clunky.  I didn’t get the speed increases with the external power supply, (there were recommendations to get an external with added power as my Yoga doesn’t offer a whole lot in the tiny package…)  I think it would make a good backup drive, but not good for running VMs and no way was I hauling the literally “book” sized external drive overseas and to conferences!

The Toshiba Canvio, along with two other 5400rpm drives I already had were solid, had space of 500MB to 1.7TB, but they didn’t show the performance I required and the USB was 2.0 on the older ones.  It was good, but not good enough, so the Toshiba Canvio lost due to size vs.  price.

The PNY 128 GB flash drive was fast enough, but it’s just too darn small.  The price is really great at $50, (and for those Best Buy shoppers, Amazon has it for a third of what you’re paying at Best Buy!) so it is only good enough to be used as an external flash drive to SUPPORT the VM environment, (which I’ll explain in a little bit.)

Which leads me to the Patriot 256GB SuperSonic-  This 256GB, blazing fast flash drive is just big enough and plenty fast enough to run your VM for demos and webinars with the speed you want, but the price is the same as what you’d be paying for the Best Buy PNY version.  This is a fantastic deal for a 256GB fast USB 3.0 flash drive.

Then comes in the Touro 1TB 7200, 3.0 USB external drive.  It out performed all the other drives, the price was fantastic.  The combination of this for my standard VMs and the Patriot 256GB SuperSonic flash drive for demos is a nice combination.  I also kept the 128GB PNY flash drive-  why?  As you work with VMs, you come across the need to download and copy files as you are building.  It’s nice to have a fast flash drive with plenty of space to download to.

The end setup is portable enough that I can easily travel and present, but it’s fast enough that I can avoid some of the technical difficulties we all have run into when attempting to demonstrate via a VM.  A special thanks to Leighton Nelson, Tyler Muth and Connor McDonald for their recommendations.  This saved me a lot of time and I was able to test out just what fell inside that range to build out what would work for the setup I needed! :)

20140316_192114 



Tags:  


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [Supporting Your Virtualbox VMs, Post 1- External Storage], All Right Reserved. 2014.

On the Topic of Technology… 6

March 16, 2014 (Back to the Previous Post in the Series) It has been a while since my last post on this blog – I guess that the simple answer is that I was busy with a lot of non-Oracle Database related items, and was suffering from a bit of a writer’s block (nothing that a […]

On the Topic of Technology… 6

March 16, 2014 (Back to the Previous Post in the Series) It has been a while since my last post on this blog – I guess that the simple answer is that I was busy with a lot of non-Oracle Database related items, and was suffering from a bit of a writer’s block (nothing that a […]

Enterprise Performance Tuning in the Age of Agile Data

The new Oracle Scene magazine is out. Checkout pages 48-50 for an article by  me.

Here are the awesome contents of Oracle Scene magazine Spring 2014:

  • THE UPGRADE UPHILL  CHALLENGE by George Wilson 12
  • READING EXECUTION PLANS by Jonathan Lewis  Ace Director 18
  • ANGELS IN THE ARCHITECTURE by Chris Muir & Frank Nimphius 28
  • PROCESSOR GROUP INTEGRATION IN ORACLE DATABASE 12c by Nikolay Manchev Ace #ff0000;">42
  • TECHNOLOGY
    • The Ultimate Modernisation Challenge by Mia Urman Ace 35
  • APPLICATIONS TRANSFORMATION:
    • How Social Can Sales Teams Get With Oracle Sales Cloud by Prity Tewary 24
  • E-BUSINESS SUITE
    • Speeding Financial Close: Cross-Instance E-Business Suite Transaction
    • Drilldown by Jay Langhurst 14
  • JD EDWARDS
    • The ERP OBA Implementation Secret by Dale Kaplan & Sagar Malvankar 39
  • SOCIAL
    • Win BIG with LinkedIn Groups by Dave Sumner Smith 46
  • EVENTS & REGIONAL FOCUS
    • Focus on Ireland: 10th Anniversary Celebration 08
    • UKOUG A Year Ahead 10
    • UKOUG Events Timeline 2014 22
  • VOLUNTEER FOCUS
    • Women in IT by Debra Lilley Ace Director 05
    • Public Speaking by Tim Hall Ace Director 17
    • Meet a Volunteer: Simon Haslam Ace Director 27
  • REGULAR FEATURES
    • News and Reviews 06
  • DIGITAL SCENE
    • Enterprise Performance Tuning in the Age of Agile Data by Kyle Hailey Ace  48
    • Large Pages in Linux by Martin Bach Ace Director 51
    • Using SQL Plan Management for Performance Testing by Maris Elsins 56
    • Slow Flashback Query? by Chris Lawson Ace 61
    • Moving Forward with B2B Modern Marketing by Ruby Cameron 63

 

Enterprise Performance Tuning in the Age of Agile Data

12c Temporary

Just one of those little snippets to cover something new and remind of something old. A single session can now have three (or more) temporary tablespaces in use at the same time for different reasons.

  • In 12c you can set parameter temp_undo_enabled to true, at which point the undo relating to global temporary tables (GTTs) will be written into the database default temporary tablespace, which means it won’t generate redo. As an interesting side effect this also means that you can do DML on temporary objects in a physical standby database. Currently the undo segment shows as type “UNDEFINED” in v$sort_usage. A detail to watch out for, though – it looks as if each session may get its own temporary undo segment – so be careful about specifying the extent size and tempfile size for the database default temporary tablespace.
  • In 11g you can specify a tablespace (though not a tablespace group) in the ‘create global temporary table’ statement, which means you keep activity about GTTs separated from the I/O resulting from sorts and hash joins etc. spilling to disc; in fact you could specify a different temporary tablespace for every GTT if you wanted to – and I could imagine a case for having a couple of different temporary tablespaces to accommodate GTTs with very different usage characteristics. (Unfortunately you still can’t specify a tablespace in the dbms_lob.create_temporary() function).  If you don’t specify a tablespace for a GTT it will go into the default temporary tablespace of the user who is using it (not the database default, and not the default for the definer of the GTT). If you create indexes on a GTT they will automatically go into the same tablespace as the table.
  • Finally, of course, there’s the default temporary tablespace for the user and this is where GTTs will go if they don’t have a tablespace specified, and where all the scratch data (sorts, hash tables, factored subqueries et. al.) will go.

This combination means, of course, that you could manage to do a single “insert as select” writing a GTT to one temporary tablespace, with its undo going to a second temporary tablespace, and the spill from a sort or hash join in the select going to a third. The flexibility probably won’t make much difference to performance (for most people), but it’s possible that it will make it easier to monitor where the work is coming from if you’r ever in the position where your single temporary tablespace is subject to a lot of I/O.

Footnote:

In the past I’ve advised DBAs to set up a small number of tablespaces (or tablespace groups) so that they can allocate different classes of users – typically grouped by business function – to different temporary tablespaces. The ability to allocate GTTs to temporary tablespaces allows a further degree of refinement to this strategy.

12c Temporary

Just one of those little snippets to cover something new and remind of something old. A single session can now have three (or more) temporary tablespaces in use at the same time for different reasons.

  • In 12c you can set parameter temp_undo_enabled to true, at which point the undo relating to global temporary tables (GTTs) will be written into the database default temporary tablespace, which means it won’t generate redo. As an interesting side effect this also means that you can do DML on temporary objects in a physical standby database. Currently the undo segment shows as type “UNDEFINED” in v$sort_usage. A detail to watch out for, though – it looks as if each session may get its own temporary undo segment – so be careful about specifying the extent size and tempfile size for the database default temporary tablespace.
  • In 11g you can specify a tablespace (though not a tablespace group) in the ‘create global temporary table’ statement, which means you keep activity about GTTs separated from the I/O resulting from sorts and hash joins etc. spilling to disc; in fact you could specify a different temporary tablespace for every GTT if you wanted to – and I could imagine a case for having a couple of different temporary tablespaces to accommodate GTTs with very different usage characteristics. (Unfortunately you still can’t specify a tablespace in the dbms_lob.create_temporary() function).  If you don’t specify a tablespace for a GTT it will go into the default temporary tablespace of the user who is using it (not the database default, and not the default for the definer of the GTT). If you create indexes on a GTT they will automatically go into the same tablespace as the table.
  • Finally, of course, there’s the default temporary tablespace for the user and this is where GTTs will go if they don’t have a tablespace specified, and where all the scratch data (sorts, hash tables, factored subqueries et. al.) will go.

This combination means, of course, that you could manage to do a single “insert as select” writing a GTT to one temporary tablespace, with its undo going to a second temporary tablespace, and the spill from a sort or hash join in the select going to a third. The flexibility probably won’t make much difference to performance (for most people), but it’s possible that it will make it easier to monitor where the work is coming from if you’r ever in the position where your single temporary tablespace is subject to a lot of I/O.

Footnote:

In the past I’ve advised DBAs to set up a small number of tablespaces (or tablespace groups) so that they can allocate different classes of users – typically grouped by business function – to different temporary tablespaces. The ability to allocate GTTs to temporary tablespaces allows a further degree of refinement to this strategy.

More and more jira resource endpoints

I have added a lot more jira rest endpoints to the jira_utl package. The progress has been a little slow for the last week, since I have been elected president of the local danish user group, so I'm trying to get up to speed on that as well. The next week should be back to normal "speed"

Interesting GNS anomaly in 12.1.0.1.2 RAC

I was encountering an interesting anomaly with my 12.1.0.1.2 RAC cluster based on Grid Naming System. I have written about the setup here.

No need to get back to the post-instead let me give you some background if you are not familiar with the Grid Naming System in RAC. Most RAC installations use a Domain Name Server (DNS) to resolve names to IP addresses. In the case of GNS the name resolution is delegated from the main DNS server to a so-called subdomain. In my case my DNS server (bind version 9) is configured on aux.example.com (192.168.100.2), and it delegates name resolution for *.gns.example.com (my cluster) to the Grid Naming System. The GNS address in use is 192.168.100.37 and must be defined in the corporate DNS. The required IP addresses have to be supplied by a DHCP server in the same range as the public network. My public network is on 192.168.100/24, the private network is on 192.168.101/24 and 192.168.102/24. Which component resolves what? Here is a summary:

  • The GNS VIP is resolved by DNS. It’s not in the subdomain according to the documentation. I am using gns-vip.example.com (192.168.100.37) for it.
  • The node public IP is resolved via GNS. Oracle uses the $(hostname) to assign the name. The IP address is fixed
  • The node VIPs are resolved via GNS, the assigned name is $(hostname)-vip and the addresses are assigned by DHCP
  • The private interconnect interface is resolved by GNS, usually as $(hostname)-priv. Addresses are either static or assigned by DHCP
  • The SCAN VIPs are resolved via GNS and assigned by DHCP. And this is exactly where my problem was.

The Problem

All over sudden I had connection problems with my tomcat server connecting to the database. The address I used in tnsnames ora was rac12scan.gns.example.com and hadn’t changed at all. It simply didn’t point to a valid IP half of the time. When I pinged the SCAN I was surprised to see that it listed adresses that weren’t in the output of “srvctl config scan”.

And sure enough, a nslookup against the SCAN resulted in the output of 7 (!) addresses. There was obviously a discrepancy between what Oracle thought the SCAN was (3 IP addresses) and what GNS reported. I tried various cures, including restarting the DNS and DHCP servers (even moving the lease file on the DHCP server to a temporary location), and variations of restarting GNS and the whole cluster (crsctl stop clsuter -all; crsctl start cluster -all). Nothing helped.

Troubleshooting

As you’d expect the name resolution only works with GNS started. If you try to “nslookup rac12scan.gns.example.com” while GNS is down, you get the NXDOMAIN result code which does not really help. The output of “srvctl status gns -list and -detail” is useful in troubleshooting the problem. I also ran “cluvfy comp gns” but that came back clean. Nevertheless, the system instisted that the SCAN was made up of 7 IP addresses, and only 4 of them had SCAN VIPs. No surprise I can’t connect half the time. Now the question is of course: why does the GNS daemon grab so many IP addresses? So I looked at the gnsd.log in $GRID_HOME/log/$(hostname -s)/gns to see what happens. Here is an excerpt from the log file:

2014-03-11 16:37:30.305: [     GNS][1009862208]main::clsgnocrOpenKeyInternal: (:CLSGN01305:) #0 of 3: OCR error 4 (PROC-4: The cluster registry key to be operated on does not exist.) is not expected 32 - no retry will be done.
2014-03-11 16:37:30.305: [     GNS][1009862208]main::clsgnocrOpenKeyInternal: (:CLSGN01307:) Pass #0: open of "SYSTEM.GNS.SUBDOMAIN.gns|dexample|dcom.rac12gns-scan1-vip.SRV" failed with error 4 Last: PROC-4: The cluster registry key to be operated on does not exist. (4) - unable to retry.
2014-03-11 16:37:30.306: [     GNS][1009862208]main::clsgnocrOpenKeyInternal: (:CLSGN01305:) #0 of 3: OCR error 4 (PROC-4: The cluster registry key to be operated on does not exist.) is not expected 32 - no retry will be done.
2014-03-11 16:37:30.306: [     GNS][1009862208]main::clsgnocrOpenKeyInternal: (:CLSGN01307:) Pass #0: open of "SYSTEM.GNS.SUBDOMAIN.gns|dexample|dcom.rac12gns-scan1-vip.TXT" failed with error 4 Last: PROC-4: The cluster registry key to be operated on does not exist. (4) - unable to retry.
2014-03-11 16:37:30.306: [     GNS][1009862208]main::clsgndhaRegisterAddresses: Name: "rac12node1-vip" Type: A 192.168.100.44 Unique: TRUE Flags: ALLOCATED
2014-03-11 16:37:30.308: [     GNS][1009862208]main::clsgndhaRegisterAddresses: Name: "rac12node2-vip" Type: A 192.168.100.39 Unique: TRUE Flags: ALLOCATED
2014-03-11 16:37:30.308: [     GNS][1009862208]main::clsgndhaRegisterAddresses: Name: "rac12scan" Type: A 192.168.100.34 Unique: TRUE Flags: ALLOCATED
2014-03-11 16:37:30.309: [     GNS][1009862208]main::clsgndhaRegisterAddresses: Name: "rac12scan" Type: A 192.168.100.36 Unique: TRUE Flags: ALLOCATED
2014-03-11 16:37:30.309: [     GNS][1009862208]main::clsgndhaRegisterAddresses: Name: "rac12scan" Type: A 192.168.100.35 Unique: TRUE Flags: ALLOCATED
2014-03-11 16:37:30.309: [     GNS][1009862208]main::clsgndhaRegisterAddresses: Name: "rac12scan" Type: A 192.168.100.40 Unique: TRUE Flags: ALLOCATED
2014-03-11 16:37:30.309: [     GNS][1009862208]main::clsgndhaRegisterAddresses: Name: "rac12scan" Type: A 192.168.100.42 Unique: TRUE Flags: ALLOCATED
2014-03-11 16:37:30.309: [     GNS][1009862208]main::clsgndhaRegisterAddresses: Name: "rac12scan" Type: A 192.168.100.41 Unique: TRUE Flags: ALLOCATED
2014-03-11 16:37:30.309: [     GNS][1009862208]main::clsgndhaRegisterAddresses: Name: "rac12scan" Type: A 192.168.100.31 Unique: TRUE Flags: ALLOCATED
2014-03-11 16:37:30.309: [     GNS][1009862208]main::clsgndhaRegisterAddresses: Name: "rac12gns-scan2-vip" Type: A 192.168.100.41 Unique: TRUE Flags: ALLOCATED
2014-03-11 16:37:30.310: [     GNS][1009862208]main::clsgndhaRegisterAddresses: Name: "rac12gns-scan2-vip" Type: A 192.168.100.34 Unique: TRUE Flags: ALLOCATED
2014-03-11 16:37:30.310: [     GNS][1009862208]main::clsgndhaRegisterAddresses: Name: "rac12gns-scan3-vip" Type: A 192.168.100.40 Unique: TRUE Flags: ALLOCATED
2014-03-11 16:37:30.310: [     GNS][1009862208]main::clsgndhaRegisterAddresses: Name: "rac12gns-scan3-vip" Type: A 192.168.100.38 Unique: TRUE Flags: ALLOCATED
2014-03-11 16:37:30.310: [     GNS][1009862208]main::clsgndhaRegisterAddresses: Name: "rac12gns-scan1-vip" Type: A 192.168.100.31 Unique: TRUE Flags: ALLOCATED
2014-03-11 16:37:30.310: [   CLSNS][1009862208]Resolve::clsns_SetTraceLevel:trace level set to 1.
2014-03-11 16:37:30.310: [   CLSNS][1009862208]Resolve::clsns_SetTraceLevel:trace level set to 1.
2014-03-11 16:37:30.311: [   CLSNS][1009862208]Resolver #0::clsns_SetTraceLevel:trace level set to 1.
2014-03-11 16:37:30.311: [   CLSNS][1009862208]Resolver #0::clsns_SetTraceLevel:trace level set to 1.
2014-03-11 16:37:30.311: [   CLSNS][1009862208]Resolver #1::clsns_SetTraceLevel:trace level set to 1.

You can see the address assignment here, and this corresponds to the DHCP leases I saw on the DHCP server host’s /var/log/messages file. Notice that rac12scan grabs 7 IP addresses. This is surprising, the documentation normally states just 3 IP addresses for use with the SCAN. As you can further see the SCAN VIPs resolve only to a subset of these. 192.168.100.36 for example does not have a corresponding SCAN-VIP like some others too. Also, rac12gns-scan2-vip and rac12gns-scan3-vip appear twice. So if rac12scan now resolves to 192.168.100.36 you will get a TNS Error in the form “Destination host does not exist”. Annoying! But why does it do so? The mesages before (PROC-4) gave me a clue.

The information about GNS VIPs seems to be stored in the OCR. An ocrdump confirmed the findings. The question remains: why do Oracle use more than 3 IP addresses for the SCAN? I can only speculate that the DHCP addresses obtained are added to the OCR, and the existing ones don’t seem to be cleared out or updated correctly.

In an effort to solve the problem I dropped the SCAN listeners and the SCAN VIPS and recreated them. But this seemed to confuse the cluster even more, and the name resolution didn’t work. I tried both the subdomain delegation (nslookup rac12scan.gns.example.com) and querying GNS directly (nslookup rac12scan.gns.example.com – 192.168.100.37), both did not resolve anything. A restart of Clusterware didn’t change the situation so I decided to reboot the cluster nodes hoping to bring everything back to normal (yes I should know better, this isn’t Windows). After some waiting until the servers come back up there was no change. Now the system was so confused that it didn’t come up with a SCAN at all. Trying to restore the OCR failed, and I couldn’t even run a rootcrs.pl -deconfig -force in preparation for a reinstallation of Clusterware. So I trashed the system. I don’t believe there is a time for GNS in my lab after this experience.

Thanks to Marcin Przepiorowski (@pioro) who helped during the investigation.

References:

Grid Infrastructure Installation Guide 12c:

  • 1.1.3 Oracle Grid Infrastructure Network Checklist
  • 5.5.1 About Oracle Grid Infrastructure Name Resolution Options
  • 5.11 Grid Naming Service Standard Cluster Configuration Example