Skip navigation.

DBA Blogs

Active Data Guard – what does it mean?

The Oracle Instructor - Mon, 2014-02-17 03:10

There are misconceptions and half-truths about that term that I see time after time again in forums, postings and comments.

Some people think that Active Data Guard is a fancy marketing term for Standby Databases in Oracle. Wrong, that is just plain Data Guard :-)

Most people think that Active Data Guard means that a Physical Standby Database can be used for queries while it is still applying redo. Not the whole truth, because that is just one featureReal-Time Query – which is included in the Active Data Guard option.

Active Data Guard is an option, coming with an extra charge. Active is supposed to indicate that you can use the standby database for production usage – it is not just waiting for the primary database to fail.

In 11g, Active Data Guard includes three features:

  • Real-Time Query
  • Automatic Block Media Recovery
  • Block Change Tracking on the physical standby

In 12c, Active Data Guard got even more enhanced and includes now the features:

  • Real-time Query
  • Automatic Block Media Recovery
  • Block Change Tracking on the physical standby
  • Far Sync
  • Real-Time Cascade
  • Global Data Services
  • Application Continuity
  • Rolling Upgrade using DBMS_ROLLING

The bad news is that many of the 12c Data Guard New Features require Active Data Guard


Tagged: Active Data Guard, Data Guard
Categories: DBA Blogs

12c Enhanced Online Index DDL Operations (Lady Godiva’s Operation)

Richard Foote - Mon, 2014-02-17 00:41
In my last couple of posts, I discussed how table partitions can be moved online since 12c, keeping all indexes in sync as part of the process. 12c also introduced enhancements to a number of index related DDL statements, removing blocking locks and making their use online and far less intrusive. The following commands now have a new ONLINE […]
Categories: DBA Blogs

Partner Webcast - Oracle WebLogic Server & Oracle Database 12c Integration

Oracle WebLogic Server 12c, the latest generation of the leading Enterprise-class Java EE Application Server, and Oracle Database 12c, the latest release of the world’s #1 database, have both been...

We share our skills to maximize your revenue!
Categories: DBA Blogs

ora 600's always make me grumpy ... ORA-1555 / ORA-600 [ktbdchk1: bad dscn] #8895202

Grumpy old DBA - Sat, 2014-02-15 09:40
These days at least for me seeing an ORA 600 is a relative rare ( thank god ) occurrence.  They always raise your blood pressure sometimes to unhealthy levels.  Looking at one that at first glance hints at possible block corruption ... not good.

This bug 8895202 was fixed already in current environment but not "enabled" ( so thanks so much ... what use is a bug that is fixed but not enabled to be fixed ).  Apparently can happen in active data guard environment after switchover/switchback?

Looks like bad interaction of commit scn and itl scn in ( index blocks )?

Good news is ( rarely do 600's give you good news ) is can enable this dynamically ... scope=both ...

Although this fix is included in 11.2.0.2 / 11.2.0.3, it has to be enabled by  setting "_ktb_debug_flags"=8;  Rediscovery Notes ORA-1555 / ORA-600 [ktbdchk1: bad dscn] / ktbGetDependentScn /  Dependent scn violations as itl has higher commit scn than block scn. This happens in a Physical Standby database after a switchover.  DBVERIFY (with fix of Bug 7517208) reports:   itl[<itl_id>] has higher commit scn(aaa.bbb) than block scn (xx.yy)   Page <Block#> failed with check code 6056  There is NO DATA CORRUPTION in the block. Workaround This fix is the workaround.  It doesn't prevent to have a higher ITL SCN than the commit scn (csc). With this fix if parameter _ktb_debug_flags = 8 the SCN is repaired when block is cleaned  out (eg: block update).  While blocks are not touched dbverify still reports 6056 errors Sometimes the fix may not repair the block and the index may need rebuilding.

Categories: DBA Blogs

SQL Server Integrity Check – A Necessary Routine

Pythian Group - Fri, 2014-02-14 15:04

Today I’ll be discussing Integrity Check on SQL Server – we have some very good info in the community, but I’d like to summarize my thoughts here. When a DBA talks about Integrity Check, the conversation is usually redirected to one subject: CheckDB. And yes, we are going to talk about it in today’s post. :)

The DBCC CheckDB is a medicine of sorts that can prevent headaches, and remedy possible damages. But it should be used with caution: Without a well-planned backup strategy you can lose data. Even with all of the warnings about the importance of Integrity Check, it’s not uncommon to see instances without it.

“I’m a Manager just trying to understand this… Could you explain this at a high level?”

Let’s cut the blah, blah, blah and go ahead with this… I’ll introduce the CheckDB for those who have never heard of it – probably less technical individuals: The CheckDB command is a Database Console Command, or simply DBCC, categorized as a Validation DBCC. This is a necessary step in the entire instance maintenance, where we can detect, and in some cases repair a possible problem with the database integrity. Running the DBCC CheckDb regularly will help to anticipate problems, and even a unexpected shutdown of the instance. We’ll go into more detail a bit later…

“What’s the objective of the CheckDB?”

CheckDB has two roles: The main objective is the integrity check itself, and the second is the correction of the findings. Please not that the CheckDB verifies the database structure and not the data consistency.

Integrity Check: The command CheckDB follows a few steps on its execution, passing from the following phases:

  • Verification of the allocated structures, i.e. GAM, SGAM, PFS, IAM…
  • Verification of table consistency, including all of its indexes.
  • Verification of the system catalogs.

Correction: After the CheckDB “standard execution” as described above, we will have information about possible damaged pages. The CheckDB itself can fix these damages.

CheckDB has an option that allows the correction of the database structure, but the data allocated into the repaired page will be lost. This option is “REPAIR_ALLOW_DATA_LOSS”, and this option changes the damaged page for a new one. This way, we save the integrity of the database. Note that we are talking about the physical structure – not about data integrity, which is why we lose data. After the execution of this command, a verification based on backups should be made in order to identify and recover the lost data.

Another option is “REPAIR_REBUILD” used to correct non clustered indexes. On this option we don’t lose data. Even though we have options to correct pages, there are few limitations:

  • PFS pages, system tables and data purity detected errors aren’t fixable by CheckDB.
  • To repair using CheckDB, the database should be on SINGLE_USER mode.

“How is the CheckDB executed?”

Not too many people are curious to look deeper and discover more about this, but the CheckDB doesn’t run directly over the database. A hidden database snapshot is created and the process runs based on this snapshot. Many people only realize this when the CheckDB execution fails, and once they begin digging on it, they find out about “some snapshot” that failed the creation.

While we’re on the topic, do you know what to do if the snapshot creation fail? I can see 3 options:

  • Fix the permission to create the snapshot.
  • Execute the CheckDB with the TABLOCK option. This can bring problems, as the DB will be a target of locks.
  • Create a process (job) doing the following steps:
    1. Create a snapshot of the database.
    2. Run the DBCC CheckDB on this snapshot. Yes, the DBCC CheckDB can run over databse snapshots. And it’s obvious that a snapshot of a snapshot won’t be created.

“When and where should we execute an Integrity Check?”
It’s recommended to check  the integrity of all the databases, without exception! The best approach is to execute the CheckDB everyday. But we understand that on busy systems (instances with hundreds of databases, VLDBs, etc.) this can be nearly impossible.

Some interesting facts:

  • SQL Server maintenance plan ignores the TempDB, but if the TempDB becomes corrupt the instance will shutdown. Use caution with this.
  • Executing the CheckDB on the master database will cause the execution on the mssqlsystemresource database as well.

Execution Options: There are few execution options for CheckDB:

  • NO_INFOMSGS: Avoid the output of info messages.
  • ALL_ERRORMSDGS: Allow the output of error messages.
    • Default since SQL Server 2008 SP1.
  • NOINDEX:  Skips nonclustered indexes verification.
  • DATA_PURITY: Validates de data based on the column characteristcs.
    • Default since SQL Server 2005.
  • ESTIMATEONLY: Estimates the space needed on TempDB.
    • As per Paul Randal, this option is broken on newer versions of SQL Server.
  • TABLOCK: Uses locks instead of database snapshots.
    • Useful when the snapshot creation is failing.
  • EXTENDED_LOGICAL_CHECKS: Allows the validation on XML columns and Spatial indexes.
    • This can be costly.
  • PHYSICAL_ONLY: Skips most of the logical verifications.
    • Need less resources to run, but a “full” execution should be sone periodically.

Other Approaches to execute: As CheckDB is a resource-intensive process, in some cases the execution may take a long time to complete. Depending on the environment, the Integrity Check on all the databases (or even in one very large database) may not finish on time, namely, the integrity check will exceed the maintenance window limit. To work around this, we can reproduce a full CheckDB executing its subset commands. As follows:

  • CHECKALLOC
  • CHECKTABLE
  • CHECKCATALOG
  • CHECKFILEGROUP

By executing those commands, one per day, we can cover a full CheckDB execution in phases. Another possible approach is execute the backups with the “CHECKSUM” option. This way we will be able to restore using the option “VERIFY ONLY” and do an extra level of verification. Anyway, we will still need the CheckDB.

So, that’s all about CheckDB. There are more information around there, but the best place to get info from CheckDB is on SQL Skills blog, where the CheckDB guru writes few articles about it. See you in another post ;)

Categories: DBA Blogs

RMAN 12c : Say goodbye to your backup when dropping your PDB

Pythian Group - Fri, 2014-02-14 14:46

I was working on my presentations for IOUG Collaborate, and I came upon this strange behaviour in RMAN 12c (12.1.0.1.0) which to me, shouldn’t happen. Seems that when you do a DROP PLUGGABLE DATABASE , it is the equivalent of DROP DATABASE INCLUDING BACKUPS. This means that if you need to restore your PDB later on, you won’t have this registered – just be careful when dropping them.

Here we go: So I took a backup of my CDB and all of its PDBs, and kept an eye on this TAG 20140212T191237  (I removed a couple of lines for better reading)


oracle@oracleenespanol2.localdomain [cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin

oracle $ rman target sys/oracle@cdb1

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Feb 12 19:12:06 2014

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

connected to target database: CDB1 (DBID=808250731)

RMAN> backup database plus archivelog ;

Starting backup at 12/02/2014 19:12:31

current log archived

...

Starting backup at 12/02/2014 19:12:37

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00003 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c520w6w_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_undotbs1_9c524cnr_.dbf

input datafile file number=00001 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c522mbz_.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_users_9c524bjm_.dbf

channel ORA_DISK_1: starting piece 1 at 12/02/2014 19:12:38

channel ORA_DISK_1: finished piece 1 at 12/02/2014 19:15:23

piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2014_02_12/<strong>o1_mf_nnndf_TAG20140212T191237_9hrbjrqd_</strong>.bkp tag=TAG20140212T191237 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:45

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00009 name=/u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf

input datafile file number=00010 name=/u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf

channel ORA_DISK_1: starting piece 1 at 12/02/2014 19:15:23

channel ORA_DISK_1: finished piece 1 at 12/02/2014 19:16:08

piece handle=/u01/app/oracle/fast_recovery_area/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbovmw_.bkp tag=<b>TAG20140212T191237</b> comment=NONE

..

Finished backup at 12/02/2014 19:16:37

Starting Control File and SPFILE Autobackup at 12/02/2014 19:16:37

piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2014_02_12/o1_mf_s_839358997_9hrbr5vr_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 12/02/2014 19:16:38

So First I want to show you that I was able to recoup the DB in case I were to lose my datafiles of my PDB, so I will first delete them

oracle@oracleenespanol2.localdomain[cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin

oracle $ sqlplus / as sysdba

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set pages 999

SQL> COLUMN PDB_ID FORMAT 999

COLUMN PDB_NAME FORMAT A8

COLUMN FILE_ID FORMAT 9999

COLUMN TABLESPACE_NAME FORMAT A10

COLUMN FILE_NAME FORMAT A45

SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME

FROM DBA_PDBS p, CDB_DATA_FILES d

WHERE p.PDB_ID = d.CON_ID

ORDER BY p.PDB_ID;SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4

PDB_ID PDB_NAME FILE_ID TABLESPACE FILE_NAME

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

2 PDB$SEED       5 SYSTEM   /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c5257ms_.dbf

2 PDB$SEED       7 SYSAUX   /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c5257mj_.dbf

3 PDB1      9 SYSAUX   /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf

3 PDB1      8 SYSTEM   /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf

3 PDB1     10 USERS   /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

oracle@oracleenespanol2.localdomain[cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin

oracle $ rm /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf

oracle@oracleenespanol2.localdomain[cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin

oracle $ rm /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf

oracle@oracleenespanol2.localdomain[cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin

oracle $ rm /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf

Now that I have deleted the datafiles, I will now proceed to restore and recover the PDB, and keep an eye on the tag 20140212T191237  used for the restore.

oracle@oracleenespanol2.localdomain [cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin
oracle $ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Feb 12 19:19:46 2014

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

connected to target database: CDB1 (DBID=808250731)

RMAN> RESTORE PLUGGABLE DATABASE PDB1;

Starting restore at 12/02/2014 19:20:12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=77 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 00008 to /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbovmw_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbovmw_.bkp tag=TAG20140212T191237
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 12/02/2014 19:20:48

RMAN> RECOVER PLUGGABLE DATABASE PDB1;

Starting recover at 12/02/2014 19:21:06
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 12/02/2014 19:21:07

RMAN> alter pluggable database pdb1 open;

Statement processed

As you can see, I was able to restore and recover my PDB without a problem. But what happens if I decide to drop my PDB, and later on decided that the PDB was needed? So I tried to go back to my backup, it will no longer be there, and it doesn’t report on the backup tag 20140212T191237


RMAN> alter pluggable database pdb1 close;

Statement processed

RMAN> drop pluggable database PDB1;

Statement processed

RMAN> RESTORE PLUGGABLE DATABASE PDB1;

Starting restore at 13/02/2014 11:18:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/13/2014 11:18:27
RMAN-06813: could not translate pluggable database PDB1

RMAN> list backup tag TAG20140212T191237;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
67 Full 1.88G DISK 00:02:41 12/02/2014 19:15:19
 BP Key: 67 Status: AVAILABLE Compressed: NO Tag: TAG20140212T191237
 Piece Name: /u01/app/oracle/fast_recovery_area/CDB1/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbjrqd_.bkp
 List of Datafiles in backup set 67
 File LV Type Ckp SCN Ckp Time Name
 ---- -- ---- ---------- ------------------- ----
 1 Full 3220602 12/02/2014 19:12:38 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c522mbz_.dbf
 3 Full 3220602 12/02/2014 19:12:38 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c520w6w_.dbf
 4 Full 3220602 12/02/2014 19:12:38 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_undotbs1_9c524cnr_.dbf
 6 Full 3220602 12/02/2014 19:12:38 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_users_9c524bjm_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
69 Full 680.13M DISK 00:00:24 12/02/2014 19:16:32
 BP Key: 69 Status: AVAILABLE Compressed: NO Tag: TAG20140212T191237
 Piece Name: /u01/app/oracle/fast_recovery_area/CDB1/EDDDB886A1191F07E043344EB2C0BE27/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbq8lm_.bkp
 List of Datafiles in backup set 69
 Container ID: 2, PDB Name: PDB$SEED
 File LV Type Ckp SCN Ckp Time Name
 ---- -- ---- ---------- ------------------- ----
 5 Full 1732663 18/12/2013 22:52:25 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c5257ms_.dbf
 7 Full 1732663 18/12/2013 22:52:25 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c5257mj_.dbf

As you can see, that backup is no longer registered. I still don’t know if this is normal behaviour for PDBs backup, or a bug – but for now just be careful when dropping a PDB. Your backup will not be reliable. Scary stuff isn’t it ?

Categories: DBA Blogs

SQL Server Statistics: Maintenance and Best Practices

Pythian Group - Fri, 2014-02-14 12:58
What are Statistics?

There are multiple paths a database can use to answer a query, some of them being faster and more efficient than others. It is the job of the query optimizer to evaluate and choose the best path, or execution plan, for a given query. Using the available indexes may not always be the most efficient plan. For example, if 95% of the values for a column are the same, an index scan will probably be more efficient than using the index on that column. Statistics are SQL Server objects which contain metrics on the data count and distribution within a column or columns used by the optimizer to help it make that choice. They are used to estimate the count of rows.

Index statistics: Created automatically when an index (both clustered and non-clustered) is created. These will have the same name as the index and will exist as long as the index exists.

Column statistics: Created manually by the DBA using the ‘CREATE STATISTICS’ command, or automatically if the “Auto Create Statistics” option is set to “True”. Column statistics can be created, modified and dropped at will.

Statistics contain two different types of information about the data; density and distribution. Density is simply the inverse of the count of distinct values for the column or columns. The distribution is a representation of the data contained in the first column of the statistic. This information is stored in a histogram; the histogram contains up to 200 steps with a lower and upper limit and contains the count of values that fall between both limits. To view this histogram, go to the details tab of the statistic’s properties or use the command DBCC SHOW_STATISTICS. The screenshot below shows the histogram of an index statistic; the RANGE_HI_KEY is the upper limit of the step, the RANGE_HI_KEY of the previous step + 1 is the lower limit, and the RANGE_ROWS is the count of rows between the limits.

stats1 Statistics Maintenance and Best Practices

When the data in the database changes the statistics become stale and outdated. When examining a query execution plan, a large discrepancy between the Actual Number of Rows and the Estimated Number of Rows is an indication of outdated stats. Outdated statistics can lead the optimizer in choosing inefficient execution plan and can dramatically affect overall performance. Steps must therefore be taken in order to keep statistics up to date.

stats2

Keep Auto Create Statistics enabled: This database property allows SQL Server to automatically create stats for a single non-indexed column if they are missing when it is used in a where or join condition. This ensures the optimizer will have the necessary information to choose a plan. The statistics automatically created by SQL Server will start with _WA_ in their name.

Keep Auto Update Statistics enabled: This database property allows SQL Server to automatically update the statistics when they are deemed outdated. The update occurs before executing a query if certain conditions are met, or after the query is executed if Auto Update Statistics Asynchronously is used instead. The three conditions that will trigger an update if one is met are:

-Table had 0 rows and increases to one or more rows.

-Table had less than 500 rows and there is an increase of 500 rows or more since the last update

-Table has over 500 rows and there is an increase of 500 + 20% of the table size since the last update

stats3

Maintenance plan: You can also proactively update the statistics yourself using TSQL (sp_updatestats for all stats in a database or UPDATE STATISTICS for a single one) or a maintenance plan task. Scheduling the statistics maintenance during off hours will help reduce the need to update statistics during peak times. The need and frequency of this proactive maintenance will depend on your environment; frequent data changes causes the statistics to become outdated more quickly. You can also specify the sample size used to update the statistic;

Ex:

UPDATE STATISTICS TableName(StatsName) WITH FULLSCAN: Costs more time and resources but will ensure that statistics are accurate.

UPDATE STATISTICS TableName(StatsName) WITH SAMPLE 50 PERCENT: Will only use half the rows and extrapolate the rest, meaning the updating will be faster, but the statistics may not be accurate.

Rebuilding an index will also update index statistics with full scan (column stats will not be updated, and an index reorg will do the update). Note however that updating statistics forces queries to recompile; you must therefore decide when the cost of the overhead for the recompiles is worth having the latest statistics.

Unused Statistics: Statistics comes with a cost, and just as with indexes, too many of them can lead to issues like increasing the cost of statistics maintenance, and can make the optimizer’s job more difficult. Updating statistics for a large database can easily take hours, even days, to complete. When Auto Create Statistics is enabled, stats can be created even for a one time query. A table could end up having a large number of statistics that serve no purpose. It is wise to review and clean up the statistics as part of general maintenance. Identifying unused statistics can be difficult since, unlike indexes, SQL Server does not record statistics usage. However you can identify the statistics that satisfy one of the thresholds for the automatic update above but still hasn’t been updated; this is a good indication of unused statistics.

In addition to unused stats, you may find overlapping stats which are covered by other statistics. The following script from Kendal Van Dyke will identify all single column statistics that are covered by an existing index statistic (share the same leading column) in a database and generates the TSQL commands to drop them.

WITH    autostats ( object_id, stats_id, name, column_id )

AS ( SELECT   sys.stats.object_id ,

sys.stats.stats_id ,

sys.stats.name ,

sys.stats_columns.column_id

FROM     sys.stats

INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id

AND sys.stats.stats_id = sys.stats_columns.stats_id

WHERE    sys.stats.auto_created = 1

AND sys.stats_columns.stats_column_id = 1

)

SELECT  OBJECT_NAME(sys.stats.object_id) AS [Table] ,

sys.columns.name AS [Column] ,

sys.stats.name AS [Overlapped] ,

autostats.name AS [Overlapping] ,

'DROP STATISTICS [' + OBJECT_SCHEMA_NAME(sys.stats.object_id)

+ '].[' + OBJECT_NAME(sys.stats.object_id) + '].['

+ autostats.name + ']'

FROM    sys.stats

INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id

AND sys.stats.stats_id = sys.stats_columns.stats_id

INNER JOIN autostats ON sys.stats_columns.object_id = autostats.object_id

AND sys.stats_columns.column_id = autostats.column_id

INNER JOIN sys.columns ON sys.stats.object_id = sys.columns.object_id

AND sys.stats_columns.column_id = sys.columns.column_id

WHERE   sys.stats.auto_created = 0

AND sys.stats_columns.stats_column_id = 1

AND sys.stats_columns.stats_id != autostats.stats_id

AND OBJECTPROPERTY(sys.stats.object_id, 'IsMsShipped') = 0

Source: http://www.kendalvandyke.com/2010/09/tuning-tip-identifying-overlapping.html

Common Mistakes

Statistics update after Index Rebuild: As mentioned previously, the index rebuild (not reorg) will also update index statistics using full scan. Scheduling stats maintenance after the index maintenance will cause duplicate work. In addition, if the stats maintenance is using a small sample size, the new updated stats will overwrite the ones that were just updated with full scan, meaning their values will be less accurate. Scheduling it after an index reorg however is fine.

Relying on Auto Update: As seen above, the threshold which triggers the auto update is around 20% of the total row count. This is fine for small tables, but larger tables require a lot of data changes before the update is triggered, during which the stats can become outdated.

Not specifying the sample size: While updating, choosing the right sample size is important to keep statistics accurate. While the cost of using full scan is higher, in some situations it is required, especially for very large databases. Running EXEC sp_updatestats @resample = ‘resample’ will update all statistics using the last sample used. If you do not specify the resample, it will update them using the default sample. The default sample is determined by SQL server and is a fraction of the total row count in a table. We have recently run into an issue where a DBA executed “EXEC sp_updatestats” on a 1 terabyte database, which caused all statistics to be updated with the default sample. Due to the size of the database, the default sample is simply not enough to represent the data distribution in the database and caused all queries to use bad execution plans which caused major performance issues. Only a full scan update of the statistics provided accurate statistics for this database, but it takes a very long time to run.  Luckily there was a QA server where the database was restored before the stats update and with almost identical data. We were able to script the statistics from the QA server and recreate them on production using their binary representation (see WITH STATS_STREAM).  This solution is not recommended and was only used as a last resort. This incident shows the importance of statistics and implementing proper maintenance appropriate for the environment.

Updating too often: Not only is there a cost in updating statistics, remember that it also causes queries to recompile. Updating statistics should be done only as required, and a schedule appropriate for your environment should be used. The frequency depends on the amount of data changes in the database, more changes require more frequent stats update.

Conclusion

Statistics are a crucial element in the overall performance of a database and require proper maintenance and attention. In addition, each environment is unique and has different needs regarding statistics maintenance. For more information regarding statistics, see http://technet.microsoft.com/en-us/library/ms190397.aspx.

Categories: DBA Blogs

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

Pythian Group - Fri, 2014-02-14 10:47

On this Valentine’s Day, what is the most romantic thing you could do as database professional? Why, yes — you could read (and then share) this scintillating and lovely Log Buffer Edition!

Oracle:

Oracle is kicking off a 17-city North American event series on how running Oracle Applications on Oracle hardware can help customers deliver maximum value and achieve dramatically better business results.

Five guidelines to follow as you begin building and employing mobile applications – plus Oracle technologies and products that support your move to mobility in your enterprise.

When you run R functions in the database, especially functions involving multiple R engines in parallel, you can monitor their progress using the Oracle R Enterprise datastore as a central location for progress notifications, or any intermediate status or results.

In the era of big data, data warehousing is becoming even more relevant for enterprises which are eager to become more information-driven.

The License Definitions and Rules (LDRs) are a critical part of every software license that Oracle grants to a customer.

SQL Server:

An examination into how the various transaction isolation levels affect locking (and blocking.)

What to do if you need to push the limits of your disk subsystem, in order to determine whether the hardware’s I/O capacity meets the needs of a database application.

An Overview of SSIS Parameters – Level 15 of the Stairway to Integration Services.

With the new SQL Server 2014 In-Memory OLTP tables, stored procedures can be natively compiled and can improve performance.

Excel spreadsheets are useful for distributing data generated by SQL Server, but SSIS lacks direct support for writing Excel files.

MySQL:

The Sign: row-based binary logging and integer signedness in MySQL and MariaDB.

Delayed Transaction Durability: A new feature in SQL Server 2014 CTP2.

Getting Started with the Spider Storage Engine.

Shard-Query is now much faster for some aggregate functions.

One of the most expensive database operations is performing Data Definition Language (DDL, e.g. CREATE, DROP, ALTER, etc.) statements, specially, the ALTER statements because MySQL blocks the entire table for both reads and writes while modifying the table.

Categories: DBA Blogs

SPFILE for a CDB vs. a PDB

DBASolved - Fri, 2014-02-14 00:28

Recently, I have had conversations with users about Oracle Database 12c.  One question that often comes up is, how does the database manage the parameters between a container database (CDB) and a pluggable database (PDB)?  In order to answer this question, I had to work with my pluggable database setup in a test environment.  Before changing anything within my database setup I first made a backup of the current parameters using a tired and true method.

SQL> create pfile=’init12ctest.ora’ from spfile;

Before diving into change and storage of parameters, it is important that you understand the concept of inheritance.  Once you under stand inheritance within the Oracle Database 12c, you will grasp how parameters can be changed and stored for PDBs.

 

Inheritance

Inheritance in dealing with Oracle Database 12c means that the value of a particular parameter in the root (CDB) is inherited by one or more PDBs.  There are parameters that can be changed at the PDB level and override what is being inherited from the CDB.  To identify which parameters can be modified for a PDB, the ISPDB_MODIFIABLE column in the V$SYSTEM_PARAMETER view must be TRUE.  If the ISPDB_MODIFIABLE is TRUE then the parameter inherits the value from the CDB until it is changed with an ALTER SYSTEM SET command at the PDB.

  Setting Parameters

Now, let’s work on changing parameters for a PDB.  In order to do this, you needed to navigate into a PDB using the ALTER SESSION SET CONTAINER command. 

SQL> ALTER SESSION SET CONTAINER=bcpdb1;

Once inside of the PDB, you needed to find what parameters are modifiable.  These parameters can be found using the following SQL statement.  Remember, the ISPDB_MODIFIABLE has to be TRUE.

SQL> SELECT NAME FROM V$SYSTEM_PARAMETER
     WHERE ISPDB_MODIFIABLE = ‘TRUE’
     ORDER BY NAME;

With this list of parameters, pick one at random to change.  Lets change the statistics_level from typical to all for bcpdb1.

SQL> ALTER SYSTEM SET STATISTICS_LEVEL = ALL SCOPE = BOTH;

By using BOTH, you are setting the parameter in memory and in the spfile.  This way on a reboot of the database, the PDB will retain the setting.

Now that the parameter has been changed, can you see this change in the parameters for the CDB?  Not at the CDB level, but you can at the PDB level from the V$PARAMETER.  If I want to see the parameters that are set system wide (CDB & PDB), you needed to use the view V$SYSTEM_PARAMETER.  The following SQL will display the statistics_level parameter for both CDB and PDB (make note of the con_id column).

SQL> SELECT NAME, VALUE, DISPLAY_VALUE, CON_ID FROM V$SYSTEM_PARAMETER
     WHERE NAME = ‘statistics_level’
     ORDER BY NAME;

image

At this point, you have two different settings for statistics_level depending on which container you are in.  Now, you’ll want to make a backup of the spfile and verify that your settings for the PDB is in fact stored in the spfile.

SQL> CREATE PFILE=’init12ctest1.ora’ FROM SPFILE;

Let’s take a look at the pfile you just created.

bc12c.__data_transfer_cache_size=0
bc12c.__db_cache_size=1442840576
bc12c.__java_pool_size=16777216
bc12c.__large_pool_size=33554432
bc12c.__oracle_base=’/oracle/app’#ORACLE_BASE set from environment
bc12c.__pga_aggregate_target=1073741824
bc12c.__sga_target=2147483648
bc12c.__shared_io_pool_size=117440512
bc12c.__shared_pool_size=503316480
bc12c.__streams_pool_size=16777216
bc12c._common_user_prefix=’CU’##
*.audit_file_dest=’/oracle/app/admin/bc12c/adump’
*.audit_trail=’db’
*.compatible=’12.1.0.0.0′
*.control_files=’/oracle/app/oradata/BC12C/controlfile/o1_mf_91pqwlwo_.ctl’,'/oracle/app/fast_recovery_area/BC12C/controlfile/o1_mf_91pqwm7g_.ctl’
*.db_block_size=8192
*.db_create_file_dest=’/oracle/app/oradata’
*.db_domain=’acme.com’
*.db_name=’bc12c’
*.db_recovery_file_dest=’/oracle/app/fast_recovery_area’
*.db_recovery_file_dest_size=16106127360
*.diagnostic_dest=’/oracle/app’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=bc12cXDB)’
*.enable_pluggable_database=true
*.local_listener=’LISTENER_BC12C’
*.log_archive_format=’%t_%s_%r.dbf’
*.open_cursors=300
*.pga_aggregate_target=1073741824
*.processes=300
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_max_size=2147483648
*.sga_target=2147483648
*.undo_tablespace=’UNDOTBS1′

Notice that the parameter statistics_level is not included in the output for the pfile.  Why is this?  If you go back to the documentation on Oracle Database 12c (here), you will find a note stating:

Note: A text initialization parameter file (PFILE) cannot contain PDB-specific parameter values.

  Where are the parameters stored

Since the parameters for the PDB are not stored in the spfile, where are they stored then?  In order to find this, you need to take the V$SYSTEM_PARAMETER view apart using GV$FIXED_VIEW_DEFINITION.

SQL> SELECT VIEW_DEFINITION FROM GV$FIXED_VIEW_DEFINITION
         WHERE VIEW_NAME = ‘V$SYSTEM_PARAMETER’;

The V$SYSTEM_PARAMETER view points you to the GV$SYSTEM_PARAMETER view.  Let’s grab the definition for this view.

SQL> SELECT VIEW_DEFINITION FROM GV$FIXED_VIEW_DEFINITION
         WHERE VIEW_NAME = ‘GV$SYSTEM_PARAMETER’;

The definition for GV$SYSTEM_PARAMTER shows you that the information is coming from the X$KSPPI and X$KSPPSV tables in the data dictionary.  The SQL that defines the GV$SYSTEM_PARAMETER view is listed below.

select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf, 
decode(bitand(ksppiflg/256,1),1,’TRUE’,'FALSE’), 
decode(bitand(ksppiflg/65536,3),1,’IMMEDIATE’,2,’DEFERRED’, 3,’IMMEDIATE’,'FALSE’), 
decode(bitand(ksppiflg/524288,1),1,’TRUE’,'FALSE’), 
decode(bitand(ksppiflg,4),4,’FALSE’,
decode(bitand(ksppiflg/65536,3), 0, ‘FALSE’, ‘TRUE’)),
decode(bitand(ksppstvf,7),1,’MODIFIED’,'FALSE’), 
decode(bitand(ksppstvf,2),2,’TRUE’,'FALSE’), 
decode(bitand(ksppilrmflg/64, 1), 1, ‘TRUE’, ‘FALSE’), 
decode(bitand(ksppilrmflg/268435456, 1), 1, ‘TRUE’, ‘FALSE’), 
ksppdesc, ksppstcmnt, ksppihash, y.con_id
from x$ksppi x, x$ksppsv
where (x.indx = y.indx)
and  bitand(ksppiflg,268435456) = 0
and  ((translate(ksppinm,’_',’#') not like ‘##%’)
and  ((translate(ksppinm,’_',’#') not like ‘#%’)     
or   (ksppstdf = ‘FALSE’)
or   (bitand(ksppstvf,5) > 0)))

 

Now that you know the X tables to use, let’s take a look and see if you can locate the statistics_level parameter for bcpdb1.

SQL> SELECT * FROM X$KSPPSV
         WHERE INDX IN (SELECT INDX FROM X$KSPPI WHERE CON_ID= 3 AND KSPPINM = ‘statistics_level’);

image

Notice that the CON_ID is 3.  If you query CDB_PDB, you will notice that bcpdb1 has  CON_ID of 3. At this point, you have located where the value of statistics_level for the PDB is stored.  If you go back to the documentation, you will find references that tell you if SCOPE=SPFILE or SCOPE=BOTH were used when setting the parameter; the parameter will be transferred and stored in the XML file when the PDB is unplugged from the CDB.  Let’s test this out.

  Unplug a PDB

To test if the parameter (statistics_level) is stored in the XML file or data dictionary. What happens to the parameter when you unplug the PDB.  According to documentation when unplugged the value of statistics_level should be stored in the associated XML file for plugging into a CDB.  Let’s unplug BCPDB1.

SQL> ALTER PLUGGABLE DATABASE BCPDB1 CLOSE;

SQL> ALTER PLUGGABLE DATABASE BCPDB1 UNPLUG TO ‘/TMP/BCPDB1.XML’;

SQL> DROP PLUGGABLE DATABASE BCPDB1 KEEP DATAFILES;

With the pluggable database BCPDB1 unplugged from the CDB, lets see if the parameter values for statistics_level are still in the data dictionary.

SQL> SELECT * FROM X$KSPPSV
         WHERE INDX IN (SELECT INDX FROM X$KSPPI WHERE CON_ID= 3 AND KSPPINM = ‘statistics_level’);

 image

Apparently, the parameter values for statistics_level are gone.  Let’s check the XML file that was created in the /tmp directory to see if the parameter is there.

 <parameters>
     <parameter>processes=300</parameter>
     <parameter>sga_max_size=2147483648</parameter>
     <parameter>sga_target=2147483648</parameter>
     <parameter>db_block_size=8192</parameter>
     <parameter>compatible=12.1.0.0.0</parameter>
     <parameter>open_cursors=300</parameter>
     <parameter>pga_aggregate_target=1073741824</parameter>
     <parameter>enable_pluggable_database=TRUE</parameter>
     <parameter>_common_user_prefix=CU</parameter>
   </parameters>

The values that are in the XML file appear to be just general settings.  The statistics_level parameter didn’t make it into the XML file either. Hmmm…  Let’s plug in the PDB and see if the value comes back to the data dictionary.
  Plug-in the PDB

In order to plug-in the PDB make sure you still have the data files and the XML file needed.  What you are hoping for is that the statistic_level parameter comes back for the PDB and is set to a value of ALL. 

SQL> CREATE PLUGGABLE DATABASE BCPDB1 USING ‘/TMP/BCPDB1.XML’ NOCOPY;

SQL> ALTER PLUGGABLE DATABASE BCPDB1 OPEN;

With the PDB (BCPDB1) open, let’s check and see if the statistic_level parameter is in the data dictionary.

SQL> SELECT * FROM X$KSPPSV
         WHERE INDX IN (SELECT INDX FROM X$KSPPI WHERE CON_ID= 3 AND KSPPINM = ‘statistics_level’);
 

image 

Sure enough, the statistics_level parameter came back.  This means that the parameters for the PDB are stored in the PDB data dictionary. :)

 

Conclusion

When working with Oracle Database 12c, you have to understand how the initialization parameters are set for the CDB and each of the PDBs associated. The traditional ways of looking at an SPFILE will only be useful for a CDB database.  This is because the CDB is the root level that controls many of the items which are shared amongst the CDB and PDBs.  In order to fully understand how parameters are set for a PDB, you need to remember that PDBs inherit parameter values from a CDB; unless they are overridden from the PDB level. 

 

Enjoy!

 

twitter: @dbasolved

blog: http://dbasolved.com


Filed under: Database
Categories: DBA Blogs

Bug 14383007 workaround

Bobby Durrett's DBA Blog - Thu, 2014-02-13 11:44

We appear to be hitting this bug on our production Exadata RAC system:

Bug 14383007 – Sort runs out of temp space in RAC even when temp space is available

One particular job errors out with ORA-01652 errors on the TEMP tablespace even though there is tons of free space in the sort segments.  So, I got the idea of building a work around to have this job only login to the node that has the most free temp space.  Normally space just gets reallocated from the node that has it to the node that needs it.  But, I guess the bug is that in certain cases this doesn’t happen and you get the ORA-01652.

Here is my example unix script (actually this is run on an HP-UX box, don’t ask me why).

# This script demonstrates how to login to the instance
# of a RAC database that has the most free space in 
# the TEMP tablespace.  It takes three arguments
# which are the connection information for the RAC
# database:
# 1 - Oracle database username
# 2 - password
# 3 - connect string
#
# in sqlplus this would be like connecting as username/password@connectstring
#
# Step one - login to the RAC database and extract a connect string that
# connects to the node with the most free temp space.  
# This has several assumptions:
# 1 - temporary tablespace is named TEMP
# 2 - all of the temp space is allocated to sort segments
# 3 - the port number is 1521
NEW_CONNECT_STRING=`sqlplus -s /nolog<<EOF
connect $1/$2@$3
set heading off
set feedback off
set linesize 32000
set trimspool on
select 
'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = '||
host_name||
')(Port = 1521))) (CONNECT_DATA = (SID = '||
INSTANCE_NAME ||
')))'
from
gv\\$instance
where inst_id=
(select 
inst_id
from 
gv\\$sort_segment
where 
TABLESPACE_NAME='TEMP' and
FREE_BLOCKS =
(select max(FREE_BLOCKS)
from 
gv\\$sort_segment
where 
TABLESPACE_NAME='TEMP'));
exit
EOF
`
#trim the leading space from the connect string
NEW_CONNECT_STRING=`echo $NEW_CONNECT_STRING|sed 's/^ //'`
echo connect string = $1/$2@$NEW_CONNECT_STRING
# Now connect to the node using this connect string just to show
# that it works and query the sort segment view to show that this
# really is the instance with the most free temp space.
sqlplus /nolog<<EOF
set echo on
set termout on
set pagesize 1000
connect $1/$2@$NEW_CONNECT_STRING
-- show current instance
select instance_name from v\$instance;
-- list instances in descending order
-- of free blocks.  current instance
-- should be listed first.
select inst_id,free_blocks
from gv\$sort_segment
where 
TABLESPACE_NAME='TEMP'
order by free_blocks desc;
EOF

You wouldn’t want to use this except for a special case like this where you need a workaround for the bug.  Otherwise you would just end up running on one node and all the temp space would get allocated to it.  But, if you are hitting bug 14383007 this may be helpful.

- Bobby

 

Categories: DBA Blogs

Creating a single-node EBS 12.1.3 Vision instance from OVM templates

Pythian Group - Thu, 2014-02-13 08:50
“Seriously John, do you blog about anything else?”

Yeah, well… Evidence is strongly against me so far. :)

One of the more common questions I’ve received as a followup to my Build an E-Business Suite 12.1.3 Sandbox In VirtualBox in One Hour post has been, “Can I do this in a single node instead of creating two VMs?” The answer of course, is yes, but it never seemed like a good topic for a full blog post. Given the number of requests, however (and the patience and persistence of one reader in particular — hi Sandip!), I’m using this post to present quick notes on how to create a single-node EBS 12.1.3 Vision instance from the OVM templates, instead of the two-node system for which they’re designed.

In addition to the normal complete-lack-of-support caveats listed in the original post, please be aware that this post contains pointers and rough notes, not detailed instructions. Basically, I’ve just wrapped some formatting around some notes from a presentation I gave on this topic last summer. If you don’t understand what’s happening in the original set of instructions, these notes will not be useful to you at all. Please read the original post carefully before asking questions about this one.

System specs

Since we’re running apps and the database in a single node we need to configure a slightly more powerful single VM. Here’s partial output from ‘vboxmanage showvminfo’ that illustrates the important points (more memory, more CPU, and an extra disk for the Apps software). Otherwise, the configuration (network interfaces, rescue boot image setup, etc) is the same as in the original post.

Memory size: 3072MB
Number of CPUs: 2
Storage Controller Name (1): SATA
Storage Controller Type (1): IntelAhci
SATA (0, 0): /Volumes/Valen/OVM_1213/EBS121RootDisk.vdi (UUID: ebd87cd3-2620-49b6-b24d-c64158b183da)
SATA (1, 0): /Volumes/Valen/OVM_1213/EBS121DB.vdi (UUID: 0ae2f4dc-bd40-4299-82f7-eebea2c34de7)
SATA (2, 0): /Volumes/Valen/OVM_1213/EBS121Apps.vdi (UUID: 7fc14a42-f4bc-4741-8ba7-a33341ac73ea)

Still the same

The following steps are almost the same as in the original post:

  1. Download the software
  2. Extract the templates
  3. Convert the disk images to .vdi format (though you can skip the Apps server System.img disk, you won’t need it, only ebs1211apps.img). Of course, you’ll only need to create 1 VM at this step, attaching the Apps vdi as the third disk.
  4. Boot the database server VM in rescue mode from the install CD — the steps to install the new kernel and run mkinitrd remain the same

Things change a bit before moving on to step 5, “Reboot and prepare for next steps,” as described below.

What’s different?

Apart from the obvious “no second VM to create,” here are the essential changes I made to my build process for a single-node Vision instance:

  • Before rebooting, add another line to /etc/fstab to attach the apps software volume:
    /dev/sdc1 /u02 ext3 defaults 1 0
  • Before rebooting, do not edit the /etc/sysconfig/oraclevm-template script. I found it to be easier to just let the script execute at boot time, although it did require me to be a bit more careful about my inputs.
  • After rebooting, the template configuration script will guide you through the configuration of the network interfaces and the Vision database tier, as described in the original post

Once the database is started, you’ll need to make a few changes to the scripts that configure, start, and stop the applications tier. First, log in to the VM as root, and then adjust the scripts to account for the new mount point. To save your sanity, it’s also necessary to comment out ovm_configure_network from the ebiz_1211_reconfig.sh script:

# cd /u02
# perl -pi.old -e 's/u01/u02/g' startapps.sh stopapps.sh ebiz_1211_reconfig.sh
# vi ebiz_1211_reconfig.sh
# diff ebiz_1211_reconfig.sh ebiz_1211_reconfig.sh.old
47c47
< #ovm_configure_network "static"
---
> ovm_configure_network "static"
61c61
< su oracle -c "perl /u02/E-BIZ/apps/apps_st/comn/clone/bin/adcfgclone.pl appsTier"
---
> su oracle -c "perl /u01/E-BIZ/apps/apps_st/comn/clone/bin/adcfgclone.pl appsTier"

After the scripts have been adjusted, you’re ready to configure the apps tier. Again, as root, run the /u02/ebiz_1211_reconfig.sh script, which will invoke AutoConfig and ask you all the necessary questions. Your answers will differ from the two-node process in two important ways:

  1. There is only one hostname for the environment now
  2. All references to the apps software locations will point to /u02, not /u01

Here’s an excerpt of the Autoconfig run, with only the important/changed bits included:

 cd /u02
[root@gkar u02]# ./ebiz_1211_reconfig.sh
Configuring Oracle E-Business Suite...

Target System Hostname (virtual or normal) [gkar] :

Target System Database SID : VIS

Target System Database Server Node [gkar] :

Target System Database Domain Name [local.org] :

Target System Base Directory : /u02/E-BIZ

Target System Tools ORACLE_HOME Directory [/u02/E-BIZ/apps/tech_st/10.1.2] :

Target System Web ORACLE_HOME Directory [/u02/E-BIZ/apps/tech_st/10.1.3] :

Target System APPL_TOP Directory [/u02/E-BIZ/apps/apps_st/appl] :

Target System COMMON_TOP Directory [/u02/E-BIZ/apps/apps_st/comn] :

Target System Instance Home Directory [/u02/E-BIZ/inst] :

Do you want to preserve the Display [atgtxk-09:0.0] (y/n)  : n

Target System Display [gkar:0.0] :

Do you want the the target system to have the same port values as the source system (y/n) [y] ? : n

Target System Port Pool [0-99] : 42

UTL_FILE_DIR on database tier consists of the following directories.

1. /usr/tmp
2. /usr/tmp
3. /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/outbound/VIS_gkar
4. /usr/tmp
Choose a value which will be set as APPLPTMP value on the target node [1] : 1

Do you want to startup the Application Services for VIS? (y/n) [y] :  y
Cleanup items and other reminders

To prevent annoyances when starting/stopping services, and logging in as oracle:

  • touch /home/oracle/.passchanged
  • rm /u02/E-BIZ/apps/apps_st/appl/*mydb*

Also, since our root disk came from the database server VM template, only database services will stop and start automatically upon server shutdown and boot. You will need to use the startapps.sh and stopapps.sh scripts in /u02 to manage the applications tier services.

That should be enough to get you going. Good luck!

Categories: DBA Blogs

Making Oozie Logs A Little Easier On The Eyes

Pythian Group - Thu, 2014-02-13 08:47

Today we’re having a quick one.

Earlier during the day, I had to peruse an Oozie log for the first time. And it looked like:


2014-02-11 20:13:14,211  INFO ActionStartXCommand:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@:start:] Start action [0004636-140111040403753-oozie-W@:start:] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
2014-02-11 20:13:14,212  WARN ActionStartXCommand:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@:start:] [***0004636-140111040403753-oozie-W@:start:***]Action status=DONE
2014-02-11 20:13:14,212  WARN ActionStartXCommand:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@:start:] [***0004636-140111040403753-oozie-W@:start:***]Action updated in DB!
2014-02-11 20:13:14,271  INFO ActionStartXCommand:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@a-first-action] Start action [0004636-140111040403753-oozie-W@a-first-action] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
2014-02-11 20:13:15,079  WARN HiveActionExecutor:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@a-first-action] credentials is null for the action
2014-02-11 20:13:18,306  INFO HiveActionExecutor:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@a-first-action] checking action, external ID [job_201401070500_217582] status [RUNNING]
2014-02-11 20:13:18,408  WARN ActionStartXCommand:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@a-first-action] [***0004636-140111040403753-oozie-W@a-first-action***]Action status=RUNNING
2014-02-11 20:13:18,409  WARN ActionStartXCommand:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@a-first-action] [***0004636-140111040403753-oozie-W@a-first-action***]Action updated in DB!
2014-02-11 20:13:34,367  INFO CallbackServlet:539 - USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@a-first-action] callback for action [0004636-140111040403753-oozie-W@a-first-action]
2014-02-11 20:13:34,424  INFO HiveActionExecutor:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@a-first-action] action completed, external ID [job_201401070500_217582]
2014-02-11 20:13:34,443  INFO HiveActionExecutor:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@a-first-action] action produced output
2014-02-11 20:13:34,653  INFO ActionStartXCommand:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@some-action] Start action [0004636-140111040403753-oozie-W@some-action] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
2014-02-11 20:13:35,418  WARN HiveActionExecutor:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@some-action] credentials is null for the action
2014-02-11 20:13:38,628  INFO HiveActionExecutor:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@some-action] checking action, external ID [job_201401070500_217583] status [RUNNING]
2014-02-11 20:13:38,731  WARN ActionStartXCommand:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@some-action] [***0004636-140111040403753-oozie-W@some-action***]Action status=RUNNING
2014-02-11 20:13:38,731  WARN ActionStartXCommand:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@some-action] [***0004636-140111040403753-oozie-W@some-action***]Action updated in DB!
2014-02-11 20:13:57,659  INFO CallbackServlet:539 - USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@some-action] callback for action [0004636-140111040403753-oozie-W@some-action]
2014-02-11 20:13:57,712  INFO HiveActionExecutor:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@some-action] action completed, external ID [job_201401070500_217583]
2014-02-11 20:13:57,729  WARN HiveActionExecutor:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@some-action] Launcher ERROR, reason: Main class [org.apache.oozie.action.hadoop.HiveMain], exit code [10044]
2014-02-11 20:13:57,895  INFO ActionEndXCommand:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@some-action] ERROR is considered as FAILED for SLA
2014-02-11 20:13:57,964  INFO ActionStartXCommand:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@fail] Start action [0004636-140111040403753-oozie-W@fail] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
2014-02-11 20:13:57,965  WARN ActionStartXCommand:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@fail] [***0004636-140111040403753-oozie-W@fail***]Action status=DONE
2014-02-11 20:13:57,965  WARN ActionStartXCommand:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@fail] [***0004636-140111040403753-oozie-W@fail***]Action updated in DB!
2014-02-11 20:13:58,036  WARN CoordActionUpdateXCommand:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[-] E1100: Command precondition does not hold before execution, [, coord action is null], Error Code: E1100
Finished: FAILURE

wut?

Okay, Java peeps have a predilection for verbose logs, but surely there is a way to make the whole thing a little more readable for poor, poor human eyes… So I quickly hacked the following:


use 5.10.0;

use strict;

my @lines = map { parse_line($_) } <>;

# the job id is big, and doesn't give us much, remove
for my $useless ( map { $_->{JOB} } @lines ) {
    for ( @lines ) {
        $_->{msg} =~ s/\Q$useless//g;
        $_->{ACTION} =~ s/^\Q$useless//;
    }
}

my %previous;
for my $l ( @lines ) {
    # we'll only print metadata that changed
    my @changes = grep { $l->{$_} ne $previous{$_} } 
                       qw/ USER GROUP TOKEN APP JOB ACTION /;

    say join ' ', map { $_ . "[" . $l->{$_} . "] " } @changes if @changes;

    say "\t", $l->{time}, " ", $l->{msg};
    %previous = %$l;
}

sub parse_line {
    my $line = shift;

    # try to parse the line as a typical log line
    my( $time, $info ) = /^\d{4}-\d{2}-\d{2}\s*  # the date. Don't care
                           (\d+:\d\d:\d\d)       # the time, More interesting
                           ,\d+\s*.*?-           # log level and stuff. Meh
                           (.*)                  # the message itself
                         /x
        or return ();

    my %data = ( time => $time );

    # capture some repeated metadata
    for my $k ( qw/ USER GROUP TOKEN APP JOB ACTION / ) {
        $data{$k} = $1 if $info =~ s/$k\[(.*?)\]\s*//;
    }

    # useless and long, scrap it
    $info =~ s/\[\*{3}.*?\*{3}\]//;

    $data{msg} = $info;

    return \%data;
}

And there we go,  a log trace that is a mite easier on the eyes…


$ perl filter oozie_mess.log
USER[running_user]  GROUP[-]  APP[some-big-job-workflow]  JOB[0004636-140111040403753-oozie-W]  ACTION[@:start:] 
    20:13:14  Start action [@:start:] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
    20:13:14  Action status=DONE
    20:13:14  Action updated in DB!
ACTION[@a-first-action] 
    20:13:14  Start action [@a-first-action] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
    20:13:15  credentials is null for the action
    20:13:18  checking action, external ID [job_201401070500_217582] status [RUNNING]
    20:13:18  Action status=RUNNING
    20:13:18  Action updated in DB!
USER[-]  TOKEN[-]  APP[-] 
    20:13:34  callback for action [@a-first-action]
USER[running_user]  TOKEN[]  APP[some-big-job-workflow] 
    20:13:34  action completed, external ID [job_201401070500_217582]
    20:13:34  action produced output
ACTION[@some-action] 
    20:13:34  Start action [@some-action] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
    20:13:35  credentials is null for the action
    20:13:38  checking action, external ID [job_201401070500_217583] status [RUNNING]
    20:13:38  Action status=RUNNING
    20:13:38  Action updated in DB!
USER[-]  TOKEN[-]  APP[-] 
    20:13:57  callback for action [@some-action]
USER[running_user]  TOKEN[]  APP[some-big-job-workflow] 
    20:13:57  action completed, external ID [job_201401070500_217583]
    20:13:57  Launcher ERROR, reason: Main class [org.apache.oozie.action.hadoop.HiveMain], exit code [10044]
    20:13:57  ERROR is considered as FAILED for SLA
ACTION[@fail] 
    20:13:57  Start action [@fail] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
    20:13:57  Action status=DONE
    20:13:57  Action updated in DB!
ACTION[-] 
    20:13:58  E1100: Command precondition does not hold before execution, [, coord action is null], Error Code: E1100
Categories: DBA Blogs

Join us at the OUG Ireland 2014!

The Oracle Instructor - Thu, 2014-02-13 07:25

Oracle User Group Ireland 2014 Conference

The Oracle User Group Ireland has their annual conference in Dublin with an interesting agenda.

Speakers like Tom Kyte and Timothy Hall stand as a guarantee for high quality.

Oracle University is also showing presence there with Joel Goodman and me, not only as speakers on Tuesday but also with two Master Classes on Wednesday during the OUG Ireland 12c Workshop.

Hope to see you there :-)


Tagged: OUG Ireland 2014
Categories: DBA Blogs

Simple Issue with ORA-00108

Surachart Opun - Wed, 2014-02-12 12:47
My friend asked me to check about error in alert log file - "dispatcher 'D000' encountered error getting listening address". After checked, I found.
Wed Feb 12 09:46:27 2014
dispatcher 'D000' encountered error getting listening address
Wed Feb 12 09:46:27 2014
found dead dispatcher 'D000', pid = (17, 154)I checked trace file about d000 processed.
Trace file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_d000_31988.trc
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name:    Linux
Node name:      linux-host
Release:        2.6.39-400.21.1.el6uek.x86_64
Version:        #1 SMP Thu Apr 4 03:49:00 PDT 2013
Machine:        x86_64
Instance name: PROD
Redo thread mounted by this instance: 1
Oracle process number: 17
Unix process pid: 31988, image: oracle@linux-host (D000)


*** 2014-02-12 09:57:35.577
*** CLIENT ID:() 2014-02-12 09:57:35.577
*** SERVICE NAME:() 2014-02-12 09:57:35.577
*** MODULE NAME:() 2014-02-12 09:57:35.577
*** ACTION NAME:() 2014-02-12 09:57:35.577

network error encountered getting listening address:
  NS Primary Error: TNS-12533: TNS:illegal ADDRESS parameters
  NS Secondary Error: TNS-12560: TNS:protocol adapter error
  NT Generic Error: TNS-00503: Illegal ADDRESS parameters
OPIRIP: Uncaught error 108. Error stack:
ORA-00108: failed to set up dispatcher to accept connection asynchronously

(END)I tried to find out... but no idea... So, checked /etc/hosts
[oracle@linux-host trace]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

[oracle@linux-host trace]$ vi /etc/hosts
[oracle@linux-host trace]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

172.30.6.10     linux-hostIssue was fixed!  Oh! no... I found out on Oracle Support & Internet, but not get solution, but issue was about hostname. - -"Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs

login.sql does not require a login

Hemant K Chitale - Wed, 2014-02-12 07:55
Oracle's sqlplus can use a login.sql file to execute commands -- e.g. setup options.
This file is read and executed when you start sqlplus, even without having logged in to a database.
Here's a quick demo :
I start an sqlplus session without a login.sql
[oracle@localhost ~]$ pwd
/home/oracle
[oracle@localhost ~]$ ls -l login.sql
ls: login.sql: No such file or directory
[oracle@localhost ~]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 12 08:01:43 2014

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show pagesize
pagesize 14
SQL> show linesize
linesize 80
SQL> show sqlprompt
sqlprompt "SQL> "
SQL>

Now, I create a login.sql and invoke sqlplus without logging in to the database.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ vi login.sql
[oracle@localhost ~]$ cat login.sql
set pagesize 60
set linesize 132
set sqlprompt 'HemantSQL>'
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 12 08:05:24 2014

Copyright (c) 1982, 2010, Oracle. All rights reserved.

HemantSQL>show pagesize
pagesize 60
HemantSQL>show linesize
linesize 132
HemantSQL>show user
USER is ""
HemantSQL>

Without having connected to a database (and created a database session), the login.sql was executed.

I can also have it dynamically use a variable --- e.g. the sqlprompt changing based on my login username.

HemantSQL>exit
[oracle@localhost ~]$ vi login.sql
[oracle@localhost ~]$ cat login.sql
set pagesize 60
set linesize 132
set sqlprompt '_USER>'
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 12 08:08:12 2014

Copyright (c) 1982, 2010, Oracle. All rights reserved.

>
>show user
USER is ""
>connect hemant/hemant
Connected.
HEMANT>show user
USER is "HEMANT"
HEMANT>connect hr/oracle
Connected.
HR>show user
USER is "HR"
HR>
HR>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$

Notice how the sqlprompt was simply ">" when no user was logged in ? On the "HEMANT" and "HR" logins, the prompt did change.

.
.
.
Categories: DBA Blogs

Library cache lock scripts for RAC

Bobby Durrett's DBA Blog - Tue, 2014-02-11 18:16

I’ve been having issues for a long time now with an Exadata RAC database that has user reports experiencing library cache lock waits.  The challenge is to figure out what is holding the library cache locks that the queries are waiting on.

My starting point on library cache locks has always been this Oracle support document:

How to Find which Session is Holding a Particular Library Cache Lock (Doc ID 122793.1)

But it doesn’t tell you how to find the session across nodes of a RAC database.

I also found this helpful blog post that briefly addresses finding the session across RAC nodes: Library cache lock and library cache pin waits

I’ve spent many hours over more than a year now dealing with these waits without a lot of success so I finally tried to build a script that I could run regularly to try to capture information about the sessions holding the library cache locks.

First, I knew from Oracle’s document that the x$kgllk table could be used to find the blocking session on a single node so I included queries against this table in my script and set it up so I would ssh into every node of the cluster and run a queries like this against each node:

-- sessions on this instance that are waiting on
-- library cache lock waits
-- unioned with
-- sessions on this instance that are holding locks that other
-- sessions on this instance are waiting on.

insert into myuser1.library_cache_lock_waits
(
SOURCETABLE,
BLOCKER,
SAMPLE_TIME,
INST_ID,
SID,
USERNAME,
STATUS,
OSUSER,
MACHINE,
PROGRAM,
LOGON_TIME,
LAST_CALL_ET,
KGLLKHDL,
KGLLKREQ,
USER_NAME,
KGLNAOBJ,
sql_id,
SQL_FULLTEXT
)
(select
'X\$KGLLK',
'N',
sysdate,
(select INSTANCE_NUMBER from v\$instance),
s.SID,
s.USERNAME,
s.STATUS,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
s.LOGON_TIME,
s.LAST_CALL_ET,
x.KGLLKHDL,
x.KGLLKREQ,
x.USER_NAME,
x.KGLNAOBJ,
s.sql_id,
q.SQL_FULLTEXT
from 
v\$session s, x\$kgllk x, v\$sql q
where
x.kgllkses=s.saddr and
s.sql_id=q.sql_id(+) and
s.event='library cache lock' and
x.KGLLKREQ > 0 and
q.child_number(+)=0)
union all
(select
'X\$KGLLK',
'Y',
sysdate,
(select INSTANCE_NUMBER from v\$instance),
s.SID,
s.USERNAME,
s.STATUS,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
s.LOGON_TIME,
s.LAST_CALL_ET,
x.KGLLKHDL,
x.KGLLKREQ,
x.USER_NAME,
x.KGLNAOBJ,
s.sql_id,
q.SQL_FULLTEXT
from 
v\$session s, x\$kgllk x, v\$sql q,
x\$kgllk x2
where
x.kgllkses=s.saddr and
s.sql_id=q.sql_id(+) and
x.KGLLKREQ = 0 and
x2.KGLLKREQ > 0 and
x2.KGLLKHDL = x.KGLLKHDL and
q.child_number(+)=0);

commit;

The dollar signs are escaped with a backslash because these queries are part of a Unix shell script.  I picked a few columns that I thought would be helpful from v$session and joined to v$sql to get the text of the blocking and blocked SQL.  Note that I ran these queries as SYSDBA.  Here is an example of my test case where the blocker and blocked sessions are both on one node:

SOURCETABLE                    B Sample Time            INST_ID        SID USERNAME                       STATUS   OSUSER                         MACHINE                                                          PROGRAM                                          Logon Time          LAST_CALL_ET KGLLKHDL           KGLLKREQ USER_NAME                      KGLNAOBJ                                                     SQL_ID        RESOURCE_NAME1                 RESOURCE_NAME2                 SQL_FULLTEXT
------------------------------ - ------------------- ---------- ---------- ------------------------------ -------- ------------------------------ ---------------------------------------------------------------- ------------------------------------------------ ------------------- ------------ ---------------- ---------- ------------------------------ ------------------------------------------------------------ ------------- ------------------------------ ------------------------------ --------------------------------------
X$KGLLK                        N 2014-02-17 17:19:01          1       1183 MYUSER1                        ACTIVE   myuser2                        MYMACHINE                                                        sqlplus.exe                                      2014-02-17 17:18:57            5 00000005F9E7D148          2 MYUSER1                        TEST                                                         g4b4j3a8mms0z                                                               select sum(b) from test
X$KGLLK                        Y 2014-02-17 17:19:03          1        995 MYUSER1                        ACTIVE   myuser2                        MYMACHINE                                                        sqlplus.exe                                      2014-02-17 17:18:52           10 00000005F9E7D148          0 MYUSER1                        TEST                                                         gv7dyp7zvspqg                                                               alter table test modify (a char(100))

Next, I noticed that on gv$session when a session was waiting on library cache lock waits sometimes FINAL_BLOCKING_INSTANCE and FINAL_BLOCKING_SESSION were populated and that might lead me to the session holding the lock.  Also, this query and the ones following can run in a less privileged account – you don’t need SYSDBA.

drop table lcl_blockers;

create table lcl_blockers as
select distinct
s.INST_ID,
s.SID,
s.USERNAME,
s.STATUS,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
s.LOGON_TIME,
s.LAST_CALL_ET,
s.sql_id
from
gv\$session s, 
gv\$session s2
where
s2.FINAL_BLOCKING_INSTANCE=s.INST_ID and
s2.FINAL_BLOCKING_SESSION=s.SID and
s2.event='library cache lock';

insert into myuser1.library_cache_lock_waits
(
SOURCETABLE,
BLOCKER,
SAMPLE_TIME,
INST_ID,
SID,
USERNAME,
STATUS,
OSUSER,
MACHINE,
PROGRAM,
LOGON_TIME,
LAST_CALL_ET,
sql_id,
SQL_FULLTEXT
)
select
'GV\$SESSION',
'Y',
sysdate,
s.INST_ID,
s.SID,
s.USERNAME,
s.STATUS,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
s.LOGON_TIME,
s.LAST_CALL_ET,
s.sql_id,
q.SQL_FULLTEXT
from 
lcl_blockers s, gv\$sql q
where
s.sql_id=q.sql_id(+) and
s.INST_ID=q.INST_ID(+) and
q.child_number(+)=0
order by s.INST_ID,s.sid;

commit;

When this works – sporadically in my tests – it shows the same sort of information the previous queries do for same node locking.  Here is an example of these gv$session queries catching the blocker:

SOURCETABLE                    B Sample Time            INST_ID        SID USERNAME                       STATUS   OSUSER                         MACHINE                                                          PROGRAM                                          Logon Time          LAST_CALL_ET KGLLKHDL           KGLLKREQ USER_NAME                      KGLNAOBJ                                                     SQL_ID        RESOURCE_NAME1                 RESOURCE_NAME2                 SQL_FULLTEXT
------------------------------ - ------------------- ---------- ---------- ------------------------------ -------- ------------------------------ ---------------------------------------------------------------- ------------------------------------------------ ------------------- ------------ ---------------- ---------- ------------------------------ ------------------------------------------------------------ ------------- ------------------------------ ------------------------------ --------------------------------------
GV$SESSION                     Y 2014-02-17 17:19:05          1        995 MYUSER1                        ACTIVE   myuser2                        MYMACHINE                                                        sqlplus.exe                                      2014-02-17 17:18:52           12                                                                                                                         gv7dyp7zvspqg                                                               alter table test modify (a char(100))

Lastly, I got a cross node query working that uses the view gv$ges_blocking_enqueue.  The key to making this query was that the pid column in gv$ges_blocking_enqueue is the same as the spid column in gv$process.

-- join gv$ges_blocking_enqueue, gv$session, gv$process to show 
-- cross node library cache lock blockers.  Blocked session will 
-- have event=library cache lock.

drop table ges_blocked_blocker;

create table ges_blocked_blocker as
(select distinct
'N' blocker,
s.INST_ID,
s.SID,
s.USERNAME,
s.STATUS,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
s.LOGON_TIME,
s.LAST_CALL_ET,
s.sql_id,
s.process,
p.spid,
e.RESOURCE_NAME1,
e.RESOURCE_NAME2
from
gv\$session s, 
gv\$process p,
gv\$ges_blocking_enqueue e
where
s.event='library cache lock' and 
s.inst_id=p.inst_id and
s.paddr=p.addr and
p.inst_id=e.inst_id and
p.spid=e.pid and
e.blocked > 0)
union
(select distinct
'Y',
s.INST_ID,
s.SID,
s.USERNAME,
s.STATUS,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
s.LOGON_TIME,
s.LAST_CALL_ET,
s.sql_id,
s.process,
p.spid,
e.RESOURCE_NAME1,
e.RESOURCE_NAME2
from
gv\$session s, 
gv\$process p,
ges_blocked b,
gv\$ges_blocking_enqueue e
where
s.inst_id=p.inst_id and
s.paddr=p.addr and
p.inst_id=e.inst_id and
p.spid=e.pid and
b.RESOURCE_NAME1=e.RESOURCE_NAME1 and
b.RESOURCE_NAME2=e.RESOURCE_NAME2 and
e.blocker > 0);

insert into myuser1.library_cache_lock_waits
(
SOURCETABLE,
BLOCKER,
SAMPLE_TIME,
INST_ID,
SID,
USERNAME,
STATUS,
OSUSER,
MACHINE,
PROGRAM,
LOGON_TIME,
LAST_CALL_ET,
sql_id,
SQL_FULLTEXT,
RESOURCE_NAME1,
RESOURCE_NAME2
)
select
'GV\$GES_BLOCKING_ENQUEUE',
s.blocker,
sysdate,
s.INST_ID,
s.SID,
s.USERNAME,
s.STATUS,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
s.LOGON_TIME,
s.LAST_CALL_ET,
s.sql_id,
q.SQL_FULLTEXT,
s.RESOURCE_NAME1,
s.RESOURCE_NAME2
from 
ges_blocked_blocker s, gv\$sql q
where
s.sql_id=q.sql_id(+) and
s.INST_ID=q.INST_ID(+) and
q.child_number(+)=0
order by s.INST_ID,s.sid;

commit;

Here is some example output from my gv$ges_blocking_enqueue script.  I edited my username, machine name, etc. to obscure these.

SOURCETABLE                    B Sample Time            INST_ID        SID USERNAME                       STATUS   OSUSER                         MACHINE                                                          PROGRAM                                          Logon Time          LAST_CALL_ET KGLLKHDL           KGLLKREQ USER_NAME                      KGLNAOBJ                                                     SQL_ID        RESOURCE_NAME1                 RESOURCE_NAME2                 SQL_FULLTEXT
------------------------------ - ------------------- ---------- ---------- ------------------------------ -------- ------------------------------ ---------------------------------------------------------------- ------------------------------------------------ ------------------- ------------ ---------------- ---------- ------------------------------ ------------------------------------------------------------ ------------- ------------------------------ ------------------------------ --------------------------------------
GV$GES_BLOCKING_ENQUEUE        N 2014-02-17 17:19:55          2        301 MYUSER1                        ACTIVE   myuser2                        MYMACHINE                                                        sqlplus.exe                                      2014-02-17 17:19:46            7                                                                                                                         g4b4j3a8mms0z [0x426d0373][0x224f1299],[LB][ 1114440563,575607449,LB        select sum(b) from test
GV$GES_BLOCKING_ENQUEUE        Y 2014-02-17 17:19:55          1        497 MYUSER1                        ACTIVE   myuser2                        MYMACHINE                                                        sqlplus.exe                                      2014-02-17 17:19:41           13                                                                                                                         gv7dyp7zvspqg [0x426d0373][0x224f1299],[LB][ 1114440563,575607449,LB        alter table test modify (a char(100))

The alter table command on node 1 is holding the lock while the select statement on node 2 is waiting on the library cache lock.

So, I've got this going on a script that runs every 15 minutes in production.  It worked great in my test case but time will tell if it yields any useful information for our real problems.

- Bobby

p.s. I've uploaded a zip of my scripts: zip

Here is a description of the included files:

Testcase to create a library cache lock:

create.sql - creates a table with one character first column CHAR(1)
alter.sql - alters table expanding CHAR column
query.sql - queries table - waits on library cache lock wait if run while alter.sql is running

all.sh - top level script - you will need to edit to have the host names for your RAC cluster and to have your own userid and password

lcl.sh - x$ table script that is run on each node.  Only key thing is that our profile required a 1 to be entered to choose the first database from a list.  You may not need that line.

resultstable.sql - create table to save results

dumpresults.sql - dump out all results

dumpresultsnosql.sql - dump out all results except sql text so easier to read.

Here is the definition of the results table:

create table myuser1.library_cache_lock_waits
(
 SOURCETABLE    VARCHAR2(30),
 BLOCKER        VARCHAR2(1),
 SAMPLE_TIME    DATE,
 INST_ID        NUMBER,
 SID            NUMBER,
 USERNAME       VARCHAR2(30),
 STATUS         VARCHAR2(8),
 OSUSER         VARCHAR2(30),
 MACHINE        VARCHAR2(64),
 PROGRAM        VARCHAR2(48),
 LOGON_TIME     DATE,
 LAST_CALL_ET   NUMBER,
 KGLLKHDL       RAW(8),
 KGLLKREQ       NUMBER,
 USER_NAME      VARCHAR2(30),
 KGLNAOBJ       VARCHAR2(60),
 SQL_ID         VARCHAR2(13),
 RESOURCE_NAME1 VARCHAR2(30),
 RESOURCE_NAME2 VARCHAR2(30),
 SQL_FULLTEXT   CLOB
);

P.P.S. This was all tested only on Exadata running 11.2.0.2.

Oracle documentation on Library Cache:

The library cache is a shared pool memory structure that stores executable SQL and PL/SQL code. This cache contains the shared SQL and PL/SQL areas and control structures such as locks and library cache handles. In a shared server architecture, the library cache also contains private SQL areas.

Oracle 12c Concepts manual diagram with library cache

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Categories: DBA Blogs

Kafka or Flume?

Chen Shapira - Tue, 2014-02-11 16:25

A question that keeps popping up is “Should we use Kafka or Flume to load data to Hadoop clusters?”

This question implies that Kafka and Flume are interchangeable components. It makes as much sense to me as “Should we use cars or umbrellas?”. Sure, you can hide from the rain in your car and you can use your umbrella when moving from place to place. But in general, these are different tools intended for different use-cases.

Flume’s main use-case is to ingest data into Hadoop. It is tightly integrated with Hadoop’s monitoring system, file system, file formats, and utilities such a Morphlines. A lot of the Flume development effort goes into maintaining compatibility with Hadoop. Sure, Flume’s design of sources, sinks and channels mean that it can be used to move data between other systems flexibly, but the important feature is its Hadoop integration.

Kafka’s main use-case is a distributed publish-subscribe messaging system. Most of the development effort is involved with allowing subscribers to read exactly the messages they are interested in, and in making sure the distributed system is scalable and reliable under many different conditions. It was not written to stream data specifically for Hadoop, and using it to read and write data to Hadoop is significantly more challenging than it is in Flume.

To summarize:
Use Flume if you have an non-relational data sources such as log files that you want to stream into Hadoop.
Use Kafka if you need a highly reliable and scalable enterprise messaging system to connect many multiple systems, one of which is Hadoop.


Categories: DBA Blogs

Partner Webcast – Oracle ISV Application Modernization: It's All about the Business

Infographic: See How Cloud  Empowers Innovation Technology is changing the world in ways we haven’t seen before. Industries are evolving,...

We share our skills to maximize your revenue!
Categories: DBA Blogs

The MySQL Community Pay-Your-Own-Way Dinner

Pythian Group - Mon, 2014-02-10 10:26

Once again, Pythian is organizing an event that by now may be considered a tradition: The MySQL community dinner at Pedro’s! This dinner is open to all MySQL community members, as many of you will be in town for the MySQL Conference that week. Here are the details:

What: The MySQL Community Pay-Your-Own-Way Dinner

When: Wednesday April 2, 2014 – Meet us at 6:30 PM in the lobby of the Hyatt Santa Clara, or at 7 PM at Pedro’s (You are welcome to show up later, too!)

Cost: The meal will be $25 USD including tax and gratuities. Please bring cash. (See menu below)

Where: Pedro’s Restaurant and Cantina – 3935 Freedom Circle, Santa Clara, CA 95054

How: RSVP through Eventbrite

Please note: Due to the historically high attendance for this event, Pedro’s has asked that each person pays in cash to simplify billing. Pedro’s can handle large groups of people, but we would like to have an idea of how many people are attending to give Pedro’s an accurate number of attendees so that they can be adequately prepared.

Pythian attendees:

1. Paul Vallee

2. Wagner Bianchi

3. Danil Zburivsky

4. Alex Gorbachev

5. Derek Downey

6. Chad Scheiter

7. Add your name…

Looking forward to seeing you all at the event!

menu_pedros
Categories: DBA Blogs

2013 Year in Review – Oracle E-Business Suite

Pythian Group - Mon, 2014-02-10 10:25

Here are the Top 5 things in Oracle E-Business Suite world that will have major impact in 2014 and beyond.

1. Oracle E-Business Suite 12.2 Now Available

2013 started on a low note in Oracle E-Business Suite (EBS) World. Many people were expecting some announcement related to upcoming EBS release during Openworld 2012. But all they got it is a extension of support deadline  for existing 11i EBS customers. Oracle finally announced Oracle EBS R12.2 few days before Openworld 2013. This releases packs exciting features like Online Patching, which elevates Oracle E-Business Suite ranking in ERP systems domain. Online Patching will enable large multi-national customers consolidate their ERP systems in different Countries into one Single Global Oracle E-Business Suite instance, as it cuts down the downtime required for patching maintenance window to all most nil.  This is a big plus point for clients who cannot afford downtime because their user base is spread all over the world. 2014 will be a years of upgrades to R12.2 for all clients.

2. 12.1.0.1 Database Certified with Oracle E-Business Suite

Around the same time as R12.2 announcement, Oracle certified 12c Database with Oracle EBS. The good news here is they certified Oracle 11i also with 12c Database. This should give EBS clients option to get onto newest version of Oracle Database and take advantage of the new features of 12c database. The effort involved in upgrading database is significantly less than upgrading to newer version of EBS. So i believe many customers will uptake 12c database upgrade before the R12.2 EBS upgrade. Also upgrading database to newer version earlier than EBS, will save some hours during future R12.2 upgrade downtime window.

3. E-Business Suite Support Timelines Updated at OpenWorld 2013

Oracle once again extended the support timelines for 11i customers. They named it as Exception support and it ends on December 2015. During this Exception support period, Oracle will primarily provide fixes for Sev 1 issues and Security patches. So this gives 2 years of additional time to Customers on 11i to migrate to latest R12.2. With typical R12 upgrades taking around 1 year time, The sooner you plan and start your R12.2 migration the better.

4. No to Third-Party Tools to Modify Your EBS Database

Oracle Development warned officially in their blog about use third party tools to modify, archive & purge data in Oracle E-Business suite. Managing data growth in Oracle EBS is a known problem. Now Oracle wants customers to use Oracle Database technologies like ILM, Advanced Compression and Partitioning, to archive the data instead of using third party utilities. Note that all these database features will cost customers additional money in licensing costs. So get your bargaining hat on with your Oracle Account Manager and score some discounts using this oracle Achilles heel namely EBS purging and archiving data.

5. Sign E-Business Suite JAR Files Now

Do you remember the days when Oracle EBS moved from Oracle Jinitiator to Sun JRE for oracle forms? Then be prepared for one more similar thing around oracle forms. With stream of viruses and malware that exploit bugs in Oracle/Sun JRE made Oracle to tighten security around Oracle JRE. Its now required to sign forms jar files with a real certificate. In future releases of Oracle JRE7, Unsigned Oracle forms will stop working completely. So customers caught unaware of this will be in for big trouble with user complaints.

Categories: DBA Blogs