Feed aggregator

Downloading ppts from sessions

Tom Kyte - Thu, 2020-02-20 21:12
HI ALl, say what is the url to the ppt for: ?BCS Office Hours - Multitenant Fundamentals & Hands On Lab? ? /thanks /paul
Categories: DBA Blogs

Handy TensorFlow.js API for Client-Side ML Development

Andrejus Baranovski - Thu, 2020-02-20 01:45
Let’s look into TensorFlow.js API for training data handling, training execution, and inference. TensorFlow.js is awesome because it brings Machine Learning into the hands of Web developers, this provides mutual benefit. Machine Learning field gets more developers and supporters, while Web development becomes more powerful with the support of Machine Learning.


Read more - Handy TensorFlow.js API for Client-Side ML Development.

Simplify RMAN Restore With Meaningful Tag

Michael Dinh - Wed, 2020-02-19 15:44

Here is a simple demo for how to restore RMAN backup in case of failed migration using RMAN tag.

List backup from File System:

[oracle@db-fs-1 ~]$ ls -alrt /u01/backup/*MIGRATION*
-rw-r----- 1 oracle oinstall  12886016 Feb 18 21:56 /u01/backup/HAWK_3291419015_20200218_1cuosf50_1_1_MIGRATION_44
-rw-r----- 1 oracle oinstall   1073152 Feb 18 21:56 /u01/backup/HAWK_3291419015_20200218_1duosf58_1_1_MIGRATION_45
-rw-r----- 1 oracle oinstall 112263168 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1buosf50_1_1_MIGRATION_43
-rw-r----- 1 oracle oinstall 212926464 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1auosf50_1_1_MIGRATION_42
-rw-r----- 1 oracle oinstall   2946560 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1euosf61_1_1_MIGRATION_46
-rw-r----- 1 oracle oinstall    114688 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1fuosf63_1_1_MIGRATION_47
-rw-r----- 1 oracle oinstall   1114112 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1guosf64_1_1_MIGRATION_48
-rw-r----- 1 oracle oinstall      3584 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1iuosf67_1_1_MIGRATION_50
-rw-r----- 1 oracle oinstall   2946560 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1huosf67_1_1_MIGRATION_49
-rw-r----- 1 oracle oinstall   1114112 Feb 18 21:57 /u01/backup/CTL_HAWK_3291419015_20200218_1juosf6a_1_1_MIGRATION_51
-rw-r----- 1 oracle oinstall      3584 Feb 18 21:57 /u01/backup/CTL_HAWK_3291419015_20200218_1kuosf6c_1_1_MIGRATION_52
-rw-r----- 1 oracle oinstall    114688 Feb 18 21:57 /u01/backup/CTL_HAWK_3291419015_20200218_1luosf6e_1_1_MIGRATION_53
-rw-r----- 1 oracle oinstall   1114112 Feb 18 21:57 /u01/backup/CTL_HAWK_3291419015_20200218_1muosf6f_1_1_MIGRATION_54
[oracle@db-fs-1 ~]$

List backup from RMAN:

[oracle@db-fs-1 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Feb 19 04:21:17 2020

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

connected to target database: HAWK (DBID=3291419015)

RMAN> list backup summary tag='MIGRATION';


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
39      B  0  A DISK        2020-FEB-18 21:56:52 1       1       YES        MIGRATION
40      B  0  A DISK        2020-FEB-18 21:56:56 1       1       YES        MIGRATION
41      B  0  A DISK        2020-FEB-18 21:57:11 1       1       YES        MIGRATION
42      B  0  A DISK        2020-FEB-18 21:57:17 1       1       YES        MIGRATION
43      B  A  A DISK        2020-FEB-18 21:57:22 1       1       YES        MIGRATION
44      B  F  A DISK        2020-FEB-18 21:57:23 1       1       YES        MIGRATION
45      B  F  A DISK        2020-FEB-18 21:57:25 1       1       YES        MIGRATION
46      B  A  A DISK        2020-FEB-18 21:57:27 1       1       YES        MIGRATION
47      B  A  A DISK        2020-FEB-18 21:57:27 1       1       YES        MIGRATION
48      B  F  A DISK        2020-FEB-18 21:57:31 1       1       YES        MIGRATION
49      B  A  A DISK        2020-FEB-18 21:57:32 1       1       YES        MIGRATION
50      B  F  A DISK        2020-FEB-18 21:57:34 1       1       YES        MIGRATION
52      B  F  A DISK        2020-FEB-18 21:57:36 1       1       YES        MIGRATION

RMAN> list backup of database summary tag='MIGRATION';


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
39      B  0  A DISK        2020-FEB-18 21:56:52 1       1       YES        MIGRATION
40      B  0  A DISK        2020-FEB-18 21:56:56 1       1       YES        MIGRATION
41      B  0  A DISK        2020-FEB-18 21:57:11 1       1       YES        MIGRATION
42      B  0  A DISK        2020-FEB-18 21:57:17 1       1       YES        MIGRATION

RMAN> list backup of archivelog all summary tag='MIGRATION';


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
43      B  A  A DISK        2020-FEB-18 21:57:22 1       1       YES        MIGRATION
46      B  A  A DISK        2020-FEB-18 21:57:27 1       1       YES        MIGRATION
47      B  A  A DISK        2020-FEB-18 21:57:27 1       1       YES        MIGRATION
49      B  A  A DISK        2020-FEB-18 21:57:32 1       1       YES        MIGRATION

RMAN> list backup of controlfile summary tag='MIGRATION';


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
45      B  F  A DISK        2020-FEB-18 21:57:25 1       1       YES        MIGRATION
48      B  F  A DISK        2020-FEB-18 21:57:31 1       1       YES        MIGRATION
52      B  F  A DISK        2020-FEB-18 21:57:36 1       1       YES        MIGRATION

RMAN> list backup of spfile summary tag='MIGRATION';


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
44      B  F  A DISK        2020-FEB-18 21:57:23 1       1       YES        MIGRATION
50      B  F  A DISK        2020-FEB-18 21:57:34 1       1       YES        MIGRATION

RMAN> list backupset 42,49,50,52;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
42      Incr 0  203.05M    DISK        00:00:29     2020-FEB-18 21:57:17
        BP Key: 42   Status: AVAILABLE  Compressed: YES  Tag: MIGRATION
        Piece Name: /u01/backup/HAWK_3291419015_20200218_1auosf50_1_1_MIGRATION_42
        Keep: BACKUP_LOGS        Until: 2020-AUG-18 21:56:48
  List of Datafiles in backup set 42
  File LV Type Ckp SCN    Ckp Time             Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- -------------------- ----------- ------ ----
  1    0  Incr 1428959    2020-FEB-18 21:56:48              NO    /u02/oradata/HAWK/datafile/o1_mf_system_h4s874gt_.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
49      3.00K      DISK        00:00:00     2020-FEB-18 21:57:32
        BP Key: 49   Status: AVAILABLE  Compressed: YES  Tag: MIGRATION
        Piece Name: /u01/backup/CTL_HAWK_3291419015_20200218_1kuosf6c_1_1_MIGRATION_52
        Keep: BACKUP_LOGS        Until: 2020-AUG-18 21:57:32

  List of Archived Logs in backup set 49
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    3       1429002    2020-FEB-18 21:57:26 1429040    2020-FEB-18 21:57:32

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
50      Full    96.00K     DISK        00:00:00     2020-FEB-18 21:57:34
        BP Key: 50   Status: AVAILABLE  Compressed: YES  Tag: MIGRATION
        Piece Name: /u01/backup/CTL_HAWK_3291419015_20200218_1luosf6e_1_1_MIGRATION_53
        Keep: BACKUP_LOGS        Until: 2020-AUG-18 21:57:33
  SPFILE Included: Modification time: 2020-FEB-18 21:51:45
  SPFILE db_unique_name: HAWK

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
52      Full    1.05M      DISK        00:00:01     2020-FEB-18 21:57:36
        BP Key: 52   Status: AVAILABLE  Compressed: YES  Tag: MIGRATION
        Piece Name: /u01/backup/CTL_HAWK_3291419015_20200218_1muosf6f_1_1_MIGRATION_54
        Keep: BACKUP_LOGS        Until: 2020-AUG-18 21:57:35
  Control File Included: Ckp SCN: 1429047      Ckp time: 2020-FEB-18 21:57:35

RMAN>

You are probably wondering why BS 49 with Piece Name: /u01/backup/CTL_HAWK_3291419015_20200218_1kuosf6c_1_1_MIGRATION_52 contains archivelog?

RMAN backup script:

[oracle@db-fs-1 ~]$ cat /u01/backup/backup_keep.rman
spool log to /u01/backup/rman_keep_backup_migration.log
connect target;
set echo on
show all;
run {
allocate channel c1 device type disk format '/u01/backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
allocate channel c2 device type disk format '/u01/backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
allocate channel c3 device type disk format '/u01/backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
backup as compressed backupset incremental level 0 filesperset 1 check logical database
keep until time 'ADD_MONTHS(SYSDATE,6)' TAG='MIGRATION';
backup as compressed backupset archivelog from time 'trunc(sysdate)'
filesperset 8
keep until time 'ADD_MONTHS(SYSDATE,6)' TAG='MIGRATION';
}
run {
allocate channel c1 device type disk format '/u01/backup/CTL_%d_%I_%T_%U_MIGRATION_%s';
backup as compressed backupset current controlfile
keep until time 'ADD_MONTHS(SYSDATE,6)' TAG='MIGRATION';
}
LIST BACKUP OF DATABASE SUMMARY TAG='MIGRATION';
LIST BACKUP OF ARCHIVELOG ALL SUMMARY TAG='MIGRATION';
LIST BACKUP OF CONTROLFILE TAG='MIGRATION';
report schema;
exit
[oracle@db-fs-1 ~]$

RMAN backup log snippet:

allocated channel: c1
channel c1: SID=57 device type=DISK

Starting backup at 2020-FEB-18 21:57:30

backup will be obsolete on date 2020-AUG-18 21:57:30
archived logs required to recover from this backup will be backed up
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 2020-FEB-18 21:57:31
channel c1: finished piece 1 at 2020-FEB-18 21:57:32
piece handle=/u01/backup/CTL_HAWK_3291419015_20200218_1juosf6a_1_1_MIGRATION_51 tag=MIGRATION comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
current log archived

backup will be obsolete on date 2020-AUG-18 21:57:32
archived logs required to recover from this backup will be backed up
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set

******* input archived log thread=1 sequence=3 RECID=30 STAMP=1032731852 *******

channel c1: starting piece 1 at 2020-FEB-18 21:57:32
channel c1: finished piece 1 at 2020-FEB-18 21:57:33
piece handle=/u01/backup/CTL_HAWK_3291419015_20200218_1kuosf6c_1_1_MIGRATION_52 tag=MIGRATION comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01

Restore backup from RMAN:

RMAN> startup force nomount;

Oracle instance started

Total System Global Area     805306368 bytes

Fixed Size                     8625856 bytes
Variable Size                314573120 bytes
Database Buffers             478150656 bytes
Redo Buffers                   3956736 bytes

RMAN> restore controlfile from '/u01/backup/CTL_HAWK_3291419015_20200218_1muosf6f_1_1_MIGRATION_54';

Starting restore at 2020-FEB-19 03:41:37
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/fra/HAWK/controlfile/o1_mf_h4r8xqh6_.ctl
Finished restore at 2020-FEB-19 03:41:38

RMAN> alter database mount;

RMAN> catalog start with '/u01/backup' noprompt;

RMAN> restore database preview summary from tag='MIGRATION';

Starting restore at 2020-FEB-19 03:43:05
using channel ORA_DISK_1


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
42      B  0  A DISK        2020-FEB-18 21:57:17 1       1       YES        MIGRATION
41      B  0  A DISK        2020-FEB-18 21:57:11 1       1       YES        MIGRATION
39      B  0  A DISK        2020-FEB-18 21:56:52 1       1       YES        MIGRATION
40      B  0  A DISK        2020-FEB-18 21:56:56 1       1       YES        MIGRATION


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
47      B  A  A DISK        2020-FEB-18 21:57:27 1       1       YES        MIGRATION
recovery will be done up to SCN 1428959
Media recovery start SCN is 1428959
Recovery must be done beyond SCN 1428964 to clear datafile fuzziness
Finished restore at 2020-FEB-19 03:43:05

RMAN> restore database from tag='MIGRATION';
RMAN> recover database until scn 1428965;

Starting recover at 2020-FEB-19 03:44:45
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/19/2020 03:44:45
RMAN-20208: UNTIL CHANGE is before RESETLOGS change

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       HAWK     3291419015       PARENT  1          2017-JAN-26 13:52:29
2       2       HAWK     3291419015       PARENT  1408558    2020-FEB-18 18:49:45
3       3       HAWK     3291419015       PARENT  1424305    2020-FEB-18 20:02:49
4       4       HAWK     3291419015       PARENT  1425161    2020-FEB-18 20:19:50
5       5       HAWK     3291419015       PARENT  1425162    2020-FEB-18 20:33:05
6       6       HAWK     3291419015       PARENT  1426203    2020-FEB-18 21:13:15
7       7       HAWK     3291419015       CURRENT 1428966    2020-FEB-18 22:05:54

RMAN> recover database until scn 1428967;

Starting recover at 2020-FEB-19 03:47:41
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1
channel ORA_DISK_1: reading from backup piece /u01/backup/HAWK_3291419015_20200218_1huosf67_1_1_MIGRATION_49
channel ORA_DISK_1: piece handle=/u01/backup/HAWK_3291419015_20200218_1huosf67_1_1_MIGRATION_49 tag=MIGRATION
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u02/oradata/HAWK/archivelog/2020_02_19/o1_mf_1_1_h4s8gfjc_.arc thread=1 sequence=1
archived log file name=/u02/oradata/HAWK/archivelog/2020_02_19/o1_mf_1_1_h4rx8c8b_.arc thread=1 sequence=1
channel default: deleting archived log(s)
archived log file name=/u02/oradata/HAWK/archivelog/2020_02_19/o1_mf_1_1_h4s8gfjc_.arc RECID=32 STAMP=1032752861
media recovery complete, elapsed time: 00:00:00
Finished recover at 2020-FEB-19 03:47:42

RMAN> alter database open resetlogs;

Statement processed

RMAN> report schema;

Report of database schema for database with db_unique_name HAWK

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    800      SYSTEM               YES     /u02/oradata/HAWK/datafile/o1_mf_system_h4s874gt_.dbf
3    470      SYSAUX               NO      /u02/oradata/HAWK/datafile/o1_mf_sysaux_h4s86of7_.dbf
4    70       UNDOTBS1             YES     /u02/oradata/HAWK/datafile/o1_mf_undotbs1_h4s86kbl_.dbf
7    5        USERS                NO      /u02/oradata/HAWK/datafile/o1_mf_users_h4s86ncz_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u02/oradata/HAWK/datafile/o1_mf_temp_h4s8jc3n_.tmp

RMAN> delete force noprompt backup tag='MIGRATION';

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
39      39      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1cuosf50_1_1_MIGRATION_44
40      40      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1duosf58_1_1_MIGRATION_45
41      41      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1buosf50_1_1_MIGRATION_43
42      42      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1auosf50_1_1_MIGRATION_42
43      43      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1euosf61_1_1_MIGRATION_46
44      44      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1fuosf63_1_1_MIGRATION_47
45      45      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1guosf64_1_1_MIGRATION_48
46      46      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1iuosf67_1_1_MIGRATION_50
47      47      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1huosf67_1_1_MIGRATION_49
48      48      1   1   AVAILABLE   DISK        /u01/backup/CTL_HAWK_3291419015_20200218_1juosf6a_1_1_MIGRATION_51
49      49      1   1   AVAILABLE   DISK        /u01/backup/CTL_HAWK_3291419015_20200218_1kuosf6c_1_1_MIGRATION_52
50      50      1   1   AVAILABLE   DISK        /u01/backup/CTL_HAWK_3291419015_20200218_1luosf6e_1_1_MIGRATION_53
52      52      1   1   AVAILABLE   DISK        /u01/backup/CTL_HAWK_3291419015_20200218_1muosf6f_1_1_MIGRATION_54
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1cuosf50_1_1_MIGRATION_44 RECID=39 STAMP=1032731809
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1duosf58_1_1_MIGRATION_45 RECID=40 STAMP=1032731816
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1buosf50_1_1_MIGRATION_43 RECID=41 STAMP=1032731808
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1auosf50_1_1_MIGRATION_42 RECID=42 STAMP=1032731808
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1euosf61_1_1_MIGRATION_46 RECID=43 STAMP=1032731841
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1fuosf63_1_1_MIGRATION_47 RECID=44 STAMP=1032731843
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1guosf64_1_1_MIGRATION_48 RECID=45 STAMP=1032731845
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1iuosf67_1_1_MIGRATION_50 RECID=46 STAMP=1032731847
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1huosf67_1_1_MIGRATION_49 RECID=47 STAMP=1032731847
deleted backup piece
backup piece handle=/u01/backup/CTL_HAWK_3291419015_20200218_1juosf6a_1_1_MIGRATION_51 RECID=48 STAMP=1032731851
deleted backup piece
backup piece handle=/u01/backup/CTL_HAWK_3291419015_20200218_1kuosf6c_1_1_MIGRATION_52 RECID=49 STAMP=1032731852
deleted backup piece
backup piece handle=/u01/backup/CTL_HAWK_3291419015_20200218_1luosf6e_1_1_MIGRATION_53 RECID=50 STAMP=1032731854
deleted backup piece
backup piece handle=/u01/backup/CTL_HAWK_3291419015_20200218_1muosf6f_1_1_MIGRATION_54 RECID=52 STAMP=1032752561
Deleted 13 objects


RMAN> exit


Recovery Manager complete.

[oracle@db-fs-1 ~]$ ls -alrt /u01/backup/
total 28
drwxrwxr-x 6 oracle oinstall  4096 Feb 18 19:11 ..
-rw-r--r-- 1 oracle oinstall  1104 Feb 18 20:40 backup_keep.rman
-rw-r--r-- 1 oracle oinstall 12346 Feb 18 21:57 rman_keep_backup_migration.log
drwxr-xr-x 2 oracle oinstall  4096 Feb 19 04:28 .
[oracle@db-fs-1 ~]$

Just a crazy idea.
Keep the same backup tag for all backups until the next level 0.

Backup TAG for daily level 0 backup:

[oracle@db-fs-1 ~]$ echo "$(date +%Y%b%d)"
2020Feb19
[oracle@db-fs-1 ~]$

Backup TAG for weekly level 0 backup

[oracle@db-fs-1 ~]$ echo "$(date +%Y%b)_WK$(date +%U)"
2020Feb_WK07
[oracle@db-fs-1 ~]$

Backup TAG for monthly level 0 backup

[oracle@db-fs-1 ~]$ echo "$(date +%Y%b)"
2020Feb
[oracle@db-fs-1 ~]$

Tag: ARCH for archivelog backup and may not be useful.
LV=A means archivelog backup.

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
69      B  A  A DISK        2020-FEB-19 13:29:02 1       1       NO         ARCH
70      B  A  A DISK        2020-FEB-19 13:29:03 1       1       NO         ARCH
71      B  F  A DISK        2020-FEB-19 13:29:04 1       1       NO         TAG20200219T132904

RMAN>

In writing this post, I realized the my own backup script will need some improvements.

Oracle Cloud Applications Achieves FedRAMP Moderate Authorization

Oracle Press Releases - Wed, 2020-02-19 08:00
Press Release
Oracle Cloud Applications Achieves FedRAMP Moderate Authorization New authorization marks key milestone for the Federal Government, extending agency access to the suite of Oracle Cloud Applications covering ERP, HCM, and CX

Redwood Shores, Calif.—Feb 19, 2020

Oracle today announced that Oracle Cloud Applications has achieved FedRAMP Moderate Authorization. FedRAMP is a government-wide program that provides a standardized approach to security assessment, authorization, and continuous monitoring for cloud products and services. With this new authorization, U.S. Federal Government customers can benefit from Oracle’s complete and integrated suite of cloud applications for finance, human resources, supply chain, and customer experience.

To outpace accelerating change in technology, government agencies need to break down data silos, embrace the latest innovations, and improve digital experiences, collaboration, and service. Built on Oracle’s industry-leading cloud platform and infrastructure, Oracle Cloud Applications enables customers to benefit from best-in-class security, high-end scalability, and performance, in addition to strong integration capabilities.

“FedRAMP Authorization for Oracle Cloud Applications is a critical step in meeting the growing demands and compliance requirements of our public sector customers,” said Tamara Greenspan, group vice president, Oracle Public Sector. “By achieving this authorization, we are able to help the Federal Government tap into our complete and innovative cloud applications suite to not just keep pace, but stay ahead of the evolving business and technology landscape.”

Oracle has been a long-standing strategic technology partner of the U.S. Federal Government. In fact, a component of the U.S. Intelligence Community was the first customer to use Oracle’s flagship database software 35 years ago. Today, more than 500 government organizations take advantage of the superior performance of Oracle’s industry-leading technologies.

Contact Info
Celina Bertallee
Oracle
559-283-2425
celina.bertallee@oracle.com
About Oracle

The Oracle Cloud offers a complete suite of integrated applications for Sales, Service, Marketing, Human Resources, Finance, Supply Chain and Manufacturing, plus Highly Automated and Secure Generation 2 Infrastructure featuring the Oracle Autonomous Database. For more information about Oracle (NYSE: ORCL), please visit us at www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Celina Bertallee

  • 559-283-2425

XS$NULL - Can we login to it and does it really have no privileges?

Pete Finnigan - Tue, 2020-02-18 15:11
I have read on line about XS$NULL over the years and particularly the documentation that states that it has no privileges. The documentation states the following: An internal account that represents the absence of a user in a session. Because....[Read More]

Posted by Pete On 17/02/20 At 01:09 PM

Categories: Security Blogs

Interval Partition(s)

Jonathan Lewis - Tue, 2020-02-18 07:45

A quirky little feature of interval partitioning showed up on Twitter today – a parallel insert that would only use a single PX slave to do the inserting. With 1.1 billion rows and the option for running parallel 32 this made the loading process rather slower than it ought to have been.

Fortunately it’s quite easy to model (and work around) the oddity. So here’s a small data set and an empty partitioned table to work with:


rem
rem     Script:         pt_int_load_anomaly.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jan 2020
rem

create table t1 
nologging 
as
select 
        ao.* 
from 
        all_Objects ao, 
        (select rownum id from dual connect by level <= 20)
;

create table pt1
partition  by range (object_id) interval (1000000) (
        partition p1 values less than (1)
)
as
select  * 
from    all_Objects
where   rownum = 0
/

I’ve created a table by copying all_objects 20 times which, for my little sandbox, has given me a total of about 1.2M rows. Then I’ve created an empty interval-partitioned clone of all_objects, with the first partition defined to hold all rows where the object_id is less than 1 (and there’s no object in the database that could match that criterion). I’ve defined the interval to be 1,000,000 and since the highest object_id in my database is about 90,000 the first partition that gets added to this table will be able to hold all the data from t1.

So now we try to do a parallel insert from t1 into pt1, and check the execution plan and parallel execution statistics:


set serveroutput off

insert /*+ append enable_parallel_dml parallel(6) */ into pt1 select * from t1;

