Manage Databases on Exadata Database Service on Cloud@Customer

Prerequisites and Limitations for Creating and Managing Oracle Databases on Oracle Exadata Database Service on Cloud@Customer

Review the prerequisites for creating and managing Oracle Databases on Oracle Exadata Database Service on Cloud@Customer.

Before you can create and use an Oracle Database on Exadata Database Service on Cloud@Customer, you must:

  • Provision Exadata Database Service on Cloud@Customer infrastructure
  • Configure a VM cluster
  • Create any required backup destinations

You can create one or more databases on each Oracle Exadata Database Service on Cloud@Customer system. Other than the storage and processing limits of your Oracle Exadata system, there is no maximum for the number of databases that you can create. By default, databases on Exadata Database Service on Cloud@Customer use Oracle Database Enterprise Edition - Extreme Performance. This edition provides all the features of Oracle Database Enterprise Edition, plus all of the database enterprise management packs, and all of the Enterprise Edition options, such as Oracle Database In-Memory, and Oracle Real Application Clusters (Oracle RAC). If you use your own Oracle Database licenses, then your ability to use various features is limited by your license holdings. TDE Encryption is required for all cloud databases. All new tablespaces will automatically be enabled for encryption.

Oracle Database Releases Supported by Oracle Exadata Database Service on Cloud@Customer

Learn about the versions of Oracle Database that Oracle Exadata Database Service on Cloud@Customer supports.

Exadata Database Service on Cloud@Customer supports the following Oracle Database software releases:

  • Oracle Database 19c (19.x)
  • Oracle Database 12c Release 2 (12.2.0.1) (requires a valid Upgrade Support contract)
  • Oracle Database 12c Release 1 (12.1.0.2) (requires a valid Upgrade Support contract)
  • Oracle Database 11g Release 2 (11.2.0.4) (requires a valid Upgrade Support contract)

For Oracle Database release and software support timelines, see Release Schedule of Current Database Releases (Doc ID 742060.1) in the My Oracle Support portal.

About Provisioning and Configuring Oracle Databases on Oracle Exadata Database Service on Cloud@Customer

Learn about provisioning and configuring Oracle Database on Oracle Exadata Database Service on Cloud@Customer

Each Oracle Database is configured as follows:
  • When you provision a database, you can associate it with a backup destination, and enable automatic backups.
  • When a database is provisioned an archivelog maintenance job is added to the crontab for the database.
    • If the database is not enabled for backups, then the archivelog job will maintain FRA space by deleting Archive Redo Logs older than 24 hours.
    • If the database is enabled for backups, then the archivelog job will backup archivelogs that have not been backed up. Once an archived log is backed up, it will be purged when older than 24 hours.
  • Each database is configured with Oracle Real Application Clusters (Oracle RAC) database instances running on every node in the virtual machine (VM) cluster.
  • Each database is created in an Oracle home, which uses a separate set of Oracle binaries in a separate Oracle home location.
  • Each database is configured with default instance parameter settings. While the defaults are reasonable for many cases, you should review the instance parameter settings to ensure that they meet your specific application needs.

    In particular, review the Oracle Database system global area (SGA) and program global area (PGA) instance parameter settings, especially if your VM cluster supports multiple databases. Also, ensure that the sum of all Oracle Database memory allocations never exceeds the available physical memory on each virtual machine.

    • When creating a container database, the initialization parameter, SGA_TARGET is set by the automation. This will automatically size the SGA memory pools. The setting will vary depending on the size of the database VM total memory. If the VM has less than or equal to 60 GB of system memory, SGA_TARGET is set to 3800 MB. If the VM has 60 GB or more system memory, SGA_TARGET is set to 7600 MB.
    • The database initialization parameter USE_LARGE_PAGES is set to ONLY upon database creation, which will require the use of large pages for SGA memory. If the VM is configured with insufficient large pages, the instance will fail to start.
    • The database initialization parameter INMEMORY_FORCE is set to CELLMEMORY_LEVEL for all 19.8 and later databases created via the cloud automation. This setting will enable the Exadata Columnar Cache feature, which dramatically speeds up analytic queries. It is available for 19.8 and later databases and no In Memory license is required when running in Exadata Cloud. For more information, see INMEMORY_FORCE
  • Exadata Database Service will only create databases with 8K block size. This parameter cannot be changed.
  • Each database using Oracle Database 12c Release 1 or a later release is configured as a container database (CDB). One pluggable database (PDB) is created inside the CDB. By default:
    • The first PDB is configured with a local PDB administration user account, named PDBADMIN.
    • The PDBADMIN user account is initially configured with the same administration password as the CDB SYS and SYSTEM users.
    • The PDBADMIN user account is initially configured with basic privileges assigned through two roles; CONNECT and PDB_DBA. However, for most practical administrative purposes you must assign extra privileges to the PDBADMIN user account, or to the PDB_DBA role.

    You can use native Oracle Database facilities to create extra PDBs, and to manage all of your PDBs. The dbaascli utility also provides a range of convenient PDB management functions.

Note

Avoid entering confidential information when assigning descriptions, tags, or friendly names to your cloud resources through the Oracle Cloud Infrastructure Console, API, or CLI.

Using the Console to Manage Databases on Oracle Exadata Database Service on Cloud@Customer

To create or terminate a database, complete procedures using the Oracle Exadata console.

Using the Console to Create a Database

To create an Oracle Database with the console, use this procedure.

  1. Open the navigation menu Under Oracle Database, and click Exadata Database Service on Cloud@Customer.

    VM Clusters is selected by default.

  2. Choose your Compartment.

    A list of VM Clusters is displayed for the chosen Compartment.

  3. Click the name of a VM cluster where you want to create the database.

    In the VM Cluster Details page, under Resources, Databases is selected by default.

  4. Click Create Database.

    (or)

    1. Open the navigation menu. Under Oracle Database, click Exadata Cloud@Customer.

      VM Clusters is selected by default.

    2. Choose your Compartment.

      A list of VM Clusters is displayed for the chosen Compartment.

    3. Click the name of a VM cluster where you want to create the database.

      In the VM Cluster Details page, under Resources, Databases is selected by default.

    4. Click Database Homes.
    5. Click the name of the Database Home where you want to create the database.
    6. Click Create Database.
  5. Provide the requested information in the Create Database page:

    Note

    You cannot modify the db_name, db_unique_name, and SID prefix after creating the database.
    • Provide the database name: Specify a user-friendly name that you can use to identify the database. The database name must contain only the permitted characters.
      Review the following guidelines when selecting a database name.
      • maximum of 8 characters
      • contain only alphanumeric characters
      • begin with an alphabetic character
      • cannot be part of first 8 characters of a db_unique_name on the VM cluster
      • unique within a VM cluster
      • DO NOT use grid because grid is a reserved name
      • DO NOT use ASM because ASM is a reserved name
    • Provide a unique name for the database: Optionally, specify a unique name for the database. This attribute defines the value of the db_unique_name database parameter. The value is case insensitive.

      The db_unique_name must contain only the permitted characters. Review the following guidelines when selecting a database name.
      • maximum of 30 characters
      • can contain alphanumeric and underscore (_) characters
      • begin with an alphabetic character
      • unique across the fleet/tenancy

      If a unique name is not provided, then the db_unique_name defaults to the following format <db_name>_<3 char unique string>_<region-name>.

      If you plan to configure the database for backup to a Recovery Appliance backup destination, then the unique database name must match the name that is configured in the Recovery Appliance.

    • Select a database version: From the list, choose the Oracle Database software release that you want to deploy.
    • Database Home: Select an existing Database Home or create one as applicable. Note that this field is not available when you create a Database from the Database Home details page.
      • Select an existing Database Home: If one or more Database Homes already exist for the database version you have selected, then this option is selected by default. And, you will be presented with a list of Database Homes. Select a Database Home from the list.
      • Create a new Database Home: If no Database Homes exist for the database version you have selected, then this option is selected by default.
        1. Enter Database Home display name.
        2. Click Change Database Image to select your software version.

          Select a Database Software Image window is displayed.

        3. Select an Image Type, Oracle Provided Database Software Images, or Custom Database Software Images.

          If you choose Oracle Provided Database Software Images, then you can use the Display all available version switch to choose from all available PSUs and RUs. The most recent release for each major version is indicated with a latest label.

          Note

          For the Oracle Database major version releases available in Oracle Cloud Infrastructure, images are provided for the current version plus the three most recent older versions (N through N - 3). For example, if an instance is using Oracle Database 19c, and the latest version of 19c offered is 19.8.0.0.0, images available for provisioning are for versions 19.8.0.0.0, 19.7.0.0, 19.6.0.0 and 19.5.0.0.
    • Provide the name of the first PDB: (Optional) Specify the name for the first PDB. A PDB is created with the database.

      To avoid potential service name collisions when using Oracle Net Services to connect to the PDB, ensure that the PDB name is unique across the entire VM cluster. If you do not provide the name of the first PDB, then a system-generated name is used.

    • Provide the administration password: Provide and confirm the Oracle Database administration password. This password is used for administration accounts and functions in the database, including:

      • The password for the Oracle Database SYS and SYSTEM users.
      • The Transparent Data Encryption (TDE) Keystore password.

      For Oracle Database 12c Release 1 or later releases, the password for the PDB administration user in the first PDB (PDBADMIN) must be nine to 30 characters and contain at least two uppercase, two lowercase, two numeric, and two special characters. The special characters must be _, #, or -. In addition, the password must not contain the name of the tenancy or any reserved words, such as Oracle or Table, regardless of casing.

      • Use the administrator password for the TDE wallet: When this option is checked, the password entered for the SYS user is also used for the TDE wallet. To set the TDE wallet password manually, uncheck this option and enter the TDE wallet password.
    • Backup Destination Type: Select a backup destination for the database. From the list, choose an option:

      • None: Select to not define a backup configuration for the database.
      • Local: Select to store backups locally in the Oracle Exadata Storage Servers on your Oracle Exadata Cloud at Customer system.

        This option is available only if you enabled backups on local Oracle Exadata storage in the VM cluster that you want to host the database.

      • Object Storage: Select to store backups in an Oracle-managed object storage container on Oracle Cloud Infrastructure.

        To use this option, your Oracle Exadata Cloud@Customer system must have egress connectivity to Oracle Cloud Infrastructure Object Storage.

      • NFS: Select to store backups in one of your previously defined backup destinations that use Network File System (NFS) storage. For more information, refer to the information about backup destinations in this publication.

        If you select this option, then you must also choose from the list of NFS Backup Destinations.

      • Recovery Appliance: Select to store backups in one of your previously defined backup destinations that use Oracle Zero Data Loss Recovery Appliance. Refer to the information about backup destination options in this document.

        If you select Oracle Zero Data Loss Recovery Appliance as your backup option, then you must also:

        • Choose from the list of appliance Backup Destinations.
        • Choose from the VPC User list, which contains the list of virtual private catalog (VPC) user names that are defined in the Oracle Zero Data Loss Recovery Appliance backup destination.
        • Provide the Password for the VPC user.
        Note

        If you select a backup destination, then you cannot change a backup location after the database is created. However, if you select None now, then you can select a backup destination after the database is created.

      • Enable automatic backups: Select this option to enable daily backups using the policy for automatic backups.

        This option is only enabled when you select a Backup Destination Type other than None. You can change this setting after database creation.

    • (Optional) Select Show Advanced Options. From this window, you can select the following options:

      • Provide the Oracle SID prefix:
        Note

        Entering a SID prefix is only available for 12.1 databases and above.

        Optionally, specify the Oracle SID prefix for the database. The instance number is automatically appended to the SID prefix to become the instance_name database parameter. If not provided, then the SID prefix defaults to the db_name.

        Review the following guidelines when selecting a database name:
        • maximum of 12 characters
        • contain only alphanumeric characters
        • begin with an alphabetic character
        • unique in the VM cluster
      • Backup retention period: From the list, you can choose the length of time that you want automatic backups to be retained.

        For backups to local Exadata storage, you can choose a retention period of 7 days or 14 days. The default retention period is 7 days.

        For backups to Oracle Cloud Infrastructure Object Storage, or to an NFS backup destination, you can choose one of the following preset retention periods: 7 days, 14 days, 30 days, 45 days, or 60 days. The default retention period is 30 days.

        This option does not apply to Oracle Zero Data Loss Recovery Appliance backup destinations. For backups to Oracle Zero Data Loss Recovery Appliance, the retention policy that is implemented in the appliance controls the retention period.

      • Character set: The character set for the database. The default is AL32UTF8.
      • National character set: The national character set for the database. The default is AL16UTF16.
      • Tags: (Optional) You can choose to apply tags. If you have permissions to create a resource, you also have permissions to apply free-form tags to that resource. To apply a defined tag, you must have permissions to use the tag namespace. For more information about tagging, refer to information about resource tags.If you are not sure if you should apply tags, then skip this option (you can apply tags later), or ask your administrator.
  6. Click Create Database.

