OracleNotes

Category: TidBits

  • Oracle Tidbits – May 2016

    “Oracle *daily* TidBits” (#oratidbit) published at https://www.facebook.com/oraclenotes on weekdays in May 2016. 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 101: In a CDB, the UNDO_MANAGEMENT parameter must be AUTO. There is one active undo tablespace for a single-instance CDB. Cannot have separate UNDO tablespace for PDBs.
    #oratidbit To create a service for a PDB using the SRVCTL utility, use the add service command and specify the PDB in the -pdb parameter. If you do not specify a PDB in the -pdb parameter when you create a service, then the service is associated with the root.
    #oratidbit PDB management with Oracle EM Express is available starting with Oracle Database 12c Release 1 (12.1.0.2).
    #oratidbit There is a default temporary tablespace for the entire CDB. You optionally can create additional temporary tablespaces for use by individual PDBs.
    #oratidbit Specify STORAGE clause in CREATE PLUGGABLE DATABASE to specify the amount of storage that can be used by all tablespaces that belong to the PDB. Default is UNLIMITED.
    #oratidbit Cluster Verification Utility (CVU) is a command-line tool that you can use for preinstallation and postinstallation checks of your cluster environment. OUI runs CVU after the Oracle Clusterware installation to verify the environment.
    #oratidbit Oracle Cluster Registry (OCR) maintains cluster configuration information and configuration information about any cluster database within the cluster. The OCR contains information such as which database instances run on which nodes and which services run on which databases.
    #oratidbit For a fixed-size undo tablespace, Oracle Database automatically tunes the system to give the undo tablespace the best possible undo retention. For an automatically extensible undo tablespace, Oracle Database retains undo data longer than the longest query duration and the low threshold of undo retention specified by the UNDO_RETENTION parameter.
    #oratidbit Query V$UNDOSTAT.TUNED_UNDORETENTION to determine the amount of time for which undo is retained for the current undo tablespace.
    #oratidbit To use Oracle Flashback Transaction Query feature, supplemental logging must be enabled in the database – ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
    #oratidbit When upgrading your database, DBUA expects that both the source (pre-12c) Oracle home and the destination (new 12c) Oracle home are owned by the same user.
    #oratidbit Starting with Oracle Database 12c, Oracle XML DB is a mandatory component of Oracle Database. You cannot uninstall Oracle XML DB, and there is no option to exclude it when you create an Oracle database.
    #oratidbit Starting with Oracle Database 11g Release 2 (11.2), setting JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and DBMS_JOB jobs to not run.
    #oratidbit A Global Data Services (GDS) configuration and its global services are created and managed using the GDSCTL command-line interface. GDS is a highly effective solution for automatic workload management across a set of replicated databases.
    #oratidbit Oracle NoSQL Database is available in Community Edition (CE) and Enterprise Edition (EE). Only EE has integration with Oracle Stack such as Oracle Database External Table Integration, Oracle Big Data SQL integration, and so on.
    #oratidbit You can model data in Oracle NoSQL Database by using Tables, JSON schemas or a raw key-value interface. Tables are the easiest way to model data and they provide the highest level of abstraction.
    #oratidbit Setting database initialization parameter STATISTICS_LEVEL to BASIC disables many Oracle Database features, including ADDM. Setting parameter CONTROL_MANAGEMENT_PACK_ACCESS to NONE also disables ADDM.
    #oratidbit ADDM analysis of I/O performance partially depends on DBIO_EXPECTED, which is the average time it takes to read a single database block in microseconds – default is 10 milliseconds.

  • Oracle Daily Tidbits – April 2016

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

    TidBit
    #oratidbit ORACLE_OCM is a predefined non-administrative account in DB that is expired and locked, used by Oracle Configuration Manager (OCM) used to associate the configuration information for the Oracle Database instance with My Oracle Support. When you log a service request, information is readily available and associated to the Service Request.
    #oratidbit SQL92_SECURITY parameter specifies whether users must be granted the SELECT object privilege to execute UPDATE or DELETE statements. Default is FALSE and you do not require SELECT to perform UPDATE or DELETE on table. So, by using DELETE/UPDATE statements with different WHERE clauses you may be able to verify the content of table.
    #oratidbit Oracle Virtual Private Database (VPD), Oracle Label Security (OLS), and Oracle Data Redaction enable you to restrict the data that different users can see in database tables. #security
    #oratidbit Use DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL procedure to clean audit records from unified audit trail (UNIFIED_AUDIT_TRAIL) and standard audit trail (DBA_AUDIT_TRAIL).
    #oratidbit 101: ALL_ALL_TABLES shows tables (relational and object) that you have access to, USER_ALL_TABLES shows tables you own, DBA_ALL_TABLES shows tables in the database or PDB, CDB_ALL_TABLES shows tables from all PDB.
    #oratidbit 101: On non-CDB, CDB_ and DBA_ views have same content. The CDB_ view has CON_ID=0 for all rows.
    #oratidbit To enable full database caching in 12c, do ALTER DATABASE FORCE FULL DATABASE CACHING in MOUNT state of DB. Enabling force full database cache mode applies to the CDB and all PDBs when using the multitenant option.
    #oratidbit In 12.1.0.1, job_queue_process was a Container Database (CDB) modifiable parameter (ie. at a global level). In 12.1.0.2, the job_queue_process parameter is not CDB modifiable; instead it’s PDB modifiable which means each PDB can have its own job_queue_process value.
    #oratidbit Oracle Scheduler in 12c has different interpreters to run custom jobs. SQL Script uses sqlplus, External script uses unix shell or windows command, Backup script uses RMAN.
    #oratidbit In 12c, DBMS_STATS.REPORT_STATS_OPERATIONS API can be used to find if automated statistics job or manual statistics collected stats on a table. Filter automatic stats operation by using AUTO_ONLY => TRUE.
    #oratidbit To preserve the in-memory column store configuration during import, use TRANSFORM=INMEMORY:Y option in impdp. When the TRANSFORM=INMEMORY:n option is specified, Data Pump drops the IM column store clause from all objects that have one.
    #oratidbit You can use full transportable export/import to upgrade a database from an Oracle Database 11g Release 2 (11.2.0.3) or later to Oracle Database 12c.
    #oratidbit In a CDB, the default Data Pump directory object, DATA_PUMP_DIR, does not work with PDBs. You must define an explicit directory object within the PDB that you are using with Data Pump export/import.
    #oratidbit 101: Oracle Container Database CDB includes CDB$ROOT (Oracle-supplied metadata and common users), PDB$SEED (template to create new PDBs) and Pluggable Databases.
    #oratidbit The Oracle Pluggable Database Self-Service Provisioning application provides an interface to Oracle Database 12c Multitenant and allows for the self-service provisioning of Pluggable Databases (PDBs). Available from 12.1.0.2.
    #oratidbit Oracle PDB Self-service provisioning application requires AL32UTF8 database characterset, Oracle APEX 4.2.5+ and REST Data Services 2.0.7+.
    #oratidbit 101: A CDB uses single SPFILE. The user who creates or modifies the SPFILE must be a common user with SYSDBA, SYSOPER, or SYSBACKUP administrative privilege.
    #oratidbit 101: All of the PDBs in the CDB must use the characterset of the CDB.
    #oratidbit 101: There is separate SYSTEM and SYSAUX tablespaces for CDB root (CDB$ROOT) and each PDB.