Search

OakieTags

Who's online

There are currently 0 users and 42 guests online.

Recent comments

Affiliations

Oakies Blog Aggregator

How to Get the DBID when Instance in in NOMOUNT State

You lost your controlfile and the catalog. To restore the controlfile, you must know the DBID. Did you follow the advise to write down the DBID in a safe place? You didn't, did you? Well, what do you do next? Don't worry; you can still get the DBID from the header of the data files. Read on to learn how.

If you have lost your controlfile and the catalog database (or the database was not registered to a recovery catalog anyway), you  need to restore the controlfile first and then restore the other files. I wrote a blog post on that activity earlier. In summary, here is what you need to do to restore the controlfile from the backup:

You need the DBID. IF you don't know the DBID, don't panic. You can extract the DBID from the header of a datafile, assuming you have access to it. The database instance needs to up in NOMOUNT mode. Well, it has to be NOMOUNT because you haven't restored the controlfile yet, a major requirement for the mount operation. If you have the database mounted, this blog post is not for you since you have access to the V$DATABASE view and therefore the DBID. But at that point the DBID is not required anyway.

  1. Bring up the instance in nomount mode.
  2. SQL> startup nomount
    ORACLE instance started.

    Total System Global Area 6.8413E+10 bytes
    Fixed Size 2238616 bytes
    Variable Size 1.6777E+10 bytes
    Database Buffers 5.1540E+10 bytes
    Redo Buffers 93618176 bytes

  3. Set a tracefile identifier for easy identification of the trace file that will be generated.
  4. SQL> alter session set tracefile_identifier = arup;

    Session altered.

  5. Dump the first few blocks of the datafile. The file of the SYSTEM tablespace works perfectly. 10 blocks will do nice
  6. SQL> alter system dump datafile '+PROQA3DATA1/PROQA3/PROQA1_system_01.dbf' block min 1 block max 10;

    System altered.

  7. Check the trace file directory for a file with the term "ARUP" in it
  8. prolin1:/PROQA/orabase/diag/rdbms/PROQA3/PROQA31/trace>ls -l *ARUP*
    -rw-r--r-- 1 oracle asmadmin 145611 Apr 24 21:17 PROQA31_ora_61079250_ARUP.trc
    -rw-r--r-- 1 oracle asmadmin 146 Apr 24 21:17 PROQA31_ora_61079250_ARUP.trm
  9. Open that file. Here is an excerpt of that file.
  10. Trace file /PROQA/orabase/diag/rdbms/PROQA3/PROQA31/trace/PROQA31_ora_61079250_ARUP.trc
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options
    ORACLE_HOME = /PROQA/oracle/db1
    System name: AIX
    Node name: prolin1
    Release: 1
    Version: 7
    Machine: 0008F1CBD400
    Instance name: PROQA31
    Redo thread mounted by this instance: 0
    Oracle process number: 26
    Unix process pid: 61079250, image: oracle@prolin1(TNS V1-V3)


    *** 2014-04-24 21:17:16.957
    *** SESSION ID:(937.3) 2014-04-24 21:17:16.957
    *** CLIENT ID:() 2014-04-24 21:17:16.957
    *** SERVICE NAME:() 2014-04-24 21:17:16.957
    *** MODULE NAME:(sqlplus@prolin1 (TNS V1-V3)) 2014-04-24 21:17:16.957
    *** ACTION NAME:() 2014-04-24 21:17:16.957


    *** TRACE CONTINUED FROM FILE /PROQA/orabase/diag/rdbms/PROQA3/PROQA31/trace/PROQA31_ora_61079250.trc ***

    Start dump data block from file +PROQA3DATA1/PROQA3/PROQA1_system_01.dbf minblk 1 maxblk 10
    V10 STYLE FILE HEADER:
    Compatibility Vsn = 186646528=0xb200000
    Db ID=2553024456=0x982c0fc8, Db Name='PROQA3'
    Activation ID=0=0x0
    Control Seq=8419=0x20e3, File size=524288=0x80000
    File Number=1, Blksiz=8192, File Type=3 DATA
    Dump all the blocks in range:
    buffer tsn: 0 rdba: 0x00400002 (1024/4194306)
    scn: 0x071b.e7e3500f seq: 0x02 flg: 0x04 tail: 0x500f1d02

  11. Note the section marked in red. The DBID is prominently displayed there.
  12. Db ID=2553024456
  13. That's it. Now you have the DBID.

Really dumb triggers

Some people hate triggers, some people love triggers…

I am not that opinionated on them in either direction, but one thing I do hate, whether it be a trigger or not, is dumb code.  And today’s post just happens to be about dumb code in a trigger.

Consider this simple trigger (you see these everywhere pre 12c):

CREATE OR REPLACE TRIGGER MY_TRG
  BEFORE INSERT ON MY_TABLE 
  FOR EACH ROW
BEGIN
   SELECT MY_SEQ.NEXTVAL INTO :NEW.MY_PK_COL FROM DUAL;
END;
/

Seems harmless enough…and I’m not talking about whether we should be using ":new.col := seq.nextval", because under the covers it will do a select-from-dual anyway.

