Oracle Tidbits – June 2017 #oratidbit

Oracle *daily* TidBits” (#oratidbit) published on Facebook, Twitter and Google+ during weekdays in June 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 #orclebs Starting with Release 12.1.3 it is possible to create an application user (FND User) with the responsibility ”Concurrent Manager Operator”. Use this user’s username and password start and stop the application services (use -secureapps option) instead of APPS password.
#oratidbit #orcldb After Applying Oracle Database 12.1.0.2 with July 2016 PSU/BP, Shared Memory segment permission changed to 600 (is no longer readable for the group but the owner). This is the default behavior for 12.2 database. Use the ALLOW_GROUP_ACCESS_TO_SGA to set the default permissions for shared memory segments.
#oratidbit #orcldb #db12cR2 SQL*Loader, expdp, impdp, exp, and imp tools are part of the instant client starting 12.2.
#oratidbit #orcldb #db12cR2 In Database 12.2, a pluggable database can have different characterset if the characterset of CDB is AL32UTF8. Applicable only to PDB clones and unplug/plug operations.
#oratidbit #orcldb The init.ora parameter ENABLE_DDL_LOGGING is licensed as part of the Database Lifecycle Management Pack when set to TRUE. When set to TRUE, the database reports schema changes in real time into the database alert log under the message group schema_ddl.
#oratidbit #orcldb #db12cR2 Memory usage can be controlled at the PDB level using DB_CACHE_SIZE, SHARED_POOL_SIZE, PGA_AGGREGATE_LIMIT, PGA_AGGREGATE_TARGET, SGA_MIN_SIZE, SGA_TARGET initialization parameters.
#oratidbit #orcldb Init parameter NONCDB_COMPATIBLE=TRUE enables you to get behavior similar to a non-CDB when issuing SQL commands inside a PDB in a CDB. You may set this parameter if you are using a single PDB in the CDB configuration.
#oratidbit #orcldb #db12cR2 init parameters MAX_IOPS and MAX_MBPS enable you to limit disk I/O generated by a PDB. MAX_IOPS limits the number of I/O operations, and MAX_MBPS limits the megabytes for I/O operations.
#oratidbit #orcldb DBUA Log File Location: $ORACLE_BASE/cfgtoollogs/dbua/upgradesession_// if ORACLE_BASE is set. If not, $ORACLE_HOME/cfgtoollogs/dbua/upgradesession_//.
#oratidbit #orcldb Direct-path network import of datapump has a new parameter in #DB12cR2 – ENABLE_NETWORK_COMPRESSION. Tells Data Pump to compress data before sending it over the network.
#oratidbit Trace File Analyzer (TFA) facilitates faster one command Service Request Data Collection (SRDC). The SRDC types include ora600, ora7445, ora4030, dbperf, internalerror and so on. For more information about SRDCs, run “tfactl diagcollect –srdc -help”.
#oratidbit Procwatcher (part of TFA Bundle) is a tool to examine and monitor Oracle database and clusterware processes. Procwatcher will collect stack traces of these processes using Oracle tools like oradebug short_stack and/or OS debuggers like pstack, gdb, dbx, or ladebug and collect SQL data if specified.
#oratidbit #orcldb #db12cr2, Local undo is the default when you create a new database using DBCA in 12.2. To change from shared to local undo, you must start the database in UPGRADE mode and do “alter database local undo on;”
#oratidbit #orcldb Oracle ASM disks in a disk group should have similar storage performance characteristics. In storage configurations with mixed speed drives, I/O performance is constrained by the slowest speed drive.
#oratidbit #orcldb Back up, copy, or move an Oracle ASM SPFILE with the ASMCMD spbackup, spcopy, or spmove commands.
#oratidbit #orcldb Oracle Database Gather Statistics AUTO_SAMPLE_SIZE value for ESTIMATE_PERCENT uses a hash-based algorithm that provides deterministic statistics, addressing the two key aspects of accuracy and speed2. It has accuracy close to a 100% sample (“reads all data”) but with the cost of, at most, a 10% sample (memory based).
#oratidbit #orcldb #datapump import PARTITION_OPTIONS parameter options. MERGE: Create a non-partitioned table during import. DEPARTITION: Creates each partition or subpartition as a new individual table.
#oratidbit #orcldb #datapump The TRANSFORM parameter enables you to alter object creation DDL for objects being imported. Turn off archive logging using DISABLE_ARCHIVE_LOGGING:Y. After the data has been loaded, the logging attributes for the objects are restored to their original settings.
#oratidbit #orcldb #datapump The TRANSFORM parameter enables you to alter object creation DDL for objects being imported. SEGMENT_ATTRIBUTES:N makes segment attributes (physical attributes, storage attributes, tablespaces, and logging) not included with DDL (values default to user / tablespace defaults).
#oratidbit #orcldb #datapump The TRANSFORM parameter enables you to alter object creation DDL for objects being imported.SEGMENT_CREATION:N makes the SEGMENT CREATION clause omitted from the CREATE TABLE statement and uses the default segment creation attributes for the table(s) being loaded.
#oratidbit #orcldb #datapump Setting the parameter _OPTIMIZER_GATHER_STATS_ON_LOAD=FALSE might improve the table loading speed. Use this parameter if you include EXCLUDE=STATISTICS, and plan to collect stats after the import.