Recovery Manager or RMAN as it is more commonly known, is Oracle’s proprietary backup, restore and recovery solution for Oracle databases.
In Part 12, we’ll setup RMAN and run through some common backup, restore and recovery scenarios. We’ll also have a little fun with some of the more interesting things RMAN can do.
It’s worth pointing out there appears to be a lot of smoke and mirrors associated with RMAN. When Oracle created it, it gave it its own cryptic language and syntax. That didn’t help. Its standard output is very verbose, some of which looks like error messages. That doesn’t help either. It often appears to hang when running a backup or restore, giving no indication that it’s actually doing anything. That can make you nervous especially in a stressful restore/recovery situation. RMAN can do a vast array of very powerful and clever things and that can get in the way of understanding the three things you’re only ever likely to use it for. Namely a full database backup, full database restore and a point-in-time recovery.
Since RMAN can be very confusing, so this section of the Infrastructure series will mainly focus on the essentials to help you backup Oracle databases and restore/recover them should the need arise. Like many things with Oracle, there are multiple layers of detail that include ever increasing levels of functionality and complexity. By all means learn and memorize everything to do with RMAN, but for now let’s keep this simple. Yes, there’s more to it, but here’s my Top 10 RMAN Things To Know:
- RMAN has 2 backup formats. COPY is an image copy. BACKUP SET is a set of one or more BACKUP SET PIECE files. A BACKUP SET PIECE file contains the data blocks from one or more files which RMAN can backup.
- RMAN can backup database datafiles, control files, archived redo log files and SPFILEs. It can even backup a BACKUP SET (yep, it can backup a backup!).
- RMAN backup activity is always recorded in the database control files and optionally in an RMAN Recovery Catalog.
- RMAN can be run interactively from the command line, via OS script, via RMAN script (stored in an RMAN Recovery Catalog) or via Enterprise Manager.
- RMAN can backup a database either online or offline.
- RMAN can backup either the whole database or part of a database (specific datafiles or tablespaces).
- RMAN supports full database backups and incremental backups (cumulative or differential).
- RMAN supports backup compression, encryption and parallelism.
- RMAN’s default persistent configuration settings can be overridden for a given RMAN session.
- RMAN supports complete (full) database restore/recovery or incomplete restore/recovery to a point in time, transaction or SCN.
Quick links to all the tasks:
- Task #1: Create an RMAN Recovery Catalog.
- Task #2: Configure the RMAN Environment.
- Task #3: Execute RMAN Backups. (coming soon)
- Task #4: Execute RMAN Restores & Recoveries. (coming soon)
- Task #5: More Advanced Uses of RMAN. (coming soon)
- Task #6: RMAN Reporting. (coming soon)
Task #1: Create an RMAN Recovery Catalog.
In most cases, using an RMAN recovery catalog is useful. It’s not strictly necessary since RMAN database backup activity will be recorded in the database control files anyway. Like with most things there are pros and cons. For example, if you have more than a few databases, recording backup activity in a central location is more efficient and convenient. Plus, if you ever had to rebuild your control files, you’d lose the backup information stored there. In any event, the backup activity information stored in the controlfiles will get aged out eventually, so it’s better to keep that data elsewhere. On the other hand, if your RMAN catalog database is down then your backups will fail. So you have the added overhead of protecting your RMAN catalog database. In most cases you should treat it like any other production database.
To create an RMAN recovery catalog, just follow these 3 simple steps:
Step #1: Create a Database for the RMAN Catalog.
Since our infrastructure is small there’s no real need to fire up a separate database to host an RMAN catalog. Instead, we’ll create another PDB within the CDB (PADMIN) which is already running the OEM Management Repository PDB (EMPDBREPOS).
Note, running more than one PDB within a CDB requires the Multitenant license. Plus in a production environment, you would more likely choose to have completely separate databases for the OEM Management Repository and the RMAN catalog anyway. What we’re doing here is for demonstration/educational purposes only.
So let’s crack on and create another PDB:
[oracle@oraemcc ~]$ . oraenv ORACLE_SID = [oracle] ? PADMIN The Oracle base has been set to /u01/app/oracle [oracle@oraemcc ~]$ sqlplus / as sysdba SQL> show con_name CON_NAME -------- CDB$ROOT SQL> create pluggable database rmancat admin user rmancat_admin identified by rmancat_admin; Pluggable database created. SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY EMPDBREPOS READ WRITE RMANCAT MOUNTED SQL> alter pluggable database rmancat open; Pluggable database altered. SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ----------- PDB$SEED READ ONLY EMPDBREPOS READ WRITE RMANCAT READ WRITE
Next, we’ll quickly add the relevant TNS connect string to the tnsnames.ora file:
[oracle@oraemcc ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora RMANCAT = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oraemcc.mynet.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RMANCAT.mynet.com) ) )
Step #2: Create the Recovery Catalog Owner (RCO).
[oracle@oraemcc ~]$ sqlplus system@rmancat SQL> create tablespace rmancat_data; Tablespace created. SQL> create user rco identified by rco default tablespace rmancat_data quota unlimited on rmancat_data; User created. SQL> grant create session, resource, recovery_catalog_owner to rco; Grant succeeded.
Step #3: Create the Recovery Catalog.
Time to use the RMAN client to login:
[oracle@oraemcc ~]$ rman RMAN> connect catalog rco/rco@rmancat connected to recovery catalog database RMAN> create catalog tablespace rmancat_data; recovery catalog created
That’s it! The RMAN recovery catalog is now ready to use.
Task #2: Configure the RMAN Environment.
Before we can start to run backups, we need to complete a few setup steps. First, let’s review the database environment we’ll be using to test out RMAN:
| Server | Database | Usage |
|---|---|---|
| oraemcc | PADMIN | CDB (12.1.0.2) containing 2 PDBs (EMPDBREPOS & RMANCAT) |
| oraemcc | EMPDBREPOS | PDB (12.1.0.2) containing the OEM Repository |
| oraemcc | RMANCAT | PDB (12.1.0.2) containing the RMAN Recovery Catalog |
| orasvr01 | T122 | Non-CDB (12.2.0.1) contains user data |
| orasvr02 | T183 | CDB (18.3) containing 1 PDB (T183_PDB1) |
| orasvr02 | T183_PDB1 | PDB (18.3) contains user data |
Step #1: Add Entries to the tnsnames.ora File.
Copy the TNS connect string for RMANCAT (see Step #1 above) to the tnsnames.ora files for the T122 and T183 databases.
Step #2: Register the Databases with the RMAN Recovery Catalog.
Since we’re using an RMAN catalog, we need to register the target databases in the catalog. When connecting to the target database and the recovery catalog, RMAN reported a problem. Solution to follow. Keep reading:
[oracle@orasvr01 ~]$ rman target=/ catalog=rco/rco@rmancat Recovery Manager: Release 12.2.0.1.0 - Production on Mon Nov 25 15:17:20 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: T122 (DBID=2185934179) connected to recovery catalog database PL/SQL package RCO.DBMS_RCVCAT version 12.01.00.02. in RCVCAT database is too old [oracle@orasvr02 ~]$ rman target=/ catalog=rco/rco@rmancat Recovery Manager: Release 18.0.0.0.0 - Production on Mon Nov 25 15:18:30 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. connected to target database: T183 (DBID=2832597398) connected to recovery catalog database PL/SQL package RCO.DBMS_RCVCAT version 12.01.00.02. in RCVCAT database is too old
The reason this happens is because the RMAN recovery catalog was created in a 12.1.0.2 database and the two databases connecting to it are both higher versions. RMAN does provide an UPGRADE CATALOG command, but running it connected to the catalog from a 12.1.0.2 RMAN client doesn’t actually upgrade it. Which makes sense. So let’s try upgrading the catalog connecting from the 18.3 RMAN client on orasvr02:
[oracle@orasvr02 ~]$ rman catalog=rco/rco@rmancat Recovery Manager: Release 18.0.0.0.0 - Production on Mon Nov 25 15:44:33 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database PL/SQL package RCO.DBMS_RCVCAT version 12.01.00.02. in RCVCAT database is too old RMAN> upgrade catalog; recovery catalog owner is RCO enter UPGRADE CATALOG command again to confirm catalog upgrade RMAN> upgrade catalog; recovery catalog upgraded to version 18.03.00.00.00 DBMS_RCVMAN package upgraded to version 18.03.00.00 DBMS_RCVCAT package upgraded to version 18.03.00.00.
Now let’s try connecting to the catalog from the 12.2.0.1 RMAN client on orasvr01:
[oracle@orasvr01 ~]$ rman target=/ catalog=rco/rco@rmancat
Recovery Manager: Release 12.2.0.1.0 - Production on Mon Nov 25 15:46:22 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: T122 (DBID=2185934179)
connected to recovery catalog database
recovery catalog schema release 18.03.00.00. is newer than RMAN release
Note the message about the schema release being newer than the RMAN release. This message is informational only and no further action is required. See MOS doc ID 73431.1 for the RMAN certification matrix. Now we know there’s no issue, let’s go ahead and register the databases:
connected to target database: T122 (DBID=2185934179) connected to recovery catalog database recovery catalog schema release 18.03.00.00. is newer than RMAN release RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete [oracle@orasvr02 ~]$ rman target=/ catalog=rco/rco@rmancat Recovery Manager: Release 18.0.0.0.0 - Production on Mon Nov 25 16:10:31 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. connected to target database: T183 (DBID=2832597398) connected to recovery catalog database RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete
Step #3: Create Database Backup Users.
You can do everything you need to do with RMAN as the Oracle Database software owner (usually oracle). However, least privilege and separation of duty security measures suggest using dedicated backup users. Follow these steps on both orasvr01 and orasvr02:
[oracle@orasvr01 ~]$ sqlplus / as sysdba
SQL> create user rmanbackup identified by rmanbackup
default tablespace users
temporary tablespace temp
quota unlimited on users;
User created.
SQL> grant sysbackup to rmanbackup;
Grant succeeded.
SQL> select username,SYSDBA,SYSOPER,SYSASM,SYSBACKUP,SYSDG,SYSKM
from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM
-------------------- ----- ----- ----- ----- ----- -----
SYS TRUE TRUE FALSE FALSE FALSE FALSE
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE
RMANBACKUP FALSE FALSE FALSE TRUE FALSE FALSE
[oracle@orasvr01 ~]$ rman target rmanbackup/rmanbackup using sysbackup
connected to target database: T122 (DBID=2185934179)
RMAN> connect catalog rco/rco@rmancat
connected to recovery catalog database
For a CDB, create a common user and assign the database backup privilege for all PDBs:
[oracle@orasvr02 ~]$ sqlplus / as sysdba
SQL> create user c##rmanbackup identified by rmanbackup
default tablespace users
temporary tablespace temp
quota unlimited on users;
User created.
SQL> grant sysbackup to c##rmanbackup container=all;
Grant succeeded.
SQL> select username,SYSDBA,SYSOPER,SYSASM,SYSBACKUP,SYSDG,SYSKM
from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM
-------------------- ----- ----- ----- ----- ----- -----
SYS TRUE TRUE FALSE FALSE FALSE FALSE
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE
C##RMANBACKUP FALSE FALSE FALSE TRUE FALSE FALSE
[oracle@orasvr02 ~]$ rman target rmanbackup/rmanbackup using sysbackup
connected to target database: T183 (DBID=2832597398)
RMAN> connect catalog rco/rco@rmancat
connected to recovery catalog database
Step #4: Review the RMAN Defaults.
Once RMAN is connected to the target database and to the catalog, you can review and change the persistent configuration settings:
RMAN> show all; RMAN configuration parameters for database with db_unique_name T122 are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/snapcf_T122.f'; # default
These default configuration settings are the same in 12.2 as they are in 18.3. Let’s take a quick look at what they mean:
- RETENTION POLICY
- Determines how long Oracle will keep your backups around. If you’re using a FRA, Oracle will automatically remove expired backups. If you’re not, Oracle marks expired backups as obsolete and you have to delete them. Retention comes in two flavors. Window based is set to a number of days. Redundancy based is set to the number of level 0 or full backups to keep. You can also have no retention. Strange but true. Backup retention granularity is at the backupset or image copy level.
- BACKUP OPTIMIZATION
- Determines if RMAN should backup a file if there’s already an identical file in an existing backup on the same device type. If set to YES, RMAN skips backing up another identical copy.
- DEFAULT DEVICE TYPE
- Backup/restore operations happen via channels. A channel is a server process which connects the target database to the backup device. Channels are opened to a specific type of device, usually either DISK or SBT (System Backup Tape). This sets the device type default.
- CONTROLFILE AUTOBACKUP
- Determines if the database controlfile is backed up automatically.
- CONTROLFILE AUTOBACKUP FORMAT
- Determines the naming convention of the backed up control file. Usually set to %F which combines the DBID, date (YYYYMMDD) and a 2 digit hexadecimal number. All the different format strings are documented here.
- DEVICE TYPE PARALLELISM n BACKUP TYPE TO BACKUPSET
- Determines the number of channels (n) RMAN will open to the specified device type. Then specifies the type of backup to create by default (BACKUPSET), the other type being COPY (an image copy).
- DATAFILE BACKUP COPIES
- Determines the number of copies of each backupset to be created when backing up.
- ARCHIVELOG BACKUP COPIES
- Determines the number of copies of archived redo log files to be created when backing up.
- MAXSETSIZE
- Specifies the maximum size of a backupset created on a channel.
- ENCRYPTION FOR DATABASE
- Determines if the backups will be encrypted. To use encryption, the ASO must be licensed.
- ENCRYPTION ALGORITHM
- The algorithm to use if you’re using backup encryption.
- COMPRESSION ALGORITHM
- Determines the level of compression to use when backing up. The default (BASIC) does not require a license for the ACO. The OPTIMIZE FOR LOAD TRUE clause ensures RMAN optimizes CPU usage and disables precompression block processing. Conversely, setting OPTIMIZE FOR LOAD FALSE enables precompression block processing whereby block free space is consolidated and set to binary zeros. This leads to improved backup compression at the expense of additional CPU overhead. It works best for blocks with high insert and delete DML activity.
- RMAN OUTPUT TO KEEP
- Determines the number of days RMAN logging information is kept in the RMAN catalog. Logging information is stored in RC_RMAN_OUTPUT. The output can also be queried via V$RMAN_OUTPUT.
- ARCHIVELOG DELETION POLICY
- Determines when archived redo logs should be deleted. The policy applies to all multiplexed archived redo log file destinations. Only archived redo logs in the FRA are automatically deleted. To delete archived redo logs as they are backed up, you can use additional syntax in the backup command, BACKUP …DELETE INPUT.
- SNAPSHOT CONTROLFILE NAME
- The database control file(s) contain the latest SCN and a map of where all the database datafiles are located. This data is constantly being updated as a part of normal database operations. When RMAN starts a backup, it needs a read consistent view of that data and that’s what the snapshot control file is. This setting simply specifies the name and path of the snapshot control file.
Task #3: Execute RMAN Backups.
The next several steps will cover the following topics (click the link you need):
- Non-CDB database backup using a default RMAN configuration (RMAN Client).
- Non-CDB database backup using a modified RMAN configuration (RMAN Client).
- CDB database backup using a customized OS RMAN backup script.
- PDB database backup using Oracle Enterprise Manager (OEM).
- Customized OS RMAN backup script scheduled via OEM.
Step #1: Non-CDB Database Backup using a Default RMAN Configuration.
To get the ball rolling, let’s run a full backup of the T122 database using the RMAN configuration default settings. Before we do, let’s review the database datafile and fast recover area configuration:
SQL> select file_id, file_name from dba_data_files order by 1; FILE_ID FILE_NAME ------- ------------------------------------------------------1 /u02/oradata/T122/datafile/o1_mf_system_gwykl8n7_.dbf3 /u02/oradata/T122/datafile/o1_mf_sysaux_gwykmzwz_.dbf4 /u02/oradata/T122/datafile/o1_mf_undotbs1_gwyko31q_.dbf7 /u02/oradata/T122/datafile/o1_mf_users_gwyko450_.dbfSQL> show parameter db_recovery NAME TYPE VALUE ------------------------------------ ----------- -------------------------------- db_recovery_file_dest string /u07/oradata/fast_recovery_area db_recovery_file_dest_size big integer 15G
Use RMAN to connect to the target database (T122) and the RMAN recovery catalog:
[oracle@orasvr01 ~]$ rman target rmanbackup/rmanbackup using sysbackup connected to target database: T122 (DBID=2185934179) RMAN> connect catalog rco/rco@rmancat connected to recovery catalog database
Run a full backup of the database (backup database) and all archived redo logs (plus archivelog) and delete the archived redo logs once they’ve been backed up (delete input):
RMAN> backup database plus archivelog delete input;
Here’s the output and what it means.
Start the backup and force a log switch so the very latest archived redo log will be available for backup:
Starting backup at 02-DEC-19 current log archived
The default backup device type is disk (DEFAULT DEVICE TYPE TO DISK), only one channel will be autoallocated and the default backup type will be a backupset (DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET):
allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=37 device type=DISK
Next, determine the sequence numbers of all the archived redo logs to backup (1 thru 41):
channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=1 RECID=1 STAMP=1024437659 input archived log thread=1 sequence=2 RECID=2 STAMP=1024447784 input archived log thread=1 sequence=3 RECID=3 STAMP=1024477806 input archived log thread=1 sequence=4 RECID=4 STAMP=1024500030 input archived log thread=1 sequence=5 RECID=5 STAMP=1024521659 input archived log thread=1 sequence=6 RECID=6 STAMP=1024556416 input archived log thread=1 sequence=7 RECID=7 STAMP=1024575714 input archived log thread=1 sequence=8 RECID=8 STAMP=1024594921 input archived log thread=1 sequence=9 RECID=9 STAMP=1024613526 input archived log thread=1 sequence=10 RECID=10 STAMP=1024696811 input archived log thread=1 sequence=11 RECID=11 STAMP=1024754429 input archived log thread=1 sequence=12 RECID=12 STAMP=1024804863 input archived log thread=1 sequence=13 RECID=13 STAMP=1024881392 input archived log thread=1 sequence=14 RECID=14 STAMP=1024964870 input archived log thread=1 sequence=15 RECID=15 STAMP=1025047154 input archived log thread=1 sequence=16 RECID=16 STAMP=1025078971 input archived log thread=1 sequence=17 RECID=17 STAMP=1025100576 input archived log thread=1 sequence=18 RECID=18 STAMP=1025121648 input archived log thread=1 sequence=19 RECID=19 STAMP=1025140212 input archived log thread=1 sequence=20 RECID=20 STAMP=1025173238 input archived log thread=1 sequence=21 RECID=22 STAMP=1025266744 input archived log thread=1 sequence=22 RECID=21 STAMP=1025266744 input archived log thread=1 sequence=23 RECID=23 STAMP=1025266747 input archived log thread=1 sequence=24 RECID=24 STAMP=1025309156 input archived log thread=1 sequence=25 RECID=25 STAMP=1025388063 input archived log thread=1 sequence=26 RECID=26 STAMP=1025460122 input archived log thread=1 sequence=27 RECID=27 STAMP=1025488809 input archived log thread=1 sequence=28 RECID=28 STAMP=1025565012 input archived log thread=1 sequence=29 RECID=29 STAMP=1025615955 input archived log thread=1 sequence=30 RECID=30 STAMP=1025617566 input archived log thread=1 sequence=31 RECID=31 STAMP=1025651916 input archived log thread=1 sequence=32 RECID=32 STAMP=1025686140 input archived log thread=1 sequence=33 RECID=33 STAMP=1025708965 input archived log thread=1 sequence=34 RECID=34 STAMP=1025721293 input archived log thread=1 sequence=35 RECID=35 STAMP=1025739590 input archived log thread=1 sequence=36 RECID=36 STAMP=1025860224 input archived log thread=1 sequence=37 RECID=37 STAMP=1025881244 input archived log thread=1 sequence=38 RECID=38 STAMP=1025894465 input archived log thread=1 sequence=39 RECID=39 STAMP=1025917284 input archived log thread=1 sequence=40 RECID=40 STAMP=1025950607 input archived log thread=1 sequence=41 RECID=41 STAMP=1025950864 channel ORA_DISK_1: starting piece 1 at 02-DEC-19 channel ORA_DISK_1: finished piece 1 at 02-DEC-19
Next, back up these archived redo logs to the first backup piece of the first backup set. Without specifying an alternative location (using the FORMAT option), RMAN will write its backups to the FRA:
piece handle=/u07/oradata/fast_recovery_area/T122/backupset/2019_12_02/o1_mf_annnn_TAG20191202T102106_gybghlmv_.bkp tag=TAG20191 channel ORA_DISK_1: backup set complete, elapsed time: 00:02:35
Next, physically delete these archived redo logs from the FRA:
channel ORA_DISK_1: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_15/o1_mf_1_1_gwyx3rmb_.arc RECID=1 STAMP=10244376 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_16/o1_mf_1_2_gwz7063b_.arc RECID=2 STAMP=10244477 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_16/o1_mf_1_3_gx04bcto_.arc RECID=3 STAMP=10244778 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_16/o1_mf_1_4_gx0t0w1o_.arc RECID=4 STAMP=10245000 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_16/o1_mf_1_5_gx1h4rrn_.arc RECID=5 STAMP=10245216 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_17/o1_mf_1_6_gx2k2yk8_.arc RECID=6 STAMP=10245564 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_17/o1_mf_1_7_gx33y05v_.arc RECID=7 STAMP=10245757 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_17/o1_mf_1_8_gx3pp73y_.arc RECID=8 STAMP=10245949 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_17/o1_mf_1_9_gx48vnmo_.arc RECID=9 STAMP=10246135 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_18/o1_mf_1_10_gx6t6715_.arc RECID=10 STAMP=102469 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_19/o1_mf_1_11_gx8lgv91_.arc RECID=11 STAMP=102475 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_20/o1_mf_1_12_gxb3pxdd_.arc RECID=12 STAMP=102480 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_21/o1_mf_1_13_gxdggg37_.arc RECID=13 STAMP=102488 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_22/o1_mf_1_14_gxgzz3pr_.arc RECID=14 STAMP=102496 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_22/o1_mf_1_15_gxkjbjcl_.arc RECID=15 STAMP=102504 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_23/o1_mf_1_16_gxlhdsks_.arc RECID=16 STAMP=102507 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_23/o1_mf_1_17_gxm4hynr_.arc RECID=17 STAMP=102510 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_23/o1_mf_1_18_gxms2gff_.arc RECID=18 STAMP=102512 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_24/o1_mf_1_19_gxnc6l3s_.arc RECID=19 STAMP=102514 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_24/o1_mf_1_20_gxocgnb1_.arc RECID=20 STAMP=102517 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_25/o1_mf_1_21_gxr6rn2j_.arc RECID=22 STAMP=102526 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_25/o1_mf_1_22_gxr6rn2s_.arc RECID=21 STAMP=102526 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_25/o1_mf_1_23_gxr6rrt0_.arc RECID=23 STAMP=102526 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_26/o1_mf_1_24_gxsj61kz_.arc RECID=24 STAMP=102530 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_26/o1_mf_1_25_gxvx7wlm_.arc RECID=25 STAMP=102538 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_27/o1_mf_1_26_gxy3mqtd_.arc RECID=26 STAMP=102546 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_28/o1_mf_1_27_gxyzn6yl_.arc RECID=27 STAMP=102548 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_28/o1_mf_1_28_gy1b1kql_.arc RECID=28 STAMP=102556 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_29/o1_mf_1_29_gy2vskbn_.arc RECID=29 STAMP=102561 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_29/o1_mf_1_30_gy2xcy7p_.arc RECID=30 STAMP=102561 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_29/o1_mf_1_31_gy3yxbfb_.arc RECID=31 STAMP=102565 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_30/o1_mf_1_32_gy50bso9_.arc RECID=32 STAMP=102568 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_30/o1_mf_1_33_gy5pn2m9_.arc RECID=33 STAMP=102570 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_30/o1_mf_1_34_gy62ocd8_.arc RECID=34 STAMP=102572 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_11_30/o1_mf_1_35_gy6nk3nj_.arc RECID=35 STAMP=102573 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_12_01/o1_mf_1_36_gy7oyxnv_.arc RECID=36 STAMP=102586 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_12_01/o1_mf_1_37_gy8bhsdj_.arc RECID=37 STAMP=102588 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_12_01/o1_mf_1_38_gy8qdz3v_.arc RECID=38 STAMP=102589 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_12_02/o1_mf_1_39_gy9fp27c_.arc RECID=39 STAMP=102591 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_12_02/o1_mf_1_40_gybg7gcr_.arc RECID=40 STAMP=102595 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_12_02/o1_mf_1_41_gybghjoc_.arc RECID=41 STAMP=102595 Finished backup at 02-DEC-19
Next, determine the datafiles to backup:
Starting backup at 02-DEC-19 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00003 name=/u02/oradata/T122/datafile/o1_mf_sysaux_gwykmzwz_.dbf input datafile file number=00001 name=/u02/oradata/T122/datafile/o1_mf_system_gwykl8n7_.dbf input datafile file number=00004 name=/u02/oradata/T122/datafile/o1_mf_undotbs1_gwyko31q_.dbf input datafile file number=00007 name=/u02/oradata/T122/datafile/o1_mf_users_gwyko450_.dbf channel ORA_DISK_1: starting piece 1 at 02-DEC-19 channel ORA_DISK_1: finished piece 1 at 02-DEC-19
Next, backup these datafiles to the first backup piece of a new backup set:
piece handle=/u07/oradata/fast_recovery_area/T122/backupset/2019_12_02/o1_mf_nnndf_TAG20191202T102345_gybgnm0b_.bkp tag=TAG20191 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55 Finished backup at 02-DEC-19
Next, find any archived redo logs which were not backed up earlier (sequence #42):
Starting backup at 02-DEC-19 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=42 RECID=42 STAMP=1025951082 channel ORA_DISK_1: starting piece 1 at 02-DEC-19 channel ORA_DISK_1: finished piece 1 at 02-DEC-19
Next, backup that archived redo log to the first backup piece of a new backup set, then delete the archived redo log file:
piece handle=/u07/oradata/fast_recovery_area/T122/backupset/2019_12_02/o1_mf_annnn_TAG20191202T102443_gybgpcog_.bkp tag=TAG20191 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_12_02/o1_mf_1_42_gybgpb5y_.arc RECID=42 STAMP=102595 Finished backup at 02-DEC-19
Finally, backup the database control file and SPFILE (CONTROLFILE AUTOBACKUP ON) to the first backup piece of a new backup set:
Starting Control File and SPFILE Autobackup at 02-DEC-19 piece handle=/u07/oradata/fast_recovery_area/T122/autobackup/2019_12_02/o1_mf_s_1025951085_gybgphgj_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 02-DEC-19
So we should end up with 3 backup set piece files in …/T122/backupset/2019_12_02:
[oracle@orasvr01 2019_12_02]$ pwd /u07/oradata/fast_recovery_area/T122/backupset/2019_12_02 [oracle@orasvr01 2019_12_02]$ ls -l -rw-r----- 1 oracle oinstall 6836020224 Dec 2 10:23 o1_mf_annnn_TAG20191202T102106_gybghlmv_.bkp -rw-r----- 1 oracle oinstall 32256 Dec 2 10:24 o1_mf_annnn_TAG20191202T102443_gybgpcog_.bkp -rw-r----- 1 oracle oinstall 1887117312 Dec 2 10:24 o1_mf_nnndf_TAG20191202T102345_gybgnm0b_.bkp
And a backup set piece file in …/T122/autobackup/2019_12_02
[oracle@orasvr01 2019_12_02]$ pwd /u07/oradata/fast_recovery_area/T122/autobackup/2019_12_02 [oracle@orasvr01 2019_12_02]$ ls -l -rw-r----- 1 oracle oinstall 10731520 Dec 2 10:24 o1_mf_s_1025951085_gybgphgj_.bkp
Step #2: Non-CDB Database Backup using a Modified RMAN Configuration.
As we saw in Step #1, a single RMAN command leveraging the default RMAN configuration settings can create a perfectly usable backup in a pre-configured FRA. However, you’re likely to want to modify your backup strategy. There are two ways to do this. The first is to change the RMAN configuration defaults. The second way is to run a customized RMAN script which we’ll get to in Step #3.
Sticking with the T122 database, log into RMAN and the catalog then change some RMAN configuration defaults. We’ll change the retention policy to 3 days, up the parallelism to 2, change the location of the snapshot control file away from being underneath ORACLE_HOME and turn on optimization. These are all changes you’d likely want to make in a production environment. Not perhaps these exact values, but certainly changes to these defaults. Finally, we’ll switch to high compression. This would require a license for the Advanced Compression Option, but I don’t have huge amounts of spare disk so it seemed like a good idea.
[oracle@orasvr01 ~]$ rman target rmanbackup/rmanbackup using sysbackup connected to target database: T122 (DBID=2185934179) RMAN> connect catalog rco/rco@rmancat connected to recovery catalog database RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS; new RMAN configuration parameters: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET; new RMAN configuration parameters: CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/nas/backups/T122/snapcf_T122.f'; new RMAN configuration parameters: CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/nas/backups/T122/snapcf_T122.f'; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete RMAN> CONFIGURE BACKUP OPTIMIZATION ON; new RMAN configuration parameters: CONFIGURE BACKUP OPTIMIZATION ON; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete RMAN> CONFIGURE COMPRESSION ALGORITHM 'HIGH'; new RMAN configuration parameters: CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete RMAN> show all; RMAN configuration parameters for database with db_unique_name T122 are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS; CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE; CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/nas/backups/T122/snapcf_T122.f';
Now check what the catalog has recorded by logging into the recovery catalog database as the recovery catalog owner:
[oracle@oraemcc ~]$ . oraenv
ORACLE_SID = [oracle] ? PADMIN
[oracle@oraemcc ~]$ sqlplus rco/rco@rmancat
SQL> col c_name format a30 heading 'RMAN Config Parameter'
SQL> col d_name format a10 heading 'DB Name'
SQL> col c_value format a55 heading 'RMAN Config Value'
SQL> select d.name d_name, c.name c_name, c.value c_value
from rc_database d, rc_rman_configuration c
where d.db_key = c.db_key
order by d.name, c.name;
DB Name RMAN Config Parameter RMAN Config Value
---------- ------------------------------ -----------------------------------------------------
T122 BACKUP OPTIMIZATION ON
T122 COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE
T122 DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET
T122 RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS
T122 SNAPSHOT CONTROLFILE NAME TO '/nas/backups/T122/snapcf_T122.f'
Now run the same backup as in Step #1. This time the output should be much less and hopefully more understandable:
[oracle@orasvr01 ~]$ rman target rmanbackup/rmanbackup using sysbackup connected to target database: T122 (DBID=2185934179) RMAN> connect catalog rco/rco@rmancat connected to recovery catalog database RMAN> backup database plus archivelog delete input; Starting backup at 04-DEC-19 current log archived allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=285 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=39 device type=DISK channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=43 RECID=43 STAMP=1026000005 input archived log thread=1 sequence=44 RECID=44 STAMP=1026079251 channel ORA_DISK_1: starting piece 1 at 04-DEC-19 channel ORA_DISK_2: starting archived log backup set channel ORA_DISK_2: specifying archived log(s) in backup set input archived log thread=1 sequence=45 RECID=45 STAMP=1026147982 channel ORA_DISK_2: starting piece 1 at 04-DEC-19 channel ORA_DISK_1: finished piece 1 at 04-DEC-19 piece handle=/u07/oradata/fast_recovery_area/T122/backupset/2019_12_04/o1_mf_annnn_TAG20191204T170624_gyjgzjtf_.bkp tag=TAG20191204T170624 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 channel ORA_DISK_1: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_12_03/o1_mf_1_43_gycyh3fw_.arc RECID=43 STAMP=1026000005 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_12_03/o1_mf_1_44_gygcvk6l_.arc RECID=44 STAMP=1026079251 channel ORA_DISK_2: finished piece 1 at 04-DEC-19 piece handle=/u07/oradata/fast_recovery_area/T122/backupset/2019_12_04/o1_mf_annnn_TAG20191204T170624_gyjgzjvj_.bkp tag=TAG20191204T170624 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:00:08 channel ORA_DISK_1: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_12_04/o1_mf_1_45_gyjgzdks_.arc RECID=45 STAMP=1026147982 Finished backup at 04-DEC-19 Starting backup at 04-DEC-19 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00003 name=/u02/oradata/T122/datafile/o1_mf_sysaux_gwykmzwz_.dbf input datafile file number=00007 name=/u02/oradata/T122/datafile/o1_mf_users_gwyko450_.dbf channel ORA_DISK_1: starting piece 1 at 04-DEC-19 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00001 name=/u02/oradata/T122/datafile/o1_mf_system_gwykl8n7_.dbf input datafile file number=00004 name=/u02/oradata/T122/datafile/o1_mf_undotbs1_gwyko31q_.dbf channel ORA_DISK_2: starting piece 1 at 04-DEC-19 channel ORA_DISK_1: finished piece 1 at 04-DEC-19 piece handle=/u07/oradata/fast_recovery_area/T122/backupset/2019_12_04/o1_mf_nnndf_TAG20191204T170632_gyjgzthp_.bkp tag=TAG20191204T170632 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 channel ORA_DISK_2: finished piece 1 at 04-DEC-19 piece handle=/u07/oradata/fast_recovery_area/T122/backupset/2019_12_04/o1_mf_nnndf_TAG20191204T170632_gyjgztjw_.bkp tag=TAG20191204T170632 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:00:25 Finished backup at 04-DEC-19 Starting backup at 04-DEC-19 current log archived using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=46 RECID=46 STAMP=1026148019 channel ORA_DISK_1: starting piece 1 at 04-DEC-19 channel ORA_DISK_1: finished piece 1 at 04-DEC-19 piece handle=/u07/oradata/fast_recovery_area/T122/backupset/2019_12_04/o1_mf_annnn_TAG20191204T170700_gyjh0ns3_.bkp tag= TAG20191204T170700 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2019_12_04/o1_mf_1_46_gyjh0mlt_.arc RECID=46 STAMP=1026148019 Finished backup at 04-DEC-19 Starting Control File and SPFILE Autobackup at 04-DEC-19 piece handle=/u07/oradata/fast_recovery_area/T122/autobackup/2019_12_04/o1_mf_s_1026148022_gyjh0rng_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 04-DEC-19
Notice RMAN utilized two channels (ORA_DISK_1 and ORA_DISK_2) because parallelism was set to 2. Consequently, more backup set piece files were created, but much smaller than before. Fewer archived redo logs were backed up, but compression was also in play. Speaking of archived redo logs, notice how the backup started (sequence 43) where the previous backup had left off (sequence 42). These were the files created:
[oracle@orasvr01 2019_12_04]$ pwd /u07/oradata/fast_recovery_area/T122/backupset/2019_12_04 [oracle@orasvr01 2019_12_04]$ ls -l -rw-r----- 1 oracle oinstall 358109696 Dec 4 17:06 o1_mf_annnn_TAG20191204T170624_gyjgzjtf_.bkp -rw-r----- 1 oracle oinstall 162172416 Dec 4 17:06 o1_mf_annnn_TAG20191204T170624_gyjgzjvj_.bkp -rw-r----- 1 oracle oinstall 7168 Dec 4 17:07 o1_mf_annnn_TAG20191204T170700_gyjh0ns3_.bkp -rw-r----- 1 oracle oinstall 1166360576 Dec 4 17:06 o1_mf_nnndf_TAG20191204T170632_gyjgzthp_.bkp -rw-r----- 1 oracle oinstall 745152512 Dec 4 17:06 o1_mf_nnndf_TAG20191204T170632_gyjgztjw_.bkp [oracle@orasvr01 2019_12_04]$ pwd /u07/oradata/fast_recovery_area/T122/autobackup/2019_12_04 [oracle@orasvr01 2019_12_04]$ ls -l -rw-r----- 1 oracle oinstall 10731520 Dec 4 17:07 o1_mf_s_1026148022_gyjh0rng_.bkp
Step #3: CDB Database Backup using a Customized OS RMAN Backup Script.
It’s becoming increasingly popular to not just backup to disk, but to backup to a backup appliance or even to the Cloud. Oracle has their own appliance of course (Oracle ZFS Storage Appliance), but there are others including the Dell EMC Avamar system. Third party backup storage appliances follow a similar approch when interfacing with RMAN. Some standard RMAN library files are replaced by third party library files and the RMAN commands think they’re accessing SBT devices, when in fact they’re accessing a sophisticated disk storage array. These devices often come with high end functionality including compression, deduplication and even encryption. These features can be completely transparent to RMAN/Oracle and they save you having to purchase additional Oracle Database option licenses. Result!
If you configure a FRA, then RMAN will use it for backups by default. However, if you’re writing to one of these disk storage arrays, you need a way to specify an alternative path to where you want to write your backups. In addition, by not using a FRA for backups, you often need to worry about managing directory hierarchies and backup retention. It’s also very common to use shell scripts to run database backups. So for this next example backup, we’ll use a simple script to demonstrate how to override the previous DEVICE TYPE DISK PARALLELISM 2 configuration setting and how to capture the RMAN output in a log file.
Note, this simple script would work for a non-CDB or a CDB. If the target were a CDB (which in our case it is), it will backup all the PDBs by default. The backup of an individual PDB is a little different and we’ll cover that in Step #4. Here’s the simple script:
#!/usr/bin/ksh
# Program : backup_db_arl.sh
# Date : 01-DEC-19
# Author : Sean Francis
# Purpose : Run an RMAN full DB backup plus archived redo logs then delete the backed up
# archived redo logs.
#
# input parameters
export ORACLE_SID=${1}
# local variables
ORACLE_BASE=/u01/app/oracle
LOCAL_BIN_DIR=/usr/local/bin
BACKUP_ROOT_DIR=/nas/backups
DATE_MASK=date +%F_%H:%M
LOG_FILE=${BACKUP_ROOT_DIR}/RMAN_LOGS/${ORACLE_SID}/${DATE_MASK}/backup_db_arl_${DATE_MASK}.log
# set environment
ORAENV_ASK=NO
. ${LOCAL_BIN_DIR}/oraenv
TNS_ADMIN=${ORACLE_HOME}/network/admin
# create log and backup directories
mkdir -p ${BACKUP_ROOT_DIR}/RMAN_LOGS/${ORACLE_SID}/${DATE_MASK}
mkdir -p ${BACKUP_ROOT_DIR}/${ORACLE_SID}/${DATE_MASK}
# run the backup
${ORACLE_HOME}/bin/rman target rmanbackup/rmanbackup using sysbackup log=${LOG_FILE} <<EOF
connect catalog rco/rco@rmancat
run
{
allocate channel D1 type disk format '${BACKUP_ROOT_DIR}/${ORACLE_SID}/${DATE_MASK}/%d_%U.bkp';
allocate channel D2 type disk format '${BACKUP_ROOT_DIR}/${ORACLE_SID}/${DATE_MASK}/%d_%U.bkp';
allocate channel D3 type disk format '${BACKUP_ROOT_DIR}/${ORACLE_SID}/${DATE_MASK}/%d_%U.bkp';
backup as compressed backupset database plus archivelog delete input;
release channel D1;
release channel D2;
release channel D3;
}
exit;
EOF
# eof backup_db_arl.sh
Run (or schedule via cron) the backup script for the T183 CDB:
[oracle@orasvr02 scripts]$ ./backup_db_arl.sh T183 The Oracle base remains unchanged with value /u01/app/oracle RMAN> RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> RMAN> [oracle@orasvr02 scripts]$
This is the output:
Recovery Manager: Release 18.0.0.0.0 - Production on Mon Dec 9 13:24:24 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. connected to target database: T183 (DBID=2832597398) RMAN> connected to recovery catalog database RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> allocated channel: D1 channel D1: SID=292 device type=DISK allocated channel: D2 channel D2: SID=60 device type=DISK allocated channel: D3 channel D3: SID=37 device type=DISK Starting backup at 09-DEC-19 current log archived channel D1: starting compressed archived log backup set channel D1: specifying archived log(s) in backup set input archived log thread=1 sequence=18 RECID=15 STAMP=1025119712 input archived log thread=1 sequence=19 RECID=16 STAMP=1025136550 input archived log thread=1 sequence=20 RECID=17 STAMP=1025160577 input archived log thread=1 sequence=21 RECID=18 STAMP=1025173800 input archived log thread=1 sequence=22 RECID=19 STAMP=1025188822 input archived log thread=1 sequence=23 RECID=20 STAMP=1025964787 input archived log thread=1 sequence=24 RECID=21 STAMP=1025964799 input archived log thread=1 sequence=25 RECID=22 STAMP=1025964799 input archived log thread=1 sequence=26 RECID=23 STAMP=1026000637 input archived log thread=1 sequence=27 RECID=24 STAMP=1026039645 input archived log thread=1 sequence=28 RECID=25 STAMP=1026083231 input archived log thread=1 sequence=29 RECID=26 STAMP=1026151136 input archived log thread=1 sequence=30 RECID=27 STAMP=1026178177 input archived log thread=1 sequence=31 RECID=28 STAMP=1026252040 channel D1: starting piece 1 at 09-DEC-19 channel D2: starting compressed archived log backup set channel D2: specifying archived log(s) in backup set input archived log thread=1 sequence=4 RECID=1 STAMP=1024605078 input archived log thread=1 sequence=5 RECID=2 STAMP=1024675302 input archived log thread=1 sequence=6 RECID=3 STAMP=1024696941 input archived log thread=1 sequence=7 RECID=4 STAMP=1024707721 input archived log thread=1 sequence=8 RECID=5 STAMP=1024779646 input archived log thread=1 sequence=9 RECID=6 STAMP=1024812029 input archived log thread=1 sequence=10 RECID=7 STAMP=1024873374 input archived log thread=1 sequence=11 RECID=8 STAMP=1024941624 input archived log thread=1 sequence=12 RECID=9 STAMP=1024967993 input archived log thread=1 sequence=13 RECID=10 STAMP=1025030790 input archived log thread=1 sequence=14 RECID=11 STAMP=1025053265 input archived log thread=1 sequence=15 RECID=12 STAMP=1025075267 input archived log thread=1 sequence=16 RECID=13 STAMP=1025089070 input archived log thread=1 sequence=17 RECID=14 STAMP=1025104692 channel D2: starting piece 1 at 09-DEC-19 channel D3: starting compressed archived log backup set channel D3: specifying archived log(s) in backup set input archived log thread=1 sequence=32 RECID=29 STAMP=1026316826 input archived log thread=1 sequence=33 RECID=30 STAMP=1026345804 input archived log thread=1 sequence=34 RECID=31 STAMP=1026371055 input archived log thread=1 sequence=35 RECID=32 STAMP=1026385467 input archived log thread=1 sequence=36 RECID=33 STAMP=1026402886 input archived log thread=1 sequence=37 RECID=34 STAMP=1026416108 input archived log thread=1 sequence=38 RECID=35 STAMP=1026435952 input archived log thread=1 sequence=39 RECID=36 STAMP=1026458177 input archived log thread=1 sequence=40 RECID=37 STAMP=1026475236 input archived log thread=1 sequence=41 RECID=38 STAMP=1026493261 input archived log thread=1 sequence=42 RECID=39 STAMP=1026506247 input archived log thread=1 sequence=43 RECID=40 STAMP=1026537181 input archived log thread=1 sequence=44 RECID=41 STAMP=1026566676 channel D3: starting piece 1 at 09-DEC-19 channel D3: finished piece 1 at 09-DEC-19 piece handle=/nas/backups/T183/2019-12-09_13:24/T183_05uj0ags_1_1.bkp tag=TAG20191209T132439 comment=NONE channel D3: backup set complete, elapsed time: 00:06:45 channel D3: deleting archived log(s) archived log file name=+RECO/T183/ARCHIVELOG/2019_12_06/thread_1_seq_32.290.1026316821 RECID=29 STAMP=1026316826 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_07/thread_1_seq_33.291.1026345799 RECID=30 STAMP=1026345804 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_07/thread_1_seq_34.292.1026371049 RECID=31 STAMP=1026371055 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_07/thread_1_seq_35.293.1026385463 RECID=32 STAMP=1026385467 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_07/thread_1_seq_36.294.1026402881 RECID=33 STAMP=1026402886 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_07/thread_1_seq_37.295.1026416103 RECID=34 STAMP=1026416108 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_08/thread_1_seq_38.296.1026435947 RECID=35 STAMP=1026435952 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_08/thread_1_seq_39.297.1026458173 RECID=36 STAMP=1026458177 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_08/thread_1_seq_40.298.1026475231 RECID=37 STAMP=1026475236 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_08/thread_1_seq_41.299.1026493257 RECID=38 STAMP=1026493261 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_08/thread_1_seq_42.300.1026506243 RECID=39 STAMP=1026506247 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_09/thread_1_seq_43.301.1026537177 RECID=40 STAMP=1026537181 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_09/thread_1_seq_44.302.1026566675 RECID=41 STAMP=1026566676 channel D1: finished piece 1 at 09-DEC-19 piece handle=/nas/backups/T183/2019-12-09_13:24/T183_03uj0agq_1_1.bkp tag=TAG20191209T132439 comment=NONE channel D1: backup set complete, elapsed time: 00:07:42 channel D1: deleting archived log(s) archived log file name=+RECO/T183/ARCHIVELOG/2019_11_23/thread_1_seq_18.276.1025119707 RECID=15 STAMP=1025119712 archived log file name=+RECO/T183/ARCHIVELOG/2019_11_24/thread_1_seq_19.277.1025136545 RECID=16 STAMP=1025136550 archived log file name=+RECO/T183/ARCHIVELOG/2019_11_24/thread_1_seq_20.278.1025160573 RECID=17 STAMP=1025160577 archived log file name=+RECO/T183/ARCHIVELOG/2019_11_24/thread_1_seq_21.279.1025173795 RECID=18 STAMP=1025173800 archived log file name=+RECO/T183/ARCHIVELOG/2019_11_24/thread_1_seq_22.280.1025188817 RECID=19 STAMP=1025188822 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_02/thread_1_seq_23.281.1025964781 RECID=20 STAMP=1025964787 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_02/thread_1_seq_24.282.1025964787 RECID=21 STAMP=1025964799 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_02/thread_1_seq_25.283.1025964787 RECID=22 STAMP=1025964799 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_03/thread_1_seq_26.284.1026000629 RECID=23 STAMP=1026000637 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_03/thread_1_seq_27.285.1026039639 RECID=24 STAMP=1026039645 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_03/thread_1_seq_28.286.1026083227 RECID=25 STAMP=1026083231 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_04/thread_1_seq_29.287.1026151131 RECID=26 STAMP=1026151136 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_05/thread_1_seq_30.288.1026178173 RECID=27 STAMP=1026178177 archived log file name=+RECO/T183/ARCHIVELOG/2019_12_05/thread_1_seq_31.289.1026252033 RECID=28 STAMP=1026252040 channel D2: finished piece 1 at 09-DEC-19 piece handle=/nas/backups/T183/2019-12-09_13:24/T183_04uj0agq_1_1.bkp tag=TAG20191209T132439 comment=NONE channel D2: backup set complete, elapsed time: 00:07:55 channel D1: deleting archived log(s) archived log file name=+RECO/T183/ARCHIVELOG/2019_11_17/thread_1_seq_4.264.1024605073 RECID=1 STAMP=1024605078 archived log file name=+RECO/T183/ARCHIVELOG/2019_11_18/thread_1_seq_5.263.1024675297 RECID=2 STAMP=1024675302 archived log file name=+RECO/T183/ARCHIVELOG/2019_11_18/thread_1_seq_6.262.1024696935 RECID=3 STAMP=1024696941 archived log file name=+RECO/T183/ARCHIVELOG/2019_11_19/thread_1_seq_7.256.1024707717 RECID=4 STAMP=1024707721 archived log file name=+RECO/T183/ARCHIVELOG/2019_11_19/thread_1_seq_8.258.1024779641 RECID=5 STAMP=1024779646 archived log file name=+RECO/T183/ARCHIVELOG/2019_11_20/thread_1_seq_9.257.1024812025 RECID=6 STAMP=1024812029 archived log file name=+RECO/T183/ARCHIVELOG/2019_11_20/thread_1_seq_10.259.1024873369 RECID=7 STAMP=1024873374 archived log file name=+RECO/T183/ARCHIVELOG/2019_11_21/thread_1_seq_11.268.1024941619 RECID=8 STAMP=1024941624 archived log file name=+RECO/T183/ARCHIVELOG/2019_11_22/thread_1_seq_12.269.1024967987 RECID=9 STAMP=1024967993 archived log file name=+RECO/T183/ARCHIVELOG/2019_11_22/thread_1_seq_13.271.1025030785 RECID=10 STAMP=1025030790 archived log file name=+RECO/T183/ARCHIVELOG/2019_11_23/thread_1_seq_14.272.1025053259 RECID=11 STAMP=1025053265 archived log file name=+RECO/T183/ARCHIVELOG/2019_11_23/thread_1_seq_15.273.1025075261 RECID=12 STAMP=1025075267 archived log file name=+RECO/T183/ARCHIVELOG/2019_11_23/thread_1_seq_16.274.1025089065 RECID=13 STAMP=1025089070 archived log file name=+RECO/T183/ARCHIVELOG/2019_11_23/thread_1_seq_17.275.1025104687 RECID=14 STAMP=1025104692 Finished backup at 09-DEC-19 Starting backup at 09-DEC-19 channel D1: starting compressed full datafile backup set channel D1: specifying datafile(s) in backup set input datafile file number=00003 name=+DATA/T183/DATAFILE/sysaux.261.1024601085 input datafile file number=00007 name=+DATA/T183/DATAFILE/users.269.1024601111 channel D1: starting piece 1 at 09-DEC-19 channel D2: starting compressed full datafile backup set channel D2: specifying datafile(s) in backup set input datafile file number=00001 name=+DATA/T183/DATAFILE/system.270.1024601039 input datafile file number=00004 name=+DATA/T183/DATAFILE/undotbs1.274.1024601109 channel D2: starting piece 1 at 09-DEC-19 channel D3: starting compressed full datafile backup set channel D3: specifying datafile(s) in backup set input datafile file number=00010 name=+DATA/T183/9796845BE029589CE0531200A8C01B41/DATAFILE/sysaux.259.1024604111 input datafile file number=00012 name=+DATA/T183/9796845BE029589CE0531200A8C01B41/DATAFILE/users.257.1025001295 channel D3: starting piece 1 at 09-DEC-19 channel D3: finished piece 1 at 09-DEC-19 piece handle=/nas/backups/T183/2019-12-09_13:24/T183_08uj0avv_1_1.bkp tag=TAG20191209T133242 comment=NONE channel D3: backup set complete, elapsed time: 00:01:45 channel D3: starting compressed full datafile backup set channel D3: specifying datafile(s) in backup set input datafile file number=00009 name=+DATA/T183/9796845BE029589CE0531200A8C01B41/DATAFILE/system.267.1024604111 input datafile file number=00011 name=+DATA/T183/9796845BE029589CE0531200A8C01B41/DATAFILE/undotbs1.266.1024604111 channel D3: starting piece 1 at 09-DEC-19 channel D2: finished piece 1 at 09-DEC-19 piece handle=/nas/backups/T183/2019-12-09_13:24/T183_07uj0avu_1_1.bkp tag=TAG20191209T133242 comment=NONE channel D2: backup set complete, elapsed time: 00:02:40 channel D2: starting compressed full datafile backup set channel D2: specifying datafile(s) in backup set input datafile file number=00006 name=+DATA/T183/64A52F53A7693286E053CDA9E80AED76/DATAFILE/sysaux.272.1024601571 channel D2: starting piece 1 at 09-DEC-19 channel D3: finished piece 1 at 09-DEC-19 piece handle=/nas/backups/T183/2019-12-09_13:24/T183_09uj0b38_1_1.bkp tag=TAG20191209T133242 comment=NONE channel D3: backup set complete, elapsed time: 00:00:56 channel D3: starting compressed full datafile backup set channel D3: specifying datafile(s) in backup set input datafile file number=00005 name=+DATA/T183/64A52F53A7693286E053CDA9E80AED76/DATAFILE/system.271.1024601571 channel D3: starting piece 1 at 09-DEC-19 channel D3: finished piece 1 at 09-DEC-19 piece handle=/nas/backups/T183/2019-12-09_13:24/T183_0buj0b50_1_1.bkp tag=TAG20191209T133242 comment=NONE channel D3: backup set complete, elapsed time: 00:00:55 channel D3: starting compressed full datafile backup set channel D3: specifying datafile(s) in backup set input datafile file number=00008 name=+DATA/T183/64A52F53A7693286E053CDA9E80AED76/DATAFILE/undotbs1.273.1024601571 channel D3: starting piece 1 at 09-DEC-19 channel D2: finished piece 1 at 09-DEC-19 piece handle=/nas/backups/T183/2019-12-09_13:24/T183_0auj0b50_1_1.bkp tag=TAG20191209T133242 comment=NONE channel D2: backup set complete, elapsed time: 00:01:03 channel D3: finished piece 1 at 09-DEC-19 piece handle=/nas/backups/T183/2019-12-09_13:24/T183_0cuj0b6n_1_1.bkp tag=TAG20191209T133242 comment=NONE channel D3: backup set complete, elapsed time: 00:00:15 channel D1: finished piece 1 at 09-DEC-19 piece handle=/nas/backups/T183/2019-12-09_13:24/T183_06uj0avu_1_1.bkp tag=TAG20191209T133242 comment=NONE channel D1: backup set complete, elapsed time: 00:04:03 Finished backup at 09-DEC-19 Starting backup at 09-DEC-19 current log archived channel D1: starting compressed archived log backup set channel D1: specifying archived log(s) in backup set input archived log thread=1 sequence=45 RECID=42 STAMP=1026567409 channel D1: starting piece 1 at 09-DEC-19 channel D1: finished piece 1 at 09-DEC-19 piece handle=/nas/backups/T183/2019-12-09_13:24/T183_0duj0b7k_1_1.bkp tag=TAG20191209T133652 comment=NONE channel D1: backup set complete, elapsed time: 00:00:01 channel D1: deleting archived log(s) archived log file name=+RECO/T183/ARCHIVELOG/2019_12_09/thread_1_seq_45.275.1026567409 RECID=42 STAMP=1026567409 Finished backup at 09-DEC-19 Starting Control File and SPFILE Autobackup at 09-DEC-19 piece handle=+RECO/T183/AUTOBACKUP/2019_12_09/s_1026567417.275.1026567419 comment=NONE Finished Control File and SPFILE Autobackup at 09-DEC-19 released channel: D1 released channel: D2 released channel: D3 RMAN> Recovery Manager complete.
Let’s check what we end up with in NAS file system storage. These are the backup set piece files:
[oracle@orasvr02 2019-12-09_13:24]$ pwd /nas/backups/T183/2019-12-09_13:24 [oracle@orasvr02 2019-12-09_13:24]$ ls -l -rw-r----- 1 oracle asmadmin 499011072 Dec 9 13:32 T183_03uj0agq_1_1.bkp -rw-r----- 1 oracle asmadmin 489146880 Dec 9 13:32 T183_04uj0agq_1_1.bkp -rw-r----- 1 oracle asmadmin 431110656 Dec 9 13:31 T183_05uj0ags_1_1.bkp -rw-r----- 1 oracle asmadmin 220266496 Dec 9 13:36 T183_06uj0avu_1_1.bkp -rw-r----- 1 oracle asmadmin 183091200 Dec 9 13:35 T183_07uj0avu_1_1.bkp -rw-r----- 1 oracle asmadmin 122044416 Dec 9 13:34 T183_08uj0avv_1_1.bkp -rw-r----- 1 oracle asmadmin 56844288 Dec 9 13:35 T183_09uj0b38_1_1.bkp -rw-r----- 1 oracle asmadmin 104628224 Dec 9 13:36 T183_0auj0b50_1_1.bkp -rw-r----- 1 oracle asmadmin 54976512 Dec 9 13:36 T183_0buj0b50_1_1.bkp -rw-r----- 1 oracle asmadmin 6676480 Dec 9 13:36 T183_0cuj0b6n_1_1.bkp -rw-r----- 1 oracle asmadmin 465408 Dec 9 13:36 T183_0duj0b7k_1_1.bkp
This is the auto-backup of the control file and SPFILE in ASM:
ASMCMD> pwd +RECO/T183/autobackup/2019_12_09 ASMCMD> ls -l Type Redund Striped Time Sys Name AUTOBACKUP UNPROT COARSE DEC 09 13:00:00 Y s_1026567417.275.1026567419
Step #4: PDB Database Backup using Oracle Enterprise Manager.
So far we’ve been explicitly connecting to the target database and the recovery catalog before running the backup. For an Oracle Enterprise Manager (OEM) based backup to take advantage of the recovery catalog, two things need to happen. First, you have to declare the presence of the catalog so OEM knows about it. Second, you have to tell OEM to use it when you backup a specific database.
To tell OEM about an existing catalog, navigate to the databases home page, then choose Availability ➡️ Recovery Catalogs. Click the Add button, then use the magnifying glass to choose the RMAN catalog database (PADMIN.mynet.com_RMANCAT):
On the next screen you need to provide 3 sets of login credentials. The recovery catalog owner (RCO), the recovery catalog database server host (oracle) and a DBA user in the recovery catalog database (RMANCAT_ADMIN). Note, the admin user of a PDB does not have DBA privileges by default. It needs to be granted DBA in order to become a true administrator database account:
Now that OEM knows about the RMAN recovery catalog, we need to tell it to use it when backing up a given database. From a given database home page (we’ll use T183_PDB1), choose Availability ➡️ Backup & Recovery ➡️ Recovery Catalog Settings. This will actually take you to a database login page for the PDB’s container database (T183):
Clicking OK on the screen above takes you back to the database home page of the CDB (T183). Before we get to run an actual backup, we need to go through some RMAN setup for the benefit of OEM. Again, from the database home page choose Availability ➡️ Backup & Recovery ➡️ Backup Settings:
Now it’s time to navigate the actual database backup screens. Choose Availability ➡️ Backup & Recovery ➡️ Schedule Backup. That will display the Database Login screen for the CDB (T183):
On the next screen you can review and make changes to the RMAN script which OEM has generated based upon your inputs. If you need to make changes you may as well just write your own script to begin with. We’ll get to that later.
As the job executes its output can be viewed via the next screen. Every time I tested this I saw the ORA-24327 error, but the backup always completed successfully. I tried various things to make the error go away without success. Just another reason to code the backup script yourself.
Let’s check to see what happened on disk (not that I’m paranoid or anything):
ASMCMD> pwd +RECO/T183/AUTOBACKUP/2019_12_13 ASMCMD> ls -l Type Redund Striped Time Sys Name AUTOBACKUP UNPROT COARSE DEC 13 10:00:00 Y s_1026902242.262.1026902245 [grid@orasvr02 T183_PDB1]$ pwd /nas/backups/T183/T183_PDB1 [grid@orasvr02 T183_PDB1]$ ls -lrt -rw-r----- 1 oracle asmadmin 1105920 Dec 13 10:35 T183_0tujai36_1_1 -rw-r----- 1 oracle asmadmin 1286144 Dec 13 10:35 T183_0uujai36_1_1 -rw-r----- 1 oracle asmadmin 56901632 Dec 13 10:35 T183_0sujai36_1_1 -rw-r----- 1 oracle asmadmin 123133952 Dec 13 10:36 T183_0rujai36_1_1 -rw-r----- 1 oracle asmadmin 30549504 Dec 13 10:37 T183_10ujai61_1_1 -rw-r----- 1 oracle asmadmin 34191872 Dec 13 10:37 T183_0vujai61_1_1
Looks good. We wrap up RMAN backups using our own customized script and schedule it via Enterprise Manager.
Step #5: Customized OS RMAN Backup Script Scheduled Via OEM.