The reason the code is really dumb…is you are totally screwed if you need to some data maintenance…

Consider the following scenario:

Customer: "Last week, I accidentally deleted MY_PK_COL = 10, here’s what the row should look like, can you please put it back?"

Your response is simple…."No I cant".

Why ? Because you can never, ever re-insert MY_PK_COL = 10 if the sequence has advanced past 10.  All of your options are nasty…

a) reset the sequence value ?  What of other transactions taking place?

b) disable the trigger ? ditto.

Now people will get up on their soap box and say "Its a surrogate key, the value should be meaningless, it shouldn’t matter what it is" etc etc…and I admire your tenacious grip on the religious argument.  But that’s like saying "All databases should have referential integrity constraints!"…Well duh, but that’s not how the real world is :-(

Its just a dumb way of coding.  If you really need these kinds of triggers (hint: you don’t), then at least code them defensively:

CREATE OR REPLACE TRIGGER MY_TRG
  BEFORE INSERT ON MY_TABLE 
  FOR EACH ROW
  when ( new.MY_PK_COL is null )
BEGIN
   SELECT MY_SEQ.NEXTVAL INTO :NEW.MY_PK_COL FROM DUAL;
END;
/

so at least you’re not clobbering someone’s data. 

Estimate Index Size With Explain Plan (I Can’t Explain)

I discussed recently an updated MOS note that details the needs vs. the implications of rebuilding indexes. Following is a neat little trick if you want to very quickly and cheaply estimate the size of an index if it were to be rebuilt or a new index before you actually create the thing. I meant to blog about this sometime […]

Delphix shines sunlight in data’s cloudy skies

About year ago or more, Oracle came out with a way to create thin clone copies of a database in EM 12c called “Snap Clone”.

Screen Shot 2014-04-11 at 9.53.57 AM

Not sure this makes working with data any sunnier and certainly doesn’t add any sunlight to the cloud movement. Snap Clone technology has seen little adoption AFAIK. I’m not aware of a single customer reference yet. Why ? Because Snap Clone doesn’t solve the real problem. Snap Clone is complex, depends on cobbling other technologies together, and lacks in crucial features. The real solution is simplicity and end-to-end integration with data source syncing all the way to data provisioning. Snap Clone will provision database thin clones using specialized storage snapshots limited to ZFS and Netapp, but has no integrated solution for syncing with source data. Oracle’s technology is a storage snapshot management tool for Netapp and ZFS and simple storage snapshot technology has been around for almost 20 years with little adoption compared to the potential.

The real solution harnesses data virtualization. Data virtualization makes data available anywhere on the network and only stores data changes and shares duplicate data, meaning a copy is instant and takes up no space. Data virtualization is used in a new paradigm called Agile data. Similar to web browser technology leading to the internet technology usage explosion, the agile data revolution is in full swing thanks to the agile data technology from Delphix.  In less than 4 years, the Delphix agile data platform customers have grown to include Walmart, HP, IBM, Facebook, Ebay, Comcast, Macys, Prudential, Krogers, SAP, Proctor and Gamble, Gap, New York  Life,   etc.  Many of these customers see 2x application development output, 10x cost reduction, 1000s of hours of DBA work saved a year, petabytes of disk freed.

Why has Delphix been so successful?  Delphix is about agile data and data virtualization of all enterprise data, be it in a database by Oracle or Microsoft or in flat files, be it on premises or in the cloud. Delphix makes both the data as well as copies of that data immediately available anywhere. It provides solutions in a few mouse clicks to problems that were extremely difficult and time consuming before. With this kind of technology Delphix can do cloud migrations, datacenter migrations, DR, regulatory compliance…the sky’s the limit. For example:

  • Data Branching – branch data, databases and application stacks to provide immediate versions for QA from development and to manage in a space and time efficient manner. Maintain multiple versions of  application environments to support development teams who may need to patch previous versions while working on the current version or support multiple development teams who are working concurrently on different release versions.#222222;"> 
  • Cross database synchronized clones – one of the hardest projects IT teams have is provisioning multiple databases at the same time for data integration, development and testing. For example, financial closes often require synchronizing financial information from  several databases into a central financial system. If there are discrepancies, then it requires getting copies of all the systems at the same point in time – the financial close time – and isolating and correcting the discrepancies. Provisioning multiple databases at the same point in time is a daunting task, but with Delphix it is a simple out of the box functionality.#222222;"> 
  • Full Application Stack Cloning – Delphix will not only virtualize the database but also the application stack, and even Oracle binaries. For example, Delphix will provide thin clones of the full Oracle EBS stack including reconfiguring the EBS stack for the new clone environment.#222222;"> 
  • Live data archive – ability to archive many historical synchronized copies of full databases, data and application stacks stored in a fraction of the space and accessible in minutes. This capability is crucial for auditing and compliance support.#222222;"> 
  • Support for RAC, SQL Server, Postgres – linking and provisioning RAC databases is as easy as a single instance. Integrated automated support is already there for SQL Server and Postgres with Sybase in beta and more databases to come.

Delphix is as simple to install as a VMware VM. Everything is run in a few mouse clicks from a simple web UI.  After an initial link to source data, in few minutes, users can start provisioning Oracle databases, SQL Server databases or application data or binaries.

 

Screen Shot 2014-04-17 at 10.20.29 AM

 

 

 

#222222;">

Screen Shot 2014-04-23 at 4.27.12 PM

*1 One Oracle blog suggests using Data Guard as the test master, which means adding another technology stack to the mix without any automated management or integration not to mention requiring more Oracle licensing.

*2 Snap Clone may claim to be storage agnostic, but to use something like EMC for example requires installing Solaris on a machine and then attaching an EMC LUN to the Solaris box, having Solaris map ZFS onto the storage, then sharing the storage with EM 12c.

 

You don’t need to take my advice. Instead, ask them to prove it:

#500050;">
  • Ask for references.  Find at least three references and ask them about their experiences.
  • Prove it.  Test setup time.  Test linking time. Test provisioning time. Test replication of VDBs in one site/cloud to another site/cloud and across heterogeneous storage. Test cross platform cloning from UNIX to Linux (Delphix does this automatically). Provision clones of MS SQL databases. Provision a full EBS stack (Delphix does this automatically). Provision a SAP sandbox. Test on SSD storage like Pure Storage, Violin, XtremIO. Provision two or more separate databases at the same point in time which supports the case of an application that uses more than one database. Branch multiple versions of the same master VBD. Make branches of the VDB branches.

Delphix has years of industry production time  and many Fortune 500 customers, including the Fortune #1 who can attest to the robustness and power of the solution. Delphix has an expert and rapid support team that has won praises from their customers. Delphix is rapidly becoming the industry standard for data and database virtualization.

 

Top Misconceptions about DBaaS Snapclone

The last couple weeks I’ve been lucky enough to get time with a new ZFSSA Simulator, (it will be out soon for everyone, so you’ll have to just play with the current one available, patience grasshopper! :)) and spent checking out the newest features available with Database as a Service, (DBaaS) Snapclone via Enteprise Manager 12c.  I’m really thrilled with Snapclone-  In two previous positions, I spent considerable time finding new ways of speeding up RMAN duplicates to ease the stress of weekly datamart builds that were sourced off of production and this feature would have been a lifesaver back then!

