Skip navigation.

DBA Blogs

Log Buffer #424: A Carnival of the Vanities for DBAs

Pythian Group - Tue, 2015-05-26 11:45

This Log Buffer Edition covers various valuable blog posts from the fields of Oracle, SQL Server and MySQL.

Oracle:

  • Oracle Big Data Appliance X5-2 with Big Data SQL for the DBA.
  • Loading, Updating and Deleting From HBase Tables using HiveQL and Python.
  • In keeping with the ODA quarterly patching strategy, Appliance Manager 12.1.2.3 is now available.
  • From time to time someone publishes a query on the OTN database forum and asks how to make it go faster, and you look at it and think, “it’s a nice example to explain a couple of principles because it’s short, easy to understand, obvious what sort of things might be wrong, and easy to fix.”
  • Optimizing the PL/SQL Challenge IV: More OR Condition Woes.

SQL Server:

  • Will RDBMs be obsolete? Should Data Professionals care about Big Data technologies? What is NoSQL? What is Hadoop?
  • In a development team, there are times when the relationships between developers and testers can become strained. How can you turn this potential conflict into something more positive?
  • Michael Fal is a huge advocate of automation and many ways it can improve the lives of developers and DBAs alike, but you can’t just automate all your problems away.
  • One way to handle a very complex database project with several databases and cross references.
  • Building the Ideal VMware-based SQL Server Virtual Machine.

MySQL:

  • Optimizing Out-of-order Parallel Replication with MariaDB 10.0.
  • General-purpose MySQL applications should read MySQL option files like /etc/my.cnf, ~/.my.cnf, … and ~/.mylogin.cnf. But ~/.mylogin.cnf is encrypted.
  • Creating and Restoring Database Backups With mysqldump and MySQL Enterprise Backup.
  • If you don’t know much about bash shell, you should start with the prior post to learn about bash arrays.
  • Installing Kubernetes Cluster with 3 minions on CentOS 7 to manage pods and services.

Learn more about Pythian’s expertise in Oracle , SQL Server and MySQL.

Categories: DBA Blogs

Oracle MAF - Managing Local Notifications

Oracle has been working really hard to keep adding new features to Oracle Mobile Application Framework. One of the New Features in Oracle MAF 2.1.1 release is Managing Local Notifications. In this...

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

Webcast - Process Cloud Service (PCS) Technical Overview

Process Rapidly design, automate and manage business processes in the cloud. This presentation provides technical detail with demo of the latest Oracle Process Cloud...

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

Recover Oracle Undo Tablespace without Backup

Pakistan's First Oracle Blog - Sun, 2015-05-24 20:10
Woke up with an issue regarding a Oracle 10.2.0 database on Linux complaining about an Undo file on startup.


sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.3.0 - Production on Fri May 22 20:11:07 2015

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup pfile='init.ora'
ORACLE instance started.

Total System Global Area 2801795072 bytes
Fixed Size                  2075504 bytes
Variable Size            1275069584 bytes
Database Buffers         1509949440 bytes
Redo Buffers               14700544 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 244 - see DBWR trace file
ORA-01110: data file 244: '/test/ORADATATEST/test/test_undo2a.dbf'


SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     21600
undo_tablespace                      string      test_UNDO02
SQL>



SQL> drop tablespace test_UNDO02  including contents and datafiles;
drop tablespace test_UNDO02  including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU4$' found, terminate dropping tablespace

 Check for active rollback segments:

 select segment_id, segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE');



Set the following parameter in the pfile.



*._offline_rollback_segments=(_SYSSMU4$)


And now try dropping UNDO tablespace again.

drop tablespace test_UNDO02  including contents and datafiles;

Tablespace dropped.

Now create a new UNDO tablespace:

create UNDO tablespace test_UNDO05 datafile '/test/oradata18/test/test_undo05_file1.dbf' size 500m autoextend on next 1m maxsize 1500m;


Tablespace created.



