Why are backups of a previous incarnation marked obsolete without regard to redundancy?

For The Interested RMAN Beginners

The RMAN policies REDUNDANCY and RECOVERY WINDOW are mutually exclusive. This means you can either set one or the other.

Redundancy Policy

Having set REDUNDANCY 2 will always keep only the last two backups and deleting (or marking obsolete) any other previous backups, that are no longer required to bring back the database into a consistent state.

Note:
If you have LEVEL 1 and ARCHIVELOG backups in between the FULL and/or LEVEL 0 backups, then they should be retained until no longer required.

Additional Information

According to the PDF file Oracle Database - Backup and Recovery Reference your retention policy REDUNDANCY 2 should not have deleted your older backups:

  The following scenario illustrates how redundancy works in an incremental
  backup strategy. Assume that the redundancy level is 1. You run a level 0
  database backup at noon Monday, a level 1 cumulative backup at noon on
  Tuesday and Wednesday, and a level 0 backup at noon on Thursday.
  Immediately after each daily backup you run a DELETE OBSOLETE. The
  Wednesday DELETE command does not remove the Tuesday level 1 backup
  because this backup is not redundant: the Tuesday level 1 backup could be used
  to recover the Monday level 0 backup to a time between noon on Tuesday and
  noon on Wednesday. However, the DELETE command on Thursday removes the
  previous level 0 and level 1 backups.

The above example uses a value of 1 but is clearly stating that earlier backups of full backups are not deleted until they are larger than the value configured.

Answering your questions

It depends,  .... (see 2. and "Possible Issues")
According to the documentation the retention policy setting still seems to be the same for all versions:

Oracle 11c (11.1) : CONFIGURE
Oracle 11c (11.2) : CONFIGURE
Oracle 12c (12.1) : CONFIGURE
Oracle 12c (12.2) : CONFIGURE
Oracle 18c (18.x) : CONFIGURE

Possible Issues

You encountered a bug during the backup and restores of your database instance.

Reproducing with Oracle 12c

Because Oracle RDBMS 9i is pretty much obsolete and our environment is nearly up-to-date, I was only able to re-iterate/reproduce the steps in a 12c environment.

RMAN Configuration

RMAN was set up using the defaults:

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORACLE12JN are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE_12\PRODUCT\12.2.0\DBHOME_1\DATABASE\SNCFORACLE12JN.ORA'; # default

RMAN Backups

RMAN backups were performed by issuing a simple backup database; command:

RMAN> backup database;

Starting backup at 17-JUL-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\ORACLE_12\ORADATA\ORACLE12JN\SYSTEM01.DBF
input datafile file number=00005 name=C:\ORACLE_12\ORADATA\ORACLE12JN\UNDOTBS01.DBF
input datafile file number=00003 name=C:\ORACLE_12\ORADATA\ORACLE12JN\SYSAUX01.DBF
input datafile file number=00007 name=C:\ORACLE_12\ORADATA\ORACLE12JN\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 17-JUL-18
channel ORA_DISK_1: finished piece 1 at 17-JUL-18
piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNNDF_TAG20180717T151608_FNVV0RH4_.BKP tag=TAG20180717T151608 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 17-JUL-18

Starting Control File and SPFILE Autobackup at 17-JUL-18
piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\AUTOBACKUP\2018_07_17\O1_MF_S_981731783_FNVV17QR_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 17-JUL-18

Verifying Backups, Check Obsolete Backups and List Incarnations

After a certain period of backups had been performed I double-checked the RMAN catalog:

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
3       B  F  A DISK        2018-07-16 10:03:01 1       1       NO         TAG20180716T100301
5       B  F  A DISK        2018-07-16 10:27:44 1       1       NO         TAG20180716T102744
7       B  F  A DISK        2018-07-16 10:49:50 1       1       NO         TAG20180716T104950
9       B  F  A DISK        2018-07-16 10:50:08 1       1       NO         TAG20180716T105007
11      B  F  A DISK        2018-07-16 10:50:22 1       1       NO         TAG20180716T105022
13      B  F  A DISK        2018-07-16 10:51:21 1       1       NO         TAG20180716T105121
15      B  F  A DISK        2018-07-16 13:06:13 1       1       NO         TAG20180716T130612
17      B  F  A DISK        2018-07-16 15:26:23 1       1       NO         TAG20180716T152623
19      B  F  A DISK        2018-07-16 15:57:07 1       1       NO         TAG20180716T155707
20      B  0  A DISK        2018-07-17 08:22:01 1       1       NO         TAG20180717T082154
21      B  F  A DISK        2018-07-17 08:22:02 1       1       NO         TAG20180717T082202
22      B  0  A DISK        2018-07-17 14:36:07 1       1       NO         TAG20180717T143558
23      B  F  A DISK        2018-07-17 14:36:14 1       1       NO         TAG20180717T143614
24      B  F  A DISK        2018-07-17 15:16:15 1       1       NO         TAG20180717T151608
25      B  F  A DISK        2018-07-17 15:16:23 1       1       NO         TAG20180717T151623
26      B  A  A DISK        2018-07-17 15:55:52 1       1       NO         TAG20180717T155551
27      B  F  A DISK        2018-07-17 15:55:53 1       1       NO         TAG20180717T155553
28      B  F  A DISK        2018-07-17 16:05:17 1       1       NO         TAG20180717T160517

Verified that no obsolete backups were lying around:

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
no obsolete backups found

RMAN>

And listed the current incarnations:

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORACLE12 2083073378       PARENT  1          2017-03-08 15:57:31
2       2       ORACLE12 2083073378       CURRENT 1490582    2018-07-16 09:27:03

Perform First Restore / Recovery

The following commands were issued to restore/recover the database into a consistent state:

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    1610612736 bytes

Fixed Size                     8747368 bytes
Variable Size                385877656 bytes
Database Buffers            1207959552 bytes
Redo Buffers                   8028160 bytes

RMAN> run
2> {
3> set until time '2018-07-17 14:36:14';
4> restore database;
5> recover database;
6> }

executing command: SET until clause

Starting restore at 2018-07-17 16:04:30
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to C:\ORACLE_12\ORADATA\ORACLE12JN\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00003 to C:\ORACLE_12\ORADATA\ORACLE12JN\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00005 to C:\ORACLE_12\ORADATA\ORACLE12JN\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00007 to C:\ORACLE_12\ORADATA\ORACLE12JN\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T143558_FNVROH49_.BKP
channel ORA_DISK_1: piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T143558_FNVROH49_.BKP tag=TAG20180717T143558
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2018-07-17 16:04:38

Starting recover at 2018-07-17 16:04:38
using channel ORA_DISK_1

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

Finished recover at 2018-07-17 16:04:39

RMAN> alter database open resetlogs;

Statement processed

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORACLE12 2083073378       PARENT  1          2017-03-08 15:57:31
2       2       ORACLE12 2083073378       PARENT  1490582    2018-07-16 09:27:03
3       3       ORACLE12 2083073378       CURRENT 1701490    2018-07-17 16:05:13

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
3       B  F  A DISK        2018-07-16 10:03:01 1       1       NO         TAG20180716T100301
5       B  F  A DISK        2018-07-16 10:27:44 1       1       NO         TAG20180716T102744
7       B  F  A DISK        2018-07-16 10:49:50 1       1       NO         TAG20180716T104950
9       B  F  A DISK        2018-07-16 10:50:08 1       1       NO         TAG20180716T105007
11      B  F  A DISK        2018-07-16 10:50:22 1       1       NO         TAG20180716T105022
13      B  F  A DISK        2018-07-16 10:51:21 1       1       NO         TAG20180716T105121
15      B  F  A DISK        2018-07-16 13:06:13 1       1       NO         TAG20180716T130612
17      B  F  A DISK        2018-07-16 15:26:23 1       1       NO         TAG20180716T152623
19      B  F  A DISK        2018-07-16 15:57:07 1       1       NO         TAG20180716T155707
20      B  0  A DISK        2018-07-17 08:22:01 1       1       NO         TAG20180717T082154
21      B  F  A DISK        2018-07-17 08:22:02 1       1       NO         TAG20180717T082202
22      B  0  A DISK        2018-07-17 14:36:07 1       1       NO         TAG20180717T143558
23      B  F  A DISK        2018-07-17 14:36:14 1       1       NO         TAG20180717T143614
24      B  F  A DISK        2018-07-17 15:16:15 1       1       NO         TAG20180717T151608
25      B  F  A DISK        2018-07-17 15:16:23 1       1       NO         TAG20180717T151623
26      B  A  A DISK        2018-07-17 15:55:52 1       1       NO         TAG20180717T155551
27      B  F  A DISK        2018-07-17 15:55:53 1       1       NO         TAG20180717T155553
28      B  F  A DISK        2018-07-17 16:05:17 1       1       NO         TAG20180717T160517

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
no obsolete backups found

The restore was successful and no backups were marked as obsolete even though the incarnation changed after the restore and with the ALTER DATABASE OPEN RESETLOGS issued.

Alternate Incarnation Listing

An alternate representation of the incarnations can be achieved with the following command:

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
set pages 50
set lines 230
column Path format a20
select INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME,
PRIOR_INCARNATION#, STATUS, LEVEL, SYS_CONNECT_BY_PATH(INCARNATION#, ' -> ') Path
from v$database_incarnation
WHERE LEVEL >=1 START WITH INCARNATION# = '1'
CONNECT BY PRIOR INCARNATION# = PRIOR_INCARNATION#
order by Level, Path, RESETLOGS_TIME;

Output:

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      PRIOR_INCARNATION# STATUS       LEVEL PATH
------------ ----------------- ------------------- ------------------ ------- ---------- --------------------
           1                 1 2017-03-08 15:57:31                  0 PARENT           1  -> 1
           2           1490582 2018-07-16 09:27:03                  1 PARENT           2  -> 1 -> 2
           3           1701490 2018-07-17 16:05:13                  2 CURRENT          3  -> 1 -> 2 -> 3

The advantage of querying the local catalog in the database instance is the representation of the incarnation as a path to follow

Perform Second Restore / Recovery

The following commands were issued to restore/recover the database into a consistent state again:

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    1610612736 bytes

Fixed Size                     8747368 bytes
Variable Size                520095384 bytes
Database Buffers            1073741824 bytes
Redo Buffers                   8028160 bytes

RMAN> run
2> {
3> set until time '2018-07-17 08:22:01';
4> restore database;
5> recover database;
6> }

executing command: SET until clause

Starting restore at 2018-07-17 16:14:37
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/17/2018 16:14:37
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

Ok, we've hit an incarnation "bump" in the restore path. We are currently on incarnation 3 as can be seen in the incarnation listing below which I copied from above:

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      PRIOR_INCARNATION# STATUS       LEVEL PATH
------------ ----------------- ------------------- ------------------ ------- ---------- --------------------
           1                 1 2017-03-08 15:57:31                  0 PARENT           1  -> 1
           2           1490582 2018-07-16 09:27:03                  1 PARENT           2  -> 1 -> 2
           3           1701490 2018-07-17 16:05:13                  2 CURRENT          3  -> 1 -> 2 -> 3