As Oracle’s DBaaS offering is so full featured, I think its easy to have misconceptions about the product or find blog posts and documentation on earlier releases that lead to misconceptions. Due to this, we thought it might help if I tried to dispel some of myths, letting more folks realize just how incredible Snapclone really is!

Misconception #1- Snapclone only works with the ZFS Storage Appliance

DBaaS does offer a fantastic hardware solution that requires a hardware NAS like Netapp and ZFS/ZFSSA, but that’s not the only option.  There is also the software solution which  can work on ANY storage.  There’s no requirement for the test master database, (used to track changes and save considerable space vs. a traditional cloning method…) on where it must reside and that means it can be on different storage than production.

There are benefits to both the hardware and software solutions of Snapclone.  Keep in mind, Oracle prefers to support hardware and software that are engineered to work together, but they realize that not all customers will have a ZFS or NetApp Solution in place, so they ensure that Snapclone solutions are available to all storage a customer may have in their shop.  Some of those benefits and requirements are listed below, but you can clearly see, Snapclone does not have a requirements of ZFS or NetApp:

tm_profile_2

Misconception #2-  Snapclone Requires a Cloud Implementation

It is true that Snapclone requires database pools to be configured and built, but this is in the environment’s best interest to ensure that it is properly governed placement is enforced. This feature is separate from an actual “cloud” implementation and the two shouldn’t be confused.  There are often dual definitions for terms and cloud is no different.  We have private cloud, public cloud, the over-all  term for cloud technology and then we have database pools that are part of Snapclone and those are configured in the cloud settings of DBaaS.  They should not be confused with having to implement “cloud”.

Misconception #3- Unable to Perform Continuous Refresh on Test Master Database

There are a couple easy ways to accomplish a refresh of the test master database used to clone from production.  Outside of the traditional profiles that would schedule a snapshot refresh, DBAs can set up active or physcial dataguard or can use storage replication technologies that they already have in place.  To see how these look from a high level, let’s first look at a traditional refresh of a test master database:

tm_profile_1

 

Now you’ll notice that the diagram states the test master is “regularly” refreshed with a current data set from production and if you inspect the diagram below, you will see an example of an example of a software or hardware refresh scenario to the test master database, (using datamasking and subsetting if required) and then creating Snap Clones.

 

tm_using_testmaster

Now as I said earlier, you can use a standby database, too, to perform a clone.  The following diagram shows the simplicity of a standby with Dataguard or Golden Gate.  Notice where Snap Clone takes over-  it’s at the Standby database tier, so you still get the benefit of the feature, but can utilize comfortable technology such as Dataguard or Golden Gate:

tm_using_standby

Misconception #4- Snapclone Can Only be Used with DB12c

Snapclone works with any supported version of the database 10gR2 to 12c. Per the DBaaS team of experts, it may work on earlier versions, they just haven’t certified it, so if there are any guinea pigs out there that want to test it out, we’d love to hear about your experience!

The process of Snapclone is very simple once the environment is set up.  With the Rapid Start option, it’s pretty much the “Easy button” for setting up the DBaaS environment and the process solidified once service templates are built and the service request has been submitted in the Self Service Portal.  There isn’t anymore confusion surrounding where an Oracle home installation should be performed or what prefix is used for database naming convention and other small issues that can end up costing an environment in unnecessary complexity later on.

Misconception #5- Snapclone doesn’t Support Exadata

A couple folks have asked me if Snapclone is suppored on Exadata and in truth, Exadata with Snapclone offers a unique opportunity with consolidations and creating a private cloud for the business.  I’ll go into it in depth in another blog post, as it deserves it’s own post, but the following diagram does offer a high level view of how Snapclone can offer a really cool option with Exadata:

tm_exadata

There are so many features that are provided by Snapclone that its difficult to keep on top of everything, but trying to dispel the misconceptions is important so people don’t miss out on this impressive opportunity to save companies time, money and storage.  I know my whitepaper was over 40 pages on DBaaS and I only focused on NetApp and ZFS, so I do understand how easy it is, but hopefully this first post will get people investigating DBaaS options more!



Tags:  


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [Top Misconceptions about DBaaS Snapclone], All Right Reserved. 2014.

“external table write” wait events… but I am only running a query?

I was helping a customer debug some external table load problems.  They are developing some code to do massive inserts via external tables.  As the code was being tested, we saw a fair number of tests that were doing simple queries of an external table.  I expected to see “external table read” wait events, but was surprised when we saw more “external table write” wait events than reads.

I thought this was due to writes to the “log” file and possible “bad” file, but I had to be sure.  I searched the docs but could not find reference to this wait event.  I specifically was seeing the following:

WAIT #139931007587096: nam='external table write' ela= 7 filectx=139931005791096 file#=13 size=41 obj#=-1 tim=1398264093597968
WAIT #139931007587096: nam='external table write' ela= 3 filectx=139931005791096 file#=13 size=89 obj#=-1 tim=1398264093597987

I searched on how to debug the filectx and file# but still couldn’t find anything.  So, I resorted to my good old friend “strace” from the Linux side of the house.  By running “strace” on the oracle shadow process, I was able to find indeed that these write events were to going to the LOG file for the external table.

mylap:EXTW glennf$ egrep 'write\(13' strace-truss-trace.txt
 write(13, "\n\n LOG file opened at 04/23/14 0"..., 41) = 41
 write(13, "KUP-05004: Warning: Intra sour"..., 100) = 100
 write(13, "Field Definitions for table ET_T"..., 36) = 36
 write(13, " Record format DELIMITED, delim"..., 43) = 43
 write(13, " Data in file has same endianne"..., 51) = 51
 write(13, " Rows with all null fields are "..., 41) = 41
 write(13, "\n", 1) = 1
 write(13, " Fields in Data Source: \n", 26) = 26
 write(13, "\n", 1) = 1
 write(13, " ID "..., 47) = 47
 write(13, " Terminated by \"7C\"\n", 25) = 25
 write(13, " Trim whitespace same as SQ"..., 41) = 41
 write(13, " TDATE "..., 46) = 46
  ....
  ....

Each time you open an external table, the time is logged as well the table definition.  We have some very wide tables so there was actually more data logged than queried.  With the proper amount of data now in the dat files, we are indeed seeing more “external table read” requests as expected.   Regardless, this was a fun exercise.

So, the moral of the story… Sometimes you have turn over a few rocks and drill down a bit to find the pot of gold.

 

Filed under: Exadata, Linux, Oracle, Storage, Uncategorized

NL History

Even the simplest things change – here’s a brief history of nested loop joins, starting from 8i, based on the following query (with some hints):

select
	t2.n1, t1.n2
from
	t2,t1
where
	t2.n2 = 45
and	t2.n1 = t1.n1
;

There’s an index to support the join from t2 to t1, and I’ve forced an (unsuitable) index scan for the predicate on t2.

Basic plan for 8i (8.1.7.4)

As reported by $ORACLE_HOME/rdbms/admin/utlxpls.sql.
Note the absence of a Predicate Information section.

Plan Table
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |   225 |    3K|   3038 |       |       |
|  NESTED LOOPS             |          |   225 |    3K|   3038 |       |       |
|   TABLE ACCESS BY INDEX RO|T2        |    15 |  120 |   3008 |       |       |
|    INDEX FULL SCAN        |T2_I1     |    15 |      |      8 |       |       |
|   TABLE ACCESS BY INDEX RO|T1        |     3K|   23K|      2 |       |       |
|    INDEX RANGE SCAN       |T1_I1     |     3K|      |      1 |       |       |
--------------------------------------------------------------------------------

Basic plan for 9i (9.2.0.8)

As reported by a call to a home-grown version of dbms_xplan.display_cursor() with statistics_level set to all.

Note the “prefetch” shape of the body of the plan but the inconsistency in the numbers reported for Rows, Bytes, and Cost seem to be reporting the “traditional” 8i values transposed to match the new arrangement of the operations. There’s also a little oddity in the A-rows column in line 2 which looks as if it is the sum of its children plus 1 when the size of the rowsource is (presumably) the 225 rowids used to access the table.

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     |  Name       | Rows  | Bytes | Cost  | Starts  | A-Rows  | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |   225 |  3600 |  3038 |         |         |         |
|   1 |  TABLE ACCESS BY INDEX ROWID  | T1          |    15 |   120 |     2 |     1   |    225  |   3061  |
|   2 |   NESTED LOOPS                |             |   225 |  3600 |  3038 |     1   |    241  |   3051  |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2          |    15 |   120 |  3008 |     1   |     15  |   3017  |
|   4 |     INDEX FULL SCAN           | T2_I1       |  3000 |       |     8 |     1   |   3000  |     17  |
|*  5 |    INDEX RANGE SCAN           | T1_I1       |    15 |       |     1 |    15   |    225  |     34  |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T2"."N2"=45)
   5 - access("T2"."N1"="T1"."N1")

Basic plan for 10g (10.2.0.5)

As reported by a call to dbms_xplan.display_cursor() with statistics_level set to all.

No change from 9i.

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      0 |        |      0 |00:00:00.01 |       0 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | T1    |      1 |     15 |    225 |00:00:00.03 |    3061 |
|   2 |   NESTED LOOPS                |       |      1 |    225 |    241 |00:00:00.03 |    3051 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2    |      1 |     15 |     15 |00:00:00.03 |    3017 |
|   4 |     INDEX FULL SCAN           | T2_I1 |      1 |   3000 |   3000 |00:00:00.01 |      17 |
|*  5 |    INDEX RANGE SCAN           | T1_I1 |     15 |     15 |    225 |00:00:00.01 |      34 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T2"."N2"=45)
   5 - access("T2"."N1"="T1"."N1")

Basic plan for 11g (11.2.0.4)

As reported by a call to dbms_xplan.display_cursor() with statisics_level set to all

Note how the nested loop has now turned into two NESTED LOOP operations – potentially opening the way for a complete decoupling of index access and table access. This has an interesting effect on the number of starts of the table access by rowid for t1, of course. The number of buffer gets for this operation looks surprisingly low (given that it started 225 times) but can be explained by the pattern of the data distribution – and cross-checked by looking at the “buffer is pinned count” statistic which accounts for most of the table visits.


-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |    225 |00:00:00.01 |    3048 |
|   1 |  NESTED LOOPS                 |       |      1 |    225 |    225 |00:00:00.01 |    3048 |
|   2 |   NESTED LOOPS                |       |      1 |    225 |    225 |00:00:00.01 |    3038 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2    |      1 |     15 |     15 |00:00:00.01 |    3013 |
|   4 |     INDEX FULL SCAN           | T2_I1 |      1 |   3000 |   3000 |00:00:00.01 |      13 |
|*  5 |    INDEX RANGE SCAN           | T1_I1 |     15 |     15 |    225 |00:00:00.01 |      25 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T1    |    225 |     15 |    225 |00:00:00.01 |      10 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T2"."N2"=45)
   5 - access("T2"."N1"="T1"."N1")

There is, however, a second possible plan for 11g. The one above is the “NLJ Batching” plan, but I could have hinted the “NLJ prefetch” strategy, which takes us back to the 9i execution plan (with a very small variation in buffer visits).

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      0 |        |      0 |00:00:00.01 |       0 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | T1    |      1 |     15 |    225 |00:00:00.01 |    3052 |
|   2 |   NESTED LOOPS                |       |      1 |    225 |    241 |00:00:00.01 |    3042 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2    |      1 |     15 |     15 |00:00:00.01 |    3017 |
|   4 |     INDEX FULL SCAN           | T2_I1 |      1 |   3000 |   3000 |00:00:00.01 |      17 |
|*  5 |    INDEX RANGE SCAN           | T1_I1 |     15 |     15 |    225 |00:00:00.01 |      25 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T2"."N2"=45)
   5 - access("T2"."N1"="T1"."N1")

Base plan for 12c (12.1.0.1)

As reported by a call to dbms_xplan.display_cursor() with statistics_level set to all.
Note that the table access to t2 in line 3 is described as “batched” (a feature that can be disabled by the /*+ no_batch_table_access_by_rowid(alias) */  hint) otherwise the plan matches the 11g plan.


---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |      1 |        |    225 |00:00:00.01 |    3052 |
|   1 |  NESTED LOOPS                         |       |      1 |        |    225 |00:00:00.01 |    3052 |
|   2 |   NESTED LOOPS                        |       |      1 |    225 |    225 |00:00:00.01 |    3042 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2    |      1 |     15 |     15 |00:00:00.01 |    3017 |
|   4 |     INDEX FULL SCAN                   | T2_I1 |      1 |   3000 |   3000 |00:00:00.01 |      17 |
|*  5 |    INDEX RANGE SCAN                   | T1_I1 |     15 |     15 |    225 |00:00:00.01 |      25 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | T1    |    225 |     15 |    225 |00:00:00.01 |      10 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T2"."N2"=45)
   5 - access("T2"."N1"="T1"."N1")

Of course 12c also has the “prefetch” version of the plan available; and again “batched” access appears – for both tables in this case – and again the feature can be disabled individually by hints addressed at the tables:


---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |      0 |        |      0 |00:00:00.01 |       0 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED  | T1    |      1 |     15 |    225 |00:00:00.01 |    3052 |
|   2 |   NESTED LOOPS                        |       |      1 |    225 |    225 |00:00:00.01 |    3042 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2    |      1 |     15 |     15 |00:00:00.01 |    3017 |
|   4 |     INDEX FULL SCAN                   | T2_I1 |      1 |   3000 |   3000 |00:00:00.01 |      17 |
|*  5 |    INDEX RANGE SCAN                   | T1_I1 |     15 |     15 |    225 |00:00:00.01 |      25 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T2"."N2"=45)
   5 - access("T2"."N1"="T1"."N1")

In these examples the difference in work done by the different variations and versions is negligible, but there may be cases where the pattern of data distribution may change the pattern of logical I/Os and buffer pins – which may affect the physical I/O. In this light it’s interesting to note the hint /*+ cluster_by_rowid(alias) */ that was introduced in 11.2.0.4 but disappeared by 12c changing the 11g plan as follows:


----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|   1 |  TABLE ACCESS BY INDEX ROWID  | T1    |      1 |     15 |    225 |00:00:00.01 |     134 |       |       |          |
|   2 |   NESTED LOOPS                |       |      1 |    225 |    241 |00:00:00.01 |     124 |       |       |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2    |      1 |     15 |     15 |00:00:00.01 |      99 |       |       |          |
|   4 |     SORT CLUSTER BY ROWID     |       |      1 |   3000 |   3000 |00:00:00.01 |       8 |   142K|   142K|  126K (0)|
|   5 |      INDEX FULL SCAN          | T2_I1 |      1 |   3000 |   3000 |00:00:00.01 |       8 |       |       |          |
|*  6 |    INDEX RANGE SCAN           | T1_I1 |     15 |     15 |    225 |00:00:00.01 |      25 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T2"."N2"=45)
   6 - access("T2"."N1"="T1"."N1")

Note the effect appearing at line 4 – and the extraordinary effect this has on the buffer visits (so significant that I did a follow-up check on v$mystat to see if the figures were consistent). This type of rowid sorting is, of course, an important fix for an Exadata issue I described some time ago, and I had assumed that the “batched” concept in the 12c plan was in some way enabling it – although the 12c rowsource execution stats don’t seem to bear that idea out.

Footnote:

You may also want to read the following note on the 12c batched rowid.

RHEL7 and Oracle Linux 7 beta

Nearly two weeks ago, Oracle announced the Oracle Linux 7 Beta 1. Being the Linux fanboy I am, I downloaded it straight away from here.

Oracle Linux is a clone of the Red Hat Enterprise Linux (RHEL) distribution. The RHEL7 beta, and therefore OL7 beta, distro is based on a cut of Fedora 19, although depending on who you ask, it’s possibly more a mix of Fedora 18, 19 and 20… Suffice to say, there are a lot of changes compared to the RHEL6/OL6 distribution.

As I’ve mentioned several times before, my desktop at home is running Fedora 20, so I’m pretty used to most of the changes, but I’ve not written much about them, apart from the odd blog post. It’s not a high priority for me, since I’m not a sysadmin, but I’ll be updating/rewriting a few of the Linux articles on the site to include the new stuff.

When Surachart Opun mentioned having to look at systemd and firewalld, it seemed like the perfect opportunity to update my firewall and services articles. You can see the new versions here.

RHEL7/OL7 is only in beta, and even after the production release I’m sure it will be a long time before Oracle actually certify any products against it, but if you are not a Fedora user, it’s probably worth you having a play around with this stuff.

Cheers

Tim…


RHEL7 and Oracle Linux 7 beta was first posted on April 23, 2014 at 10:15 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Presenting at ODTUG Kscope14 Conference in Seattle June 22-26 2014

  Just a short note to say I’ll be presenting at the Oracle Development Tools User Group (ODTUG) Kaleidoscope 14 Conference this year in beautiful Seattle, Washington on June 22-26 2014. I had a fantastic time when I attended this conference a few years ago when it was held in Monterey so I’m really looking forward to […]

Restoring Controlfile When AUTOBACKUP Fail

Allow me to present the snapshot of a day from the life of John--the DBA at Acme Bank. On this particular day a database John manages crashed entirely and had to be restored from the backup. He takes regular (backupset) RMAN backups to tape. Since everything--including the controlfile--had crashed, John had to first restore the controlfile and then restore the database. The controlfile is always backed up with the backup database command. John was sure of that. However, restore controlfile from autobackup gave the error:

RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

Without the controlfile, the recovery was stuck, even though all the valid pieces were there. It was a rather alarming situation. Others would have panicked; but not John. As always, he managed to resolve the situation by completing recovery. Interested to learn how? Read on.

Background