Using the Console to Manage SYS User and TDE Wallet Passwords

Learn to manage administrator (SYS user) and TDE wallet passwords.

  1. Open the navigation menu. Click Oracle Database, then click Exadata Database Service on Cloud@Customer
  2. Choose your Compartment that contains the VM cluster that hosts the database that you want to change passwords.
  3. Click the name of the VM cluster that contains the database that you want to change passwords.
  4. In the Resources list of the VM Cluster Details page, click Databases.
  5. Click the name of the database that you want to change passwords.

    The Database Details page displays information about the selected database.

  6. On the Database Details page, click More actions, and then click Manage passwords.
  7. In the resulting Manage passwords dialog, click Update Administrator Password or Update TDE Wallet Password.

    Depending on the option you select, the system displays the fields to edit.

    • Update Administrator Password: Enter the new password in both the New administrator password and Confirm administrator password fields.
      Note

      The Update Administrator Password option will change the sys user password only. Passwords for other administrator accounts such as system, pdbadmin, and TDE wallet will not be changed.
    • Update TDE Wallet Password: Enter the current wallet password in the Enter existing TDE wallet password field, and then enter the new password in both the New TDE wallet password and Confirm TDE wallet password fields.
  8. Click Apply to update your chosen password.

Using the Console to Move a Database to Another Database Home

Learn to move a database to another Database Home.

  1. Open the navigation menu. Under Oracle Database, click Exadata Database Service on Cloud@Customer.
    VM Clusters is selected by default.
  2. Choose your Compartment that contains the VM cluster that hosts the database that you want to move.
  3. Click the name of the VM cluster that contains the database that you want to move.
  4. In the Resources list of the VM Cluster Details page, click Databases.
  5. Click the name of the database that you want to move.
    The Database Details page displays information about the selected database.
  6. Click Move Database.
  7. In the resulting dialog, select the target Database Home.
    Note

    Oracle recommends using Database Homes, which are running the latest (N) to 3 versions from the latest (N-3) RU versions when updating the software version of the database by moving them to a target DB Home. Only DB Homes provisioned with database versions, which meet this best practice criterion are available as target homes to move your database.
  8. Click Move Database.

The database will be stopped in the current home and then restarted in the destination home. While the database is being moved, the Database Home status displays as Moving Database. When the operation completes, Database Home is updated with the current home. If the operation is unsuccessful, the status of the database displays as Failed, and the Database Home field provides information about the reason for the failure.

Using the Console to Terminate a Database

You can terminate a database and thereby remove the terminated database from the Cloud Control Plane.

Terminating a database removes it from the Cloud Control Plane. In the process, all of the associated data files and backups are destroyed.
  1. Open the navigation menu. Under Oracle Database, click Exadata Database Service on Cloud@Customer.
    VM Clusters is selected by default.
  2. Choose your Compartment that contains the VM cluster that hosts the database that you want to terminate.
  3. Click the name of the VM cluster that contains the database that you want to terminate.
  4. In the Resources list of the VM Cluster Details page, click Databases.
  5. Click the name of the database that you want to terminate.
    The Database Details page displays information about the selected database.
  6. Click Terminate.
  7. In the resulting dialog, enter the name of the database, and then click Terminate Database to confirm the action.

Using the API to Manage Oracle Database Components

Use various API features to help manage your databases on Oracle Exadata Database Service on Cloud@Customer.

For information about using the API and signing requests, see "REST APIs" and "Security Credentials". For information about SDKs, see "Software Development Kits and Command Line Interface".

Use the following API operations to manage various database components.

Database homes:
  • CreateDbHome
  • DeleteDbHome
  • GetDbHome
  • ListDbHomes
Databases:
  • CreateDatabase
  • GetDatabase
  • ListDatabases
  • UpdateDatabase
  • UpdateDatabaseDetails
Nodes:
  • GetDbNode
  • List DbNodes

Use UpdateDatabase to move a database to a different Database Home, thereby updating the database to the same version as the target Database Home.

For the complete list of APIs, see "Database Service API".

Changing the Database Passwords

To change the SYS password, or to change the TDE wallet password, use this procedure.

The password that you specify in the Database Admin Password field when you create a new Exadata Database Service on Cloud@Customer instance or database is set as the password for the SYS, SYSTEM, TDE wallet, and PDB administrator credentials. Use the following procedures if you need to change passwords for an existing database.

Note

if you are enabling Data Guard for a database, then the SYS password and the TDE wallet password of the primary and standby databases must all be the same.
Note

Using the dbaascli to change the SYS password will ensure the backup/restore automation can parallelize channels across all nodes in the cluster.

To Change the SYS Password for an Exadata Database Service on Cloud@Customer Database

  1. Log onto the Exadata Database Service on Cloud@Customer virtual machine as opc.
  2. Run the following command:
    sudo dbaascli database changepassword --dbname database_name --user SYS

To Change Database Passwords in a Data Guard Environment

  1. Run the following command on the primary database:
    dbaascli database changePassword —dbName <dbname> --user SYS --prepareStandbyBlob true --blobLocation <location to create the blob file>
  2. Copy the blob file created to all the standby databases and update the file ownership to oracle user.
  3. Run the following command on all the standby databases:
    dbaascli database changePassword —dbName <dbname> --user SYS --standbyBlobFromPrimary <location of copies the blob file>

To Change the TDE Wallet Password for an Exadata Database Service on Cloud@Customer Database

  1. Log onto the Exadata Database Service on Cloud@Customer virtual machine as opc.
  2. Run the following command:
    sudo dbaascli tde changepassword --dbname database_name

Manage Pluggable Databases on Exadata Database Service on Cloud@Customer

Learn to manage pluggable databases on Exadata Cloud@Customer.

Pluggable Database Operations

You can create and manage pluggable databases (PDBs) in Oracle Exadata Cloud@Customer systems using the Console and APIs.

In this documentation, "database" refers to a container database, also called a CDB. For more information on these resource types, see Multitenant Architecture in the Oracle Database documentation.

Oracle 19c or later databases created in a virtual machine include an initial PDB that you can access from the CDB's Database Details page in the Console. Using the Console or APIs, you can start, stop, clone, and delete the PDB. You can also create additional PDBs in the container database. You can monitor all PDB operations performed using the Console or APIs using the work request generated by the operation.

  • Backup

    You can take a backup of the PDB optionally during create, clone, or relocate operations when the CDB is configured with the auto-backup feature. The PDB backup destination will always be the same as CDB, and the backups cannot be accessed directly or created on demand. Oracle recommends immediately backing up the PDB after you create or clone it. This is because the PDB will not be recoverable until the next daily auto-backup completes successfully, leading to a possible data loss.

  • Restore
    • Base Database Service / Oracle Exadata Database Service on Dedicated Infrastructure:
      • In place restore: You can restore a PDB within the same CDB to last known good state or to a specified timestamp.
      • Out of place restore: You can restore a PDB by creating a database (CDB) from the backup, then selecting a PDB or a subset of them you want to restore on the new database.
    • Oracle Exadata Database Service on Cloud@Customer:
      • In place restore: You can restore a PDB within the same CDB to last known good state and specified timestamp.
      • Out of place restore: It's not available.
    You can perform an in-place restore when you want to move a PDB back to a specified state or time. Both the CDB and PDB must be up and running and only one PDB can be restored at a time.
    • If you have multiple PDBs in your CDB and want to restore multiple of them to the same CDB, then you could restore each individual PDB, one PDB at a time, from the CDB backup.
    • When the CDB is down, you could restore the complete CDB and all the PDBs in that CDB will also be restored.
    • You could either restore the database to the specified timestamp or to its last known good state.
  • Relocate
    You can relocate a PDB from one CDB to another CDB within the same availability domain (AD):
    • Across compartments, VM clusters, DB system (for BaseDB only), or VCNs (not applicable to ExaDB-C@C). If two different VCNs are used, then both VCNs must be peered before relocating.
    • To the same or a higher database version.

    During relocate, the PDB will be removed from the source CDB and moved to the destination CDB that is up and running. In a Data Guard association, a PDB relocated to the primary will be synchronized with the standby as well.

  • Clone

    A clone is an independent and complete copy of the given database as it existed at the time of the cloning operation. You can create clones of your PDB within the same CDB or a different CDB and refresh the cloned PDB.

    The following types of clones are supported:
    • Local clone: A copy of the PDB is created within the same CDB.
    • Remote clone: A copy of the PDB is created in a different CDB.
      You can perform a remote clone of a PDB from one CDB to another CDB within the same availability domain (AD):
      • Across compartments, VM clusters, DB system (for BaseDB only), or VCNs (not applicable to ExaDB-C@C). If two different VCNs are used, then both VCNs must be peered before cloning.
      • To the same or a higher database version.
    • Refreshable clone: A copy of the PDB is created in a different CDB, and you will be able to refresh the cloned PDB.

      You can perform a refreshable clone of a PDB from one CDB to another CDB within the same availability domain (AD):
      • Across compartments, VM clusters, DB system (for BaseDB only), or VCNs (not applicable to ExaDB-C@C). If two different VCNs are used, then both VCNs must be peered before cloning.
      • To the same or a higher database version.
  • Refreshable Clone
    A refreshable clone enables you to keep your remote clone updated with the source PDB. You can only refresh while the PDB is in mount mode. The only open mode you can have is read-only and refresh cannot be done while it is in read-only mode.
    • A database link user credential is required for creating a refreshable clone.
    • Clone, relocate, and in-place restore operations are not supported in the refreshable clone. Relocate and in-place restore operations are not supported in the source, and the source can only be deleted after disconnecting or deleting the refreshable clone.
    • In a Data Guard association, a refreshable clone cannot be created on standby, but it can be created on the primary. However, the primary will not be synced to the standby.
      Note

      A PDB in standby cannot be used as the source for a refreshable PDB.

  • Convert Refreshable PDB to Regular PDB

    You can convert a refreshable PDB to a regular PDB by disconnecting the refreshable clone (destination PDB) from the source PDB at any time. If the refresh PDB is in a Data Guard association, when it is converted to a regular PDB the PDB will be synced to the standby as part of the conversion process.

  • Open Modes

    On the Console, you can see the open modes of a PDB, such as read-write, read-only, and mounted. If the PDB status is the same across all nodes, the system displays the same status for all PDBs. If the PDB statuses are different across the nodes, the system displays a message indicating on which nodes the PDBs are opened in read-write mode. You cannot change the open mode of a PDB through the API or Console. However, you can start or stop a PDB. Starting the PDB will start it in read-write mode. Stopping the PDB will close it and it will remain in mount mode.

Limitations for Pluggable Database Management

Review the list of limitations in managing PDBs.

  • Oracle recommends using the Console or API-based tools (including the OCI CLI, SDKs, and Terraform) to create and manage PDBs. However, there would be periodic sync of the PDBs created through DBAASCLI and SQL*Plus.
  • PDB management using the OCI Console and API is available only for Oracle Database versions 19c and later.
  • PDBs are backed up at the CDB level, and each backup includes all the PDBs in the database. OCI Control Plane does not support the creation of backups for individual PDBs. However, bkup_api supports PDB backup operations. For more information, see Configuring and Customizing Backups with bkup_api.
    Examples:
    • List backups:
      /var/opt/oracle/bkup_api/bkup_api list --dbname psarch
    • Create initial PDB backup manually:
      /var/opt/oracle/bkup_api/bkup_api bkup_start --level1 --dbname psarch --pdb NEWPDBA
  • Restore operations are performed at the CDB level. OCI Control Plane does not support restoring individual PDBs. However, bkup_api supports PDB restore operations.
    Examples:
    • Recover a PDB with least or no data loss possible:
      /var/opt/oracle/bkup_api/bkup_api recover_start --latest --dbname psarch --pdb NEWPDBA
    • Recover a PDB back to a point in time:
      /var/opt/oracle/bkup_api/bkup_api recover_start -t '17-AUG2021 21:15:00' --dbname psarch --pdb NEWPDBA
    • Recover until SCN:
      /var/opt/oracle/bkup_api/bkup_api recover_start -scn 138935800 -pdb=NEWPDBA -uuid=fec6579e077211ec8b0a00102ee75632 -bname=psarch
Create a Pluggable Database

You can create a PDB from the OCI Console, or with the pluggable database APIs.

Using the Console to Create a Pluggable Database

