RMAN: Archivelogs, the UNTIL clause – expectations vs. reality

Sorry for the lengthy title, I just couldn’t put it shorter. So let’s start the story. Yesterday I was implementing a desaster recovery solution. The solution consists of a database copy, a process that transfers archivelogs from the original database and a process that applies the archivelogs to the copy. Dataguard would do that much better, but the system is running on Standard Edition 2, so no Dataguard at all. The solution worked quite well until I wanted to introduce an apply delay yesterday. That would mean, I still ship archivelogs on a regular basis but apply them only until a specific point in time. Assuming all archivelogs are present at the DR site, that is quite easy using RMAN:

RMAN> recover database until time 'sysdate-%s/1440';

The “%s” is filled with the desired apply delay in minutes. The fun starts with the housekeeping of the archivelogs. My asumption was, I can use the UNTIL clause to get rid of all the archivelogs, that are no longer required for recovery. Let’s first check the available archivelogs:

QL> select sequence#, first_time, next_time, first_change#, next_change# from v$archived_log where status='A' and first_change# >= 8582498175253 order by 1;

                 SEQUENCE# FIRST_TIME          NEXT_TIME                        FIRST_CHANGE#               NEXT_CHANGE#
-------------------------- ------------------- ------------------- -------------------------- --------------------------
                     16865 2024-04-08 10:26:16 2024-04-08 10:33:33              8582498176626              8582498177307

Just one archivelog. Let’s further asume, I already recovered until 10:30. So the archivelog is still needed for recovery since it ranges from 10:26 till 10:33. Now I run the RMAN DELETE command to remove the archivelogs, that are no longer needed. That should be none in this case. Of cause I use NOPROMPT FORCE because the command will be run by an automatic script.

MAN> delete  force noprompt archivelog until time "to_date('2024-04-08 10:30:00', 'yyyy-mm-dd hh24:mi:ss')";

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2 device type=DISK
archived log file name=/u01/app/oracle/standby/o1_mf_1_16865_m17byx6b_.arc thread=1 sequence=16865

But see, the archivelog was removed even though its next time is beyond my given timestamp. So I crosscheck that using a SCN in between. Of cause I made the archivelog available again for testing this testing purpose.

RMAN> delete  archivelog until scn 8582498177000;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2 device type=DISK
archived log file name=/u01/app/oracle/standby/o1_mf_1_16865_m17byx6b_.arc thread=1 sequence=16865

And, surprise surprise, it also removed my archivelog. So what shall I do? Maybe reading the docs sheds some light on this issue.

Oracle Backup and Recovery Reference – archivelogRecordSpecifier

The table clearly states, that UNTIL SCN and UNTIL TIME both should use the FIRST_TIME to identify the archivelogs. If the FIRST_TIME “is less or equal” to the given timestamp, the archivelog falls into the range. So it is expected behaviour. The logic is good for restoring archivelogs because I will get all redo information that fall into the range. It is not as good for removing archivelogs because I will loose a little more redo information than specified.

What do we learn from that? Asumptions are a good starting point. Reading the docs is the next logical step. But if you want to make sure, things work as you want it to be, test it.

Update 2024-04-10

Changed the last paragraphs as I got really confused during testing and changed my understanding of the docs and the real behaviour several times. But that’s one thing, why writing is a good thing. You start thinking more deeply about things and do not stop thinking about it for some time.

Duplicating a PDB with Read Only tablespaces

I am a big fan of Oracle Recovery Manager (RMAN) since the very beginning. It evolved over time and makes the life of a DBA much easier, as long as you know the concepts. Todays blog post is about duplicating a database. Initially I wanted to perform a schema point in time restore which is not natively supported by RMAN. So let’s outline the system I have.

We are talking about an Oracle Database 19c, 19.18 to be precise. It is running the multitenant architecture and has 3 pluggable databases. I am using Oracle Managed Files (OMF) because this makes file handling easier for me. In one of these PDBs are several schemas spread over a couple of tablespaces. This is the tablespace layout:

SYS @ ORCL19:CDB$ROOT:>select con_id, tablespace_name, status from cdb_tablespaces;

    CON_ID TABLESPACE_NAME                STATUS
---------- ------------------------------ ---------
         3 SYSTEM                         ONLINE
         3 SYSAUX                         ONLINE
         3 UNDOTBS1                       ONLINE
         3 TEMP                           ONLINE
         3 USERS                          READ ONLY     <<<<<<<<
         3 TBS_DATA                       READ ONLY
         3 TBS_LOB                        READ ONLY
         3 TBS_ORG_UTPLSQL                ONLINE
         1 SYSTEM                         ONLINE
         1 SYSAUX                         ONLINE
         1 UNDOTBS1                       ONLINE
         1 TEMP                           ONLINE
         1 USERS                          ONLINE
         4 SYSTEM                         ONLINE
         4 SYSAUX                         ONLINE
         4 UNDOTBS1                       ONLINE
         4 TEMP                           ONLINE
         4 USERS                          ONLINE
         4 DATA                           ONLINE
         4 TBS_DATA                       READ ONLY
         4 TBS_LOB                        READ ONLY
         5 SYSTEM                         ONLINE
         5 SYSAUX                         ONLINE
         5 UNDOTBS1                       ONLINE
         5 TEMP                           ONLINE
         5 USERS                          ONLINE

I wanted to restore a specific tablespace, USERS in CON_ID 3, to some point in the past. Since I cannot use an automatic auxiliary instance for this, I created my own auxiliary instance. Not that complex, just create a parameterfile.

[oracle@vm160 ~]$ cat $ORACLE_HOME/dbs/initaux.ora
db_name=aux
db_block_size=8192
db_create_file_dest='/u01/app/oracle/oradata'
db_recovery_file_dest='/u01/app/oracle/fra'
db_recovery_file_dest_size=10g
sga_target=1500M
enable_pluggable_database=true

Having that, I started RMAN, connected to my target and my auxiliary database and started the duplicate. The DUPLICATE command allows us to include or exclude PDBs and/or skip specific tablespaces. Since I wanted to only restore one tablespace of one PDB, I combined these features. Furthermore I used the “NOOPEN” option to prevent the auxiliary instance from opening automatically after the duplication.

[oracle@vm160 ~]$ rman

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Mar 21 08:21:41 2024
Version 19.18.0.0.0

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

RMAN> connect target sys/********@vm160/orcl19.support.robotron.de

connected to target database: ORCL19 (DBID=422142402)

RMAN> connect auxiliary /

connected to auxiliary database (not started)

RMAN> startup clone nomount

Oracle instance started

Total System Global Area    1577055136 bytes

Fixed Size                     9163680 bytes
Variable Size                385875968 bytes
Database Buffers            1174405120 bytes
Redo Buffers                   7610368 bytes

RMAN> sql clone 'create spfile from pfile';

using target database control file instead of recovery catalog
sql statement: create spfile from pfile

RMAN> startup clone nomount force

Oracle instance started

Total System Global Area    1577055136 bytes

Fixed Size                     9163680 bytes
Variable Size                385875968 bytes
Database Buffers            1174405120 bytes
Redo Buffers                   7610368 bytes

RMAN> duplicate database to aux pluggable database pdb1 skip tablespace pdb1:TBS_DATA,pdb1:TBS_LOB,pdb1:TBS_ORG_UTPLSQL until time 'sysdate-1' noopen;

Starting Duplicate Db at 2024-03-21 08:22:30
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=179 device type=DISK
duplicating Online logs to Oracle Managed File (OMF) location
duplicating Datafiles to Oracle Managed File (OMF) location

contents of Memory Script:
{
   set until scn  42779928;
   sql clone "alter system set  control_files =
  ''/u01/app/oracle/oradata/AUX/controlfile/o1_mf_lzqr1q5x_.ctl'', ''/u01/app/oracle/fra/AUX/controlfile/o1_mf_lzqr1q60_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''ORCL19'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''aux'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

executing command: SET until clause

sql statement: alter system set  control_files =   ''/u01/app/oracle/oradata/AUX/controlfile/o1_mf_lzqr1q5x_.ctl'', ''/u01/app/oracle/fra/AUX/controlfile/o1_mf_lzqr1q60_.ctl'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''ORCL19'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''aux'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    1577055136 bytes

Fixed Size                     9163680 bytes
Variable Size                385875968 bytes
Database Buffers            1174405120 bytes
Redo Buffers                   7610368 bytes

Starting restore at 2024-03-21 08:22:53
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=4 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fra/ORCL19/autobackup/2024_03_20/o1_mf_s_1164074408_lznf8rgr_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fra/ORCL19/autobackup/2024_03_20/o1_mf_s_1164074408_lznf8rgr_.bkp tag=TAG20240320T020008
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/AUX/controlfile/o1_mf_lzqr1q5x_.ctl
output file name=/u01/app/oracle/fra/AUX/controlfile/o1_mf_lzqr1q60_.ctl
Finished restore at 2024-03-21 08:22:55

database mounted
Skipping pluggable database ORCL19PDB2
Skipping pluggable database PDB2
Checking that duplicated tablespaces are self-contained
Automatically adding tablespace SYSTEM
Automatically adding tablespace SYSAUX
Automatically adding tablespace PDB$SEED:SYSTEM
Automatically adding tablespace PDB$SEED:SYSAUX
Automatically adding tablespace UNDOTBS1
Skipping tablespace USERS
Skipping tablespace PDB1:TBS_ORG_UTPLSQL
Skipping tablespace PDB1:TBS_LOB
Skipping tablespace PDB1:TBS_DATA

contents of Memory Script:
{
   set until scn  42779928;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   set newname for clone datafile  8 to new;
   set newname for clone datafile  19 to new;
   set newname for clone datafile  20 to new;
   set newname for clone datafile  21 to new;
   set newname for clone datafile  22 to new;
   restore
   clone database
   skip forever tablespace  "USERS",
 "ORCL19PDB2":"USERS",
 "ORCL19PDB2":"UNDOTBS1",
 "ORCL19PDB2":"SYSTEM",
 "ORCL19PDB2":"SYSAUX",
 "PDB2":"USERS",
 "PDB2":"UNDOTBS1",
 "PDB2":"TBS_LOB",
 "PDB2":"TBS_DATA",
 "PDB2":"SYSTEM",
 "PDB2":"SYSAUX",
 "PDB2":"DATA",
 "PDB1":"TBS_ORG_UTPLSQL",
 "PDB1":"TBS_LOB",
 "PDB1":"TBS_DATA"   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2024-03-21 08:23:13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00019 to /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00020 to /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00021 to /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00022 to /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20240317_data_gh2lsmoo_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20240317_data_gh2lsmoo_1_1 tag=TAG20240317T010008
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/AUX/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/AUX/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/AUX/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20240317_data_gj2lsmq0_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20240317_data_gj2lsmq0_1_1 tag=TAG20240317T010008
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20240317_data_gl2lsmqn_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20240317_data_gl2lsmqn_1_1 tag=TAG20240317T010008
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2024-03-21 08:23:51

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=14 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/datafile/o1_mf_system_lzqr3jxq_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/datafile/o1_mf_sysaux_lzqr3jwy_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=16 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/datafile/o1_mf_undotbs1_lzqr3jy4_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=17 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_system_lzqr400z_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=18 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_sysaux_lzqr4014_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=19 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_undotbs1_lzqr401g_.dbf
datafile 19 switched to datafile copy
input datafile copy RECID=20 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_system_lzqr31r4_.dbf
datafile 20 switched to datafile copy
input datafile copy RECID=21 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_lzqr31rf_.dbf
datafile 21 switched to datafile copy
input datafile copy RECID=22 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_lzqr31rq_.dbf
datafile 22 switched to datafile copy
input datafile copy RECID=23 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqr31r2_.dbf

contents of Memory Script:
{
   set until time  "to_date('2024/03/20 08:22:30', 'YYYY/MM/DD HH24:MI:SS')";
   recover
   clone database
   skip forever tablespace  "USERS",
 "ORCL19PDB2":"USERS",
 "ORCL19PDB2":"UNDOTBS1",
 "ORCL19PDB2":"SYSTEM",
 "ORCL19PDB2":"SYSAUX",
 "PDB2":"USERS",
 "PDB2":"UNDOTBS1",
 "PDB2":"TBS_LOB",
 "PDB2":"TBS_DATA",
 "PDB2":"SYSTEM",
 "PDB2":"SYSAUX",
 "PDB2":"DATA",
 "PDB1":"TBS_ORG_UTPLSQL",
 "PDB1":"TBS_LOB",
 "PDB1":"TBS_DATA"    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 2024-03-21 08:23:51
using channel ORA_AUX_DISK_1
datafile 22 not processed because file is read-only

Executing: alter database datafile 7 offline drop
Executing: alter database datafile 29, 30, 33 offline drop
Executing: alter database datafile 24, 25, 26, 27, 28, 31, 32 offline drop
Executing: alter database datafile 15, 16, 17, 23 offline drop
starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=234
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20240317_arch_gm2lsmqq_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20240317_arch_gm2lsmqq_1_1 tag=TAG20240317T010114
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_234_lzqr4b89_.arc thread=1 sequence=234
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_234_lzqr4b89_.arc RECID=726 STAMP=1164183834
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=235
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20240317_arch_go2lsq94_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20240317_arch_go2lsq94_1_1 tag=TAG20240317T020004
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_235_lzqr4ck8_.arc thread=1 sequence=235
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_235_lzqr4ck8_.arc RECID=727 STAMP=1164183835
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=236
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=237
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=238
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20240318_arch_gq2lvel5_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20240318_arch_gq2lvel5_1_1 tag=TAG20240318T020005
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_236_lzqr4gz1_.arc thread=1 sequence=236
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_236_lzqr4gz1_.arc RECID=729 STAMP=1164183840
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_237_lzqr4gys_.arc thread=1 sequence=237
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_237_lzqr4gys_.arc RECID=730 STAMP=1164183840
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_238_lzqr4h00_.arc thread=1 sequence=238
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_238_lzqr4h00_.arc RECID=728 STAMP=1164183840
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=239
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=240
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=241
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=242
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20240319_arch_gs2m2315_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20240319_arch_gs2m2315_1_1 tag=TAG20240319T020005
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_239_lzqr55dj_.arc thread=1 sequence=239
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_239_lzqr55dj_.arc RECID=733 STAMP=1164183862
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_240_lzqr55f6_.arc thread=1 sequence=240
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_240_lzqr55f6_.arc RECID=731 STAMP=1164183861
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_241_lzqr55d4_.arc thread=1 sequence=241
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_241_lzqr55d4_.arc RECID=734 STAMP=1164183862
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_242_lzqr55ds_.arc thread=1 sequence=242
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_242_lzqr55ds_.arc RECID=732 STAMP=1164183861
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=243
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=244
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20240320_arch_gu2m4nd7_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20240320_arch_gu2m4nd7_1_1 tag=TAG20240320T020007
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_243_lzqr5nvb_.arc thread=1 sequence=243
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_243_lzqr5nvb_.arc RECID=736 STAMP=1164183878
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_244_lzqr5nvn_.arc thread=1 sequence=244
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_244_lzqr5nvn_.arc RECID=735 STAMP=1164183877
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=245
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20240321_arch_h02m7bp5_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20240321_arch_h02m7bp5_1_1 tag=TAG20240321T020005
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_245_lzqr6354_.arc thread=1 sequence=245
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_245_lzqr6354_.arc RECID=737 STAMP=1164183891
media recovery complete, elapsed time: 00:00:02
Finished recover at 2024-03-21 08:24:55
Oracle instance started

Total System Global Area    1577055136 bytes

Fixed Size                     9163680 bytes
Variable Size                385875968 bytes
Database Buffers            1174405120 bytes
Redo Buffers                   7610368 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''AUX'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
}
executing Memory Script

sql statement: alter system set  db_name =  ''AUX'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile
Oracle instance started

Total System Global Area    1577055136 bytes

Fixed Size                     9163680 bytes
Variable Size                385875968 bytes
Database Buffers            1174405120 bytes
Redo Buffers                   7610368 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP     1  SIZE 200 M ,
  GROUP     2  SIZE 200 M ,
  GROUP     3  SIZE 200 M
 DATAFILE
  '/u01/app/oracle/oradata/AUX/datafile/o1_mf_system_lzqr3jxq_.dbf',
  '/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_system_lzqr400z_.dbf',
  '/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_system_lzqr31r4_.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   set newname for clone tempfile  2 to new;
   set newname for clone tempfile  3 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/AUX/datafile/o1_mf_sysaux_lzqr3jwy_.dbf",
 "/u01/app/oracle/oradata/AUX/datafile/o1_mf_undotbs1_lzqr3jy4_.dbf",
 "/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_sysaux_lzqr4014_.dbf",
 "/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_undotbs1_lzqr401g_.dbf",
 "/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_lzqr31rf_.dbf",
 "/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_lzqr31rq_.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/AUX/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 2 to /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_temp_%u_.tmp in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/datafile/o1_mf_sysaux_lzqr3jwy_.dbf RECID=1 STAMP=1164183915
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/datafile/o1_mf_undotbs1_lzqr3jy4_.dbf RECID=2 STAMP=1164183915
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_sysaux_lzqr4014_.dbf RECID=3 STAMP=1164183915
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_undotbs1_lzqr401g_.dbf RECID=4 STAMP=1164183915
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_lzqr31rf_.dbf RECID=5 STAMP=1164183915
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_lzqr31rq_.dbf RECID=6 STAMP=1164183915

datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=1164183915 file name=/u01/app/oracle/oradata/AUX/datafile/o1_mf_sysaux_lzqr3jwy_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=1164183915 file name=/u01/app/oracle/oradata/AUX/datafile/o1_mf_undotbs1_lzqr3jy4_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=3 STAMP=1164183915 file name=/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_sysaux_lzqr4014_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=4 STAMP=1164183915 file name=/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_undotbs1_lzqr401g_.dbf
datafile 20 switched to datafile copy
input datafile copy RECID=5 STAMP=1164183915 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_lzqr31rf_.dbf
datafile 21 switched to datafile copy
input datafile copy RECID=6 STAMP=1164183915 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_lzqr31rq_.dbf
Leaving database unopened, as requested
Finished Duplicate Db at 2024-03-21 08:25:15

So far, so good. See line 180, my datafile got restored. It was also switched to after restoration, see line 229. But then, in line 260, it was skipped for the recovery part. That sounds not like a big deal since the file / the tablespace is read only. But at the end you see, that neither the CREATE CONTROLFILE nor the CATALOG and SWITCH part contains my datafile 22. Strange. Let’s see, what we have at this point.

SYS @ AUX:CDB$ROOT:>select file#, name from v$datafile;

     FILE# NAME
---------- ------------------------------------------------------------------------------------------------------------------------
         1 /u01/app/oracle/oradata/AUX/datafile/o1_mf_system_lzqr3jxq_.dbf
         3 /u01/app/oracle/oradata/AUX/datafile/o1_mf_sysaux_lzqr3jwy_.dbf
         4 /u01/app/oracle/oradata/AUX/datafile/o1_mf_undotbs1_lzqr3jy4_.dbf
         5 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_system_lzqr400z_.dbf
         6 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_sysaux_lzqr4014_.dbf
         8 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_undotbs1_lzqr401g_.dbf
        19 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_system_lzqr31r4_.dbf
        20 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_lzqr31rf_.dbf
        21 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_lzqr31rq_.dbf

9 rows selected.

SYS @ AUX:CDB$ROOT:>show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 _###_UNKNOWN_PDB_#_3           MOUNTED
SYS @ AUX:CDB$ROOT:>select status from v$instance;

STATUS
------------
MOUNTED

As you can see, no file 22 is there. So let’s go ahead and open the database read only just to check things.

SYS @ AUX:CDB$ROOT:>alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16008: indeterminate control file checkpoint

SYS @ AUX:CDB$ROOT:>!oerr ora 16008
16008, 00000, "indeterminate control file checkpoint"
// *Cause:  The control file for the database being opened for read-only access
//          was created using the CREATE CONTROLFILE statement. Therefore, a
//          control file checkpoint could not be calculated and the database
//          could not be opened for read-only access.
// *Action: First, open the database for read/write access. This creates
//          a valid control file checkpoint. Then, reopen the database for
//          read-only access.

Ok, that makes sense, RMAN did create a new controlfile. I’ll open the database read/write then and check again.

SYS @ AUX:CDB$ROOT:>alter database open resetlogs;

Database altered.

SYS @ AUX:CDB$ROOT:>show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
         5 ORCL19PDB2                     MOUNTED
         
SYS @ AUX:CDB$ROOT:>select file#, name from v$datafile;

     FILE# NAME
---------- ------------------------------------------------------------------------------------------------------------------------
         1 /u01/app/oracle/oradata/AUX/datafile/o1_mf_system_lzqr3jxq_.dbf
         3 /u01/app/oracle/oradata/AUX/datafile/o1_mf_sysaux_lzqr3jwy_.dbf
         4 /u01/app/oracle/oradata/AUX/datafile/o1_mf_undotbs1_lzqr3jy4_.dbf
         5 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_system_lzqr400z_.dbf
         6 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_sysaux_lzqr4014_.dbf
         7 /u01/app/oracle/product/db/19/dbs/MISSING00007
         8 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_undotbs1_lzqr401g_.dbf
        19 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_system_lzqr31r4_.dbf
        20 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_lzqr31rf_.dbf
        21 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_lzqr31rq_.dbf
        22 /u01/app/oracle/product/db/19/dbs/MISSING00022
        29 /u01/app/oracle/product/db/19/dbs/MISSING00029
        30 /u01/app/oracle/product/db/19/dbs/MISSING00030
        33 /u01/app/oracle/product/db/19/dbs/MISSING00033

14 rows selected.

As you can see, we have much more information after opening the database. This is because we now have access to the data dictionary too, not only to the controlfile. So we can see, that the database knows about file 22, but not where to find it. But I do know, it was written in the RMAN output and can also be derived from the paths of the other datafiles.

SYS @ AUX:CDB$ROOT:>!ls -l /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile
total 3490876
-rw-r-----. 1 oracle dba  555753472 Mar 21 08:29 o1_mf_sysaux_lzqr31rf_.dbf
-rw-r-----. 1 oracle dba  713039872 Mar 21 08:29 o1_mf_system_lzqr31r4_.dbf
-rw-r-----. 1 oracle dba  104865792 Mar 21 08:29 o1_mf_temp_lzqrh54t_.tmp
-rw-r-----. 1 oracle dba  157294592 Mar 21 08:29 o1_mf_undotbs1_lzqr31rq_.dbf
-rw-r-----. 1 oracle dba 1073750016 Mar 21 08:23 o1_mf_users_lzqr31r2_.dbf

The file is there, so let’s just use it. The database allows us to rename files, that should do the trick. The file itself is consistent since it was read/only in the source database.

SYS @ AUX:CDB$ROOT:>alter database rename file '/u01/app/oracle/product/db/19/dbs/MISSING00022' to '/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqr31r2_.dbf';

Database altered.

SYS @ AUX:CDB$ROOT:>alter pluggable database pdb1 open ;

Pluggable database altered.

SYS @ AUX:CDB$ROOT:>alter session set container=pdb1;

Session altered.

SYS @ AUX:CDB$ROOT:>select * from hr.employees;
select * from hr.employees
                 *
ERROR at line 1:
ORA-00376: file 22 cannot be read at this time
ORA-01110: data file 22: '/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqr31r2_.dbf'


SYS @ AUX:CDB$ROOT:>alter database datafile 22 online;
alter database datafile 22 online
*
ERROR at line 1:
ORA-01190: control file or data file 22 is from before the last RESETLOGS
ORA-01110: data file 22: '/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqr31r2_.dbf'

Obviously I cannot use the restored file at all. But why is that? To figure this out, I dropped the auxiliary database and did another DUPLICATE, this time ommiting the NOOPEN option, just to see, what RMAN will do. I’ll only paste the relevant parts of this operation for the sake of clarity.

RMAN> duplicate database to aux pluggable database pdb1 skip tablespace pdb1:TBS_ORG_UTPLSQL until time 'sysdate-1' ;

Starting Duplicate Db at 2024-03-21 08:37:59

[...]

channel ORA_AUX_DISK_1: restoring datafile 00022 to /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_%u_.dbf

[...]

Starting recover at 2024-03-21 08:39:22
using channel ORA_AUX_DISK_1
datafile 22 not processed because file is read-only

[...]

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP     1  SIZE 200 M ,
  GROUP     2  SIZE 200 M ,
  GROUP     3  SIZE 200 M
 DATAFILE
  '/u01/app/oracle/oradata/AUX/datafile/o1_mf_system_lzqs0qjg_.dbf',
  '/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_system_lzqs16m6_.dbf',
  '/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_system_lzqrzyg9_.dbf'
 CHARACTER SET AL32UTF8

[...]

contents of Memory Script:
{
   sql clone 'alter system set "_system_trig_enabled"=FALSE';
   Alter clone database open resetlogs;
   sql clone 'alter system reset "_system_trig_enabled"';
}
executing Memory Script

sql statement: alter system set "_system_trig_enabled"=FALSE

database opened

sql statement: alter system reset "_system_trig_enabled"
Executing: drop pluggable database "ORCL19PDB2" including datafiles
Executing: drop pluggable database "PDB2" including datafiles

contents of Memory Script:
{
   sql clone "alter pluggable database all open";
}
executing Memory Script

sql statement: alter pluggable database all open

contents of Memory Script:
{
   catalog clone datafilecopy  "/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqrzyg7_.dbf",
 "/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_data_lzqrzygh_.dbf",
 "/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_lob_lzqrzyg4_.dbf";
   switch clone datafile  22 to datafilecopy
 "/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqrzyg7_.dbf";
   switch clone datafile  29 to datafilecopy
 "/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_data_lzqrzygh_.dbf";
   switch clone datafile  30 to datafilecopy
 "/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_lob_lzqrzyg4_.dbf";
   #online the readonly tablespace
   sql clone 'PDB1' "alter tablespace
 USERS online";
   #online the readonly tablespace
   sql clone 'PDB1' "alter tablespace
 TBS_LOB online";
   #online the readonly tablespace
   sql clone 'PDB1' "alter tablespace
 TBS_DATA online";
}
executing Memory Script

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqrzyg7_.dbf RECID=7 STAMP=1164184865
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_data_lzqrzygh_.dbf RECID=8 STAMP=1164184865
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_lob_lzqrzyg4_.dbf RECID=9 STAMP=1164184865

datafile 22 switched to datafile copy
input datafile copy RECID=7 STAMP=1164184865 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqrzyg7_.dbf

datafile 29 switched to datafile copy
input datafile copy RECID=8 STAMP=1164184865 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_data_lzqrzygh_.dbf

datafile 30 switched to datafile copy
input datafile copy RECID=9 STAMP=1164184865 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_lob_lzqrzyg4_.dbf

sql statement: alter tablespace  USERS online

sql statement: alter tablespace  TBS_LOB online

sql statement: alter tablespace  TBS_DATA online
Dropping offline and skipped tablespaces
Executing: alter database default tablespace system
Executing: drop tablespace "USERS" including contents cascade constraints
Executing: drop tablespace "TBS_ORG_UTPLSQL" including contents cascade constraints
Finished Duplicate Db at 2024-03-21 08:41:25

As you see, the restore and recover work the same way as with the NOOPEN option, no surprise here, I highlighted those lines. But then RMAN opens the database and after opening the database, it catalogs all the remaining files and switches the location of these files, see lines 61-64 and 82-83. The SWITCH command does something else than just renaming the file. And just to proof it works, here we go.

SYS @ AUX:CDB$ROOT:>select status from v$instance;

STATUS
------------
OPEN

SYS @ AUX:CDB$ROOT:>col name format a120
SYS @ AUX:CDB$ROOT:>set lines 200
SYS @ AUX:CDB$ROOT:>set pages 200
SYS @ AUX:CDB$ROOT:>select file#, name from v$datafile;

     FILE# NAME
---------- ------------------------------------------------------------------------------------------------------------------------
         1 /u01/app/oracle/oradata/AUX/datafile/o1_mf_system_lzqs0qjg_.dbf
         3 /u01/app/oracle/oradata/AUX/datafile/o1_mf_sysaux_lzqs0qjb_.dbf
         4 /u01/app/oracle/oradata/AUX/datafile/o1_mf_undotbs1_lzqs0qjk_.dbf
         5 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_system_lzqs16m6_.dbf
         6 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_sysaux_lzqs16mb_.dbf
         8 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_undotbs1_lzqs16mf_.dbf
        19 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_system_lzqrzyg9_.dbf
        20 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_lzqrzygc_.dbf
        21 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_lzqrzygf_.dbf
        22 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqrzyg7_.dbf
        29 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_data_lzqrzygh_.dbf
        30 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_lob_lzqrzyg4_.dbf

12 rows selected.

SYS @ AUX:CDB$ROOT:>alter session set container=pdb1;

Session altered.

SYS @ AUX:CDB$ROOT:>select count(*) from hr.employees;

  COUNT(*)
----------
       107

But can we use the SWITCH trick also for the NOOPEN case? I dropped the auxiliary database again and did another duplicate with the NOOPEN option as in the first case. After this duplication, the situation is again like this.

YS @ AUX:CDB$ROOT:>select file#, name from v$datafile;

     FILE# NAME
---------- ------------------------------------------------------------------------------------------------------------------------
         1 /u01/app/oracle/oradata/AUX/datafile/o1_mf_system_lzqskm2l_.dbf
         3 /u01/app/oracle/oradata/AUX/datafile/o1_mf_sysaux_lzqskm2f_.dbf
         4 /u01/app/oracle/oradata/AUX/datafile/o1_mf_undotbs1_lzqskm2p_.dbf
         5 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_system_lzqsl251_.dbf
         6 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_sysaux_lzqsl256_.dbf
         8 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_undotbs1_lzqsl259_.dbf
        19 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_system_lzqsk40g_.dbf
        20 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_lzqsk40j_.dbf
        21 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_lzqsk40k_.dbf

9 rows selected.

SYS @ AUX:CDB$ROOT:>!ls -l /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/
total 2441260
-rw-r-----. 1 oracle dba  555753472 Mar 21 08:49 o1_mf_sysaux_lzqsk40j_.dbf
-rw-r-----. 1 oracle dba  713039872 Mar 21 08:49 o1_mf_system_lzqsk40g_.dbf
-rw-r-----. 1 oracle dba  157294592 Mar 21 08:49 o1_mf_undotbs1_lzqsk40k_.dbf
-rw-r-----. 1 oracle dba 1073750016 Mar 21 08:47 o1_mf_users_lzqsk40b_.dbf

No file 22 in the views, but at least it got restored again. So let’s try to open the auxiliary database again and use the SWITCH command this time. Note, that the command needs to put into a RUN-block.

[oracle@vm160 ~]$ export ORACLE_SID=aux
[oracle@vm160 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Mar 21 09:59:32 2024
Version 19.18.0.0.0

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

connected to target database: AUX (DBID=2017441707, not open)

RMAN> alter database open resetlogs;

Statement processed

RMAN> run {
2> catalog datafilecopy  "/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqsk40b_.dbf";
3> switch datafile 22 to datafilecopy "/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqsk40b_.dbf";
4> }

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqsk40b_.dbf RECID=7 STAMP=1164189777

datafile 22 switched to datafile copy
input datafile copy RECID=7 STAMP=1164189777 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqsk40b_.dbf

RMAN> sql "alter pluggable database pdb1 open";

sql statement: alter pluggable database pdb1 open

RMAN> sql 'PDB1' "alter tablespace users online";

sql statement: alter tablespace users online

Looks promising so far. But am I able to query some data from this file now?

[oracle@vm160 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 21 10:04:55 2024
Version 19.18.0.0.0

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


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

SYS @ AUX:CDB$ROOT:>alter session set container=pdb1;

Session altered.

SYS @ AUX:CDB$ROOT:>select count(*) from hr.employees;

  COUNT(*)
----------
       107

Yes, I can! Finally! So what did I learn? RMAN duplication skips read only files during the recover operation which is totally fine. It adds those files *after* opening the database. If I open the database myself, I have to find the restored files myself and use the RMAN SWITCH command to make them part of the restored database, An “alter database rename file” is not sufficient. Good to know.

ORA-1031 during Oracle Transportable Tablespace Import

I really do like the idea of transportable tablespaces in the Oracle database world for several reasons. It is a good feature for migrating data from one system to another, it allows to skip lenghty upgrade procedures, it can be used to change the OS platform and it can reduce downtime by utilizing incremental updates. In a current project we are using this feature to migrate to AWS. Please don’t ask why, in my opinion there are other cloud providers, that would fit better to host an Oracle database. But that’s only a side note. AWS provides Oracle databases as a managed service, somehow similar to Oracle Autonomous Database. You don’t get SYSDBA access to the database, there is only an administrative account with restricted privileges and access to some AWS-provided packages. Since the database in question is roughly 6.5TB in size, Datapump is not the first approach to migrate the data. But AWS allows to use the Transportable Tablespace feature as well. So we decided to go that way.

We followed these steps using the XTTS V4 scripts from Oracle to generate the initial backup and an incremental backup with the tablespaces in read only state. Finally we did a metadata and a tablspace export. During the final import at the AWS end, we run into an issue that I could also reproduce in the old on-prem world. Before starting a transportable tablespace import some prerequisites must be fullfilled. The TTS import creates all objects, that are in the transported tablespaces. Basically we talk about tables and indexes. Nothing else has segments in a tablespace. And nothing else is in the TTS export file. Not quite true, dependend objects are part of it, like grants, triggers etc. So we need to create all the users, that own segments in these tablespaces beforehand since their definition is not part of the TTS export. I did that with a simple “create user XYZ” statement. Also we might need to create all sequences since ID columns might use sequences as a default value. I simply took these from the full metadata export using these IMPDP parameters.

full=y
include=sequence

Then I started the TTS import. But unfortunately it did not succeed.

oracle@olca0046:~/xtts_ref> impdp parfile=imp_ts.par

Import: Release 19.0.0.0.0 - Production on Tue Jan 9 14:08:02 2024
Version 19.19.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
09-JAN-24 14:08:05.558: W-1 Startup took 0 seconds
09-JAN-24 14:08:10.276: W-1 Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
09-JAN-24 14:08:15.467: Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/********@olca0046:1523/xttback parfile=imp_ts.par
09-JAN-24 14:08:16.050: W-1 Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
09-JAN-24 14:08:20.865: W-1      Completed 1 PLUGTS_BLK objects in 4 seconds
09-JAN-24 14:08:20.865: W-1 Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
09-JAN-24 14:08:22.262: W-1      Completed 1 TYPE objects in 0 seconds
09-JAN-24 14:08:22.262: W-1 Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
09-JAN-24 14:08:24.473: W-1      Completed 1 PROCACT_INSTANCE objects in 1 seconds
09-JAN-24 14:08:24.473: W-1 Processing object type TRANSPORTABLE_EXPORT/TABLE
09-JAN-24 14:12:52.460: ORA-39083: Object type TABLE:"EC_NDB"."NDB_GETAG_CACHE" failed to create with error:
ORA-01031: insufficient privileges

CREATE TABLE "EC_NDB"."NDB_GETAG_CACHE" ("ID" NUMBER NOT NULL ENABLE, "KALK_ID" NUMBER, "DB_USER" VARCHAR2(50 BYTE) DEFAULT user NOT NULL ENABLE, "REQUEST_START" DATE, "REQUEST_END" DATE, "REQUEST" VARCHAR2(50 BYTE), "STATUS" VARCHAR2(10 BYTE), "ERROR" VARCHAR2(1000 BYTE), "RESPONSE" "SYS"."XMLTYPE" ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING STORAGE(SEG_FILE 34 SEG_BLOCK 87786 OBJNO_REUSE 67318 INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EC_TBS_SYS"  XMLTYPE COLUMN "RESPONSE" STORE AS SECUREFILE BINARY XML ( TABLESPACE "EC_TBS_LOB" ENABLE STORAGE IN ROW CHUNK 8192 CACHE  NOCOMPRESS  KEEP_DUPLICATES  STORAGE(SEG_FILE 27 SEG_BLOCK 1505 OBJNO_REUSE 67321 INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  INDEX ( INITRANS 2 MAXTRANS 255 STORAGE(SEG_FILE 27 SEG_BLOCK 1522 OBJNO_REUSE 67322 INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ) ALLOW NONSCHEMA DISALLOW ANYSCHEMA

09-JAN-24 14:12:52.460: ORA-39083: Object type TABLE:"EC_UK"."UK_XCH_XML_IMPORT" failed to create with error:
ORA-01031: insufficient privileges

Failing sql is:
CREATE TABLE "EC_UK"."UK_XCH_XML_IMPORT" ("ID" NUMBER(15,0) NOT NULL ENABLE, "XML_CONTENT" "SYS"."XMLTYPE" , "LOADINGDATE" DATE DEFAULT sysdate, "PROCESSDATE" DATE, "STATUS" NUMBER DEFAULT 100 NOT NULL ENABLE, "IFI_ID" NUMBER(15,0), "STATUSTEXT" VARCHAR2(1000 BYTE), "MSG_ID" VARCHAR2(100 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING STORAGE(SEG_FILE 34 SEG_BLOCK 87802 OBJNO_REUSE 67394 INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EC_TBS_SYS"  XMLTYPE COLUMN "XML_CONTENT" STORE AS SECUREFILE BINARY XML ( TABLESPACE "EC_TBS_LOB" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES  STORAGE(SEG_FILE 27 SEG_BLOCK 1545 OBJNO_REUSE 67395 INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  INDEX ( INITRANS 2 MAXTRANS 255 STORAGE(SEG_FILE 27 SEG_BLOCK 1562 OBJNO_REUSE 67396 INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ) ALLOW NONSCHEMA DISALLOW ANYSCHEMA
[...]

There were some more tables like these which failed to import. My first assumption was, all these tables have LOBs in it. But there are other tables with LOBs which were imported successfully. So that was not the root cause. On the other hand, all of these failing tables have columns of type XMLTYPE. So I investigated in that direction and found MOS note 2224183.1: “ORA-39083 / ORA-01031 ‘ insufficient privileges’ Error Using Data Pump to Import Data when the Schema Used to Import has the Necessary Privileges”. The note explains the behaviour. For importing normal tables it is sufficient to have “soft” privileges to create tables in any schema. But for these tables with XMLTYPE columns, it is neccessary to act as the owning user. Therefore the user must be granted the “CREATE TABLE” privilege. Having that, I did grant the privilege to the users in qestion and re-run the import again.

oracle@olca0046:~/xtts_ref> impdp parfile=imp_ts.par

Import: Release 19.0.0.0.0 - Production on Tue Jan 9 15:12:57 2024
Version 19.19.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "statistics=none" Location: Parameter File, ignored.
09-JAN-24 15:13:09.061: W-1 Startup took 1 seconds
09-JAN-24 15:13:17.169: W-1 Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
09-JAN-24 15:13:22.628: Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/********@olca0046:1523/xttback parfile=imp_ts.par
09-JAN-24 15:13:23.180: W-1 Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
09-JAN-24 15:13:31.874: W-1      Completed 1 PLUGTS_BLK objects in 8 seconds
09-JAN-24 15:13:31.874: W-1 Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
09-JAN-24 15:13:33.209: W-1      Completed 1 TYPE objects in 0 seconds
09-JAN-24 15:13:33.209: W-1 Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
09-JAN-24 15:13:35.363: W-1      Completed 1 PROCACT_INSTANCE objects in 1 seconds
09-JAN-24 15:13:35.363: W-1 Processing object type TRANSPORTABLE_EXPORT/TABLE
09-JAN-24 15:18:25.860: W-1      Completed 5756 TABLE objects in 289 seconds
09-JAN-24 15:18:25.860: W-1 Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
09-JAN-24 15:20:12.815: W-1      Completed 92252 OBJECT_GRANT objects in 106 seconds
09-JAN-24 15:20:12.815: W-1 Processing object type TRANSPORTABLE_EXPORT/GRANT/WITH_GRANT_OPTION/OBJECT_GRANT
09-JAN-24 15:20:14.143: W-1      Completed 61 OBJECT_GRANT objects in 0 seconds
09-JAN-24 15:20:14.143: W-1 Processing object type TRANSPORTABLE_EXPORT/GRANT/CROSS_SCHEMA/OBJECT_GRANT
09-JAN-24 15:20:15.902: W-1      Completed 455 OBJECT_GRANT objects in 1 seconds
09-JAN-24 15:20:15.902: W-1 Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
09-JAN-24 15:24:11.479: W-1      Completed 16611 INDEX objects in 234 seconds
09-JAN-24 15:24:11.479: W-1 Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
[...]

This time all the tables were imported successfully. There were other issues though, but that might make up another blog post.

Oracle RMAN: connect / as sysbackup

This post is about RMAN connects and role separation. During an Oracle Database installtion, one can choose which OS group should map to which role for database administration. There is the well-known “dba” group which can do nearly everything to the database and maps to the SYSDBA role. And there are a few more groups for key management, dataguard administration and also for backup/restore. The backup/restore group is todays topic. A customer wants to perform the database backups by an OS user other than “oracle” which is the primary DBA user. That shouldn’t be a big thing, because we have these roles I mentioned. But wait, at first we need to check if the database software is configured for role separtion. In the file “$ORACLE_HOME/rdbms/lib/config.c” we can find this information.

[oracle@vm160 lib]$ grep -e "^\.L.*string" config.c
.Ldba_string:     .string "dba"
.Loper_string:    .string "oper"
.Lasm_string:     .string ""
.Lbkp_string:     .string "backupdba"
.Ldgd_string:     .string "dgdba"
.Lkmt_string:     .string "kmdba"
.Lrac_string:     .string "racdba"

In this case we are good to go. If we find the same OS group for the different roles, we need to recompile the software. The basic steps are described in MOS Note 558478.1 and are roughly these:

• Stop everything that runs from this database home
• make a backup of $ORACLE_HOME/rdbms/lib/config.o
• change $ORACLE_HOME/rdbms/lib/config.c to map the roles to the desired OS groups
• re-create config.o: make -f ins_rdbms.mk config.o
• do a recompile/relink by running “make -f ins_rdbms.mk ioracle”
• bring everything up again

Of cause the OS groups should be present at that time. Mine look like this:

[oracle@vm160 ~]$ tail -7 /etc/group
oinstall:x:54321:oracle
dba:x:54322:oracle
oper:x:54323:oracle
backupdba:x:54324:oracle
dgdba:x:54325:oracle
kmdba:x:54326:oracle
racdba:x:54330:oracle

What we now need is an OS user, that shall be used for performing database backups in the future. This user must be part of the “backupdba” group for being able to connect as SYSBACKUP to the database but must not be in the “dba” group because that would allow a connection as SYSDBA which we do not want. Beside that, the user must be part of the “oinstall” group which is the group that owns the database software installation.

[root@vm160 ~]# useradd -g oinstall-G backupdba,oinstall orarman
[root@vm160 ~]# id orarman
uid=54322(orarman) gid=54321(oinstall) groups=54321(oinstall),54324(backupdba)

I also defined a password for the new user, you can provide SSH keys as well to allow logins. So first I check with SQL*Plus if the user can connect to the database as SYSBACKUP but not as SYSDBA.

[orarman@vm160 ~]$ echo $ORACLE_SID
orcl19
[orarman@vm160 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 21 07:25:07 2023
Version 19.18.0.0.0

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
[orarman@vm160 ~]$ sqlplus / as sysbackup

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 21 07:25:15 2023
Version 19.18.0.0.0

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


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

SQL>

That looks good so far and is exactly how I wanted it to behave. But in the end I need RMAN for doing all the backup things.

[orarman@vm160 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Dec 21 07:28:31 2023
Version 19.18.0.0.0

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01017: invalid username/password; logon denied

[orarman@vm160 ~]$ rman target / as sysbackup

Argument     Value          Description
-----------------------------------------------------------------------------
target       quoted-string  connect-string for target database
catalog      quoted-string  connect-string for recovery catalog
auxiliary    quoted-string  connect-string for auxiliary database
nocatalog    none           if specified, then no recovery catalog
cmdfile      quoted-string  name of input command file
log          quoted-string  name of output message log file
trace        quoted-string  name of output debugging message log file
append       none           if specified, log is opened in append mode
debug        optional-args  activate debugging
msgno        none           show RMAN-nnnn prefix for all messages
send         quoted-string  send a command to the media manager
pipe         string         building block for pipe names
script       string         name of catalog script to execute
using        list of args   arguments for rman variables
timeout      integer        number of seconds to wait for pipe input
checksyntax  none           check the command file for syntax errors
-----------------------------------------------------------------------------
Both single and double quotes (' or ") are accepted for a quoted-string.
Quotes are not required unless the string contains embedded white-space.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00552: syntax error in command line arguments
RMAN-01009: syntax error: found "as": expecting one of: "append, auxiliary, catalog, checksyntax, cmdfile, log, msgno, nocatalog, pipe, script, send, target, timeout, using, @, ;"
RMAN-01007: at line 2 column 1 file: command line arguments

Ok, obviously not as straight-forward as I thought. Let’s check the docs how to connect from RMAN to a database: Oracle 19 Backup and Recovery Reference – connectStringSpec.

Reading the syntax diagram my connect strings are correct. Single quotes are optional and I can add “as sysbackup” to any connect identifier. Reading a bit further, there is this sentence:

If you do not specify a user ID or password when connecting to a target database, then a slash establishes a connection using the SYSDBA privilege by using operating system authentication (see Example 4-12).

Oracle 19 Backup and Recovery Reference: connectStringSpec

Does that mean, when I use the slash, it will always use the AS SYSDBA connection? I can’t believe this to be true. Let’s try RMAN without any connection and try to establish a connection at the RMAN prompt.

[orarman@vm160 ~]$ rman

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Dec 21 06:59:21 2023
Version 19.18.0.0.0

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

RMAN> connect target / as sysbackup

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "as": expecting one of: "newline, ;"
RMAN-01007: at line 1 column 18 file: standard input

The error message is somewhat helpful. RMAN finds the word “as” when it expects a newline or semicolon. So maybe RMAN expects the connect string as a single value so let’s try with quotes.

RMAN> connect target '/ as sysbackup'

connected to target database: ORCL19 (DBID=422142402)

RMAN>

Ok, great. This works. Let’s give it another try using double-quotes. Not in the syntax diagram but who knows?

[orarman@vm160 ~]$ rman

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Dec 21 06:59:41 2023
Version 19.18.0.0.0

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

RMAN> connect target "/ as sysbackup"

connected to target database: ORCL19 (DBID=422142402)

RMAN>

That works too. Good to know. According to the docs, only single-quotes are allowed and are optional. That is obviously not true for all cases. Now finally I want to invoke RMAN directly from the command line including the connect string. Maybe these quotes will help to make the “/ as sysdba” a single parameter value rather than three.

[orarman@vm160 ~]$ rman target "'/ as sysbackup'"

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Dec 21 07:47:41 2023
Version 19.18.0.0.0

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

connected to target database: ORCL19 (DBID=422142402)

RMAN>

This does the trick. And it doesn’t matter, if we enclose the single-quoted string in double-quotes or vice versa. We need two quotes because the outer quotes are interpreted by the shell and tell the shell, that anyting inside these quotes shall be treated as a single value. The inner string with the final connect string is then passed to RMAN which handles it as shown above.

Conclusion: It is possible to establish a RMAN connection using the SYSBACKUP privilege without using a password, e.g. using OS authentication. But it is not as straight-forward as I thought and the docs are not quite clear about the connection string.

RMAN recovery window over redundancy

One of my favorite topics around the Oracle Database is backup and recovery because it is so essential for successfully operating a database landscape. The topic today is a basic setting in RMAN, that controls the lifetime of backups, the “retention policy”. Let’s get into it.

When planning a backup strategy, there are two major values, that should be taken into account. One is the Recovery Time Objective (RTO) which is the amount of time spent to bring a system back to life after an incident. The second is the Recovery Point Objective (RPO) which represents the point in time to which a system shall be restored to get back to operation. The latter one is mainly influencing our retention policy. Imagine the following situation, a major application update takes place which does a lot of DDL and DML to the database because there are so many cool new features in the new release of that application. But for some reason the update fails when it was halfway through. Of cause it cannot be rolled back easily because of the DDL that took place. So the DBA is asked to restore the database to the time just before the update began.

That is, where the fun part starts. Basically it should be an easy thing using RMAN. Set the point in time, restore the controlfile, restore the database, roll it forward until the point in time and then open the database. So let’s go ahead.

[oracle@vm160 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Nov 7 08:33:41 2023
Version 19.18.0.0.0

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

connected to target database: ORCL19 (DBID=422142402)

RMAN> startup nomount force

Oracle instance started

Total System Global Area    2147482320 bytes

Fixed Size                     9165520 bytes
Variable Size               1342177280 bytes
Database Buffers             788529152 bytes
Redo Buffers                   7610368 bytes

RMAN> set dbid=422142402

executing command: SET DBID

RMAN> run {
2> set until time "to_date('06.11.2023 04:00:00', 'dd.mm.yyyy hh24:mi:ss')";
3> restore controlfile from autobackup;
4> alter database mount;
5> restore database;
6> recover database;
7> }

executing command: SET until clause

Starting restore at 2023-11-07 08:35:08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=237 device type=DISK

recovery area destination: /u01/app/oracle/fra
database name (or database unique name) used for search: ORCL19
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20231106
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20231105
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20231104
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20231103
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20231102
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20231101
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20231031
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/07/2023 08:35:11
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

What? Why don’t I have any backup of my controlfile? Let’s check that.

RMAN> list backup of controlfile summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
68      B  F  A DISK        2023-11-07 08:44:01 1       1       NO         TAG20231107T084400

Ok, there is a controlfile backup, but just one. So what are my RMAN settings? Can I see something?

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL19 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/db/19/dbs/snapcf_orcl19.f'; # default

The thing is the redundancy=1 as the retention policy. A redundancy of 1 (in words: one) means just what it says, RMAN keeps exactly one backup of each file it backs up. And often I see backup strategies relying highly on redundancy. Say I want to keep 2 weeks of backups for my database. A full backup takes place every sunday, so I set retention policy to “redundancy 2”. That way, RMAN keeps 2 full backups of my database which, according to the backup schedule, will be good for 2 weeks of backups. No, it is not. Why not? For a couple of reasons. First, as shown, the redundancy applies to all types of backups, also controlfile bakups etc. First, to be able to restore the database to the point 2 weeks ago, we’ll need a controlfile backup from that time. And there might be many of those backups. Second, if we change the backup schedule to backup the database twice a week, we change the retention to about one week implicitely. That’s not what I want it to be.

So how do I come around this? The answer is quite simple: Change the retention policy to be a recovery window rather than redundancy.

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
new RMAN configuration parameters are successfully stored

With that setting the previous example would have run without any errors. There are many backups of the controlfile:

RMAN> list backup of controlfile summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
17      B  F  A DISK        2023-02-24 13:54:24 1       1       NO         TAG20230224T135422
24      B  F  A DISK        2023-10-27 09:13:43 1       1       NO         TAG20231027T091343
26      B  F  A DISK        2023-10-27 09:16:32 1       1       NO         TAG20231027T091632
28      B  F  A DISK        2023-10-28 02:00:06 1       1       NO         TAG20231028T020006
35      B  F  A DISK        2023-10-29 01:00:47 1       1       NO         TAG20231029T010046
37      B  F  A DISK        2023-10-29 02:00:05 1       1       NO         TAG20231029T020005
39      B  F  A DISK        2023-10-30 02:00:08 1       1       NO         TAG20231030T020008
41      B  F  A DISK        2023-10-31 02:00:05 1       1       NO         TAG20231031T020005
43      B  F  A DISK        2023-11-01 02:00:07 1       1       NO         TAG20231101T020007
45      B  F  A DISK        2023-11-02 02:00:06 1       1       NO         TAG20231102T020006
46      B  F  A DISK        2023-11-02 13:28:47 1       1       NO         TAG20231102T132847
47      B  F  A DISK        2023-11-02 13:44:19 1       1       NO         TAG20231102T134419
48      B  F  A DISK        2023-11-02 13:47:53 1       1       NO         TAG20231102T134753
49      B  F  A DISK        2023-11-02 13:57:21 1       1       NO         TAG20231102T135721
52      B  F  A DISK        2023-11-03 02:00:08 1       1       NO         TAG20231103T020008
54      B  F  A DISK        2023-11-04 02:00:07 1       1       NO         TAG20231104T020006
61      B  F  A DISK        2023-11-05 01:00:37 1       1       NO         TAG20231105T010037
63      B  F  A DISK        2023-11-05 02:00:06 1       1       NO         TAG20231105T020006
65      B  F  A DISK        2023-11-06 02:00:05 1       1       NO         TAG20231106T020005
66      B  F  A DISK        2023-11-07 08:24:25 1       1       NO         TAG20231107T082424
67      B  F  A DISK        2023-11-07 02:00:06 1       1       NO         TAG20231107T020006
68      B  F  A DISK        2023-11-07 08:44:01 1       1       NO         TAG20231107T084400

With all these backups I can easily restore the database to the given point in time.

[oracle@vm160 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Nov 7 08:35:59 2023
Version 19.18.0.0.0

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

connected to target database: ORCL19 (not mounted)

RMAN> set dbid=422142402

executing command: SET DBID

RMAN> run {
2> set until time "to_date('06.11.2023 04:00:00', 'dd.mm.yyyy hh24:mi:ss')";
3> restore controlfile from autobackup;
4> alter database mount;
5> restore database;
6> recover database;
7> }

executing command: SET until clause

Starting restore at 2023-11-07 08:36:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

recovery area destination: /u01/app/oracle/fra
database name (or database unique name) used for search: ORCL19
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fra/ORCL19/autobackup/2023_11_06/o1_mf_s_1152151205_lnjgnooh_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20231106
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fra/ORCL19/autobackup/2023_11_06/o1_mf_s_1152151205_lnjgnooh_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/ORCL19/controlfile/o1_mf_kq6lhlbv_.ctl
output file name=/u01/app/oracle/fra/ORCL19/controlfile/o1_mf_kq6lhlmd_.ctl
Finished restore at 2023-11-07 08:36:53

released channel: ORA_DISK_1
Statement processed

Starting restore at 2023-11-07 08:36:58
Starting implicit crosscheck backup at 2023-11-07 08:36:58
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 48 objects
Finished implicit crosscheck backup at 2023-11-07 08:36:59

Starting implicit crosscheck copy at 2023-11-07 08:36:59
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2023-11-07 08:36:59

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fra/ORCL19/autobackup/2023_11_06/o1_mf_s_1152151205_lnjgnooh_.bkp
File Name: /u01/app/oracle/fra/ORCL19/autobackup/2023_11_07/o1_mf_s_1152260664_lnmsk92o_.bkp
File Name: /u01/app/oracle/fra/ORCL19/autobackup/2023_11_07/o1_mf_s_1152237606_lnm30pow_.bkp

using channel ORA_DISK_1

skipping datafile 5; already restored to file /u01/app/oracle/oradata/ORCL19/datafile/o1_mf_system_kq6m8z4c_.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/ORCL19/datafile/o1_mf_sysaux_kq6m8z7g_.dbf
skipping datafile 8; already restored to file /u01/app/oracle/oradata/ORCL19/datafile/o1_mf_undotbs1_kq6m8zgv_.dbf
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 /u01/app/oracle/oradata/ORCL19/datafile/o1_mf_system_kq6l8vf4_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL19/datafile/o1_mf_sysaux_kq6lbyd0_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL19/datafile/o1_mf_undotbs1_kq6ld1qb_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCL19/datafile/o1_mf_users_kq6ld4xm_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20231105_data_1s2am3om_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20231105_data_1s2am3om_1_1 tag=TAG20231105T010006
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 00019 to /u01/app/oracle/oradata/ORCL19/ED7F830814134AADE053A024100A5D08/datafile/system_umbenannt.dbf
channel ORA_DISK_1: restoring datafile 00020 to /u01/app/oracle/oradata/ORCL19/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_kzgxy7n2_.dbf
channel ORA_DISK_1: restoring datafile 00021 to /u01/app/oracle/oradata/ORCL19/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_kzgxy7n4_.dbf
channel ORA_DISK_1: restoring datafile 00022 to /u01/app/oracle/oradata/ORCL19/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_kzgxy7n5_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20231105_data_1t2am3p6_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20231105_data_1t2am3p6_1_1 tag=TAG20231105T010006
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/ORCL19/EF608A6519330511E053A024100A91E2/datafile/o1_mf_system_ks5pktn8_.dbf
channel ORA_DISK_1: restoring datafile 00016 to /u01/app/oracle/oradata/ORCL19/EF608A6519330511E053A024100A91E2/datafile/o1_mf_sysaux_ks5pktn9_.dbf
channel ORA_DISK_1: restoring datafile 00017 to /u01/app/oracle/oradata/ORCL19/EF608A6519330511E053A024100A91E2/datafile/o1_mf_undotbs1_ks5pktnb_.dbf
channel ORA_DISK_1: restoring datafile 00023 to /u01/app/oracle/oradata/ORCL19/EF608A6519330511E053A024100A91E2/datafile/o1_mf_users_ln761vy3_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20231105_data_1u2am3pd_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20231105_data_1u2am3pd_1_1 tag=TAG20231105T010006
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2023-11-07 08:37:29

Starting recover at 2023-11-07 08:37:29
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fra/ORCL19/onlinelog/o1_mf_1_kq6lkxbr_.log
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fra/ORCL19/onlinelog/o1_mf_2_kq6lk9px_.log
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20231105_arch_202am3pk_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20231105_arch_202am3pk_1_1 tag=TAG20231105T010035
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fra/ORCL19/archivelog/2023_11_07/o1_mf_1_4_lnmt9trc_.arc thread=1 sequence=4
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/ORCL19/archivelog/2023_11_07/o1_mf_1_4_lnmt9trc_.arc RECID=268 STAMP=1152261450
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=5
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20231105_arch_222am794_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20231105_arch_222am794_1_1 tag=TAG20231105T020004
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fra/ORCL19/archivelog/2023_11_07/o1_mf_1_5_lnmt9w16_.arc thread=1 sequence=5
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/ORCL19/archivelog/2023_11_07/o1_mf_1_5_lnmt9w16_.arc RECID=269 STAMP=1152261452
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20231106_arch_242aorl4_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20231106_arch_242aorl4_1_1 tag=TAG20231106T020004
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fra/ORCL19/archivelog/2023_11_07/o1_mf_1_6_lnmt9yvg_.arc thread=1 sequence=6
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/ORCL19/archivelog/2023_11_07/o1_mf_1_6_lnmt9yvg_.arc RECID=270 STAMP=1152261455
archived log file name=/u01/app/oracle/fra/ORCL19/onlinelog/o1_mf_1_kq6lkxbr_.log thread=1 sequence=7
archived log file name=/u01/app/oracle/fra/ORCL19/onlinelog/o1_mf_2_kq6lk9px_.log thread=1 sequence=8
media recovery complete, elapsed time: 00:00:07
Finished recover at 2023-11-07 08:37:42

Setting the RMAN retention policy to any redundancy is a really bad idea. It might work out for the backup of the datafiles, but it will definitely cause issues with the backups of controlfiles.

So typically we have a requirement for the RTO. We should always use the given value for setting the RMAN retention policy to a recovery windows so that RMAN can keep track of all the necessary backups and is able to keep them as long as they might be required.

Connecting to an Oracle Database – what could possible go wrong

This is gonna be a very short blog post, isn’t it? Connecting to an Oracle Database is straight forward. Add and alias to your tnsnames.ora file with the propper connection description, save it, start SQL*Plus (or SQLcl or whatever) and type

SQL> connect myuser/mysecret@mytnsalias

And here we go, we have a connection. Or even simpler, use the EZConnect syntax for creating a connection which makes the tnsnames.ora obsolete.

SQL> connect myuser/mysecret@db-hostname:1521/db-servicename

But you guess it, I wouldn’t be writing this if it is always as easy as this. Errors during the process of establishing a connection to an Oracle Database are propably the most common issues that I need to troubleshoot and solve.

For this post, I asume that a tnsnames.ora file is being used rather than EZConnect or a central LDAP directory. I’ll walk through the connection process step by step by using “SQL*Plus” and “tnsping”.

TNS-03505: Failed to resolve name / ORA-12154: TNS:could not resolve the connect identifier specified

[opc@cmp-openvpn ~]$ sqlplus system/********@db23c

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 28 06:27:09 2023
Version 19.19.0.0.0

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

[oracle@db23ctest ~]$ tnsping db23c

TNS Ping Utility for Linux: Version 23.0.0.0.0 - Production on 28-SEP-2023 06:29:45

Copyright (c) 1997, 2023, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/23.0.0.0/dbhome_1/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name

These two error messages are basically the same. It means, that we were unable to find the given alias in the tnsnames.ora file. Sounds like a simple thing, but may have different root causes:

• The alias is not in the TNSNAMES.ORA file
• A wrong TNSNAMES.ORA file was being used, the path for the SQLNET.ORA file might indicate the location. I wrote about the search order in another blog post.
• The parameter NAMES.DEFAULT_DOMAIN is specified in SQLNET.ORA. The value of this parameter is automatically added to the connection alias before searching the TNSNAMES.ORA in case it contains no dots.

TNS-12545 / ORA-12545: Connect failed because target host or object does not exist

[opc@cmp-openvpn ~]$ sqlplus system/********@db23ctest

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 28 06:43:28 2023
Version 19.19.0.0.0

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

ERROR:
ORA-12545: Connect failed because target host or object does not exist

This error means, that the given hostname in the connect description could not be resolved by DNS. So we need to verify the hostname using “nslookup” or “ping” for instance.

TNS-12535: TNS:operation timed out / ORA-12170: TNS:Connect timeout occurred

[opc@cmp-openvpn ~]$ sqlplus system/********@db23ctest

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 28 06:56:23 2023
Version 19.19.0.0.0

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

ERROR:
ORA-12170: TNS:Connect timeout occurred

C:\Users\marco\>tnsping db23test

TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 28-SEP-2023 08:54:44

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\client19\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.30.110)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB23C_db23test.snrsbctl.vcnrsbctl.oraclevcn.com)))
TNS-12535: TNS:operation timed out

When encountering this error, we were able to resolve the alias via TNSNAMES.ORA and were also able to identify the database hostname. But for some reason we were not able to connect to the listener port. Most propably this is due to a firewall rule preventing the connection.

ORA-12541: TNS:no listener

[opc@cmp-openvpn ~]$ sqlplus system/********@db23ctest

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 28 07:02:13 2023
Version 19.19.0.0.0

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

ERROR:
ORA-12541: TNS:no listener

[oracle@db23ctest ~]$ tnsping db23test

TNS Ping Utility for Linux: Version 23.0.0.0.0 - Production on 28-SEP-2023 07:02:42

Copyright (c) 1997, 2023, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/23.0.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.30.110)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB23C_db23test.snrsbctl.vcnrsbctl.oraclevcn.com)))
TNS-12541: Cannot connect. No listener at host 10.0.30.110 port 1522.
 TNS-12560: Database communication protocol error.
  TNS-00511: No listener
   Linux Error: 111: Connection refused

This error seems to be straight forward, but may occur for several reasons. At least we made it to the database server. Possible reasons are:

• The listener is not running
• The listener is running but listens to another port
• The listener is running and listens to the given port, but is not registered with the specified IP address

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

[opc@cmp-openvpn ~]$ sqlplus system/********@db23ctest

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 28 07:15:34 2023
Version 19.19.0.0.0

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

At this point we made it to the listener, but as the error messages states, the listener does not know the service name we are trying to connect to. This error can only be seen using SQL*Plus (or similar), TNSPING will not reveal any issues at this point since it only checks the connection until it gets a response from the server at the given port. It does not verify the service name. The service name comes into play, when really we want to establish a database connection.

[oracle@db23ctest ~]$ tnsping db23test

TNS Ping Utility for Linux: Version 23.0.0.0.0 - Production on 28-SEP-2023 07:17:39

Copyright (c) 1997, 2023, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/23.0.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.30.110)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB23C_db23test.snrsbctl.vcnrsbctl.oraclevcn)))
OK (0 msec)

Again there are several possible reasons for encountering an ORA-12514:

• We are using a wrong service name.
• The service is not registered with the listener. Maybe the database is down or the service is not started.
• The databases “listener*” parameters are not properly configured preventing the database from registering its services with the listener.

ORA-01017: invalid credential or not authorized; logon denied

[opc@cmp-openvpn ~]$ sqlplus system/Wr0ngPwd@db23ctest

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 28 07:30:15 2023
Version 19.19.0.0.0

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

ERROR:
ORA-01017: invalid credential or not authorized; logon denied

If we see this error message, we finally made it to the database. We reached the listener, the listener did know of our requested service name and handed the connection over to the database. The database now checks the logon credentials and returns this error in case this validation failed.

Summary

Establishing a connection to an Oracle Database is quite a complex thing. And it starts the be a “real” database issue when we encounter an ORA-1017 error. Anything else happens before even touching the database.

OpenVPN Connect to my Oracle Cloud

In another post I already outlined the installation and setup of an OpenVPN server in my Oracle Cloud Infrastructure (OCI). As a result, I was able to securely connect to my cloud resources from my laptop by establishing a VPN connection. Because my new router at home can now handle VPN connections on it’s own, I wanted to utilize this feature. At a first glance, an IPsec connection would be best. But at the OCI side I need to create a “Customer On-Prem Equipment” (CPE) which represents the peer that connects to OCI. But unfortunately this has to be an IP address rather than a name that can be resolved via DNS. Since my provider changes my IP from time to time, using IPsec is not really an option. That’s why I use OpenVPN instead.

In the previous post I used a configuration file along with separate files for the required keys and certificates. My router now requires a single .ovpn file. This file is quite easy to build, I found the answer on ServerFault which has a reference to the OpenVPN manuals. The basic idea is to include the keysand certificates in XML-style syntax to the config file:

INLINE FILE SUPPORT
OpenVPN allows including files in the main configuration for the –ca, –cert, –dh, –extra-certs, –key, –pkcs12, –secret and –tls-auth options.Each inline file started by the line <option> and ended by the line </option>

For my case I create the single configuration file as follows, assuming all the required files are in the same directory:

[root@dbamarco-01 ~]# cd /etc/openvpn/client/

[root@dbamarco-01 client]# ll
total 28
-rw-------. 1 root root 1212 Jun 23 18:18 ca.crt
-rw-r--r--. 1 root root  453 Jun 24 07:05 client.ovpn
-rw-r--r--. 1 root root 7945 Aug  8 12:24 client.ovpn.full
-rw-------. 1 root root 4534 Jun 23 18:18 dbamarco-client01.crt
-rw-------. 1 root root 1704 Jun 23 18:18 dbamarco-client01.key

[root@dbamarco-01 client]# (
> echo '<ca>'
> cat ca.crt
> echo '</ca>'
> echo '<cert>'
> cat dbamarco-client01.crt
> echo '</cert>'
> echo '<key>'
> cat dbamarco-client01.key
> echo '</key>'
> ) >> client.ovpn

The single configurationfile then looks like this (stripped key/certificate data):

[root@dbamarco-01 client]# cat client.ovpn
client
dev tun
proto udp

remote 287.387.10.1 1194   # obfuscated

ca ca.crt
cert dbamarco-client01.crt
key dbamarco-client01.key

;cipher AES-256-CBC
;auth SHA512
;auth-nocache
;tls-version-min 1.2
;tls-cipher TLS-DHE-RSA-WITH-AES-256-GCM-SHA384:TLS-DHE-RSA-WITH-AES-256-CBC-SHA256:TLS-DHE-RSA-WITH-AES-128-GCM-SHA256:TLS-DHE-RSA-WITH-AES-128-CBC-SHA256

resolv-retry infinite
compress lz4
nobind
persist-key
persist-tun
mute-replay-warnings
verb 3
<ca>
-----BEGIN CERTIFICATE-----
XXX
-----END CERTIFICATE-----
</ca>
<cert>
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:

XXX
</cert>
<key>
-----BEGIN PRIVATE KEY-----
XXX
-----END PRIVATE KEY-----
</key>

In this file, I just changed the “remote” IP address to point to a DNS name, that I keep updated using my preferred dynamic DNS service. I can now configure my router using this file.

Here I use the configuration file and can then connect my router to the OCI.

Once the connection is established, I can connect to my cloud resources using their private IP addresses. All the required routing and security is already set up.

Cool new router, that I have 🙂

Suspicous ORA-12516 by Oracle SCAN Listener

A couple of days ago, I had to move some databases to new hardware for one of my customers. Don’t ask about the Oracle Database version we came from… The new systems are two 2-node clusters connected using some standby machanism. Due to the lack of a RAC license, the cluster is working as a failover cluster. Of cause we use Oracle Grid Infrastructure to achieve that.

$ opatch lspatches
34863894;TOMCAT RELEASE UPDATE 19.0.0.0.0 (34863894)
34768569;ACFS RELEASE UPDATE 19.18.0.0.0 (34768569)
34768559;OCW RELEASE UPDATE 19.18.0.0.0 (34768559)
34765931;DATABASE RELEASE UPDATE : 19.18.0.0.230117 (REL-JAN230131) (34765931)
33575402;DBWLM RELEASE UPDATE 19.0.0.0.0 (33575402)

When the surrounding applications came up again, we began to experience strange errors when applications tried to connect to the database. They got ORA-12516 and ORA-12520 errors. Most propably we run out of processes inside the database instance causing the listener to block new connections. MOS note “Intermittent TNS-12520 or TNS-12519 or TNS-12516 Connecting via Oracle Net Listener (Doc ID 240710.1)” describes that quite well. I checked the resource

SQL:>select * from v$resource_limit where resource_name in ('processes', 'sessions');

   RESOURCE_NAME    CURRENT_UTILIZATION    MAX_UTILIZATION    INITIAL_ALLOCATION    LIMIT_VALUE    CON_ID
________________ ______________________ __________________ _____________________ ______________ _________
processes                           170                209       1000                  4000             0
sessions                            184                210       1540                  6048             0

Obviously the processes limit is not the issue, there are plenty of processes left to handle new sessions. So I had a look into to SCAN listener log and found those errors:

17-JUL-2023 08:26:53 * (CONNECT_DATA=(SERVICE_NAME=xdb03_svc)(CID=(PROGRAM=quvae.exe)(HOST=CLIENT-W004)(USER=someuser))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.101.85.4)(PORT=53249)) * establish * xdb03_svc * 12516
TNS-12516: TNS:listener could not find available handler with matching protocol stack

Before that, I found hundreds of entries indicating an established connection, here are some of these:

17-JUL-2023 08:26:46 * (CONNECT_DATA=(SERVICE_NAME=xdb03_svc)(CID=(PROGRAM=quvae.exe)(HOST=CLIENT-W004)(USER=someuser))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.101.85.4)(PORT=53059)) * establish * xdb03_svc * 0
17-JUL-2023 08:26:46 * (CONNECT_DATA=(SERVICE_NAME=xdb03_svc)(CID=(PROGRAM=quvae.exe)(HOST=CLIENT-W004)(USER=someuser))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.101.85.4)(PORT=53060)) * establish * xdb03_svc * 0
17-JUL-2023 08:26:46 * (CONNECT_DATA=(SERVICE_NAME=xdb03_svc)(CID=(PROGRAM=quvae.exe)(HOST=CLIENT-W004)(USER=someuser))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.101.85.4)(PORT=53061)) * establish * xdb03_svc * 0
17-JUL-2023 08:26:46 * (CONNECT_DATA=(SERVICE_NAME=xdb03_svc)(CID=(PROGRAM=quvae.exe)(HOST=CLIENT-W004)(USER=someuser))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.101.85.4)(PORT=53062)) * establish * xdb03_svc * 0
17-JUL-2023 08:26:46 * (CONNECT_DATA=(SERVICE_NAME=xdb03_svc)(CID=(PROGRAM=quvae.exe)(HOST=CLIENT-W004)(USER=someuser))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.101.85.4)(PORT=53063)) * establish * xdb03_svc * 0
17-JUL-2023 08:26:46 * (CONNECT_DATA=(SERVICE_NAME=xdb03_svc)(CID=(PROGRAM=quvae.exe)(HOST=CLIENT-W004)(USER=someuser))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.101.85.4)(PORT=53064)) * establish * xdb03_svc * 0
17-JUL-2023 08:26:46 * (CONNECT_DATA=(SERVICE_NAME=xdb03_svc)(CID=(PROGRAM=quvae.exe)(HOST=CLIENT-W004)(USER=someuser))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.101.85.4)(PORT=53065)) * establish * xdb03_svc * 0
17-JUL-2023 08:26:46 * (CONNECT_DATA=(SERVICE_NAME=xdb03_svc)(CID=(PROGRAM=quvae.exe)(HOST=CLIENT-W004)(USER=someuser))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.101.85.4)(PORT=53066)) * establish * xdb03_svc * 0
17-JUL-2023 08:26:46 * (CONNECT_DATA=(SERVICE_NAME=xdb03_svc)(CID=(PROGRAM=quvae.exe)(HOST=CLIENT-W004)(USER=someuser))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.101.85.4)(PORT=53067)) * establish * xdb03_svc * 0
17-JUL-2023 08:26:46 * (CONNECT_DATA=(SERVICE_NAME=xdb03_svc)(CID=(PROGRAM=quvae.exe)(HOST=CLIENT-W004)(USER=someuser))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.101.85.4)(PORT=53068)) * establish * xdb03_svc * 0

In a single minute there were more than 1500 established connections:

$ grep "17-JUL-2023 08:26" /u01/app/oracle/diag/tnslsnr/oracle-srv2/listener_scan1/trace/listener_scan1.log | wc -l
1504

Using a SCAN listener, the connection process is as follows:

1. Client calls the SCAN listener and asks for a service to connect to
2. SCAN listener returns the local listener that hosts the given service to the client
3. Client calls the local listener and asks for a connection to the service
4. Local listener forks a database process and hands over the TCP connection

After that, the listeners are no longer involved in the communication. So the next step was to check the log of the local listener. None of these connections reported by the SCAN listener showed up in the local listener.

The client never made it to the local listener, no server process was spawned and no datatabase session was created. Why is the listener blocking then? The MOS note has an explanation for that too:

The listener counts the number of connections it has established to the instance
but does not immediately get information about connections that have terminated.
Only when PMON/LREG updates the listener via SERVICE_UPDATE is the listener
informed of current load. Since this can take as long as 10 minutes, there can be
a difference between the current instance load according to the listener
and the actual instance load.

When the listener believes the current number of connections has reached maximum
load, it may set the state of the service handler for an instance to “blocked”
and begin refusing incoming client connections with either of the following
errors:

Given that information, it is crystal-clear why the SCAN listener started to block new connections. It counted up to a thousand connections which is the limit of the database. At this point it started to block even though none of these connections made it to the database.

But why not? In the SCAN listeners log we can see, that a connection attempt follows the previous attempt without any delay. In contrast to this, the TNSNAMES.ORA alias of cause has some timeouts and delays defined according to MAA whitepapers (here and here):

XDB03 =
  (DESCRIPTION = 
    (CONNECT_TIMEOUT= 90)(RETRY_COUNT=50)(RETRY_DELAY=3)(TRANSPORT_CONNECT_TIMEOUT=3) 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = TCP)(HOST=oracle-scan1)(PORT=1521))
    ) 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = TCP)(HOST=oracle-scan2)(PORT=1521))
    ) 
    (CONNECT_DATA=
      (SERVICE_NAME = xdb03_svc)
    )
  )

Obviously these parameters are not in effect. Otherwise there should be delays between two connection attempts. Also, if the attempts would really fail, they should stop after 50 retries.

The customer does not really know, what interface the application is using for database connections. It is “some native driver which does not pick up tnsnames.ora”, whatever that means. As soon as they switched to an installed Oracle Client, those strange connection storms disappeared. Obviously the application is now using the retry and timeout parameters as it should be.

We tried to capture network packets from the clients in questions, but that put to much load on the client causing it to slow down to much. If you have any ideas, how such a situation can be further debugged, I would be happy to get that feedback.

Remounting an ACFS filesystem

Another story that kept me awake like this one will be the todays topic. By some funny coincidence it’s also about the same customer. This time I was called because a very important ACFS filesystem could not be mounted. The customer tried to change the group- and user-ID of the Grid Infrastructure Home which was not a good idea at all. So they reverted all the changes but an ACFS filesystem did not come up again. That was the moment they called me.

So I first checked the ASM volumes. There was only one volume in “disabled” state.

[oracle@server02 ~]$ asmcmd volinfo --all
Diskgroup Name: DATA

         Volume Name: U27
         Volume Device: /dev/asm/u27-510
         State: DISABLED
         Size (MB): 31815680
         Resize Unit (MB): 64
         Redundancy: UNPROT
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /u27

The obvious idea was to re-enable to volume. But that didn’t succeed.

[oracle@server02 ~]$ asmcmd volenable --all
ORA-15032: not all alterations performed
ORA-15489: conflicting volume already open on this node (DBD ERROR: OCIStmtExecute)

[oracle@server02 ~]$ oerr ora 15489
15489, 00000, "conflicting volume already open on this node"
// *Cause: An ASM volume was not closed prior to instance shutdown
// on node specified. It is conflicting with the volume that
// is to be enabled.
// *Action: Close the ASM volume either via unmount or node restart and
// retry the operation.
//

Ok, we have a disabled volume which is still in use somehow. It really is, see this:

[root@server02 ~]# mount|grep u27
/dev/asm/u27-510 on /u27 type acfs (rw,relatime,device,ordered)
[root@server02 ~]# ll /u27
ls: cannot open directory /u27: Input/output error
[root@server02 ~]# umount /u27
umount: /u27: target is busy.
        (In some cases useful info about processes that use
         the device is found by lsof(8) or fuser(1))
umount.acfs: CLSU-00107: operating system function: OfsWaitProc; failed with error data: 32; at location: OWPR_1
umount.acfs: CLSU-00101: operating system error message: Broken pipe
umount.acfs: ACFS-04151: unmount of mount point /u27 failed

I can’t use the filesystem and cannot unmount the filesystem either. So what is preventing me from unmounting the filesytem?

[root@server02 ~]# fuser /u27
[root@server02 ~]# lsof |grep  /u27
[root@server02 ~]# umount /u27
umount: /u27: target is busy.
        (In some cases useful info about processes that use
         the device is found by lsof(8) or fuser(1))
umount.acfs: CLSU-00107: operating system function: OfsWaitProc; failed with error data: 32; at location: OWPR_1
umount.acfs: CLSU-00101: operating system error message: Broken pipe
umount.acfs: ACFS-04151: unmount of mount point /u27 failed
[root@server02 ~]# lsof |grep u27-510

At this point the customer and I agreed to simply reboot the system. Beside the fact, that the system did not come up in the first place due to some misconfiguration in the grub.conf, this finally solved the issue.

I just needed to load the kernel modules since this was a stand-alone installation, enable the volume and finally mount the filesysem again.

[root@server02 ~]# lsmod|grep advm
[root@server02 ~]# $GRID_HOME/bin/acfsload start
[root@server02 ~]# su - oracle
[oracle@server02 ~]$ asmcmd  volenable --all
[root@server02 ~]# /sbin/fsck -a -y -t acfs /dev/asm/u27-510
[root@server02 ~]# mount -t acfs /dev/asm/u27-510 /u27

Customer happy, their Golden Gate Trails where back again.

Oracle ASM – why user defined filenames should be avoided

Last week my night was interrupted by a customer call. He told me, that on one of his systems an ASM diskgroup ran out of space. All the instances are stuck and so on, you know those stories. Luckily, he already found two files that were consuming some terrabytes which he considered as obsolete. I’ll tell you in a minute, why he was assuming that. This is an Exadata system running several databases, they use bigfile tablespaces, just to mention that. But unfortunately he was not able to delete these two files and that’s why he wanted me to get rid of these files.

So I logged in to his system and had a look for myself. From what the customer told me, the files seemed to be not in use anymore. At least when reading the directory name.

ASMCMD> cd +DATAC1/ASM/DATAFILE_OLD
ASMCMD> pwd
+DATAC1/ASM/DATAFILE_OLD
ASMCMD> ls -lsh
Type      Redund  Striped  Time             Sys  Block_Size     Blocks          Bytes           Space  Name
DATAFILE  HIGH    COARSE   MAY 29 20:00:00  Y            8K  857110785           6.4T           19.2T  EXCON_DATEN_TS.1157.1127667053
DATAFILE  HIGH    COARSE   MAY 29 20:00:00  Y            8K  475892737           3.5T           10.6T  t_bundle_uses_tbs.1574.1110647865

Because the directory was named “DATAFILE_OLD”, these two files were probably some orphaned files. So I gave it another try to get rid of them:

ASMCMD> rm -rf EXCON_DATEN_TS.1157.1127667053
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATAC1/ASM/DATAFILE_OLD/EXCON_DATEN_TS.1157.1127667053' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)

But I experienced the same error that the customer got beforehand. That is where the story really starts. The reason for not being able to delete the files is quite obvious, the files are still in use by some instance. Fortunately ASM takes care of that and prevents us from deleting files, that are currently in use. But the question is, to which instance/database these files belong to? ASM has a “lsof” command similar to the “lsof” in Linux that shows a list of instances and files that are currently in use. So let’s start with that.

[grid@some-server ~]$ asmcmd lsof -G DATAC1 | grep -i datafile_old
[grid@some-server ~]$ asmcmd lsof -G DATAC1 | grep -i excon
APLOUAT_  APLOUAT1       +DATAC1/APLOUAT_FRA1XP/DATAFILE/local_excon_index_ts.680.1072824757
APLOUAT_  APLOUAT1       +DATAC1/APLOUAT_fra1xp/DATAFILE/excon_daten_ts
LIONCDB_  LIONCDB1       +DATAC1/LIONCDB_DB/FB877908850202CFE0538C200A0A1A63/DATAFILE/excon_daten_ts.323.1136681069
LIONCDB_  LIONCDB1       +DATAC1/LIONCDB_DB/FB877908850202CFE0538C200A0A1A63/DATAFILE/excon_index_ts.1137.1136679463
LIONCDB_  LIONCDB1       +DATAC1/LIONCDB_DB/FB877908850202CFE0538C200A0A1A63/DATAFILE/local_excon_data_ts.1139.1136679463
LIONCDB_  LIONCDB1       +DATAC1/LIONCDB_DB/FB877908850202CFE0538C200A0A1A63/DATAFILE/local_excon_index_ts.1160.1136679461

Ok, no datafiles containing the strange “DATAFILE_OLD” and some files containing at least parts of the filename in question, but not exactly what I was looking for.

After some time of thinking about this, I had an idea. It could be related to aliases. If you create a datafile from within a database inside ASM and specify a filename, you will get two things: A datafile in Oracle Managed File format and an alias with in the given directory and the given filename pointing to that datafile. ASM obviously knows, that the file is in use, but since the database is opening the file using the user defined filename, “lsof” will show the name of the alias rather than the real filename.

How can I find out, to which database the files belong? There are views like V$ASM_ALIAS and V$ASM_FILE, but they are not easy to query. Some searching lead me to this very helpful thread in the Oracle Forums. The query was exactly what I needed. I simply had to adapt the diskgroup number and then walk through all the databases to find the aliases pointing to the two files in question. Here’s the query again:

WITH qry1 AS
(
SELECT aa.name,
       ag.name group_name,
       af.type,
       aa.file_number,
       aa.file_incarnation,
       aa.parent_index parent_index
FROM v$asm_alias aa,
     v$asm_diskgroup ag,
     v$asm_file af
WHERE aa.file_number = af.file_number
AND aa.group_number = ag.group_number
AND ag.group_number = af.group_number
AND aa.file_incarnation = af.incarnation
AND aa.system_created = 'N'
AND ag.group_number = &&groupnumber.
)
SELECT alias_name,
       '+'||group_name||'/'||LISTAGG(CASE WHEN alias_name = root_name THEN name END,'/') WITHIN GROUP(ORDER BY lvl DESC) alias_path,
       '+'||group_name||'/'||LISTAGG(CASE WHEN alias_name != root_name THEN name END,'/') WITHIN GROUP(ORDER BY lvl DESC) file_path
FROM
(
SELECT aa.name,
       q1.group_name,
       q1.file_number,
       q1.name alias_name,
       LEVEL lvl,
       CONNECT_BY_ROOT aa.name root_name
FROM v$asm_alias aa,
     qry1 q1
WHERE aa.group_number = &&groupnumber.
START WITH (aa.name = q1.name OR (aa.name != q1.name AND aa.file_number = q1.file_number))
CONNECT BY PRIOR aa.parent_index = aa.reference_index
AND q1.name = PRIOR q1.name
)
GROUP BY group_name,file_number,alias_name;

I found the aliases in database APLOUAT. Let’s check the ASM directory:

ASMCMD> cd DATAC1/APLOUAT_fra1xp/DATAFILE/
ASMCMD> ls -lsh
Type      Redund  Striped  Time             Sys  Block_Size     Blocks          Bytes           Space  Name
DATAFILE  HIGH    COARSE   MAY 29 20:00:00  Y            8K       9185          71.8M            216M  CDC_METADATA_TS.667.1072824731
DATAFILE  HIGH    COARSE   MAY 29 20:00:00  N            8K  857110785           6.4T           19.2T  EXCON_DATEN_TS => +DATAC1/ASM/DATAFILE_OLD/EXCON_DATEN_TS.1157.1127667053
DATAFILE  HIGH    COARSE   MAY 29 20:00:00  Y            8K   25586305         195.2G          585.6G  IIMEX_BRIDGEHEAD_TS.295.1072823641
DATAFILE  HIGH    COARSE   MAY 29 20:00:00  Y            8K   80536705         614.4G            1.8T  IMEX_TS.302.1072792101
DATAFILE  HIGH    COARSE   MAY 29 20:00:00  Y            8K  603979777           4.5T           13.5T  LICENSING_SMALL_TS.306.1072792099
DATAFILE  HIGH    COARSE   MAY 29 20:00:00  Y            8K   94317569         719.6G            2.1T  LION_STAGE_DATA_TS.298.1072792101
DATAFILE  HIGH    COARSE   MAY 29 20:00:00  Y            8K     262145             2G              6G  LOCAL_EXCON_INDEX_TS.680.1072824757
DATAFILE  HIGH    COARSE   MAY 29 20:00:00  Y            8K   10838145          82.7G          248.1G  PROCESS_INDEX_TS.315.1072823415
DATAFILE  HIGH    COARSE   MAY 29 20:00:00  Y            8K   15859713           121G            363G  UNDOTS1.676.1132578301
DATAFILE  HIGH    COARSE   MAY 29 20:00:00  Y            8K   19660801           150G            450G  UNDOTS2.2364.1132578301
DATAFILE  HIGH    COARSE   MAY 29 20:00:00  N            8K  475892737           3.5T           10.6T  t_bundle_uses_tbs => +DATAC1/ASM/DATAFILE_OLD/t_bundle_uses_tbs.1574.1110647865

At that point, I was able to tell the customer, that these two files belong to tablespaces in a running database which is the reason, that these files cannot be easily deleted. The customer story ends here, but I have something more to tell. There might be an easier way to find the alias. Remember the output of “asmcmd lsof”:

[grid@some-server ~]$ asmcmd lsof -G DATAC1 | grep -i excon
APLOUAT_  APLOUAT1       +DATAC1/APLOUAT_FRA1XP/DATAFILE/local_excon_index_ts.680.1072824757
APLOUAT_  APLOUAT1       +DATAC1/APLOUAT_fra1xp/DATAFILE/excon_daten_ts
LIONCDB_  LIONCDB1       +DATAC1/LIONCDB_DB/FB877908850202CFE0538C200A0A1A63/DATAFILE/excon_daten_ts.323.1136681069
LIONCDB_  LIONCDB1       +DATAC1/LIONCDB_DB/FB877908850202CFE0538C200A0A1A63/DATAFILE/excon_index_ts.1137.1136679463
LIONCDB_  LIONCDB1       +DATAC1/LIONCDB_DB/FB877908850202CFE0538C200A0A1A63/DATAFILE/local_excon_data_ts.1139.1136679463
LIONCDB_  LIONCDB1       +DATAC1/LIONCDB_DB/FB877908850202CFE0538C200A0A1A63/DATAFILE/local_excon_index_ts.1160.1136679461

See the highlighted line, this is one of the two aliases pointing to the files in question. Since the names are somehow similar, doing a case-insensitive search for open files containing parts of the name can reveal the aliases, that are pointing to these files. But this only works, when the user defined filename contains the tablespace name. If the filename is completely different, then you have to fall back to the query above.

At the end, this is a good example, why one should not use user defined filenames when using ASM. Better stick to Oracle Manged Filenames (OMF), this will make things much easier.