Author: Biju

www.bijoos.com
Posted on

Oracle Tidbits – April 2017 #oratidbit

“Oracle *daily* TidBits” (#oratidbit) published on Facebook, Twitter and Google+ during weekdays in April 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 #orcldb Multiple Instance Redo Apply (MIRA), a #DB12cR2 feature, enables redo apply on all or some standby instances with RAC configuration.
#oratidbit #orcldb Init parameter ARCHIVE_LAG_TARGET forces a log switch after the specified amount of time (minutes) elapses. The typical value is 1800 (30 minutes). Low values can result in frequent log switches, which could degrade performance.
#oratidbit #orcldb Init parameter AWR_SNAPSHOT_TIME_OFFSET specifies an offset for the AWR snapshot start time. AWR snapshots normally start at the top of the hour. On large systems with many databases, this setting can help to avoid CPU spikes at the top of the hour.
#oratidbit #orcldb Init parameter AWR_SNAPSHOT_TIME_OFFSET has a special value 1000000, which is automatic mode, in which the offset is based on the database name.
#oratidbit #orcldb If init parameter CPU_COUNT is set to 0 (its default setting), then Oracle Database continuously monitors the number of CPUs reported by the operating system and uses the current count. If CPU_COUNT is set to a value other than 0, then Oracle Database will use this count rather than the actual number of CPUs, thus disabling dynamic CPU reconfiguration.
#oratidbit #orcldb Use ASMCMD spbackup, spcopy, or spmove commands to back up, copy, or move an Oracle ASM SPFILE.
#oratidbit #orcldb The minimum MEMORY_TARGET for Oracle ASM instance is 1 GB. If you set MEMORY_TARGET lower, then Oracle increases the value for MEMORY_TARGET to 1 GB automatically.
#oratidbit #orcldb You can upgrade Oracle database directly to 12.2 from versions 11.2.0.3 or later, 12.1.0.1 and 12.1.0.2.
#oratidbit #orcldb The COMPATIBLE parameter minimum value for 12cR1 database is 11.0.0 and for 12cR2 database is 11.2.0.
#oratidbit #orcldb To properly convert from an Enterprise Edition database to a Standard Edition database, perform an Export/Import operation. Use the Standard Edition Export utility to export the data.
#oratidbit #orcldb #windows Starting with Oracle Grid Infrastructure 12c Release 2 (12.2), the Oracle Grid Infrastructure software is available as an image file for download and installation. Extract the image software into the Grid home directory, and run the gridSetup.bat script to start the Oracle Grid Infrastructure installation.
#oratidbit #orcldb #windows Starting with Oracle Database 12c Release 2 (12.2), Oracle Database is supported on Resilient File System (ReFS). ReFS uses checksums for file metadata, and an allocate-on-write method to update data which minimizes the risk of corruption.
#oratidbit #orcldb #windows Oracle Database 12.2 is supported on Windows 7, 8, 8.1, 10, Windows Server 2012, 2012 R2. x64 only.
#oratidbit #orcldb DBA_GOLDENGATE_NOT_UNIQUE view displays all tables that have no primary and no non-null unique indexes. New in #DB12cR2
#oratidbit #orcldb #db12cR2 Oracle implicitly returns to the client application the results of SQL statements executed within a stored procedure, bypassing the explicit use REF CURSORs. This feature eliminates the overhead of re-writing the client-side code.
#oratidbit #orcldb Reference partitioning enables the partitioning of two tables that are related to one another by referential constraints. The partitioning key is resolved through an existing parent-child relationship, enforced by enabled and active primary key and foreign key constraints.
#oratidbit #orcldb I/O calibration statistics can be gathered with the PL/SQL DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure. I/O calibration is a one-time action if the physical hardware does not change.
#oratidbit #orcldb A bigfile tablespace consists of a single data or temporary file which can be up to 128 TB. The use of bigfile tablespaces can significantly reduce the number of data files for your database. Oracle Database supports parallel RMAN backup and restore on single data files.
#oratidbit #orcldb Automatic data optimization (ADO) is a component of Information Lifecycle Management (ILM) that automates the compression and movement of data between different tiers of storage within the database.
Posted on

Oracle Tidbits – March 2017 #oratidbit

“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.