OracleNotes

“Oracle *daily* TidBits” (#oratidbit) published at https://www.facebook.com/oraclenotes on weekdays in October 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 In Oracle #DB12c 12.2, if Container database Character Set is AL32UTF8 then PDBs are allowed to have different character set.

#oratidbit Oracle Health Check Collections Manager is a companion application to Oracle #ORAchk that gives you an enterprise-wide view of health check collection data. It provides a dashboard to track your collection data in one easy-to-use interface using APEX.

#oratidbit Oracle #ORAchk health check collection can be scheduled to run periodically by using the -set option. The AUTORUN_INTERVAL daemon option provides an alternative method of regularly running health checks.

#oratidbit Oracle #ORAchk and Oracle #EXAchk automatically compare the two most recent HTML reports and generate a third diff report, when run in automated daemon mode.

#oratidbit Oracle #ORAchk and Oracle #EXAchk are capable of uploading collection results from multiple instances into a single database for easier consumption of check results using Oracle Health Check Collections Manager or using custom application.

#oratidbit A non-partitioned table in Oracle #DB12cR2 can be converted to a partitioned table by using the MODIFY clause of ALTER TABLE. MODIFY PARTITION clause specifies the partition properties.

#oratidbit In Oracle #DB12cR2 new function VALIDATE_CONVERSION can be used to identify problem data that cannot be converted to the required data type. It returns 1 if a given expression can be converted to the specified data type, else it returns 0. Example: VALIDATE_CONVERSION(‘$100,00’ AS NUMBER, ‘$999D99’, ‘NLS_NUMERIC_CHARACTERS = ”,.”’) returns 1; VALIDATE_CONVERSION(‘$29.99’ AS BINARY_FLOAT) returns 0.

#oratidbit In Oracle #DB12cR2 many data type conversion functions (TO_DATE, TO_NUMBER, etc) and CAST function includes data error handling using the “DEFAULT value ON CONVERSION ERROR” option. Example: TO_NUMBER(‘2,00’ DEFAULT 0 ON CONVERSION ERROR) returns 0 instead of error.

#oratidbit In Oracle #DB12cR2 a table segment or table partition segment can be moved to a new segment (or tablespace) with the ONLINE keyword in ALTER TABLE … MOVE while DML operations can continue to run uninterrupted on the table.

#oratidbit In Oracle #DB12cR2 most object names can be as long as 128 bytes. You no longer have to limit a table name to 30 characters, for example. Exceptions are database names (8 bytes); Names of disk groups, pluggable databases (PDBs), rollback segments, and tablespaces (30 bytes).

#oratidbit In Oracle #DB12c, the DBMS_ILM package provides an interface for implementing Information Lifecycle Management (ILM) strategies using Automatic Data Optimization (ADO) policies.

#oratidbit Oracle #DB12c Feature: In-database archiving enables to archive rows within a table by marking them as invisible. These invisible rows are in the database but are not visible to an application. The data in these rows is available for compliance purposes if needed by setting session parameter “alter session set row archival visibility = all”.

#oratidbit Oracle #DB12c Feature: In-database archiving is enabled by using the attribute ROW ARCHIVAL in the CREATE TABLE or ALTER TABLE statements. Invisible column ORA_ARCHIVE_STATE is added to the table with default value 0 (not archived).

#oratidbit Materialized View Synchronous refresh is an approach for maintaining tables and materialized views in a data warehouse where tables and materialized views are refreshed at the same time. Tables are registered with synchronous refresh by creating a staging log on them.

#oratidbit DBA_OPTSTAT_OPERATION_TASKS displays the history of tasks that are performed as part of statistics operations (recorded in DBA_OPTSTAT_OPERATIONS). Each task represents a target object to be processed in the corresponding parent operation. Task start time and end time columns help to know duration of each task.

#oratidbit The HugePages Linux OS feature allocates non-swappable memory for large page tables using memory-mapped files. Automatic Memory Management should not be used when using HugePages – both MEMORY_TARGET and MEMORY_MAX_TARGET initialization parameters are unset.

#oratidbit In #DB12c 12.1.0.2 the NO DATA clause of the CREATE PLUGGABLE DATABASE statement specifies that a PDB’s data model definition is cloned but not the PDB’s data. The dictionary data in the source PDB is cloned, but all user-created table and index data from the source PDB is discarded. This clause is useful for quickly creating clones of a PDB with only the object definitions and no data. This clause does not apply to the SYSTEM and SYSAUX tablespaces.

#oratidbit While creating #DB12c, DBCA picks a free port from 5500 to 5599 for Enterprise Manager Express. If you want a specific port to be used, set the environment variable DBEXPRESS_HTTPS_PORT before starting Oracle Universal Installer (OUI) or Database Configuration Assistant (DBCA).

#oratidbit Oracle Wallet Manager is used to manage public key security credentials on Oracle clients and servers. The wallets it creates can be read by Oracle Database, Oracle Application Server, and the Oracle Identity Management infrastructure.

Thanks Tim for coming up with this idea! Pretty neat… And THANK YOU OTN!!

There are quite a few features I like in the Oracle Database, but today I choose ORAchk (and EXAchk). Troubleshooting and diagnostics are my favorite area and I was amazed by the enhancements Oracle made in integrating the various diagnostics tools and preserving history. And ORA/EXAchk is a no-cost tool, provided by Oracle, enhanced every quarter with more and more health checks…

ORAchk and EXAchk tools started as just another configuration verification tool (used to be called RACcheck). ORAchk (for non-Engineered systems) and EXAchk (for engineered systems) grown into a “product” from a “tool”. This is evident in 12.2, as the documentation from a simple MOS note grown into a full document User Guide.

What’s cool is that ORAchk and EXAchk can be daemonized to run these checks periodically. The daemon can also purge old reports based on the retention policy you set. The most favorite enhancement I like is the ability to compare two reports. Oracle ORAchk and Oracle EXAchk automatically compare the two most recent HTML reports and generate a difference report, when run in an automated daemon mode.

To manually compare two reports, orachk (or exachk) can be invoked with the -diff option.

$ ./orachk –diff report1 report2

$ ./exachk –diff report1 report2

The comparison report’s sections are very appropriate.

Difference between report 1 and report 2 (status change of checks)
Unique findings in report 1 (issues that were fixed)
Unique findings in report 2 (new issues after report 1)
Common findings in report 1 and 2 (issues that were not fixed)

Oracle Health Check Collections Manager is another cool application that comes with these tools, where the ORAchk (and EXAchk) results can be uploaded to an Oracle database APEX workspace for analysis and reporting. ORAchk (and EXAchk) can be configured to automatically upload check results to the Oracle Health Check Collections Manager database. Specify the connection string and the password to connect to the database to upload collection results. Oracle Health Check Collections Manager stores the connection details in an encrypted wallet.

If you have not run ORAchk recently, it’s time to download and run the check today. And, don’t forget to schedule it! Forgot to mention, since 12.1.0.2.5 version you have the ability to Create User Defined Checks too!

Download ORAchk – My Oracle Support Note 1268927.2

While you are there, check out TFA (Trace File Analyzer: MOS 1513912.2) and CHM (Cluster Health Monitor: MOS 1328466.1) as well!

Posts

Oracle Tidbits – October 2016 #oratidbit

OTN Appreciation Day: The ORAchk and EXAchk Tools #ThanksOTN