Joords Oracle DBA blog

How to Detect and Repair Corruption in Your Oracle Database

noreply@blogger.com (joordsblog) — Mon, 11 Mar 2024 10:08:00 +0000

The DBMS_DICTIONARY_CHECK is a read-only and lightweight PL/SQL package procedure that helps you identify Oracle Database dictionary inconsistencies that are manifested in unexpected entries in the Oracle Database dictionary tables or invalid references between dictionary tables. Oracle Database dictionary inconsistencies can cause process failures and, in some cases, instance crash.

How to Execute a Dictionary Check

First, The database must be on Oracle Database 19.22.0 or higher
Execute the Check


set serverout on
exec dbms_dictionary_check.full;

The database spools to the console and a trace file:


dbms_dictionary_check on 10-MAR-2024 13:31:56
----------------------------------------------
Catalog Version 19.0.0.0.0 (1900000000)
db_name: UP19
Is CDB?: NO
Trace File:
/u01/app/oracle/diag/rdbms/up19/UP19/trace/UP19_ora_3343_DICTCHECK.trc

                                Catalog       Fixed
Procedure Name                  Version    Vs Release    Timestamp      Result
--------------------------- ... ---------- -- ---------- -------------- ------
.- OIDOnObjCol		       ... 1900000000 <=  *All Rel* 03/02 13:31:56 PASS
.- LobNotInObj		       ... 1900000000 <=  *All Rel* 03/02 13:31:56 PASS
.- SourceNotInObj	       ... 1900000000 <=  *All Rel* 03/02 13:31:56 PASS

...

.- LobSeg                   ... 1900000000 <=  *All Rel* 03/02 13:31:56 PASS
---------------------------------------
02-MAR-2024 13:31:56  Elapsed: 0 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File:
/u01/app/oracle/diag/rdbms/up19/UP19/trace/UP19_ora_3343_DICTCHECK.trc

PL/SQL procedure successfully completed.

If there are any warnings or errors, you can find a detailed description of the check in the documentation. Some checks even have a fix mode that allows you to correct the corruption.

In multitenant, you run the check in the root container and all PDBs. The PDBs are most important.

Words of Advice. Run the check in due time before a major maintenance operation. Although many checks have a fix option, you sometimes need to engage with Oracle Support. This can take time, so don’t do it the day before an important upgrade.

AutoUpgrade makes your life easier. Of course, it can run the dictionary check for you. Add the following to your config file:


<prefix>.run_dictionary_health=full

During the pre-upgrade analysis, AutoUpgrade executes the dictionary check and stores the full report in the precheck folder in the AutoUpgrade log directory.

Transportable Tablespaces to the Extreme

noreply@blogger.com (joordsblog) — Fri, 08 Mar 2024 10:02:00 +0000

Oracle has engineered a new method to execute a Transportable Tablespaces to the Extreme. See the recording on Oracle's YouTube channel. You can read the Q&A and the slides .

The New Procedure called M5

For cross-platform and cross-endian migrations, we have a new procedure called M5.
It replaces the former XTTS v4 Perl script that has been used for many migrations.
We could see the need for changes as Oracle Databases world-wide grows in size and complexity. M5 implements the latest RMAN and Data Pump technology to deliver the fastest possible migrations. You can download the M5 scripts and read the procedure on My Oracle Support (Doc ID 2999157.1).

M5 Migration Script


# source database
RUN
{
  ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '...';
  ALLOCATE CHANNEL d2 DEVICE TYPE DISK FORMAT '...';
  BACKUP FILESPERSET 1 SECTION SIZE 64G
  TAG UP19_L0_240206101548
  TABLESPACE <list-of-tablespace> ; 
}


# target database
RUN
{
  ALLOCATE CHANNEL DISK1 DEVICE TYPE DISK FORMAT '...';
  ALLOCATE CHANNEL DISK2 DEVICE TYPE DISK FORMAT '...';
  RESTORE ALL FOREIGN DATAFILES TO NEW FROM BACKUPSET
   '<backup-set-1>', 
   '<backup-set-2>',
   ...
   '<backup-set-n>'
};

Requirements

Source and Taerget MUST

Be 19.18.0 or higher
Use Data Pump Bundle Patch

Target must use Oracle Managed Files (OMF)

Parameter DB_CREATE_FILE_DEST

Oracle Zero Data Loss Recovery Appliance Cyber Security Architecture

noreply@blogger.com (joordsblog) — Thu, 08 Feb 2024 13:16:00 +0000

Separation of Duty

Ensuring that backup data is validated is key to being prepared for a future recovery, which means that all data, including TDE databases, must be validated for recoverability at each step of the workflow into the Cyber Vault.

Multi-layer anomaly detection in the architecture ensures that data is valid at all points in the Oracle ecosystem. Each zone indicated above will perform independent checks to prevent invalid or compromised data from entering or exiting the zone and ultimately the Cyber Vault.

Zone 1

RMAN will do consistency checks at the database to ensure the backup data is valid before being sent to the Recovery Appliance in the production data center

Zone 2

The Recovery Appliance in the production data center performs checks to ensure that the data is valid and complete.
Data is validated when the RMAN database backup arrives on the Recovery Appliance in the production data center.
Data replicated from the Recovery Appliance in the production data center is validated again before being sent to the Recovery Appliance in the Cyber Vault.
Regular automated validation is scheduled within the Recovery Appliance in the production data center to ensure ongoing validity as the data ages.

Zone 3

The Recovery Appliance in the Cyber Vault performs checks to ensure that the data is valid and complete
Data is validated upon arrival into the Recovery Appliance in the Vault from the Recovery Appliance in the production data center.

Regular automated validation is scheduled within the Recovery Appliance in the Cyber Vault to ensure ongoing validity as the data ages

Enterprise Manager positioning

Oracle Enterprise Manager is the single dashboard for your entire Oracle deployment. In this Cyber Vault architecture, Enterprise Manager is used for management, monitoring and alerting for the Recovery Appliance and for backup automation. This centralized management helps verify the integrity and provide compliance reporting across the entire infrastructure.

Synchronizing the Vault with Latest Backups

The Recovery Appliance real time redo capability produces backups that are always available for the Cyber Vault. This eliminates the troublesome synchronization process with the backup schedule and the Cyber Vault replication network gateway that opens randomly. The most current backup data is automatically transferred to the Cyber Vault the moment the Cyber Vault replication gateway opens. This replication gateway is randomized and controlled from the Cyber Vault location.

Data Recovery

The Recovery Appliance can backup and recover data to any platform supported by the Oracle database. This eases the planning required for recovery operations since the data is available for recovery to any known good location during a cyber event. The gateway can only push data from Cyber Vault location to the environment and is controlled from the Cyber Vault location

Documentation reference

Zero Data Loss Recovery Appliance Cyber Security Architecture

Connecting CMAN - Traffic Director Mode to an Oracle Database

noreply@blogger.com (joordsblog) — Fri, 26 Jan 2024 15:40:00 +0000

Oracle Connection Manager (CMAN) serves as a proxy for database connections within Oracle deployments. It facilitates the transmission of client requests to the Oracle Database and seamlessly transmits the database responses back to the client, whether it be an application, middle-tier, or other components. CMAN operates as a versatile networking solution, providing features such as TLS security, protocol switching/routing, and session multiplexing to enhance the accessibility of both local and remote Oracle databases.

Traffic Director Mode (TDM) is an intelligent layer introduced in Oracle Client 18c and beyond, designed to enhance CMAN. TDM introduces additional dimensions to the system, including transparent High Availability (HA) with outage support, advanced security features for cloud environments (protection against DoS and fuzzing attacks, tenant isolation, etc.), and capabilities for optimizing performance.

In summary, CMAN-TDM allows any client application to establish a connection with an Oracle database, whether it's on-premises or in the cloud, without revealing the underlying database details to the client. An important aspect worth highlighting is the interoperability of CMAN-TDM with various versions of Oracle databases.

Sample CMAN-TDM Architecture

HA with Dedicated Connections in CMAN-TDM

I will not use the remote_listener to cman, so that cman will be used as a listener

Some documents may recommend to set the instance's REMOTE_LISTENER parameter to both the SCAN address and the CMAN address. However, in doing so, you never connect to the database via the SCAN listeners - you simply connect to CMAN and then are redirected directly to a database instance.

CONFIGURATION OF CMAN-TDM - DEDICATED CONNECTIONS

DATABASE PROXY USER CREATION
THE CLIENT USER
DATABASE PROXY USER WALLET CREATION
CMAN.ORA CONFIGURATION
RUNNING CMAN-TDM
TESTING CMAN_TDM
Validation van de CMAN and DATABASE via netstat and logfiles

Step 1. DATABASE PROXY USER CREATION

CMAN-TDM uses a database proxy user, usually named TDM, to create proxy connections for all users accessing the database through CMAN-TDM. The DBA needs to create this user and grant CREATE SESSION privilege, as shown in the example below:

SQL> CREATE USER TDM IDENTIFIED BY TDM_PASSWORD;
 SQL> GRANT CREATE SESSION TO TDM;

Step 2. THE CLIENT USER

The client application should have already created an application user on the database. Grant the CREATE SESSION privilege to the application user through the database proxy user by using the CONNECT THROUGH keyword:

SQL> CREATE USER joords IDENTIFIED BY joords_PASSWORD 
        default tablespace users 
        temporary tablespace temp 
        account unlock quota unlimited on users; 
 SQL> GRANT connect, resource TO joords; 
 SQL> GRANT create session, create table, create procedure, 
            create sequence,create trigger, create view,
            create synonym, alter session, 
            create type to joords; 
 SQL> ALTER USER JOORDS GRANT CONNECT THROUGH TDM;

Step 3. DATABASE PROXY USER WALLET CREATION

[oracle@ol8-21-rac1 scripts]$ echo $ORACLE_HOME
      /u01/app/oracle/product/21.0.0/client_1

 [oracle@ol8-21-rac1 scripts]$ echo $ORACLE_BASE
      /u01/app/oracle 

 [oracle@ol8-21-rac1 scripts]$ export TNS_ADMIN=/u01/app/oracle/product/21.0.0/client_1/network/admin 
 
 [oracle@ol8-21-rac1 scripts]$ export WLLT_CMAN=$ORACLE_BASE/cman 

 [oracle@ol8-21-rac1 scripts]$ mkdir -p $WLLT_CMAN 

 [oracle@ol8-21-rac1 scripts]$ mkstore -wrl $WLLT_CMAN -create 

 [oracle@ol8-21-rac1 scripts]$ echo cmanwllt01 | mkstore -wrl $WLLT_CMAN -createCredential pdb_tacservice TDM 'TDM_PASSWORD' 

 [oracle@ol8-21-rac1 scripts]$ echo cmanwllt01 | mkstore -wrl $WLLT_CMAN -listCredential 
      List credential (index: connect_string username) 1: pdb_tacservice TDM

Step 4. CMAN.ORA CONFIGURATION


[oracle@ol8-21-rac1 scripts]$ cat <<EOF > $TNS_ADMIN/cman-tdm.ora

cman-tdm = (configuration=
  (address=(protocol=tcp)(host=ol8-21-rac1)(port=1888))
  (parameter_list =
    (tdm=true)
    (tdm_threading_mode=dedicated)
    (log_level=ADMIN)
    (max_connections=50)
    (idle_timeout=0)
    (inbound_connect_timeout=0)
    (session_timeout=0)
    (outbound_connect_timeout=0)
    (max_gateway_processes=16)
    (min_gateway_processes=2)
    (remote_admin=on)
    (trace_level=off)
    (max_cmctl_sessions=4)
    (event_group=init_and_term,memory_ops)
  )
  (NEXT_HOP=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=tcp)(HOST=ol8-21-scan.joordsdomain)(PORT=1521))))
  (rule_list=
    (rule=
       (src=*)(dst=*)(srv=*)(act=accept)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
    )
  )
)

wallet_location=(source=(method=file)(method_data=(directory="/u01/app/oracle/cman")))
sqlnet.wallet_override=true

EOF

[oracle@ol8-21-rac1 scripts]$ echo "IFILE=$TNS_ADMIN/cman-tdm.ora" >> $TNS_ADMIN/cman.ora

When we use the IFILE parameter inside cman.ora, multiple CMAN and CMAN-TDM configurations can be managed in the same host without editing cman.ora directly. Multiple CMAN-TDM config files for different instances can be referred to within the cman.ora file using the IFILE parameter.

step 5. RUNNING CMAN-TDM

Startup CMAN for cman-tdm:

$ORACLE_HOME/bin/cmctl startup -c cman-tdm

Shutdown CMAN for cman-tdm:

$ORACLE_HOME/bin/cmctl shutdown  -c cman-tdm

Reload CMAN for cman-tdm:

$ORACLE_HOME/bin/cmctl reload  -c cman-tdm

Start the CMAN-TDM


[oracle@ol8-21-rac1 admin] $ $ORACLE_HOME/bin/cmctl startup -c cman-tdm

CMCTL for Linux: Version 21.0.0.0.0 - Production on 23-JAN-2024 09:22:24

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

Current instance cman-tdm is not yet started
Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=ol8-21-rac1)(port=1888)))
Starting Oracle Connection Manager instance cman-tdm. Please wait...
CMAN for Linux: Version 21.0.0.0.0 - Production
Status of the Instance
----------------------
Instance name             cman-tdm
Version                   CMAN for Linux: Version 21.0.0.0.0 - Production
Start date                23-JAN-2024 09:22:24
Uptime                    0 days 0 hr. 0 min. 9 sec
Num of gateways started   2
Average Load level        0
Log Level                 ADMIN
Trace Level               OFF
Instance Config file      /u01/app/oracle/product/21.0.0/client_1/network/admin/cman.ora
Instance Log directory    /u01/app/oracle/diag/netcman/ol8-21-rac1/cman-tdm/alert
Instance Trace directory  /u01/app/oracle/diag/netcman/ol8-21-rac1/cman-tdm/trace
The command completed successfully.

Step 6. TESTING CMAN-TDM

A weird issue:

Be careful if populating the cman tnsnames.ora!

You do not need to have it at all and you may find weird issues if you have a tnsnames entry

with the same name of the database service you want to connect to:

When I had the pdb_tacservice service used for testing the SEPS and was added to the as a tns entry in its tnsnames.ora ,

I got a generic “ORA-28725: Invalid CMAN in Traffic Director mode configuration” error.

This error is really generic and made me waste quite a lot of time!
==> no sqlnet and tnsnames provided for the CMAN configuration - This solved the problem

Testing


[oracle@ol8-21-rac1 ~]$ srvctl status service -db $ORACLE_UNQNAME
Service pdb_tacservice is running on instance(s) cdbrac1,cdbrac2

[oracle@ol8-21-rac1 ~]$ cat <<EOF>  /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora

pdb_tactdm =
 (DESCRIPTION =
     (CONNECT_TIMEOUT=90)(RETRY_COUNT=50)(RETRY_DELAY=3)   
     (TRANSPRRT_CONNECT_TIMEOUT=3)
     (ADDRESS_LIST=
         (LOAD_BALANCE=ON)
         (ADDRESS = 
            (PROTOCOL = TCP)
            (HOST= ol8-21-rac1.joordsdomain)
            (PORT=1888)
         )
     )
  (CONNECT_DATA=
    (SERVICE_NAME = pdb_tacservice)
  )
 )

EOF

[oracle@ol8-21-rac1 admin]$ tnsping pdb_tactdm

TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 23-JAN-2024 09:36:38

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (CONNECT_TIMEOUT=90)(RETRY_COUNT=50)(RETRY_DELAY=3)(TRANSPRRT_CONNECT_TIMEOUT=3) (ADDRESS_LIST= (LOAD_BALANCE=ON) (ADDRESS = (PROTOCOL = TCP)(HOST= ol8-21-rac1.joordsdomain)(PORT=1888))) (CONNECT_DATA= (SERVICE_NAME = pdb_tacservice) (COLOCATION_TAG=abc)))
OK (0 msec)


[oracle@ol8-21-rac1 admin]$ sqlplus joords/joords_PASSWORD@pdb_tactdm

SQL*Plus: Release 21.0.0.0.0 - Production on Tue Jan 23 09:37:44 2024
Version 21.3.0.0.0

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

Last Successful login time: Thu Jan 18 2024 14:42:02 +00:00

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
connected via Oracle Connection Manager in Traffic Director mode 21.3.0.0.0


SQL> show user
USER is "JOORDS"

SQL>  set linesize 200
      col  DB_UNIQUE_NAME format a20
      col  INSTANCE_NAME  format a20
      col  HOST           format a20 
      col  SERVICE_NAME   format a20 
      col  AUTH_METHOD    format a20 
      col  PROXY_USER     format a20 	  
      select sys_context('USERENV','DB_UNIQUE_NAME') as DB_UNIQUE_NAME
           , sys_context('USERENV','INSTANCE_NAME')  as INSTANCE_NAME
           , sys_context('USERENV','SERVER_HOST')    as HOST
           , sys_context('USERENV','SERVICE_NAME')    as SERVICE_NAME	
           , sys_context('USERENV','AUTHENTICATION_METHOD') as AUTH_METHOD	
           , sys_context('USERENV','PROXY_USER') as PROXY_USER	
           from dual;

DB_UNIQUE_NAME       INSTANCE_NAME        HOST                 SERVICE_NAME         AUTH_METHOD          PROXY_USER
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
cdbrac               cdbrac2              ol8-21-rac2          PDB_TACSERVICE       NONE                 TDM


[oracle@ol8-21-rac1 admin]$ sqlplus joords/joords_PASSWORD@ol8-21-rac1:1888/PDB_TACSERVICE

SQL*Plus: Release 21.0.0.0.0 - Production on Tue Jan 23 09:42:02 2024
Version 21.3.0.0.0

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

Last Successful login time: Tue Jan 23 2024 09:40:38 +00:00

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
connected via Oracle Connection Manager in Traffic Director mode 21.3.0.0.0

SQL> set linesize 200
     col  DB_UNIQUE_NAME format a20
     col  INSTANCE_NAME  format a20
     col  HOST           format a20
     col  SERVICE_NAME   format a20
     col  AUTH_METHOD    format a20
     col  PROXY_USER     format a20

    select sys_context('USERENV','DB_UNIQUE_NAME') as DB_UNIQUE_NAME
         , sys_context('USERENV','INSTANCE_NAME')  as INSTANCE_NAME
         , sys_context('USERENV','SERVER_HOST')    as HOST
         , sys_context('USERENV','SERVICE_NAME')    as SERVICE_NAME
         , sys_context('USERENV','AUTHENTICATION_METHOD') as AUTH_METHOD
         , sys_context('USERENV','PROXY_USER') as PROXY_USER
      from dual;  

DB_UNIQUE_NAME       INSTANCE_NAME        HOST                 SERVICE_NAME         AUTH_METHOD          PROXY_USER
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
cdbrac               cdbrac1              ol8-21-rac1          PDB_TACSERVICE       NONE                 TDM

Connect as sysdba and validate the user JOORDS on the database


[oracle@ol8-21-rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Tue Jan 23 09:44:41 2024
Version 21.3.0.0.0

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


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> alter session set container=PDB1:

SQL> col service_name format a20
     col username format a30

     select inst_id
          , sid,username
          , service_name
          , failover_type
          , failover_method
          , failed_over
          , PROGRAM
      from gv$session where username='JOORDS';

   INST_ID        SID USERNAME                       SERVICE_NAME         FAILOVER_TYPE FAILOVER_M FAI PROGRAM
---------- ---------- ------------------------------ -------------------- ------------- ---------- --- ------------------------------------------------------------------------------------
         1        161 JOORDS                         PDB_TACSERVICE       AUTO          BASIC      NO  cmop@ol8-21-rac1 (TNS V1-V3)

The program is a cmop@ol8-21-rac instead of sqlplus.
CMAN has mapped the sqlplus session to a cman session dedicated and the cman will login as a proxy to the database

Global overview of the CMAN PRCP

Below a global overview of the CMAN PRCP which act as a streaming service between the Client connections and the Database access.

Step 7. Validation CMAN and DATABASE via netstat and logfiles

View /u01/app/oracle/diag/netcman/ol8-21-rac1/cman-tdm/trace/cman-tdm.log


2024-01-23T09:42:02.797365+00:00
23-JAN-2024 09:42:02 * (CONNECT_DATA=(SERVICE_NAME=PDB_TACSERVICE)(CID=(PROGRAM=sqlplus)(HOST=ol8-21-rac1)(USER=oracle))(CONNECTION_ID=D5rCMuGqELDgY2U4qMDJxw==)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=27002)) * establish * PDB_TACSERVICE * 0

View /u01/app/grid/diag/tnslsnr/ol8-21-rac1/listener/trace/listener.log

23-JAN-2024 09:42:02 * (CONNECT_DATA=(SERVICE_NAME=PDB_TACSERVICE)(CONNECTION_ID=D5p8DXU09EjgY2U4qMD2wg==)(USE_DBROUTER=YES)(CID=(PROGRAM=cmop)(HOST=ol8-21-rac1)(USER=oracle))(SERVER=dedicated)(INSTANCE_NAME=cdbrac1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=22814)) * establish * PDB_TACSERVICE * 0

Netstat commands:


[oracle@ol8-21-rac1 ~]$ netstat -anp | grep sqlplus|grep 1888
tcp        0      0 192.168.56.101:27002    192.168.56.101:1888     ESTABLISHED 69808/sqlplus

[oracle@ol8-21-rac1 ~]$ netstat -anp | grep 27002
tcp        0      0 192.168.56.101:27002    192.168.56.101:1888     ESTABLISHED 69808/sqlplus
tcp6       0      0 192.168.56.101:1888     192.168.56.101:27002    ESTABLISHED 62536/cmop

CMAN information:


[oracle@ol8-21-rac1 admin]$ $ORACLE_HOME/bin/cmctl

CMCTL for Linux: Version 21.0.0.0.0 - Production on 23-JAN-2024 11:32:07

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

Welcome to CMCTL, type "help" for information.

CMCTL> administer cman-tdm
Current instance cman-tdm is already started
Connections refer to (DESCRIPTION=(address=(protocol=tcp)(host=ol8-21-rac1)(port=1888))).
The command completed successfully.

CMCTL:cman-tdm> show all
listener_address            | (DESCRIPTION=(address=(protocol=tcp)(host=ol8-21-rac1)(port=1888)))
aso_authentication_filter   |   OFF
connection_statistics       |   OFF
event_group                 | (init_and_term, memory_ops)
log_directory               | /u01/app/oracle/diag/netcman/ol8-21-rac1/cman-tdm/alert
log_level                   | ADMIN
max_connections             |    50
idle_timeout                |     0
inbound_connect_timeout     |     0
session_timeout             |     0
outbound_connect_timeout    |     0
max_gateway_processes       |    16
min_gateway_processes       |     2
max_cmctl_sessions          |     4
password                    |   OFF
remote_admin                |    ON
trace_directory             | /u01/app/oracle/diag/netcman/ol8-21-rac1/cman-tdm/trace
trace_level                 |   OFF
trace_timestamp             |    ON
trace_filelen               |     0
trace_fileno                |     0
service_rate                |     0
connection_rate             |     0
max_all_connections         |     0
max_reg_connections         |     0
compression                 |   OFF
sdu                         |  8192
expire_time                 |     0
non_tunnel_gateways         |  1000
use_sid_as_service          |   OFF
valid_node_checking_registration |   OFF
Number of filtering rules currently in effect: 1
(rule_list=
  (rule=
    (src=*)
    (dst=*)
    (srv=*)
    (act=accept)
    (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
  )
)
The command completed successfully.

CMCTL:cman-tdm> show defaults
listener_address            | (ADDRESS=(PROTOCOL=TCP)(HOST=ol8-21-rac1)(PORT=1521))
aso_authentication_filter   |   OFF
connection_statistics       |   OFF
event_group                 |   OFF
log_directory               | /u01/app/oracle/product/21.0.0/client_1/network/log/
log_level                   | SUPPORT
max_connections             |   256
idle_timeout                |     0
inbound_connect_timeout     |    60
session_timeout             |     0
outbound_connect_timeout    |     0
max_gateway_processes       |    16
min_gateway_processes       |     2
max_cmctl_sessions          |     4
password                    |   OFF
remote_admin                |   OFF
trace_directory             | /u01/app/oracle/product/21.0.0/client_1/network/trace/
trace_level                 |   OFF
trace_timestamp             |   OFF
trace_filelen               |     0
trace_fileno                |     0
The command completed successfully.

CMCTL:cman-tdm> show connections detail

Connection ID                 5
Gateway ID                    0
Source                        192.168.56.101
Source Hostname               ol8-21-rac1
Source Process Id             115863
Source Program Name           sqlplus@ol8-21-rac1 (TNS V1-V3)
Destination                   192.168.56.103
Destination Hostname          ol8-21-rac1
Service                       PDB_TACSERVICE
Idle time                     19916243
Thread Wait time              0
Active time                   445070
State                         IDLE
PRCP Wait time                0
PRCP Checked-out time         0
PRCP Active time              0
PRCP State                    NO STATE
Total Session Gets            1
Session Get Hits              0
The command completed successfully.

COLOCATION_TAG of Client Connections

noreply@blogger.com (joordsblog) — Tue, 23 Jan 2024 12:32:00 +0000

The COLOCATION_TAG parameter is an alphanumeric string that you can use with the CONNECT_DATA parameter of the TNS connect string.

When you set the colocation_tag within the CONNECT_DATA parameter, load balancing is ignored.

The use of COLOCATION_TAG facilitates the preference for colocating sessions that handle identical data when operating across multiple nodes, mitigating the need for inter-node cache synchronization. Simply assign a character string, potentially carrying business significance, and the load balancer will make an effort to keep together sessions that hash to the same value.

As an illustration, the listener endeavors to direct all clients with the COLOCATION_TAG set to "abcworkload" in the connection descriptor to the same database instance.



pdb_tacservice =
 (DESCRIPTION =
  (CONNECT_TIMEOUT=90)(RETRY_COUNT=50)(RETRY_DELAY=3)(TRANSPRRT_CONNECT_TIMEOUT=3)
   (ADDRESS_LIST=
    (LOAD_BALANCE=ON)
    (ADDRESS = (PROTOCOL = TCP)(HOST= ol8-21-scan.joordsdomain)(PORT=1521))
   )
  (CONNECT_DATA=
    (SERVICE_NAME = pdb_tacservice)
    (COLOCATION_TAG=abcworkload)
   )
 )

Note:Under certain conditions, such as, when maximum load of an instance is reached or when new instances are added or deleted for a service, the colocation of client connections that have the same colocation_tag to the same database instance may not be consistent.

Where is my TNSNAMES.ORA from Oracle database 21c

noreply@blogger.com (joordsblog) — Fri, 12 Jan 2024 10:33:00 +0000

Oracle has started from Oracle version 18c to use Oracle read-only software trees. See my blog Configuring Read Only OracleHome - 18c

From Oracle version 21c there are no distinctions between software trees in read-only and read-write modes. As of now, the default configuration is set to utilize the read-only mode introduced in Oracle 18c.

When installing a CDB through DBCA on Oracle Database 21c, you will observe the presence of TNSNAMES.ORA.

The question then arises: where is it located?


[oracle@ol8-21-rac1 admin]$ ${GRID_HOME}/bin/crsctl status resource -w " NAME co cdbrac " -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cdbrac.db
      1        ONLINE  OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      2        ONLINE  ONLINE       ol8-21-rac1              Open,HOME=/u01/app/o
                                                             racle/product/21.0.0
                                                             /dbhome_1,STABLE

We see we have a CDB called CDBRAC lets see if there is a TNSNAMES.ORA file


[oracle@ol8-21-rac1 admin]$ tnsping cdbrac

TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 12-JAN-2024 09:38:29

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ol8-21-scan.joordsdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdbrac)))
OK (10 msec)

Where is this TNSNAMES.ORA file located?


[oracle@ol8-21-rac1 admin]$ echo $ORACLE_HOME/network/admin/
/u01/app/oracle/product/21.0.0/dbhome_1/network/admin/

ls -trl $ORACLE_HOME/network/admin/
total 4
-rw-r--r--. 1 oracle oinstall 1624 Feb 18  2020 shrept.lst
drwxr-xr-x. 2 oracle oinstall   64 Jan 11 09:48 samples

So lets look at the files starting with "ORABASE*" in the bin directory of the ORACLE_HOME


[oracle@ol8-21-rac1 admin]$ ls -trl $ORACLE_HOME/bin/orabas*
-rwxr-xr-x. 1 oracle oinstall   36208 Jul 27  2021 /u01/app/oracle/product/21.0.0/dbhome_1/bin/orabase
-rwxr-x---. 1 oracle oinstall 1858568 Jul 27  2021 /u01/app/oracle/product/21.0.0/dbhome_1/bin/orabasehome
-rwxr-x---. 1 oracle oinstall 1858576 Jul 27  2021 /u01/app/oracle/product/21.0.0/dbhome_1/bin/orabaseconfig

[oracle@ol8-21-rac1 admin]$ ls -d $(orabase)
/u01/app/oracle
[oracle@ol8-21-rac1 admin]$ ls -d $(orabasehome)
/u01/app/oracle/homes/OraDB21Home1
[oracle@ol8-21-rac1 admin]$ ls -d $(orabaseconfig)
/u01/app/oracle

we see that the $(orabasehome) is not equal to the $ORACLE_HOME which means that the TNSNAMES.ORA file will be located outside the $ORACLE_HOME


[oracle@ol8-21-rac1 admin]$ locate tnsnames.ora
/u01/app/21.0.0/grid/network/admin/samples/tnsnames.ora
/u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora
/u01/app/oracle/product/21.0.0/dbhome_1/network/admin/samples/tnsnames.ora

[oracle@ol8-21-rac1 admin]$ pwd  $(orabaseconfig)/network/admin
/u01/app/oracle/homes/OraDB21Home1/network/admin

[oracle@ol8-21-rac1 oracle]$ ls -trl $(orabasehome)/network/admin
total 4
-rw-r-----. 1 oracle oinstall 2914 Jan 11 15:08 tnsnames.ora
[oracle@ol8-21-rac1 oracle]$ cat $(orabasehome)/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

CDBRAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ol8-21-scan.joordsdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdbrac)
    )
  )

Setting the TNSNAMES.ORA as was before the following steps needs to be done

Create a TNS_ADMIN environment variable set the $ORACLE_HOME/netwerk/admin
Copy the $(orabasehome)/network/admin/tnsnames.ora to $TNS_ADMIN/tnsnames.ora
Update your profile by add the TNS_ADMIN variable
Update the ORACLE clusterware attribute, by adding the TNS_ADMIN environment variable to the CDB resource

Managing Oracle Database 19c Users in Active Directory (Part 2 - CMU)

noreply@blogger.com (joordsblog) — Wed, 05 Oct 2022 12:06:00 +0000

To integrate with Active Directory we are going to use two database features

Kerberos authentication (Part 1),
Centrally Managed Users (Part 2)

(note: Centrally Managed Users is an Enterprise Edition feature).

Centrally Managed Users (Part 2)

Managing Oracle Database 19c Users in Active Directory (Part 1 - Kerberos)

noreply@blogger.com (joordsblog) — Wed, 05 Oct 2022 11:55:00 +0000

To integrate with Active Directory we are going to use two database features

Kerberos authentication (Part 1),
Centrally Managed Users (Part 2)

(note: Centrally Managed Users is an Enterprise Edition feature)

Kerberos authentication (Part 1)

FORWARD_LISTENER : the offsite listener forwards the connection to the new Oracle listener

noreply@blogger.com (joordsblog) — Tue, 20 Sep 2022 11:34:00 +0000

The parameter FORWARD_LISTENER is useful when it is difficult to change an existing client connect string, for example, after an offsite database has been moved into the Oracle Cloud. With the FORWARD_LISTENER parameter, clients can continue to connect to their offsite listener, and the offsite listener forwards the connection to the Oracle Cloud listener.

Once a forward listener has been configured through the FORWARD_LISTENER parameter, the LOCAL_LISTENER parameter can be cleared by setting its value to "-oracle-none-" so that all the connections coming to an existing set of remote listeners configured through the REMOTE_LISTENER parameter are forwarded only to listeners configured through FORWARD_LISTENER.

The FORWARD_LISTENER parameter can also be specified in the LISTENER_NETWORKS parameter.

Lets play and test it, information of the environment

Listener overview service endpoint

[oracle@hol ~]$ lsnrctl status listener
=======================================
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hol)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                13-SEP-2022 11:25:32
Uptime                    0 days 0 hr. 7 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/hol/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hol)(PORT=1521)))
Services Summary...
...
...
Service "relocate" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
The command completed successfully

[oracle@hol ~]$ lsnrctl status listener_cdb2
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hol)(PORT=1525)))
STATUS of the LISTENER
------------------------
Alias                     listener_cdb2
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                13-SEP-2022 11:25:36
Uptime                    0 days 0 hr. 9 min. 55 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/hol/listener_cdb2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hol)(PORT=1525)))
Services Summary...
...
...
Service "relocate" has 1 instance(s).
  Instance "cdb2", status READY, has 1 handler(s) for this service...
The command completed successfully

Databases

[oracle@hol ~]$ ps -ef|grep smon
oracle   14537     1  0 11:25 ?        00:00:00 ora_smon_CDB1
oracle   15399     1  0 11:30 ?        00:00:00 ora_smon_cdb2

Listeners

[oracle@hol ~]$ ps -ef|grep lsnr
oracle   14439     1  0 11:25 ?        00:00:00 /u01/app/oracle/product/19/bin/tnslsnr listener -inherit
oracle   14446     1  0 11:25 ?        00:00:00 /u01/app/oracle/product/19/bin/tnslsnr listener_cdb2 -inherit

We have an environment existing of the following construction :

CDB1 instance with a pluggable database PDB1 using default listener on port 1521
CDB2 instance with a pluggable database relocate using the listener_cdb2 on port 1525

The relocate service on the CDB1 is created within the PDB1 using the commands

below:

CDB1_pdb1> BEGIN
  DBMS_SERVICE.create_service(
    service_name => 'relocate',
    network_name => 'relocate'
  );
END;
/

CDB1_pdb1> BEGIN
  DBMS_SERVICE.start_service(
    service_name => 'relocate'
  );
END;
/

CDB1_pdb1> BEGIN
  DBMS_SERVICE.start_service(
    service_name => 'relocate'
  );
END;
/

Start setup the connection forwarding for the application which is using the listener_cdb2 on port 1525 to connect to the new migrated pluggable database listening on a other port an another CDB without changing the application connection string [CDB1 database using relocate service for PDB1 and listener port 1521]

To get this on a my environment operational I have to trick this by using the listener_cdb2 string 'hol:1525' settings for the remote-listener

Validation of the connection to listener_cdb2 which listen on port 1525
No changed are executed on the databases and pdbs

sqlplus c##relocate/oracle@hol:1525/relocate

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 12 13:30:54 2022
Version 19.15.0.0.0

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

Last Successful login time: Mon Sep 12 2022 12:02:51 +02:00

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

CDB2_relocate> col CDB_NAME for a10
CDB2_relocate> col PDB_NAME for a10
CDB2_relocate> SELECT SYS_CONTEXT ('USERENV','CDB_NAME') as CDB_NAME,  SYS_CONTEXT ('USERENV','DB_NAME') as PDB_NAME from dual;

CDB_NAME   PDB_NAME
---------- ----------
cdb2       RELOCATE

On the relocate PDB within the CDB2 we will activate the forward_listener parameter.

CDB2_relocate> alter system set forward_listener='hol:1521' scope=memory
                --- no change the remote listener is not available to reconnect the request to the forward_listener address
 CDB2_relocate> alter system set remote_listener='hol:1525' scope=memory
                -- the trick when working without ASM/grid configuration

The last statement will show in the lsnrctl services listener_cdb2 command the following rules in the listener_cdb2 a entry for forwarding is found for the service relocate

Service "relocate" has 1 instance(s).
  Instance "cdb2", status READY, has 4 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: hol.localdomain, pid: 15427>
         (ADDRESS=(PROTOCOL=tcp)(HOST=hol)(PORT=33773))
      "DEDICATED" established:1 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER   (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=1525)))
      "COMMON" established:0 refused:0 state:ready
         FORWARD SERVER   (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=1521)))

Still the connections are not forwarded to the new database, here for we have to execute the change for the parameter LOCAL_LISTENER

CDB2_relocate> alter system set local_listener='-oracle-none-' scope=memory;

In the listener_cdb2 listener log the service is died for the CDB2

==> listener_cdb2/trace/listener_cdb2.log <==
13-SEP-2022 12:10:29 * service_died * cdb2 * 12537

This will result into a redirect of the new connections to the new environment CDB1 with PDB1

CDB2_relocate> show parameter list

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
forward_listener                     string      hol:1521
listener_networks                    string
local_listener                       string      -oracle-none-
remote_listener                      string      hol:1525

Do the test of connecting again

[oracle@hol ~]$ sqlplus c##relocate/oracle@hol:1525/relocate

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 13 12:11:02 2022
Version 19.15.0.0.0

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

Last Successful login time: Mon Sep 12 2022 13:30:54 +02:00

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

SQL> col CDB_NAME for a10
SQL> col PDB_NAME for a10
SQL> SELECT SYS_CONTEXT ('USERENV','CDB_NAME') as CDB_NAME,  SYS_CONTEXT ('USERENV','DB_NAME') as PDB_NAME from dual;

CDB_NAME   PDB_NAME
---------- ----------
CDB1	   PDB1

The connection request will show two logging events, one in every listener log

=> listener_cdb2/trace/listener_cdb2.log <==

2022-09-13T12:11:02.316515+02:00
13-SEP-2022 12:11:02 * (CONNECT_DATA=(SERVICE_NAME=relocate)(CID=(PROGRAM=sqlplus)(HOST=hol.localdomain)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=19496)) * establish * relocate * 0

==> listener/trace/listener.log <==
2022-09-13T12:11:02.330769+02:00
13-SEP-2022 12:11:02 * (CONNECT_DATA=(SERVICE_NAME=relocate)(CID=(PROGRAM=sqlplus)(HOST=hol.localdomain)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=40100)) * establish * relocate * 0

The connection is forwarded to the an new Oracle Database located somewhere else without changing the application connection string. Now we can easy change this when we have time to do it.

DBMS_SERVICE: drain the existing service connections on the database

Using the dbms_service package we can drain the existing session, so that they will disconnect and when new request are made they will go to the new database environment

The DISCONNECT_SESSION procedure disconnects all sessions currently connected to the service. The disconnection can take one of three forms, indicated by package constants.

POST_TRANSACTION : Sessions disconnect once their current transaction ends with a commit or rollback. This is the default value (0).
IMMEDIATE : Sessions disconnect immediately. Value (1).
NOREPLAY : Sessions disconnect immediately, and are flagged not to be replayed by application continuity. Value (2).

CDB2_relocate> BEGIN
  DBMS_SERVICE.disconnect_session(
   service_name      => 'RELOCATE',
   disconnect_option => DBMS_SERVICE.post_transaction
  );
END;
/

PDB relocate using "AVAILABILITY MAX" clause

noreply@blogger.com (joordsblog) — Mon, 12 Sep 2022 14:08:00 +0000

A PDB relocation executes an online block level copy of the source PDB data files, redo, and undo while the source PDB is open with active sessions. When the target PDB comes online because of an ALTER PLUGGABLE DATABASE OPEN statement, Oracle Database terminates the active sessions and closes the source PDB.

The AVAILABILITY MAX clause in CREATE PLUGGABLE DATABASE ... RELOCATE implicitly instructs the SQL*Net layer to reconfigure the original listener. This situation may be common when relocating a PDB between data centers. This configuration is intended to be temporary while the Oracle Internet Directory (OID) or LDAP server is updated or the client connections are modified.

Overview image Relocate PDB

Requirements for executing a relocate PDB between two CDB's

The user in the local database has the CREATE PLUGGABLE DATABASE privilege in the root container.
The remote CDB is in local undo mode.
The remote and local CDBs are in ARCHIVELOG mode.
The common user in the remote CDB to whom the database link connects has the CREATE PLUGGABLE DATABASE, SESSION, and SYSOPER privilege.
The local and remote CDBs have the same options installed.

Environment

CDB1 with multiple PDB's including the RELOCATE pdb

A default listener used bij de CDB1 on port 1521

CDB2 without pdb's

A second listener used by CDB2 on port 1525

Steps to execute a PDB relocation with minimal downtime

Create common user in both CDB's


> CDB1> create user c##relocate identified by oracle container=all;


 CDB1> grant create session, SYSOPER to C##RELOCATE container=all;

Create databaselink to source from the target


 CDB2> create database link lnk_src connect to c##relocate identified by oracle using 'hol:1521/cdb1';

Create test table for validation


CDB1_relocate> create table scott.relocate ( step number, text varchar2(100)) tablespace users;
CDB1_relocate> insert into scott.relocate values (1, 'before relocation insert')
CDB1_relocate> commit;

Execute the relocate PDB command


CDB2> create pluggable database relocate from relocate@lnk_src relocate AVAILABILITY MAX;

Insert new data to the test table


CDB1_relocate> insert into scott.relocate values (2, 'after relocation insert');
CDB1_relocate> commit;
CDB1_relocate> update scott.relocate set text = 'after relocation update' where step=2 ;
CDB1_relocate> commit;

Open the new PDB on CDB2


CDB2> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 RELOCATE                       MOUNTED

CDB2> alter pluggable database relocate open;

CDB2> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 RELOCATE                       READ WRITE NO

CDB2> ALTER SESSION SET CONTAINER=RELOCATE;

CDB2_relocate>select * from scott.relocate;

      STEP TEXT                       
---------- ------------------------------ 
         1 before relocation insert
         2 after relocation update

Testing connectivity using the entry of CDB1 on port 1521

We want to see if the sql string from the application can contact to the new relocated pdb database without chanhing the connection string


oracle@hol ~]$ sqlplus c##relocate/oracle@hol:1521/relocate

When executing this we see the following happening in the listener logs

====> listener log CDB1 ============
2022-09-12T12:29:29.658798+02:00
12-SEP-2022 12:29:29 * (CONNECT_DATA=(SERVICE_NAME=relocate)(CID=(PROGRAM=sqlplus)(HOST=hol.localdomain)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=16014)) * establish * relocate * 0

====> listener log CDB2 ============
2022-09-12T12:29:29.671058+02:00
12-SEP-2022 12:29:29 * (CONNECT_DATA=(SERVICE_NAME=relocate)(CID=(PROGRAM=sqlplus)(HOST=hol.localdomain)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=58266)) * establish * relocate * 0

SQLPLUS test case in which CDB are we connected


oracle@hol ~]$ SQL> SELECT SYS_CONTEXT('USERENV','CDB_NAME') database_name from dual;
DATABASE_NAME
--------------------------------------------------------------------------------
cdb2

Perfect the listener of the CDB1 will forward it to the listener CDB2, and we get a session connected on the cdb database CDB2

The old PDB relocate on CDB1 cannot be opened as you can see


CDB1> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 RELOCATE                       MOUNTED

CDB1> alter pluggable database relocate open;
alter pluggable database relocate open
*
ERROR at line 1:
ORA-65086: cannot open/close the pluggable database

Dynamic CPU Scaling in Oracle Database

noreply@blogger.com (joordsblog) — Sun, 14 Mar 2021 10:42:00 +0000

Dynamic CPU Scaling in Oracle Database

Prior to this new feature, DBAs had to allocate resources for peak loads to a database to ensure scale up was possible, and to ensure needed resources weren’t taken by other applications, commonly known as "Noisy Neighbors".

Oracle offers the ability to configure "shares" and "limits" for each Pluggable Database within a Container Database.

Shares are expressed as a share value, whereas utilization limit is expressed as a percentage such as shown in the example below:

Dynamic CPU Scaling in Oracle Database 19c (19.4)

With CPU min/max ranges, administrators can establish lower and upper bounds of vCPU available to each Pluggable Database.

This feature is controlled by 2 simple parameters within each Pluggable Database as follows:

CPU_MIN_COUNT
CPU_COUNT

CPU_MIN_COUNT is the minimum number of vCPUs the Pluggable Database Instance will receive. The total of CPU_MIN_COUNT for all Pluggable Database instances should not exceed the CPU_COUNT of the Container Database instance. When DBRM is enabled, and when CPU_MIN_COUNT has been set, the CPU_COUNT parameter defines the maximum number of vCPUs that can be used by a Pluggable Database Instance.

References:

Stop Hardcoding your TDE keystore-password

noreply@blogger.com (joordsblog) — Fri, 05 Mar 2021 10:31:00 +0000

When you encrypt your databases, you will often end up needing the keystore password to perform certain operations. For instance, cloning an encrypted PDB will require the keystore password:

CREATE PLUGGABLE DATABASE ... KEYSTORE IDENTIFIED BY S3cr3t;

Using TDE password into an Oracle Secure External Password Store ([TDE-]SEPS)

Link : https://www.youtube.com/embed/Txg8FyripKU

References :

DBCA - useWalletForDBCredentials 19c

noreply@blogger.com (joordsblog) — Sat, 25 Jul 2020 17:30:00 +0000

You can use Oracle wallet as a secure external password store for authenticating database users in DBCA silent mode commands. Oracle wallet is a secure software container external to Oracle Database, which can be used to store authentication credentials of Oracle Database users.
The configuration of the Oracle wallet as a secure external password store will be done by the mkstore command-line utility.

Database User Authentication in DBCA Commands Using Oracle Wallet

useWalletForDBCredentials
Specify true to use Oracle wallet for database user authentication, else specify false.
Default is false.

dbCredentialsWalletLocation
Directory in which the Oracle wallet files are stored

(Optional) dbCredentialsWalletPasswordPassword for the Oracle wallet account user. If the Oracle wallet is auto-login enabled, then you need not specify this password

Which password keys can be stored into the wallet and using DBCA in silent mode for authenticating users:

oracle.dbsecurity.sysPassword: SYS user password
oracle.dbsecurity.systemPassword: SYSTEM user password
oracle.dbsecurity.pdbAdminPassword: Pluggable database (PDB) administrator password
oracle.dbsecurity.dbsnmpPassword: DBSNMP user password
oracle.dbsecurity.asmsnmpPassword: ASMSNMP user password
oracle.dbsecurity.lbacsysPassword: LBACSYS user password
oracle.dbsecurity.sysdbaUserPassword: SYSDBA role user password for the database that you are creating or configuring
oracle.dbsecurity.oracleHomeUserPassword: Oracle home user password
oracle.dbsecurity.dvUserPassword: Oracle Data Vault user password
oracle.dbsecurity.dvAccountManagerPassword: Oracle Data Vault account manager password
oracle.dbsecurity.emPassword: Enterprise Manager administrator password
oracle.dbsecurity.asmPassword: ASM user password
oracle.dbsecurity.asmsysPassword: ASMSYS user password
oracle.dbsecurity.walletPassword: Oracle wallet account user password for authenticating with a directory service
oracle.dbsecurity.userDNPassword: Directory service user password
oracle.dbsecurity.srcDBsysdbaUserPassword: SYSDBA role user password for the database that you are using as a source to perform certain operations, such as duplicating a database
oracle.dbsecurity.dbLinkUserPassword: Database link user password
oracle.dbsecurity.walletPassword:Oracle Unified Directory (OUD), then the OUD account passwords
oracle.dbsecurity.userDNPassword:Oracle Unified Directory (OUD), then the OUD account passwords

How to use the wallet functionality is documented in chapter 2 of the Database Administration Guide 19c.

dbca -silent -createDatabase -help
...

    [-useWalletForDBCredentials] Specify true to load database credentials from wallet]
    [-dbCredentialsWalletLocation]
 
...

Configure the wallet

The mkstore utility lives in $ORACLE_HOME/bin.

mkstore -wrl ~/wallet -create

Oracle Secret Store Tool Release 20.0.0.0.0 - Production
Version 21.0.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Enter password:
Enter password again:

This Wallets can be copied to different machines, which can represent a security risk.
prevent the auto login functionality of the wallet from working if it is copied to another machine by creating a local wallet using the "orapki" command, instead of the "mkstore" command.

orapki wallet create -wallet "/home/oracle/wallet" -pwd "MyPassword1" -auto_login_local

Add the following passwords to the wallet:

oracle.dbsecurity.sysPassword: SYS user password

oracle.dbsecurity.systemPassword: SYSTEM user password

oracle.dbsecurity.pdbAdminPassword: Pluggable database (PDB) administrator password

mkstore -wrl ~/wallet -createEntry oracle.dbsecurity.systemPassword
Oracle Secret Store Tool Release 20.0.0.0.0 - Production
Version 21.0.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:

mkstore -wrl ~/wallet -createEntry oracle.dbsecurity.systemPassword
 Oracle Secret Store Tool Release 20.0.0.0.0 - Production
Version 21.0.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:

mkstore -wrl ~/wallet -createEntry oracle.dbsecurity.pdbAdminPassword
Oracle Secret Store Tool Release 20.0.0.0.0 - Production
Version 21.0.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:

mkstore -wrl ~/wallet -list
Oracle Secret Store Tool Release 20.0.0.0.0 - Production
Version 21.0.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Oracle Secret Store entries:
oracle.dbsecurity.sysPassword
oracle.dbsecurity.systemPassword
oracle.dbsecurity.pdbAdminPassword

Now lets create the database with a PDB

dbca -silent -createDatabase -gdbName WCDB -templateName joords_db.dbc \
  -useWalletForDBCredentials true -dbCredentialsWalletLocation ~/wallet \
  -datafileDestination /u02/data -useOMF true \
  -memoryMgmtType AUTO_SGA \
  -recoveryAreaDestination /u03/fra  \
  -totalMemory 4096 \
  -createAsContainerDatabase true \
  -numberOfPDBs 1 \
  -pdbName joords \
  -useLocalUndoForPDBs true


Prepare for db operation
10% complete
Copying database files
40% complete
Creating and starting Oracle instance
42% complete
....
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/WCDB/WCDB.log" for further details.

EASY CONNECT PLUS - EZCONNECT

noreply@blogger.com (joordsblog) — Sun, 19 Jul 2020 19:00:00 +0000

Oracle Database 19c introduces a significantly enhanced connection string syntax called Easy Connect Plus.

By reducing the need for external configuration files, Easy Connect Plus makes it easier to use features such as TLS connections, wallets, load balancing, connection timeouts, and to tune network buffer sizes.

Easy Connect Plus can be used in JDBC and .NET applications. It is also usable in applications and drivers built on the C language Oracle Call Interface (OCI) such as the Oracle Database drivers for Python, Node.js, PHP, Ruby, and Go.

Easy connect background

An Easy Connect string includes a host name, an optional port, and the database service name:

database_host[:port][/[service_name]

Easy connect plus syntax

The Oracle Database 19c enhanced Easy Connect Plus syntax is a superset of Easy Connect that supports more connection options. The syntax is available in Oracle Database drivers (JDBC, ODP.Net, cx_Oracle, node-oracledb etc) that use Oracle Client 19c and connect to Oracle Database 11.2 or later.

The Oracle Database 19c Easy Connect PLUS syntax is

[[protocol:]//]host1{,host2}[:port1]{,host2:port2}[/[service_name]
[:server_type][/instance_name]][?parameter_name=value{&parameter_name=value}]

For more information read:

Oracle-Net-19c-Easy-Connect-Plus

Understanding the Easy Connect Naming Method

Examples