Feed aggregator

Note to self for blocking locks

Michael Dinh - Sat, 2016-08-06 05:20
Session 1 starts UPDATE and nothing else.
++++++++++
Session 1:
02:53:39 ARROW:(MDINH@leo):PRIMARY> update t set object_id=100;

1 row updated.

02:53:45 ARROW:(MDINH@leo):PRIMARY>
++++++++++
Session 2:
02:53:50 ARROW:(SYSTEM@leo):PRIMARY> update mdinh.t set object_id=2;
++++++++++
Session 3:
02:53:58 ARROW:(DEMO@leo):PRIMARY> update mdinh.t set object_id=200;
++++++++++
Monitor blocking locks
$ sysdba @b.sql

SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 6 02:55:03 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options


STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   MDINH           22,1947                        57 SQL*Net message from INACTIVE    1 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           27,487       0sst1nnb8vw49     45 enq: TX - row lock c ACTIVE      1 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *SYSTEM         35,795       dh603ks5ggumy     45 enq: TX - row lock c ACTIVE      1 arrow.localdomain    oracle     sqlplus@arrow.l


STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   MDINH           6827jhnufmcfx update t set object_id=100
WAITING    *DEMO           0sst1nnb8vw49 update mdinh.t set object_id=200
WAITING    *SYSTEM         dh603ks5ggumy update mdinh.t set object_id=2

ARROW:(SYS@leo):PRIMARY> select sql_id, prev_sql_id from v$session where sid=22;

SQL_ID        PREV_SQL_ID
------------- -------------
              6827jhnufmcfx

ARROW:(SYS@leo):PRIMARY>
Blocking session is INACTIVE and the UPDATE SQL is available.

.

Session 1 execute SELECT following UPDATE.
++++++++++
Session 1:
02:56:16 ARROW:(MDINH@leo):PRIMARY> select sysdate from dual;

SYSDATE
-------------------
2016-08-06 02:56:23

02:56:23 ARROW:(MDINH@leo):PRIMARY>
++++++++++
Monitor blocking locks
ARROW:(SYS@leo):PRIMARY> @b

STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   MDINH           22,1947      7h35uxf5uhmm1     61 SQL*Net message from INACTIVE    0 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           27,487       0sst1nnb8vw49     45 enq: TX - row lock c ACTIVE      3 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *SYSTEM         35,795       dh603ks5ggumy     45 enq: TX - row lock c ACTIVE      3 arrow.localdomain    oracle     sqlplus@arrow.l


STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   MDINH           7h35uxf5uhmm1 select sysdate from dual
WAITING    *DEMO           0sst1nnb8vw49 update mdinh.t set object_id=200
WAITING    *SYSTEM         dh603ks5ggumy update mdinh.t set object_id=2

ARROW:(SYS@leo):PRIMARY> select sql_id, prev_sql_id from v$session where sid=22;

SQL_ID        PREV_SQL_ID
------------- -------------
7h35uxf5uhmm1 7h35uxf5uhmm1

ARROW:(SYS@leo):PRIMARY>
Note: SQL_ID=PREV_SQL_ID and SQL is not the blocking SQL.
ARROW:(SYS@leo):PRIMARY> !cat b.sql
col username for a15 trunc
col state for a10 trunc
col osuser for a10 trunc
col program for a15 trunc
col sid_serial for a12 trunc
col event for a20 trunc
col machine for a20 trunc
col sid for 999999
col wait_min for 999
col sql_text for a100 trunc
col seq# for 99999
col min for 999
col sql_text for a80 trunc
set lines 200 pages 10000 tab off trimspool off
SELECT
decode(level,1,'BLOCKING','WAITING') state,
LPAD('*',(level-1)*1,' ') || NVL(s.username,'(oracle)') AS username,
s.sid||','||s.serial# sid_serial,
sql_id,
s.seq#,
s.event,
s.status,
round(s.last_call_et/60) min,
s.machine,
s.osuser,
s.program
FROM   v$session s
WHERE  level > 1
OR     EXISTS (SELECT null FROM v$session WHERE blocking_session = s.sid)
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL
order by 1,2
;
---
with s as (
SELECT
decode(level,1,'BLOCKING','WAITING') state,
LPAD('*',(level-1)*1,' ') || NVL(s.username,'(oracle)') AS username,
decode(status,'INACTIVE',prev_sql_id,sql_id) sql_id
FROM   v$session s
WHERE  level > 1
OR     EXISTS (SELECT null FROM v$session WHERE blocking_session = s.sid)
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL
)
SELECT s.state, s.username, s.sql_id, sql_text
FROM v$sqlarea a, s
WHERE a.sql_id=s.sql_id
order by 1,2
;

ARROW:(SYS@leo):PRIMARY>
Nice Script from Jeffrey M. Hunter

http://www.idevelopment.info/data/Oracle/DBA_scripts/Locks/locks_blocking.sql

$ sysdba @locks_blocking.sql

SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 6 03:12:55 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options


+------------------------------------------------------------------------+
| Report   : Blocking Locks                                              |
| Instance : leo                                                         |
+------------------------------------------------------------------------+


+------------------------------------------------------------------------+
| BLOCKING LOCKS (Summary)                                               |
+------------------------------------------------------------------------+

Number of blocking lock incidents: 2

Incident 1
---------------------------------------------------------------------------------------------------------
                        WAITING                                  BLOCKING
                        ---------------------------------------- ----------------------------------------
Instance Name         : leo                                      leo
Oracle SID            : 27                                       22
Serial#               : 487                                      1947
Oracle User           : DEMO                                     MDINH
O/S User              : oracle                                   oracle
Machine               : arrow.localdomain                        arrow.localdomain
O/S PID               : 20525                                    20521
Terminal              : pts/3                                    pts/2
Lock Time             : 19 minutes                               19 minutes
Status                : ACTIVE                                   INACTIVE
Program               : sqlplus@arrow.localdomain (TNS V1-V3)    sqlplus@arrow.localdomain (TNS V1-V3)
Waiter Lock Type      : Transaction
Waiter Mode Request   : Exclusive
Waiting SQL           : update mdinh.t set object_id=200

Incident 2
---------------------------------------------------------------------------------------------------------
                        WAITING                                  BLOCKING
                        ---------------------------------------- ----------------------------------------
Instance Name         : leo                                      leo
Oracle SID            : 35                                       22
Serial#               : 795                                      1947
Oracle User           : SYSTEM                                   MDINH
O/S User              : oracle                                   oracle
Machine               : arrow.localdomain                        arrow.localdomain
O/S PID               : 20706                                    20521
Terminal              : pts/7                                    pts/2
Lock Time             : 19 minutes                               19 minutes
Status                : ACTIVE                                   INACTIVE
Program               : sqlplus@arrow.localdomain (TNS V1-V3)    sqlplus@arrow.localdomain (TNS V1-V3)
Waiter Lock Type      : Transaction
Waiter Mode Request   : Exclusive
Waiting SQL           : update mdinh.t set object_id=2


+------------------------------------------------------------------------+
| LOCKED OBJECTS                                                         |
+------------------------------------------------------------------------+

Instance  SID / Serial#   Status    Locking Oracle User  Object Owner    Object Name               Object Type     Locked Mode
--------- --------------- --------- -------------------- --------------- ------------------------- --------------- -------------------------
leo       22 / 1947       INACTIVE  MDINH                MDINH           T                         TABLE           Row-Exclusive (SX)
leo       27 / 487        ACTIVE    DEMO                 MDINH           T                         TABLE           Row-Exclusive (SX)
leo       35 / 795        ACTIVE    SYSTEM               MDINH           T                         TABLE           Row-Exclusive (SX)

ARROW:(SYS@leo):PRIMARY>

Trying VirtualBox

Bobby Durrett's DBA Blog - Fri, 2016-08-05 23:49

I have been using  VMware Player to build test virtual machines on my laptop with an external drive for some time now. I used to use the free VMware Server. My test VMs weren’t fast because of the slow disk drive but they were good enough to run small Linux VMs to evaluate software. I also had one VM to do some C hacking of the game Nethack for fun. I got a lot of good use out of these free VMware products and VMware is a great company so I’m not knocking them. But, this week I accidentally wiped out all the VMs that I had on my external drive so I tried to rebuild one so I at least have one to boot up if I need a test Linux VM. I spend several hours trying to get the Oracle Linux 6.8 VM that I created to work with a screen resolution that matched my monitor. I have a laptop with a smaller 14 inch 1366 x 768 resolution built-in monitor and a nice new 27 inch 1920 x 1080 resolution external monitor. VMware player wouldn’t let me set the resolution to more than 1366 x 768 no matter what I did.

Finally after a lot of googling and trying all kinds of X Windows and VMware settings I finally gave up and decided to try VirtualBox. I was able to quickly install it and get my OEL 6.8 VM up with a larger resolution with no problem. It still didn’t give me 1920 x 1080 for some reason but had a variety of large resolutions to choose from.

After getting my Linux 6.8 machine to work acceptably I remembered that I was not able to get Linux 7 to run on VMware either. I had wanted to build a VM with the latest Linux but couldn’t get it to install. So, I downloaded the 7.2 iso and voilà it installed like a charm in VirtualBox. Plus I was able to set the resolution to exactly 1920 x 1080 and run in full screen mode taking up my entire 27 inch monitor.  Very nice!

I have not yet tried it, but VirtualBox seems to come with the ability to take a snapshot of a VM and to clone a VM. To get these features on VMware I’m pretty sure you need to buy the $249 VMware Workstation. I have a feeling that Workstation is a good product but I think it makes sense to try VirtualBox and see if the features that it comes with meet all my needs.

I installed VirtualBox at the end of the work day today so I haven’t had a lot of time to find its weaknesses and limitations. But so far it seems to have addressed several weaknesses that I found in VMware Player so it may have a lot of value to me. I think it is definitely worth trying out before moving on to the commercial version of VMware.

Bobby

P.S. Just tried the snapshot and clone features. Very neat. Also I forgot another nuisance with VMware Player. It always took a long time to shut down a machine. I think it was saving the current state. I didn’t really care about saving the state or whatever it was doing. Usually I just wanted to bring something up real quick and shut it down fast. This works like a charm on VirtualBox. It shuts down a VM in seconds. So far so good with VirtualBox.

P.P.S This morning I easily got both my Linux 6.8 and 7.2 VM’s to run with a nice screen size that takes up my entire 27 inch monitor but leaves room so I can see the menu at the top of the VM window and my Windows 7 status bar below the VM’s console window. Very nice. I was up late last night tossing and turning in bed thinking about all that I could do with the snapshot and linked clone features. 🙂

Categories: DBA Blogs

New OA Framework 12.2.5 Update 5 Now Available

Steven Chan - Fri, 2016-08-05 14:10

Web-based content in Oracle E-Business Suite 12 runs on the Oracle Application Framework (OAF or "OA Framework") user interface libraries and infrastructure.   Since the release of Oracle E-Business Suite 12.2 in 2013, we have released several cumulative updates to Oracle Application Framework to fix performance, security, and stability issues. 

These updates are provided in cumulative Release Update Packs, and cumulative Bundle Patches that can be applied on top of the Release Update Pack. "Cumulative" means that the latest RUP or Bundle Patch contains everything released earlier.

The latest OAF update for EBS 12.2.5 is now available:

EBS 12.2.5 Bundle Patch 5 download patch

Where is the documentation for this update?

Instructions for installing this OAF Release Update Pack are here:

Who should apply this patch?

All EBS 12.2.5 users should apply this patch.  Future OAF patches for EBS 12.2.5 will require this patch as a prerequisite. 

What's new in this update?

This bundle patch is cumulative: it includes all fixes released in previous EBS 12.2.5 bundle patches.

This latest bundle patch includes new fixes for the following critical issues:

    • The getIndexedChild API throws an exception when all the subtabs are hidden in the subtab layout.
    • Adding new rows to the scroll mode table with more than 30 records leads to UI distortion and table becomes empty.    
    • The value in Table cells with style OraTableCellNumber and OraTableTotalNumber is left aligned.
    • The inline error message at the table cell is not wrapped in 12.2 code lines and consequently, the table columns are expanded to the length of error messages if the error messages are long.
    • Mechanism to render table header tag from Row wise rendering(Row Bean) is not available in OAF
    This Bundle Patch requires the R12.ATG_PF.C.Delta.5 Release Update Pack as a mandatory prerequisite.

    Related Articles


    Categories: APPS Blogs

    Journalismus, den die Welt braucht. Teil 1 | Pearls of journalism. Part 1

    Dietrich Schroff - Fri, 2016-08-05 14:07
    Today i browsed the magazine IT-BUSINESS  and read some really annoying articles. It was just words without building meaningful setences. It was so stunned, that i decided: I have to do something...


    The  following idea came into my mind:
    What about translating the part of the article, which i did not understand via Google Translate? Perhaps in english it sounds more meaningful (and more than 20% of the words are just technical terms in english...)






    And after Google Translate:

    Matching promotional concepts

    In replacement markets optimization of promotional activities is one of the easiest ways to improve the profit. A price reduction associated with the direct delivery into the channel leading to a significant decline in sales in the distribution. A channel conflict could follow: The inventory turnover in the distribution decreases the Profitabliltät in the width of the channels decreases. It is a further price action required to stimulate demand again. It is more settled, for all concerned but worsens the profit situation.
    Wow. It is really easier to understand this...


    Steve Miranda’s Big Bet: One Hundred Percent SaaS

    Linda Fishman Hoyle - Fri, 2016-08-05 12:41

    EVP Steve Miranda (pictured left) is certain of one thing: one hundred percent of businesses will move their applications to the cloud. It’s just a matter of time, Miranda says, because as Oracle makes better software faster, "this is the model that all our customers will want."

    Technology writer Donovan Jackson published "One hundred percent SaaS?" after Steve's recent visit to Australia.

    Basicfile LOBs

    Jonathan Lewis - Fri, 2016-08-05 10:12

    I got a call to a look at a performance problem involving LOBs a little while ago. The problem was with an overnight batch that had about 40 sessions inserting small LOBs (12KB to 22KB) concurrently, for a total of anything between 100,000 and 1,000,000 LOBs per night. You can appreciate that this would eventually become a very large LOB segment – so before the batch started all LOBs older than one month were deleted.

    The LOB column had the following (camouflaged) declaration:

     LOB (little_lob) STORE AS BASICFILE (
            TABLESPACE lob_ts 
            ENABLE STORAGE IN ROW 
            RETENTION
            NOCACHE 
            LOGGING
    )
    

    The database was 11gR2, the tablespace was defined with ASSM with uniform 1MB extents and a blocksize of 8KB (so the LOBs were all 2 or 3 chunks) and the undo retention time was 900 seconds. The effect of the “enable storage in row” is that the LOBINDEX didn’t have to hold any details of current LOB chunks (for in-row, the first 12 chunks are listed in the LOB Locator in the base table).

    So, examining an AWR report covering the critical interval, reviewing the captured ASH data, and checking the database, a few questions came to mind:

    • With 200 GB of current LOB data in the segment, why was the segment roughly 800GB ?
    • With no need for current LOBs to be indexed, how had the LOB Index reached 500,000 blocks in size ?
    • There had been 500,000 inserts that night – so why had Oracle done 6 Billion (cached) buffer gets on the (nocache) LOB segment ?
    • Given that the LOB Segment had not changed size during the night, why had there been millions of HW enqueue wait on the inserts ?

    Knowing the stuff that I did know about basicfile LOBs it seemed likely that the most significant problem was that the segment hadn’t been created with multiple freepools which, according to the very sparse (and not entirely self-consistent) documentation, exist to allow improved concurrency. So I thought I’d search the Internet for any useful information about freepools, how they worked, what impact they might have on this problem, why their absence might produce the symptoms I’d seen, and what the best course of action would be to address the problem.

    Of course the “correct” solution according to MoS would be to convert from basicfile to securefile – with a strange insistence on using online redefinition, but no explanation of why a simple CTAS or alter table move is undesirable or dangerous. Unfortunately there are a couple of notes on MoS describing performance issues with “high” levels of concurrent inserts that need to be addressed by setting hidden parameters so I’m not (yet) keen on rebuilding 700GB of a production system to produce a change that might still not work quickly enough; especially since I couldn’t find anything on MoS that could quantify the time needed to do the conversion.

    To my surprise I couldn’t find a single useful piece of information about the problem. The only articles I could find seemed to be little bits of cut-n-paste from the Oracle manual pages about using multiple freepools, and the best of those actually demonstrated rebuilding or changing the freepools settings on a LOB of a few megabytes. The most significant MoS note did say that the process “could be slow” and would lock the table. But surely someone, somewhere, must have tried it on a big system and had some idea of “how slow”.

    In the end I had to start building some simple models and doing a few experiments to find out what happens and where the time goes and what causes the strange results and – most importantly – how freepools might help. Fortunately, following a call to the Oak Table for any ideas or links to useful documents, Kurt van Meerbeeck directed me to the original Oracle patents which were enormously helpful in showing why freepools could help and why, in the wrong circumstances, you could still end up with a (slightly smaller) disaster on your hands.

     

    To be continued …

     

    Until I find time to write up the mechanisms I’ve discovered I’m happen to respond to any quick questions about this problem and I’m more than happy to hear about experiences others have had either rebuilding freepools, changing the number of freepools, or migrating from basicfile to securefile LOBs.

     


    List usernames instead of uids with the ps command for long usernames

    Yann Neuhaus - Fri, 2016-08-05 08:27

    Have your ever faced such a situation. You have usernames in your /etc/passwd file with more than 8 characters. This is no problem for Linux at all, usernames may be up to 32 characters long, only your ps output might look a little scrambled.

    It shows you the uid instead of the username like in the following example:

    $ id
    
    uid=20001(longuser01) gid=10002(oinstall) groups=10002(oinstall)
    
    $ sleep 1000000 &
    
    $ ps -ef | grep sleep | grep -v grep
    
    20001    14069 11739  0 14:11 pts/0    00:00:00 sleep 1000000

     

    But you want to see the username instead of the uid. The workaround is

    • Don’t use more than eight characters for your usernames  :-)
    • Or …. format your ps output the right way

    You could use the following alias to get the job done.

    $ alias psx='export PS_FORMAT="user:12,pid,%cpu,%mem,vsz,rss,tty,stat,start,time,command"; ps ax'
    
    $ psx | grep sleep | grep -v grep
    
    longuser01 14069  0.0  58940 520 pts/0 S 14:11:50 sleep 1000000

     

    Now it looks better.

    Cheers, William

     

    Cet article List usernames instead of uids with the ps command for long usernames est apparu en premier sur Blog dbi services.

    The new Identity clause doesn't increment the id

    Tom Kyte - Fri, 2016-08-05 08:26
    To reproduce the problem : <code>CREATE TABLE MY_TABLE ( ID NUMBER(19, 0) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name NVARCHAR2(255) NOT NULL ); INSERT INTO MY_TABLE (ID, name) VALUES (1, 'test1'); INSERT INTO MY_TABLE (name) VAL...
    Categories: DBA Blogs

    Use select query to concatinate, and remove spaces from two columns

    Tom Kyte - Fri, 2016-08-05 08:26
    Compare table with spaces in between data, with table without spaces Example:- Table A has three fields: ID, Cd_1, and Cd_2 Fields: Cd_1 or Cd_2 can have spaces between the codes. Below example has space between X and Y in Cd_1 ID Cd...
    Categories: DBA Blogs

    I've done the test and got the wrong answer but I don't know why

    Tom Kyte - Fri, 2016-08-05 08:26
    It's about the code, I don't know why it displays "before 5 after 5". CREATE OR REPLACE PROCEDURE plch_test AS BEGIN NULL; -- 5 lines of code END; / CREATE OR REPLACE TRIGGER plch_bca BEFORE CREATE OR ALTER ON SCHEMA DECLARE l_li...
    Categories: DBA Blogs

    Data Redaction and Data Pump in #Oracle 12c

    The Oracle Instructor - Fri, 2016-08-05 08:07

    What happens upon Data Pump Export if tables are being exported that have a Data Redaction Policy? I got that question several times in class, which is why I put the answer here , so I can refer to it subsequently.  Might also be of interest to the Oracle Community:-)

    SYS@orcl > BEGIN
    DBMS_REDACT.ADD_POLICY
    (object_schema => 'SCOTT',
    object_name => 'EMP',
    policy_name => 'EMPSAL_POLICY',
    column_name => 'SAL',
    function_type => DBMS_REDACT.FULL,
    expression => '1=1');
    END;
    /  
    
    PL/SQL procedure successfully completed.
    
    SYS@orcl > connect scott/tiger
    Connected.
    SCOTT@orcl > select ename,sal from emp;
    
    ENAME             SAL
    ---------- ----------
    SMITH               0
    ALLEN               0
    WARD                0
    JONES               0
    MARTIN              0
    BLAKE               0
    CLARK               0
    SCOTT               0
    KING                0
    TURNER              0
    ADAMS               0
    JAMES               0
    FORD                0
    MILLER              0
    
    14 rows selected.
    

    Scott doesn’t see the values of the SAL column because of the Data Redaction Policy. SYS is not subject to that policy, because SYS has the privilege EXEMPT REDACTION POLICY:

    SYS@orcl > select ename,sal from scott.emp;
    
    ENAME             SAL
    ---------- ----------
    SMITH             800
    ALLEN            1600
    WARD             1250
    JONES            2975
    MARTIN           1250
    BLAKE            2850
    CLARK            2450
    SCOTT            9000
    KING             5000
    TURNER           1500
    ADAMS            1100
    JAMES             950
    FORD             9000
    MILLER           1300
    
    14 rows selected.
    

    If Data Pump Export is done as a user who owns that privilege, the table is just exported with all its content, regardless of the policy:

    SYS@orcl >  create directory dpdir as '/home/oracle/';
    [oracle@uhesse ~]$ expdp tables=scott.emp directory=DPDIR
    
    Export: Release 12.1.0.2.0 - Production on Fri Aug 5 08:56:51 2016
    
    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
    
    Username: / as sysdba
    
    Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
    and Unified Auditing options
    Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA tables=scott.emp directory=DPDIR
    Estimate in progress using BLOCKS method...
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 64 KB
    Processing object type TABLE_EXPORT/TABLE/TABLE
    Processing object type TABLE_EXPORT/TABLE/RADM_POLICY
    Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
    Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
    Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
    . . exported "SCOTT"."EMP"                               8.781 KB      14 rows
    Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
      /home/oracle/expdat.dmp
    Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 5 08:57:10 2016 elapsed 0 00:00:15
    

    If Scott tries to export the table, that raises an error message:

    SYS@orcl > grant read,write on directory dpdir to scott;
    
    Grant succeeded.
    
    [oracle@uhesse ~]$ expdp scott/tiger tables=scott.emp directory=DPDIR
    
    Export: Release 12.1.0.2.0 - Production on Fri Aug 5 08:55:10 2016
    
    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
    
    Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
    and Unified Auditing options
    Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** tables=scott.emp directory=DPDIR
    Estimate in progress using BLOCKS method...
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 64 KB
    Processing object type TABLE_EXPORT/TABLE/TABLE
    Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
    Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
    Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
    ORA-31693: Table data object "SCOTT"."EMP" failed to load/unload and is being skipped due to error:
    ORA-28081: Insufficient privileges - the command references a redacted object.
    Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
      /home/oracle/expdat.dmp
    Job "SCOTT"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Fri Aug 5 08:55:28 2016 elapsed 0 00:00:16
    

    Taken from the 12c New Features class that I delivered this week in Hinckley. As always: Don’t believe it, test it:-)


    Tagged: 12c New Features
    Categories: DBA Blogs

    How to do a Filesystem Resize (ext3/ext4) on Redhat running on VMware

    Yann Neuhaus - Fri, 2016-08-05 07:43

    A filesystem resize can be done in several ways, online, offline, with LVM2 or without LVM2.  However, this blog will describe how to do an online resize of ext3/ext4 filesystems where a virtual disk (vmdk) is online added to a VMware Redhat guest OS.

    So let’s start with the online filesystem resize of ext3/4 filesystems on the Redhat guest OS.  A new virutal disk (preferably an eagerd zero thick on VM running Oracle) was added as a pre requirement. Adding a new virtual disk is an online operation and no downtime is required to do it.

    The whole procedure in this document is described by using the command line only. There is also a graphical user interface `system-config-lvm` that can perform the job, but that tool is out of scope in this document.

    Online resize a ext3/4 filesystem

    There are several steps that have to be done. These are in general:

    1. Scanning for new LUN’s
    2. Partition the new LUN’s and partprobe
    3. Create the physical volume
    4. Extend the volume group and the logical volume
    5. Extend the filesystem online
    Rescan for new LUN’s

    Depending on the number of virtual controllers, you have to scan for your new LUN’s on each of these. In case you know on which the disk was added, then of course, you need to scan only the appropriate one.

    Rescan for new LUN’s on the first SCSI Controller (LSI Logic Parallel)
    # echo "- - -"  > /sys/class/scsi_host/host0/scan*
    Rescan for new LUN’s on the second SCSI Controller (Paravirtualized)
    # echo "- - -"  > /sys/class/scsi_host/host1/scan*
    Create a Primary Partion on the new devices
    # fdisk /dev/sdx??
    
    # fdisk /dev/sdy??
    Partprobe the new devices

    Partprobe is a program that informs the operating system kernel of partition table changes, by requesting that the operating system re-read the partition table.

    # partprobe /dev/sdx??
    
    # partprobe /dev/sdy??
    Create the Pysical Volumes
    # pvcreate /dev/sdx??
    
    Physical volume "/dev/sdx??" successfully created
    # pvcreate /dev/sdy??
    
    Physical volume "/dev/sdy??" successfully created
    Extend the Volume Group
    # vgextend VGOracle /dev/sdx??
    
    Volume group "VGOracle" successfully extended
    # vgextend VGOracle /dev/sdy??
    
    Volume group "VGOracle" successfully extended
    Extend the Logical Volume
    # lvextend -L 72G /dev/VGOracle/LVOracleu??
    
    Extending logical volume LVOracleu?? to 72.00 GB
    
    Logical volume LVOracleu01 successfully resized
    Online Resize the ext3/ext4 Filesystem

    After the logical volume is resized successfully, you can resize, in fact any filesystem that is online re-sizable. The following are examples for the ext3/ext4 filesystems. The syntax for ext3 and ext4 differ only slightly. For ext3 you use `resize2fs` even if its ext3 and not ext2, and in case of ext4 you use `resize4fs` were the command name is more logically.

    ext3
    # resize2fs /dev/VGOracle/LVOracleu??
    ext4
    # resize4fs /dev/VGOracle/LVOracleu??

     

    That’s it. Now have fun with the bigger filesystem.

    Cheers,

    William

     

     

    Cet article How to do a Filesystem Resize (ext3/ext4) on Redhat running on VMware est apparu en premier sur Blog dbi services.

    Orchestration vs CloudFormation

    Pat Shuff - Fri, 2016-08-05 02:07
    Today we are going to do a compare and contrast with Oracle Orchestration and Amazon CloudFormation. The two have the same objectives and can perform the same operations when provisioning an instance. The key difference is the way that they both operate and define the elements needed to create an instance. In the past few days we have gone through and looked that the three files needed to provision a WordPress blog. Information on Oracle Orchestration can be found in the documentation section and tutorial section. Information on Amazon CloudFormation can be found at the home page and tutorial section. We will dive into the WordPress example and look at the json file that is used to provision the service.

    The key components to the json file are

    {
      "AWSTemplateFormatVersion" : "2010-09-09",
    
      "Description" : " ... ",
    
      "Parameters" : { ... },
    
      "Mappings" : { ...  },
    
      "Resources" : { ...  },
    
      "Outputs" : { ...   }
    }
    

    We can create a simple storage element in S3 with the following file

    {
        "Resources" : {
            "HelloBucket" : {
                "Type" : "AWS::S3::Bucket"
            }
        }
    }
    

    Note that the only thing that we truly need is the definition of a resource. The resource has a label of "HelloBucket" and the resource consists of an element defined as "AWS::S3::Bucket". Note that the Type is very specific to AWS. We could not take this generic definition and port it to any other platform. We don't know how much storage to allocate because S3 is typically an open ended definition. This is radically different from out storage creation from a few days ago where we had to define the storage_pool, size of the disk, and properties of the instance like is it bootable, what image to boot from, and what account it is associated with. The CloudFormation interface assumes account information because it is run from a web based or command line based interface that has your account information embedded into the user interface.

    We could get a little more complex and define an instance. With this instance we reference an AMI that predefines the content and operating system. We also define the security ports and connection keys for this instance in the definition.

    {
      "Resources" : {
        "Ec2Instance" : {
          "Type" : "AWS::EC2::Instance",
          "Properties" : {
            "SecurityGroups" : [ { "Ref" : "InstanceSecurityGroup" }, "MyExistingSecurityGroup" ],
            "KeyName" : "mykey",
            "ImageId" : "ami-7a11e213"
          }
        },
    
        "InstanceSecurityGroup" : {
          "Type" : "AWS::EC2::SecurityGroup",
          "Properties" : {
            "GroupDescription" : "Enable SSH access via port 22",
            "SecurityGroupIngress" : [ {
              "IpProtocol" : "tcp",
              "FromPort" : "22",
              "ToPort" : "22",
              "CidrIp" : "0.0.0.0/0"
            } ]
          }
        }
      }
    }
    
    In this example we are going to provision an EC2 instance from ami-7a11e213. We will be using the security credentials labeled MyExistingSecurityGroup and open up port 22 for ssh access. We don't know what version the operating system unless we look up the characteristics of the ami. This is different from the Oracle Orchestration where we define the storage element and what operating system to boot from. They both define the security groups but do it a little differently but have the same effect.

    We can also define some of the characteristics into the application. For CloudFormation we can configure WordPress with the following parameters

     "Parameters": {
        "KeyName": {
          "Description" : "Name of an existing EC2 KeyPair to enable SSH access into the WordPress web server",
          "Type": "AWS::EC2::KeyPair::KeyName"
        },
        "WordPressUser": {
          "Default": "admin",
          "NoEcho": "true",
          "Description" : "The WordPress database admin account user name",
          "Type": "String",
          "MinLength": "1",
          "MaxLength": "16",
          "AllowedPattern" : "[a-zA-Z][a-zA-Z0-9]*"
        },
        "WebServerPort": {
          "Default": "8888",
          "Description" : "TCP/IP port for the WordPress web server",
          "Type": "Number",
          "MinValue": "1",
          "MaxValue": "65535"
        }
      },
    
    Note that we define these parameters based on the application and pass into the operating system as it is booted. Oracle Orchestration takes a different tactic when it comes to adding parameters to a configuration. Rather than having parameters defined for each application, customizations like this are done with a post install script that is executed at boot time. These configurations can be done from a snapshot or from a post install script based on how you like to initialize systems. This functionality started with Enterprise Manager and the scripts that you use for in house systems can be ported to the cloud without changing or updating.

    In summary, the Amazon CloudFormation and Oracle Orchestration are very similar. The components that you use to define a system are done similarly. Amazon makes assumptions that you are running on AWS and gives you short cuts and shorthand that allows you to create predefined components quickly and easily. Unfortunately this configuration does not translate to any other cloud provider or an in house solution. Oracle Orchestration is a little more nuts and bolts but is designed to help you create everything from scratch and build upon the foundation for system definitions. CloudFormation has a graphical user interface that generates json files for you based on dragging and dropping components into a design pallet. Oracle takes a slightly different approach and uses the Oracle Marketplace to automatically generate the json files. There is not a graphical design tool that allows you to drag and drop components but there are tools to take a configuration that is in your data center and generate the parameter list that can be used to generate the json files for Orchestration. We are not saying that one is better than the other in this blog. We are mainly pointing out that they two tools and utilities have different target audiences and functionality. Unfortunately, you can't take one configuration and easily map it into the other configuration. Hopefully someone at some point will take these files and create a translator.

    Oracle 12c: Indexing JSON in the Database Part II (Find A Little Wood)

    Richard Foote - Fri, 2016-08-05 02:00
    In Part I, we looked at how you can now store JSON documents within the Oracle 12c Database. For efficient accesses to JSON documents stored in the Oracle database, we can either create a function-based index based on the JSON_VALUE function or on JSON .dot notation. These indexes are useful for indexing specific JSON attributes, […]
    Categories: DBA Blogs

    Links for 2016-08-04 [del.icio.us]

    Categories: DBA Blogs

    No Fun with EM 12c

    Michael Dinh - Thu, 2016-08-04 17:21

    Confession: I have not been using OEM for decades since there was never a real need for it and it’s starting to take its toll on me.

    Throughout the day, I keep getting paged from OEM – Message=Number of failed login attempts exceeds threshold value.

    The information provided is utterly useless, e.g. what is the threshold value and what’s the error code.

    What would be useful is to provide the SQL used for the check for ease of troubleshooting.

    Then I found Finding the source of failed login attempts. (Doc ID 352389.1)

    SQL> @pr "select username,os_username,userhost,client_id,trunc(timestamp),count(*) failed_logins from dba_audit_trail where returncode=1017 and timestamp>trunc(sysdate) group by username,os_username,userhost, client_id,trunc(timestamp) order by 5";
    USERNAME              : JANE
    OS_USERNAME           : oracle
    FAILED_LOGINS         : 1
    

    That wasn’t it.

    SQL> @pr "select username,os_username,RETURNCODE,userhost,trunc(timestamp),count(*) failed_logins from dba_audit_trail where returncode<>0 and timestamp>trunc(sysdate) group by username,os_username,RETURNCODE,userhost,trunc(timestamp) order by 5";
    USERNAME              : 
    OS_USERNAME           : tomcat
    RETURNCODE            : 28000
    FAILED_LOGINS         : 1065
    -------------------------
    USERNAME              : JANE
    OS_USERNAME           : oracle
    RETURNCODE            : 1017
    FAILED_LOGINS         : 1
    
    $ oerr ora 28000
    28000, 00000, "the account is locked"
    // *Cause:   The user has entered wrong password consequently for maximum
    //           number of times specified by the user's profile parameter
    //           FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account
    // *Action:  Wait for PASSWORD_LOCK_TIME or contact DBA
    
    $ oerr ora 1017
    01017, 00000, "invalid username/password; logon denied"
    // *Cause:
    // *Action:
    

    1065 failed logins and no one even knows about this?

    Lesson learned, there are many types of failed logins.

     


    Installing SQLcl on OEL/RHEL

    Dimitri Gielis - Thu, 2016-08-04 16:55
    In my previous post I talked about how SQLcl came in handy to work with JavaScript against the database.

    The installation of SQLcl is easy... you just download the zip, unpack and run the executable.

    But to be fair, before I got SQLcl running (especially the script part) I encountered a number of issues, so hopefully this post helps you be able to run SQLcl with all features in minutes as it's meant to be :)


    Those were the error messages I received when running sql (script):

    javax.script.ScriptException: sun.org.mozilla.javascript.EvaluatorException: Java class "java.util.ArrayList" has no public instance field or method named "0".

    javax.script.ScriptException: sun.org.mozilla.javascript.EcmaError: ReferenceError: "Java" is not defined. (#1) in at line number 1

    The solution for me was to upgrade my Java version to Java 8.

    Here're the steps on my OEL/RHEL system to upgrade Java:

    $ cd /opt

    $ wget --no-cookies --no-check-certificate --header "Cookie: gpw_e24=http%3A%2F%2Fwww.oracle.com%2F; oraclelicense=accept-securebackup-cookie" "http://download.oracle.com/otn-pub/java/jdk/8u102-b14/jdk-8u102-linux-x64.tar.gz"

    $ tar xzf jdk-8u102-linux-x64.tar.gz 

    cd jdk1.8.0_102/

    alternatives --install /usr/bin/java java /opt/jdk1.8.0_102/bin/java 2
    $ alternatives --config java

    There are 5 programs which provide 'java'.

      Selection    Command
    -----------------------------------------------
       1           /usr/lib/jvm/jre-1.7.0-openjdk.x86_64/bin/java
       2           /usr/lib/jvm/jre-1.6.0-openjdk.x86_64/bin/java
       3           /usr/lib/jvm/jre-1.5.0-gcj/bin/java
    *+ 4           /usr/java/jre1.8.0_101/bin/java
       5           /opt/jdk1.8.0_102/bin/java

    Enter to keep the current selection[+], or type selection number: 5

    $ alternatives --install /usr/bin/jar jar /opt/jdk1.8.0_102/bin/jar 2
    $ alternatives --install /usr/bin/javac javac /opt/jdk1.8.0_102/bin/javac 2
    $ alternatives --set jar /opt/jdk1.8.0_102/bin/jar
    $ alternatives --set javac /opt/jdk1.8.0_102/bin/javac
    $ java -version
    java version "1.8.0_102"
    Java(TM) SE Runtime Environment (build 1.8.0_102-b14)
    Java HotSpot(TM) 64-Bit Server VM (build 25.102-b14, mixed mode)


    $ export JAVA_HOME=/opt/jdk1.8.0_102
    export JRE_HOME=/opt/jdk1.8.0_102/jre
    $ export PATH=$PATH:/opt/jdk1.8.0_102/bin:/opt/jdk1.8.0_102/jre/bin

    Now when running SQLcl everything worked like a charm. Hurray :)

    Categories: Development

    SELECT Query

    Tom Kyte - Thu, 2016-08-04 14:06
    How to select a last value of a column which is not in group by For eg Emp id salary j_dt 1. 100. 1-Jan 1. 200. 1-Feb 1. 130. 1-Mar select last value of amount, sum of amount, avg of amount ...
    Categories: DBA Blogs

    Identify overlapped intervals

    Tom Kyte - Thu, 2016-08-04 14:06
    Hi team, I have a table with intervals of numbers. However, my table have intervals that overlap and a number can be in more than one interval. For example, start_interval | end_interval 1 | 2 2 | 4 3 ...
    Categories: DBA Blogs

    Nullable Property When Copying a Table Using CTAS

    Tom Kyte - Thu, 2016-08-04 14:06
    Hello, In the example below, I'm using a CTAS statement with where 1=2 to copy the structure of a table. Four of the columns that are defined as NOT NULL on the source table retain that property in the new table. However, one column (patient_id) b...
    Categories: DBA Blogs

    Pages

    Subscribe to Oracle FAQ aggregator