How to extract hostname withouth FQDN in a shell variable

Taken from https://stackoverflow.com/questions/36151206/hostname-variable-in-shell-script-not-fqdn and really useful:

export HOST=${HOSTNAME%%.*}

Workaround for ORA-65011 in an upgraded EBS database running multitenant

If you are converting a non-cdb EBS database to a pdb in a multitenant setup, and your non-cdb had a name that used lower case ("pdb1" instead of "PDB1") the different configurationscripts that you will run during the upgrade will set the hidden parameter _pdb_name_case_sensitive to TRUE.

If you try to connect to your pdb as you normally would, you will see this error:

SYS@cdb SQL>  alter session set container=pdb1;
ERROR:
ORA-65011: Pluggable database PDB1 does not exist.

Check the setting of _pdb_name_case_sensitive:

SYS@cdb SQL> show parameter pdb

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_pdb_name_case_sensitive             boolean     TRUE

To connect, enclose your pdb name with double quotation marks:

SYS@cdb SQL> alter session set container="pdb1";

Session altered.

SYS@cdb SQL> show con_name

CON_NAME
------------------------------
pdb1
SYS@cdb SQL> show con_id

CON_ID
------------------------------
3

The note ORA-65011: Pluggable Database Does Not Exist (Doc ID 2642230.1) from Oracle Support confirms this finding.

How to change an Oracle instance parameter based on database version and container type

Here is a simple pl/sql script that will set parameters based on version and container type.

set serveroutput on
DECLARE

v_version     sys.v_$instance.version%type;
v_param_value sys.v_$system_parameter.value%type;
v_pdb boolean;
v_con_id number;
v_con_type varchar(7);

BEGIN

-- find current setting of optimizer_dynamic_sampling
select value
into   v_param_value
from   v$parameter
where  name = 'optimizer_dynamic_sampling';

-- find current(major) version
select version
into   v_version
from   sys.v_$instance;

-- find container type
select 
    case when (sys_context('USERENV','CON_ID') = 0 ) then 'NON-CDB' 
       when (sys_context('USERENV','CON_ID') = 1 ) then 'CDB'
       when (sys_context('USERENV','CON_ID') > 1 ) then 'PDB'
    end
into v_con_type
from DUAL;

--dbms_output.put_line('v_con_type: ' || v_con_type);
--dbms_output.put_line('version: ' || v_version);
--dbms_output.put_line('optimizer_dynamic_sampling: ' || v_param_value);

v_pdb := FALSE;

IF v_con_type = 'PDB' THEN
  v_pdb := TRUE;
ELSE
  v_pdb := FALSE;
END IF;


CASE WHEN v_version IN ('18.0.0.0.0','19.0.0.0.0') AND v_param_value = '0' THEN
    dbms_output.put_line('version is >= 18 and optimizer_dynamic_sampling = 0');
    
    IF v_pdb = FALSE THEN
      dbms_output.put_line('execute immediate alter system set optimizer_dynamic_sampling=4 scope=both;');
      execute immediate 'alter system set optimizer_dynamic_sampling=4 scope=both';
    ELSIF v_pdb = TRUE THEN
      dbms_output.put_line('execute immediate alter system set optimizer_dynamic_sampling=4 container=current scope=both;');
      execute immediate 'alter system set optimizer_dynamic_sampling=4 container=current scope=both';
    END IF;
    
WHEN v_version IN ('18.0.0.0.0','19.0.0.0.0') AND v_param_value <> '0' THEN   
     dbms_output.put_line('version is >= 18 and optimizer_dynamic_sampling is already set.');
ELSE
  dbms_output.put_line('version is < 18');
END CASE;

END;
/

How to check for existence of multiple files in a shell script

# check for installation files existence
if  [ ! -f /tmp/file1 -a -f /tmp/file2 -a -f /tmp/file3 ]
then
echo "Some of the files file1, file2 or file3 are missing from /tmp "
exit 1
fi

How to list all nfs mountpoints

findmnt -t nfs

TARGET SOURCE                                               FSTYPE OPTIONS
/u01   pzl2ora1:/Oracle/software/pzh0oric/u01 nfs    rw,nodiratime,relatime,vers=3,rsize=65536,wsize=65536,namlen=255,acregmin=300,acregmax=300,acdirmin=300,ac
/u02   pzl2ora1:/Oracle/pzh0oric/u02          nfs    rw,nodiratime,relatime,vers=3,rsize=65536,wsize=65536,namlen=255,acregmin=300,acregmax=300,acdirmin=300,ac
/u03   pzl2ora1:/Oracle/pzh0oric/u03          nfs    rw,nodiratime,relatime,vers=3,rsize=65536,wsize=65536,namlen=255,acregmin=300,acregmax=300,acdirmin=300,ac
/u04   pzl2ora1:/Oracle/pzh0oric/u04          nfs    rw,nodiratime,relatime,vers=3,rsize=65536,wsize=65536,namlen=255,acregmin=300,acregmax=300,acdirmin=300,ac

How to solve ORA-01187: cannot read from file because it failed verification tests

After a successful clone, alert log reports:

022-06-30T04:04:17.542368+02:00
Errors in file /orainst/oracle/diag/rdbms/testdb01/testdb01/trace/testdb01_dbw0_130154.trc:
ORA-01186: file 201 failed verification tests
ORA-01122: database file 201 failed verification check
ORA-01110: data file 201: '/data/oradata/testdb01/temp01.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN
File 201 not verified due to error ORA-01122

Any operation you try against the database will give the following error stack in return:

ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 201: '/data/oradata/testdb01/temp01.dbf'
ORA-06512: at "SYS.DBMS_LOB", line 741
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5420
ORA-06512: at line 1

Check tempfile situation:

SYS@testdb01>SQL>select ts#,status,enabled, name from v$tempfile;

       TS# STATUS  ENABLED    NAME
---------- ------- ---------- ---------------------------------
         3 ONLINE  READ WRITE /data/oradata/testdb01/temp01.dbf
         3 ONLINE  READ WRITE /data/oradata/testdb01/temp02.dbf

A query against dba_temp_files however, will give an error:

SYS@testdb01>SQL>SELECT tablespace_name, file_name  FROM dba_temp_files WHERE tablespace_name = 'TEMP';
SELECT tablespace_name, file_name  FROM dba_temp_files WHERE tablespace_name = 'TEMP'
                                        *
ERROR at line 1:
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 201: '/data/oradata/testdb01/temp01.dbf'

Is the temporary tablespace database default?

SYS@testdb01>SQL>
col property_value format a30
SELECT PROPERTY_VALUE   
FROM DATABASE_PROPERTIES 
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
-----------------------
TEMP

Yes it is, so we cannot drop it, but will have to add new files and drop the old ones

Add new tempfile:

SYS@testdb01>SQL>ALTER TABLESPACE temp ADD TEMPFILE '/data/oradata/testdb01/temp.dbf' size 256M;

Tablespace altered.

Drop the old ones:

SYS@testdb01>SQL>ALTER DATABASE TEMPFILE '/data/oradata/testdb01/temp01.dbf' DROP INCLUDING DATAFILES;

Database altered.

SYS@testdb01>SQL>ALTER DATABASE TEMPFILE '/data/oradata/testdb01/temp02.dbf' DROP INCLUDING DATAFILES;

Database altered.

Both of the old datafiles are now gone from the data dictionary:

SYS@pserser4>SQL>
set lines 200
col tablespace_name format a20
col file_name format a50
SELECT tablespace_name, file_name  FROM dba_temp_files WHERE tablespace_name = 'TEMP';

TABLESPACE_NAME         FILE_NAME
---------------------   --------------------------------------
TEMP                    /data/oradata/testdb01/temp.dbf

Note that the old tempfiles are still on disk:

SYS@testdb01>SQL>!ls -la /data/oradata/testdb01/temp01.dbf
-rw-r----- 1 oracle dba 114302976 Jun 30 04:04 /data/oradata/testdb01/temp01.dbf

If you want to add the old datafiles back to your temp tablespace you can do this with the REUSE keyword:

SYS@testdb01>SQL>ALTER TABLESPACE temp ADD TEMPFILE '/data/oradata/testdb01/temp01.dbf' size 256M reuse;

Tablespace altered.

In my case, the other tempfile /data/oradata/testdb01/temp02.dbf, was not present physically on disk.
The alert log confirms this:

2022-06-30T04:04:18.302852+02:00
Errors in file /data/oracle/diag/rdbms/testdb01/testdb01/trace/testdb01_dbw0_130154.trc:
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/data/oradata/testdb01/temp02.dbf'
ORA-17503: ksfdopn:4 Failed to open file /data/oradata/testdb01/temp02.dbf
ORA-17500: ODM err:File does not exist

ORA-01017: incorrect username/password, login denied when using a wallet

If you find yourself in the situation where you cannot make a connection using your wallet because of the error

ORA-01017: incorrect username/password, login denied when using a wallet

It might be worth checking that the user you have created an entry in your wallet for, also is granted the sysdba role.
Example:
I want to use a database user called "dbamaster" to performa a clone from active database over the network.
Since I do not wish to reveal the password of the "dbamaster" user, I add the user to a wallet, and then use the notation

connect target /@proddb
connect auxiliary /@testdb

to connect.

Listing the content of the wallet shows that everything is in order:

mkstore -wrl $TNS_ADMIN/wallet -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)
2: testdb dbamaster
1: proddb dbamaster

You can connect successfully to both databases using the "dbamaster" user, using TNS:

-- From auxiliary to target
sqlplus dbamaster@proddb

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 29 20:03:09 2022
Version 19.15.0.0.0

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

Enter password:

Koblet til:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

DBAMASTER@proddb>

-- From target to auxiliary
sqlplus dbamaster@testdb

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 29 20:43:43 2022
Version 19.15.0.0.0

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

Enter password:

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

DBAMASTER@testdb SQL>

Everything seems in order, but incorrect password nevertheless.

Check the v$pwfile_users view, which holds the current users that have sysdba privilege:

col account_status format a20
col username format a20
col password_profile format a20
select username,sysdba,sysoper,account_Status,password_profile from v$pwfile_users;

USERNAME             SYSDB SYSOP ACCOUNT_STATUS       PASSWORD_PROFILE
-------------------- ----- ----- -------------------- --------------------
SYS                  TRUE  TRUE  OPEN                 NOEXP

In this case user "dbamaster" was missing.

Add it:

grant sysdba to dbamaster;

Check the list of sysdbas again. You should now have two entries there:

 select username,sysdba,sysoper,account_Status,password_profile from v$pwfile_users;

USERNAME             SYSDB SYSOP ACCOUNT_STATUS       PASSWORD_PROFILE
-------------------- ----- ----- -------------------- --------------------
SYS                  TRUE  TRUE  OPEN                 NOEXP
DBAMASTER            TRUE  FALSE OPEN                 NOEXP

Your connections should now work:

RMAN> connect target /@proddb

connected to target database: proddb (DBID=253631174)

RMAN> connect auxiliary /@testdb

connected to auxiliary database: TESTDB (DBID=296823958)