OracleNotes

Author: Biju

  • Exploring #Oracle #Cloud #DBaaS – The Start!

    Got excited to see Oracle Database 12.2 available on Oracle Cloud DBaaS, and wanted to try it out immediately. This blog is my first experience, and I will continue sharing as I learn Oracle DBaaS myself!

    Oracle has provided an excellent step by step quick start document. Though the document is not updated with 12.2 offering, it is pretty good to get started, and hopefully, by the time you read this blog, the updated content would be available.

    Reference: Oracle Database Cloud – Database as a Service Quick Start

    I followed the steps as is for the following sections in the document:

    • Create SSH Private and Public Keys
    • Creating a Service Instance
      • Invoking the Create Database Cloud Service Wizard
      • Defining the Subscription Type

    The next section is “Selecting the Software Release”, here I picked the latest and greatest.

    No specific reason why I chose “High Performance” in the next screen, it looked good 🙂

    The next screen collects a lot of information and offers choices. The “?” icon is very helpful. The DBaaS service gives not just the database, but a host too, and we can choose the host compute level.

    As you can see here, you could have the host as small as 1 OCPU (remember one vCPU is different from one OCPU – An OCPU provides CPU capacity equivalent of one physical core of an Intel Xeon processor with hyper threading enabled. Each OCPU corresponds to two hardware execution threads, known as vCPUs) and 8GB RAM; or as big as 16 OCPU and 240GB RAM.

    Service Configuration section is where you specify the database host name (service name), compute size, time zone and the SSH public key file generated in the earlier “Create SSH Private and Public Keys” section.

    In the Database Configuration section, specify the storage required for the database, administrator password, database name and the first pluggable database name.

    In the Backup and Recovery Configuration section, there are options to perform local backup, cloud backup or no backup. I chose no backup for now, will enable backup at a later time.

    In this section, there are also configuration options to choose such as needing a standby database or configuring golden gate. I think these are pretty sleek, simple to the end-user.

    Now all required information are entered, and ready to create the DBaaS instance. The confirmation screen is shown.

    Fingers crossed, and I clicked 

    Got the instance creation progress screen.

    I thought this would be quick, waited 15 minutes, progress screen did not change. There are few other places to check status, such as the “Activity” section.

    After about 45 minutes, the instance creation was complete.

    The instance information screen showed all the details I need to connect to the database.

    The instance (host) seems to be down (yellow bar). So clicked on the menu icon, and chose Start.

        

    And, there I have my host and database ready to use!

    Next, I will share how to connect to this DBaaS instance using SQL Developer.

  • Oracle Tidbits – October 2016 #oratidbit

    Oracle TidBit
    Oracle TidBits

    “Oracle *daily* TidBits” (#oratidbit) published at https://www.facebook.com/oraclenotes on weekdays in October 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 In Oracle #DB12c 12.2, if Container database Character Set is AL32UTF8 then PDBs are allowed to have different character set.
    #oratidbit Oracle Health Check Collections Manager is a companion application to Oracle #ORAchk that gives you an enterprise-wide view of health check collection data. It provides a dashboard to track your collection data in one easy-to-use interface using APEX.
    #oratidbit Oracle #ORAchk health check collection can be scheduled to run periodically by using the -set option. The AUTORUN_INTERVAL daemon option provides an alternative method of regularly running health checks.
    #oratidbit Oracle #ORAchk and Oracle #EXAchk automatically compare the two most recent HTML reports and generate a third diff report, when run in automated daemon mode.
    #oratidbit Oracle #ORAchk and Oracle #EXAchk are capable of uploading collection results from multiple instances into a single database for easier consumption of check results using Oracle Health Check Collections Manager or using custom application.
    #oratidbit A non-partitioned table in Oracle #DB12cR2 can be converted to a partitioned table by using the MODIFY clause of ALTER TABLE. MODIFY PARTITION clause specifies the partition properties.
    #oratidbit In Oracle #DB12cR2 new function VALIDATE_CONVERSION can be used to identify problem data that cannot be converted to the required data type. It returns 1 if a given expression can be converted to the specified data type, else it returns 0. Example: VALIDATE_CONVERSION(‘$100,00’ AS NUMBER, ‘$999D99’, ‘NLS_NUMERIC_CHARACTERS = ”,.”’) returns 1; VALIDATE_CONVERSION(‘$29.99’ AS BINARY_FLOAT) returns 0.
    #oratidbit In Oracle #DB12cR2 many data type conversion functions (TO_DATE, TO_NUMBER, etc) and CAST function includes data error handling using the “DEFAULT value ON CONVERSION ERROR” option. Example: TO_NUMBER(‘2,00’ DEFAULT 0 ON CONVERSION ERROR) returns 0 instead of error.
    #oratidbit In Oracle #DB12cR2 a table segment or table partition segment can be moved to a new segment (or tablespace) with the ONLINE keyword in ALTER TABLE … MOVE while DML operations can continue to run uninterrupted on the table.
    #oratidbit In Oracle #DB12cR2 most object names can be as long as 128 bytes. You no longer have to limit a table name to 30 characters, for example. Exceptions are database names (8 bytes); Names of disk groups, pluggable databases (PDBs), rollback segments, and tablespaces (30 bytes).
    #oratidbit In Oracle #DB12c, the DBMS_ILM package provides an interface for implementing Information Lifecycle Management (ILM) strategies using Automatic Data Optimization (ADO) policies.
    #oratidbit Oracle #DB12c Feature: In-database archiving enables to archive rows within a table by marking them as invisible. These invisible rows are in the database but are not visible to an application. The data in these rows is available for compliance purposes if needed by setting session parameter “alter session set row archival visibility = all”.
    #oratidbit Oracle #DB12c Feature: In-database archiving is enabled by using the attribute ROW ARCHIVAL in the CREATE TABLE or ALTER TABLE statements. Invisible column ORA_ARCHIVE_STATE is added to the table with default value 0 (not archived).
    #oratidbit Materialized View Synchronous refresh is an approach for maintaining tables and materialized views in a data warehouse where tables and materialized views are refreshed at the same time. Tables are registered with synchronous refresh by creating a staging log on them.
    #oratidbit DBA_OPTSTAT_OPERATION_TASKS displays the history of tasks that are performed as part of statistics operations (recorded in DBA_OPTSTAT_OPERATIONS). Each task represents a target object to be processed in the corresponding parent operation. Task start time and end time columns help to know duration of each task.
    #oratidbit The HugePages Linux OS feature allocates non-swappable memory for large page tables using memory-mapped files. Automatic Memory Management should not be used when using HugePages – both MEMORY_TARGET and MEMORY_MAX_TARGET initialization parameters are unset.
    #oratidbit In #DB12c 12.1.0.2 the NO DATA clause of the CREATE PLUGGABLE DATABASE statement specifies that a PDB’s data model definition is cloned but not the PDB’s data. The dictionary data in the source PDB is cloned, but all user-created table and index data from the source PDB is discarded. This clause is useful for quickly creating clones of a PDB with only the object definitions and no data. This clause does not apply to the SYSTEM and SYSAUX tablespaces.
    #oratidbit While creating #DB12c, DBCA picks a free port from 5500 to 5599 for Enterprise Manager Express. If you want a specific port to be used, set the environment variable DBEXPRESS_HTTPS_PORT before starting Oracle Universal Installer (OUI) or Database Configuration Assistant (DBCA).
    #oratidbit Oracle Wallet Manager is used to manage public key security credentials on Oracle clients and servers. The wallets it creates can be read by Oracle Database, Oracle Application Server, and the Oracle Identity Management infrastructure.