Hemant K Chitale

Subscribe to Hemant K Chitale feed
I am an Oracle Database Specialist in Singapore. Please note that this site uses cookies.

Updated: 2 hours 40 min ago

RESTORE and RECOVER a NOARCHIVELOG Database, with Incremental Backups -- 2nd Post

Thu, 2021-02-18 08:10

 As a follow up on a question in the previous blog post,  I demonstrate it again without restoring the Controlfile


The current SCN and available backups (Level-0 and Level-1) :



SQL> select  current_scn from v$database;

CURRENT_SCN
-----------
1084836

SQL>
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /opt/oracle/archivelog/HEMANT
Oldest online log sequence 6
Current log sequence 8
SQL>
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 Thu Feb 18 21:50:02 2021
Version 19.3.0.0.0

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

connected to target database: HEMANT (DBID=432411782)

RMAN> list backup;

using target database control file instead of recovery catalog

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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
44 Incr 0 67.09M DISK 00:00:19 18-FEB-21
BP Key: 44 Status: AVAILABLE Compressed: YES Tag: TAG20210218T212223
Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/19vnh8kg_1_1
List of Datafiles in backup set 44
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 0 Incr 974397 18-FEB-21 NO /opt/oracle/oradata/HEMANT/system.dbf
2 0 Incr 974397 18-FEB-21 NO /opt/oracle/oradata/HEMANT/sysaux.dbf
3 0 Incr 974397 18-FEB-21 NO /opt/oracle/oradata/HEMANT/undotbs.dbf
4 0 Incr 974397 18-FEB-21 NO /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
5 0 Incr 974397 18-FEB-21 NO /opt/oracle/oradata/HEMANT/indx01.dbf
6 0 Incr 974397 18-FEB-21 NO /opt/oracle/oradata/HEMANT/HR_DATA.dbf
10 0 Incr 974397 18-FEB-21 NO /opt/oracle/oradata/HEMANT/indx02.dbf
11 0 Incr 974397 18-FEB-21 NO /opt/oracle/oradata/HEMANT/indx03.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
45 Full 11.52M DISK 00:00:02 18-FEB-21
BP Key: 45 Status: AVAILABLE Compressed: NO Tag: TAG20210218T212249
Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20210218-00
SPFILE Included: Modification time: 18-FEB-21
SPFILE db_unique_name: HEMANT
Control File Included: Ckp SCN: 974397 Ckp time: 18-FEB-21

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
46 Incr 1 1.84M DISK 00:00:16 18-FEB-21
BP Key: 46 Status: AVAILABLE Compressed: YES Tag: TAG20210218T212541
Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/1bvnh8ql_1_1
List of Datafiles in backup set 46
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 975490 18-FEB-21 NO /opt/oracle/oradata/HEMANT/system.dbf
2 1 Incr 975490 18-FEB-21 NO /opt/oracle/oradata/HEMANT/sysaux.dbf
3 1 Incr 975490 18-FEB-21 NO /opt/oracle/oradata/HEMANT/undotbs.dbf
4 1 Incr 975490 18-FEB-21 NO /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
5 1 Incr 975490 18-FEB-21 NO /opt/oracle/oradata/HEMANT/indx01.dbf
6 1 Incr 975490 18-FEB-21 NO /opt/oracle/oradata/HEMANT/HR_DATA.dbf
10 1 Incr 975490 18-FEB-21 NO /opt/oracle/oradata/HEMANT/indx02.dbf
11 1 Incr 975490 18-FEB-21 NO /opt/oracle/oradata/HEMANT/indx03.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
47 Full 11.52M DISK 00:00:01 18-FEB-21
BP Key: 47 Status: AVAILABLE Compressed: NO Tag: TAG20210218T212606
Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20210218-01
SPFILE Included: Modification time: 18-FEB-21
SPFILE db_unique_name: HEMANT
Control File Included: Ckp SCN: 975490 Ckp time: 18-FEB-21

RMAN>


Now my current SCN is 1084826 .
My Level-0 Backup (BackupSet 44) was at 974397 (and so was the Controlfile backup in BackupSet 45)
My Level-1 Backup (BackupSet 46) was at 975490 (and so was the Controlfile backup in BackupSet 47).

So, all of those backups are older than the current SCN.

Can I restore and recover the database without restoring the Controlfile ?


RMAN> quit


Recovery Manager complete.
oracle19c>rm /opt/oracle/oradata/HEMANT/system.dbf
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 18 21:53:38 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> select current_scn from v$database;

CURRENT_SCN
-----------
1084978

SQL>
SQL> shutdown immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/opt/oracle/oradata/HEMANT/system.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
toracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 18 21:57:23 2021
Version 19.3.0.0.0

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area 1207958960 bytes

Fixed Size 8895920 bytes
Variable Size 318767104 bytes
Database Buffers 872415232 bytes
Redo Buffers 7880704 bytes

RMAN>
RMAN> restore database;

Starting restore at 18-FEB-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=257 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 /opt/oracle/oradata/HEMANT/system.dbf
channel ORA_DISK_1: restoring datafile 00002 to /opt/oracle/oradata/HEMANT/sysaux.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/HEMANT/undotbs.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
channel ORA_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/HEMANT/indx01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /opt/oracle/oradata/HEMANT/HR_DATA.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/oracle/oradata/HEMANT/indx02.dbf
channel ORA_DISK_1: restoring datafile 00011 to /opt/oracle/oradata/HEMANT/indx03.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/product/19c/dbhome_1/dbs/19vnh8kg_1_1
channel ORA_DISK_1: piece handle=/opt/oracle/product/19c/dbhome_1/dbs/19vnh8kg_1_1 tag=TAG20210218T212223
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 18-FEB-21

RMAN> recover database noredo;

Starting recover at 18-FEB-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /opt/oracle/oradata/HEMANT/system.dbf
destination for restore of datafile 00002: /opt/oracle/oradata/HEMANT/sysaux.dbf
destination for restore of datafile 00003: /opt/oracle/oradata/HEMANT/undotbs.dbf
destination for restore of datafile 00004: /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
destination for restore of datafile 00005: /opt/oracle/oradata/HEMANT/indx01.dbf
destination for restore of datafile 00006: /opt/oracle/oradata/HEMANT/HR_DATA.dbf
destination for restore of datafile 00010: /opt/oracle/oradata/HEMANT/indx02.dbf
destination for restore of datafile 00011: /opt/oracle/oradata/HEMANT/indx03.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/product/19c/dbhome_1/dbs/1bvnh8ql_1_1
channel ORA_DISK_1: piece handle=/opt/oracle/product/19c/dbhome_1/dbs/1bvnh8ql_1_1 tag=TAG20210218T212541
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

Finished recover at 18-FEB-21

RMAN>
RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 02/18/2021 21:59:38
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

RMAN>
RMAN> exit


Recovery Manager complete.
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 18 22:00:12 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> recover datbase using backup controlfile until cancel;
ORA-00905: missing keyword


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 975490 generated at 02/18/2021 21:25:11 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/HEMANT/1_4_1063318051.dbf
ORA-00280: change 975490 for thread 1 is in sequence #4


Specify log: {=suggested | filename | AUTO | CANCEL} -- commented the RET as it was being treated as an HTML Tag
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
975656

SQL>


I can't exactly use the same method as I did in the previous blog post.  This is because RMAN doesn't properly recognise this as an Incomplete Recovery if I have used the current Controlfile.
What is the workaround ?  Use sqlplus !  I can use the SQL command "recover database using backup controlfile until cancel" and then CANCEL to simulate an Incomplete Recovery that allows me to "open resetlogs" !
This is similar to the "OPEN RESETLOGS without really doing a Recovery" demo that I had presented earlier.

Note : The CURRENT_SCN is now 975656.  This is because I have restored and recovered from "older" database backups (BackupSet 45 at SCN 975490) and ignored any transactions after those backups.  The OPEN RESETLOGS recreates the Online Redo Logs and resynchronizes the Controlfile but, in the process some SCNs are incremented so it is higher than 975490 and lower than 1084978.
This doesn't mean that User Transactions after 975490 have been recovered. They have, actually been discarded.


Categories: DBA Blogs

Checking for Active Transactions

Thu, 2021-02-11 03:18

 Oracle 11.2 introduced the WAIT_ON_PENDING_DML function in the DBMS_UTILITY Package.

Here is a demonstration of how to use it :



17:05:21 SQL> @Check_for_Transactions
17:05:22 SQL> declare
17:05:22 2 check_for_transactions boolean;
17:05:22 3 scnvalue number;
17:05:22 4 begin
17:05:22 5 check_for_transactions := dbms_utility.wait_on_pending_dml
17:05:22 6 (tables=>'HEMANT.MY_TXN_TABLE',
17:05:22 7 timeout=>60,
17:05:22 8 scn=>scnvalue);
17:05:22 9 if check_for_transactions then
17:05:22 10 dbms_output.put_line('No Transaction(s) at start time OR Transactions present at start have committed before Timeout');
17:05:22 11 dbms_output.put_line('Other Transactions *may* have begun after the start of this Check');
17:05:22 12 else
17:05:22 13 dbms_output.put_line('One or More Active Transaction(s) present until Timeout');
17:05:22 14 end if;
17:05:22 15 end;
17:05:22 16 /
No Transaction(s) at start time OR Transactions present at start have committed before Timeout
Other Transactions *may* have begun after the start of this Check

PL/SQL procedure successfully completed.

17:05:22 SQL>


17:05:43 SQL> @Check_for_Transactions
17:05:44 SQL> declare
17:05:44 2 check_for_transactions boolean;
17:05:44 3 scnvalue number;
17:05:44 4 begin
17:05:44 5 check_for_transactions := dbms_utility.wait_on_pending_dml
17:05:44 6 (tables=>'HEMANT.MY_TXN_TABLE',
17:05:44 7 timeout=>60,
17:05:44 8 scn=>scnvalue);
17:05:44 9 if check_for_transactions then
17:05:44 10 dbms_output.put_line('No Transaction(s) at start time OR Transactions present at start have committed before Timeout');
17:05:44 11 dbms_output.put_line('Other Transactions *may* have begun after the start of this Check');
17:05:44 12 else
17:05:44 13 dbms_output.put_line('One or More Active Transaction(s) present until Timeout');
17:05:44 14 end if;
17:05:44 15 end;
17:05:44 16 /
One or More Active Transaction(s) present until Timeout

PL/SQL procedure successfully completed.

17:06:44 SQL>


