DBA Blogs

The CATALOG START WITH command allows you to update the RMAN Repository with information about backup pieces (or archivelogs) in the specified location.  

For example, if older backups have already been purged from RMAN but are now restored from tape, they can be made visible to RMAN with the CATALOG START WITH command.

Another case would be if you relocate backups  to an alternate filesystem or diskgroup and the RMAN repository needs to updated to identify the new location.

If you copy a backup to another server and then restore the controlfile from a different backup, you can have the controlfile updated with information about the copied backups using this command.

You can also take a backup from a Primary database and catalog it to a Standby (e.g. when you want to update the Standby which is significantly lagging). Oracle also allows you to catalog a backup from a Standby into the Primary server if the backup / backups is/are not available on the Primary.

 A few demonstrations :

Demonstration 1 : Relocated Backup Set / BackupPiece for Datafile Backup(s)

SQL> select file#, name, checkpoint_change#
  2  from v$datafile
  3  where name = '/opt/oracle/oradata/ORCLCDB/users01.dbf'
  4  /

     FILE# NAME                                             CHECKPOINT_CHANGE#
---------- ------------------------------------------------ ------------------
         7 /opt/oracle/oradata/ORCLCDB/users01.dbf                     7583758

SQL>
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 22:18:20 2021
Version 19.3.0.0.0

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


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

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jan 25 22:18:26 2021
Version 19.3.0.0.0

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

connected to target database: ORCLCDB (DBID=2778483057)

RMAN> list backup of datafile 7;

using target database control file instead of recovery catalog

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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
42      Full    229.31M    DISK        00:00:26     14-NOV-20
        BP Key: 42   Status: AVAILABLE  Compressed: YES  Tag: TAG20201114T162700
        Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_11_14/o1_mf_nnndf_TAG20201114T162700_htz56nnc_.bkp
  List of Datafiles in backup set 42
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 7343626    14-NOV-20              NO    /opt/oracle/oradata/ORCLCDB/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
53      Full    229.31M    DISK        00:00:26     25-JAN-21
        BP Key: 53   Status: AVAILABLE  Compressed: YES  Tag: TAG20210125T221421
        Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp
  List of Datafiles in backup set 53
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 7583529    25-JAN-21              NO    /opt/oracle/oradata/ORCLCDB/users01.dbf

RMAN>
-- Datafile 7 is currently at a higher SCN (7583758) then the latest backup as of 25-Jan-21





RMAN> crosscheck backup of datafile 7;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=288 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=46 device type=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp RECID=53 STAMP=1062800062
Crosschecked 1 objects

crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_11_14/o1_mf_nnndf_TAG20201114T162700_htz56nnc_.bkp RECID=42 STAMP=1056472020
Crosschecked 1 objects


RMAN>
----- both backups are no longer available on disk





oracle19c>pwd
/var/tmp/For_Restore
oracle19c>ls -l
total 318016
-rw-r-----. 1 oracle oinstall   9194496 Jan 25 22:14 o1_mf_annnn_TAG20210125T221418_j0xnkv4w_.bkp
-rw-r-----. 1 oracle oinstall   4457984 Jan 25 22:14 o1_mf_annnn_TAG20210125T221418_j0xnkvdk_.bkp
-rw-r-----. 1 oracle oinstall   2251776 Jan 25 22:14 o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp
-rw-r-----. 1 oracle oinstall     62976 Jan 25 22:15 o1_mf_annnn_TAG20210125T221517_j0xnmoj0_.bkp
-rw-r-----. 1 oracle oinstall 240459776 Jan 25 22:14 o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp
-rw-r-----. 1 oracle oinstall  69206016 Jan 25 22:14 o1_mf_nnndf_TAG20210125T221421_j0xnkym5_.bkp
oracle19c>
----- backups of 25-Jan have been restored from Tape to /var/tmp/For_Restore





RMAN> catalog start with '/var/tmp/For_Restore';

searching for all files that match the pattern /var/tmp/For_Restore

List of Files Unknown to the Database
=====================================
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkv4w_.bkp
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkvdk_.bkp
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221517_j0xnmoj0_.bkp
File Name: /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp
File Name: /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnkym5_.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkv4w_.bkp
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkvdk_.bkp
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221517_j0xnmoj0_.bkp
File Name: /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp
File Name: /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnkym5_.bkp

RMAN>
RMAN> list backup of datafile 7;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
42      Full    229.31M    DISK        00:00:26     14-NOV-20
        BP Key: 42   Status: EXPIRED  Compressed: YES  Tag: TAG20201114T162700
        Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_11_14/o1_mf_nnndf_TAG20201114T162700_htz56nnc_.bkp
  List of Datafiles in backup set 42
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 7343626    14-NOV-20              NO    /opt/oracle/oradata/ORCLCDB/users01.dbf

BS Key  Type LV Size
------- ---- -- ----------
53      Full    229.31M
  List of Datafiles in backup set 53
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 7583529    25-JAN-21              NO    /opt/oracle/oradata/ORCLCDB/users01.dbf

  Backup Set Copy #2 of backup set 53
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:26     25-JAN-21       YES        TAG20210125T221421

    List of Backup Pieces for backup set 53 Copy #2
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    64      1   AVAILABLE   /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp

  Backup Set Copy #1 of backup set 53
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:26     25-JAN-21       YES        TAG20210125T221421

    List of Backup Pieces for backup set 53 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    53      1   EXPIRED     /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp

RMAN>
----- Now RMAN finds that there is one more backup in /var/tmp/For_Restore
----- RMAN also identifies that Backup Set 53 actually has 2 copies -- Copy#2 being in /var/tmp/For_Restore
----- The BackupSet is 53 but the BackupPiece is 53 at the FRA location and 64 for the Copy at /var/tmp/For_Restore
----- So, the CATALOG command has added this copy is a new BackupPiece in the Repository





RMAN> crosscheck backup of datafile 7;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_11_14/o1_mf_nnndf_TAG20201114T162700_htz56nnc_.bkp RECID=42 STAMP=1056472020
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp RECID=64 STAMP=1062800572
Crosschecked 1 objects

crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp RECID=53 STAMP=1062800062
Crosschecked 2 objects


RMAN> delete expired backup of datafile 7;

using channel ORA_DISK_1
using channel ORA_DISK_2

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
42      42      1   1   EXPIRED     DISK        /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_11_14/o1_mf_nnndf_TAG20201114T162700_htz56nnc_.bkp
53      53      1   1   EXPIRED     DISK        /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp RECID=53 STAMP=1062800062
Deleted 1 EXPIRED objects

deleted backup piece
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_11_14/o1_mf_nnndf_TAG20201114T162700_htz56nnc_.bkp RECID=42 STAMP=1056472020
Deleted 1 EXPIRED objects


RMAN> list backup of datafile 7;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
53      Full    229.31M    DISK        00:00:26     25-JAN-21
        BP Key: 64   Status: AVAILABLE  Compressed: YES  Tag: TAG20210125T221421
        Piece Name: /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp
  List of Datafiles in backup set 53
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 7583529    25-JAN-21              NO    /opt/oracle/oradata/ORCLCDB/users01.dbf

RMAN>
----- after running CROSSCHECK and DELETE EXPIRED, RMAN now identifies that Backupset 53 has only one BackupPiece at /var/tmp/For_Restore
----- Any attempt to RESTORE DATAFILE 7 would now use this BackupPiece

Demonstration 2 : Relocated ArchiveLog and Backup of ArchiveLog

RMAN> list archivelog from  sequence 119 until sequence 119;

List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
286     1    119     A 25-JAN-21
        Name: /opt/oracle/archivelog/ORCLCDB/1_119_1036108814.dbf


RMAN> list backup of archivelog from  sequence 119 until sequence 119;


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


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
51      2.15M      DISK        00:00:01     25-JAN-21
        BP Key: 51   Status: AVAILABLE  Compressed: YES  Tag: TAG20210125T221418
        Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp

  List of Archived Logs in backup set 51
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    119     7582383    25-JAN-21 7583492    25-JAN-21

RMAN>
RMAN> crosscheck archivelog from  sequence 119 until sequence 119;

released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=288 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=46 device type=DISK
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_119_1036108814.dbf RECID=286 STAMP=1062800057
Crosschecked 1 objects


RMAN> crosscheck  backup of archivelog from  sequence 119 until sequence 119;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp RECID=51 STAMP=1062800060
Crosschecked 1 objects


RMAN>
----- The CROSSCHECK command finds that both the ArchiveLog and it's backup are missing





RMAN> catalog start with '/var/tmp/ArchLogs_Restore/';

searching for all files that match the pattern /var/tmp/ArchLogs_Restore/

List of Files Unknown to the Database
=====================================
File Name: /var/tmp/ArchLogs_Restore/1_119_1036108814.dbf
File Name: /var/tmp/ArchLogs_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /var/tmp/ArchLogs_Restore/1_119_1036108814.dbf
File Name: /var/tmp/ArchLogs_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp

RMAN> crosscheck archivelog from  sequence 119 until sequence 119;

released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=288 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=46 device type=DISK
validation succeeded for archived log
archived log file name=/var/tmp/ArchLogs_Restore/1_119_1036108814.dbf RECID=299 STAMP=1062801628
Crosschecked 1 objects

validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_119_1036108814.dbf RECID=286 STAMP=1062800057
Crosschecked 1 objects


RMAN> crosscheck  backup of archivelog from  sequence 119 until sequence 119;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp RECID=51 STAMP=1062800060
Crosschecked 1 objects

crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/var/tmp/ArchLogs_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp RECID=66 STAMP=1062801628
Crosschecked 1 objects


RMAN>
RMAN> delete expired backup of archivelog from  sequence 119 until sequence 119;

using channel ORA_DISK_1
using channel ORA_DISK_2

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
51      51      1   1   EXPIRED     DISK        /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp RECID=51 STAMP=1062800060
Deleted 1 EXPIRED objects


RMAN> crosscheck  backup of archivelog from  sequence 119 until sequence 119;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/var/tmp/ArchLogs_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp RECID=66 STAMP=1062801628
Crosschecked 1 objects


RMAN>
----- After I CROSSCHECK in the new (restored) location, RMAN finds the ArchiveLog and it's backup
----- I can DELETE the EXPIRED backup
----- (note that the missing ArchiveLog /opt/oracle/archivelog/ORCLCDB/1_119_1036108814.dbf is no longer listed as the CROSSCHECK had already marked it as "validation failed")

Demonstration 3 : Datafile Backup from Standby available at Primary

----- Backup of Datafile 7 taken at the Standby
RMAN> backup as compressed backupset datafile 7  format '/var/tmp/For_Primary/datafile_7.bkp';

Starting backup at 25-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=264 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/opt/oracle/oradata/STDBYDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-JAN-21
channel ORA_DISK_1: finished piece 1 at 25-JAN-21
piece handle=/var/tmp/For_Primary/datafile_7.bkp tag=TAG20210125T225828 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-JAN-21

Starting Control File and SPFILE Autobackup at 25-JAN-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_01_25/o1_mf_s_1062802630_j0xq4pmm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-JAN-21

RMAN> 
----- The backup is then copied over to the Primary Server





RMAN> catalog start with '/var/tmp/From_Standby/';
RMAN> catalog start with '/var/tmp/From_Standby/';

searching for all files that match the pattern /var/tmp/From_Standby/

List of Files Unknown to the Database
=====================================
File Name: /var/tmp/From_Standby/datafile_7.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /var/tmp/From_Standby/datafile_7.bkp

RMAN>  list backup of datafile 7;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
60      Full    229.31M    DISK        00:00:26     25-JAN-21
        BP Key: 70   Status: AVAILABLE  Compressed: YES  Tag: TAG20210125T221421
        Piece Name: /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp
  List of Datafiles in backup set 60
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 7583529    25-JAN-21              NO    /opt/oracle/oradata/ORCLCDB/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
62      Full    1.18M      DISK        00:00:00     25-JAN-21
        BP Key: 73   Status: AVAILABLE  Compressed: YES  Tag: TAG20210125T225828
        Piece Name: /var/tmp/From_Standby/datafile_7.bkp
  List of Datafiles in backup set 62
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 7591636    25-JAN-21              NO    /opt/oracle/oradata/ORCLCDB/users01.dbf

RMAN>
----- The Primary now recognises that there are 2 distinct backups of datafile 7
----- That in /var/tmp/For_Restore  is as of CheckPoint SCN 7583529  (it has a new BS Key and BackupPiece as I have deleted and re-cataloged it for this, third, demo)
----- The one from the Standby at /var/tmp/From_Standby  is at CheckPoint SCN 7591636  -- which is a higher SCN as it is a more recent backup
----- I can actualy use the backup from th Standby and Restore to the Primary





RMAN> sql 'alter database datafile 7 offline';

sql statement: alter database datafile 7 offline

RMAN> restore datafile 7;

Starting restore at 25-JAN-21
using channel ORA_DISK_1
using channel ORA_DISK_2

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 00007 to /opt/oracle/oradata/ORCLCDB/users01.dbf
channel ORA_DISK_1: reading from backup piece /var/tmp/From_Standby/datafile_7.bkp
channel ORA_DISK_1: piece handle=/var/tmp/From_Standby/datafile_7.bkp tag=TAG20210125T225828
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 25-JAN-21

RMAN> sql 'alter database datafile 7 online';

sql statement: alter database datafile 7 online
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 01/25/2021 23:02:55
RMAN-11003: failure during parse/execution of SQL statement: alter database datafile 7 online
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/opt/oracle/oradata/ORCLCDB/users01.dbf'

RMAN> recover datafile 7;

Starting recover at 25-JAN-21
using channel ORA_DISK_1
using channel ORA_DISK_2

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

Finished recover at 25-JAN-21

RMAN> sql 'alter database datafile 7 online';

sql statement: alter database datafile 7 online

RMAN>
----- So, when datafile 7 is corrupt at the Primary, I take it OFFLINE and then issue a RESTORE command
----- RMAN automatically identifies that, of the two backups, the "From_Standby/datafile_7.bkp' is more recent 
----- So, the Backup from the Standby can be Restored to the Primary and the datafile brought ONLINE
----- RECOVERy is still required because the Primary database is currently at a higher SCN than the backup of that datafile from the Standby
----- So, the RECOVER command applies all Redo that is for SCN higher than 7591636 that needs to be applied to Datafile 7
----- For the duration when I had datafile 7 OFFLINE I had stopped Database Recovery at the Standby

Thus, there are different uses for the CATALOG START WITH command in RMAN

Assuming you are an IRL player who wants to get as close to the real thing as possible, that’s what I’d recommend:

Make sure you have enough space to play

The green box is your playing space. It should be a square of 2.50 m X 2.50 m ideally. Make sure to leave some space at the front, so you can reach balls close to the net and even a little across the net. Otherwise you may become a victim of ghost serves. Leave enough room at the sides – some opponents play angled, just like IRL.

If you don’t have enough space for this setup – maybe you shouldn’t play multiplayer mode then. You can still have fun, playing against the ballmachine or against the AI. Actually, I think it’s worth the money even in that case.

Use the discord channel

The Eleven TT community is on this discord channel: https://discord.gg/s8EbXWG

I recommend you register there and use the same or a similar name as the name you have in the game. For example, I’m Uwe on discord and uwe. in the game (because the name uwe was already taken). This is handy to get advice from more experienced players, also the game developers are there. They are very responsive and keen to improve Eleven TT even more, according to your feedback.

There’s a preview version presently, that has improved tracking functionality. You can just ask the developers there to get you this preview version. I did, and I find it better than the regular version, especially for fast forehand strokes.

Setup your paddle

When you have the Sanlaki paddle adapter (as recommended in the previous post), go to the menu and then to Paddle Settings:

Click on Paddle Position and select the Sanlaki Adapter:

As an IRL player, you may start with an Advanced Paddle Surface:

Se how that works for you. Bounciness translates to the speed of your blade. An OFF ++ blade would be maximum bounciness. Spin is self-explaining. You have no tackiness attribute, though. Throw Coefficient translates to the sponge thickness. The higher that value, the thicker the sponge.

Serving

This takes some time to get used to. You need to press the trigger on the left controller to first “produce” a ball, then you throw it up and press the trigger again to release the ball. Took me a while to practice that and still sometimes I fail to release the ball as smoothly as I would like to.

What I like very much: You have a built-in arbiter, who makes sure your serve is legal according to the ITTF rules. That is applied for matches in multiplayer mode as well as for matches in single player mode. But not in free hit mode! Check out the Serve Practice:

It tells you what went wrong in case:

Remove AI Spin Lock

I recommend you practice with the AI opponent in single player mode for a while. It has spin lock on per default, which means it will never produce any side spin. I find that unrealistic. After some practicing against the AI in single player mode, you’re ready for matches in multiplayer mode against other human opponents.

Oracle Cloud Infrastructure is a set of complementary cloud services that enable you to build and run a wide range of applications and services in a highly available hosted environment. Oracle Cloud...

We share our skills to maximize your revenue!

Hi Tom and Team, I have a very simple table with the following records. site_no, sku_no, vendor_id, ven_type A sku_no can have multiple vendor_id values associated to it. A vendor_id can have a ven_type = P (Primary) or S (Secondary) I want to find all those sku_no values from the table that do not have any ven_type='P' records. Kindly help me write a query for this. Please let me know if the explanation is not clear or if you want me to provide any other details. Thank you.

Hello Chris, Hello Connor. I know that hash join performance might suffer a lot if it has to store the hash table on a disk, but I have no idea how to check if that's happening and that is the reason a query is slow. Admittedly I do not know much about memory usage in Oracle and how to track it. Could you please, point me toward some articles on the topic or data dictionaries, that would help? Unfortunately I have neither a dba access nor an access to trace files.

It is probably apparent from my job title that my role is far from a dba. I am, however, a frequent user of Oracle SQL Developer. I work in mass appraisal and use SQL Developer to select, update, insert, delete, etc., on a regular basis. I would like to bring some automation to some of the select statements that I run most frequently and spool the results to a .csv file. An example of this is a select statement for identifying recently sold properties that need to be reviewed. The following command [using the Windows Command Prompt] has been stored as a scheduled task [using Windows Task Scheduler]: G:\>sqlplus username/password@connection @G:\SALES_VALIDATION\bat_files\weekly_salesval_rev.sql Weekly_salesval_rev.sql is the script containing the spool commands and select statement. I have included a link so you can view the script. What command [or commands] can I incorporate so that the data will be formatted appropriately [and include column headings] for review in Excel? While there should be approximately 21 columns of data, the results are currently displaying in a scattered fashion within the first 3 columns of the .csv file. If you need any other detail, please let me know. Also, if you would suggest or recommend other approaches to automating frequently run SQL select statements, let me know and I would be glad to look into those alternatives. Thank you for your time and help! Berkley Rose Product Versions: SQL Plus Release 12.2.0.1.0 Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 Oracle SQL Developer 4.0.3.16 Windows Version 10.0.17763.1577

 If you have a small schema in the Oracle Cloud Autonomous Database, you can actually run DataPump from SQL Developer Web.  DATA_PUMP_DIR maps to a DBFS mount inside the Oracle Database.

Logged in to my Oracle ADB as "ADMIN"

I check if DATA_PUMP_DIR exists  and I find that it is in dbfs  :

I run a PLSQL Block to export the HEMANT schema using the DBMS_DATAPUMP API :

After I drop the two tables in the schema, I run the import using the DBMS_DATAPUMP API and then refresh the list of Tables owned by "HEMANT" :

This method is a quick way of using the Autonomous Database itself when you don't have an external Object Store to hold the Datapump file.  So, I'd use this only for very small schemas as the dump is itself loaded into DBFS.

The PLSQL Code is :

REM  Based on Script posted by Dick Goulet, posted to oracle-l@freelists.org
REM  With modifications by me.  
REM  Hemant K Chitale

REM Export schema "HEMANT"
declare
   h1   NUMBER := 0;
   h2 varchar2(1000);
   ex boolean := TRUE;
   fl number := 0;
   schema_exp varchar2(1000) := 'in(''HEMANT'')';
   f_name varchar2(50) := 'My_DataPump';
   dp_mode varchar2(100) := 'export';
   blksz number := 0;
   SUCCESS_WITH_INFO exception;
begin
      utl_file.fgetattr('DATA_PUMP_DIR', dp_mode||'.log', ex, fl, blksz);
      if(ex = TRUE) then utl_file.fremove('DATA_PUMP_DIR',dp_mode||'.log');
      end if;
      h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => upper(dp_mode)||'_EXP', version => 'COMPATIBLE');
     dbms_datapump.set_parallel(handle => h1, degree => 2);
     dbms_datapump.add_file(handle => h1, filename => f_name||'.dmp%U', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
     dbms_datapump.add_file(handle => h1, filename => f_name||'.log', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
     dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
     dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
     dbms_datapump.metadata_filter(handle=>h1, name=>'SCHEMA_EXPR',value=>schema_exp);
     dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
     dbms_datapump.wait_for_job(handle=>h1, job_state=>h2);
exception
      when SUCCESS_WITH_INFO THEN NULL;
      when others then
         h2 := sqlerrm;
         if(h1 != 0) then dbms_datapump.stop_job(h1,1,0,0);
         end if;
         dbms_output.put_line(h2);
end;





REM Import schema "HEMANT"
declare
   h1   NUMBER := 0;
   h2 varchar2(1000);
   ex boolean := TRUE;
   fl number := 0;
   schema_exp varchar2(1000) := 'in(''HEMANT'')';
   f_name varchar2(50) := 'My_DataPump';
   dp_mode varchar2(100) := 'import';
   blksz number := 0;
   SUCCESS_WITH_INFO exception;
begin
      utl_file.fgetattr('DATA_PUMP_DIR', dp_mode||'.log', ex, fl, blksz);
      if(ex = TRUE) then utl_file.fremove('DATA_PUMP_DIR',dp_mode||'.log');
      end if;
      h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'SCHEMA', job_name => upper(dp_mode)||'_EXP');
     dbms_datapump.set_parallel(handle => h1, degree => 2);
     dbms_datapump.add_file(handle => h1, filename => f_name||'.dmp%U', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
     dbms_datapump.add_file(handle => h1, filename => f_name||'.log', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
     dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
     dbms_datapump.set_parameter(handle => h1, name => 'TABLE_EXISTS_ACTION', value=>'SKIP');
     dbms_datapump.metadata_filter(handle=>h1, name=>'SCHEMA_EXPR',value=>schema_exp);
     dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
     dbms_datapump.wait_for_job(handle=>h1, job_state=>h2);
exception
      when SUCCESS_WITH_INFO THEN NULL;
      when others then
         h2 := sqlerrm;
         if(h1 != 0) then dbms_datapump.stop_job(h1,1,0,0);
         end if;
         dbms_output.put_line(h2);
end;

Again, I emphasise that this is only for small dumps.  

In my previous post in this series, I discussed out Automatic Indexing currently does not consider Non-Equality predicates. Automatic Indexing will index columns based only on Equality predicates. So how does Oracle handle the scenario when an SQL has a mixture of both Equality and Non-Equality predicates? I’ll begin by creating two very similar tables, […]

We have batch_table with column batch_id having values 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Running sample code for demonstration. <code> PACKAGE XXX PROCEDURE YYY IS variables... BEGIN FOR i IN (SELECT batch_id FROM batch_table) LOOP -- Function call IF get_running_threads('XXEXTRACT_DATA') <= l_max_allowed_threads THEN l_request_id := fnd_request.submit_request ( application => g_application_name, program => 'XXEXTRACT_DATA', description => 'XXEXTRACT_DATA', start_time => SYSDATE, sub_request => FALSE, argument1 => i.batch_id); ELSE BEGIN -- WAIT LOGIC -- Wait till previous concurrent program completes successfully. -- Call API FND_CONCURRENT.WAIT_FOR_REQUEST DBMS_LOCK.sleep(10); END; END IF; END LOOP; END YYY; END XXX; </code> My ask is : Batch_id 1, 2, 3 successfully processed by calling the concurrent program XXEXTRACT_DATA. When FOR Loop starts processing batch_id 4, control comes to ELSE part. In the next iteration, this starts with batch_id 5. So batch_id 4 is never processed. How can I process batch_id 4 ? Is this possible to achieve without reopening the cursor more than one ? Pls respond.

Hello, AskTom! Excuse me for the "idle" question, but none of my colleagues could answer this. Many-many years ago, back in Oracle 7, was a "constraining error" ORA-04094 "Table is constraining, trigger may not modify it". This restriction was relaxed since Oracle 8i. In the Oracle (8i and later) documentation you can read - "constraining error prevented a row trigger from modifying a table when the parent statement implicitly read that table to enforce a foreign key constraint" and it's absolutely clear for me. But the Oracle 7 documentation says - "A constraining table is a table that a triggering statement might need to read either directly, for a SQL statement, or indirectly, for a declarative referential integrity constraint". I never undestood the first part of the sentence - "read directly, for a SQL statement": how a reading of the table can "constraint" the table in the triggering statement if the SQL operator reads the data in the table in the state at the moment in time BEFORE the row trigger can do any changes in the table to be read? My opinion is - it was a mistake in the Oracle documentation and a correct definition of the constraining table would be "constraining table is a table that a triggering statement might need to read indirectly, for a declarative referential integrity constraint", without the first part.

Hi Tom, We are dealing with a system which has some legacy tables with LONG column. One of these tables contains critical data (email bodies and attachments, chat transcripts, etc) in LONG column. Unfortunately we cannot remove or even change this table in any way, system is heavily dependent on it. 2 years ago we introduced the following solution: - New table with same columns, but CLOB instead of LOB - Minutely job, which copies newly inserted records from original table into new table, and converts LONG to CLOB - Daily job which deletes already copied records from old table When this solution was introduced, we did a manual cleanup of the original table: - Stop the system which uses the table - Create new table with exact same definition (LONG column) - Copy over all records which had to be kept - Rename original table to "_OLD" postfix and new table to original table name - Drop old table However, after 2 years, customer where this database is located wanted to drop old partitions (note: the problematic table is NOT partitioned), and while checking disk space, noticed that the original table is still growing to be very large (this was the original problem also 2 years ago). Our expectation from the above solution was that while Oracle will not free up the disk space after the DELETE because of the LONG column, it will still reuse existing blocks, so that table will not grow after a certain point. However this is not what is happening, the table keeps growing, day by day. Currently it has about 13 000 records, but over 19 million blocks. About 18 000 records are added during the day, and each of those are deleted at night. I tried to reproduce the problem using the below SQL, but I'm not sure it's fully correct, it's producing a different result, something which is what we were expecting: after we delete and insert again, used blocks count is not increasing. <code> -- Helper proc CREATE OR REPLACE PROCEDURE show_space ( PI_TABLE_NAME IN VARCHAR2 ) AS l_unformatted_blocks NUMBER; l_unformatted_bytes NUMBER; l_fs1_blocks NUMBER; l_fs1_bytes NUMBER; l_fs2_blocks NUMBER; l_fs2_bytes NUMBER; l_fs3_blocks NUMBER; l_fs3_bytes NUMBER; l_fs4_blocks NUMBER; l_fs4_bytes NUMBER; l_full_blocks NUMBER; l_full_bytes NUMBER; PROCEDURE p ( p_label IN VARCHAR2, p_num IN NUMBER ) IS BEGIN dbms_output.put_line(rpad(p_label, 40, '.') || p_num); END; BEGIN dbms_space.space_usage( 'CCQ', PI_TABLE_NAME, 'TABLE', l_unformatted_blocks, l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes, l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes, l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes); p('Unformatted Blocks ', l_unformatted_blocks); p('FS1 Blocks (0-25) ', l_fs1_blocks); p('FS2 Blocks (25-50) ', l_fs2_blocks); p('FS3 Blocks (50-75) ', l_fs3_blocks); p('FS4 Blocks (75-100)', l_fs4_blocks); p('Full Blocks ', l_full_blocks); END; / -- Dummy table, exact same definition as original table CREATE TABLE test_long ( qwkey NUMBER(38, 0) NOT NULL, tablename VARCHAR2(90 BYTE), fieldname VARCHAR2(90 BYTE), textkey NUMBER(38, 0), text LONG ); ALTER TABLE test_long ADD CONSTRAINT pk_test_long PRIMARY KEY (qwkey) USING INDEX; -- Original stats BEGIN dbms_stats.gather_table_stats(ownname => '"CCQ"', tabname => '"TEST_LONG"', estimate_percent => 1); END; BEGIN show_space('TEST_LONG'); END; /* Output: Unformatted...

Hi, I have a sequence name s1 start with 1 and incremented by 1 and end with 999. I am inserting these sequence value into one table name 'e'. E table contains eno (pk) column. insert into e values(s1.nextval); I inserted 9 rows. sequence current value is 9 and 10 is already inserted in backend. I try to insert into e values(s1.nextval); then it will come pk violation error. In this case i want to insert 11 in place of 10

Hi all, I am having a struggle with a view. The outcome of the view can be 1 row of 3 different views with the same fields. Can someone point me in the right direction how to think please? :) Very basic, this is my problem: <code> /* MAINVIEW */ SELECT * FROM (SELECT * FROM VIEW1) --returns n rows /* IF VIEW1 IS EMPTY THEN SELECT ALL FROM VIEW2 */ (SELECT * FROM VIEW2) -- returns n rows /* IF VIEW2 IS EMPTY THEN SELECT VIEW3 (=HAS ALWAYS DATA) */ (SELECT * FROM VIEW3) -- returns 1 row </code> I don't need full code, just a hint in the right direction ;) . Thanks in advance.

Hi, I'm try to evaluate the Json query. e.g I have following json string in the table <code>{ "WO": [ { "id": "1", "name": "WO1", "action": "add", "items": [ { "id": "1", "author": "Tom Ask" }, { "id": "2", "author": "Lee Frik" } ] }, { "id": "3", "name": "WO1", "action": "add", "items": [ { "id": "1", "author": "John A" }, { "id": "2", "author": "Jean Pontus" } ] } ] }</code> <code> select d.wo_data.WO.items.id from workorder_json d;</code> I get following results: <code>["Tom Ask","Lee Frik","John A","Jean Pontus"] </code> What I hope is return with full path like following <code>{ "WO": [ "items": [ { "author": "Tom Ask" }, { "author": "Lee Frik" } ], "items": [ { "author": "John A" }, { "author": "Jean Pontus" } ] ] } </code> Is there anyway to return like above?

The Oracle Cloud Observability and Management Platform brings together a comprehensive set of management, diagnostic, and analytics services that help customers eliminate the complexity, risk, and...

We share our skills to maximize your revenue!

I’m please to announce that both of my highly acclaimed webinars scheduled for February are now confirmed. These webinars are a must for any Oracle DBA or Developer interested in designing, developing or maintaining high performance, highly scalable Oracle-based applications or databases. However only a few places are currently available on each webinar with numbers […]

Hi, is it possible to audit (unified auditing) access to tables only once per session? I don't need to catch all selects from audited tables in a session, I just want to to know if a table was at querried at least once. Currently it generates huge amount of audit data, from which only fraction is needed. Thanks, Julius

Good Morning, It seems like no time is spent during development efforts to assure that an application is able to survive database corruptions that don't require a database restart like say a datafile corruption or even a block corruption. If a corruption of a datafile occurs, for instance, the DBA can restore and recover the datafile, but I don't think the clients or patch processes using the application that access the corrupt datafile will survive the operation and/or the 15-60 minutes it may take to recover. I would assume that the application developers would need to do additional work on their end to even have a chance. Is there any Oracle documentation or a book that you can point me to that an organization can use to make their applications bullet proof against database corruptions that don't require shutting down the database? I would hate to have to try to reinvent this wheel by figuring out all possible Oracle error codes, timeout settings, etc. since it seems like this is something that should be done by lots of application development efforts, but my guess is that 98%+ of the time, nothing is done to try to have in-progress client work survive database corruptions that don't require a database restart. Thank you

Environment: Oracle 18XE 64-bit for Windows. I have a question about dequeueing an array of messages from persistent queue. It?s a simple point-to-point messaging. Queue is ?single_consumer?, without propagation. I registered my PL/SQL callback function. I need to know an exact size of array of messages to dequeue in every call of my callback function from Oracle AQ internal job. And I found the only legal way how to have done it. And this way is to register callback with <b>qosflags</b> parameter of <b>sys.aq$reg_info</b> equal to <i><u>dbms_aq.NTFN_QOS_PAYLOAD</u></i>. Here is the registration PL/SQL block: <code>declare v_qosflags number := dbms_aq.NTFN_QOS_PAYLOAD; r_info SYS.AQ$_REG_INFO; begin r_info := SYS.AQ$_REG_INFO( 'STERN.FOUNDERS_QUEUE', DBMS_AQ.NAMESPACE_AQ, 'plsql://stern.dosomecalc', HEXTORAW('FF') ); r_info.qosflags := v_qosflags; r_info.ntfn_grouping_class := dbms_aq.NTFN_GROUPING_CLASS_TIME ; r_info.ntfn_grouping_value := 60; r_info.ntfn_grouping_type := dbms_aq.NTFN_GROUPING_TYPE_SUMMARY ; DBMS_AQ.REGISTER ( SYS.AQ$_REG_INFO_LIST( r_info ), 1 ); end;</code> Here is the declaration of callback procedure. It is a standard declaration: <code>create or replace procedure dosomecalc (context RAW ,reginfo SYS.AQ$_REG_INFO ,descr SYS.AQ$_DESCRIPTOR ,payload raw ,payloadl NUMBER)</code> Now, thankfully to <b>qosflags</b> parameter initialized with <i><u>dbms_aq.NTFN_QOS_PAYLOAD</u></i> ,my callback function is registered in such a way that I always can see real size of messages to dequeue in callback session. It may be evaluated as counting size of <b>descr.msgid</b>_array part of descr parameter. Without setting of <b>qosflags</b> during registration to some value - this part of descr parameter always comes empty to callback procedure call. Once I know the real size of messages array , I can use it in <code>Dbms_aq.dequeue_array(?, array_size => descr.msgid_array.count,?) /*dequeuing call*/.</code> inside my callback function. Than, after analyze of contents of descr parameter, I found in it an <b>ntfnsRecdInGrp</b> element, and decided that <b>ntfnsRecdInGrp </b>is always equal to <b>descr.msgid_array.count</b>, and just made for programmer?s convenience, just for duplicate <b> descr.msgid_array.count</b>. AQ documentation says: <code>msgid_array - Group notification message ID list ntfnsRecdInGrp - Notifications received in group</code> That was why I decided that they are equal by value. It was a my mistake. When I use callback with array size equal to<b> descr.msgid_array.count</b> ? everything is OK. With n<b>tfnsRecdInGrp</b> ? no. Sometimes <b>descr.msgid_array.count</b> and <b>ntfnsRecdInGrp</b> equal to each other, sometimes not. Now the question is: What is the meaning of <b>ntfnsRecdInGrp</b> part of descr parameter? Why it is not the same as <b>Msgid_array.count</b>? <u></u>

Today’s competitive market demands that stakeholders understand, monitor, and react to rapidly changing conditions. Businesses need flexible, dynamic, and detailed insight – and they need...

We share our skills to maximize your revenue!