Since controlfile was also damaged, the first task at hand was to restore the controlfile. To restore the controlfile, John needs a very special information: the DBID--database identifier. This is not something that would be available until the database is at least mounted. In unmounted state--which is how the database is in right now--John couldn't just go and get it from the database. Fortunately, he follows a best practice: he records the DBID in a safe place.

This is  the command John used to restore the controlfile from the backup. The commands assume the usage of Data Domain Boost, the media management layer (MML) plugin for Data Domain backup appliance; but it could apply to any MML--NetBackup, TSM, etc.

SQL> startup nomount;

RMAN> run {
2>      allocate channel c1 type sbt_tape PARMS 
'BLKSIZE=1048576,SBT_LIBRARY=/prodb/oradb/db1/lib/libddobk.so,ENV=(STORAGE_UNIT=DDB01,BACKUP_HOST=prolin1.proligence.com,ORACLE_HOME=/prodb/oradb/db1)';
3>      send 'set username ddboostadmin password password servername prolin1.proligence.com';
4>      set dbid = 2553024456;
5>      restore controlfile from autobackup;
6>      release channel c1;
7> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=1045 device type=SBT_TAPE
channel c1: Data Domain Boost API
sent command to channel: c1

executing command: SET DBID

Starting restore at 22-APR-14

channel c1: looking for AUTOBACKUP on day: 20140422
channel c1: looking for AUTOBACKUP on day: 20140421
channel c1: looking for AUTOBACKUP on day: 20140420
channel c1: looking for AUTOBACKUP on day: 20140419
channel c1: looking for AUTOBACKUP on day: 20140418
channel c1: looking for AUTOBACKUP on day: 20140417
channel c1: looking for AUTOBACKUP on day: 20140416
channel c1: no AUTOBACKUP in 7 days found

released channel: c1

RMAN-00571:===========================================================
RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:===========================================================
RMAN-03002: failure of restore command at 04/22/2014 16:08:25
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

So, RMAN couldn't locate the backup of the controlfile. John knew that by default, RMAN searches only 7 days of backup. Thinking that perhaps the controlfile was somehow not backed up in the last seven days, he expanded the search to 20 days, using the special parameter maxdays, shown below:

RMAN> run {
2>      allocate channel c1 type sbt_tape PARMS 'BLKSIZE=1048576,SBT_LIBRARY=/prodb/oradb/db1/lib/libddobk.so,ENV=(STORAGE_UNIT=DDB01,BACKUP_HOST=prolin1.proligence.com,ORACLE_HOME=/prodb/oradb/db1)';
3>      send 'set username ddboostadmin password password servername prolin1.proligence.com';
4>      set dbid = 2553024456;
5>      restore controlfile from autobackup maxdays 20;
6>      release channel c1;
7> }

allocated channel: c1
channel c1: SID=1045 device type=SBT_TAPE
channel c1: Data Domain Boost API
sent command to channel: c1
executing command: SET DBID

Starting restore at 22-APR-14
channel c1: looking for AUTOBACKUP on day: 20140422
channel c1: looking for AUTOBACKUP on day: 20140421
channel c1: looking for AUTOBACKUP on day: 20140420
channel c1: looking for AUTOBACKUP on day: 20140419
channel c1: looking for AUTOBACKUP on day: 20140418
channel c1: looking for AUTOBACKUP on day: 20140417
channel c1: looking for AUTOBACKUP on day: 20140416
channel c1: looking for AUTOBACKUP on day: 20140415
channel c1: looking for AUTOBACKUP on day: 20140414
channel c1: looking for AUTOBACKUP on day: 20140413
channel c1: looking for AUTOBACKUP on day: 20140412
channel c1: looking for AUTOBACKUP on day: 20140411
channel c1: looking for AUTOBACKUP on day: 20140410
channel c1: looking for AUTOBACKUP on day: 20140409
channel c1: looking for AUTOBACKUP on day: 20140408
channel c1: looking for AUTOBACKUP on day: 20140407
channel c1: looking for AUTOBACKUP on day: 20140406
channel c1: looking for AUTOBACKUP on day: 20140405
channel c1: looking for AUTOBACKUP on day: 20140404
channel c1: looking for AUTOBACKUP on day: 20140403
channel c1: no AUTOBACKUP in 20 days found

released channel: c1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/22/2014 16:17:56
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece  

No luck; it gave the same error. So--John concluded--it was not an issue with the absence of controlfile backup. Something else caused the backup of controlfile to be invisible. He did, however, know that the controlfiles are backed up along with the regular backups. Without the database in mounted mode, he couldn't find out the location of those controlfile backups. If this database was registered to a catalog, he could have got that information from the catalog; but unfortunately, being a new database, it was not yet registered. That avenue was closed.

He did, however, follow another best practice--saving the rman log files. As a rule, he sends the RMAN output logs to the tape along with the backup. He recalled the most recent backup log and checked the log for the name of the backup piece. Here is an excerpt from the log:

... output truncated ...
channel c8: starting piece 1 at 21-APR-14
channel c8: finished piece 1 at 21-APR-14
piece handle=#ff6666;">14p69u7q_1_1 tag=TAG20140421T141608 comment=API Version 2.0,MMS Version 1.1.1.0
channel c8: backup set complete, elapsed time: 00:00:01
channel c5: finished piece 1 at 21-APR-14
piece handle=#ff6666;">10p69rhb_1_1 tag=TAG20140421T141608 comment=API Version 2.0,MMS Version 1.1.1.0
channel c5: backup set complete, elapsed time: 00:47:33
channel c6: finished piece 1 at 21-APR-14
... output truncated ...

Looking at the output, John notes the names of the backup pieces created, listed next to "piece handle"--14p69u7q_1_1, 10p69rhb_1_1, etc.He still did not know exactly which one contained the controlfile backup; but it was not difficult to try them one by one. He tried to get the controlfile from the first backuppiece, using the following command where he used a special clause: restore controlfile from a location.

RMAN> run {
2>      allocate channel c1 type sbt_tape PARMS 'BLKSIZE=1048576,SBT_LIBRARY=/prodb/oradb/db1/lib/libddobk.so,ENV=(STORAGE_UNIT=DDB01,BACKUP_HOST=prolin1.proligence.com,ORACLE_HOME=/prodb/oradb/db1)';
3>      send 'set username ddboostadmin password password servername prolin1.proligence.com';
4>      set dbid = 2553024456;
5>      restore controlfile from '14p69u7q_1_1';
6>      release channel c1;
7> }

allocated channel: c1
channel c1: SID=1045 device type=SBT_TAPE
channel c1: Data Domain Boost API

sent command to channel: c1
executing command: SET DBID
Starting restore at 22-APR-14

channel c1: restoring control file
channel c1: restore complete, elapsed time: 00:01:25
output file name=+prodb3CTL1/prodb3/control01.ctl
output file name=+prodb3DATA1/prodb3/control02.ctl
output file name=+prodb3INDX1/prodb3/control03.ctl

Finished restore at 22-APR-14
released channel: c1  

It worked; the controlfile was restored! If it hadn't worked, John would have tried the other backup pieces one by one until he hit the one with the controlfile backup. After this, John mounted the database.

RMAN> alter database mount;
database mounted

The rest was easy; all he had to do was to issue "restore database" and "recover database using backup controlfile". The first thing John did after the database was mounted was checking the controlfile autobackup setting:

RMAN> show CONTROLFILE AUTOBACKUP;

RMAN configuration parameters for database with db_unique_name prodb3 are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF; #default

Well, lesson learned. John immediately changed it to ON.

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

Someone suggested that he could have tried to restore the controlfile from the TAG instead of the actual backup piece. Had he attempted the restore from the TAG, he would have got a different error:

RMAN> run {
2>      allocate channel c1 type sbt_tape PARMS 'BLKSIZE=1048576,SBT_LIBRARY=/prodb/oradb/db1/lib/libddobk.so,ENV=(STORAGE_UNIT=DDB01,BACKUP_HOST=prolin1.proligence.com,ORACLE_HOME=/prodb/oradb/db1)';
3>      send 'set username ddboostadmin password password servername prolin1.proligence.com';
4>      set dbid = 2553024456;
5>      restore controlfile from tag=TAG20140421T141608;
6>      release channel c1;
7> }

allocated channel: c1
channel c1: SID=1045 device type=SBT_TAPE
channel c1: Data Domain Boost API

sent command to channel: c1
 
executing command: SET DBID
 
Starting restore at 22-APR-14
 
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/22/2014 16:10:04
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

So that command could not have helped John.

No Backup of Controlfile

Let's consider another scenario: there was no backup of controlfile. Dreaded as it sounds, it's still not end of the world. John could create the controlfile from a backup located somewhere else. This special backup could be created by two special commands:

SQL> alter database backup controlfile to '/tmp/cont.dbf'
2 /

Database altered.

The above command creates a copy of the controlfile with the data as of the time of the command. Another command is:

SQL> alter database backup controlfile to trace as '/tmp/cbk.trc' reuse;

Database altered.

This command creates a text file that you can use as SQL statement (after some minor editing) to create a controlfile. The major difference between these two approaches is that the first approach produces a snapshot of the controlfile as of that time, along with all the data--the backup, the archived logs, etc. The second approach creates a brand new "blank" controfile that you must feed to bring up. John uses both options as a Plan B. On another post we will see how he saved the day using these two special controlfile backups.

Takeaways

What did you learn from the story. Here are some key takeaways:

  1. Always write down the DBID of all your databases somewhere. If you use a recovery catalog, it's there; but it's good to note it down separately. This number does not change unless you use NID utility; so recording once is enough.
  2. Always configure controlfile autobackup. The default is OFF; make it ON.
  3. Always save the backup log files. In a crunch, they yield valuable information otherwise not available.
  4. When controlfile is not found, you can use restore controlfile from 'location' syntax in RMAN to pull the controlfile from the location. If that location does not have a controlfile backup, don't worry; just try all available locations. One might contain what you are looking for. You have nothing to lose but everything to gain.
  5. Always use a script for this type RMAN restore activities instead of typing at the prompt. You will find changing data, e.g. various backup locations, easier and make less mistakes that way.
  6. Always create a backup controlfile everyday, even if you don't think you need it. You may someday and you will thank yourself when you do.