OracleNotes

“Oracle *daily* TidBits” (#oratidbit) published on Facebook & Twitter, during weekdays in February 2019. Hope you find these helpful to learn something new or to remind you of its existence and use.

#oratidbit In Oracle Database 18c, RMAN can recover a standby database using “FROM SERVICE” to perform an Active Data Guard synchronization and refresh the standby DB with a single RMAN command: RECOVER STANDBY DATABASE FROM SERVICE primary_db.

#oratidbit The LISTAGG aggregate function in #db19c supports duplicate elimination by using the DISTINCT keyword. With the new DISTINCT keyword, duplicate values can be removed from the specified expression before concatenation into a single string.

#oratidbit #db19c contains a keenly awaited fix to the optimizer costing for for ANSI fetch first/offset queries. Now they can take advantage of an index scan + stopkey as per the older style order by/rownum syntax.

#oratidbit #db18c New routine validate_character_encoding in UTL_I18N allow for validation of characters within a particular characterset.

#oratidbit #db18c The TREAT … as JSON operator provides a way to inform the database that the content of a VARCHAR2, BLOB, CLOB should be treated as containing JSON. This enables the ability to use “Simplified Syntax” on db objects that do not have an “IS JSON” constraint.

#oratidbit #db18c A scalable sequence adds a 6 digit prefix to the sequence. The prefix is made up of a 3 digit instance offset concatenated to a 3 digit session offset.

#oratidbit #thinkautonomous Oracle GoldenGate On Premises releases 12.3.0.1.2 and later are certified with Oracle Autonomous Cloud for remote delivery using the non-integrated Replicats. You can’t set up Autonomous Cloud database as a source for GoldenGate On Premises.

#oratidbit Oracle Enterprise Manager Cloud Control releases 13.1 and 13.2 support integration with Oracle ORAchk and Oracle EXAchk through the OEM ORAchk Healthchecks Plug-in.

#oratidbit Each database PSU/RU contains the latest Oracle ORAchk version available at time of creation. When a database PSU is applied, the ORAchk zip version contained is staged in $ORACLE_HOME/suptools.

#oratidbit Oracle ORAchk and Oracle EXAchk include full REST support allowing invocation and query over HTTPS. To enable REST, setup ORDS and then provide a user password when prompted “orachk -ordssetup”.

#oratidbit #db19c Most of the Oracle Database supplied user accounts, except SYS and sample schemas are schema only accounts in 19c, these accounts are created without passwords. This prevents malicious users from logging into these accounts.

#oratidbit #db19c The automatic indexing feature automates index management tasks, such as creating, rebuilding, and dropping indexes in an Oracle database based on changes in the application workload.

#oratidbit In #db19c you can configure quarantine for execution plans for SQL statements that are terminated by the Resource Manager due to their excessive consumption of system resources, such as CPU and I/O. The quarantined execution plans are not allowed to run again.

#oratidbit #db19c. A hybrid partitioned table is a partitioned table in which some partitions reside in the database and some partitions reside outside the database in external files, such as operating system files or Hadoop Distributed File System (HDFS) files.

#oratidbit #db19c The new SQL Repair Advisor function DBMS_SQLDIAG.SQL_DIAGNOSE_AND_REPAIR creates a diagnostic task, executes it, and accepts SQL patch recommendation for a SQL statement that is generating a critical error.

#oratidbit #db19c The script catpcat.sql runs the scripts catalog.sql and catproc.sql with parallel processes, thus improving the performance of building the data dictionary manually.

#oratidbit The #db19c view DBA_REGISTRY_BACKPORTS shows the bugs that have been fixed by the patches applied to the database. Only the bug fixes that have modified the data dictionary are displayed in this view.

#oratidbit #db19c The LISTAGG aggregate function supports duplicate elimination by using the new DISTINCT keyword.

#oratidbit #db19c The standby database is flashed back automatically when Flashback Database is performed on the primary database. If you do not want the standby to automatically follow the primary, either keep the standby database in OPEN mode or stop the MRP process on the standby.

#oratidbit #db19c The ALTER SYSTEM clause FLUSH PASSWORDFILE_METADATA_CACHE refreshes the metadata cache with the latest details of the database password file. The latest details of the database password file can be retrieved by querying the V$PASSWORDFILE_INFO view.

“Oracle *daily* TidBits” (#oratidbit) published on Facebook, Twitter, and Google+ during weekdays in January 2019. Hope you find these helpful to learn something new or to remind you of its existence and use.

#oratidbit #goldengate Oracle Database 18c and later support replication of identity columns using Golden Gate. It requires usage of the Integrated Extract and Integrated Replicat or Integrated Parallel Replicat.

#oratidbit #goldengate The Classic Extract (capture) is deprecated as of Oracle GoldenGate 18c (18.1.0). There will be no further enhancements to the Classic Capture (Extract) process.

#oratidbit #goldengate Oracle GoldenGate Veridata compares one set of data to another and identifies data that is out-of-sync, and allows you to repair any data that is found out-of-sync (while data sources are online).

#oratidbit The RMAN block change tracking file can be enabled on an Oracle Active Data Guard standby that is using multi-instance Redo Apply in 18c (18.1.0)

#oratidbit #oracleatp #oraclecloud The default data and temporary tablespaces for the Autonomous Transaction Processing database are configured automatically. Adding, removing, or modifying tablespaces is not allowed.

#oratidbit #oracleatp #oraclecloud Compression is not enabled by default on Autonomous Transaction Processing instance, but ATP honors a compression clause if compression is specified on a table.

#oratidbit A Global Data Services configuration and its global services are created and managed using the GDSCTL command-line interface, which is similar to the SRVCTL command-line interface used to manage an Oracle RAC database and its services.

#oratidbit To enable secure communication between Oracle Analytics Cloud and Oracle Autonomous Data Warehouse Cloud, upload trusted SSL certificates from Oracle Autonomous Data Warehouse Cloud to Oracle Analytics Cloud.

#oratidbit “dbms_xdb_config.setglobalportenabled(TRUE)” enables global port for EM Express 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 Using the EM Express Performance Hub you can view performance statistics for a standby database in an Oracle Active Data Guard environment. Only real-time data for the standby database can be viewed in the Performance Hub.

#oratidbit Optimizer Statistics Advisor is built-in diagnostic software that analyzes the quality of statistics and statistics-related tasks. The advisor task runs automatically in the maintenance window.

#oratidbit An on-premise database can be duplicated to Oracle Cloud using the DUPLICATE PLUGGABLE DATABASE command in 18c. Similarly, you can duplicate a database in Oracle Cloud as an on-premise database.

#oratidbit #exadata Starting with Oracle Exadata System 19.1.0, Database Machine provides automated, cloud-scale performance monitoring including CPU, memory, file system, IO, and network. This feature combines AI, real-world performance experience, and best practices.

#oratidbit #db18c DBMS_PDB.EXPORTRMANBACKUP procedure exports the RMAN backup information that belongs to a pluggable database to its dictionary before unplug so that pre-plugin backups can be used. Use for non-CDB to PDB migration. Not needed for PDB relocation.

#oratidbit A PDB snapshot is a named copy of a PDB at a specific point in time. When a PDB is enabled for PDB snapshots, you can create up to eight snapshots of it. The set of snapshots is called a snapshot carousel.

#oratidbit The Oracle Database password file in ORACLE_BASE allows Oracle home to be used as a static repository of database files enabling the Oracle home to be configured in read-only mode and used as a software image that can be shared across multiple database servers.

#oratidbit Passwords in database links are encrypted in #db18c. Oracle Data Pump handles the export and import of these passwords. Import operations from older versions and export operations to older versions can still be used.

#oratidbit #db18c The DataPump Import DATA_OPTIONS parameter CONTINUE_LOAD_ON_FORMAT_ERROR option. If DataPump encounters a stream format error when loading table data, attempts to skip forward to find the start of the next row and continue loading data from that location.

#oratidbit #db18c The DATA_OPTIONS parameter of the DBMS_DATAPUMP.SET_PARAMETER procedure option KU$_DATAOPT_CONT_LOAD_ON_FMT_ERR tells DataPump to continue loading data from the next row if it encounters stream format errors in the table data.

#oratidbit In Oracle Database 18c, RMAN preserves the PDB backup history for PDBs, even if it is moved from one CDB to another. Use syntax RESTORE/RECOVER PLUGGABLE DATABASE <pdb> FROM PREPLUGIN BACKUP.