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: 13 hours 10 min ago

Restoring a Standby Database to the Primary

Thu, 2021-06-03 11:20

 When you lose the Primary database, you can restore a backup of the Standby to the Primary.


First, I start with a backup of the Standby :



SQL> select
2 i.host_name, i.status,
3 d.dbid, d.name, d.controlfile_type, d.database_role, d.current_scn
4 from v$instance i, v$database d
5 /

HOST_NAME STATUS DBID NAME CONTROL DATABASE_ROLE CURRENT_SCN
---------------- ------------ ---------- --------- ------- ---------------- -----------
ora19cs1 MOUNTED 2778483057 ORCLCDB STANDBY PHYSICAL STANDBY 12445248

SQL> !echo $ORACLE_SID
STDBYDB

SQL>
SQL> alter database recover managed standby database cancel;

Database altered.

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

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jun 3 22:29:59 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, not open)

RMAN> backup as compressed backupset database;

Starting backup at 03-JUN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 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=00001 name=/opt/oracle/oradata/STDBYDB/system01.dbf
input datafile file number=00003 name=/opt/oracle/oradata/STDBYDB/sysaux01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/STDBYDB/undotbs01.dbf
input datafile file number=00007 name=/opt/oracle/oradata/STDBYDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 03-JUN-21
channel ORA_DISK_1: finished piece 1 at 03-JUN-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckswmk4_.bkp tag=TAG20210603T223042 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf
input datafile file number=00012 name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf
input datafile file number=00009 name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf
input datafile file number=00011 name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf
input datafile file number=00026 name=/opt/oracle/oradata/STDBYDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_j36xw6vr_.dbf
channel ORA_DISK_1: starting piece 1 at 03-JUN-21
channel ORA_DISK_1: finished piece 1 at 03-JUN-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jcksybw9_.bkp tag=TAG20210603T223042 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00028 name=/opt/oracle/oradata/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_sysaux_j80dl94j_.dbf
input datafile file number=00027 name=/opt/oracle/oradata/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_system_j80dl8qy_.dbf
input datafile file number=00029 name=/opt/oracle/oradata/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_undotbs1_j80dl96d_.dbf
input datafile file number=00030 name=/opt/oracle/oradata/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_rman_tbs_j80f1p8x_.dbf
channel ORA_DISK_1: starting piece 1 at 03-JUN-21
channel ORA_DISK_1: finished piece 1 at 03-JUN-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckszgbr_.bkp tag=TAG20210603T223042 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf
input datafile file number=00008 name=/opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 03-JUN-21
channel ORA_DISK_1: finished piece 1 at 03-JUN-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckt07r9_.bkp tag=TAG20210603T223042 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 03-JUN-21

Starting Control File and SPFILE Autobackup at 03-JUN-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_06_03/o1_mf_s_1074102643_jckt126x_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 03-JUN-21

RMAN>


Let's say that some of the datafiles on my Primary are corrupt and I need to restore the Primary database from this Standby backup.  (Alternately, instead of restoring to the actual Primary, I might be planning to build a new Primary, maybe at a different Data Centre, from the Standby backup)
Let's assume that I had captured this information about the Primary before I lost the datafiles of the Primary  (I don't really need all this, I just need to know the Archive Log Sequence#s from the alert.log) :

SQL> select
2 i.host_name, i.status,
3 d.dbid, d.name, d.controlfile_type, d.database_role, d.current_scn
4 from v$instance i, v$database d
5 /

HOST_NAME STATUS DBID NAME CONTROL DATABASE_ROLE CURRENT_SCN
---------------- ------------ ---------- --------- ------- ---------------- -----------
ora19cP1 OPEN 2778483057 ORCLCDB CURRENT PRIMARY 12770765

SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archivelog/ORCLCDB
Oldest online log sequence 224
Next log sequence to archive 226
Current log sequence 226
SQL>
SQL> select sequence#, next_change#
2 from v$archived_log
3 where sequence# > 215
4 order by 1
5 /

SEQUENCE# NEXT_CHANGE#
---------- ------------
216 12439207
216 12439207
217 12545592
218 12645850
219 12746127
220 12760012
221 12760153
222 12760157
223 12760486
224 12770051
225 12770693

11 rows selected.

SQL>


Given that the Standby had SCN 12445248, after the RESTORE, I must RECOVER from SEQUENCE#217.
So I begin to restore the Standby backup which I've copied to the /var/tmp folder on the Primary / New Primary Server.  My ORACLE_SID here is ORCLCDB.


oracle19c>cd /var/tmp
oracle19c>tar xvf STDBYDB_bak.TAR
oracle19c>cd STDBYDB
oracle19c>pwd
/var/tmp/STDBYDB
oracle19c>ls -l
total 0
drwxr-x---. 3 oracle oinstall 23 May 22 2020 88129263B99F4BBDE0530100007F7BDF
drwxr-x---. 3 oracle oinstall 23 May 22 2020 8812E29864F47615E0530100007FA424
drwxr-x---. 5 oracle oinstall 60 Jun 3 22:33 autobackup
drwxr-x---. 5 oracle oinstall 60 Jun 3 22:30 backupset
drwxr-x---. 3 oracle oinstall 23 Apr 21 22:38 C07D1F2BA98C23D0E0530100007F7D34
drwxr-x---. 2 oracle oinstall 6 Mar 27 2020 flashback
drwxr-x---. 2 oracle oinstall 6 Mar 26 2020 onlinelog
oracle19c>
oracle19c>echo $ORACLE_SID
ORCLCDB
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jun 3 23:11:01 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 nomount;

Oracle instance started

Total System Global Area 1207955552 bytes

Fixed Size 9134176 bytes
Variable Size 822083584 bytes
Database Buffers 369098752 bytes
Redo Buffers 7639040 bytes

RMAN> restore PRIMARY controlfile from
2> '/var/tmp/STDBYDB/autobackup/2021_06_03/o1_mf_s_1074102643_jckt126x_.bkp';

Starting restore at 03-JUN-21
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/ORCLCDB/control01.ctl
output file name=/opt/oracle/oradata/ORCLCDB/control02.ctl
Finished restore at 03-JUN-21

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN>


I now have the Standby Controlfile restored as a Primary Controlfile.
Next, I must remove information about unavailable backups and catalog only the backups that I have copied from the Standby.


RMAN> crosscheck backup;

Starting implicit crosscheck backup at 03-JUN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
Crosschecked 19 objects
Finished implicit crosscheck backup at 03-JUN-21

Starting implicit crosscheck copy at 03-JUN-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 03-JUN-21

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_02_22/o1_mf_nnndf_TAG20210222T173935_j36yyqqo_.bkp RECID=21 STAMP=1065202775
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_02_22/o1_mf_nnndf_TAG20210222T173935_j36z0h1w_.bkp RECID=22 STAMP=1065202831
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_02_22/o1_mf_nnndf_TAG20210222T173935_j36z1lx8_.bkp RECID=23 STAMP=1065202866
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_02_22/o1_mf_s_1065202724_j36z2q4m_.bkp RECID=24 STAMP=1065202903
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_02_22/o1_mf_annnn_TAG20210222T174817_j36zh2k1_.bkp RECID=25 STAMP=1065203298
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_02_22/o1_mf_s_1065203261_j36zl5fz_.bkp RECID=26 STAMP=1065203397
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4j55_.bkp RECID=27 STAMP=1070491024
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4hv6_.bkp RECID=28 STAMP=1070491023
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g5qmn_.bkp RECID=29 STAMP=1070491063
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g5v6j_.bkp RECID=30 STAMP=1070491067
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g6nhq_.bkp RECID=31 STAMP=1070491092
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g6p4q_.bkp RECID=32 STAMP=1070491093
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g7j4f_.bkp RECID=33 STAMP=1070491119
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g7gj2_.bkp RECID=34 STAMP=1070491118
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_04_21/o1_mf_s_1070490998_j80g8k1q_.bkp RECID=35 STAMP=1070491152
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckswmk4_.bkp RECID=36 STAMP=1074292243
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jcksybw9_.bkp RECID=37 STAMP=1074292298
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckszgbr_.bkp RECID=38 STAMP=1074292334
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckt07r9_.bkp RECID=39 STAMP=1074292359
Crosschecked 19 objects


RMAN>

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
------- ------- --- --- ----------- ----------- ----------
21 21 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_02_22/o1_mf_nnndf_TAG20210222T173935_j36yyqqo_.bkp
22 22 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_02_22/o1_mf_nnndf_TAG20210222T173935_j36z0h1w_.bkp
23 23 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_02_22/o1_mf_nnndf_TAG20210222T173935_j36z1lx8_.bkp
24 24 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_02_22/o1_mf_s_1065202724_j36z2q4m_.bkp
25 25 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_02_22/o1_mf_annnn_TAG20210222T174817_j36zh2k1_.bkp
26 26 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_02_22/o1_mf_s_1065203261_j36zl5fz_.bkp
27 27 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4j55_.bkp
28 28 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4hv6_.bkp
29 29 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g5qmn_.bkp
30 30 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g5v6j_.bkp
31 31 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g6nhq_.bkp
32 32 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g6p4q_.bkp
33 33 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g7j4f_.bkp
34 34 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g7gj2_.bkp
35 35 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_04_21/o1_mf_s_1070490998_j80g8k1q_.bkp
36 36 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckswmk4_.bkp
37 37 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jcksybw9_.bkp
38 38 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckszgbr_.bkp
39 39 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckt07r9_.bkp
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_02_22/o1_mf_nnndf_TAG20210222T173935_j36yyqqo_.bkp RECID=21 STAMP=1065202775
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_02_22/o1_mf_nnndf_TAG20210222T173935_j36z0h1w_.bkp RECID=22 STAMP=1065202831
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_02_22/o1_mf_nnndf_TAG20210222T173935_j36z1lx8_.bkp RECID=23 STAMP=1065202866
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_02_22/o1_mf_s_1065202724_j36z2q4m_.bkp RECID=24 STAMP=1065202903
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_02_22/o1_mf_annnn_TAG20210222T174817_j36zh2k1_.bkp RECID=25 STAMP=1065203298
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_02_22/o1_mf_s_1065203261_j36zl5fz_.bkp RECID=26 STAMP=1065203397
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4j55_.bkp RECID=27 STAMP=1070491024
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4hv6_.bkp RECID=28 STAMP=1070491023
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g5qmn_.bkp RECID=29 STAMP=1070491063
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g5v6j_.bkp RECID=30 STAMP=1070491067
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g6nhq_.bkp RECID=31 STAMP=1070491092
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g6p4q_.bkp RECID=32 STAMP=1070491093
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g7j4f_.bkp RECID=33 STAMP=1070491119
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g7gj2_.bkp RECID=34 STAMP=1070491118
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_04_21/o1_mf_s_1070490998_j80g8k1q_.bkp RECID=35 STAMP=1070491152
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckswmk4_.bkp RECID=36 STAMP=1074292243
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jcksybw9_.bkp RECID=37 STAMP=1074292298
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckszgbr_.bkp RECID=38 STAMP=1074292334
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckt07r9_.bkp RECID=39 STAMP=1074292359
Deleted 19 EXPIRED objects


RMAN>


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

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

List of Files Unknown to the Database
=====================================
File Name: /var/tmp/STDBYDB_bak.TAR
File Name: /var/tmp/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckt07r9_.bkp
File Name: /var/tmp/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jcksybw9_.bkp
File Name: /var/tmp/STDBYDB/autobackup/2021_06_03/o1_mf_s_1074102643_jckt126x_.bkp
File Name: /var/tmp/STDBYDB/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckswmk4_.bkp
File Name: /var/tmp/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckszgbr_.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/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckt07r9_.bkp
File Name: /var/tmp/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jcksybw9_.bkp
File Name: /var/tmp/STDBYDB/autobackup/2021_06_03/o1_mf_s_1074102643_jckt126x_.bkp
File Name: /var/tmp/STDBYDB/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckswmk4_.bkp
File Name: /var/tmp/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckszgbr_.bkp

List of Files Which Were Not Cataloged
=======================================
File Name: /var/tmp/STDBYDB_bak.TAR
RMAN-07517: Reason: The file header is corrupted

RMAN>

RMAN> list backup;


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
40 Full 119.55M DISK 00:00:20 03-JUN-21
BP Key: 40 Status: AVAILABLE Compressed: YES Tag: TAG20210603T223042
Piece Name: /var/tmp/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckt07r9_.bkp
List of Datafiles in backup set 40
Container ID: 2, PDB Name: PDB$SEED
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 4931679 20-MAY-20 NO /opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf
6 Full 4931679 20-MAY-20 NO /opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf
8 Full 4931679 20-MAY-20 NO /opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41 Full 150.14M DISK 00:00:32 03-JUN-21
BP Key: 41 Status: AVAILABLE Compressed: YES Tag: TAG20210603T223042
Piece Name: /var/tmp/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jcksybw9_.bkp
List of Datafiles in backup set 41
Container ID: 3, PDB Name: ORCLPDB1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
9 Full 12441929 01-JUN-21 12444076 NO /opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf
10 Full 12441973 01-JUN-21 NO /opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf
11 Full 12442030 01-JUN-21 12444077 NO /opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf
12 Full 12442056 01-JUN-21 NO /opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf
26 Full 12442098 01-JUN-21 NO /opt/oracle/oradata/STDBYDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_j36xw6vr_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
42 Full 17.95M DISK 00:00:02 03-JUN-21
BP Key: 42 Status: AVAILABLE Compressed: NO Tag: TAG20210603T223304
Piece Name: /var/tmp/STDBYDB/autobackup/2021_06_03/o1_mf_s_1074102643_jckt126x_.bkp
SPFILE Included: Modification time: 03-JUN-21
SPFILE db_unique_name: STDBYDB
Standby Control File Included: Ckp SCN: 12445249 Ckp time: 01-JUN-21

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
43 Full 318.54M DISK 00:00:45 03-JUN-21
BP Key: 43 Status: AVAILABLE Compressed: YES Tag: TAG20210603T223042
Piece Name: /var/tmp/STDBYDB/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckswmk4_.bkp
List of Datafiles in backup set 43
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 12441752 01-JUN-21 12444640 NO /opt/oracle/oradata/STDBYDB/system01.dbf
3 Full 12441842 01-JUN-21 12445226 NO /opt/oracle/oradata/STDBYDB/sysaux01.dbf
4 Full 12441901 01-JUN-21 12445227 NO /opt/oracle/oradata/STDBYDB/undotbs01.dbf
7 Full 12441924 01-JUN-21 NO /opt/oracle/oradata/STDBYDB/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
44 Full 121.72M DISK 00:00:22 03-JUN-21
BP Key: 44 Status: AVAILABLE Compressed: YES Tag: TAG20210603T223042
Piece Name: /var/tmp/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckszgbr_.bkp
List of Datafiles in backup set 44
Container ID: 4, PDB Name: RMANCAT
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
27 Full 11082204 21-APR-21 NO /opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_system_j80dl8qy_.dbf
28 Full 11082204 21-APR-21 NO /opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_sysaux_j80dl94j_.dbf
29 Full 11082204 21-APR-21 NO /opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_undotbs1_j80dl96d_.dbf
30 Full 11082204 21-APR-21 NO /opt/oracle/oradata/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_rman_tbs_j80f1p8x_.dbf

RMAN>

RMAN> quit


Recovery Manager complete.
oracle19c>


So I have now CATALOGed all the backups that I copied over from the Standby server.
Before I proceed with the RESTORE, I verify the "database" information in the controlfile :


oracle19c>echo $ORACLE_SID
ORCLCDB
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 3 23:37:54 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> col host_name format a16
SQL> select
2 i.host_name, i.status,
3 d.dbid, d.name, d.controlfile_type, d.database_role, d.current_scn
4 from v$instance i, v$database d
5 /

HOST_NAME STATUS DBID NAME CONTROL DATABASE_ROLE CURRENT_SCN
---------------- ------------ ---------- --------- ------- ---------------- -----------
ora19cP1 MOUNTED 2778483057 ORCLCDB BACKUP PRIMARY 0

SQL>
SQL> show parameter db_create

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /opt/oracle/oradata
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL>
SQL> create pfile from spfile;

File created.

SQL> shutdown;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> --- at this point modify the created pfile to set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT
SQL> !tail -2 $ORACLE_HOME/dbs/initORCLCDB.ora
*.db_file_name_convert='STDBYDB','ORCLCDB'
*.log_file_name_convert='STDBYDB','ORCLCDB'

SQL>
SQL> startup mount pfile='$ORACLE_HOME/dbs/initORCLCDB.ora';
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 822083584 bytes
Database Buffers 369098752 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> show parameter file_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string STDBYDB, ORCLCDB
log_file_name_convert string STDBYDB, ORCLCDB
pdb_file_name_convert string
SQL>



This shows that I have the correct database controlfile based on the DBID and NAME and that database will now be a PRIMARY, not a Standby.
I cannot check the SCN because the database is not OPEN (in fact, it hasn't been RESTOREd yet !)
However, I had to set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT because the file names in the control file had STDBYDB in the path and db_create_file_dest would not overwrite that.

I can now proceed to RESTORE database :
SQL> quit
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 Jun 3 23:58:05 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, not open)

RMAN> restore database;

Starting restore at 03-JUN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=259 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/ORCLCDB/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/ORCLCDB/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/ORCLCDB/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /opt/oracle/oradata/ORCLCDB/users01.dbf
channel ORA_DISK_1: reading from backup piece /var/tmp/STDBYDB/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckswmk4_.bkp
channel ORA_DISK_1: piece handle=/var/tmp/STDBYDB/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckswmk4_.bkp tag=TAG20210603T223042
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
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/ORCLCDB/pdbseed/system01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /var/tmp/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckt07r9_.bkp
channel ORA_DISK_1: piece handle=/var/tmp/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckt07r9_.bkp tag=TAG20210603T223042
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
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 00009 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
channel ORA_DISK_1: restoring datafile 00026 to /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_jcky1j3g_.dbf
channel ORA_DISK_1: reading from backup piece /var/tmp/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jcksybw9_.bkp
channel ORA_DISK_1: piece handle=/var/tmp/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jcksybw9_.bkp tag=TAG20210603T223042
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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 00027 to /opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_system_j80dl8qy_.dbf
channel ORA_DISK_1: restoring datafile 00028 to /opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_sysaux_j80dl94j_.dbf
channel ORA_DISK_1: restoring datafile 00029 to /opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_undotbs1_j80dl96d_.dbf
channel ORA_DISK_1: restoring datafile 00030 to /opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_rman_tbs_j80f1p8x_.dbf
channel ORA_DISK_1: reading from backup piece /var/tmp/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckszgbr_.bkp
channel ORA_DISK_1: piece handle=/var/tmp/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckszgbr_.bkp tag=TAG20210603T223042
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 04-JUN-21

