This post is part of a series of blog posts on the Oracle Autonomous Database.

Creating users in Autonomous Database

• No need to specify anything except the password
• The password you specify must meet the default complexity rules in ADB
• “DWROLE” includes all necessary privileges for a DW developer/user
• By default, default password will not expire until 360 days

SQL> create user ocitest identified by ….;
SQL> grant dwrole to ocitest;

If you specify a weaker or NULL Password Verify Function (PVF) for a new profile, then the default PVF CLOUD_VERIFY_FUNCTION applies. If the verify function you specify is more strict than the default CLOUD_VERIFY_FUNCTION, then the new verify function is used.

Starting, Stopping, and Scaling

Can all be done by clicking through the console or through API calls.

Scaling:

• Scale your Autonomous Database Instance on demand without tedious manual steps
• Independently scale compute or storage
• Resizing occurs instantly, fully online
• Memory, IO bandwidth, concurrency scales linearly with CPU

ADB Auto Scaling:

• Auto Scaling is enabled by default (it can be disabled at any time)
• ADB autonomously and continuously monitors overall system performance
• ADB scales CPU and IO resources up and down based on overall workload requirements
• Scaling up autonomously expands CPU-IO resources by up to 3x the base core count
• Can be Enabled when provisioning an ADB instance or anytime using Scale Up/Down on the OCI Console
• Changing the setting does not require database downtime
• You are only charged for the baseline (i.e. number of OCPUs provisioned)
• Auto Scaling is billed based on average number of OCPUs consumed during an hour

Starting & Stopping:

• Stop your instance on demand to conserve resources and halt CPU billing
• Start your instance instantly on demand

Backup, Restore, and Cloning

Autonomous Database Backups:

• The ADB is automatically backed up
• The retention period for backups is 60 days
• The database can be restored and recovered to any point-in-time within the retention period
• Recovery can be initiated using the Cloud Console

When the restore operation finished, the ADB instance opens in read-only mode and the instance details page Lifecycle State shows “Available Needs Atttention”. This is for the user to sanity check the restore. To open the database in read-write mode, click Stop, then Start.

When you restore your database and open it in read-write mode, all backups between the date the restore completes and the date you specified for the restore operation, the restore time, are invalidated. After the database is opened in read-write mode after a restore, you cannot initiate further restore operations to any point in time between the restore time and restore completion time. You can only initiate new restore operations to a point in time older than the restore time or more recent than the time when the actual restore succeeded.

For example, assume you initiate a restore operation on Oct 8, 2018, 2 pm and specify Oct 1, 2018, 2 pm as the point in time to restore to and the restore completes on Oct 8, 2018, 2:30 pm. If you open your database in read-write mode at this point, backups between Oct 8, 2018, 2:30 pm and Oct 1, 2018, 2 pm will be invalidated. You will not be able to restore to any date between Oct 1, 2018, 2 pm and Oct 8, 2018, 2:30 pm. If you initiate a restore to a point in time between these dates the restore will fail with an error.

The restore operation also restores the DATA_PUMP_DIR directory and user defined directories to the timestamp you specified for the restore; files that were created after that timestamp would be lost.

When you restore, the Oracle Machine Learning workspaces, projects, and notebooks are not restored.

For external tables, partitioned external tables, and the external partitions of hybrid partitioned tables a backup does not include the external files that reside on your Object Store. Thus, for operations where you use a backup to restore your database, such as Restore or Clone from a backup it is your responsibility to backup, and restore if necessary, the external files associated with external tables, external partitioned tables, or the external files for a hybrid partitioned table.

Manual Backups:

In addition to automatic backups, Autonomous Database also allows you to take manual backups to your OCI Object Storage.

To perform manual backups you need to:

• Define OCI Object Storage credentials
• Define your OCI Object Storage tenancy URL
• Need to create a bucket to hold the backups

The manual backup configuration tasks are a one-time operation. Once credentials, object bucket and tenancy URL are set, manual backups can be initiated without requiring the same operations again unless the URL, credentials or bucket change.

When creating the object storage bucket for manual backup:

• The name format for the bucket in object storage must follow: backup_
• This is the “Database Name” and not the “Display Name”
• The object bucket name has to be all lower case

After the bucket has been created, connect to the database and perform the following:

• Set the database default_bucket property to your OCI Object Storage tenancy URL

ALTER DATABASE PROPERTY
SET default_bucket = ‘https://…/backup_adb_20200505’;

Create a credential for your OCI Object Storage account:

DBMS_CLOUD.CREATE_CREDENTIAL
(
credential_name => ‘DEF_CRED_NAME’,
username => ‘adwc_user@oracle.com’.
password => ‘auth-token-string’
);

ALTER DATABASE PROPERTY
SET default_credential = ‘ADMIN.DEF_CRED_NAME’;

Cloning:
It is possible to create a new Autonomous Database as a clone of an existing Autonomous Database.

Autonomous Transaction Processing provides cloning where you can choose to create a full clone, create a metadata clone, or create a refreshable clone. You can also use cloning to upgrade your database to a newer Oracle Database version when newer Oracle Database versions are available. You can move an Autonomous Transaction Processing database to a different Oracle Cloud Infrastructure compartment.

Clone Type:

• Full Clone of the source ADB
• Metadata Clone of the source ADB
• Refreshable Clone: creates a read-only full clone that can be easily refreshed with the data from the source database.

