Oracle *daily* TidBits” (#oratidbit) published on Facebook, Twitter and Google+ during weekdays in December 2016. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well… Hope you find these helpful to learn something new or to remind you of its existence and use.

OraTidBit
#oratidbit #DB12cR2 You can capture Automatic Workload Repository (AWR) data for Oracle Active Data Guard standby databases. This feature enables performance tuning of Oracle Active Data Guard standby databases.
#oratidbit #DB12cR2 INACTIVE_ACCOUNT_TIME initialization parameter automatically locks the database user account who has not logged in to the database instance in a specified number of days.
#oratidbit #db12cR2 You can prioritize the PDBs in a CDB when you upgrade the CDB. The PDBs with higher priority are upgraded before PDBs with lower priority.
#oratidbit #db12cR2 A CDB can run in local undo mode or shared undo mode. Local undo mode means that every container in the CDB uses local undo. Shared undo mode means that there is one active undo tablespace for a single-instance CDB.
#oratidbit #db12cR2 In 12.2, tables with BFILE columns can be redefined online.
#oratidbit #db12cR2 In 12.2, Query the V$ONLINE_REDEF view to monitor the progress of an online table redefinition operation.
#oratidbit #db12cR2 In 12.2, SYSRAC is a new administrative privilege for tasks related to Oracle Real Application Clusters (#Oracle #RAC) operations.
#oratidbit #DB12cR2 ALLOW_GROUP_ACCESS_TO_SGA is new parameter and controls group access to shared memory on UNIX platforms. The default value is false, which means that database shared memory is created with owner access only. In Oracle Database releases prior to Oracle Database 12c Release 2 (12.2.0.1), database shared memory was created with owner and group access.
#oratidbit #DB12cR2 AWR_PDB_AUTOFLUSH_ENABLED is new parameter and enables you to specify whether to enable or disable automatic Automatic Workload Repository (AWR) snapshots for all the PDBs in a CDB or for individual PDBs in a CDB.The default value of AWR_PDB_AUTOFLUSH_ENABLED is false. Thus, by default, automatic AWR snapshots are disabled for all the PDBs in a CDB. When you change the value of AWR_PDB_AUTOFLUSH_ENABLED in the CDB root, the new value takes effect in all the PDBs in the CDB. Therefore, if you change the value of AWR_PDB_AUTOFLUSH_ENABLED in the CDB root to true, the value of AWR_PDB_AUTOFLUSH_ENABLED is also changed to true in all of the PDBs, so that automatic AWR snapshots are enabled for all the PDBs. You can also change the value of AWR_PDB_AUTOFLUSH_ENABLED in any of the individual PDBs in a CDB, and the value that is set for each individual PDB will be honored. This enables you to enable or disable automatic AWR snapshots for individual PDBs.
#oratidbit #DB12cR2 ENABLE_AUTOMATIC_MAINTENANCE_PDB is new parameter and can be used to enable or disable the running of automated maintenance tasks for all the PDBs in a CDB or for individual PDBs in a CDB. When you change the value of ENABLE_AUTOMATIC_MAINTENANCE_PDB in the CDB root, the new value takes effect in the root and in all the PDBs in the CDB. You can also change the value of ENABLE_AUTOMATIC_MAINTENANCE_PDB in any of the individual PDBs in a CDB, and the value that is set for each individual PDB will be honored. This enables you to enable or disable automated maintenance tasks for individual PDBs.
#oratidbit #DB12cR2 ENABLED_PDBS_ON_STANDBY is new parameter and specifies which pluggable databases (PDBs) to replicate on an Oracle Data Guard standby database. The asterisk (*) and question mark (?) wildcard characters are supported.
#oratidbit #DB12cR2 REMOTE_RECOVERY_FILE_DEST is new parameter and specifies a directory from which to read archive log files during a pluggable database (PDB) refresh operation if the source is not available.
#oratidbit #DB12cR2 UNIFORM_LOG_TIMESTAMP_FORMAT is new parameter and specifies that a uniform timestamp format be used in Oracle Database trace (.trc) files. When the value of UNIFORM_LOG_TIMESTAMP_FORMAT is TRUE, the format used for timestamps in trace files is standardized on universal time with millisecond precision. When the value of UNIFORM_LOG_TIMESTAMP_FORMAT is FALSE, trace files include a mix of timestamps using different precisions, with some timestamps showing local time and other timestamps showing universal time.
#oratidbit Before you create a DBaaS instance on Oracle Database #Cloud (#DBaaS), you must setup an SSH public/private key pair to connect to compute node and optionally Oracle storage cloud service container to backup the database to cloud.
#oratidbit Use dbaascli utility on Oracle #DBaaS cloud deployments to perform a variety of life-cycle and administration operations including patching, database recovery and data guard switchover.
#oratidbit Use raccli utility #DBaaS cloud deployments that use Oracle Real Application Clusters (RAC) to perform a variety of life-cycle and administration operations including backup, recovery, patching, security configurations and so on.
#oratidbit Use dbpatchmdg utility #DBaaS cloud deployments that are configured with Oracle Data Guard to perform a variety of patching operations.
#oratidbit Use the oracle-dbcs-cli utility on your Linux computer to connect to Oracle Cloud and perform a variety of life-cycle and administration operations on Oracle Database Cloud Service deployments (non RAC).
#oratidbit To use #Oracle Database Backup Cloud Service you’ll subscribe to the service, install the Oracle Database Cloud Backup Module, and configure your environment to send backups to the cloud using familiar RMAN commands.
#oratidbit Oracle Database Exadata Express Cloud Service delivers a managed Oracle Database 12c Release 2 Enterprise Edition with options, running on Oracle Exadata engineered systems.
#oratidbit #DB12cR2 In 12.2, the maximum length for most database object names has increased from 30 bytes to 128 bytes.
#oratidbit #DB12cR2 In 12.2, you can encrypt undo tablespaces, temporary tablespaces, and the SYSTEM and SYSAUX tablespaces. Also, encrypt both offline and online tablespaces.
Oracle TidBit

Oracle TidBits

Oracle *daily* TidBits” (#oratidbit) published at https://www.facebook.com/oraclenotes on weekdays in November 2016. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well… Hope you find these helpful to learn something new or to remind you of its existence and use…

#oratidbit The orapki command line utility enables administrators to manage wallets, certificate revocation lists, and other public key infrastructure (PKI) elements from the command line. It can be used inside scripts, enabling administrators to automate many routine PKI tasks.
#oratidbit By default, Oracle database audit trail records are written to the SYSAUX tablespace. You can designate a different tablespace by using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure.
#oratidbit In #DB12c Oracle Database writes unified audit records to system global area (SGA) queues and then periodically writes them to the AUDSYS schema audit table in the SYSAUX tablespace.
#oratidbit UNIFIED_AUDIT_SGA_QUEUE_SIZE initialization parameter sets the size of SGA area where unified audit queues are saved before writing to AUDSYS schema.
#oratidbit Use the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL procedure to manually purge the audit trail from Oracle Database.
#oratidbit In #DB12c the READ object privilege and READ ANY TABLE system privilege are more secure than SELECT and SELECT ANY TABLE privileges. SELECT privilege includes LOCK capability on the table/rows.
#oratidbit In #DB12c DB_WRITER_PROCESSES can be up to 100. The processes are DBW0-DBW9, DBWa-DBWz and BW36-BW99.
#oratidbit #DB12cR2 Duties that belonged exclusively to PMON now belong to the PMON process group, which includes PMON, Cleanup Main Process (CLMN), and Cleanup Helper Processes (CLnn). This process group is responsible for the monitoring and cleanup of other processes.
#oratidbit #DB12cR2 Optimizer Statistics Advisor analyzes how you are currently gathering statistics, the effectiveness of existing statistics gathering jobs, and the quality of the gathered statistics. Optimizer Statistics Advisor maintains rules, which embody Oracle best practices based on the current feature set.
#oratidbit #DB12cR2 #Oracle Sharding is a scalability and availability feature for suitable OLTP applications in which data is horizontally partitioned across discrete Oracle databases, called shards, which share no hardware or software.
#oratidbit #DB12cR2 You can relocate a PDB from one CDB to another with minimal down time.
#oratidbit #DB12cR2 During a PDB clone operation, the source PDB no longer needs to be in read-only mode.
#oratidbit #DB12cR2 You can create a proxy PDB, which references a PDB in a different CDB and provides fully functional access to the referenced PDB.
#oratidbit #DB12cR2 You can use FLASHBACK PLUGGABLE DATABASE command to rewind a PDB to any SCN without affecting other PDBs in a CDB. You can also create a restore point specific for a PDB, and rewind the PDB to this restore point without affecting other PDB.
#oratidbit #DB12cR2 A PDB lockdown profile is a security mechanism to restrict operations that are available to users connected to a specified PDB.
#oratidbit #DB12cR2 You can configure PDB parameters to guarantee or limit SGA memory, PGA memory, sessions, CPU, and I/O rates for each PDB.
#oratidbit #DB12cR2 You can create local, nonshared temporary tablespaces in PDB. When many read-only instances access a single database, local temporary tablespaces can improve performance for queries that involve sorts, hash aggregations, and joins.
#oratidbit #DB12cR2 You can pre-create a pool of server processes by using the DBMS_PROCESS PL/SQL package. The new Process Manager (PMAN) background process monitors the pool of pre-created processes, which wait to be associated with a client request.
#oratidbit #DB12cR2 Read-only and read-write instances can co-exist within a single database. To designate an instance as read-only, set the INSTANCE_MODE initialization parameter to READ_ONLY. The default value of the parameter is READ_WRITE.
#oratidbit #DB12cR2 In the root of a CDB, you can configure a single port (known as the global port), which enables you to use EM Express to connect to all of the PDBs in the CDB using the HTTPS port for the CDB.
#oratidbit #DB12cR2 You can capture Automatic Workload Repository (AWR) data for the PDBs in a multitenant environment. This feature enables performance tuning of PDBs in a multitenant environment.
#oratidbit #DB12cR2 In EM Express, you can view real-time performance statistics for a standby database in the Performance Hub.

 

dbcloudApproximate Query Processing appears to be a major feature of Oracle Database 12c Release 2 SQL. In 12cR1 patchset 2 (12.1.0.2), we saw one approximate query function appear – APPROX_COUNT_DISTINCT to use instead of COUNT(DISTINCT). In 12.2 there are several APPROX functions introduced:

 

  • APPROX_COUNT_DISTINCT_DETAIL
  • APPROX_COUNT_DISTINCT_AGG
  • TO_APPROX_COUNT_DISTINCT
  • APPROX_MEDIAN
  • APPROX_PERCENTILE
  • APPROX_PERCENTILE_DETAIL
  • APPROX_PERCENTILE_AGG
  • TO_APPROX_PERCENTILE

According to Oracle documentation, “Approximate query processing is primarily used in data discovery applications to return quick answers to explorative queries. Users typically want to locate interesting data points within large amounts of data and then drill down to uncover further levels of detail. For explorative queries, quick responses are more important than exact values.”

The interesting part is that you can utilize the Approximate functions without changing code. There are three initialization parameters introduced to control which functions should be treated as an approximate function during run time… cool!

The initialization parameters are:

  • approx_for_aggregation
  • approx_for_count_distinct
  • approx_for_percentile

To replace only the COUNT(DISTINCT) function in queries with the APPROX_COUNT_DISTINCT function, set the approx_for_count_distinct initialization parameter to TRUE for the current session or for the entire database.

To replace percentile functions with the corresponding functions that return approximate results, set approx_for_percentile to PERCENTILE_CONT, PERCENTILE_DISC, or ALL (replaces all percentile functions) for the current session or for the entire database.

To run all queries using the corresponding SQL functions that return approximate results instead of the specified SQL functions, set the approx_for_aggregation initialization parameter to TRUE for the current session or for the entire database.

Read more… Oracle Documentation

%d bloggers like this: