OracleNotes

Author: Biju

  • 12 Days of #Cloud: Connecting to #Oracle #DBaaS using #SQLDev

    image2212 Days of #Cloud: Day 1

    There are two ways for SQL Developer to connect to a DBaaS Cloud Instance.

    Review how to create a DBaaS Cloud Instance.

    1. Using SQL*Net

    By default, the Oracle DBaaS Cloud Service blocks access to 1521 port used by SQL*Net. You have to open the port before you can connect using SQL Developer.

    Log in to the Oracle Database Cloud Service and click on the service name to see the details. Note down the Public IP address and the Connect String.

    Click on the Database Cloud Service menu and choose “Access Rules”.

    Enable the “ora_p2_dblistener” access rule, by clicking the menu under Actions.

    Now, you can use SQL Developer from your desktop to connect to the Cloud Database as you would connect to any on-premise database. Use the Public IP address as the hostname and the service name in the connect string.

    2. Using SSH

    This method is a bit more secure. Let’s turn off the 1521 port to make sure our connection using SSH works indeed. Confirm SSH is enabled.

    In SQL Developer menu, click on View –> SSH. Then right click and choose “Create New SSH Host”.

    Provide the Cloud Database Service Public IP address and the SSH key file. Enable port forwarding on port 1521.

    Oops, I chose a wrong key file. Instead of the private key, selected the public key file. Let me fix by editing the SSH host entry.

    Now choose “New Connection” in SQL Developer, and configure a connection using connection type SSH. You still have to enter the service name of the cloud database.

    When you click Test button,  you will be asked the SSH key passphrase.

    And, that’s it. You are ready to go.

     

  • Oracle Tidbits – November 2016 #oratidbit

    Oracle TidBit
    Oracle TidBits

    “Oracle *daily* TidBits” (#oratidbit) published at https://www.facebook.com/oraclenotes on weekdays in November 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 The orapki command line utility enables administrators to manage wallets, certificate revocation lists, and other public key infrastructure (PKI) elements from the command line. It can be used inside scripts, enabling administrators to automate many routine PKI tasks.
    #oratidbit By default, Oracle database audit trail records are written to the SYSAUX tablespace. You can designate a different tablespace by using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure.
    #oratidbit In #DB12c Oracle Database writes unified audit records to system global area (SGA) queues and then periodically writes them to the AUDSYS schema audit table in the SYSAUX tablespace.
    #oratidbit UNIFIED_AUDIT_SGA_QUEUE_SIZE initialization parameter sets the size of SGA area where unified audit queues are saved before writing to AUDSYS schema.
    #oratidbit Use the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL procedure to manually purge the audit trail from Oracle Database.
    #oratidbit In #DB12c the READ object privilege and READ ANY TABLE system privilege are more secure than SELECT and SELECT ANY TABLE privileges. SELECT privilege includes LOCK capability on the table/rows.
    #oratidbit In #DB12c DB_WRITER_PROCESSES can be up to 100. The processes are DBW0-DBW9, DBWa-DBWz and BW36-BW99.
    #oratidbit #DB12cR2 Duties that belonged exclusively to PMON now belong to the PMON process group, which includes PMON, Cleanup Main Process (CLMN), and Cleanup Helper Processes (CLnn). This process group is responsible for the monitoring and cleanup of other processes.
    #oratidbit #DB12cR2 Optimizer Statistics Advisor analyzes how you are currently gathering statistics, the effectiveness of existing statistics gathering jobs, and the quality of the gathered statistics. Optimizer Statistics Advisor maintains rules, which embody Oracle best practices based on the current feature set.
    #oratidbit #DB12cR2 #Oracle Sharding is a scalability and availability feature for suitable OLTP applications in which data is horizontally partitioned across discrete Oracle databases, called shards, which share no hardware or software.
    #oratidbit #DB12cR2 You can relocate a PDB from one CDB to another with minimal down time.
    #oratidbit #DB12cR2 During a PDB clone operation, the source PDB no longer needs to be in read-only mode.
    #oratidbit #DB12cR2 You can create a proxy PDB, which references a PDB in a different CDB and provides fully functional access to the referenced PDB.
    #oratidbit #DB12cR2 You can use FLASHBACK PLUGGABLE DATABASE command to rewind a PDB to any SCN without affecting other PDBs in a CDB. You can also create a restore point specific for a PDB, and rewind the PDB to this restore point without affecting other PDB.
    #oratidbit #DB12cR2 A PDB lockdown profile is a security mechanism to restrict operations that are available to users connected to a specified PDB.
    #oratidbit #DB12cR2 You can configure PDB parameters to guarantee or limit SGA memory, PGA memory, sessions, CPU, and I/O rates for each PDB.
    #oratidbit #DB12cR2 You can create local, nonshared temporary tablespaces in PDB. When many read-only instances access a single database, local temporary tablespaces can improve performance for queries that involve sorts, hash aggregations, and joins.
    #oratidbit #DB12cR2 You can pre-create a pool of server processes by using the DBMS_PROCESS PL/SQL package. The new Process Manager (PMAN) background process monitors the pool of pre-created processes, which wait to be associated with a client request.
    #oratidbit #DB12cR2 Read-only and read-write instances can co-exist within a single database. To designate an instance as read-only, set the INSTANCE_MODE initialization parameter to READ_ONLY. The default value of the parameter is READ_WRITE.
    #oratidbit #DB12cR2 In the root of a CDB, you can configure a single port (known as the global port), which enables you to use EM Express to connect to all of the PDBs in the CDB using the HTTPS port for the CDB.
    #oratidbit #DB12cR2 You can capture Automatic Workload Repository (AWR) data for the PDBs in a multitenant environment. This feature enables performance tuning of PDBs in a multitenant environment.
    #oratidbit #DB12cR2 In EM Express, you can view real-time performance statistics for a standby database in the Performance Hub.