How do I set up an Oracle DB backup under Windows

Operatingsystem

Windows

Software

8.5 and higher

Solution

First check whether the backup is supported via the requirements below.

Requirements

The backup software must be installed on the same machine that the Oracle DB server is running on.
It is wise to increase the memory limit from 2048MB to 4096MB to improve performance.
 

---

Tools
The following tools must be installed:

• RMAN (Recovery manager), If all goes well, this is already the case by default
  • Use the following commands to check this:
    set ORACLE_SID_orcl
    rman target /
• SQL*Plus
  • Use the following command to check this:
    sqlplus / as sysdba

 

---

Software / Windows / Oracle versions
The Oracle version can be checked via the command:
sqlplus / as sysdba

• Oracle 19c (8.5.0.63 en hoger)
  • Windows Server 2012 R2 / 2016 / 2019 / 2022^*
    * 8.5.4.80 en hoger
• Oracle 18c (8.5.0.77 en hoger)
  • Windows Server 2012 / 2012 R2 / 2016
• Oracle 12c
  • Windows Server 2008 / 2008 R2 / 2012 / 2012 R2

 

---

Windows account rights
The Windows account used to make the backup must be a member of the following security groups:

• Administrators
• ORA_DBA
• ORA_OraDB19Home1_SYSBACKUP
• ORA_OraDB19Home1_SYSDG
• ORA_OraDB19Home1_SYSKM

 

---

Oracle Windows services
The following Oracle database related services must be active:

• OracleJobScheduler$ID$
• OracleOraDB19Home1MTSRecoveryService
• OracleOraDB19Home1TNSListener
• OracleRemExecServiceV2
• OracleService$SID

 

---

Instance and status
Check whether the instance is correct and what the database status is, the status of the database must be open:
SQL > select instance_name, status from v$instance;

INSTANCE_NAME --------------	STATUS ----------------------
orcl	OPEN

 

---

Java installation
Java must be installed in the Oracle database instance. the status must be VALID.
This can be checked with the following command:
SQL> select comp_name, status from dba_registry;

• JServer JAVA Virtual Machine
• Oracle Database Java Packages

 

---

Oracle system account rights
The backup software uses the Oracle system account to connect to the database server and requires the JAVASYSPRIV right to do so.
This can be checked via the following command:
SQL> select * from DBA_ROLE_PRIVS where r(grantee)='SYSTEM';
If it is not assigned, it can be assigned via the command:
SQL> grant javasyspriv to system;

Furthermore, the sysdba right must also be granted.
This can be checked via the following command:
SQL> select * from v$pwfile_users where sysdba='TRUE';
If it is not assigned, it can be assigned via the command (19c & 18c):
SQL> grant sysdba to system container=ALL;
If it is not assigned, it can be assigned via the command (12c):
SQL> grant sysdba to system;

 

---

Temporary directory
During the backup process, the temporary folder is used for:

• The spooled Oracle database(s) and archived logs
• Deduplication blocks files

We recommend choosing a location with sufficient free space and it is wise not to do this somewhere on C:\ or on the partition where the DB is located.

To get an idea of how large the DBs are, a list of the permanent data files can be requested via the Oracle RMAN REPORT SCHEMA feature:

C:\Users\Administrator>set ORACLE_SID=orcl
C:\Users\Administrator>rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Oct 29 18:29:44 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1562659286)


RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================

File ----	Size(MB) --------	Tablespace ----------	RB segs -------	Datafile Name -------------
1	910	SYSTEM	YES	D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
3	920	SYSAUX	NO	D:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
4	60	UNDOTBS1	YES	D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
....

 

---

Practical tips and tips

• To enable a full recovery of the Oracle database instance, all databases, including SYSAUX, SYSTEM, UNDOTBS1, USERS and related application databases, except "TEMP" must be selected in the backup source when creating the backup upset. Otherwise, without a backup of these databases, a full recovery of the Oracle database instance is NOT possible.
• Database backups should be scheduled when system activity is low to achieve the best possible performance and minimize the impact on database server performance (for example: scheduled to run on weekends).
• For archived log backups, the backup frequency should depend on the number of transactions or activities in the database. Databases with more transactions should perform archived log backups more frequently (for example, instead of a daily backup, it should be performed several times a day).
• To provide maximum data protection and flexible recovery options, it is recommended to configure the following:
  • At least one cloud destination
  • At least one local destination for quick recovery
• Perform periodic test restore to ensure your backup is set up and data is being backed up properly.
• Performing recovery tests can also help identify potential problems or gaps in your recovery plan. It is important that you do not try to make the test easier, because the purpose of a successful test is not to show that everything is error-free. Deficiencies in the plan may be identified during testing, and it is important to identify those deficiencies.
• The Restore Raw File option is intended for advanced Oracle database administrators and should only be used if you have in-depth knowledge and understanding of the Oracle database engine, Oracle database schema, database server knowledge, and network infrastructure. Therefore, it is not recommended to use this recovery option as additional Oracle techniques and scripts must be used to facilitate manual database recovery.

Limitations

• Oracle Express Edition and Oracle XE are not supported
• Backup and restore via another machine is not supported
• Only standalone Oracle installations are supported, the following configurations are not supported:
  • Clusterware or RAC (Real Application Clusters)
  • ASM (Automatic Storage Management)
  • Data Guard etc.
• A separate backup set must be created for each Oracle instance.
• Even if you already select TEMP as part of the source selection, it will still be skipped

Create Backupset

1. Launch the software and log in
2. Click on Backup Sets.
3. Click on Add.
   1. Choose Oracle Database Server Backup as the type.
   2. Give the set a clear name.
   3. Enter the login ID (standard system).
   4. Enter the password for the account.
   5. Host is not customizable.
   6. Port (default 1521)
   7. SID - The Oracle System Identifier (This can be found via the instructions under the heading Instance).
   8. Click Next.
   9. Select the databases that should be included in the backup and then click Next.
   10. The schedule includes the database backup once a week and the log backup for the other days, as the database backup can have a performance impact on the system.
       Adjust things here if desired and click Next.
   11. In the destination, you can add a local destination for quick recovery and then click Next.
   12. Choose the desired encryption and document it properly!.
   13. Now complete the last few steps of creation.
4. Don't forget to edit the backup set after creating it to choose a good location for the temporary folder!
5. Clearing archive logs older than 60 days is enabled by default and is performed after every backup. If you want this differently, you can adjust this in the backup set > Other
6. Click Save.