Tag: oratidbit

Posted on

Oracle Tidbits – June 2018 #oratidbit

“Oracle *daily* TidBits” (#oratidbit) published on  Facebook, Twitter, and Google+ during weekdays in June 2018. Hope you find these helpful to learn something new or to remind you of its existence and use.

#oratidbit #db18c A shared single client access name (Shared SCAN) enables the sharing of one set of SCAN virtual IPs (VIPs) and Listeners on one dedicated cluster in the data center with other clusters to avoid the deployment of one SCAN setup per cluster.
#oratidbit #db18c You can convert a conventional disk group (disk group created before Oracle ASM 18c) to an Oracle ASM flex disk group without using the restrictive mount (MOUNTED RESTRICTED) option.
#oratidbit #orachk #exachk Oracle Health Check Collections Manager by default purges collections that are older than three months.
#oratidbit #db18c You can drop a file group and its associated files (drop including content) using the CASCADE keyword with ALTER DISKGROUP … DROP FILEGROUP SQL statement.
#oratidbit #orachk #exachk The -failedchecks option to run only the checks that failed previously. After fixing the issues identified by orachk, you may generate another health check report to verify only the issues that failed before.
#oratidbit The Oracle ASM Intelligent Data Placement (IDP) feature has been deprecated since Oracle ASM 12c Release 2 (12.2) and desupport is planned in a future release.
#oratidbit Oracle #ORAchk supports Oracle Grid Infrastructure stand-alone checks where no database is installed. Use the option -nordbms.
#oratidbit #db18c The Data Pump Import DATA_OPTIONS parameter has a new CONTINUE_LOAD_ON_FORMAT_ERROR option. If Data Pump encounters a stream format error, it attempts to skip forward to find the start of the next row and continue loading data from that location.
#oratidbit #db18c The DATA_OPTIONS parameter of the DBMS_DATAPUMP.SET_PARAMETER procedure has a new option, KU$_DATAOPT_CONT_LOAD_ON_FMT_ERR, to continue loading data from the next row if it encounters stream format errors in the table data.
#oratidbit Use the ENCRYPTION_MODE=TRANSPARENT setting to transparently encrypt the data pump dump file set with the TDE master encryption key stored in the keystore. The keystore must be present and open at the target database during an import operation.
#oratidbit #db18c #SQLDev #ADWC SQL Developer 18.1 Data Import Wizard can load data from files in the Oracle Cloud (or local storage) to Autonomous Data Wareshouse Cloud using the DBMS_CLOUD API.
#oratidbit #SQLDev #ADWC SQL Developer Cloud Migrations wizard can migrate schemas, objects (tables), and data from an Amazon #Redshift database to Oracle Autonomous Data Warehouse Cloud.
#oratidbit #SQLDev SQL Developer provides interactive advisories in the code editor identifying code that is at risk of SQL injection. First-order injection is when user-specified data from function parameters is used in a dynamic SQL statement that can be exploited.
#oratidbit #sqlcl SET HISTORY BLACKLIST allows you to set the commands that should not be recorded in history. By default, the SHOW, HISTORY, CONNECT, and SET commands are not saved in history.
#oratidbit PL/SQL data type PLS_INTEGER has advantages over the NUMBER data type. Requires less storage and uses hardware arithmetic, so they are faster than NUMBER operations, which use library arithmetic.
#oratidbit #sqldev SQL Developer Web is bundled with Oracle Public Cloud Database Services. Access to SQL Developer Web is provided through schema-based authentication, after enabling the schema for SQL Developer Web.
#oratidbit #sqldev You can use SQL Developer Web to monitor the CPU utilization, memory, storage, and operating system processes for the DBaaS compute node.
#oratidbit Init parameter CURSOR_INVALIDATION controls whether deferred or immediate cursor invalidation is used for DDL statements. Deferred invalidation spreads the recompilation workload over time. A cursor may run with a sub-optimal plan until recompiled.
#oratidbit #db18c To create a schema only account, replace the IDENTIFIED BY clause with NO AUTHENTICATION in a CREATE USER statement. You can grant system privileges and administrator roles to schema only accounts. A schema only account cannot log in to the database.
Posted on

Oracle Tidbits – May 2018 #oratidbit

“Oracle *daily* TidBits” (#oratidbit) published on Facebook, Twitter, and Google+ during weekdays in May 2018. Hope you find these helpful to learn something new or to remind you of its existence and use.

#oratidbit #db18c When cloning a non-CDB as a PDB or relocating a PDB, use the DBMS_PDB.EXPORTRMANBACKUP procedure to export RMAN backup metadata into the PDB dictionary, enabling backups of the source non-CDB or PDB to be usable for restore and recovery of the target PDB.
#oratidbit #db18c When a PDB resides in Oracle ASM, you can use a split mirroring technique to clone a PDB. The cloned PDB is independent of the original PDB.
#oratidbit #multitenant Every CDB has one and only one root container named CDB$ROOT. The root stores the system metadata required to manage PDBs. All PDBs belong to the root. The system container is the CDB root and all PDBs that belong to this root.
#oratidbit #multitenant A proxy PDB refers to a remote PDB, called the referenced PDB. Although you issue SQL statements in the proxy (referring) PDB, the statements execute in the referenced PDB. A proxy PDB is loosely analogous to a symbolic link file in Linux.
#oratidbit #multitenant By default, a user connected to one PDB must use database links to access objects in a different PDB in the same CDB. This behavior is directly analogous to a user in a non-CDB accessing objects in a different non-CDB.
#oratidbit #orclebs When a user tries to login and cannot be validated, meaning is not a valid user, a record is inserted into FND_UNSUCCESSFUL_LOGINS table for tracking purposes with username ANONYMOUS.
#oratidbit #orclebs The E-Business Suite Technology Codelevel Checker (ETCC) tool helps to identify application or database tier overlay patches that need to be applied to the Oracle E-Business Suite Release 12.2 system.
#oratidbit Oracle Database 18c is the terminal release for support of the Oracle Streams feature. Oracle Streams will be desupported from Oracle Database 19 onwards. Oracle Streams was deprecated in Oracle Database 12c (12.1).
#oratidbit #db18c When a PDB resides in Oracle ASM, a split mirroring technique can be used to clone a PDB. The cloned PDB is independent of the original PDB. The principal use case is to rapidly provision test and development PDBs in an Oracle ASM environment.
#oratidbit #orcldb The utllockt.sql script displays, in a tree fashion, the sessions in the system that are waiting for locks and the locks that they are waiting for.
#oratidbit #orcldb DBA_DDL_LOCKS view lists all DDL locks held in the database and all outstanding requests for a DDL lock. DBA_DML_LOCKS lists all DML locks held in the database and all outstanding requests for a DML lock.
#oratidbit DBMS_UTILITY.GET_TIME function determines the current time in 100th’s of a second. Call this function twice- at the beginning and end of some process– and then the first (earlier) number is subtracted from the second (later) number to determine the time elapsed.
#oratidbit DBMS_UTILITY.EXPAND_SQL_TEXT procedure recursively replaces any view references in the input SQL query with the corresponding view subquery.
#oratidbit DBMS_UTILITY.GET_DEPENDENCY procedure shows the dependencies on the object passed in. Uses the DBMS_OUTPUT package to display results, so declare SET SERVEROUTPUT ON to view dependencies.
#oratidbit #db18c Storage of private temporary table is memory only. Storage of global temporary table (pre-18c) is disk (temp tablespace). For global and private temporary tables, the database allocates temporary segments when a session first inserts data.
#oratidbit #db18c Starting 18c, each pluggable database (PDB) can have its own keystore, instead of there being only one keystore for the entire container database (CDB).
#oratidbit #db18c In a WITH or FROM clause of a SELECT statement, a query of an analytic view may include the FILTER FACT keywords to filter the data accessed by the analytic view prior to aggregation.
#oratidbit Analytic views provide a fast and efficient way to create analytic queries of data stored in existing database tables and views. Analytic views organize data using a dimensional model.
#oratidbit The #ORAchk Upgrade Readiness Assessment can be used to obtain an automated upgrade specific health check for upgrades to 11.2.x to 12.2.x. Many of the manual pre and post checks detailed in various upgrade related documents are automated using ORAchk.
#oratidbit Use the DBA_REGISTRY view to check the status of the database components. Status of NOSCRIPT indicates that the component is enabled, but there is no upgrade/downgrade script for this component.
#oratidbit Database Upgrade Assistant (DBUA) gets the information of the databases available for upgrade from the /etc/oratab file, and other locations in the oraInventory.
#oratidbit When upgrading database using DBUA, hidden parameters are not carried to the target database by DBUA. To keep the underscore (hidden) parameters after an upgrade, invoke “dbua -keepHiddenParams”.