select * from table(dbms_xplan.display_cursor);

start pq_tqstat

Note how I’ve used the hint /*+ enable_parallel_dml */ (possible a 12c hint back-ported to 11.2.0.4) rather than using an “alter session”, it’s just a little convenience to be able to embed the directive in the SQL. The pq_tqstat script is one I published some time ago to report the contents of the session-specific dynamic performance view v$pq_tqstat immediately after running a parallel statement.

Here’s the plan:


SQL_ID  25hub68pf1z1s, child number 0
-------------------------------------
insert /*+ append enable_parallel_dml parallel(6) */ into pt1 select *
from t1

Plan hash value: 2888707464

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                            |          |       |       |   631 (100)|          |        |      |            |
|   1 |  PX COORDINATOR                             |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)                       | :TQ10001 |  1235K|   159M|   631  (10)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HIGH WATER MARK BROKERED)| PT1      |       |       |            |          |  Q1,01 | PCWP |            |
|   4 |     OPTIMIZER STATISTICS GATHERING          |          |  1235K|   159M|   631  (10)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE                             |          |  1235K|   159M|   631  (10)| 00:00:01 |  Q1,01 | PCWP |            |
|   6 |       PX SEND RANDOM LOCAL                  | :TQ10000 |  1235K|   159M|   631  (10)| 00:00:01 |  Q1,00 | P->P | RANDOM LOCA|
|   7 |        PX BLOCK ITERATOR                    |          |  1235K|   159M|   631  (10)| 00:00:01 |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL                   | T1       |  1235K|   159M|   631  (10)| 00:00:01 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------

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

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

Note
-----
   - Degree of Parallelism is 6 because of hint

The most important detail of this plan is that the PX slaves do the load as select (operation 3), then send a message to the query coordinator (PX send QC, operation 2) to tell it about the data load. They do not send their data to the QC for the QC to do the load.

So the plan says we will be doing parallel DM, but here’s what v$pq_tqstat tells us:


DFO_NUMBER      TQ_ID SERVER_TYPE     INSTANCE PROCESS           NUM_ROWS      BYTES ROW_SHARE DATA_SHARE      WAITS   TIMEOUTS AVG_LATENCY
---------- ---------- --------------- -------- --------------- ---------- ---------- --------- ---------- ---------- ---------- -----------
         1          0 Producer               1 P006                215880   34785363     17.47      16.86         16          0           0
                                             1 P007                202561   34436325     16.39      16.69         17          0           0
                                             1 P008                207519   34564496     16.79      16.75         17          0           0
                                             1 P009                208408   34594770     16.86      16.77         17          0           0
                                             1 P00A                198915   33529627     16.10      16.25         16          0           0
                                             1 P00B                202537   34430603     16.39      16.69         16          0           0
                      Consumer               1 P000                     0        144      0.00       0.00         51         47           0
                                             1 P001                     0        144      0.00       0.00         51         47           0
                                             1 P002               1235820  206340464    100.00     100.00         75         47           0
                                             1 P003                     0        144      0.00       0.00         51         47           0
                                             1 P004                     0        144      0.00       0.00       1138       1134           0
                                             1 P005                     0        144      0.00       0.00       1137       1133           0

                    1 Producer               1 P000                     0         24      0.00       5.91         51         42           0
                                             1 P001                     0         24      0.00       5.91         50         41           0
                                             1 P002                     2        286    100.00      70.44         58         14           0
                                             1 P003                     0         24      0.00       5.91         51         43           0
                                             1 P004                     0         24      0.00       5.91         51         42           0
                                             1 P005                     0         24      0.00       5.91         51         43           0
                      Consumer               1 QC                       2        406    100.00     100.00        311        179           0

