Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


terça-feira, 20 de fevereiro de 2018

Verificando a versão, features e options instaladas no banco de dados Oracle

Por Eduardo Legatti

Olá,

Quando instalamos o Oracle e configuramos uma instância do banco de dados Oracle, podemos verificar posteriormente a sua versão, quais "options" e "features" do banco de dados foram instaladas e usadas. Segue abaixo 4 views do dicionário de dados que geralmente utilizo para verificar tais informações em um banco de dados Oracle.

  • V$VERSION: Lista a versão e edição do banco de dados Oracle.
  • V$OPTION: Lista as options e features instaladas no banco de dados. Geralmente as options são licenciadas separadamente e as features costumam já vir no produto que foi instalado (Standard Edition, Enterprise Edition).
  • DBA_REGISTRY: Lista os componentes que foram instalados no banco de dados.
  • DBA_FEATURE_USAGE_STATISTICS: Lista as features e as estatísticas de uso. Essas estatísticas são geradas aptravés de uma procedure não documentada do Oracle.
Segue abaixo um exemplo de saída das views acima executadas em um banco de dados Oracle Standard Edition e um Oracle Enterprise Edition.

-- --------------------------
-- Oracle Standard Edtion  --
-- --------------------------

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select * from v$option order by 2,1;

PARAMETER                                                        VALUE
---------------------------------------------------------------- ---------------
Active Data Guard                                                FALSE
Advanced Compression                                             FALSE
Advanced replication                                             FALSE
Application Role                                                 FALSE
Automatic Storage Management                                     FALSE
Backup Encryption                                                FALSE
Basic Compression                                                FALSE
Bit-mapped indexes                                               FALSE
Block Change Tracking                                            FALSE
Block Media Recovery                                             FALSE
Change Data Capture                                              FALSE
Data Mining                                                      FALSE
Data Redaction                                                   FALSE
Database resource manager                                        FALSE
Deferred Segment Creation                                        FALSE
Duplexed backups                                                 FALSE
Enterprise User Security                                         FALSE
Export transportable tablespaces                                 FALSE
Fast-Start Fault Recovery                                        FALSE
File Mapping                                                     FALSE
Fine-grained access control                                      FALSE
Fine-grained Auditing                                            FALSE
Flashback Database                                               FALSE
Flashback Table                                                  FALSE
Join index                                                       FALSE
Managed Standby                                                  FALSE
Materialized view rewrite                                        FALSE
OLAP                                                             FALSE
Online Index Build                                               FALSE
Online Redefinition                                              FALSE
Oracle Data Guard                                                FALSE
Oracle Database Vault                                            FALSE
Oracle Label Security                                            FALSE
Parallel backup and recovery                                     FALSE
Parallel execution                                               FALSE
Partitioning                                                     FALSE
Point-in-time tablespace recovery                                FALSE
Real Application Clusters                                        FALSE
Real Application Testing                                         FALSE
Result Cache                                                     FALSE
SecureFiles Encryption                                           FALSE
Server Flash Cache                                               FALSE
Spatial                                                          FALSE
SQL Plan Management                                              FALSE
Streams Capture                                                  FALSE
Transparent Data Encryption                                      FALSE
Trial Recovery                                                   FALSE
Unused Block Compression                                         FALSE
Coalesce Index                                                   TRUE
Connection multiplexing                                          TRUE
Connection pooling                                               TRUE
Database queuing                                                 TRUE
DICOM                                                            TRUE
Flashback Data Archive                                           TRUE
Incremental backup and recovery                                  TRUE
Instead-of triggers                                              TRUE
Java                                                             TRUE
Objects                                                          TRUE
OLAP Window Functions                                            TRUE
Parallel load                                                    TRUE
Plan Stability                                                   TRUE
Proxy authentication/authorization                               TRUE
Sample Scan                                                      TRUE
Transparent Application Failover                                 TRUE
XStream                                                          TRUE

65 linhas selecionadas.

SQL> select name,version,detected_usages,description
  2    from DBA_FEATURE_USAGE_STATISTICS
  3   where rownum<=50 order by 1;

NAME                                               VERSION           DETECTED_USAGES DESCRIPTION
-------------------------------------------------- ----------------- --------------- ----------------------------------------------------------------------------------------------------------------------------------------
Automatic Memory Tuning                            11.2.0.4.0                      0 Automatic Memory Tuning is enabled.
Automatic SGA Tuning                               11.2.0.4.0                     25 Automatic SGA Tuning is enabled.
Backup ZLIB Compression                            11.2.0.4.0                      0 ZLIB compressed backups are being used.
Character Semantics                                11.2.0.4.0                      0 Character length semantics is used in Oracle Database
Character Set                                      11.2.0.4.0                     25 Character set is used in Oracle Database
Client Identifier                                  11.2.0.4.0                      0 Application User Proxy Authentication: Client Identifier is used at this specific time.
Clusterwide Global Transactions                    11.2.0.4.0                      0 Clusterwide Global Transactions is being used.
Crossedition Triggers                              11.2.0.4.0                      0 Crossedition triggers is being used.
CSSCAN                                             11.2.0.4.0                      0 Oracle Database has been scanned at least once for character set:CSSCAN has been run at least once.
Data Guard                                         11.2.0.4.0                      0 Data Guard, a set of services, is being used to create, maintain, manage, and monitor one or more standby databases.
Data Mining                                        11.2.0.4.0                      0 There exist Oracle Data Mining models in the database.
Database Migration Assistant for Unicode           11.2.0.4.0                      0 Database Migration Assistant for Unicode has been used.
Deferred Segment Creation                          11.2.0.4.0                     25 Deferred Segment Creation is being used
Dynamic SGA                                        11.2.0.4.0                      0 The Oracle SGA has been dynamically resized through an ALTER SYSTEM SET statement.
Editioning Views                                   11.2.0.4.0                      0 Editioning views is being used.
Editions                                           11.2.0.4.0                      0 Editions is being used.
EM Database Control                                11.2.0.4.0                      0 EM Database Control Home Page has been visited at least once.
EM Grid Control                                    11.2.0.4.0                      0 EM Grid Control Database Home Page has been visited at least once.
EM Performance Page                                11.2.0.4.0                      0 EM Performance Page has been visited at least once.
Encrypted Tablespaces                              11.2.0.4.0                      0 Encrypted Tablespaces is enabled.
File Mapping                                       11.2.0.4.0                      0 File Mapping, the mechanism that shows a complete mapping of a file to logical volumes and physical devices, is being used.
Flashback Data Archive                             11.2.0.4.0                      0 Flashback Data Archive, a historical repository of changes to data contained in a table, is used
Flashback Database                                 11.2.0.4.0                      0 Flashback Database, a rewind button for the database, is enabled
Internode Parallel Execution                       11.2.0.4.0                      0 Internode Parallel Execution is being used.
Label Security                                     11.2.0.4.0                      0 Oracle Label Security, that enables label-based access control Oracle applications, is being used.
Locally Managed Tablespaces (system)               11.2.0.4.0                     25 There exists tablespaces that are locally managed in the database.
Locally Managed Tablespaces (user)                 11.2.0.4.0                     25 There exists user tablespaces that are locally managed in the database.
Messaging Gateway                                  11.2.0.4.0                      0 Messaging Gateway, that enables communication between non-Oracle messaging systems and Advanced Queuing (AQ), link configured.
MTTR Advisor                                       11.2.0.4.0                      0 Mean Time to Recover Advisor is enabled.
Multiple Block Sizes                               11.2.0.4.0                      0 Multiple Block Sizes are being used with this database.
OLAP - Analytic Workspaces                         11.2.0.4.0                      0 OLAP - the analytic workspaces stored in the database.
OLAP - Cubes                                       11.2.0.4.0                      0 OLAP - number of cubes in the OLAP catalog that are fully mapped and accessible by the OLAP API.
Oracle Database Vault                              11.2.0.4.0                      0 Oracle Database Vault is being used
Oracle Managed Files                               11.2.0.4.0                      0 Database files are being managed by Oracle.
Oracle Secure Backup                               11.2.0.4.0                      0 Oracle Secure Backup is used for backups to tertiary storage.
Oracle Text                                        11.2.0.4.0                      0 Oracle Text is being used - there is at least one oracle text index
Parallel SQL DDL Execution                         11.2.0.4.0                      0 Parallel SQL DDL Execution is being used.
Parallel SQL DML Execution                         11.2.0.4.0                      0 Parallel SQL DML Execution is being used.
Parallel SQL Query Execution                       11.2.0.4.0                      0 Parallel SQL Query Execution is being used.
Partitioning (system)                              11.2.0.4.0                     25 Oracle Partitioning option is being used - there is at least one partitioned object created.
Partitioning (user)                                11.2.0.4.0                      0 Oracle Partitioning option is being used - there is at least one user partitioned object created.
PL/SQL Native Compilation                          11.2.0.4.0                      0 PL/SQL Native Compilation is being used - there is at least one natively compiled PL/SQL library unit in the database.
Quality of Service Management                      11.2.0.4.0                      0 Quality of Service Management has been used.
Real Application Clusters (RAC)                    11.2.0.4.0                      0 Real Application Clusters (RAC) is configured.
Recovery Area                                      11.2.0.4.0                      0 The recovery area is configured.
Recovery Manager (RMAN)                            11.2.0.4.0                     24 Recovery Manager (RMAN) is being used to backup the database.
RMAN - Disk Backup                                 11.2.0.4.0                     24 Recovery Manager (RMAN) is being used to backup the database to disk.
RMAN - Tape Backup                                 11.2.0.4.0                      0 Recovery Manager (RMAN) is being used to backup the database to tape.
SQL Monitoring and Tuning pages                    11.2.0.4.0                      0 EM SQL Monitoring and Tuning pages has been visited at least once.
Very Large Memory                                  11.2.0.4.0                      0 Very Large Memory is enabled.

50 linhas selecionadas.

SQL> select comp_id,comp_name,version,status,modified,schema,procedure
  2    from dba_registry
  3   order by 1;

COMP_ID                        COMP_NAME                                          VERSION                        STATUS      MODIFIED             SCHEMA                         PROCEDURE
------------------------------ -------------------------------------------------- ------------------------------ ----------- -------------------- ------------------------------ ----------------------------------
CATALOG                        Oracle Database Catalog Views                      11.2.0.4.0                     VALID       20-JAN-2017 19:29:19 SYS                            DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATPROC                        Oracle Database Packages and Types                 11.2.0.4.0                     VALID       20-JAN-2017 19:29:19 SYS                            DBMS_REGISTRY_SYS.VALIDATE_CATPROC
CONTEXT                        Oracle Text                                        11.2.0.4.0                     VALID       20-JAN-2017 19:29:20 CTXSYS                         VALIDATE_CONTEXT
EM                             Oracle Enterprise Manager                          11.2.0.4.0                     VALID       12-JAN-2017 10:24:21 SYSMAN
OWM                            Oracle Workspace Manager                           11.2.0.4.0                     VALID       20-JAN-2017 19:29:20 WMSYS                          VALIDATE_OWM
XDB                            Oracle XML Database                                11.2.0.4.0                     VALID       20-JAN-2017 19:29:20 XDB                            DBMS_REGXDB.VALIDATEXDB

6 linhas selecionadas.


-- ----------------------------
-- Oracle Enterprise Edtion  --
-- ----------------------------

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select * from v$option order by 2,1;

PARAMETER                                                        VALUE
---------------------------------------------------------------- ---------------
Automatic Storage Management                                     FALSE
Data Mining                                                      FALSE
OLAP                                                             FALSE
Oracle Database Vault                                            FALSE
Oracle Label Security                                            FALSE
Real Application Clusters                                        FALSE
Real Application Testing                                         FALSE
Active Data Guard                                                TRUE
Advanced Compression                                             TRUE
Advanced replication                                             TRUE
Application Role                                                 TRUE
Backup Encryption                                                TRUE
Basic Compression                                                TRUE
Bit-mapped indexes                                               TRUE
Block Change Tracking                                            TRUE
Block Media Recovery                                             TRUE
Change Data Capture                                              TRUE
Coalesce Index                                                   TRUE
Connection multiplexing                                          TRUE
Connection pooling                                               TRUE
Database queuing                                                 TRUE
Database resource manager                                        TRUE
Deferred Segment Creation                                        TRUE
DICOM                                                            TRUE
Duplexed backups                                                 TRUE
Enterprise User Security                                         TRUE
Export transportable tablespaces                                 TRUE
Fast-Start Fault Recovery                                        TRUE
File Mapping                                                     TRUE
Fine-grained access control                                      TRUE
Fine-grained Auditing                                            TRUE
Flashback Data Archive                                           TRUE
Flashback Database                                               TRUE
Flashback Table                                                  TRUE
Incremental backup and recovery                                  TRUE
Instead-of triggers                                              TRUE
Java                                                             TRUE
Join index                                                       TRUE
Managed Standby                                                  TRUE
Materialized view rewrite                                        TRUE
Objects                                                          TRUE
OLAP Window Functions                                            TRUE
Online Index Build                                               TRUE
Online Redefinition                                              TRUE
Oracle Data Guard                                                TRUE
Parallel backup and recovery                                     TRUE
Parallel execution                                               TRUE
Parallel load                                                    TRUE
Partitioning                                                     TRUE
Plan Stability                                                   TRUE
Point-in-time tablespace recovery                                TRUE
Proxy authentication/authorization                               TRUE
Result Cache                                                     TRUE
Sample Scan                                                      TRUE
SecureFiles Encryption                                           TRUE
Server Flash Cache                                               TRUE
Spatial                                                          TRUE
SQL Plan Management                                              TRUE
Streams Capture                                                  TRUE
Transparent Application Failover                                 TRUE
Transparent Data Encryption                                      TRUE
Trial Recovery                                                   TRUE
Unused Block Compression                                         TRUE
XStream                                                          TRUE

64 linhas selecionadas.

SQL> select name,version,detected_usages,description
  2    from DBA_FEATURE_USAGE_STATISTICS
  3   where rownum<=50 order by 1;

NAME                                                                             VERSION           DETECTED_USAGES DESCRIPTION
-------------------------------------------------------------------------------- ----------------- --------------- ----------------------------------------------------------------------------------------------------------------------
Active Data Guard - Real-Time Query on Physical Standby                          11.2.0.3.0                      0 Active Data Guard real-time query is enabled on a physical standby
ADDM                                                                             11.2.0.3.0                     11 ADDM has been used.
Advanced Replication                                                             11.2.0.3.0                      0 Advanced Replication has been enabled.
ASO native encryption and checksumming                                           11.2.0.3.0                      0 ASO network native encryption and checksumming is being used.
Audit Options                                                                    11.2.0.3.0                    281 Audit options in use.
Automatic Maintenance - Optimizer Statistics Gathering                           11.2.0.3.0                    247 Automatic initiation of Optimizer Statistics Collection
Automatic Maintenance - Space Advisor                                            11.2.0.3.0                     52 Automatic initiation of Space Advisor
Automatic Maintenance - SQL Tuning Advisor                                       11.2.0.3.0                    210 Automatic initiation of SQL Tuning Advisor
Automatic Segment Space Management (system)                                      11.2.0.3.0                    281 Extents of locally managed tablespaces are managed automatically by Oracle.
Automatic Segment Space Management (user)                                        11.2.0.3.0                    281 Extents of locally managed user tablespaces are managed automatically by Oracle.
Automatic SQL Execution Memory                                                   11.2.0.3.0                    281 Sizing of work areas for all dedicated sessions (PGA) is automatic.
Automatic Storage Management                                                     11.2.0.3.0                      0 Automatic Storage Management has been enabled
Automatic Undo Management                                                        11.2.0.3.0                    281 Oracle automatically manages undo data using an UNDO tablespace.
Automatic Workload Repository                                                    11.2.0.3.0                      0 A manual Automatic Workload Repository (AWR) snapshot was taken in the last sample period.
AWR Baseline                                                                     11.2.0.3.0                      0 At least one AWR Baseline has been created by the user
AWR Baseline Template                                                            11.2.0.3.0                      0 At least one AWR Baseline Template has been created by the user
AWR Report                                                                       11.2.0.3.0                      9 At least one Workload Repository Report has been created by the user
Backup Encryption                                                                11.2.0.3.0                      0 Encrypted backups are being used.
Backup Rollforward                                                               11.2.0.3.0                      0 Backup Rollforward strategy is being used to backup the database.
Baseline Adaptive Thresholds                                                     11.2.0.3.0                      0 Adaptive Thresholds have been configured.
Baseline Static Computations                                                     11.2.0.3.0                      0 Static baseline statistics have been computed.
Change-Aware Incremental Backup                                                  11.2.0.3.0                    120 Track blocks that have changed in the database.
Character Semantics                                                              11.2.0.3.0                      0 Character length semantics is used in Oracle Database
Character Set                                                                    11.2.0.3.0                    281 Character set is used in Oracle Database
Client Identifier                                                                11.2.0.3.0                      0 Application User Proxy Authentication: Client Identifier is used at this specific time.
Clusterwide Global Transactions                                                  11.2.0.3.0                      0 Clusterwide Global Transactions is being used.
Crossedition Triggers                                                            11.2.0.3.0                      0 Crossedition triggers is being used.
CSSCAN                                                                           11.2.0.3.0                      0 Oracle Database has been scanned at least once for character set:CSSCAN has been run at least once.
Data Guard                                                                       11.2.0.3.0                    278 Data Guard, a set of services, is being used to create, maintain, manage, and monitor one or more standby databases.
Data Mining                                                                      11.2.0.3.0                      0 There exist Oracle Data Mining models in the database.
Data Recovery Advisor                                                            11.2.0.3.0                      0 Data Recovery Advisor (DRA) is being used to repair the database.
Database Migration Assistant for Unicode                                         11.2.0.3.0                      0 Database Migration Assistant for Unicode has been used.
Direct NFS                                                                       11.2.0.3.0                      0 Direct NFS is being used to connect to an NFS server
Dynamic SGA                                                                      11.2.0.3.0                      2 The Oracle SGA has been dynamically resized through an ALTER SYSTEM SET statement.
Editioning Views                                                                 11.2.0.3.0                      0 Editioning views is being used.
Editions                                                                         11.2.0.3.0                      0 Editions is being used.
EM Database Control                                                              11.2.0.3.0                      0 EM Database Control Home Page has been visited at least once.
Instance Caging                                                                  11.2.0.3.0                      0 Instance Caging is being used to limit the CPU usage by the database instance.
Locator                                                                          11.2.0.3.0                      0 There is at least one usage of the Oracle Locator index metadata table.
Resource Manager                                                                 11.2.0.3.0                      0 Oracle Database Resource Manager is being used to manage database resources.
Server Flash Cache                                                               11.2.0.3.0                      0 Server Flash Cache is being used with this database.
Server Parameter File                                                            11.2.0.3.0                    281 The server parameter file (SPFILE) was used to startup the database.
Shared Server                                                                    11.2.0.3.0                      0 The database is configured as Shared Server, where one server process can service multiple client programs.
Spatial                                                                          11.2.0.3.0                      0 There is at least one usage of the Oracle Spatial index metadata table.
SQL Access Advisor                                                               11.2.0.3.0                      0 SQL Access Advisor has been used.
SQL Performance Analyzer                                                         11.2.0.3.0                      0 SQL Performance Analyzer has been used.
SQL Tuning Advisor                                                               11.2.0.3.0                     16 SQL Tuning Advisor has been used.
SQL Workload Manager                                                             11.2.0.3.0                      0 SQL Workload Manager has been used.
Tune MView                                                                       11.2.0.3.0                      0 Tune MView has been used.
Undo Advisor                                                                     11.2.0.3.0                      0 Undo Advisor has been used.

50 linhas selecionadas.

SQL> select comp_id,comp_name,version,status,modified,schema,procedure
  2    from dba_registry
  3   order by 1;

COMP_ID                        COMP_NAME                                          VERSION                        STATUS      MODIFIED             SCHEMA                         PROCEDURE
------------------------------ -------------------------------------------------- ------------------------------ ----------- -------------------- ------------------------------ ----------------------------------
CATALOG                        Oracle Database Catalog Views                      11.2.0.3.0                     VALID       03-OUT-2012 22:47:46 SYS                            DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATPROC                        Oracle Database Packages and Types                 11.2.0.3.0                     VALID       03-OUT-2012 22:47:46 SYS                            DBMS_REGISTRY_SYS.VALIDATE_CATPROC
OWM                            Oracle Workspace Manager                           11.2.0.3.0                     VALID       03-OUT-2012 22:48:14 WMSYS                          VALIDATE_OWM



Nenhum comentário:

Postagens populares