SQL> create UNDO tablespace test_UNDO05 datafile '/test/oradata18/test/test_undo05_file1.dbf' size 500m autoextend on next 1m maxsize 1500m;


Tablespace created.



SQL> startup pfile='inittest.ora'
ORACLE instance started.

Total System Global Area 2801795072 bytes
Fixed Size                  2075504 bytes
Variable Size            1392510096 bytes
Database Buffers         1392508928 bytes
Redo Buffers               14700544 bytes
Database mounted.
Database opened.

All good now.
Categories: DBA Blogs

Parallel Execution -- 6 Parallel DML Restrictions

Hemant K Chitale - Sun, 2015-05-24 09:18
Here's link to the 11.2 documentation on Restrictions on Parallel DML

So, some of the implications are :

1.  You cannot do Parallel DML if a Trigger is present on the target table

2.  Certain integrity constraints disable Parallel DML.

3,  Parallel DML cannot be part of a Distributed Transaction.

Oracle silently converts the Parallel DML to a Serial DML without raising a warning / error.

.
.
.

Categories: DBA Blogs

Webcast - Hybrid Cloud Integration Business Opportunities for Partners

Integration Simplify Integration. Customers are running their business applications in multiple ways; on–premise, hosted or as Cloud application (SaaS). There are demand and...

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

EM12c : Login to GUI with the correct password causes authentication failure

Pythian Group - Thu, 2015-05-21 16:47

So the other day I was trying to log in to my EM12c R4 environment with the SSA_ADMINISTRATOR user, and I got the error:

“Authentication failed. If problem persists, contact your system administrator”

I was quite sure that the password that I had was correct, so I tried with the SYSMAN user and had the same error. I still wanted to verify that I had the correct password , so I tried with the SYSMAN user to log in to the repository database, and was successful, so I know something was wrong there.


SQL> connect sysman/
Enter password:
Connected.

So I went to the<gc_inst>/em/EMGC_OMS1/sysman/log/emoms.log and saw the following error


2015-05-18 21:22:06,103 [[ACTIVE] ExecuteThread: '15' for queue: 'weblogic.kernel.Default (self-tuning)'] ERROR audit.AuditManager auditLog.368 - Could not Log audit data, Error:java.sql.SQLException: ORA-14400: inserted partition key does not map to any partition
ORA-06512: at &quot;SYSMAN.MGMT_AUDIT&quot;, line 492
ORA-06512: at &quot;SYSMAN.MGMT_AUDIT&quot;, line 406
ORA-06512: at line 1

Which led me to believe that the JOB_QUEUE_PROCESSES was set to 0, but it wasn’t the case, since it was set to 50. Though, this is actually an incorrect limit, so I bumped it up to 1000 and tried to rerun the EM12c repository DBMS Scheduler jobs as per the documentation in 1498456.1:


SQL&gt; show parameter JOB_QUEUE_PROCESSES

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 50
SQL&gt; alter system set JOB_QUEUE_PROCESSES=1000 scope = both;

System altered.

SQL&gt; show parameter both
SQL&gt; show parameter job

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
SQL&gt; connect / as sysdba
Connected.
SQL&gt; alter system set job_queue_processes = 0;

System altered.

SQL&gt; connect sysman/alyarog1605
Connected.
SQL&gt; exec emd_maintenance.remove_em_dbms_jobs;

PL/SQL procedure successfully completed.

SQL&gt; exec gc_interval_partition_mgr.partition_maintenance;

PL/SQL procedure successfully completed.

SQL&gt; @$OMS_HOME/sysman/admin/emdrep/sql/core/latest/admin/admin_recompile_invalid.sql SYSMAN
old 11: AND owner = upper('&amp;RECOMPILE_REPOS_USER')
new 11: AND owner = upper('SYSMAN')
old 26: dbms_utility.compile_schema(upper('&amp;RECOMPILE_REPOS_USER'),FALSE);
new 26: dbms_utility.compile_schema(upper('SYSMAN'),FALSE);
old 41: WHERE owner = upper('&amp;RECOMPILE_REPOS_USER')
new 41: WHERE owner = upper('SYSMAN')
old 84: AND owner = upper('&amp;RECOMPILE_REPOS_USER')
new 84: AND owner = upper('SYSMAN')
old 104: AND ds.table_owner = upper('&amp;RECOMPILE_REPOS_USER')
new 104: AND ds.table_owner = upper('SYSMAN')

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

SQL&gt; connect / as sysdba
Connected.
SQL&gt; alter system set job_queue_processes = 1000;

System altered.

SQL&gt; connect sysman/
Enter password:
Connected.
SQL&gt; exec emd_maintenance.submit_em_dbms_jobs;

PL/SQL procedure successfully completed.

SQL&gt; commit;

Commit complete.

After this I bounced the OMS, but still kept getting the same error. And though it fixed the scheduler jobs, I was now seeing the following error in the emoms.log:


2015-05-18 22:29:09,573 [[ACTIVE] ExecuteThread: '15' for queue: 'weblogic.kernel.Default (self-tuning)'] WARN auth.EMRepLoginFilter doFilter.450 - InvalidEMUserException caught in EMRepLoginFilter: Failed to login using repository authentication for user: SSA_ADMIN
oracle.sysman.emSDK.sec.auth.InvalidEMUserException: Failed to login using repository authentication for user: SSA_ADMIN

So what I did was an update to the SYSMAN.MGMT_AUDIT_MASTER table and ran the procedure MGMT_AUDIT_ADMIN.ADD_AUDIT_PARTITION as was stated in document id 1493151.1:


oracle $ sqlplus

&amp;nbsp;

Enter user-name: sysman
Enter password:

SQL&gt; update mgmt_audit_master set prepopulate_days=5 where prepopulate_days is null;

1 rows updated.

SQL&gt; select count(1) from mgmt_audit_master where prepopulate_days is null;

COUNT(1)
----------
0

SQL&gt; exec mgmt_audit_admin.add_audit_partition;

PL/SQL procedure successfully completed.

SQL&gt; commit;

Commit complete.

Once I did this, I was able to login with all my EM12c administrators without any issues:


oracle@em12cr4.localdomain [emrep] /home/oracle
oracle $ emcli login -username=ssa_admin
Enter password

Login successful

Conclusion

Even though the JOB_QUEUE_PROCESSES were not set to 0, it was the cause that it was failing, as it was a low value for this parameter. Thus, be careful when setting up this parameter, be sure to follow the latest installation guidelines.

Note– This was originally published on rene-ace.com

Categories: DBA Blogs

Oracle Big Data Appliance X5-2 with Big Data SQL for the DBA

During January 2015, Oracle Executive Chairman and Chief Technology Officer Larry Ellison announced Oracle’s next-generation engineered systems, the fifth generation X5. The X5 portfolio of Oracle's...

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

Log Buffer #423: A Carnival of the Vanities for DBAs

Pythian Group - Wed, 2015-05-20 16:36

This Log Buffer edition covers Oracle, SQL Server and MySQL blog posts from all over the blogosphere!


Oracle:

Hey DBAs:  You know you can  install and run Oracle Database 12c on different platforms, but if you install it on an Oracle Solaris 11 zone, you can take additional advantages.

Here is a video with Oracle VP of Global Hardware Systems Harish Venkat talking with Aaron De Los Reyes, Deputy Director at Cognizant about his company’s explosive growth & how they managed business functions, applications, and supporting infrastructure for success.

Oracle Unified Directory is an all-in-one directory solution with storage, proxy, synchronization and virtualization capabilities. While unifying the approach, it provides all the services required for high-performance enterprise and carrier-grade environments. Oracle Unified Directory ensures scalability to billions of entries. It is designed for ease of installation, elastic deployments, enterprise manageability, and effective monitoring.

Understanding Flash: Summary – NAND Flash Is A Royal Pain In The …

Extracting Oracle data & Generating JSON data file using ROracle.

SQL Server:

It is no good doing some or most of the aspects of SQL Server security right. You have to get them all right, because any effective penetration of your security is likely to spell disaster. If you fail in any of the ways that Robert Sheldon lists and describes, then you can’t assume that your data is secure, and things are likely to go horribly wrong.

How does a column store index compare to a (traditional )row store index with regards to performance?

Learn how to use the TOP clause in conjunction with the UPDATE, INSERT and DELETE statements.

Did you know that scalar-valued, user-defined functions can be used in DEFAULT/CHECK CONSTRAINTs and computed columns?

Tim Smith blogs as how to measure a behavioral streak with SQL Server, an important skill for determining ROI and extrapolating trends.

Pilip Horan lets us know as How to run SSIS Project as a SQL Job.

MySQL:

Encryption is important component of secure environments. While being intangible, property security doesn’t get enough attention when it comes to describing various systems. “Encryption support” is often the most of details what you can get asking how secure the system is. Other important details are often omitted, but the devil in details as we know. In this post I will describe how we secure backup copies in TwinDB.

The fsfreeze command, is used to suspend and resume access to a file system. This allows consistent snapshots to be taken of the filesystem. fsfreeze supports Ext3/4, ReiserFS, JFS and XFS.

Shinguz: Controlling worldwide manufacturing plants with MySQL.

MySQL 5.7.7 was recently released (it is the latest MySQL 5.7, and is the first “RC” or “Release Candidate” release of 5.7), and is available for download

Upgrading Directly From MySQL 5.0 to 5.6 With mysqldump.

One of the cool new features in 5.7 Release Candidate is Multi Source Replication.

 

Learn more about Pythian’s expertise in Oracle , SQL Server and MySQL.

Categories: DBA Blogs

Webcast - Oracle Document Cloud Service (DOCS): Technical Overview

Documents Enterprise File Sync and Share in the Oracle Cloud. This presentation provides technical detail with demo of the latest Oracle Document Cloud Services.  ...

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

Free MIT Computer Science Classes Online

Bobby Durrett's DBA Blog - Wed, 2015-05-20 14:30

This is very cool:

There are a bunch of nice computer science classes online from MIT: Free online MIT computer science classes

Here is an introductory computer science class: Intro to computer science

Here is a graded version of the same class on edX: Graded version of MIT intro CS class starting June 10th.

edX does not have as many computer science classes but edX may motivate students because edX classes include grades and certificates for those who pass.

I use computer science every day in my database work but I have not taken a formal class since 1989.

I have been on a computer science kick ever since watching The Imitation Game.  I downloaded Turing’s 1936 paper after watching the movie.  I got about halfway through it before giving up.  It was dense! Maybe will take another stab at it some day. But, the MIT classes are neat because they are the way computer science is now taught, and hopefully they are easier to understand than Turing’s paper.

– Bobby

Categories: DBA Blogs

Troubleshooting ASM Proxy instance startup

Oracle in Action - Wed, 2015-05-20 08:53

RSS content

Recently, I had trouble starting ASM proxy instance on one of the nodes in my  2 node flex cluster having nodes host01 and host02. As a result I could not access the volume I created on an ASM  diskgroup.  This post explains  how I resolved it.

While connected to host01, I created a volume VOL1 on DATA diskgroup with corresponding volume device /dev/asm/vol1-106 .

[grid@host01 root]$ asmcmd volcreate -G DATA -s 300m VOL1

[grid@host01 root]$ asmcmd volinfo -G DATA VOL1

Diskgroup Name: DATA

Volume Name: VOL1
Volume Device: /dev/asm/vol1-106
State: ENABLED
Size (MB): 320
Resize Unit (MB): 32
Redundancy: MIRROR
Stripe Columns: 4
Stripe Width (K): 128
Usage: ACFS
Mountpath:

I created  ACFS file system on the newly created volume

[root@host01 ~]# mkfs -t acfs /dev/asm/vol1-106

I also created corresponding mount point /mnt/acfsmounts/acfs1 on both the nodes in the cluster.

root@host01 ~]# mkdir -p /mnt/acfsmounts/acfs1

root@host02 ~]# mkdir -p /mnt/acfsmounts/acfs1

When I tried to mount the volume device, I could mount the volume device on host01 but not on host02 .

[root@host01 ~]#mount -t acfs /dev/asm/vol1-106 /mnt/acfsmounts/acfs1

[root@host01 ~]# mount | grep vol1

/dev/asm/vol1-106 on /mnt/acfsmounts/acfs1 type acfs (rw)

[root@host02 ~]# mount -t acfs /dev/asm/vol1-106 /mnt/acfsmounts/acfs1

mount.acfs: CLSU-00100: Operating System function: open64 failed with error data: 2
mount.acfs: CLSU-00101: Operating System error message: No such file or directory
mount.acfs: CLSU-00103: error location: OOF_1
mount.acfs: CLSU-00104: additional error information: open64 (/dev/asm/vol1-106)
mount.acfs: ACFS-02017: Failed to open volume /dev/asm/vol1-106. Verify the volume exists.

The corresponding volume device was visible on host01 but not on host02

[root@host01 ~]# cd /dev/asm
[root@host01 asm]# ls
vol1-106

[root@host02 ~]# cd /dev/asm
[root@host02 asm]# ls

Since ADVM / ACFS utilize an ASM Proxy instance in a flex cluster to access metadata from a local /  remote  ASM instance ,  I checked whether ASM Proxy instance was running on both the nodes and realized that whereas ASM Proxy instance was running on host01, it  was not running on host02

[root@host01 ~]# ps -elf | grep pmon | grep APX

0 S grid 27782 1 0 78 0 – 350502 – 10:09 ? 00:00:00 apx_pmon_+APX1

[root@host02 asm]# ps -elf | grep pmon | grep APX

[root@host01 ~]# srvctl status asm -proxy

ADVM proxy is running on node host01

[root@host01 ~]# crsctl stat res ora.proxy_advm -t
——————————————————————————–
Name Target State Server State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.proxy_advm
ONLINE ONLINE host01 STABLE
ONLINE OFFLINE host02 STABLE

I tried to start ASM  proxy instance manually on host02

[grid@host02 ~]$ . oraenv
ORACLE_SID = [grid] ? +APX2
The Oracle base has been set to /u01/app/grid

[grid@host02 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.1.0 Production on Sat May 2 10:31:45 2015

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

Connected to an idle instance.

SQL> startup

ORA-00099: warning: no parameter file specified for ASMPROXY instance
ORA-00443: background process "VUBG" did not start

SQL> ho oerr ORA 00443

00443, 00000, "background process \"%s\" did not start"
// *Cause: The specified process did not start.
// *Action: Ensure that the executable image is in the correct place with
// the correct protections, and that there is enough memory.

I checked the memory allocated to VM for host02 – It was 1.5 GB as against 2.5 GB assigned to VM for host01. I  increased the memory of host02 to 2.5 GB and ASM proxy instance started automatically.

[root@host01 ~]# crsctl stat res ora.proxy_advm -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.proxy_advm
ONLINE ONLINE host01 STABLE
ONLINE ONLINE host02 STABLE

Hope it helps!

References: 

Oracle documentation

———————————————————————————————————

Related Links :

Home

12c RAC Index

12c RAC: ORA-15477: cannot communicate with the volume driver



Tags:  

Del.icio.us
Digg

Comments:  0 (Zero), Be the first to leave a reply!
You might be interested in this:  
Copyright © ORACLE IN ACTION [Troubleshooting ASM Proxy instance startup], All Right Reserved. 2015.

The post Troubleshooting ASM Proxy instance startup appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Indexing and Transparent Data Encryption Part II (Hide Away)

Richard Foote - Wed, 2015-05-20 02:03
In Part I, I quickly ran through how to setup an encrypted tablespace using Transparent Data Encryption and to take care creating indexes outside of these tablespaces. Another method of encrypting data in the Oracle database is to just encrypt selected columns. Although the advantage here is that we can just encrypt sensitive columns of interest (and that the […]
Categories: DBA Blogs

Indexing and Transparent Data Encryption Part I (The Secret Life of Arabia)

Richard Foote - Mon, 2015-05-18 23:42
Database security has been a really hot topic recently so I thought I might write a few posts in relation to indexing and Transparent Data Encryption (TDE) which is available as part of the Oracle Advanced Security option. To protect the database from unauthorized “backed-door” accesses, the data within the actual database files can be encrypted. […]
Categories: DBA Blogs

Webcast - Digital Mobile Cloud Business Opportunities for Partners

Mobile Simplify Enterprise Mobile Connectivity. Mobility has been penetrating the enterprise for the last couple of years, and there is no sign of it slowing down.  In...

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

Parallel Execution -- 5b Parallel INSERT Execution Plan

Hemant K Chitale - Sat, 2015-05-16 09:19
As noted in my previous post, Oracle does NOT enable Parallel DML by default.  You need to explicitly enable it with ALTER SESSION ENABLE PARALLEL DML.

Can you use the Execution Plan of an INSERT statement to identify if the INSERT was executed in Parallel ?

Here's a brief demonstration :

SQL*Plus: Release 11.2.0.2.0 Production on Sat May 16 22:27:49 2015

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

HEMANT>truncate table another_large_table;

Table truncated.

HEMANT>select degree from user_tables where table_name = 'ANOTHER_LARGE_TABLE';

DEGREE
----------------------------------------
1

HEMANT>alter table another_large_table parallel 4;

Table altered.

HEMANT>insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE
2 select /*+ PARALLEL */ * from large_table;

4802944 rows created.

HEMANT>select * from table(dbms_xplan.display_cursor('fuuygy5k8nfrh'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fuuygy5k8nfrh, child number 0
-------------------------------------
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */
* from large_table

Plan hash value: 2350597358

---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1314 (100)| | | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | LARGE_TABLE | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select * from table(dbms_xplan.display_cursor('fuuygy5k8nfrh',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fuuygy5k8nfrh, child number 1
-------------------------------------
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */
* from large_table

Plan hash value: 2350597358

---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1314 (100)| | | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | LARGE_TABLE | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: Computed Degree of Parallelism is 16


27 rows selected.

HEMANT>
HEMANT>select count(*) from another_large_table;

COUNT(*)
----------
4802944

HEMANT>

Notice the "LOAD TABLE CONVENTIONAL" ?  Parallel Execution Servers were used for querying the source table LARGE_TABLE but the actual INSERT was executed as a non-parallel INSERT.  Another piece of evidence that the INSERT was not executed using PX Servers is that I was able to query the table without an ORA-12838 error.

Let me try again with the correct ALTER SESSION command.

HEMANT>truncate table another_large_table;

Table truncated.

HEMANT>alter session enable parallel dml;

Session altered.

HEMANT>insert /*+ PARALLEL */ into another_large_table tgt
2 select /*+ PARALLEL */ * from large_table src;

4802944 rows created.

HEMANT>select * from table(dbms_xplan.display_cursor('9scm06z0m9vz6'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9scm06z0m9vz6, child number 0
-------------------------------------
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL
*/ * from large_table src

Plan hash value: 474933689

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1314 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL| LARGE_TABLE | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select * from table(dbms_xplan.display_cursor('9scm06z0m9vz6',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID: 9scm06z0m9vz6, child number: 1 cannot be found


HEMANT>select count(*) from another_large_table;
select count(*) from another_large_table
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


HEMANT>

Here, the ORA-12838 is evidence that the INSERT was Parallel. But that evidence is only visible from the same session. What if we had to use another session to check the Execution ? We'd then use the DBMS_XPLAN.DISPLAY_CURSOR method.  Here we notice the PX COORDINATOR at ID=1 appearing *above* the LOAD AS SELECT.  There is no LOAD TABLE CONVENTIONAL step.  These are evidence that the LOAD was executed by PX Servers.
Note : Ignore the "P->S" in ID=2.



Question : Why does the first (non-parallel insert) statement have 2 child cursors.  And the 2nd child cursor actually does indicate an Auto DoP.  I have evidence that child cursor 0 is executed and child cursor 1, although parsed, shows as 0 executions in V$SQL :

SYS>select sql_id, child_number, executions, sql_text
2 from v$sql
3 where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
4 order by 1,2;

SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
9scm06z0m9vz6 0 1
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL */ * from large_table src

fuuygy5k8nfrh 0 1
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

fuuygy5k8nfrh 1 0
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table


SYS>
SYS>l
1 select sql_id, child_number, executions, px_servers_executions, sql_text
2 from v$sql
3 where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
4* order by 1,2
SYS>/

SQL_ID CHILD_NUMBER EXECUTIONS PX_SERVERS_EXECUTIONS
------------- ------------ ---------- ---------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
9scm06z0m9vz6 0 1 0
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL */ * from large_table src

fuuygy5k8nfrh 0 1 0
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

fuuygy5k8nfrh 1 0 4
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

Interesting, isn't it ? This time, the PX_SERVERS_EXECUTIONS doesn't seem to be correct.  So, either EXECUTIONS is correct or PX_SERVERS_EXECUTIONS is correct.  These need to be re-verified in 11.2.0.4.

UPDATE 18-May-15 :  In 11.2.0.4,  I see 1 child cursor (not 2) with the correct EXECUTIONS and PX_SERVERS_EXECUTIONS being reported.  I will need to rerun my tests in the same 11.2.0.2 environment.


Re-testing in the same 11.2.0.2 environment :
First, the insert that does NOT get executed as a Parallel INSERT :

[oracle@localhost ~]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.2.0 Production on Sun May 24 22:58:28 2015

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

HEMANT>truncate table ANOTHER_LARGE_TABLE;

Table truncated.

HEMANT>select degree from user_tables where table_name = 'ANOTHER_LARGE_TABLE';

DEGREE
----------------------------------------
4

HEMANT>insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE
2 select /*+ PARALLEL */ * from large_table;

4802944 rows created.

HEMANT>select * from table(dbms_xplan.display_cursor('fuuygy5k8nfrh'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fuuygy5k8nfrh, child number 0
-------------------------------------
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */
* from large_table

Plan hash value: 2350597358

---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1314 (100)| | | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | LARGE_TABLE | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select * from table(dbms_xplan.display_cursor('fuuygy5k8nfrh',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fuuygy5k8nfrh, child number 1
-------------------------------------
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */
* from large_table

Plan hash value: 2350597358

---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1314 (100)| | | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | LARGE_TABLE | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: Computed Degree of Parallelism is 16


27 rows selected.

HEMANT>select count(*) from another_large_table;

COUNT(*)
----------
4802944

HEMANT>
SYSTEM>select sql_id, child_number, executions, px_servers_executions, sql_text
2 from v$sql
3 where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
4 order by 1,2;

SQL_ID CHILD_NUMBER EXECUTIONS PX_SERVERS_EXECUTIONS
------------- ------------ ---------- ---------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
fuuygy5k8nfrh 0 1 0
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

fuuygy5k8nfrh 1 0 4
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table


SYSTEM>

Next, the actual Parallel Insert.

HEMANT>commit;

Commit complete.

HEMANT>alter session enable parallel dml;

Session altered.

HEMANT>insert /*+ PARALLEL */ into another_large_table tgt
2 select /*+ PARALLEL */ * from large_table src;

4802944 rows created.

HEMANT>select * from table(dbms_xplan.display_cursor('9scm06z0m9vz6'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9scm06z0m9vz6, child number 0
-------------------------------------
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL
*/ * from large_table src

Plan hash value: 474933689

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1314 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL| LARGE_TABLE | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select * from table(dbms_xplan.display_cursor('9scm06z0m9vz6',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID: 9scm06z0m9vz6, child number: 1 cannot be found


HEMANT>select count(*) from another_large_table;
select count(*) from another_large_table
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


HEMANT>
SYSTEM>l
1 select sql_id, child_number, executions, px_servers_executions, sql_text
2 from v$sql
3 where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
4* order by 1,2
SYSTEM>
SYSTEM>/

SQL_ID CHILD_NUMBER EXECUTIONS PX_SERVERS_EXECUTIONS
------------- ------------ ---------- ---------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
9scm06z0m9vz6 0 1 0
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL */ * from large_table src

fuuygy5k8nfrh 0 1 0
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

fuuygy5k8nfrh 1 0 4
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table


SYSTEM>
SYSTEM>l
1 select sql_id, executions, px_servers_executions, sql_text
2 from v$sqlstats
3 where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
4* order by 1,2
SYSTEM>/

SQL_ID EXECUTIONS PX_SERVERS_EXECUTIONS
------------- ---------- ---------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
9scm06z0m9vz6 1 0
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL */ * from large_table src

fuuygy5k8nfrh 1 4
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table


SYSTEM>

The behaviour of the V$ views in the 11.2.0.2 environment doesn't seem correct. (Note : V$SQLSTATS doesn't differentiate by CHILD_NUMBER as does V$SQL).
.
.



Categories: DBA Blogs

fsfreeze in Linux

Pythian Group - Thu, 2015-05-14 10:17

The fsfreeze command, is used to suspend and resume access to a file system. This allows consistent snapshots to be taken of the filesystem. fsfreeze supports Ext3/4, ReiserFS, JFS and XFS.

A filesystem can be frozen using following command:

# /sbin/fsfreeze -f /data

Now if you are writing to this filesystem, the process/command will be stuck. For example, following command will be stuck in D (UNINTERUPTEBLE_SLEEP) state:

# echo “testing” > /data/file

Only after the filesystem is unfreezed using the following command, can it continue:

# /sbin/fsfreeze -u /data

As per the fsfreeze main page, “fsfreeze is unnecessary for device-mapper devices. The device-mapper (and LVM) automatically freezes filesystem on the device when a snapshot creation is requested.”

fsfreeze is provided by the util-linux package in RHEL systems. Along with userspace support, fsfreeze also requires kernel support.

For example, in the following case, fsfreeze was used in the ext4 filesystem of an AWS CentOS node:

# fsfreeze -f /mysql
fsfreeze: /mysql: freeze failed: Operation not supported

From strace we found that ioctl is returning EOPNOTSUPP:

fstat(3, {st_dev=makedev(253, 0), st_ino=2, st_mode=S_IFDIR|0755,
st_nlink=4, st_uid=3076, st_gid=1119, st_blksize=4096, st_blocks=8,
st_size=4096, st_atime=2014/05/20-10:58:56,
st_mtime=2014/11/17-01:39:36, st_ctime=2014/11/17-01:39:36}) = 0
ioctl(3, 0xc0045877, 0) = -1 EOPNOTSUPP (Operation not
supported)

From latest upstream kernel source:

static int ioctl_fsfreeze(struct file *filp)
{
struct super_block *sb = file_inode(filp)->i_sb;if (!capable(CAP_SYS_ADMIN))
return -EPERM;

/* If filesystem doesn’t support freeze feature, return. */
if (sb->s_op->freeze_fs == NULL)
return -EOPNOTSUPP;

/* Freeze */
return freeze_super(sb);
}

EOPNOTSUPP is returned when a filesystem does not support the feature.

On testing to freeze ext4 in CentOs with AWS community AMI, fsfreeze worked fine.

This means that the issue was specific to the kernel of the system. It was found that AMI used to build the system was having a customized kernel without fsfreeze support.

Categories: DBA Blogs