Feed aggregator

Oracle Cloud Registry: Use Cases, Benefits & Steps To Configure

Online Apps DBA - 9 hours 21 min ago

Oracle Cloud Registry (OCIR) is an Oracle-managed registry that enables you to simplify your development to production workflow. It is used to store, manage, and deploy container images, like Docker images securely within an Oracle Cloud. Check out the blog post at https://k21academy.com/1z099722 which covers: – Overview of OCI Registry – Use cases – Benefits […]

The post Oracle Cloud Registry: Use Cases, Benefits & Steps To Configure appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Verifying an RMAN Backup

Hemant K Chitale - 10 hours 17 min ago
 In general, most database backups with RMAN always include the "correct" set of ArchiveLogs.  This is done either with :
1. BACKUP DATABASE PLUS ARCHIVELOG
or
2. ALTER SYSTEM ARCHIVE LOG CURRENT ;  followed by BACKUP ARCHIVELOG

But if you receive a Backup from another DBA, can you validate that you have all the ArchiveLogs required to RECOVER DATABASE upto a consistent point (SEQUENCE# or TIME ?) ?
If you use an RMAN Catalog schema, you can query that RMAN Catalog schema for information.
But if there is no RMAN Catalog schema, all the information you need is in the Controlfile backup
One technique that can be used is
1. Create a dummy parameter file with
    a. DB_NAME the same as the source database
    b. A different DB_UNIQUE_NAME (particulary if you have an existing database on the target server with the same DB_NAME)
    c. CONTROL_FILE specifying a "temporary" location -- you will be removing the control files and restoring them to the actual desired target location when you choose to do a Full Restore
2. Restore the Controlfile
3. Remove all entries about RMAN Backups from the Controlfile (as it has a history of recent backups and may even be a Controlfile backup newer than the Database backup that is provided to you, capturing more recent backups
4. Catalog the set of Backup Pieces that you receive
5. Query the catalog that you now create in the Controlfile to check the ArchiveLogs vis-a-vis the Datafiles in the set of Backup Pieces.

At the end of the exercise, I discard the "temporary" parameter file that I used and also remove the Contolfile that I have restored.
If I find that the Backup is Good (i.e. ArchiveLogs contain enough Redo (SCNs) to RECOVER the datafiles, I can do a proper RESTORE DATABASE and RECOVER DATABASE or DUPLICATE DATABASE from the Backup.

Let's say that I receive Backup Pieces, organised as would be an FRA :

$pwd
/u01/app/Backup_from_Source/ORCL12C
$ls -l
total 32
drwxr-x--- 3 oracle oinstall 4096 Sep 18 2017 49BFE9E2D73E2038E0530100007F846C
drwxr-x--- 3 oracle oinstall 4096 Sep 18 2017 49BFF8A6BB912582E0530100007F8BE4
drwxr-x--- 3 oracle oinstall 4096 Jun 5 2017 4F793A6D323D1344E0530100007FABC7
drwxr-x--- 3 oracle oinstall 4096 Sep 18 2017 53F8012866211264E0530100007FD493
drwxr-x--- 3 oracle oinstall 4096 Jan 13 2018 5C9E4689632518EBE0530100007F03C5
drwxr-x--- 3 oracle oinstall 4096 Jun 17 22:36 A84987FDF4C51164E0530100007FEB9C
drwxr-x--- 3 oracle oinstall 4096 Sep 24 17:45 autobackup
drwxr-x--- 3 oracle oinstall 4096 Sep 24 17:45 backupset
$


I first create a parameter file as :

$cat initRTST.ora
db_name = 'ORCL12C'
db_unique_name = 'RTST'
control_files='/tmp/RTST_control.ctl'
enable_pluggable_database=true
$


Then, with ORACLE_SID set to RTST, I restore and mount the Controlfile

$ORACLE_SID=RTST;export ORACLE_SID
$ls -l /tmp/RT*
ls: cannot access /tmp/RT*: No such file or directory
$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 25 23:03:06 2020

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

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/12.2/db_1/dbs/initRTST.ora';
ORACLE instance started.

Total System Global Area 318767104 bytes
Fixed Size 8792152 bytes
Variable Size 251660200 bytes
Database Buffers 50331648 bytes
Redo Buffers 7983104 bytes
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Sep 25 23:03:50 2020

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

connected to target database: ORCL12C (not mounted)

RMAN> restore controlfile from '/u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983813_hprtl5pc_.bkp';

Starting restore at 25-SEP-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=179 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/tmp/RTST_control.ctl
Finished restore at 25-SEP-20

RMAN>
RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN>


Next, I "clear" information about all other backups from the controlfile.

RMAN> delete noprompt backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=182 device type=DISK

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
54 54 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp
55 55 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp
56 56 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp
57 57 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp
58 58 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp
59 59 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp
60 60 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp
61 61 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp
62 62 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp
63 63 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp
64 64 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp
65 65 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp
66 66 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp
67 67 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp
68 68 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp RECID=63 STAMP=1051983568
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp RECID=64 STAMP=1051983703
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp RECID=65 STAMP=1051983676
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp RECID=66 STAMP=1051983711
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp RECID=67 STAMP=1051983712
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp RECID=68 STAMP=1051983737
Deleted 6 objects

RMAN-06207: warning: 9 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp
RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp
RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp
RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp
RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp
RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp
RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp
RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp
RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp


RMAN>
RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp RECID=54 STAMP=1051891224
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp RECID=55 STAMP=1051891279
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp RECID=56 STAMP=1051891314
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp RECID=57 STAMP=1051891339
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp RECID=58 STAMP=1051891355
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp RECID=59 STAMP=1051891372
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp RECID=60 STAMP=1051891375
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp RECID=61 STAMP=1051891431
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp RECID=62 STAMP=1051891432
Crosschecked 9 objects


RMAN> delete noprompt expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
54 54 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp
55 55 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp
56 56 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp
57 57 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp
58 58 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp
59 59 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp
60 60 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp
61 61 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp
62 62 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp RECID=54 STAMP=1051891224
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp RECID=55 STAMP=1051891279
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp RECID=56 STAMP=1051891314
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp RECID=57 STAMP=1051891339
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp RECID=58 STAMP=1051891355
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp RECID=59 STAMP=1051891372
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp RECID=60 STAMP=1051891375
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp RECID=61 STAMP=1051891431
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp RECID=62 STAMP=1051891432
Deleted 9 EXPIRED objects


RMAN> list backup;

specification does not match any backup in the repository

RMAN>


Now I am ready the catalog the Backup Pieces that I have received

RMAN> catalog start with '/u01/app/Backup_from_Source/ORCL12C';

searching for all files that match the pattern /u01/app/Backup_from_Source/ORCL12C

List of Files Unknown to the Database
=====================================
File Name: /u01/app/Backup_from_Source/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983813_hprtl5pc_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.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: /u01/app/Backup_from_Source/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983813_hprtl5pc_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp

RMAN>


Note how the CATALOG command found 3 PDBs in the Backup.
I can now query from RMAN to get information

RMAN> list backup;


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
69 Full 163.55M DISK 00:01:06 24-SEP-20
BP Key: 69 Status: AVAILABLE Compressed: YES Tag: TAG20200924T173928
Piece Name: /u01/app/Backup_from_Source/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp
List of Datafiles in backup set 69
Container ID: 2, PDB Name: PDB$SEED
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 1443131 02-MAR-17 NO /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
6 Full 1443131 02-MAR-17 NO /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
8 Full 1443131 02-MAR-17 NO /u01/app/oracle/oradata/orcl12c/pdbseed/undotbs01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
70 Full 503.88M DISK 00:01:46 24-SEP-20
BP Key: 70 Status: AVAILABLE Compressed: YES Tag: TAG20200924T173928
Piece Name: /u01/app/Backup_from_Source/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp
List of Datafiles in backup set 70
Container ID: 3, PDB Name: ORCL
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
9 Full 3285704 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
10 Full 3285704 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
11 Full 3285704 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf
12 Full 3285704 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf
13 Full 3285704 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf
14 Full 3285704 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
71 Full 18.22M DISK 00:00:01 24-SEP-20
BP Key: 71 Status: AVAILABLE Compressed: NO Tag: TAG20200924T174150
Piece Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp
SPFILE Included: Modification time: 24-SEP-20
SPFILE db_unique_name: ORCL12C
Control File Included: Ckp SCN: 3286161 Ckp time: 24-SEP-20

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
72 Full 18.22M DISK 00:00:00 24-SEP-20
BP Key: 72 Status: AVAILABLE Compressed: NO Tag: TAG20200924T174333
Piece Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983813_hprtl5pc_.bkp
SPFILE Included: Modification time: 24-SEP-20
SPFILE db_unique_name: ORCL12C
Control File Included: Ckp SCN: 3286305 Ckp time: 24-SEP-20

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
73 Full 161.83M DISK 00:00:19 24-SEP-20
BP Key: 73 Status: AVAILABLE Compressed: YES Tag: TAG20200924T173928
Piece Name: /u01/app/Backup_from_Source/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp
List of Datafiles in backup set 73
Container ID: 4, PDB Name: NEWPDB
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
41 Full 3286164 24-SEP-20 NO /u01/app/oracle/oradata/ORCL12C/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_system_hgnbd696_.dbf
42 Full 3286164 24-SEP-20 NO /u01/app/oracle/oradata/ORCL12C/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_sysaux_hgnbd6c1_.dbf
43 Full 3286164 24-SEP-20 NO /u01/app/oracle/oradata/ORCL12C/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_undotbs1_hgnbd6c2_.dbf
44 Full 3286164 24-SEP-20 NO /u01/app/oracle/oradata/ORCL12C/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_my_user__hgnbjwg7_.dbf

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
74 23.55M DISK 00:00:03 24-SEP-20
BP Key: 74 Status: AVAILABLE Compressed: YES Tag: TAG20200924T174142
Piece Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp

List of Archived Logs in backup set 74
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 73 3030419 17-JUN-20 3033316 17-JUN-20
1 74 3033316 17-JUN-20 3033319 17-JUN-20
1 75 3033319 17-JUN-20 3033326 17-JUN-20
1 76 3033326 17-JUN-20 3033329 17-JUN-20
1 77 3033329 17-JUN-20 3033340 17-JUN-20
1 78 3033340 17-JUN-20 3033343 17-JUN-20
1 79 3033343 17-JUN-20 3033358 17-JUN-20
1 80 3033358 17-JUN-20 3035646 17-JUN-20
1 81 3035646 17-JUN-20 3035675 17-JUN-20
1 82 3035675 17-JUN-20 3036658 17-JUN-20
1 83 3036658 17-JUN-20 3038913 09-JUL-20
1 84 3038913 09-JUL-20 3057240 13-JUL-20
1 85 3057240 13-JUL-20 3163574 23-SEP-20
1 86 3163574 23-SEP-20 3165215 23-SEP-20
1 87 3165215 23-SEP-20 3165221 23-SEP-20
1 88 3165221 23-SEP-20 3165687 23-SEP-20
1 89 3165687 23-SEP-20 3165755 23-SEP-20
1 90 3165755 23-SEP-20 3165858 23-SEP-20
1 91 3165858 23-SEP-20 3167178 23-SEP-20
1 92 3167178 23-SEP-20 3168603 23-SEP-20
1 93 3168603 23-SEP-20 3284332 24-SEP-20
1 94 3284332 24-SEP-20 3285739 24-SEP-20
1 95 3285739 24-SEP-20 3285960 24-SEP-20
1 96 3285960 24-SEP-20 3286131 24-SEP-20

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
75 Full 327.08M DISK 00:00:31 24-SEP-20
BP Key: 75 Status: AVAILABLE Compressed: YES Tag: TAG20200924T173928
Piece Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp
List of Datafiles in backup set 75
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 3286067 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/system01.dbf
3 Full 3286067 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
7 Full 3286067 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/users01.dbf
15 Full 3286067 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/undotbs2.dbf

RMAN>


From "eyeballing" the output, I can see that :
a. the highest Checkpoint SCN for datafiles is 3286164 (for PDB "NEWPDB")
but
b. the highest ArchiveLog SCN is 3286130 (3286131-1) from Sequence#96.
Quite obviously, I do not have enough Redo Information in the ArchiveLogs to be able to RECOVER to a consistent SCN.
Of course, the RMAN LIST BACKUP listing is quite short here.  What if it was very long  ?  How would I "query" ?  Using SQL, of course.

$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 25 23:18:31 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session set nls_date_format='DD-MON-RR HH24:MI';

Session altered.

SQL>
SQL> select df.con_id, max(df.checkpoint_change#)
2 from v$backup_datafile df, v$database d
3 where df.resetlogs_change#=d.resetlogs_change#
4 and df.con_id > 0
5 group by df.con_id
6 /

CON_ID MAX(DF.CHECKPOINT_CHANGE#)
---------- --------------------------
1 3286305
2 1443131
3 3285704
4 3286164

SQL>
SQL> select df.con_id, max(df.checkpoint_time)
2 from v$backup_datafile df, v$database d
3 where df.resetlogs_change#=d.resetlogs_change#
4 and df.con_id > 0
5 group by df.con_id
6 /

CON_ID MAX(DF.CHECKPOI
---------- ---------------
1 24-SEP-20 17:43
2 02-MAR-17 07:57
3 24-SEP-20 17:39
4 24-SEP-20 17:41

SQL>

SQL> select arc.thread#, max(arc.next_change#)-1
2 from v$backup_archivelog_details arc, v$database d
3 where arc.resetlogs_change#=d.resetlogs_change#
4 group by arc.thread#
5 /

THREAD# MAX(ARC.NEXT_CHANGE#)-1
---------- -----------------------
1 3286130

SQL>
SQL> select arc.thread#, max(arc.next_time)-1/1440
2 from v$backup_archivelog_details arc, v$database d
3 where arc.resetlogs_change#=d.resetlogs_change#
4 group by arc.thread#
5 /

THREAD# MAX(ARC.NEXT_TI
---------- ---------------
1 24-SEP-20 17:40

SQL>
SQL> select arc.thread#, max(arc.sequence#)
2 from v$backup_archivelog_details arc, v$database d
3 where arc.resetlogs_change#=d.resetlogs_change#
4 group by arc.thread#
5 /

THREAD# MAX(ARC.SEQUENCE#)
---------- ------------------
1 96

SQL>



The information I get is that CON_ID=4 (which is NEWPDB) has at least one datafile at a higher Checkpoint SCN and Time then the last ArchiveLog in the backup.
Therefore, I would not be able to do an OPEN RESETLOGS because Oracle will expect some more Redo to be applied (from at least Sequence #97).

Why do I query for CON_ID > 0 ?  Because CON_ID=0 is for the CDB, not the actual Root (which is CON_ID=1)

Why I do filter for RESETLOGS_CHANGE#?  Because I want to query for the current Incarnation of the database, as reflected in the Controlfile.  


Categories: DBA Blogs

Microsoft Azure Data Engineer Associate DP-200/DP-201: All You Need To Know

Online Apps DBA - 11 hours 41 min ago

The Microsoft Azure Data Engineer Associate Certification is for those candidates who are interested in implementing and designing the management, monitoring, security, and privacy of data using the full stack of Azure data services to satisfy business needs. Want to know more about the Microsoft Azure Data Engineer Certification? Read the blog post at https://k21academy.com/dp20011 […]

The post Microsoft Azure Data Engineer Associate DP-200/DP-201: All You Need To Know appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Microsoft Certified Azure Data Engineer Associate (DP-200 and DP-201): Step By Step Activity Guides (Hands-On Labs)

Online Apps DBA - 11 hours 48 min ago

Microsoft Azure Data Engineers design and implement the management, monitoring, security, and privacy of data using the full stack of Azure data services to satisfy business needs. There are some Activity Guides or Hands-on which are part of DP-200 and DP-201. Want to know more about the hands-on required to clear the certification? Read the […]

The post Microsoft Certified Azure Data Engineer Associate (DP-200 and DP-201): Step By Step Activity Guides (Hands-On Labs) appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

AZ-304 | Microsoft Azure Architect Design | Step By Step Activity Guides (Hands-On Labs)

Online Apps DBA - 12 hours 25 min ago

The Microsoft Azure Architect Design AZ-304 Exam meshes towards those who advise collaborators and translate business requirements into scalable, protected, and well-founded solutions. Check out the blog at https://k21academy.com/az30405 where we have mentioned all about AZ-304 that covers all the Activity Guides which you’ll need to pass the AZ304 exam with flying colors as well […]

The post AZ-304 | Microsoft Azure Architect Design | Step By Step Activity Guides (Hands-On Labs) appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

AZ-303 | Microsoft Azure Architect Technologies | Step By Step Activity Guides (Hands-On Labs)

Online Apps DBA - 12 hours 37 min ago

The Microsoft Azure Architect Technologies AZ-303 exam mesh towards those who advise collaborators and translate business requirements into scalable, protected, and well-founded solutions. Read the blog post at k21academy.com/az30305 which gives a walkthrough of the Step-By-Step Activity Guides of the Microsoft Azure Architect Technologies AZ-303 Training program that you must perform to learn this course. […]

The post AZ-303 | Microsoft Azure Architect Technologies | Step By Step Activity Guides (Hands-On Labs) appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Oracle 19c Automatic Indexing: Data Skew Fixed By Baselines Part I (The Prettiest Star))

Richard Foote - Thu, 2020-09-24 20:53
In my previous few blog posts, I’ve been discussing some issues in relation to how Automatic Indexes handle SQL statements that accesses skewed data. In this post, I’m going to setup the scenario in which Automatic Indexing can potentially use Baselines to help address some of these issues. BUT, as we’ll see, I’m having to […]
Categories: DBA Blogs

Mission accomplished: OpenHack: Migrating Microsoft Workloads to Azure

Dietrich Schroff - Thu, 2020-09-24 14:42

 After three days of hard work i got my first OPENHACK badge:


Authorized by Microsoft

Here the details from Microsoft:

Earners of the OpenHack: Migration badge understand how to execute an end-to-end migration through optimization. They have shown that they can utilize Azure Migrate to migrate virtual machines to Microsoft Azure and can modernize legacy applications by migrating to PaaS services such as Azure SQL Database and Azure App Service. They have also have a foundational understanding of Azure identity, including hybrid identity with Azure AD and how to leverage Azure RBAC to govern and secure workloads.

It was really a great challenge to discuss and implement all the goals. Thanks to the excellent coaches and for providing the infrastructure!

Amazon AWS SageMaker For Machine Learning: Overview & Capabilities

Online Apps DBA - Thu, 2020-09-24 11:12

AWS SageMaker is a managed service from Amazon that provides developers and data scientists with the ability to build, train, and deploy machine learning models. Want to know more about AWS SageMaker and how it can be used for Machine Learning Models? Read the blog post at https://k21academy.com/dp10016 to learn more. The blog post covers: […]

The post Amazon AWS SageMaker For Machine Learning: Overview & Capabilities appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Convolutional Neural Network (CNN) | Azure Machine Learning

Online Apps DBA - Thu, 2020-09-24 11:04

Machine learning is a subset of Artificial Intelligence. We mostly use CNN’s in Computer Vision for image classification, object detection, and Neural Style Transfer. Check this blog https://k21academy.com/dp10015 which will cover the basics of the Convolutional Neural Network (CNN) and how we train our convolutional neural networks model on Azure ML service without knowing to […]

The post Convolutional Neural Network (CNN) | Azure Machine Learning appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Certified Kubernetes Security Specialist (CKS): Step-by-Step Activity Guide (Hands-on Lab)

Online Apps DBA - Thu, 2020-09-24 10:40

Certified Kubernetes Security Specialist (CKS) is designed for those who are already CKA Certified and want to move ahead in security. Security is something everyone is concern about. So CNCF has planned to add a new Certified Kubernetes Security Specialist (CKS) to the growing list of Kubernetes certification programs. To find out all about our […]

The post Certified Kubernetes Security Specialist (CKS): Step-by-Step Activity Guide (Hands-on Lab) appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Certified Kubernetes Security Specialist (CKS): Everything You Must Know

Online Apps DBA - Thu, 2020-09-24 09:26

Security is the first and foremost thing everyone is concern about. Everything is moving towards containers these days, working with a large crowd needs security as well. So CNCF has planned to add a new Certified Kubernetes Security Specialist (CKS) to the growing list of Kubernetes certification programs. CKS Certification will be available by November […]

The post Certified Kubernetes Security Specialist (CKS): Everything You Must Know appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

静岡で水道修理を依頼する場合の方法

The Feature - Thu, 2020-09-24 05:57

水道が故障した場合には、とても不便を感じると同時に水漏れや詰まりなどの状況で、住宅設備にも被害を与えてしまうおそれもあります。そのためにも速やかな水道修理が必要となりますが、ご自身で対処しようとお考えの方も少なくないでしょう。一般的な修理やつまりの解消などは、静岡にも多数あるホームセンター等で購入できる部品や補修材、パイプクリーナー等で対処できる場合もあります。作業には経験や技術が必要なために、手慣れている人や器用な人であれば比較的スムーズに水道修理ができるかもしれませんが、状況を悪化させてしまうことが心配な方や、失敗をしたくないという方であれば、やはり静岡の水道修理会社に連絡をして作業を行ってもらうことが最適解と言えます。

水道関係の作業を請け負っている会社は静岡に多数ありますので、依頼先を見つける方法としてはインターネットの情報サイトがお勧めです。実績が豊富で対応が良好な水道修理の会社は、静岡に多く存在していますので、それらの中からより信頼性が高く技術力のある会社を見つけることで快適な生活を守ることにも繋がります。普段から水道の不具合が生じた場合には、どこに連絡して修理依頼をすればよいのかを把握しておくこともおすすめです。インターネットのウェブサイトで適切な情報を集め、水道修理をきちんと行ってもらいましょう。

水道は誰もが毎日使う設備ですので、いつでも快適な状態に保つことがとても大切です。

Categories: APPS Blogs

静岡で水道修理を依頼する場合の方法

Marian Crkon - Thu, 2020-09-24 05:57
水道が故障した場合には、とても不便を感じると同時に水漏れや詰まりなどの状況で、住宅設備にも被害を与えてしまうおそれもあります。そのためにも速やかな水道修理が必要となりますが、ご自身で対処しようとお考えの方も少なくないでしょう。一般的な修理やつまりの解消などは、静岡にも多数あるホームセンター等で購入できる部品や補修材、パイプクリーナー等で対処できる場合もあります。作業には経験や技術が必要なために、手慣れている人や器用な人であれば比較的スムーズに水道修理ができるかもしれませんが、状況を悪化させてしまうことが心配な方や、失敗をしたくないという方であれば、やはり静岡の水道修理会社に連絡をして作業を行ってもらうことが最適解と言えます。 水道関係の作業を請け負っている会社は静岡に多数ありますので、依頼先を見つける方法としてはインターネットの情報サイトがお勧めです。実績が豊富で対応が良好な水道修理の会社は、静岡に多く存在していますので、それらの中からより信頼性が高く技術力のある会社を見つけることで快適な生活を守ることにも繋がります。普段から水道の不具合が生じた場合には、どこに連絡して修理依頼をすればよいのかを把握しておくこともおすすめです。インターネットのウェブサイトで適切な情報を集め、水道修理をきちんと行ってもらいましょう。 水道は誰もが毎日使う設備ですので、いつでも快適な状態に保つことがとても大切です。

BARC Survey confirms: #Exasol dominates Analytical Database Peers

The Oracle Instructor - Thu, 2020-09-24 05:23

Exasol leads the categories Performance, Platform Reliability and Support Quality for Analytical Database products. And we get a 100% recommendation score from the 782 customers in the survey.

So it’s not one of the big names in the industry who comes out on top of this survey. Not Oracle, not Teradata, not Snowflake, not SAP Hana leads in Analytical Databases but Exasol!

Customer quote: “Unbelievable query performance with almost zero administration effort. You just have to experience it yourself. Once you see it for yourself, you won’t want to work with any other database.”

 

 

 

To summarize:

  1. Exasol is the world’s fastest analytical database
  2. Exasol is reliable and easy to maintain
  3. Exasol’s services and attitude towards customers are highly appreciated

Compare that with your legacy platform: It’s time to contact us now!

Categories: DBA Blogs

Partner Webcast – Enterprise Features for Hosting Web Apps on OCI

Web-based applications have become an essential component of business in today's world, be it Hosted, SaaS or on-demand applications. The majority of users is typically expecting easy, via a web...

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

What are the alternatives for ORA_EXCEL (Oracle Excel) is a PL/SQL package for Oracle® database that produces Excel XLSX documents?

Tom Kyte - Wed, 2020-09-23 12:26
I wish to generate reports from my Oracle 11g database in an MS excel document(multiple tabs). Do we have a solution or a product from Oracle for this task? Thank you, Sunil
Categories: DBA Blogs

how to use profiler (similar to MS SQL Server) in Oracle SQL Developer

Tom Kyte - Wed, 2020-09-23 12:26
how to use profiler (similar to MS SQL Server) in Oracle SQL Developer
Categories: DBA Blogs

Split Records into Groups of N Rows

Tom Kyte - Wed, 2020-09-23 12:26
Got a SQL from the table with 100,000 + records , one particular varchar2 field contains strings like '12345', '56789', '1111'. I would like to create a table with split / chunks with appropriate comma seperate example in the live link ::: select listagg(id, ',') within group (order by id) from (select course_id as id from ad.AD_STUDENT_COURSE_DETAILS) Example :: 100 records each record has the following ids <code>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99</code> Now Assume based on the above result sets I should be able to make my sequence dynamic and insert group of records into comma separated values (listagg) , on my own definition If I give 22 it should get values and distribute into 4 chunks and the remaining left based on the select query results and make the respective chunks of sequence. Here in this example the 99 records from my select query have been loaded into new table with 5 records (5 sequence number) each record carry the 22 records and the remaining. New Table <code>seq_number list_of_ids 1 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22 2 23,2425,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44 3 45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66 4 67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88 5 89,90,91,92,93,94,95,96,97,98,99</code> Is it possible to achieve the same in SQL query to who the results in above format or PL/SQL block? Appreciate your help.
Categories: DBA Blogs

impdp truncate date imported in timestamp

Tom Kyte - Wed, 2020-09-23 12:26
Hi, I created a table with a date column in an Oracle 18 database and I filled it with some date values: create table test_date (col1 date); insert into test_date values (sysdate); insert into test_date values (sysdate); select * from test_date; COL1 ------------------- 22.09.2020 16:33:05 22.09.2020 16:33:15 The same table is created in an Oracle 19 database but with a timestamp(6) column: create table test_date (col1 timestamp(6)) If I import the table from 18 to 19 the date values are truncated (I used a database link but the error occurs also with a dumpfile): impdp system/...@ora_instance CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=TRUNCATE TABLES=user_in_18db.test_date network_link=db_link_from_19_to_18 remap_schema=user_in_18db:user_in_19db Import: Release 19.0.0.0.0 - Production on Tue Sep 22 16:45:26 2020 Version 19.5.0.0.0 ... . . "USER_IN_19DB"."TEST_DATE" 2 Zeilen importiert Job "SYSTEM"."SYS_IMPORT_TABLE_01" erfolgreich um Di Sep 22 16:45:37 2020 elapsed 0 00:00:10 abgeschlossen but select * from test_date; -- in the Oracle 19 database COL1 ------------------- 22.09.2020 00:00:00 22.09.2020 00:00:00 For output I am using: NLS_TIMESTAMP_FORMAT = 'DD.MM.YYYY HH24:MI:SS' NLS_DATE_FORMAT = 'DD.MM.YYYY HH24:MI:SS' YS Nicola
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator