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

Please be careful when using the SYSDATE function in Oracle Autonomous Database!

SYSDATE is used extensively in PL/SQL based applications. What will happen when you migrate your data to Autonomous Cloud (or build a new application on Autonomous) as by default the Time Zone is set to Coordinated Universal Time (UTC) in an OCI environment?

You are allowed to change the database and session timezones in ADB, but this doesn’t change the SYSDATE and SYSTIMESTAMP in the timezones. So, the PL/SQL packages, procedure and functions and in particular all SQL using SYSDATE and SYSTIMESTAMP might not return what you expect. For example, you might need to check what happened during the previous hour (sysdate-1/24) – and you might be surprised – you will get the result for the past one hour but it might not be your last hour.

Currently, it is not possible to change the timezone of SYSDATE and SYSTIMESTAMP since it is coming from the operating system. The SYSDATE and SYSTIMESTAMP functions simply perform a system-call to the server Operating System to get the time – the “gettimeofday” call. The server OS (Unix) timezone settings influences the time that the OS will pass on to Oracle and returned by SYSDATE and SYSTIMESTAMP.

So, how can we get around this issue?

The general recommendation is to use CURRENT_DATE and CURRENT_TIMESTAMP which will return the date in session timezone.

In a globally distributed database environment, UTC is the recommended time zone to use.

– How to change the Time Zone in Oracle Database hosted in OCI with an Example: Doc ID 2459830.1
– Timestamps and time zones – Frequently Asked Questions: Doc ID 340512.1
– How to Change the “Database Time” (SYSDATE and SYSTIMESTAMP) to another Time/Timezone: Doc ID 1988586.1

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

Autonomous Database uses a feature called Autonomous Data Guard to enable a standby (peer) database to provide data protection and disaster recovery for your Autonomous Database instance.

When you enable Autonomous Data Guard, the system creates a standby database that continuously gets updated with the changes from the primary database.

With Autonomous Data Guard enabled Autonomous Database provides one identical standby database that allows the following, depending on the state of the primary database:

• If your primary database goes down, Autonomous Data Guard converts the standby database to the primary database with minimal interruption. After failover completes, Autonomous Data Guard creates a new standby database for you.
  
• You can perform a switchover operation, where the primary database becomes the standby database, and the standby database becomes the primary database.

Autonomous Database does not provide access to the standby database. You perform all operations, such as scaling up the OCPU Count and enabling Auto Scaling on the primary database and Autonomous Data Guard then performs the same actions on the standby database. Likewise, you only perform actions such as stopping or restarting the database on the primary database.

All Autonomous Database features from the primary database are available when the standby instance becomes the primary after the system fails over or after you perform a switchover operation, including the following:

Database Options: The OCPU Count, Storage, Display Name, Database Name, Auto Scaling, Tags, and Licensing options have the same values after a failover to the standby database or after you perform a switchover.

OML Notebooks: Notebooks and users created in the primary database are available in the standby.

APEX Data and Metadata: APEX information created in the primary database is copied to the standby.

ACLs: The Access Control List (ACL) of the primary database is duplicated for the standby.

Private Endpoint: The private endpoint from the primary database applies to the standby.

APIs or Scripts: Any APIs or scripts you use to manage the Autonomous Database continue to work without any changes after a failover operation or after you perform a switchover.

Client Application Connections: Client applications do not need to change their connection strings to connect to the database after a failover to the standby database or after you perform a switchover.

Wallet Based Connections: You can continue using your existing wallets to connect to the database after a failover to the standby database or after you perform a switchover.