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.
No comments:
Post a Comment