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

 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.

Checking for Active Transactions

 Oracle 11.2 introduced the WAIT_ON_PENDING_DML function in the DBMS_UTILITY Package.

Here is a demonstration of how to use it (with anonymous PL/SQL blocks, instead of a Stored Procedure):

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.