19 rows selected.

The query did run parallel 6 as hinted – and 6 PX slaves scanned the t1 table; but they all sent all their data to one PX slave in the second slave set and that one PX slave did all the inserts. The plan was parallel, but the execution was effectively serial. (You’ll note there is something a little odd about the waits and timeout for p004 and p005 when they are acting as consumers. I may worry about that later, but it could be a host-based side effect of running parallel 6 on a VM with 4 CPUs).

The serialization leads to two questions

  1. What went wrong?
  2. How do we work around this and make the insert “truly” parallel

My answer to (1) is “I don’t know – but I’ll look at it if necessary” combined with the guess – it’s something to do with the table having only one partition at the outset and this has an unexpected side effect on the randomising function for the PX distribution.

My answer to (2) is “if I’m right about (1), why not try pre-defining two partitions, and I’ll even let both of them stay empty”.

So here’s my new definition for pt1:


create table pt1
partition  by range (object_id) interval (1000000) (
        partition p0 values less than (0),
        partition p1 values less than (1)
)
as
select  * 
from    all_Objects
where   rownum = 0
/

Re-running the test with the completely redundant, and permanently empty p0 partition the plan doesn’t change but the results from v$pq_tqstat change dramatically:


DFO_NUMBER      TQ_ID SERVER_TYPE     INSTANCE PROCESS           NUM_ROWS      BYTES ROW_SHARE DATA_SHARE      WAITS   TIMEOUTS AVG_LATENCY
---------- ---------- --------------- -------- --------------- ---------- ---------- --------- ---------- ---------- ---------- -----------
         1          0 Producer               1 P006                207897   34581153     16.82      16.76         23          4           0
                                             1 P007                215669   34786429     17.45      16.86         30          5           0
                                             1 P008                221474   36749626     17.92      17.81         28          5           0
                                             1 P009                204959   34497164     16.58      16.72         22          2           0
                                             1 P00A                177755   30141002     14.38      14.61         21          0           0
                                             1 P00B                208066   35585810     16.84      17.25         25          2           0
                      Consumer               1 P000                213129   35612973     17.25      17.26         82         57           0
                                             1 P001                200516   33570586     16.23      16.27         84         55           0
                                             1 P002                203395   33950449     16.46      16.45         83         56           0
                                             1 P003                205458   34235575     16.63      16.59         82         54           0
                                             1 P004                204111   33999932     16.52      16.48        581        555           0
                                             1 P005                209211   34971669     16.93      16.95        580        553           0

                    1 Producer               1 P000                     2        286     16.67      16.67        422        149           0
                                             1 P001                     2        286     16.67      16.67        398        130           0
                                             1 P002                     2        286     16.67      16.67        405        128           0
                                             1 P003                     2        286     16.67      16.67        437        161           0
                                             1 P004                     2        286     16.67      16.67        406        116           0
                                             1 P005                     2        286     16.67      16.67        440        148           0
                      Consumer               1 QC                      12       1716    100.00     100.00        242        111           0