...and the backup we are aiming for is after the RESETLOGS for incarnation 2. Let's reset the incarnation to 2 and proceed with the backup:

RMAN> reset database to incarnation 2;

database reset to incarnation 2

RMAN>

Seems to work. Let's restart the restore/recover again:

RMAN> run
2> {
3> set until time '2018-07-17 08:22:01';
4> restore database;
5> recover database;
6> }

executing command: SET until clause

Starting restore at 2018-07-17 16:16:12
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to C:\ORACLE_12\ORADATA\ORACLE12JN\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00003 to C:\ORACLE_12\ORADATA\ORACLE12JN\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00005 to C:\ORACLE_12\ORADATA\ORACLE12JN\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00007 to C:\ORACLE_12\ORADATA\ORACLE12JN\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T082154_FNV2R328_.BKP
channel ORA_DISK_1: piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T082154_FNV2R328_.BKP tag=TAG20180717T082154
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2018-07-17 16:16:20

Starting recover at 2018-07-17 16:16:20
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\ARCHIVELOG\2018_07_17\O1_MF_1_3_FNVXC7R7_.ARC
archived log file name=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\ARCHIVELOG\2018_07_17\O1_MF_1_3_FNVXC7R7_.ARC thread=1 sequence=3
media recovery complete, elapsed time: 00:00:00
Finished recover at 2018-07-17 16:16:21

RMAN> alter database open resetlogs;

Statement processed

RMAN> report obsolete
2> ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
no obsolete backups found

RMAN>

The restore was successful and no backups were marked as obsolete even though the incarnation changed after the restore and the ALTER DATABASE OPEN RESETLOGS was issued again. Now we are on a newer incarnation and still no Obsolete Backups reported.

List of Current Incarnations After Second Restore (SQL)

SQL> select INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, PRIOR_INCARNATION#, STATUS, LEVEL, SYS_CONNECT_BY_PATH(INCARNATION#, ' -> ') Path from v$database_incarnation WHERE LEVEL >=1 START WITH INCARNATION# = '1' CONNECT BY PRIOR INCARNATION# = PRIOR_INCARNATION# order by Level, Path, RESETLOGS_TIME;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      PRIOR_INCARNATION# STATUS       LEVEL PATH
------------ ----------------- ------------------- ------------------ ------- ---------- --------------------
           1                 1 2017-03-08 15:57:31                  0 PARENT           1  -> 1
           2           1490582 2018-07-16 09:27:03                  1 PARENT           2  -> 1 -> 2
           3           1701490 2018-07-17 16:05:13                  2 ORPHAN           3  -> 1 -> 2 -> 3
           4           1691865 2018-07-17 16:16:29                  2 CURRENT          3  -> 1 -> 2 -> 4

Initiate Backup

After restoring the second time let's backup the database again:

RMAN> backup database;

Starting backup at 2018-07-17 16:51:39
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\ORACLE_12\ORADATA\ORACLE12JN\SYSTEM01.DBF
input datafile file number=00005 name=C:\ORACLE_12\ORADATA\ORACLE12JN\UNDOTBS01.DBF
input datafile file number=00003 name=C:\ORACLE_12\ORADATA\ORACLE12JN\SYSAUX01.DBF
input datafile file number=00007 name=C:\ORACLE_12\ORADATA\ORACLE12JN\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 2018-07-17 16:51:39
channel ORA_DISK_1: finished piece 1 at 2018-07-17 16:51:46
piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNNDF_TAG20180717T165139_FNW0MVRT_.BKP tag=TAG20180717T165139 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 2018-07-17 16:51:46

Starting Control File and SPFILE Autobackup at 2018-07-17 16:51:46
piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\AUTOBACKUP\2018_07_17\O1_MF_S_981737506_FNW0N31S_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 2018-07-17 16:51:47

RMAN>

Check Obsolete

We'll now check which (backup) files have become obsolete:

RMAN> report obsolete
2> ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           20     2018-07-17 08:22:01
  Backup Piece       20     2018-07-17 08:22:01 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T082154_FNV2R328_.BKP
Backup Set           22     2018-07-17 14:36:07
  Backup Piece       22     2018-07-17 14:36:07 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T143558_FNVROH49_.BKP
Backup Set           24     2018-07-17 15:16:15
  Backup Piece       24     2018-07-17 15:16:15 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNNDF_TAG20180717T151608_FNVV0RH4_.BKP
Archive Log          2      2018-07-17 15:55:51 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\ARCHIVELOG\2018_07_17\O1_MF_1_3_FNVXC7R7_.ARC
Backup Set           26     2018-07-17 15:55:52
  Backup Piece       26     2018-07-17 15:55:52 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_ANNNN_TAG20180717T155551_FNVXC84F_.BKP
Archive Log          3      2018-07-17 16:05:13 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\ARCHIVELOG\2018_07_17\O1_MF_1_4_FNVXWSDY_.ARC

RMAN>

Check Incarnations (SQL)

SQL> select INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, PRIOR_INCARNATION#, STATUS, LEVEL, SYS_CONNECT_BY_PATH(INCARNATION#, ' -> ') Path from v$database_incarnation WHERE LEVEL >=1 START WITH INCARNATION# = '1' CONNECT BY PRIOR INCARNATION# = PRIOR_INCARNATION# order by Level, Path, RESETLOGS_TIME;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      PRIOR_INCARNATION# STATUS       LEVEL PATH
------------ ----------------- ------------------- ------------------ ------- ---------- --------------------
           1                 1 2017-03-08 15:57:31                  0 PARENT           1  -> 1
           2           1490582 2018-07-16 09:27:03                  1 PARENT           2  -> 1 -> 2
           3           1701490 2018-07-17 16:05:13                  2 ORPHAN           3  -> 1 -> 2 -> 3
           4           1691865 2018-07-17 16:16:29                  2 CURRENT          3  -> 1 -> 2 -> 4

As can be seen the incarnation 3 is now orphaned, because its direct line of heritage in regards to the current state of the database is broken. After the first restore we went back in time an re-restored the database again which results in the incarnation path 1 -> 2 -> 4 being the direct line of current ancestors for the open database.

Because the direct line for restoring the current database is along the incarnations of 1, 2, 4 there is no need for RMAN to keep the obsolete backups listed above. They can safely be deleted.

Delete Obsolete

Let's go ahead and delete the obsolete backups:

RMAN> delete obsolete
2> ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           20     2018-07-17 08:22:01
  Backup Piece       20     2018-07-17 08:22:01 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T082154_FNV2R328_.BKP
Backup Set           22     2018-07-17 14:36:07
  Backup Piece       22     2018-07-17 14:36:07 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T143558_FNVROH49_.BKP
Backup Set           24     2018-07-17 15:16:15
  Backup Piece       24     2018-07-17 15:16:15 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNNDF_TAG20180717T151608_FNVV0RH4_.BKP
Archive Log          2      2018-07-17 15:55:51 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\ARCHIVELOG\2018_07_17\O1_MF_1_3_FNVXC7R7_.ARC
Backup Set           26     2018-07-17 15:55:52
  Backup Piece       26     2018-07-17 15:55:52 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_ANNNN_TAG20180717T155551_FNVXC84F_.BKP
Archive Log          3      2018-07-17 16:05:13 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\ARCHIVELOG\2018_07_17\O1_MF_1_4_FNVXWSDY_.ARC
Backup Set           30     2018-07-17 16:17:52
  Backup Piece       30     2018-07-17 16:17:52 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNNDF_TAG20180717T161743_FNVYN815_.BKP

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T082154_FNV2R328_.BKP RECID=20 STAMP=981706915
deleted backup piece
backup piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T143558_FNVROH49_.BKP RECID=22 STAMP=981729359
deleted backup piece
backup piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNNDF_TAG20180717T151608_FNVV0RH4_.BKP RECID=24 STAMP=981731768
deleted archived log
archived log file name=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\ARCHIVELOG\2018_07_17\O1_MF_1_3_FNVXC7R7_.ARC RECID=2 STAMP=981734151
deleted backup piece
backup piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_ANNNN_TAG20180717T155551_FNVXC84F_.BKP RECID=26 STAMP=981734152
deleted archived log
archived log file name=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\ARCHIVELOG\2018_07_17\O1_MF_1_4_FNVXWSDY_.ARC RECID=3 STAMP=981734713
deleted backup piece
backup piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNNDF_TAG20180717T161743_FNVYN815_.BKP RECID=30 STAMP=981735464
Deleted 7 objects

List Backup Summary

Now that we have deleted some backup files and RMAN has performed some internal cleaning, let's have a look at the backup summary:

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
3       B  F  A DISK        2018-07-16 10:03:01 1       1       NO         TAG20180716T100301
5       B  F  A DISK        2018-07-16 10:27:44 1       1       NO         TAG20180716T102744
7       B  F  A DISK        2018-07-16 10:49:50 1       1       NO         TAG20180716T104950
9       B  F  A DISK        2018-07-16 10:50:08 1       1       NO         TAG20180716T105007
11      B  F  A DISK        2018-07-16 10:50:22 1       1       NO         TAG20180716T105022
13      B  F  A DISK        2018-07-16 10:51:21 1       1       NO         TAG20180716T105121
15      B  F  A DISK        2018-07-16 13:06:13 1       1       NO         TAG20180716T130612
17      B  F  A DISK        2018-07-16 15:26:23 1       1       NO         TAG20180716T152623
19      B  F  A DISK        2018-07-16 15:57:07 1       1       NO         TAG20180716T155707
21      B  F  A DISK        2018-07-17 08:22:02 1       1       NO         TAG20180717T082202
23      B  F  A DISK        2018-07-17 14:36:14 1       1       NO         TAG20180717T143614
25      B  F  A DISK        2018-07-17 15:16:23 1       1       NO         TAG20180717T151623
27      B  F  A DISK        2018-07-17 15:55:53 1       1       NO         TAG20180717T155553
28      B  F  A DISK        2018-07-17 16:05:17 1       1       NO         TAG20180717T160517
29      B  F  A DISK        2018-07-17 16:16:34 1       1       NO         TAG20180717T161634
31      B  F  A DISK        2018-07-17 16:17:59 1       1       NO         TAG20180717T161759
32      B  F  A DISK        2018-07-17 16:51:46 1       1       NO         TAG20180717T165139
33      B  F  A DISK        2018-07-17 16:51:47 1       1       NO         TAG20180717T165146
34      B  F  A DISK        2018-07-18 07:24:13 1       1       NO         TAG20180718T072405
35      B  F  A DISK        2018-07-18 07:24:21 1       1       NO         TAG20180718T072421

RMAN>

As we can see we still have "at least" two complete backup  that will allow us to restore the database two backups in the past. RMAN (in 12c) did not delete any other backups along the incarnation path or outside of the orphaned incarnation.

Conclusion

Regarding the deleted backups after your initial restore in 9i I believe there are two possible scenarios:

You hit a bug.
After the restore two (FULL) backups were initiated which resulted in the backups being deleted on tape.

Reference Material

Doc ID 206862.1 (How does the RMAN Retention Policy Obsolete Incremental Backupsets and Archivelog Files)

Valid for Oracle 9i+


Which database is more suitable for executing user submitted queries?

I can only comment on the (never ending) MySQL vs. Postgres question. I have never used MongoDB.

  more control over each individual query

Not sure what you mean with that. If that refers to limiting resource consumption then I don't think any of those three products will help you with that.

If that is about the possibilities you have when writing queries, then Postgres is the clear choice for that. It has a lot more modern SQL features then MySQL does. My understanding is that MongoDB's aggregation capabilities are still not on the same level as those available in modern relational databases - but I have never used MongoDB so I can't tell.

  more sophisticated user-based permissions and access control system

Postgres has definitely the more sophisticated permission systems with user groups and row level security.

  and a better built-in performance overload handling in case of many small queries or few huge queries that can potentially crash the database

To my knowledge, neither Postgres nor MySQL have any built-in features to limit resource consumption of individual queries like e.g. Oracle or SQL Server offer (apart from things like global statement timeouts). But as I don't really use MySQL any more there might be something I don't know about.


How to control execution plans in SQL Server

1) Can I prevent degradation of execution plans from second stage to
  first, so I always have fixed execution plan for each stored
  procedure? I dont have enough evidences, but I feel like sometimes sql
  server decides to recompile execution plans, and if they are bad my
  app performance drops significantly.

The main things to watch out for here are statistics getting stale and parameter sniffing. Without going into too much depth here are some ways to combat this.

Statistics out of date

Enabling trace flag 2371 (for databases below compatibility level 130) as to lower the modification threshold when statistics are updated.
Updating your statistics daily / weekly
Changing the sample rate of your stat updates (and possibly persisting these rates Starting from SQL Server 2016 SP1 CU4 / SQL Server 2017 CU1)

Parameter sniffing

Sometimes SQL Server needs to create a new plan due to statistic updates, the plan was removed from cache, etc..

Sometimes this plan is not optimal for other parameters.

One solution for this is adding the OPTION(RECOMPILE) hint to your query as to increase cpu usage by compiling the plan on each execution, but getting a better plan for the parameters specified. This is mostly dependent on the amount of times the query is executed.

  2) Is the third option is a right way to grantee that I would have
  only one execution of fnLatestDirectoryData?

The executions

The executions are corresponding to normal nested loop join operator behaviour.
Finding matches by using a row from the outer input, these executions should match the amount of rows in the outer input (ignoring special cases like nested loop prefetching)

This does not mean that the function is called these 269 times, as the inline table valued function is expanded into the query plan, behaviour you would also see in views.

Reliability of the hint

The hint makes the optimizer create a plan with a HASH JOIN, and that is the only guarantee.

  3) Can I reliably fix the order of joins? I see joins happen in
  different order, so if I do something like group them in pair (select
  (select from tableA join tableB) join tableC) it will actually help.

The brackets

Splitting up the joins with brackets should not do anything as the optimizer removes them and optimizes the query the same way.

Forcing the order

If you really  want to force the order in which the joins are specified, OPTION(FORCE ORDER) can be used or SET FORCEPLAN ON.

Splitting up the query

Another method could be inserting the result of a part of the query into a temporary table, and joining the rest of the query to this temp table.

More information (as of time of  writing)

Without more information about what is going inside the function, and the query plan it is hard to say. An index or query adaption could very well resolve your issue.


Table Valued Parameter not pulling the correct data for my sp?

I'm going to suggest working to the solution in two phases. The first part is to make sure all the data you need is being returned. The second part is to combine the rows the way that you want.

I'm having trouble getting SqlFiddle to work with table types as well, so I'm going to work with the query directly. Here is the base query from above:

;WITH f AS
(
SELECT FacilityID FROM @facilities
UNION ALL SELECT @facilitydivisionID
)
SELECT
  di.DocumentNumber, di.DocumentVersion, di.DocumentTitle,tsl.TransmittalSiteLeadFacility,
  ts.TransmittalNumber, ts.TransmittalVersion,
  FacilityImpDate = COALESCE(tsl.TransmittalSiteLeadImpDate, ts.TransmittalImpDate)
FROM
  TransmittalSiteLead tsl
  INNER JOIN TransmittalSheet as ts
    ON ts.TransmittalID =   tsl.TransmittalSiteLeadTSID
  INNER JOIN documentInfo as di
    ON ts.transmittalID = di.documentTransmittalImp
  INNER JOIN f
    ON tsl.TransmittalSiteLeadFacility = f.FacilityId
WHERE
  transmittalSiteLeadImpDate
    BETWEEN  @startdate AND @enddate

Even if there are duplicates or extra information, all of the data you want to return should be in there. If it isn't, then look at the JOINs or WHERE clause to see how to include that info.

The second part of the problem is to condense or limit the rows that are returned. There are a lot of ways to do this and the best way depends on a lot of factors. Here's what I'm sure is a partial list:

SELECT DISTINCT to limit results
GROUP BY with MIN to include information about a related table (See below)
EXISTS to limit the rows return
ROW_NUMBER() to pick a row based on an ordering (See below)
APPLY with TOP to get some information from a joined table

All this is to say that there's a lot of ways to do this, so do what makes sense for your situation. I've given two ways of doing this below, these aren't best for performance critical situations, but are easier to understand.

This query selects the document info and the staff id of someone who is implementing it.

;WITH f AS
(
SELECT FacilityID FROM @facilities
UNION ALL SELECT @facilitydivisionID
)
SELECT
  di.DocumentNumber
  ,di.DocumentVersion
  ,di.DocumentTitle
  ,MIN(tsl.TransmittalSiteLeadStaffId) AS TransmittalSiteLeadStaffId
FROM
  TransmittalSiteLead tsl
  INNER JOIN TransmittalSheet as ts
    ON ts.TransmittalID =   tsl.TransmittalSiteLeadTSID
  INNER JOIN documentInfo as di
    ON ts.transmittalID = di.documentTransmittalImp
  INNER JOIN f
    ON tsl.TransmittalSiteLeadFacility = f.FacilityId
WHERE
  transmittalSiteLeadImpDate
    BETWEEN  @startdate AND @enddate
GROUP BY
    di.DocumentNumber
    ,di.DocumentVersion
    ,di.DocumentTitle
ORDER BY
    di.DocumentNumber

Another way of doing it is using ROW_NUMBER. This way is very flexible, but can be more difficult to construct and understand.

This query selects matching documents and the facility and contact of the first to implement it. PARTITION sets how you want to divide the rows and ORDER BY sets how you want to select which matching row you want to use.

;WITH f AS
(
SELECT FacilityID FROM @facilities
UNION ALL SELECT @facilitydivisionID
),
FullData AS (
    SELECT
      di.DocumentNumber
      ,di.DocumentVersion
      ,di.DocumentTitle
      ,tsl.TransmittalSiteLeadFacility
      ,tsl.TransmittalSiteLeadStaffId
      ,ROW_NUMBER() OVER (PARTITION BY di.DocumentNumber ORDER BY tsl.TransmittalSiteLeadImpDate) AS RowNum
    FROM
      TransmittalSiteLead tsl
      INNER JOIN TransmittalSheet as ts
        ON ts.TransmittalID =   tsl.TransmittalSiteLeadTSID
      INNER JOIN documentInfo as di
        ON ts.transmittalID = di.documentTransmittalImp
      INNER JOIN f
        ON tsl.TransmittalSiteLeadFacility = f.FacilityId
    WHERE
      transmittalSiteLeadImpDate
        BETWEEN  @startdate AND @enddate
)
SELECT
    DocumentNumber
    ,DocumentVersion
    ,DocumentTitle
    ,TransmittalSiteLeadFacility
    ,TransmittalSiteLeadStaffId
FROM
    FullData
WHERE
    RowNum = 1
ORDER BY
    DocumentNumber


Impact of replication compression & encryption on performance, traffic?

I like this presentation by Giuseppe Bianchi. Starting on page 5 it contains a desctiption of the TLS protocol - segment size, header size, HMAC overhead. As for the handshake, the impact on replication should be negligible. It will only occur on connection, and there may be a key exchange going on every hour, depending on the configuration.
As for the compression, I'd go with what the book says. Or you can try gzipping your binlog file, as the ratio will likely be application-specific. (This may not be accurate if the compression is initialized per packet, not once for the replication stream.)
On performance - type "openssl speed" in the command line, you'll get a benchmark of the throughput of different ciphers on your computer. Find the relevant ones and calculate the utilization given the throughput you expect from the replication stream.


How to get multiple row data into a row with multiple columns

You must Pivot data using GROUP BY with MAX aggregate and use CASE to filter by User_id.
Query:
SELECT User_id
    , MAX(
        CASE WHEN course_id = 1 THEN course_location END
    ) as Course_1
    , MAX(CASE WHEN course_id = 2 THEN course_location END) as Course_2
    , MAX(CASE WHEN course_id = 3 THEN course_location END) as Course_3
    , MAX(CASE WHEN course_id = 4 THEN course_location END) as Course_4
FROM data
GROUP BY User_id;

Sample query in SQL Fiddle.
You can replace course_location by CONCAT('YES-', course_location) is the leading YES is indeed needed.
Output:
User_Id | Course_1      | Course_2      | Course_3      | Course_4
1       | location 1    | location 2    | location 1    | (null)
2       | (null)        | location 1    | (null)        | location 4


Here's a short step-by-step walkthrough:

disconnect users and disable incoming connections to the database
make a copy of database file (or two copies) and work on that
use GFIX with -v option to validate the database file
use GFIX with -v and -f to do full validation

If problem is not too serious, you can try to backup the broken db and restore under a new name:

use GFIX -mend to prepare corrupt database for backup
use GBAK -b -g to backup the database. -g disables garbage collection
use GBAK -c to restore backup to a new database.

If you succeed, you have fixed the problem and have a functional database. If not, you can try to create an empty database with the same structure and pump the data to it .

One of the reasons why backup or restore can fail is if some broken database triggers exist, and prevent connection to the database. For example, a database trigger might use some table which has a broken index, etc. To work around this, connect to database with isql tool using -nodbtriggers option and then disable those triggers. You can enable them later when you fix other problems and get a working database again.

Another reason restore might fail is when you have broken data, so some of validity constraints (check constraints, etc.) cannot be satisfied. In this case, you can try to restore your database using -N[O_VALIDITY] command switch to gbak.

If you're interested in a more detailed information of the process of fixing the database, as well as explanation of some types of corruption, take a look at the following page:

http://www.ibphoenix.com/resources/documents/search/doc_5

If all fails, you can try IBSurgeon tool, which is able to fix most problems and extract data. Also, IBSurgeon's website has a detailed
explanation of causes of database corruption and ways to fix it:

http://ib-aid.com/option,com_content/task,view/id,58/Itemid,62/

check this guide http://www.firebirdfaq.org/faq324/


Window functions , for instance LAG should do the trick:

SELECT run_date, count(distinct car_id)
  FROM
  (
   SELECT id as car_id, date_trunc('day', (time::timestamp) AT TIME ZONE 'EST') as run_date,
   battery - LAG(battery,1) OVER (partition by id,date_trunc('day', (time::timestamp) AT TIME ZONE 'EST')
     order by time) as diff
   from test_db
  )a
  WHERE diff >0
  GROUP BY run_date


PARTITION is unlikely to help.

CHAR(32) -- is it utf8?  If so, then it is taking 96 bytes; what a waste.  And it is hex, correct?  Consider converting to binary and using BINARY(16) to save space.  Note that (in InnoDB) the PRIMARY KEY is included in each secondary key.  So, 8 indexes times (96-16) bytes = 640 bytes per row saved.

In MySQL there are HEX() and UNHEX().  Your programming language probabls has something.

Yes, do consider "vertical partitioning" to move the body to another table (body + id; primary key is id):

Searching in the main table will be faster because of not tripping over the bodies.
The JOIN to get the body won't be bad, especially since you won't be fetching many bodies at once.
Consider making the body a MEDIUMBLOB and compressing (in the client) the text.  This will give you a big savings in disk space.  (And it is the only significant saving.)  Text typically compresses 3:1.

Make a decision soon.  Any change is likely to need both the old copy of the bodies and the new during the conversion.  The 50% is scary.

If you have 8 indexes, are any of them "composite"?  Are any just on a flag?  Please provide SHOW CREATE TABLE.


How to get list of enabled/disabled features of SQLAnywhere

You don't say which version of SQL Anywhere you are using but for v12.0.1 you can execute
SELECT PROPERTY ( 'ServerEdition' ); The result is explained in the docs thus:

  ServerEdition

  Returns a space-separated list of words describing the database server type.
  Values include:

Evaluation
Developer
Web
Educational
Standard
Advanced
Workgroup
OEM
Authenticated

  If you have a separate license for any of the following features, then the appropriate string(s) are added to the license string that is returned:

HighAvailability   See SQL Anywhere high availability option.
InMemory           See SQL Anywhere in-memory mode option.
ECC                See SQL Anywhere security option.
FIPS               See SQL Anywhere security option.

You can get more help at the dedicated SQL Anywhere forum http://sqlanywhere-forum.sap.com/


Why do database servers have different time_source values

According to MSDN, SQL Server uses the following logic to decide which timer to use:

Windows does not have an API to indicate what source QueryPerformanceCounter is using at this time so SQL Server 2008 R2 startup times the invocation of QueryPerformanceCounter (10 times) and when it exhibits repeated, small cycle behavior (< 600 cycles) the QueryPerformanceCounter will be used to accept invariant TSC timings instead of the GetTickCount/timeBeginPeriod interrupt timing behavior.   If the timing exceeds 600 cycles the multi-media timer behavior of SQL Server is used instead.
Note:  Windows is exposing the timer source API for QueryPerformanceCounter in a Windows 2008 R2 based fix so the timing source determination by SQL Server will be replaced with the API call in a future build.  Target releases are the SP1 of Windows 2008 R2 and SQL Server 2008 R2.

The above explanation indirectly answers your questions:

SQL Server uses an internal process to determine which timer to use.
You cannot tell SQL Server 2008 R2 which timer to use, other than by using trace flags 8049 and 8038.  In my testing, neither of those trace flags made any difference to the timer_source column of sys.dm_os_sys_info.

We rebooted the majority of our servers over the weekend.  I compared the list of machines using MULTIMEDIA_TIMER from before and after the reboot.  I can now report that some of the servers that were using the MULTIMEDIA_TIMER are now using QUERY_PERFORMANCE_COUNTER and vice-versa.
Since our servers are virtualized, I presume simultaneously booting machines in our virtualized environment is resulting in some machines taking longer than 600 CPU cycles to return a value through the QueryPerformanceCounter API.  This effect appears to be randomly distributed across our environment.


Please refer to the official documentation on how to create, store and restore a full backup with Percona XtraBackup. Percona XtraBackup does guarantee having everything for no data loss:

It can be summarized as:

Perform the backup (innobackupex). You can optionally send it remotely, compress it, encrypt it, etc. at the same time the backup is running. This is the only point where you need access to the original database server.
If you did not send it remotely on the first step, do it now. A local backup, while useful, is not a real backup strategy. For the backup to be complete, you may want to copy also your config file (/etc/my.cnf and your binary executables, if you cannot get them from other source -your distro, for example).
At any time between the backup and the recovery process, you need to do the prepare phase (innobackupex --apply-log). This only requires the backup files, not the original server. This will create the transaction logs for you
After you have applied it, just copy back the files to the original directory while the server is down. You can do that with innobackupex --copy-back if you have the files locally, or just do (s)cp/rsync. Remember to change the file permissions to the appropriate user (usually the mysql user).
Start the server. You are done.

This will restore a full backup. To assure you this is a secure method, Galera cluster can use it to clone nodes automatically (without human intervention) and it is the safest and fastest method available.

If you do not see individual .ibd files, it could be for several reasons- either you are using MyISAM or another engine that do not use those, or you are using InnoDB with innodb_file_per_table = 0, or you lost some of the files on the copy process (or they have wrong permissions). Please provide the full error message and the error log since the last start, and a recursive listing of your datadir.

As the writer of the training for Percona XtraBackup, I am quite familiar with all the typical silly mistakes we do when working with it.

Thank you for the extra information, here are my suggestions as a follow up:

The latest versions of XtraBackup are not compatible with 5.1 without the innodb plugin. Are you using MySQL 5.1 with stock InnoDB (not the plugin, or Percona/MariaDB)? Also, recovering to a newer version of MySQL in binary form may require additional upgrade steps/precautions- it can be done in binary form. Suggestion: try using an older version of Xtrabackup or upgrading the server. 5.1 without the plugin is a 6-year-old release with very little support everywhere. I can also provide alternative backup tools if you want to stick to <5.1 (but very few are so fast).
I do not like the database/table name- just to double check, can you confirm that db: lead@002dmanager@002dbackup table: crm_word_ban is the right name for one of your tables, and that it is the exact name of a .frm file? I want to discard filesystem issues.


Constraint on row value based on values in other rows

As @NeilMcGuigan suggested, an Exclusion Constraint is the key here.  The part I was unfamiliar with was the ability to use the <> operator.  As available post Postgres 9.0:

CREATE TABLE accrual (
    add_date date NOT NULL,
    user_id integer NOT NULL,
    rate integer NOT NULL,
    amount numeric(7,3),
    EXCLUDE USING gist
        (date WITH =,
        user_id WITH =,
        rate WITH <>)
);

Two helpful links are:

http://www.tutorialspoint.com/postgresql/postgresql_constraints.htm -- a short tutorial on constraints in Postgres, with an example for the Exclusion constraint that uses the <> operator

and

http://www.slideshare.net/pgconf/not-just-unique-exclusion-constraints (specifically slide 23) -- a presentation on the need for and use of Exclusion constraints.  Slide 23 introduces the <> operator and its usefulness.


How to alter tables safely to set row_format as Compressed or Dynamic in Mariadb Galera Cluster?

Since you are not really changing any data nor the schema, just the underlying row_format, seems like RSU would be better, and a lot less invasive.

You might also take each node out of the cluster while doing the ALTER, or at least remove the one node from the proxy's list.  This should avoid letting queries hit the table.

I feel pretty sure that the table will be blocked from writes and possibly reads during the conversion.

It would be wise to test this on an offline cluster, including testing reads/writes.


700GB is too much for mysqldump. Restore will take too much time (but I would measure to know numbers). XtraBackup will be faster.

Taking backup from a slave is OK, but you have to make sure the master and slave are in sync. Use pt-table-checksum to verify that.

And what is missing in your strategy is backups verification. If you don't verify backups you don't have backups.


How can I restore SQL Server database to a custom location, if the backup file was renamed?

You can use the filelistonly option to retrieve the files names in a backup:

restore filelistonly from disk='c:\mumu.bak'


You can use the generate_series() set returning function:

INSERT INTO bins (id)
SELECT g.id
FROM generate_series(1, 10000) AS g (id) ;


How do I list all databases and tables using psql in a pipable format?

psql -q -A -t -c "SELECT datname FROM pg_database" does the trick.


A trigger is executed as result of a certain event (e.g insert/update/delete on a table). You need something like a job scheduler.

You did not specified the database. By example Oracle has something like:

DBMS_SCHEDULER.CREATE_JOB(....


"Tuples in a weak entity set are partitioned according to their relationship with tuples in a strong entity" - meaning?

I believe what it's trying to say is because weak entity sets don't have their own primary keys, they are dependent on the strong entity sets that they have a foreign key relationship with (because those related strong entity sets do have primary keys).
So in other words, a tuple in a weak entity set will be partitioned (split up) from other tuples in that same weak entity set by their related strong entity set tuples.
I'm assuming the purpose of weak entity sets is a form of normalization by breaking related fields out of the strong entity set into a separate related set all together (a weak entity set without a primary key).


column name as variable in sql statement

SELECT CASE another_table.field
           WHEN 'A' THEN main_table.field_A
           WHEN 'B' THEN main_table.field_B
           WHEN 'C' THEN main_table.field_C
           WHEN 'D' THEN main_table.field_D
           ELSE NULL
       END, ...


Sql Server Management Studio can't remember passwords

As said in the link from comment, It's a bug in SQL Server Management Studio

SQL Server 2012 Management Studio spuriously forgets password. and SQL Server Management studio solution explorer doesn't remember SQL authentication password

Some alternate solutions may work for you.


Building year to date aggregations using SQL

For SQL Server 2012, given this sample data:

CREATE TABLE #t(TranID INT, DateCode DATE, Account INT, Value INT);
CREATE CLUSTERED INDEX x ON #t(Account, DateCode);

INSERT #t VALUES(1,'20140101',1,5 ),(2,'20140106',1,-3),(3,'20140207',1,6 ),
(4,'20140409',1,3 ),(5,'20140103',2,3 ),(6,'20140215',2,7 ),(7,'20140519',2,6 );

I would use this query (imagine the first line as stored procedure parameters):

DECLARE @year INT = 2014, @cutoff DATE = NULL; -- just state the year

-- and optionally an explicit cutoff date (leave NULL for today)
SET @cutoff = '20140731';

DECLARE @startdate DATE = DATEADD(YEAR, @year-1900, 0);

;WITH accounts(a) AS
(
  SELECT DISTINCT Account FROM #t
    WHERE DateCode >= @startdate
    AND DateCode < DATEADD(YEAR, 1, @startdate)
),
months(m) AS
(
  SELECT TOP (12) DATEADD(MONTH, ROW_NUMBER() OVER
      (ORDER BY s.[object_id])-1, @startdate)
    FROM sys.all_objects AS s
),
s AS
(
  SELECT a.a, m.m, v = COALESCE(SUM(t.Value),0)
  FROM accounts AS a
  CROSS APPLY months AS m
  LEFT OUTER JOIN #t AS t
    ON t.DateCode >= m.m
    AND t.DateCode < DATEADD(MONTH, 1, m.m)
    AND t.Account = a.a
  WHERE m.m < COALESCE(@cutoff, SYSDATETIME())
  GROUP BY a.a, m.m
)
SELECT
  MonthID = m,
  Account = a,
  YTD = SUM(v) OVER(PARTITION BY a ORDER BY m ROWS UNBOUNDED PRECEDING)
FROM s
ORDER BY a,m;

If you are using a version older than SQL Server 2012, then you may want to try some of the alternative approaches here:

Best approaches for grouped running totals


MySQL DB design for very long growing row size

So the pieces are there, but not structured in a way for a database to quickly access the data you need, or to allow growth without major restructuring.
In your case the best way to do that is to arrange the data in a longer format instead of the wide format you have.  So instead of having each column represent an entity identifiable only through a metadata table, you will have a key identify each row and the information will be organized by that key/timestamp combination.  Not only will this allow your database to grow without issue, you can also add new entities through inserting rows, not rebuilding your main table.  It's also much faster for most queries.
After discussion in chat, this is the model I believe this is a good starting point for your data. (For anyone not familiar with ER Diagrams, I always recommend reading this first).

This may require some tweaks as you go along, but the general structure should be there.
Note: The current diagram allows the same ModelCode to be used with more than one Instrument.  This is useful if the code is easily understood to mean a certain type of model or prediction.  If that is the case, adding a ModelType entity to give additional meaning/integrity to ModelCode would be desirable.  But if you want ModelCode to be unique across all instruments, you would need to place an alternate key (unique constraint) on that column.


Changes to the database schema are called migrations, what are data changes called?

Check out DML - Data Manipulation Language. This includes CRUD operations (Create (i.e. INSERT - not to be confused with the CREATE TABLE statement below), Read (i.e. SELECT), Update, Delete) on Tables. It's what people associate most classically with SQL.

Compare this with DDL - Data Definition Language. DDL involves the CREATE TABLE and ALTER TABLE operations (among others - related to INDEXes amongst other things).

For completeness, there is also DCL - Data Control Language. DCL is for the control of access to tables - GRANT and REVOKE (i.e. GRANT READ ON schema_name.table_name TO user_name.

The SQL standard is an attempt to homogenise DML among various vendors - its success has been limited so far. Many servers implement non-standard extensions, their own Stored Procedure language, DDL and DCL. Take a look at the "Procedural extensions" section on the SQL page above.

Changes to a schema (from the same version of the same server to another or even within the same instance) are simply called DDL - CREATE TABLE, ALTER TABLE, DROP TABLE.

Changes to a schema brought about (normally) by an upgrade (e.g. Oracle 9 to Oracle 11) are called migrations - going the other way is called either downgrading or backward migration.

Changes to a schema due to a change of server (i.e. Oracle to SQL Server) are called ports ("porting"). These are normally quite difficult and involved due to the differences between servers mentioned above.


SQL Server 2012 not loading its self-generated SSL certificate

Discovered that a service broker endpoint was created using the same cert that we were trying to remove. After querying DMVs for sessions and connections we discovered that no connections were using the service broker endpoint so we deleted the server broker, dropped the certificate, then followed all of the steps for removing the cert in SQL Server Manager, removed the certificate from the Windows certificate store, then restarted the database instance. Confirmed that it loaded by reading in the SQL Server error log... "A self-generated certificate was successfully loaded for encryption."


How do I use the result of a CASE statement in a WHERE clause?

The problem is that Oracle evaluates the SELECT after the WHERE clause.

You can either put your query in a subselect:

SELECT gpaScore
FROM   (SELECT ( CASE grade
                   WHEN 'A+' THEN 4
                   WHEN 'A' THEN 4
                   WHEN 'A-' THEN 3.7
                   WHEN 'B+' THEN 3.3
                   WHEN 'B' THEN 3
                   WHEN 'B-' THEN 2.7
                   WHEN 'C+' THEN 2.3
                   WHEN 'C' THEN 2
                   WHEN 'C-' THEN 1.7
                   WHEN 'D+' THEN 1.3
                   WHEN 'D' THEN 1
                   WHEN 'D-' THEN 0.7
                   WHEN 'F' THEN 0
                   ELSE NULL
                 END ) gpaScore
        FROM   PGFINALGRADES) b
WHERE  gpaScore = '3';

Or, you could replicate the CASE statement in the WHERE clause:

SELECT ( CASE grade
           WHEN 'A+' THEN 4
           WHEN 'A' THEN 4
           WHEN 'A-' THEN 3.7
           WHEN 'B+' THEN 3.3
           WHEN 'B' THEN 3
           WHEN 'B-' THEN 2.7
           WHEN 'C+' THEN 2.3
           WHEN 'C' THEN 2
           WHEN 'C-' THEN 1.7
           WHEN 'D+' THEN 1.3
           WHEN 'D' THEN 1
           WHEN 'D-' THEN 0.7
           WHEN 'F' THEN 0
           ELSE NULL
         END ) gpaScore
FROM   PGFINALGRADES
WHERE ( CASE grade
           WHEN 'A+' THEN 4
           WHEN 'A' THEN 4
           WHEN 'A-' THEN 3.7
           WHEN 'B+' THEN 3.3
           WHEN 'B' THEN 3
           WHEN 'B-' THEN 2.7
           WHEN 'C+' THEN 2.3
           WHEN 'C' THEN 2
           WHEN 'C-' THEN 1.7
           WHEN 'D+' THEN 1.3
           WHEN 'D' THEN 1
           WHEN 'D-' THEN 0.7
           WHEN 'F' THEN 0
           ELSE NULL
         END ) = '3';

Or, if your real case is about as simple as this, you can put grade in your WHERE clause:

SELECT ( CASE grade
           WHEN 'A+' THEN 4
           WHEN 'A' THEN 4
           WHEN 'A-' THEN 3.7
           WHEN 'B+' THEN 3.3
           WHEN 'B' THEN 3
           WHEN 'B-' THEN 2.7
           WHEN 'C+' THEN 2.3
           WHEN 'C' THEN 2
           WHEN 'C-' THEN 1.7
           WHEN 'D+' THEN 1.3
           WHEN 'D' THEN 1
           WHEN 'D-' THEN 0.7
           WHEN 'F' THEN 0
           ELSE NULL
         END ) gpaScore
FROM   PGFINALGRADES
WHERE grade = 'B'


Mongodb Products schema with proper indexes

Based on your requirements, following is the solutions:

You must find the frequent query areas. At initial lookup it looks like  adding an index for each of the use case is good idea. But you may need to revise it over the period of time and observation of index utilization. Following statement from the official document describes the most important point about the cost of indexing:

  Before you build indexes, map out the types of queries you will run so that you can build indexes that reference those fields. Indexes come with a performance cost, but are more than worth the cost for frequent queries on large data set. Consider the relative frequency of each query in the application and whether the query justifies an index.

You can find more details here.
You are correct on this point.


SQL Server: Identifying object closest to the end of a datafile

I think this will do it.  This code basically does the following:

Retrieve highest allocated page ID from the last GAM interval in the file
Retrieve highest allocated page ID from the last SGAM interval in the file
Compare two values to find highest page
Identify the last ObjectId (table) from the Last allocated page
Identify the Index defined on the Object as well as it's partition
Provide a DBCC SHRINKFILE command that will release only the remaining white-space at the end of the file back to the OS (which should be immediate) and is effectively equivalent to DBCC SHRINKFILE using TRUNCATEONLY

This is nested in a cursor that iterates through Page IDs of the data files within the database and executes pretty quickly based on my localized testing.  I've also added functionality to identify if the end of a data file is occupied by pages that are not reserved by tables or indexes, such as an IAM or PFS page.

SET NOCOUNT ON;

-- Create Temp Table to push DBCC PAGE results into
CREATE TABLE #dbccPage_output(
      ID                INT IDENTITY(1,1)
    , [ParentObject]    VARCHAR(255)
    , [Object]          VARCHAR(255)
    , [Field]           VARCHAR(255)
    , [Value]           VARCHAR(255)
)
GO

-- Variables to hold pointer information for traversing GAM and SGAM pages
DECLARE @GAM_maxPageID INT, @SGAM_maxPageID INT, @maxPageID INT,
        @GAM_page INT, @SGAM_page INT
DECLARE @stmt VARCHAR(2000)

-- Final Output Table
DECLARE @myOutputTable TABLE
(
      LogicalFileName   VARCHAR(255)
    , ObjectID          BIGINT
    , IndexID           BIGINT
    , PartitionID       BIGINT
    , MaxPageID         BIGINT
)

-- Cursor to iterate through each file
DECLARE cursorFileIds CURSOR
FOR
        SELECT file_id, size
        FROM sys.database_files
        WHERE type = 0

-- Variable to hold fileID
DECLARE @fileID INT, @size INT, @interval INT

-- Inject the data into the cursor
OPEN cursorFileIds
FETCH NEXT FROM cursorFileIds
INTO @fileID, @size

-- Enter the While Loop.  This loop will end when the
--  end of the data injected into the cursor is reached.
WHILE @@FETCH_STATUS = 0
BEGIN
        -- Truncate table (mainly used for 2nd pass and forward)
        TRUNCATE TABLE #dbccPage_output

        -- Referenced if we need to step back a GAM/SGAM interval
        STEPBACK:

        -- # of pages in a GAM interval
        SET @interval = @size / 511232
        -- Set GAM Page to read
        SET @GAM_page = CASE @interval WHEN 0 THEN 2 ELSE @interval * 511232 END
        -- Set SGAM page to read (always the next page after the GAM)
        SET @SGAM_page = CASE @interval WHEN 0 THEN 3 ELSE (@interval * 511232) + 1 END

        -- Search Last GAM Interval page
        SET @stmt = 'DBCC PAGE(0, ' + CAST(@fileID AS VARCHAR(10)) + ', ' + CAST(@GAM_page AS VARCHAR(20)) + ', 3) WITH TABLERESULTS, NO_INFOMSGS' -- GAM on Primary Datafile
        PRINT @stmt

        INSERT INTO #dbccPage_output ([ParentObject], [Object], [Field], [Value])
        EXEC (@stmt)

        -- Get Last Allocated Page Number
        SELECT TOP 1
                @GAM_maxPageID = REVERSE(SUBSTRING(REVERSE(Field), CHARINDEX(')', REVERSE(Field)) + 1, CHARINDEX(':', REVERSE(Field)) - CHARINDEX(')', REVERSE(Field)) - 1))
        FROM #dbccPage_output
        WHERE [Value] = '    ALLOCATED'
        ORDER BY ID DESC

        -- Truncate Table
        TRUNCATE TABLE #dbccPage_output

        -- Search Last SGAM Interval page
        SET @stmt = 'DBCC PAGE(0, ' + CAST(@fileID AS VARCHAR(10)) + ', ' + CAST(@SGAM_page AS VARCHAR(20)) + ', 3) WITH TABLERESULTS, NO_INFOMSGS' -- SGAM on Primary Datafile
        PRINT @stmt

        INSERT INTO #dbccPage_output ([ParentObject], [Object], [Field], [Value])
        EXEC (@stmt)

        -- Get Last Allocated Page Number
        SELECT TOP 1
                @SGAM_maxPageID = REVERSE(SUBSTRING(REVERSE(Field), CHARINDEX(')', REVERSE(Field)) + 1, CHARINDEX(':', REVERSE(Field)) - CHARINDEX(')', REVERSE(Field)) - 1))
        FROM #dbccPage_output
        WHERE [Value] = '    ALLOCATED'
        ORDER BY ID DESC

        -- Get highest page value between SGAM and GAM
        SELECT @maxPageID = MAX(t.value)
        FROM (VALUES (@GAM_maxPageID), (@SGAM_maxPageID)) t(value)

        TRUNCATE TABLE #dbccPage_output

        -- Check if GAM or SGAM is last allocated page in the chain, if so, step back one interval
        IF(@maxPageID IN (@GAM_page, @SGAM_page))
        BEGIN
            SET @size = ABS(@size - 511232)
            GOTO STEPBACK
        END

        -- Search Highest Page Number of Data File
        SET @stmt = 'DBCC PAGE(0, ' + CAST(@fileID AS VARCHAR(10)) + ', ' + CAST(CASE WHEN @maxPageID = @SGAM_maxPageID THEN @maxPageID + 7 ELSE @maxPageID END AS VARCHAR(50)) + ', 1) WITH TABLERESULTS, NO_INFOMSGS' -- Page ID of Last Allocated Object
        PRINT @stmt

        INSERT INTO #dbccPage_output ([ParentObject], [Object], [Field], [Value])
        EXEC (@stmt)

        -- Capture Object Name of DataFile
        INSERT INTO @myOutputTable
        SELECT (SELECT name FROM sys.database_files WHERE file_id = @fileID) AS LogicalFileName
            , CASE WHEN (SELECT [Value] FROM #dbccPage_output WHERE Field = 'm_type') IN ('1', '2') THEN -- If page type is data or index
                        CAST((SELECT [Value] FROM #dbccPage_output WHERE Field = 'Metadata: ObjectId') AS BIGINT)
                   ELSE CAST((SELECT [Value] FROM #dbccPage_output WHERE Field = 'm_type') AS BIGINT)
              END AS ObjectID
            , NULLIF(CAST((SELECT [Value] FROM #dbccPage_output WHERE Field = 'Metadata: IndexId') AS BIGINT), -1) AS IndexID
            , NULLIF(CAST((SELECT [Value] FROM #dbccPage_output WHERE Field = 'Metadata: PartitionId') AS BIGINT), 0) AS PartitionID
            , @maxPageID + 7 AS MaxPageID

        -- Reset Max Page Values
        SELECT @GAM_maxPageID = 0, @SGAM_maxPageID = 0, @maxPageID = 0

     -- Traverse the Data in the cursor
     FETCH NEXT FROM cursorFileIds
     INTO @fileID, @size
END

-- Close and deallocate the cursor because you've finished traversing all it's data
CLOSE cursorFileIds
DEALLOCATE cursorFileIds

-- page type values pt. 1: https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-using-dbcc-page-and-dbcc-ind-to-find-out-if-page-splits-ever-roll-back/
-- page type values pt. 2: https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/
-- ObjectIDs of either 0 or 99: https://www.sqlskills.com/blogs/paul/finding-table-name-page-id/
-- Output Object Closest to the End
SELECT  t.LogicalFileName
    ,   CAST(CASE WHEN t.IndexID IS NULL THEN
                CASE t.ObjectID
                    WHEN 0 THEN  '>>No MetaData Found<<'  -- This isn't m_type, rather ObjectID
                    WHEN 1 THEN  '>>Data Page<<'
                    WHEN 2 THEN  '>>Index Page<<'
                    WHEN 3 THEN  '>>Text Mix Page<<'
                    WHEN 4 THEN  '>>Text Tree Page<<'
                    WHEN 6 THEN  '>>DCM Page<<<'
                    WHEN 7 THEN  '>>Sort Page<<'
                    WHEN 8 THEN  '>>GAM Page<<'
                    WHEN 9 THEN  '>>SGAM Page<<'
                    WHEN 10 THEN '>>IAM Page<<'
                    WHEN 11 THEN '>>PFS Page<<'
                    WHEN 13 THEN '>>Boot Page<<'
                    WHEN 15 THEN '>>File Header Page<<'
                    WHEN 16 THEN '>>Diff Map Page<<'
                    WHEN 17 THEN '>>ML Map Page<<'
                    WHEN 18 THEN '>>Deallocated DBCC CHECKDB Repair Page<<'
                    WHEN 19 THEN '>>Temporary ALTER INDEX Page<<'
                    WHEN 20 THEN '>>Pre-Allocated BULK LOAD Page<<'
                    WHEN 99 THEN '>>Possible Page Corruption/Run DBCC CHECKDB<<'  -- This isn't m_type, rather ObjectID
                    ELSE CAST(t.ObjectID AS VARCHAR(50))
                END
            ELSE QUOTENAME(OBJECT_SCHEMA_NAME(t.ObjectID)) + '.' + QUOTENAME(OBJECT_NAME(t.ObjectID)) END AS VARCHAR(250)) AS TableName
    ,   QUOTENAME(i.name) AS IndexName
    ,   p.partition_number AS PartitionNumber
    ,   'DBCC SHRINKFILE(' + t.LogicalFileName + ', ' + CAST(CEILING((t.MaxPageID + 8) * 0.0078125) AS VARCHAR(50)) + ')' AS ShrinkCommand_Explicit
    ,   'DBCC SHRINKFILE(' + t.LogicalFileName + ', TRUNCATEONLY)' AS ShrinkCommand_TRUNCATEONLY
FROM @myOutputTable t
    LEFT JOIN sys.indexes i
        ON t.ObjectID = i.object_id
        AND t.IndexID = i.index_id
    LEFT JOIN sys.partitions p
        ON t.ObjectID = p.object_id
        AND t.PartitionID = p.partition_id

-- Cleanup
DROP TABLE #dbccPage_output
GO


Is Oracle 10g smart enough to work on a WHERE clause identically as the equivalent INNER JOIN?

Is it possible?  Sure.  Is there likely to be an improvement in performance?  No.  If there is a change in performance (barring cases where you discover that a join is missing or otherwise fix a query), it'm more likely that the old implicit join syntax will be more efficient.  But that's pretty unlikely.

Behind the scenes, when you have a query using the SQL 99 inner join\'left outer join` syntax, Oracle actually transforms that to use the old style syntax before optimizing it.  When Oracle first introduced the SQL 99 syntax, there were occasionally bugs in this transformation process.  By the time you get to 10.2.0.4, the odds that you'd encounter one of those bugs is pretty low.  But if you look at enough queries, you might find one that is more efficient using the old syntax.


Select like and unlike record pattern SQL

Assuming that your file column is unique, you can do this:

SELECT
REPLACE(file, '.delete.', '') AS file
FROM your_table
GROUP BY REPLACE(file, '.delete.', '')
HAVING COUNT(*) = 1

You "delete" the ".delete." and then return only the rows that don't appear multiple times.


This should be the Oracle equivalent to the accepted answer:

SELECT *
FROM a a1
  JOIN b b1
    ON a1.id = b1.id
   AND EXISTS(SELECT * FROM a a2 where a2.id = a1.id
              MINUS
              SELECT * FROM b b2 where b2.id = b1.id);

I have never seen a select a.* before (i.e. a select without a FROM clause using an alias instead of constant values) but I assume it maps to a co-related subquery

Note that onedaywhen's answer would work as well in Oracle (when replacing except with minus):

WITH a_minus_b AS (
  SELECT *
  FROM A
  minus
  SELECT *
  FROM B
)
SELECT *
FROM a_minus_b T
   JOIN B ON T.ID = B.ID;

SQLFiddle: http://sqlfiddle.com/#!4/3f369/1

Just for completeness: in standard SQL this could be written as:

SELECT *
FROM a
  JOIN b
    ON a.id = b.id
where (a.string, a.dt, a.b, a.num) is distinct from (b.string, b.dt, b.b, b.num);

which is e.g. supported by Postgres: http://sqlfiddle.com/#!15/b431b/1


Just use ALTER DATABASE:

ALTER DATABASE mydb
MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name )


xp_sendmail: failed with mail error 0x80004005

Have you logged into the server with the account which the SQL service is running under, and configured outlook under that account?


is there any point of moving tempdb files into different disk drive if all LUNs are from the same RAID pool?

There are several potential benefits.

Constraining TempDb Growth
Making disk-level performance counters more informative.
Separating the IO Queues so a log write IO never waits behind TempDB IOs in Windows.
Simplifying storage reconfiguration both in Windows and on the SAN.
Scaling across SAN controllers and enabling per-lun SAN optimizations.


Create a job from dbms_scheduler with stream redirection?

I found a patch to this problem. The idea is to create a bash file:

#!/bin/sh
echo $1 > $2

And now, you can create and run your job:

exec dbms_scheduler.create_job('write_file','executable','/usr/bin/oracle/transfer',2,auto_drop=>false);
exec dbms_scheduler.set_job_argument_value('write_file',1,'some_text');
exec dbms_scheduler.set_job_argument_value('write_file',2,'/tmp/some_file');
exec dbms_scheduler.run_job('write_file');

And you can see the result:

$ cat /tmp/some_file
some_text


I'm not sure I understand your question. But yes, sharding and replication work together.

http://docs.mongodb.org/manual/faq/sharding/#how-does-sharding-work-with-replication

Anyway, the master-slave replication approach has been discouraged for some time.

http://docs.mongodb.org/manual/core/master-slave/

The docs are pretty good, take a look at the replication section.


Postgres role does not inherit permissions from its member role

Thanks to one of my colleague, I could resolve this issue. You have to first grant permission to the schema. EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', schem, role_name);.
Until then things won't work. Also, there was a typo in my passing parameter which I found after I passed this step.
I was debugging the issue in another direction since I was referring the admin user created by RDS when database getting created. That user has all the grant records in role_table_grants table. That is why I thought this is an inheritance issue. Good learning BTW :)


How to guess or recover a timestamp conversion in sqllite?

The Unix epoch timestamp is defined as the number of seconds since the beginning of 1970 (in UTC).

These timestamps are measured from the beginning of 2001, and appear to use the respective local time zone:

> select datetime(strftime('%s', '2013-11-10') - 405720000, 'unixepoch');
2001-01-01 04:00:00
> select strftime('%s', '2013-11-10') - strftime('%s', '2001-01-01 04:00:00');
405720000

To correct for the random time zone differences, you should round to the nearest midnight.


Using XEvents Trace, I can see SQL Server Management Studio is getting confused around the [move] column.

The DML query it uses to update the row actually works, however the query it issues to retrieve the updated row looks like:

DECLARE @Param1 int, @Param2 float,@Param3 float;
SET @Param1 = 1;
SET @Param2 = 2;
SET @Param3 = 3;

SELECT TOP (200) identkey, Move, Wind, Traverse
FROM tbl_Rv8WindProfile
WHERE ('Move' = @Param1)
    AND (Wind = @Param2)
    AND (Traverse = @Param3);

This looks like a bug in SQL Server Management Studio (SSMS).  I tested this with SSMS 17.4, SSMS 2016, SSMS 2012; all of them exhibit this behavior.  SSMS is wrapping the [move] column with single-quotes instead of square-brackets or double-quotes as would be expected.

Interestingly, SSMS 2008 R2 won't even open the "Edit top 200 rows" window.  It raises an error-dialog containing the text "Invalid prefix or suffix characters (MS Visual Database Tools)".

FYI, the INSERT statement used by SSMS to insert new rows is:

DECLARE @Move int, @Wind float,@Traverse float;
SET @Move = 1;
SET @Wind = 2;
SET @Traverse = 3;

INSERT TOP (200)
INTO tbl_Rv8WindProfile(Move, Wind, Traverse)
VALUES (@Move, @Wind, @Traverse)

The INSERT and subsequent SELECT statement do not appear to be executed in the same transaction; in my tests, I ended up with multiple rows in the table, one for each error I received.  You may want to manually confirm the table contains the correct data.


How to repair corruption for "Possible chain linkage problem."

When facing database corruption, you will need to determine the extent of the problem. Do you have one corrupt table/index, or many? You will probably want to run DBCC CHECKDB on the database.

Depending on what you find, you have the following options (in decreasing order of safety):

Restore from a backup

This is usually the simplest and safest solution. However, if you're not certain how long ago the corruption appeared, this may be impractical - it's possible that you have no recent backups without the corruption. And, depending on the usage of the database, throwing away a day's worth of data to fix the corruption might be severely costly, in terms of continuing to be able to run your business.

However, if the corruption is sever, this may be the only solution you have available. Even if you try some of the following options, you may need to come back to this one.

If you have backups, keep them handy, and don't cycle any out (if possible) until you've resolved the problem.
Use DBCC CHECKTABLE or DBCC CHECKDB with a repair option

Your run of DBCC CHECKTABLE and/or DBCC CHECKDB may have indicated a repair option you could use to try to restore things to normal.

The commands have the same repair options:

REPAIR_FAST - as it says, it tried some simple things, and will take less time than the other repair options. As you might guess, this also means it's the least likely to be successfully.
REPAIR_REBUILD - This does what REPAIR_FAST does, but is also willing to try rebuilding indexes from scratch - of the problem is in a non-clustered index, this should resolve it.
REPAIR_ALLOW_DATA_LOSS - As indicate, it will try to restore the affected objects to usability, but may have to eliminate partial rows and such to do so; you may lose some of your data. This is one of the reasons why restoring a backup is often the best solution.

Frankensteining your bad table (so to speak)

If only this table is affected, you can try to:

Locate a backup where the table is not corrupt (and stand up the backup, under a different name);
Drop the bad table;
Confirm that this hasn't moved your problem elsewhere by running DBCC CHECKDB again; and
Copy in the table and its data from the backup.

Or, to try to retain as much newer data as possible:

Get a backup ready, as above
Use REPAIR_ALLOW_DATA_LOSS on the table.
If the table is now working, locate rows in the backup that aren't in the repaired table, and copy them in.

As noted by CaM in the comments, you should use this as motivation to make sure you're performing regular maintenance tasks on your database.

First, if you aren't today, start backing it up regularly. Most of the databases I administer have a weekly full backup, and daily differential backups, so we should never lose more than a day's data.

In addition, you should run DBCC CHECKDB on a regular basis as well, to catch corruption before it becomes too serious to fix. This is also something that I try to run weekly.


No it's the same syntax

The output still goes immediately after the set.

There is no updated table available though.

UPDATE T1
SET T1.a = T2.a
OUTPUT INSERTED.*,DELETED.*
FROM Tab1 T1 JOIN Tab2 T2 ON T1.a = T2.a


How to specify the domain of an attribute in an entity-relationship diagram (ERD)?

That sounds like Chen notation. I don't think Chen has a standard representation for attribute domains.

If you're drawing the diagrams free-hand (or using a generic diagramming program) then just write it in there. If your using specific ERD software go with what it supports.


mysql workbench failed to connect at localhost:3306 with user root ssl connection error: ssl is required but the server doesn't support it

Configure MySQL Workbench to not require SSL encryption

If you get the MySQL Workbench error of "SSL connection error: SSL is required but the server doesn't support it" then you likely just need to change a setting that's defined within the MySQL Connections within MySQL Workbench.

From the MySQL Connections screen click on the little wrench icon  to the right to bring up Manage Server Connections window.

From the Manage Server Connections window click on the "MySQL Connection" that is giving you this error, then from the SSL tab you will want to ensure the Use SSL field has a value of If available so it only uses SSL if it's available or else it'll connect without SSL encryption.

Press the Test Connection option from the bottom of the Manage Server Connections window after you make the to confirm the error goes away.

Continued Trouble

If you continue to have trouble you might set the Use SSL field to a value of No and also ensure all the SSL fields below that are blank and then test the connection again.


Creating Sort Index takes 96% of query time even without any ORDER BY

Each row in the EXPLAIN's output having NULL in the 'key' column should be investigated. Those row marked as (derivedN) aren't the culprits but rather the victims. In your case flyer_items table need the indexes: (id), (flyer_id), (id, flyer_id) and (flyer_id, id). I can't predict which one of the last two will be choosed by optimizer.

Then derived tables produced by JOINs will get the suitable inherited indexes for further proceeding.


Is there any reason I shouldn't delete orphaned database users?

As pointed out in a comment by eckes you should contact management and stakeholders for approval before deleting them.

Additionally if there are test or development instances that have copies of the DB, there may be logins on those instances where the users have access. You will want to consider impact there before making changes.

Part of reaching out to stakeholders is identifying who is currently the responsible business user(s).  It is not unusual to find a database that everyone forgot about and reaching out to these people that used to have access can be helpful.


What is the optimal solution for storing real time time-series in MariaDB / MySQL?

With just MySQL/MariaDB, I would employ:

High speed ingestion:  http://mysql.rjweb.org/doc.php/staging_table
Summary tables (to make fetching the data much faster):  http://mysql.rjweb.org/doc.php/summarytables
I would even consider not storing the raw data; instead summarize the data, then toss it.  If this is practical, it avoids most of the questions you pose.
(If data needs purging):  Rapid deletion of old data:  http://mysql.rjweb.org/doc.php/partitionmaint
I would avoid FOREIGN KEYS because of the extra overhead.  (Instead, I would debug the SQL.)
I would not use UUID keys; performance degenerates terribly in huge tables.  (http://mysql.rjweb.org/doc.php/uuid)
I would avoid extra indexes -- Don't use AUTO_INCREMENT if some other column(s) is unique.
You mention Spatial -- Please elaborate.  2D lookups are tricky; SPATIAL is one approach; here is another: http://mysql.rjweb.org/doc.php/latlng

Your last paragraph throws in the kitchen sink of questions (Toku, MyRocks, archive, compression, history table).  I'm surprised the posting has not be killed for being "too broad".  Please elaborate on what your data and queries are like; otherwise, all we can do is throw a kitchen sink full of solutions.

You say "realtime", yet you need "thousands/sec".  Can you allow 1 minute delay in realtime?  1 sec?  You cannot get 1ms; 1s will be hard to achieve.  How long does a burst last?  What is a per-minute burst?  1K/sec will probably spill into the next couple of seconds.  6K/minute is not much trouble.

How many clients are storing data?  Some solutions work well with a single client; different solutions are needed for multiple clients.

Keep in mind that benchmarks are tuned to show off one thing, and rarely match real life.


Is there a faster way to count JSONB tags?

This should be faster and simpler overall:

SELECT *, tag1 + tag2 AS total_tags
FROM  (
   SELECT (data->>'recruiter_id')::int AS recruiter_id  -- cheaper to group by int
        , count(*) FILTER (WHERE data->'tags' ? 'tag1') AS tag1
        , count(*) FILTER (WHERE data->'tags' ? 'tag2') AS tag2
   FROM   people
   WHERE  data->'tags' ?| ARRAY['tag1','tag2']
   GROUP  BY 1
   ) target;

Assuming that recruiter_id is an integer entity, it's cheaper to group by the integer value than the jsonb object containing an integer value. I also assume, you'd rather get the integer value in the result anyway.
Only count once in the subquery, then add counts for the total sum in the outer SELECT.
Use the aggregate FILTER clause for conditional counts:

Return counts for multiple ranges in a single SELECT statement

If you want shorter syntax, this gets you the same result and performance:

count(data->'tags' ? 'tag1' OR NULL) AS tag1

Index and missing statistics for jsonb

Typically, indexes are the deciding factor for performance with big tables. But since your query retrieves 48.493 of 400.000 rows, i.e. > 12 %, the index is not helping this query at all.

Why the poor decision? The query planner has no statistics for values inside a json / jsonb object and has to chose the best query plans based on generic selectivity estimates. It expects to find rows = 550 while the query actually finds ~ 90 x as many (rows=48493). The query plan using bitmap index scans is a poor decision. A sequential scan would be faster (not using the index at all).

The index may still be instrumental for less frequent tags (if you have those), an expression index on data->'tags' should serve best. Maybe even a jsonb_path_ops index, combined with an adapted query. More:

Index for finding an element in a JSON array

However, for this and other reasons, while working with common tags, a plain Postgres array or a fully normalized schema will beat the performance of your jsonb object by a long shot.

This discussion in the postgresql-performance list is about your problem exactly:

https://www.postgresql.org/message-id/54C738E1.8080405@agliodbs.com


Optimize command issuing "'View is not base table" and "Corrupt" error messages

You cannot run OPTIMIZE TABLE against a View. I can see why the error message would be Corrupt

In mysql, a view is represented as a table in INFORMATION_SCHEMA.TABLES.

I have discussed views in this light before

However, a View in INFORMATION_SCHEMA.TABLES has NULL as a Storage Engine.

Mar 21, 2012 : How do I get the execution plan for a view?
Nov 23, 2011 : How can I define the datatype of a view in MySQL?

Consequently, there must be a storage engine to optimize on. Since the Storage Engine is NULL, Corrupt error message is understandable. Therefore, don't be concerned with the error message. Just run OPTIMIZE TABLE on the underlying base tables.


Can't install mongodb-enterprise. Error: nothing provides libsasl2.so.2()(64bit)

As i saw in your statement , you are going to install MongoDB Enterprise in Fedora 25. As you said already run

sudo yum install -y mongodb-enterprise

First i would like to say about Red Hat Enterprise Linux (RHEL) & Fedora.

Red Hat Enterprise Linux (RHEL) is a product of Red Hat and is purchased from Red Hat and includes a support contact with Red Hat that you renew every year.

Fedora was created by Red Hat as a community-based open source project that would always have the latest versions as well as trying out new ideas. Items that do well in Fedora can make it into RHEL at a later time.

I am sure about that you are doing mistake here. Even as per MongoDB Fedora documentation in place of

sudo yum install -y mongodb-enterprise // for Red Hat installation

you have to run Here

dnf install mongodb-org mongodb-org-server // for Fedora installation

what is dnf?

  DNF by default uses the global configuration file at /etc/dnf/dnf.conf
  and all *.repo files found under /etc/yum.repos.d. The latter is
  typically used for repository configuration and takes precedence over
  global configuration.

The steps to install MongoDB on Fedora is pretty simple.

First, login to the server as root user.

Then, we should add the repository to download the package. Open a new repo file mongodb.repo using below command.

For 64-bit system

[mongodb]

name=MongoDB Repository

baseurl=http://downloads-distro.mongodb.org/repo/redhat/os/x86_64/

gpgcheck=0

enabled=1

Then exit and save the file with the command :wq

Then installing MongoDB is now one command away

dnf install mongodb-org mongodb-org-server

When rompted with Y / n, enter yes or y to download and install the package.

Once installed Start-Up MongoDB

[root@fedora]#systemctl start mongod

For further your ref Here and Here


How to scale MySQL transaction wrapped queries?

Read/Write splitting doesn't move all reads to a replica server, it only moves the reads away from the master that are sensible to move... so any reads, for example, done by your processes that are integrating third party data would remain on the master, along with any reads related to transactions, but many other reads, such as report generation or compiling of aggregate/summary views could probably be offloaded to another machine.

How much this would help vs. how complicated it is to implement is very situation-specific.  The 85%/15% split you're observing seems promising, but it doesn't tell us how many of the reads are directly related to the writes and thus need to stick with the master server.

Another concern with read/write splitting is the read-after-write consistency seen by your users.  If I'm browsing my bank account transactions, that information could easily be read from a replica; on the other hand, if I just did a transfer between accounts, then go back to look at my transactions, the safest thing for the system to do is to read my transactions from the master to ensure that what I just did is immediately visible to me.  Again, the nature of your application is the driving factor in determining the implications of this.

Neither of these points should be taken as advice "against" read/write splitting.  You almost certainly should do some of it... the questions are "how much can you safely do?" followed by "how much will that help?"

When you are integrating the third-party data, something you want to keep in mind is that ideally, database transactions should generally be as quick as possible and as small as possible. From what you've described about using transactions to ensure consistency in the event of a lost connection from a third-party data source, it sounds like you might have room for improvement, here.  The best bet -- probably -- is to retrieve the data from the external source, save it locally, verify its integrity, and then proceed with the integration.

If a large portion of your load is machine-generated, you probably also have options of making those processes less aggressive in their actions, more deterministic in their scheduling, and more cooperative with each other.  Some of those processes, possibly, could be staged on another server, the calculations done, and the final result sent to your master server... but like everything else, it depends on the nature of the processes.


How to clear Last_SQL_Error in show slave status

You can do this with

STOP SLAVE;
RESET SLAVE;
START SLAVE;

read more about it here


As you plan to update location Y, you should consider merge replication but it isn't simply a case of turning it on and you may need to make some changes to your DB or application.

You do have other options.

Reduce database chatter. Minimise the number of calls to the database to reduce the impact of latency. If you're talking between London and Hong Kong, you'll pick up 250ms of latency with each packet (round trip). Four packet would accumulate 1 second of latency, so batch your queries up and use stored procedures to reduce the overall size of your queries.
Split reads and writes. The reads could talk to the local DB (Y) and the writes sent back to the master (X). This could enable you to use AlwaysOn Availability Groups, Log Shipping or other forms of replication like Snapshot and Transactional.
WAN optimization. Most firewalls have a WAN optimiser option (usually a bolt on). This would compress the traffic, reducing the latency of larger payloads. Or look at Windows Server BranchCache.


How to store multiple values in a column in database?

While there are different approaches to manage your problem, the simplest way to do it is following the most conventional relational and normalised way, which is separating Campaigns, Programs and Assets as different tables. You can specify relationships between tables like "has" or "belongs to" with foreign keys that references other table rows.

You can read an introduction about the relational model here: http://en.wikipedia.org/wiki/Relational_model

When querying complete information, you have to use the JOIN clause ( http://en.wikipedia.org/wiki/Join_(SQL) ) to merge results from different tables, for example:

SELECT * FROM campaign
JOIN program ON campaign.id = program.campaign_id
JOIN asset ON program.id = asset.program_id


One-directional Transactional Replication is causing one table's Clustered Index to be copied over as a Nonclustered Index in the subscriber database?

maybe it's a bug in the SSMS; try to scritp the @schema_option and verity the proper value based on the list here:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addarticle-transact-sql?view=sql-server-ver15


Does creating an index on BIT(n) attribute in MySQL make sense?

Unless you are querying for an exact match on the whole value stored in the bit column, an index will not help, because it can't generally be used.  On the remote chance that you are, then it would.

The reasons an index can't be used when storing a bitmapped value and then querying based on the state of one or more bits include the fact that the commonly-available index types in MySQL are only useful for exact-value or range matching, and bitwise comparisons aren't truly either of those things.

Because of the nature of this limitation, it also holds true if you use any of the integer data types or even the SET data type.

Granted, for example, testing the state of the high bit on an 8-bit number is the same as testing whether the number is >= 128, which is indeed a range match and could be accomplished using a b-tree index, but to make use of an index for this test, the optimizer would have to understand that this what you're "really" asking when what you actually asked for is WHERE bin_col & b'10000000' ... the optimizer isn't going to realize that.

My inclination for storing bitmapped values would be to use an UNSIGNED [something]INT column, since BIT columns in MySQL are actually more analogous to CHAR/BINARY columns than they are to integer data, but this will ultimately depend on your application as well as your storage engine.

MyISAM apparently stores different bit columns together in the raw row data, so if you're not using even increments of 8/16/32/64, there might be a small storage advantage there when using BITs vs INTs -- but unless you're already using MyISAM, I wouldn't think this would be of enough of an advantage to make you consider using it.

The MEMORY and InnoDB engines allocate the smallest standard integer size that can hold the required number of bits in a BIT column.

The SET data type also stores the values as an unsigned integer of 1, 2, 3, 4, or 8 bytes, as required by the number of labels you define for the bits.  It is easier on the eyeballs when you're looking at your stored bitmapped data, because if you select from it without casting the result back to an integer (either explicitly or implicitly with SELECT column_name + 0), you get back a comma-separated list of the labels of the bits that are set to 'on'... It doesn't offer any optimization in querying but it does give you the enhancement of expanding your bits in to labels without any real penalty compared to using a raw integer column.


Paging in SQL Server 2012 vs. previous versions

Actually, the OFFSET/FETCH extension is more syntactic sugar than performance enhancement. I've heard of a few cases where performance can be improved, but in all of my tests thus far, the performance is equivalent to the CTE with ROW_NUMBER() that you're already using - or at least not "better enough" to justify a code change. IMHO. You'll want to actually test your queries in your environment against your data to see if the performance benefit is worth the refactor.

Some very early performance comparisons here:

https://sqlblog.org/2010/11/10/sql-server-v-next-denali-using-the-offset-clause-paging

And from an updated article in 2015:

https://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch

And the documentation:

http://msdn.microsoft.com/en-us/library/ms188385%28v=SQL.110%29.aspx


Based on the EXPLAIN, I would recommend focusing on second SELECT in the UNION.  It is probably the slowest piece, and the hardest to optimize.  Can you explain what it is doing?

One thing to try is to use literals in place of @_START and @_END.  @variables, even if constant, do not optimize well.

You have a lot of non-sargable expressions in the WHERE clauses.  See http://en.wikipedia.org/wiki/Sargable
Rewrite them.  One of note is that

@prevDate = DATE_FORMAT(c.`end`,'%Y-%m-%d')

is the same as

@prevDate = c.`end`

because of the way DATETIME works.  Fix all the non-sargable expressions; that will give the optimizer more options.

You would be better turning this into INT:

`duration` varchar(5) DEFAULT NULL,

As a VARCHAR it is useless in an index.  If you need non-integer values, either use NULL or have an extra column.

Please explain what the query is supposed to do -- in English.

Is limit  5000000 real?  Or an optimization trick?

What version of MySQL are you using?

(called, callid, and calling look so much alike that the query is hard to read.)

If you must name the INDEXes, at least be consistent:

KEY `called end` (`calling`,`end`),

Actually (called, end) might be better, at least for Derived #3.  I can't tell, but this might be the quickest partial fix.  It might avoid a 'filesort' necessary for

ORDER BY  c.called, c.`end`

However, due to the mixing of ASC and DESC, this won't be helped:

ORDER BY  c.called, c.`end` desc

Will it run any faster without @S := and @G :=?  These seem to do nothing inside the query, but perhaps lead to some kind of 'output' from the whole query.  And perhaps their presence forces the optimizer to do something sub-optimal.

Carrying data from one row to the next (re: @nextComp) is likely to be an optimization-killer.  One trick to avoid such things, is to have a sequence number (perhaps an AUTO_INCREMENT in a temp table) that lets you do a "self JOIN" to match up a row and the preceding row.  This is complex, but potentially beneficial.

You have a lot of redundant indexes; DROP them.  (This won't help the question in hand.)

After you have fixed what you can, post the new SELECT and the new CREATE TABLE.  Then we can continue this discussion.


Oracle: what permission is necessary to "grant create table" to another user?

It looks like you are attempting to grant permissions one schema to another schema.  Such a thing does not exist in Oracle, but there are workarounds.

The easiest way to make this happen is to grant the CREATE ANY TABLE privilege to B, but there are obvious security concerns with doing so.


MySQL replication much slower on MySQL 5.7 compared to MySQL 5.5

I appear to have found the solution. Simply put I set MySQL 5.5 to also use ROW based Replication, using the following process:

SELECT @@GLOBAL.binlog_format;
STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'row';
SET SESSION binlog_format = 'row';
FLUSH LOGS;
UNLOCK TABLES;
START SLAVE;
SELECT @@GLOBAL.binlog_format;

After this, I ran the large query on MySQL 5.5 (ran instantly), and when it replicated to MySQL 5.7, it also ran instantly.

So there appears to be some problem with MySQL 5.7 processing statement based replication queries from a MySQL 5.5 server.

. . . But the plot thickens. Although the MySQL 5.5 database claimed to be using ROW Based Replication:

SELECT @@GLOBAL.binlog_format; = ROW

and the fact that this appears to have helped with replication to MySQL 5.7, when I grabbed the latest binary logs (an hour later or about 10 binary logs later) and ran mysqlbinlog against them, I was surprised to see that they were still populated with STATEMENTs. It was only after a full restart of the database that the binary logs started producing the new ROW based format ! ! !