For a Full Clone or a Metadata Clone, you have the option to select the clone source:

• Clone from a live database instance. This creates a clone of a running database.
• Clone from a backup. This creates a clone when you select a backup from a list of backups, or when you enter a backup point-in-time to clone.

When you perform the clone operation you can select a newer Oracle Database version for the target database. This allows you to use cloning to upgrade your Autonomous Transaction Processing database.

With clone from backup, the Oracle Machine Learning workspaces, projects, and notebooks of the source database are not cloned to the new database.

For a Metadata Clone, the APEX Apps and the OML Projects and Notebooks are copied to the clone. For a Metadata Clone, the underlying database data of the APEX App or OML Notebook is not cloned.

If you define a network Access Control List (ACL) on the source database, the currently set network ACL is cloned to the new database. If a database is cloned from a backup, the current source database’s ACL is applied (not the ACL that was valid of the time of the backup).

An ADB can be configured to use Private Endpoints by cloning from an existing ADB with Public Endpoints.

If you create a clone and the source database has an access control list (ACL) and you specify the private endpoint network access option, Virtual cloud network for the target database, the ACL is not cloned to the new database. In this case, you must define security rules within your Network Security Group (or groups) to control traffic to and from your target database (instead of using the access control rules that were specified in the ACL on the clone source).

You can only clone an Autonomous Transaction Processing instance to the same tenancy and the same region as the source database.

The clone has its own wallet and you cannot use the wallet of the source database to connect to the clone.

About Refreshable Clones on Autonomous Database:
A refreshable clone allows you to do the following:

• Maintain one or more copies of the source database for use as read-only databases. A clone database is available when you need it, and when you want to update the data, you can refresh the clone from the source database.

• Share copies of a production database with multiple business units. For example, one business unit might use the source database for ongoing transactions and another business unit could at the same time use the refreshable clone database for read-only operations.

This option also allows you to spread the cost of database usage across multiple business units. You can bill the different units separately, based on their usage of one or more refreshable clone databases.

Note: Refreshable clones have a one week refresh age limit. If you do not perform a refresh within a week, then the refreshable clone is no longer refreshable. After a refreshable clone passes the refresh time limit, you can use the instance as a read only database or you can disconnect from the source to make the database a read/write (standard) database.

When you make certain changes on a source Autonomous Database instance that has one or more refreshable clones attached to it, the changes are applied to both the source database and to the refreshable clones as follows:

• Storage: The storage value you set on the source database applies to both the source database and to any attached refreshable clones.
  
• ADMIN password: The ADMIN password value you set on the source database applies to both the source database and to any attached refreshable clones.

Limitations and notes for Autonomous Database refreshable clones:

• You cannot create a refreshable clone from an Autonomous JSON Database source. That is, if the Autonomous Database workload type is JSON Database, then you cannot create a refreshable clone.
  
• You cannot create a refreshable clone for an Autonomous Database instance if your database version is Oracle Database 18c. You must first upgrade to Oracle Database 19c or higher to create a refreshable clone.
  
• You cannot create a cascading series of refreshable clones. Thus, a refreshable clone cannot be created from another refreshable clone.
  
• You cannot backup or restore a refreshable clone.
  
• Oracle Application Express (APEX) URLs do not work in a refreshable clone read-only database and the APEX URLs are disabled in the Oracle Cloud Infrastructure console and the Autonomous Database Service Console. APEX URLs are enabled for a read/write database when a refreshable clone is disconnected from the source.
  
• Automatic Workload Repository (AWR) data and reports are not available for refreshable clones.

Renaming ADB Database

The database rename operation is only supported with Oracle Database 19c onwards.

The database rename operation changes the connection strings required to connect to the database. Thus, after you rename a database you must download a new wallet for any existing instance wallets that you use to connect to the database (and any applications also must be updated to use the new connection string and the new wallet to connect to the database).

If you are using a regional wallet, you can continue to use the existing wallet to connect to any databases that were not renamed. However, if you want to connect to a renamed database, you must download a new regional wallet.

Please note: The rename operation terminates all connections to the database. After the rename operation completes, you can reconnect to the database.

Renaming your database does not change global references to your database from existing database links on remote databases. Changing such references is the responsibility of the administrator of the remote databases.

When you rename a database, the Autonomous Database OCID does not change.

If your database has Autonomous Data Guard enabled, the rename operation is not available.

You cannot use the rename operation on a refreshable clone instance or on a database that is the source for a refreshable clone.

The rename operation restarts the database.

If you configured your Autonomous Database for manual backups prior to the rename operation, then your existing manual backups continue to work after the rename operation. If you perform new manual backups after the rename, they are stored in the same bucket that you configured prior to the rename operation.

Move an Autonomous Transaction Processing Database to a Different Compartment

To move an Autonomous Transaction Processing database you must have the right to manage autonomous databases in the database’s current compartment and in the compartment you are moving it to. As soon as you move an Autonomous Transaction Processing database to a different compartment, the policies that govern the new compartment apply immediately and affect access to the database. Therefore, your access to the database may change, depending on the policies governing your Oracle Cloud user account’s access to resources.

Choose your region. See Switching Regions for information on switching regions and working in multiple regions.

Choose your Compartment. See Compartments for information on using and managing compartments.

Select an Autonomous Transaction Processing instance from the list in your compartment.

On the Details page, from the More Actions drop-down list, select Move Resource.

In the Move Resource to a Different Compartment page, select the new compartment.