19 rows selected.

Every consumer receives and inserts roughly 200,000 rows – it’s a totally fair parallel DML. Timings are pretty irrelevant for such a small data set but the excution time did drop from 7 seconds to 4 seconds when parallelism was working “properly”.

I’ve tested this script on 12.2.0.1 and 19.3.0.0 – the same anomaly appears in both versions though it might be worth noting that the strange skew in the waits and timeouts doesn’t appear in 19.3.0.0.

Synchronous refresh in mview ORA-31922: Foreign key must contain partition key in table

Tom Kyte - Tue, 2020-02-18 06:11
Team, Here is my testcase which got failed during Synchronous refresh in mview. <code>create table products as select rownum as prod_id, object_name as prod_name, object_type as prod_category, object_id as prod_category_id, data_object...
Categories: DBA Blogs

Oracle 19C database issue with table types and pipelining

Tom Kyte - Tue, 2020-02-18 06:11
I have a package working fine in 11g version. But when I deploy the same package in 19c version, the behavior is different. PFB the description. Package specification has an cursor and created a table type with cursor%rowtype. Having a pipel...
Categories: DBA Blogs

Sqoop ojdbc8.jar throws error ORA-06502:PL/SQL:: numeric or value error

Tom Kyte - Tue, 2020-02-18 06:11
Hi Tom, We are trying to do a sqoop import to hive from Oracle and struck with a weird error below: WARN[main] org.apache.hadoop.mapred.YarnChild: Exception running child : java.io.IOException: java.sql.SQLExcepion: ORA-00606:error occurred ...
Categories: DBA Blogs

Scheduling Compilation or Execution of Stored Procedures

Tom Kyte - Tue, 2020-02-18 06:11
Hello We have a Oracle 11g Release 2 database with five identical working Schemas being accessed by a VB Client Server business application The db server, with 32GB RAM just hosts this one database Copies of Client applications are installed a...
Categories: DBA Blogs

Finding when someone dropped an object

Tom Kyte - Tue, 2020-02-18 06:11
Hi Team, I have a DB, where a table is dropped from the schema accidentally. We are trying to find whether it got dropped due to manual execution of DROP query and by whom? Is there any way that we can find that the DROP query which is executed ...
Categories: DBA Blogs

How to fetch part of a string for LONG datatype

Tom Kyte - Tue, 2020-02-18 06:11
HI, I am writing a query to find missing table partitions for next year using all_tab_partitions table, I am able to fetch the records with the help of column partition positions, but I have to extract the last partition date (YYYY-MM-DD) from HIG...
Categories: DBA Blogs

When Less Is Best

Floyd Teter - Mon, 2020-02-17 17:47
Late in 2019, Apple obsoleted my trusty iPad Mini II.  No more OS updates, may not run the latest apps, etc.  So I figured it was time for a new iPad... right up until the time I saw the prices. Yikes!!!

The expense of a new iPad, even a Mini, made me step back and reconsider my approach.  I started by considering my uses of the iPad.  I don't really create much with an iPad - it's just too limited for the type of work I do. And I don't communicate much with it, as I have an iPhone for calls, txt, and video chats. I use my iPad to consume:  books, movies, social media and news.

Once I understood my own use cases for the iPad, I realized I could fill my needs with a much less expensive device.  I settled on an Amazon Fire HD 8 (thanks to my kids for the nifty Christmas present).  And it works great for what I do, especially after setting it up to side load from Google Play.  My old Mini has been retired to a life as a digital photo frame and I'm really happy with the Fire.  Is the Fire as elegant of an experience as the Mini?  Not by a long shot.  But it gets the job done quite well.  And, at last check, the Fire is about 20% of the price of a new iPad Mini.  A great example of less being best.

I see this quite a bit in the way users work with enterprise applications - in my specific case, observations are from helping customers with Oracle HCM Cloud Enterprise Applications.  Users expend significant costs in terms of time and labor facilitating some very complicated use cases, many of which are either unnecessary or rarely encountered.  When we collaborate with those customers on those use cases, the end result is often that less is best.  Often they're focused on a specific use case because they have not considered another process for getting the job done. Other times it's a matter of the use case not being as critical to the business as originally thought.  And sometimes it's due to a legal or industry requirement that no longer exists.  And there are many more causes.  But it always makes my day when the lightbulb comes on and people realize they can make do quite well with much less than they originally thought.

I've seen this surface frequently in working with customers on the mobile-enabled Responsive UX.  Simplification was a driving design concept in building out Responsive UX, which means some less used and less important features were culled from the product.  Less is best, based on the feedback we've received from customers who have adopted the new UX.

So the upshot here?  Step back, take a breath, and think about how you use your enterprise apps.  Can you find instances where less would be best?  I'll bet you find more than one.

Join Elimination bug

Jonathan Lewis - Mon, 2020-02-17 09:37

It is possible to take subquery factoring (common table expressions / CTEs) too far. The most important purpose of factoring is to make a complex query easier to understand – especially if you can identify a messy piece of text that is used in more than one part of the query – but I have seen a couple of patterns appearing that make the SQL harder to read.

  • In one pattern each table is given its own factored subquery holding the non-join predicates (and, possibly, filter subqueries) and then the main query is just a simple join of the factored subqueries with nothing but join (ON) predicates.
  • In another pattern each factored subquery consists of the previous subquery with one more table added to it, so every subquery is no more that a “two-table” query block and the final subquery is a simple “select from last_factored_subquery”.

Neither of these patterns is helpful – but today’s blog note is not going to be about going to extremes with subquery factoring; instead it’s an example of a fairly reasonable use of subquery factoring that ran into a “wrong results” bug.

Consider a system that collects data from some type of meters. Here’s a two-table definition for meters and meter readings:


create table meters (
        meter_id        number,
        meter_type      varchar2(10),  -- references meter_types
        date_installed  date,
        padding         varchar2(100),
        constraint met_pk primary key(meter_id)
)
;

create table meter_readings (
        meter_id        number,
        date_read       date,
        reading         number(10,3),
        padding         varchar2(100),
        constraint      mrd_pk primary key(meter_id, date_read),
        constraint      mrd_fk_met foreign key (meter_id) references meters
)
;

insert into meters
select
        1e6 + rownum,
        case mod(rownum,3)
                when 0 then 'A'
                when 1 then 'B'
                       else 'C'
        end,
        trunc(sysdate) - mod(rownum,5),
        rpad('x',100,'x')
from
        dual 
connect by 
        level <= 10
;

execute dbms_stats.gather_table_stats(null,'meters')

insert into meter_readings 
select
        met.meter_id,
        met.date_installed - v.id + 2,
        dbms_random.value,
        rpad('x',100,'x')
from
        meters met,
        (select rownum id from dual connect by level <= 4) v
;

commit;

execute dbms_stats.gather_table_stats(null,'meter_readings')

I’ve picked the obvious primary keys for the two tables and defined the appropriate referential integrity constraint – which means the optimzer should be able to choose the best possible strategies for any query that joins the two tables.

I’ve created a very small data set – a few meters installed in the last few days, and a few readings per meters over the last few days. So lets report the readings for the last 48 hours, and include in the output any meters that haven’t logged a reading in that interval.

Here’s the query I wrote, with its output, running on a 19.3 instance on 17th Feb 2020:


with mrd_cte as (
        select 
                meter_id, date_read, reading
        from 
                meter_readings
        where 
                date_read in (trunc(sysdate), trunc(sysdate)+1)
)
select
        met.meter_id, met.date_installed, mrd_cte.date_read, reading
from
        meters met
left join 
        mrd_cte
on      mrd_cte.meter_id = met.meter_id
;

  METER_ID DATE_INST DATE_READ    READING
---------- --------- --------- ----------
   1000001 16-FEB-20 17-FEB-20       .063
   1000002 15-FEB-20
   1000003 14-FEB-20
   1000004 13-FEB-20
   1000005 17-FEB-20 18-FEB-20        .37
   1000005 17-FEB-20 17-FEB-20       .824
   1000006 16-FEB-20 17-FEB-20       .069
   1000007 15-FEB-20
   1000008 14-FEB-20
   1000009 13-FEB-20
   1000010 17-FEB-20 17-FEB-20       .161
   1000010 17-FEB-20 18-FEB-20       .818

12 rows selected.

The query returns 12 rows – which SQL*Plus can report because it counts them as it fetches them so it can give you the total at the end of the query.

