Quels sont les produits, composants, options, packs, advisors, features... sur ma base? - What is installed on my base?
Introduction
Est-ce qu'un jour vous vous êtes demandé ce qui était installé sur votre base Oracle? J'entends par là : produits, composants, options, packs, advisors, features ...? Si oui, vous aurez compris qu'Oracle ne donne pas toutes ces réponses dans une seule vue mais dans plusieurs et c'est ce que nous allons voir.
Points d'attention
Aucun.
Base de tests
N'importe quelle base Oracle.
Exemples
============================================================================================
Les composants
============================================================================================
Je mettrais en bleu gras les infos qui me semblent les plus intéressantes.
Voici déjà ce qu'on a comme information en se connectant à la base avec SQL*Plus.
[oracle@vbgeneric ~]$ sqlplus SYS as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Nov 24 13:48:20 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Ensuite on utilise la vue V$VERSION pour voir les composants installés.
"V$VERSION displays version numbers of core library components in the Oracle Database".
SQL> select banner from V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
Une autre vue, PRODUCT_COMPONENT_VERSION, donne aussi les mêmes infos sur les composants.
"PRODUCT_COMPONENT_VERSION contains version and status information for component products."
SQL> select * from PRODUCT_COMPONENT_VERSION order by PRODUCT;
PRODUCT VERSION STATUS
---------------------------------------- -------------------- --------------------
NLSRTL 12.2.0.1.0 Production
Oracle Database 12c Enterprise Edition 12.2.0.1.0 64bit Production
PL/SQL 12.2.0.1.0 Production
TNS for Linux: 12.2.0.1.0 Production
DBA_REGISTRY parle d'autres composants, ceux chargés dans la base.
"DBA_REGISTRY displays information about the components loaded into the database."
SQL> select COMP_NAME, STATUS, STARTUP from DBA_REGISTRY order by 1;
COMP_NAME STATUS STARTUP
-------------------------------------------------- -------------------- --------
JServer JAVA Virtual Machine VALID
OLAP Analytic Workspace VALID
Oracle Database Catalog Views VALID
Oracle Database Java Packages VALID
Oracle Database Packages and Types VALID
Oracle Database Vault VALID
Oracle Label Security VALID
Oracle Multimedia VALID
Oracle OLAP API VALID
Oracle Real Application Clusters OPTION OFF
Oracle Text VALID
Oracle Workspace Manager VALID
Oracle XDK VALID
Oracle XML Database VALID
Spatial VALID
15 rows selected.
============================================================================================
Les advisors
============================================================================================
Pour lister les advisors installés, ces produits qui vous donnent des conseils, il faut interroger DBA_ADVISOR_DEFINITIONS.
"DBA_ADVISOR_DEFINITIONS displays the properties of all advisors in the database."
SQL> select ADVISOR_NAME from DBA_ADVISOR_DEFINITIONS order by 1;
ADVISOR_NAME
--------------------------------------------------
ADDM
Compression Advisor
SPM Evolve Advisor
SQL Access Advisor
SQL Performance Analyzer
SQL Repair Advisor
SQL Tuning Advisor
SQL Workload Manager
Segment Advisor
Statistics Advisor
Tune MView
Undo Advisor
12 rows selected.
============================================================================================
Les occupants
============================================================================================
Savez-vous ce qu'est un occupant dans l'univers Oracle?"V$SYSAUX_OCCUPANTS displays SYSAUX tablespace occupant information."
select OCCUPANT_NAME, OCCUPANT_DESC from v$sysaux_occupants order by OCCUPANT_NAME;
OCCUPANT_NAME OCCUPANT_DESC
----------------------------------------------------------------
AO Analytical Workspace Object Table
AUDIT_TABLES DB audit tables
AUDSYS AUDSYS schema objects
AUTO_TASK Automated Maintenance Tasks
EM Enterprise Manager Repository
EM_MONITORING_USER Enterprise Manager Monitoring User
EXPRESSION_FILTER Expression Filter System
JOB_SCHEDULER Unified Job Scheduler
LOGMNR LogMiner
LOGSTDBY Logical Standby
ORDIM Oracle Multimedia ORDSYS Components
ORDIM/ORDDATA Oracle Multimedia ORDDATA Components
ORDIM/ORDPLUGINS Oracle Multimedia ORDPLUGINS Components
ORDIM/SI_INFORMTN_SCHEMA Oracle Multimedia SI_INFORMTN_SCHEMA Components
PL/SCOPE PL/SQL Identifier Collection
SDO Oracle Spatial
SM/ADVISOR Server Manageability - Advisor Framework
SM/AWR Server Manageability - Automatic Workload Repository
SM/OPTSTAT Server Manageability - Optimizer Statistics History
SM/OTHER Server Manageability - Other Components
SMON_SCN_TIME Transaction Layer - SCN to TIME mapping
SQL_MANAGEMENT_BASE SQL Management Base Schema
STATSPACK Statspack Repository
STREAMS Oracle Streams
TEXT Oracle Text
TSM Oracle Transparent Session Migration User
ULTRASEARCH Oracle Ultra Search
ULTRASEARCH_DEMO_USER Oracle Ultra Search Demo User
WM Workspace Manager
XDB XDB
XSAMD OLAP Catalog
XSOQHIST OLAP API History Tables
============================================================================================
Les options et fonctionnalités
============================================================================================
Pour les options et fonctionnalités, c'est V$OPTION qu'il faut interroger.
"V$OPTION lists database options and features. Typically, although not always, options must be separately purchased and installed, whereas features come with the product and are enabled based on the product that is running (Standard Edition, Enterprise Edition, or Personal Oracle)."
SQL> select PARAMETER, VALUE from V$OPTION order by PARAMETER;
PARAMETER VALUE
-------------------------------------------------- ------------------------------
ASM Proxy Instance FALSE
Active Data Guard TRUE
Adaptive Execution Plans TRUE
Advanced Analytics TRUE
Advanced Compression TRUE
Advanced Index Compression TRUE
Advanced replication TRUE
Application Role TRUE
Automatic Data Optimization TRUE
Automatic Storage Management FALSE
Backup Encryption TRUE
Basic Compression TRUE
Bit-mapped indexes TRUE
Block Change Tracking TRUE
Block Media Recovery TRUE
Cache Fusion Lock Accelerator TRUE
Change Data Capture TRUE
Coalesce Index TRUE
Connection multiplexing TRUE
Connection pooling TRUE
Cross Transportable Backups TRUE
DICOM TRUE
Data Mining TRUE
Data Redaction TRUE
Database queuing TRUE
Database resource manager TRUE
Deferred Segment Creation TRUE
Duplexed backups TRUE
Enterprise User Security TRUE
Exadata Discovery TRUE
Export transportable tablespaces TRUE
Fast-Start Fault Recovery TRUE
File Mapping TRUE
Fine-grained Auditing TRUE
Fine-grained access control TRUE
Flashback Data Archive TRUE
Flashback Database TRUE
Flashback Table TRUE
Global Data Services TRUE
Heat Map TRUE
I/O Server FALSE
In-Memory Aggregation TRUE
In-Memory Column Store TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Java TRUE
Join index TRUE
Managed Standby TRUE
Management Database FALSE
Materialized view rewrite TRUE
OLAP TRUE
OLAP Window Functions TRUE
Objects TRUE
Online Index Build TRUE
Online Redefinition TRUE
Oracle Data Guard TRUE
Oracle Database Vault FALSE
Oracle Label Security FALSE
Parallel backup and recovery TRUE
Parallel execution TRUE
Parallel load TRUE
Partitioning TRUE
Plan Stability TRUE
Point-in-time tablespace recovery TRUE
Privilege Analysis TRUE
Proxy authentication/authorization TRUE
Real Application Clusters FALSE
Real Application Security TRUE
Real Application Testing TRUE
Result Cache TRUE
SQL Plan Management TRUE
Sample Scan TRUE
SecureFiles Encryption TRUE
Server Flash Cache TRUE
Snapshot time recovery TRUE
Spatial TRUE
Streams Capture TRUE
Table Clustering TRUE
Transparent Application Failover TRUE
Transparent Data Encryption TRUE
Transparent Sensitive Data Protection TRUE
Trial Recovery TRUE
Unified Auditing FALSE
Unused Block Compression TRUE
XStream TRUE
Zone Maps TRUE
86 rows selected.
Pour savoir si ces fonctionnalités dont certaines sont payantes, ont été utilisées, utilisez la vue DBA_FEATURE_USAGE_STATISTICS.
"DBA_FEATURE_USAGE_STATISTICS displays information about database feature usage statistics."
SQL> select NAME, CURRENTLY_USED, LAST_USAGE_DATE from DBA_FEATURE_USAGE_STATISTICS order by 1
NAME CURRE LAST_USAG
---------------------------------------------------------------------- ----- ---------
ACFS FALSE
ACFS Encryption FALSE
ACFS Snapshot FALSE
ADDM FALSE
ASM Filter Driver FALSE
ASO native encryption and checksumming FALSE
AWR Baseline FALSE
AWR Baseline Template FALSE
AWR Report FALSE
Active Data Guard - Real-Time Query on Physical Standby FALSE
Adaptive Plans TRUE 08-OCT-17
Advanced Index Compression FALSE
Advanced Replication FALSE
Application Express FALSE
Automatic Maintenance - Optimizer Statistics Gathering TRUE 08-OCT-17
Automatic Maintenance - SQL Tuning Advisor TRUE 08-OCT-17
Automatic Maintenance - Space Advisor TRUE 08-OCT-17
Automatic Memory Tuning FALSE
Automatic Reoptimization TRUE 08-OCT-17
Automatic SGA Tuning FALSE
Automatic SQL Execution Memory TRUE 08-OCT-17
Automatic SQL Tuning Advisor TRUE 08-OCT-17
Automatic Segment Space Management (system) TRUE 08-OCT-17
Automatic Segment Space Management (user) FALSE
Automatic Storage Management FALSE
Automatic Undo Management TRUE 08-OCT-17
Automatic Workload Repository FALSE
BA Owner FALSE
Backup BASIC Compression FALSE
Backup BZIP2 Compression FALSE
Backup Encryption FALSE
Backup HIGH Compression FALSE
Backup LOW Compression FALSE
Backup MEDIUM Compression FALSE
Backup Rollforward FALSE
Backup ZLIB Compression FALSE
Backup and Restore of plugged database FALSE
Baseline Adaptive Thresholds FALSE
Baseline Static Computations FALSE
Bigfile Tablespace FALSE
Block Media Recovery FALSE
CONVERT command FALSE
CSSCAN FALSE
Change Data Capture FALSE
Change-Aware Incremental Backup FALSE
Character Semantics FALSE
Character Set TRUE 08-OCT-17
Client Identifier FALSE
Cloud DB with EHCC FALSE
Clusterwide Global Transactions FALSE
Compression Advisor FALSE
Concurrent Statistics Gathering FALSE
Container Usage FALSE
Cross-Platform Backups FALSE
Crossedition Triggers FALSE
DBFS Content FALSE
DBFS HS FALSE
DBFS SFS FALSE
DBMS_STATS Incremental Maintenance TRUE 08-OCT-17
Data Guard FALSE
Data Mining FALSE
Data Recovery Advisor FALSE
Data Redaction FALSE
Database Migration Assistant for Unicode FALSE
Database Replay: Workload Capture FALSE
Database Replay: Workload Replay FALSE
Database Resident Connection Pooling (DRCP) FALSE
Deferred Open Read Only FALSE
Deferred Segment Creation TRUE 08-OCT-17
Direct NFS FALSE
Duplicate Db from Active Db using BackupSet FALSE
Dynamic SGA FALSE
EM Cloud Control FALSE
EM Express FALSE
EM Performance Page FALSE
Editioning Views FALSE
Editions FALSE
Encrypted Tablespaces FALSE
Exadata FALSE
Extensibility FALSE
File Mapping FALSE
Fine Grained Audit FALSE
Flashback Data Archive FALSE
Flashback Database FALSE
Flex ASM FALSE
GDS Catalog FALSE
Gateways FALSE
Global Data Services FALSE
GoldenGate FALSE
HeapCompression FALSE
Heat Map FALSE
Hybrid Columnar Compression FALSE
Hybrid Columnar Compression Conventional Load FALSE
Hybrid Columnar Compression Row Level Locking FALSE
INSTANT RESTORE command FALSE
In-Memory ADO Policies FALSE
In-Memory Aggregation FALSE
In-Memory Column Store FALSE
In-Memory Distribute For Service (User Defined) FALSE
In-Memory Expressions FALSE
In-Memory FastStart FALSE
In-Memory Join Groups FALSE
Index Organized Tables FALSE
Information Lifecycle Management FALSE
Instance Caging FALSE
Internode Parallel Execution FALSE
JSON FALSE
Job Scheduler FALSE
LOB FALSE
Label Security FALSE
Locally Managed Tablespaces (system) TRUE 08-OCT-17
Locally Managed Tablespaces (user) TRUE 08-OCT-17
Locator FALSE
Logfile Multiplexing FALSE
Long-term Archival Backup FALSE
MTTR Advisor FALSE
Materialized Views (User) FALSE
Messaging Gateway FALSE
Multi Section Backup FALSE
Multiple Block Sizes TRUE 08-OCT-17
OLAP - Analytic Workspaces FALSE
OLAP - Cubes FALSE
Object FALSE
Online Move Datafile FALSE
Online Redefinition FALSE
Oracle Advanced Network Compression Service FALSE
Oracle Database Vault FALSE
Oracle In-Database Hadoop FALSE
Oracle Java Virtual Machine (system) TRUE 08-OCT-17
Oracle Java Virtual Machine (user) FALSE
Oracle Managed Files FALSE
Oracle Multimedia FALSE
Oracle Multimedia DICOM FALSE
Oracle Multitenant TRUE 08-OCT-17
Oracle Secure Backup FALSE
Oracle Text FALSE
Oracle Utility Datapump (Export) FALSE
Oracle Utility Datapump (Import) FALSE
Oracle Utility External Table (ORACLE_BIGSQL) FALSE
Oracle Utility External Table (ORACLE_DATAPUMP) FALSE
Oracle Utility External Table (ORACLE_LOADER) TRUE 08-OCT-17
Oracle Utility Metadata API FALSE
Oracle Utility SQL Loader (Direct Path Load) FALSE
PDB I/O Rate Limits FALSE
PL/SQL Native Compilation FALSE
Parallel SQL DDL Execution FALSE
Parallel SQL DML Execution FALSE
Parallel SQL Query Execution TRUE 08-OCT-17
Partitioning (system) TRUE 08-OCT-17
Partitioning (user) FALSE
Pillar Storage FALSE
Pillar Storage with EHCC FALSE
Privilege Capture FALSE
Quality of Service Management FALSE
RMAN - Disk Backup FALSE
RMAN - Tape Backup FALSE
Read Only Tablespace FALSE
Real Application Cluster One Node FALSE
Real Application Clusters (RAC) FALSE
Real Application Security FALSE
Real-Time SQL Monitoring TRUE 08-OCT-17
Recover Table FALSE
Recover Until Snapshot FALSE
Recovery Area TRUE 08-OCT-17
Recovery Manager (RMAN) FALSE
Resource Manager FALSE 23-SEP-17
Restore Point FALSE
Result Cache TRUE 08-OCT-17
Rules Manager FALSE
SPM Evolve Advisor FALSE
SQL Access Advisor FALSE
SQL Monitoring and Tuning pages FALSE
SQL Performance Analyzer FALSE
SQL Plan Directive TRUE 08-OCT-17
SQL Plan Management FALSE
SQL Profile FALSE
SQL Repair Advisor FALSE
SQL Tuning Advisor FALSE
SQL Tuning Set (system) FALSE
SQL Tuning Set (user) FALSE
SQL Workload Manager FALSE
SecureFile Compression (system) FALSE
SecureFile Compression (user) FALSE
SecureFile Deduplication (system) FALSE
SecureFile Deduplication (user) FALSE
SecureFile Encryption (system) FALSE
SecureFile Encryption (user) FALSE
SecureFiles (system) TRUE 08-OCT-17
SecureFiles (user) TRUE 08-OCT-17
Segment Advisor (user) FALSE
Segment Maintenance Online Compress FALSE
Segment Shrink FALSE
Semantics/RDF FALSE
Server Flash Cache FALSE
Server Parameter File TRUE 08-OCT-17
Services FALSE
Shard Database FALSE
Sharding Catalog FALSE
Shared Server FALSE
Spatial FALSE
Statistics Advisor FALSE
Streams (system) FALSE
Streams (user) FALSE
Sun ZFS with EHCC FALSE
TRANSPORT TABLESPACE command FALSE
Thin Provisioning FALSE
Traditional Audit TRUE 08-OCT-17
Transparent Data Encryption FALSE
Transparent Gateway FALSE
Transparent Sensitive Data Protection FALSE
Transportable Tablespace FALSE
Tune MView FALSE
Undo Advisor FALSE
Unified Audit TRUE 08-OCT-17
Very Large Memory FALSE
Virtual Private Database (VPD) FALSE
Workspace Manager FALSE
XDB FALSE
XStream In FALSE
XStream Out FALSE
XStream Streams FALSE
ZFS Storage FALSE
Zone maps FALSE
223 rows selected.
============================================================================================
Les patchs
============================================================================================
Et on termine par les patchs avec l'utilitaire opatch :-)
Il se trouve sous $ORACLE_HOME/OPatch. Dans cet exemple, ma base n'a aucun patch d'installé.
[oracle@vbgeneric ~]$ cd $ORACLE_HOME/OPatch
[oracle@vbgeneric OPatch]$ pwd
/u01/app/oracle/product/12.2/db_1/OPatch
[oracle@vbgeneric OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2017, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/12.2/db_1
Central Inventory : /u01/installervb
from : /u01/app/oracle/product/12.2/db_1/oraInst.loc
OPatch version : 12.2.0.1.6
OUI version : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2/db_1/cfgtoollogs/opatch/opatch2017-11-25_06-44-03AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/12.2/db_1/cfgtoollogs/opatch/lsinv /lsinventory2017-11-25_06-44-03AM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: localhost
ARU platform id: 0
ARU platform description::
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)