Skip navigation.

Pythian Group

Syndicate content
Official Pythian Blog - Love Your Data
Updated: 9 hours 16 min ago

Watch: Advanced Hadoop Security Features

Wed, 2014-04-30 07:41

As a follow up to a previous video, Pythian CTO Alex Gorbachev gives an overview of the advanced security features within Hadoop.

Categories: DBA Blogs

Watch: Hadoop in the Cloud

Tue, 2014-04-29 08:01

The Pythian team has received many questions about big data in the cloud, and specifically about Hadoop. Pythian CTO, Alex Gorbachev shares some of his recommendations in our latest video:

Categories: DBA Blogs

Watch: In-Memory Option for Oracle 12c

Mon, 2014-04-28 08:04

Back in September at Oracle OpenWorld 2013, Larry Ellison announced the Oracle Database In-Memory Option to Oracle 12c. Today, one of Pythian’s Advanced Technology Consultants, Christo Kutrovsky shares his thoughts on the new feature in our latest video. Stay tuned for updates while it’s being tested out in the field.

Categories: DBA Blogs

Log Buffer #368, A Carnival of the Vanities for DBAs

Mon, 2014-04-28 08:03

The beats of big data and symphonies of small data are creating a dazzling contrast in the realm of databases. Oracle, SQL Server, and MySQL are creating new tunes and all these tonal qualities are being captured by this Log Buffer Edition. Relax, sit back and enjoy.

Oracle:

This blog shows how you can write a SQL query to recommend products (cross-sell) to a customer based on products already placed in his current shopping cart.

R provides a rich set of statistical functions that we may want to use directly from SQL. Many of these results can be readily expressed as structured table data for use with other SQL tables, or for use by SQL-enabled applications, e.g., dashboards or other statistical tools.

Insight in the number, type and severity of errors that happen in a test or production environment is crucial to resolve them, and to make a stable ADF application that is less error-prone.

In order to get meaningful database statistics, the TIMED_STATISTICS parameter must be enabled for the database instance. The performance impact of having TIMED_STATISTICS enabled is minimal compared to instance performance.

As you all know that ADS is basically a push technology which send data from server to client without any user intervention . However client will send request periodically to server ask for update. Which further configure in adf-config.xml.

SQL Server:

SQL Server 2014 and the DBA: Building Bridges.

RS, SharePoint and Forefront UAG Series – Intro.

Introducing the Microsoft Analytics Platform System – the turnkey appliance for big data analytics.

Progressive Insurance data performance grows by factor of four, fueling business growth online experience.

Version 9.04.0013 of the RML Utilities for x86 and x64 has been released to the download center.

MySQL:

Since the dawn of time, MySQL indexes have a limit: they cannot be descending. Yes, ALTER TABLE and CREATE INDEX can contain the ASC and DESC keywords.

It’s now 3 weeks since the MariaDB & MySQL community day in Santa Clara.

FromDual.en: MySQL Environment MyEnv 1.0.3 has been released.

Managing Percona Xtradb Cluster with Puppet.

Every Relation is in First Normal Form, by definition. Every Table may not.

Categories: DBA Blogs

Developing a Data Infrastructure Sourcing Strategy

Fri, 2014-04-25 07:51

Today’s blog post is the second of three in a series dedicated to data infrastructure outsourcing, with excerpts from our latest white paper.

The trends in our first blog post reveal that enterprises are looking for outside help with their data infrastructure not to reduce costs, but to:

  • Improve service quality
  • Increase productivity
  • Access specialized skills
  • Increase innovation

In the full white paper, we examine some of the strengths and weaknesses of the three sourcing options available to help you realize these benefits.

In-House

Having data management experts on staff provides organizations with greater control over their work. Additionally, in-house experts have in-depth knowledge about their specific systems and processes. However, relying on in-house resources alone presents multiple challenges for an organization.

Offshoring

There’s no doubt that offshore providers have offered cost savings in the past. However, the fact that many companies are now reshoring their operations indicates that those benefits did not compensate for the downsides of this approach—typically reduced productivity.

Outsourcing

The right outsourcing vendor can deliver the benefits of the other two sourcing models without the equivalent downsides. For a deeper analysis of the strengths and weaknesses of the three sourcing options, download the rest of our white paper, Data Infrastructure Outsourcing.

Read the first blog post in this series, The Growing Trend Toward Data Infrastructure Outsourcing.

Categories: DBA Blogs

Data toons: Cirque du DBA

Wed, 2014-04-23 09:09

It’s not uncommon for database administrators (DBAs) to feel like ring masters at the circus. But what happens when you free up in-house DBAs by outsourcing database management?
Cirque cartoon

This work is licensed under a Creative Commons Attribution-NoDerivs 3.0 Unported License. Based on a work at pythian.com/blog.

Categories: DBA Blogs

What Does oracle-rdbms-server-11gR2-preinstall Not Do?

Mon, 2014-04-21 08:47

I was working on a project to clone Oracle install to a new Linux server and this should be simple as oracle-rdbms-server-11gR2-preinstall RPM can be used to configure the server, right?

So simple that I did not read the documentation.

However, I did review Requirements for Installing Oracle 11gR2 RDBMS on RHEL6 or OL6 64-bit (x86-64) (Doc ID 1441282.1) and it’s not quite the same.

From Oracle Database Quick Installation Guide 11g Release 2 (11.2) for Linux x86-64 the following packages are required:

binutils-2.20.51.0.2-5.11.el6 (x86_64)
compat-libcap1-1.10-1 (x86_64)
compat-libstdc++-33-3.2.3-69.el6 (x86_64)
compat-libstdc++-33-3.2.3-69.el6.i686
gcc-4.4.4-13.el6 (x86_64)
gcc-c++-4.4.4-13.el6 (x86_64)
glibc-2.12-1.7.el6 (i686)
glibc-2.12-1.7.el6 (x86_64)
glibc-devel-2.12-1.7.el6 (x86_64)
glibc-devel-2.12-1.7.el6.i686
ksh
libgcc-4.4.4-13.el6 (i686)
libgcc-4.4.4-13.el6 (x86_64)
libstdc++-4.4.4-13.el6 (x86_64)
libstdc++-4.4.4-13.el6.i686
libstdc++-devel-4.4.4-13.el6 (x86_64)
libstdc++-devel-4.4.4-13.el6.i686
libaio-0.3.107-10.el6 (x86_64)
libaio-0.3.107-10.el6.i686
libaio-devel-0.3.107-10.el6 (x86_64)
libaio-devel-0.3.107-10.el6.i686
make-3.81-19.el6
sysstat-9.0.4-11.el6 (x86_64)

RPM oracle-rdbms-server-11gR2-preinstall was installed

$  rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep oracle-rdbms
oracle-rdbms-server-11gR2-preinstall-1.0-9.el6 (x86_64)

Health Check Validation Engine was run for 11g R2 (11.2.0) Preinstall (Linux)

$ ./rda.sh -T hcve
Processing HCVE tests ...
Available Pre-Installation Rule Sets:
   1.  Oracle Database 10g R1 (10.1.0) Preinstall (Linux)
   2.  Oracle Database 10g R2 (10.2.0) Preinstall (Linux)
   3.  Oracle Database 11g R1 (11.1) Preinstall (Linux)
   4.  Oracle Database 11g R2 (11.2.0) Preinstall (Linux)
   5.  Oracle Database 12c R1 (12.1.0) Preinstallation (Linux)

Enter the HCVE rule set number or 0 to cancel the test
Hit "Return" to accept the default (0)
> 4

Performing HCVE checks ...
Enter value for < Planned ORACLE_HOME location >
Hit "Return" to accept the default (/u01/app/oracle/product/11.2.0.4/dbhome_1)
>

Enter value for < JDK Home >
>

Test "Oracle Database 11g R2 (11.2.0) Preinstall (Linux)" executed at 18-Apr-2014 05:28:37

Test Results
~~~~~~~~~~~~

ID     NAME                 RESULT  VALUE
====== ==================== ======= ==========================================
A00010 OS Certified?        PASSED  Adequate
A00020 User in /etc/passwd? PASSED  userOK
A00040 Group in /etc/group? PASSED  GroupOK
A00050 Enter ORACLE_HOME    RECORD  /u01/app/oracle/product/11.2.0.4/dbho...
A00060 ORACLE_HOME Valid?   PASSED  OHexists
A00070 O_H Permissions OK?  PASSED  CorrectPerms
A00080 oraInventory Permiss PASSED  oraInventoryOK
A00090 Got Software Tools?  PASSED  ld_nm_ar_make_found
A00100 Umask Set to 022?    PASSED  UmaskOK
A00120 Limits Processes     PASSED  Adequate
A00125 Limits Stacksize     PASSED  Adequate
A00130 Limits Descriptors   PASSED  Adequate
A00140 LDLIBRARYPATH Unset? PASSED  UnSet
A00180 JAVA_HOME Unset?     PASSED  UnSet
A00190 Enter JDK Home       RECORD
A00200 JDK Version          FAILED  JDK home is missing
A00210 Other O_Hs in PATH?  PASSED  NoneFound
A00220 Other OUI Up?        PASSED  NoOtherOUI
A00230 Temp Adequate?       PASSED  TempSpaceOK
A00240 Disk Space OK?       PASSED  DiskSpaceOK
A00250 Swap (in MB)         RECORD  6143
A00260 RAM (in MB)          PASSED  3954
A00270 Swap OK?             PASSED  SwapToRamOK
A00280 Network              PASSED  Connected
A00290 IP Address           RECORD  NotFound
A00300 Domain Name          RECORD  NotFound
A00310 DNS Lookup           FAILED  Host not known
A00320 /etc/hosts Format    PASSED  Adequate IPv4 entry
A00330 Kernel Parameters OK PASSED  KernelOK
A00380 Tainted Kernel?      PASSED  NotVerifiable
A00400 ip_local_port_range  PASSED  RangeOK
A00480 OL4 RPMs OK?         SKIPPED NotOL4
A00490 OL5 RPMs OK?         SKIPPED NotOL5
A00500 OL6 RPMs OK?         FAILED  [compat-libstdc++-33(i686)] not insta...
A00530 RHEL4 RPMs OK?       SKIPPED NotRedHat
A00540 RHEL5 RPMs OK?       SKIPPED NotRedHat
A00550 RHEL6 RPMs OK?       SKIPPED NotRedHat
A00570 SLES10 RPMs OK?      SKIPPED NotSuSE
A00580 SLES11 RPMs OK?      SKIPPED NotSuSE
Result file: output/collect/DB_HCVE_A_DB11R2_lin_res.htm

Just a note as the following entries will fail validation for A00320 /etc/hosts Format

192.168.56.11  arrow arrow.localdomain

since the engine is looking for the format below:

192.168.56.11  arrow.localdomain arrow

A00500 OL6 RPMs OK? FAILED because the following RPMs were missing:

[compat-libstdc++-33(i686)] not installed
[glibc-devel(i686)] not installed
[libaio(i686)] not installed
[libaio-devel(i686)] not installed
[libstdc++(i686)] not installed
[libstdc++-devel(i686)] not installed

RPMs check failed since the 32-bit version were not installed:

# rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | egrep '^compat-libstdc++|glibc-devel|libaio|^libstdc++'|sort
compat-libstdc++-296-2.96-144.el6 (i686)
compat-libstdc++-33-3.2.3-69.el6 (x86_64)
glibc-devel-2.12-1.132.el6 (x86_64)
libaio-0.3.107-10.el6 (x86_64)
libaio-devel-0.3.107-10.el6 (x86_64)
libstdc++-4.4.7-4.el6 (x86_64)
libstdc++-devel-4.4.7-4.el6 (x86_64)

Let’s install it.

# yum install compat-libstdc++-33
Loaded plugins: security
Setting up Install Process
Package compat-libstdc++-33-3.2.3-69.el6.x86_64 already installed and latest version
Nothing to do

Need to specific .i686. Learned something new here.

# yum install compat-libstdc++-33.i686
Loaded plugins: security
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package compat-libstdc++-33.i686 0:3.2.3-69.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=====================================================================================================================================================
 Package                                  Arch                      Version                             Repository                              Size
=====================================================================================================================================================
Installing:
 compat-libstdc++-33                      i686                      3.2.3-69.el6                        public_ol6_latest                      188 k

Transaction Summary
=====================================================================================================================================================
Install       1 Package(s)

Total download size: 188 k
Installed size: 716 k
Is this ok [y/N]: y
Downloading Packages:
compat-libstdc++-33-3.2.3-69.el6.i686.rpm                                                                                     | 188 kB     00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : compat-libstdc++-33-3.2.3-69.el6.i686                                                                                             1/1
  Verifying  : compat-libstdc++-33-3.2.3-69.el6.i686                                                                                             1/1

Installed:
  compat-libstdc++-33.i686 0:3.2.3-69.el6

Complete!

Let’s verify the RPMs.

# rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | egrep '^compat-libstdc++|glibc-devel|libaio|^libstdc++'|sort
compat-libstdc++-296-2.96-144.el6 (i686)
compat-libstdc++-33-3.2.3-69.el6 (i686)
compat-libstdc++-33-3.2.3-69.el6 (x86_64)
glibc-devel-2.12-1.132.el6 (i686)
glibc-devel-2.12-1.132.el6 (x86_64)
libaio-0.3.107-10.el6 (i686)
libaio-0.3.107-10.el6 (x86_64)
libaio-devel-0.3.107-10.el6 (i686)
libaio-devel-0.3.107-10.el6 (x86_64)
libstdc++-4.4.7-4.el6 (i686)
libstdc++-4.4.7-4.el6 (x86_64)
libstdc++-devel-4.4.7-4.el6 (i686)
libstdc++-devel-4.4.7-4.el6 (x86_64)

Let’s run RDA HCVE again.

[oracle@arrow:]/home/oracle/rda
$ ./rda.sh -T hcve
Processing HCVE tests ...
Available Pre-Installation Rule Sets:
   1.  Oracle Database 10g R1 (10.1.0) Preinstall (Linux)
   2.  Oracle Database 10g R2 (10.2.0) Preinstall (Linux)
   3.  Oracle Database 11g R1 (11.1) Preinstall (Linux)
   4.  Oracle Database 11g R2 (11.2.0) Preinstall (Linux)
   5.  Oracle Database 12c R1 (12.1.0) Preinstallation (Linux)

Enter the HCVE rule set number or 0 to cancel the test
Hit "Return" to accept the default (0)
> 4

Performing HCVE checks ...
Enter value for < Planned ORACLE_HOME location >
Hit "Return" to accept the default (/u01/app/oracle/product/11.2.0.4/dbhome_1)
>

Enter value for < JDK Home >
>

Test "Oracle Database 11g R2 (11.2.0) Preinstall (Linux)" executed at 18-Apr-2014 05:57:52

Test Results
~~~~~~~~~~~~

ID     NAME                 RESULT  VALUE
====== ==================== ======= ==========================================
A00010 OS Certified?        PASSED  Adequate
A00020 User in /etc/passwd? PASSED  userOK
A00040 Group in /etc/group? PASSED  GroupOK
A00050 Enter ORACLE_HOME    RECORD  /u01/app/oracle/product/11.2.0.4/dbho...
A00060 ORACLE_HOME Valid?   PASSED  OHexists
A00070 O_H Permissions OK?  PASSED  CorrectPerms
A00080 oraInventory Permiss PASSED  oraInventoryOK
A00090 Got Software Tools?  PASSED  ld_nm_ar_make_found
A00100 Umask Set to 022?    PASSED  UmaskOK
A00120 Limits Processes     PASSED  Adequate
A00125 Limits Stacksize     PASSED  Adequate
A00130 Limits Descriptors   PASSED  Adequate
A00140 LDLIBRARYPATH Unset? PASSED  UnSet
A00180 JAVA_HOME Unset?     PASSED  UnSet
A00190 Enter JDK Home       RECORD
A00200 JDK Version          FAILED  JDK home is missing
A00210 Other O_Hs in PATH?  PASSED  NoneFound
A00220 Other OUI Up?        PASSED  NoOtherOUI
A00230 Temp Adequate?       PASSED  TempSpaceOK
A00240 Disk Space OK?       PASSED  DiskSpaceOK
A00250 Swap (in MB)         RECORD  6143
A00260 RAM (in MB)          PASSED  3954
A00270 Swap OK?             PASSED  SwapToRamOK
A00280 Network              PASSED  Connected
A00290 IP Address           RECORD  NotFound
A00300 Domain Name          RECORD  NotFound
A00310 DNS Lookup           FAILED  Host not known
A00320 /etc/hosts Format    PASSED  Adequate IPv4 entry
A00330 Kernel Parameters OK PASSED  KernelOK
A00380 Tainted Kernel?      PASSED  NotVerifiable
A00400 ip_local_port_range  PASSED  RangeOK
A00480 OL4 RPMs OK?         SKIPPED NotOL4
A00490 OL5 RPMs OK?         SKIPPED NotOL5
A00500 OL6 RPMs OK?         PASSED  OL6rpmsOK
A00530 RHEL4 RPMs OK?       SKIPPED NotRedHat
A00540 RHEL5 RPMs OK?       SKIPPED NotRedHat
A00550 RHEL6 RPMs OK?       SKIPPED NotRedHat
A00570 SLES10 RPMs OK?      SKIPPED NotSuSE
A00580 SLES11 RPMs OK?      SKIPPED NotSuSE
Result file: output/collect/DB_HCVE_A_DB11R2_lin_res.htm

Does not edit of /etc/pam.d/login to include the following:

session required pam_limits.so

So what is the lesson learned? Trust, but verify. Alternatively, RTFM!

Categories: DBA Blogs

Log Buffer #367, A Carnival of the Vanities for DBAs

Thu, 2014-04-17 07:47

Log Buffer is globe trotting this week from end to end. From every nook, it has brought you some sparkling gems of blog posts. Enjoy!!!

Oracle:

On April 16th, Oracle announced the Oracle Virtual Compute Appliance X4-2.

Do your Cross Currency Receipts fail Create Accounting?

Oracle Solaris 11.2 Launch in NY.

WebCenter Portal 11gR1 dot8 Bundle Patch 3 (11.1.1.8.3) Released.

What do Sigma, a Leadership class and a webcast have in common?

SQL Server:

Stairway to SQL Server Agent – Level 9: Understanding Jobs and Security.

SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

SQL Server 2014 In-Memory OLTP Dynamic Management Views.

Why every SQL Server installation should be a cluster.

SQL Server Backup Crib Sheet.

MySQL:

Looking for Slave Consistency: Say Yes to –read-only and No to SUPER and –slave-skip-errors.

More details on disk IO-bound, update only for MongoDB, TokuMX and InnoDB.

Making the MTR rpl suite GTID_MODE Agnostic.

Open Source Appreciation Day’ draws OpenStack, MySQL and CentOS faithful.

MongoDB, TokuMX and InnoDB for disk IO-bound, update-only by PK.

Categories: DBA Blogs

Why is Affinity Mask Negative in sp_configure?

Tue, 2014-04-15 07:56

While looking at a SQL server health report, I found affinity mask parameter in sp_configure output showing a negative value.

name                                minimum     maximum     config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask                       -2147483648 2147483647  -1066394617  -1066394617

Affinity mask is a SQL Server configuration option which is used to assign processors to specific threads for improved performance. To know more about affinity mask, read this. Usually, the value for affinity mask is a positive integer (decimal format) in sp_configure. The article in previous link shows an example of binary bit mask and corresponding decimal value to be set in sp_configure.

 

I was curious to find out why the value of affinity mask could be negative as according to BOL http://technet.microsoft.com/en-us/library/ms187104(v=sql.105).aspx

 

 affinity_mask_memeThe values for affinity mask are as follows:

          · A one-byte affinity mask covers up to 8 CPUs in a multiprocessor computer.

       

          · A two-byte affinity mask covers up to 16 CPUs in a multiprocessor computer.

         

          · A three-byte affinity mask covers up to 24 CPUs in a multiprocessor computer.

         

          · A four-byte affinity mask covers up to 32 CPUs in a multiprocessor computer.

         

         · To cover more than 32 CPUs, configure a four-byte affinity mask for the first 32    CPUs and up to a four-byte affinity64 mask for the remaining CPUs.

 

Time to unfold the mystery. Windows Server 2008 R2 supports more than 64 logical processors. From ERRORLOG, I see there are 40 logical processors on the server:

 

2014-03-31 18:18:18.18 Server      Detected 40 CPUs. This is an informational message; no user action is required.

 

Further, going down in the ERRORLOG I see this server has four NUMA nodes configured.

 

Processor affinity turned on: node 0, processor mask 0x0000000000001c00.

Processor affinity turned on: node 1, processor mask 0×0000000000000007.

Processor affinity turned on: node 2, processor mask 0×0000000000700000.

Processor affinity turned on: node 3, processor mask 0x00000001c0000000.

:

Node configuration: node 0: CPU mask: 0x00000000000ffc00:0 Active CPU mask: 0x0000000000001c00:0.

Node configuration: node 1: CPU mask: 0x00000000000003ff:0 Active CPU mask: 0×0000000000000007:0.

Node configuration: node 2: CPU mask: 0x000000003ff00000:0 Active CPU mask: 0×0000000000700000:0.

Node configuration: node 3: CPU mask: 0x000000ffc0000000:0 Active CPU mask: 0x00000001c0000000:0. 

 

These were hard NUMA nodes. No soft NUMA node configured on the server (no related registry keys exist)

 

An important thing to note is that the affinity mask value forsp_configure ranges from -2147483648 to 2147483647 = 2147483648 + 2147483647 + 1 = 4294967296 = 2^32 = the range of int data type. Hence affinity mask value from sp_configure is not sufficient to hold more than 64 CPUs. To deal with this, ALTER SERVER CONFIGURATION was introduced in SQL Server 2008 R2 to support and set the processor affinity for more than 64 CPUs. However, the value of affinity mask in sp_configure, in such cases, is still an *adjusted* value which we are going to find out below.

 

Let me paste the snippet from ERRORLOG again:

 

Processor affinity turned on: node 0, processor mask 0x0000000000001c00.

Processor affinity turned on: node 1, processor mask 0×0000000000000007.

Processor affinity turned on: node 2, processor mask 0×0000000000700000.

Processor affinity turned on: node 3, processor mask 0x00000001c0000000.

 

As it says, the underlined values above are for the processor mask i.e. processor affinity or affinity mask. These values correspond to that of online_scheduler_mask in sys.dm_os_nodes which makes up the ultimate value for affinity mask in sp_configure. Ideally, affinity mask should be a sum of these values. Let’s add these hexadecimal values using windows calculator (Choose Programmer from Viewmenu)

 

  0x0000000000001c00

+ 0×0000000000000007

+ 0×0000000000700000

+ 0x00000001c0000000

--------------------

= 0x00000001C0701C07

 

= 7523539975 (decimal)

 

So, affinity mask in sp_configure should have been equal to 7523539975. Since this no. is greater than the limit of 2^32 i.e. 4294967296 we see an *adjusted* value (apparently a negative value). The reason I say it an *adjusted* value is because sum of processor mask values (in decimal) is adjusted (subtracted from the int range i.e. 4294967296 so that it fits within the range and falls below or equal to 4294967296 ). Here’s is an example which explains the theory:

 

7523539975 – 4294967296  – 4294967296 = –1066394617 = the negative value seen in sp_configure

name                                minimum     maximum     config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask                       -2147483648 2147483647  -1066394617  -1066394617

That explains why affinity mask shows up as a negative number in sp_configure.

 

To make the calculation easier, I wrote a small script to find out the sp_configure equivalent value of affinity mask in case of NUMA nodes

               


-- Find out the sp_configure equivalent value of affinity mask in case of NUMA nodes

--------------------------------------------------------------------------------------

BEGIN
DECLARE @real_value bigint; -- to hold the sum of online_scheduler_mask

DECLARE @range_value bigint = 4294967296; -- range of int dataype i.e. 2^32

DECLARE @config_value int = 0; -- default value of affinity_mask in sp_configure output. to be set later.
-- Fetch the sum of Online Scheudler Mask excluding node id 64 i.e. Hidden scheduler

SET @real_value =( SELECT SUM(online_scheduler_mask) as online_scheduler_mask

FROM sys.dm_os_nodes

WHERE memory_node_id <> 64

);
-- Calculate the value for affinity_mask in sp_configure

WHILE (@real_value > 2147483647)

BEGIN

SET @real_value=(@real_value - @range_value);

END;
-- Copy the value for affinity_mask as seen in sp_configure

SET @config_value = @real_value;
PRINT 'The current config_value for affinity_mask parameter in sp_configure is: ' + cast(@config_value as varchar);

END;

This script will give the current config value for SQL server in any case, NUMA nodes, >64 procs, SQL Server 2008 R2..

 

Hope this post will help you if were as puzzled as I was seeing the negative no. in sp_configure.

 

Stay tuned!

Categories: DBA Blogs

Oracle RMAN Restore to the Same Machine as the Original Database

Fri, 2014-04-11 07:52

Among the most critical but often most neglected database administration tasks is testing restore from backup. But sometimes, you don’t have a test system handy, and need to test the restore on the same host as the source database. In such situations, the biggest fear is overwriting the original database. Here is a simple procedure you can follow, which will not overwrite the source.

  1. Add an entry to the oratab for the new instance, and source the new environment:
    oracle$ cat >> /etc/oratab <<EOF
    > foo:/u02/app/oracle/product/11.2.0/dbhome_1:N
    > EOF
    
    oracle$ . oraenv
    ORACLE_SID[oracle]? foo
    The Oracle base remains unchanged with value /u02/app/oracle
  2. Create a pfile and spfile with a minimum set of parameters for the new instance. In this case the source database is named ‘orcl’ and the new database will have a DB unique name of ‘foo’. This example will write all files to the +data ASM diskgroup, under directories for ‘foo’. You could use a filesystem directory as the destination as well. Just make sure you have enough space wherever you plan to write:
    oracle$ cat > $ORACLE_HOME/dbs/initfoo.ora <<EOF
    > db_name=orcl
    > db_unique_name=foo
    > db_create_file_dest=+data
    > EOF
    
    oracle$ sqlplus / as sysdba
    SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 9 15:35:00 2014
    Copyright (c) 1982, 2011, Oracle.  All rights reserved.
    Connected to an idle instance.
    
    SQL> create spfile from pfile;
    File created.
    
    SQL> exit
    Disconnected
  3. Now, using the backup pieces from your most recent backup, try restoring the controlfile only. Start with the most recently written backup piece, since RMAN writes the controlfile at the end of the backup. It may fail once or twice, but keep trying backup pieces until you find the controlfile:
    oracle$ ls -lt /mnt/bkup
    total 13041104
    -rwxrwxrwx 1 root root      44544 Apr  4 09:32 0lp4sghk_1_1
    -rwxrwxrwx 1 root root   10059776 Apr  4 09:32 0kp4sghi_1_1
    -rwxrwxrwx 1 root root 2857394176 Apr  4 09:32 0jp4sgfr_1_1
    -rwxrwxrwx 1 root root 3785719808 Apr  4 09:31 0ip4sgch_1_1
    -rwxrwxrwx 1 root root 6697222144 Apr  4 09:29 0hp4sg98_1_1
    -rwxrwxrwx 1 root root    3647488 Apr  4 09:28 0gp4sg97_1_1
    
    $ rman target /
    Recovery Manager: Release 11.2.0.3.0 - Production on Wed Apr 9 15:37:10 2014
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    connected to target database (not started)
    
    RMAN> startup nomount;
    Oracle instance started
    Total System Global Area     238034944 bytes
    Fixed Size                     2227136 bytes
    Variable Size                180356160 bytes
    Database Buffers              50331648 bytes
    Redo Buffers                   5120000 bytes
    
    RMAN> restore controlfile from '/mnt/bkup/0lp4sghk_1_1';
    Starting restore at 09-APR-14
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=1 device type=DISK
    channel ORA_DISK_1: restoring control file
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of restore command at 04/09/2014 15:42:10
    ORA-19870: error while restoring backup piece /mnt/bkup/0lp4sghk_1_1
    ORA-19626: backup set type is archived log - can not be processed by this conversation
    
    RMAN> restore controlfile from '/mnt/bkup/0kp4sghi_1_1';
    Starting restore at 09-APR-14
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=19 device type=DISK
    channel ORA_DISK_1: restoring control file
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
    output file name=+DATA/foo/controlfile/current.348.844443549
    Finished restore at 09-APR-14

    As you can see above, RMAN will report the path and name of the controlfile that it restores. Use that path and name below:

    RMAN> sql "alter system set
    2>  control_files=''+DATA/foo/controlfile/current.348.844443549''
    3>  scope=spfile";
    
    sql statement: alter system set 
    control_files=''+DATA/foo/controlfile/current.348.844443549'' 
    scope=spfile
  4. Mount the database with the newly restored controlfile, and perform a restore to the new location. The ‘set newname’ command changes the location that RMAN will write the files to the db_create_file_dest of the new instance. The ‘switch database’ command updates the controlfile to reflect the new file locations. When the restore is complete, use media recovery to apply the archived redologs.
    RMAN> startup force mount
    Oracle instance started
    database mounted
    Total System Global Area     238034944 bytes
    Fixed Size                     2227136 bytes
    Variable Size                180356160 bytes
    Database Buffers              50331648 bytes
    Redo Buffers                   5120000 bytes
    
    RMAN> run {
    2> set newname for database to new;
    3> restore database;
    4> }
    
    executing command: SET NEWNAME
    Starting restore at 09-APR-14
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=23 device type=DISK
    channel ORA_DISK_1: starting datafile backup set restore
    channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_DISK_1: restoring datafile 00002 to +data
    channel ORA_DISK_1: reading from backup piece /mnt/bkup/0hp4sg98_1_1
    channel ORA_DISK_1: piece handle=/mnt/bkup/0hp4sg98_1_1 tag=TAG20140404T092808
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:01:35
    channel ORA_DISK_1: starting datafile backup set restore
    channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_DISK_1: restoring datafile 00001 to +data
    channel ORA_DISK_1: restoring datafile 00004 to +data
    channel ORA_DISK_1: restoring datafile 00005 to +data
    channel ORA_DISK_1: reading from backup piece /mnt/bkup/0ip4sgch_1_1
    channel ORA_DISK_1: piece handle=/mnt/bkup/0ip4sgch_1_1 tag=TAG20140404T092808
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
    channel ORA_DISK_1: starting datafile backup set restore
    channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_DISK_1: restoring datafile 00003 to +data
    channel ORA_DISK_1: reading from backup piece /mnt/bkup/0jp4sgfr_1_1
    channel ORA_DISK_1: piece handle=/mnt/bkup/0jp4sgfr_1_1 tag=TAG20140404T092808
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
    Finished restore at 09-APR-14
    
    RMAN> switch database to copy;
    
    datafile 1 switched to datafile copy "+DATA/foo/datafile/system.338.844531637"
    datafile 2 switched to datafile copy "+DATA/foo/datafile/sysaux.352.844531541"
    datafile 3 switched to datafile copy "+DATA/foo/datafile/undotbs1.347.844531691"
    datafile 4 switched to datafile copy "+DATA/foo/datafile/users.350.844531637"
    datafile 5 switched to datafile copy "+DATA/foo/datafile/soe.329.844531637"
    
    RMAN> recover database;
    
    Starting recover at 09-APR-14
    using channel ORA_DISK_1
    starting media recovery
    archived log file name=/u02/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_25_841917031.dbf thread=1 sequence=25
    archived log file name=/u02/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_26_841917031.dbf thread=1 sequence=26
    archived log file name=/u02/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_27_841917031.dbf thread=1 sequence=27
    media recovery complete, elapsed time: 00:00:01
    Finished recover at 09-APR-14
    
    RMAN> exit
    
    Recovery Manager complete.
  5. Before opening the database, we need to re-create the controlfile so that we don’t step on any files belonging to the source database. The first step is to generate a “create controlfile” script, and to locate the trace file where it was written:
    $ sqlplus / as sysdba
    SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 16 10:56:28 2014
    Copyright (c) 1982, 2011, Oracle.  All rights reserved.
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    
    SQL> alter database backup controlfile to trace;
    Database altered.
    
    SQL> select tracefile
      2  from v$session s,
      3       v$process p
      4  where s.paddr = p.addr
      5  and s.audsid = sys_context('USERENV', 'SESSIONID');
    TRACEFILE
    ----------------------------------------------------------
    /u02/app/oracle/diag/rdbms/foo/foo/trace/foo_ora_19168.trc
    
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition
  6. Next, we need to edit the controlfile creation script so that all we have left is the “create controlfile … resetlogs” statement, and so that all file paths to the original database are removed or changed to reference the db_unique_name of the test database.Below is a pipeline of clumsy awks I created that creates a script called create_foo_controlfile.sql. It should take care of most permutations of these trace controlfile scripts.
    $ sed -n '/CREATE.* RESETLOGS/,$p' /u02/app/oracle/diag/rdbms/foo/foo/trace/foo_ora_18387.trc | \
    > sed '/.*;/q' | \
    > sed 's/\(GROUP...\).*\( SIZE\)/\1\2/' | \
    > sed 's/orcl/foo/g' | \
    > sed 's/($//' | \
    > sed 's/[\)] SIZE/SIZE/' | \
    > grep -v "^    '" > create_foo_controlfile.sql

    If it doesn’t work for you, just edit the script from your trace file, so that you end up with something like this:

    CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
        MAXLOGFILES 16
        MAXLOGMEMBERS 3
        MAXDATAFILES 100
        MAXINSTANCES 8
        MAXLOGHISTORY 292
    LOGFILE
      GROUP 1 
      SIZE 50M BLOCKSIZE 512,
      GROUP 2 
      SIZE 50M BLOCKSIZE 512,
      GROUP 3 
      SIZE 50M BLOCKSIZE 512
    -- STANDBY LOGFILE
    DATAFILE
      '+DATA/foo/datafile/system.338.845027673',
      '+DATA/foo/datafile/sysaux.347.845027547',
      '+DATA/foo/datafile/undotbs1.352.845027747',
      '+DATA/foo/datafile/users.329.845027673',
      '+DATA/foo/datafile/soe.350.845027673'
    CHARACTER SET WE8MSWIN1252
    ;
  7. The next step is to use the above script to open the database with the resetlogs option on a new OMF controlfile:
    $ sqlplus / as sysdba
    SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 16 10:56:28 2014
    Copyright (c) 1982, 2011, Oracle.  All rights reserved.
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    
    SQL> alter system reset control_files scope=spfile;
    System altered.
    
    SQL> startup force nomount
    ORACLE instance started.
    
    Total System Global Area  238034944 bytes
    Fixed Size                  2227136 bytes
    Variable Size             180356160 bytes
    Database Buffers           50331648 bytes
    Redo Buffers                5120000 bytes
    
    SQL> @create_foo_controlfile
    Control file created.
    
    SQL> select value from v$parameter where name = 'control_files';
    VALUE
    -------------------------------------------
    +DATA/foo/controlfile/current.265.845031651
    
    SQL> alter database open resetlogs;
    Database altered.
  8. Last but not least, don’t forget to provide a tempfile or two to the temporary tablespaces:
    SQL> alter tablespace temp
      2  add tempfile size 5G;
    Tablespace altered.
Categories: DBA Blogs

The Growing Trend Toward Data Infrastructure Outsourcing

Fri, 2014-04-11 07:45

Today’s blog post is the first of three in a series dedicated to data infrastructure outsourcing, with excerpts from our latest white paper.

Despite the strong push to outsource corporate functions that began more than two decades ago, many IT shops have been hesitant to outsource their data management requirements.

Generally, the more mission-critical the data, the more organizations have felt compelled to control it, assigning that responsibility to their brightest and most trusted in-house database experts. The reasoning has been that with greater control comes greater security.

That mindset is rapidly changing. Macroeconomic trends are putting mounting pressure on organizations to rethink the last bastion of IT in-housing—data infrastructure management—and instead look for flexible, cost-effective outsourcing solutions that can help them improve operational efficiency, optimize performance, and increase overall productivity.

This trend toward outsourcing to increase productivity, and not simply reduce costs, is supported by a recent Forrester Research report that highlights the key reasons companies  are looking for outside help: too many new technologies and data sources, and difficulty finding people with the skills and experience to optimize and manage them.

To learn how to develop a data infrastructure sourcing strategy, download the rest of our white paper, Data Infrastructure Outsourcing.

Categories: DBA Blogs

What Happens in Vegas, Doesn’t Stay in Vegas – Collaborate 14

Thu, 2014-04-10 08:04

IOUG’s Collaborate 14, is star-studded this year with the Pythian team illuminating various tracks in the presentation rooms. It’s acting like a magnet in the expo halls of The Venetian for data lovers. It’s a kind of rendezvous for those who love their data. So if you want your data to be loved, feel free to drop by at Pythian booth 1535.

Leading from the front is Paul Vallée with an eye-catching title, with real world gems. Then there is Michael Abbey’s rich experience, Marc Fielding’s in-depth technology coverage and Vasu’s forays into Apps Database Administration. There is my humble attempt at Exadata IORM, and Rene’s great helpful tips, and Alex Gorbachev’s mammoth coverage of mammoth data – it’s all there with much more to learn, share and know.

Vegas Strip is buzzing with the commotion of Oracle. Even the big rollers are turning their necks to see what the fuss is about. Poker faces have broken into amazed grins, and even the weird, kerbside card distribution has stopped. Everybody is focused on the pleasures of Oracle technologies.

Courtesy of social media, all of this fun isn’t confined to Vegas. You can follow @Pythian on Twitter to know it all, live, and in real time.

Come Enjoy!

Categories: DBA Blogs

Paul Vallée’s Interview with Oracle Profit Magazine

Wed, 2014-04-09 23:00

Aaron Lazenby, Editor at Oracle’s Profit Magazine interviewed Pythian Founder, Paul Vallée this week to discuss the growing risk of internal threats to IT. Paul Vallee

“What we need to create is complete accountability for everything that happens around a data center, and that’s where our industry is not up to snuff right now. We tend to think that if you secure access to the perimeter of the data center, then what happens in the meeting inside can be unsupervised. But that’s not good enough.” says Paul.

The interview, Inside Job, is a preview to Paul’s Collaborate ’14 session taking place in later today in Las Vegas. If you’re at Collaborate, make sure you don’t miss Paul’s presentation Thou Shalt Not Steal: Securing Your Infrastructure in the Age of Snowden. The presentation begins at 4:15 PM Pacific at the Venetian, Level 3 – Murano 3306.

What are your thoughts? How else can organizations mitigate the risk of internal threats? Comment below.

Categories: DBA Blogs

4 Things Every CMO Should Do Before Approaching IT About Big Data

Tue, 2014-04-08 10:42

Read the full article, 4 Things Every CMO Should Do Before Approaching IT About Big Data.  Approaching IT with Big Data article

“You’ve likely heard the whispers (or shouts) about Big Data’s potential, how it’s the holy grail of marketing—and it can be. But to uncover this information and take action on it, marketing needs to partner closely with all departments, especially with IT.” says Samer Forzley, VP of Marketing at Pythian.

“IT can go off and develop as many Big Data initiatives as it wants, but without the necessary insights from the marketing team, those projects will never translate into sales. But if each plays to its strengths, with CMOs entering the Big Data conversation with logistics upfront, then IT’s structural knowhow can bring that solution to fruition.”

Categories: DBA Blogs

RMAN Infatuation?

Tue, 2014-04-08 09:59

Lately, I am becoming infatuated with RMAN again.

Have you ever run “restore database preview”?

Are you curious about how the recovery SCN are determined?

Media recovery start SCN is 1515046
Recovery must be done beyond SCN 1515051 to clear datafile fuzziness

If you are, then I will demonstrate this for you.

RMAN LEVEL0 backup and restore database preview summary:

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
366     B  A  A DISK        07-APR-2014 14:11:32 1       1       YES        ARCHIVELOG
367     B  0  A DISK        07-APR-2014 14:11:34 1       1       YES        LEVEL0
368     B  0  A DISK        07-APR-2014 14:11:35 1       1       YES        LEVEL0
369     B  0  A DISK        07-APR-2014 14:11:37 1       1       YES        LEVEL0
370     B  0  A DISK        07-APR-2014 14:11:44 1       1       YES        LEVEL0
371     B  0  A DISK        07-APR-2014 14:11:44 1       1       YES        LEVEL0
372     B  0  A DISK        07-APR-2014 14:11:52 1       1       YES        LEVEL0
373     B  A  A DISK        07-APR-2014 14:11:55 1       1       YES        ARCHIVELOG
374     B  F  A DISK        07-APR-2014 14:11:58 1       1       NO         TAG20140407T141156

RMAN> restore database preview summary;

Starting restore at 07-APR-2014 14:13:04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=107 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=23 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=108 device type=DISK

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
371     B  0  A DISK        07-APR-2014 14:11:38 1       1       YES        LEVEL0
370     B  0  A DISK        07-APR-2014 14:11:39 1       1       YES        LEVEL0
367     B  0  A DISK        07-APR-2014 14:11:34 1       1       YES        LEVEL0
368     B  0  A DISK        07-APR-2014 14:11:34 1       1       YES        LEVEL0
369     B  0  A DISK        07-APR-2014 14:11:37 1       1       YES        LEVEL0
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
373     B  A  A DISK        07-APR-2014 14:11:55 1       1       YES        ARCHIVELOG
Media recovery start SCN is 1524017
Recovery must be done beyond SCN 1524025 to clear datafile fuzziness
Finished restore at 07-APR-2014 14:13:05

RMAN>

Query database to determine recovery SCN:

ARROW:(SYS@db01):PRIMARY> r
  1  SELECT
  2    MIN(checkpoint_change#) start_scn,
  3    GREATEST(MAX(checkpoint_change#),MAX(absolute_fuzzy_change#)) beyond_scn
  4  FROM v$backup_datafile
  5  WHERE incremental_level=(SELECT MAX(incremental_level) FROM v$backup_datafile WHERE incremental_level>=0)
  6*

 START_SCN BEYOND_SCN
---------- ----------
   1524017    1524025

ARROW:(SYS@db01):PRIMARY>

RMAN LEVEL0 and LEVEL1 backup and restore database preview summary:

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
366     B  A  A DISK        07-APR-2014 14:11:32 1       1       YES        ARCHIVELOG
367     B  0  A DISK        07-APR-2014 14:11:34 1       1       YES        LEVEL0
368     B  0  A DISK        07-APR-2014 14:11:35 1       1       YES        LEVEL0
369     B  0  A DISK        07-APR-2014 14:11:37 1       1       YES        LEVEL0
370     B  0  A DISK        07-APR-2014 14:11:44 1       1       YES        LEVEL0
371     B  0  A DISK        07-APR-2014 14:11:44 1       1       YES        LEVEL0
372     B  0  A DISK        07-APR-2014 14:11:52 1       1       YES        LEVEL0
373     B  A  A DISK        07-APR-2014 14:11:55 1       1       YES        ARCHIVELOG
374     B  F  A DISK        07-APR-2014 14:11:58 1       1       NO         TAG20140407T141156
375     B  A  A DISK        07-APR-2014 14:14:37 1       1       YES        ARCHIVELOG
376     B  1  A DISK        07-APR-2014 14:14:40 1       1       YES        LEVEL1
377     B  1  A DISK        07-APR-2014 14:14:40 1       1       YES        LEVEL1
378     B  1  A DISK        07-APR-2014 14:14:41 1       1       YES        LEVEL1
379     B  1  A DISK        07-APR-2014 14:14:42 1       1       YES        LEVEL1
380     B  1  A DISK        07-APR-2014 14:14:42 1       1       YES        LEVEL1
381     B  1  A DISK        07-APR-2014 14:14:45 1       1       YES        LEVEL1
382     B  A  A DISK        07-APR-2014 14:14:47 1       1       YES        ARCHIVELOG
383     B  F  A DISK        07-APR-2014 14:14:51 1       1       NO         TAG20140407T141448

RMAN> restore database preview summary;

Starting restore at 07-APR-2014 14:15:59
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=107 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=23 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=108 device type=DISK

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
371     B  0  A DISK        07-APR-2014 14:11:38 1       1       YES        LEVEL0
376     B  1  A DISK        07-APR-2014 14:14:39 1       1       YES        LEVEL1
370     B  0  A DISK        07-APR-2014 14:11:39 1       1       YES        LEVEL0
377     B  1  A DISK        07-APR-2014 14:14:40 1       1       YES        LEVEL1
367     B  0  A DISK        07-APR-2014 14:11:34 1       1       YES        LEVEL0
378     B  1  A DISK        07-APR-2014 14:14:41 1       1       YES        LEVEL1
368     B  0  A DISK        07-APR-2014 14:11:34 1       1       YES        LEVEL0
380     B  1  A DISK        07-APR-2014 14:14:41 1       1       YES        LEVEL1
369     B  0  A DISK        07-APR-2014 14:11:37 1       1       YES        LEVEL0
379     B  1  A DISK        07-APR-2014 14:14:41 1       1       YES        LEVEL1
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
382     B  A  A DISK        07-APR-2014 14:14:47 1       1       YES        ARCHIVELOG
Media recovery start SCN is 1524335
Recovery must be done beyond SCN 1524339 to clear datafile fuzziness
Finished restore at 07-APR-2014 14:16:00

RMAN>

Query database to determine recovery SCN:

ARROW:(SYS@db01):PRIMARY> r
  1  SELECT
  2    MIN(checkpoint_change#) start_scn,
  3    GREATEST(MAX(checkpoint_change#),MAX(absolute_fuzzy_change#)) beyond_scn
  4  FROM v$backup_datafile
  5  WHERE incremental_level=(SELECT MAX(incremental_level) FROM v$backup_datafile WHERE incremental_level>=0)
  6*

 START_SCN BEYOND_SCN
---------- ----------
   1524335    1524339

ARROW:(SYS@db01):PRIMARY>

Why is all of this important?

It allows one to automate the process to validate backup without having to actually run “restore database preview”.

Tested on 11.2.0.4 database.

Categories: DBA Blogs

COLLABORATE minus 3

Mon, 2014-04-07 07:50

I always like to get to the location for a conference a day in advance so I can

  • Get accustomed to the time change
  • Get a feel for my way around the venue
  • Figure out where my room is
  • Establish a few landmarks so I do not wander aimlessly around the facility and hotel as though every voyage is a new life experience

COLLABORATE officially starts on Tuesday, though there are education sessions all day Monday facilitated by the three main groups responsible for the show – the IOUG, OAUG, and Quest International Users Group. So where did this animal called COLLABORATE come from one may wonder?

Rewind to about 2004. The three above-mentioned user groups each had their own show. Each reached out to Oracle for logistic and education support, something that the vendor was (and still is) happy to give. It was starting to become obvious that the marketplace upheaval was having a dramatic effect on user group conference attendance. At the same time Oracle expressed a desire to support fewer shows. You do the math – it only made sense. Why not have a 4-5 day mega conference and work with Oracle for many facets of support. Not only were the attendees of each show being asked to pick one or the other; Oracle was investing a massive number of personnel to support all three shows separately. It was a cumulative decision to amalgamate the shows and we wondered where it all would start.

With the blessing of the IOUG board I made one of those very first phone calls to one more people on the OAUG board and the rest is history. I do not remember who I spoke to first and there were probably a handful of feelers going out from other places in the IOUG infrastructure to OAUG bigwigs. I spoke to board member Donna Rosentrater  (@DRosentrater) and we jammed on what could/should become of a co-operative effort. We chatted a few times and the interest amongst board members of the IOUG and OAUG reflected cautious optimism that we could pull if off. Each user group had its own revenue stream from separate shows. We needed to embark down a path that would not put these at risk. That is what the brunt of the negotiations centered on and the work we did together led to the very first COLLBORATE at the Gaylord in Nashville in 2006.

Once the initial framework was established, it was time to turn the discussions over to the professionals. Both groups’ professional resources collaborated (hence the name maybe) and this mega/co-operative show became a reality. COLLABORATE 14 is the 9th show put on by Quest, OAUG, and IOUG. I am not going to say “this year’s show is going to be the best yet” as I believe that implicitly belittles previous successful events. Suffice to say, for what the user community needs from an information-sharing perspective – COLLABORATE is just what the doctor ordered.

Tomorrow’s a day off; wander aimlessly through Las Vegas tempted by curios, shops, food emporiums, and just about every other possible temptation one could think of. Sunday starts with a helicopter trip to the Grand Canyon and I went all out and forked over the extra $50 to sit in the convex bubble beside the pilot. There’s a bazillion vendors poised to whisk ine away to the canyon with a fly over the Hoover dam there or on the way back. I chose Papillon and am looking forward to the excitement of the day which starts at 5:10am with a shuttle to the site where the whirlybird takes off. Talk about taking one’s breath away.

Categories: DBA Blogs