Wednesday, June 29, 2022

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)

Tuesday, June 28, 2022

List parameters in a container database

To see the list of parameters in a container database, use can use the following query.
select name,value,display_value, default_value,isdefault,
    case 
     when issys_modifiable = 'FALSE'     then  'Change must go to spfile. Restart of cdb required'
     when issys_modifiable = 'IMMEDIATE' then  'Current sessions will pick up new value.'
     when issys_modifiable = 'DEFERRED'   then 'Future sessions will pick up the new value'
     else issys_modifiable
    end "issys_modifiable",
     case 
     when ispdb_modifiable = 'TRUE' then 'Parameter can be set in PDB'
     when ispdb_modifiable = 'FALSE' then 'Parameter cannot be set in PDB'
    end "ispdb_modifiable" 
    from v$system_parameter; 
Example output shows that the parameters can be set at different levels in your multitenant database structure

NAME VALUE DISPLAY_VALUE DEFAULT_VALUE ISDEFAULT issys_modifiable ispdb_modifiable
sga_max_size 8287944704 7904M 0 TRUE Change must go to spfile. Restart of cdb required Parameter cannot be set in PDB
shared_pool_size 671088640 640M 134217728 FALSE Current sessions will pick up new value. Parameter can be set in PDB
sga_target 8287944704 7904M 0 FALSE Current sessions will pick up new value. Parameter can be set in PDB
sort_area_size 65536 65536 65536 TRUE Future sessions will pick up the new value Parameter can be set in PDB

* Some parameters may only be written to the spfile and the CDB instance must be restarted.
* Other parameters may be set in the CDB and will be picked up in the individual PDBs, either affecting current sessions or future sessions only
* Some parameters may be set in each PDB, regardless of the CDB parameter value for the same parameter is set to


"Listing the Modifiable Initialization Parameters in PDBs Oracle 19c" is documented here

The v$system_parameter view in Oracle 19c is documented here

Tuesday, June 21, 2022

What is the difference between mkstore and orapki?

The difference is mainly that the orapki tool is dealing with certificates rather than the wallet itself. The mkstore is more of a tool for administrating privileged users and their passwords, so that you can set up connections without exposing their passwords in your scripts.

Can both of these tools be used to manage my wallets?
Yes.
From MOS Doc ID 2044185.1 "What is an Oracle Wallet?":

If you configure TDE the database creates the wallet for you when you issue the ALTER SYSTEM command to initialize TDE, the other tools to create and inspect wallets are Oracle Wallet Manager (owm) which is a GUI tool, then we also have command line tools orapki to setup certificates and mkstore more suited to store so called secret store entries like the above mentioned user credentials, often the tools can be used interchangeably, for example if you create a wallet for TDE using the database with SQL you can inspect the contents later using mkstore etc.

What exactly is the orapki tool?

From the latest Oracle documentation:
The orapki utility manages public key infrastructure (PKI) elements, such as wallets and certificate revocation lists, from the command line.

What exactly is the mkstore tool?

From the latest Oracle Documentation:
The mkstore command-line utility manages credentials from an external password store.

What exactly is a wallet?

A wallet is a password-protected container that is used to store authentication and signing credentials, including private keys, certificates, and trusted certificates needed by SSL.
It can be stored directly on the server, wherever suits the DBA. The path must be pointed out in the client's sqlnet.ora file, using the directive WALLET_LOCATION


For an example on how to set up a wallet, see this post

Friday, June 17, 2022

How to solve ORA-17628: Oracle error 1031 returned by remote Oracle server ORA-01031: insufficient privileges when cloning a non-cdb oracle instance to a PDB

When attempting to clone my database testdb01, a normal, non-cdb database, into a CDB and convert it to a PDB, I hit a permission error. I found the solution on Oracle Supports web site, Doc ID 2485839.1.

Prior to this error, I had set up a database link in my CDB:
SYS@cdb>SQL>create database link noncdb connect to system identified by mypassword using 'testdb01.mydomain.no';
I tested the database link, worked fine:
SYS@cdb>SQL>select host_name from v$instance@noncdb;

HOST_NAME
--------------------------------------------------
mynoncdbserver.mydomain.no
I tried to create the pluggable database, using the appropriate file destination paths already created:
create pluggable database testdb01 
from non$cdb@noncdb 
file_name_convert=('/data1/oradata/testdb01/','/data1/oradata/cdb/testdb01/', 
                   '/data2/oradata/testdb01/', '/data2/oradata/cdb/testdb01/');
Errors out:
ORA-17628: Oracle error 1031 returned by remote Oracle server
ORA-01031: insufficient privileges
To solve the error, simply logon to your non-cdb database as a sysdba user and grant the privilege "create pluggable database" to the user you're using for copying (in my case, SYSTEM):
grant create pluggable database to system;
Try the create pluggable database command again, and it succeeds.

Tuesday, June 14, 2022

How to alter a flashback data archive that needs additional quota

SELECT F.FLASHBACK_ARCHIVE_NAME, F.TABLESPACE_NAME,F.QUOTA_IN_MB, 
       (SELECT ROUND(SUM(S.BYTES)/1024/1024/1024)
    FROM DBA_SEGMENTS S 
    WHERE S.TABLESPACE_NAME=F.TABLESPACE_NAME) "occupied"
 FROM  DBA_FLASHBACK_ARCHIVE_TS F;
FLASHBACK_ARCHIVE_NAME TABLESPACE_NAME QUOTA_IN_MB occupied
MY_FDA FDA 20480
19

If you've reached the quota, add a larger one, like this:
ALTER FLASHBACK ARCHIVE MY_FDA 
MODIFY TABLESPACE FDA QUOTA 20 G; 

Tuesday, May 31, 2022

How to solve ORA-65035: unable to create pluggable database from PDB$SEED

The error means:
oerr ora 65035
65035, 00000, "unable to create pluggable database from %s"
// *Cause:  An attempt was made to clone a pluggable database that did not have
//          local undo enabled.
// *Action: Enable local undo for the PDB and and retry the operation.
So let's do that: add local undo to our CDB, so that we can create new PDBs from the PDB$SEED container:
SYS@cdb01>SQL>show con_name

CON_NAME
------------------------------
CDB$ROOT

SYS@cdb01>SQL>alter database local undo on;
alter database local undo on
*
ERROR at line 1:
ORA-65192: database must be in UPGRADE mode for this operation
SYS@cdb01>SQL>shutdown
SYS@cdb01>SQL>startup upgrade
SYS@cdb01>SQL>ALTER DATABASE LOCAL UNDO ON;

Database altered.

SYS@cdb01>SQL>shutdown immediate
SYS@cdb01>SQL>startup
Verify that local undo is enabled:
column property_name format a30
column property_value format a30

select property_name, property_value
from   database_properties
where  property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE
You can now create your PDB:
 SYS@cdb01>SQL>create pluggable database veg1 admin user pdbadmin identified by mypassword file_name_convert=('/data/pdbseed','/data/veg1');

Pluggable database created.

Wednesday, May 11, 2022

Find available directories on your server

set lines 200
col directory_name format a30
col directory_path format a60
select directory_name,directory_path
from dba_directories;
exit