ChatGPT vs Oracle DBA

Introduction

Oracle database and ChatGPT come from different galaxies. On one hand, a 45-year-old piece of software that needs experts to setup, configure and maintain, but runs millions of critical databases all other the world. On the other hand, a brand new AI chatbot made by OpenIA and supposed to answer any question you want to ask. But could ChatGPT be usefull for an Oracle DBA willing to get helped?

Is ChatGPT usefull for an Oracle DBA?

An Artifical Intelligence should be able to answer this question, so let’s ask ChatGPT.

ChatGPT can be useful for an Oracle DBA in several ways, such as:

• Automating repetitive tasks such as generating SQL scripts, reports, and monitoring metrics
• Providing quick answers to common database questions, such as troubleshooting errors and performance issues
• Generating documentation and tutorials for database management and administration
• Simulating and testing different scenarios to make better decisions and plan for future changes to the database.

However it’s worth noting that ChatGPT is a general-purpose language model, and may not have the specific expertise of a human Oracle DBA in all cases.

What a program! It sounds great, but ChatGPT is also aware of its limits.

I want a backup script for my Oracle 19c database, please.

Here is an example of a script that can be used to perform a full backup of an Oracle 19c database:

#!/bin/bash

# Set ORACLE_HOME and PATH
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

# Set the database name
export ORACLE_SID=your_db_name

# Create the backup directory
mkdir -p /backup/oracle

# Perform the full backup
rman target / <<EOF
run {
  allocate channel c1 type disk;
  backup format '/backup/oracle/%U' full database;
  release channel c1;
}
EOF

This script sets the ORACLE_HOME and PATH environment variables, sets the ORACLE_SID variable to the name of your database, creates a directory for the backup, and then uses RMAN to perform a full backup of the database, saving the backup files in the /backup/oracle directory.
Please note that this is a very basic script, and you may need to customize it to suit your specific requirements, such as compression, encryption, backup retention policy, and so on.
Also, it’s recommended that you test the script in a development environment before running it in production.

This is not bad. No archivelogs backup is done, and channel allocation is manual, but this script will backup the database.

Should I also backup archivelogs? How can I do that ?

Yes, it’s a best practice to also back up the archivelogs in addition to the database. This will allow you to recover the database to a specific point in time in case of a failure or data loss.
Here is an example of how you could modify the previous script to also include backup of archivelogs:

#!/bin/bash

# Set ORACLE_HOME and PATH
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

# Set the database name
export ORACLE_SID=your_db_name

# Create the backup directory
mkdir -p /backup/oracle

# Perform the full backup
rman target / <<EOF
run {
  allocate channel c1 type disk;
  backup format '/backup/oracle/%U' full database;
  backup archivelog all format '/backup/oracle/%U' ;
  release channel c1;
}
EOF

The above script will take backup of both full database and archivelogs. With this you can recover your database to a specific point in time. And also you may want to keep a backup retention policy for archivelogs.
It’s always a good idea to test the backup and recovery process in a non-production environment to ensure that it works as expected.

Also, it’s a good practice to validate your backup and archivelog files after the backup process is completed to ensure that they are not corrupted and can be used for recovery if needed.

This is also correct, but archivelogs backup is done at the same time, you’d better make 2 different scripts because you will run archivelog backup much more often.

Isn’t it better to backup archivelogs separately?

It can be beneficial to backup archivelogs separately from the database backup for several reasons:

Reduced backup time: Backing up archivelogs separately from the database backup can reduce the time required for the backup process.
Reduced storage space: Backing up archivelogs separately allows you to retain only the required number of archivelogs for your recovery window, which can help save storage space.
Increased flexibility: Backing up archivelogs separately can provide greater flexibility when it comes to recovery. For example, you can restore the database to the point in time of the last full backup, and then apply the archivelogs incrementally to bring the database to a more recent state.
Here’s an example of a script that can be used to backup archivelogs separately from the database backup:

#!/bin/bash

# Set ORACLE_HOME and PATH
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

# Set the database name
export ORACLE_SID=your_db_name

# Create the backup directory
mkdir -p /backup/oracle/archivelogs

# Perform the archivelog backup
rman target / <<EOF
run {
  allocate channel c1 type disk;
  backup format '/backup/oracle/archivelogs/%U' archivelog all;
  release channel c1;
}
EOF

This script will backup all archivelogs, saving them in the /backup/oracle/archivelogs directory.
Please note that, as with any backup, it’s important to test your archivelog backup and recovery process in a non-production environment before running it in production.

Please note that in this case, you will also need to keep track of archivelogs files that are backed up and which are not, so that during recovery you can apply the right archivelogs files. And also make sure to retain the archivelogs files as per your recovery time frame.

OK, but no word about archivelog backup frequency. The real benefit is that doing archivelog backups during the day take few resources and is far better for minimal data loss.

How can I configure retention? Is there anything else to configure for Oracle backups?

Retention policies for Oracle backups can be configured using RMAN’s retention policy command. The retention policy command allows you to specify how long RMAN should keep backups before they are considered obsolete and eligible for deletion.

Here is an example of how you might configure a retention policy for your backups:

RMAN> configure retention policy to recovery window of 7 days;
This command tells RMAN to keep backups for 7 days.

You can also configure retention policy based on number of backups or based on the date of the backup.

Here is an example of how you might configure a retention policy based on number of backups:

RMAN> configure retention policy to redundancy 2;
This command tells RMAN to keep two backups of each file.

Here is an example of how you might configure a retention policy based on the date of the backup:

RMAN> configure retention policy to recovery window of 7 days;
This command tells RMAN to keep backups which are less than 7 days old.

In addition to configuring a retention policy, there are several other important configuration steps that should be taken for Oracle backups, such as:

Configuring backup optimization
Enabling block change tracking
Configuring a backup catalog
Configuring a control file autobackup
Configuring a duplexed backup strategy
Configuring a backup compression
It’s important to note that, configuring backup retention policy and other backup settings is only a small part of a comprehensive backup and recovery strategy. It’s important to test your backups and recovery process regularly, and to monitor your backups to ensure that they are working as expected.

Correct. You would need a catalog if your recovery window is greater than 30 days, and you should take care of control_file_record_keep_time, but ChatGPT is not an Oracle expert for sure.

How can I schedule these backups, database and archivelogs?

There are several ways to schedule Oracle backups, including:

Using the Oracle Scheduler: The Oracle Scheduler is a built-in feature in the Oracle Database that allows you to schedule jobs, including backup jobs. You can use the Oracle Scheduler to schedule the execution of your backup scripts at specific times or intervals.
Using the Operating System’s Scheduler: You can use the operating system’s scheduler, such as cron on Linux or Task Scheduler on Windows, to schedule the execution of your backup scripts.
Using a Third-Party Scheduling Tool: There are several third-party scheduling tools available that can be used to schedule Oracle backups, such as Control-M, Autosys, and Tivoli Workload Scheduler.
Here’s an example of how you might schedule a full database and archivelog backups using the cron on Linux:

# Edit the crontab file
crontab -e

# Schedule full database backups every day at 2:00 AM
0 2 * * * /path/to/your/backup/script/full_db_backup.sh > /path/to/your/backup/logs/full_db_backup.log 2>&1

# Schedule archivelog backups every hour
0 * * * * /path/to/your/backup/script/archivelog_backup.sh > /path/to/your/backup/logs/archivelog_backup.log 2>&1

In this example, the first line schedules a full database backup to run every day at 2:00 AM, and the second line schedules an archivelog backup to run every hour.
You can also schedule the backup in a specific day of week or month.

Please note that it’s important to test your scheduled backups and recovery process in a non-production environment before running it in production, and also make sure that there are enough resources available during the scheduled time for the backups to run smoothly.

Easy, isn’t it? I would not take an archivelogs backup at the same time the full is started.

I have tried to do a backup but I had an error: RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode.

The error message “RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode” indicates that you are trying to perform a backup of the database in NOARCHIVELOG mode.
NOARCHIVELOG mode is a database operating mode in which the database is not configured to create and store archive logs. Without the archivelogs, you can not do a complete recovery of the database, in case of a failure.

To fix this issue, you will need to switch the database to ARCHIVELOG mode and configure it to create and store archive logs.
Here are the steps to switch the database to ARCHIVELOG mode:

Start the database in MOUNT mode:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
Switch the database to ARCHIVELOG mode:

SQL> ALTER DATABASE ARCHIVELOG;
Open the database:

SQL> ALTER DATABASE OPEN;

Once you have switched the database to ARCHIVELOG mode, you should be able to perform a backup of the database. Remember to backup the archivelogs as well, since they are necessary to restore the database to a specific point in time.

It’s recommended to test your backup and recovery process in a non-production environment before running it in production, and also make sure to have enough disk space to store archivelog files

Correct.

My backup is now running fine, how can I restore to a specific point in time, for example today at 9.30AM?

To restore your Oracle database to a specific point in time, you can use the RMAN restore and recover commands. Here is an example of how you might restore and recover your database to a specific point in time:

# Connect to the RMAN target
rman target /

# Restore the database to the specific point in time
rman> restore database until time "to_date('2022-01-12 09:30:00', 'yyyy-mm-dd hh24:mi:ss')";

# Recover the database
rman> recover database;

The above example will restore the entire database to the point in time of 9:30 AM on January 12, 2022, and then it will recover the database.
Please note that, before doing this, you have to have your backups and archivelogs in place and also you have to have a good retention policy in place.

It’s important to note that a restore and recover operation will result in the loss of any data that was created or modified after the point in time to which you are restoring.
It’s always a good practice to test your backup and recovery process in a non-production environment before running it in production.

It’s a good idea to create a restore and recover script and test it in a non-production environment before using it in production, to ensure that it works as expected and that you know how to use it in case of a disaster recovery scenario.

I’m not so sure it will work like that. You need to put RESTORE and RECOVER commands in the same run block, unless the RECOVER process will not stop to the specified point in time. And it makes a huge difference!

I did the point in time restore but I cannot open the database? What’s wrong?

There are several possible reasons why you may not be able to open the database after performing a point-in-time restore:

Incorrect restore point: You may have specified the wrong point-in-time to restore the database to. Double check the time that you have specified in the restore command and make sure it is the correct time.
Incomplete recovery: The recovery process may have been incomplete or failed. Check the RMAN logs for any errors or messages indicating that recovery failed.
Incorrect backup: You may have used an incorrect backup to restore the database. Double check that you are using the correct backup files, and that they are not corrupted or incomplete.
Incompatible restore: The restore may not be compatible with the current version of the database. Make sure that the version of the database that you are restoring to is the same as the version of the database that the backup was taken from.
Missing archivelogs: The archivelogs that were generated after the backup was taken may be needed for the recovery process. Make sure that you have all the archivelogs that were generated between the backup and the point-in-time that you want to restore to.
Inconsistency in the datafiles: There may be an inconsistency in the datafiles. In this case, you may need to perform a media recovery.
It’s recommended that you check the alert.log and the trace files for more information about the error and to identify the root cause of the problem. Once you have identified the problem, you can take the appropriate action to resolve it.
It’s always a good practice to test your backup and recovery process in a non-production environment before running it in production.

OK, in my mind it was opening database in normal mode, and I would expect that ChatGPT tells me to open in resetlogs mode.

Oracle tells me to open the base with the reset logs option but how can I do that?

To open a database with the resetlogs option, you can use the SQL command “ALTER DATABASE OPEN RESETLOGS” while connected to the target database as a user with the SYSDBA privilege.
Here is an example of how you might use the SQL command to open a database with the resetlogs option:

SQL> connect / as sysdba

SQL> alter database open resetlogs;

This command will open the database and reset the online redo logs, which will enable you to recover the database to the most recent point in time. However, after resetting the logs, all changes made to the database before the reset will be lost and the database will be in a consistent state.

It’s important to note that the resetlogs option should only be used as a last resort, when other recovery options have failed.

It’s always a good practice to test your backup and recovery process in a non-production environment before running it in production. Before doing the resetlogs option, make sure that you have the backups and archivelogs of the entire time period in case you need to recover any lost data.

Also, it’s a good idea to inform your end users and any other stakeholders that the database will be down while the resetlogs option is being performed, and to provide an estimate of how long the process will take.

OK, but after doing an incomplete recovery, RESETLOGS is needed.

Conclusion

ChatGPT will not replace (for now) an Oracle DBA with years of experience, but it definitely can help beginner DBAs or DBAs not used to Oracle databases. ChatGPT’s answers are correct, scripts are clean and it always reminds you to test on a non-production database and warns that data may be lost. And this is already quite good! Consider ChatGPT as an helpful assistant that may save your precious time.