Senin, 12 Agustus 2024

RMAN-backup oracle19c

 

Getting Started with RMAN backups and Oracle 19c

Update: This blog is an updated version of the original version that was focused on Oracle 11g, in this blog post the examples are based on an Oracle 19c Database.

In this post, we will provide you with a few quick and easy steps you can follow to start implementing RMAN (Recovery Manager) backups. The Oracle documentation is a great source for those who would like to get into all the details of database backups and recovery using RMAN, but here I will focus on a few of the basics to get you going.

 

One area that is critical to any database environment is backup and recovery, but too many times we have seen DBAs neglecting backups. A perfect example of this is when backups are implemented but never tested. Then six months later when a production database needs to be restored the DBA realizes that something is actually wrong with the backups, and a full recovery is not possible

Did you know that some DBAs are scared of using RMAN backups?  

In older versions, when Oracle introduced RMAN and it was in its 'infancy', it is fair to say that occasionally it did create havoc, and some DBAs simply refused to use it. Instead, they preferred the “online backup” method of placing tablespaces in backup mode, copying the datafiles to a backup location and then ending the tablespace backup mode.

But since Oracle 10g RMAN has improved, more DBAs are starting to use it and Oracle has done an amazing job with new features implemented as part of all the versions since. So if you are using Oracle 12c and above, you no longer have any excuses for not using RMAN.  Some might say it is too complex; but did you know that you can perform a backup of your database with simple commands such as:

backup database;

Does not look that complex, does it? But sometimes, we, the DBAs, complicate matters by writing long and elaborate scripts to perform backups when in most environments it is not needed (although in some cases there are good reasons for this). Just implementing a basic solution might be a good starting point. You can always refine it and make it more sophisticated as you become more familiar with RMAN and all the extra options it provides. With a few easy steps, you can have backups in place with ease.

When performing backups you may prefer using backups to local disk-based storage. The disk location where the database backups are stored should then be included as part of the database server backup cycle (which could just be a normal daily file system backup). This is a quick and easy way and no special database backup software agents are required.

There are a few reasons for using disk-based backups, and one of the main ones is that you do not have to wait for tapes to be loaded or returned from secure storage when a restore or recovery is required. Then you can just start the restore process with your latest backups being directly available on disk.

In my examples below there is Oracle 19c on Oracle Linux 7. So let’s get started.

Step 1: Setting the environment

One of the first things to do when setting up new backup scripts or even just setting up a new Oracle environment is to update your environment variables to include “NLS_DATE_FORMAT”. By setting this environment variable, the dates being displayed will be in a more readable format, including the time, not just the date. I normally add this variable to my Unix account .bash_profile.

For example, just add the following two lines to the oracle .bash_profile:

NLS_DATE_FORMAT=”DD/MM/YYYY HH24:MI:SS”
export NLS_DATE_FORMAT

To give you an idea of what effect this parameter has, if a “list backup” command is executed to show known backups, you'll see the following: Without NLS_DATE_FORMAT:

..
..
  List of Archived Logs in backup set 12
  Thrd Seq     Low SCN Low Time  Next SCN Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    21   1406347   19-MAR-20 1585517    15-APR-20
  1    22   1585517   15-APR-20 1587391    15-APR-20
  1    23   1587391   15-APR-20 1587456    15-APR-20
  1    24   1587456   15-APR-20 1588071    15-APR-20
  1    25   1588071   15-APR-20 1588079    15-APR-20

..
..

With NLS_DATE_FORMAT set:

..
..
  List of Archived Logs in backup set 12
  Thrd Seq     Low SCN Low Time            Next SCN Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    21   1406347   19/03/2020:11:27:14 1585517    15/04/2020:15:26:44
  1    22   1585517   15/04/2020:15:26:44 1587391    15/04/2020:15:37:40
  1    23   1587391   15/04/2020:15:37:40 1587456    15/04/2020:15:39:33
  1    24   1587456   15/04/2020:15:39:33 1588071    15/04/2020:15:44:58
  1    25   1588071   15/04/2020:15:44:58 1588079    15/04/2020:15:44:58
..

As you can see above, the dates are showing the time as well when this parameter is set. In my scripts, I usually have a small function setting the environment and this is one variable that I always set.

Step 2: Default RMAN configuration

One of the hidden “gems” of RMAN is that you can update your default configuration to make use of specific values. To see the default values you can execute the “show all” command when connected to your database. Below is an example:

[oracle@dbv1 ~]$ rman
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 15 15:29:56 2020
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /
connected to target database: DEV (DBID=4113794352)

RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DEV are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_DEV.f'; # default
RMAN>

Note: do not just update these values without doing a full review and understanding the implications of altering these values. One of the most important values you might want to specify is to enable auto backup of the controlfile, which by default is set to OFF in earlier database versions, but in 19c it is ON which is perfect. When you update this to ON, RMAN will automatically backup the control file at the end of every backup. This is the first configuration worth a review and if it is not set to ON, I will update it. This can easily be done with:

RMAN> configure controlfile autobackup on;

Following the above, update the controlfile autobackup location. In our example we are using local disk based backups, and we are updating the configuration to create the control file backup in my backup location which in my example is: /backups/DEV/. To do this, execute the following command:

RMAN> configure controlfile autobackup format for device type disk to '/backups/DEV/%F';

To configure RMAN to make use of a specific location for disk based backups, you can update the default channel configuration to include the disk location where the backups should be stored. In this example we are using /backups/DEV as the backup destination. I will update the default RMAN configuration to reflect this:

RMAN> configure channel device type disk format '/backups/DEV/%U';

The final configuration change I make is to ensure RMAN is making use of compressed backup sets by default. Creating compressed backups is one of the best features of RMAN (since 10g). This is extremely useful as you can now, in most cases, keep multiple backups on disk. This means that you have a larger recovery window.  With Oracle Standard Edition 2 only the “BASIC” compression algorithm can be used, for more details on this and the compression algorithm please review the Oracle Documentation. Configuring the use of compressed backup sets when using disk-based backups can be done with the following command:

RMAN> configure device type disk backup type to compressed backupset;

Once this is done, by default my disk-based backups will be compressed. Now to show you the configuration following the above changes:

RMAN> show all;

RMAN configuration parameters for database with db_unique_name DEV are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/DEV/%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backups/DEV/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_DEV.f'; # default
RMAN>

Step 3: Creating a backup

Now let’s assume you are following the above and have changed the default configuration as described.  With this in place, you can just execute a one-line set of commands to back up the database. Below is an example, including output.

RMAN> backup database plus archivelog;
Starting backup at 15/04/2020:15:37:40
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=262 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=21 RECID=1 STAMP=1037806005
input archived log thread=1 sequence=22 RECID=2 STAMP=1037806660
channel ORA_DISK_1: starting piece 1 at 15/04/2020:15:37:41
channel ORA_DISK_1: finished piece 1 at 15/04/2020:15:37:48
piece handle=/backups/DEV/02utnb25_1_1 tag=TAG20200415T153741 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 15/04/2020:15:37:48
Starting backup at 15/04/2020:15:37:48
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/DEV/datafile/o1_mf_system_h754v3kb_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DEV/datafile/o1_mf_sysaux_h754v8xy_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/DEV/datafile/o1_mf_undotbs1_h754vcpg_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/DEV/datafile/o1_mf_users_h754vwdz_.dbf
channel ORA_DISK_1: starting piece 1 at 15/04/2020:15:37:48
channel ORA_DISK_1: finished piece 1 at 15/04/2020:15:38:33
piece handle=/backups/DEV/03utnb2c_1_1 tag=TAG20200415T153748 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/DEV/A129A5525C601CCFE053020011AC6470/datafile/o1_mf_undotbs1_h758gd4m_.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/DEV/A129A5525C601CCFE053020011AC6470/datafile/o1_mf_system_h758gd48_.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/DEV/A129A5525C601CCFE053020011AC6470/datafile/o1_mf_sysaux_h758gd4l_.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/DEV/A129A5525C601CCFE053020011AC6470/datafile/o1_mf_users_h758gr5q_.dbf
channel ORA_DISK_1: starting piece 1 at 15/04/2020:15:38:33
channel ORA_DISK_1: finished piece 1 at 15/04/2020:15:38:58
piece handle=/backups/DEV/04utnb3p_1_1 tag=TAG20200415T153748 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/DEV/A128C9760B650223E053020011AC71F3/datafile/o1_mf_undotbs1_h754vdc8_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/DEV/A128C9760B650223E053020011AC71F3/datafile/o1_mf_system_h754v5vh_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DEV/A128C9760B650223E053020011AC71F3/datafile/o1_mf_sysaux_h754vbmt_.dbf
channel ORA_DISK_1: starting piece 1 at 15/04/2020:15:38:58
channel ORA_DISK_1: finished piece 1 at 15/04/2020:15:39:33
piece handle=/backups/DEV/05utnb4i_1_1 tag=TAG20200415T153748 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 15/04/2020:15:39:33
Starting backup at 15/04/2020:15:39:33
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=23 RECID=3 STAMP=1037806773
channel ORA_DISK_1: starting piece 1 at 15/04/2020:15:39:34
channel ORA_DISK_1: finished piece 1 at 15/04/2020:15:39:35
piece handle=/backups/DEV/06utnb5m_1_1 tag=TAG20200415T153934 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15/04/2020:15:39:35
Starting Control File and SPFILE Autobackup at 15/04/2020:15:39:35
piece handle=/backups/DEV/c-4113794352-20200415-01 comment=NONE
Finished Control File and SPFILE Autobackup at 15/04/2020:15:39:36
RMAN>

Now let's look at the size of the backups, bearing in mind that this example utilizes a small database, and your database backup size will be different:

[oracle@dbv1 ~]$ ls -l /backups/DEV/
total 802540
-rw-r----- 1 oracle oinstall  43122176 Apr 15 15:37 02utnb25_1_1
-rw-r----- 1 oracle oinstall 368041984 Apr 15 15:38 03utnb2c_1_1
-rw-r----- 1 oracle oinstall 204496896 Apr 15 15:38 04utnb3p_1_1
-rw-r----- 1 oracle oinstall 205004800 Apr 15 15:39 05utnb4i_1_1
-rw-r----- 1 oracle oinstall     12800 Apr 15 15:39 06utnb5m_1_1
-rw-r----- 1 oracle oinstall   1114112 Apr 15 15:39 c-4113794352-20200415-01

A quick and easy script that can be used regularly, taking into account the default values set as above, is:

RMAN> run {
           backup database filesperset 5;
           sql 'alter system archive log current';
           backup archivelog all filesperset 50;
    }

If you did not configure the default RMAN values above (step 2), you can achieve similar results by running the following backup script:

RMAN> run {
 backup as compressed backupset filesperset 5 database format '/backups/DEV/dbf-%U';
 sql 'alter system archive log current';
 backup as compressed backupset filesperset 50 archivelog all format '/backups/DEV/arc-%U';
 backup current controlfile format '/backups/DEV/cf-%U';
}

And as we mentioned earlier, once you have the basics working, you can start looking at more complex scripts and scenarios.

Next Steps:

The above was just a quick example of how you can utilize the default RMAN configuration to get backups in place quickly.  It is however important to remember that when you are looking at implementing a backup solution for a production environment, you implement a backup retention period that fits in with your company's backup and recovery policies. Retention periods weren't covered in this post, but we recommend you to read up on retention policies and use the list, report, crosscheck and delete commands.

The most important recommendation we can give you when implementing backups is to TEST it. Always test your backups and recovery procedures and document them. Yes, we know everyone hates documentation, but if you have an urgent restore at 3 am, having documentation to follow will make it easier. To test your backups, you can easily perform backups on your production system, then copy the backups to another server and perform a restore there. It is crucial that your production backups are tested on a regular basis. 

In the next blog post, we will cover how you can offload backups on your standby database, specifically when using Oracle Standard Edition using a Standby™️ configuration.

Other Useful Notes:

Without going into too much detail below are some useful tips:

  • If you are not using a recovery catalog when doing RMAN backups, increase the database parameter controlfile_record_keep_time from the default value of 7 (days) to a higher value. For example, 30 is a good starting point.

  • For Oracle 11g RAC environments, if you are getting an ORA-00245 error when backing up the controlfile, you have to update the default configuration of RMAN to place the snapshot controlfile on shared storage.

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '<shared_disk>/snapcf_<DBNAME>.f';

See Oracle Support Note 1472171.1 for more details. (The original Note 1268725.1 was removed from Oracle Support).

For more insights about RMAN backups, read the following blog: RMAN backups on your standby database... yes, it is easy.

If you have any questions on the above, please don't hesitate to contact us.

Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)