Oracle 23c AI introduces new V$ views to diagnose Data Pump performance issues. These views help monitor and troubleshoot Data Pump activities by providing detailed insights into job execution, resource usage, and potential bottlenecks.
Key V$ Views:
(G)V$DATAPUMP_PROCESS_INFO: Provides information about active Data Pump processes
(G)V$DATAPUMP_PROCESSWAIT_INFO: Details the waits encountered by Data Pump processes
(G)V$DATAPUMP_SESSIONWAIT_INFO: Provides information about session waits related to Data Pump operations.
These views enable administrators to efficiently manage and optimize Data Pump tasks. The outputs will be needed, along with the DataPump job logs, traces and other job specific info for diagnostic purpose.
(G)V$DATAPUMP_PROCESS_INFO
SQL> desc gv$datapump_process_info;Name Null? Type---------------------------INST_ID NUMBERCUR_DATE VARCHAR2(19)PROGRAM VARCHAR2(84)SESSIONID NUMBERSTATUS VARCHAR2(8)USERNAME VARCHAR2(128)JOBNAME VARCHAR2(128)SPID VARCHAR2(24)SERIALNUMBER NUMBERPROCESSID NUMBERCON_ID NUMBERSample output:select * from v$datapump_process_info;CUR_DATE PROGRAM SESSIONID STATUS USERNAME JOBNAME SPID SERIALNUMBER PROCESSID CON_ID------------------------------------------------------------------------------------------------------2023-01-0913:56:07 ude@orcl (TNS V1-V3) 42 ACTIVE SYSTEM SYS_EXPORT_FULL_01 3891480118297002023-01-0913:56:07 oracle@orcl (DW00) 48 ACTIVE SYSTEM SYS_EXPORT_FULL_01 3891500362448202023-01-0913:56:07 oracle@orcl (DM00) 149 ACTIVE SYSTEM SYS_EXPORT_FULL_01 389149442966790
Where <xx> is the version of PostgreSQL you are installing. For example, if you are installing version 16, the package name would be postgresql16-server postgresql16-contrib.
NetworkManager is a utility in Linux that simplifies the management of network connections and devices. It provides both command-line and graphical interfaces for configuring and controlling network settings. Two of the key tools associated with NetworkManager are nmtui and nmcli.
1. NetworkManager (nmcli):
nmcli Overview:
nmcli is the command-line client for NetworkManager, offering a powerful and flexible way to manage network connections in a text-based environment.
Features:
Connection Management:nmcli allows users to create, edit, activate, and deactivate network connections, including wired, wireless, and virtual connections.
Device Status: Users can check the status and details of network devices, such as their IP addresses, MAC addresses, and connection profiles.
Profile Management:nmcli enables users to manage connection profiles, modify settings, and prioritize connections.
Usage Examples:
Displaying all available connections:
nmcli connection show
Activating a specific connection:
nmcli connection up ConnectionName
Displaying details of a specific device:
nmcli device show DeviceName
2. NetworkManager TUI (nmtui):
nmtui Overview:
nmtui stands for NetworkManager Text User Interface. It is a user-friendly, console-based interface that provides a simple and interactive way to manage network connections.
Features:
Intuitive Menu System:nmtui presents a menu-driven interface that allows users to navigate through various options to configure and control network settings.
Connection Management: Users can easily create, edit, activate, and deactivate network connections using the interactive menus.
Device Configuration:nmtui provides options to configure settings for specific network devices, such as Ethernet or Wi-Fi.
Usage Examples:
Launching the nmtui interface:
nmtui
Navigating through the menus to configure a new connection or modify an existing one.
Activating or deactivating a connection using the on-screen options.
3. Advantages of NetworkManager, nmtui, and nmcli:
User-Friendly Interfaces: Both nmtui and nmcli provide interfaces that cater to users with different preferences – interactive and menu-driven (nmtui) or command-line and scriptable (nmcli).
Versatility: NetworkManager supports a wide range of connection types and devices, making it suitable for various networking scenarios.
Consistency:nmtui and nmcli provide consistent interfaces across different Linux distributions, promoting a standardized approach to network management.
Scripting and Automation:nmcli allows users to automate network-related tasks by scripting commands, making it a powerful tool for system administrators and advanced users.
CentOS 7 and Oracle Linux 8 are both popular Linux distributions that serve different purposes within the open-source community.
CentOS 7:
1. Origin and Community-driven:
CentOS (Community ENTerprise Operating System) is a free and open-source Linux distribution derived from the sources of Red Hat Enterprise Linux (RHEL).
CentOS 7, based on RHEL 7, was known for its stability, long-term support, and compatibility with RHEL packages.
Oracle Linux 8:
Origin and Enterprise Focus:
Oracle Linux is an open-source Linux distribution sponsored by Oracle Corporation. Oracle Linux 8 is based on the source code of RHEL 8, emphasizing enterprise features and performance.
Transition from CentOS 7 to Oracle Linux 8:
This guide explains how to combine a software upgrade and migration into a single process, taking you from CentOS 7 directly to Oracle Linux 8. It demonstrates using the open-source projects Leapp and Elevate to achieve this streamlined migration and upgrade in one go.
The first step is to get your existing CentOS 7 system updated to latest packages release available and then reboot. To do this, please execute following step:
#update YUM packageyum update -y#rebootinit 6
Installing the “elevate” release RPM is necessary to access and utilize the Elevate utilities.
Before upgrading, use the preupgrade check to assess your system’s readiness. This analysis will highlight potential conflicts without making any changes. The report files and debug log it creates offer valuable insights into your system’s configuration and possible upgrade challenges. To initiate this process, simply run the leapp preupgrade command.
leapp preupgrade
Edit /var/log/leapp/answerfile and uncomment Confirm and add TRUE as answer.
leapp upgradereboot
Post-Migration Steps:
Verify Successful Migration:
Once the system reboots, log in and confirm that the operating system is now Oracle Linux 8.
Review Leftover Packages:
Check for any remaining CentOS 7 packages that might not have been migrated automatically. You can then decide whether to remove them manually or update them to their Oracle Linux 8 equivalents.
Examine Upgrade Logs:
Finally, review the upgrade logs to ensure the migration process completed successfully and identify any potential issues that require attention.
SRVCTL, short for Server Control Utility, is a command-line tool, used to manage various entities in an Oracle Cluster environment, including databases, instances, listeners, services, and more. It allows you to perform various tasks like starting, stopping, modifying.
# /u01/app/oragrid/product/19.0.0/grid/rdbms/install/rootadd_rdbms.shIf this is a GI Home, astherootuserexecute:# /u01/app/oragrid/product/19.0.0/grid/crs/install/rootcrs.sh -postpatchIf this is an Oracle Restart Home, astherootuserexecute:# /u01/app/oragrid/product/19.0.0/grid/crs/install/roothas.sh -postpatch
After this just login to the user you want to login the database and set the environment by .oraenv and providing the SID.
[root@sql ~]# su - test
Last login: Tue Aug 22 11:53:14 UTC 2023 on pts/0
[test@sql ~]$
[test@sql ~]$
[test@sql ~]$
[test@sql ~]$ . oraenv
ORACLE_SID = [test] ?
ORACLE_BASE environment variable is not being set since this
information is not available for the current user ID test.
You can set ORACLE_BASE manually if it is required.
Resetting ORACLE_BASE to its previous value or ORACLE_HOME
The Oracle base has been set to /u01/app/oracle/product/19.3/db_home
[test@sql ~]$
[test@sql ~]$
[test@sql ~]$ sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 22 11:53:47 2023
Version 19.20.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Enter user-name: system
Enter password:
Last Successful login time: Tue Aug 22 2023 09:04:05 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
SQL>
Oracle Database 19c standalone patching involves applying patches and updates to an existing Oracle Database 19c installation that is not part of an Oracle Real Application Clusters (RAC) configuration.
High-Level steps for applying the patching
Check current version of Opatch Tool
Upgrade the Opatch utility
Take the backup of ORACLE_HOME
Shutdown the Database and Listener
Apply PSU patch on ORACLE_HOME
Execute Post installation Scripts (datapatch)
Startup the Database and Listener
Check the dba_registry_sqlpatch .
Check current version of Opatch Tool
[oracle@sql ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.17
OPatch succeeded.
Move the current OPatch tool as backup
[oracle@sql db_home]$ mv OPatch/ OPatch_old
unzip the new OPatch tool to the ORACLE_HOME location
Check for any potential conflicts between the currently installed interim patches and the patch you’re planning to install.
[oracle@sql 35320081]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.39
Copyright (c) 2023, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/19.3/db_home
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/19.3/db_home/oraInst.loc
OPatch version : 12.2.0.1.39
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.3/db_home/cfgtoollogs/opatch/opatch2023-08-21_15-27-06PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
[oracle@sql 35320081]$
Make sure to stop all services running from the Oracle home where the patching is taking place. This includes shutting down any databases and the listener that are associated with the same ORACLE_HOME as the patch being applied.
[oracle@sql 35320081]$ lsnrctl stop
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-AUG-2023 15:39:44
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sql.dbace.in)(PORT=1521)))
The command completed successfully
[oracle@sql 35320081]$
[oracle@sql 35320081]$
[oracle@sql 35320081]$
[oracle@sql 35320081]$
[oracle@sql 35320081]$
[oracle@sql 35320081]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 21 15:39:50 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
[oracle@sql 35320081]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 21 16:22:24 2023
Version 19.20.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2415917856 bytes
Fixed Size 8928032 bytes
Variable Size 520093696 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7847936 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
RunPerform the necessary post-patch procedures and execute the “datapatch” command.
[oracle@sql 35320081]$ cd $ORACLE_HOME/OPatch
[oracle@sql OPatch]$
[oracle@sql OPatch]$
[oracle@sql OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.20.0.0.0 Production on Mon Aug 21 16:25:43 2023
Copyright (c) 2012, 2023, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_13350_2023_08_21_16_25_43/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
No interim patches found
Current state of release update SQL patches:
Binary registry:
19.20.0.0.0 Release_Update 230715022800: Installed
SQL registry:
Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 21-AUG-23 08.58.21.281567 AM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
No interim patches need to be rolled back
Patch 35320081 (Database Release Update : 19.20.0.0.230718 (35320081)):
Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.20.0.0.0 Release_Update 230715022800
No interim patches need to be applied
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...done
Patch 35320081 apply: SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/35320081/25314491/35320081_apply_TEST_2023Aug21_16_26_19.log (no errors)
SQL Patching tool complete on Mon Aug 21 16:33:05 2023
[oracle@sql OPatch]$
Validate the patches applied to the database
SQL>
SET PAGES 55
SET LINESIZE 601
COLUMN ACTION_TIME FORMAT A21
COLUMN ACTION FORMAT A11
COLUMN STATUS FORMAT A11
COLUMN DESCRIPTION FORMAT A55
COLUMN VERSION FORMAT A11
COLUMN BUNDLE_SERIES FORMAT A11
SELECT TO_CHAR(ACTION_TIME, 'DD-MON-YYYY HH24:MI:SS') AS ACTION_TIME, PATCH_TYPE, ACTION,STATUS,DESCRIPTION, SOURCE_VERSION,TARGET_VERSION, PATCH_ID FROM SYS.DBA_REGISTRY_SQLPATCH ORDER BY ACTION_TIME DESC;
PATCH_ID ACTION STATUS DESCRIPTION ACTION_TIME
---------- ---------- ---------- ---------------------------------------- --------------------
29517242 APPLY SUCCESS Database Release Update : 19.3.0.0.19041 21-AUG-23 08.58.21.2
6 (29517242) 81567 AM
35320081 APPLY SUCCESS Database Release Update : 19.20.0.0.2307 21-AUG-23 04.33.05.4
18 (35320081) 02900 PM
Patching is done, start the Listener and services
[oracle@sql OPatch]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-AUG-2023 16:40:19
Copyright (c) 1991, 2023, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19.3/db_home/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.3/db_home/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/sql/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sql.dbace.in)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sql.dbace.in)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 21-AUG-2023 16:40:19
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.3/db_home/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/sql/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sql.dbace.in)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@sql OPatch]$
Rollback
Check existing patches
Shutdown Database and Listener
Rollback patch
$ORACLE_HOME/OPatch/opatch rollback -id 35320081
Start the Database and Listener
Rollback SQL changes (datapatch)
$ORACLE_HOME/OPatch/datapatch -verbose
Check Registry Status
sqlplus / as sysdba
SET LINESIZE 500
SET PAGESIZE 1000
SET SERVEROUT ON
SET LONG 2000000
COLUMN action_time FORMAT A25
COLUMN action FORMAT A10
COLUMN bundle_series FORMAT A4
COLUMN comments FORMAT A30
COLUMN description FORMAT A40
COLUMN namespace FORMAT A20
COLUMN status FORMAT A10
COLUMN version FORMAT A10
SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,
action,
status,
description,
version,
patch_id,
bundle_series
FROM sys.dba_registry_sqlpatch
ORDER by action_time;