RMAN>
RMAN> quit


Recovery Manager complete.


I can now proceed to RECOVER the database. Note how I had identified that I would need the ArchiveLogs from SEQUENCE#217 onwards.  Here, either I already have the ArchiveLogs on-disk (if I am restoring the database to the Primary)  OR have copied them from the Old Primary server to the New Primary Server (if I am building the database with a RESTORE on a new Server)
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 4 00:00:48 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>
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12441752 generated at 06/01/2021 17:33:06 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_217_1036108814.dbf
ORA-00280: change 12441752 for thread 1 is in sequence #217


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 12545592 generated at 06/03/2021 14:12:45 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_218_1036108814.dbf
ORA-00280: change 12545592 for thread 1 is in sequence #218
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_217_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 12645850 generated at 06/03/2021 14:19:21 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_219_1036108814.dbf
ORA-00280: change 12645850 for thread 1 is in sequence #219
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_218_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 12746127 generated at 06/03/2021 14:21:22 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_220_1036108814.dbf
ORA-00280: change 12746127 for thread 1 is in sequence #220
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_219_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 12760012 generated at 06/03/2021 15:04:16 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_221_1036108814.dbf
ORA-00280: change 12760012 for thread 1 is in sequence #221
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_220_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 12760153 generated at 06/03/2021 15:04:42 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_222_1036108814.dbf
ORA-00280: change 12760153 for thread 1 is in sequence #222
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_221_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 12760157 generated at 06/03/2021 15:04:43 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_223_1036108814.dbf
ORA-00280: change 12760157 for thread 1 is in sequence #223
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_222_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 12760486 generated at 06/03/2021 22:43:35 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_224_1036108814.dbf
ORA-00280: change 12760486 for thread 1 is in sequence #224
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_223_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 12770051 generated at 06/03/2021 22:45:50 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_225_1036108814.dbf
ORA-00280: change 12770051 for thread 1 is in sequence #225
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_224_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 12770693 generated at 06/03/2021 22:45:55 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_226_1036108814.dbf
ORA-00280: change 12770693 for thread 1 is in sequence #226
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_225_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/opt/oracle/archivelog/ORCLCDB/1_226_1036108814.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7


SQL>
SQL> alter database open resetlogs;

Database altered.

SQL>
SQL> select
2 i.host_name, i.status,
3 d.dbid, d.name, d.controlfile_type, d.database_role, d.current_scn
4 from v$instance i, v$database d
5 /

HOST_NAME STATUS DBID NAME CONTROL DATABASE_ROLE CURRENT_SCN
---------------- ------------ ---------- --------- ------- ---------------- -----------
ora19cP1 OPEN 2778483057 ORCLCDB CURRENT PRIMARY 12773006

SQL>
SQL> select name from v$datafile order by 1;

NAME
------------------------------------------------------------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_jckz2yvk_.dbf
/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_rman_tbs_jckz3r09_.dbf
/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_sysaux_jckz3qy8_.dbf
/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_system_jckz3qym_.dbf
/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_undotbs1_jckz3r04_.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/system01.dbf
/opt/oracle/oradata/ORCLCDB/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/users01.dbf

16 rows selected.

SQL>
SQL> create spfile from pfile;

File created.

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

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 822083584 bytes
Database Buffers 369098752 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /opt/oracle/product/19c/dbhome
_1/dbs/spfileORCLCDB.ora
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archivelog/ORCLCDB
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL> alter system archive log current;

System altered.

SQL>


(Note : I removed the "<" and ">" signs around the "RET" to preservve HTML formatting)

If I had the Online Redo Logs still available on-disk, I could have done a COMPLETE Recovery of the INCOMPLETE Recovery until SEQUENCE#225.  Transactions in SEQUENCE#226 are lost because they were in the Online Redo Log that hadn't been Archived out at the Primary.

Earlier, my Primary was at SCN 12770765,  now (with the INCOMPLETE Recovery) it has been recovered to some transaction at around SCN 12773005 or thereabouts (because the OPEN RESETLOGS also increments the SCN).


Thus, I have restored from a Standby backup as of SCN 12445248 on server ora19cs1, where the ORACLE_SID and file names were STDBYDB  to SCN 12773005 as a new Primary on server ora19cP1 where the ORACLE_SID and file names are ORCLCDB

Of course, since this was an INCOMPLETE Recovery and I have created a new Incarnation of the Primary, I have to FLASHBACK the Standby to a Lower SCN  OR  Rebuild the Standby.
Had I been able to do a COMPLETE Recovery, I could have simply resumed DataGuard between the Primary and the Standby.

Note :  If the Restored Database is no longer to be a Primary in a DataGuard configuration, you can unset LOG_ARCHIVE_DEST_x  (where x is 2 or greater) and drop any Standby Log files currently defined.
Categories: DBA Blogs

Performance Hub for On-Premises Database

Sun, 2021-05-30 04:48

Previously, I have demonstrated the Performance Hub on an Oracle Autonomous Transaction Processing database on the Oracle Cloud.

 However, you can use the DBMS_PERF package for a similar report on your On-Premises database.

The code to generate such a report is (you can connect explicitly to the target PDB to generate the Performance Hub report) :

set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000
spool DBMS_PERF_REPORT.html
select dbms_perf.report_perfhub(is_realtime=>1,type=>'active') from dual;
-- is_realtime 1 and active shows the report for the last 1hour
-- for more options see the documentation on DBMS_PERF
spool off
-- then edit the html file to remove the first line and the spool off command if necessary


I have generated a similar report for the database in my On-Premises VM.


See the 19c documentation on DBMS_PERF for more details.


Note :   The window is only 5minutes because the default selected_start_time parameter is 5minutes before the current time (i.e. the time when the report is generated)


Categories: DBA Blogs

A DDL Trigger ... and writing to the alert log file

Thu, 2021-05-27 08:42

 Below is sample code for a DDL Trigger that writes to the alert log when a specific condition is encountered.



SQL> show user
USER is "SYS"
SQL> alter session set container=orclpdb1;

Session altered.

SQL> @Drop_Table_not_permitted.sql
SQL> create or replace trigger DROP_TABLE_NOT_PERMITTED
2 after ddl
3 on database
4 begin
5 if (ora_sysevent='DROP' and ora_dict_obj_type = 'TABLE')
6 then
7 --- code for INSERT into an audit log table
8 --- INSERT INTO ....
9 ---
10 --- code below is to write to the alert log file
11 --- dbms_log is undocumented but available since 12c or 11.2.0.4
12 --- see https://jonathanlewis.wordpress.com/2018/10/12/dbms_log/
13 --- dbms_log.ksdwrt is the same as dbms_system.ksdwrt
14 --- see https://hemantoracledba.blogspot.com/2008/11/database-event-trigger-and-sysoper.html
15 dbms_log.ksdwrt(2,' Warning : ' || sys_context('USERENV','SESSION_USER')
16 || ' tried to drop ' || ora_dict_obj_name
17 || ' in SID ' || sys_context('USERENV','SID')
18 || ', AUDSID ' || sys_context('USERENV','SESSIONID')
19 || ' and service name ' || sys_context('USERENV','SERVICE_NAME'));
20 raise_application_error(-20001,'You are not permitted to Drop Tables');
21 end if;
22 end;
23 /

Trigger created.

SQL>


When I connect to the PDB and attempt to execute a DROP TABLE ...

SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> desc dummy_tab
Name Null? Type
-------------------------------------------------------------- -------- ------------------------
ID_COL NUMBER

SQL> drop table dummy_tab;
drop table dummy_tab
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.DROP_TABLE_NOT_PERMITTED'
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You are not permitted to Drop Tables
ORA-06512: at line 17


SQL>


The alert log shows this entry :

2021-05-27T21:38:11.859746+08:00
ORCLPDB1(3): Warning : HEMANT tried to drop DUMMY_TAB in SID 272, AUDSID 1061583 and service name orclpdb1


You could use more information from SYS_CONTEXT ( I have retrieved only some pieces of information in my PL/SQL Trigger code above) and populate an Audit Log table with more information.

Comment : The "2" as the first parameter to dbms_log.ksdwrt references the instance's alert log. "1" would reference the session's trace file.  "3" would reference both files.
Categories: DBA Blogs

All Email Subscriptions have been removed

Sun, 2021-05-23 09:43

 I have removed email subscriptions to this blog.

Please try the "Follow This Blog On follow.it"  link on the right



Categories: DBA Blogs

This is a temporary, trial post

Sat, 2021-05-22 10:15

 I am testing something, including email subscription.

This is a temporary, trial post.

This post will be deleted soon.


Categories: DBA Blogs

Email Subscriptions to be disabled (per notice from FeedBurner)

Thu, 2021-05-20 09:39

 I have received this notice from FeedBurner :

Starting in July, we are transitioning FeedBurner onto a more stable, modern infrastructure. This will keep the product up and running for all users, but it also means that we will be turning down most non-core feed management features, including email subscriptions, at that time.

 Therefore, email subscriptions to this blog may stop working sometime in July.

My apologies for the inconvenience.

This blog still does get aggregated by different websites, the one I prefer is OracleFAQ.  So, you may visit that website for this and other Oracle Blog posts.

In the meantime, I might explore if I can setup another RSS Feed for this blog.



Categories: DBA Blogs

Does a Conventional INSERT block Parallel or Direct Path INSERTs ?

Wed, 2021-05-19 09:56


Note : This test is in a 19c database

 In my previous post, I have demonstrated how a Parallel or Direct Path INSERT blocks a Conventional INSERT.

Does a Conventional INSERT block a Parallel or Direct Path INSERT ?  OR is there a different effect on the session attempting the Parallel or Direct Path ?

Taking the same target table,  this is the first session, with a Conventional INSERT :



22:30:18 SQL> insert into target_objects_list
22:30:20 2 select * from dba_objects
22:30:32 3 where rownum lessthan 2 -- changed the "lessthansign" to "lessthan" to preserve formatting in HTML
22:30:43 4 /

1 row created.

22:30:44 SQL>


With the Conventional INSERT not having issued a COMMIT yet, the second session attempts a Parallel INSERT

22:32:14 SQL> alter session enable parallel dml;

Session altered.

22:32:20 SQL> insert /*+ PARALLEL (t 2) */ into target_objects_list t
22:32:30 2 select /*+ PARALLEL (s 2) */ * from objects_list s
22:32:37 3 /



With the second session waiting, let's check what the wait is

WAITING_SESSION
------------------------------------------------------------------------------------------------------------------------------------
LOCK_TYPE MODE_REQUE MODE_HELD LOCK_ID1 LOCK_ID2
----------------- ---------- ---------- ---------- ----------
325
None

w*82
DML Exclusive Row-X (SX) 78449 0


2 rows selected.

SQL> select sql_text from v$sql where sql_id =
2 (select sql_id from v$session
3 where sid=82)
4 /

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
insert /*+ PARALLEL (t 2) */ into target_objects_list t select /*+ PARALLEL (s 2) */ * from objects_list s

1 row selected.

SQL>

Note how the MODE_REQUESTED and MODE_HELD are different from the previous blog post.

What happens when the Conventional INSERT issues a COMMIT ?

22:30:18 SQL> insert into target_objects_list
22:30:20 2 select * from dba_objects
22:30:32 3 where rownum lessthan 2 -- changed the "lessthansign" to "lessthan" to preserve formatting in HTML
22:30:43 4 /

1 row created.

22:30:44 SQL>
22:35:54 SQL>
22:35:55 SQL> commit;

Commit complete.

22:35:56 SQL>


And here is the second session now :

22:32:20 SQL> insert /*+ PARALLEL (t 2) */ into target_objects_list t
22:32:30 2 select /*+ PARALLEL (s 2) */ * from objects_list s
22:32:37 3 /

289641 rows created.

22:36:00 SQL>
22:36:50 SQL> select count(*) from target_objects_list;
select count(*) from target_objects_list
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


22:36:58 SQL> commit;

Commit complete.

22:37:13 SQL> select count(*) from target_objects_list;

COUNT(*)
----------
289642

22:37:20 SQL>


The Parallel INSERT succeeded after the COMMIT by the Conventional INSERT.

If you compare the MODE_REQUESTED and MODE_HELD (from dba_locks) in the two blog posts, the Parallel, Direct Path INSERT requests an Exclusive Lock.

FYI, LOCK_ID1=78449 is :
SQL> l
1 select object_id, object_name
2 from dba_objects
3* where object_id = 78449
SQL> /

OBJECT_ID OBJECT_NAME
---------- ------------------------------
78449 TARGET_OBJECTS_LIST

1 row selected.

SQL>


Here's an experiment you can try :
What if the target is not a table but a Partition of a Table ?
What if the target is an explicitly named Partition ?


Categories: DBA Blogs

Parallel or Direct Path INSERT blocks Conventional INSERT

Tue, 2021-05-18 10:14

 A session executing Parallel (or Direct Path, with APPEND) INSERT will block conventional insert from another session.



23:02:31 SQL> l
1 insert /*+ PARALLEL (t 2) */ into target_objects_list t
2* select /*+ PARALLEL (s 2) */ * from objects_list s
23:02:31 SQL> /

289641 rows created.

23:02:39 SQL>


Session 1 ran a Parallel INSERT from 23:02:31 to 23:02:39.  What happens to another session attempting Conventional Insert from a different session -- even if the second INSERT begins after the Parallel INSERT has completed (but not COMMITed) ?

23:02:36 SQL> insert into target_objects_list
23:02:50 2 select * from dba_objects
23:02:55 3 where rownum lessthan 2 -- changed the "lessthansign" to "lessthan" to preserve formatting in HTML
23:02:58 4 /


A Blocker-Waiter Tree shows :

WAITING_SESSION
------------------------------------------------------------------------------------------------------------------------------------
LOCK_TYPE MODE_REQUE MODE_HELD LOCK_ID1 LOCK_ID2
----------------- ---------- ---------- ---------- ----------
17
None

w*60
DML Row-X (SX) Exclusive 78449 0


2 rows selected.

SQL> select sql_text from v$sql where sql_id =
2 (select sql_id from v$session
3 where sid=60)
4 /

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
insert into target_objects_list select * from dba_objects where rownum lessthan 2 -- changed the "lessthansign" to "lessthan" to preserve formatting in HTML

1 row selected.

SQL>


As soon as I issue a COMMIT in the first session :

23:11:10 SQL>
23:11:11 SQL> select count(*) from target_objects_list
23:11:20 2 /
select count(*) from target_objects_list
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


23:11:23 SQL> commit;

Commit complete.

23:11:28 SQL>


The waiting session with a single-row Conventional INSERT succeeds :

23:02:36 SQL> insert into target_objects_list
23:02:50 2 select * from dba_objects
23:02:55 3 where rownum lessthan 2 -- changed the "lessthansign" to "lessthan" to preserve formatting in HTML
23:02:58 4 /

1 row created.

23:11:27 SQL>


So, be careful when attempting Parallel or Direct Path (APPEND) INSERTs into a table where other sessions may be attempting Conventional INSERTs.



Categories: DBA Blogs

Tracking the Standby Lag from the Primary

Sun, 2021-05-09 10:38

 Here is a quick way of tracking the Standby Lag from the Primary.

This relies on the information in V$ARCHIVE_DEST on the Primary.

Note that this query will not work if the lag is so great that the SCN_TO_TIMESTAMP mapping fails (because the underlying table holds only a limited number of records) OR if the Standby instance is shutdown and the Primary cannot communicate with it.


Note : The lag based on "SCN_TO_TIMESTAMP" is always an approximation.  

SQL> l
1 select scn_to_timestamp(current_scn) - scn_to_timestamp(applied_scn) Time_Diff
2 from v$database d,
3* (select applied_scn from v$archive_dest a where target = 'STANDBY')
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 00:41:09.000000000

SQL>
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 01:07:22.000000000

SQL>
SQL> l
1 select scn_to_timestamp(current_scn) - scn_to_timestamp(applied_scn) Time_Diff
2 from v$database d,
3* (select applied_scn from v$archive_dest a where target = 'STANDBY')
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 01:07:58.000000000

SQL>
SQL> l
1 select scn_to_timestamp(current_scn) - scn_to_timestamp(applied_scn) Time_Diff
2 from v$database d,
3* (select applied_scn from v$archive_dest a where target = 'STANDBY')
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 01:13:16.000000000

SQL>
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 01:13:37.000000000

SQL>
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000000 00:00:00.000000000

SQL>


Here, the lag was 4 days and it took some time for the Standby to catchup with the Primary.
(this is my Lab environment, not a real production environment at my work place, so don't ask how I managed to create a lag of 4 days or how long it took for the Standby to catch-up with the Pirmary)

Note : If the Standby database is down and/or the lag is very high, you will get error :
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

for the "applied_scn" from v$archive_dest.  (If the Standby is down, the value for "applied_scn" in v$archive_dest on the Primary is "0").


If you have access to the Standby you can run this query :

select name, value from v$dataguard_stats where name like '%lag'


The demo above is only a quick away by querying the Primary without accessing the Standby
Categories: DBA Blogs

RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

Wed, 2021-05-05 09:40

 You are attempting to restore a database to another server.  

So, you have verified that you have controlfile and datafile backups on the source server  :



RMAN> list backup of controlfile;

using target database control file instead of recovery catalog

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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51 Full 11.52M DISK 00:00:01 20-FEB-21
BP Key: 51 Status: AVAILABLE Compressed: NO Tag: TAG20210220T114245
Piece Name: /opt/oracle/FRA/HEMANT/autobackup/2021_02_20/o1_mf_s_1065008565_j3119p5t_.bkp
Control File Included: Ckp SCN: 1093419 Ckp time: 20-FEB-21

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
55 Full 11.52M DISK 00:00:02 04-MAY-21
BP Key: 55 Status: AVAILABLE Compressed: NO Tag: TAG20210504T232054
Piece Name: /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703254_j92slr2m_.bkp
Control File Included: Ckp SCN: 1126526 Ckp time: 04-MAY-21

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
56 Full 11.48M DISK 00:00:01 04-MAY-21
BP Key: 56 Status: AVAILABLE Compressed: NO Tag: TAG20210504T232851
Piece Name: /home/oracle/controlfile.bak
Control File Included: Ckp SCN: 1126757 Ckp time: 04-MAY-21

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
57 Full 11.52M DISK 00:00:02 04-MAY-21
BP Key: 57 Status: AVAILABLE Compressed: NO Tag: TAG20210504T232853
Piece Name: /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703733_j92t1pow_.bkp
Control File Included: Ckp SCN: 1126766 Ckp time: 04-MAY-21

RMAN>


You have copied the backups to the target, new, server and attempt to restore :

oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 5 22:27:26 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 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/controlfile.bak';

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/05/2021 22:27:47
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

RMAN>
RMAN> quit


Recovery Manager complete.
oracle19c>ls /home/oracle/controlfile.bak
/home/oracle/controlfile.bak
oracle19c>ls /opt/oracle/FRA/HEMANT/autobackup/2021_02_20/o1_mf_s_1065008565_j3119p5t_.bkp
/opt/oracle/FRA/HEMANT/autobackup/2021_02_20/o1_mf_s_1065008565_j3119p5t_.bkp
oracle19c>ls /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703254_j92slr2m_.bkp
/opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703254_j92slr2m_.bkp
oracle19c>ls /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703733_j92t1pow_.bkp
/opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703733_j92t1pow_.bkp
oracle19c>


So, why do you get the RMAN-06172 error ?  All the controlfile backups, including the manual backup to /home/oracle/controlfile.bak and the three autobackups, one from February 2021 and two from 04-May-2021 are available.

oracle19c>oerr rman 6172
6172, 1, "no AUTOBACKUP found or specified handle is not a valid copy or piece"
// *Cause: A restore could not proceed because no AUTOBACKUP was found or
// specified handle is not a valid copy or backup piece.
// In case of restore from AUTOBACKUP, it may be the case that a
// backup exists, but it does not satisfy the criteria specified in
// the user's restore operands.
// In case of restore from handle, it may be the handle is not a
// backup piece or control file copy. In may be that it does not
// exist.
// *Action: Modify AUTOBACKUP search criteria or verify the handle.
oracle19c>
oracle19c>ls -l /home/oracle/controlfile.bak
-rw-r-----. 1 root root 12058624 May 4 23:28 /home/oracle/controlfile.bak
oracle19c>ls -l /opt/oracle/FRA/HEMANT/autobackup/2021_02_20/o1_mf_s_1065008565_j3119p5t_.bkp
-rw-r-----. 1 root root 12091392 Feb 20 11:42 /opt/oracle/FRA/HEMANT/autobackup/2021_02_20/o1_mf_s_1065008565_j3119p5t_.bkp
oracle19c>ls -l /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703254_j92slr2m_.bkp
-rw-r-----. 1 root root 12091392 May 4 23:20 /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703254_j92slr2m_.bkp
oracle19c>ls -l /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703733_j92t1pow_.bkp
-rw-r-----. 1 root root 12091392 May 4 23:28 /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703733_j92t1pow_.bkp
oracle19c>


You get the "error" message that there are no AUTOBACKUPs because the "oracle19c" account is unable to actually *read* those pieces.  It can list them using "ls" because it has permission to read the OS folders containing them, but it does no have permission to read the files owned by root without having granted read permission.

So, before you start wondering about your AUTOBACKUP configuration or search criteria specification like "RESTORE CONTROLFILE FROM AUTOBACKUP MAXDAYS 30",  check if the backup pieces are readable.


Categories: DBA Blogs

My Posts on RMAN

Sat, 2021-05-01 23:05

 My series of posts on RMAN :

1. 1 : Backup Job Details

2. 2 : ArchiveLog Deletion Policy

3. 3 : The DB_UNIQUE_NAME in Backups to the FRA

4. 4 : Recovering from an Incomplete Restore

5. 4b : Recovering from an Incomplete Restore with OMF Files

6. 5 : Useful KEYWORDs and SubClauses

7. 5b : (More) Useful KEYWORDs and SubClauses

8. 5c : (Some More) Useful KEYWORDs and SubClauses

9. 6 : RETENTION POLICY and CONTROL_FILE_RECORD_KEEP_TIME

10. 7 : Recovery Through RESETLOGS -- how are the ArchiveLogs identified ?

11. 8 : Using a Recovery Catalog Schema

12. 9 : Querying the RMAN Views / Catalog

13. 10 : VALIDATE


An older series of "tips" :

14. Tips -- 1

15. Tips -- 2

16. Tips -- 3

17. Tips -- 4


Other RMAN posts not in the  above series : (not in any particular order)

18. RMAN's CATALOG command

19. RESTORE and RECOVER a NOARCHIVELOG Database, with Incremental Backups

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

21. Primary and Standby in the same RMAN Catalog

22. Understanding Obsolescence of RMAN Backups

23. "SET TIME ON" in RMAN

24. RMAN Backup of a Standby Database

25. RMAN Image Copy File Names

26. Verifying an RMAN Backup

27. Verifying an RMAN Backup - Part 2

28. Misinterpreting RESTORE DATABASE VALIDATE

29. RMAN Backup and Recovery for Loss of ALL Files

30. CONTROLFILE AUTOBACKUPs are OBSOLETE[d]

31.RMAN Consistent ("COLD" ?) Backup and Restore

32. Archive Log Deletion Policy with a Standby Database

33. Datafiles not Restored -- using V$DATAFILE and V$DATAFILE_HEADER

34. Read Only Tablespaces and BACKUP OPTIMIZATION


Categories: DBA Blogs

Pro*C in Oracle

Sat, 2021-05-01 05:48

 Oracle also ships a Pro*C Precompiler that can convert a Pro*C source file to a C source file which can then be compiled  using a C Compiler (e.g  using "gcc").  Of course, you need the Pro*C Developer Licence to use this product.

Here is a quick demo with the command line display and then the actual code below.



oracle19c>ls -ltr
total 12
-rw-r--r--. 1 oracle oinstall 2255 May 1 18:07 instancedbinfo.pc
-rwxr--r--. 1 oracle oinstall 786 May 1 18:14 Compile_my_ProC.SH
-rwxr--r--. 1 oracle oinstall 356 May 1 18:15 Run_my_ProC.SH
oracle19c>./Compile_my_ProC.SH
*****Set LD_LIBRARY_PATH
*****Set C_INCLUDE_PATH
*****PreCompile Pro*C program file

Pro*C/C++: Release 19.0.0.0.0 - Production on Sat May 1 18:15:17 2021
Version 19.3.0.0.0

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

System default option values taken from: /opt/oracle/product/19c/dbhome_1/precomp/admin/pcscfg.cfg

*****Compile using C Compiler and specifying Oracle Client library file libclntsh.so
*****Compiled files:
-rw-r--r--. 1 oracle oinstall 2255 May 1 18:07 instancedbinfo.pc
-rw-r--r--. 1 oracle oinstall 0 May 1 18:15 instancedbinfo.lis
-rw-r--r--. 1 oracle oinstall 11875 May 1 18:15 instancedbinfo.c
-rwxr-xr-x. 1 oracle oinstall 14424 May 1 18:15 instancedbinfo
oracle19c>
oracle19c>
oracle19c>
oracle19c>./Run_my_ProC.SH
*****Set LD_LIBRARY_PATH
*****Set Connection String
*****Execute the program
Connected to ORACLE
At ORCLCDB which is on oracle-19c-vagrant running 19.0.0.0.0 and is OPEN, started at 01-MAY-21 17:54:52
This is ORCLPDB1 database running in READ WRITE mode since 01-MAY-21 05.55.21.573 PM +08:00

oracle19c>


The file "instancedbinfo.pc" is my Pro*C source code.
I Precompile it using the "proc" precompiler into "instancedbinfo.c".  Any compilation errors would have been logged into "instancedbinfo.lis"
Then, the same script "Compile_my_ProC.SH" compiles the C program source code into an executable "instancedbinfo" using "gcc"

Finally, I use "Run_my_ProC.SH" to execute the file "instancedbinfo"  (which is now an executable) and the execution displays information about the Pluggable database it is connected to.


Here is the code for the two shell scripts :


oracle19c>cat Compile_my_ProC.SH

echo "*****Set LD_LIBRARY_PATH"
LD_LIBRARY_PATH=/usr/lib/gcc/x86_64-redhat-linux/4.8.2/include:/usr/include/linux:/opt/oracle/product/19c/dbhome_1/precom/lib:/opt/oracle/product/19c/dbhome_1/lib
export LD_LIBRARY_PATH


echo "*****Set C_INCLUDE_PATH"
C_INCLUDE_PATH=/usr/lib/gcc/x86_64-redhat-linux/4.8.2/include:/usr/include/linux:/opt/oracle/product/19c/dbhome_1/precom/lib:/opt/oracle/product/19c/dbhome_1/lib:/opt/oracle/product/19c/dbhome_1/precomp/public
export C_INCLUDE_PATH

echo "*****PreCompile Pro*C program file"
proc instancedbinfo.pc

echo "*****Compile using C Compiler and specifying Oracle Client library file libclntsh.so"
gcc instancedbinfo.c -o instancedbinfo -L /opt/oracle/product/19c/dbhome_1/lib -l clntsh

echo "*****Compiled files:"
ls -ltr instancedbinfo*
oracle19c>


oracle19c>cat Run_my_ProC.SH

echo "*****Set LD_LIBRARY_PATH"
LD_LIBRARY_PATH=/usr/lib/gcc/x86_64-redhat-linux/4.8.2/include:/usr/include/linux:/opt/oracle/product/19c/dbhome_1/precom/lib:/opt/oracle/product/19c/dbhome_1/lib
export LD_LIBRARY_PATH

echo "*****Set Connection String"
CNCTSTRING=hemant/hemant@orclpdb1
export CNCTSTRING

echo "*****Execute the program"
./instancedbinfo
oracle19c>


The Compilation script specifies the LD_LIBRARY_PATH and the Paths to the Include (.h Header) files.  
It then executes "proc"  (which is in $ORACLE_HOME/bin) to precompile the "instancedbinfo.pc" source file.
Finally, it calls "gcc" to compile the c-language source code file (generated by the Precomipler), also specifiying the client shared library file libclntsh.so  in $ORACLE_HOME/lib  (only "-l clntsh" is sufficient to identify the file name).  The compiled executable is called "instancedbinfo" with Execute Permission.

The Run script specifies the Connect-String that the executable will be reading from the environment and executes it.


Here is the code of the source Pro*C file :


oracle19c>cat instancedbinfo.pc

/* standard C includes */
#include << stdio.h >>
#include << stdlib.h >>
#include << string.h >>



/* Oracle Pro*C includes from $ORACLE_HOME/precomp/public */
#include << sqlca.h >>
#include << sqlda.h >>
#include << sqlcpr.h >>




/* my variables */
varchar MYinstanceName[16];
varchar MYhostName[64];
varchar MYversion[17];
varchar MYstatus[12];
varchar MYinstanceStartupTime[18];
varchar MYdbName[128];
varchar MYdbOpenMode[10];
varchar MYdbOpenTime[32];



/* function for error handling */
void sql_error(msg)
char msg[200];
{
char err_msg[128];
size_t buf_len, msg_len;

EXEC SQL WHENEVER SQLERROR CONTINUE;

printf("\n%s\n", msg);
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s\n", msg_len, err_msg);

EXEC SQL ROLLBACK RELEASE;
exit(EXIT_FAILURE);
}


/* MAIIN program */
int main(argc,argv)
int argc;
char *argv[];
{

/* read Connection String from environment -- or, it could have been hardcoded here */
const char *conn = getenv("CNCTSTRING");
if (!conn) {
printf("! require CNCTSTRING env variable\n");
return (1);
}

EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");

/* connect to targe database */
EXEC SQL CONNECT :conn ;
printf("Connected to ORACLE \n");


/* execute query and populate variables */
/* NOTE : This expects to connect to a PDB ! */
/* If the target is a Non-PDB, change references from v$pdbs to V$database */
EXEC SQL SELECT instance_name,host_name, version,
to_char(startup_time,'DD-MON-RR HH24:MI:SS'), status,
name, open_mode, to_char(open_time)
INTO :MYinstanceName, :MYhostName, :MYversion,
:MYinstanceStartupTime, :MYstatus,
:MYdbName, :MYdbOpenMode, :MYdbOpenTime
FROM v$instance, v$pdbs ;


/* display query results */
printf("At %s which is on %s running %s and is %s, started at %s \n",
MYinstanceName.arr, MYhostName.arr, MYversion.arr, MYstatus.arr, MYinstanceStartupTime.arr);
printf("This is %s database running in %s mode since %s \n",
MYdbName.arr, MYdbOpenMode.arr, MYdbOpenTime.arr);
printf("\n");

/* end of MAIN */
}
oracle19c>


(Note :  I have put doube angle brackets for the #includes so as to preserve them in HTML)
Pro*C allows embedding of SQL calls into a C program be including the Proc include files and then running the source code through a Precompiler.
My Pro*C source code file is 2,255 bytes and the C source code is 11,875 bytes.

Note that the variables defined as varchar in my Pro*C source file are actually become C structures :

/* my variables */
/* varchar MYinstanceName[16]; */
struct { unsigned short len; unsigned char arr[16]; } MYinstanceName;

/* varchar MYhostName[64]; */
struct { unsigned short len; unsigned char arr[64]; } MYhostName;

/* varchar MYversion[17]; */
struct { unsigned short len; unsigned char arr[17]; } MYversion;

/* varchar MYstatus[12]; */
struct { unsigned short len; unsigned char arr[12]; } MYstatus;

/* varchar MYinstanceStartupTime[18]; */
struct { unsigned short len; unsigned char arr[18]; } MYinstanceStartupTime;

/* varchar MYdbName[128]; */
struct { unsigned short len; unsigned char arr[128]; } MYdbName;

/* varchar MYdbOpenMode[10]; */
struct { unsigned short len; unsigned char arr[10]; } MYdbOpenMode;

/* varchar MYdbOpenTime[32]; */
struct { unsigned short len; unsigned char arr[32]; } MYdbOpenTime;


Similarly, my EXEC SQL query also gets re-written :
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 13;
sqlstm.arrsiz = 8;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.stmt = "select instance_name ,host_name ,version ,to_char(startup\
_time,'DD-MON-RR HH24:MI:SS') ,status ,name ,open_mode ,to_char(open_time) int\
o :b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7 from v$instance ,v$pdbs ";
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )51;
sqlstm.selerr = (unsigned short)1;
sqlstm.sqlpfmem = (unsigned int )0;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlstm.sqhstv[0] = (unsigned char *)&MYinstanceName;
sqlstm.sqhstl[0] = (unsigned long )18;
sqlstm.sqhsts[0] = ( int )0;
sqlstm.sqindv[0] = ( short *)0;
sqlstm.sqinds[0] = ( int )0;
sqlstm.sqharm[0] = (unsigned long )0;
sqlstm.sqadto[0] = (unsigned short )0;
sqlstm.sqtdso[0] = (unsigned short )0;
sqlstm.sqhstv[1] = (unsigned char *)&MYhostName;
and so on .....


Pro*C is a very good way of combining C programming with SQL and creating an executable binary instead of an interpreted file (like a Java or Python program outside the database).



Categories: DBA Blogs

OJVM : Loading Java Code and Running it from a Database Session

Sun, 2021-04-25 06:08

 Oracle's JVM in the database, available since 8i and also known as "OJVM" allows you (Developer or DBA) to load Java code into the database and execute it from a database session.  Note that any OS calls that it makes will be from the server process, not the client process. Thus, if the code tries to read or write to a filesystem or to connect to an external website, the call will be executed by the database server process, not your client (sqlplus or any other program).


Here is a simple example :



SQL> -- grant Java Code permission to only READ only /home/oracle/tmp
SQL> -- this should have been provided by the DBA
SQL>
SQL> begin
2 dbms_java.grant_permission('HEMANT',
3 'SYS:java.io.FilePermission',
4 '/home/oracle/tmp', 'read');
5
6 dbms_java.grant_permission( 'HEMANT',
7 'SYS:java.io.FilePermission',
8 '/home/oracle/tmp/*',
9 'read' );
10
11 dbms_java.grant_permission( 'HEMANT',
12 'SYS:java.lang.RuntimePermission',
13 'getFileSystemAttributes',
14 '' );
15 end;
16 /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL>
SQL> -- here is where I load the Java code into my schema
SQL> connect hemant/hemant@orclpdb1
Connected.
SQL>
SQL> -- load the java code into the database
SQL> create or replace and compile java source named "readOSDirectory"
2 as
3 /* using java.io */
4 import java.io.*;
5
6 /* create the main class */
7 public class readOSDirectory
8 {
9 /* create the class to be executed from a procedure */
10 public static void getList(String directory)
11 {
12 /* use File class from java.io */
13 File directoryPath = new File( directory );
14 File filesList[] = directoryPath.listFiles();
15
16 /* read till end of list */
17 for (File file : filesList)
18 {
19 System.out.println("File Name: "+file.getName()
20 + " File Size: "+file.length());
21 }
22 }
23 }
24 /

Java created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> /* create a PL/SQL procedure to call the getList class */
SQL> create or replace
2 procedure Read_Directory( p_directory in varchar2 )
3 as language java
4 name 'readOSDirectory.getList( java.lang.String )';
5 /

Procedure created.

SQL>
SQL> /* enable output to screen-- serveroutput is for my sqlplus session */
SQL> SET SERVEROUTPUT ON SIZE 100000
SQL> CALL dbms_java.set_output (100000);

Call completed.

SQL>
SQL> begin
2 Read_Directory('/home/oracle/tmp');
3 end;
4 /
File Name: File_1.txt File Size: 13
File Name: File_2.txt File Size: 25
File Name: Run_Sequence_Loop.sql File Size: 1047
File Name: List_LongOps.sql File Size: 303
File Name: create_db_HEMANT.sql File Size: 1530
File Name: create_db_HEMANT.lst File Size: 47

PL/SQL procedure successfully completed.

SQL>
SQL> begin
2 Read_Directory('/home/oracle');
3 end;
4 /
Exception in thread "Root Thread" java.security.AccessControlException: the Permission ("java.io.FilePermission" "/home/oracle"
"read") has not been granted to HEMANT. The PL/SQL to grant this is dbms_java.grant_permission( 'HEMANT',
'SYS:java.io.FilePermission', '/home/oracle', 'read' )
at java.security.AccessControlContext.checkPermission(AccessControlContext.java)
at java.security.AccessController.checkPermission(AccessController.java:926)
at java.lang.SecurityManager.checkPermission(SecurityManager.java:551)
at oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java:210)
at java.lang.SecurityManager.checkRead(SecurityManager.java:890)
at java.io.File.list(File.java:1117)
at java.io.File.listFiles(File.java:1207)
at readOSDirectory.getList(readOSDirectory:11)
begin
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission
("java.io.FilePermission" "/home/oracle" "read") has not been granted to HEMANT. The PL/SQL to grant this is
dbms_java.grant_permission( 'HEMANT', 'SYS:java.io.FilePermission', '/home/oracle', 'read' )
ORA-06512: at "HEMANT.READ_DIRECTORY", line 1
ORA-06512: at line 2


SQL>
SQL> !ls -l /home/oracle/tmp
total 24
-rw-r--r--. 1 oracle oinstall 47 Apr 25 18:46 create_db_HEMANT.lst
-rw-r--r--. 1 oracle oinstall 1530 Apr 25 18:46 create_db_HEMANT.sql
-rw-r--r--. 1 oracle oinstall 13 Apr 25 18:07 File_1.txt
-rw-r--r--. 1 oracle oinstall 25 Apr 25 18:34 File_2.txt
-rw-r--r--. 1 oracle oinstall 303 Apr 25 18:46 List_LongOps.sql
-rw-r--r--. 1 oracle oinstall 1047 Apr 25 18:46 Run_Sequence_Loop.sql

SQL>


(Note : The Java call reads from the OS's directory object "as is" returned by the directory.  The "ls -l" command sorts the output, so you may see a difference in the sorted output)

Since OJVM runs in a database server process, strong permission controls are enforced.  So, the first set of calls show permissions being granted by the DBA to the "HEMANT" schema to only read from /home/oracle/tmp

Then, the user (HEMANT) connects and loads his Java code into the database.  (Note : The first time you run this, it may take some time to load the java classes).  Then he creates a PL/SQL procedure as a "wrapper" that can execute the Java code.

Finally, he executes the java code to read the target directory /home/oracle/tmp

I also demonstrate how his attempt to read /home/oracle fails as he has not been granted explicit permission on this directory

Categories: DBA Blogs

Primary and Standby in the same RMAN Catalog

Wed, 2021-04-21 10:23

 A quick demo of an RMAN Catalog shared by both Primary and Standby databases

For this demo, the RMAN Catalog is in the "rmanschema" account in the "rmancat" database, accessible from both Primary and Standby

At the Primary :



oracle19c>echo $ORACLE_SID
ORCLCDB
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 22:22:02 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 name, open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
ORCLCDB READ WRITE

SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ORCLCDB
SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:22:54 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)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> quit


Recovery Manager complete.
oracle19c>


Now, at the Standby



oracle19c>echo $ORACLE_SID
STDBYDB
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 22:25:13 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 name, open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
ORCLCDB MOUNTED

SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string STDBYDB
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:26:03 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, not open)
connected to recovery catalog database

RMAN> register database;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of register command at 04/21/2021 22:26:19
RMAN-01005: Mounted control file type must be CURRENT to register the database

RMAN> quit


Recovery Manager complete.
oracle19c>


(Note that the Standby has the same NAME and DBID, but a distinct DB_UNIQUE_NAME.)

It is NOT necessary to register the Standby as long as the DB_UNIQUE_NAME is different and is not currently "known to the Catalog" (i.e. is not already registered for some other database).

So, how do I run RMAN Backups and have them saved in the Catalog ?

On the Primary, I run a FULL Backup.


oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:31:08 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)
connected to recovery catalog database

RMAN> backup as compressed backupset database;

Starting backup at 21-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=280 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=00001 name=/opt/oracle/oradata/ORCLCDB/system01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/ORCLCDB/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 21-APR-21
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00003 name=/opt/oracle/oradata/ORCLCDB/sysaux01.dbf
input datafile file number=00007 name=/opt/oracle/oradata/ORCLCDB/users01.dbf
channel ORA_DISK_2: starting piece 1 at 21-APR-21
channel ORA_DISK_2: finished piece 1 at 21-APR-21
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223134_j80ft7v9_.bkp tag=TAG20210421T223134 comment=NONE

...
...
...
Starting Control File and SPFILE Autobackup at 21-APR-21
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2021_04_21/o1_mf_s_1070490815_j80fy0hv_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-APR-21

RMAN> quit


Recovery Manager complete.
oracle19c>


Then, I run a separate backup at the Standby :


oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:36:37 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, not open)
connected to recovery catalog database

RMAN> backup as compressed backupset database;

Starting backup at 21-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=253 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=249 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=00001 name=/opt/oracle/oradata/STDBYDB/system01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/STDBYDB/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 21-APR-21
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00003 name=/opt/oracle/oradata/STDBYDB/sysaux01.dbf
input datafile file number=00007 name=/opt/oracle/oradata/STDBYDB/users01.dbf
channel ORA_DISK_2: starting piece 1 at 21-APR-21
channel ORA_DISK_2: finished piece 1 at 21-APR-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4j55_.bkp tag=TAG20210421T223703 comment=NONE

...
...
...
Starting Control File and SPFILE Autobackup at 21-APR-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_04_21/o1_mf_s_1070490998_j80g8k1q_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-APR-21

RMAN> quit


Recovery Manager complete.
oracle19c>


So, I now have two different database backups of the same NAME and DBID registered in one catalog.

How do I distinguish them ?  From the RMAN command lime query  I can verify in this manner (querying only for datafile 1 for the purpose of this demo)

At the Primary :
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:43:42 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)
connected to recovery catalog database

RMAN> list backup of datafile 1 completed after "sysdate-1";


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1245 Full 231.82M DISK 00:00:38 21-APR-21
BP Key: 1255 Status: AVAILABLE Compressed: YES Tag: TAG20210421T223134
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223134_j80ft7pb_.bkp
List of Datafiles in backup set 1245
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 11072202 21-APR-21 NO /opt/oracle/oradata/ORCLCDB/system01.dbf

RMAN> quit


Recovery Manager complete.
oracle19c>


And at the Standby :

oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:45:03 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, not open)
connected to recovery catalog database

RMAN> list backup of datafile 1 completed after "sysdate-1";


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1300 Full 241.08M DISK 00:00:40 21-APR-21
BP Key: 1308 Status: AVAILABLE Compressed: YES Tag: TAG20210421T223703
Piece Name: /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4hv6_.bkp
List of Datafiles in backup set 1300
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 11061162 21-APR-21 11072277 NO /opt/oracle/oradata/STDBYDB/system01.dbf

RMAN> quit


Recovery Manager complete.
oracle19c>


RMAN doesn't show the database hostname (and a RAC database could be on multiple hosts, with rman backup channels running from multiple hosts concurrently, backups could be written to tape instead of disk).

But the listing does show that 

the Primary database datafile /opt/oracle/oradata/ORCLCDB/system01.dbf  is in the backup- piece /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223134_j80ft7pb_.bkp 

while  the Standby database datafile /opt/oracle/oradata/STDBYDB/system01.dbf is in the backup-piece /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4hv6_.bkp.

You might also notice that the Checkpoint SCNs are different.



Can I run an SQL query in the RMAN Catalog schema itself ?

oracle19c>sqlplus rmanschema/rmanschema@rmancat

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 22:49:02 2021
Version 19.3.0.0.0

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

Last Successful login time: Wed Apr 21 2021 22:45:04 +08:00

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

SQL> select db_key, dbid, name from rc_database;

DB_KEY DBID NAME
---------- ---------- --------
1 2778483057 ORCLCDB

SQL>
SQL> col db_unique_name format a16
SQL> select site_key, db_key, database_role, db_unique_name from rc_site;

SITE_KEY DB_KEY DATABAS DB_UNIQUE_NAME
---------- ---------- ------- ----------------
3 1 PRIMARY ORCLCDB
804 1 STANDBY STDBYDB

SQL>
SQL> l
1 select site.site_key, site.database_role, site.db_unique_name,
2 bs.bs_key, bs.backup_type
3 from rc_site site, rc_backup_set bs
4 where site.site_key=bs.site_key
5 and site.db_key=bs.db_key
6 and bs.start_time > sysdate-1
7* and bs.bs_key in (1245,1300)
SQL> /

SITE_KEY DATABAS DB_UNIQUE_NAME BS_KEY B
---------- ------- ---------------- ---------- -
3 PRIMARY ORCLCDB 1245 D
804 STANDBY STDBYDB 1300 D

SQL>
SQL> l
1 select site.site_key, site.database_role, site.db_unique_name,
2 df.bs_key, df.file#, df.checkpoint_change#
3 from rc_site site, rc_backup_set bs, rc_backup_datafile df
4 where site.site_key=bs.site_key
5 and site.db_key=bs.db_key
6 and site.db_key=df.db_key
7 and bs.bs_key=df.bs_key
8 and bs.start_time > sysdate-1
9 and bs.bs_key in (1245,1300)
10* and df.file#=1
SQL> /

SITE_KEY DATABAS DB_UNIQUE_NAME BS_KEY FILE# CHECKPOINT_CHANGE#
---------- ------- ---------------- ---------- ---------- ------------------
3 PRIMARY ORCLCDB 1245 1 11072202
804 STANDBY STDBYDB 1300 1 11061162


Here we can set that RC_DATABASE has only 1 record for the actual database.  But we can distinguish the Primary and Standby from RC_SITE. SITE_KEY is 3 is for the Primary and 804 is for the Standby.

BS_KEY is the "BS Key" in the "LIST BACKUP" command output from RMAN.  Today's backup of Datafile#1 at the Primary was in Backup Set  1245 while that of the same datafile at the Standby was in Backup Set 1300.  (Yes, the Primary and Standby can have different Backup Set numbers and the Standby may well have higher Backup Set numbers if I have run more frequent Backups at the Standby !)
You can also compare the CHECKPOINT_CHANGE# as the "Ckp SCN" in the LIST BACKUP output. The SYSTEM datafile#1 was at SCN 11072202 in the Primary Backup and SCN 11061162 in the Standby Backup.

There are many more RC_%  Tables and Views in the RMAN Schema that you can query.


Categories: DBA Blogs

Python with Oracle using sqlalchemy and cx_oracle

Sun, 2021-04-11 09:46

 Here is a simple demo of using Python and the sqlalchemy and cx_oracle libraries


This is the code :

#import required libraries
import pandas as pd
import cx_Oracle
import sqlalchemy
from sqlalchemy.exc import SQLAlchemyError

#setup connection
try:
oracle_pdb = sqlalchemy.create_engine("oracle+cx_oracle://hemant:hemant@localhost/?service_name=orclpdb1", arraysize=100)
except SQLAlchemyError as e:
print(e)

#setup query and pandas dataframe for results
try:
employees_query = """SELECT * FROM hr.employees order by employee_id""";
df_employees = pd.read_sql(employees_query, oracle_pdb)
except SQLAlchemyError as e:
print(e)

#Info on the dataframe
print(df_employees.info())
#the first five rows
print(df_employees.head())

#create a new dataframe with a subset of columns
df_emp_selected_cols=df_employees
df_emp_selected_cols.drop(['email','phone_number','salary','commission_pct','manager_id','department_id'],axis=1, inplace=True)
print(df_emp_selected_cols.head())


And here is the output from my database :

Info on the dataframe

RangeIndex: 108 entries, 0 to 107
Data columns (total 11 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 employee_id 108 non-null int64
1 first_name 108 non-null object
2 last_name 108 non-null object
3 email 108 non-null object
4 phone_number 107 non-null object
5 hire_date 108 non-null datetime64[ns]
6 job_id 108 non-null object
7 salary 107 non-null float64
8 commission_pct 35 non-null float64
9 manager_id 106 non-null float64
10 department_id 107 non-null float64
dtypes: datetime64[ns](1), float64(4), int64(1), object(5)
memory usage: 9.4+ KB
None
The first 5 rows
employee_id first_name last_name ... commission_pct manager_id department_id
0 100 Steven King ... NaN NaN 90.0
1 101 Neena Kochhar ... NaN 100.0 90.0
2 102 Lex De Haan ... NaN 100.0 90.0
3 103 Alexander Hunold ... NaN 102.0 60.0
4 104 Bruce Ernst ... NaN 103.0 60.0

[5 rows x 11 columns]
With selected columns only
employee_id first_name last_name hire_date job_id
0 100 Steven King 2003-06-17 AD_PRES
1 101 Neena Kochhar 2005-09-21 AD_VP
2 102 Lex De Haan 2001-01-13 AD_VP
3 103 Alexander Hunold 2006-01-03 IT_PROG
4 104 Bruce Ernst 2007-05-21 IT_PROG


Once you are familiar with this method, you can use numpy, matplotlib and a host of other python libraries with the dataset.

This article by Yuli Vasiliev is a good starter.


Categories: DBA Blogs

Using the SESSION_LONGOPS view with DBMS_APPLICATION_INFO

Sat, 2021-04-03 10:38

 I have, in the past, demonstrated DBMS_APPLICATION_INFO   here    and   here.

Also, I have demonstrated how V$SESSION_LONGOPS can be used (and misunderstood !!) 


Here is a demo of how you can use both features to enable monitoring of a (batch ?) job run.

The job is modified to include calls to DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS.

The DBA can then monitor the job from V$SESSION_LONGOPS.


The job is to update a PRODUCTS table with new PRODUCT_IDs, after some validation.  Since, this can take some time to run, the DBA wants to monitor it via V$SESSION_LONGOPS.


Here is the PL/SQL code for the batch (job) run :(look up the documentaion on DBMS_APPLICATION_INFO in the PL/SQL Developers Guide for our version -- I know that is available since at least 10.2, although this demonstation below is in 19c)



DECLARE
rindex BINARY_INTEGER;
slno BINARY_INTEGER;
target_count number;
updated_count number;

product_row products%rowtype;
check_flag varchar2(8);

BEGIN
rindex := dbms_application_info.set_session_longops_nohint;
updated_count := 0;
select num_rows into target_count --- this is an approximation based on last updated statistics on the table
from user_tables
where table_name = 'PRODUCTS';

-- begin the updates here
for product_row in (select product_id, product_name from products)
loop

-- validate_for_update is a function that checks if this product should get a new PRODUCT_ID
select validate_for_update(product_row.product_id, product_row.product_name) into check_flag from dual ;

if check_flag='OK' then
update products set product_id = product_id+10000 where product_id=product_row.product_id;
insert into update_run_log values (product_row.product_id,systimestamp);
updated_count := updated_count+1;
dbms_application_info.set_session_longops(rindex,
slno,
op_name=>'New_Product_IDs',
target=>0, -- default, not used by me
context=>0, -- default, not used by me
sofar=>updated_count,
totalwork=>target_count,
target_desc=>'Table : PRODUCTS',
units=>'rows');
else
null;
end if;

end loop;
END;
/


commit;



And here is the DBA monitoring the job as it is running :



23:22:15 SQL> l
1 select sid, opname, target_desc, sofar, totalwork, units, start_time, elapsed_seconds, time_remaining
2 from v$session_longops
3* where username = 'HEMANT'
23:22:15 SQL> /

no rows selected

23:22:16 SQL>
23:22:21 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 3 500 rows 03-APR-21 23:22:18 2 331

23:22:22 SQL>
23:22:30 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 12 500 rows 03-APR-21 23:22:18 12 488

23:22:31 SQL>
23:22:44 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 26 500 rows 03-APR-21 23:22:18 26 474

23:22:45 SQL>
23:23:26 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 67 500 rows 03-APR-21 23:22:18 69 446

23:23:27 SQL>
23:26:28 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 245 500 rows 03-APR-21 23:22:18 251 261

23:26:30 SQL>
23:28:31 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 364 500 rows 03-APR-21 23:22:18 373 139

23:28:32 SQL>
23:29:24 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 416 500 rows 03-APR-21 23:22:18 426 86

23:29:25 SQL>
23:30:16 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 467 500 rows 03-APR-21 23:22:18 478 34

23:30:17 SQL>
23:30:43 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 493 500 rows 03-APR-21 23:22:18 505 7

23:30:44 SQL>
23:30:48 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 497 500 rows 03-APR-21 23:22:18 509 3

23:30:49 SQL>
23:30:50 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 500 500 rows 03-APR-21 23:22:18 512 0

23:30:51 SQL>
23:31:04 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 500 500 rows 03-APR-21 23:22:18 512 0

23:31:05 SQL>


(In this demo, all 500 rows actually do pass the "VALIDATE_FOR_UPDATE" check) 
 Normally, we expect V$SESSION_LONGOPS to be populated by Long Running Operations (I specify Operations because of the "misinterpretation" that I demonstrate here).  Operations that are Full Table Scans  or Parallel Execution or RMAN Operations --- all of these are cases where the view is populated "automatically" by Oracle.

However, as I have demonstrated above, you can use your own code to populate this view so that it can be used to monitor Long Running "Operations" that you have defined (or, rather, worked with the Developers to define if you are the DBA).



Categories: DBA Blogs

Patching -- opatch and datapatch in Oracle vs a single executable in SQL Server

Fri, 2021-03-19 09:21

 In the Oracle universe, when applying a Release Update patch, the DBA has to run "opatch" to patch the binaries and library files but also has to run a separate "datapatch" to update the data dictionary with SQL "Apply" Actions in each database.

In what seems to be a contrast, SQL Server patching requires only execution of the Patch exe file.  Does that mean that no SQL "Apply" Actions are required ?  See how it is done in SQL Server in my other blog post.



Categories: DBA Blogs

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

Pages