Of course, sometimes people write preliminary queries to find out how big the result set would be before they run the query to acquire the result set itself. In cases like that (where they’re just going to select a “count(*)” the optimizer may a choose different execution path from the base query – perhaps finding a way to do an index-only execution, and maybe eliminating a few table joins from the query. So let’s execute a count of the above query:


rem
rem     Script:         join_elimination_bug.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jan 2020
rem

with mrd_cte as (
        select 
                meter_id, date_read, reading
        from 
                meter_readings
        where 
                date_read in (trunc(sysdate), trunc(sysdate)+1)
)
select count(*) from (
        select
                met.meter_id, met.date_installed, mrd_cte.date_read, mrd_cte.reading
        from
                meters met
        left join 
                mrd_cte
        on      mrd_cte.meter_id = met.meter_id
)
;

  COUNT(*)
----------
        10

1 row selected.

You’ll have to take my word for it, of course, but no-one else was using this database while I was running this test, and no-one else has access to the schema I was using anyway. Unfortunately when I count the 12 rows instead of reporting them Oracle thinks there are only 10 rows. Oops!

Step 1 in investigating the problem – check the execution plans to see if there are any differences in the structure of the plan, the use of predicates, or the outline information. I won’t bother with the plan for the base query because it was very obvious from the count query where the problem lay.


-------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |     1 (100)|          |
|   1 |  SORT AGGREGATE  |        |     1 |            |          |
|   2 |   INDEX FULL SCAN| MET_PK |    10 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$69B21C86")
      ELIMINATE_JOIN(@"SEL$00F67CF8" "METER_READINGS"@"SEL$1")
      OUTLINE(@"SEL$00F67CF8")
      MERGE(@"SEL$C43CA2CA" >"SEL$2")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$C43CA2CA")
      MERGE(@"SEL$D28F6BD4" >"SEL$E6E74641")
      OUTLINE(@"SEL$E6E74641")
      ANSI_REARCH(@"SEL$4")
      OUTLINE(@"SEL$D28F6BD4")
      MERGE(@"SEL$1" >"SEL$006708EA")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$006708EA")
      ANSI_REARCH(@"SEL$3")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$3")
      INDEX(@"SEL$69B21C86" "MET"@"SEL$3" ("METERS"."METER_ID"))
      END_OUTLINE_DATA
  */

This is the plan as pulled from memory by a call to dbms_xplan.display_cursor(). We note particularly the following: meter_readings doesn’t appear in the plan, there is no predicate section (and no asterisks against any of the operations that would tell us there ought to be some predicate information), and there’s a very revealing ELIMINATE_JOIN(@”SEL$00F67CF8″ “METER_READINGS”@”SEL$1”) in the outline information.

For some reason the optimizer has decided that it’s okay to remove meter_readings from the query (even though there may be many meter readings for each meter), so it was inevitable that it produced the wrong result.

Despite my opening note, this is not an issue with subquery factoring – it just looked that way when I started poking at the problem. In fact, if you rewrite the query using an inline view you get the same error, if you turn the inline view into a stored view you get the error, and if you turn the whole query into a simple (left) join with the date predicate as part of the ON clause you still get the error.

The problem lies somewhere in the join elimination transformation. If you go back to the outline information from the bad plan you’ll see the line: ELIMINATE_JOIN(@”SEL$00F67CF8″ “METER_READINGS”@”SEL$1”) – by changing this to NO_ELIMINATE_JOIN(…) and adding it to the main query block I got a suitable plan joining the two tables and producing the right result.

The problem appears in 12.2.0.1 and 19.5.0.0 (tested on livesql) – but does not appear in 12.1.0.2 or 11.2.0.4

There is a known bug associated with this problem:

Bug: 29182901
Abstract: WRONG COUNT WAS RETURNED WHEN _OPTIMIZER_ENHANCED_JOIN_ELIMINATION=TRUE
Query with Outer Join Returned a Wrong Result due to Join Elimination (Doc ID 29182901.8)

The bug is fixed in 20.1, with a fix that has been backported into the Jan 2020 patches for 19, 18, and 12.1

Is tech supporting Google? It sure doesn’t appear so.

Oracle Press Releases - Mon, 2020-02-17 08:00
Blog
Is tech supporting Google? It sure doesn’t appear so.

Ken Glueck, Executive Vice President, Oracle—Feb 17, 2020

There is a lot of chatter that “tech” is supportive of Google’s position in Google v. Oracle. That was certainly the impression Google tried to spin when its Amici filed briefs with the Court last month. But a closer inspection of Google’s Amici makes clear that the technology community is not supporting Google’s position. Not even slightly.

As we stated in our prior blog post, the issues of copyrightability of Java software have been settled since the Federal Circuit Decision in 2014. It is Google that is urging the Supreme Court to draw some new magic line between some code that is copyrightable and other code that isn’t. Google tries to create a sense of urgency where none exists and controversy where there is actually agreement. And a close read of Google’s Amicus briefs reveals that Google appears to be virtually alone—at least among the technology community—in seeking to weaken copyright for software.

We also highlighted in our prior blog that Google has not provided a single real-world example of innovation that has suffered due to the pendency of this case. In the intervening six years since 2014—there is zero contemporaneous evidence where anyone has identified the Federal Circuit Decision as a barrier or impediment to innovation. Which is exactly why Google is receiving such scant support from tech.

When you look at the Amici filing on Google’s behalf what’s noteworthy is not who did file, but who didn’t. If you take a quick look at the top 100 technology companies, exactly two of them filed briefs on Google’s behalf. If you look at the leading Silicon Valley-based companies, exactly none of them filed briefs on Google’s behalf. And among the major technology trade associations, same answer. Not one.

This becomes much more significant when you understand just how hard Google tried to get the technology industry’s support… but it was not forthcoming.

The impression that tech is supporting Google comes primarily from the fact that Microsoft and IBM submitted Amicus briefs on Google’s behalf. We suppose one could argue that the Computer and Communications Industry Association (CCIA) also purportedly represents tech, but that’s not entirely correct (more on them later).

We should start by saying that Microsoft and IBM are entitled to their opinions and Microsoft and IBM are both great partners and strong competitors of Oracle. But the fact is both Microsoft and IBM have commercial interests in this litigation that require a little context and perspective.

We address Microsoft, IBM and CCIA, in turn.

Let’s start with Microsoft, the original sinner.

First, let’s go back to U.S. v. Microsoft. We would encourage everyone to take a look at the Findings of Fact in U.S. v. Microsoft, in particular Microsoft’s anticompetitive conduct against Sun’s Java. Long before Google broke Java’s interoperability, Microsoft did exactly the same thing. Java’s “write once, run anywhere” innovation was antithetical to maintaining Microsoft’s barrier of entry to its Windows monopoly. So, it made just enough changes and created just enough dependencies that applications written in Sun-compliant Java wouldn’t run on Windows. In other words, they broke Java’s interoperability. Sound familiar?

Second, Microsoft actually filed an Amicus brief on Oracle’s side of this matter in 2013 before the Federal Circuit. Likewise, Microsoft’s primary trade association, the Business Software Alliance also supported Oracle’s side before the Federal Circuit on both copyrightability and on fair use.

It is essential to read Microsoft’s new brief. How does it reconcile its previous position with its new position? It doesn’t. On the critical question of the copyrightability of “interfaces” (which Microsoft previously supported) Microsoft is now completely silent. Instead, its new brief focusses exclusively on fair use. So, does Microsoft support Google’s position of picking and choosing which lines of software code are copyrightable and which are not? The entirety of the public record is clear that Microsoft is actually on Oracle’s side of this critical component of the case.

Third, let’s not forget that Microsoft itself was once among Google’s strongest antagonists. Remember Microsoft’s 2013 “Scroogled” ad campaign against Google? As Ad Age put it, “the commercials are hard hitting, beating up Google for everything from invasive ads in Gmail to sharing data with app developers to placing paid results on its search page. Google, the ads claim, is "Scroogling" its users, or exploiting their private data to maximize advertising profits.”

Then came 2015 and the commercial agreement between Microsoft and Google to partner on, among other things, intellectual property. What changed Microsoft’s stance in this litigation was that commercial agreement. Microsoft’s position is as principled as that.

Now to IBM, before there was Codebreak (Houston Astros) there was Jailbreak.

Let’s start with the fact that IBM was silent both times Oracle v. Google was argued at the Federal Circuit. No Amicus Brief from IBM whatsoever on either copyrightability or fair use. It’s not as if IBM wasn’t paying attention or lacked the resources to participate. Moreover, IBM is an active member of the Business Software Alliance which, as we stated, filed a brief on both copyrightability and fair use in favor or Oracle’s position at the Federal Circuit. No objection from IBM.

Next, let’s not forget that IBM has been working to control Java since Java’s inception. In fact, as reported in the New York Times, IBM had negotiated a deal to buy Sun for $7 billion which would, among other things, “give I.B.M. more strength in competing against Oracle” and of course would have given them control of Java.

In the end, that transaction didn’t quite work out for IBM because Oracle acquired Sun, which Sun concluded would be a far better fit.

Third, IBM spent years trying to undermine Sun’s stewardship of Java in a scheme known in the industry as “Jailbreak.” Oracle knows well IBM’s efforts here because we were part of the community. The “Jailbreak” initiative as IBM named it was meant to pressure Sun and its leadership into changing the licensing regime for Java to something which would give IBM more control and the ability to “fork” Java for its own commercial purposes.

Ultimately IBM abandoned Jailbreak because it fully understood that creating a fork of Java would require a license from Sun. We were there at the time. Unlike Google, IBM never argued that some code was copyrightable but other code was not. Unlike Google, IBM never thought copying Java was permissible under fair use. IBM knew full well it needed a license and as a result it abandoned the Jailbreak effort.

So, after sitting silent before the Courts for a decade, with a clear understanding of what is permissible and what isn’t, why does IBM all of a sudden decide this case “threatens to undermine and adversely impact a core aspect of IBM’s… business” when it hadn’t at any point in the past?

Because IBM just completed its blockbuster $34 billion bet-the-company acquisition of Red Hat, and is simply running out of time. The stakes are now higher and IBM really wants its own, non-compatible version of Java for its own commercial purposes. After failing to acquire Sun or to Jailbreak Java, IBM is now turning to the Supreme Court in a Hail Mary attempt to get the Court to give it the control it couldn’t achieve in the market.

Again, IBM is entitled to its opinion. But IBM has a long history with Java, and we shouldn’t confuse IBM’s commercial and competitive interests with a new-found interest in the proper balancing of copyright protection for software.

Lastly, let’s address CCIA.

It is hardly a secret that CCIA is completely beholden to Google, financially and otherwise. Now, we don’t want to throw stones, but in a town where “pay to play” is commonplace, CCIA really sets a whole new standard for transactional advocacy. CCIA was founded on the principles of competition and interoperability and was one of the main protagonists in U.S. v. Microsoft, and before that AT&T and IBM. So how does CCIA go from fighting for interoperability to defending Google who admittedly broke interoperability with Java?

This author knows a little bit about the governance of CCIA because Oracle used to support CCIA and I sat on its Board of Directors during this formative about-face. The first step was Microsoft simply wrote CCIA’s CEO a check for $10 million (for his personal benefit, not the organization). Then Google stepped in and is now one of CCIA’s primary financial backers.

On the substance, CCIA’s argument essentially boils down to the point that the U.S. legal framework for IP should be more like Europe. If we care about innovation, we can’t imagine why anyone would trade the U.S. system, the global leader in technology, for Europe’s system, the global laggard? But CCIA is entitled to its views.

With that background, is it really credible to say that when 98 of the top 100 technology companies are absent; when Microsoft and IBM have substantial parochial agendas; and when nearly all of the major technology advocacy groups are silent, that “tech” is on Google’s side?

The answer is of course no. There is no outpouring of support for Google’s views.

And the reason is that despite the sky is falling rhetoric, this case is fact bound. Google stole and copied verbatim more than 11,000 lines of software code. It is now trying to define its’ copying out of the Copyright Act or alternatively claim fair use.

Nice new look and feel to spring.io web site!!!!

Pas Apicella - Sun, 2020-02-16 22:06
Seen the new look and feel for the spring.io? Worth a look

https://spring.io/



Categories: Fusion Middleware

Note to self for how to easily add user to sudoers file

Michael Dinh - Sun, 2020-02-16 07:49

Unable to sudo su -:

[grid@ol7-fpp-fpps ~]$ sudo su -

We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:

    #1) Respect the privacy of others.
    #2) Think before you type.
    #3) With great power comes great responsibility.

[sudo] password for grid:
grid is not in the sudoers file.  This incident will be reported.

[grid@ol7-fpp-fpps ~]$ 

Group wheel has ALL privileges:

[root@ol7-fpp-fpps ~]# grep wheel /etc/sudoers
## Allows people in group wheel to run all commands
%wheel  ALL=(ALL)       ALL
# %wheel        ALL=(ALL)       NOPASSWD: ALL
[root@ol7-fpp-fpps ~]#

Modify user grid:

[root@ol7-fpp-fpps ~]# usermod -aG wheel grid

Or: usermod -a -G sudo user

Test sudo -:

[grid@ol7-fpp-fpps ~]$ sudo su -
[sudo] password for grid:
Last login: Sun Feb 16 08:35:52 -05 2020 on pts/0
[root@ol7-fpp-fpps ~]#

[1Z0-238] Oracle EBS R12 Certified Specialist Everything You Must Know About

Online Apps DBA - Sun, 2020-02-16 02:29

The Oracle E-Business Suite R12 Applications Database Administrator Certified Professional Certification is designed for individuals who possess a strong foundation and expertise in implementing Oracle E-Business Suite solutions. This exam is required as part of earning the new Oracle EBS R12 Applications Database Administrator Certified Professional certification. Check Out K21 Academy’s blog post at https://k21academy.com/appsdba65 […]

The post [1Z0-238] Oracle EBS R12 Certified Specialist Everything You Must Know About appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Average of 0 and Value - gives incorrect output. Is there a way to ignore the 0 during the average function.

Tom Kyte - Fri, 2020-02-14 21:11
Hi Tom, I am having a SQL output as follows. <code>A B C D E ---------------- ---------- ---------- ------------ ----------- 2020-02-12 221 68677 99.6...
Categories: DBA Blogs

Indexing strategy for dates in a query

Tom Kyte - Fri, 2020-02-14 21:11
Hello, Ask Tom team. I have the following query: <code>SELECT guid, sender_id, doc, status, arrived_date, register_date, last_updated_date FROM user1.table1 WHERE (sender_id=:SENDER OR :SENDER IS NULL ) AND (status=:STATUS OR :status IS NU...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator