Oracle *daily* TidBits” (#oratidbit) published on Facebook, Twitter and Google+ during weekdays in March 2017. 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 #db12cR2 #sqlplus New SET STATEMENTCACHE to allow statement caching of repeated statements to avoid parsing again (performance gain). Default is 0, maximum is 32767.
#oratidbit #db12cR2 #sqlplus To make SQL*Plus run “faster”, invoke sqlplus with “-Fast” option. -Fast option sets ARRAYSIZE=100, LOBPREFETCH=16384, PAGESIZE=50000, ROWPREFETCH=2, STATEMENTCACHE=20
#oratidbit #sqlplus Use the SYSTEM.PRODUCT_USER_PROFILE (PUP) table to disable certain SQL and SQL*Plus commands in the SQL*Plus environment on a per-user basis. Create the table using script pupbld.sql.
#oratidbit #sqlplus #db12c When connected to CDB, STARTUP/SHUTDOWN commands start/stop the container database including all PDBs. When connected to a PDB, the STARTUP/SHUTDOWN commands open/close the PDB. SHUTDOWN TRANSACTIONAL and SHUTDOWN ABORT are not applicable to PDB.
#oratidbit #orcldb When Database Smart Flash Cache is enabled, part of the buffer cache can reside in the flash disk devices (solid state storage devices). The database can improve performance by caching buffers in flash memory instead of reading from magnetic disk. Use the DB_FLASH_CACHE_FILE and DB_FLASH_CACHE_SIZE initialization parameters to configure flash devices.
#oratidbit #orcldb The PMON process group includes process monitor (PMON), Cleanup Main Process (CLMN), and Cleanup Helper Processes (CLnn). These processes are responsible for the monitoring and cleanup of other processes. CLMN periodically performs cleanup of terminated processes, terminated sessions, transactions, network connections, idle sessions, detached transactions, and detached network connections that have exceeded their idle timeout.
#oratidbit #orcldb #db12c Multiple indexes on same table with same column order can exist if 1) they are of different types 2) have different partition schemes 3) have different uniqueness properties.
#oratidbit #orcldb Reference partitioning is where partitioning in a (child) table is solely defined through the foreign key relationship with a parent table. For every partition in the parent table, exactly one corresponding partition exists in the child table. The parent table stores the parent records in a specific partition, and the child table stores the child records in the corresponding partition.
#oratidbit #orcldb the data in dynamic performance (V$) views depend on the state of the database and database instance. Examples: You can query V$INSTANCE and V$BGPROCESS when the database is started but not mounted. You can query V$DATAFILE when the database is mounted.
#oratidbit An Oracle ASM allocation unit (AU) is the fundamental unit of allocation within an ASM disk group. An allocation unit is the smallest contiguous disk space that Oracle ASM allocates. One or more AUs form an Oracle ASM extent. An Oracle ASM extent is a section of an Oracle ASM file. An Oracle ASM file consists of one or more file extents.
#oratidbit #orcldb #db12cR2 A local temporary tablespace stores separate, non-shared temp files for every database instance in RAC configuration. A local temporary tablespace is created using CREATE LOCAL TEMPORARY TABLESPACE statement and is always a bigfile tablespace, but the BIGFILE keyword is not required in the creation statement.
#oratidbit #db12cR2 parameter ENABLE_AUTOMATIC_MAINTENANCE_PDB 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.
#oratidbit #db12cR2 parameter SGA_MIN_SIZE sets the guaranteed SGA size for a pluggable database (PDB). When SGA_MIN_SIZE is set for a PDB, it guarantees the specified SGA size for the PDB.
#oratidbit #db12cR2 parameter DBA_INDEX_USAGE displays cumulative statistics for each index. Total number of times the index has been accessed and timestamp of last index use are included.
#oratidbit #orcldb V$LGWRIO_OUTLIER contains entries corresponding to Log Writer (LGWR) process I/Os that have taken a long time (more than 500 ms) to complete. Use this view to see if there any delays in serving disk I/O requests by the storage subsystem.
#oratidbit #orcldb V$IO_OUTLIER contains entries corresponding to I/Os that have taken a long time (more than 500 ms) to complete. On Solaris platform check V$KERNEL_IO_OUTLIER as well. Use these view to see if there any occasional delays in serving disk I/O requests by the storage subsystem.
#oratidbit #orcldb Oracle Autonomous Health Framework includes Oracle ORAchk and Oracle EXAchk, Cluster Health Monitor, Trace File Analyzer Collector, Cluster Health Advisor, Memory Guard, Hang Manager and Database Quality of Service (QoS) Management.
#oratidbit #orcldb #db12cR2 Oracle Cluster Health Advisor collects data from Oracle Real Application Clusters (Oracle RAC) and Oracle RAC One Node databases, and from operating system and hardware resources. Advises how to fix configuration or performance issues.
#oratidbit #orcldb Real Applicaiton Clusters (RAC) Memory Guard automatically monitors cluster nodes to prevent node stress caused by the lack of memory. If insufficient memory detected it prevents new database sessions from being created as well as stops all CRS-managed services transactionally on the node, allowing the existing workload on the node to complete and free their memory.
#oratidbit #orcldb ORAchk and Oracle EXAchk can be configured to automatically upload health check collection results to the Oracle Health Check Collections Manager database.
#oratidbit #orcldb Init parameter JOB_QUEUE_PROCESSES default and max value in 12.2 is 4000. The actual number of job slaves created for Oracle Scheduler jobs is auto-tuned by the Scheduler. If the value of JOB_QUEUE_PROCESSES is set to 0 in a non-CDB or in a CDB root, then DBMS_JOB jobs and Oracle Scheduler jobs will not run in the non-CDB or in the root.

A new pre-built VirtualBox image is available for download  from OTN (updated on March 27, 2017).

Once you download, import and configure the VM, you can pretty much do whatever you want. But Roy and Mike have put together a very good lab environment and exercise for you to practice Oracle database upgrade from 11gR2 to 12cR2. The lab includes

  • Manual upgrade of database to
  • Attach 12c non-container database as a PDB to existing CDB
  • Upgrade database using Full Transportable Export/Import to as a PDB
  • Upgrade PDB to using unplug / plug method

The pre-built VM comes with Oracle software already installed.

  • 11gR2 – /u01/app/oracle/product/
  • 12cR1 – /u01/app/oracle/product/
  • 12cR2 – /u01/app/oracle/product/

Practice upgrades, new features, patches, PSU, migration using this VirtualBox image.

Thanks to Mike Dietrich and Roy Swonger!

VirtualBox Download

12c Database Image Download


Oracle *daily* TidBits” (#oratidbit) published on Facebook, Twitter and Google+ during weekdays in February 2017. 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 Setting STATISTICS_LEVEL parameter to BASIC disables AWR. If STATISTICS_LEVEL is set to BASIC, you can still manually capture AWR statistics using the DBMS_WORKLOAD_REPOSITORY package.
#oratidbit The $ORACLE_HOME/rdbms/admin/awrextr.sql script extracts AWR data for a range of snapshots from the database into a Data Pump export file (to load into another database).
#oratidbit #db12cR2 When you take AWR snapshot at CDB level, snapshot data is stored in SYSAUX tablespace of CDB. The PDB-level snapshot data is stored in the SYSAUX tablespace of a PDB.
#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 DBMS_SPM PL/SQL package is used to perform SQL plan management tasks – creating, dropping, and loading SQL plan baselines. DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE can be used to show one or more execution plans for the SQL statement.
#oratidbit Run the asmcmd command with -p option to include the current directory in the ASMCMD prompt.
#oratidbit Active Session History (ASH) sampling is available (#db12c) on Oracle ASM instances. This activity is exposed in the dynamic view V$ACTIVE_SESSION_HISTORY.
#oratidbit #db12cR2 Case-insensitive data(base) support available through collation. Can be declared on a schema, table or column level.
#oratidbit #db12cR2 The AL32UTF8 character set is the default for the database character set in Oracle Database installs and in the Database Configuration Assistant (DBCA). In earlier versions, the default database character set was based on the operating system locale setting.
#oratidbit #db12cR1 OPTIMIZER_ADAPTIVE_FEATURES functions are replaced by two new parameters in #db12cR2 OPTIMIZER_ADAPTIVE_PLANS (default TRUE), and OPTIMIZER_ADAPTIVE_STATISTICS (default FALSE).
#oratidbit before installing #DB12cR2 Grid Infrastructure or Database, simplify OS configuration by installing oracle-database-server-12cR2-preinstall RPM.
#oratidbit Starting with Oracle Database 12c Release 1 (, the script replaces the script in the Oracle Grid Infrastructure home.
#oratidbit To enable or disable a particular database feature for an Oracle home, shut down the database and use the “chopt” tool.
#oratidbit #db12cR2 New function ORA_MAX_NAME_LEN_SUPPORTED can be used to check the maximum length of identifiers allowed in the database. In #DB12cR2, if COMPATIBLE parameter is set to 12.1.0 or lower, the limit is 30 bytes.
#oratidbit The pl/sql package DBMS_DB_VERSION provides the Oracle version numbers and other information useful for simple conditional compilation based on version and release.
#oratidbit “In a future PL/SQL release, to accommodate emerging SQL standards, VARCHAR might become a separate data type, no longer synonymous with VARCHAR2.” — A note from PL/SQL Language Reference Document.
#oratidbit PL/SQL data types PLS_INTEGER, BINARY_FLOAT and BINARY_DOUBLE use hardware arithmetic and are faster than NUMBER data type (uses library arithmetic).
#oratidbit PL/SQL package DBMS_PARALLEL_EXECUTE can be used to implement DML parallelism in PL/SQL manually; you can execute a SQL or PL/SQL statement in parallel by taking the data to be processed and breaking it up into multiple smaller chunks.
#oratidbit #db12cR2 #sqlplus New SET LOBPREFETCH to define the amount of LOB data (in bytes) to prefetch from database at one time. Default is 0, meaning prefetching is off. Maximum value is 2GB.
#oratidbit #db12cR2 #sqlplus New SET ROWPREFETCH to define the number of rows to prefetch from database at one time. Default is 1. The amount of data contained in the prefetched rows should not exceed 2GB.
%d bloggers like this: