Feed aggregator

Update statement with outer join

Tom Kyte - Sat, 2019-05-04 08:06
Hi, From the sql script, I would like to understand the difference in the behavior between executions of statement 8 and statement 11. While statement 8 updates the flag_1 to null, statement 11 updates it to 2. What difference it makes when I u...
Categories: DBA Blogs

Pass parameter to where clause in bulk collect statement

Tom Kyte - Sat, 2019-05-04 08:06
Hi I have a basic procedure which bulk collects the results of a select statement into a table array. I then print out one line to show that it has worked.. Code (SQL): <code>CREATE OR REPLACE PROCEDURE use_var IS TYPE r_tab IS TABLE OF msf010%...
Categories: DBA Blogs

firefox extension do not work anymore....

Dietrich Schroff - Sat, 2019-05-04 02:39
Today some of my extensions stopped to work and a reinstall failed due to "Download failed. Please check your connection.":

There is an article about this issue, which says, that this is due to an expired certificate:
https://www.bleepingcomputer.com/news/software/firefox-addons-being-disabled-due-to-an-expired-certificate/

The workarounds stated there, do not work for my extensions, so i have to wait, that firefox gets a solution (and a new certificate).

For all others with this problem: Do not deinstall your extensions (like i did) - just wait...

Edit: Here the statement from mozilla: https://bugzilla.mozilla.org/show_bug.cgi?id=1548973
On twitter i found this nice comment

Edit: For latest infos read https://blog.mozilla.org/addons/2019/05/04/update-regarding-add-ons-in-firefox/

Working with ArrayDataProviders in JavaScript Functions in Visual Builder

Shay Shmeltzer - Fri, 2019-05-03 18:15

Storing data in ArrayDataProviders (rather than SDP) is useful whenever you want to further modify the data on the client side - for example if you are looking to create updatable tables in your UI. A common follow up question is "how can I do additional processing/updates on all the records I'm storing in the ADP" - this is what this blog is about.

Since the records are now stored on the client side, you can access them through JavaScript. You can, for example, write a page level module function to loop over the set of records and modify them. For example in the video below I'm using this little function to raise the salary of all the employees:

  PageModule.prototype.arrayModifier = function(array){     console.log(array.length + " is what we got")       for (var i = 0; i < array.length  ; i++ ) {         array[i].salary = array[i].salary+2;         console.log("salary after " + array[i].salary);     }     return array;   }

Into this function you'll pass the array of data from the ArrayDataProvider - you can do this in the parameter mapping of the function pointing to the data object like this:

ADP Data as Parameter

Once your function finished its processing of the data, you'll want to update the ArrayDataProvider back to reflect the changes you did. To do this you can use the action called "Fire Data Provider Event". This function has the option to do mutate events (update, insert, delete). You can read the doc about this and other actions parameters here. For the update you simply need to provide the array of updated data like this:

ADP Update Mutation Parameter

That's it. You can see all the pieces of this process working together in the video below:

 

Categories: Development

GRID Out Of Place (OOP) Rollback Disaster

Michael Dinh - Fri, 2019-05-03 11:45

Now I understand the hesitation to use Oracle new features, especially any auto.

It may just be simpler and less stress to perform manual task having control and knowing what is being executed and validated.

GRID Out Of Place (OOP) patching completed successfully for 18.6.0.0.0.

GRID_HOME=/u01/18.3.0.0/grid_2
ORACLE_HOME=/u01/app/oracle/12.1.0.1/db1

Here is an example of inventory after patching.

+ /u01/18.3.0.0/grid_2/OPatch/opatch lspatches
29302264;OCW RELEASE UPDATE 18.6.0.0.0 (29302264)
29301643;ACFS RELEASE UPDATE 18.6.0.0.0 (29301643)
29301631;Database Release Update : 18.6.0.0.190416 (29301631)
28547619;TOMCAT RELEASE UPDATE 18.0.0.0.0 (28547619)
28435192;DBWLM RELEASE UPDATE 18.0.0.0.0 (28435192)
27908644;UPDATE 18.3 DATABASE CLIENT JDK IN ORACLE HOME TO JDK8U171
27923415;OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)

+ /u01/app/oracle/12.1.0.1/db1/OPatch/opatch lspatches
28731800;Database Bundle Patch : 12.1.0.2.190115 (28731800)
28729213;OCW PATCH SET UPDATE 12.1.0.2.190115 (28729213)

Run cluvfy was successful too.

[oracle@racnode-dc1-1 ~]$ cluvfy stage -post crsinst -n racnode-dc1-1,racnode-dc1-2 -verbose

Post-check for cluster services setup was successful.

CVU operation performed:      stage -post crsinst
Date:                         Apr 30, 2019 8:17:49 PM
CVU home:                     /u01/18.3.0.0/grid_2/
User:                         oracle
[oracle@racnode-dc1-1 ~]$

GRID OOP Rollback Patching completed successfully for node1.

[root@racnode-dc1-1 ~]# crsctl check cluster -all
**************************************************************
racnode-dc1-1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
racnode-dc1-2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
[root@racnode-dc1-1 ~]#
[root@racnode-dc1-1 ~]# echo $GRID_HOME
/u01/18.3.0.0/grid_2
[root@racnode-dc1-1 ~]# $GRID_HOME/OPatch/opatchauto rollback -switch-clone -logLevel FINEST

OPatchauto session is initiated at Fri May  3 01:06:47 2019

System initialization log file is /u01/18.3.0.0/grid_2/cfgtoollogs/opatchautodb/systemconfig2019-05-03_01-06-50AM.log.

Session log file is /u01/18.3.0.0/grid_2/cfgtoollogs/opatchauto/opatchauto2019-05-03_01-08-00AM.log
The id for this session is R47N

Update nodelist in the inventory for oracle home /u01/18.3.0.0/grid.
Update nodelist in the inventory is completed for oracle home /u01/18.3.0.0/grid.


Bringing down CRS service on home /u01/18.3.0.0/grid
CRS service brought down successfully on home /u01/18.3.0.0/grid


Starting CRS service on home /u01/18.3.0.0/grid
CRS service started successfully on home /u01/18.3.0.0/grid


Confirm that all resources have been started from home /u01/18.3.0.0/grid.
All resources have been started successfully from home /u01/18.3.0.0/grid.


OPatchAuto successful.

--------------------------------Summary--------------------------------
Out of place patching clone home(s) summary
____________________________________________
Host : racnode-dc1-1
Actual Home : /u01/18.3.0.0/grid_2
Version:18.0.0.0.0
Clone Home Path : /u01/18.3.0.0/grid


Following homes are skipped during patching as patches are not applicable:

/u01/app/oracle/12.1.0.1/db1

OPatchauto session completed at Fri May  3 01:14:25 2019
Time taken to complete the session 7 minutes, 38 seconds

[root@racnode-dc1-1 ~]# crsctl check cluster -all
**************************************************************
racnode-dc1-1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
racnode-dc1-2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

[root@racnode-dc1-1 ~]# /media/patch/findhomes.sh
   PID NAME                 ORACLE_HOME
 10486 asm_pmon_+asm1       /u01/18.3.0.0/grid/
 10833 apx_pmon_+apx1       /u01/18.3.0.0/grid/

[root@racnode-dc1-1 ~]# cat /etc/oratab
#Backup file is  /u01/app/oracle/12.1.0.1/db1/srvm/admin/oratab.bak.racnode-dc1-1 line added by Agent
#+ASM1:/u01/18.3.0.0/grid:N
hawk1:/u01/app/oracle/12.1.0.1/db1:N
hawk:/u01/app/oracle/12.1.0.1/db1:N             # line added by Agent
[root@racnode-dc1-1 ~]#

GRID OOP Rollback Patching completed successfully for node2.

[root@racnode-dc1-2 ~]# crsctl check cluster -all
**************************************************************
racnode-dc1-1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
racnode-dc1-2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
[root@racnode-dc1-2 ~]#
[root@racnode-dc1-2 ~]# echo $GRID_HOME
/u01/18.3.0.0/grid_2
[root@racnode-dc1-2 ~]# $GRID_HOME/OPatch/opatchauto rollback -switch-clone -logLevel FINEST

OPatchauto session is initiated at Fri May  3 01:21:39 2019

System initialization log file is /u01/18.3.0.0/grid_2/cfgtoollogs/opatchautodb/systemconfig2019-05-03_01-21-41AM.log.

Session log file is /u01/18.3.0.0/grid_2/cfgtoollogs/opatchauto/opatchauto2019-05-03_01-22-46AM.log
The id for this session is 9RAT

Update nodelist in the inventory for oracle home /u01/18.3.0.0/grid.
Update nodelist in the inventory is completed for oracle home /u01/18.3.0.0/grid.


Bringing down CRS service on home /u01/18.3.0.0/grid
CRS service brought down successfully on home /u01/18.3.0.0/grid


Starting CRS service on home /u01/18.3.0.0/grid
CRS service started successfully on home /u01/18.3.0.0/grid


Confirm that all resources have been started from home /u01/18.3.0.0/grid.
All resources have been started successfully from home /u01/18.3.0.0/grid.


OPatchAuto successful.

--------------------------------Summary--------------------------------
Out of place patching clone home(s) summary
____________________________________________
Host : racnode-dc1-2
Actual Home : /u01/18.3.0.0/grid_2
Version:18.0.0.0.0
Clone Home Path : /u01/18.3.0.0/grid


Following homes are skipped during patching as patches are not applicable:

/u01/app/oracle/12.1.0.1/db1


OPatchauto session completed at Fri May  3 01:40:51 2019
Time taken to complete the session 19 minutes, 12 seconds
[root@racnode-dc1-2 ~]#

GRID OOP Rollback completed successfully for 18.5.0.0.0.

GRID_HOME=/u01/18.3.0.0/grid
ORACLE_HOME=/u01/app/oracle/12.1.0.1/db1

Here is an example of inventory after rollback.

+ /u01/18.3.0.0/grid/OPatch/opatch lspatches
28864607;ACFS RELEASE UPDATE 18.5.0.0.0 (28864607)
28864593;OCW RELEASE UPDATE 18.5.0.0.0 (28864593)
28822489;Database Release Update : 18.5.0.0.190115 (28822489)
28547619;TOMCAT RELEASE UPDATE 18.0.0.0.0 (28547619)
28435192;DBWLM RELEASE UPDATE 18.0.0.0.0 (28435192)
27908644;UPDATE 18.3 DATABASE CLIENT JDK IN ORACLE HOME TO JDK8U171
27923415;OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)

+ /u01/app/oracle/12.1.0.1/db1/OPatch/opatch lspatches
28731800;Database Bundle Patch : 12.1.0.2.190115 (28731800)
28729213;OCW PATCH SET UPDATE 12.1.0.2.190115 (28729213)

Validation shows database is OFFLINE,

+ crsctl stat res -w '((TARGET != ONLINE) or (STATE != ONLINE)' -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.GHCHKPT.advm
               OFFLINE OFFLINE      racnode-dc1-1            STABLE
               OFFLINE OFFLINE      racnode-dc1-2            STABLE
ora.crs.ghchkpt.acfs
               OFFLINE OFFLINE      racnode-dc1-1            STABLE
               OFFLINE OFFLINE      racnode-dc1-2            STABLE
ora.helper
               OFFLINE OFFLINE      racnode-dc1-1            STABLE
               OFFLINE OFFLINE      racnode-dc1-2            IDLE,STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.hawk.db
      1        ONLINE  OFFLINE                               Instance Shutdown,STABLE
      2        ONLINE  OFFLINE                               Instance Shutdown,STABLE

Start database FAILED.

[oracle@racnode-dc1-2 ~]$ . /media/patch/hawk.env
The Oracle base has been set to /u01/app/oracle
ORACLE_UNQNAME=hawk
ORACLE_SID=hawk2
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/12.1.0.1/db1
Oracle Instance not alive for sid "hawk2"

[oracle@racnode-dc1-2 ~]$ srvctl status database -d $ORACLE_UNQNAME -v
Instance hawk1 is not running on node racnode-dc1-1
Instance hawk2 is not running on node racnode-dc1-2

[oracle@racnode-dc1-2 ~]$ srvctl start database -d $ORACLE_UNQNAME
PRCR-1079 : Failed to start resource ora.hawk.db
CRS-5017: The resource action "ora.hawk.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/hawk/spfilehawk.ora'
ORA-17503: ksfdopn:10 Failed to open file +DATA/hawk/spfilehawk.ora
ORA-27140: attach to post/wait facility failed
ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: Operation not permitted
ORA-27302: failure occurred at: skgpwinit6
ORA-27303: additional information: startup egid = 54321 (oinstall), current egid = 54322 (dba)
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/racnode-dc1-1/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.hawk.db' on 'racnode-dc1-1' failed
CRS-2632: There are no more servers to try to place resource 'ora.hawk.db' on that would satisfy its placement policy
CRS-5017: The resource action "ora.hawk.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/hawk/spfilehawk.ora'
ORA-17503: ksfdopn:10 Failed to open file +DATA/hawk/spfilehawk.ora
ORA-27140: attach to post/wait facility failed
ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: Operation not permitted
ORA-27302: failure occurred at: skgpwinit6
ORA-27303: additional information: startup egid = 54321 (oinstall), current egid = 54322 (dba)
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/racnode-dc1-2/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.hawk.db' on 'racnode-dc1-2' failed
[oracle@racnode-dc1-2 ~]$


[oracle@racnode-dc1-1 ~]$ . /media/patch/hawk.env
The Oracle base has been set to /u01/app/oracle
ORACLE_UNQNAME=hawk
ORACLE_SID=hawk1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/12.1.0.1/db1
Oracle Instance not alive for sid "hawk1"

[oracle@racnode-dc1-1 ~]$ srvctl start database -d hawk
PRCR-1079 : Failed to start resource ora.hawk.db
CRS-5017: The resource action "ora.hawk.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/hawk/spfilehawk.ora'
ORA-17503: ksfdopn:10 Failed to open file +DATA/hawk/spfilehawk.ora
ORA-27140: attach to post/wait facility failed
ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: Operation not permitted
ORA-27302: failure occurred at: skgpwinit6
ORA-27303: additional information: startup egid = 54321 (oinstall), current egid = 54322 (dba)
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/racnode-dc1-2/crs/trace/crsd_oraagent_oracle.trc".

CRS-5017: The resource action "ora.hawk.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/hawk/spfilehawk.ora'
ORA-17503: ksfdopn:10 Failed to open file +DATA/hawk/spfilehawk.ora
ORA-27140: attach to post/wait facility failed
ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: Operation not permitted
ORA-27302: failure occurred at: skgpwinit6
ORA-27303: additional information: startup egid = 54321 (oinstall), current egid = 54322 (dba)
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/racnode-dc1-1/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.hawk.db' on 'racnode-dc1-2' failed
CRS-2632: There are no more servers to try to place resource 'ora.hawk.db' on that would satisfy its placement policy
CRS-2674: Start of 'ora.hawk.db' on 'racnode-dc1-1' failed
[oracle@racnode-dc1-1 ~]$

Incorrect permissions for oracle library was the cause.
Change permissions for $GRID_HOME/bin/oracle (chmod 6751 $GRID_HOME/bin/oracle), stop and start CRS resolved the failure.

[oracle@racnode-dc1-1 dbs]$ ls -lhrt $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle dba 314M Apr 20 16:06 /u01/app/oracle/12.1.0.1/db1/bin/oracle

[oracle@racnode-dc1-1 dbs]$ ls -lhrt /u01/18.3.0.0/grid/bin/oracle
-rwxr-x--x 1 oracle oinstall 396M Apr 20 19:21 /u01/18.3.0.0/grid/bin/oracle

[oracle@racnode-dc1-1 dbs]$ cd /u01/18.3.0.0/grid/bin/
[oracle@racnode-dc1-1 bin]$ chmod 6751 oracle
[oracle@racnode-dc1-1 bin]$ ls -lhrt /u01/18.3.0.0/grid/bin/oracle
-rwsr-s--x 1 oracle oinstall 396M Apr 20 19:21 /u01/18.3.0.0/grid/bin/oracle

[root@racnode-dc1-1 ~]# . /media/patch/gi.env
The Oracle base has been set to /u01/app/oracle
ORACLE_SID=+ASM1
ORACLE_BASE=/u01/app/oracle
GRID_HOME=/u01/18.3.0.0/grid
ORACLE_HOME=/u01/18.3.0.0/grid
Oracle Instance alive for sid "+ASM1"
[root@racnode-dc1-1 ~]# crsctl stop crs

====================================================================================================

[root@racnode-dc1-2 ~]# . /media/patch/gi.env
The Oracle base has been set to /u01/app/oracle
ORACLE_SID=+ASM2
ORACLE_BASE=/u01/app/oracle
GRID_HOME=/u01/18.3.0.0/grid
ORACLE_HOME=/u01/18.3.0.0/grid
Oracle Instance alive for sid "+ASM2"

[root@racnode-dc1-2 ~]# ls -lhrt $GRID_HOME/bin/oracle
-rwxr-x--x 1 oracle oinstall 396M Apr 21 01:44 /u01/18.3.0.0/grid/bin/oracle

[root@racnode-dc1-2 ~]# chmod 6751 $GRID_HOME/bin/oracle
[root@racnode-dc1-2 ~]# ls -lhrt $GRID_HOME/bin/oracle
-rwsr-s--x 1 oracle oinstall 396M Apr 21 01:44 /u01/18.3.0.0/grid/bin/oracle

[root@racnode-dc1-2 ~]# crsctl stop crs

====================================================================================================

[root@racnode-dc1-2 ~]# crsctl start crs
[root@racnode-dc1-1 ~]# crsctl start crs

Reference: RAC Database Can’t Start: ORA-01565, ORA-17503: ksfdopn:10 Failed to open file +DATA/BPBL/spfileBPBL.ora (Doc ID 2316088.1)

Occurence

Jonathan Lewis - Fri, 2019-05-03 07:34

Before you comment – I do know that the title has a spelling mistake in it. That’s because the Oracle code uses exactly this spelling in one of the little-used features of tracing.

I write a note a few years ago about enabling sql_trace (and other tracing events) system-wide for a single SQL statement. In the note I suggested that you could enable tracing for a few minutes then disable it to minimise the impact on the system while still capturing a reasonable number of statement traces. A recent ODC thread, however, described a problem where a particular statement executed in the order of 1,000,000 times per hour – which is getting on for about 300 executions per second, and you probably don’t want to leave a system-wide trace running for any length of time when things are operating at that rate. Fortunately we can refine the method with the occurence filter to capture a small and limited number of executions, spread over as many sessions as are running. Here’s an example of the syntax:

rem
rem     Script: trace_occur.sql
rem     Author: Jonathan Lewis
rem     Dated:  April 2019
rem

define m_sql_id = 'gu1s28n6y73dg'
define m_sql_id = 'fu0ftxk6jcyya'

alter system set events 
        '
        sql_trace[SQL:&m_sql_id] 
        {occurence: start_after 101, end_after 496}
        bind=true,
        wait=true
        '
;

pause   Run the test script here and press return when it ends

alter system set events 
        '
        sql_trace[SQL:&m_sql_id]
        off
        '
;

All I’ve done, compared to the earlier note, is include in curly brackets, just after identifying the SQL ID, the text: “{occurence: start_after 101 , end_after 496}”. Roughly speaking this means that every session will start counting calls to the given statement and on the hundred and first it will start dumping the trace file, and for a total of 496 calls it will continue dumping the trace file. So it’s possible to make sure that a session does trace but doesn’t dump a huge volume of trace data. Of course I do still execute a call to switch tracing off for the statement otherwise every session that subsequently logs on will still start tracing and dump a few executions into their trace file.

There is, unfortunately, a catch. I don’t know how Oracle is counting for the start_after/end_after values – but it’s not executions of the statement, and it varies with working environment, and it changes as the trace is enabled, and it changes with version, and is probably dependent on the session_cached_cursors parameter, and it behaves differently when interacting with the PL/SQL cursor cache. It is perhaps easiest to show an example.

I have table called test_lobs (id, bytes …) with a unique index on (id) for this test.


create table test_lobs (
        id        number(8,0) primary key,
        bytes     number(8,0)
);

insert into test_lobs values(-1,999):
commit;

execute dbms_stats.gather_table_stats(user,'test_lobs')

And one of my test scripts is as follows:

rem
rem     This generates a statement with SQL_ID = gu1s28n6y73dg
rem

declare
        m_result number;
begin
        for i in 1..1000 loop
                begin
                        select bytes into m_result from test_lobs where id = i;
                exception
                        when others then null;
        end;
        end loop;
end;
/

Running 18.3 I start the trace script from one session, then start the test script from another session. As it stands the SQL statement embedded in the PL/SQL loop will have the SQL_ID I am tracing, so the second session will start dumping a trace file. The big question is: which executions of the statement will it dump? Since I’ve enabled bind variable dumping and the bound value is a simple loop counter it will be easy (!) to find the answer to this question.

To stabilise the results I did the following:

  • Session 1: Create the table.
  • Session 1: Start the trace event
  • Session 2: Connect to the database and run the test
  • Session 1: End the trace event
  • Session 1: Start the trace event again
  • Session 2: Connect to the database again and run the test a second time
  • Session 1: End the trace event

I’ll explain the need for looking at the results of the second cycle in a moment.

The trace file I produced started with the first three lines below, and then repeated the 10 line highlighted fragment a number of times:


PARSING IN CURSOR #140126713239784 len=43 dep=1 uid=104 oct=3 lid=104 tim=168304257545 hv=233016751 ad='63b8f0c0' sqlid='gu1s28n6y73dg'
SELECT BYTES FROM TEST_LOBS WHERE ID = :B1
END OF STMT

====================================================================================================
BINDS #140126713239784:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f71cb0c67c0  bln=22  avl=02  flg=05
  value=50
EXEC #140126713239784:c=0,e=57,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=2054437130,tim=168304262695
FETCH #140126713239784:c=0,e=3,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=1,plh=2054437130,tim=168304262729
CLOSE #140126713239784:c=0,e=1,dep=1,type=3,tim=168304262772
====================================================================================================

Notice the bind value report. A key feature that we are interested in is the first “value=” reported and the last “value=”. In my case the low/high were 26 and 87, for a total of 62 executions. A little arithmetic (and a few corroborating checks) highlight the significance of the following:

  • I started tracing after the 25th execution, and 25 * 4 + 1 = 101, my start_after value.
  • I traced 62 executions and 62 * 8 = 496, my end_after value.

Oracle is counting something whenever it hits the SQL_ID we’ve specified but (for SQL inside a PL/SQL loop) it’s counting something which happens 4 times for each execution; then when it hits the start_after and starts tracing whatever it counts happens twice as often each time around the loop.

My general response to this type of detail is: “Argh!!!” – by the way. Maybe a call to Frits or Stefan asking them to trace C functions is in order.

By this time you may have guessed why I examined the trace file from the second run of the test. The counting seems to include counts of database calls that take place in the recursive SQL needed to optimise / hard parse the query – anything that relates to the SQL_ID we specify may be included in the counts. So on the first test I got a load of garbage in the trace file then saw a partial dump of the trace data for value=2 and the trace file ended partway through the trace data for value=17.

As a further test, I had a go with pure SQL calls in a test script:


set serveroutput off
variable b1 number;

exec :b1 := 100
select bytes from test_lobs where id = :b1;

exec :b1 := 101
select bytes from test_lobs where id = :b1;

...

exec :b1 := 129
select bytes from test_lobs where id = :b1;

After getting a stable result, versions 12.1.0.2 and 18.3.0.0 behaved differently;

  • 18.3.0.0 – counted 5 for every execution, so start_after = 16 skipped the first 3 executions and started tracing for value = 103
  • 12.1.0.2 – counted 7 for the first execution and 5 thereafter, so start_after=8 skipped one execution, start_after=13 skipped two and so on.
  • Both versions counted 10 for every execution while tracing was enabled, so end_after = 30 traced 3 executions in both cases.

It’s possible, of course, that some differences in the way session_cached_cursors works would for the small difference – but I suspect I could have spent a couple of days trying to sort out minor variations due to slight parameter and implementation changes between versions. It’s also possible that some of my guesses are wrong and there is more method to the madness than I have spotted.

Conclusion

It is possible to enable tracing system-wide for a limited number of executions per session of a given statement; however the number of executions that might take place before tracing starts and the number of executions actually traced depends on a variety of details of which some may be outside your control.

As a baseline, it looks as if the number of executions before tracing starts is going to be about one-fifth of the value you set for start_after, and the number of executions trace will be about one-tenth of the end_after; however recursive SQL (perhaps even including dynamic sampling) can get caught up in the counts, potentially reducing the number of executions of the target statement that you see.

Wireguard: Installation & configuration

Dietrich Schroff - Fri, 2019-05-03 04:31
To install wireguard i followed this instruction.
First step is to add the repository to your machine:

root@zerberus:~# add-apt-repository ppa:wireguard/wireguard
 WireGuard is a novel VPN that runs inside the Linux Kernel. This is the Ubuntu packaging for WireGuard. More info may be found at its website, listed below.

More info: https://www.wireguard.com/
Packages: wireguard wireguard-tools wireguard-dkms

Install with: $ apt install wireguard

For help, please contact
 Mehr Informationen: https://launchpad.net/~wireguard/+archive/ubuntu/wireguard
[ENTER] drücken zum Weitermachen oder Strg-c, um das Hinzufügen abzubrechen.

OK:1 http://de.archive.ubuntu.com/ubuntu bionic InRelease
OK:2 http://ppa.launchpad.net/wireguard/wireguard/ubuntu bionic InRelease                                
OK:3 https://packages.microsoft.com/repos/azure-cli bionic InRelease                                                               
OK:4 http://ppa.launchpad.net/yannubuntu/boot-repair/ubuntu bionic InRelease                                                       
Paketlisten werden gelesen... Fertig               
Then the installion:

root@zerberus:~# apt install wireguard
Paketlisten werden gelesen... Fertig
Abhängigkeitsbaum wird aufgebaut.      
Statusinformationen werden eingelesen.... Fertig
Die folgenden Pakete wurden automatisch installiert und werden nicht mehr benötigt:
  btrfs-tools geoip-database-extra libcryptui0a libjs-openlayers seahorse-daemon
Verwenden Sie »apt autoremove«, um sie zu entfernen.
Die folgenden zusätzlichen Pakete werden installiert:
  wireguard-dkms wireguard-tools
Die folgenden NEUEN Pakete werden installiert:
  wireguard wireguard-dkms wireguard-tools
0 aktualisiert, 3 neu installiert, 0 zu entfernen und 1 nicht aktualisiert.
Es müssen 640 kB an Archiven heruntergeladen werden.
Nach dieser Operation werden 4.814 kB Plattenplatz zusätzlich benutzt.
Möchten Sie fortfahren? [J/n]
Holen:1 http://ppa.launchpad.net/wireguard/wireguard/ubuntu bionic/main amd64 wireguard-dkms all 0.0.20190123-wg1~bionic [551 kB]
Holen:2 http://ppa.launchpad.net/wireguard/wireguard/ubuntu bionic/main amd64 wireguard-tools amd64 0.0.20190123-wg1~bionic [85,2 kB]
Holen:3 http://ppa.launchpad.net/wireguard/wireguard/ubuntu bionic/main amd64 wireguard all 0.0.20190123-wg1~bionic [4.136 B]
Es wurden 640 kB in 0 s geholt (1.307 kB/s).
Vormals nicht ausgewähltes Paket wireguard-dkms wird gewählt.
(Lese Datenbank ... 471444 Dateien und Verzeichnisse sind derzeit installiert.)
Vorbereitung zum Entpacken von .../wireguard-dkms_0.0.20190123-wg1~bionic_all.deb ...
Entpacken von wireguard-dkms (0.0.20190123-wg1~bionic) ...
Vormals nicht ausgewähltes Paket wireguard-tools wird gewählt.
Vorbereitung zum Entpacken von .../wireguard-tools_0.0.20190123-wg1~bionic_amd64.deb ...
Entpacken von wireguard-tools (0.0.20190123-wg1~bionic) ...
Vormals nicht ausgewähltes Paket wireguard wird gewählt.
Vorbereitung zum Entpacken von .../wireguard_0.0.20190123-wg1~bionic_all.deb ...
Entpacken von wireguard (0.0.20190123-wg1~bionic) ...
wireguard-dkms (0.0.20190123-wg1~bionic) wird eingerichtet ...
Loading new wireguard-0.0.20190123 DKMS files...
Building for 4.17.0-rc3
Building initial module for 4.17.0-rc3
Secure Boot not enabled on this system.
Done.

wireguard:
Running module version sanity check.
 - Original module
   - No original module exists within this kernel
 - Installation
   - Installing to /lib/modules/4.17.0-rc3/updates/dkms/

depmod.....

DKMS: install completed.
wireguard-tools (0.0.20190123-wg1~bionic) wird eingerichtet ...
wireguard (0.0.20190123-wg1~bionic) wird eingerichtet ...
Trigger für man-db (2.8.3-2ubuntu0.1) werden verarbeitet ...
root@zerberus:~#
And then the configuration:
(i extracted the steps from the video here)
root@zerberus:~# wg genkey > /root/private.wireguard
Warning: writing to world accessible file.
Consider setting the umask to 077 and trying again.

root@zerberus:~# ls -l /root/
insgesamt 4
-rw-r--r-- 1 root root 45 Apr 27 18:55 private.wireguard

root@zerberus:~# cat /root/private.wireguard 
XXXYYYY....=
root@zerberus:~# wg pubkey  < /root/private.wireguard
ZZZAAAA...=

root@zerberus:~# ip link add wg0 type wireguard
root@zerberus:~# ip addr add 10.0.0.1/24 dev wg0
root@zerberus:~# wg set wg0 private-key /root/private.wireguard
root@zerberus:~# ip link set wg0 up

root@zerberus:~# ifconfig
....
wg0: flags=209  mtu 1420
        inet 10.0.0.1  netmask 255.255.255.0  destination 10.0.0.1
        unspec 00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00  txqueuelen 1000  (UNSPEC)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
All the steps up to here, have to be done on both servers.
My setup was
  • server 1: "public ip" 192.168.178.39, vpn ip 10.0.0.1
    public wireguard key XXX
    wirguard port: 46932 (how to get this number, just move on)
  • server 2: "public ip" 192.168.178.54, vpn ip 10.0.0.2
    public wireguard key YYY
    wireguard port: 35891
 To get the public keys and the port number use this command:
root@zerberus:~# wg

interface: wg0
  public key: XXX
  private key: (hidden)
  listening port: 46932
Then use the ip, port and public key from the secondary server:
root@zerberus:~# wg set wg0 peer 23P8GMzwpnpaw38wEERXev1jJIQlkhB/lZB35wwXVD4= allowed-ips 10.0.0.2/32 endpoint 192.168.178.54:35891
Do the some on the secondary machine with the ip, port and public key from primary server.

And after that you can check with:
root@zerberus:~# ping 10.0.0.2

PING 10.0.0.2 (10.0.0.2) 56(84) bytes of data.
64 bytes from 10.0.0.2: icmp_seq=1 ttl=64 time=47.0 ms
64 bytes from 10.0.0.2: icmp_seq=2 ttl=64 time=63.8 ms
^C
--- 10.0.0.2 ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1000ms
rtt min/avg/max/mdev = 47.018/55.442/63.866/8.424 ms
I think this was much easier than setting up IPSec or OpenVPN.


CDB Fleet in Oracle Database 18c

Oracle in Action - Fri, 2019-05-03 03:21

RSS content

Oracle database 18c  introduces a new CDB Fleet feature  which allows many CDBs to be managed as one. A CDB fleet is a collection of CDBs and hosted PDBs that you can monitor and manage as one logical CDB from a centralized location.

There are two possible roles within a CDB Fleet:

  • Lead CDB: Only one CDB in the Fleet may be designated as the Lead CDB. The lead CDBis the central location for monitoring and managing all the CDBs in the fleet.
  • Member CDB: The CDBs registered with a lead CDB are called member CDBs. There can be one or more member CDB’s in a CDB fleet.

For every  member CDB,  proxy PDBs for the member CDB and its PDB(s) are automatically created in the lead CDB,.  Consequently, all the member CDBs  and their PDBs are now “visible” in the lead CDB . This enables management and monitoring of the entire estate of PDBs in the fleet, physically distributed across various CDBs, from the lead CDB.

Advantages

Reporting, monitoring, and management of the entire CDB fleet through a single interface:

  • Provides massive scalability of the underlying infrastructure
  • Reduces capital and operational costs
  • Provides greater efficiencies to the business.

Related Links:

‘STUB’ Status In DBA_PDBS

References:

https://docs.oracle.com/cd/E96517_01/newft/database-new-features-guide.pdf

https://docs.oracle.com/en/database/oracle/oracle-database/18/multi/administering-cdb-fleet.html#GUID-5951E81B-4351-4FA4-9F7B-52D2FEB0428D



Tags:  

Del.icio.us
Digg

Copyright © ORACLE IN ACTION [CDB Fleet in Oracle Database 18c], All Right Reserved. 2019.

The post CDB Fleet in Oracle Database 18c appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

View SYS permissions

Tom Kyte - Thu, 2019-05-02 21:46
A vendor would like me to run a script against a client's database. The script requires 'SYS' user access and makes the following grants: <code>grant select on sys.gv_\$mystat to public;</code> <code>grant select on sys.gv_\$vsession to public;</...
Categories: DBA Blogs

Ways to Name the Output Columns of a Pipelined Table Function

Tom Kyte - Thu, 2019-05-02 21:46
How many ways are there to name the output columns of a pipelined table function? My understanding is the only way is via a RECORD type like this: <code>CREATE PACKAGE blah_blah_blah IS TYPE abc_rec IS RECORD (ID NUMBER, ...
Categories: DBA Blogs

Sequence behavior skipping values

Tom Kyte - Thu, 2019-05-02 21:46
Hello, Ask Tom Team. I have a table in a 2-node RAC with an identity column. The sequence is generated by default, cache 3000 and noorder option. I see something weird. The first insert was id 1, which means that it came from instance 1, then...
Categories: DBA Blogs

Unable to complete finish_redef_table on reference partitioned table

Tom Kyte - Thu, 2019-05-02 21:46
Hi , Could you please have a look at below reference partition scenario Rowcount of order_items is 25 millions: I need to change the partition technique of child table i.r. order_items to DAILY-RANGE INTERVAL partition. Table dont have primary k...
Categories: DBA Blogs

Search on concatenated index values

Tom Kyte - Thu, 2019-05-02 21:46
I am looking into an issue with concatenated index (also known as multi-column, composite or combined index). So, by the current design, I have 2 columns KEY1 and KEY2, which produce my PRIMARY KEY PK1. <code> CREATE TABLE "myTable" ( "...
Categories: DBA Blogs

Error while cloning remote database using dblink: ORA-65345: cannot refresh pluggable database

Tom Kyte - Thu, 2019-05-02 21:46
<code>create pluggable database test3 from test32@ttxtest_link1 parallel 8 refresh mode none create_file_dest ='/u02/app/oracle/oradata/test3/' * ERROR at line 1: ORA-65345: cannot refresh pluggable database ORA-17627: ORA-03135: connection lost ...
Categories: DBA Blogs

DML Operation in PL/SQL Function

Tom Kyte - Thu, 2019-05-02 21:46
Sir, 1) Can I perform DML (Insert, Update, Delete) also into a PL/SQL function.....? 2) Can I call a Trigger Explicitly.
Categories: DBA Blogs

Migrating Oracle database

Tom Kyte - Thu, 2019-05-02 21:46
Hello, Thanks for taking up this question. There is an enterprise java application hosted on a huge (40 TB) Oracle database. Can this Oracle database be migrated to : 1. SQL Server or any other RDBMS? 2. Any No SQL database? I have been as...
Categories: DBA Blogs

When-Tree-Node-Selected question.

Tom Kyte - Thu, 2019-05-02 21:46
I've created a tree and it is populating correctly during the when-new-form-instance trigger, but my question is that when I click the mouse on a tree node, the when-tree-node-selection trigger fires properly and the node is highlighted. But wh...
Categories: DBA Blogs

Metrics Driven Blue-green Deployments using Spinnaker’s Cloud Foundry Integration

Pas Apicella - Thu, 2019-05-02 18:24
I recently attended CF Summit in Philadelphia in March 2019 and here is the talk track to that.

Metrics Driven Blue-green Deployments using Spinnaker’s Cloud Foundry Integration - Amith Nambiar & Pas Apicella, Pivotal

https://www.youtube.com/watch?v=9C8m7n_sG38 
Categories: Fusion Middleware

Partner Webcast – Oracle Mobile Hub Simplifies Enterprise Mobile

Mobile is everywhere and touches every facet of our lives. Oracle Mobile Hub provides an open, comprehensive platform that simplifies development for mobile app developers, backend service...

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

Installed 19.3 on Linux on VirtualBox

Bobby Durrett's DBA Blog - Thu, 2019-05-02 15:14

I noticed that Oracle database 19.3 was available to download so I installed it on a Linux VM under VirtualBox.

I cloned my base Oracle Linux 7 VM and did a yum -y update to get it up to the latest Linux version.

I installed the preinstall RPM:

yum install oracle-database-preinstall-19c

Created the Oracle home, set some variables, and disabled the firewall.

mkdir -p /home/oracle/product/db/19.0.0
chgrp oinstall /home/oracle/product/db/19.0.0
cd /home/oracle/product/db/19.0.0

export ORACLE_BASE=/home/oracle/product
export ORACLE_HOME=/home/oracle/product/db/19.0.0

[root@ora19 ~]# systemctl stop firewalld
[root@ora19 ~]# systemctl disable firewalld

Edited my .bashrc

export ORACLE_BASE=/home/oracle/product
export ORACLE_HOME=/home/oracle/product/db/19.0.0
export ORAENV_ASK=NO
export ORACLE_SID=orcl
. oraenv

Unzipped the downloaded file LINUX.X64_193000_db_home.zip in my ORACLE_HOME. Then I ran runInstaller from the ORACLE_HOME through MobaXterm. For some reason this decided to put the install window overlapping my two screens so I couldn’t tell what I was typing, and it didn’t allow me to move it. Fun times.

It seemed to hang on the last step, but it eventually finished. The process named ora_mz00_orcl was spinning on the CPU for part of the time that it seemed hung. I guess it was 5 or 10 minutes, but I didn’t time it.

Seems to be up and working:

SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 2 13:00:59 2019
Version 19.3.0.0.0

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

Last Successful login time: Thu May 02 2019 12:51:54 -07:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

Bobby

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator