Oracle Tidbits – May & June 2015

Oracle *daily* TidBits” (#oratidbit) published at https://www.facebook.com/oraclenotes on weekdays in May and June 2015. 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…

Date TidBit
11-May #oratidbit #Oracle In-Memory column store is a static pool in the #DB12c SGA, configured using INMEMORY_SIZE initialization parameter.
12-May #oratidbit In-Memory column store can be enabled at Column, Table, Materialized view, Tablespace or Partition level.
13-May #oratidbit The In-Memory column store objects are visible in V$IM_SEGMENTS view. Enable database objects for the IM column store by including INMEMORY clause in ALTER or CREATE TABLE/TABLESPACE/MATERIALIZED VIEW statements.
14-May #oratidbit setting initialization parameter INMEMORY_FORCE=OFF will disable populating tables or materialized views in memory, even if they are configured as INMEMORY. Default value for this parameter is DEFAULT.
15-May #oratidbit When setting up objects for In-Memory column store, you can prioritize when the objects are populated using the INMEMORY PRIORITY clause. The options are NONE (default, populated when object is queried), LOW, MEDIUM, HIGH, CRITICAL are the valid values.
18-May #oratidbit To manually purge the oracle Optimizer Stats, use procedure dbms_stats.purge_stats (timestamp).
19-May #oratidbit The Optimizer Statistics history retention can be changed at the database using procedure dbms_stats.alter_stats_history_retention (days). The current value can be obtained using dbms_stats.get_stats_history_retention function. The oldest date of stats history availability can be checked using dbms_stats.get_stats_history_availability function.
20-May #oratidbit A common user in a multitenant database with the SET CONTAINER privilege can use the “ALTER SESSION SET CONTAINER=” statement to swtich between databases including the root container. When you connect using this method, the AFTER LOGON trigger does not fire.
21-May #oratidbit When you use ALTER SESSION SET CONTAINER to switch container, the transactions that are not committed or rolled back in the original container are still in pending state. Thus the SET CONTAINER is different from using CONNECT. When you use CONNECT, the current transaction is committed and new connection is established with the container (PDB).
1-Jun #oratidbit Cluster Verification Utility (CVU) is a command-line tool used for preinstallation and postinstallation checks of Oracle Clusterware and Oracle RAC components. OUI runs CVU after the Oracle Clusterware installation to verify the environment.
2-Jun #oratidbit Cluster Ready Services Control (CRSCTL) is a command-line tool that you can use to start and stop Oracle Clusterware and to determine the current status of your Oracle Clusterware installation. CRSCTL manages Oracle Clusterware daemons. These daemons include Cluster Synchronization Services (CSS), Cluster-Ready Services (CRS), and Event Manager (EVM).
3-Jun #oratidbit Oracle Automatic Storage Management Configuration Assistant (ASMCA) supports installing and configuring Oracle ASM instances, disk groups, volumes, and Oracle Automatic Storage Management Cluster File System (Oracle ACFS). ASMCA provides both a GUI and a non-GUI interface.
4-Jun #oratidbit Oracle Automatic Storage Management Command Line utility (ASMCMD) is used to manage Oracle ASM instances, Oracle ASM disk groups, file access control for disk groups, files and directories within Oracle ASM disk groups, templates for disk groups, and Oracle ASM volumes.
4-Jun #oratidbit If you use ASMCMD, SRVCTL, SQL*Plus, or LSNRCTL to manage Oracle ASM or its listener, then use the executable files located in the Grid home, not the executable files located in the Oracle Database home, and set the ORACLE_HOME environment variable to the location of the Grid home.
5-Jun #oratidbit If you use SRVCTL, SQL*Plus, or LSNRCTL to manage a database instance or its listener, then use the binaries located in the Oracle home where the database instance or listener is running, and set the ORACLE_HOME environment variable to the location of that Oracle home.
8-Jun #oratidbit After Oracle Grid Infrastructure (or Database software) installation is complete, do not remove manually or run cron jobs that remove /tmp/.oracle or /var/tmp/.oracle directories or their files while Oracle software is running on the server. If you remove these files, then the Oracle software can encounter intermittent hangs.
9-Jun #oratidbit Use rconfig or Oracle Enterprise Manager to convert a single-instance database to an Oracle Real Application Clusters (Oracle RAC) database. rconfig is a non-interactive command line utility.
10-Jun #oratidbit In Oracle Real Application Clusters (Oracle RAC), the voting disk records node membership information. Oracle Cluster Registry (OCR) is a file that contains information about the cluster node list and instance-to-node mapping information.
11-Jun #oratidbit If the Oracle Clusterware files (voting disk and OCR) are stored in an Oracle ASM disk group, then the only way to shut down the Oracle ASM instances is to shut down the Oracle Clusterware stack.
12-Jun #oratidbit Oracle RAC, the voting disk data is automatically backed up in OCR as part of any configuration change so you do not have to perform manual backups of the voting disk.
15-Jun #oratidbit Oracle Clusterware automatically creates OCR backups every four hours. At any one time, Oracle Clusterware always retains the latest three backup copies of the OCR that are four hours old, one day old, and one week old. Use “ocrconfig -showbackup” to view the available backups.
16-Jun #oratidbit In #Oracle #DB12c 12.1.0.2, Oracle ASM Filter Driver (Oracle ASMFD) is installed with Grid Infrastructure installation. This filter helps to prevent users with administrative privileges from inadvertently overwriting Oracle ASM disks.
17-Jun #oratidbit Oracle Flex ASM enables an Oracle ASM instance to run on a separate physical server from the database servers. Many Oracle ASM instances can be clustered to support a large number of database clients.
18-Jun #oratidbit #Oracle Enterprise Manager uses ASMSNMP account to monitor Oracle ASM instances to retrieve data from Oracle ASM-related data dictionary views. The ASMSNMP account status is set to OPEN upon creation, and it is granted the SYSDBA privilege.
19-Jun #oratidbit If you set ORACLE_BASE variable during DB or GI installation, then Oracle Universal Installer creates the Oracle Inventory directory one level above the ORACLE_BASE in the path $ORACLE_BASE/../oraInventory. Inventory location is saved in file /etc/oraInst.loc (or /var/opt/oracle/oraInst.loc on Solaris).
22-Jun #oratidbit chopt tool can be used to enable or disable database options such as OLAP, Partitioning, data mining, real application testing. You must shutdown database before using this tool to enable or disable an option.
23-Jun #oratidbit In #oracle #db12c Performance Hub active report enables you to view all performance data available for a time period and can be generated using perfhubrpt.sql.
24-Jun #oratidbit In Oracle DB, DB time is calculated by combining the times from all non-idle user sessions, it is possible that the DB time can exceed the actual time elapsed after the instance started. DB time represents the total time spent in database calls and is an indicator of the total instance workload.
25-Jun #oratidbit Setting STATISTICS_LEVEL to BASIC disables AWR. If STATISTICS_LEVEL is set to BASIC, you can still manually capture AWR statistics using the DBMS_WORKLOAD_REPOSITORY package. However, because in-memory collection of many system statistics—such as segments statistics and memory advisor information—will be disabled, the statistics captured in these snapshots may not be complete.
26-Jun #oratidbit awrextr.sql script extracts AWR data for a range of snapshots from the database into a Data Pump export file. After it is created, you can transport this dump file to another database where you can load the extracted data using awrload.sql.
29-Jun #oratidbit in #Oracle #EM12c, Real-Time ADDM helps you to analyze and resolve problems in unresponsive or hung database that traditionally require you to restart the database. Real-Time ADDM helps you to resolve the identified issues such as deadlocks, hangs, shared pool connections, and other exception situations.
30-Jun #oratidbit ADDM analysis of I/O performance depends DBIO_EXPECTED prameter. The value of DBIO_EXPECTED is the average time it takes to read a single database block in microseconds. Default is 10 milliseconds. For significantly faster I/O subsystem, adjust the parameter value using DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER (‘ADDM’, ‘DBIO_EXPECTED’, );