17:07:08 SQL> @Check_for_Transactions
17:07:09 SQL> declare
17:07:09 2 check_for_transactions boolean;
17:07:09 3 scnvalue number;
17:07:09 4 begin
17:07:09 5 check_for_transactions := dbms_utility.wait_on_pending_dml
17:07:09 6 (tables=>'HEMANT.MY_TXN_TABLE',
17:07:09 7 timeout=>60,
17:07:09 8 scn=>scnvalue);
17:07:09 9 if check_for_transactions then
17:07:09 10 dbms_output.put_line('No Transaction(s) at start time OR Transactions present at start have committed before Timeout');
17:07:09 11 dbms_output.put_line('Other Transactions *may* have begun after the start of this Check');
17:07:09 12 else
17:07:09 13 dbms_output.put_line('One or More Active Transaction(s) present until Timeout');
17:07:09 14 end if;
17:07:09 15 end;
17:07:09 16 /
No Transaction(s) at start time OR Transactions present at start have committed before Timeout
Other Transactions *may* have begun after the start of this Check

PL/SQL procedure successfully completed.

17:07:23 SQL>


When I ran the Check code at17:05:22, there were no active transaction against the target table "HEMANT.MY_TXN_TABLE", so the Check completed immediately (the returned BOOLEAN is TRUE)

When I re-ran the Check code at 17:05:44, there were one or more transactions (uncommitted) present.  The Check code ran for 60 seconds until the specified timeout and returned the message "One or More Active Transaction(s) present until Timeout"

When I ran the Check code again at 17:07:09 there were one or more transactions present.  However, they committed within the 60seconds timeout so the Check ended at 17:07:23  (i.e. the function returned TRUE at 17:07:23)

However, this Check only checks for transactions present as at the time it began running.  If a third or fourth session begins a transaction after this start and yet does not commit, it would not be identified by this Check.  

With the caveat that the Check doesn't check for *new* transactions, this is useful when you are monitoring for the presence of transactions at a specific time --- .e.g you expected an ETL job to complete by 17:30 and know that no other session would have any transaction against the target table.

The "TABLES" parameter can actually take a comma-separated list of tables.
The "SCN" parameter is an IN OUT in that you can put in a specific SCN prior to which transactions may begun.  If a NULL or invalid value is passed, the function takes the current SCN.

Categories: DBA Blogs

RESTORE and RECOVER a NOARCHIVELOG Database, with Incremental Backups

Sun, 2021-01-31 08:25

 Oracle does support all Incremental (as well as Full) Backups of Databases running in NOARCHIVELOG mode.  Such backups can be made when the database is in MOUNT (not OPEN) mode.

There are 2 "downsides" to Backups in NOARCHIVELOG mode :

1.  The database is unavailable (as it is not OPEN) for the duration of the BACKUP DATABASE run.  So, it would be a good idea to make frequent Incremental Level-1 backups as they could be faster (shorter duration) than the Level-0 backups (which could be scheduled during longer maintenance weekend hours)

2. If you lose any datafile(s) (one or more) you have to RESTORE and RECOVER the *whole* database.  You cannot restore and recover individual datafiles for a database in NOARCHIVELOG mode as you would be able to do with backups with ArchiveLogs.


oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jan 31 20:01:50 2021
Version 19.3.0.0.0

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

connected to target database: HEMANT (DBID=432411782)

RMAN> backup as compressed backupset incremental level 0 database ;

Starting backup at 31-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 01/31/2021 20:02:07
RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode

RMAN>
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 1207958960 bytes

Fixed Size 8895920 bytes
Variable Size 318767104 bytes
Database Buffers 872415232 bytes
Redo Buffers 7880704 bytes

RMAN> backup as compressed backupset incremental level 0 database ;

Starting backup at 31-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=257 device type=DISK
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/HEMANT/system.dbf
input datafile file number=00002 name=/opt/oracle/oradata/HEMANT/sysaux.dbf
input datafile file number=00003 name=/opt/oracle/oradata/HEMANT/undotbs.dbf
input datafile file number=00004 name=/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
input datafile file number=00005 name=/opt/oracle/oradata/HEMANT/indx01.dbf
input datafile file number=00010 name=/opt/oracle/oradata/HEMANT/indx02.dbf
input datafile file number=00011 name=/opt/oracle/oradata/HEMANT/indx03.dbf
input datafile file number=00006 name=/opt/oracle/oradata/HEMANT/HR_DATA.dbf
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/14vm1l86_1_1 tag=TAG20210131T200317 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 31-JAN-21

Starting Control File and SPFILE Autobackup at 31-JAN-21
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20210131-00 comment=NONE
Finished Control File and SPFILE Autobackup at 31-JAN-21

RMAN>
RMAN> exit


Recovery Manager complete.
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 20:05:44 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> select max(sequence#), current_scn from v$log, v$database group by current_scn;

MAX(SEQUENCE#) CURRENT_SCN
-------------- -----------
865 864084

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /opt/oracle/archivelog/HEMANT
Oldest online log sequence 863
Current log sequence 865
SQL>


At approximately 20:05 on 31-Jan-2021, the database is in NOARCHIVELOG mode. So, an RMAN BACKUP DATABASE command fails when the Database is OPEN.  I must restart the Database Instance in MOUNT (no OPEN) state to run an RMAN Backup.  I am particular to make this backup explicitly a Level-0 backup so that I can later take a Level-1 backup.

The highest Log Sequence# is 865 (the CURRENT Redo Log file) and the SCN is 864084.

Later ...


oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 21:40:27 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> select max(sequence#), current_scn from v$log, v$database group by current_scn;

MAX(SEQUENCE#) CURRENT_SCN
-------------- -----------
872 869174

SQL> select count(*) from hemant.my_test_table;

COUNT(*)
----------
24554

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
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 Sun Jan 31 21:43:17 2021
Version 19.3.0.0.0

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

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area 1207958960 bytes

Fixed Size 8895920 bytes
Variable Size 318767104 bytes
Database Buffers 872415232 bytes
Redo Buffers 7880704 bytes

RMAN> backup as compressed backupset incremental level 1 database;

Starting backup at 31-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=257 device type=DISK
channel ORA_DISK_1: starting compressed incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/HEMANT/system.dbf
input datafile file number=00002 name=/opt/oracle/oradata/HEMANT/sysaux.dbf
input datafile file number=00003 name=/opt/oracle/oradata/HEMANT/undotbs.dbf
input datafile file number=00004 name=/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
input datafile file number=00005 name=/opt/oracle/oradata/HEMANT/indx01.dbf
input datafile file number=00010 name=/opt/oracle/oradata/HEMANT/indx02.dbf
input datafile file number=00011 name=/opt/oracle/oradata/HEMANT/indx03.dbf
input datafile file number=00006 name=/opt/oracle/oradata/HEMANT/HR_DATA.dbf
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/16vm1r4l_1_1 tag=TAG20210131T214349 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 31-JAN-21

Starting Control File and SPFILE Autobackup at 31-JAN-21
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20210131-01 comment=NONE
Finished Control File and SPFILE Autobackup at 31-JAN-21

RMAN>
RMAN> alter database open;

Statement processed

RMAN> exit


Recovery Manager complete.
oracle19c>


So, with further transactions between 20:05 and 21:43, the highest Log Sequence# has gone from 865 to 872 (none of which are Archived) and the Database SCN has gone from 864084 to 869174.
I use the table "HEMANT.MY_TEST_TABLE" as the reference table at this point.

After some time :
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 21:57:21 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> select max(sequence#), current_scn from v$log, v$database group by current_scn;

MAX(SEQUENCE#) CURRENT_SCN
-------------- -----------
877 870492

SQL> select count(*) from hemant.my_test_table;

COUNT(*)
----------
27554

SQL>


There have been more transactions (incremented Log Sequence#, SCN and Row Count). However, I do not have a fresh backup of the database (and the database does not generate ArchiveLogs).

If I lose some or all of the Datafiles (and, possibly, even the Redo Log and Control Files) :


SQL> shutdown immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/opt/oracle/oradata/HEMANT/system.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>

RMAN> shutdown abort;

using target database control file instead of recovery catalog
Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 1207958960 bytes

Fixed Size 8895920 bytes
Variable Size 318767104 bytes
Database Buffers 872415232 bytes
Redo Buffers 7880704 bytes

RMAN> restore controlfile from '/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20210131-01';

Starting restore at 31-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/oracle/oradata/HEMANT/control01.ctl
output file name=/opt/oracle/oradata/HEMANT/control02.ctl
Finished restore at 31-JAN-21

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN>
RMAN> restore database;

Starting restore at 31-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 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 /opt/oracle/oradata/HEMANT/system.dbf
channel ORA_DISK_1: restoring datafile 00002 to /opt/oracle/oradata/HEMANT/sysaux.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/HEMANT/undotbs.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
channel ORA_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/HEMANT/indx01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /opt/oracle/oradata/HEMANT/HR_DATA.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/oracle/oradata/HEMANT/indx02.dbf
channel ORA_DISK_1: restoring datafile 00011 to /opt/oracle/oradata/HEMANT/indx03.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/product/19c/dbhome_1/dbs/14vm1l86_1_1
channel ORA_DISK_1: piece handle=/opt/oracle/product/19c/dbhome_1/dbs/14vm1l86_1_1 tag=TAG20210131T200317
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 31-JAN-21

RMAN> recover database noredo;

Starting recover at 31-JAN-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /opt/oracle/oradata/HEMANT/system.dbf
destination for restore of datafile 00002: /opt/oracle/oradata/HEMANT/sysaux.dbf
destination for restore of datafile 00003: /opt/oracle/oradata/HEMANT/undotbs.dbf
destination for restore of datafile 00004: /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
destination for restore of datafile 00005: /opt/oracle/oradata/HEMANT/indx01.dbf
destination for restore of datafile 00006: /opt/oracle/oradata/HEMANT/HR_DATA.dbf
destination for restore of datafile 00010: /opt/oracle/oradata/HEMANT/indx02.dbf
destination for restore of datafile 00011: /opt/oracle/oradata/HEMANT/indx03.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/product/19c/dbhome_1/dbs/16vm1r4l_1_1
channel ORA_DISK_1: piece handle=/opt/oracle/product/19c/dbhome_1/dbs/16vm1r4l_1_1 tag=TAG20210131T214349
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

Finished recover at 31-JAN-21

RMAN>
RMAN> alter database open resetlogs;

Statement processed

RMAN> exit


Recovery Manager complete.
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 22:07:53 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> select current_scn from v$database;

CURRENT_SCN
-----------
870157

SQL> select max(sequence#) from v$log;

MAX(SEQUENCE#)
--------------
1

SQL>
SQL> select count(*) from hemant.my_test_table;

COUNT(*)
----------
24554

SQL>


Note that I was able to use the controlfile autobackup. Then, the RESTORE DATABASE command restored datafiles from the Backup Piece(s) /opt/oracle/product/19c/dbhome_1/dbs/14vm1l86_1_1 that contained the Level-0 backup.  The RECOVER DATABASE NOREDO actually copied datafile incremental changes from the Level-1 backup piece /opt/oracle/product/19c/dbhome_1/dbs/16vm1r4l_1_1.  

I have to OPEN RESETLOGS because I must discard the Online Redo Logs as they are not consistent with what been restored (the Online Redo Logs, even if still present on disk, are in the "future" of the Recover and I do not have ArchiveLogs to bring the datafiles in sync).  The Redo Logs get reset to Sequence#=1.  The CURRENT_SCN would be slightly higher than the SCN recorded at the time of the backup -- it should not be lower than that at the time of the Incremental Backup.

All new rows inserted in the MY_TEST_TABLE are lost, as the Row Count reverts to 24,554 that was present when the Level-1 backup was taken.  All other transactions (and SCN increments) since the Level-1 Backup are also lost.

Note : It does not matter that I don't use an FRA.  The method is the same whether an FRA is used or not.

This shows the Level-0 and Level-1 backps (I am listing for only 1 datafile).  
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jan 31 22:20:04 2021
Version 19.3.0.0.0

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

connected to target database: HEMANT (DBID=432411782)

RMAN> list backup of datafile 1;

using target database control file instead of recovery catalog

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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41 Incr 0 73.66M DISK 00:00:12 31-JAN-21
BP Key: 41 Status: AVAILABLE Compressed: YES Tag: TAG20210131T200317
Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/14vm1l86_1_1
List of Datafiles in backup set 41
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 0 Incr 863156 31-JAN-21 NO /opt/oracle/oradata/HEMANT/system.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
43 Incr 1 2.83M DISK 00:00:10 31-JAN-21
BP Key: 43 Status: AVAILABLE Compressed: YES Tag: TAG20210131T214349
Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/16vm1r4l_1_1
List of Datafiles in backup set 43
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 869282 31-JAN-21 NO /opt/oracle/oradata/HEMANT/system.dbf

RMAN>


For the Level-0 Backup, the datafile Checkpoint SCN is lower than that I had from the SQL Query because the SQL query was when the database was opened *after* the Backup.
For the Level-1 Backup, the datafile Checkpoint SCN is higher than that had from the SQL Query because the SQL query was before the Backup was taken.
Similarly, the Restored database has a higher SCN because the act of Restore+Recover+Open also increments the Database SCN.


Categories: DBA Blogs

RMAN's CATALOG command

Mon, 2021-01-25 09:14

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
(what I haven't demonstrated here is restoring a Full Database -- either on the same server or to another server, when the BackupPieces are at an alternate locatoin).


Categories: DBA Blogs

Datapump in Oracle ADB using SQL Developer Web

Thu, 2021-01-21 04:20

 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  :

Query


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

PLSQL Block


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" :

PL/SQL Block


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.  


Categories: DBA Blogs

Configuring Transparent Data Encryption -- 2 : For Columns

Thu, 2021-01-14 05:15
The previous demo of TDE in 19c was for a full Tablespace (converting an existing, non-TDE, Tablespace to an Encrypted Tablespace).

Pre-creating a Table with an Encrypted column would be straightforward :

CREATE TABLE employees (
emp_id number primary key,
first_name varchar2(128),
last_name varchar2(128),
national_id_no varchar2(18) encrypt,
salary number(6) )
tablespace hr_data
/


This encrypts the column with the AES encryption algorithm with a 192-bit key length ("AES192").

But what if you want to encrypt an existing, non-encrypted column ? You can use the MODIFY clause.

ALTER TABLE employees (
MODIFY (national_id_no encrypt)
/


A quick demo :

SQL> create tablespace hr_data datafile '/opt/oracle/oradata/HEMANT/HR_DATA.dbf' size 5M;

Tablespace created.

SQL> CREATE TABLE employees (
2 emp_id number primary key,
3 first_name varchar2(128),
4 last_name varchar2(128),
5 national_id_no varchar2(18),
6 salary number(6) )
7 tablespace hr_data;

Table created.

SQL> ^C

SQL> insert into employees
2 select rownum, 'Hemant', 'Hemant' || to_char(rownum), dbms_random.string('X',12), 1000
3 from dual
4 connect by level "less than" 21 --- "less than" symbol replaced by string to preserve HTML formatting
5 /

20 rows created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> !sync ; sync

SQL>
SQL> !strings -a /opt/oracle/oradata/HEMANT/HR_DATA.dbf | more
}|{z
HEMANT
3J?5
HR_DATA
H4J?
AAAAAAAA
Hemant
Hemant1
LH6RUZRISE11
Hemant
Hemant2
DFIN8FZ7B6J0
Hemant
Hemant3
PLJ1R2QYRG2C
Hemant
Hemant4
UT3HB9ALF3B5
Hemant
Hemant5
LQMDUTFB2PTM
Hemant
Hemant6
1IGKV4E78M5J
Hemant
Hemant7
P9TQAV5BC5EM
Hemant
Hemant8
V69U6VZWCK26
Hemant
Hemant9
EOTOQHOB0F45
Hemant
Hemant10
OKMEV89XOQE1
Hemant
Hemant11
0D4L77P3YNF0
Hemant
Hemant12
CTMCLJSKQW82
Hemant
Hemant13
49T0AG7E2Y9X
Hemant
Hemant14
ODEY2J51D8RH
Hemant
Hemant15
R1HFMN34MYLH
Hemant
Hemant16
OXI0LOX161BO
Hemant
Hemant17
2XL44ZJVABGW
Hemant
Hemant18
4BIPWVECBWYO
Hemant
Hemant19
732KA25TZ3KR
Hemant
Hemant20
NN0X92ES90PH
AAAAAAAA

SQL> alter table employees
2 MODIFY (national_id_no encrypt)
3 /

Table altered.

SQL> alter system checkpoint;

System altered.

SQL> !sync ; sync

SQL>

SQL> select emp_id, national_id_no
2 from employees
3 order by 1
4 /

EMP_ID NATIONAL_ID_NO
---------- ------------------
1 LH6RUZRISE11
2 DFIN8FZ7B6J0
3 PLJ1R2QYRG2C
4 UT3HB9ALF3B5
5 LQMDUTFB2PTM
6 1IGKV4E78M5J
7 P9TQAV5BC5EM
8 V69U6VZWCK26
9 EOTOQHOB0F45
10 OKMEV89XOQE1
11 0D4L77P3YNF0
12 CTMCLJSKQW82
13 49T0AG7E2Y9X
14 ODEY2J51D8RH
15 R1HFMN34MYLH
16 OXI0LOX161BO
17 2XL44ZJVABGW
18 4BIPWVECBWYO
19 732KA25TZ3KR
20 NN0X92ES90PH

20 rows selected.

SQL>
SQL> select emp_id, dump(national_id_no) col_dump
2 from employees
3 order by emp_id
4 /

EMP_ID COL_DUMP
---------- ------------------------------------------------------
1 Typ=1 Len=12: 76,72,54,82,85,90,82,73,83,69,49,49
2 Typ=1 Len=12: 68,70,73,78,56,70,90,55,66,54,74,48
3 Typ=1 Len=12: 80,76,74,49,82,50,81,89,82,71,50,67
4 Typ=1 Len=12: 85,84,51,72,66,57,65,76,70,51,66,53
5 Typ=1 Len=12: 76,81,77,68,85,84,70,66,50,80,84,77
6 Typ=1 Len=12: 49,73,71,75,86,52,69,55,56,77,53,74
7 Typ=1 Len=12: 80,57,84,81,65,86,53,66,67,53,69,77
8 Typ=1 Len=12: 86,54,57,85,54,86,90,87,67,75,50,54
9 Typ=1 Len=12: 69,79,84,79,81,72,79,66,48,70,52,53
10 Typ=1 Len=12: 79,75,77,69,86,56,57,88,79,81,69,49
11 Typ=1 Len=12: 48,68,52,76,55,55,80,51,89,78,70,48
12 Typ=1 Len=12: 67,84,77,67,76,74,83,75,81,87,56,50
13 Typ=1 Len=12: 52,57,84,48,65,71,55,69,50,89,57,88
14 Typ=1 Len=12: 79,68,69,89,50,74,53,49,68,56,82,72
15 Typ=1 Len=12: 82,49,72,70,77,78,51,52,77,89,76,72
16 Typ=1 Len=12: 79,88,73,48,76,79,88,49,54,49,66,79
17 Typ=1 Len=12: 50,88,76,52,52,90,74,86,65,66,71,87
18 Typ=1 Len=12: 52,66,73,80,87,86,69,67,66,87,89,79
19 Typ=1 Len=12: 55,51,50,75,65,50,53,84,90,51,75,82
20 Typ=1 Len=12: 78,78,48,88,57,50,69,83,57,48,80,72

20 rows selected.

SQL>

SQL> !strings -a /opt/oracle/oradata/HEMANT/HR_DATA.dbf | more
}|{z
HEMANT
3J?5
HR_DATA
AAAAAAAA
Hemant
Hemant204
Hemant
Hemant194
Hemant
Hemant184
Hemant
Hemant174[Q#
Hemant
Hemant164
Hemant
Hemant154
$^?[
Hemant
Hemant1448
Hemant
Hemant134
Hemant
Hemant124
Hemant
Hemant114
Hemant
Hemant104J
Hemant
Hemant94
Hemant
Hemant84M
zCAGp
Q(ru
Hemant
Hemant74
$o7tN
Hemant
Hemant6418
( i+W
Hemant
Hemant54
f(cCL
Hemant
Hemant44
Hemant
Hemant34
Hemant
Hemant24
e{_
Hemant
Hemant14
Hemant
Hemant1
LH6RUZRISE11
Hemant
Hemant2
DFIN8FZ7B6J0
Hemant
Hemant3
PLJ1R2QYRG2C
Hemant
Hemant4
UT3HB9ALF3B5
Hemant
Hemant5
LQMDUTFB2PTM
Hemant
Hemant6
1IGKV4E78M5J
Hemant
Hemant7
P9TQAV5BC5EM
Hemant
Hemant8
V69U6VZWCK26
Hemant
Hemant9
EOTOQHOB0F45
Hemant
Hemant10
OKMEV89XOQE1
Hemant
Hemant11
0D4L77P3YNF0
Hemant
Hemant12
CTMCLJSKQW82
Hemant
Hemant13
49T0AG7E2Y9X
Hemant
Hemant14
ODEY2J51D8RH
Hemant
Hemant15
R1HFMN34MYLH
Hemant
Hemant16
OXI0LOX161BO
Hemant
Hemant17
2XL44ZJVABGW
Hemant
Hemant18
4BIPWVECBWYO
Hemant
Hemant19
732KA25TZ3KR
Hemant
Hemant20
NN0X92ES90PH
AAAAAAAA


SQL> select version, version_full from v$instance;

VERSION VERSION_FULL
----------------- -----------------
19.0.0.0.0 19.3.0.0.0

SQL>


When I insert a new row, the plain-text for this is not present. But the old (20) rows plain-text is still present.

SQL> insert into employees
2 values (21,'HemantNew','HemantNew21','ABCDEFGHIJ88',2000);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> !sync;sync

SQL> !strings -a /opt/oracle/oradata/HEMANT/HR_DATA.dbf
}|{z
HEMANT
3J?5
SJ?
HR_DATA
UTJ?
AAAAAAAA
HemantNew
HemantNew214S
Hemant
Hemant204
Hemant
Hemant194
Hemant
Hemant184
Hemant
Hemant174[Q#
Hemant
Hemant164
Hemant
Hemant154
$^?[
Hemant
Hemant1448
Hemant
Hemant134
Hemant
Hemant124
Hemant
Hemant114
Hemant
Hemant104J
Hemant
Hemant94
Hemant
Hemant84M
zCAGp
Q(ru
Hemant
Hemant74
$o7tN
Hemant
Hemant6418
( i+W
Hemant
Hemant54
f(cCL
Hemant
Hemant44
Hemant
Hemant34
Hemant
Hemant24
e{_
Hemant
Hemant14
Hemant
Hemant1
LH6RUZRISE11
Hemant
Hemant2
DFIN8FZ7B6J0
Hemant
Hemant3
PLJ1R2QYRG2C
Hemant
Hemant4
UT3HB9ALF3B5
Hemant
Hemant5
LQMDUTFB2PTM
Hemant
Hemant6
1IGKV4E78M5J
Hemant
Hemant7
P9TQAV5BC5EM
Hemant
Hemant8
V69U6VZWCK26
Hemant
Hemant9
EOTOQHOB0F45
Hemant
Hemant10
OKMEV89XOQE1
Hemant
Hemant11
0D4L77P3YNF0
Hemant
Hemant12
CTMCLJSKQW82
Hemant
Hemant13
49T0AG7E2Y9X
Hemant
Hemant14
ODEY2J51D8RH
Hemant
Hemant15
R1HFMN34MYLH
Hemant
Hemant16
OXI0LOX161BO
Hemant
Hemant17
2XL44ZJVABGW
Hemant
Hemant18
4BIPWVECBWYO
Hemant
Hemant19
732KA25TZ3KR
Hemant
Hemant20
NN0X92ES90PH
AAAAAAAA

SQL>



So, it seems that after I ran the MODIFY to encrypt a column, Oracle created new copies of the 20 rows with encrypted values.  However, the old plain-text (non-encrypted) values are still present in the datafile.

Apparently, those "still present" plain-text representations of the "NATIONAL_ID_NO" column in the datafile are explained in the documentation as :

"Column values encrypted using TDE are stored in the data files in encrypted form. However, these data files may still contain some plaintext fragments, called ghost copies, left over by past data operations on the table. This is similar to finding data on the disk after a file was deleted by the operating system."

You should remove old plaintext fragments that can appear over time.

Old plaintext fragments may be present for some time until the database overwrites the blocks containing such values. If privileged operating system users bypass the access controls of the database, then they might be able to directly access these values in the data file holding the tablespace.

To minimize this risk:

  1. Create a new tablespace in a new data file.

    You can use the CREATE TABLESPACE statement to create this tablespace.

  2. Move the table containing encrypted columns to the new tablespace. You can use the ALTER TABLE.....MOVE statement.

    Repeat this step for all of the objects in the original tablespace.

  3. Drop the original tablespace.

    You can use the DROP TABLESPACE tablespace INCLUDING CONTENTS KEEP DATAFILES statement. Oracle recommends that you securely delete data files using platform-specific utilities.

  4. Use platform-specific and file system-specific utilities to securely delete the old data file. Examples of such utilities include shred (on Linux) and sdelete (on Windows).

Categories: DBA Blogs

Oracle Database 21c

Tue, 2020-12-08 16:05
Oracle Database 21c is now available in the Oracle Cloud.

21c is an Innovation Release so it can be used for testing of the new features.  For Long Term Support, customers still now upgrading should be upgrading to 19c.
Categories: DBA Blogs

How the World Wide Web was created

Thu, 2020-12-03 21:52
Categories: DBA Blogs

Configuring Transparent Data Encryption -- 1 : For a Tablespace

Sat, 2020-11-21 05:49

 Oracle allows TDE (Transparent Data Encryption) for specific (i.e. selected) columns or a full Tablespace.

Here is a quick demo of TDE for a Tablespace.

First, I setup a target tablespace with some data



SQL> connect hemant/hemant
Connected.
SQL> create tablespace TDE_TARGET_TBS datafile '/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS.dbf' size 100M;

Tablespace created.

SQL>
SQL> create table TDE_TARGET_TABLE
2 (id_col number,
3 data_col varchar2(50))
4 tablespace TDE_TARGET_TBS
5 /

Table created.

SQL> insert into TDE_TARGET_TABLE
2 select rownum,
3 'MY DATA CONTENT : ' || rownum
4 from dual
5 connect by level "less than" 1001 -- the "less than" symbol replaced by string to preserve formatting for HTML
6 /

1000 rows created.

SQL> commit;

Commit complete.

SQL>


oracle19c>dd if=/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS.dbf ibs=8192 skip=128 > /tmp/dump_of_TDE_TARGET_TBS.TXT
12673+0 records in
202768+0 records out
103817216 bytes (104 MB) copied, 1.10239 s, 94.2 MB/s
oracle19c>
oracle19c>strings -a /tmp/dump_of_TDE_TARGET_TBS.TXT |grep 'CONTENT' |head -5
MY DATA CONTENT : 944,
MY DATA CONTENT : 945,
MY DATA CONTENT : 946,
MY DATA CONTENT : 947,
MY DATA CONTENT : 948,
oracle19c>strings -a /tmp/dump_of_TDE_TARGET_TBS.TXT |grep 'CONTENT' | wc -l
1000
oracle19c>


I inserted 1000 rows with the text "MY DATA CONTENT" and it is visible as plain-text when I dump the datafile.   

Note how not all the inserted rows appear to be in physical order -- the "last" 57 "records" (i.e. rows) seem to appear before the first "record" (row) as I show in this video recording of a viewing of the dump.  Never assume physical ordering of data in a datafile or when retrieving output (for ordering the results of a SELECT statement, *always* use the ORDER BY clause)






So, I now intend to encrypt the tablespace.

Step 1 : Specify the ENCRYPTION WALLET LOCATION
In earlier releases, this is specified in the sqlnet.ora file like this :

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=/home/oracle/wallet))) -- or this could be any other folder, or defaulting to $ORACLE_BASE/admin/db_unique_name/wallet


However, in 19c, Oracle recommends using the KEYSTORE_CONFIGURATION attribute of the TDE_CONFIGURATION initialization parameter after setting the WALLET_ROOT.


SQL> show parameter tde

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
one_step_plugin_for_pdb_with_tde boolean FALSE
tde_configuration string
SQL> show parameter wallet

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet string
wallet_root string
SQL>
SQL> alter system set wallet_root='/opt/oracle/product/19c/dbhome_1/TDE_WALLETS' scope=SPFILE;

System altered.

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1207958960 bytes
Fixed Size 8895920 bytes
Variable Size 318767104 bytes
Database Buffers 872415232 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter wallet

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet string
wallet_root string /opt/oracle/product/19c/dbhome
_1/TDE_WALLETS
SQL>
SQL> alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=SPFILE;

System altered.

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1207958960 bytes
Fixed Size 8895920 bytes
Variable Size 318767104 bytes
Database Buffers 872415232 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SQL> show parameter wallet

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet string
wallet_root string /opt/oracle/product/19c/dbhome
_1/TDE_WALLETS
SQL> show parameter tde

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
one_step_plugin_for_pdb_with_tde boolean FALSE
tde_configuration string KEYSTORE_CONFIGURATION=FILE
SQL>


In the case of this database, since I had not earlier configured these parameters, I had to do a manual restart for them to take effect (Note : "WALLET_ROOT" must have been configured before "TDE_CONFIGURATION" can be set, that is why I had to do an additional restart between setting the two parameters).
I have deliberately configured WALLET_ROOT to a non-default/standard location.


Step 2 : Create the KEYSTORE (under WALLET_ROOT)

This is where I actually  create the Wallet.  The syntax specifies KEYSTORE location, but can default to WALLET_ROOT as I have already defined it. I can also create an Auto-Login Keystore

SQL> administer key management create keystore identified by mysecretpassword;

keystore altered.

SQL> !ls -l /opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde
total 4
-rw-------. 1 oracle oinstall 2555 Nov 21 18:38 ewallet.p12

SQL>
SQL> administer key management create LOCAL auto_login keystore
2 from keystore '/opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde'
3 identified by mysecretpassword
4 /

keystore altered.

SQL> !ls -l /opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde
total 8
-rw-------. 1 oracle oinstall 2600 Nov 21 18:49 cwallet.sso
-rw-------. 1 oracle oinstall 2555 Nov 21 18:38 ewallet.p12

SQL>
SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
------------------------------------------------------------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
------------------------------ -------------------- --------- -------- --------- ----------
FILE
/opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde/
OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN SINGLE NONE UNDEFINED 0


SQL>


In this case, "ewallet.p12" is the Password Protected Keystore and "cwallet.sso" is the Auto-Login Keystore (created LOCALly only, not for remote servers/clients).


Step 3 : OPEN the Keystore  (only if it is NOT already OPEN) 

I can see that the Keystore is already OPEN (from the query on v$encryption_wallet) but I could attempt OPENing it with :


SQL> administer key management set keystore open
2 identified by mysecretpassword
3 /
administer key management set keystore open
*
ERROR at line 1:
ORA-28354: Encryption wallet, auto login wallet, or HSM is already open


SQL>
SQL> !oerr ora 28354
28354, 0000, "Encryption wallet, auto login wallet, or HSM is already open"
// *Cause: Encryption wallet, auto login wallet, or HSM was already opened.
// *Action: None.
//

SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
------------------------------------------------------------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
------------------------------ -------------------- --------- -------- --------- ----------
FILE
/opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde/
OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN SINGLE NONE UNDEFINED 0


SQL>


Step 4 : Setup the Master Encryption Key

Now, I setup the Master Key (and also backup the existing key file)


SQL> administer key management set key
2 using tag 'For_Tablespace_TDE'
3 force keystore -- because I am using LOCAL_AUTOLOGIN
4 identified by mysecretpassword
5 with backup using 'tde_key_backup'
6 /

keystore altered.

SQL>
SQL> !ls -l /opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde
total 20
-rw-------. 1 oracle oinstall 4232 Nov 21 19:04 cwallet.sso
-rw-------. 1 oracle oinstall 2555 Nov 21 19:04 ewallet_2020112111043216_tde_key_backup.p12
-rw-------. 1 oracle oinstall 4171 Nov 21 19:04 ewallet.p12

SQL>
SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
------------------------------------------------------------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
------------------------------ -------------------- --------- -------- --------- ----------
FILE
/opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde/
OPEN LOCAL_AUTOLOGIN SINGLE NONE NO 0


SQL>
SQL> select key_id, creation_time, keystore_type, tag from v$encryption_keys;

KEY_ID
------------------------------------------------------------------------------
CREATION_TIME KEYSTORE_TYPE
--------------------------------------------------------------------------- -----------------
TAG
------------------------------------------------------------------------------------------------------------------------------------
AaHRyuP8yE+ivzI/hZHcOdoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
21-NOV-20 07.04.32.347090 PM +08:00 SOFTWARE KEYSTORE
For_Tablespace_TDE


SQL>


Now I am ready the encrypt my Tablespace.


Step 5 :  Online Encryption of Tablespace
This is a 19c feature, earlier versions required Offline Encryption.
This method creates a new datafile with encrypted data

SQL> alter tablespace TDE_TARGET_TBS
2 encryption online
3 using 'AES192'
4 encrypt file_name_convert = ('/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS.dbf','/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf')
5 /

Tablespace altered.

SQL>

oracle19c>pwd
/opt/oracle/oradata/HEMANT
oracle19c>ls -l TDE*
-rw-r-----. 1 oracle oinstall 104865792 Nov 21 19:42 TDE_TARGET_TBS_encrypted.dbf
oracle19c>
oracle19c>dd if=/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf ibs=8192 skip=128 > /tmp/dump_of_TDE_TARGET_TBS_encrypted.TXT
12673+0 records in
202768+0 records out
103817216 bytes (104 MB) copied, 0.414517 s, 250 MB/s
oracle19c>
oracle19c>strings -a /tmp/dump_of_TDE_TARGET_TBS_encrypted.TXT |grep 'CONTENT' |head -5
oracle19c>

SQL> l
1 select t.name, e.ts#, e.encryptionalg, e.encryptedts, e.key_version, e.status
2 from v$tablespace t, v$encrypted_tablespaces e
3* where t.ts#=e.ts#
SQL> /

NAME TS# ENCRYPT ENC KEY_VERSION STATUS
------------------------------ ---------- ------- --- ----------- ----------
TDE_TARGET_TBS 8 AES192 YES 1 NORMAL

SQL>



Oracle has replaced the TDE_TARGET_TBS.dbf file with TDE_TARGET_TBS_encrypted.dbf file. The new file does NOT have any plain-text values "CONTENT"

For subsequent Tablespaces, Steps 1 to 4 would not be required.



Categories: DBA Blogs

Active Data Guard and DML Redirection

Sat, 2020-11-14 03:15

 Active Data Guard (also known as "ADG") allows you to open a Standby Database in Read Only mode and query it while Media Recovery (i.e. Redo Apply from the Primary) is concurrently running.

Caveat : ADG requires purchase of additional licences to use this feature on a Standby Database.

If you issue the commands from sqlplus and not from dgmrl, you must first stop Media Recovery before you OPEN the database and then re-enable it on the Standby :


SQL> select FORCE_LOGGING, FLASHBACK_ON,DATABASE_ROLE,PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE
2 from v$database;

FORCE_LOGGING FLASHBACK_ON DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
--------------------------------------- ------------------ ---------------- -------------------- --------------------
OPEN_MODE
--------------------
YES NO PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
MOUNTED


SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select FORCE_LOGGING, FLASHBACK_ON,DATABASE_ROLE,PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE
2 from v$database;

FORCE_LOGGING FLASHBACK_ON DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
--------------------------------------- ------------------ ---------------- -------------------- --------------------
OPEN_MODE
--------------------
YES NO PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
READ ONLY


SQL>
SQL> alter pluggable database orclpdb1 open read only;

Pluggable database altered.

SQL>
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>


Thereafter, you would be able to query the Standby Database.  DML (INSERT, UPDATE, DELETE) and DDL (CREATE, DROP, ALTER etc) are *not* allowed by default.  

19c does have a DML Redirection feature ADG_REDIRECT_DML which I demonstrate below :

SQL> connect hemant/hemant@stdbypdb1
Connected.
SQL> select * from x;

D
-
X

SQL> delete x;
delete x
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access


SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;

Session altered.

SQL> delete x;

1 row deleted.

SQL> commit;

Commit complete.

SQL>


The actual execution of the DML will take some time as Oracle has to actually push it to the Primary database for execution and then wait for the Redo Apply to replicate back to the Standby.

PLSQL also can be executed with ADG_REDIRECT_PLSQL     which I am not demonstrating here.

Note :  According to Oracle's documentation the DML Redirect feature is for "read-mostly applications, which occasionally execute DMLs, on the standby database."    .  I wouldn't advise trying this for all sessions at the Instance ("alter system") level but only to be used occasionally at session level.

Categories: DBA Blogs

Compressing VARCHAR2 and CHAR column data

Sun, 2020-11-08 09:21

 Most applications that I see use VARCHAR2 columns.  However, there are some (including many built by "in house database designers" that use CHAR columns).  CHAR columns are for Fixed-Length character strings.  Any inserted values that are shorter than the defined length are right-padded with blanks.  Comparison semantics are also different in that VARCHAR2 uses non-padded comparison while CHAR uses blank-padding for comparison.

In this demonstration, I'd like to focus on compressibility of columns defined as VARCHAR2 and CHAR.

These scripts are executed in 19c on Linux.


First with VARCHAR2


SQL> create tablespace VC2_TBS datafile '/opt/oracle/oradata/HEMANT/VC2_TBS.dbf' size 2G;

Tablespace created.

SQL>
SQL> create table VC2_TABLE
2 (id_col number(8), data_col_1 varchar2(50), data_col_2 varchar2(50), data_col_3 varchar2(50))
3 pctfree 0
4 tablespace VC2_TBS
5 /

Table created.

SQL>
SQL> declare
2 l_c number;
3 begin
4 for l_c in 1 .. 100
5 loop
6 insert into VC2_TABLE
7 select rownum, dbms_random.string('X',25), dbms_random.string('X',15), dbms_random.string('X',10)
8 from dual
9 connect by level "less than sign" 100001; -- "less than sign" replaced by words to preserve HTML formatting
10 commit;
11 end loop;
12 end;
13 /

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats('','VC2_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL>
SQL> select compression, compress_for, num_rows, blocks, avg_row_len
2 from user_tables
3 where table_name = 'VC2_TABLE'
4 /

COMPRESS COMPRESS_FOR NUM_ROWS BLOCKS AVG_ROW_LEN
-------- ------------------------------ ------------ ------------ -----------
DISABLED 10,000,000 78,437 58

SQL>
SQL> select bytes/1048576
2 from user_segments
3 where segment_name = 'VC2_TABLE'
4 /

BYTES/1048576
-------------
616

SQL>
SQL> pause For RMAN BACKUP AS COMPRESSED BACKUPSET
For RMAN BACKUP AS COMPRESSED BACKUPSET

RMAN> backup as compressed backupset tablespace VC2_TBS;

Starting backup at 08-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 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=00004 name=/opt/oracle/oradata/HEMANT/VC2_TBS.dbf
channel ORA_DISK_1: starting piece 1 at 08-NOV-20
channel ORA_DISK_1: finished piece 1 at 08-NOV-20
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0svf1p8h_1_1 tag=TAG20201108T221753 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 08-NOV-20


oracle19c>ls -l /opt/oracle/product/19c/dbhome_1/dbs/0svf1p8h_1_1
-rw-r-----. 1 oracle oinstall 386490368 Nov 8 22:18 /opt/oracle/product/19c/dbhome_1/dbs/0svf1p8h_1_1
oracle19c>
-- approx 369MB RMAN Compressed Backup

SQL>
SQL> alter table VC2_TABLE
2 move compress
3 /

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats('','VC2_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL>
SQL> select compression, compress_for, num_rows, blocks, avg_row_len
2 from user_tables
3 where table_name = 'VC2_TABLE'
4 /

COMPRESS COMPRESS_FOR NUM_ROWS BLOCKS AVG_ROW_LEN
-------- ------------------------------ ------------ ------------ -----------
ENABLED BASIC 10,000,000 79,027 58

SQL>
SQL> select bytes/1048576
2 from user_segments
3 where segment_name = 'VC2_TABLE'
4 /

BYTES/1048576
-------------
624

SQL>


So these are the facts for the table with 3 VARCHAR2(50) columns with random 25, 15 and 10 character-strings. 
Note : I deliberately chose dbms_random.string to ensure that I'd get very few (if any) repeatable values that are compressible themselves.

10million rows of Average Row Length of 58bytes
616MB Segment (78,437 blocks equal 613MB approx).
RMAN Compressed Size of the Datafile 369MB (approx 60% of the Segment Size)
BASIC Compression Size of the Table :  624MB  (i.e. no compression achieved because I used random strings)



Next with CHAR


SQL> create tablespace CHAR_TBS datafile '/opt/oracle/oradata/HEMANT/CHAR_TBS.dbf' size 2G;  -- subsequently resized to 4000M

Tablespace created.

SQL>
SQL> create table CHAR_TABLE
2 (id_col number(8), data_col_1 char(50), data_col_2 char(50), data_col_3 char(50))
3 pctfree 0
4 tablespace CHAR_TBS
5 /

Table created.

SQL>
SQL> declare
2 l_c number;
3 begin
4 for l_c in 1 .. 100
5 loop
6 insert into CHAR_TABLE
7 select rownum, dbms_random.string('X',25), dbms_random.string('X',15), dbms_random.string('X',10)
8 from dual
9 connect by level "less than" 100001; -- "less than sign" replaced by words to preserve HTML formatting
10 commit;
11 end loop;
12 end;
13 /

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats('','CHAR_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL>
SQL> select compression, compress_for, num_rows, blocks, avg_row_len
2 from user_tables
3 where table_name = 'CHAR_TABLE'
4 /

COMPRESS COMPRESS_FOR NUM_ROWS BLOCKS AVG_ROW_LEN
-------- ------------------------------ ------------ ------------ -----------
DISABLED 10,000,000 204,116 158

SQL>
SQL> select bytes/1048576
2 from user_segments
3 where segment_name = 'CHAR_TABLE'
4 /

BYTES/1048576
-------------
1600

SQL>
SQL> pause For RMAN BACKUP AS COMPRESSED BACKUPSET
For RMAN BACKUP AS COMPRESSED BACKUPSET


RMAN> backup as compressed backupset tablespace CHAR_TBS;

Starting backup at 08-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 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=00004 name=/opt/oracle/oradata/HEMANT/CHAR_TBS.dbf
channel ORA_DISK_1: starting piece 1 at 08-NOV-20
channel ORA_DISK_1: finished piece 1 at 08-NOV-20
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0vvf1r7f_1_1 tag=TAG20201108T225127 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 08-NOV-20


oracle19c>ls -l /opt/oracle/product/19c/dbhome_1/dbs/0vvf1r7f_1_1
-rw-r-----. 1 oracle oinstall 441876480 Nov 8 22:51 /opt/oracle/product/19c/dbhome_1/dbs/0vvf1r7f_1_1
oracle19c>
-- appox 421MB RMAN Compressed Backup

SQL>
SQL> alter table CHAR_TABLE
2 move compress
3 /

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats('','CHAR_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL>
SQL> select compression, compress_for, num_rows, blocks, avg_row_len
2 from user_tables
3 where table_name = 'CHAR_TABLE'
4 /

COMPRESS COMPRESS_FOR NUM_ROWS BLOCKS AVG_ROW_LEN
-------- ------------------------------ ------------ ------------ -----------
ENABLED BASIC 10,000,000 204,766 158

SQL>
SQL> select bytes/1048576
2 from user_segments
3 where segment_name = 'CHAR_TABLE'
4 /

BYTES/1048576
-------------
1600

SQL>

So these are the facts for the table with 3 CHAR(50) columns with random 25, 15 and 10 character-strings. 
 Note : I deliberately chose dbms_random.string to ensure that I'd get very few (if any) repeatable values that are compressible themselves. 

10million rows of Average Row Length of 158bytes (up from 58 bytes for the VARCHAR2 columns) 
1600MB Segment (204,116 blocks equal 1595MB approx). 
RMAN Compressed Size of the Datafile 421MB  (approx 26% of the Segment Size)
BASIC Compression Size of the Table : 1600MB (i.e. no compression achieved, because I used random strings ? -- shouldn't the right-padded blanks be compressed ?

 So, in this test comparing random strings in VARCHAR2 and CHAR, quite obviously the table with CHAR columns took up much more space.
BASIC Compression didn't achieve anything (again : because I used random strings?)

RMAN default compression ("CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ") achieved better compression with CHAR

Of course, since I used random strings and you might have actual data that is compressible and the length of the actual strings inserted into the CHAR columns may be different in your production / test table, you would see different levels of compression achieved.

The point is that compression success depends not just on the type of data but can be different whether you use BASIC compression in the tablespace or the default BASIC in RMAN.

Note :  Given RMAN's default behaviour of "unused block compression" do not expect RMAN to have to backup all the blocks in entire 2GB or 4000MB datafile  -- particularly because I specifically create the tablespace just before the test and it doesn't have any other objects (segments).


Categories: DBA Blogs

An SQL Server Blog

Sun, 2020-11-01 08:04

 As I have recently started working with SQL Server, I have created a new SQL Server Blog.

I will be periodically updating both my Oracle and SQL Server blogs.


Categories: DBA Blogs

Getting your SQL Statement's SQL_ID

Thu, 2020-10-29 09:55

 SQL*Plus now can provide you the SQL_ID of the last statement executed in your own session with SET FEEDBACK SQL_ID.

A quick demo :


SQL> set feedback on sql_id
SQL> select count(*) from my_target where factory='SYS';

COUNT(*)
----------
52217

1 row selected.

SQL_ID: g1mk14hdxc1ww
SQL> select * from table(dbms_xplan.display_cursor('g1mk14hdxc1ww'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g1mk14hdxc1ww, child number 0
-------------------------------------
select count(*) from my_target where factory='SYS'

Plan hash value: 1690349505

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| MY_TARGET_NDX | 2683 | 13415 | 6 (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

2 - access("FACTORY"='SYS')


19 rows selected.

SQL_ID: 5dyyqqwuyu01v
SQL>


After I executed my query against the "my_target" table, SQL*Plus provided my the SQL_ID ("g1mk14hdxc1ww").  I could then immediately get the Execution Plan for the statement, without having to query for the SQL_ID in V$SQL or V$SQLAREA.

This demonstration is with SQL*Plus 19.0 against a 19c Database.  (I think "set feedback on sql_id" was introduced in SQL*Plus 18)




Categories: DBA Blogs

Real Time SQL Monitoring using SQL Developer

Sat, 2020-10-24 11:41

Since a previous post with screenshots was very popular


I have created a  Video Demo on my Youtube Channel



Categories: DBA Blogs

On-Premises SQL Server to Oracle ADB on the Cloud -- 2

Sun, 2020-10-11 10:23

 Having configured connectivity between an On-Premises SQL Server Database to an Oracle ADB Database in the Oracle Cloud in the previous post, I will now copy data from SQL Server to Oracle


  • Create the Target Table in Oracle ADB 

SQL Developer


  • Verify Existing Rows in SQL Server


SQL Server Management Studio

  • Insert into Oracle with Select from SQL Server

SQL Server Management Studio


  • Verify Rows Inserted into Oracle

SQL Developer


  • Create New Row in SQL Server and Verify that it is NOT present in Oracle
SQL Server Management Studio


The last screenshot is literally a NOT IN query between SQL Server On-Premises and Oracle in the Cloud.












Categories: DBA Blogs

On-Premises SQL Server to Oracle ADB on the Cloud - 1

Sat, 2020-10-10 10:58
Setting up a connection between an On-Premises SQL Server Database Instance to an Oracle ADB on the Oracle Cloud

Strong Caveat :  This is only a POC.  Most organisations would NOT allow an open direct connection between an On-Premises Database and and External Site (whether a Database or any other Service).  

1. On my Free-Tier Oracle ADB, I login as the ADMIN user and configure a new database account "ss_user"

SQL Developer to Oracle ADB


2. I install Oracle Client and configure connectivity to the ADB database with the Wallet information

(for detailed instructions see the Oracle Cloud documentation here).  {I used an 18c Client on Windows to connect to 19c ADB, simply because I already and 18c client and didn't want to wait to download the 19c client}

sqlnet.ora



tnsnames.ora



3.  Optionally re-register the OraOLEDB18.DLL file  (using CMD as Administrator)  (you might also need to reboot your Windows or restart the SQL Server Instance)

CMD as Administrator




4.  Define the Linked Server in SQL Server (using SSMS)


Oracle OLEDB Provider




Linked Server Configuration Pag


Linked Server Security Configuration


5.  Test Connectivity

Test Connectivity Option for Linked Server



6  Run queries against from SSMS


SSMS Query and Results Pane


Categories: DBA Blogs

Extracting DDL using SQL Developer

Fri, 2020-10-02 21:50

 The "DDL" command in SQL Developer 20.2 new mimics the command in sqlcl


Here I extract the definition my table OBJECTS_LIST and it's index



Here I extract the code for a Stored Procedure





Categories: DBA Blogs

Verifying an RMAN Backup -- Part 2

Tue, 2020-09-29 10:06

 Continuing on my previous blog post,  the question being "when you receive an RMAN Backup from another DBA, how do you confirm that the database can be restored and recovered to a Consistent Point In Time ?"

The quick steps, without actually running a RESTORE DATABASE command are :

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.


So, I'll demonstrate them again in 19c and a Non-CDB database here.  The source Database DB_NAME is "HEMANT" so I create in RTST parameter file with DB_NAME='HEMANT' and DB_UNIQUE_NAME='RTST'

I then restore the Controlfile, remove all entries of previous backups, CATALOG the Backup Pieces that I have received and then query the Controlfile.  (The CATALOG START WITH updates the Controlfile with information from the Backup Pieces, although the REPORT SCHEMA command is from the database structure in the controlfile).



oracle19c>echo $ORACLE_SID
RTST
oracle19c>cat $ORACLE_HOME/dbs/initRTST.ora
db_name = 'HEMANT'
db_unique_name = 'RTST'
control_files='/tmp/RTST_control.ctl'
#enable_pluggable_database=true
oracle19c>
oracle19c>cd HEMANT_DB_Backup
oracle19c>pwd
/home/oracle/HEMANT_DB_Backup
oracle19c>ls -l
total 140504
-rw-r-----. 1 oracle oinstall 4390912 Sep 29 22:01 0cvbkgui_1_1
-rw-r-----. 1 oracle oinstall 58507264 Sep 29 22:01 0evbkh0d_1_1
-rw-r-----. 1 oracle oinstall 6381568 Sep 29 22:01 0fvbkh0k_1_1
-rw-r-----. 1 oracle oinstall 51978240 Sep 29 22:01 0gvbkh0r_1_1
-rw-r-----. 1 oracle oinstall 2179072 Sep 29 22:01 0hvbkh12_1_1
-rw-r-----. 1 oracle oinstall 1622016 Sep 29 22:01 0ivbkh13_1_1
-rw-r-----. 1 oracle oinstall 4863488 Sep 29 22:01 0jvbkh14_1_1
-rw-r-----. 1 oracle oinstall 2187264 Sep 29 22:01 0kvbkh14_1_1
-rw-r-----. 1 oracle oinstall 11763712 Sep 29 22:01 c-432411782-20200929-06
oracle19c>
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Sep 29 22:05:03 2020
Version 19.3.0.0.0

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 268434280 bytes

Fixed Size 8895336 bytes
Variable Size 201326592 bytes
Database Buffers 50331648 bytes
Redo Buffers 7880704 bytes

RMAN> restore controlfile from '/home/oracle/HEMANT_DB_Backup/c-432411782-20200929-06';

Starting restore at 29-SEP-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 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 29-SEP-20

RMAN>
RMAN> delete backup;

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

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
12 12 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1
13 13 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03
14 14 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1
15 15 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1
16 16 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1
17 17 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1
18 18 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1
19 19 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1
20 20 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1
21 21 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05

Do you really want to delete the above objects (enter YES or NO)? YES

RMAN-06207: warning: 10 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 /opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05


RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1 RECID=12 STAMP=1052394450
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03 RECID=13 STAMP=1052394452
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1 RECID=14 STAMP=1052394509
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1 RECID=15 STAMP=1052394516
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1 RECID=16 STAMP=1052394523
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1 RECID=17 STAMP=1052394530
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1 RECID=18 STAMP=1052394532
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1 RECID=19 STAMP=1052394532
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1 RECID=20 STAMP=1052394533
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05 RECID=21 STAMP=1052394534
Crosschecked 10 objects


RMAN> delete expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
12 12 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1
13 13 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03
14 14 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1
15 15 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1
16 16 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1
17 17 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1
18 18 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1
19 19 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1
20 20 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1
21 21 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1 RECID=12 STAMP=1052394450
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03 RECID=13 STAMP=1052394452
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1 RECID=14 STAMP=1052394509
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1 RECID=15 STAMP=1052394516
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1 RECID=16 STAMP=1052394523
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1 RECID=17 STAMP=1052394530
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1 RECID=18 STAMP=1052394532
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1 RECID=19 STAMP=1052394532
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1 RECID=20 STAMP=1052394533
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05 RECID=21 STAMP=1052394534
Deleted 10 EXPIRED objects


RMAN>
RMAN> list backup;

specification does not match any backup in the repository

RMAN> catalog start with '/home/oracle/HEMANT_DB_Backup/';

searching for all files that match the pattern /home/oracle/HEMANT_DB_Backup/

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0evbkh0d_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0fvbkh0k_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0gvbkh0r_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0hvbkh12_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0ivbkh13_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0kvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/c-432411782-20200929-06

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

List of Cataloged Files
=======================
File Name: /home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0evbkh0d_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0fvbkh0k_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0gvbkh0r_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0hvbkh12_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0ivbkh13_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0kvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/c-432411782-20200929-06

RMAN>
RMAN> report schema;

RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name RTST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 400 SYSTEM *** /opt/oracle/oradata/HEMANT/system.dbf
2 400 SYSAUX *** /opt/oracle/oradata/HEMANT/sysaux.dbf
3 200 UNDOTBS1 *** /opt/oracle/oradata/HEMANT/undotbs.dbf
4 10 USERS *** /opt/oracle/oradata/HEMANT/users01.dbf
5 10 INDX *** /opt/oracle/oradata/HEMANT/indx01.dbf
6 10 USERS *** /opt/oracle/oradata/HEMANT/users02.dbf
7 10 USERS *** /opt/oracle/oradata/HEMANT/users03.dbf
8 10 USERS *** /opt/oracle/oradata/HEMANT/users04.dbf
9 10 USERS *** /opt/oracle/oradata/HEMANT/users05.dbf
10 10 INDX *** /opt/oracle/oradata/HEMANT/indx02.dbf
11 10 INDX *** /opt/oracle/oradata/HEMANT/indx03.dbf

RMAN>
RMAN> quit


Recovery Manager complete.
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 29 22:07:56 2020
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>
SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';

Session altered.

SQL> select file#, checkpoint_change#, checkpoint_time, completion_time
2 from v$backup_datafile
3 order by 1
4 /

FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME COMPLETION_TIME
---------- ------------------ ------------------ ------------------
0 463290 29-SEP-20 11:49:14 29-SEP-20 11:49:15
1 456249 29-SEP-20 11:48:29 29-SEP-20 11:48:34
2 457590 29-SEP-20 11:48:36 29-SEP-20 11:48:39
3 458680 29-SEP-20 11:48:43 29-SEP-20 11:48:47
4 459759 29-SEP-20 11:48:50 29-SEP-20 11:48:51
5 459765 29-SEP-20 11:48:51 29-SEP-20 11:48:52
6 459779 29-SEP-20 11:48:53 29-SEP-20 11:48:53
7 459759 29-SEP-20 11:48:50 29-SEP-20 11:48:51
8 459765 29-SEP-20 11:48:51 29-SEP-20 11:48:52
9 459779 29-SEP-20 11:48:53 29-SEP-20 11:48:53
10 458680 29-SEP-20 11:48:43 29-SEP-20 11:48:43
11 456249 29-SEP-20 11:48:29 29-SEP-20 11:48:29

12 rows selected.

SQL>
SQL> select file#, checkpoint_change#, checkpoint_time, completion_time
2 from v$backup_datafile
3 order by 2
4 /

FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME COMPLETION_TIME
---------- ------------------ ------------------ ------------------
1 456249 29-SEP-20 11:48:29 29-SEP-20 11:48:34
11 456249 29-SEP-20 11:48:29 29-SEP-20 11:48:29
2 457590 29-SEP-20 11:48:36 29-SEP-20 11:48:39
3 458680 29-SEP-20 11:48:43 29-SEP-20 11:48:47
10 458680 29-SEP-20 11:48:43 29-SEP-20 11:48:43
4 459759 29-SEP-20 11:48:50 29-SEP-20 11:48:51
7 459759 29-SEP-20 11:48:50 29-SEP-20 11:48:51
5 459765 29-SEP-20 11:48:51 29-SEP-20 11:48:52
8 459765 29-SEP-20 11:48:51 29-SEP-20 11:48:52
6 459779 29-SEP-20 11:48:53 29-SEP-20 11:48:53
9 459779 29-SEP-20 11:48:53 29-SEP-20 11:48:53
0 463290 29-SEP-20 11:49:14 29-SEP-20 11:49:15

12 rows selected.

SQL>
SQL> select sequence#, first_change#, next_change#-1, next_time
2 from v$backup_archivelog_details
3 order by sequence#
4 /

SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#-1 NEXT_TIME
---------- ------------- -------------- ------------------
170 442901 448665 29-SEP-20 11:45:17
171 448666 450050 29-SEP-20 11:45:51
172 450051 451367 29-SEP-20 11:47:30
173 451368 454869 29-SEP-20 11:48:19
174 454870 457557 29-SEP-20 11:48:33
175 457558 457611 29-SEP-20 11:48:41
176 457612 459744 29-SEP-20 11:48:48
177 459745 459767 29-SEP-20 11:48:52

8 rows selected.

SQL>


In this case, file#=0  is actually the Controlfile --- so it has the highest Checkpoint SCN and Time.  As I noted in my previous post, it doesn't matter that the Controlfile is "newer" than the Datafiles.  We need to check the Datafiles with the ArchiveLogs. So, we see that the datafiles have slightly different Checkpoint SCNs (the backup was created with FILESPERSET=2 so every pair of datafiles has a Checkpoint).  The highest Datafile Checkpoint is 459779.  But the ArchiveLogs end at 459767.  Therefore, this database cannot be RECOVERed to a Consistent Point In Time.

Should I try doing a RESTORE and RECOVER, nevertheless ?

I first revert to ORACLE_SID=HEMANT and use the initHEMANT.ora parameter file that I obtained from the source server.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>ORACLE_SID=HEMANT;export ORACLE_SID
oracle19c>ls -l $ORACLE_HOME/dbs/initHEMANT.ora
-rw-r--r--. 1 oracle oinstall 693 Sep 28 23:05 /opt/oracle/product/19c/dbhome_1/dbs/initHEMANT.ora
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Sep 29 22:25:13 2020
Version 19.3.0.0.0

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

connected to target database (not started)

RMAN>
RMAN> startup nomount;

Oracle instance started

Total System Global Area 1207958960 bytes

Fixed Size 8895920 bytes
Variable Size 318767104 bytes
Database Buffers 872415232 bytes
Redo Buffers 7880704 bytes

RMAN> restore controlfile from '/home/oracle/HEMANT_DB_Backup/c-432411782-20200929-06';

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

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/oracle/oradata/HEMANT/control01.ctl
output file name=/opt/oracle/oradata/HEMANT/control02.ctl
Finished restore at 29-SEP-20

RMAN>
RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN> crosscheck backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1 RECID=12 STAMP=1052394450
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03 RECID=13 STAMP=1052394452
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1 RECID=14 STAMP=1052394509
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1 RECID=15 STAMP=1052394516
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1 RECID=16 STAMP=1052394523
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1 RECID=17 STAMP=1052394530
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1 RECID=18 STAMP=1052394532
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1 RECID=19 STAMP=1052394532
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1 RECID=20 STAMP=1052394533
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05 RECID=21 STAMP=1052394534
Crosschecked 10 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
------- ------- --- --- ----------- ----------- ----------
12 12 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1
13 13 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03
14 14 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1
15 15 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1
16 16 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1
17 17 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1
18 18 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1
19 19 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1
20 20 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1
21 21 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1 RECID=12 STAMP=1052394450
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03 RECID=13 STAMP=1052394452
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1 RECID=14 STAMP=1052394509
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1 RECID=15 STAMP=1052394516
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1 RECID=16 STAMP=1052394523
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1 RECID=17 STAMP=1052394530
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1 RECID=18 STAMP=1052394532
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1 RECID=19 STAMP=1052394532
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1 RECID=20 STAMP=1052394533
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05 RECID=21 STAMP=1052394534
Deleted 10 EXPIRED objects


RMAN>
RMAN> catalog start with '/home/oracle/HEMANT_DB_Backup/';

searching for all files that match the pattern /home/oracle/HEMANT_DB_Backup/

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0evbkh0d_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0fvbkh0k_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0gvbkh0r_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0hvbkh12_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0ivbkh13_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0kvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/c-432411782-20200929-06

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

List of Cataloged Files
=======================
File Name: /home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0evbkh0d_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0fvbkh0k_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0gvbkh0r_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0hvbkh12_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0ivbkh13_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0kvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/c-432411782-20200929-06

RMAN>
RMAN> restore database;

Starting restore at 29-SEP-20
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 /opt/oracle/oradata/HEMANT/system.dbf
channel ORA_DISK_1: restoring datafile 00011 to /opt/oracle/oradata/HEMANT/indx03.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0evbkh0d_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0evbkh0d_1_1 tag=TAG20200929T114829
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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 00002 to /opt/oracle/oradata/HEMANT/sysaux.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0fvbkh0k_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0fvbkh0k_1_1 tag=TAG20200929T114829
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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 00003 to /opt/oracle/oradata/HEMANT/undotbs.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/oracle/oradata/HEMANT/indx02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0gvbkh0r_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0gvbkh0r_1_1 tag=TAG20200929T114829
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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 00004 to /opt/oracle/oradata/HEMANT/users01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /opt/oracle/oradata/HEMANT/users03.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0hvbkh12_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0hvbkh12_1_1 tag=TAG20200929T114829
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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 00005 to /opt/oracle/oradata/HEMANT/indx01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /opt/oracle/oradata/HEMANT/users04.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0ivbkh13_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0ivbkh13_1_1 tag=TAG20200929T114829
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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 00006 to /opt/oracle/oradata/HEMANT/users02.dbf
channel ORA_DISK_1: restoring datafile 00009 to /opt/oracle/oradata/HEMANT/users05.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0kvbkh14_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0kvbkh14_1_1 tag=TAG20200929T114829
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 29-SEP-20

RMAN>
RMAN> list backup of archivelog all;


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


BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
22 4.19M DISK 00:00:00 29-SEP-20
BP Key: 22 Status: AVAILABLE Compressed: YES Tag: TAG20200929T114730
Piece Name: /home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1

List of Archived Logs in backup set 22
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 170 442901 29-SEP-20 448666 29-SEP-20
1 171 448666 29-SEP-20 450051 29-SEP-20
1 172 450051 29-SEP-20 451368 29-SEP-20

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
28 4.64M DISK 00:00:01 29-SEP-20
BP Key: 28 Status: AVAILABLE Compressed: YES Tag: TAG20200929T114852
Piece Name: /home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1

List of Archived Logs in backup set 28
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 173 451368 29-SEP-20 454870 29-SEP-20
1 174 454870 29-SEP-20 457558 29-SEP-20
1 175 457558 29-SEP-20 457612 29-SEP-20
1 176 457612 29-SEP-20 459745 29-SEP-20
1 177 459745 29-SEP-20 459768 29-SEP-20

RMAN>
RMAN> recover database until sequence 178;

Starting recover at 29-SEP-20
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/29/2020 22:31:03
RMAN-06556: datafile 6 must be restored from backup older than SCN 459768

RMAN>
RMAN> restore archivelog all;

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/29/2020 22:35:48
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of archived log for thread 1 with sequence 178 and starting SCN of 459768 found to restore

RMAN> restore archivelog until sequence 178;

Starting restore at 29-SEP-20
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/29/2020 22:36:17
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of archived log for thread 1 with sequence 178 and starting SCN of 459768 found to restore

RMAN>
RMAN> list archivelog all;

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

Key Thrd Seq S Low Time
------- ---- ------- - ---------
9 1 178 A 29-SEP-20
Name: /opt/oracle/archivelog/HEMANT/1_178_1052392838.dbf


RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK
validation failed for archived log
archived log file name=/opt/oracle/archivelog/HEMANT/1_178_1052392838.dbf RECID=9 STAMP=1052394543
Crosschecked 1 objects


RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK
List of Archived Log Copies for database with db_unique_name HEMANT
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
9 1 178 X 29-SEP-20
Name: /opt/oracle/archivelog/HEMANT/1_178_1052392838.dbf


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/opt/oracle/archivelog/HEMANT/1_178_1052392838.dbf RECID=9 STAMP=1052394543
Deleted 1 EXPIRED objects


RMAN>
RMAN> restore archivelog all;

Starting restore at 29-SEP-20
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/29/2020 22:37:59
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of archived log for thread 1 with sequence 178 and starting SCN of 459768 found to restore

RMAN> restore archivelog until sequence 177;

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

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=170
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=171
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=172
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1 tag=TAG20200929T114730
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=173
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=174
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=175
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=176
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=177
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1 tag=TAG20200929T114852
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 29-SEP-20

RMAN>
RMAN> exit


Recovery Manager complete.
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 29 22:48:53 2020
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> alter database recover using backup controlfile until cancel;
alter database recover using backup controlfile until cancel
*
ERROR at line 1:
ORA-00279: change 456249 generated at 09/29/2020 11:48:29 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf
ORA-00280: change 456249 for thread 1 is in sequence #174


SQL>
SQL> alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf';
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf'
*
ERROR at line 1:
ORA-00279: change 457558 generated at 09/29/2020 11:48:33 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf
ORA-00280: change 457558 for thread 1 is in sequence #175
ORA-00278: log file '/opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf' no longer needed for this recovery


SQL> alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf';
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf'
*
ERROR at line 1:
ORA-00279: change 457612 generated at 09/29/2020 11:48:41 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf
ORA-00280: change 457612 for thread 1 is in sequence #176
ORA-00278: log file '/opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf' no longer needed for this recovery


SQL> alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf';
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf'
*
ERROR at line 1:
ORA-00279: change 459745 generated at 09/29/2020 11:48:48 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf
ORA-00280: change 459745 for thread 1 is in sequence #177
ORA-00278: log file '/opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf' no longer needed for this recovery


SQL> alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf';
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf'
*
ERROR at line 1:
ORA-00279: change 459768 generated at 09/29/2020 11:48:52 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/HEMANT/1_178_1052392838.dbf
ORA-00280: change 459768 for thread 1 is in sequence #178
ORA-00278: log file '/opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf' no longer needed for this recovery


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-01153: an incompatible media recovery is active


SQL> alter database recover cancel;
alter database recover cancel
*
ERROR at line 1:
ORA-01112: media recovery not started


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 6 was not restored from a sufficiently old backup
ORA-01110: data file 6: '/opt/oracle/oradata/HEMANT/users02.dbf'


SQL>



ArchiveLog Sequence#178 had been created in the source server before the controlfile backup but is not in the Backup Pieces I received.  So, Oracle refuses to allow me to RECOVER the database. 
A RESTORE is succesful, but the RECOVER fails.  The database cannot be OPENed.
Datafiles 6 and 9 have a higher Checkpoint SCN than the highest available in the ArchiveLogs.

Unfortunately, the default behaviour of Oracle is only to report the first Datafiles that has a higher SCN, it doesn't report all of them --- the database might have had 10 or 100 Datafiles that are "newer" than the ArchiveLogs.  That is why the SQL queries on V$BACKUP_DATAFILE and V$BACKUP_ARCHIVELOG_DETAILS that I have demonstrated earlier in this post are useful.


This is what the alert log shows :


2020-09-29T22:29:17.560085+08:00
Full restore complete of datafile 1 /opt/oracle/oradata/HEMANT/system.dbf. Elapsed time: 0:00:06
checkpoint is 456249
2020-09-29T22:29:23.415906+08:00
Full restore complete of datafile 2 /opt/oracle/oradata/HEMANT/sysaux.dbf. Elapsed time: 0:00:05
checkpoint is 457590
last deallocation scn is 450639
2020-09-29T22:29:25.874043+08:00
Full restore complete of datafile 10 /opt/oracle/oradata/HEMANT/indx02.dbf. Elapsed time: 0:00:00
checkpoint is 458680
last deallocation scn is 3
2020-09-29T22:29:29.812208+08:00
Full restore complete of datafile 3 /opt/oracle/oradata/HEMANT/undotbs.dbf. Elapsed time: 0:00:04
checkpoint is 458680
last deallocation scn is 3
2020-09-29T22:29:33.129942+08:00
Full restore complete of datafile 4 /opt/oracle/oradata/HEMANT/users01.dbf. Elapsed time: 0:00:01
checkpoint is 459759
last deallocation scn is 3
Full restore complete of datafile 7 /opt/oracle/oradata/HEMANT/users03.dbf. Elapsed time: 0:00:01
checkpoint is 459759
last deallocation scn is 3
Full restore complete of datafile 5 /opt/oracle/oradata/HEMANT/indx01.dbf. Elapsed time: 0:00:00
checkpoint is 459765
last deallocation scn is 3
Full restore complete of datafile 8 /opt/oracle/oradata/HEMANT/users04.dbf. Elapsed time: 0:00:01
checkpoint is 459765
last deallocation scn is 3
2020-09-29T22:29:35.182200+08:00
Full restore complete of datafile 6 /opt/oracle/oradata/HEMANT/users02.dbf. Elapsed time: 0:00:01
checkpoint is 459779
last deallocation scn is 3
Full restore complete of datafile 9 /opt/oracle/oradata/HEMANT/users05.dbf. Elapsed time: 0:00:01
checkpoint is 459779
last deallocation scn is 3
2020-09-29T22:34:44.026271+08:00
alter database recover using backup controlfile
2020-09-29T22:34:44.026373+08:00
Media Recovery Start
Started logmerger process
2020-09-29T22:34:44.322629+08:00
Parallel Media Recovery started with 2 slaves
ORA-279 signalled during: alter database recover using backup controlfile...
2020-09-29T22:35:34.263529+08:00
*************************************************************


2020-09-29T22:49:27.004784+08:00
alter database recover using backup controlfile until cancel
2020-09-29T22:49:27.004864+08:00
Media Recovery Start
Started logmerger process
2020-09-29T22:49:27.132583+08:00
Parallel Media Recovery started with 2 slaves
ORA-279 signalled during: alter database recover using backup controlfile until cancel...
2020-09-29T22:50:52.692824+08:00
alter database recover using backup controlfile
2020-09-29T22:50:52.692943+08:00
Media Recovery Start
ORA-275 signalled during: alter database recover using backup controlfile...
2020-09-29T22:52:19.356431+08:00
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf'
2020-09-29T22:52:19.356498+08:00
Media Recovery Log /opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf
ORA-279 signalled during: alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf'...
2020-09-29T22:52:36.435252+08:00
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf'
2020-09-29T22:52:36.435374+08:00
Media Recovery Log /opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf
ORA-279 signalled during: alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf'...
2020-09-29T22:52:51.906865+08:00
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf'
2020-09-29T22:52:51.906956+08:00
Media Recovery Log /opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf
ORA-279 signalled during: alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf'...
2020-09-29T22:53:18.228572+08:00
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf'
2020-09-29T22:53:18.228668+08:00
Media Recovery Log /opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf
ORA-279 signalled during: alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf'...
2020-09-29T22:53:25.958701+08:00
alter database open resetlogs
2020-09-29T22:53:26.113916+08:00
Recovery interrupted!
ORA-10877 signalled during: alter database open resetlogs...
2020-09-29T22:53:35.846419+08:00
2020-09-29T22:53:35.846419+08:00
alter database recover cancel
ORA-1112 signalled during: alter database recover cancel...
2020-09-29T22:54:03.274546+08:00
alter database open resetlogs
2020-09-29T22:54:03.306918+08:00
Signalling error 1152 for datafile 6!
ORA-1152 signalled during: alter database open resetlogs...


So, even if I manually RESTORE the ArchiveLogs and then apply each one with the RECOVER LOGFILE command, Oracle still doesn't allow an OPEN RESETOGS because Sequence#178 is missing.


Categories: DBA Blogs

Verifying an RMAN Backup

Fri, 2020-09-25 10:41
 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

Is SQL (Relational ?) difficult : A lesson from NoSQL

Tue, 2020-09-22 01:38

 An excellent article by Franck Pachot : 

A lesson from NoSQL (vs. RDBMS): listen to your users


Categories: DBA Blogs

Pages