OracleNotes

Category: TidBits

  • Oracle Tidbits – July 2015 #oratidbit

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

    Publish Date TidBit
    1-Jul #oratidbit use V$INSTANCE_RECOVERY view column OPTIMAL_LOGFILE_SIZE to determine the size of your online redo logs. This column shows the redo log file size in megabytes that is considered optimal based on the current setting of FAST_START_MTTR_TARGET.
    2-Jul #oratidbit The V$PROCESS view contains one row for each Oracle process connected to the database instance. Columns program, pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem show the PGA memory usage for each connection.
    3-Jul #oratidbit check out the V$OSSTAT view, which displays system utilization statistics from the operating system, one row for each system statistic.
    6-Jul #oratidbit In #DB12c, the SYSBACKUP administrative privilege encompasses the permissions required for backup and recovery, including the ability to connect to a closed database. System administrators can grant SYSBACKUP instead of SYSDBA to users who perform backup and recovery, thus reducing the proliferation of the SYSDBA privilege. In contrast to SYSDBA, SYSBACKUP does not include data access privileges such as SELECT ANY TABLE.
    7-Jul #oratidbit In #DB12c, #RMAN supports DESCRIBE command similar to SQL*Plus.
    8-Jul #oratidbit In #DB12c, #RMAN DUPLICATE DATABASE has NOOPEN clause to keep the database in mount state after duplicate.
    9-Jul #oratidbt The REPORT UNRECOVERABLE command in #RMAN lists all data files for which an unrecoverable operation has been performed against an object in the data file since the last backup of the data file.
    10-Jul #oratidbit The RESTORE DATABASE PREVIEW command in #RMAN can be used to report, but not restore, the backups that RMAN can use to restore. The database can be open when you run this command. RMAN queries the metadata and does not actually read the backup files.
    13-Jul #oratidbit The RECOVER CORRUPTION LIST command in #RMAN recovers all corrupted blocks listed in V$DATABASE_BLOCK_CORRUPTION.
    14-Jul #oratidbit #RMAN command-line argument CHECKSYNTAX can be used to validate RMAN command files. “rman CHECKSYNTAX @commandfile”. Argument CHECKSYNTAX starts the RMAN client in a mode in which it only parses the commands that you enter and returns an RMAN-00558 error for commands that are not legal RMAN syntax.
    15-Jul #oratidbit If you are licensed to use Oracle Advanced Compression option, you have HIGH, LOW, MEDIUM compression choices with #RMAN Backups. The default is CONFIGURE COMPRESSION ALGORITHM ‘BASIC’.
    16-Jul #oratidbit STORAGE_SIZE column in V$RESTORE_POINT is zero for normal restore points. For guaranteed restore points, STORAGE_SIZE indicates the amount of disk space in the fast recovery area used to retain logs required to guarantee FLASHBACK DATABASE to that restore point.
    17-Jul #oratidbit #RMAN in #DB12c excludes undo not needed for recovery of a backup, that is, for transactions that have been committed. Backup undo optimization works for disk backups and Oracle Secure Backup (OSB) tape backups.
    20-Jul #oratidbit The equivalent of SQL*Plus STARTUP RESTRICT in #RMAN is STARTUP DBA. STARTUP FORCE DBA is valid too. Restricts database access to users with the RESTRICTED SESSION privilege.
    21-Jul #oratidbit Trace File Analyzer Collector (TFA) has a collection of health check and diagnostic tools including orachk, exachk, SQLT, procwatcher, osw. Read MOS Note 1513912.2
    27-Jul #oratidbit #SQLDeveloper can format a .sql file or all .sql files in a directory, use the format.sh or format.bat on the operating system command line.
    28-Jul #oratidbit Using #SQLDeveloper you can connect to schemas for non-Oracle databases, such as MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, and IBM DB2, and view metadata and data in these databases; and you can migrate these non-Oracle databases to Oracle.
    29-Jul #oratidbit When you use #SQLDeveloper to connect to a schema that owns #APEX applications, you can perform Import, Deploy, Drop, Modify APEX application operations through the Application Express node in Navigator.
    30-Jul #oratidbit Using #SQLDeveloper you can display SQL Trace output files in a formatted way similar to TKPROF. You can examine the information in the List View, Statistics View, and History panes, with each pane including options for filtering and controlling the display.
    31-Jul #oratidbit #SQLDeveloper SQL Worksheet does not support few SQL*Plus commands such as archive, copy, oradebug, password, recover, run, startup, shutdown.

  • 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’, );