To create a pluggable database with the console, use this procedure.

  1. Open the navigation menu Under Oracle Database, and click Exadata Cloud@Customer.

    VM Clusters is selected by default.

  2. Choose your Compartment.

    A list of VM Clusters is displayed for the chosen Compartment.

  3. In the list of cloud VM clusters, click the name of the cluster in which you want to create the PDB, and then click its name to display the database details page.
  4. In the lower-left corner of the database details page, click Pluggable Databases.

    A list of existing PDBs in this database is displayed.

  5. Click Create Pluggable Database.

    The Create Pluggable Database dialog box is displayed.

  6. In the Create Pluggable Database dialog box, enter the following:
    • Enter PDB Name: Enter a name for the PDB. The name must begin with an alphabetic character and can contain a maximum of 30 alphanumeric characters.
    • Unlock my PDB Admin Account:
      • To enter the administrator's password, check this check box.
        • PDB Admin Password: Enter PDB admin password. The password must contain:
          • a minimum of 9 and a maximum of 30 characters
          • at least two uppercase characters
          • at least two lowercase characters
          • at least two special characters. The valid special characters are underscore ( _ ), a pound or hash sign (#), and dash (-). You can use two of the same characters or any combination of two of the same characters.
          • at least two numeric characters (0 - 9)
        • Confirm PDB Admin Password: Enter the same PDB Admin password in the confirmation field.
      • To skip entering the administrator's password, uncheck this check box. If you uncheck this check box, then the PDB is created but you cannot use it. To use the PDB, you must reset the administrator password.
        Note

        When you create a new PDB, a local user in the PDB is created as the administrator and granted the PDB_DBA role locally to the administrator.
        To reset the password:
        1. Connect to the container where your PDB exists using the SQL*Plus CONNECT statement.
          SQL> show con_name;
          CON_NAME
          ------------------------
          CDB$ROOT

          For more information, see Administering a CDB and Administering PDBs in the Oracle® Multitenant Administrator’s Guide.

        2. Find the administrator name of your PDB:
          SQL> select grantee from cdb_role_privs where con_id = (select con_id from cdb_pdbs where pdb_name = '<PDB_NAME>') and granted_role = 'PDB_DBA';
        3. Switch into your PDB:
          SQL> alter session set container=<PDB_NAME>;
          Session altered.
          
          SQL> show con_name;
          CON_NAME
          ------------------------
          <PDB_NAME>
        4. Reset the PDB administrator password:
          SQL> alter user <PDB_Admin> identified by <PASSWORD>;
          User altered.
    • TDE Wallet password of database: Enter a wallet password for the CDB. This password has the same rules as the PDB Admin Password.
    • Take a backup of the PDB immediately after creating it: You must enable auto-backup on the CDB to back up a PDB immediately after creating it. This check box is checked by default if auto-backup was enabled on the CDB.
      Note

      If the checkbox is unchecked, the system displays a warning stating that PDB cannot be recovered until the next daily backup has been successfully completed.

    • Advanced Options:
      • Tags: Optionally, you can apply tags. If you have permission to create a resource, you also have permission to apply free-form tags to that resource. To apply a defined tag, you must have permission to use the tag namespace. For more information about tagging, see Resource Tags. If you are not sure if you should apply tags, skip this option (you can apply tags later) or ask your administrator.
  7. Click Create Pluggable Database.

    The system starts the creation process and opens the Work Request page for the new PDB. The Work request page shows the status of the creation process of the new PDB.

    By default, the Work Request details page shows the log messages created by the system. Click Error Messages or Associated Resources to see any error messages or associated resources for the creation process, in the Resources area on the left side of the page.

    Note

    The numbers at the right side of the Log Messages, Error Messages, and Associated Resources links indicate how many of each item exists.
Using the Console to Relocate a Pluggable Database

To relocate a pluggable database with the console, use this procedure.

  1. Open the navigation menu Under Oracle Database, and click Exadata Cloud@Customer.

    VM Clusters is selected by default.

  2. Choose your Compartment.

    A list of VM Clusters is displayed for the chosen Compartment.

  3. In the list of VM clusters, click the name of the cluster in which you want to create the PDB, and then click its name to display the database details page.
  4. In the lower-left corner of the database details page, click Pluggable Databases.

    A list of existing PDBs in this database is displayed.

  5. Click the name of the PDB that you want to relocate.

    From the Pluggable Database details page, click More Actions, and then select Relocate.

    (or)

    Click the Actions menu (three dots) and select Relocate.

  6. In the resulting Relocate Pluggable Database window, enter the following:
    • VM Cluster: Use the menu to select the destination VM cluster.
    • Destination database: Use the menu to select an existing database where the PDB will be created. This database can be of the same version as the CDB the source PDB is in or of a higher version.
    • New PDB name for the clone: The name must begin with an alphabetic character and can contain up to 30 characters. To keep the PDB name the same, just re-enter the source PDB name.
    • Database TDE wallet password: Enter the TDE wallet password for the parent CDB of the source PDB.
    • Unlock my PDB Admin Account:
      • To enter the administrator's password, check this check box.
        • PDB Admin Password: Enter PDB admin password. The password must contain:
          • a minimum of 9 and a maximum of 30 characters
          • at least two uppercase characters
          • at least two lowercase characters
          • at least two special characters. The valid special characters are underscore ( _ ), a pound or hash sign (#), and dash (-). You can use two of the same characters or any combination of two of the same characters.
          • at least two numeric characters (0 - 9)
        • Confirm PDB Admin Password: Enter the same PDB Admin password in the confirmation field.
      • To skip entering the administrator's password, uncheck this check box. If you uncheck this check box, then the PDB is created but you cannot use it. To use the PDB, you must reset the administrator password.
        Note

        When you create a new PDB, a local user in the PDB is created as the administrator and granted the PDB_DBA role locally to the administrator.

        To reset the password:
        1. Connect to the container where your PDB exists using the SQL*Plus CONNECT statement.
          SQL> show con_name;
          CON_NAME
          ------------------------
          CDB$ROOT

          For more information, see Administering a CDB and Administering PDBs in the Oracle® Multitenant Administrator’s Guide.

        2. Find the administrator name of your PDB:
          SQL> select grantee from cdb_role_privs where con_id = (select con_id from cdb_pdbs where pdb_name = '<PDB_NAME>') and granted_role = 'PDB_DBA';
        3. Switch into your PDB:
          SQL> alter session set container=<PDB_NAME>;
          Session altered.
          
          SQL> show con_name;
          CON_NAME
          ------------------------
          <PDB_NAME>
        4. Reset the PDB administrator password:
          SQL> alter user <PDB_Admin> identified by <PASSWORD>;
          User altered.
    • TDE Wallet password of database: Enter a wallet password for the CDB. This password has the same rules as the PDB Admin Password.
    • Take a backup of the PDB immediately after creating it: You must enable auto-backup on the CDB to back up a PDB immediately after creating it. This check box is checked by default if auto-backup was enabled on the CDB.
      Note

      If the checkbox is unchecked, the system displays a warning stating that PDB cannot be recovered until the next daily backup has been successfully completed.

    • Advanced Options:
      • Tags: Optionally, you can apply tags. If you have permission to create a resource, you also have permission to apply free-form tags to that resource. To apply a defined tag, you must have permission to use the tag namespace. For more information about tagging, see Resource Tags. If you are not sure if you should apply tags, skip this option (you can apply tags later) or ask your administrator.
  7. Click Relocate pluggable database.
    Note

    Relocate will incur downtime during the process and that the time required is based on the size of the PDB.

Using the API to Create a Pluggable Database

Use various API features to help create your pluggable databases on Oracle Exadata Cloud@Customer.

For information about using the API and signing requests, see REST APIs and Security Credentials. For information about SDKs, see Software Development Kits and Command Line Interface.

Use this API operation to create pluggable databases on Exadata Cloud@Customer systems.
  • CreatePluggableDatabase
Manage a Pluggable Database

To start, stop, clone, and delete a PDB, use these procedures.

Using the Console to Start a Pluggable Database

The PDB must be available and stopped to use this procedure.

  1. Open the navigation menu Under Oracle Database, and click Exadata Cloud@Customer.

    VM Clusters is selected by default.

  2. Choose your Compartment.

    A list of VM Clusters is displayed for the chosen Compartment.

  3. In the list of VM clusters, click the name of the VM cluster that contains the PDB you want to start, and then click its name to display the details page.
  4. Under Databases, find the database containing the PDB you want to start.
  5. Click the name of the database to view the Database Details page.
  6. Click Pluggable Databases in the Resources section of the page.

    A list of existing PDBs in this database is displayed.

  7. Click the name of the PDB that you want to start.

    The pluggable details page is displayed.

  8. Click Start.

    The Start PDB dialog box is displayed.

  9. Click Start PDB to confirm the start operation.
Using the Console to Stop a Pluggable Database

The PDB must be available and running (started) to use this procedure.

  1. Open the navigation menu Under Oracle Database, and click Exadata Cloud@Customer.

    VM Clusters is selected by default.

  2. Choose your Compartment.

    A list of VM Clusters is displayed for the chosen Compartment.

  3. In the list of VM clusters, click the name of the VM cluster that contains the PDB you want to stop, and then click its name to display the details page.
  4. Under Databases, find the database containing the PDB you want to stop.
  5. Click the name of the database to view the Database Details page.
  6. Click Pluggable Databases in the Resources section of the page.

    A list of existing PDBs in this database is displayed.

  7. Click the name of the PDB that you want to stop.

    The pluggable details page is displayed.

  8. Click Stop.

    The Stop PDB dialog box is displayed.

  9. Click Stop PDB to confirm the stop operation.
Using the Console to Delete a Pluggable Database

The PDB must be available and stopped to use this procedure.

  1. Open the navigation menu Under Oracle Database, and click Exadata Cloud@Customer.

    VM Clusters is selected by default.

  2. Choose your Compartment.

    A list of VM Clusters is displayed for the chosen Compartment.

  3. In the list of VM clusters, click the name of the VM cluster that contains the PDB you want to delete, and then click its name to display the details page.
  4. Under Databases, find the database containing the PDB you want to delete.
  5. Click the name of the database to view the Database Details page.
  6. Click Pluggable Databases in the Resources section of the page.

    A list of existing PDBs in this database is displayed.

  7. Click the name of the PDB that you want to delete.

    The pluggable details page is displayed.

  8. Click More Actions, and then choose Delete.

    The Delete PDB dialog box is displayed.

  9. Click Delete PDB to confirm the delete operation.
Using the Console to Get Connection Strings for a Pluggable Database

Learn how to get connection strings for the administrative service of a PDB. Oracle recommends connecting applications to an application service using the strings created for the application service.

  1. Open the navigation menu Under Oracle Database, and click Exadata Cloud@Customer.

    VM Clusters is selected by default.

  2. Choose your Compartment.

    A list of VM Clusters is displayed for the chosen Compartment.

  3. In the list of VM clusters, click the name of the VM cluster that contains the PDB you want to get connections strings for, and then click its name to display the details page.
  4. Under Databases, find the database containing the PDB you want to get connection strings.
  5. Click the name of the database to view the Database Details page.
  6. Click Pluggable Databases in the Resources section of the page.

    A list of existing PDBs in this database is displayed.

  7. Click the name of the PDB that you want to get connection strings.

    The pluggable details page is displayed.

  8. Click PDB Connection.
  9. In the Pluggable Database Connection dialog, use the Show and Copy links to display and copy connection strings, as needed.
  10. Click Close to exit the dialog.
Clone a Pluggable Database (PDB)

A clone is an independent and complete copy of the given database as it existed at the time of the cloning operation. You can create clones of your PDB within the same CDB or a different CDB and also refresh the cloned PDB.

The following types of clones are supported:

  • Local clone: A clone of the PDB is created within the same CDB.
  • Remote clone: A clone of the PDB is created in a different CDB.
  • Refreshable clone: A clone of the PDB is created in a different CDB, and you will be able to refresh the cloned PDB.
Using the Console to Create a Local Clone of a Pluggable Database (PDB)

To create a local clone of your PDBs, follow this procedure.

  1. Open the navigation menu Under Oracle Database, and click Exadata Cloud@Customer.

    VM Clusters is selected by default.

  2. Choose your Compartment.

    A list of VM Clusters is displayed for the chosen Compartment.

  3. In the list of VM clusters, click the name of the VM cluster that contains the PDB you want to clone, and then click its name to display the details page.
  4. Under Databases, find the database containing the PDB you want to clone.
  5. Click Pluggable Databases in the Resources section of the page.

    A list of existing PDBs in this database is displayed.

  6. Click the name of the PDB that you want to clone.

    The pluggable details page is displayed.

  7. Click Clone.
  8. In the Clone PDB dialog box, enter the following:
    • Select clone type: Select Local clone to create a copy of the source PDB to the same CDB.
    • VM Cluster: Use the menu to select the source VM cluster.
    • Destination database: This field is disabled.
    • New PDB name for the clone: The name must begin with an alphabetic character and can contain up to 30 characters.
    • Database TDE wallet password: Enter the TDE wallet password for the parent CDB of the source PDB.
    • Unlock my PDB Admin Account:
      • To enter the administrator's password, check this check box.
        • PDB Admin Password: Enter PDB admin password. The password must contain:
          • a minimum of 9 and a maximum of 30 characters
          • at least two uppercase characters
          • at least two lowercase characters
          • at least two special characters. The valid special characters are underscore ( _ ), a pound or hash sign (#), and dash (-). You can use two of the same characters or any combination of two of the same characters.
          • at least two numeric characters (0 - 9)
        • Confirm PDB Admin Password: Enter the same PDB Admin password in the confirmation field.
      • To skip entering the administrator's password, uncheck this check box. If you uncheck this check box, then the PDB is created but you cannot use it. To use the PDB, you must reset the administrator password.
        Note

        When you create a new PDB, a local user in the PDB is created as the administrator and granted the PDB_DBA role locally to the administrator.
        To reset the password:
        1. Connect to the container where your PDB exists using the SQL*Plus CONNECT statement.
          SQL> show con_name;
          CON_NAME
          ------------------------
          CDB$ROOT

          For more information, see Administering a CDB and Administering PDBs in the Oracle® Multitenant Administrator’s Guide.

        2. Find the administrator name of your PDB:
          SQL> select grantee from cdb_role_privs where con_id = (select con_id from cdb_pdbs where pdb_name = '<PDB_NAME>') and granted_role = 'PDB_DBA';
        3. Switch into your PDB:
          SQL> alter session set container=<PDB_NAME>;
          Session altered.
          
          SQL> show con_name;
          CON_NAME
          ------------------------
          <PDB_NAME>
        4. Reset the PDB administrator password:
          SQL> alter user <PDB_Admin> identified by <PASSWORD>;
          User altered.
    • Take a backup of the PDB immediately after creating it: You must enable auto-backup on the CDB to back up a PDB immediately after creating it. This check box is checked by default if auto-backup was enabled on the CDB.
      Note

      If the checkbox is unchecked, the system displays a warning stating that PDB cannot be recovered until the next daily backup has been successfully completed.

    • Advanced Options:
      • Tags: Optionally, you can apply tags. If you have permission to create a resource, you also have permission to apply free-form tags to that resource. To apply a defined tag, you must have permission to use the tag namespace. For more information about tagging, see Resource Tags. If you are not sure if you should apply tags, skip this option (you can apply tags later) or ask your administrator.
  9. Click Clone pluggable database.

Related Topics

Using the Console to Create a Remote Clone of a Pluggable Database (PDB)

To create a remote clone of your PDBs, follow this procedure.

  1. Open the navigation menu Under Oracle Database, and click Exadata Cloud@Customer.

    VM Clusters is selected by default.

  2. Choose your Compartment.

    A list of VM Clusters is displayed for the chosen Compartment.

  3. In the list of VM clusters, click the name of the VM cluster that contains the PDB you want to clone, and then click its name to display the details page.
  4. Under Databases, find the database containing the PDB you want to clone.
  5. Click Pluggable Databases in the Resources section of the page.

    A list of existing PDBs in this database is displayed.

  6. Click the name of the PDB that you want to clone.

    The pluggable details page is displayed.

  7. Click Clone.
  8. In the Clone PDB dialog box, enter the following:
    • Select clone type: Select Remote clone to create a copy of the source PDB to the same CDB.
    • VM Cluster: Use the menu to select the destination VM cluster.
    • Destination database: Use the menu to select an existing database where the PDB will be created. This database can be of the same version as the CDB the source PDB is in or of a higher version.
    • New PDB name for the clone: The name must begin with an alphabetic character and can contain up to 30 characters.
    • Database TDE wallet password: Enter the TDE wallet password for the parent CDB of the source PDB.
    • Unlock my PDB Admin Account:
      • To enter the administrator's password, check this check box.
        • PDB Admin Password: Enter PDB admin password. The password must contain:
          • a minimum of 9 and a maximum of 30 characters
          • at least two uppercase characters
          • at least two lowercase characters
          • at least two special characters. The valid special characters are underscore ( _ ), a pound or hash sign (#), and dash (-). You can use two of the same characters or any combination of two of the same characters.
          • at least two numeric characters (0 - 9)
        • Confirm PDB Admin Password: Enter the same PDB Admin password in the confirmation field.
      • To skip entering the administrator's password, uncheck this check box. If you uncheck this check box, then the PDB is created but you cannot use it. To use the PDB, you must reset the administrator password.
        Note

        When you create a new PDB, a local user in the PDB is created as the administrator and granted the PDB_DBA role locally to the administrator.
        To reset the password:
        1. Connect to the container where your PDB exists using the SQL*Plus CONNECT statement.
          SQL> show con_name;
          CON_NAME
          ------------------------
          CDB$ROOT

          For more information, see Administering a CDB and Administering PDBs in the Oracle® Multitenant Administrator’s Guide.

        2. Find the administrator name of your PDB:
          SQL> select grantee from cdb_role_privs where con_id = (select con_id from cdb_pdbs where pdb_name = '<PDB_NAME>') and granted_role = 'PDB_DBA';
        3. Switch into your PDB:
          SQL> alter session set container=<PDB_NAME>;
          Session altered.
          
          SQL> show con_name;
          CON_NAME
          ------------------------
          <PDB_NAME>
        4. Reset the PDB administrator password:
          SQL> alter user <PDB_Admin> identified by <PASSWORD>;
          User altered.
    • Source database SYS password: Enter the database admin password.
    • Database link: Enter the user name and password for the database link. Note that the user must be precreated in the source database. The DB link will be created in the destination using that username and password.
    • Take a backup of the PDB immediately after creating it: You must enable auto-backup on the CDB to back up a PDB immediately after creating it. This check box is checked by default if auto-backup was enabled on the CDB.
      Note

      If the checkbox is unchecked, the system displays a warning stating that PDB cannot be recovered until the next daily backup has been successfully completed.

    • Advanced Options:
      • Tags: Optionally, you can apply tags. If you have permission to create a resource, you also have permission to apply free-form tags to that resource. To apply a defined tag, you must have permission to use the tag namespace. For more information about tagging, see Resource Tags. If you are not sure if you should apply tags, skip this option (you can apply tags later) or ask your administrator.
  9. Click Clone pluggable database.

Related Topics

Using the Console to Create a Refreshable Clone of a Pluggable Database (PDB)

To create a refreshable clone of your PDBs, follow this procedure.

  1. Open the navigation menu Under Oracle Database, and click Exadata Cloud@Customer.

    VM Clusters is selected by default.

  2. Choose your Compartment.

    A list of VM Clusters is displayed for the chosen Compartment.

  3. In the list of VM clusters, click the name of the VM cluster that contains the PDB you want to clone, and then click its name to display the details page.
  4. Under Databases, find the database containing the PDB you want to clone.
  5. Click Pluggable Databases in the Resources section of the page.

    A list of existing PDBs in this database is displayed.

  6. Click the name of the PDB that you want to clone.

    The pluggable details page is displayed.

  7. Click Clone.
  8. In the Clone PDB dialog box, enter the following:
    • Select clone type: Select Refreshable clone to create a copy of the source PDB to the same CDB.

      For more information about refreshable clones, see About Refreshable Clone PDBs.

    • VM Cluster: Use the menu to select the destination VM cluster.
    • Destination database: Use the menu to select an existing database where the PDB will be created. This database can be of the same version as the CDB the source PDB is in or of a higher version.
    • New PDB name for the clone: The name must begin with an alphabetic character and can contain up to 30 characters.
    • Database TDE wallet password: Enter the TDE wallet password for the parent CDB of the source PDB.
    • Unlock my PDB Admin Account:
      • To enter the administrator's password, check this check box.
        • PDB Admin Password: Enter PDB admin password. The password must contain:
          • a minimum of 9 and a maximum of 30 characters
          • at least two uppercase characters
          • at least two lowercase characters
          • at least two special characters. The valid special characters are underscore ( _ ), a pound or hash sign (#), and dash (-). You can use two of the same characters or any combination of two of the same characters.
          • at least two numeric characters (0 - 9)
        • Confirm PDB Admin Password: Enter the same PDB Admin password in the confirmation field.
      • To skip entering the administrator's password, uncheck this check box. If you uncheck this check box, then the PDB is created but you cannot use it. To use the PDB, you must reset the administrator password.
        Note

        When you create a new PDB, a local user in the PDB is created as the administrator and granted the PDB_DBA role locally to the administrator.
        To reset the password:
        1. Connect to the container where your PDB exists using the SQL*Plus CONNECT statement.
          SQL> show con_name;
          CON_NAME
          ------------------------
          CDB$ROOT

          For more information, see Administering a CDB and Administering PDBs in the Oracle® Multitenant Administrator’s Guide.

        2. Find the administrator name of your PDB:
          SQL> select grantee from cdb_role_privs where con_id = (select con_id from cdb_pdbs where pdb_name = '<PDB_NAME>') and granted_role = 'PDB_DBA';
        3. Switch into your PDB:
          SQL> alter session set container=<PDB_NAME>;
          Session altered.
          
          SQL> show con_name;
          CON_NAME
          ------------------------
          <PDB_NAME>
        4. Reset the PDB administrator password:
          SQL> alter user <PDB_Admin> identified by <PASSWORD>;
          User altered.
    • Source database SYS password: Enter the database admin password.
    • Database link: Enter the user name and password for the database link. Note that the user must be precreated in the source database. The DB link will be created in the destination using that username and password.
    • Advanced Options:
      • Tags: Optionally, you can apply tags. If you have permission to create a resource, you also have permission to apply free-form tags to that resource. To apply a defined tag, you must have permission to use the tag namespace. For more information about tagging, see Resource Tags. If you are not sure if you should apply tags, skip this option (you can apply tags later) or ask your administrator.
  9. Click Clone pluggable database.

Related Topics

Using the Console to Refresh a Cloned Pluggable Database (PDB)

To create a refresh a cloned PDB, follow this procedure.

  1. Open the navigation menu Under Oracle Database, and click Exadata Cloud@Customer.

    VM Clusters is selected by default.

  2. Choose your Compartment.

    A list of VM Clusters is displayed for the chosen Compartment.

  3. In the list of VM clusters, click the name of the VM cluster that contains the PDB you want to refresh, and then click its name to display the details page.
  4. Under Databases, find the database containing the PDB you want to refresh.
  5. Click the name of the database to view the Database Details page.
  6. Click Pluggable Databases in the Resources section of the page.

    A list of existing PDBs in this database is displayed.

  7. Click the name of the PDB that you want to refresh.

    The pluggable details page is displayed.

  8. Click More Actions and select Refresh.
  9. In the resulting Refresh dialog box, click Refresh to confirm.
Using the Console to Convert a Refreshable Clone to a Regular Pluggable Database (PDB)

To create a convert a refreshable clone to a regular PDB, follow this procedure.

  1. Open the navigation menu Under Oracle Database, and click Exadata Cloud@Customer.

    VM Clusters is selected by default.

  2. Choose your Compartment.

    A list of VM Clusters is displayed for the chosen Compartment.

  3. In the list of VM clusters, click the name of the VM cluster that contains the PDB you want to convert to a regular PDB, and then click its name to display the details page.
  4. Under Databases, find the database containing the PDB you want to convert to a regular PDB.
  5. Click the name of the database to view the Database Details page.
  6. Click Pluggable Databases in the Resources section of the page.

    A list of existing PDBs in this database is displayed.

  7. Click the name of the PDB that you want to convert to a regular PDB.

    From the Pluggable Database details page, click More Actions, and then select Convert to regular PDB.

    (or)

    Click the Actions menu (three dots) and select Convert to regular PDB.

  8. In the resulting Convert to regular PDB dialog, enter the following:
    • Database TDE wallet password: Enter the TDE wallet password for the parent CDB of the source PDB.
    • Take a backup of the PDB immediately after creating it: You must enable auto-backup on the CDB to back up a PDB immediately after creating it. This check box is checked by default if auto-backup was enabled on the CDB.
      Note

      If the checkbox is unchecked, the system displays a warning stating that PDB cannot be recovered until the next daily backup has been successfully completed.

  9. Click Convert.
Restore a Pluggable Database (PDB)

You can restore a PDB within the same CDB to last known good state and specified timestamp.

Using the Console to Perform an In-Place Restore of a Pluggable Database (PDB)

To perform an in-place restore, follow this procedure.

  1. Open the navigation menu Under Oracle Database, and click Exadata Cloud@Customer.

    VM Clusters is selected by default.

  2. Choose your Compartment.

    A list of VM Clusters is displayed for the chosen Compartment.

  3. In the list of VM clusters, click the name of the VM cluster that contains the PDB you want to restore, and then click its name to display the details page.
  4. Under Databases, find the database containing the refreshable PDB you want to restore.
  5. Click the name of the database to view the Database Details page.
  6. Click Pluggable Databases in the Resources section of the page.

    A list of existing PDBs in this database is displayed.

  7. Click the name of the PDB that you want to restore.

    From the Pluggable Database details page, click More Actions, and then select Restore.

    (or)

    Click the Actions menu (three dots) and select Restore.

  8. In the resulting Restore PDB dialog, enter the following:
    • Restore to latest: Select this option to restore and recover the database with zero, or least possible, data loss.
    • Restore to a timestamp: Select this option to restore and recover the database to the specified timestamp.
  9. Click Restore.
Using the API to Manage Pluggable Databases

Use various API features to help manage your pluggable databases on Oracle Exadata Cloud@Customer.

For information about using the API and signing requests, see REST APIs and Security Credentials. For information about SDKs, see Software Development Kits and Command Line Interface.

Use these APIs to manage pluggable databases on Exadata Cloud@Customer systems.
  • ListPluggableDatabases
  • GetPluggableDatabase
  • StartPluggableDatabase
  • StopPluggableDatabase
  • CreatePluggableDatabase
  • DeletePluggableDatabase
  • LocalclonePluggableDatabase
  • RemoteclonePluggabledatabase

For the complete list of APIs for the Database service, see Database Service API.

Using the API to Clone a Pluggable Database

Clone a pluggable database (PDB) in the same database (CDB) as the source PDB or to a different database from the source PDB.

For information about using the API and signing requests, see REST APIs and Security Credentials. For information about SDKs, see Software Development Kits and Command Line Interface.

Use these APIs to manage pluggable databases on Exadata Cloud@Customer systems.
  • LocalclonePluggableDatabase
  • RemoteclonePluggabledatabase

For the complete list of APIs for the Database service, see Database Service API.

Connect to an Oracle Database using Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Users

You can configure Oracle Exadata Database Service on Cloud@Customer to use Oracle Cloud Infrastructure Identity and Access Management (IAM) authentication and authorization to allow IAM users to access an Oracle Database with IAM credentials.

Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Authentication with Oracle Database

Learn to enable an Oracle Database instance on Oracle Exadata Database Service on Cloud@Customer to allow user access with an Oracle Cloud Infrastructure IAM database password (using a password verifier), or SSO tokens.

About Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Authentication with Oracle Database

You can enable an Oracle Database instance to use Oracle Cloud Infrastructure (IAM) authentication and authorization for users.

Note

Oracle Database supports the Oracle DBaaS integration for Oracle Cloud Infrastructure (OCI) IAM with identity domains as well as the legacy IAM, which does not include identity domains. Both default and non-default domain users and groups are supported when using IAM with Identity Domains.

Support for non-default custom domains are only available with Oracle Database Release 19c, Version 19.21 and higher (but not Oracle Database Release 21c).

Oracle Cloud Infrastructure IAM integration with Oracle Exadata Database Service on Cloud@Customer supports the following:

  • IAM Database Password Authentication
  • Identity and Access Management (IAM) SSO Token Based Authentication

See Authenticating and Authorizing IAM Users for Oracle DBaaS Databases for complete details about the architecture for using IAM users on Oracle Exadata Database Service on Cloud@Customer.

Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Database Password Authentication

You can enable an Oracle Database instance to allow user access with an Oracle Cloud Infrastructure IAM database password (using a password verifier).

Note

Any supported 12c and above database client can be used for IAM database password access to Oracle Database.

An Oracle Cloud Infrastructure IAM database password allows an IAM user to log in to an Oracle Database instance as Oracle Database users typically log in with a username and password. The user enters their IAM user name and IAM database password. An IAM database password is a different password than the Oracle Cloud Infrastructure Console password. Using an IAM user with the password verifier, you can log in to Oracle Database with any supported database client.

For password verifier database access, you create the mappings for IAM users and OCI applications to the Oracle Database instance. The IAM user accounts themselves are managed in IAM. The user accounts and user groups can be in either the default domain or in a custom, non-default domain.

For more information about managing IAM database password, see Managing User Credentials.

Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) SSO Token Based Authentication

You can enable an Oracle Database instance to use Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) SSO tokens.

For token verifier database access, you create the mappings for IAM users and OCI applications to the Oracle Database instance. The IAM user accounts themselves are managed in IAM. The user accounts and user groups can be in either the default domain or in a custom, non-default domain.

There are several ways a database client can obtain an IAM database token:

  • A client application or tool can request the database token from IAM for the user and can pass the database token through the client API. Using the API to send the token overrides other settings in the database client. Using IAM tokens requires the latest Oracle Database client 19c (at least 19.16). Some earlier clients (19c and 21c) provide a limited set of capabilities for token access. Oracle Database client 21c does not fully support the IAM token access feature. For more information about the clients supported for this type of IAM database token usage, see Supported Client Drivers for IAM Connections.
  • If the application or tool does not support requesting an IAM database token through the client API, the IAM user can first use the Oracle Cloud Infrastructure command line interface (CLI) to retrieve the IAM database token and save it in a file location. For example, to use SQL*Plus and other applications and tools using this connection method, you first obtain the database token using the Oracle Cloud Infrastructure (OCI) Command Line Interface (CLI). For more information, see db-token get. If the database client is configured for IAM database tokens, when a user logs in with the slash login form, the database driver uses the IAM database token that has been saved in the default or specified file location.
  • A client application or tool can use an Oracle Cloud Infrastructure IAM instance principal or resource principal to get an IAM database token and use the IAM database token to authenticate itself to an Oracle Database instance.
  • IAM users and OCI applications can request a database token from IAM with several methods, including using an API key. See Configuring a Client Connection for SQL*Plus That Uses an IAM Token for an example. See Authenticating and Authorizing IAM Users for Oracle DBaaS Databases for a description of other methods such as using a delegation token within an OCI cloud shell.

In previous releases, you could only use the IAM username and database password to get a password verifier from IAM. Getting a token with these credentials is more secure than getting a password verifier because a password verifier is considered sensitive. Using a token means that you do not need to pass or use the verifier. Applications cannot pass a token that was retrieved by the IAM user name and password through the database client API. Only the database client can retrieve this type of token. A database client can only retrieve a database token using the IAM user name and IAM database password.

Prerequisites for Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Authentication on Oracle Database

Review the prerequisites for Identity and Access Management (IAM) authentication on an Oracle Database.

Disable External Authentication Scheme

Review the prerequisites for enabling IAM user access to Oracle Database.

If the database is enabled for another external authentication scheme, verify that you want to use IAM on the Oracle Database instance. There can only be one external authentication scheme enabled at any given time.

If you want to use IAM and another external authentication scheme is enabled, you must first disable the other external authentication scheme.

Configure a Network Connection to OCI

Configure a network connection to OCI to be able to make calls to OCI IAM for the database instances on Oracle Exadata Database Service on Cloud@Customer to accept IAM database access tokens (db-tokens), or get IAM database password verifiers.

  1. Consult your ExaDB-C@C administrator to determine the OCI region assigned to your ExaDB-C@C installation.
  2. Determine the OCI IAM endpoint for that OCI region. For more information, see Identity and Access Management Service API.
  3. Find the port number for Identity Service for name resolution of Oracle operators. For more information, see Table 3-2 Ports to Open for Control Plane Connectivity in Network Requirements for Oracle Exadata Database Service on Cloud@Customer.

    For example, if your OCI region is Phoenix, then open port 443 to https://identity.us-phoenix-1.oci.oraclecloud.com.

  4. Configure your network to open this connection.

For more information on troubleshooting login failures, see Troubleshooting IAM Logins.

Configure TLS to Use IAM Tokens

When sending IAM tokens from the database client to the database server, a TLS connection must be established. The TLS wallet with the database certificate for the ExaDB-C@C service instance must be stored under the WALLET_ROOT location. Create a tls directory so it looks like: WALLET_ROOT/<PDB GUID>/tls.

When configuring TLS between the database client and server there are several options to consider.
  • Using a self-signed database server certificate vs a database server certificate signed by a commonly known certificate authority
  • One-way TLS (TLS) vs Mutual or two-way TLS (mTLS)
  • Client with or without a wallet

Self-Signed Certificate

Using a self-signed certificate is a common practice for internally facing IT resources since you can create these yourself and it's free. The resource (in our case, the database server) will have a self-signed certificate to authenticate itself to the database client. The self-signed certificate and root certificate will be stored in the database server wallet. For the database client to be able to recognize the database server certificate, a copy of the root certificate will also be needed on the client. This self-created root certificate can be stored in a client-side wallet or installed in the client system default certificate store (Windows and Linux only). When the session is established, the database client will check to see that the certificate sent over by the database server has been signed by the same root certificate.

A Well-Known Certificate Authority

Using a commonly known root certificate authority has some advantages in that the root certificate is most likely already stored in the client system default certificate store. There is no extra step for the client to store the root certificate if it is a common root certificate. The disadvantage is that this normally has a cost associated with it.

One-Way TLS

In the standard TLS session, only the server provides a certificate to the client to authenticate itself. The client doesn't need to have a separate client certificate to authenticate itself to the server (similar to how HTTPS sessions are established). While the database requires a wallet to store the server certificate, the only thing the client needs to have is the root certificate used to sign the server certificate.

Two-Way TLS (also called Mutual TLS, mTLS)

In mTLS, both the client and server have identity certificates that are presented to each other. In most cases, the same root certificate will have signed both of these certificates so the same root certificate can be used with the database server and client to authenticate the other certificate. mTLS is sometimes used to authenticate the user since the user identity is authenticated by the database server through the certificate. This is not necessary for passing IAM tokens but can be used when passing IAM tokens.

Client with a Wallet

A client wallet is mandatory when using mTLS to store the client certificate. However, the root certificate can be stored either in the same wallet or in the system default certificate store.

A Client without a Wallet

Clients can be configured without a wallet when using TLS under these conditions: 1) One-way TLS is being configured where the client does not have its own certificate and 2) the root certificate that signed the database server certificate is stored in the system default certificate store. The root certificate would most likely already be there if the server certificate is signed by a common certificate authority. If it's a self-signed certificate, then the root certificate would need to be installed in the system default certificate store to avoid using a client wallet.

For details on how to configure TLS between the database client and database server including the options described above, see Configuring Transport Layer Security Authentication in the Oracle Database Security Guide.

If you choose to use self-signed certificates and for additional wallet related tasks, Managing Public Key Infrastructure (PKI) Elements in the Oracle Database Security Guide.

Configure Proxy Settings

Configure network proxy settings in your environment to allow the database to access OCI IAM. Replace the network proxy URL http://www-proxy.example.com:80/ and the database name given in the example with yours.

  1. Log in to the host operating system.
  2. Set the proxy environment variables.
    srvctl setenv database -db exampledbname -env "https_proxy=http://www-proxy.example.com:80/"
    srvctl setenv database -db exampledbname -env "http_proxy=http://www-proxy.example.com:80/"
    
  3. Stop the database and verify that the variables have been set:
    $ srvctl stop database -db exampledbname
    $ srvctl getenv database -db exampledbname
    http_proxy=http://www-proxy.example.com:80/
    https_proxy=http://www-proxy.example.com:80/
  4. Restart the database.
    $ srvctl start database -db exampledbname

Enable, Disable, and Re-enable Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Authentication on Oracle Database

Learn to enable, disable, and re-enable Identity and Access Management (IAM) Authentication on Oracle Database. Also, to change the external identity provider from (IAM) authentication and authorization to another and vice-versa.

Enable Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Authentication on Oracle Database

Review the steps to enable IAM user access to Oracle Database.

Note

Oracle Database supports the Oracle DBaaS integration for Oracle Cloud Infrastructure (OCI) IAM with identity domains as well as the legacy IAM, which does not include identity domains. Both default and non-default domain users and groups are supported when using IAM with Identity Domains.
  1. Perform the prerequisites for IAM authorization and authentication on Oracle Database.

    See Prerequisites for Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Authentication on Oracle Database for more information.

  2. Enable Oracle Cloud Infrastructure (IAM) Authentication and Authorization using the ALTER SYSTEM command.
    ALTER SYSTEM SET IDENTITY_PROVIDER_TYPE=OCI_IAM SCOPE=BOTH;
  3. Verify the value of IDENTITY_PROVIDER_TYPE system parameter.
    SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME='identity_provider_type';
    NAME                     VALUE
    ----------------------   -------
    identity_provider_type   OCI_IAM
Change External Identity Providers on Oracle Exadata Database Service on Cloud@Customer

Review the steps to change the external identity provider from (IAM) authentication and authorization to another and vice-versa.

If Oracle Cloud Infrastructure (IAM) authentication and authorization for users is enabled and you wish to switch to a different external service (CMU for Active Directory, EUS for OID or OUD), you must first disable IAM integration before you enable the other integration. There can only be one external authentication scheme enabled at any given time. If IAM and another directory service is configured at the same time, IAM integration will take precedence.

  1. Disable IAM integration using the ALTER SYSTEM command.
    ALTER SYSTEM RESET IDENTITY_PROVIDER_TYPE SCOPE=BOTH;
  2. Verify the value of IDENTITY_PROVIDER_TYPE system parameter.
    SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME='identity_provider_type';
    NAME                   VALUE   
    ---------------------- ------- 
    identity_provider_type None
  3. Configure the other directory service integration.
Re-enable Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Authentication and Authorization

Review the steps to re-enable IAM users to connect to Oracle Database using Oracle Cloud Infrastructure (IAM) Authentication and Authorization

  1. Disable the integration with the other identity provider or directory service.
  2. Enable IAM integration as described in Enable Identity and Access Management (IAM) Authentication on Oracle Database.
Disable Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Authentication on Oracle Database

Describes the steps to disable IAM external authentication user access for Oracle Database.

To disable IAM user access on your Oracle Database instance:

  1. Disable IAM integration using the ALTER SYSTEM command.
    ALTER SYSTEM RESET IDENTITY_PROVIDER_TYPE SCOPE=BOTH;
  2. If you also want to remove IAM user access to the database, you may need to remove or modify the IAM group and the IAM policies you set up to allow access to the database.
Using Oracle Database Tools with Identity and Access Management (IAM) Authentication

Review the notes for using Oracle Database tools with IAM authentication enabled.

  • Oracle APEX is not supported for IAM users with Oracle Database.
  • Database Actions is not supported for IAM users with Oracle Database. See Provide Database Actions Access to Database Users for information on using regular database users with Oracle Database.
  • Oracle Machine Learning Notebooks and other components are not supported for IAM Authorized users with Oracle Database. See Add Existing Database User Account to Oracle Machine Learning Components for information on using regular database users with Oracle Database.

Manage Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Groups and Policies, Users, Roles, and Database Passwords

Learn to create and manage IAM policies, add IAM users to the Oracle Database and grant global roles, and create IAM database passwords for IAM users.

Create Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Groups and Policies for IAM Users

Review the the steps to write policy statements for an IAM group to enable IAM user access to OCI resources, specifically the Oracle Database instances.

A policy is a group of statements that specifies who can access particular resources, and how. Access can be granted for the entire tenancy, databases in a compartment, or individual databases. This means you write a policy statement that gives a specific group a specific type of access to a specific type of resource within a specific compartment.

Note

Defining a policy is required to use IAM tokens to access the database. A policy is not required when using IAM database passwords to access the Oracle Database.

To enable the Oracle Database to allow OCI IAM users to connect to the database using OCI IAM tokens:

  1. Create an IAM group for IAM users to access OCI databases. Add users to this group.

    For example, create the group sales_dbusers. For more information, see Managing Groups.

  2. Write policy statements to enable access to Oracle Cloud Infrastructure resources.
    1. In the Oracle Cloud Infrastructure console, click Identity and Security, and then click Policies.
    2. To a write policy, click Create Policy, and then enter a Name and a Description.
    3. Use the Policy Builder to create a policy.
      For example, to create a policy to allow users in IAM group DBUsers to access any Oracle Database in their tenancy:
      Allow group DBUsers to use database-connections in tenancy
      For example to create a policy that limits members of DBUsers group to access Oracle Databases in the compartment testing_compartment only:
      allow group DBUsers to use database-connections in compartment testing_compartment
      For example, to create a policy that limits group access to a single database in a compartment:
      allow group DBUsers to use database-connections in compartment testing_compartment where target.database.id = 'ocid1.autonomousdatabase.oc1.iad.aaaabbbbcccc'
    4. Click Create.
Note

Note the following for creating policies for use with IAM users on database in the ExaDB-C@C service.
  • Policies can allow IAM users to access Oracle Database instances across the entire tenancy, in a compartment, or can limit access to a single Oracle Database instance.
  • You can use either instance principal or resource principal to retrieve database tokens to establish a connection from your application to an Oracle Database instance. If you are using an instance pricipal or resource principal, you must map a dynamic group. Thus, you cannot exclusively map instance and resource principals; you only can map them through a shared mapping and putting the instance or resource instance in an IAM dynamic group.

    You can create Dynamic Groups and reference dynamic groups in the policies you create to access Oracle Cloud Infrastructure. See Accessing Cloud Resources by Configuring Policies and Roles and Managing Dynamic Groups for details.

Add Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Users on Oracle Database

Review the steps to authorize IAM users on an Oracle Database instance.

To add IAM users to allow access to Oracle Database, map database global users to IAM groups or users with CREATE USER or ALTER USER statements with IDENTIFIED GLOBALLY AS clause.

An IAM user must be mapped to one schema to be authorized to access the database. This could be an exclusive schema or a shared schema.

The authorization of IAM users to an Oracle Database instance works by mapping IAM global users (schemas) to IAM users (exclusive mapping) or IAM groups (shared schema mapping).

  1. Log in as the ADMIN user to the database that is enabled to use IAM.

    The ADMIN user has the required CREATE USER and ALTER USER system privileges that you need for these steps.

  2. Create a mapping between the Oracle Database user (schema) with CREATE USER or ALTER USER statements and include the IDENTIFIED GLOBALLY AS clause, specifying the IAM group name.
    Use the following syntax to map a global user to an IAM group:
    CREATE USER global_user IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME=IAM_GROUP_NAME';
    For example, to map an IAM group named db_sales_group to a shared database global user named sales_group:
    CREATE USER sales_group IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME=db_sales_group';

    This creates a shared global user mapping. The mapping, with the global user sales_group is effective for all users in the IAM group. Thus, anyone in the db_sales_group can log in to the database using their IAM credentials through the shared mapping of the sales_group global user.

    If you want to create additional global user mappings for other IAM groups or users, follow these steps for each IAM group or user.

    Note

    Database users that are not IDENTIFIED GLOBALLY can continue to login as before, even when the Oracle Database is enabled for IAM authentication.
To Exclusively Map a Local IAM User to an Oracle Database Global User
  1. Log in as the ADMIN user to the database that is enabled to use IAM.

    The ADMIN user has the required CREATE USER and ALTER USER system privileges that you need for these steps.

  2. Create a mapping between the Oracle Database user (schema) with CREATE USER or ALTER USER statements and include the IDENTIFIED GLOBALLY AS clause, specifying the IAM local IAM user name.
    For example, to create a new database global user named peter_fitch and map this user to an existing local IAM user named peterfitch:
    CREATE USER peter_fitch IDENTIFIED GLOBALLY AS 'IAM_PRINCIPAL_NAME=peterfitch'
Add Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Roles on Oracle Database

Review the steps to map Oracle Database global roles to IAM groups.

Optionally, create global roles to provide additional database roles and privileges to IAM users when multiple IAM users are mapped to the same shared global user.

Using global roles is optional when a user is mapped exclusively to a global schema or mapped to a shared schema. For example, all privileges and roles can be granted to the shared schema and all IAM users who map to the shared schema would be granted the privileges and roles assigned to the shared schema.

Use a global role to optionally differentiate users who use the same shared schema. For example, a set of users can all have the same shared schema and the shared schema could have the CREATE SESSION privilege. Then global roles can be used to provide differentiated privileges and roles assigned to different groups of users who all use the same shared schema.

Granting additional roles to IAM users in Oracle Database works by mapping Oracle Database global roles to IAM groups.

  1. Log in as the ADMIN user to the database that is enabled to use IAM.

    The ADMIN user has the required CREATE USER and ALTER USER system privileges that you need for these steps.

  2. Set database authorization for Oracle Database roles with CREATE ROLE or ALTER ROLE statements and include the IDENTIFIED GLOBALLY AS clause, specifying the IAM group name.
    Use the following syntax to map a global role to an IAM group:
    CREATE ROLE global_role IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME=IAM_GROUP_of_WHICH_the_IAM_USER_IS_a_MEMBER';
    For example, to map an IAM group named ExporterGroup to a shared database global role named export_role:
    CREATE ROLE export_role IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME=ExporterGroup';
  3. Use the GRANT statements to grant the required privileges or other roles to the global role.
    GRANT CREATE SESSION TO export_role;
    GRANT DWROLE TO export_role;
  4. If you want an existing database role to be associated with an IAM group, then use the ALTER ROLE statement to alter the existing database role to map the role to an IAM group.
    Use the following syntax to alter an existing database role to map it to an IAM group:
    ALTER ROLE existing_database_role IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME=IAM_Group_Name';

Follow these steps for each IAM group to add additional global role mappings for other IAM groups.

Create Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Database Password for IAM Users

To add an IAM user and allow the IAM user to login to Oracle Database by supplying a username and password, you must create an IAM database password.

For more information, see Working with IAM Database Passwords.

Connect to Oracle Database with Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Authentication

After the DBA user enables Oracle Cloud Infrastructure IAM on Oracle Database, users log in to the Oracle Database instance using their Oracle Cloud Infrastructure IAM credentials or access the database through an Oracle Cloud Infrastructure IAM database token.

After you enable Oracle Cloud Infrastructure IAM user access, you can also log in to the Oracle Database using your local database account username and password (non-global database user account).

You can use a database client to access an Oracle Database instance as an Oracle Cloud Infrastructure IAM user. Enter the IAM user name and IAM database password (not the Oracle Cloud Infrastructure console password) using any currently supported database client. The only constraint is that the database client version be either Oracle Database release 12.1.0.2 or later (or patched) to allow Oracle Database 12c passwords. The database client must be able to use the 12C password verifier. Using the 11G verifier encryption is not supported with IAM.

Alternatively, you can use an Oracle Cloud Infrastructure IAM database token to access an Oracle Database instance with supported clients. IAM database token usage requires the Oracle Database client 19.16 and above (not 21c). Limited (not full) IAM database token capabilities are available with some Oracle Database clients 21.5 and above.

The following examples show password verifier with SQL*Plus to access the database with an Oracle Cloud Infrastructure IAM username and password and the steps required to use SQL*Plus with an Oracle Cloud Infrastructure IAM database token.

Note

If your Oracle Database instance is in Restricted mode, only the users with the RESTRICTED SESSION privilege can connect to the database.
About Connecting to an Oracle Database Instance Using Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM)

IAM users can connect to the Oracle Database instance by using either an IAM database password verifier or an IAM token.

Using the IAM database password verifier is similar to the Oracle Database password authentication process. However, instead of the password verifier (encrypted hash of the password) being stored in the Oracle Database, the verifier is instead stored as part of the Oracle Cloud Infrastructure (OCI) IAM user profile.

The second connection method, the use of an IAM token for the database, is more modern. The use of token-based access is a better fit for Cloud resources such as Oracle Database. The token is based on the strength that the IAM endpoint can enforce. This can be multi-factor authentication, which is stronger than the use of passwords alone. Another benefit of using tokens is that the password verifier (which is considered sensitive) is never stored or available in memory. A TCPS (TLS) connection is required when using tokens for database access.

Note

You cannot configure native network encryption when passing an IAM token. Only Transport Layer Security (TLS) by itself is supported, not native network encryption or native network encryption with TLS.
Client Connections That Use an Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Database Password Verifier

After you have configured the authorization needed for the IAM user, this user can log in using an existing client application, such as SQL*Plus or SQLcl without additional configuration.

The IAM user enters the IAM user name and IAM database password (not the Oracle Cloud Infrastructure console password) using any currently supported database client. The only constraint is that the database client version be either Oracle Database release 12.1.0.2 or later (or patched) to allow Oracle Database 12c passwords. The database client must be able to use the 12C password verifier.

Using the 11G verifier encryption is not supported with IAM. No special client or tool configuration is needed for the IAM user to connect to the Oracle Database instance.

Client Connections That Use a Token Requested by a Client Application or Tool

For IAM token access to the Oracle DBaaS, the client application or tool requests a database token from IAM for the IAM user.

The client application will pass the database token directly to the database client through the database client API.

If the application or tool has not been updated to request an IAM token, then the IAM user can use Oracle Cloud Infrastructure (OCI) command line interface (CLI) to request and store the database token. You can request a database access token (db-token) using the following credentials:

  • Security tokens (with IAM authentication), delegation tokens (in the OCI cloud shell) and API-keys, which are credentials that represent the IAM user to enable the authentication
  • Instance principal tokens, which enable instances to be authorized actors (or principals) to perform actions on service resources after authenticating
  • Resource principal token, which is a credential that enables the application to authenticate itself to other Oracle Cloud Infrastructure services
  • Using an IAM username and IAM database password (can only be requested by database client).

When the IAM users log into the client with a slash / login and the OCI_IAM parameter is configured (sqlnet.ora, tnsnames.ora, or as part of a connect string), then the database client retrieves the database token from a file. If the IAM user submits a username and password, the connection will use the IAM database verifier access described for client connections that use IAM database password verifiers. The instructions in this guide show how to use the OCI CLI as a helper for the database token. If the application or tool has been updated to work with IAM, then follow the instructions for the application or tool. Some common use cases include the following: SQLPlus on-premises, SQLcl on-premises, SQL*Plus in Cloud Shell, or applications that use SEP wallets.

Client Connections That Use a Token Requested by an IAM User Name and Database Password

You can create a client connection that uses a token requested by an IAM user name and database password.

For more information, see:
  • About Client Connections That Use a Token Requested by an IAM User Name and Database Password
  • Parameters to Set for Client Connections That Use a Token Requested by an IAM User Name and Database Password
  • Configuring the Database Client to Retrieve a Token Using an IAM User Name and Database Password
  • Configuring a Secure External Password Store Wallet to Retrieve an IAM Token
Configure a Secure External Password Store Wallet to Retrieve an Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Token

You can enable an IAM user name and a secure external password store (SEPS) to request the IAM database token.

  1. Log in to the Oracle Database client.
  2. Configure this client to use the secure external password store.
  3. Set the appropriate parameters to retrieve a token that will be requested by an IAM user name and database password.
Configure a Client Connection for SQL*Plus That Uses an Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Database Password

You can configure SQL*Plus to use an IAM database password.

  1. As the IAM user, log in to the Oracle Database.
    CONNECT user_name@db_connect_string
    Enter password: password

    In this specification, user_name is the IAM user name. There is a limit of 128 bytes for the combined domain_name/user_name.

    The following example shows how IAM user peter_fitch can log in to an Oracle Database instance.

    sqlplus /nolog
    connect peter_fitch@db_connect_string
    Enter password: password
    Some special characters will require double quotation marks around user_name and password. For example:
    "peter_fitch@example.com"@db_connect_string
    "IAM database password"
Configure a Client Connection for SQL*Plus That Uses an Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Token

You can configure a client connection for SQL*Plus that uses an IAM token.

  1. Ensure you have an IAM user account.
  2. Check with an IAM administrator and Oracle Database administrator to ensure you have a policy allowing you to access the database in the compartment or your tenancy and that you are mapped to a global schema in the database.
  3. If your application or tool does not support direct IAM integration, then download, install, and configure the OCI CLI.

    See OCI Command Line Interface Quickstart.

  4. Set up an API key as part of the OCI CLI configuration and select default values.
    1. Set up the API key access for the IAM user.
    2. Retrieve the db-token.
      For example:
      • Retrieving a db-token with an API-key using the Oracle Cloud Infrastructure (OCI) command-line interface:
        oci iam db-token get
      • Retrieving a db-token with a security (or session) token:
        oci iam db-token get --auth security_token

        If the security token has expired, a window will appear so the user can log in to OCI again. This generates the security token for the user. OCI CLI will use this refreshed token to get the db-token.

      • Retrieving a db-token with a delegation token: When you log in to the cloud shell, the delegation token is automatically generated and placed in the /etc directory. To get this token, execute the following command in the cloud shell:
        oci iam db-token get
      • Retrieving an instance token by using the OCI command-line interface:
        oci iam db-token get --auth instance_principal

      See Required Keys and OCIDs for more information.

  5. Ensure that you are using the latest release updates for the Oracle Database client release 19c.

    This configuration only works with the Oracle Database client release 19c (Oracle Database release 21c offers limited IAM token features).

  6. Ensure that TLS is configured for the database connection.
    1. Confirm that DN matching is enabled by looking for SSL_SERVER_DN_MATCH=ON in the sqlnet.ora file.
    2. Configure the database client to use the IAM token by adding TOKEN_AUTH=OCI_TOKEN to the sqlnet.ora file.

      Because you will be using the default locations for the database token file, you do not need to include the token location.

    The TOKEN_AUTH and TOKEN_LOCATION values in the tnsnames.ora connect strings take precedence over the sqlnet.ora settings for that connection.

    For example, for the connect string, assuming that the token is in the default location (~/.oci/db-token for Linux):
    (description= 
      (retry_count=20)(retry_delay=3)
      (address=(protocol=tcps)(port=1522)
      (host=example.us-phoenix-1.oraclecloud.com))
      (connect_data=(exa1scan.example.com:1521/PDB1.example.yourcloud.com))
      (security=(ssl_server_cert_dn="CN=example.uscom-east-1.oraclecloud.com, 
         OU=Oracle BMCS US, O=Example Corporation, 
         L=Redwood City, ST=California, C=US")
      (TOKEN_AUTH=OCI_TOKEN)))

    After the connect string is updated with the TOKEN_AUTH parameter, the IAM user can log in to the Oracle Database instance by running the following command to start SQL*Plus. You can include the connect descriptor itself or use the name of the descriptor from the tnsnames.ora file.

    connect /@exampledb_high

    (or)

    (description= 
      (retry_count=20)(retry_delay=3)
      (address=(protocol=tcps)(port=1522)
      (host=example.us-phoenix-1.oraclecloud.com))
      (connect_data=(exa1scan.example.com:1521/PDB1.example.yourcloud.com))
      (security=(ssl_server_cert_dn="CN=example.uscom-east-1.oraclecloud.com, 
         OU=Oracle BMCS US, O=Example Corporation, 
         L=Redwood City, ST=California, C=US")
      (TOKEN_AUTH=OCI_TOKEN)))

Ensure that the database client is configured to get a db-token by setting TOKEN_AUTH=OCI_IAM in either sqlnet.ora, tnsnames.ora, or in the connect string. The database client gets the db-token and signs it using the private key and then sends the token to the Oracle Database. If an IAM user name and IAM database password are specified instead of slash /, then the database client will connect using the password instead of using the db-token.

Use Instance Principal to Access Oracle Database with Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Authentication

After the DBA user enables Oracle Cloud Infrastructure IAM on Oracle Database, an application can access the database through an Oracle Cloud Infrastructure IAM database token using an instance principal.

Database Links in an Oracle DBaaS-to-IAM Integration

The use of database links when accessing the Oracle DBaaS database using IAM credentials is supported.

The method of configuring database links for Oracle DBaaS connections to IAM depends on the Oracle DBaaS platform. Review the topic below that corresponds to your Oracle DBaaS platform and then click on the associated link for more information.

  • Oracle Autonomous Database on Shared Exadata Infrastructure: You can use fixed user database links in which a database user is used for the fixed database link. The database user for creating the database link can only use password authentication with the database link. The IAM user can authenticate to the source database using either password or token access. You cannot configure IAM users as fixed database links, nor can you use connected or current user database links. See, Use Database Links with Autonomous Database.
  • Oracle Autonomous Database on Dedicated Exadata Infrastructure and all non-Autonomous Database DBaaS platforms: You can use connected user and fixed user database links, but not current user database links. For connected user database links, an IAM user must be provisioned to both the source and target link databases. You can use a database password verifier or an IAM database token to connect and use connected user database links. For a fixed user database link, a user can connect to the target database using a target database user with password authentication. In addition, an IAM user can connect to the first PDB by using an IAM user name and password or an IAM token. See, Use Identity and Access Management (IAM) Authentication with Autonomous Database.

Configuring Authorization for IAM Users and Oracle Cloud Infrastructure Applications

An Oracle DBaaS database administrator can map IAM users and Oracle Cloud Infrastructure (OCI) applications to the Oracle Database global schemas and global roles.

About Configuring Authorization for IAM Users and Oracle Cloud Infrastructure Applications

You create the mappings for IAM users and Oracle Cloud Infrastructure (OCI) applications to database users (schemas) in the Oracle DBaaS.

There is a difference with authorization between IAM database password authentication and using IAM token based authentication. IAM database password verifier authorization is only based on mappings of database schemas and global roles to IAM users and group. With IAM token based authentication, IAM policies are an additional authorization for IAM users to access their tenancy databases. An IAM user must be authorized through an IAM policy and be authorized through a mapping to a database global schema (exclusive or shared).

For both token and password verifier database access, you create the mappings for IAM users and OCI applications to the Oracle DBaaS instance. The IAM user accounts themselves are managed in IAM. The user accounts and user groups can be in either the default domain or in a custom, non-default domain.

When the IAM user accesses the Oracle DBaaS instance with a token, the database will perform an authorization check against IAM policies to ensure the user is allowed to access the database. If the IAM user is allowed to access the database by IAM policy, then the database will query IAM for the user groups. When using password verifier authentication, the database will query IAM for user groups once the IAM user successfully completes authentication. The database queries the IAM endpoint to find the groups of which the user is a member. If your deployment is using shared schemas, then one of the IAM groups will map to a shared database schema and the IAM user will be assigned to that database schema. The IAM user will have the roles and privileges that are granted to the database schema. Because multiple IAM users can be assigned to the same shared database schema, only the minimal set of roles and privileges should be granted to the shared schema. In some cases, no privileges and roles should be granted to the shared schema. Users will be assigned the appropriate set of roles and schemas through database global roles. Global roles are mapped to IAM groups. This way, different users can have different roles and privileges even if they are mapped to the same database shared schema. A newly hired user will be assigned to an IAM group mapped to a shared schema and then to one or more additional groups mapped to global roles to gain the additional roles and privileges required to complete their tasks. The combination of shared schemas and global roles allows for centralized authorization management with minimal changes to the database operationally. The database must be initially provisioned with the set of shared schemas and global roles mapped to the appropriate IAM groups, but then user authorization management can happen within IAM.

Ensure that the IAM user is only mapped to one schema, either through exclusive mapping to a database schema or as a member of one IAM group that is mapped to a shared database schema. If more than one schema is mapped for an IAM user, then the database will take exclusive mapping as precedence over any group mapping to a shared schema. If more than one group is mapped for a user, then the database will select the oldest mapping.

When using global roles to grant privileges and roles to the user, remember that the maximum number of enabled roles in a session is 150.

If you drop and recreate IAM users and groups using the same names, then the mappings from the database to IAM using the same names will continue to work. However, recreating an IAM user will require the IAM user to do one or more of the following: create the IAM database password, re-upload the API public key, update the OCI configuration file, and then re-examine the IAM policy for database authentication and authorization with IAM. If the IAM policy specifies a group that can use or manage the database-connections and autonomous-database-family resource types, then the user will need to be added to that group to allow IAM authentication and authorization.

Accessing the database with tokens requires the user to be authorized by IAM policy and by database mapping. Accessing the database with the IAM database password verifier requires authorization through database mapping. If no database schema mapping exists for the IAM user, the IAM user is prevented from accessing the database even if they have a valid token or password.

IAM users get their authorizations to perform various tasks based on the roles that they have been granted. The following scenarios are possible:

  • IAM group mapped to a shared Oracle Database global user: With the shared database global user account, an IAM user is assigned to a shared database schema (user) through the mapping of an IAM group to the shared schema. The IAM users that are members of the group can connect to the database through this shared schema. Use of shared schemas allows for centralized management of user authorization in IAM.
  • IAM group mapped to an Oracle Database global role: The privileges that have been granted to the shared Oracle Database global role become available to the users who have added to the IAM group.
  • Local IAM user exclusively mapped to an Oracle Database global user: With an exclusive global user mapping, a dedicated database user is exclusively mapped to a local IAM user. Not as common as the shared database schema, this user is created for when the user requires their own schema objects. Oracle recommends that you grant database privileges to these users through global roles, which facilitates authorization management. These users can also have direct privilege and role grants to their exclusive schema.

    In IAM with Identity Domains, users and groups are supported in the default domain as well as custom non-default domains. When you specify users and groups in the default domain, then no domain prefix is required. When you specify users and groups in a non-default domain, then the domain must be prefixed.

Mapping an IAM Group to a Shared Oracle Database Global User

Oracle Database global users that are mapped to IAM groups and IAM dynamic groups give IAM users and OCI applications a schema when they log in along with the privileges and roles granted to that schema.

  1. Log in to the Oracle DBaaS instance as a user who has the CREATE USER or ALTER USER system privilege.
  2. Run the CREATE USER or ALTER USER statement with the IDENTIFIED GLOBALLY AS clause specifying the IAM group name (which can be a dynamic group).
    For example, to create a new database global user account named shared_sales_schema and map it to an existing IAM group named WidgetSalesGroup:
    CREATE USER shared_sales_schema IDENTIFIED GLOBALLY AS
    'IAM_GROUP_NAME=WidgetSalesGroup';

    The following example shows how to accomplish this for a non-default domain:

    CREATE USER shared_sales_schema IDENTIFIED GLOBALLY AS
    'IAM_GROUP_NAME=sales_domain/WidgetSalesGroup';
Mapping an IAM Group to an Oracle Database Global Role

Oracle Database global roles that are mapped to IAM groups and dynamic groups give member users and applications additional privileges and roles above what they have been granted through their login schemas.

Global roles cannot be granted to a database schema (user), they can only be mapped to a group and be assigned to an IAM user when accessing the database.
  1. Log in to the Oracle DBaaS instance as a user who has been granted the CREATE ROLE or ALTER ROLE system privilege
  2. Run the CREATE ROLE or ALTER ROLE statement with the IDENTIFIED GLOBALLY AS clause specifying the name of the IAM group (which can be a dynamic group).
    For example, to create a new database global role named widget_mgr_role and map it to an existing IAM group named WidgetManagerGroup, using the default domain:
    CREATE ROLE widget_mgr_role IDENTIFIED GLOBALLY AS 
    'IAM_GROUP_NAME=WidgetManagerGroup';

    The following example shows how to create the role by specifying a non-default domain, sales_domain:

    CREATE ROLE widget_sales_role IDENTIFIED GLOBALLY AS 
    'IAM_GROUP_NAME=sales_domain/WidgetManagerGroup';
    All members of the WidgetManagerGroup in the sales_domain domain will be authorized with the database global role widget_sales_role when they log in to the database.
Exclusively Mapping an IAM User to an Oracle Database Global User

You can map an IAM user exclusively to an Oracle Database global user.

  1. Log in to the Oracle DBaaS instance as a user who has been granted the CREATE USER or ALTER USER system privilege.
  2. Run the CREATE USER or ALTER USER statement with the IDENTIFIED GLOBALLY AS clause specifying the IAM database user name.
    By default, the IAM database user name is the same as the IAM user name, including the domain name. You can also create a unique IAM database user name for ease of authentication to the database. In your OCI IAM user profile, you can create a unique IAM database user name for ease of authentication to the database. This can be set when you create and manage your IAM database password in your IAM profile. Adding or changing the IAM database user name will invalidate the IAM user to schema mapping, so the database schema will need to be remapped to the new IAM database user name.
    For example, to create a new database global user named peter_fitch and map this user to an existing IAM user named with an IAM database user name of peterfitch, using the default domain:
    CREATE USER peter_fitch IDENTIFIED GLOBALLY AS 
    'IAM_PRINCIPAL_NAME=peterfitch';

    The following example shows how to create the user by specifying a non-default domain, sales_domain:

    CREATE USER peter_fitch2 IDENTIFIED GLOBALLY AS
    'IAM_PRINCIPAL_NAME=sales_domain/peterfitch';
Altering or Migrating an IAM User Mapping Definition

You can update an IAM user to a database global user mapping by using the ALTER USER statement.

You can update database schemas that were mapped to an IAM user, and whose accounts were created using any of the CREATE USER statement clauses: IDENTIFIED BY password, IDENTIFIED EXTERNALLY, or IDENTIFIED GLOBALLY. This is useful when migrating existing schemas to using IAM. If you delete and recreate an IAM user or an IAM group using the exact same name as the previous IAM user or group, then the existing mapping from the database that uses that IAM user or IAM group name will continue to work.
  1. Log in to the Oracle DBaas instance as a user who has been granted the ALTER USER system privilege.
  2. Run the ALTER USER statement with the IDENTIFIED GLOBALLY AS clause.
    For example, suppose you want to change the existing schema shared_sales_schema to a different IAM group:
    ALTER USER shared_sales_schema IDENTIFIED GLOBALLY AS
    'IAM_GROUP_NAME=BiggerWidgetSalesGroup';

    The following example shows how to modify the schema by specifying a non-default domain, sales_domain:

    ALTER USER shared_sales_schema IDENTIFIED GLOBALLY AS 
    'IAM_GROUP_NAME=sales_domain/BiggerWidgetSalesGroup';
Mapping Instance and Resource Principals

Instance principals and resource principals can be used by applications to retrieve database tokens to establish a connection to an Oracle DBaaS instance.

Only dynamic groups can be mapped when you use instance and resource principals. You cannot exclusively map instance and resource principals; you only can map them through a shared mapping and putting the instance or resource instance in an IAM dynamic group.

Verifying the IAM User Logon Information

After you configure and authorize an IAM user for the Oracle DBaaS instance, you can verify the user logon information by executing a set of SQL queries on the Oracle database side.

  1. Log in to the Oracle DBaaS instance as an IAM user that you have just configured and authorized.
    For example, to log in to the database instance inst1 as the database global user peterfitch, who is using the default domain in IAM:
    sqlplus /nolog
    CONNECT "peterfitch"@inst1
    Enter password: password

    This example shows how to log in if user peterfitch is in a non-default domain, sales_domain:

    sqlplus /nolog
    CONNECT "sales_domain/peterfitch"@inst1
    Enter password: password
  2. Verify the mapped global user.
    The mapped global user is the database user account that has the IAM user authorization. User PETER_FITCH_SCHEMA is considered a global user with exclusive mapping for the IAM user peterfitch, while user WIDGET_SALES is considered a global user with shared mapping for IAM group widget_sales_group of which peterfitch is a member.
    SHOW USER;

    Output similar to the following appears, depending on if it is an exclusive mapping or a shared mapping:

    USER is "PETER_FITCH_SCHEMA"

    Or

    USER is "WIDGET_SALES"
  3. Find the roles that have been granted to the centrally managed user.
    SELECT ROLE FROM SESSION_ROLES ORDER BY ROLE;

    Output similar to the following appears:

    ROLE
    ----------------------------------------------------------------------
    WIDGET_SALES_ROLE
    ...
  4. Run the following queries to check the SYS_CONTEXT namespace values for the current schema being used in this database session, current user name, session user name, authentication method, authenticated identity, enterprise identity, identification type, and server type.
    • Verify the current schema that is being used in this database session. A database schema is an object container that identifies the objects it contains. The current schema is the default container for objects name resolution in this database session.
      SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;

      Output similar to the following appears, depending on if it is an exclusive mapping or a shared mapping:

      SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
      ----------------------------------------------------------------------
      PETER_FITCH_SCHEMA

      Or

      SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
      ----------------------------------------------------------------------
      WIDGET_SALES
    • Verify the current user. In this case, the current user is the same as the current schema.
      SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER') FROM DUAL;

      Output similar to the following appears, depending on if it is an exclusive mapping or a shared mapping:

      SYS_CONTEXT('USERENV','CURRENT_USER')
      ----------------------------------------------------------------------
      PETER_FITCH_SCHEMA

      Or

      SYS_CONTEXT('USERENV','CURRENT_USER')
      ----------------------------------------------------------------------
      WIDGET_SALES
    • Verify the session user.
      SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;

      Output similar to the following appears, depending on if it is an exclusive mapping or a shared mapping:

      SYS_CONTEXT('USERENV','SESSION_USER')
      ----------------------------------------------------------------------
      PETER_FITCH_SCHEMA

      Or

      SYS_CONTEXT('USERENV','SESSION_USER')
      ----------------------------------------------------------------------
      WIDGET_SALES
    • Verify the authentication method.
      SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATION_METHOD') FROM DUAL;

      Output similar to the following appears:

      SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD')
      ----------------------------------------------------------------------
      PASSWORD_GLOBAL

      If the user is authenticating with a token, then the output is TOKEN_GLOBAL.

    • Verify the authenticated identity for the enterprise user. The IAM authenticated user identity is captured and audited when this user logs on to the database.
      SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY') FROM DUAL;

      Output similar to the following appears:

      SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
      ----------------------------------------------------------------------
      sales_domain/peterfitch
    • If a user nickname has been set for the enterprise user, then verify this nickname.
      SELECT SYS_CONTEXT('USERENV', 'USER_NICKNAME') FROM DUAL;

      Output similar to the following appears:

      SYS_CONTEXT('USERENV','USER_NICKNAME')
      ----------------------------------------------------------------------
      pfitch
    • Verify the centrally managed user's enterprise identity.
      SELECT SYS_CONTEXT('USERENV', 'ENTERPRISE_IDENTITY') FROM DUAL;

      Enterprise Identity will show the OCI Identity (OCID) of the IAM user or OCI application. Output similar to the following appears:

      SYS_CONTEXT('USERENV','ENTERPRISE_IDENTITY')
      ----------------------------------------------------------------------
      ocid1.user.region1..aaaaaaaaj7ot4g2sagkjtw3enbg4ied3x554zwyywurgrm2232j4crm5zha
      
    • Verify the identification type.
      SELECT SYS_CONTEXT('USERENV', 'IDENTIFICATION_TYPE') FROM DUAL

      Output similar to the following appears, depending on if it is an exclusive mapping or a shared mapping:

      SYS_CONTEXT('USERENV','IDENTIFICATION_TYPE')
      ----------------------------------------------------------------------
      GLOBAL EXCLUSIVE

      Or

      SYS_CONTEXT('USERENV','IDENTIFICATION_TYPE')
      ----------------------------------------------------------------------
      GLOBAL SHARED
    • Verify the server type.
      SELECT SYS_CONTEXT('USERENV', 'LDAP_SERVER_TYPE') FROM DUAL;

      Output similar to the following appears. In this case, the LDAP server type is IAM.

      SYS_CONTEXT('USERENV','LDAP_SERVER_TYPE')
      ----------------------------------------------------------------------
      OCI_IAM

Configure Proxy Authentication

Proxy authentication allows an Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) user to proxy to a database schema for tasks such as application maintenance.

About Configuring Proxy Authentication

Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) users can connect to Oracle Database by using proxy authentication.

Proxy authentication is typically used to authenticate the real user and then authorize them to use a database schema with the schema privileges and roles in order to manage an application. Alternatives such as sharing the application schema password are considered insecure and unable to audit which actual user performed an action.

A use case can be in an environment in which a named IAM user who is an application database administrator can authenticate by using their credentials and then proxy to a database schema user (for example, hrapp). This authentication enables the IAM administrator to use the hrapp privileges and roles as user hrapp in order to perform application maintenance, yet still use their IAM credentials for authentication. An application database administrator can sign in to the database and then proxy to an application schema to manage this schema.

You can configure proxy authentication for both password authentication and token authentication methods.

Configure Proxy Authentication for the Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) User

To configure proxy authentication for an IAM user, the IAM user must already have a mapping to a global schema (exclusive or shared mapping). A separate database schema for the IAM user to proxy to must also be available.

After you ensure that you have this type of user, alter the database user to allow the IAM user to proxy to it.
  1. Log in to the Oracle Database instance as a user who has the ALTER USER system privileges.
  2. Grant permission for the IAM user to proxy to the local database user account.
    An IAM user cannot be referenced in the command so the proxy must be created between the database global user (mapped to the IAM user) and the target database user. In the following example, hrapp is the database schema to proxy to, and peterfitch_schema is the database global user exclusively mapped to user peterfitch.
    ALTER USER hrapp GRANT CONNECT THROUGH peterfitch_schema;

    At this stage, the IAM user can log in to the database instance using the proxy.

    For example, to connect using a password verifier:
    CONNECT peterfitch[hrapp]@connect_string
    Enter password: password
  3. To connect using a token:
    CONNECT [hrapp]/@connect_string
Validate the Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) User Proxy Authentication

You can validate the IAM user proxy configuration for both password and token authentication methods.

  1. Log in to the Oracle Database instance as a user who has the CREATE USER and ALTER USER system privileges.
  2. Connect at the IAM user and execute the SHOW USER and SELECT SYS_CONTEXT commands.

    For example, suppose you want to check the proxy authentication of the IAM user peterfitch when they proxy to database user hrapp. You will need to connect to the database using the different types of authentication methods shown here, but the output of the commands that you execute will be the same for all types.

    • For password authentication:
      CONNECT peterfitch[hrapp]/password\!@connect_string
      SHOW USER;
      --The output should be USER is "HRAPP"
      SELECT SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD') FROM DUAL;
      --The output should be "PASSWORD_GLOBAL"
      SELECT SYS_CONTEXT('USERENV','PROXY_USER') FROM DUAL;
      --The output should be "PETERFITCH_SCHEMA"
      SELECT SYS_CONTEXT('USERENV','CURRENT_USER') FROM DUAL;
      --The output should be "HRAPP"
    • For token authentication:
      CONNECT [hrapp]/@connect_string
      SHOW USER;
      --The output should be USER is "HRAPP"
      SELECT SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD') FROM DUAL;
      --The output should be "TOKEN_GLOBAL"
      SELECT SYS_CONTEXT('USERENV','PROXY_USER') FROM DUAL;
      --The output should be "PETERFITCH_SCHEMA"
      SELECT SYS_CONTEXT('USERENV','CURRENT_USER') FROM DUAL;
      --The output should be "HRAPP"